Please note that DBI is officially discouraging the use of semicolons at the end of statements. ¹

> Some command-line SQL tools use statement terminators, like a semicolon, to indicate the end of a statement. Such terminators should not normally be used with the DBI.

I personally never had problems with that in the past when interfacing with MariaDB, but your driver/engine constellation with MySQL² may be different and/or outdated.³

Other than that only the use of weird unicode characters/whitespace/line-endings comes to mind, but analyzing this would really require you to provide us with a real SSCCE.

¹) tybalt already mentioned that in the meantime, but I prepared this post already hours ago and the site was always unresponsive.

²) not the same!

³) or you have an obscure DELIMITER directive which redefines the terminator to something else

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.

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.

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...

Are you using question marks? You can't for identifiers (table, column, or database names).

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.

As a guess based on the "near" position given in the error messages, I'd try removing the semicolon from your SQL statement. i.e.

my $sqlcmd = "SELECT MAX($p_db_art_id) FROM $my_articletable";
[download]

I need the highest article ID in the column "db_art_id".

No, you don't. Or at least, you should not need it.

An ID column should be just that - an identifier, uniquely identifying a single record. It should have no other meaning, no other value, no ordering. It should not matter if the ID is a number or any other unique collection of bits. It should not matter how the ID is generated. The generator simply must never return an ID value more than once.

A common idea that I've heard by way too many database users in the last few decades is to get the maximum of an ID column, increment it by 1, and use that value as the ID value for the next record to be inserted - or worse - to predict the next ID that will be used by the database engine for that column. It sounds like it would work, and the most evil part of this idea is that it will actually work for a while. But then, parallel access and/or rollbacks will happen and things will go horribly wrong.

Yes, in theory, you really could get away with max(id_col)+1 - if you add a lot of locking and transaction handling to that. Things you can't do in SQL, and must do in your application. In other words: If you re-invent what has already been implemented in the database engine. And you can be very sure that the implementation in the database has been tested way better than anything you can come up with, including UUIDs. (And no, UUIDs aren't guaranteed to be unique, UUID collisions are just unlikely. The same is true for hash functions like the SHA family.)

See Re^2: Perl DBI and Foreign Keys, Re^6: Perl DBI and Foreign Keys, and SELECT LAST_INSERT_ID does not work for details.

Alexander

Footnote: It is sometimes really hard to make people not use max(id)+1. I had to threaten to change database sequences to begin at MAXINT and decrement by 3, 7 or 11 just to stop collegues from using max(id)+1.

Well, that's very opinionated take on what he's trying to accomplish. I have some "select max(id)" queries in production right now, for the purpose of monitoring whether a database has had any new records added to certain tables, and sets off alarms if they stop changing within a certain time window. It's a nice efficient query, and applicable to a very large percentage of table designs.

Similar in my case. I use PostgreSQL and a lot of tables have BIGSERIAL as primary key (bigint automatically generated by a sequence). Yes, seuqnces may have "holes" and are not always perfectly in time order (depending on the transactions that generated them), but a "ORDER BY article_id DESC LIMIT 10" gives a nice overview of the last vew additions to a table.

PerlMonks XP is useless? Not anymore: XPD - Do more with your PerlMonks XP
Also check out my sisters artwork and my weekly webcomics

To make sure that there is no permissions problem etc, I even let DBI log on the MySQL server as root.

Even without seeing the code which fails (which you should indeed have shown - please do) it is obvious that it is not a permissions problem but rather a syntax problem because that is what the error message helpfully tells you. :-)

See also: SSCCE.

Show the code that fails, not (just) the code that succeeds.

Basically the code that fails is this:

Sorry for late reply. It needs some luck and time to load the reply form without timing out and then successfully submit it.

Apparently people are so focused on code examples so that only @LanX and @ysth's replies did not completely miss the question. Actually I regret having edited the original post to add the suspected code, because it distracted from the question. But when some of the most respected forum users all demand to see code, it is sometimes not easy not to cave in.

The answer turned out to be this:

First I tried to find a method to log all DBI requests.

So I found the DBI::Log module, which unfortunately turned out dysfunctional (exiting the application after logging the first USE). However, its documentation hinted at an alternative: DBI Trace.

With DBI Trace it was easy as snap to find the actual offending command and its precise location, which was a different one than originally suspected.

This represents years of experience of being shown everything but the actual problem code :). And lots of experience with misuse of ids, too. So often people want to use id to indicate temporal order and as hosed when it turns out a partial outage left some inserts out of order or when they want to add more historical data, I totally agree with afoken, wanting the max id usually means you're painting yourself into a corner.

Glad you found DBI_TRACE and solved your problem. What was the "offending command" in the end?



The way forward always starts with a minimal test.

You say we were wrong to ask for an actual demonstration of the problem, but then you say we were spot-on in identifying that something was missing from your description of the problem. Despite your ingratitude, I'm glad I directed you to collect more information about the problem, which led you to look elsewhere for the actual problem. Oh the irony of you saying I missed the point.

> so that only @LanX and @ysth's replies did not completely miss the question.

Look, you literally said:

> > Basically the code that fails is this:

and many people spent a lot of time trying to help you "fixing" correct code.

And now you complain that we missed the point? So, what was the question?

I don't know what you expect from us, but mind-reading is probably better done at a circus.

Cheers Rolf
_{(addicted to the Perl Programming Language :)
see Wikisyntax for the Monastery}