View Revisions: Issue #1295

Summary 0001295: fix_assurer_flag() function in includes/lib/account.php causes mysql 5.5 server warnings
Revision 2014-08-05 21:13 by BenBE
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
Revision 2014-08-04 14:42 by wytze
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: 0000011#011WHERE `u`.`id` = 'XXXXXX'
Jun 23 20:09:52 webdb mysqld: 0000011#011#011AND EXISTS(
Jun 23 20:09:52 webdb mysqld: 0000011#011#011#011SELECT 1 FROM `cats_passed` AS `cp`, `cats_variant` AS `cv`
Jun 23 20:09:52 webdb mysqld: 0000011#011#011#011WHERE `cp`.`variant_id` = `cv`.`id`
Jun 23 20:09:52 webdb mysqld: 0000011#011#011#011#011AND `cv`.`type_id` = 1
Jun 23 20:09:52 webdb mysqld: 0000011#011#011#011#011AND `cp`.`user_id` = `u`.`id`
Jun 23 20:09:52 webdb mysqld: 0000011#011#011)
Jun 23 20:09:52 webdb mysqld: 0000011#011#011AND (
Jun 23 20:09:52 webdb mysqld: 0000011#011#011#011SELECT SUM(`points`) FROM `notary` AS `n`
Jun 23 20:09:52 webdb mysqld: 0000011#011#011#011WHERE `n`.`to` = `u`.`id`
Jun 23 20:09:52 webdb mysqld: 0000011#011#011#011#011AND (`n`.`expire` > now()
Jun 23 20:09:52 webdb mysqld: 0000011#011#011#011#011 OR `n`.`expire` IS NULL)
Jun 23 20:09:52 webdb mysqld: 0000011#011#011#011#011AND `n`.`deleted` = 0
Jun 23 20:09:52 webdb mysqld: 0000011#011#011) >= 100