I have upgraded from 2.0 to latest DEV and got this errors:

    * user warning: Table 'drupal6.date_format' doesn't exist query: SELECT df.dfid, df.format, df.type, df.locked, dfl.language FROM date_format df LEFT JOIN date_format_types dft ON df.type = dft.type LEFT JOIN date_format_locale dfl ON df.format = dfl.format AND df.type = dfl.type ORDER BY df.type, df.format in sites\all\modules\date\date_api.module on line 2000.
    * user warning: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'date_format (format, type, locked) VALUES ('Y-m-d H:i', 'short', 1)' at line 1 query: INSERT INTO date_format (format, type, locked) VALUES ('Y-m-d H:i', 'short', 1) in includes\common.inc on line 3422.
    * user warning: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'date_format (format, type, locked) VALUES ('m/d/Y - H:i', 'short', 1)' at line 1 query: INSERT INTO date_format (format, type, locked) VALUES ('m/d/Y - H:i', 'short', 1) in includes\common.inc on line 3422.
    * user warning: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'date_format (format, type, locked) VALUES ('d/m/Y - H:i', 'short', 1)' at line 1 query: INSERT INTO date_format (format, type, locked) VALUES ('d/m/Y - H:i', 'short', 1) in includes\common.inc on line 3422.
    * user warning: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'date_format (format, type, locked) VALUES ('Y/m/d - H:i', 'short', 1)' at line 1 query: INSERT INTO date_format (format, type, locked) VALUES ('Y/m/d - H:i', 'short', 1) in includes\common.inc on line 3422.
    * user warning: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'date_format (format, type, locked) VALUES ('d.m.Y - H:i', 'short', 1)' at line 1 query: INSERT INTO date_format (format, type, locked) VALUES ('d.m.Y - H:i', 'short', 1) in includes\common.inc on line 3422.
    * user warning: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'date_format (format, type, locked) VALUES ('m/d/Y - g:ia', 'short', 1)' at line 1 query: INSERT INTO date_format (format, type, locked) VALUES ('m/d/Y - g:ia', 'short', 1) in includes\common.inc on line 3422.
    * user warning: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'date_format (format, type, locked) VALUES ('d/m/Y - g:ia', 'short', 1)' at line 1 query: INSERT INTO date_format (format, type, locked) VALUES ('d/m/Y - g:ia', 'short', 1) in includes\common.inc on line 3422.
    * user warning: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'date_format (format, type, locked) VALUES ('Y/m/d - g:ia', 'short', 1)' at line 1 query: INSERT INTO date_format (format, type, locked) VALUES ('Y/m/d - g:ia', 'short', 1) in includes\common.inc on line 3422.
    * user warning: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'date_format (format, type, locked) VALUES ('M j Y - H:i', 'short', 1)' at line 1 query: INSERT INTO date_format (format, type, locked) VALUES ('M j Y - H:i', 'short', 1) in includes\common.inc on line 3422.
    * user warning: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'date_format (format, type, locked) VALUES ('j M Y - H:i', 'short', 1)' at line 1 query: INSERT INTO date_format (format, type, locked) VALUES ('j M Y - H:i', 'short', 1) in includes\common.inc on line 3422.
    * user warning: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'date_format (format, type, locked) VALUES ('Y M j - H:i', 'short', 1)' at line 1 query: INSERT INTO date_format (format, type, locked) VALUES ('Y M j - H:i', 'short', 1) in includes\common.inc on line 3422.
    * user warning: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'date_format (format, type, locked) VALUES ('M j Y - g:ia', 'short', 1)' at line 1 query: INSERT INTO date_format (format, type, locked) VALUES ('M j Y - g:ia', 'short', 1) in includes\common.inc on line 3422.
    * user warning: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'date_format (format, type, locked) VALUES ('j M Y - g:ia', 'short', 1)' at line 1 query: INSERT INTO date_format (format, type, locked) VALUES ('j M Y - g:ia', 'short', 1) in includes\common.inc on line 3422.
    * user warning: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'date_format (format, type, locked) VALUES ('Y M j - g:ia', 'short', 1)' at line 1 query: INSERT INTO date_format (format, type, locked) VALUES ('Y M j - g:ia', 'short', 1) in includes\common.inc on line 3422.
    * user warning: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'date_format (format, type, locked) VALUES ('D, Y-m-d H:i', 'medium', 1)' at line 1 query: INSERT INTO date_format (format, type, locked) VALUES ('D, Y-m-d H:i', 'medium', 1) in includes\common.inc on line 3422.
    * user warning: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'date_format (format, type, locked) VALUES ('D, m/d/Y - H:i', 'medium', 1)' at line 1 query: INSERT INTO date_format (format, type, locked) VALUES ('D, m/d/Y - H:i', 'medium', 1) in includes\common.inc on line 3422.
    * user warning: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'date_format (format, type, locked) VALUES ('D, d/m/Y - H:i', 'medium', 1)' at line 1 query: INSERT INTO date_format (format, type, locked) VALUES ('D, d/m/Y - H:i', 'medium', 1) in includes\common.inc on line 3422.
    * user warning: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'date_format (format, type, locked) VALUES ('D, Y/m/d - H:i', 'medium', 1)' at line 1 query: INSERT INTO date_format (format, type, locked) VALUES ('D, Y/m/d - H:i', 'medium', 1) in includes\common.inc on line 3422.
    * user warning: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'date_format (format, type, locked) VALUES ('F j, Y - H:i', 'medium', 1)' at line 1 query: INSERT INTO date_format (format, type, locked) VALUES ('F j, Y - H:i', 'medium', 1) in includes\common.inc on line 3422.
    * user warning: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'date_format (format, type, locked) VALUES ('j F, Y - H:i', 'medium', 1)' at line 1 query: INSERT INTO date_format (format, type, locked) VALUES ('j F, Y - H:i', 'medium', 1) in includes\common.inc on line 3422.
    * user warning: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'date_format (format, type, locked) VALUES ('Y, F j - H:i', 'medium', 1)' at line 1 query: INSERT INTO date_format (format, type, locked) VALUES ('Y, F j - H:i', 'medium', 1) in includes\common.inc on line 3422.
    * user warning: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'date_format (format, type, locked) VALUES ('D, m/d/Y - g:ia', 'medium', 1)' at line 1 query: INSERT INTO date_format (format, type, locked) VALUES ('D, m/d/Y - g:ia', 'medium', 1) in includes\common.inc on line 3422.
    * user warning: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'date_format (format, type, locked) VALUES ('D, d/m/Y - g:ia', 'medium', 1)' at line 1 query: INSERT INTO date_format (format, type, locked) VALUES ('D, d/m/Y - g:ia', 'medium', 1) in includes\common.inc on line 3422.
    * user warning: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'date_format (format, type, locked) VALUES ('D, Y/m/d - g:ia', 'medium', 1)' at line 1 query: INSERT INTO date_format (format, type, locked) VALUES ('D, Y/m/d - g:ia', 'medium', 1) in includes\common.inc on line 3422.
    * user warning: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'date_format (format, type, locked) VALUES ('F j, Y - g:ia', 'medium', 1)' at line 1 query: INSERT INTO date_format (format, type, locked) VALUES ('F j, Y - g:ia', 'medium', 1) in includes\common.inc on line 3422.
    * user warning: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'date_format (format, type, locked) VALUES ('j F Y - g:ia', 'medium', 1)' at line 1 query: INSERT INTO date_format (format, type, locked) VALUES ('j F Y - g:ia', 'medium', 1) in includes\common.inc on line 3422.
    * user warning: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'date_format (format, type, locked) VALUES ('Y, F j - g:ia', 'medium', 1)' at line 1 query: INSERT INTO date_format (format, type, locked) VALUES ('Y, F j - g:ia', 'medium', 1) in includes\common.inc on line 3422.
    * user warning: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'date_format (format, type, locked) VALUES ('j. F Y - G:i', 'medium', 1)' at line 1 query: INSERT INTO date_format (format, type, locked) VALUES ('j. F Y - G:i', 'medium', 1) in includes\common.inc on line 3422.
    * user warning: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'date_format (format, type, locked) VALUES ('l, F j, Y - H:i', 'long', 1)' at line 1 query: INSERT INTO date_format (format, type, locked) VALUES ('l, F j, Y - H:i', 'long', 1) in includes\common.inc on line 3422.
    * user warning: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'date_format (format, type, locked) VALUES ('l, j F, Y - H:i', 'long', 1)' at line 1 query: INSERT INTO date_format (format, type, locked) VALUES ('l, j F, Y - H:i', 'long', 1) in includes\common.inc on line 3422.
    * user warning: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'date_format (format, type, locked) VALUES ('l, Y, F j - H:i', 'long', 1)' at line 1 query: INSERT INTO date_format (format, type, locked) VALUES ('l, Y, F j - H:i', 'long', 1) in includes\common.inc on line 3422.
    * user warning: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'date_format (format, type, locked) VALUES ('l, F j, Y - g:ia', 'long', 1)' at line 1 query: INSERT INTO date_format (format, type, locked) VALUES ('l, F j, Y - g:ia', 'long', 1) in includes\common.inc on line 3422.
    * user warning: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'date_format (format, type, locked) VALUES ('l, j F Y - g:ia', 'long', 1)' at line 1 query: INSERT INTO date_format (format, type, locked) VALUES ('l, j F Y - g:ia', 'long', 1) in includes\common.inc on line 3422.
    * user warning: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'date_format (format, type, locked) VALUES ('l, Y, F j - g:ia', 'long', 1)' at line 1 query: INSERT INTO date_format (format, type, locked) VALUES ('l, Y, F j - g:ia', 'long', 1) in includes\common.inc on line 3422.
    * user warning: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'date_format (format, type, locked) VALUES ('l, j. F Y - G:i', 'long', 1)' at line 1 query: INSERT INTO date_format (format, type, locked) VALUES ('l, j. F Y - G:i', 'long', 1) in includes\common.inc on line 3422.
    * user warning: Table 'drupal6.date_format' doesn't exist query: SELECT df.dfid, df.format, df.type, df.locked, dfl.language FROM date_format df LEFT JOIN date_format_types dft ON df.type = dft.type LEFT JOIN date_format_locale dfl ON df.format = dfl.format AND df.type = dfl.type ORDER BY df.type, df.format in sites\all\modules\date\date_api.module on line 2000.
