Replication failed. Error: (web_database) in MySQL server: (localhost) Incorrect integer value

Discussion in 'Installation/Configuration' started by vaio1, Jun 9, 2016.

  1. vaio1

    vaio1 Member

    Hi guys,
    I get this error after the MySQL 5.7 upgrade.
    I have changed these lines in the /etc/mysql/my.cnf as suggested here: https://www.howtoforge.com/communit...entos-6-5-sql-sync-null-from-bugreport.66054/
    with these:
    The problem persists. Any hint?
    Regards
     
    vaio1, Jun 9, 2016
    #1
  2. till

    till Super Moderator Staff Member ISPConfig Developer

    MySQL 5.7 is not fully compatible with prior MySQL versions as far as I know, or at least the software that worked before with MySQL 5.0 - 5.6 stops working, MySQL 5.7 applies much stricter SQL checks so queries that work in older versions fail in 5.7. ISPConfig 3.0.5.x is not made for MySQL 5.7, so you have SQL errors when you use these versions together.
     
    till, Jun 10, 2016
    #2
  3. vaio1

    vaio1 Member

    So the Replication failed error could be not solved?

    The issue is related to a wrong query.
    two fields involved:
    • last_quota_notification
    • database_quota
    last_quota_notification is a "date" field without any default value
    the cron executes the updates into the sys_datalog table on the database server, setting the last_quota_notification with an empty string but the sys_datalog record (data field) shows a NULL value.

    The problem of the database_quota field is the same of the last_quota_notification.

    For instance:
    ERROR - Replication failed. Error: (web_database) in MySQL server: (localhost) Incorrect date value: '' for column 'last_quota_notification' at row 1 # SQL: REPLACE INTO web_database (`database_id`,`sys_userid`,`sys_groupid`,`sys_perm_user`,`sys_perm_group`,`sys_perm_other`,`server_id`,`parent_domain_id`,`type`,`database_name`,`database_name_prefix`,`database_quota`,`last_quota_notification`,`database_user_id`,`database_ro_user_id`,`database_charset`,`remote_access`,`remote_ips`,`backup_copies`,`active`,`backup_interval`) VALUES ('549','1','4','riud','riud','','2','1658','mysql','user_test2','user_','0','','181','0','utf8','y','','1','y','none')

    the zero value in the insert query was written just for testing the default value. I have set the database_quota default value to zero in the database and it works! but the last_quota_notification is a date and you cannot set it with an empty string.

    So the problem is just a wrong query.
     
    Last edited: Jun 14, 2016
    vaio1, Jun 14, 2016
    #3
  4. till

    till Super Moderator Staff Member ISPConfig Developer

    Like I explained above, the query is absolutely fine for MySQL 5.x incl. version 5.6 as these MySQL versions support empty values for date strings. What changed is MySQL 5.7 and this MySQL version is incompatible with ISPConfig 3.0.5. So you can either downgrade MySQL to 5.6 or upgrade ISPConfig to 3.1
     
    till, Jun 14, 2016
    #4
  5. vaio1

    vaio1 Member

    How have I to upgrade the ISPConfig from the 3.0.5.4p8 to the latest version?
     
    vaio1, Jun 14, 2016
    #5
  6. till

    till Super Moderator Staff Member ISPConfig Developer

    ispconfig_update.sh

    But be aware that 3.1 is still in Beta an a update from 3.0.5.4p8 to p9 will not solve your problem.
     
    till, Jun 14, 2016
    #6
  7. vaio1

    vaio1 Member

    so there is not any patch for this issue. MySQL 5.7 has been released and I cannot use the old 5.6 version.
    how can I help you to solve this issue also for other ISPConfig users?
    Thanks
     
    vaio1, Jun 14, 2016
    #7
  8. till

    till Super Moderator Staff Member ISPConfig Developer

    MySQL 5.7 is not fully downwards compatible with older MySQL versions, so MySQL is the problem here and not ISPConfig as they decided to drop compatibility. You can ask MySQL devs if there are any switches in their software to make 5.7 fully compatible with MySQL 5.5 and 5.6. Btw. I won't use MySQL 5.7 on any production server yet due to their fail to make it compatible and there is a good chance that your websites start to fail partially when you upgrade to MySQl 5.7.

    In regard to ISPConfig, the new 3.1 version has support for MySQL 5.7.
     
    till, Jun 14, 2016
    #8

Share This Page