[FORMATTING] [MySQL] Newline after USE keyword causes syntax error

[raahii]

Input data

Which SQL and options did you provide as input?

CREATE DATABASE IF NOT EXISTS sample;

USE sample;

sql-formatter -l mysql

Expected Output

CREATE DATABASE
  IF NOT EXISTS sample;

USE sample;

Actual Output

CREATE DATABASE
  IF NOT EXISTS sample;

USE
  sample;

Why?

The current formatting result causes syntax error on MySQL 8.0.30.
It seems should not place \n between USE and <DB NAME>.

$ mysql --version
mysql  Ver 8.0.30 for Linux on x86_64 (MySQL Community Server - GPL)

$ cat test.sql
CREATE DATABASE
  IF NOT EXISTS sample;

USE
  sample;
$ mysql < test.sql
ERROR at line 4: USE must be followed by a database name
ERROR 1064 (42000) at line 5: 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 'sample' at line 1

Usage

• How are you calling / using the library?
  • cat test.sql | sql-formatter -l mysql
• What SQL language(s) does this apply to?
  • mysql
• Which SQL Formatter version are you using?
  • 10.6.0

[nene]

The problem seems to be that USE is both an SQL statement and a client command. The command-version of USE works slightly differently, it forbids newline in-between and doesn't require a semicolon in the end. I tried the USE \n db; syntax with some online MySQL tools, and there it worked just fine. So this problem is definitely specific to the mysql client program.

You can turn on binary mode, which disables all the interactive commands, so USE will accept newlines in its syntax:

mysql --binary-mode < test.sql

I have plans to change the formatting of USE and several other SQL statements to not include line-breaks like they currently do, which should take care of this issue in future. But for now, I think the suggested command-line flag should help.

[raahii]

I didn't know the option, it helps me, thanks.

I have plans to change the formatting of USE and several other SQL statements to not include line-breaks like they currently do, which should take care of this issue in future

That's good, thank you for maintaining the nice formatter.