Cannot upgrade to 3.9 because of ddl sql error 'wrong create options'

Cannot upgrade to 3.9 because of ddl sql error 'wrong create options'

by Jeff Hopper -
Number of replies: 6

My current 3.8.x instance of Moodle is using MariaDb with page compression.

When trying to upgrade to 3.9, my install fails with a DDL sql execution error - citing errno: 140 "Wrong create options".  I'm fairly confident the issue is related to creation of the mdl_contentbank_content table with the ROW_FORMAT=Compressed option (table compression). The two compression schemes being mutually exclusive.

My question to the community is: Where do I find and modify the upgrade script/code to remove the offending table compression directives before attempting the database update? Is there possibly a config option to select page compression? (I can hope... right?)

Searching the documentation, I see references to such files being called /db/install.xml and/or /db/upgrade.php. But I did not find either in my instance of Moodle.

I would appreciate it if someone could point me in the correct direction on this. Thanks in advance.

Attachment 2020-06-16_21-39-36.png
Average of ratings: -
In reply to Jeff Hopper

Re: Cannot upgrade to 3.9 because of ddl sql error 'wrong create options'

by Ken Task -
Picture of Particularly helpful Moodlers

Confused ... page compression is typically a web server thang .... which isn't the same as DB compressed rows.

Prior to upgrade attempt did you check server environment to see if server had versions needed?

https://docs.moodle.org/dev/Moodle_3.9_release_notes#Database_requirements

show that MariaDB version should be a minimum of 10.2.29 and notes that is an increase since Moodle 3.8.

There is a script in moodlecode/admin/cli/ called mysql_compressed_rows.php

command line only .... run with the -i (information) option to see what's up with your DB and share back what that script outputs.

If you have compat version, the following will show tables that are compressed or need to be compressed:

