View Issue Details

IDProjectCategoryView StatusLast Update
0001295Main CAcert Websitesource codepublic2015-12-12 17:20
Reporterwytze Assigned ToBenBE  
PrioritynormalSeveritymajorReproducibilityalways
Status fix availableResolutionopen 
PlatformDefaultOSany 
Product Version2014 Q2 
Target Version2014 Q4 
Summary0001295: fix_assurer_flag() function in includes/lib/account.php causes mysql 5.5 server warnings
DescriptionEach 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 Reproducefix_assurer_flag() is called from several places in the application, pick any.
TagsNo tags attached.
Reviewed by
Test Instructions

Relationships

related to 0001408 new API to return the Assurer Status to be used from CAcert systems 

Activities

INOPIAE

2014-08-05 20:54

updater   ~0004913

Last edited: 2014-08-05 20:55

View 2 revisions

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

Issue History

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 View Revisions
2014-08-05 21:13 BenBE Description Updated View Revisions
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