SQL error on creation of core tables? [#60983]

Upon creation of the tables, via phpMyAdmin, and continuing after 3 attempts, the core file 'database.4.1.mysql' throws an SQL error, reported as so:

-- $Id: database.4.1.mysql,v 1.1 2006/04/10 13:45:28 unconed Exp $
--
-- Table structure for table 'access'

and further described as so:

#1064 - 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 'DEFAULT CHARACTER SET utf8' at line 13

This prevents the continued installation and use of Drupal 4.7RC4 completely, as the proper tables are not created.

The same is repeatedly generated on successive attempts to install.

Comments

Comment #1

killes@www.drop.org commented 29 April 2006 at 15:06

Are you sure you are running mysql 4.1? Try the file for 4.0.

Comment #2

lanesharon commented 30 April 2006 at 14:32

try dowloading the zip file again. Drop all the tables in your database (through phpmyadmin). And enter the SQL again, rebuilding the dropped tables. I had a bad download on RC4. You may have also.

Comment #3

kbahey commented 1 May 2006 at 00:37

I don't think this is a real issue.

I just tried it with a fresh install of RC4, and did not have this issue on MySQL 4.1 and PHP 4.4 and Linux.

Druppy, what is your environment exactly (operating system, PHP version, MySQL version)

Comment #4

Zen commented 1 May 2006 at 13:09

Category:	bug	» support
Priority:	Critical	» Normal

Moving to support until problem can be verified.

-K

Comment #5

pfaocle

He/Him

English

commented 4 May 2006 at 18:17

Title:	4.7-RC4 SQL ERROR on CREATION OF NEW BASE TABLES	» SQL error on creation of core tables?
Version:	4.7.0-rc4	» 4.7.0

Needs to be confirmed for 4.7.0.

I haven't had a problem using MySQL 4.1 and the appropriate ...41.mysql file.

Comment #6

Celestina commented 11 May 2006 at 06:58

Category:

support

» bug

I found this page by searching for the very error code you show in your posts. I got this error while trying to set up the Drupal MySQL database. I tried importing both the 4.0 and 4.1 sql files (in separate instances with a fresh, empty database), and neither one worked. The 4.1 gives me the error listed here, and the 4.0 tells me there is an error in my syntax.

Here is my system info:

Operating system Linux
Kernel version 2.4.21-37.0.1.ELsmp
Machine Type i686
Apache version 1.3.34 (Unix)
PERL version 5.8.4
PHP version 4.4.1
MySQL version 4.0.25-standard
cPanel Build 10.8.1-STABLE 114

Comment #7

Celestina commented 11 May 2006 at 07:00

Sorry - I forgot to mention that I'm using the release version of 4.7.0

Comment #8

Zen commented 11 May 2006 at 07:31

Component:

base system

» database system

What was the syntax error with 4.0?

-K

Comment #9

Celestina commented 11 May 2006 at 15:47

I was unable to reproduce the syntax error with the 4.0 sql file import, although it did happen twice before. However, I now get this when I go to my site in a browser:

Warning: Table 'username_database.tableprefixsessions' doesn't exist query: SELECT sid FROM tableprefixsessions WHERE sid = 'a8937d36a1ccfdd538c70922c32f1db6' in /home/username/public_html/includes/database.mysql.inc on line 120

Warning: Table 'username_database.tableprefixusers' doesn't exist query: SELECT u.* FROM tableprefixusers u WHERE u.uid = 0 in /home/username/public_html/includes/database.mysql.inc on line 120

Warning: session_start(): Cannot send session cookie - headers already sent by (output started at /home/username/public_html/includes/database.mysql.inc:120) in /home/username/public_html/includes/bootstrap.inc on line 747

Warning: session_start(): Cannot send session cache limiter - headers already sent (output started at /home/username/public_html/includes/database.mysql.inc:120) in /home/username/public_html/includes/bootstrap.inc on line 747

Warning: Table 'username_database.tableprefixaccess' doesn't exist query: SELECT * FROM tableprefixaccess WHERE status = 1 AND type = 'host' AND LOWER('70.249.43.246') LIKE LOWER(mask) in /home/username/public_html/includes/database.mysql.inc on line 120

Warning: Table 'username_database.tableprefixaccess' doesn't exist query: SELECT * FROM tableprefixaccess WHERE status = 0 AND type = 'host' AND LOWER('70.249.43.246') LIKE LOWER(mask) in /home/username/public_html/includes/database.mysql.inc on line 120

Warning: Table 'username_database.tableprefixcache' doesn't exist query: SELECT data, created, headers, expire FROM tableprefixcache WHERE cid = 'variables' in /home/username/public_html/includes/database.mysql.inc on line 120

Warning: Table 'username_database.tableprefixvariable' doesn't exist query: SELECT * FROM tableprefixvariable in /home/username/public_html/includes/database.mysql.inc on line 120

Warning: Table 'username_database.tableprefixcache' doesn't exist query: LOCK TABLES tableprefixcache WRITE in /home/username/public_html/includes/database.mysql.inc on line 120

Warning: Table 'username_database.tableprefixcache' doesn't exist query: UPDATE tableprefixcache SET data = 'N;', created = 1147361784, expire = 0, headers = '' WHERE cid = 'variables' in /home/username/public_html/includes/database.mysql.inc on line 120

Warning: Table 'username_database.tableprefixurl_alias' doesn't exist query: SELECT COUNT(pid) FROM tableprefixurl_alias in /home/username/public_html/includes/database.mysql.inc on line 120

Warning: Table 'username_database.tableprefixwatchdog' doesn't exist query: INSERT INTO tableprefixwatchdog (uid, type, message, severity, link, location, referer, hostname, timestamp) VALUES (0, 'php', 'Cannot modify header information - headers already sent by (output started at /home/username/public_html/includes/database.mysql.inc:120) in /home/username/public_html/includes/common.inc on line 139.', 2, '', 'http://www.cosmichammer.com/index_old.php', '', '70.249.43.246', 1147361784) in /home/username/public_html/includes/database.mysql.inc on line 120

Warning: Table 'username_database.tableprefixwatchdog' doesn't exist query: INSERT INTO tableprefixwatchdog (uid, type, message, severity, link, location, referer, hostname, timestamp) VALUES (0, 'php', 'Table &#039;username_database.tableprefixsystem&#039; doesn&#039;t exist\nquery: SELECT name, filename, throttle, bootstrap FROM tableprefixsystem WHERE type = &#039;module&#039; AND status = 1 ORDER BY weight ASC, filename ASC in /home/username/public_html/includes/database.mysql.inc on line 120.', 2, '', 'http://www.cosmichammer.com/index_old.php', '', '70.249.43.246', 1147361784) in /home/username/public_html/includes/database.mysql.inc on line 120

Warning: Table 'username_database.tableprefixwatchdog' doesn't exist query: INSERT INTO tableprefixwatchdog (uid, type, message, severity, link, location, referer, hostname, timestamp) VALUES (0, 'php', 'Table &#039;username_database.tableprefixsystem&#039; doesn&#039;t exist\nquery: SELECT filename FROM tableprefixsystem WHERE name = &#039;filter&#039; AND type = &#039;module&#039; in /home/username/public_html/includes/database.mysql.inc on line 120.', 2, '', 'http://www.cosmichammer.com/index_old.php', '', '70.249.43.246', 1147361784) in /home/username/public_html/includes/database.mysql.inc on line 120

Warning: Table 'username_database.tableprefixwatchdog' doesn't exist query: INSERT INTO tableprefixwatchdog (uid, type, message, severity, link, location, referer, hostname, timestamp) VALUES (0, 'php', 'Table &#039;username_database.tableprefixsystem&#039; doesn&#039;t exist\nquery: SELECT filename FROM tableprefixsystem WHERE name = &#039;node&#039; AND type = &#039;module&#039; in /home/username/public_html/includes/database.mysql.inc on line 120.', 2, '', 'http://www.cosmichammer.com/index_old.php', '', '70.249.43.246', 1147361784) in /home/username/public_html/includes/database.mysql.inc on line 120

Warning: Table 'username_database.tableprefixwatchdog' doesn't exist query: INSERT INTO tableprefixwatchdog (uid, type, message, severity, link, location, referer, hostname, timestamp) VALUES (0, 'php', 'Table &#039;username_database.tableprefixsystem&#039; doesn&#039;t exist\nquery: SELECT filename FROM tableprefixsystem WHERE name = &#039;system&#039; AND type = &#039;module&#039; in /home/username/public_html/includes/database.mysql.inc on line 120.', 2, '', 'http://www.cosmichammer.com/index_old.php', '', '70.249.43.246', 1147361784) in /home/username/public_html/includes/database.mysql.inc on line 120

Warning: Table 'username_database.tableprefixwatchdog' doesn't exist query: INSERT INTO tableprefixwatchdog (uid, type, message, severity, link, location, referer, hostname, timestamp) VALUES (0, 'php', 'Table &#039;username_database.tableprefixsystem&#039; doesn&#039;t exist\nquery: SELECT filename FROM tableprefixsystem WHERE name = &#039;user&#039; AND type = &#039;module&#039; in /home/username/public_html/includes/database.mysql.inc on line 120.', 2, '', 'http://www.cosmichammer.com/index_old.php', '', '70.249.43.246', 1147361784) in /home/username/public_html/includes/database.mysql.inc on line 120

Warning: Table 'username_database.tableprefixwatchdog' doesn't exist query: INSERT INTO tableprefixwatchdog (uid, type, message, severity, link, location, referer, hostname, timestamp) VALUES (0, 'php', 'Table &#039;username_database.tableprefixsystem&#039; doesn&#039;t exist\nquery: SELECT filename FROM tableprefixsystem WHERE name = &#039;watchdog&#039; AND type = &#039;module&#039; in /home/username/public_html/includes/database.mysql.inc on line 120.', 2, '', 'http://www.cosmichammer.com/index_old.php', '', '70.249.43.246', 1147361784) in /home/username/public_html/includes/database.mysql.inc on line 120

Warning: Table 'username_database.tableprefixwatchdog' doesn't exist query: INSERT INTO tableprefixwatchdog (uid, type, message, severity, link, location, referer, hostname, timestamp) VALUES (0, 'php', 'Table &#039;username_database.tableprefixcache&#039; doesn&#039;t exist\nquery: SELECT data, created, headers, expire FROM tableprefixcache WHERE cid = &#039;menu::en&#039; in /home/username/public_html/includes/database.mysql.inc on line 120.', 2, '', 'http://www.cosmichammer.com/index_old.php', '', '70.249.43.246', 1147361784) in /home/username/public_html/includes/database.mysql.inc on line 120

Warning: Table 'username_database.tableprefixwatchdog' doesn't exist query: INSERT INTO tableprefixwatchdog (uid, type, message, severity, link, location, referer, hostname, timestamp) VALUES (0, 'php', 'Table &#039;username_database.tableprefixrole&#039; doesn&#039;t exist\nquery: SELECT DISTINCT(p.perm) FROM tableprefixrole r INNER JOIN tableprefixpermission p ON p.rid = r.rid WHERE r.rid IN (1) in /home/username/public_html/includes/database.mysql.inc on line 120.', 2, '', 'http://www.cosmichammer.com/index_old.php', '', '70.249.43.246', 1147361784) in /home/username/public_html/includes/database.mysql.inc on line 120

Warning: Table 'username_database.tableprefixwatchdog' doesn't exist query: INSERT INTO tableprefixwatchdog (uid, type, message, severity, link, location, referer, hostname, timestamp) VALUES (0, 'php', 'Table &#039;username_database.tableprefixsystem&#039; doesn&#039;t exist\nquery: SELECT * FROM tableprefixsystem WHERE type = &#039;theme&#039; in /home/username/public_html/includes/database.mysql.inc on line 120.', 2, '', 'http://www.cosmichammer.com/index_old.php', '', '70.249.43.246', 1147361784) in /home/username/public_html/includes/database.mysql.inc on line 120

Warning: Table 'username_database.tableprefixwatchdog' doesn't exist query: INSERT INTO tableprefixwatchdog (uid, type, message, severity, link, location, referer, hostname, timestamp) VALUES (0, 'php', 'Table &#039;username_database.tableprefixcache&#039; doesn&#039;t exist\nquery: LOCK TABLES tableprefixcache WRITE in /home/username/public_html/includes/database.mysql.inc on line 120.', 2, '', 'http://www.cosmichammer.com/index_old.php', '', '70.249.43.246', 1147361784) in /home/username/public_html/includes/database.mysql.inc on line 120

Warning: Table 'username_database.tableprefixwatchdog' doesn't exist query: INSERT INTO tableprefixwatchdog (uid, type, message, severity, link, location, referer, hostname, timestamp) VALUES (0, 'php', 'Table &#039;username_database.tableprefixcache&#039; doesn&#039;t exist\nquery: UPDATE tableprefixcache SET data = &#039;a:4:{s:10:\\&quot;path index\\&quot;;a:44:{s:13:\\&quot;admin/filters\\&quot;;i:-1;s:18:\\&quot;admin/filters/list\\&quot;;i:-2;s:17:\\&quot;admin/filters/add\\&quot;;i:-3;s:20:\\&quot;admin/filters/delete\\&quot;;i:-4;s:11:\\&quot;filter/tips\\&quot;;i:-5;s:10:\\&quot;admin/node\\&quot;;i:-6;s:19:\\&quot;admin/node/overview\\&quot;;i:-7;s:19:\\&quot;admin/settings/node\\&quot;;i:-8;s:28:\\&quot;admin/settings/content-types\\&quot;;i:-9;s:4:\\&quot;node\\&quot;;i:-10;s:8:\\&quot;node/add\\&quot;;i:-11;s:7:\\&quot;rss.xml\\&quot;;i:-12;s:12:\\&quot;system/f in /home/username/public_html/includes/database.mysql.inc on line 120

Warning: Table 'username_database.tableprefixwatchdog' doesn't exist query: INSERT INTO tableprefixwatchdog (uid, type, message, severity, link, location, referer, hostname, timestamp) VALUES (0, 'php', 'Cannot modify header information - headers already sent by (output started at /home/username/public_html/includes/database.mysql.inc:120) in /home/username/public_html/includes/common.inc on line 139.', 2, '', 'http://www.cosmichammer.com/index_old.php', '', '70.249.43.246', 1147361784) in /home/username/public_html/includes/database.mysql.inc on line 120

Warning: Table 'username_database.tableprefixwatchdog' doesn't exist query: INSERT INTO tableprefixwatchdog (uid, type, message, severity, link, location, referer, hostname, timestamp) VALUES (0, 'php', 'Table &#039;username_database.tableprefixsystem&#039; doesn&#039;t exist\nquery: SELECT * FROM tableprefixsystem WHERE type = &#039;theme&#039; in /home/username/public_html/includes/database.mysql.inc on line 120.', 2, '', 'http://www.cosmichammer.com/index_old.php', '', '70.249.43.246', 1147361784) in /home/username/public_html/includes/database.mysql.inc on line 120

Warning: Table 'username_database.tableprefixwatchdog' doesn't exist query: INSERT INTO tableprefixwatchdog (uid, type, message, severity, link, location, referer, hostname, timestamp) VALUES (0, 'php', 'Table &#039;username_database.tableprefixurl_alias&#039; doesn&#039;t exist\nquery: SELECT COUNT(pid) FROM tableprefixurl_alias in /home/username/public_html/includes/database.mysql.inc on line 120.', 2, '', 'http://www.cosmichammer.com/index_old.php', '', '70.249.43.246', 1147361784) in /home/username/public_html/includes/database.mysql.inc on line 120

Warning: Table 'username_database.tableprefixwatchdog' doesn't exist query: INSERT INTO tableprefixwatchdog (uid, type, message, severity, link, location, referer, hostname, timestamp) VALUES (0, 'php', 'Table &#039;username_database.tableprefixwatchdog&#039; doesn&#039;t exist\nquery: INSERT INTO tableprefixwatchdog (uid, type, message, severity, link, location, referer, hostname, timestamp) VALUES (0, &#039;access denied&#039;, &#039;&lt;em&gt;node&lt;/em&gt; denied access.&#039;, 1, &#039;&lt;a href=\\&quot;/?q=node\\&quot; class=\\&quot;active\\&quot;&gt;view&lt;/a&gt;&#039;, &#039;http://www.cosmichammer.com/index_old.php&#039;, &#039;&#039;, &#039;70.249.43.246&#039;, 1147361784) in /home/username/public_html/includes/database.mysql.inc on line 120.', 2, '', 'http://www.cosmichammer.com/index_old.php', '', '70.249.43.246', 1147361784) in /home/username/public_html/includes/database.mysql.inc on line 120

Warning: Table 'username_database.tableprefixwatchdog' doesn't exist query: INSERT INTO tableprefixwatchdog (uid, type, message, severity, link, location, referer, hostname, timestamp) VALUES (0, 'php', 'Table &#039;username_database.tableprefixurl_alias&#039; doesn&#039;t exist\nquery: SELECT COUNT(pid) FROM tableprefixurl_alias in /home/username/public_html/includes/database.mysql.inc on line 120.', 2, '', 'http://www.cosmichammer.com/index_old.php', '', '70.249.43.246', 1147361784) in /home/username/public_html/includes/database.mysql.inc on line 120

Access denied
warning: Cannot modify header information - headers already sent by (output started at /home/username/public_html/includes/database.mysql.inc:120) in /home/username/public_html/includes/common.inc on line 139.
user warning: Table 'username_database.tableprefixsystem' doesn't exist query: SELECT name, filename, throttle, bootstrap FROM tableprefixsystem WHERE type = 'module' AND status = 1 ORDER BY weight ASC, filename ASC in /home/username/public_html/includes/database.mysql.inc on line 120.
user warning: Table 'username_database.tableprefixsystem' doesn't exist query: SELECT filename FROM tableprefixsystem WHERE name = 'filter' AND type = 'module' in /home/username/public_html/includes/database.mysql.inc on line 120.
user warning: Table 'username_database.tableprefixsystem' doesn't exist query: SELECT filename FROM tableprefixsystem WHERE name = 'node' AND type = 'module' in /home/username/public_html/includes/database.mysql.inc on line 120.
user warning: Table 'username_database.tableprefixsystem' doesn't exist query: SELECT filename FROM tableprefixsystem WHERE name = 'system' AND type = 'module' in /home/username/public_html/includes/database.mysql.inc on line 120.
user warning: Table 'username_database.tableprefixsystem' doesn't exist query: SELECT filename FROM tableprefixsystem WHERE name = 'user' AND type = 'module' in /home/username/public_html/includes/database.mysql.inc on line 120.
user warning: Table 'username_database.tableprefixsystem' doesn't exist query: SELECT filename FROM tableprefixsystem WHERE name = 'watchdog' AND type = 'module' in /home/username/public_html/includes/database.mysql.inc on line 120.
user warning: Table 'username_database.tableprefixcache' doesn't exist query: SELECT data, created, headers, expire FROM tableprefixcache WHERE cid = 'menu::en' in /home/username/public_html/includes/database.mysql.inc on line 120.
user warning: Table 'username_database.tableprefixrole' doesn't exist query: SELECT DISTINCT(p.perm) FROM tableprefixrole r INNER JOIN tableprefixpermission p ON p.rid = r.rid WHERE r.rid IN (1) in /home/username/public_html/includes/database.mysql.inc on line 120.
user warning: Table 'username_database.tableprefixsystem' doesn't exist query: SELECT * FROM tableprefixsystem WHERE type = 'theme' in /home/username/public_html/includes/database.mysql.inc on line 120.
user warning: Table 'username_database.tableprefixcache' doesn't exist query: LOCK TABLES tableprefixcache WRITE in /home/username/public_html/includes/database.mysql.inc on line 120.
user warning: Table 'username_database.tableprefixcache' doesn't exist query: UPDATE tableprefixcache SET data = 'a:4:{s:10:\"path index\";a:44:{s:13:\"admin/filters\";i:-1;s:18:\"admin/filters/list\";i:-2;s:17:\"admin/filters/add\";i:-3;s:20:\"admin/filters/delete\";i:-4;s:11:\"filter/tips\";i:-5;s:10:\"admin/node\";i:-6;s:19:\"admin/node/overview\";i:-7;s:19:\"admin/settings/node\";i:-8;s:28:\"admin/settings/content-types\";i:-9;s:4:\"node\";i:-10;s:8:\"node/add\";i:-11;s:7:\"rss.xml\";i:-12;s:12:\"system/files\";i:-13;s:5:\"admin\";i:-14;s:12:\"admin/themes\";i:-15;s:19:\"admin/themes/select\";i:-16;s:21:\"admin/themes/settings\";i:-17;s:28:\"admin/themes/settings/global\";i:-18;s:14:\"admin/settings\";i:-19;s:13:\"admin/modules\";i:-20;s:4:\"user\";i:-21;s:17:\"user/autocomplete\&q in /home/username/public_html/includes/database.mysql.inc on line 120.
warning: Cannot modify header information - headers already sent by (output started at /home/username/public_html/includes/database.mysql.inc:120) in /home/username/public_html/includes/common.inc on line 139.
user warning: Table 'username_database.tableprefixsystem' doesn't exist query: SELECT * FROM tableprefixsystem WHERE type = 'theme' in /home/username/public_html/includes/database.mysql.inc on line 120.
user warning: Table 'username_database.tableprefixurl_alias' doesn't exist query: SELECT COUNT(pid) FROM tableprefixurl_alias in /home/username/public_html/includes/database.mysql.inc on line 120.
user warning: Table 'username_database.tableprefixwatchdog' doesn't exist query: INSERT INTO tableprefixwatchdog (uid, type, message, severity, link, location, referer, hostname, timestamp) VALUES (0, 'access denied', '<em>node</em> denied access.', 1, '<a href=\"/?q=node\" class=\"active\">view</a>', 'http://www.cosmichammer.com/index_old.php', '', '70.249.43.246', 1147361784) in /home/username/public_html/includes/database.mysql.inc on line 120.
user warning: Table 'username_database.tableprefixurl_alias' doesn't exist query: SELECT COUNT(pid) FROM tableprefixurl_alias in /home/username/public_html/includes/database.mysql.inc on line 120.
You are not authorized to access this page. 	

Warning: Table 'username_database.tableprefixwatchdog' doesn't exist query: INSERT INTO tableprefixwatchdog (uid, type, message, severity, link, location, referer, hostname, timestamp) VALUES (0, 'php', 'Table &#039;username_database.tableprefixsessions&#039; doesn&#039;t exist\nquery: SELECT sid FROM tableprefixsessions WHERE sid = &#039;a8937d36a1ccfdd538c70922c32f1db6&#039; in /home/username/public_html/includes/database.mysql.inc on line 120.', 2, '', 'http://www.cosmichammer.com/index_old.php', '', '70.249.43.246', 1147361784) in /home/username/public_html/includes/database.mysql.inc on line 120

Comment #10

Celestina commented 13 May 2006 at 07:39

I'm an ass. Go ahead, slap me. Really.

Turns out my problem with the 4.0 sql file import had to do with the table prefix. In the site's settings.php file, I had specified a table prefix of "drupal470". However, the database import file did not specify a prefix. Naturally, this meant that settings.php was told to find and use tables with a prefix of "drupal470", none of which existed. I changed settings.php to no table prefix, and now it works fine. Feel free to use me as a good example of why you should pay more attention during installation :)

Comment #11

magico commented 16 September 2006 at 12:38

Status:

Active

» Fixed

Comment #12

(not verified) commented 30 September 2006 at 12:46

Status:

Fixed

» Closed (fixed)

SQL error on creation of core tables?