MySQL Error: Template Insert

[David M. Graham]

For some time now, people have been noticing a serious MySQL error in the template installation routine within the Template Configuration / Administration tool in the CRE Loaded admin.  It presents as a MySQL 1364 error when you try to load a new template like this:

Code: [Select]

1364 - Field 'edit_customer_greeting_personal' doesn't have a default value

insert into template (template_name, date_added) values ('TEST-ME', now())


There has been no response to these reports (now 3 pages of posts) by the CRE Loaded staff, but plenty of input from folks who are obviously trying to resolve the issue on their own.

Even if CRE staff had responded, I don't think they would have had much to say -- my contact there tell me that they had not encountered the issue themselves.  I did, yesterday, and it was an illuminating experience. 

It happened while working on a local development server with MySQL 5 set in Strict Mode.   I found a number of helpful links which I Googled the issue.  Applying suggested solutions such as assuring all fields were populated during an INSERT operation did help  a bit - but led to a new 1366 error.  Eventually, I found the MySQL Strict mode connection, reconfigured the MySQL server and everything resolved.

So - the quick solution for this issue is just not to use MySQL 5 strict mode.

However, this does pose some interesting questions that should be investigated in order to assure the quality of future developments.

The biggest one is,  why does MySQL complain of a missing default value for a text field which, by their own standards, can't have one??

Another important question is the extent to which the issue occurs in the various osCommerce code bases.

Related pages can be found at:

https://www.creloaded.com/forums/Forums/viewtopic/t=12529/postdays=0/postorder=asc/start=30.html  (CRE Loaded Forum posts about this specific issue).

http://forums.oscommerce.com/index.php?showtopic=225371  (osCommerce forum post reporting similar issue in shopping cart: 1364 - Field 'final_price' doesn't have a default value) No resolution, but interesting in terms of extent of the issue.

http://forums.oscommerce.com/index.php?showtopic=287657 (osCommerce forum post about the 1366 error which finally brought the MySQL 5 Strict Mode connection to light.  Thanks to Jan Zonjee (http://forums.oscommerce.com/index.php?showuser=39592) for bringing this up, and Robert Fisher (http://forums.oscommerce.com/index.php?showuser=68771) for noting that osCommerce developers used sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,TRADITIONAL,STRICT_ALL_TABLES" routinely to avoid this issue. 

Why this does not seem to be documented in the programming standards at http://svn.oscommerce.com/confluence/display/OSCDEV/3.2.+Coding+Standards  would also seem to be an important question - but the stale state of osCommerce develpment rather explains that.

David