「MySQL and MariaDB」- 常见错误汇总

更新日期:2019年08月12日

内容简介

在本部分中,我们整理了到目前为止在MySQL中遇到的一些错误。总有一款适合你。

#1 (JSON)

-「Use JSON data type in MariaDB got error #13622

问题描述:
'You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near \'json not null, `expired` datetime not null) default character set utf8 collate u\' at line 1',

问题原因:
在MariaDB 10.1.x以下(包括)的版本中,不支持JSON类型:

I am experiencing this problem as well. Saw that $table->json('tags')->nullable(); always evaluates to 'json' in \Illuminate\Database\Schema\Grammars\MySqlGrammar (line 485). But the json column data type is not supported in MariaDB 10.1.x and lower. It will be available in version 10.2 (which is already in Alpha).

I wrote all my tests with a SQLite database, where the SQLite grammar evaluates to 'text', Because all my tests passed i changed my 'json' columns to 'text'.

Another way would be to somehow make use of the dynamic columns in MariaDB. and select json data with COLUMN_JSON. Use 'blob' as datatype for your column in that case.

解决办法:
在我的场景中,由于是使用第三方开源系统,所以修改程序是不太可能了,只能升级数据库。

#2 (Couldn't execute)

-「SOLVED: Mysqldump issues after upgrade to 5.7

问题描述:
D:\phpStudy\PHPTutorial\MySQL\bin>mysqldump -utest -ptest -h10.10.50.238 -P3307
-t -c --databases veryeast_stat --tables stat_company_log --where "add_time BETW
EEN '2016-01-01' AND '2016-01-02'" >stat_company_log.sql 
mysqldump: Couldn't execute 'SELECT /*!40001 SQL_NO_CACHE */ * FROM `apidoc_chap
ter` WHERE add_time BETWEEN '2016-01-01' AND '2016-01-02'': Unknown column 'add_
time' in 'where clause' (1054)

问题原因:
这种问题的原因也有很多。

我的场景是:Windows,PHPStudy,mysqldump Ver 10.13 Distrib 5.5.53, for Win32 (AMD64)

问题原因是SQL语句的结尾多了一个制表符。

# mysqld_safe error: log-error set to '/path/to/mysqd.log'

问题描述:
mysqld_safe error: log-error set to '/path/to/mysqd.log', however file don't exists. Create writable for user 'mysql'

问题原因:
文件是存在的,文件所在的盘是挂载进来的。通过ls -l发现,原来是SELinux没有正确配置。

解决办法:
最然不太好,但是还是把SELinux关闭了。


ToC

内容简介

#1 (JSON)

#2 (Couldn't execute)

# mysqld_safe error: log-error set to '/path/to/mysqd.log'