Solved

MySQL - phpMyAdmin - syntax error 1064 when trying to import database

Posted on 2007-03-21
6
1,948 Views
Last Modified: 2013-12-13
Using phpMyAdmin I have exported a database from one server (php version 5.0.4) and want to import it onto a new server (php version 5.0.37) but when I import it I get an error message as follows:


Error
SQL query:

-- phpMyAdmin SQL Dump
-- version 2.6.0-pl3
-- http://www.phpmyadmin.net
--
-- Host: mysql4db.gradwell.net
-- Generation Time: Mar 21, 2007 at 12:51 PM
-- Server version: 4.1.10
-- PHP Version: 5.0.4
--
-- Database: `smallbone`
--
-- --------------------------------------------------------
--
-- Table structure for table `admin_user`
--
CREATE TABLE  `admin_user` (
 `user_name` tinytext NOT NULL ,
 `user_pass` tinytext NOT NULL ,
 `user_id` smallint( 6 ) NOT NULL AUTO_INCREMENT ,
 `type` tinyint( 4 ) NOT NULL default  '0',
KEY  `user_id` (  `user_id` )
) TYPE = MYISAM DEFAULT CHARACTER SET = latin1 AUTO_INCREMENT =3;


MySQL said:

#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 'user_name` tinytext NOT NULL,
  `user_pass` tinytext NOT NULL,
  `user_id` small' at line 1




I don't know enough PHP/SQL to solve this! Help!
Thank you.
0
Comment
Question by:bbourgeaux
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 14

Expert Comment

by:Aamir Saeed
ID: 18765366
try

CREATE TABLE  `admin_user` (
 `user_name` tinytext NOT NULL ,
 `user_pass` tinytext NOT NULL ,
 `user_id` smallint( 6 ) NOT NULL AUTO_INCREMENT ,
 `type` tinyint( 4 ) NOT NULL default  '0',
KEY  `user_id` (  `user_id` )
)
0
 

Author Comment

by:bbourgeaux
ID: 18765469
Thanks for your quick answer but it didn't work. I get the exact same error message.
0
 
LVL 28

Expert Comment

by:gamebits
ID: 18768890
What version of mysql are you using?

In the meantime you can try this

replace `user_id` smallint( 6 ) NOT NULL AUTO_INCREMENT ,

by `user_id` 'smallint'( 6 ) NOT NULL AUTO_INCREMENT ,

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 17

Accepted Solution

by:
psimation earned 500 total points
ID: 18769720
The problem is almost 100% with the difference in MySQL versions than with php versions.

Try to re-export the db from the server using different options like "Complete Inserts" "Extended Inserts" and "Enclose table and field names with backquotes ". The last time I ran into a similar problem, I just tried different combinations of those options and I eventually got it to work. Better still, just make sure you have the same version of phpmyadmin installed on both servers and the same major number of mysql ( ie. mysql4.x or mysql5.x on both servers)
0
 
LVL 17

Expert Comment

by:psimation
ID: 18769726
Sorry, meant to say PHPMyAdmin AND MySQL versions, ie. PHP version itself should not cause any problems, but the version of phpmyadmin and mysql will play a role; try to get the versions of both to be the same on both servers for maximum compatability.
0
 

Author Comment

by:bbourgeaux
ID: 18778978
I re-exported the database on a different computer this time and it worked! The settings were the same though... strange.
0

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to count occurrences of each item in an array.

717 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