Jump to content
sirnick

Need help with Database issue - site is down

Recommended Posts

My site has been online for 14 years and never seen this issue, It worked fine all day till about 4pm now I get this error. I cant repair the whos online db. My who_is is InnoDB so it cant be repaired I am not sure the cause of this issue.

1665 - Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging. InnoDB is limited to row-logging when transaction isolation level is READ COMMITTED or READ UNCOMMITTED.

delete from whos_online where time_last_click < '1619655512'

Share this post


Link to post
Share on other sites

That is due to how mysql is setup on the server. Your host will have to make a change to fix it. My guess is that your host updated php and/or mysql since you said the shop has been working. If they did that, your shop probably won't work since a shop that old wouldn't be able to run on php higher than 5.4 unless the code has been altered. I suggest that when you contact them about the error, you also ask what php version the site is using and if they have made changes to the servers version.

Share this post


Link to post
Share on other sites
Posted (edited)

Hi I did call but they say everything the same on their end. I already had the DB adjusted and PHP upgraded to 7.0 a while back so i don't think its that. Godaddy is not helpful cause I don't really know when to tell them they say to contact my web admin that its not on their end. But he not responding atm

Edited by sirnick

Share this post


Link to post
Share on other sites
  • Server: Localhost via UNIX socket
  • Server type: MySQL
  • Server connection: SSL is not being used Documentation
  • Server version: 5.6.49-cll-lve - MySQL Community Server (GPL)
  • Protocol version: 10
  • User: 
  • Server charset: cp1252 West European (latin1)

Web server

  • cpsrvd 11.86.0.30
  • Database client version: libmysql - 5.6.43
  • PHP extension: mysqliDocumentation curlDocumentation mbstringDocumentation
  • PHP version: 7.3.6

Share this post


Link to post
Share on other sites

Are they using 7.3.6 and I am on 7?

I know mine has 7 selected I can drop to 5.6 but I moved away from that when they made me couple years ago

 

Share this post


Link to post
Share on other sites

Would adjusting any of this help? If I try to check or repair the DB it says theres nothing wrong

phpver.png

Share this post


Link to post
Share on other sites

If your 14 year old shop can run on php 7, then it has definitely been altered. That makes troubleshooting more difficult since it is not standard. The first thing I would try is to lower the php version. But the error you mentioned is due a server setting so that may not help. Godaddy is not good when it comes to helping with php issues so I wouldn't put a lot of faith in their response.

Share this post


Link to post
Share on other sites
Posted (edited)

I appreciate your insight. I will get to the bottom of it or switch hosts. Any recommendations in this situation for a host that does help or understand these situations? For future sake

 

Edited by sirnick

Share this post


Link to post
Share on other sites

The error is due to how MySQL is configured on the server, something you have no control over. You need to speak to someone else at your host's technical support about the issue. Or if they don't care to help you, then switch hosts.

The host you choose depends a little on your geographic location, and you should do your homework on the hosting company so that you don't have to keep switching hosts in future.

https://stackoverflow.com/questions/33142095/impossible-to-write-to-binary-log-since-binlog-format-statement-and-at-least-o

Share this post


Link to post
Share on other sites
Posted (edited)
21 hours ago, sirnick said:

My site has been online for 14 years and never seen this issue, It worked fine all day till about 4pm now I get this error. I cant repair the whos online db. My who_is is InnoDB so it cant be repaired I am not sure the cause of this issue.

1665 - Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging. InnoDB is limited to row-logging when transaction isolation level is READ COMMITTED or READ UNCOMMITTED.

delete from whos_online where time_last_click < '1619655512'

Hmm "site has been online for 14 years and never seen this issue.." is kinda a funny statement...also "repair the whos online db..." I know this is a serious issue and there's nothing funny about it...k, couple questions:

  1. why was it that you need to repair the whosonilne table?
  2. safe to assume your osc prod db tables are running entirely on InnoDB?

You hosting is providing mysql with binlog (transaction logging) that is good for recovery or replication etc. Very well. So for mysql the default isolation level is REPEATABLE READ and don't ask me why while the rest of the DBs in this world seems to defaults to READ COMMITTED. READ COMMITTED is good so your hosting company knew what they were doing and had modified the default isolation level of myql...again, very well...

K, try this...

  1. fix the reason that caused the whosonline table to be corrupted.
  2. check to see if there is an index on the time_last_click column. mysql row level locking locks by index and without it it may try to obtain a table level lock which typically is a no no. so the index may help.
  3. can't image the whosonline table is huge and if it was i would make sure that u run analyze table to update the table stats so the engine optimizer runs in tip-top shape.
  4. it is the whosonline table...try truncating it...

Good luck!

Oh, one more thing, you can also set the session isolation level to repeatable read before doing the repair...if u think that's the reason that the repair table was failing....

 

 

 

 

Edited by clustersolutions

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×