10000 Cannot use mysql sql mode · Issue #2018 · typeorm/typeorm · GitHub
[go: up one dir, main page]
More Web Proxy on the site http://driver.im/
Skip to content

Cannot use mysql sql mode #2018

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom
Closed
sevenryze opened this issue Apr 24, 2018 · 4 comments
Closed

Cannot use mysql sql mode #2018

sevenryze opened this issue Apr 24, 2018 · 4 comments
Assignees
@AlexMesser

Comments

@sevenryze
Copy link
Contributor

Issue type:

[ ] question
[x] bug report
[ ] feature request
[ ] documentation issue

Database system/driver:

[ ] cordova
[ ] mongodb
[ ] mssql
[x] mysql / mariadb
[ ] oracle
[ ] postgres
[ ] sqlite
[ ] sqljs
[ ] react-native

TypeORM version:

[x] latest
[ ] @next
[ ] 0.x.x (or put your version here)

Steps to reproduce or a small repository showing the problem:
When I set the mysql sql mode to sql_mode = ANSI, and set the log feature to true, there will be an error with selecting query, like Unknown column 'PRIMARY' in 'where clause' in almost every startup. The most important thing is that i cannot find any doc mention that.

You could reproduce this problem using the init project and set sql mode to ANSI or something other than default.

Of course, when not changing sql mode or the sql mode not effecting the quote string? i guess, this problem will be fixed.

I think we may add feature to reflex the sql mode change or add doc to remind user not changing the sql mode.

Anyway, thank you for the awesome project. 👍
@pleerock
Copy link
Member

I don't know what changing sql mode leads to. Can you please research about differences between modes and find exact one that lead to this issue?

@sevenryze
Copy link
Contributor Author
sevenryze commented Apr 25, 2018 •
edited
Loading

Ok, I will list the steps:

  1. use the typeorm init --database mysql to create a clean project.
  2. set the logging option to true.
  3. set the sql_mode = ANSI into /etc/mysql/mysql.conf.d/mysqld.conf, the config file of ubuntu mysql 5.7.22.
  4. login to mysql to create an empty test database.
  5. Restart your mysql server.
  6. Run the npm run start.

Then, i get the error:

query: START TRANSACTION
query: SELECT DATABASE() AS `db_name`
query: SELECT * FROM `INFORMATION_SCHEMA`.`TABLES` WHERE (`TABLE_SCHEMA` = 'test' AND `TABLE_NAME` = 'user')
query: SELECT * FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE (`TABLE_SCHEMA` = 'test' AND `TABLE_NAME` = 'user')
query: SELECT * FROM `INFORMATION_SCHEMA`.`KEY_COLUMN_USAGE` WHERE `CONSTRAINT_NAME` = "PRIMARY" AND ((`TABLE_SCHEMA` = 'test' AND `TABLE_NAME` = 'user'))
query: SELECT `SCHEMA_NAME`, `DEFAULT_CHARACTER_SET_NAME` as `CHARSET`, `DEFAULT_COLLATION_NAME` AS `COLLATION` FROM `INFORMATION_SCHEMA`.`SCHEMATA`
query: SELECT `s`.* FROM `INFORMATION_SCHEMA`.`STATISTICS` `s` LEFT JOIN `INFORMATION_SCHEMA`.`REFERENTIAL_CONSTRAINTS` `rc` ON `s`.`INDEX_NAME` = `rc`.`CONSTRAINT_NAME` WHERE ((`s`.`TABLE_SCHEMA` = 'test' AND `s`.`TABLE_NAME` = 'user')) AND `s`.`INDEX_NAME` != 'PRIMARY' AND `rc`.`CONSTRAINT_NAME` IS NULL
query: SELECT `kcu`.`TABLE_SCHEMA`, `kcu`.`TABLE_NAME`, `kcu`.`CONSTRAINT_NAME`, `kcu`.`COLUMN_NAME`, `kcu`.`REFERENCED_TABLE_SCHEMA`, `kcu`.`REFERENCED_TABLE_NAME`, `kcu`.`REFERENCED_COLUMN_NAME`, `rc`.`DELETE_RULE` `ON_DELETE`, `rc`.`UPDATE_RULE` `ON_UPDATE` FROM `INFORMATION_SCHEMA`.`KEY_COLUMN_USAGE` `kcu` INNER JOIN `INFORMATION_SCHEMA`.`REFERENTIAL_CONSTRAINTS` `rc` ON `rc`.`constraint_name` = `kcu`.`constraint_name` WHERE (`kcu`.`TABLE_SCHEMA` = 'test' AND `kcu`.`TABLE_NAME` = 'user')
query failed: SELECT * FROM `INFORMATION_SCHEMA`.`KEY_COLUMN_USAGE` WHERE `CONSTRAINT_NAME` = "PRIMARY" AND ((`TABLE_SCHEMA` = 'test' AND `TABLE_NAME` = 'user'))
error: { Error: ER_BAD_FIELD_ERROR: Unknown column 'PRIMARY' in 'where clause'
    at Query.Sequence._packetToError (\untitled\node_modules\mysql\lib\protocol\sequences\Sequence.js:52:14)
    at Query.ErrorPacket (\untitled\node_modules\mysql\lib\protocol\sequences\Query.js:77:18)
    at Protocol._parsePacket (\untitled\node_modules\mysql\lib\protocol\Protocol.js:279:23)
    at Parser.write (untitled\node_modules\mysql\lib\protocol\Parser.js:76:12)
    at Protocol.write (\untitled\node_modules\mysql\lib\protocol\Protocol.js:39:16)
    at Socket.<anonymous> (\untitled\node_modules\mysql\lib\Connection.js:103:28)
    at Socket.emit (events.js:180:13)
    at Socket.emit (domain.js:421:20)
    at addChunk (_stream_readable.js:274:12)
    at readableAddChunk (_stream_readable.js:261:11)
    --------------------
    at Protocol._enqueue (\untitled\node_modules\mysql\lib\protocol\Protocol.js:145:48)
    at PoolConnection.query (\untitled\node_modules\mysql\lib\Connection.js:208:25)
    at MysqlQueryRunner.<anonymous> (\untitled\src\driver\mysql\MysqlQueryRunner.ts:146:36)
    at step (\untitled\node_modules\typeorm\driver\mysql\MysqlQueryRunner.js:42:23)
    at Object.next (\untitled\node_modules\typeorm\driver\mysql\MysqlQueryRunner.js:23:53)
    at fulfilled (\untitled\node_modules\typeorm\driver\mysql\MysqlQueryRunner.js:14:58)
    at <anonymous>
    at process._tickCallback (internal/process/next_tick.js:118:7)
  code: 'ER_BAD_FIELD_ERROR',
  errno: 1054,
  sqlMessage: 'Unknown column \'PRIMARY\' in \'where clause\'',
  sqlState: '42S22',
  index: 0,
  sql: 'SELECT * FROM `INFORMATION_SCHEMA`.`KEY_COLUMN_USAGE` WHERE `CONSTRAINT_NAME` = "PRIMARY" AND ((`TABLE_SCHEMA` = \'test\' AND `TABLE_NAME` = \'user\'))' }
