Re: Need advice how to diagnose the problem when syntax using MAX() is correct according to MySQL monitor, but errors out in DBI

From the little bit you've shown us, << the right syntax to use near '"articles_table"' >> would indicate that your MySQL connection via DBI has not enabled any of the modern defaults. The original identifier-quoting character for MySQL is backquote ` and it treats double quotes as string literals. You have to run statements at startup to get your connection to be more standard compliant. I have this DBI connection config in all my apps:

RaiseError => 1,
AutoCommit => 1,
ShowErrorStatement => 1,
quote_names => 1,
quote_identifiers => 1,
mysql_enable_utf8 => 1,
on_connect_call => 'set_strict_mode',
[download]

Without that last 'set_strict_mode', mysql does all sorts of horrible things like silently truncating strings or inserting zeroes into datetime fields. I think it also controls whether the identifier-quoting character is MySQL's backquote or the SQL standard doublequote.

It does what you expect in your database browser app probably because that app initializes the connection differently.

Comment on Re: Need advice how to diagnose the problem when syntax using MAX() is correct according to MySQL monitor, but errors out in DBI Select or Download Code

Replies are listed 'Best First'.
Re^2: Need advice how to diagnose the problem when syntax using MAX() is correct according to MySQL monitor, but errors out in DBI by ysth (Canon) on Nov 23, 2025 at 02:10 UTC
I think that set_strict_mode is a DBIx::Class thing, not vanilla DBD::mysql. Though the underlying sql_mode settings can certainly be set either way. And I wouldn't conceptually lump compatibility settings like handling of double quotes with actual strict modes, which should definitely be used in all new code.	[reply]
Re^2: Need advice how to diagnose the problem when syntax using MAX() is correct according to MySQL monitor, but errors out in DBI by dissident (Beadle) on Nov 23, 2025 at 15:22 UTC
This is very interesting, strict mode and quoting names/identifiers. I clearly do not want what you described, truncating strings or inserting zeroes in place of formatted data. Now I am asking myself, is it better to add backticks for identifiers? Maybe these can help prevent the behaviour you described? Regarding the other data... iirc the DBI documentation says one should not put the question marks into quotes or ticks...	[reply]
Re^3: Need advice how to diagnose the problem when syntax using MAX() is correct according to MySQL monitor, but errors out in DBI by ysth (Canon) on Nov 23, 2025 at 17:18 UTC
Are you using question marks? You can't for identifiers (table, column, or database names).	[reply]
Re^3: Need advice how to diagnose the problem when syntax using MAX() is correct according to MySQL monitor, but errors out in DBI by ysth (Canon) on Nov 23, 2025 at 17:49 UTC
Note that MySQL has had a decent set of defaults since 5.5. The non-strictness mentioned mostly just applies if you have config files preserving the sql_mode from earlier versions.	[reply]