mysql错误解决办法集合

一、This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its de 错误解决办法

这是我们开启了bin-log, 我们就必须指定我们的函数是否是
1 DETERMINISTIC 不确定的
2 NO SQL 没有SQl语句，当然也不会修改数据
3 READS SQL DATA 只是读取数据，当然也不会修改数据
4 MODIFIES SQL DATA 要修改数据
5 CONTAINS SQL 包含了SQL语句
其中在function里面，只有 DETERMINISTIC, NO SQL 和 READS SQL DATA 被支持。如果我们开启了 bin-log, 我们就必须为我们的function指定一个参数。

在MySQL中创建函数时出现这种错误的解决方法：执行下面语句
set global log_bin_trust_function_creators=TRUE;

二 、navicat 连接mysql 错误提示 ： 2003 Can't connect to MySql server on "localhost"(10061)

mysql 服务 关闭 不能重启

windows 意外关闭 mysql 服务不能启动，报错

2003 Can't connect to MySql server on "localhost"(10061)

在mysql 的data目录下找到 .err错误日志

[ERROR] [MY-000000] [InnoDB] InnoDB: Assertion failure: dict0dict.cc:1235:table2 == NULL
InnoDB: thread 20556
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/8.0/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
02:18:37 UTC - mysqld got exception 0x80000003 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
Attempting to collect some information that could help diagnose the problem.
As this is a crash and something is definitely wrong, the information
collection process might fail.

key_buffer_size=8388608
read_buffer_size=131072
max_used_connections=2
max_threads=151
thread_count=3
connection_count=2
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 67690 K bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x1c5f286a6a0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
7ff74ffeb242 mysqld.exe!my_sigabrt_handler()[my_thr_init.cc:373]
7ffee836dc17 ucrtbase.dll!raise()
7ffee836eaa1 ucrtbase.dll!abort()
7ff7502360b7 mysqld.exe!ut_dbg_assertion_failed()[ut0dbg.cc:90]
7ff750177e0c mysqld.exe!dict_table_add_to_cache()[dict0dict.cc:1235]
7ff75028d02e mysqld.exe!dd_open_table_onedd::Table()[dict0dd.cc:4062]
7ff75028bad6 mysqld.exe!dd_open_tabledd::Table()[dict0dd.cc:4229]
7ff75013a5d0 mysqld.exe!ha_innobase::open()[ha_innodb.cc:6150]
7ff74f28a1ea mysqld.exe!handler::ha_open()[handler.cc:2633]
7ff74f31c7b8 mysqld.exe!open_table_from_share()[table.cc:2941]
7ff74f42495e mysqld.exe!open_table()[sql_base.cc:3298]
7ff74f423920 mysqld.exe!open_and_process_table()[sql_base.cc:4958]
7ff74f425886 mysqld.exe!open_tables()[sql_base.cc:5583]
7ff74f425dd1 mysqld.exe!open_tables_for_query()[sql_base.cc:6363]
7ff74f4d0ec9 mysqld.exe!Sql_cmd_dml::prepare()[sql_select.cc:345]
7ff74f4cd88d mysqld.exe!Sql_cmd_dml::execute()[sql_select.cc:494]
7ff74f432d38 mysqld.exe!mysql_execute_command()[sql_parse.cc:4147]
7ff74f433816 mysqld.exe!mysql_parse()[sql_parse.cc:4927]
7ff74f42d6b8 mysqld.exe!dispatch_command()[sql_parse.cc:1607]
7ff74f42e5e5 mysqld.exe!do_command()[sql_parse.cc:1233]
7ff74f2ac538 mysqld.exe!handle_connection()[connection_handler_per_thread.cc:308]
7ff7503d3e87 mysqld.exe!pfs_spawn_thread()[pfs.cc:2839]
7ff74ffeb1dc mysqld.exe!win_thread_start()[my_thread.cc:52]
7ffee831cd70 ucrtbase.dll!_o__realloc_base()
7ffeeb708364 KERNEL32.DLL!BaseThreadInitThunk()
7ffeeb9c70b1 ntdll.dll!RtlUserThreadStart()

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (1c5f32f33e8): SELECT COUNT(1) FROM mdmDB.contract_ticket
Connection ID (thread ID): 8
Status: NOT_KILLED

The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.

可能是windows 非正常关机 导致的问题

网上解决办法

1、重启 MySQL服务

2、重改一下密码

都无效

下面的方法 帮到了我

其中重点的几句是：

2020-04-07T02:08:36.359052Z 8 [ERROR] [MY-013183] [InnoDB] Assertion failure: dict0dict.cc:1224:table2 == NULL thread 123145436229632
InnoDB: We intentionally generate a memory trap.

InnoDB: We intentionally generate a memory trap.

Connection ID (thread ID): 8
Status: NOT_KILLED

从网上搜了下发现这种大多是因为突然宕机造成的数据库数据出了问题，我这块出错的表是在django的apscheduler中，在运行时这部分表的确会不断进行查询等操作，所以突遇意外也是很有可能的。

解决方法首选是修改 innodb_force_recovery 的值，这个值的说明如下：

innodb_force_recovery 会影响整个InnoDB存储引擎的恢复状况。默认为0，表示当需要恢复时执行所有的

innodb_force_recovery可以设置为1-6,大的数字包含前面所有数字的影响。当设置参数值大于0后，可以对表进行

select,create,drop操作,但insert,update或者delete这类操作是不允许的。

值为1：(SRV_FORCE_IGNORE_CORRUPT):忽略检查到的corrupt页。
值为2：(SRV_FORCE_NO_BACKGROUND):阻止主线程的运行，如主线程需要执行full purge操作，会导致crash。
值为3：(SRV_FORCE_NO_TRX_UNDO):不执行事务回滚操作。
值为4：(SRV_FORCE_NO_IBUF_MERGE):不执行插入缓冲的合并操作。
值为5：(SRV_FORCE_NO_UNDO_LOG_SCAN):不查看重做日志，InnoDB存储引擎会将未提交的事务视为已提交。
值为6：(SRV_FORCE_NO_LOG_REDO):不执行前滚的操作。

修改my.ini文件

在[mysqld]下面加上一行

innodb_force_recovery = 1

，建议从1开始试，未成功再变成2、3、4…我理解的是，数字越大，恢复的数据就离最新版本越远。
然后保存my.ini，重启mysql

我的修改成2 后 启动成功 恢复了

innodb_force_recovery 改回0

之后记得把my.ini里的 innodb_force_recovery 值改回0，重启mysql。

三、 本地计算机上的mysql服务启动后停止，某些服务在未由其他服务或程序使用时将自动停止。

其实是my.ini保存的时候，自动使用了 utf-8 编码，导致mysql 无法正常识别my.ini。解决方法很简单：my.ini另存，选编码 ANSI ，就可以了。亲测有效。