You have an error in your SQL syntax

[johnjelinek]

DBD::mysql version

5.008

MySQL client version

8.4.2

Server version

5.7.32

Operating system version

Rocky Linux 8

What happened?

$dbh->column_info(undef, undef, 'report', undef);

>count_params statement DESCRIBE `report` '%'
	<- dbd_st_prepare
 -> dbd_st_execute for 1498f0378
	>- dbd_st_free_result_sets
	<- dbd_st_free_result_sets RC -1
	<- dbd_st_free_result_sets
mysql_st_internal_execute MYSQL_VERSION_ID 80402
>parse_params statement DESCRIBE `report` '%'
reconnecting
Can't reconnect on unexpected error 1064
		--> do_error
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 ''%'' at line 1 error 1064 recorded: 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 ''%'' at line 1
		<-- do_error

I think it'd be better to have column_info call something like this instead:

$dbh->prepare("SHOW COLUMNS FROM $table_id LIKE " . $dbh->quote('%'))

Other information

No response

[dveeden]

Both these work for me from a MySQL Client prompt:

DESCRIBE `report` '%';

SHOW COLUMNS FROM `report` LIKE '%';

Why doesn't it raise error 1064 while doing this via DBD::mysql?

[dveeden]

#!/bin/perl
use v5.36;
use DBI;
use Data::Dumper;


my $dbh = DBI->connect('DBI:mysql:database=test', 'root', '', {
    mysql_auto_reconnect => 1,
    RaiseError => 1,
    AutoCommit => 1,
});

my $sth = $dbh->column_info(undef, undef, 'report', undef);
$sth->execute;

while (my $ref = $sth->fetchrow_hashref()) {
	print Dumper($ref);
}

Gives me this:

$VAR1 = {
          'DECIMAL_DIGITS' => undef,
          'COLLATION_CAT' => undef,
          'IS_NULLABLE' => 'NO',
          'DOMAIN_NAME' => undef,
          'IS_SELF_REF' => undef,
          'TABLE_CAT' => undef,
          'mysql_values' => undef,
          'CHAR_OCTET_LENGTH' => undef,
          'COLUMN_NAME' => 'id',
          'DATA_TYPE' => 4,
          'TABLE_NAME' => 'report',
          'CHAR_SET_SCHEM' => undef,
          'COLLATION_SCHEM' => undef,
          'mysql_type_name' => 'int',
          'COLUMN_DEF' => undef,
          'SCOPE_SCHEM' => undef,
          'NULLABLE' => 0,
          'mysql_is_pri_key' => !!1,
          'TYPE_NAME' => 'INT',
          'CHAR_SET_CAT' => undef,
          'MAX_CARDINALITY' => undef,
          'SCOPE_NAME' => undef,
          'DOMAIN_SCHEM' => undef,
          'NUM_PREC_RADIX' => 10,
          'COLUMN_SIZE' => undef,
          'COLLATION_NAME' => undef,
          'REMARKS' => undef,
          'DOMAIN_CAT' => undef,
          'CHAR_SET_NAME' => undef,
          'TABLE_SCHEM' => undef,
          'UDT_SCHEM' => undef,
          'mysql_is_auto_increment' => 0,
          'SQL_DATETIME_SUB' => undef,
          'UDT_NAME' => undef,
          'SQL_DATA_TYPE' => 4,
          'DTD_IDENTIFIER' => undef,
          'SCOPE_CAT' => undef,
          'ORDINAL_POSITION' => 1,
          'BUFFER_LENGTH' => undef,
          'UDT_CAT' => undef
        };

This is with MySQL 9.0.1 server and libraries. and with a table created with create table report(id int primary key);

Does this only happen with 5.7 as server? with 8.4 libraries? with a specific table layout? with a specific SQL mode?

[johnjelinek]

I get the same issue even with the mysql client, I don't see documentation for DESCRIBE table '%' -- may be valuable to move to SHOW COLUMNS either way.

[dveeden]

I get the same issue even with the mysql client, I don't see documentation for DESCRIBE table '%' -- may be valuable to move to SHOW COLUMNS either way.

I think moving go SHOW COLUMNS would be good. But I also would like to be able to reproduce and understand the issue if that's possible. That could improve the quality of the patch, test(s), etc.

This is actually in the docs on https://dev.mysql.com/doc/refman/8.4/en/explain.html

By default, DESCRIBE displays information about all columns in the table. col_name, if given, is the name of a column in the table. In this case, the statement displays information only for the named column. wild, if given, is a pattern string. It can contain the SQL % and _ wildcard characters. In this case, the statement displays output only for the columns with names matching the string. There is no need to enclose the string within quotation marks unless it contains spaces or other special characters.

[dveeden]

Information that could be useful:

• The SQL Mode of your session
• Complete set of statements that fails in the MySQL Client (and full output or a screenshot)
• Maybe the general log of a session where this fails
• Any Proxy/Router/etc in use?
• Information on the used table. Is it a base table, a view? Anything else that might be important?

[dveeden]

This is what I get with a MySQL 5.7 server in a container.

podman run --env MYSQL_ALLOW_EMPTY_PASSWORD=1 --env MYSQL_ROOT_HOST='%' -p3307:3306 -it container-registry.oracle.com/mysql/community-server:5.7

mysql-5.7.33> DESCRIBE mysql.user `%plugin`;
+--------+----------+------+-----+-----------------------+-------+
| Field  | Type     | Null | Key | Default               | Extra |
+--------+----------+------+-----+-----------------------+-------+
| plugin | char(64) | NO   |     | mysql_native_password |       |
+--------+----------+------+-----+-----------------------+-------+
1 row in set (0.00 sec)

mysql-5.7.33> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.7.33    |
+-----------+
1 row in set (0.00 sec)

mysql-5.7.33> DESCRIBE mysql.user '%plugin';
+--------+----------+------+-----+-----------------------+-------+
| Field  | Type     | Null | Key | Default               | Extra |
+--------+----------+------+-----+-----------------------+-------+
| plugin | char(64) | NO   |     | mysql_native_password |       |
+--------+----------+------+-----+-----------------------+-------+
1 row in set (0.00 sec)

mysql-5.7.33> SHOW SESSION VARIABLES LIKE 'sql_mode';
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                                                                     |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| sql_mode      | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

[dveeden]

Note that this would work around a compatibility issue with TiDB: pingcap/tidb#46814
However I would rather see that issue getting a proper fix in TiDB instead.

[dveeden]

@johnjelinek could you provide any more details on this?

[dveeden]

@johnjelinek ?

[johnjelinek]

Sorry, I haven't been able to return to this. I'm using a MySQL compatible equivalent like TiDB that demonstrates the same symptoms here.

[dveeden]

Ok. I think I know enough to proceed with this

What MySQL compatible server are you using exactly? Maybe we can add it to CI/CD?

[johnjelinek]

It's called SingleStore