Error activating module in version WHMCs 7.4.1

[grupouweb]

Hello man

I loved your initiative in developing and distributing your module for free.

I need to know if your module works in version WHMCS 7.4.1 because when trying to activate its module, it returned the error below.

exception 'PDOException' with message 'SQLSTATE[42S21]: Column already exists: 1060 Duplicate column name 'updated_at'' in /home/siscob/public_html/vendor/illuminate/database/Connection.php:441
Stack trace:
#0 /home/siscob/public_html/vendor/illuminate/database/Connection.php(441): PDOStatement->execute(Array)
#1 /home/siscob/public_html/vendor/illuminate/database/Connection.php(706): Illuminate\Database\Connection->Illuminate\Database{closure}(Object(Illuminate\Database\MySqlConnection), 'alter table tb...', Array) #2 /home/siscob/public_html/vendor/illuminate/database/Connection.php(669): Illuminate\Database\Connection->runQueryCallback('alter table tb...', Array, Object(Closure))
#3 /home/siscob/public_html/vendor/illuminate/database/Connection.php(442): Illuminate\Database\Connection->run('alter table tb...', Array, Object(Closure)) #4 /home/siscob/public_html/vendor/illuminate/database/Schema/Blueprint.php(83): Illuminate\Database\Connection->statement('alter table tb...')
#5 /home/siscob/public_html/vendor/illuminate/database/Schema/Builder.php(229): Illuminate\Database\Schema\Blueprint->build(Object(Illuminate\Database\MySqlConnection), Object(Illuminate\Database\Schema\Grammars\MySqlGrammar))
#6 /home/siscob/public_html/vendor/illuminate/database/Schema/Builder.php(130): Illuminate\Database\Schema\Builder->build(Object(Illuminate\Database\Schema\Blueprint))
#7 /home/siscob/public_html/modules/addons/proxmox/proxmox.php(340): Illuminate\Database\Schema\Builder->table('tblinvoiceitems', Object(Closure))
#8 /home/siscob/public_html/admin/configaddonmods.php(0): proxmox_activate()
#9 {main}
Next exception 'Illuminate\Database\QueryException' with message 'SQLSTATE[42S21]: Column already exists: 1060 Duplicate column name 'updated_at' (SQL: alter table tblinvoiceitems add updated_at timestamp not null, add status text not null)' in /home/siscob/public_html/vendor/illuminate/database/Connection.php:713
Stack trace:
#0 /home/siscob/public_html/vendor/illuminate/database/Connection.php(669): Illuminate\Database\Connection->runQueryCallback('alter table tb...', Array, Object(Closure)) #1 /home/siscob/public_html/vendor/illuminate/database/Connection.php(442): Illuminate\Database\Connection->run('alter table tb...', Array, Object(Closure))
#2 /home/siscob/public_html/vendor/illuminate/database/Schema/Blueprint.php(83): Illuminate\Database\Connection->statement('alter table `tb...')
#3 /home/siscob/public_html/vendor/illuminate/database/Schema/Builder.php(229): Illuminate\Database\Schema\Blueprint->build(Object(Illuminate\Database\MySqlConnection), Object(Illuminate\Database\Schema\Grammars\MySqlGrammar))
#4 /home/siscob/public_html/vendor/illuminate/database/Schema/Builder.php(130): Illuminate\Database\Schema\Builder->build(Object(Illuminate\Database\Schema\Blueprint))
#5 /home/siscob/public_html/modules/addons/proxmox/proxmox.php(340): Illuminate\Database\Schema\Builder->table('tblinvoiceitems', Object(Closure))
#6 /home/siscob/public_html/admin/configaddonmods.php(0): proxmox_activate()
#7 {main}

Let me know your thoughts!

Thanks and Regards,
Renato

[baonq-me]

Hello Renato,

Thanks for your attention.

