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
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
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
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.
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.
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
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?
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
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.
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 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.
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
