plugin-mysql 在启动时报出语法错误 #238

kecrily · 2021-04-18T07:43:14Z

Describe the bug

在执行 npx koishi start 命令时，koishi-plugin-mysql 创建尝试初始化数据库，报出 mysql 语句语法错误。

To Reproduce

在我的环境中执行

npx koishi start

Expected behavior

正常启动

Screenshots

...
[I] mysql auto creating table subscribe
[W] mysql CREATE TABLE `subscribe` (`id` `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,`names` TEXT,primary key (`id`)) COLLATE = 'utf8mb4_general_ci'
[E] app ER_PARSE_ERROR: 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 'INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,`names` TEXT,primary key (`id`)) COL...' at line 1   at MysqlDatabase.query (/home/bot/node_modules/koishi-plugin-mysql/lib/index.js:155:19)
        at MysqlDatabase.start (/home/bot/node_modules/koishi-plugin-mysql/lib/index.js:133:20)
        at processTicksAndRejections (internal/process/task_queues.js:93:5)
        at async Promise.all (index 0)
        at async _App.start (/home/bot/node_modules/koishi-core/lib/index.js:1941:5)

Versions

OS: Debian 10.0(core: 4.19.132-1 gcc: 8.3.0 )
Node Version: 14.16.1
Npm Version: 6.14.12
go-cqhttp Version: 1.0.0-beta3
Koishi Version: 3.9.1
Koishi-plugin-mysql: 3.3.1
MariaDB: 10.4

Additional context

插件列表：

onebot
chat
common
github
monitor
rss
schedule
teach
tools
webui

The text was updated successfully, but these errors were encountered:

KoishiMoe · 2021-08-12T11:38:47Z

请问你这边解决了吗，我这边也是这样

NWYLZW · 2021-08-12T11:40:29Z

解决应该解决了吧
你的版本信息是？

KoishiMoe · 2021-08-12T12:32:43Z

解决应该解决了吧
你的版本信息是？

OS:Archlinux (内核5.12.14-arch1-1)
Node Version:12.22.4 14.74.4 16.6.2 这三者都试过
npm Vsersion:7.20.5
go-cqhttp Version: v1.0.0-beta5
koishi Version: 3.13.1
Koishi-plugin-mysql: 3.5.0
MariaDB: 10.6.4 (也试过mysql 8.0.25 和mysql 5.6.51)

插件列表：

onebot
chat
common
github
image-search
puppeteer
schedule
teach
tools
webui

NWYLZW · 2021-08-12T12:41:40Z

离谱，你的报错也是和他的一样吗

KoishiMoe · 2021-08-12T13:08:29Z

离谱，你的报错也是和他的一样吗

我的是这样的

[I] mysql auto creating table user
[W] mysql CREATE TABLE `user` (`lastCall` timestamp,`password` varchar(64),`token` varchar(64),`expire` bigint unsigned default 0,`onebot` varchar(50) null default null,primary key (`id`),unique index (`onebot`),bigint(20) unsigned not null auto_increment,varchar(50) null,int(20) unsigned not null,int(4) unsigned not null,varchar(65536) not null default ,varchar(65536) not null default ,varchar(50) null,varchar(50) null) COLLATE = 'utf8mb4_general_ci'
[E] app ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'bigint(20) unsigned not null auto_increment,varchar(50) null,int(20) unsigned no' at line 1   at MysqlDatabase.query (/home/koishibot/koishi/node_modules/koishi-plugin-mysql/lib/index.js:216:19)
        at MysqlDatabase.start (/home/koishibot/koishi/node_modules/koishi-plugin-mysql/lib/index.js:194:20)
        at processTicksAndRejections (internal/process/task_queues.js:97:5)
        at async Promise.all (index 0)
        at async _App.start (/home/koishibot/koishi/node_modules/koishi-core/lib/index.js:2030:5)
error Command failed with exit code 1.

KoishiMoe · 2021-08-12T13:10:26Z

离谱，你的报错也是和他的一样吗

我的是这样的

[I] mysql auto creating table user
[W] mysql CREATE TABLE `user` (`lastCall` timestamp,`password` varchar(64),`token` varchar(64),`expire` bigint unsigned default 0,`onebot` varchar(50) null default null,primary key (`id`),unique index (`onebot`),bigint(20) unsigned not null auto_increment,varchar(50) null,int(20) unsigned not null,int(4) unsigned not null,varchar(65536) not null default ,varchar(65536) not null default ,varchar(50) null,varchar(50) null) COLLATE = 'utf8mb4_general_ci'
[E] app ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'bigint(20) unsigned not null auto_increment,varchar(50) null,int(20) unsigned no' at line 1   at MysqlDatabase.query (/home/koishibot/koishi/node_modules/koishi-plugin-mysql/lib/index.js:216:19)
        at MysqlDatabase.start (/home/koishibot/koishi/node_modules/koishi-plugin-mysql/lib/index.js:194:20)
        at processTicksAndRejections (internal/process/task_queues.js:97:5)
        at async Promise.all (index 0)
        at async _App.start (/home/koishibot/koishi/node_modules/koishi-core/lib/index.js:2030:5)
error Command failed with exit code 1.

另外手动创建user表之后会继续报错如下

[W] mysql CREATE TABLE `channel` (`name` varchar(50),`activity` text,primary key (`id`),bigint(20) unsigned not null auto_increment,int(20) unsigned not null,varchar(50) null,varchar(65536) not null default ,varchar(65536) not null default ) COLLATE = 'utf8mb4_general_ci'
[E] app ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'bigint(20) unsigned not null auto_increment,int(20) unsigned not null,varchar(50' at line 1   at MysqlDatabase.query (/home/koishibot/koishi/node_modules/koishi-plugin-mysql/lib/index.js:216:19)
        at MysqlDatabase.start (/home/koishibot/koishi/node_modules/koishi-plugin-mysql/lib/index.js:194:20)
        at processTicksAndRejections (internal/process/task_queues.js:97:5)
        at async Promise.all (index 0)
        at async _App.start (/home/koishibot/koishi/node_modules/koishi-core/lib/index.js:2030:5)

NWYLZW · 2021-08-12T13:29:01Z

这段代码你能直接在你的数据库执行吗？

CREATE TABLE `user` (
  `lastCall` timestamp,
  `password` varchar(64),
  `token` varchar(64),
  `expire` bigint unsigned default 0,
  `onebot` varchar(50) null default null,
  primary key (`id`),
  unique index (`onebot`),
  bigint(20) unsigned not null auto_increment,
  varchar(50) null,
  int(20) unsigned not null,
  int(4) unsigned not null,
  varchar(65536) not null default ,
  varchar(65536) not null default ,
  varchar(50) null,
  varchar(50) null
) COLLATE = 'utf8mb4_general_ci'

NWYLZW · 2021-08-12T13:35:03Z

我看着像某个插件的导致的问题（猜的
可以试试移除某些插件直到数据库能够建表为止

KoishiMoe · 2021-08-12T14:00:51Z

这段代码你能直接在你的数据库执行吗？

CREATE TABLE `user` (
  `lastCall` timestamp,
  `password` varchar(64),
  `token` varchar(64),
  `expire` bigint unsigned default 0,
  `onebot` varchar(50) null default null,
  primary key (`id`),
  unique index (`onebot`),
  bigint(20) unsigned not null auto_increment,
  varchar(50) null,
  int(20) unsigned not null,
  int(4) unsigned not null,
  varchar(65536) not null default ,
  varchar(65536) not null default ,
  varchar(50) null,
  varchar(50) null
) COLLATE = 'utf8mb4_general_ci'

用这段得到的报错如下（版本 10.6.3-MariaDB 和5.6.51 MySQL均如此）

ERROR 1064 (42000): 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 'bigint(20) unsigned not null auto_increment,
  varchar(50) null,
  int(20) un...' at line 9

KoishiMoe · 2021-08-12T14:01:59Z

我看着像某个插件的导致的问题（猜的
可以试试移除某些插件直到数据库能够建表为止

只保留mysql插件和onebot也这样，我一会用windows试试吧

NWYLZW · 2021-08-12T14:25:07Z

具体还是要看 shigma 来看到底是啥问题了
我在 koishi 里面还没有试过 mysql
你也许可以换个数据库，换成 mongo 看看问题是否还会继续出现

NWYLZW · 2021-08-12T15:22:31Z

我看着像某个插件的导致的问题（猜的
可以试试移除某些插件直到数据库能够建表为止

只保留mysql插件和onebot也这样，我一会用windows试试吧

还有一件事，你就算没安装，引入了也是会奏效的
最好是把 import / require 都删了

shigma · 2021-08-12T15:24:18Z

这是 koishi 3.13.1 新引入的问题，回退版本到 3.13.0 可以解决

KoishiMoe · 2021-08-12T17:06:35Z

具体还是要看 shigma 来看到底是啥问题了
我在 koishi 里面还没有试过 mysql
你也许可以换个数据库，换成 mongo 看看问题是否还会继续出现

mongo不会报创建错误，koishi可以正常启动，但是要使用数据库的插件（如teach）依然会报错，如下

[W] command executing command: # test2 Answer
    TypeError: Cannot read property 'id' of undefined
        at createFilter (/home/koishibot/koishi2/node_modules/koishi-plugin-mongo/lib/index.js:190:13)
        at MongoDatabase.get (/home/koishibot/koishi2/node_modules/koishi-plugin-mongo/lib/index.js:203:20)
        at MongoDatabase.getUser (/home/koishibot/koishi2/node_modules/koishi-plugin-mongo/lib/index.js:261:29)
        at /home/koishibot/koishi2/node_modules/koishi-plugin-teach/lib/index.js:1750:38
        at _App.serial (/home/koishibot/koishi2/node_modules/koishi-core/lib/index.js:1518:37)
        at create (/home/koishibot/koishi2/node_modules/koishi-plugin-teach/lib/index.js:807:13) +30s

另外刚才Windows虚拟机上试了一下mysql也不行（同样的报错）

KoishiMoe · 2021-08-12T17:06:47Z

这是 koishi 3.13.1 新引入的问题，回退版本到 3.13.0 可以解决

好的，感谢

MindXL · 2021-08-13T16:45:14Z

我今天在扩展自己的数据表时也出现了这个问题。
使用新式的Table.extend('user',{})，也就是传入 Meta 类型参数时，plugin-mysql在将 Meta 接口中的 fileds 转换为 sql 语句时出了问题。

Meta:

koishi/packages/koishi-core/src/database.ts

Line 36 in cb23788

export interface Meta<O = any> {

plugin-mysql中这一行定义了每个小段sql，它的成品就是varchar(50) null这种字符串。它的开头少加了相应的key。

koishi/packages/plugin-mysql/src/database.ts

Line 145 in cb23788

let def = getTypeDefinition(fields[key])

NWYLZW · 2021-08-13T16:46:55Z

你这个应该是 dup 了，继续在那个 issue 下面评论最好

shigma · 2021-08-13T17:08:21Z

你这个应该是 dup 了，继续在那个 issue 下面评论最好

理论上这都应该单独再开一个 issue 的（

NWYLZW · 2021-08-13T17:13:07Z

好吧，我感觉还是那个建表的问题，所以觉得 dup 了

…#335)

Monkeysama · 2021-08-17T02:05:37Z

同一个问题，我猜测是某些原因导致建表语句里缺少了字段名

NWYLZW · 2021-08-17T02:07:37Z

同一个问题，我猜测是某些原因导致建表语句里缺少了字段名

不说我都忘记关了，这个 bug 已经修复了，暂时还没有发版

shigma added bug BUG database: mysql priority: high commited labels Apr 18, 2021

shigma closed this as completed in 6ccca1b Apr 21, 2021

shigma reopened this Aug 12, 2021

MindXL mentioned this issue Aug 13, 2021

fix(orm): sql statement lacks fields when creating tables #335

Merged

shigma pushed a commit that referenced this issue Aug 14, 2021

fix(mysql): sql statement lacks fields when creating tables, fix #238 (…

1e2bee6

…#335)

NWYLZW pushed a commit that referenced this issue Aug 14, 2021

fix(mysql): sql statement lacks fields when creating tables, fix #238 (…

f26edac

…#335)

NWYLZW closed this as completed Aug 17, 2021

MindXL mentioned this issue Aug 17, 2021

这段代码你能直接在你的数据库执行吗？ #346

Closed

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

plugin-mysql 在启动时报出语法错误 #238

plugin-mysql 在启动时报出语法错误 #238

kecrily commented Apr 18, 2021

KoishiMoe commented Aug 12, 2021

NWYLZW commented Aug 12, 2021

KoishiMoe commented Aug 12, 2021

NWYLZW commented Aug 12, 2021

KoishiMoe commented Aug 12, 2021

KoishiMoe commented Aug 12, 2021

NWYLZW commented Aug 12, 2021 •

edited

NWYLZW commented Aug 12, 2021

KoishiMoe commented Aug 12, 2021

KoishiMoe commented Aug 12, 2021

NWYLZW commented Aug 12, 2021

NWYLZW commented Aug 12, 2021

shigma commented Aug 12, 2021

KoishiMoe commented Aug 12, 2021

KoishiMoe commented Aug 12, 2021

MindXL commented Aug 13, 2021

NWYLZW commented Aug 13, 2021

shigma commented Aug 13, 2021

NWYLZW commented Aug 13, 2021

Monkeysama commented Aug 17, 2021

NWYLZW commented Aug 17, 2021

plugin-mysql 在启动时报出语法错误 #238

plugin-mysql 在启动时报出语法错误 #238

Comments

kecrily commented Apr 18, 2021

KoishiMoe commented Aug 12, 2021

NWYLZW commented Aug 12, 2021

KoishiMoe commented Aug 12, 2021

NWYLZW commented Aug 12, 2021

KoishiMoe commented Aug 12, 2021

KoishiMoe commented Aug 12, 2021

NWYLZW commented Aug 12, 2021 • edited

NWYLZW commented Aug 12, 2021

KoishiMoe commented Aug 12, 2021

KoishiMoe commented Aug 12, 2021

NWYLZW commented Aug 12, 2021

NWYLZW commented Aug 12, 2021

shigma commented Aug 12, 2021

KoishiMoe commented Aug 12, 2021

KoishiMoe commented Aug 12, 2021

MindXL commented Aug 13, 2021

NWYLZW commented Aug 13, 2021

shigma commented Aug 13, 2021

NWYLZW commented Aug 13, 2021

Monkeysama commented Aug 17, 2021

NWYLZW commented Aug 17, 2021

NWYLZW commented Aug 12, 2021 •

edited