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

Mysql error log getting huge due to delayed inserts?

kontrabass

Well-known member
#1
Code:
('412552')
140509 13:34:08 [Warning] Statement may not be safe to log in statement format. Statement: INSERT DELAYED INTO xf_attachment_view
(attachment_id)
VALUES
('416316')
140509 13:34:08 [Warning] Statement may not be safe to log in statement format. Statement: INSERT DELAYED INTO xf_attachment_view
(attachment_id)
VALUES
('412551')
140509 13:34:08 [Warning] Statement may not be safe to log in statement format. Statement: INSERT DELAYED INTO xf_attachment_view
(attachment_id)
VALUES
('416320')
140509 13:34:08 [Warning] Statement may not be safe to log in statement format. Statement: INSERT DELAYED INTO xf_attachment_view
(attachment_id)
VALUES
('417094')
140509 13:34:08 [Warning] Statement may not be safe to log in statement format. Statement: INSERT DELAYED INTO xf_attachment_view
(attachment_id)
VALUES
('417101')
140509 13:34:08 [Warning] Statement may not be safe to log in statement format. Statement: INSERT DELAYED INTO xf_thread_view
(thread_id)
VALUES
('708091')
140509 13:34:09 [Warning] Statement may not be safe to log in statement format. Statement: INSERT DELAYED INTO xf_thread_view
(thread_id)
VALUES
('673555')
140509 13:34:09 [Warning] Statement may not be safe to log in statement format. Statement: INSERT DELAYED INTO xf_thread_view
(thread_id)
VALUES
('560632')
140509 13:34:09 [Warning] Statement may not be safe to log in statement format. Statement: INSERT DELAYED INTO xf_thread_view
(thread_id)
VALUES
('1075779')
140509 13:34:10 [Warning] Statement may not be safe to log in statement format. Statement: INSERT DELAYED INTO xf_thread_view
(thread_id)
VALUES
('1070836')
140509 13:34:10 [Warning] Statement may not be safe to log in statement format. Statement: INSERT DELAYED INTO xf_thread_view
(thread_id)
VALUES
('1073453')
140509 13:34:10 [Warning] Statement may not be safe to log in statement format. Statement: INSERT DELAYED INTO xf_thread_view
(thread_id)
VALUES
('1076189')
My server tech worked around this issue by changing the bin log format to "mixed" (error log is 2.3G and growing fast). But he said there's a deeper issue that could be fixed:

I have changed the binlog format to mixed which prevents this warning and works around the problem, really though this problem should be fixed by removing the 'DELAYED' option as delayed inserts that get replicated to slaves can cause auto increment fields to get out of sync.
Ideas? Thoughts?

Thanks!
 

Mike

XenForo developer
Staff member
#2
Frankly, I think a "mixed" bin log format is a good option as there are some other statements that cannot be rewritten.

Regardless, to deal with this specific case, you can disable the "Enable Delayed Insert SQL Queries" option.
 

kontrabass

Well-known member
#3
Thanks Mike! I like the idea of delayed inserts for things like view counts. If I leave delayed inserts on and use the mixed bin log format, is my slave DB going to be corrupt or otherwise unusable ("out of sync" as the tech puts it)?. Or are the delayed inserts trivial and something that can be rebuilt if we have to fall back to our slave db?

Thanks again, I know you're busy!