mysql error stops job queue

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

 

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

 

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

 

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

 

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

 

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

 

here it is from the log file on the slave
150128 13:16:09 25 Connect ispconfig@localhost 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','','','','')

 

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

 

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

 

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

 

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