query: ROLLBACK
{ QueryFailedError: ER_BAD_FIELD_ERROR: Unknown column 'PRIMARY' in 'where clause'
    at new QueryFailedError (\untitled\src\error\QueryFailedError.ts:7:9)
    at Query._callback (\untitled\src\driver\mysql\MysqlQueryRunner.ts:157:37)
    at Query.Sequence.end (\untitled\node_modules\mysql\lib\protocol\sequences\Sequence.js:88:24)
    at Query.ErrorPacket (\untitled\node_modules\mysql\lib\protocol\sequences\Query.js:90:8)
    at Protocol._parsePacket (\untitled\node_modules\mysql\lib\protocol\Protocol.js:279:23)
    at Parser.write (\untitled\node_modules\mysql\lib\protocol\Parser.js:76:12)
    at Protocol.write (\untitled\node_modules\mysql\lib\protocol\Protocol.js:39:16)
    at Socket.<anonymous> (\untitled\node_modules\mysql\lib\Connection.js:103:28)
    at Socket.emit (events.js:180:13)
    at Socket.emit (domain.js:421:20)
  message: 'ER_BAD_FIELD_ERROR: Unknown column \'PRIMARY\' in \'where clause\'',
  code: 'ER_BAD_FIELD_ERROR',
  errno: 1054,
  sqlMessage: 'Unknown column \'PRIMARY\' in \'where clause\'',
  sqlState: '42S22',
  index: 0,
  sql: 'SELECT * FROM `INFORMATION_SCHEMA`.`KEY_COLUMN_USAGE` WHERE `CONSTRAINT_NAME` = "PRIMARY" AND ((`TABLE_SCHEMA` = \'test\' AND `TABLE_NAME` = \'user\'))',
  name: 'QueryFailedError',
  query: 'SELECT * FROM `INFORMATION_SCHEMA`.`KEY_COLUMN_USAGE` WHERE `CONSTRAINT_NAME` = "PRIMARY" AND ((`TABLE_SCHEMA` = \'test\' AND `TABLE_NAME` = \'user\'))',
  parameters: [] }

And when I comment the sql_mode = ANSI of mysql config file, everything will be normal.

@pleerock
Copy link
Member

its a bug. Fix will be released in 0.2.2

@sevenryze
Copy link
Contributor Author

@pleerock Fine, thanks for your work and I'm waiting the new version.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@AlexMesser AlexMesser

Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
3 participants
@pleerock @AlexMesser @sevenryze
0