This module will create additional columns for management purpose. In this case, a column named "updated_at" have been created in table "tblinvoiceitems". Sometimes (for example you refresh the page when activating) this column already existed when you activate the module. That will lead to this error message from MySQL that prevent WHMCS from activating the module.

What you can do now is removing this column
ALTER TABLE tblinvoiceitems DROP COLUMN "updated_at"

Best,
Quoc-Bao

[PitbullOL]

That works Fine because another Problem is here:

PDOException: SQLSTATE[42000]: Syntax error or access violation: 1235 This version of MariaDB doesn't yet support 'multiple triggers with the same action time and event for one table' in /var/www/vhosts/xxx.xx/whmcs.xxx.xx/modules/addons/proxmox/proxmox.php:350
Stack trace:
#0 /var/www/vhosts/xxx.xx/whmcs.xxx.xx/modules/addons/proxmox/proxmox.php(350): PDO->exec('\nCREATE TRIGGER...')
#1 /var/www/vhosts/xxx.xx/whmcs.xxx.xx/admin/configaddonmods.php(0): proxmox_activate()
#2 {main}

[grupouweb]

Hello Quoc-Bao,

Thank you for your feedback!

After your instructions, activation has now stopped in a new error:

exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1235 This version of MySQL doesn't yet support 'multiple triggers with the same action time and event for one table'' in /home/siscob/public_html/modules/addons/proxmox/proxmox.php:350
Stack trace:
#0 /home/siscob/public_html/modules/addons/proxmox/proxmox.php(350): PDO->exec('\nCREATE TRIGGER...')
#1 /home/siscob/public_html/admin/configaddonmods.php(0): proxmox_activate()
#2 {main}

Thanks and Regards,
Renato

[grupouweb]

Hello Quoc-Bao,

Can you kindly help me with the problem posted above?

Thanks and Regards,
Renato

[baonq-me]

Hello Renato,

Please apologize for late reply.

You have to upgrade your mysql-server to a higher version to support "multiple triggers". Version 5.6.10 or above is fine.

Best,
Quoc-Bao

[grupouweb]

Hi Quoc-Bao,

Sorry ... I'm going to ask my staff to update the Mysql version and come back to you okay?

Thanks and Regards,
Renato

[grupouweb]

Hi Quoc-Bao,

Thanks for your feedback!

I upgraded mysql to version 5.6.39 and tried to activate its module, but returned the error below:

exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1235 This version of MySQL doesn't yet support 'multiple triggers with the same action time and event for one table'' in /home/siscob/public_html/modules/addons/proxmox/proxmox.php:350
Stack trace:
#0 /home/siscob/public_html/modules/addons/proxmox/proxmox.php(350): PDO->exec('\nCREATE TRIGGER...')
#1 /home/siscob/public_html/admin/configaddonmods.php(0): proxmox_activate()
#2 {main}

Thanks and Regards,
Renato

[baonq-me]

Hello Renato,

I'm so sorry for not reading the error message carefully. Like my comment above, while creating additional columns, the plugin will create some triggers (for example 'updateInvoiceAndOrder' helps activate an order automatically whenever it is paid).

When you fail to activate the plugin, these triggers still remain in the database. What you must do is removing these triggers by SQL query (remember removing additional columns as well):
DROP TRIGGER IF EXISTS trigger_name;

About the mysql-server, while performing a query, I pass LIMIT as an argument in store procedures. But this style is only supported by mysql-server 5.5.6 and above. For that reason, an upgrade to mysql-server 5.5.6 or higher version is mandatory.

Best,
Quoc-Bao

[baonq-me]

Please note that I didn't finish the plugin as well as documents. But it's fine if you want to take a look. Thanks for your attention.

[grupouweb]

Hi Quoc-Bao,

Thanks for your feedback!
I want to apologize!
I am not an expert in programming and I do not know how to debug the errors.!
For this reason, all that error, I report to you.

I have run the commands listed below in mysql to proceed with the installation:

ALTER TABLE tblinvoiceitems DROP COLUMN updated_at and ALTER TABLE tblinvoiceitems DROP COLUMN status until you get the error:

exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1235 This version of MySQL doesn't yet support 'multiple triggers with the same action time and event for one table'' in /home/siscob/public_html/modules/addons/proxmox/proxmox.php:350
Stack trace:
#0 /home/siscob/public_html/modules/addons/proxmox/proxmox.php(350): PDO->exec('\nCREATE TRIGGER...')
#1 /home/siscob/public_html/admin/configaddonmods.php(0): proxmox_activate()
#2 {main}

From this error forward I can not debug anymore what to do ...

In time: Do you intend to continue developing this module for Proxmox?

Thanks and Regards,
Renato

[baonq-me]

Run these additional sql queries:
DROP TRIGGER IF EXISTS activeHostingService;
DROP TRIGGER IF EXISTS updateInvoiceAndOrder;
DROP PROCEDURE IF EXISTS getCustomField;
DROP PROCEDURE IF EXISTS getCustomFields;
DROP PROCEDURE IF EXISTS getItemsByStatus;
DROP PROCEDURE IF EXISTS updateCustomField;
DROP PROCEDURE IF EXISTS updateItemStatus;

I'm a little bit busy now but I will come back as soon as possible.

[grupouweb]

Hi Quoc-Bao,

Thanks for your feedback!

After inserting the command lines in mysql and trying to activate its module, it returned the error:

exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1227 Access denied; you need (at least one of) the SUPER privilege(s) for this operation' in /home/siscob/public_html/modules/addons/proxmox/proxmox.php:350
Stack trace:
#0 /home/siscob/public_html/modules/addons/proxmox/proxmox.php(350): PDO->exec('\nCREATE DEFINER...')
#1 /home/siscob/public_html/admin/configaddonmods.php(0): proxmox_activate()
#2 {main}

Thanks and Regards,
Renato

[grupouweb]

Hello Quoc-Bao,

Can you kindly help me with the problem posted above?

Thanks and Regards,
Renato

[baonq-me]

The error message said that your mysql user doesn't have enough permissions. Please check this permission on your current user

If your system is built for testing purpose, you should consider using root user

[grupouweb]

Hello Quoc-Bao,
My WHMCS system is in its own hosting using the Control Web Panel.

My WHMCS is working normally, I'm just having trouble installing your module.

Thanks and Regards,
Renato

[mhos]

This is what I did to get this to activate on WHMCS 7.5.1 on WHM/cPanel latest.
You have to grant super privlages to the DB user globally. Yes not safe but can be changed back once you have activated the plugin:

1. GRANT SUPER ON *.* TO WHMCSDBUserName@'localhost' IDENTIFIED BY 'dbuserpassword';
   Note: Change the above username and password to what you have set for your WHMCS install
2. Upload the module to the correct location.
3. Navigate to WHMCS > Setup > Addon Modules > Click activate on the Module and wait for it to error.
4. Open PHPMyAdmin and navigate to the WHMCS DB and run the following commands using the SQL tab:
   DROP TRIGGER IF EXISTS activeHostingService;
DROP TRIGGER IF EXISTS updateInvoiceAndOrder;
DROP PROCEDURE IF EXISTS getCustomField;
DROP PROCEDURE IF EXISTS getCustomFields;
DROP PROCEDURE IF EXISTS getItemsByStatus;
DROP PROCEDURE IF EXISTS updateCustomField;
DROP PROCEDURE IF EXISTS updateItemStatus;
ALTER TABLE tblinvoiceitems DROP COLUMN updated_at
5. Go back to WHMCS > Setup > Addon Modules > Click activate on the Module and wait for it to error again.
6. Go back to PHPMyAdmin and navigate to the WHMCS DB and run the following commands using the SQL tab:
   ALTER TABLE tblinvoiceitems DROP COLUMN STATUS
7. Go back to WHMCS > Setup > Addon Modules > Click activate on the Module and this time it should activate and allow you to set it up.

Hope this helps.