CommentFileSizeAuthor
#39 date_prevent_mysql_5_0_67_failures.patch5.95 KBhass
#34 date_api_table_check.patch1.11 KBquicksketch

Comments

hass’s picture

hass commented

date_format_locale and date_format_types tables are installed... hmmm.... !?!?

IcyAndy’s picture

IcyAndy commented
Priority: Critical » Normal

Changed to normal

Did you run update.php? I just upgraded to the 2.0 version and got the same error before and while running update.php for the first time. At the end it should give a hint that there are still updates missing. So you need to run update.php a second time where another date update is done. After that the error messages are gone and the calendar seems to work (at the first glance, not really tested).

hass’s picture

hass commented

It may was an update from DEV to DEV... I'm no more sure. update.php was executed.

karens’s picture

karens commented
Status: Active » Postponed (maintainer needs more info)

You got two tables installed but not the other one? No one else has reported that. Maybe some hiccup in your system? The code to install all three tables is in the same update. No one else has reported this, don't know what to suggest. Do you have a backup to go back to and try again?

You will have to run update more than once because they don't happen in the right order (the update to populate the tables will try to run before the tables are created).

hass’s picture

hass commented

I also taken a look to the update hook and saw this, but I can say for sure I only have two tables and the third one is missing and there where no errors at update. I have also uninstalled the module and all tables disappeared, than reinstalled and only two tables are now installed.

