mariadb cant create table wrong create options

MySQL Can’t create table (error: 140 “Wrong create options”)

Problem

Last week, we had to migrate a magento 1.7 website to latest magento 1.9.3.6 and we met an errorERROR 1005 (HY000) at line 3324: Can't create table `localdb`.`catalog_product_relation` (errno: 140 "Wrong create options")

It happened when I tried to run MySQL command to import Db into local development environment

mysql -hmysql -uroot -p localdb < clean_magento_db.sql
Code language: CSS (css)

Debugging

I did some research and found a blog of MariaDb wrote about this issue so I think there might be some differences between our local MariaDb and production MySQL Server.

MySQL ProductionLocal MariaDb
mysql Ver 14.14 Distrib 5.5.62-38.14,
for Linux (x86_64) using readline 5.1
mysql Ver 15.1 Distrib 10.3.22-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

I was a bit worried because the version is quite different.

I tried to compare the InnoDb settings between on production and local MariaDB as well

Production Server

+-------------------------------------------+------------------------+ | Variable_name | Value | +-------------------------------------------+------------------------+ | innodb_adaptive_flushing | ON | | innodb_adaptive_flushing_method | estimate | | innodb_adaptive_hash_index | ON | | innodb_adaptive_hash_index_partitions | 1 | | innodb_additional_mem_pool_size | 8388608 | | innodb_autoextend_increment | 2 | | innodb_autoinc_lock_mode | 1 | | innodb_blocking_buffer_pool_restore | OFF | | innodb_buffer_pool_instances | 2 | | innodb_buffer_pool_populate | OFF | | innodb_buffer_pool_restore_at_startup | 0 | | innodb_buffer_pool_shm_checksum | ON | | innodb_buffer_pool_shm_key | 0 | | innodb_buffer_pool_size | 4294967296 | | innodb_change_buffering | all | | innodb_checkpoint_age_target | 0 | | innodb_checksums | ON | | innodb_commit_concurrency | 0 | | innodb_concurrency_tickets | 500 | | innodb_corrupt_table_action | assert | | innodb_data_file_path | ibdata1:10M:autoextend | | innodb_data_home_dir | | | innodb_dict_size_limit | 0 | | innodb_doublewrite | ON | | innodb_doublewrite_file | | | innodb_fake_changes | OFF | | innodb_fast_checksum | OFF | | innodb_fast_shutdown | 1 | | innodb_file_format | Barracuda | | innodb_file_format_check | ON | | innodb_file_format_max | Barracuda | | innodb_file_per_table | ON | | innodb_flush_log_at_trx_commit | 2 | | innodb_flush_method | O_DIRECT | | innodb_flush_neighbor_pages | area | | innodb_force_load_corrupted | OFF | | innodb_force_recovery | 0 | | innodb_ibuf_accel_rate | 100 | | innodb_ibuf_active_contract | 1 | | innodb_ibuf_max_size | 2147467264 | | innodb_import_table_from_xtrabackup | 0 | | innodb_io_capacity | 200 | | innodb_kill_idle_transaction | 0 | | innodb_large_prefix | OFF | | innodb_lazy_drop_table | 0 | | innodb_lock_wait_timeout | 100 | | innodb_locking_fake_changes | ON | | innodb_locks_unsafe_for_binlog | OFF | | innodb_log_block_size | 512 | | innodb_log_buffer_size | 8388608 | | innodb_log_file_size | 134217728 | | innodb_log_files_in_group | 2 | | innodb_log_group_home_dir | ./ | | innodb_max_bitmap_file_size | 104857600 | | innodb_max_changed_pages | 1000000 | | innodb_max_dirty_pages_pct | 75 | | innodb_max_purge_lag | 0 | | innodb_merge_sort_block_size | 1048576 | | innodb_mirrored_log_groups | 1 | | innodb_old_blocks_pct | 37 | | innodb_old_blocks_time | 0 | | innodb_open_files | 300 | | innodb_page_size | 16384 | | innodb_print_all_deadlocks | OFF | | innodb_purge_batch_size | 20 | | innodb_purge_threads | 1 | | innodb_random_read_ahead | OFF | | innodb_read_ahead | linear | | innodb_read_ahead_threshold | 56 | | innodb_read_io_threads | 8 | | innodb_recovery_stats | OFF | | innodb_recovery_update_relay_log | OFF | | innodb_replication_delay | 0 | | innodb_rollback_on_timeout | OFF | | innodb_rollback_segments | 128 | | innodb_show_locks_held | 10 | | innodb_show_verbose_locks | 0 | | innodb_spin_wait_delay | 6 | | innodb_stats_auto_update | 1 | | innodb_stats_method | nulls_equal | | innodb_stats_on_metadata | ON | | innodb_stats_sample_pages | 8 | | innodb_stats_update_need_lock | 1 | | innodb_strict_mode | OFF | | innodb_support_xa | ON | | innodb_sync_spin_loops | 30 | | innodb_table_locks | ON | | innodb_thread_concurrency | 8 | | innodb_thread_concurrency_timer_based | OFF | | innodb_thread_sleep_delay | 10000 | | innodb_track_changed_pages | OFF | | innodb_use_atomic_writes | OFF | | innodb_use_global_flush_log_at_trx_commit | ON | | innodb_use_native_aio | ON | | innodb_use_sys_malloc | ON | | innodb_use_sys_stats_table | OFF | | innodb_version | 5.5.62-38.14 | | innodb_write_io_threads | 8 | +-------------------------------------------+------------------------+

