Error writing to database while upgrading to Moodle 3.5

by Bernard OUERAOGO - Wednesday, January 13, 2021, 1:53 AM

Hello,

I've been moving a moodle 2.4.5 site from one hosting company to another using CPanel (shared hosting). Everything worked. I even upgraded respectively to versions 2.7 and 3.1 without many difficulties.

The next upgrade to 3.5 does not work so well. The process started and stuck to this:

Upgrading to new version

format_topics

Error writing to database

More information about this error

Debug info: Field 'id' doesn't have a default value
INSERT INTO mdl_course_sections (course,section,summary,summaryformat,sequence,name,visible,availability,timemodified) VALUES(?,?,?,?,?,?,?,?,?)
[array (
0 => 530,
1 => 1,
2 => '',
3 => '1',
4 => '',
5 => NULL,
6 => 1,
7 => NULL,
8 => 1610472992,
)]
Error code: dmlwriteexception

Stack trace:

line 489 of /lib/dml/moodle_database.php: dml_write_exception thrown
line 1329 of /lib/dml/mysqli_native_moodle_database.php: call to moodle_database->query_end()
line 1375 of /lib/dml/mysqli_native_moodle_database.php: call to mysqli_native_moodle_database->insert_record_raw()
line 871 of /course/lib.php: call to mysqli_native_moodle_database->insert_record()
line 142 of /course/format/topics/db/upgradelib.php: call to course_create_section()
line 80 of /course/format/topics/db/upgradelib.php: call to format_topics_upgrade_add_empty_sections()
line 41 of /course/format/topics/db/upgrade.php: call to format_topics_upgrade_remove_numsections()
line 632 of /lib/upgradelib.php: call to xmldb_format_topics_upgrade()
line 1857 of /lib/upgradelib.php: call to upgrade_plugins()
line 694 of /admin/index.php: call to upgrade_noncore()

I use PHP 7.0 (PHP 7.0 (ea-php70))

Thanks in advance for your help.

Re: Error writing to database while upgrading to Moodle 3.5

by Ken Task - Wednesday, January 13, 2021, 2:45 AM

If you followed the link to 'more information about this error'
https://docs.moodle.org/35/en/error/moodle/dmlwriteexception

It says one possible fix is:

"MySQL
If you're using a MySQL database for your Moodle installation, this error can be caused by the server's max_allowed_packet size being configured incorrectly. Increasing this value may resolve the issue."

Is new hosting on shared hosting?

IF so, you won't be able to make the recommended change because you do not have a localhost DB, but a shared DB server and you cannot make changes to the config file for MySQL on shared hosting where DB server is shared.

Check the config.php file of code for DBhost variable.

Does it say 'localhost' or point to some 'ip address' or FQDN for a shared database server?

'SoS', Ken

Average of ratings:Useful (1)

Re: Error writing to database while upgrading to Moodle 3.5

by Bernard OUERAOGO - Thursday, January 14, 2021, 12:08 AM

Thanks Ken Task for replying.
The new site is on shared hosting. But CPanel allows creation of .user.ini and php.ini files for every domain and subdomain.
In config file, it's locqlhost that is used.

Average of ratings: -

Re: Error writing to database while upgrading to Moodle 3.5

by Ken Task - Thursday, January 14, 2021, 2:55 AM

localhost for DB is good but user.ini is a PHP thang.

Think you still might be restricted. Providers typically have something in cPanel that allows you to set up new databases for apps, but that DB user isn't the same as the superuser (root) for the DB server. Some providers do have access to your DB as superuser, but not the customer. They know that messed up databases by user might not be 'fixable' - time is $ and they don't want to spend a lot of time with such tweaks ... not needed for other apps ... like WordPress/Joomla's/Drupals ... but for Moodle, those tweaks are needed.

Even with Leon's fix, the db user in config.php of your moodle must have privileges enough to 'alter' a table.

In your cPanel, the icon for PHPMyAdmin for a domain will show only the DB for that domain ... won't show other databases that exist: mysql, information_schema, performance_schema, and sys.

Hopefully, the DB user in config.php has the privs to alter a table.