I'm running MySQL 5.0 in strict mode - maybe of interest.

karens’s picture

karens commented

Can you try re-running the update and see what happens? It's update 6002 for Date API and it should just give you an error that your tables already exist for the two that do. I'm curious whether you can force it to create the table that way or if there's something in the table creation code that is a problem.

guillaumeduveau’s picture

guillaumeduveau
Location Toulouse
commented

I had the same problem, upgrading from 6.x-2.0-rc6 to the latest dev (as I need a fix that is not in 6.x-2.0).
Running update.php multiple times didn't help.
I didn't dig though, as it was just on a local test site, I just desinstalled and reinstalled Date API and run the update without problems.

Moonshine’s picture

Moonshine commented

Howdy..

I think the problem here is with date_api's 6002 update. During the creation request for the date_format table, there is a key requested that exceeds MySQL's max key length (1000 bytes).

user warning: Specified key was too long; max key length is 1000 bytes query: CREATE TABLE date_format ( `dfid` INT unsigned NOT NULL auto_increment, `format` VARCHAR(255) NOT NULL, `type` VARCHAR(255) NOT NULL, `locked` TINYINT NOT NULL DEFAULT 0, PRIMARY KEY (dfid), UNIQUE KEY format (format, type) ) /*!40100 DEFAULT CHARACTER SET UTF8 */ in /var/www/html/drupal/drupal-6.10/includes/database.inc on line 515.