and Local Maria Db Server

+---------------------------------------------+------------------------+ | 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 | 134217728 | | 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 | 268435456 | | 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 | OFF | | 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:12M:autoextend | | innodb_data_home_dir | | | 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 | ON | | innodb_encrypt_log | OFF | | innodb_encrypt_tables | OFF | | innodb_encrypt_temporary_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_format | | | 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 | O_DIRECT | | 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 | 400 | | innodb_io_capacity_max | 2000 | | innodb_large_prefix | | | innodb_lock_schedule_algorithm | fcfs | | innodb_lock_wait_timeout | 50 | | innodb_locks_unsafe_for_binlog | OFF | | innodb_log_buffer_size | 8388608 | | 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 | ./ | | innodb_log_optimize_ddl | ON | | 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 | 400 | | 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.22 | | innodb_write_io_threads | 4 | +---------------------------------------------+------------------------+

Well, a lot of differences so I started worrying if I could fix the issue quickly or not so I made a more detail check for InnoDb Restrict Mode setting on my local MariaDB db server according to recommendation from MariaDb Blog

| innodb_strict_mode | ON

Solution

I tried to disable the InnoDb Strict Mode and run the mysql import command again

MariaDB [(none)]> set global innodb_strict_mode=OFF;
Code language: JavaScript (javascript)
mysql -hmysql -uroot -p localdb < clean_magento_db.sql
Code language: CSS (css)

It run without any error and imported all 350 tables into the local database successfully.

