mysql error stops job queue

Discussion in 'ISPConfig 3 Priority Support' started by muekno, Jan 27, 2015.

  1. muekno

    muekno Member HowtoForge Supporter

    Found the reason why job queue stops
    Replication failed. Error: (client) in MySQL server: (localhost) Incorrect integer value: '' for column 'created_at' at row 1 # SQL: REPLACE INTO client (`client_id`,`sys_userid`,`sys_groupid`,`sys_perm_user`,`sys_perm_group`,`sys_perm_other`,`company_name`,`company_id`,`contact_name`,`customer_no`,`vat_id`,`street`,`zip`,`city`,`state`,`country`,`telephone`,`mobile`,`fax`,`email`,`internet`,`icq`,`notes`,`bank_account_owner`,`bank_account_number`,`bank_code`,`bank_name`,`bank_account_iban`,`bank_account_swift`,`paypal_email`,`default_mailserver`,`limit_maildomain`,`limit_mailbox`,`limit_mailalias`,`limit_mailaliasdomain`,`limit_mailforward`,`limit_mailcatchall`,`limit_mailrouting`,`limit_mailfilter`,`limit_fetchmail`,`limit_mailquota`,`limit_spamfilter_wblist`,`limit_spamfilter_user`,`limit_spamfilte.. on new secondary DNS slave

    mysql-comunity-server/client 5.6.12-7.2.2 latest on SuSe for 13.1 32 bit, can not run 64-bit (restriction of ESXi 4.x on my hardware) set to sql_mode=NO_ENGINE_SUBSTITUTION as of tutorial

    what to do to fix

    thanks for help
     
  2. till

    till Super Moderator Staff Member ISPConfig Developer

    Mysql seems to run still in wrong mode. Please try to find the config file where sql_mode is defines wrong:

    grep -r sql_mode /etc
     
  3. muekno

    muekno Member HowtoForge Supporter

    ns:/usr/local/ispconfig/server/temp # grep -r sql_mode /etc
    /etc/my.cnf:sql_mode=NO_ENGINE_SUBSTITUTION
     
  4. till

    till Super Moderator Staff Member ISPConfig Developer

    The error that you posted above is shown in ispconfig monitor module?
     
  5. muekno

    muekno Member HowtoForge Supporter

    it is copied from the output of
    /usr/local/ispconfig/server/server.sh at the slave console, but it is seen also in the monitor module in the system log when debug turned on
    grep ist also from new slave, master is running more than a year, ispconfig updated 2 times

    see other thread what I did't before
     
  6. till

    till Super Moderator Staff Member ISPConfig Developer

    Login to ispconfig, go to the monitor, select system log and there status error, the replication error should be shown there. Click on the delete button that is shown in that row to confirm that you fixed the errir so that ispconfig can proceed with processing the queue.
     
  7. muekno

    muekno Member HowtoForge Supporter

    i did this before 2 or 3 time error persists
    tried again now, same problem
     
  8. till

    till Super Moderator Staff Member ISPConfig Developer

    Mysql on the slave runs still in wrong mode, it wont throw that error in correct mode as it would convert '' to 0. Please check the mysql config on the salve again and e.g. restart the slave vm completely.
     
  9. muekno

    muekno Member HowtoForge Supporter

    Rebooted, delete error still same problem
    to remember master is running opensuse 12.3 latest patches, mysql 5.5.33-1.13.1
    here is my my.cnf and a mysqld status


    ---
    [client]
    # password = your_password
    # port = 3306
    # socket = /var/run/mysql/mysql.sock
    [mysqld]
    innodb_file_format=Barracuda
    innodb_file_per_table=ON
    # datadir = /var/lib/mysql
    server-id = 1
    # These are commonly set, remove the # and set as required.
    # port = 3306
    # socket = /var/run/mysql/mysql.sock
    # skip-networking
    # join_buffer_size = 128M
    # sort_buffer_size = 2M
    # read_rnd_buffer_size = 2M
    sql_mode=NO_ENGINE_SUBSTITUTION
    [mysqld_multi]
    mysqld = /usr/bin/mysqld_safe
    mysqladmin = /usr/bin/mysqladmin
    log = /var/log/mysqld_multi.log

    !includedir /etc/my.cnf.d
    ----
    ns:~ # service mysql status
    mysql.service - LSB: Start the MySQL database server
    Loaded: loaded (/etc/init.d/mysql)
    Active: active (running) since Tue 2015-01-27 18:07:55 CET; 9min ago
    Process: 915 ExecStart=/etc/init.d/mysql start (code=exited, status=0/SUCCESS)
    CGroup: /system.slice/mysql.service
    ├─1039 /bin/sh /usr/bin/mysqld_safe --mysqld=mysqld --user=mysql --pid-file=/var/run/mysql/mysqld.pid --socket=/var/run/mysql/mysql.sock --datadir=/var/lib/mysql
    └─1306 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/lib/mysql/ns.err --pid-file=/var/run/mysql/mysqld.pid --socket=/var/run/mysql/mysql.sock

    Jan 27 18:07:41 ns systemd[1]: Starting LSB: Start the MySQL database server...
    Jan 27 18:07:55 ns systemd[1]: Started LSB: Start the MySQL database server.

    Rainer
     
  10. till

    till Super Moderator Staff Member ISPConfig Developer

    The mysql on master does not matter for the replication, only the mysql config on the slave is important.

    The my.cnf is from the slave server?

    are there any files in the directory:

    /etc/my.cnf.d

    on the slave?
     
  11. muekno

    muekno Member HowtoForge Supporter

    yes it is from the slave, as master and other slave are working fine

    there is on file in the my.conf.d with the following content

    [server]
    plugin-load=blackhole=ha_blackhole.so
    plugin-load=federated=ha_federated.so
    plugin-load=archive=ha_archive.so
     
  12. till

    till Super Moderator Staff Member ISPConfig Developer

    Please enable logging in mysql on the slave, then get the full mysql query that fails from the log and post it, so that I can check the syntax and test it here on my server.
     
  13. muekno

    muekno Member HowtoForge Supporter

    here it is from the log file on the slave
    150128 13:16:09 25 Connect [email protected] on dbispconfig
    25 Query SET NAMES utf8
    25 Query SET character_set_results = 'utf8', character_set_client = 'utf8', character_set_connection = 'utf8', character_set_database = 'utf8', character_set_server = 'utf8'
    25 Query REPLACE INTO client (`client_id`,`sys_userid`,`sys_groupid`,`sys_perm_user`,`sys_perm_group`,`sys_perm_other`,`company_name`,`company_id`,`contact_name`,`customer_no`,`vat_id`,`street`,`zip`,`city`,`state`,`country`,`telephone`,`mobile`,`fax`,`email`,`internet`,`icq`,`notes`,`bank_account_owner`,`bank_account_number`,`bank_code`,`bank_name`,`bank_account_iban`,`bank_account_swift`,`paypal_email`,`default_mailserver`,`limit_maildomain`,`limit_mailbox`,`limit_mailalias`,`limit_mailaliasdomain`,`limit_mailforward`,`limit_mailcatchall`,`limit_mailrouting`,`limit_mailfilter`,`limit_fetchmail`,`limit_mailquota`,`limit_spamfilter_wblist`,`limit_spamfilter_user`,`limit_spamfilter_policy`,`default_webserver`,`limit_web_ip`,`limit_web_domain`,`limit_web_quota`,`web_php_options`,`limit_cgi`,`limit_ssi`,`limit_perl`,`limit_ruby`,`limit_python`,`force_suexec`,`limit_hterror`,`limit_wildcard`,`limit_ssl`,`limit_web_subdomain`,`limit_web_aliasdomain`,`limit_ftp_user`,`limit_shell_user`,`ssh_chroot`,`limit_webdav_user`,`limit_aps`,`default_dnsserver`,`limit_dns_zone`,`default_slave_dnsserver`,`limit_dns_slave_zone`,`limit_dns_record`,`default_dbserver`,`limit_database`,`limit_cron`,`limit_cron_type`,`limit_cron_frequency`,`limit_traffic_quota`,`limit_client`,`limit_mailmailinglist`,`limit_openvz_vm`,`limit_openvz_vm_template_id`,`parent_client_id`,`username`,`password`,`language`,`usertheme`,`template_master`,`template_additional`,`created_at`,`id_rsa`,`ssh_rsa`) VALUES ('1','1','1','riud','riud','','','','Helmut Fröhlich','','','','','','','DE','','','','','http://www.abcde.de','','','','','','','','','','1','-1','-1','-1','-1','-1','-1','0','-1','-1','-1','0','0','0','1','','-1','-1','no,fast-cgi,cgi,mod,suphp,php-fpm','n','n','n','n','n','y','n','n','n','-1','-1','-1','0','no,jailkit,ssh-chroot','0','-1','1','-1','1','-1','-1','1','-1','0','url','5','-1','1','-1','0','0','0','elmers','$1$PRfTW7XB$V0a28N4fFHsKCHB/MFFUV1','de','default','0','','','','')
     
  14. till

    till Super Moderator Staff Member ISPConfig Developer

    Please check the file /usr/my.cnf if it contains a wrong sql mode.
     
  15. muekno

    muekno Member HowtoForge Supporter

    Changed mode in usr/my.cnf as in /etc/my.cnf (working at least 15 years with mysql on Linux I never hat to change usr/my.cnf, always only etc/my.cnf)
    Seams to work now, job queue is now empty.
    Thank you, Danke, merci for your help
    Thumbs up
     
  16. till

    till Super Moderator Staff Member ISPConfig Developer

    Yes, its a bad choice by opensuse to put a my.cnf file in such a uncommon location in my opinion especially when it overides the common my.cnf file in /etc. I found it ony my opensuse test server by doing a grep on all files of the system.
     
  17. muekno

    muekno Member HowtoForge Supporter

    Yes its really bad. Seems to be a bug, I am working with opensuse since the 90th, never hat my.cnf on other place than in /etc, so I even did'nt think it could be elsewhere. On the other side /etc has a my.cnf an the changes I made there i.e. the entries to get the debug log. So it seams they read /etc and then /usr and override settings from /etc. The my.cnf in /usr ist still a fragment of the my.cnf in /etc.

    Thanks you again
    Rainer
     
  18. muekno

    muekno Member HowtoForge Supporter

    just FYI
    In the meantime I installed at least 3 new server with opensuse 13.1 an mysql the same way als always, I never found a second my.cnf beside /etc/my.cnf in /usr or elsewhere. So I think that must have been a bug in a single release we fall in.

    Rainer
     
    till likes this.
  19. till

    till Super Moderator Staff Member ISPConfig Developer

    Thanks for the update on the issue!
     
  20. muekno

    muekno Member HowtoForge Supporter

    Did install two new servers hat /usr/my.cnf again and found this in the mysql log
    Feb 26 14:25:50 w3 mysql[1150]: New default config file was created as /usr/my.cnf and
    Feb 26 14:25:50 w3 mysql[1150]: will be used by default by the server when you start it.
    Feb 26 14:25:50 w3 mysql[1150]: You may edit this file to change server settings
    Feb 26 14:25:50 w3 mysql[1150]: WARNING: Default config file /etc/my.cnf exists on the system
    Feb 26 14:25:50 w3 mysql[1150]: This file will be read by default by the MySQL server
    Feb 26 14:25:50 w3 mysql[1150]: If you do not want to use this, either remove it, or use the
    Feb 26 14:25:50 w3 mysql[1150]: --defaults-file argument to mysqld_safe when starting the server
    Feb 26 14:25:53 w3 mysql[1150]: Starting service MySQL ..done
    Feb 26 14:25:53 w3 systemd[1]: Started LSB: Start the MySQL database server.

    seems that will be created now on every restart of mysql. I think I move /etc/my.cnf to /usr
     

Share This Page