UTF8 characters can take up to 3 bytes each, so I *think MySQL is seeing format (255) + type (255) * 3 = 1530 bytes.

Also it looks like the definitions in the 6002 update section have different field sizes from what is defined in date_api_schema() (which look like they would work).

It *looks like these lengths are supposed to then be "corrected" in the 6003 update, but at least in the case of the format_date table, it won't exist because of the key problem previously.

Hope that makes sense...

Moonshine’s picture

Moonshine commented
Status: Postponed (maintainer needs more info) » Active

Oops.. forgot to change the status :/

karens’s picture

karens commented
Status: Active » Fixed

Ah! That makes sense. I've changed the update itself then to prevent future problems. Anyone with this problem can re-do the update from a backup (preferred solution) or as a fall-back re-run update 6002 to generate the tables (if you do the second one, you lost the Date module update to populate the tables with custom formats for your existing date fields and you'll have to edit each of your Date fields and set them up with the right formats).

Thanks!

hass’s picture

hass commented
Status: Fixed » Active

Repro:
1. Install Date API, nothing else.

Found this in my watchdog:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'date_format ( `dfid` INT unsigned NOT NULL auto_increment, `format` VARCHAR(100' at line 1 query: CREATE TABLE date_format ( `dfid` INT unsigned NOT NULL auto_increment, `format` VARCHAR(100) NOT NULL, `type` VARCHAR(200) NOT NULL, `locked` TINYINT NOT NULL DEFAULT 0, PRIMARY KEY (dfid), UNIQUE KEY format (format, type) ) /*!40100 DEFAULT CHARACTER SET UTF8 */ in C:\Inetpub\wwwroot\drupal6\includes\database.inc in Line 515.

Manually executing in MySQL Query I've found in the debug log of MySQL is:

CREATE TABLE date_format (
`dfid` INT unsigned NOT NULL auto_increment, 
`format` VARCHAR(100) NOT NULL, 
`type` VARCHAR(200) NOT NULL, 
`locked` TINYINT NOT NULL DEFAULT 0, 
PRIMARY KEY (dfid), 
UNIQUE KEY format (format, type)
)

