0001295: fix_assurer_flag() function in includes/lib/account.php causes mysql 5.5 server warnings - CAcert Bug Tracker

ID	Project	Category	View Status	Date Submitted	Last Update

0001295	Main CAcert Website	source code	public	2014-08-04 14:42	2015-12-12 17:20

Reporter	wytze	Assigned To	BenBE
Priority	normal	Severity	major	Reproducibility	always
Status	fix available	Resolution	open
Platform	Default	OS	any	OS Version	any
Product Version	2014 Q2
Target Version	2014 Q4

Summary	0001295: fix_assurer_flag() function in includes/lib/account.php causes mysql 5.5 server warnings
Description	Each invocation of the fix_assurer_flag() function in includes/lib/account.php causes the mysql 5.5 server to log a warning like this: Jun 23 20:09:52 webdb mysqld: 140623 20:09:52 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statements writing to a table with an auto-increment column after selecting from another table are unsafe because the order in which rows are retrieved determines what (if any) rows will be written. This order cannot be predicted and may differ on master and the slave. Statement: UPDATE `users` AS `u` SET `assurer` = 1 Jun 23 20:09:52 webdb mysqld: WHERE `u`.`id` = 'XXXXXX' Jun 23 20:09:52 webdb mysqld: AND EXISTS( Jun 23 20:09:52 webdb mysqld: SELECT 1 FROM `cats_passed` AS `cp`, `cats_variant` AS `cv` Jun 23 20:09:52 webdb mysqld: WHERE `cp`.`variant_id` = `cv`.`id` Jun 23 20:09:52 webdb mysqld: AND `cv`.`type_id` = 1 Jun 23 20:09:52 webdb mysqld: AND `cp`.`user_id` = `u`.`id` Jun 23 20:09:52 webdb mysqld: ) Jun 23 20:09:52 webdb mysqld: AND ( Jun 23 20:09:52 webdb mysqld: SELECT SUM(`points`) FROM `notary` AS `n` Jun 23 20:09:52 webdb mysqld: WHERE `n`.`to` = `u`.`id` Jun 23 20:09:52 webdb mysqld: AND (`n`.`expire` > now() Jun 23 20:09:52 webdb mysqld: OR `n`.`expire` IS NULL) Jun 23 20:09:52 webdb mysqld: AND `n`.`deleted` = 0 Jun 23 20:09:52 webdb mysqld: ) >= 100
Steps To Reproduce	fix_assurer_flag() is called from several places in the application, pick any.
Tags	No tags attached.

Reviewed by
Test Instructions

INOPIAE 2014-08-05 20:54 updater ~0004913 Last edited: 2014-08-05 20:55	Can you provide some information about the mysql server setup or point to the documentation is the wiki. The main point of question is the mysql server is replicated or not and if it is replicated what the what the settings of the binlog_format is?

wytze 2014-08-06 09:16 developer ~0004918	The mysql server is not replicated. Binlogging is enabled with these statements in /etc/mysql/my.cnf: log_bin = /var/log/mysql/mysql-bin.log expire_logs_days = 0 max_binlog_size = 100M It may be helpful to know that the cacert[12] test servers are using exactly the same setup, except for the setting of expire_log_days (10 on the test servers). Identical mysql server warnings can be observed on these test servers.

wytze 2014-08-06 09:25 developer ~0004919	Perhaps it is sufficient to add: binlog_format = mixed to the configuration, but a review of such a change by a knowledgeable mysql person would be appreciated.

INOPIAE 2014-11-22 14:31 updater ~0005116	I pushed a fix to https://github.com/INOPIAE/CAcert/commit/660c548b541f45a48d1268f74f868d4d19c27f5d

Date Modified	Username	Field	Change
2014-08-04 14:42	wytze	New Issue
2014-08-05 20:54	INOPIAE	Note Added: 0004913
2014-08-05 20:54	INOPIAE	Assigned To	=> wytze
2014-08-05 20:54	INOPIAE	Status	new => needs feedback
2014-08-05 20:55	INOPIAE	Note Edited: 0004913
2014-08-05 21:13	BenBE	Description Updated
2014-08-06 09:16	wytze	Note Added: 0004918
2014-08-06 09:16	wytze	Status	needs feedback => needs work
2014-08-06 09:17	wytze	Assigned To	wytze => INOPIAE
2014-08-06 09:25	wytze	Note Added: 0004919
2014-11-04 07:03	INOPIAE	Target Version	2014 Q3 => 2014 Q4
2014-11-22 14:31	INOPIAE	Note Added: 0005116
2014-11-22 14:31	INOPIAE	Assigned To	INOPIAE => BenBE
2014-11-22 14:31	INOPIAE	Status	needs work => fix available
2015-12-12 17:20	INOPIAE	Relationship added	related to 0001408