Installation help

2.0 Upgrade Database Error

 
 
Picture of Chris Reed
2.0 Upgrade Database Error
 

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: -
Picture of alex liu
Re: 2.0 Upgrade Database Error
 

Hi Chris

 

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

 

Alex

 
Average of ratings: -
Picture of Dzianis Kahanovich
Re: 2.0 Upgrade Database Error
 

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: -
ego
Re: 2.0 Upgrade Database Error
 

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: -
Klaus Schramm (in early years)
Re: 2.0 Upgrade Database Error
 

@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)
Picture of sac m
Re: 2.0 Upgrade Database Error
 

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

 
Average of ratings: -
Picture of Guy Thomas
Re: 2.0 Upgrade Database Error
 

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: -
Picture of Stefan Sch
Re: 2.0 Upgrade Database Error
 

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)
Picture of rupamoy bhattacharya
Re: 2.0 Upgrade Database Error
 

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 (1)
tim
Re: 2.0 Upgrade Database Error
 

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: -
Picture of Abdellah Azizi
Re: 2.0 Upgrade Database Error
 

Hi, smile

What about MSSQL?

 
Average of ratings: -
Picture of madhu bheemagani
Re: 2.0 Upgrade Database Error
 

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: -