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.sqlCode 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 Production | Local 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.sqlCode 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!!!
