2.0 Upgrade Database Error

2.0 Upgrade Database Error

by Chris Reed -
Number of replies: 11

Windows Server 2003, IIS, PHP 5.3.3, MySQL database 5.1.29 Comm Server.  Anyone know how to fix this error?

Debug info: Binary logging not possible. Message: Transaction level 'READ-COMMITTED' in InnoDB is not safe for binlog mode 'STATEMENT'
DELETE FROM mdl_block_instances WHERE parentcontextid = ?
[array (
0 => '5',
)]
Stack trace:
•line 394 of \lib\dml\moodle_database.php: dml_write_exception thrown
•line 1046 of \lib\dml\mysqli_native_moodle_database.php: call to moodle_database->query_end()
•line 1575 of \lib\dml\moodle_database.php: call to mysqli_native_moodle_database->delete_records_select()
•line 1653 of \lib\blocklib.php: call to moodle_database->delete_records()
•line 1971 of \lib\accesslib.php: call to blocks_delete_all_for_context()
•line 2137 of \lib\accesslib.php: call to delete_context()
•line 1326 of \lib\upgradelib.php: call to cleanup_contexts()
•line 252 of \admin\index.php: call to upgrade_core()

Database transaction aborted automatically in D:\moodle/admin/index.php Default exception handler: Error writing to database Debug: Binary logging not possible. Message: Transaction level 'READ-COMMITTED' in InnoDB is not safe for binlog mode 'STATEMENT' DELETE FROM mdl_block_instances WHERE parentcontextid = ? [array ( 0 => '5', )] * line 394 of \lib\dml\moodle_database.php: dml_write_exception thrown * line 1046 of \lib\dml\mysqli_native_moodle_database.php: call to moodle_database->query_end() * line 1575 of \lib\dml\moodle_database.php: call to mysqli_native_moodle_database->delete_records_select() * line 1653 of \lib\blocklib.php: call to moodle_database->delete_records() * line 1971 of \lib\accesslib.php: call to blocks_delete_all_for_context() * line 2137 of \lib\accesslib.php: call to delete_context() * line 1326 of \lib\upgradelib.php: call to cleanup_contexts() * line 252 of \admin\index.php: call to upgrade_core()

Average of ratings: -
In reply to Chris Reed

Re: 2.0 Upgrade Database Error

by alex liu -

Hi Chris

 

I have the same error, did you find solution for this problem?

 

Alex

Average of ratings: -
In reply to Chris Reed

Re: 2.0 Upgrade Database Error

by Dzianis Kahanovich -

According to mysql bugzilla, InnoDB required "binlog_format=mixed" (my.cnf [mysqld]).

IMHO it may be verifyed in moodle as "binlog_format=STATEMENT" -> [upgrade] error. With post-upgrade fix for alredy failed upgrades or instructions.

Average of ratings: -
In reply to Dzianis Kahanovich

Re: 2.0 Upgrade Database Error

by Juan Segarra Montesinos -
Picture of Core developers Picture of Plugin developers

Hi smile

Moodle set transaction isolation level to READ-COMMITTED when beginning a transaction. In Innodb this mode is not safe with statement based binary logging. So you have to set binlog_format to MIXED or ROW... or don't use binnary logging smile

Mysql manual says that you must use ROW binlog_format... maybe MIXED should be enough:

http://dev.mysql.com/doc/refman/5.1/en/set-transaction.html

Space requirement for row based replication may be more... so be careful.

Regards smile

Average of ratings: -
In reply to Juan Segarra Montesinos

Re: 2.0 Upgrade Database Error

by Klaus Schramm -

@Juan:

Yes, the MIXED format worked for me (new install with Moodle 2.1 on old mysql database). I used the following steps:

1. added the following line to the section [mysqld] of the config file my.cnf:

binlog_format = 'MIXED'

2. restarted the server (killing of mysqld was not sufficient)

Best regards,
Klaus

Average of ratings: Useful (1)
In reply to Klaus Schramm

Re: 2.0 Upgrade Database Error

by sac m -

Thanks, this fixed my new moodle 2.2.1 installation on mariadb 5.3 on ubuntu 11.10

Average of ratings: -
In reply to Chris Reed

Re: 2.0 Upgrade Database Error

by Guy Thomas -
Picture of Core developers Picture of Plugin developers

I've experienced the same issue with this and I think that setting the bin log mode to MIXED has the following implications:

Logging will use STATEMENT mode under most circumstances and then switches to ROW mode for other circumstances as described in:

http://dev.mysql.com/doc/refman/5.1/en/binary-log-mixed.html

STATEMENT mode requires much less storage space as it will literally log the statement which was executed to affect the database.

ROW mode takes more space as it logs the changes that have occured to row data.

So if you ran "UPDATE mdl_user SET lastname='Smith'" on a moodle database (don't run this!) then STATEMENT mode would literally just log the statement you have executed (obviously this will not take up much space in the bin log). However, if you ran the same SQL in ROW mode then it would have to log the change of lastname to Smith for every single row in your database. So you can see how STATEMENT row is better in terms of bin log size efficiency.

MIXED mode is the best option to use for Moodle 2 as it will keep the bin log size as low as possible by using STATEMENT logging where ever possible.

I hope this helps people understand the concequences of having a MIXED bin log mode.

Guy Thomas - OvernetData

www.overnetdata.com

Average of ratings: -
In reply to Guy Thomas

Re: 2.0 Upgrade Database Error

by Stefan Sch -

Hi,

are there any other solutions for the problem. In my case it is not an option to change the binlog because the database runs in a mysql-cluster with different databases. Is it possible to change the parameter only for the moodle database or can I change the moodle configuration in my installation ?

 

Best Regards

Stefan

 

Average of ratings: Useful (1)
In reply to Stefan Sch

Re: 2.0 Upgrade Database Error

by rupamoy bhattacharya -

Hi, In addition to binlog_format update, no need to update manually my.cnf. Run the following command in mysq and wait for approx 2 minutes, it will reflect.

 

SHOW VARIABLES LIKE '%bin%'

It will show initially STATEMENT. Then run the following command and it will update the status to MIXED. It takes some time, reopen the mysql command line window and check.

SET GLOBAL binlog_format = 'MIXED';

 

Average of ratings: Useful (2)
In reply to rupamoy bhattacharya

Re: 2.0 Upgrade Database Error

by Tim Dalton -

SHOW VARIABLES LIKE '%bin%'

It will show initially STATEMENT. Then run the following command and it will update the status to MIXED. It takes some time, reopen the mysql command line window and check.

SET GLOBAL binlog_format = 'MIXED';

This worked perfectly for me today, thanks smile

Tim

Average of ratings: -
In reply to Tim Dalton

Re: 2.0 Upgrade Database Error

by Abdellah Azizi -

Hi, smile

What about MSSQL?

Average of ratings: -
In reply to Abdellah Azizi

Re: 2.0 Upgrade Database Error

by madhu bheemagani -

Before Upgrading you must check below requirements

Before upgrading what all the plug-ins available in Moodle 1.9, you have to cross check once in upgraded version plug-ins available or not in Moodle 2.2 package, if not there you have to put in respective plugins folder.

You have to be more careful if you have installed additional plug-ins or customized the code.


See More

Average of ratings: -