Creating Service on PVE >> IP-related SQL Error (fix: save configproducts.php?action=edit&id=...#tab=3)

[rchen9012]

whmcs 8.7.3
mysql 8.0.43

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near

') where i.ipaddress not in(select ipaddress from mod_pvewhmcs_vms) limit 1' at line 1

SQL:

select ipaddress,mask,gateway from mod_pvewhmcs_ip_addresses i INNER JOIN mod_pvewhmcs_ip_pools p on (i.pool_id=p.id and p.id=) where i.ipaddress not in(select ipaddress from mod_pvewhmcs_vms) limit 1)

[lsthompson]

Thanks for this. Do you mean when adding a new Service Plan, or actually trying to order a Service?

It seems related to the IP Address side of it. Can you please share your IP/Pool, Plan/Package, and Service pages?

[jbrouhard98]

I am getting this same error when creating a service for a client. I have my IP Pool populated with public IPs that are available to me.

Do you need screenshots of the above ?

[lsthompson]

Yes please, and URLs of each step, what you clicked, exact error text, etc.

[jbrouhard98]

url: admin/clientsservices.php?userid=2&id=121

Clicked on "create" as we do not automate creation right now (we're testing the solution out on our WHMCS to see if we want to use it)

Exact Error:
Module Create Failed - [Service ID: 121](https://my.tech-3.com/admin/clientsservices.php?id=121) - Error: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') where i.ipaddress not in(select ipaddress from mod_pvewhmcs_vms) limit 1' at line 1 (SQL: select ipaddress,mask,gateway from mod_pvewhmcs_ip_addresses i INNER JOIN mod_pvewhmcs_ip_pools p on (i.pool_id=p.id and p.id=) where i.ipaddress not in(select ipaddress from mod_pvewhmcs_vms) limit 1)

Let me know if you need more info!

[jbrouhard98]

Seems to happen when i do an order as well. I don't know if it's got to do with my IP pool having the ID of 2, or if there's something missing from the syntax. What i noticed is

(i.pool_id=p.id and p.id=)

That seems to have a missing element.. shouldn't p.id= be assigned something ?

[jbrouhard98]

Looking through the code, it seems this variable is not being generated: $params['configoption2'] [( Proxmox-VE-for-WHMCS/modules/servers/pvewhmcs
/pvewhmcs.php](https://github.com/The-Network-Crew/Proxmox-VE-for-WHMCS/blob/master/modules/servers/pvewhmcs/pvewhmcs.php#L73)

Where is that being generated from ? or is it supposed to be auto-populated by something in the background ?

[lsthompson]

@jbrouhard98 have you explicitly saved the page showing "Plan" and "IP Pool" in your 2nd screenshot?

As there appears to be no un-selected option if there is only 1 option, so I'm thinking perhaps it's selecting it by default.

As we are using $param['configoption1'] a few lines above to get the Plan, hence that method does likely seem OK.

If that is the case, then we need to look at adding more checks/warnings for a case of insufficient config.

Where is that being generated from?

Your 2nd screenshot, as mentioned above.

[jbrouhard98]

There is only one IP Pool in my configuration. And yes, I saved the plan and IP Pool in both cases.

[lsthompson]

Thanks for confirming. Just to check though, which URL/s are you talking about when you say the below?

And yes, I saved the plan and IP Pool in both cases.

[jbrouhard98]

admin/addonmodules.php?module=pvewhmcs&tab=vmplans&action=editplan&id=1&vmtype=kvm

and

/admin/addonmodules.php?module=pvewhmcs&tab=ippools&action=list_ips&id=2

[lsthompson]

Okay, that's what I suspected.

Please save here, substituting for your VM/CT Service ID (for usage as a Client Service) in WHMCS Admin.

configproducts.php?action=edit&id=...#tab=3

After that, is the problem the same?

[jbrouhard98]

admin/configproducts.php?action=edit&id=1#tab=3

ON this page the IP Pool was auto populated to "Main Public" which is what it's named in my setup, and the problem persists.

[jbrouhard98]

Correction. I clicked "save" on the aformentioned page, and then went to re-create it. It created the service.

[lsthompson]

OK, so that fixed it for you, right?

[jbrouhard98]

Correct.

[lsthompson]

Thanks. If you update from the repo (not releases), then create a new Service type in WHMCS and do NOT save that page (ie. cause same case), then try to Create - does it stop you and error about the missing config?

[jbrouhard98]

I will attempt to do this when i'm not exhausted and risk blowing up my configuration lol. Might be a day or two ?

[lsthompson]

Sure thing, no worries.

Note the new SQL column in the CHANGELOG.md file too (VLAN ID).

[jbrouhard98]

Updating and testing. Will report back shortly

[jbrouhard98]

ParseError: syntax error, unexpected token ";" in /var/www/html/modules/servers/pvewhmcs/pvewhmcs.php:801
Stack trace:
#0 /var/www/html/admin/clientsservices.php(0): WHMCS\Module\AbstractModule->load()
#1 {main}

D'oh

[jbrouhard98]

Also for refrence, copy/pasting your SQL code stated in sql.md doesn't exactly work in MariaDB:

MariaDB [whmcs]> ALTER TABLE mod_pvewhmcs (

-> ADD COLUMN `debug_mode` tinyint(1) unsigned DEFAULT '0',
-> );

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '(
ADD COLUMN debug_mode tinyint(1) unsigned DEFAULT '0',
)' at line 1

MariaDB [whmcs]> ALTER TABLE mod_pvewhmcs_plans (
-> ADD COLUMN vlanid varchar(10) DEFAULT NULL,
-> );
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '(
ADD COLUMN vlanid varchar(10) DEFAULT NULL,
)' at line 1

MariaDB [whmcs]> ALTER TABLE mod_pvewhmcs ( ADD COLUMN debug_mode tinyint(1) unsigned DEFAULT '0' );
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '( ADD COLUMN debug_mode tinyint(1) unsigned DEFAULT '0' )' at line 1

MariaDB [whmcs]> ALTER TABLE mod_pvewhmcs ( ADD COLUMN debug_mode tinyint(1) unsigned DEFAULT '0', );
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '( ADD COLUMN debug_mode tinyint(1) unsigned DEFAULT '0', )' at line 1

MariaDB [whmcs]> ALTER TABLE mod_pvewhmcs ADD COLUMN debug_mode tinyint(1) unsigned DEFAULT '0';
Query OK, 0 rows affected (0.136 sec)
Records: 0 Duplicates: 0 Warnings: 0

MariaDB [whmcs]> alter table mod_pvewhmcs_plans add column vlanid varchar(10) DEFAULT NULL;
Query OK, 0 rows affected (0.172 sec)
Records: 0 Duplicates: 0 Warnings: 0
`

[lsthompson]

Please update again and try again?

ParseError: syntax error, unexpected token ";" in /var/www/html/modules/servers/pvewhmcs/pvewhmcs.php:801

That's now fixed.

Also for refrence, copy/pasting your SQL code stated in sql.md doesn't exactly work in MariaDB:

That should also be fixed.

[jbrouhard98]

Can confirm this all works now. Although start/stop/reboot are non-functional on my set up (Clustered and it's confused as to where the VM is..)

[lsthompson]

I wonder if that's what they meant in #34

What's the error for that? Note that you have the Debug Mode option in the Addon Module now, which will route to the WHMCS Module Log to check etc.

(if the actual error isn't detailed enough)

[jbrouhard98]

I didn't get an error at all actually. I can just see the error in PVE itself.

TASK ERROR: Configuration file 'nodes/pve1/qemu-server/122.conf' does not exist'

That shows up on PVE2 node. now if i tell WHMCS that it's on PVE1 and save the changes, it works. So essentially, the WHMCS code know it moved if I migrate it IN the cluster itself. It might be beneficial to talk about this more in the other thread, or a new bug

[lsthompson]

Thanks, can you please send that to #16 and we'll dig into that side?

As long as those functions DO work when the PVE Server is set in WHMCS, then it's all good. Is that so?

[jbrouhard98]

i tested all functions that i saw. I assume that "Suspend, Unsuspend, Terminate" are related to internal WHMCS stuff, and not related to the VM itself.

[lsthompson]

Can confirm this all works now

So, with the Tab 3 config not saved, if you try to create the service, the error tells you that your WHMCS Tab 3 isn't saved? (etc, just para phrasing)

[lsthompson]

Suspend, Unsuspend, Terminate

Should actually do this to the VM/CT. I thought you tested un/suspend on a VM but not CT and it worked OK?

[jbrouhard98]

it did not throw any error. It just let me move along.

Actually should do this to the VM/CT.

I tested it on a VM, but it did absolutely nothing that i saw in the Proxmox GUI. Let me confirm this real quick

[jbrouhard98]

Suspend/Terminate did nothing in PVE itself. it doesn't throw any errors in the WHMCS panel itself.

[lsthompson]

Interesting. No log in Proxmox?

What about the Service Creation for a plan that is not saved (Tab 3) - does it now give you a helpful error (not the IP SQL one)?

[jbrouhard98]

Interesting. No log in Proxmox?

Nope. Not a peep out of PVE when i click any of those three.

What about the Service Creation for a plan that is not saved (Tab 3) - does it now give you a helpful error (not the IP SQL one)?

I am not sure if my system is automatically saving the IP Pool or auto-populating it based on a previous option, but i'm getting NO errors whatsoever, and it did not assign an IP either. I presume it should be assigning an IP...

[jbrouhard98]

Correction.. it IS assigning an IP. I'm seeing it in the client area. So I would say this is working as intended.