gives me the error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'date_format (
`dfid` INT unsigned NOT NULL auto_increment, 
`format` VARCHAR(1' at line 1

So I tried to figure out what I need to change in the SQL statement to get this executed successfully and found:

CREATE TABLE `date_format` (
`dfid` INT unsigned NOT NULL auto_increment,
`format` VARCHAR(100) NOT NULL,
`type` VARCHAR(200) NOT NULL,
`locked` TINYINT NOT NULL DEFAULT 0,
PRIMARY KEY (dfid),
UNIQUE KEY `format` (format, type)
)

The only differences I made was adding the curly quotes to the unique key `format` and the table name `date_format`. So - I'm not yet sure what the real issue is, but potentially this could be reserved names!?

hass’s picture

hass commented

Cross posted, but seems not to be my issue... so active for now.

hass’s picture

hass commented

Also re-executed the update_6002 and got many INSERT errors, too.

user warning: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'date_format (format, type, locked) VALUES ('Y-m-d H:i', 'short', 1)' at line 1 query: INSERT INTO date_format (format, type, locked) VALUES ('Y-m-d H:i', 'short', 1) in C:\Inetpub\wwwroot\drupal6\includes\common.inc on line 3422.

Broken:

INSERT INTO date_format (format, type, locked) VALUES ('Y-m-d H:i', 'short', 1)

Works:

INSERT INTO `date_format` (format, type, locked) VALUES ('Y-m-d H:i', 'short', 1)

I expect that date_format IS a reserved name in mysql...

karens’s picture

karens commented

Shoot! Well this will be messy :(

hass’s picture

hass commented

Here is the bunch of SQL statements you need to fix this bug manually:

CREATE TABLE `date_format` (
`dfid` INT unsigned NOT NULL auto_increment,
`format` VARCHAR(100) NOT NULL,
`type` VARCHAR(200) NOT NULL,
`locked` TINYINT NOT NULL DEFAULT 0,
PRIMARY KEY (dfid),
UNIQUE KEY `format` (format, type)
);
INSERT INTO `date_format` (format, type, locked) VALUES ('Y-m-d H:i', 'short', 1);
INSERT INTO `date_format` (format, type, locked) VALUES ('m/d/Y - H:i', 'short', 1);
INSERT INTO `date_format` (format, type, locked) VALUES ('d/m/Y - H:i', 'short', 1);
INSERT INTO `date_format` (format, type, locked) VALUES ('Y/m/d - H:i', 'short', 1);
INSERT INTO `date_format` (format, type, locked) VALUES ('d.m.Y - H:i', 'short', 1);
INSERT INTO `date_format` (format, type, locked) VALUES ('m/d/Y - g:ia', 'short', 1);
INSERT INTO `date_format` (format, type, locked) VALUES ('d/m/Y - g:ia', 'short', 1);
INSERT INTO `date_format` (format, type, locked) VALUES ('Y/m/d - g:ia', 'short', 1);
INSERT INTO `date_format` (format, type, locked) VALUES ('M j Y - H:i', 'short', 1);
INSERT INTO `date_format` (format, type, locked) VALUES ('j M Y - H:i', 'short', 1);
INSERT INTO `date_format` (format, type, locked) VALUES ('Y M j - H:i', 'short', 1);
INSERT INTO `date_format` (format, type, locked) VALUES ('M j Y - g:ia', 'short', 1);
INSERT INTO `date_format` (format, type, locked) VALUES ('j M Y - g:ia', 'short', 1);
INSERT INTO `date_format` (format, type, locked) VALUES ('Y M j - g:ia', 'short', 1);
INSERT INTO `date_format` (format, type, locked) VALUES ('D, Y-m-d H:i', 'medium', 1);
INSERT INTO `date_format` (format, type, locked) VALUES ('D, m/d/Y - H:i', 'medium', 1);
INSERT INTO `date_format` (format, type, locked) VALUES ('D, d/m/Y - H:i', 'medium', 1);
INSERT INTO `date_format` (format, type, locked) VALUES ('D, Y/m/d - H:i', 'medium', 1);
INSERT INTO `date_format` (format, type, locked) VALUES ('F j, Y - H:i', 'medium', 1);
INSERT INTO `date_format` (format, type, locked) VALUES ('j F, Y - H:i', 'medium', 1);
INSERT INTO `date_format` (format, type, locked) VALUES ('Y, F j - H:i', 'medium', 1);
INSERT INTO `date_format` (format, type, locked) VALUES ('D, m/d/Y - g:ia', 'medium', 1);
INSERT INTO `date_format` (format, type, locked) VALUES ('D, d/m/Y - g:ia', 'medium', 1);
INSERT INTO `date_format` (format, type, locked) VALUES ('D, Y/m/d - g:ia', 'medium', 1);
INSERT INTO `date_format` (format, type, locked) VALUES ('F j, Y - g:ia', 'medium', 1);
INSERT INTO `date_format` (format, type, locked) VALUES ('j F Y - g:ia', 'medium', 1);
INSERT INTO `date_format` (format, type, locked) VALUES ('Y, F j - g:ia', 'medium', 1);
INSERT INTO `date_format` (format, type, locked) VALUES ('j. F Y - G:i', 'medium', 1);
INSERT INTO `date_format` (format, type, locked) VALUES ('l, F j, Y - H:i', 'long', 1);
INSERT INTO `date_format` (format, type, locked) VALUES ('l, j F, Y - H:i', 'long', 1);
INSERT INTO `date_format` (format, type, locked) VALUES ('l, Y, F j - H:i', 'long', 1);
INSERT INTO `date_format` (format, type, locked) VALUES ('l, F j, Y - g:ia', 'long', 1);
INSERT INTO `date_format` (format, type, locked) VALUES ('l, j F Y - g:ia', 'long', 1);
INSERT INTO `date_format` (format, type, locked) VALUES ('l, Y, F j - g:ia', 'long', 1);
INSERT INTO `date_format` (format, type, locked) VALUES ('l, j. F Y - G:i', 'long', 1);
karens’s picture

karens commented

It's not a reserved word (http://www.petefreitag.com/tools/sql_reserved_words_checker/?word=date_f... and http://dev.mysql.com/doc/refman/5.0/en/reserved-words.html). But obviously it is not working right.

hass’s picture

hass commented

Karen: as I remember - I have opened a core bug case long time ago about the UNIQUE KEY need to have curly quotes around... The issue seems partly to be the same like my #354287: db_change_field() cannot rename reserved name columns...

karens’s picture

karens commented

There's a bigger problem. In D7 we use PDO and I have no idea what will happen there. Even if adding the quotes works for D6 I don't know that this will work in D7. I'm starting to think we just need to rename the table.

hass’s picture

hass commented

MySQL is 5.0.67-community-nt

hass’s picture

hass commented

Isn't this not more a core DB layer bug?

hass’s picture

hass commented

Crazy... I changed

1. the key name only to "formats", executed, failed
2. the table name only to "date_formats", executed, failed
3. both key to "formats" and table to "date_formats", executed, success

All without curly quotes...

CREATE TABLE date_formats (
`dfid` INT unsigned NOT NULL auto_increment,
`format` VARCHAR(100) NOT NULL,
`type` VARCHAR(200) NOT NULL,
`locked` TINYINT NOT NULL DEFAULT 0,
PRIMARY KEY (dfid),
UNIQUE KEY formats (format, type)
)

Now I'm upgrading to MySQL 5.0.77 and give it a new try.

hass’s picture

hass commented

*DAMN* - MySQL 5.0.77 works.

We should we figure out what versions are broken and add a hook_requirement with MySQL Version checking...

karens’s picture

karens commented

I don't want to add a hook requirements because I need this to work in all versions. And I don't want to use curly braces because I don't know that this will work in D7. So I think that leaves the only option is to change the name of the table and index.

I pinged stella, who created the original patch, so see if she has other ideas, but I think that is going to be the necessary solution.

karens’s picture

karens commented
Priority: Normal » Critical

Upgrading the status.

hass’s picture

hass commented
Priority: Critical » Normal

Hm... I hate changing module code for mysql server bugs that have been fixed in an official release... sounds strange to me. I would add a incompatibility note somewhere.

hass’s picture

hass commented
Priority: Normal » Critical
karens’s picture

karens commented

Well what MYSQL versions are 'officially' supported by Drupal? Whatever they are, I need to support. Aren't we still supporting MYSQL 4.1 in D6? Who knows what happens there. Are you sure this was a bugfix? It worries me a bit to make assumptions about MYSQL versions.

hass’s picture

hass commented

@Karen: there is no need to add curly brackets... only a need not to use at least MySQL 5.0.67... and 5.0.77 works.

hass’s picture

hass commented

I'm not yet sure which bug is the source of this issue and what versions are affected, but this bug sounds very similar:

The mysql client incorrectly parsed statements containing the word “delimiter” in mid-statement.

This fix is different from the one applied for this bug in MySQL 5.0.66. (Bug#33812)

See also Bug#38158.

From the bug case:

mysql> create table t1 (f1_delimiter int);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near '' at line 1
hass’s picture

hass commented

I leave this up to you to rename or not. Support wise it may be much better to rename...

Also checked on a SuSE 10.3 with MySQL 5.0.45 and all WFM.

karens’s picture

karens commented

My sites are using MYSQL 5.0.51a and they are on a shared host where I cannot upgrade the version, so if I added a requirement for 5.0.67 I could not use the module myself. :-)

Plus we don't have any information about what happens in 4.1 (which should still be supported in D6) or in Postgres.

stella’s picture

stella commented

Hmmm that's annoying. I think the easiest thing is to rename the table and key names. We'll need to change the hook_schema(), the 6002 update function, and also provide another update function to change the table name for those people who have already successfully installed it. Not to mention update the code everywhere. I'll have a look at doing it tomorrow.

As hass suggested in #21, I think changing the table name to 'date_formats' and the key to 'formats' would be ok.

hass’s picture

hass commented

No I would only add a requirement with ERROR if the version equals to 5.0.67. So you are able to use your module :-)

quicksketch’s picture

quicksketch
he/him
commented
StatusFileSize
new date_api_table_check.patch1.11 KB

In my experience, the entire upgrade works out fine, it just throws several dozen errors in the process. In a full upgrade from Drupal 5 to Drupal 6, I have to run update.php 3 times after the initial system update to get the date.module to finally run its update 6005. The wall of errors is indeed very daunting, but in the end all the needed tables are created, seemingly with all the necessary data.

Most of the errors are caused by cache clearing, which in turn asks date_api for a fresh list of date_format and date_format_types data. Since these tables don't exist yet we end up with buckets of errors. Here's a patch that short-circuits these queries and returns empty lists if we're in the middle of doing updates, but keeps the errors for naughty users that are running with an out-of-date schema. This patch does not address the apparently reserved-wordiness of "date_format".

hass’s picture

hass commented
Title: Table 'drupal6.date_format' doesn't exist » MySQL 5.0.67: Table 'drupal6.date_format' not created

but in the end all the needed tables are created, seemingly with all the necessary data.

This is incorrect, see above.

karens’s picture

karens commented

I asked quicksketch to make a patch for the errors, which are pretty disconcerting. That doesn't fix the issue of situations where the table is not created. So I'm going to handle this in a couple steps.

1) Apply the patch to make the error messages less annoying.
2) Create a new release with that patch, which will take care of all situations but the few that do not get the table created and fix several critical errors that have popped up since the last release.
3) Add an update to change the table name, a step I think we will have to take for the unknown number of situations where the table isn't created correctly.

