On Tue, 13 Dec 2016 15:05:31 +0000
"Miethe, Martin" <mie...@studentenwerk-leipzig.de> wrote:

> I just spent some time trying to set up dynamic mysql tables. The log
> keeps saying:
> Dec 13 15:26:01 INFO ( out/mysql ): *** Purging cache - START (PID:
> 20506) *** Dec 13 15:26:01 ERROR ( out/mysql ): PRIMARY 'mysql'
> backend trouble. Dec 13 15:26:01 ERROR ( out/mysql ): The SQL server
> says: Table 'pmacct.acct_out_20161213-1525' doesn't exist
> The table 'pmacct.acct_out_20161213-1525' definitly exists - pmacct
> did succesfully create it using the sql_table_schema from my config.
> After some debugging I found that the problem is the "-" sign I am
> using for my table names, as it is considered as a special character
> for mysql. To use special characters in mysql you need to use the
> quote character - the backtick. 
> Its not a problem for me to go without these characters and its
> probably not a good practice to use them in first place. But still
> maybe its worth thinking about to wrap the dynamic sql statements
> generation into backticks to get around this problem if someone needs
> to use such characters? 

This is something that needs real thought put into it.  For example,
the SQL working draft of the 2003 Standard (which happens to be what 
I can get my hands on to easily without paying)

ISO/IEC 9075-2:2003 (E)
(ISO-ANSI Working Draft) Foundation (SQL/Foundation)
5.2 <token> and <separator>
page 134

says that the double quote character is supposed to be used to quote
identifiers, not the backtick.  So MySQL is "special" in this regard.
(And others.  MySQL brings out the cranky in me.  :)

I don't know what SQL 2011 says.

In PostgreSQL you do use double quotes to quote identifiers containing
special characters.  When you do so (and according to the SQL standard)
the result is case-sensitive.  But when you don't it's case-insensitive.
The PG docs say that PG is non-SQL conformant in that unquoted
identifiers are folded to lower case, but the standard says that
they should be folded to upper case.

In any event, indiscriminately quoting identifiers will lead to
case-insensitivity, or it should.  This can break existing setups.

My inclination is to leave well enough alone.  The SQL standard
is bad, allowing myriad variations, and SQL implementations are worse,
introducing yet more non-standard inconsistencies.  If there's a real
reason to get involved with SQL details that would be one thing, but
going there without a good reason sounds like trouble.  Let the people
using the various databases deal with the weirdness themselves.

All that said, wouldn't hurt to add some note regards the generated SQL
syntax somewhere in the docs.  Martin, if you have any suggestions
along these lines I'm sure Paolo would be happy to consider them.


Karl <k...@meme.com>
Free Software:  "You don't pay back, you pay forward."
                 -- Robert A. Heinlein

pmacct-discussion mailing list

Reply via email to