[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Upload sql-file into PHPMyadmin/Drupal fails

Posted on 2007-08-09
13
Medium Priority
?
978 Views
Last Modified: 2013-11-05
I have an sql-file that I want to import with PPMyAdmin into Drupal, but then I get the above message and nothing gets imported. The file itself is a converted Wordpress-file that is made for me by a person who has developed special software to convert WP-files into Drupal files. He is kowledgeable and helpful, but apparently he doesnt know where the error comes from.
The PHP Myadmin version I use is 2.10.2
Mysql versio is 4.0.27
Drupal 5.1
0
Comment
Question by:TheoRichel
  • 7
  • 6
13 Comments
 
LVL 17

Expert Comment

by:Aleksandar Bradarić
ID: 19660700
It's a known bug, see here: http://bugs.mysql.com/bug.php?id=11149. I'm not sure which version has it fixed, but you'll most likely have to upgrade (4.1?)...

0
 
LVL 17

Expert Comment

by:Aleksandar Bradarić
ID: 19660709
Here's a link on how to work around it: http://wordpress.org/support/topic/129242
0
 

Author Comment

by:TheoRichel
ID: 19660792
Thank you for the quick reply. I did as suggested, at the WP-supoort-site,  dropped all the tables from my database (I had no tables left)  and tried to import the file again, but the result was the same, see below. BTW: Upgrading the Mysql version is out of the question.
 
Error

SQL query:

-- phpMyAdmin SQL Dump
-- version 2.10.1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Jul 27, 2007 at 09:45 PM
-- Server version: 5.0.41
-- PHP Version: 5.2.3
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";

MySQL said: Documentation
#1193 - Unknown system variable 'SQL_MODE'
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 17

Expert Comment

by:Aleksandar Bradarić
ID: 19660853
If an update is out of the question, then you might have to remove the `SET SQL_MODE` lines from the file.
0
 

Author Comment

by:TheoRichel
ID: 19660915
That doesnt work either:  MySQL said: Documentation
#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 CHARSET=utf8 AUTO_INCREMENT=1' at line 7

I must also tell you that I am simultaneously trying to upload another dump that fails with this same error. That one doesnt have that sql-mode statement. In case you worry that these two problems interfere: my db is now completely empty, both files refuse to get in. If one succeeds I may run into new problems, but we'll cross that bridge when we get there.
Thanks
0
 
LVL 17

Expert Comment

by:Aleksandar Bradarić
ID: 19661004
> #1064 - You have an error in your SQL syntax.

You've not deleted those properly. You've made the query incorrect. Can you isolate the queries not working from the file? Just the ones making the trouble?
0
 

Author Comment

by:TheoRichel
ID: 19661088
Just what have I not deleted properly? Have I not dropped the tables properly?. I Cannot see anyone.

And how to ísolate the queries that are not working?'. I have the same problem now in two searate files:
one dump from Drupal and one converted Wordpress dump, from different sources.
0
 
LVL 17

Expert Comment

by:Aleksandar Bradarić
ID: 19661250
> Just what have I not deleted properly?

The `SET SQL_MODE` lines... if you did that?

Please, if you wish anyone here to be able to help, you need to first help us realize what you are doing and what's going wrong.

> And how to ísolate the queries that are not working?

I would take one of these files and remove everything from it, except the first CREATE TABLE statement. Then try to import it through phpoMyAdmin. If it fails, post the query and the error message here. If not, continue until it fails.
0
 

Author Comment

by:TheoRichel
ID: 19661283
You asked me to delete *two* things, that particular line as well as the tables (drop). I did both and had no way of knowing toa what you referred when you asked me whether I had deleted something properly. Hence my question. Meanwhile I have also deleted the entire database and so can start completely afresh. I will now first import the structure and try to import the data thereafter, tabel for table, through the sql-box.
0
 
LVL 17

Expert Comment

by:Aleksandar Bradarić
ID: 19661316
> can start completely afresh.

Please, keep us posted.
0
 

Author Comment

by:TheoRichel
ID: 19662087
I have found part of the answer: replacing 'default charset=utf8' by 'charset=utf8', so removing the word 'default' from this statement allows me to import 20 tables (of the 60 total).  Someone found it for me on the Drupal.org forum. It now stops with another syntax error. See below:

SQL-query:

-- --------------------------------------------------------
--
-- Tabel structuur voor tabel `GRK_cache_page`
--
CREATE TABLE `GRK_cache_page` (
`cid` varchar( 255 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL default '',
`data` longblob,
`expire` int( 11 ) NOT NULL default '0',
`created` int( 11 ) NOT NULL default '0',
`headers` text,
PRIMARY KEY ( `cid` ) ,
KEY `expire` ( `expire` )
) ENGINE = MYISAM CHARSET = utf8;

MySQL retourneerde: Documentatie
#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 'collate utf8_bin NOT NULL default '',
  `data` longblob,
  `e
0
 
LVL 17

Accepted Solution

by:
Aleksandar Bradarić earned 2000 total points
ID: 19662261
It seems you server does not recognize the COLLATE keyword. I've tested it and it works on both 4.1.22 and 5.0.45. Try modifying the line to:
---
`cid` varchar( 255 ) CHARACTER SET utf8 NOT NULL default '',
---
0
 

Author Comment

by:TheoRichel
ID: 19662409
Thanks that worked indeed. File one is jnow imported entirely and I will continue now with file two, see what new problems surface.
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Creating and Managing Databases with phpMyAdmin in cPanel.
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month17 days, 15 hours left to enroll

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question