That update needs to be tested before going into a release, which is the reason for creating a release before making that change. Then the change can live in -dev so we have a chance to iron the kinks out. Once it seems to be working well, I'll roll a new release with the table name change.

quicksketch’s picture

quicksketch
he/him
commented

hass: I should have said, "if not using mySQL 5.0.67, all the needed tables are created, seemingly with all the necessary data." Like I said in the end of my post, the patch doesn't address the reserved word issue, but it does significantly reduce the number of errors for all other users.

hitinternet’s picture

hitinternet commented

Just to chip in, I have had these problems but when I tried to run update, the Date update didn't work because it said I have to update Views first. I don't even have Views installed!

Date version: Version 6.2-dev
MySQL: 5.0.51a

hass’s picture

hass commented
Status: Active » Needs review
StatusFileSize
new date_prevent_mysql_5_0_67_failures.patch5.95 KB

d.o eated my comment...

Patch attached. I have not yet been able to test it myself, but code wise it should be ok. I'm not 100% sure about date_formats_rebuild()... is this function installing the default date_formats table entries? Do we need to execute something else?

karens’s picture

karens commented

I just committed quicksketch's patch to hide errors during this process. I also made a change to abort the update of the Date module formats if date_format doesn't exist (it was only checking for date_format_types and still getting triggered when date_format was missing). That should set the stage for a new release.

