[TYPO3-dev] TYPO3 and MySQL Strict Mode

[Ries van Twisk]

On Jul 12, 2009, at 7:09 AM, Dmitry Dulepov wrote:

> Hi Ingo!
>
> Ingo Schmitt wrote:
>> we tried to install TYPO3 on a plain XAMP package on Windows. In this
>> Case MySQL 5.1.x is configured to run in MySQL Strict Mode
>> (http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html).
>>
>> We discovered some errors by generated SQL statments, where the field
>> type is not correct set, e.g. when an SQL integer is filled by a ''.
>
> Please, do not attach files here, especially TIFFs! Think how much  
> more traffic you generated now for TYPO3 servers. There are plenty  
> of free servers where you can put the image and send a link here.
>
>> I tracked this issue down, and dicovered that in this case the  
>> boolean
>> php Variable was passed to the TYPO3-DB exec_INSERT_query method.  
>> Here
>> automatically all fields are quoted.
>>
>> In my case we have disabled strict_mode, which solves this issue.
>> But I do see two things we should think about:
>>
>> (1) Passing Values to the SQL Query parser, where the values have not
>> correctly set to the SQL Fields (e.g. in this issue page_id as  
>> '3460')
>> causes load on the SQL Server and slows down the execution of the
>> statement.
>
> Integer fields can be quoted. This is not a error but a fully valid  
> SQL behavior. In your case this was not a cause of error. The image  
> says that 'temp_content' was invalid.



Dmitry,

in some databases and MySQL in strict mode an integer quoted as a  
empty string will not result in a 0 being added, but generates an error.

Besides, good practice will be to not quote numbers in general....  
(but this is out of scope of this mail).

This is done in a other DB to also demonstrate this:
testdb=# create table test (a integer);
CREATE TABLE
testdb =# insert into test (a) values('0');
INSERT 0 1
testdb =# insert into test (a) values('');
ERROR:  invalid input syntax for integer: ""




			regards, Ries van Twisk


-------------------------------------------------------------------------------------------------
Ries van Twisk
tags: Freelance TYPO3 Glassfish JasperReports JasperETL Flex Blaze-DS  
WebORB PostgreSQL DB-Architect
email: ries at vantwisk.nl   web:   http://www.rvantwisk.nl/
skype: callto://r.vantwisk   Phone: +1-810-476-4196
Cell: +593 9901 7694       SIP: +1-747-690-5133