sidorares / sidorares/node-mysql2
Prepared statement integer arguments converted to string causes issues with BIT(n)
Nobody has claimed this yet.
- Dominant language
- TypeScript
- Stars
- 4.4k
- Forks
- 680
- Avg merge
- 9h 7m
- Merged PRs (30d)
- 59
Description
So I'm having an odd issue related to the BIT data type, and its behavior is acting quite strange compared to the npm mysql driver, which I used prior to mysql2.
In that version, their prepared statements allowed me to interact with the BIT data type as if it was a number with no issue - it returned me an integer from the data base, and inserting/using bitwise operations worked just fine.
For some reason, when I perform the same queries with mysql2, the numbers in the prepared statements are being converted to zero (as expected)... but the values actually inserted into the database are 1 and 0110000, (for BIT(1) and BIT(7)) respectively, even though the MySQL event log + query log on my end show that it should be zero.
These are the two arguments passed to pool.execute, which I log in my own file:
{"query":"INSERT INTO `chat_messages` (sender, recipient, text, time, type, flags) VALUES (?, ?, ?, ?, ?, ?)","arguments":[139580,1,"Hello from bot",1492249595,0,0]}
And this is what the MySQL General Log logged:
413 Prepare INSERT INTO `chat_messages` (sender, recipient, text, time, type, flags) VALUES (?, ?, ?, ?, ?, ?)
413 Execute INSERT INTO `chat_messages` (sender, recipient, text, time, type, flags) VALUES ('139580', '1', 'Hello from bot', '1492249595', '0', '0')
Looks all good, I expect zero and zero to be inserted - however...

The issue appears to be in the way the prepared statements function. Instead of allowing numbers to be numbers, all arguments are forced to be strings, which confuses the MySQL parser since it is interpreting '0' as a bitset instead of the number zero. If I copy the same query and perform it without the quotes around the numbers, it works a treat.
Is there any way to disable the quotes around non-string arguments?
Contributor guide
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
Research direction
The issue points to pool.execute and MySQL prepared-statement logging; start by tracing how execute arguments are typed and serialized. Reproduce the BIT(1) and BIT(7) cases, then verify that numeric zero is stored correctly while string arguments retain their behavior.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- mysql, node.js, typescript
- Domain
- backend, databases
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 30/100