After that I will look at #39 and see if there is anything else needed.

karens’s picture

karens commented
Status: Needs review » Fixed

I'm committing this now, since I also have to commit another fix to these tables, #483682: Add custom format case insensitive.

Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for 2 weeks with no activity.

ggevalt’s picture

ggevalt commented
Status: Closed (fixed) » Active

I am not sure why this issue was closed. So I reopened it. I may be breaking protocol and if I am, I'm sorry; but I'm doing so because the issue seems to be quite alive....

I, too, had a working Drupal 6 site that now has the date_formats doesn't exist warning after updating my Date module and I can't a) make it go away and b) have no idea what to do about it. It seems from this thread that there was a flurry of activity, a recognition that this was a serious problem but then it falls off the cliff. Please do not misunderstand; my tone is not anger but confusion. Have you figured out what's wrong and how to fix it? Should I revert to an earlier version of the module?

If you have, I would greatly appreciate an update here. And, to stress this point, I am greatly appreciative of all your work on this. For us out in the hinterlands, you do much to help make our lives easier with using Drupal and these modules, so thank you.

geoff gevalt

stella’s picture

stella commented

@ggevalt: what version of date are you using? I think this was already fixed. Did you run update.php after upgrading the module?

rjbrown99’s picture

rjbrown99 commented

