| Bug #104507 | Insert query drops the first letter of fields that start with certain letters | ||
|---|---|---|---|
| Submitted: | 2 Aug 2021 16:04 | Modified: | 2 Aug 2021 22:32 |
| Reporter: | Roy Crisp | Email Updates: | |
| Status: | Unsupported | Impact on me: | |
| Category: | Connector / NET | Severity: | S3 (Non-critical) |
| Version: | 8.0.26 | OS: | Windows |
| Assigned to: | CPU Architecture: | Any | |
[2 Aug 2021 16:56]
Roy Crisp
PowerShell 5.1.18362.1171 XAMPP for Windows 8.0.8
[2 Aug 2021 18:05]
Daniel Valdez
Hi Roy, Did you really meant to raise this bug against MySQL Connector/NET?
[2 Aug 2021 18:32]
Roy Crisp
Sorry. I meant to raise this bug against MySQL Connector/NET.
[2 Aug 2021 19:07]
Daniel Valdez
Are you sure? This sounds more like a powershell command. But if it is indeed a Connector/NET issue, could you provide a code snippet which shows how to reproduce the bug in our end? Thanks!
[2 Aug 2021 19:29]
Roy Crisp
I downloaded mysql-connector-net-8.0.26.msi to connect PowerShell 5.1.18362.1171 to MySQL in xampp-windows-x64-8.0.8-0-VS16-installer.exe. I created a table called bugtest an executed the following from PowerShell: Invoke-MySQL -Connection $sqlConnect -Query "INSERT INTO bugtest (`alpha`, `bravo`, `charlie`, `delta`, `echo`, `foxtrot`, `golf`, `hotel`, `india`, `juliett`, `kilo`, `lima`, `mike`, `november`, `oscar`, `papa`, `quebec`, `romeo`, `sierra`, `tango`, `uniform`, `victor`, `whiskey`, `xray`, `yankee`, `zulu`) VALUES ('1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','19','20','21','22','23','24','25','26')". The error I received was as follows: Invoke-MySQL : Malformed SQL Statement: Unknown token encountered during parsing at position 21
Statement:INSERT INTO bugtest (lpha, ravo, charlie, delta, echo, oxtrot, golf, hotel, india, juliett, kilo, lima, mike,
ovember, oscar, papa, quebec, omeo, sierra, ango, uniform, ictor, whiskey, xray, yankee, zulu) VALUES
('1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','19','20','21','22','23','24','25','26').
[2 Aug 2021 22:32]
MySQL Verification Team
Thank you for the bug report. XAMPP installation aren't supported here, install an Oracle MySQL package, if you are able to repeat the issue file a new bug report. Thanks.
[4 Aug 2021 5:16]
Bradley Grainger
Roy, The backtick is an escape character in PowerShell: https://ss64.com/ps/syntax-esc.html The characters you're having trouble with (A, B, F, N, R, T, and V) are precisely the ones (on that page) that have a special meaning in PowerShell. To fix this, double all your backticks in your PowerShell command.
Description: Insert query returns Malformed SQL Statement error. Field names that begin with certain letters generate the error. A, B, F, N, R, T, and V. Invoke-MySQL : Malformed SQL Statement: Unknown token encountered during parsing at position 21 Statement:INSERT INTO bugtest (lpha, ravo, charlie, delta, echo, oxtrot, golf, hotel, india, juliett, kilo, lima, mike, ovember, oscar, papa, quebec, omeo, sierra, ango, uniform, ictor, whiskey, xray, yankee, zulu) VALUES ('1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','19','20','21','22','23','24','25','26') At line:1 char:1 + Invoke-MySQL -Connection $sqlConnect -Query "INSERT INTO bugtest (`al ... + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : OperationStopped: (CData.MySQLCmdlets.Commands.MySQLInvoke:MySQLInvoke) [Invoke-MySQL], EMr + FullyQualifiedErrorId : 1,CData.MySQLCmdlets.Commands.MySQLInvoke How to repeat: Invoke-MySQL -Connection $sqlConnect -Query "INSERT INTO bugtest (`alpha`, `bravo`, `charlie`, `delta`, `echo`, `foxtrot`, `golf`, `hotel`, `india`, `juliett`, `kilo`, `lima`, `mike`, `november`, `oscar`, `papa`, `quebec`, `romeo`, `sierra`, `tango`, `uniform`, `victor`, `whiskey`, `xray`, `yankee`, `zulu`) VALUES ('1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','19','20','21','22','23','24','25','26')" Suggested fix: Adding the letter 'q' to the beginning of the field name in the database and in the INSERT query will get around the problem.