sql:drop errors when using MariaDB client-executable on Windows due to its output's line-endings not matching PHP_EOL

[af-rutgers-nb-its]

Describe the bug

drush sql:drop errors due to bad SQL syntax in a DROP TABLE query when using a MariaDB client-executable in a Microsoft Windows environment.

• The error text is "Unable to drop all tables: ERROR 1103 (42000) at line 1: Incorrect table name [...]"

Drush uses the constant PHP_EOL to create a list of table names from the SQL output of SHOW TABLES.

• The MariaDB client-executable does not produce the same line endings as PHP_EOL in a Windows environment.
• PHP_EOL is \r\n on Windows, but the MariaDB client-executable's output is simply \n for line endings.
• Specific line that is problematic:
  https://github.com/drush-ops/drush/blob/12.4.2/src/Sql/SqlMysql.php#L173

To Reproduce

Run drush sql:drop on a Windows computer where MariaDB 10.11's mysql client-executable is available in the PATH.

Full log of the error, using drush's debug output drush sql:drop -vvv, is attached here:
drush-mariadb-sqldrop-error.log

Expected behavior

drush sql:drop would not produce an error, and would successfully erase all tables in the database.

The SQL statement drush intends to create is like
DROP TABLES `tablename1` `tablename2` `tablename3` `tablename4`;

Actual behavior

The SQL backend returns a syntax error to drush, and drush will terminate with an error.

The SQL statement with bad syntax drush actually creates is like:

DROP TABLES `tablename1
tablename2
tablename3
tablename4`;

Workarounds

Strangely, Oracle's MySQL 8.x mysql client-executable does work with drush 12.x with no problems. So simply install Oracle's MySQL on your Drupal webserver even though your SQL backend uses MariaDB? Clearly not an ideal solution.

• I do not know why MySQL 8.x's client-executable's output has different line endings than MariaDB 10.11's output.

I may be so ignorant that MariaDB has a configuration setting to set its line-endings?

• There is "binary-mode", which solves this drush sql:drop problem. BUT we think there may be compatibility problems setting binary-mode to true across other Drupal projects or Drupal Core functionality. No where in Drupal documentation does it say that SQL binary-mode must be enabled as a requirement?

Replacing PHP_EOL with "\n" in Drush\SqlMySql->listTables() makes drush work with MariaDB, but then it breaks when using Oracle's MySQL on Windows. This is clearly not the way to address the problem.

• listTables(): https://github.com/drush-ops/drush/blob/12.4.2/src/Sql/SqlMysql.php#L173

System Configuration

Q	A	Specifically
Drush version?	12.x	12.4.2 (latest 12.x as of this writing)
Drupal version?	10.1	10.1.6 (latest 10.1.x of this writing)
PHP version	8.1	8.1.25 (latest 8.1.x of this writing)
OS?	Windows Server	seen on both 2019 and 2012R2
SQL client?	MariaDB 10.11	10.11.6 (latest 10.11.x as of this writing)
SQL server?	does not matter	seen on MariaDB 10.11 and MySQL 5.7

Additional information
The backend SQL server/service does not matter. For example, we reproduced the same error when the backend SQL is running Oracle's MySQL 5.7 instead of MariaDB 10.11.

• In other words, we think what matters is only the client-executable that is used by drush.

Bypassing Drush, Symfony, and PHP, we verified that the output of mariadb's client-executable does use only \n line-endings while MySQL uses \r\n on Windows.

• mysql -h DB_HOSTNAME_HERE -u DB_USER_HERE -p -e 'use `DB_SCHEMA_HERE`; show tables;' > output.txt

---

Thank you for any help on this!