You have an error in your SQL syntax

johnjelinek commented 3 weeks ago

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 commented 2 weeks ago

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 commented 2 weeks ago

#!/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 commented 2 weeks ago

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

dveeden commented 2 weeks ago

I get the same issue even with the mysql client, I don't see documentation for DESCRIBEtable'%' -- 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. colname, 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 commented 2 weeks ago

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 commented 2 weeks ago

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 commented 2 weeks ago

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

dveeden commented 2 weeks ago

@johnjelinek could you provide any more details on this?

perl5-dbi / DBD-mysql