I had the same problem today moving to the latest release of date. A check of my web error logs showed this:

[Sun Aug 02 17:05:25 2009] [error] [client 1.2.3.4] ModSecurity: Access denied with code 501 (phase 2). Match of "rx (?:^(?:application\\\\/x-www-form-urlencoded(?:;(?:\\\\s?charset\\\\s?=\\\\s?[\\\\w\\\\d\\\\-]{1,18})?)??$|multipart/form-data;)|text/xml)" against "REQUEST_HEADERS:Content-Type" required. [file "/my/path/etc/httpd/modsecurity.d/modsecurity_crs_30_http_policy.conf"] [line "69"] [id "960010"] [msg "Request content type is not allowed by policy"] [severity "WARNING"] [tag "POLICY/ENCODING_NOT_ALLOWED"] [hostname "mysitename.com"] [uri "/update.php"] [unique_id "SnYpxQrQKjEAAHMPWngAAAAD"]

Bad news, right? This is on Fedora, which stores local Modsecurity rules in /etc/httpd/modsecurity.d/modsecurity_localrules.conf. I added the following to that file to ignore update.php:

<LocationMatch "/update.php">
SecRuleEngine off
</LocationMatch>

I restarted httpd, and that particular issue went away.

I then went back to update.php and did a stepped update of the date API. That means heading to update.php, continue, select versions, and then changing the date_api module to 6002, doing that process again for 6003, 6004, and 6005. I have no idea if this is correct or not or what the ramifications are on a production site, but by the end of it I was fully up to date with no more errors or warnings from update.php.

houstonaplus’s picture

houstonaplus commented

getting the same error too.

hass’s picture

hass commented
Status: Active » Closed (fixed)

The DB table/index naming issue was fixed months ago.