Error when using MailWizz PHP API (v2)

L

Lakjin

Active Member
I am using the PHP client at https://github.com/ems-api/php-client. I am using MailWizz v2.2.7. The following code...

Code: 
    $endpoint = new EmsApi\Endpoint\ListSubscribers();
      
    $response = $endpoint->createUpdate('LIST-UNIQUE-ID', array(
        'EMAIL'    => $email,
    ));

...throws this error...

Code: 
O:13:"EmsApi\Params":2:{s:20:"EmsApi\Params_data";a:2:{s:6:"status";s:5:"error";s:5:"error";s:156:"CDbCommand failed to execute the SQL statement: SQLSTATE[22003]: Numeric value out of range: 167 Out of range value for column 'value_id' at row 1";}s:24:"EmsApi\Params_readOnly";b:0;}

Here is what the application log says:

Code: 
2023/02/15 17:57:48 [error] [system.db.CDbCommand] [ip address] CDbCommand::execute() failed: SQLSTATE[22003]: Numeric value out of range: 167 Out of range value for column 'value_id' at row 1. The SQL statement executed was: INSERT INTO `mw_list_field_value` (`field_id`, `subscriber_id`, `value`, `date_added`, `last_updated`) VALUES (:yp0, :yp1, :yp2, NOW(), NOW()).
2023/02/15 17:57:48 [error] [exception.CDbException] [ip address] CDbException: CDbCommand failed to execute the SQL statement: SQLSTATE[22003]: Numeric value out of range: 167 Out of range value for column 'value_id' at row 1 in /home/xxx/public_html/vendor/yiisoft/yii/framework/db/CDbCommand.php:358
Stack trace:
#0 /home/xxx/public_html/vendor/yiisoft/yii/framework/db/ar/CActiveRecord.php(1083): CDbCommand->execute()
#1 /home/xxx/public_html/vendor/yiisoft/yii/framework/db/ar/CActiveRecord.php(811): CActiveRecord->insert(NULL)
#2 /home/xxx/public_html/apps/api/controllers/List_subscribersController.php(976): CActiveRecord->save()
#3 [internal function]: List_subscribersController->actionUpdate('LISTUID', 'SUBUID')
#4 /home/xxx/public_html/vendor/yiisoft/yii/framework/web/actions/CAction.php(115): ReflectionMethod->invokeArgs(Object(List_subscribersController), Array)
#5 /home/xxx/public_html/vendor/yiisoft/yii/framework/web/actions/CInlineAction.php(47): CAction->runWithParamsInternal(Object(List_subscribersController), Object(ReflectionMethod), Array)
#6 /home/xxx/public_html/vendor/yiisoft/yii/framework/web/CController.php(308): CInlineAction->runWithParams(Array)
#7 /home/xxx/public_html/vendor/yiisoft/yii/framework/web/filters/CFilterChain.php(134): CController->runAction(Object(CInlineAction))
#8 /home/xxx/public_html/vendor/yiisoft/yii/framework/web/filters/CFilter.php(40): CFilterChain->run()
#9 /home/xxx/public_html/vendor/yiisoft/yii/framework/web/CController.php(1148): CFilter->filter(Object(CFilterChain))
#10 /home/xxx/public_html/vendor/yiisoft/yii/framework/web/filters/CInlineFilter.php(58): CController->filterAccessControl(Object(CFilterChain))
#11 /home/xxx/public_html/vendor/yiisoft/yii/framework/web/filters/CFilterChain.php(131): CInlineFilter->filter(Object(CFilterChain))
#12 /home/xxx/public_html/vendor/yiisoft/yii/framework/web/filters/CFilter.php(40): CFilterChain->run()
#13 /home/xxx/public_html/vendor/yiisoft/yii/framework/web/filters/CFilterChain.php(131): CFilter->filter(Object(CFilterChain))
#14 /home/xxx/public_html/vendor/yiisoft/yii/framework/web/CController.php(291): CFilterChain->run()
#15 /home/xxx/public_html/vendor/yiisoft/yii/framework/web/CController.php(265): CController->runActionWithFilters(Object(CInlineAction), Array)
#16 /home/xxx/public_html/vendor/yiisoft/yii/framework/web/CWebApplication.php(282): CController->run('update')
#17 /home/xxx/public_html/vendor/yiisoft/yii/framework/web/CWebApplication.php(141): CWebApplication->runController('list_subscriber...')
#18 /home/xxx/public_html/vendor/yiisoft/yii/framework/base/CApplication.php(185): CWebApplication->processRequest()
#19 /home/xxx/public_html/apps/init.php(218): CApplication->run()
#20 /home/xxx/public_html/api/index.php(18): require_once('/home/xxx/pub...')
#21 {main}
REQUEST_URI=/api/lists/LISTUID/subscribers/SUBUID
---
 
Last edited:
If you upgraded from 1.x to 2.x, your list_field_value table primary key only allows values up to 2 147 483 647, which should suffice, but here we are.
Version 2 allows values up to 2^63-1(that is 9 223 372 036 854 775 807) BUT, we do this only for new installs, older installs don't get this upgrade, so in case you hit the limit, you need to make the column upgrade by yourself, by running following sql query against mailwizz's database:
Code: 
ALTER TABLE `mw_list_field_value` CHANGE `value_id` `value_id` BIGINT NOT NULL AUTO_INCREMENT;
This query might literally take days to complete.
Make sure you do a backup before.
 
Thank you.

Are there any other SQL modifications like these that should be made for people upgrading from v1 to v2?
 
twisted1919 said:
If you upgraded from 1.x to 2.x, your list_field_value table primary key only allows values up to 2 147 483 647, which should suffice, but here we are.
Version 2 allows values up to 2^63-1(that is 9 223 372 036 854 775 807) BUT, we do this only for new installs, older installs don't get this upgrade, so in case you hit the limit, you need to make the column upgrade by yourself, by running following sql query against mailwizz's database:
Code: 
ALTER TABLE `mw_list_field_value` CHANGE `value_id` `value_id` BIGINT NOT NULL AUTO_INCREMENT;
This query might literally take days to complete.
Make sure you do a backup before.
Click to expand...
Should the length of the column be left at 11 after changing it to bigint?
 
Lakjin said:
Should the length of the column be left at 11 after changing it to bigint?
Click to expand...
Okay, I think leaving the length unchanged at 11 is the thing to do.

I also changed value_id to BIGINT and the API works successfully now. Thanks!

But, I still would like to know if there are other sql changes we should make if we upgraded from v1 to v2. Thanks!
 
Lakjin said:
I still would like to know if there are other sql changes we should make if we upgraded from v1 to v2
Click to expand...
Nope, this one was the only one and the reason was that doing this in an upgrade could kill the upgrade because it could take a lot of time and some servers are not ready for such change during the upgrade.
 
I am also trying to add the code for the column upgrade. However, when I try to log into mysql, before being prompted for my password, I am seeing the error:
mysql: unknown variable 'max_connections=2000'

How do I get around this? Is there something I need to adjust in the my.cnf file?
 
You must log in or register to reply here.
Back
Top