• This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn more.

XF 1.3 SQL error log

Tracy Perry

Well-known member
#1
Just got to reviewing my mySQL error logs on one of my sites and found a ton of these in it
Code:
2014-06-10 18:30:53 3209 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. INSERT... ON DUPLICATE KEY UPDATE  on a table with more than one UNIQUE KEY is unsafe Statement: INSERT INTO xf_deferred
                (execute_class, execute_data, unique_key, manual_execute, trigger_date)
            VALUES
                ('Cron', 'a:0:{}', 'cron', '0', '1402443109')
            ON DUPLICATE KEY UPDATE
                execute_class = VALUES(execute_class),
                execute_data = VALUES(execute_data),
                manual_execute = VALUES(manual_execute),
                trigger_date = VALUES(trigger_date)
2014-06-10 18:31:53 3209 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. INSERT... ON DUPLICATE KEY UPDATE  on a table with more than one UNIQUE KEY is unsafe Statement: INSERT INTO xf_deferred
                (execute_class, execute_data, unique_key, manual_execute, trigger_date)
            VALUES
                ('Cron', 'a:0:{}', 'cron', '0', '1402443413')
            ON DUPLICATE KEY UPDATE
                execute_class = VALUES(execute_class),
                execute_data = VALUES(execute_data),
                manual_execute = VALUES(manual_execute),
                trigger_date = VALUES(trigger_date)
2014-06-10 18:31:53 3209 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. INSERT... ON DUPLICATE KEY UPDATE  on a table with more than one UNIQUE KEY is unsafe Statement: INSERT INTO xf_deferred
                (execute_class, execute_data, unique_key, manual_execute, trigger_date)
            VALUES
                ('Cron', 'a:0:{}', 'cron', '0', '1402443169')
            ON DUPLICATE KEY UPDATE
                execute_class = VALUES(execute_class),
                execute_data = VALUES(execute_data),
                manual_execute = VALUES(manual_execute),
                trigger_date = VALUES(trigger_date)
Not sure if it's an add-on (and about to check my other two sites to see if they have the same) or specific to this site.
Using Percona 5.6:
mysql Ver 14.14 Distrib 5.6.17-65.0, for debian-linux-gnu (x86_64) using EditLine wrapper
 

MattW

Well-known member
#2
The last 2 are directly from deferred.php

PHP:
public function defer($class, array $data, $uniqueKey = null, $manual = false, $triggerDate = null)
        {
                $runner = XenForo_Deferred_Abstract::create($class);
                if (!$runner)
                {
                        return false;
                }

                if (!$triggerDate)
                {
                        $triggerDate = XenForo_Application::$time;
                }
                if (!$uniqueKey)
                {
                        $uniqueKey = null;
                }

                $manual = ($manual ? 1 : 0);

                $db = $this->_getDb();

                if ($uniqueKey)
                {
                        $uniqueHash = "$uniqueKey-$triggerDate" . ($data ? '-' . md5(serialize($data)) : '');
                        if (isset(self::$_uniqueDefers[$uniqueHash]))
                        {
                                return self::$_uniqueDefers[$uniqueHash];
                        }
                }
                else
                {
                        $uniqueHash = false;
                }

                $db->query('
                        INSERT INTO xf_deferred
                                (execute_class, execute_data, unique_key, manual_execute, trigger_date)
                        VALUES
                                (?, ?, ?, ?, ?)
                        ON DUPLICATE KEY UPDATE
                                execute_class = VALUES(execute_class),
                                execute_data = VALUES(execute_data),
                                manual_execute = VALUES(manual_execute),
                                trigger_date = VALUES(trigger_date)
                ', array($class, serialize($data),  $this->_getSafeUniqueKey($uniqueKey), $manual, $triggerDate));

                $id = $db->lastInsertId();

                if (!$manual)
                {
                        $this->updateNextDeferredTime();
                        XenForo_Application::$autoDeferredIds[] = $id;
                }
                else
                {
                        XenForo_Application::$manualDeferredIds[] = $id;
                }

                if ($uniqueHash)
                {
                        self::$_uniqueDefers[$uniqueHash] = $id;
                }

                return $id;
        }
 

Tracy Perry

Well-known member
#3
Yeah, I had tracked it down to that just a few minutes ago. I'm running the same my.cnf (except for a few settings pertaining to lower memory constraints on them) on the other two VPS's now and will monitor the error log.

If it happens on all 3 then something is definitely wonky in Willy World.
 

Tracy Perry

Well-known member
#4
And I just got on another one of my VPS's
Code:
Version: '5.6.17-65.0-587.wheezy-log'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Debian)
2014-06-10 19:24:25 8853 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. INSERT... ON DUPLICATE KEY UPDATE  on a table with more than one UNIQUE KEY is unsafe Statement: INSERT INTO xf_deferred
                (execute_class, execute_data, unique_key, manual_execute, trigger_date)
            VALUES
                ('Cron', 'a:0:{}', 'cron', '0', '1402446565')
            ON DUPLICATE KEY UPDATE
                execute_class = VALUES(execute_class),
                execute_data = VALUES(execute_data),
                manual_execute = VALUES(manual_execute),
                trigger_date = VALUES(trigger_date)
2014-06-10 19:24:27 8853 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. INSERT... ON DUPLICATE KEY UPDATE  on a table with more than one UNIQUE KEY is unsafe Statement: INSERT INTO xf_deferred
                (execute_class, execute_data, unique_key, manual_execute, trigger_date)
            VALUES
                ('Cron', 'a:0:{}', 'cron', '0', '1402446325')
            ON DUPLICATE KEY UPDATE
                execute_class = VALUES(execute_class),
                execute_data = VALUES(execute_data),
                manual_execute = VALUES(manual_execute),
                trigger_date = VALUES(trigger_date)
Same version of mysql.

@MattW, you want to check your mysql-error log and see if you happen to see the same.
 

MattW

Well-known member
#5
And I just got on another one of my VPS's
Code:
Version: '5.6.17-65.0-587.wheezy-log'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Debian)
2014-06-10 19:24:25 8853 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. INSERT... ON DUPLICATE KEY UPDATE  on a table with more than one UNIQUE KEY is unsafe Statement: INSERT INTO xf_deferred
                (execute_class, execute_data, unique_key, manual_execute, trigger_date)
            VALUES
                ('Cron', 'a:0:{}', 'cron', '0', '1402446565')
            ON DUPLICATE KEY UPDATE
                execute_class = VALUES(execute_class),
                execute_data = VALUES(execute_data),
                manual_execute = VALUES(manual_execute),
                trigger_date = VALUES(trigger_date)
2014-06-10 19:24:27 8853 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. INSERT... ON DUPLICATE KEY UPDATE  on a table with more than one UNIQUE KEY is unsafe Statement: INSERT INTO xf_deferred
                (execute_class, execute_data, unique_key, manual_execute, trigger_date)
            VALUES
                ('Cron', 'a:0:{}', 'cron', '0', '1402446325')
            ON DUPLICATE KEY UPDATE
                execute_class = VALUES(execute_class),
                execute_data = VALUES(execute_data),
                manual_execute = VALUES(manual_execute),
                trigger_date = VALUES(trigger_date)
Same version of mysql.

@MattW, you want to check your mysql-error log and see if you happen to see the same.
I don't have binlog enabled, so don't have these errors.
 

Tracy Perry

Well-known member
#6
That's just what I was going to ask you... about to disable it since I don't use replication (the main need for it) and it does effect performance negatively somewhat.