Database changed MariaDB [localdb]> show tables; +---------------------------------------------+ | Tables_in_localdb | +---------------------------------------------+ | admin_assert | | admin_role | | admin_rule | | admin_user | | adminnotification_inbox | | api2_acl_attribute | | api2_acl_role | | api2_acl_rule | | api2_acl_user | | api_assert | | api_role | | api_rule | | api_session | | api_user | | aw_blog | | aw_blog_cat | | aw_blog_cat_store | | aw_blog_comment | | aw_blog_post_cat | | aw_blog_store | | aw_blog_tags | | captcha_log | | catalog_category_anc_categs_index_idx | | catalog_category_anc_categs_index_tmp | | catalog_category_anc_products_index_idx | | catalog_category_anc_products_index_tmp | | catalog_category_entity | | catalog_category_entity_datetime | | catalog_category_entity_decimal | | catalog_category_entity_int | | catalog_category_entity_text | | catalog_category_entity_varchar | | catalog_category_flat_store_1 | | catalog_category_flat_store_2 | | catalog_category_flat_store_3 | | catalog_category_product | | catalog_category_product_index | | catalog_category_product_index_enbl_idx | | catalog_category_product_index_enbl_tmp | | catalog_category_product_index_idx | | catalog_category_product_index_tmp | | catalog_compare_item | | catalog_eav_attribute | | catalog_product_bundle_option | | catalog_product_bundle_option_value | | catalog_product_bundle_price_index | | catalog_product_bundle_selection | | catalog_product_bundle_selection_price | | catalog_product_bundle_stock_index | | catalog_product_enabled_index | | catalog_product_entity | | catalog_product_entity_datetime | | catalog_product_entity_decimal | | catalog_product_entity_gallery | | catalog_product_entity_group_price | | catalog_product_entity_int | | catalog_product_entity_media_gallery | | catalog_product_entity_media_gallery_value | | catalog_product_entity_text | | catalog_product_entity_tier_price | | catalog_product_entity_varchar | | catalog_product_flat_1 | | catalog_product_index_eav | | catalog_product_index_eav_decimal | | catalog_product_index_eav_decimal_idx | | catalog_product_index_eav_decimal_tmp | | catalog_product_index_eav_idx | | catalog_product_index_eav_tmp | | catalog_product_index_group_price | | catalog_product_index_price | | catalog_product_index_price_bundle_idx | | catalog_product_index_price_bundle_opt_idx | | catalog_product_index_price_bundle_opt_tmp | | catalog_product_index_price_bundle_sel_idx | | catalog_product_index_price_bundle_sel_tmp | | catalog_product_index_price_bundle_tmp | | catalog_product_index_price_cfg_opt_agr_idx | | catalog_product_index_price_cfg_opt_agr_tmp | | catalog_product_index_price_cfg_opt_idx | | catalog_product_index_price_cfg_opt_tmp | | catalog_product_index_price_downlod_idx | | catalog_product_index_price_downlod_tmp | | catalog_product_index_price_final_idx | | catalog_product_index_price_final_tmp | | catalog_product_index_price_idx | | catalog_product_index_price_opt_agr_idx | | catalog_product_index_price_opt_agr_tmp | | catalog_product_index_price_opt_idx | | catalog_product_index_price_opt_tmp | | catalog_product_index_price_tmp | | catalog_product_index_tier_price | | catalog_product_index_website | | catalog_product_link | | catalog_product_link_attribute | | catalog_product_link_attribute_decimal | | catalog_product_link_attribute_int | | catalog_product_link_attribute_varchar | | catalog_product_link_type | | catalog_product_option | | catalog_product_option_price | | catalog_product_option_title | | catalog_product_option_type_price | | catalog_product_option_type_title | | catalog_product_option_type_value | | catalog_product_relation | | catalog_product_super_attribute | | catalog_product_super_attribute_label | | catalog_product_super_attribute_pricing | | catalog_product_super_link | | catalog_product_website | | cataloginventory_stock | | cataloginventory_stock_item | | cataloginventory_stock_status | | cataloginventory_stock_status_idx | | cataloginventory_stock_status_tmp | | catalogrule | | catalogrule_affected_product | | catalogrule_customer_group | | catalogrule_group_website | | catalogrule_product | | catalogrule_product_price | | catalogrule_website | | catalogsearch_fulltext | | catalogsearch_query | | catalogsearch_result | | checkout_agreement | | checkout_agreement_store | | cms_block | | cms_block_store | | cms_page | | cms_page_store | | core_cache | | core_cache_option | | core_cache_tag | | core_config_data | | core_email_template | | core_flag | | core_layout_link | | core_layout_update | | core_resource | | core_session | | core_store | | core_store_group | | core_translate | | core_url_rewrite | | core_variable | | core_variable_value | | core_website | | coupon_aggregated | | coupon_aggregated_order | | coupon_aggregated_updated | | cron_schedule | | customer_address_entity | | customer_address_entity_datetime | | customer_address_entity_decimal | | customer_address_entity_int | | customer_address_entity_text | | customer_address_entity_varchar | | customer_eav_attribute | | customer_eav_attribute_website | | customer_entity | | customer_entity_datetime | | customer_entity_decimal | | customer_entity_int | | customer_entity_text | | customer_entity_varchar | | customer_form_attribute | | customer_group | | dataflow_batch | | dataflow_batch_export | | dataflow_batch_import | | dataflow_import_data | | dataflow_profile | | dataflow_profile_history | | dataflow_session | | design_change | | directory_country | | directory_country_format | | directory_country_region | | directory_country_region_name | | directory_currency_rate | | downloadable_link | | downloadable_link_price | | downloadable_link_purchased | | downloadable_link_purchased_item | | downloadable_link_title | | downloadable_sample | | downloadable_sample_title | | eav_attribute | | eav_attribute_group | | eav_attribute_label | | eav_attribute_option | | eav_attribute_option_value | | eav_attribute_set | | eav_entity | | eav_entity_attribute | | eav_entity_datetime | | eav_entity_decimal | | eav_entity_int | | eav_entity_store | | eav_entity_text | | eav_entity_type | | eav_entity_varchar | | eav_form_element | | eav_form_fieldset | | eav_form_fieldset_label | | eav_form_type | | eav_form_type_entity | | gift_message | | googlecheckout_notification | | importexport_importdata | | index_event | | index_process | | index_process_event | | log_customer | | log_quote | | log_summary | | log_summary_type | | log_url | | log_url_info | | log_visitor | | log_visitor_info | | log_visitor_online | | newsletter_problem | | newsletter_queue | | newsletter_queue_link | | newsletter_queue_store_link | | newsletter_subscriber | | newsletter_template | | oauth_consumer | | oauth_nonce | | oauth_token | | paypal_cert | | paypal_payment_transaction | | paypal_settlement_report | | paypal_settlement_report_row | | persistent_session | | poll | | poll_answer | | poll_store | | poll_vote | | product_alert_price | | product_alert_stock | | rating | | rating_entity | | rating_option | | rating_option_vote | | rating_option_vote_aggregated | | rating_store | | rating_title | | report_compared_product_index | | report_event | | report_event_types | | report_viewed_product_aggregated_daily | | report_viewed_product_aggregated_monthly | | report_viewed_product_aggregated_yearly | | report_viewed_product_index | | review | | review_detail | | review_entity | | review_entity_summary | | review_status | | review_store | | sales_bestsellers_aggregated_daily | | sales_bestsellers_aggregated_monthly | | sales_bestsellers_aggregated_yearly | | sales_billing_agreement | | sales_billing_agreement_order | | sales_flat_creditmemo | | sales_flat_creditmemo_comment | | sales_flat_creditmemo_grid | | sales_flat_creditmemo_item | | sales_flat_invoice | | sales_flat_invoice_comment | | sales_flat_invoice_grid | | sales_flat_invoice_item | | sales_flat_order | | sales_flat_order_address | | sales_flat_order_grid | | sales_flat_order_item | | sales_flat_order_payment | | sales_flat_order_status_history | | sales_flat_quote | | sales_flat_quote_address | | sales_flat_quote_address_item | | sales_flat_quote_item | | sales_flat_quote_item_option | | sales_flat_quote_payment | | sales_flat_quote_shipping_rate | | sales_flat_shipment | | sales_flat_shipment_comment | | sales_flat_shipment_grid | | sales_flat_shipment_item | | sales_flat_shipment_track | | sales_invoiced_aggregated | | sales_invoiced_aggregated_order | | sales_order_aggregated_created | | sales_order_aggregated_updated | | sales_order_status | | sales_order_status_label | | sales_order_status_state | | sales_order_tax | | sales_order_tax_item | | sales_payment_transaction | | sales_recurring_profile | | sales_recurring_profile_order | | sales_refunded_aggregated | | sales_refunded_aggregated_order | | sales_shipping_aggregated | | sales_shipping_aggregated_order | | salesrule | | salesrule_coupon | | salesrule_coupon_usage | | salesrule_customer | | salesrule_customer_group | | salesrule_label | | salesrule_product_attribute | | salesrule_website | | sendfriend_log | | shipping_tablerate | | sitemap | | tag | | tag_properties | | tag_relation | | tag_summary | | tax_calculation | | tax_calculation_rate | | tax_calculation_rate_title | | tax_calculation_rule | | tax_class | | tax_order_aggregated_created | | tax_order_aggregated_updated | | turnkeye_brand | | turnkeye_brand_store | | turnkeye_quotefeature_method | | turnkeye_testimonial | | weee_discount | | weee_tax | | widget | | widget_instance | | widget_instance_page | | widget_instance_page_layout | | wishlist | | wishlist_item | | wishlist_item_option | | xmlconnect_application | | xmlconnect_config_data | | xmlconnect_history | | xmlconnect_notification_template | | xmlconnect_queue | +---------------------------------------------+ 350 rows in set (0.001 sec)
Code language: JavaScript (javascript)

Conclusion

Migrating data between different database servers is a task that a developer has to deal with frequently. Meeting an error on that process is normal and we always have that issues. Keeping calm or visiting frequently our technical blogs may save you a lot of time from fixing a problem. Happy Coding!!!

Related Articles

Share on facebook
Share on twitter
Share on linkedin
Share on pinterest
Share on print
Share on email