Acid test ... from phpmyadmin, run this query:

show privileges;

If you can.

If not, you'll have to contact your providers support desk and ask them to run the query that Leon provided.

My 2 cents!

'SoS', Ken

Average of ratings: -

Re: Error writing to database while upgrading to Moodle 3.5

by Bernard OUERAOGO - Thursday, January 14, 2021, 8:03 PM

Thanks Ken, for your constant help in this forum.

Average of ratings: -

Re: Error writing to database while upgrading to Moodle 3.5

by Leon Stringer - Thursday, January 14, 2021, 12:35 AM

"Field 'id' doesn't have a default value" – This error suggests there's a problem with the id field's definition in database table mdl_course_sections. The id field should be set to auto_increment which is used for the default value. If you view the table's definition, e.g with the statement DESC mdl_course_sections, it should start with:

+---------------+--------------+------+-----+---------+----------------+
| Field         | Type         | Null | Key | Default | Extra          |
+---------------+--------------+------+-----+---------+----------------+
| id            | bigint(10)   | NO   | PRI | NULL    | auto_increment |

If your table is missing the auto_increment you should be able to fix this with:

ALTER TABLE mdl_course_sections MODIFY id MEDIUMINT AUTO_INCREMENT PRIMARY KEY;

Average of ratings: -

Re: Error writing to database while upgrading to Moodle 3.5

by Bernard OUERAOGO - Thursday, January 14, 2021, 8:02 PM

Hello Leon.
Thanks a lot. I did what you suggested, and the database update succeeded. The plugins are updated.
Then, another problem arose:

Error reading from database

More information about this error (http://docs.moodle.org/35/en/error/moodle/dmlreadexception)
Debug info: Unknown column 'availability' in 'field list'
SELECT section, id, course, name, summary, summaryformat, sequence, visible, availability FROM mdl_course_sections WHERE course = ? ORDER BY section
[array (
0 => '1',
)]
Error code: dmlreadexception
Stack trace:

line 486 of /lib/dml/moodle_database.php: dml_read_exception thrown
line 1245 of /lib/dml/mysqli_native_moodle_database.php: call to moodle_database->query_end()
line 1400 of /lib/dml/moodle_database.php: call to mysqli_native_moodle_database->get_records_sql()
line 1351 of /lib/dml/moodle_database.php: call to moodle_database->get_records_select()
line 573 of /lib/modinfolib.php: call to moodle_database->get_records()
line 672 of /lib/modinfolib.php: call to course_modinfo::build_course_section_cache()
line 463 of /lib/modinfolib.php: call to course_modinfo::inner_build_course_cache()
line 418 of /lib/modinfolib.php: call to course_modinfo->__construct()
line 2169 of /lib/modinfolib.php: call to course_modinfo::instance()
line 1973 of /lib/navigationlib.php: call to get_fast_modinfo()
line 2037 of /lib/navigationlib.php: call to global_navigation->generate_sections_and_activities()
line 502 of /course/format/lib.php: call to global_navigation->load_generic_course_sections()
line 1952 of /lib/navigationlib.php: call to format_base->extend_course_navigation()
line 1244 of /lib/navigationlib.php: call to global_navigation->load_course_sections()
line 3977 of /lib/navigationlib.php: call to global_navigation->initialise()
line 758 of /lib/pagelib.php: call to settings_navigation->__construct()
line 820 of /lib/pagelib.php: call to moodle_page->magic_get_settingsnav()
line 7933 of /lib/adminlib.php: call to moodle_page->__get()
line 32 of /admin/purgecaches.php: call to admin_externalpage_setup()

Still needing your help.

Average of ratings: -

Re: Error writing to database while upgrading to Moodle 3.5

by Bernard OUERAOGO - Thursday, January 14, 2021, 8:22 PM

I found the solution< A column was missing in the table and I altered it to add it. Column 'availability'.
Now, the moodle website is running perfetcly.
Thanks to all of you who helped.

Average of ratings: -

Installing and upgrading help

Error writing to database while upgrading to Moodle 3.5

Upgrading to new version

format_topics