I'm trying to move my eCommerce website I created my self from a WAMPSERVER localhost to my online host.
In order to move the database(which was working perfectly on my localhos), I export it as an sql file,Then I created a new database in my online server to import the database sql file.
The problem I get after hit import is this :
ErrorSQL query:
-- --------------------------------------------------------
--
-- Table structure for table `users`
--
CREATE TABLE `users` (
`UserID` INT( 11 ) NOT NULL COMMENT 'To Identify User',
`Username` VARCHAR( 255 ) NOT NULL COMMENT 'Username To Login',
`Password` VARCHAR( 255 ) NOT NULL COMMENT 'Password To Login',
`Email` VARCHAR( 255 ) NOT NULL COMMENT 'User Email',
`FullName` VARCHAR( 255 ) NOT NULL COMMENT 'User Full name',
`GroupID` INT( 11 ) NOT NULL DEFAULT '0' COMMENT
);
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 ')' at line 14
UPDATE : The problem solved after adding the comment, but I'm getting another error messages like this :
Error
SQL query:
--
-- AUTO_INCREMENT for table `users`
--
ALTER TABLE `users` MODIFY `UserID` INT( 11 ) NOT NULL AUTO_INCREMENT ,
AUTO_INCREMENT =9 COMMENT 'To Identify User';
MySQL said: Documentation
#1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key
the problem is in the last line you need to set a COMMENT 'your comment ' or completely remove COMMENT
CREATE TABLE `users` (
`UserID` INT( 11 ) NOT NULL COMMENT 'To Identify User',
`Username` VARCHAR( 255 ) NOT NULL COMMENT 'Username To Login',
`Password` VARCHAR( 255 ) NOT NULL COMMENT 'Password To Login',
`Email` VARCHAR( 255 ) NOT NULL COMMENT 'User Email',
`FullName` VARCHAR( 255 ) NOT NULL COMMENT 'User Full name',
`GroupID` INT( 11 ) NOT NULL DEFAULT '0' COMMENT 'group id'
);
You miss the comment for the Last column. Add it
--------------------------------------------------------
--
-- Table structure for table `users`
--
CREATE TABLE `users` (
`UserID` INT( 11 ) NOT NULL COMMENT 'To Identify User',
`Username` VARCHAR( 255 ) NOT NULL COMMENT 'Username To Login',
`Password` VARCHAR( 255 ) NOT NULL COMMENT 'Password To Login',
`Email` VARCHAR( 255 ) NOT NULL COMMENT 'User Email',
`FullName` VARCHAR( 255 ) NOT NULL COMMENT 'User Full name',
`GroupID` INT( 11 ) NOT NULL DEFAULT '0' COMMENT ''
);
or if you do not need it, remove the COMMENT keyword:
--------------------------------------------------------
--
-- Table structure for table `users`
--
CREATE TABLE `users` (
`UserID` INT( 11 ) NOT NULL COMMENT 'To Identify User',
`Username` VARCHAR( 255 ) NOT NULL COMMENT 'Username To Login',
`Password` VARCHAR( 255 ) NOT NULL COMMENT 'Password To Login',
`Email` VARCHAR( 255 ) NOT NULL COMMENT 'User Email',
`FullName` VARCHAR( 255 ) NOT NULL COMMENT 'User Full name',
`GroupID` INT( 11 ) NOT NULL DEFAULT '0'
);
UPADTE:
To alter the autoincrement you have to add a primary key. Complete syntax looks like:
CREATE TABLE `users` (
`UserID` INT( 11 ) NOT NULL COMMENT 'To Identify User',
`Username` VARCHAR( 255 ) NOT NULL COMMENT 'Username To Login',
`Password` VARCHAR( 255 ) NOT NULL COMMENT 'Password To Login',
`Email` VARCHAR( 255 ) NOT NULL COMMENT 'User Email',
`FullName` VARCHAR( 255 ) NOT NULL COMMENT 'User Full name',
`GroupID` INT( 11 ) NOT NULL DEFAULT '0',
PRIMARY Key (UserID)
);
ALTER TABLE users MODIFY UserID INT( 11 ) NOT NULL AUTO_INCREMENT COMMENT 'To Identify User';
ALTER TABLE users AUTO_INCREMENT=9
To test this see my SQLFIDDLE