MySQl Error Code: 1273. Unknown collation: 'utf8mb4_0900_ai_ci'

Question

I realized this question has been asked many times but all of the available solutions I've tried are not working. I'm getting an error code:

CREATE TABLE `payment_methods` (   `payment_method_id` tinyint(4) NOT NULL AUTO_INCREMENT,   `name` varchar(50) NOT NULL,   PRIMARY KEY (`payment_method_id`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8mb4_general_ci  Error Code: 1253. COLLATION 'utf8mb4_general_ci' is not valid for CHARACTER SET 'utf8'

For some reason that does not stop the code but later I'm get another error which actually does stop the code

CREATE TABLE `payment_methods` (   `payment_method_id` tinyint(4) NOT NULL AUTO_INCREMENT,   `name` varchar(50) NOT NULL,   PRIMARY KEY (`payment_method_id`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8mb4_general_ci  Error Code: 1253. COLLATION 'utf8mb4_general_ci' is not valid for CHARACTER SET 'utf8'  

I was recommend to try the solutions here here

But I am not using utf8mb4_unicode_520_ci so that is not an option. In this article

here

it recommends changing

replace “utf8mb4_0900_ai_ci” with “utf8mb4_general_ci” and “CHARSET=utf8mb4” with “CHARSET=utf8“.

and

ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

with:

ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

But that results in another error, namely,

'utf8mb4_general_ci' is not valid for CHARACTER SET 'utf8'.

Another solution is to change the settings as seen in this photo

here

But I cannot find those settings.

Another solution recommended changing the configurations in the my.cnf file but no such file exists in the local/var/mysql folder.

Another solution is to make sure

MySQL server is the same version as the destination

but I don't know what that means.

#######UPDATE

I forgot to mention that I got this error because I was trying to execute a code from this tutorial https://www.youtube.com/watch?v=7S_tz1z_5bA&t=930s He doesn't get the error but I do. Here is a snippet of the relevant code.

SET NAMES utf8 ;
SET character_set_client = utf8mb4 ;

CREATE TABLE `payment_methods` (
  `payment_method_id` tinyint(4) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  PRIMARY KEY (`payment_method_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

I don't understand why that is wrong. I have the right charset matched to the correct collate.

############ UPDATE

Shadow raised some good points in the comments. I downloaded the workbench version 8.0.22 at first, but then I decided to download the exact version that the youtube tutorial uses which is 8.0.15, however, that didn't change anything. In the file: /usr/local/mysql/readme it says that I'm using 5.7. I still can't find the my.cnf files that others have talked about. I need to find out what collations are installed in my database and I can't figure out how to do that. I know it's very long but here is the tree that is produced of my directory, if that helps.

├── COPYING
├── README
├── bin
│   ├── innochecksum
│   ├── lz4_decompress
│   ├── my_print_defaults
│   ├── myisam_ftdump
│   ├── myisamchk
│   ├── myisamlog
│   ├── myisampack
│   ├── mysql
│   ├── mysql_client_test_embedded
│   ├── mysql_config
│   ├── mysql_config_editor
│   ├── mysql_embedded
│   ├── mysql_install_db
│   ├── mysql_plugin
│   ├── mysql_secure_installation
│   ├── mysql_ssl_rsa_setup
│   ├── mysql_tzinfo_to_sql
│   ├── mysql_upgrade
│   ├── mysqladmin
│   ├── mysqlbinlog
│   ├── mysqlcheck
│   ├── mysqld
│   ├── mysqld-debug
│   ├── mysqld_multi
│   ├── mysqld_safe
│   ├── mysqldump
│   ├── mysqldumpslow
│   ├── mysqlimport
│   ├── mysqlpump
│   ├── mysqlshow
│   ├── mysqlslap
│   ├── mysqltest_embedded
│   ├── mysqlxtest
│   ├── perror
│   ├── replace
│   ├── resolve_stack_dump
│   ├── resolveip
│   └── zlib_decompress
├── data
│   ├── Admins-MacBook-Pro-4.local.err
│   ├── Admins-MacBook-Pro-4.local.pid
│   ├── auto.cnf
│   ├── ib_buffer_pool
│   ├── ib_logfile0
│   ├── ib_logfile1
│   ├── ibdata1
│   ├── inference_engine
│   │   ├── archives.frm
│   │   ├── archives.ibd
│   │   ├── auth_group.frm
│   │   ├── auth_group.ibd
│   │   ├── auth_group_permissions.frm
│   │   ├── auth_group_permissions.ibd
│   │   ├── auth_permission.frm
│   │   ├── auth_permission.ibd
│   │   ├── auth_user.frm
│   │   ├── auth_user.ibd
│   │   ├── auth_user_groups.frm
│   │   ├── auth_user_groups.ibd
│   │   ├── auth_user_user_permissions.frm
│   │   ├── auth_user_user_permissions.ibd
│   │   ├── db.opt
│   │   ├── define3.frm
│   │   ├── define3.ibd
│   │   ├── django_admin_log.frm
│   │   ├── django_admin_log.ibd
│   │   ├── django_content_type.frm
│   │   ├── django_content_type.ibd
│   │   ├── django_migrations.frm
│   │   ├── django_migrations.ibd
│   │   ├── django_session.frm
│   │   ├── django_session.ibd
│   │   ├── input.frm
│   │   ├── input.ibd
│   │   ├── output.frm
│   │   └── output.ibd
│   ├── mysql
│   │   ├── columns_priv.MYD
│   │   ├── columns_priv.MYI
│   │   ├── columns_priv.frm
│   │   ├── db.MYD
│   │   ├── db.MYI
│   │   ├── db.frm
│   │   ├── db.opt
│   │   ├── engine_cost.frm
│   │   ├── engine_cost.ibd
│   │   ├── event.MYD
│   │   ├── event.MYI
│   │   ├── event.frm
│   │   ├── func.MYD
│   │   ├── func.MYI
│   │   ├── func.frm
│   │   ├── general_log.CSM
│   │   ├── general_log.CSV
│   │   ├── general_log.frm
│   │   ├── gtid_executed.frm
│   │   ├── gtid_executed.ibd
│   │   ├── help_category.frm
│   │   ├── help_category.ibd
│   │   ├── help_keyword.frm
│   │   ├── help_keyword.ibd
│   │   ├── help_relation.frm
│   │   ├── help_relation.ibd
│   │   ├── help_topic.frm
│   │   ├── help_topic.ibd
│   │   ├── innodb_index_stats.frm
│   │   ├── innodb_index_stats.ibd
│   │   ├── innodb_table_stats.frm
│   │   ├── innodb_table_stats.ibd
│   │   ├── ndb_binlog_index.MYD
│   │   ├── ndb_binlog_index.MYI
│   │   ├── ndb_binlog_index.frm
│   │   ├── plugin.frm
│   │   ├── plugin.ibd
│   │   ├── proc.MYD
│   │   ├── proc.MYI
│   │   ├── proc.frm
│   │   ├── procs_priv.MYD
│   │   ├── procs_priv.MYI
│   │   ├── procs_priv.frm
│   │   ├── proxies_priv.MYD
│   │   ├── proxies_priv.MYI
│   │   ├── proxies_priv.frm
│   │   ├── server_cost.frm
│   │   ├── server_cost.ibd
│   │   ├── servers.frm
│   │   ├── servers.ibd
│   │   ├── slave_master_info.frm
│   │   ├── slave_master_info.ibd
│   │   ├── slave_relay_log_info.frm
│   │   ├── slave_relay_log_info.ibd
│   │   ├── slave_worker_info.frm
│   │   ├── slave_worker_info.ibd
│   │   ├── slow_log.CSM
│   │   ├── slow_log.CSV
│   │   ├── slow_log.frm
│   │   ├── tables_priv.MYD
│   │   ├── tables_priv.MYI
│   │   ├── tables_priv.frm
│   │   ├── time_zone.frm
│   │   ├── time_zone.ibd
│   │   ├── time_zone_leap_second.frm
│   │   ├── time_zone_leap_second.ibd
│   │   ├── time_zone_name.frm
│   │   ├── time_zone_name.ibd
│   │   ├── time_zone_transition.frm
│   │   ├── time_zone_transition.ibd
│   │   ├── time_zone_transition_type.frm
│   │   ├── time_zone_transition_type.ibd
│   │   ├── user.MYD
│   │   ├── user.MYI
│   │   └── user.frm
│   ├── mysqld.local.err
│   ├── performance_schema
│   │   ├── accounts.frm
│   │   ├── cond_instances.frm
│   │   ├── db.opt
│   │   ├── events_stages_current.frm
│   │   ├── events_stages_history.frm
│   │   ├── events_stages_history_long.frm
│   │   ├── events_stages_summary_by_account_by_event_name.frm
│   │   ├── events_stages_summary_by_host_by_event_name.frm
│   │   ├── events_stages_summary_by_thread_by_event_name.frm
│   │   ├── events_stages_summary_by_user_by_event_name.frm
│   │   ├── events_stages_summary_global_by_event_name.frm
│   │   ├── events_statements_current.frm
│   │   ├── events_statements_history.frm
│   │   ├── events_statements_history_long.frm
│   │   ├── events_statements_summary_by_account_by_event_name.frm
│   │   ├── events_statements_summary_by_digest.frm
│   │   ├── events_statements_summary_by_host_by_event_name.frm
│   │   ├── events_statements_summary_by_program.frm
│   │   ├── events_statements_summary_by_thread_by_event_name.frm
│   │   ├── events_statements_summary_by_user_by_event_name.frm
│   │   ├── events_statements_summary_global_by_event_name.frm
│   │   ├── events_transactions_current.frm
│   │   ├── events_transactions_history.frm
│   │   ├── events_transactions_history_long.frm
│   │   ├── events_transactions_summary_by_account_by_event_name.frm
│   │   ├── events_transactions_summary_by_host_by_event_name.frm
│   │   ├── events_transactions_summary_by_thread_by_event_name.frm
│   │   ├── events_transactions_summary_by_user_by_event_name.frm
│   │   ├── events_transactions_summary_global_by_event_name.frm
│   │   ├── events_waits_current.frm
│   │   ├── events_waits_history.frm
│   │   ├── events_waits_history_long.frm
│   │   ├── events_waits_summary_by_account_by_event_name.frm
│   │   ├── events_waits_summary_by_host_by_event_name.frm
│   │   ├── events_waits_summary_by_instance.frm
│   │   ├── events_waits_summary_by_thread_by_event_name.frm
│   │   ├── events_waits_summary_by_user_by_event_name.frm
│   │   ├── events_waits_summary_global_by_event_name.frm
│   │   ├── file_instances.frm
│   │   ├── file_summary_by_event_name.frm
│   │   ├── file_summary_by_instance.frm
│   │   ├── global_status.frm
│   │   ├── global_variables.frm
│   │   ├── host_cache.frm
│   │   ├── hosts.frm
│   │   ├── memory_summary_by_account_by_event_name.frm
│   │   ├── memory_summary_by_host_by_event_name.frm
│   │   ├── memory_summary_by_thread_by_event_name.frm
│   │   ├── memory_summary_by_user_by_event_name.frm
│   │   ├── memory_summary_global_by_event_name.frm
│   │   ├── metadata_locks.frm
│   │   ├── mutex_instances.frm
│   │   ├── objects_summary_global_by_type.frm
│   │   ├── performance_timers.frm
│   │   ├── prepared_statements_instances.frm
│   │   ├── replication_applier_configuration.frm
│   │   ├── replication_applier_status.frm
│   │   ├── replication_applier_status_by_coordinator.frm
│   │   ├── replication_applier_status_by_worker.frm
│   │   ├── replication_connection_configuration.frm
│   │   ├── replication_connection_status.frm
│   │   ├── replication_group_member_stats.frm
│   │   ├── replication_group_members.frm
│   │   ├── rwlock_instances.frm
│   │   ├── session_account_connect_attrs.frm
│   │   ├── session_connect_attrs.frm
│   │   ├── session_status.frm
│   │   ├── session_variables.frm
│   │   ├── setup_actors.frm
│   │   ├── setup_consumers.frm
│   │   ├── setup_instruments.frm
│   │   ├── setup_objects.frm
│   │   ├── setup_timers.frm
│   │   ├── socket_instances.frm
│   │   ├── socket_summary_by_event_name.frm
│   │   ├── socket_summary_by_instance.frm
│   │   ├── status_by_account.frm
│   │   ├── status_by_host.frm
│   │   ├── status_by_thread.frm
│   │   ├── status_by_user.frm
│   │   ├── table_handles.frm
│   │   ├── table_io_waits_summary_by_index_usage.frm
│   │   ├── table_io_waits_summary_by_table.frm
│   │   ├── table_lock_waits_summary_by_table.frm
│   │   ├── threads.frm
│   │   ├── user_variables_by_thread.frm
│   │   ├── users.frm
│   │   └── variables_by_thread.frm
│   ├── sql_hr [error opening dir]
│   ├── sql_invoicing [error opening dir]
│   ├── store [error opening dir]
│   └── sys
│       ├── db.opt
│       ├── host_summary.frm
│       ├── host_summary_by_file_io.frm
│       ├── host_summary_by_file_io_type.frm
│       ├── host_summary_by_stages.frm
│       ├── host_summary_by_statement_latency.frm
│       ├── host_summary_by_statement_type.frm
│       ├── innodb_buffer_stats_by_schema.frm
│       ├── innodb_buffer_stats_by_table.frm
│       ├── innodb_lock_waits.frm
│       ├── io_by_thread_by_latency.frm
│       ├── io_global_by_file_by_bytes.frm
│       ├── io_global_by_file_by_latency.frm
│       ├── io_global_by_wait_by_bytes.frm
│       ├── io_global_by_wait_by_latency.frm
│       ├── latest_file_io.frm
│       ├── memory_by_host_by_current_bytes.frm
│       ├── memory_by_thread_by_current_bytes.frm
│       ├── memory_by_user_by_current_bytes.frm
│       ├── memory_global_by_current_bytes.frm
│       ├── memory_global_total.frm
│       ├── metrics.frm
│       ├── processlist.frm
│       ├── ps_check_lost_instrumentation.frm
│       ├── schema_auto_increment_columns.frm
│       ├── schema_index_statistics.frm
│       ├── schema_object_overview.frm
│       ├── schema_redundant_indexes.frm
│       ├── schema_table_lock_waits.frm
│       ├── schema_table_statistics.frm
│       ├── schema_table_statistics_with_buffer.frm
│       ├── schema_tables_with_full_table_scans.frm
│       ├── schema_unused_indexes.frm
│       ├── session.frm
│       ├── session_ssl_status.frm
│       ├── statement_analysis.frm
│       ├── statements_with_errors_or_warnings.frm
│       ├── statements_with_full_table_scans.frm
│       ├── statements_with_runtimes_in_95th_percentile.frm
│       ├── statements_with_sorting.frm
│       ├── statements_with_temp_tables.frm
│       ├── sys_config.TRG
│       ├── sys_config.frm
│       ├── sys_config.ibd
│       ├── sys_config_insert_set_user.TRN
│       ├── sys_config_update_set_user.TRN
│       ├── user_summary.frm
│       ├── user_summary_by_file_io.frm
│       ├── user_summary_by_file_io_type.frm
│       ├── user_summary_by_stages.frm
│       ├── user_summary_by_statement_latency.frm
│       ├── user_summary_by_statement_type.frm
│       ├── version.frm
│       ├── wait_classes_global_by_avg_latency.frm
│       ├── wait_classes_global_by_latency.frm
│       ├── waits_by_host_by_latency.frm
│       ├── waits_by_user_by_latency.frm
│       ├── waits_global_by_latency.frm
│       ├── x@0024host_summary.frm
│       ├── x@0024host_summary_by_file_io.frm
│       ├── x@0024host_summary_by_file_io_type.frm
│       ├── x@0024host_summary_by_stages.frm
│       ├── x@0024host_summary_by_statement_latency.frm
│       ├── x@0024host_summary_by_statement_type.frm
│       ├── x@0024innodb_buffer_stats_by_schema.frm
│       ├── x@0024innodb_buffer_stats_by_table.frm
│       ├── x@0024innodb_lock_waits.frm
│       ├── x@0024io_by_thread_by_latency.frm
│       ├── x@0024io_global_by_file_by_bytes.frm
│       ├── x@0024io_global_by_file_by_latency.frm
│       ├── x@0024io_global_by_wait_by_bytes.frm
│       ├── x@0024io_global_by_wait_by_latency.frm
│       ├── x@0024latest_file_io.frm
│       ├── x@0024memory_by_host_by_current_bytes.frm
│       ├── x@0024memory_by_thread_by_current_bytes.frm
│       ├── x@0024memory_by_user_by_current_bytes.frm
│       ├── x@0024memory_global_by_current_bytes.frm
│       ├── x@0024memory_global_total.frm
│       ├── x@0024processlist.frm
│       ├── x@0024ps_digest_95th_percentile_by_avg_us.frm
│       ├── x@0024ps_digest_avg_latency_distribution.frm
│       ├── x@0024ps_schema_table_statistics_io.frm
│       ├── x@0024schema_flattened_keys.frm
│       ├── x@0024schema_index_statistics.frm
│       ├── x@0024schema_table_lock_waits.frm
│       ├── x@0024schema_table_statistics.frm
│       ├── x@0024schema_table_statistics_with_buffer.frm
│       ├── x@0024schema_tables_with_full_table_scans.frm
│       ├── x@0024session.frm
│       ├── x@0024statement_analysis.frm
│       ├── x@0024statements_with_errors_or_warnings.frm
│       ├── x@0024statements_with_full_table_scans.frm
│       ├── x@0024statements_with_runtimes_in_95th_percentile.frm
│       ├── x@0024statements_with_sorting.frm
│       ├── x@0024statements_with_temp_tables.frm
│       ├── x@0024user_summary.frm
│       ├── x@0024user_summary_by_file_io.frm
│       ├── x@0024user_summary_by_file_io_type.frm
│       ├── x@0024user_summary_by_stages.frm
│       ├── x@0024user_summary_by_statement_latency.frm
│       ├── x@0024user_summary_by_statement_type.frm
│       ├── x@0024wait_classes_global_by_avg_latency.frm
│       ├── x@0024wait_classes_global_by_latency.frm
│       ├── x@0024waits_by_host_by_latency.frm
│       ├── x@0024waits_by_user_by_latency.frm
│       └── x@0024waits_global_by_latency.frm
├── docs
│   ├── ChangeLog
│   ├── INFO_BIN
│   └── INFO_SRC
├── include
│   ├── big_endian.h
│   ├── binary_log_types.h
│   ├── byte_order_generic.h
│   ├── byte_order_generic_x86.h
│   ├── decimal.h
│   ├── errmsg.h
│   ├── keycache.h
│   ├── little_endian.h
│   ├── m_ctype.h
│   ├── m_string.h
│   ├── my_alloc.h
│   ├── my_byteorder.h
│   ├── my_command.h
│   ├── my_compiler.h
│   ├── my_config.h
│   ├── my_dbug.h
│   ├── my_dir.h
│   ├── my_getopt.h
│   ├── my_global.h
│   ├── my_list.h
│   ├── my_sys.h
│   ├── my_thread.h
│   ├── my_thread_local.h
│   ├── my_xml.h
│   ├── mysql
│   │   ├── client_authentication.h
│   │   ├── client_plugin.h
│   │   ├── client_plugin.h.pp
│   │   ├── com_data.h
│   │   ├── get_password.h
│   │   ├── group_replication_priv.h
│   │   ├── innodb_priv.h
│   │   ├── mysql_lex_string.h
│   │   ├── plugin.h
│   │   ├── plugin_audit.h
│   │   ├── plugin_audit.h.pp
│   │   ├── plugin_auth.h
│   │   ├── plugin_auth.h.pp
│   │   ├── plugin_auth_common.h
│   │   ├── plugin_ftparser.h
│   │   ├── plugin_ftparser.h.pp
│   │   ├── plugin_group_replication.h
│   │   ├── plugin_keyring.h
│   │   ├── plugin_keyring.h.pp
│   │   ├── plugin_trace.h
│   │   ├── plugin_validate_password.h
│   │   ├── psi
│   │   │   ├── mysql_file.h
│   │   │   ├── mysql_idle.h
│   │   │   ├── mysql_mdl.h
│   │   │   ├── mysql_memory.h
│   │   │   ├── mysql_ps.h
│   │   │   ├── mysql_socket.h
│   │   │   ├── mysql_sp.h
│   │   │   ├── mysql_stage.h
│   │   │   ├── mysql_statement.h
│   │   │   ├── mysql_table.h
│   │   │   ├── mysql_thread.h
│   │   │   ├── mysql_transaction.h
│   │   │   ├── psi.h
│   │   │   ├── psi_base.h
│   │   │   └── psi_memory.h
│   │   ├── service_command.h
│   │   ├── service_locking.h
│   │   ├── service_my_plugin_log.h
│   │   ├── service_my_snprintf.h
│   │   ├── service_mysql_alloc.h
│   │   ├── service_mysql_keyring.h
│   │   ├── service_mysql_password_policy.h
│   │   ├── service_mysql_string.h
│   │   ├── service_parser.h
│   │   ├── service_rpl_transaction_ctx.h
│   │   ├── service_rpl_transaction_write_set.h
│   │   ├── service_rules_table.h
│   │   ├── service_security_context.h
│   │   ├── service_srv_session.h
│   │   ├── service_srv_session_info.h
│   │   ├── service_ssl_wrapper.h
│   │   ├── service_thd_alloc.h
│   │   ├── service_thd_engine_lock.h
│   │   ├── service_thd_wait.h
│   │   ├── service_thread_scheduler.h
│   │   ├── services.h
│   │   ├── services.h.pp
│   │   ├── thread_pool_priv.h
│   │   └── thread_type.h
│   ├── mysql.h
│   ├── mysql_com.h
│   ├── mysql_com_server.h
│   ├── mysql_embed.h
│   ├── mysql_time.h
│   ├── mysql_version.h
│   ├── mysqld_ername.h
│   ├── mysqld_error.h
│   ├── plugin.h
│   ├── plugin_audit.h
│   ├── plugin_ftparser.h
│   ├── plugin_group_replication.h
│   ├── plugin_keyring.h
│   ├── plugin_validate_password.h
│   ├── sql_common.h
│   ├── sql_state.h
│   ├── sslopt-case.h
│   ├── sslopt-longopts.h
│   ├── sslopt-vars.h
│   ├── thr_cond.h
│   ├── thr_mutex.h
│   ├── thr_rwlock.h
│   └── typelib.h
├── lib
│   ├── libmysqlclient.20.dylib
│   ├── libmysqlclient.a
│   ├── libmysqlclient.dylib -> libmysqlclient.20.dylib
│   ├── libmysqld-debug.a
│   ├── libmysqld.a
│   ├── libmysqlservices.a
│   ├── mecab
│   │   ├── dic
│   │   │   ├── ipadic_euc-jp
│   │   │   │   ├── char.bin
│   │   │   │   ├── dicrc
│   │   │   │   ├── left-id.def
│   │   │   │   ├── matrix.bin
│   │   │   │   ├── pos-id.def
│   │   │   │   ├── rewrite.def
│   │   │   │   ├── right-id.def
│   │   │   │   ├── sys.dic
│   │   │   │   └── unk.dic
│   │   │   ├── ipadic_sjis
│   │   │   │   ├── char.bin
│   │   │   │   ├── dicrc
│   │   │   │   ├── left-id.def
│   │   │   │   ├── matrix.bin
│   │   │   │   ├── pos-id.def
│   │   │   │   ├── rewrite.def
│   │   │   │   ├── right-id.def
│   │   │   │   ├── sys.dic
│   │   │   │   └── unk.dic
│   │   │   └── ipadic_utf-8
│   │   │       ├── char.bin
│   │   │       ├── dicrc
│   │   │       ├── left-id.def
│   │   │       ├── matrix.bin
│   │   │       ├── pos-id.def
│   │   │       ├── rewrite.def
│   │   │       ├── right-id.def
│   │   │       ├── sys.dic
│   │   │       └── unk.dic
│   │   └── etc
│   │       └── mecabrc
│   ├── pkgconfig
│   │   └── mysqlclient.pc
│   └── plugin
│       ├── adt_null.so
│       ├── connection_control.so
│       ├── debug
│       │   ├── adt_null.so
│       │   ├── connection_control.so
│       │   ├── group_replication.so
│       │   ├── ha_example.so
│       │   ├── innodb_engine.so
│       │   ├── keyring_file.so
│       │   ├── keyring_udf.so
│       │   ├── libmemcached.so
│       │   ├── libpluginmecab.so
│       │   ├── libtest_framework.so
│       │   ├── libtest_services.so
│       │   ├── libtest_services_threaded.so
│       │   ├── libtest_session_detach.so
│       │   ├── libtest_session_in_thd.so
│       │   ├── libtest_session_info.so
│       │   ├── libtest_sql_2_sessions.so
│       │   ├── libtest_sql_all_col_types.so
│       │   ├── libtest_sql_cmds_1.so
│       │   ├── libtest_sql_commit.so
│       │   ├── libtest_sql_complex.so
│       │   ├── libtest_sql_errors.so
│       │   ├── libtest_sql_lock.so
│       │   ├── libtest_sql_processlist.so
│       │   ├── libtest_sql_replication.so
│       │   ├── libtest_sql_shutdown.so
│       │   ├── libtest_sql_sqlmode.so
│       │   ├── libtest_sql_stored_procedures_functions.so
│       │   ├── libtest_sql_views_triggers.so
│       │   ├── libtest_x_sessions_deinit.so
│       │   ├── libtest_x_sessions_init.so
│       │   ├── locking_service.so
│       │   ├── mypluglib.so
│       │   ├── mysql_no_login.so
│       │   ├── mysqlx.so
│       │   ├── rewrite_example.so
│       │   ├── rewriter.so
│       │   ├── semisync_master.so
│       │   ├── semisync_slave.so
│       │   ├── test_security_context.so
│       │   ├── test_udf_services.so
│       │   ├── validate_password.so
│       │   └── version_token.so
│       ├── group_replication.so
│       ├── ha_example.so
│       ├── innodb_engine.so
│       ├── keyring_file.so
│       ├── keyring_udf.so
│       ├── libmemcached.so
│       ├── libpluginmecab.so
│       ├── libtest_framework.so
│       ├── libtest_services.so
│       ├── libtest_services_threaded.so
│       ├── libtest_session_detach.so
│       ├── libtest_session_in_thd.so
│       ├── libtest_session_info.so
│       ├── libtest_sql_2_sessions.so
│       ├── libtest_sql_all_col_types.so
│       ├── libtest_sql_cmds_1.so
│       ├── libtest_sql_commit.so
│       ├── libtest_sql_complex.so
│       ├── libtest_sql_errors.so
│       ├── libtest_sql_lock.so
│       ├── libtest_sql_processlist.so
│       ├── libtest_sql_replication.so
│       ├── libtest_sql_shutdown.so
│       ├── libtest_sql_sqlmode.so
│       ├── libtest_sql_stored_procedures_functions.so
│       ├── libtest_sql_views_triggers.so
│       ├── libtest_x_sessions_deinit.so
│       ├── libtest_x_sessions_init.so
│       ├── locking_service.so
│       ├── mypluglib.so
│       ├── mysql_no_login.so
│       ├── mysqlx.so
│       ├── rewrite_example.so
│       ├── rewriter.so
│       ├── semisync_master.so
│       ├── semisync_slave.so
│       ├── test_security_context.so
│       ├── test_udf_services.so
│       ├── validate_password.so
│       └── version_token.so
├── man
│   ├── man1
│   │   ├── comp_err.1
│   │   ├── innochecksum.1
│   │   ├── lz4_decompress.1
│   │   ├── my_print_defaults.1
│   │   ├── myisam_ftdump.1
│   │   ├── myisamchk.1
│   │   ├── myisamlog.1
│   │   ├── myisampack.1
│   │   ├── mysql-stress-test.pl.1
│   │   ├── mysql-test-run.pl.1
│   │   ├── mysql.1
│   │   ├── mysql.server.1
│   │   ├── mysql_client_test.1
│   │   ├── mysql_client_test_embedded.1
│   │   ├── mysql_config.1
│   │   ├── mysql_config_editor.1
│   │   ├── mysql_install_db.1
│   │   ├── mysql_plugin.1
│   │   ├── mysql_secure_installation.1
│   │   ├── mysql_ssl_rsa_setup.1
│   │   ├── mysql_tzinfo_to_sql.1
│   │   ├── mysql_upgrade.1
│   │   ├── mysqladmin.1
│   │   ├── mysqlbinlog.1
│   │   ├── mysqlcheck.1
│   │   ├── mysqld_multi.1
│   │   ├── mysqld_safe.1
│   │   ├── mysqldump.1
│   │   ├── mysqldumpslow.1
│   │   ├── mysqlimport.1
│   │   ├── mysqlman.1
│   │   ├── mysqlpump.1
│   │   ├── mysqlshow.1
│   │   ├── mysqlslap.1
│   │   ├── mysqltest_embedded.1
│   │   ├── perror.1
│   │   ├── replace.1
│   │   ├── resolve_stack_dump.1
│   │   ├── resolveip.1
│   │   └── zlib_decompress.1
│   └── man8
│       └── mysqld.8
├── share
│   ├── aclocal
│   │   └── mysql.m4
│   ├── bulgarian
│   │   └── errmsg.sys
│   ├── charsets
│   │   ├── Index.xml
│   │   ├── README
│   │   ├── armscii8.xml
│   │   ├── ascii.xml
│   │   ├── cp1250.xml
│   │   ├── cp1251.xml
│   │   ├── cp1256.xml
│   │   ├── cp1257.xml
│   │   ├── cp850.xml
│   │   ├── cp852.xml
│   │   ├── cp866.xml
│   │   ├── dec8.xml
│   │   ├── geostd8.xml
│   │   ├── greek.xml
│   │   ├── hebrew.xml
│   │   ├── hp8.xml
│   │   ├── keybcs2.xml
│   │   ├── koi8r.xml
│   │   ├── koi8u.xml
│   │   ├── latin1.xml
│   │   ├── latin2.xml
│   │   ├── latin5.xml
│   │   ├── latin7.xml
│   │   ├── macce.xml
│   │   ├── macroman.xml
│   │   └── swe7.xml
│   ├── czech
│   │   └── errmsg.sys
│   ├── danish
│   │   └── errmsg.sys
│   ├── dictionary.txt
│   ├── dutch
│   │   └── errmsg.sys
│   ├── english
│   │   └── errmsg.sys
│   ├── errmsg-utf8.txt
│   ├── estonian
│   │   └── errmsg.sys
│   ├── fill_help_tables.sql
│   ├── french
│   │   └── errmsg.sys

│   ├── innodb_memcached_config.sql
│   ├── install_rewriter.sql
│   ├── italian
│   │   └── errmsg.sys
│   ├── japanese
│   │   └── errmsg.sys
│   ├── korean
│   │   └── errmsg.sys
│   ├── mysql_security_commands.sql
│   ├── mysql_sys_schema.sql
│   ├── mysql_system_tables.sql
│   ├── mysql_system_tables_data.sql
│   ├── mysql_test_data_timezone.sql
│   ├── norwegian
│   │   └── errmsg.sys

└── support-files
    ├── magic
    ├── my-default.cnf
    ├── mysql-log-rotate
    ├── mysql.server
    └── mysqld_multi.server

Answer 1

I ran into this error trying to run a (locally-defined) script against a remote MySQL DB. The Drop-and-restore-with-different-charset specification of most answers seemed like a bad idea, and my local script didn't specify either the charset or collation.

So instead I ran SHOW CHARACTER SET WHERE charset LIKE '%utf%'; in the remote DB, and specified the charset & collation of my connection to match.

The charset and collation parameters can be specified using mysql-connector-python or the --default-character-set argument from the MySQL command line.

Answer 2

I also did a new installation, without success. The version of the Mosh's Youtube video is not available anymore.

I found a workaround though: the last table 'products' of sql_inventory was actually generated - despite the error code above. So I extracted the generation of tables hand by hand and let them run in their own SQL tab. That worked : )

e.g.:

DROP DATABASE IF EXISTS `sql_hr`;
CREATE DATABASE `sql_hr`;
USE `sql_hr`;


CREATE TABLE `offices` (
  `office_id` int(11) NOT NULL,
  `address` varchar(50) NOT NULL,
  `city` varchar(50) NOT NULL,
  `state` varchar(50) NOT NULL,
  PRIMARY KEY (`office_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO `offices` VALUES (1,'03 Reinke Trail','Cincinnati','OH');
INSERT INTO `offices` VALUES (2,'5507 Becker Terrace','New York City','NY');
INSERT INTO `offices` VALUES (3,'54 Northland Court','Richmond','VA');
INSERT INTO `offices` VALUES (4,'08 South Crossing','Cincinnati','OH');
INSERT INTO `offices` VALUES (5,'553 Maple Drive','Minneapolis','MN');
INSERT INTO `offices` VALUES (6,'23 North Plaza','Aurora','CO');
INSERT INTO `offices` VALUES (7,'9658 Wayridge Court','Boise','ID');
INSERT INTO `offices` VALUES (8,'9 Grayhawk Trail','New York City','NY');
INSERT INTO `offices` VALUES (9,'16862 Westend Hill','Knoxville','TN');
INSERT INTO `offices` VALUES (10,'4 Bluestem Parkway','Savannah','GA');

now the 'offices' table in 'sql_hr' is generated. If you want to generate the 'employees' table, delete the first to rows and start with use 'sql_hr'

--    DROP DATABASE IF EXISTS `sql_hr`;
--    CREATE DATABASE `sql_hr`;

USE 'sql_hr';

CREATE TABLE `employees` (
  `employee_id` int(11) NOT NULL,
  `first_name` varchar(50) NOT NULL,
  `last_name` varchar(50) NOT NULL,
  `job_title` varchar(50) NOT NULL,
  `salary` int(11) NOT NULL,
  `reports_to` int(11) DEFAULT NULL,
  `office_id` int(11) NOT NULL,
  PRIMARY KEY (`employee_id`),
  KEY `fk_employees_offices_idx` (`office_id`),
  KEY `fk_employees_employees_idx` (`reports_to`),
  CONSTRAINT `fk_employees_managers` FOREIGN KEY (`reports_to`) REFERENCES `employees` (`employee_id`),
  CONSTRAINT `fk_employees_offices` FOREIGN KEY (`office_id`) REFERENCES `offices` (`office_id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO `employees` VALUES (37270,'Yovonnda','Magrannell','Executive Secretary',63996,NULL,10);
INSERT INTO `employees` VALUES (33391,'D\'arcy','Nortunen','Account Executive',62871,37270,1);
INSERT INTO `employees` VALUES (37851,'Sayer','Matterson','Statistician III',98926,37270,1);
INSERT INTO `employees` VALUES (40448,'Mindy','Crissil','Staff Scientist',94860,37270,1);
INSERT INTO `employees` VALUES (56274,'Keriann','Alloisi','VP Marketing',110150,37270,1);
INSERT INTO `employees` VALUES (63196,'Alaster','Scutchin','Assistant Professor',32179,37270,2);
INSERT INTO `employees` VALUES (67009,'North','de Clerc','VP Product Management',114257,37270,2);
INSERT INTO `employees` VALUES (67370,'Elladine','Rising','Social Worker',96767,37270,2);
INSERT INTO `employees` VALUES (68249,'Nisse','Voysey','Financial Advisor',52832,37270,2);
INSERT INTO `employees` VALUES (72540,'Guthrey','Iacopetti','Office Assistant I',117690,37270,3);
INSERT INTO `employees` VALUES (72913,'Kass','Hefferan','Computer Systems Analyst IV',96401,37270,3);
INSERT INTO `employees` VALUES (75900,'Virge','Goodrum','Information Systems Manager',54578,37270,3);
INSERT INTO `employees` VALUES (76196,'Mirilla','Janowski','Cost Accountant',119241,37270,3);
INSERT INTO `employees` VALUES (80529,'Lynde','Aronson','Junior Executive',77182,37270,4);
INSERT INTO `employees` VALUES (80679,'Mildrid','Sokale','Geologist II',67987,37270,4);
INSERT INTO `employees` VALUES (84791,'Hazel','Tarbert','General Manager',93760,37270,4);
INSERT INTO `employees` VALUES (95213,'Cole','Kesterton','Pharmacist',86119,37270,4);
INSERT INTO `employees` VALUES (96513,'Theresa','Binney','Food Chemist',47354,37270,5);
INSERT INTO `employees` VALUES (98374,'Estrellita','Daleman','Staff Accountant IV',70187,37270,5);
INSERT INTO `employees` VALUES (115357,'Ivy','Fearey','Structural Engineer',92710,37270,5);

and so on... make sure to adapt the CREATE and USE in the beginning..

Answer 3

When I watched the tutorial I only downloaded the workbench, I did not bother to download mysql itself because I already had it, except that I had version 5.7. I decided to go back and download the exact script that the tutorial used here

https://dev.mysql.com/downloads/mysql/

which was 8.0.15. That solved the problem.

Answer 4

The following: 'utf8mb4_general_ci' is not valid for CHARACTER SET 'utf8'.

is happening because you are trying to use a utf8 MB4 collation for a utf8 MB3 charset (MySQL's UTF8 is not actually the full UTF8 charset, it's actually UTF8MB3 which is only a subset).

You are using MySQL 5.7 which is an old version and does not support a modern collation such as utf8mb4_0900_ai_ci.

As for utf8_general ci, the following is from MySQL's official documentation:

utf8_general_ci is a legacy collation that does not support expansions, contractions, or ignorable characters. It can make only one-to-one comparisons between characters.

https://dev.mysql.com/doc/refman/5.7/en/charset-unicode-sets.html#:~:text=utf8_general_ci%20is%20a%20legacy%20collation,to%2Done%20comparisons%20between%20characters.&text=MySQL%20implements%20utf8%20language%2Dspecific,work%20well%20for%20a%20language.

You can either update to a more modern MySQL Version (and use the code from the tutorial) or check which UTF8 MB4 collation your older version supports via running:

SHOW COLLATION WHERE Charset = 'utf8mb4';