php mysql_compressed_rows.php -l (that's a lower case L)

-f, --fix             Attempt to fix all tables (requires SUPER privilege)
-s, --showsql         Print SQL statements for fixing of tables

'SoS', Ken

Average of ratings: -
In reply to Ken Task

Re: Cannot upgrade to 3.9 because of ddl sql error 'wrong create options'

by Jeff Hopper -
Ken,

Thank you for your quick reply.

I understand how proper names that cross domains can cause confusion. I'm certain this is a 'database thang' smile Here's info on the page compression I'm referring to: https://mariadb.com/kb/en/innodb-page-compression/

I also confirmed I'm using MariaDB 10.3.7

I don't believe it wise for me to try to compress tables using the referenced script - as that option is incompatible with my existing DB configuration.

Any insight on modifying the 3.8->3.9 database change requests?

Blessings and thanks!
Average of ratings: -
In reply to Jeff Hopper

Re: Cannot upgrade to 3.9 because of ddl sql error 'wrong create options'

by Ken Task -
Picture of Particularly helpful Moodlers

If you taking about hacking 3.9 code ... beyond me. 

However, once hacked (if one finds a way) to get by this won't you forever be facing the same issues on any update to 3.9 and beyond?

From MariaDB docs:

Enabling InnoDB Page Compression

InnoDB page compression is not enabled by default. However, InnoDB page compression can be enabled for just individual InnoDB tables or it can be enabled for all new InnoDB tables by default.

InnoDB page compression is also only supported if the InnoDB table is in a file per-table tablespace. Therefore, the innodb_file_per_table system variable must be set to ON to use InnoDB page compression.

InnoDB page compression is only supported if the InnoDB table uses the Barracuda file format.Therefore, in MariaDB 10.1 and before, the innodb_file_format system variable must be set to Barracuda to use InnoDB page compression.

InnoDB page compression is also only supported if the InnoDB table's row format is COMPACT or DYNAMIC.

You can use set global commands

SET GLOBAL innodb_compression_default=ON;

This system variable's session value can be changed dynamically with SET SESSION. For example:

SET GLOBAL innodb_file_per_table=ON;

SET GLOBAL innodb_file_format='Barracuda';

SET GLOBAL innodb_default_row_format='dynamic';

*SET GLOBAL innodb_compression_algorithm='lzma';

SET SESSION  innodb_compression_default=ON;

* the one above is something not in any docs for Moodle known to me.

What does:

php mysql_compressed_rows.php -i

show?

as well as

php mysql_compressed_rows.php -l

Neither one of those changes ... but they do inform.

What does:

show variables like '%innodb%';

show?

'SoS', Ken


Average of ratings: -
In reply to Ken Task

Re: Cannot upgrade to 3.9 because of ddl sql error 'wrong create options'

by Jeff Hopper -
Ken,

Thanks for taking your time to offer suggestions.

I'm confident that I have the database configured and tuned for my particular SSD-storage optimized database server (at least as far as I know how to... ;) And regarding 'hacking the code', I'm a software developer by trade, so making a configuration adjustment before running the database upgrade doesn't scare me. I just need to know where it's happening and, more importantly, how the Moodle upgrade process is open to such configuration tweaks. I originally installed Moodle and upgrade it using the git repository strategy, so configuration modifications seem like a viable option. I'm absolutely surprised I couldn't find any existing dialog on this, since page-based table compression appeared to be commonly discussed as an avenue for SSD-based mysql/mariadb database optimization. Maybe someone with experience with this type of configuration will chime in...

However, for completeness, the following is the information you requested. Again thanks for your time.

mysql_compressed_rows.php -i
Database version: 5.5.5-10.3.7-MariaDB
Database name: moodle-some-alt-name
Database engine: InnoDB
innodb_file_per_table: ON
innodb_file_format:

mysql_compressed_rows.php -l
mdl_badge Dynamic
mdl_data Dynamic
mdl_data_fields Dynamic
mdl_enrol_lti_lti2_consumer Dynamic
mdl_enrol_paypal Dynamic
mdl_lti Dynamic
mdl_oauth2_issuer Dynamic
mdl_user Dynamic
mdl_user_info_field Dynamic

show variables like '%innodb%';
+---------------------------------------------+--------------------------------------+
| Variable_name | Value |
+---------------------------------------------+--------------------------------------+
| ignore_builtin_innodb | OFF |
| innodb_adaptive_flushing | ON |
| innodb_adaptive_flushing_lwm | 10.000000 |
| innodb_adaptive_hash_index | ON |
| innodb_adaptive_hash_index_parts | 8 |
| innodb_adaptive_max_sleep_delay | 150000 |
| innodb_autoextend_increment | 64 |
| innodb_autoinc_lock_mode | 1 |
| innodb_background_scrub_data_check_interval | 3600 |
| innodb_background_scrub_data_compressed | OFF |
| innodb_background_scrub_data_interval | 604800 |
| innodb_background_scrub_data_uncompressed | OFF |
| innodb_buf_dump_status_frequency | 0 |
| innodb_buffer_pool_chunk_size | 16777216 |
| innodb_buffer_pool_dump_at_shutdown | ON |
| innodb_buffer_pool_dump_now | OFF |
| innodb_buffer_pool_dump_pct | 25 |
| innodb_buffer_pool_filename | ib_buffer_pool |
| innodb_buffer_pool_instances | 1 |
| innodb_buffer_pool_load_abort | OFF |
| innodb_buffer_pool_load_at_startup | ON |
| innodb_buffer_pool_load_now | OFF |
| innodb_buffer_pool_size | 16777216 |
| innodb_change_buffer_max_size | 25 |
| innodb_change_buffering | all |
| innodb_checksum_algorithm | crc32 |
| innodb_checksums | ON |
| innodb_cmp_per_index_enabled | OFF |
| innodb_commit_concurrency | 0 |
| innodb_compression_algorithm | zlib |
| innodb_compression_default | ON |
| innodb_compression_failure_threshold_pct | 5 |
| innodb_compression_level | 6 |
| innodb_compression_pad_pct_max | 50 |
| innodb_concurrency_tickets | 5000 |
| innodb_data_file_path | ibdata1:10M:autoextend |
| innodb_data_home_dir | /var/packages/MariaDB10/target/mysql |
| innodb_deadlock_detect | ON |
| innodb_default_encryption_key_id | 1 |
| innodb_default_row_format | dynamic |
| innodb_defragment | OFF |
| innodb_defragment_fill_factor | 0.900000 |
| innodb_defragment_fill_factor_n_recs | 20 |
| innodb_defragment_frequency | 40 |
| innodb_defragment_n_pages | 7 |
| innodb_defragment_stats_accuracy | 0 |
| innodb_disable_sort_file_cache | OFF |
| innodb_disallow_writes | OFF |
| innodb_doublewrite | OFF |
| innodb_encrypt_log | OFF |
| innodb_encrypt_tables | OFF |
| innodb_encryption_rotate_key_age | 1 |
| innodb_encryption_rotation_iops | 100 |
| innodb_encryption_threads | 0 |
| innodb_fast_shutdown | 1 |
| innodb_fatal_semaphore_wait_threshold | 600 |
| innodb_file_per_table | ON |
| innodb_fill_factor | 100 |
| innodb_flush_log_at_timeout | 1 |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_flush_method | fsync |
| innodb_flush_neighbors | 1 |
| innodb_flush_sync | ON |
| innodb_flushing_avg_loops | 30 |
| innodb_force_load_corrupted | OFF |
| innodb_force_primary_key | OFF |
| innodb_force_recovery | 0 |
| innodb_ft_aux_table | |
| innodb_ft_cache_size | 8000000 |
| innodb_ft_enable_diag_print | OFF |
| innodb_ft_enable_stopword | ON |
| innodb_ft_max_token_size | 84 |
| innodb_ft_min_token_size | 3 |
| innodb_ft_num_word_optimize | 2000 |
| innodb_ft_result_cache_limit | 2000000000 |
| innodb_ft_server_stopword_table | |
| innodb_ft_sort_pll_degree | 2 |
| innodb_ft_total_cache_size | 640000000 |
| innodb_ft_user_stopword_table | |
| innodb_idle_flush_pct | 100 |
| innodb_immediate_scrub_data_uncompressed | OFF |
| innodb_io_capacity | 10000 |
| innodb_io_capacity_max | 20000 |
| innodb_lock_schedule_algorithm | vats |
| innodb_lock_wait_timeout | 50 |
| innodb_locks_unsafe_for_binlog | OFF |
| innodb_log_buffer_size | 16777216 |
| innodb_log_checksums | ON |
| innodb_log_compressed_pages | ON |
| innodb_log_file_size | 50331648 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | /var/packages/MariaDB10/target/mysql |
| innodb_log_write_ahead_size | 8192 |
| innodb_lru_scan_depth | 1024 |
| innodb_max_dirty_pages_pct | 75.000000 |
| innodb_max_dirty_pages_pct_lwm | 0.000000 |
| innodb_max_purge_lag | 0 |
| innodb_max_purge_lag_delay | 0 |
| innodb_max_undo_log_size | 10485760 |
| innodb_monitor_disable | |
| innodb_monitor_enable | |
| innodb_monitor_reset | |
| innodb_monitor_reset_all | |
| innodb_old_blocks_pct | 37 |
| innodb_old_blocks_time | 1000 |
| innodb_online_alter_log_max_size | 134217728 |
| innodb_open_files | 300 |
| innodb_optimize_fulltext_only | OFF |
| innodb_page_cleaners | 1 |
| innodb_page_size | 16384 |
| innodb_prefix_index_cluster_optimization | OFF |
| innodb_print_all_deadlocks | OFF |
| innodb_purge_batch_size | 300 |
| innodb_purge_rseg_truncate_frequency | 128 |
| innodb_purge_threads | 4 |
| innodb_random_read_ahead | OFF |
| innodb_read_ahead_threshold | 56 |
| innodb_read_io_threads | 4 |
| innodb_read_only | OFF |
| innodb_replication_delay | 0 |
| innodb_rollback_on_timeout | OFF |
| innodb_rollback_segments | 128 |
| innodb_scrub_log | OFF |
| innodb_scrub_log_speed | 256 |
| innodb_sort_buffer_size | 1048576 |
| innodb_spin_wait_delay | 4 |
| innodb_stats_auto_recalc | ON |
| innodb_stats_include_delete_marked | OFF |
| innodb_stats_method | nulls_equal |
| innodb_stats_modified_counter | 0 |
| innodb_stats_on_metadata | OFF |
| innodb_stats_persistent | ON |
| innodb_stats_persistent_sample_pages | 20 |
| innodb_stats_sample_pages | 8 |
| innodb_stats_traditional | ON |
| innodb_stats_transient_sample_pages | 8 |
| innodb_status_output | OFF |
| innodb_status_output_locks | OFF |
| innodb_strict_mode | ON |
| innodb_sync_array_size | 1 |
| innodb_sync_spin_loops | 30 |
| innodb_table_locks | ON |
| innodb_temp_data_file_path | ibtmp1:12M:autoextend |
| innodb_thread_concurrency | 0 |
| innodb_thread_sleep_delay | 10000 |
| innodb_tmpdir | |
| innodb_undo_directory | ./ |
| innodb_undo_log_truncate | OFF |
| innodb_undo_logs | 128 |
| innodb_undo_tablespaces | 0 |
| innodb_use_atomic_writes | ON |
| innodb_use_native_aio | ON |
| innodb_version | 10.3.7 |
| innodb_write_io_threads | 4 |
+---------------------------------------------+--------------------------------------+
Average of ratings: -
In reply to Jeff Hopper

Re: Cannot upgrade to 3.9 because of ddl sql error 'wrong create options'

by Ken Task -
Picture of Particularly helpful Moodlers

I certainly do not have the creds you do.  Am not a programmer nor a true DB admin.  I do 'dig' a lot ... sooo the following is a 'dig' ... nugget or fools gold ... I can't really tell.  Maybe one of the following will help:

https://www.percona.com/blog/2017/11/20/innodb-page-compression/

https://www.percona.com/doc/percona-server/LATEST/flexibility/compressed_columns.html

The error is about tables related to something new in Moodle ... contentbank ... am wondering if one could set tables with characteristics where Moodle upgrade wouldn't complain upon attempts to upgrade.

If it helps:

mysql> show tables like '%content%';
+--------------------------------+
| Tables_in_moodle39 (%content%) |
+--------------------------------+
| mdl_contentbank_content        |
| mdl_data_content               |
| mdl_h5p_contents_libraries     |
+--------------------------------+


explain  mdl_contentbank_content;
+--------------+--------------+------+-----+---------+----------------+
| Field        | Type         | Null | Key | Default | Extra          |
+--------------+--------------+------+-----+---------+----------------+
| id           | bigint(10)   | NO   | PRI | NULL    | auto_increment |
| name         | varchar(255) | NO   | MUL |         |                |
| contenttype  | varchar(100) | NO   |     |         |                |
| contextid    | bigint(10)   | NO   | MUL | NULL    |                |
| instanceid   | bigint(10)   | YES  |     | NULL    |                |
| configdata   | longtext     | YES  |     | NULL    |                |
| usercreated  | bigint(10)   | NO   | MUL | NULL    |                |
| usermodified | bigint(10)   | YES  | MUL | NULL    |                |
| timecreated  | bigint(10)   | NO   |     | 0       |                |
| timemodified | bigint(10)   | YES  |     | 0       |                |
+--------------+--------------+------+-----+---------+----------------+

Hope a true DB admin person comes along soon! smile

'SoS', Ken

Average of ratings: -
In reply to Jeff Hopper

Re: Cannot upgrade to 3.9 because of ddl sql error 'wrong create options'

by Jeff Hopper -
Followup Info:
Finding no documented solution to the issue, I decided to troubleshoot the error by following the logic outlined in the Stack trace. That quickly led me to the offending code - a function in mysqli_native_moodle_database.php that tests to determine if ROW_FORMAT = Compressed is supported, and if so, adds that property when creating new tables in the Moodle database. Unfortunately, that function does not test the status of innodb_compression_default... for which ON = page compression active (the two compression strategies are mutually exclusive.) I've created a bug report for the Moodle team on the problem (https://tracker.moodle.org/browse/MDL-69196) which includes a patch that corrects the issue.

I'm pleased to report that applying the patch locally before updating the database resolved my issues. Hopefully the patch will be merged into the Moodle source code for the benefit of others choosing this optimization technique in the near future. If not, a patch is available in the bug report that can be applied locally.

Lastly, I want to give a shout out to Ken Task for his quick and generous offering of assistance and research on my issue. Kudos my friend smile
Average of ratings: Useful (1)