When working with programs that have a database, you face the possibility of having database errors as well as regular php errors. This guide explains what the 1064 error is, some likely causes, and general troubleshooting

Causes for the 1064 error

This error message may seem cryptic at first. That is because it is a general MySQL error pointing to a syntax error in the SQL Query statement. This error can have multiple causes which are explained below.

Using reserved words

Every version of MySQL has its own list of reserved words. These are words that are used for specific purposes or perform specific functions within the MySQL engine. If you attempt to use one of these reserved words, you will receive the 1064 error. For example, below is a short SQL query that uses a reserved word as a table name.

CREATE TABLE alter (first_day DATE, last_day DATE);

How to fix it:
Just because the word alter is reserved does not mean it cannot be used, it just has special requirements to use it as the MySQL engine is trying to call the functionality for the alter command. To fix the issue, you will want to surround the word with backticks (“`”), this is usually the button just to the left of the "1" button on the keyboard. The code block below shows how the code will need to look in order to run properly.

CREATE TABLE `alter` (first_day DATE, last_day DATE);

Inserting incompatible data types into a field

One of the most common causes for the 1064 error is when a SQL statement is trying to insert a value for a data column that is of a different type. Simply put, if you are trying to place a number into a text field, a date in a number field, or text into a numeric field you will get the error.

Below is a simple example of a query that is trying to insert text into a numeric field, meaning the table structure has the id field set to accept numbers.

UPDATE table1 SET id = 'none';

How to fix it:
Since the id field is set as an INT (integer: numeric, whole number) field, the word 'none' is not correct. Changing the value to a number will fix the issue. If you are unsure what data type the columns are, read our article on How to determine the data type of a data column in phpMyAdmin.

Below is the syntax for the correct query statement.

UPDATE table1 SET id = 0;

Summary

As you can see there is more than one cause for this type error within MySQL code. This list will be updated as more specific instances are reported.

Did you find this article helpful?

We value your feedback!

Why was this article not helpful? (Check all that apply)
The article is too difficult or too technical to follow.
There is a step or detail missing from the instructions.
The information is incorrect or out-of-date.
It does not resolve the question/problem I have.
How did you find this article?
Please tell us how we can improve our Support Center:
Email Address
Optional, but our team may contact you for more information.
Like this Article?

Comments

Post a comment
2013-11-01 7:40 am
Hi, I have got this 1064 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 'ORDER BY content.created DESC' at line 25 SQL=SELECT content.id AS ID, cats.name AS cat_name, cats.alias AS cat_alias, content.catid AS cat_id, users.name AS author, users.email AS author_email, users.id AS author_id, content_rating.rating_sum AS rating_sum, content_rating.rating_count AS rating_count FROM gg1qt_k2_items AS content LEFT JOIN gg1qt_k2_categories AS cats ON cats.id = content.catid LEFT JOIN gg1qt_users AS users ON users.id = content.created_by LEFT JOIN gg1qt_k2_rating AS content_rating ON content.id = content_rating.itemID WHERE ORDER BY content.created DESC


How do i fix this? Please help
Staff
10,101 Points
2013-11-01 10:14 am
Hello Mariak,
A 1064 error is simply a syntax error in the code. The error you gave says that the error occurs near the ORDER BY statement, so you want to take a look at the code right in front of that.

Looking at the statement you have an empty WHERE clause right in front of the ORDER BY portion of the statement. The ORDER BY itself does not need the WHERE clause, so if you do not need anything else in the WHERE, simply removing it may correct the issue.

Kindest Regards,
Scott M
2013-12-03 9:13 am
Hi there i am totally new to this world of coding. And i just got this message on PHPmyadmin:

#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 ') NOT NULL DEFAULT '0', `ID` INT(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`ID`)' at line 1

sorry for my language, but what the fuck!?
can you help me fix this problem?
Staff
4,324 Points
2013-12-03 11:06 am
It appears that something in your SQL statement produced an error. Could you provide us with the SQL query that you were trying to run?
2014-01-09 4:29 pm
Hi all,

I'm using concrete 5 and I've imported a backup SQL into phpMyAdmin to restore my site to the point before I was having problems. I can normally upload a csv file that creates pages for me, but I get this error now... I've never gotten it before...

mysql error: [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 'LIMIT 1' at line 1] in EXECUTE("select max(cDisplayOrder) from Pages where cParentID = LIMIT 1")
Staff
10,655 Points
2014-01-09 8:10 pm
Hello Jhood,

Sorry to hear that you're having problems with the SQL import. I unfortunately don't have enough information from you in order to find an account so that I can investigate the issue here. If you can provide us more information we would be happy to review the issue. I did go directly to the Concrete5 Forums and that very same error is reported there. There are currently no replies to it. Here's the link: Concrete5 Forum. You will need to be registered in order to make any comments on their forum.

If you are hosting with us can you please provide account information and the exact steps you're taking to get the problem? I did add to the post in the forum per the information you have reported, since it was the exact same error message. Hopefully, we'll see more information soon.

If you have any further questions, or wish to provide us further information, please reply to this post.

Regards,
Arnel C.
n/a Points
2014-02-27 11:45 am

mi problema es el siguiente instale el wampserver 2.4 y al entrar a localhost/phpmyadmin/ me sale que tengo q poner el usuario y contraseña le pongo la q trae x defecto que es "root" y me sale este error #1045 El servidor MySQL no autorizó su ingreso. Me voy al panel de control de wampserver y en Mysql abrao la consola y escribo este codigo para q supuestamente me corriga el error al abrir la consola de Mysql me pide la clave pero como no la tengo solo le doy enter despues le pongo use mysql le doy enter me abre la base raiz despues pongo UPDATE mysql.user SET Password = PASSWORD<"aqui escribo la contraseña"> WHERE User = "aqui pongo el nombre de usuario"; y despues le doy enter y me sale esto. 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... Me puede ayudar xfavor 

Staff
5,498 Points
2014-02-27 12:21 pm
Hello Alex,

Thank you for your question. Unfortunately, I don't speak spanish, but I believe you are having trouble connecting to PHPMyAdmin after installing wampserver 2.4.

I found a solution to a similar problem via google search, you can view the post here.

If you have any further questions, feel free to post them below.
Thank you,

-John-Paul
n/a Points
2014-02-27 12:59 pm

ok here it costs translated into English and thank you very much my problem is this install wampserver 2.4 and enter localhost / phpmyadmin / q leaves me I have put the username and password I put the q brings x default is "root" and I get this error # 1045 The MySQL server is not authorized admission. I go to control panel wampserver and Mysql I open the console and write this code for q me supposedly corrects the mistake to open the console Mysql asking for the key, but as I have not only give you enter after I put use mysql you I enter root opens the database after UPDATE mysql.user SET password put = PASSWORD <'here I write the password "> WHERE user =" here I put the user name "and then give him enter and I get this. ERROR 1064 <42000>: You have an error in your SQL syntax; Manual That Corresponds the check to your MySQL server version for the right syntax to use near ... I can help xfavor

Staff
4,324 Points
2014-02-27 2:08 pm
The exact syntax for updating the password for a MySQL user is the following:

UPDATE mysql.user SET Password=PASSWORD('new-password-here') WHERE User='user-name-here';


Be sure to only replace the text here and not any surrounding quotes or parentheses.
n/a Points
2014-02-27 3:12 pm

Many thank you very much and johnpaulb-imhs1 jeffMa helped me helped me answers thank you very much. I get this message in the phpMyAdmin panel. The phpMyAdmin configuration storage is not completely configured, some extended features were disabled. Click here to find out why. and if not paying attention or let haci. if I can help with this too please thank you very much and many are the maximum ..

n/a Points
2014-03-12 5:53 pm

Please I have this error:

Could not make the query numbered : 78MySQL Error No : 1064MySQL 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 'function.is-readable'>function.is-readable]: open_basedir restriction in eff' at line 5

Am confused pls what do i do,I am new to this kind of problem

Staff
4,324 Points
2014-03-14 8:27 am
Hello Momodu Lucky,

It does appear that you do have invalid syntax there. I'm unable to see the full code that you have written on and around this line, but my best guess is that you probably missed a closing quote somewhere as the syntax that your error is referring to is far from valid.
n/a Points
2014-03-16 7:12 am

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual thatcorresponds to your MySQL server version for the right syntax to use near '--EXECUTION OF PROCEDURE FOR THE FIRST TIME IF DISE = '' THEN  SET SYM_COL = (' at line 16

---------------------------------------------------------

DROP PROCEDURE IF EXISTS DIAGNOSIS;delimiter //CREATE PROCEDURE DIAGNOSIS(IN SYM INT,IN DISE VARCHAR(250))BEGIN DECLARE I INT; DECLARE SYM_COL VARCHAR(50); DECLARE N INT; DECLARE C INT; DECLARE ST VARCHAR(250); DECLARE DID INT; CREATE TEMPORARY TABLE SYM_TEMP (SID INT, SNAME VARCHAR(50)); CREATE TEMPORARY TABLE DISE_TEMP (DID INT, DNAME VARCHAR(100)); IF DISE = '' THEN  SET SYM_COL = (SELECT SNAME FROM SYMPTOMS WHERE SID = SYM);  SET ST = CONCAT('SELECT DISTINCT DID, DNAME FROM DISEASES WHERE ',SYM_COL,'!=0');  PREPARE STMT FROM ST;  EXECUTE STMT;        --RETURNS DISEASES

.

.

.

DEALLOCATE PREPARE STMT; END IF; END//delimiter ;

------------------------------------------

Can someone fix this???

n/a Points
2014-03-17 2:34 am
i have an error in mysql error no1064 when i will join the table and then i will write select *from customer c,rental r,property p,propertytype pt where c.customerid=r.customerid and r.propertyid=p.propertyid and p.propertytypeid=pt.propertytypeid; And propertytypename ='residential';
n/a Points
2014-03-19 2:24 am
when i run this stored procedure i get error like SQL Error (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 'NULL' at line 1.Please help me DELIMITER //
DROPPROCEDUREIFEXISTScrosstab//
CREATEPROCEDUREcrosstab()
BEGIN
SET@cols=(SELECTGROUP_CONCAT(
'(TX_SHORT_NAME = ',TX_SHORT_NAME,') AS ',TX_SHORT_NAME
SEPARATOR', ')FROMlst_inv_grps);
SET@sql=CONCAT('
SELECT fac.FAC_NAME,  ',@sql,'
FROM facility fac left join  dbilling b
on fac.SEQ_ID=b.FAC_SEQID left join invoicegrps inv
on b.InvoiceGrp=inv.InvoiceGrp left join lst_inv_grps grp
on inv.invID=grp.KYS_ID
group by fac.FAC_NAME');
PREPAREstmtFROM@sql;
EXECUTEstmt;
END;
/
DELIMITER;
Staff
4,324 Points
2014-03-19 7:40 am
Hello Nandan,

Try adding a space between DELIMITER and the semicolon as you are defining ; as the argument which may be confusing MySQL. Also, check for white space at the beginning of the script as well.
n/a Points
2014-03-19 9:07 am

Hello Jeffma ,

Thanq so much for your valuable comment

n/a Points
2014-03-20 5:53 am

CREATE TABLE `equipment` (  `id` int(11) NOT NULL auto_increment,  `parent_id` int(11) NOT NULL default '0',  `description` varchar(20) NOT NULL default '',  PRIMARY KEY `id` (`id`)) TYPE=MyISAM COMMENT='Equipment' AUTO_INCREMENT=201

 

Err] 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 'TYPE=MyISAM COMMENT='Equipment' AUTO_INCREMENT=201' at line 6

Staff
4,324 Points
2014-03-20 8:06 am
Hello gaisie,

Your error may be caused by defining TYPE=MyISAM. Depending on your MySQL version, you may need to change this to ENGINE=MyISAM
n/a Points
2014-03-20 8:44 am

[SQL] CREATE TABLE equipment (  id int(11) NOT NULL auto_increment,  parent_id int(11) NOT NULL default '0',  description varchar(20) NOT NULL default '',  PRIMARY KEY  (id))ENGINE=MyISAM COMMENT= Equipment AUTO_INCREMENT=201 [Err] 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 'Equipment AUTO_INCREMENT=201' at line 6change it and still giving me error

Staff
4,324 Points
2014-03-20 8:52 am
It appears that you changed COMMENT='Equipment' into COMMENT= Equipment by accident on this second comment which would also cause an error.
n/a Points
2014-03-20 1:51 pm

@gaisie,

I tested JeffMa's comment, and it appears to fix the problem.

  1. I ran your query and was able to replicate the same exact error.
  2. I fixed the query by putting "Equipment" within quotes after COMMENT= at the end.

Here's a working query that I've run successfully:

CREATE TABLE equipment
(
id int(11) NOT NULL auto_increment,
parent_id int(11) NOT NULL default '0',
description varchar(20) NOT NULL default '',
PRIMARY KEY (id)
) ENGINE=MyISAM COMMENT="Equipment" AUTO_INCREMENT=201
n/a Points
2014-03-21 12:11 pm

i have the same problem,can anyone help me with this....

my error is ike this,

Error

 

SQL query:

 

---- PostgreSQL database dump-- '\CONNECT' - hms SET search_path = PUBLIC,pg_catalog;

 

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 ''\CONNECT' - hms SET search_path = PUBLIC,                   pg_catalog' at line 4

Staff
5,498 Points
2014-03-21 1:04 pm
Hello vishal,

According to your error, you seem to be using Postgre statement, for a mySQL engine; which will not work.

If you have any further questions, feel free to post them below.
Thank you,

-John-Paul
Staff
2,770 Points
2014-03-21 1:49 pm
@vishal - Can you give us the full query that you're running? It's a bit difficult to troubleshoot an SQL error without actually seeing the entire query that you're running.
n/a Points
2014-03-22 11:51 am

here's full query:

--
-- PostgreSQL database dump
--
 \CONNECT - hms
SET search_path = PUBLIC,
                  pg_catalog;
 
 --
-- TOC entry 2 (OID 217698)
-- Name: add_users; Type: TABLE; Schema: public; Owner: hms
--

Comment updated by moderator. Too long.

Staff
2,770 Points
2014-03-24 11:09 am

@vishal - Based upon the information you've provided, you are trying to import a PostgreSQL database.

POSSIBLE PROBLEM

System Error Codes
MySQLPostgreSQL
106442000 or 42601

The problem you're having is that you're trying to import a PostgreSQL database as a MySQL database. Because MySQL and PostgreSQL are not backwards compatible, MySQL is giving you a 1064 error, meaning a syntax error. We believe this is the case because 1064 is the code for Syntax errors in MySQL, while 42000 and 42601 are the codes for Syntax errors in PostgreSQL.

RECOMMENDED SOLUTION

If you are trying to import a PostgreSQL, please follow the directions on this page. Based upon that documentation, you'll need to run a command similar to:

psql dbname < infile
n/a Points
2014-03-25 6:50 am

Please help me with this MySQL PHPMYADMIN

#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 'BINARY NOT NULL, `aphototype` VARCHAR(200) CHARACTER SET latin1 COLLATE latin1_s' at line 1

 

thanks .. i would really appreciate it..

Staff
4,324 Points
2014-03-25 8:24 am
Could you provide us with the full query that you are trying to run?
n/a Points
2014-03-26 9:19 am

When try to install my script got this error :

 

Cannot execute MySQL query: #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 'TYPE=MyISAM' at line 5

 

When replace "TYPE" for "ENGINE" at source DB don't work, please help me

Staff
5,671 Points
2014-03-26 9:52 am
Hello Freddy,

Unfortunately it is typically the solution to replace TYPE with ENGINE when you encounter that specific error.

I've searched online a bit more for this error and also see this noted as a possible solution:

ENGINE=MyISAM DEFAULT CHARSET=utf8;



Another thing you might want to try is leaving out the TYPE or ENGINE statements altogether and let the MySQL server try to determine what should be used.

Also, taking a look at some of the .sql files you have on the server, it appears some of them have entries like:

ENGINE=ENGINE_TYPE DEFAULT CHARSET=utf8;



If this is the case of the .sql file you're trying to import, you'll need to make sure to actually enter in the ENGINE_TYPE instead of just using that placeholder text.

If you are still having any issues, if you can provide us with the location of the SQL script you are trying to restore we can take a look for you.

- Jacob
n/a Points
2014-03-26 1:35 pm

Hye..

I wants ask u about this error (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 'WHERE st_mykad_staff = ''' at line 11)

 

I dont know what the problem, I already double check the .php and database

 

but still cannot update the information. When I update the information and click update, it show this error.

 

Thank You .

Regards :)

Staff
5,671 Points
2014-03-26 1:51 pm
Hello N_Syakilah, and thank you for your question.

It looks like the specific MySQL error that you're getting is in reference to the WHERE statement in your SQL query.

Unfortunately we'd need to see the full query to see what the exact issues is, but it looks like you have your WHERE clause possibly in the wrong place, or for that particular table you are querying you can't look for a blank value.

For instance on a database that I have, if I do:

SELECT * FROM myDB.myTable
WHERE created_by = ""


I get the error:

Warning: Incorrect integer value: " for column 'created_by' at row 1


This is because my created_by column is an INT datatype, so it's looking for a number. If I instead use:

SELECT * FROM myDB.myTable
WHERE created_by = "123456"


Then the query is successfully executed, and all records with that user ID are returned.

So if you try your query without the WHERE statement, it will probably work. Then you just need modify the WHERE statement to work according to the column you're attempting to compare your query against.

- Jacob
n/a Points
2014-03-27 2:47 am

am getting this error am not good in code so am reading gibberish but i am good at following instructions

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 ') AND TAG.PUBLISHED = 1' AT LINE 4 SQL=SELECT TAG.NAME, TAG.ID FROM Y5CYM_K2_TAGS AS TAG LEFT JOIN Y5CYM_K2_TAGS_XREF AS XREF ON XREF.TAGID = TAG.ID WHERE XREF.ITEMID IN () AND TAG.PUBLISHED = 1

on this site www.procurementtoday.org

help me out

 

Staff
4,324 Points
2014-03-27 8:02 am
Could you provide us with the full query that you are running?
n/a Points
2014-03-31 2:35 pm

hello ...

i need your help..

this is my query :

SELECT jenis_kasus, COUNT( * ) AS TOTAL FROM ( SELECT *

FROM jan2012 UNION ALL SELECT * FROM feb2012 ) feb2012

WHERE jenis_kasus ="Hak Kuasa Asuh"

Err] 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 ') feb2012WHERE 0 =1'at ;line 1

 

please help me....

thank you

Staff
10,101 Points
2014-03-31 3:53 pm
Hello Auges,

The error just means the statement is not written correctly. The area you want to check is given in the error message itself. Check the area where it mentions the table named feb2012 right before the WHERE.

You may want to check the MySQL site regarding Select Syntax for exact assistance.

Kindest Regards,
Scott M
Staff
2,770 Points
2014-03-31 7:45 pm
Hi @auges, Your error messages indicates that your original query included the following text:
') feb2012WHERE 0 =1'

However, the query that you put in your comment does not include that text. Can you confirm that you pasted in the full query? Seeing the full query will help us troubleshoot the issue further for you.
n/a Points
2014-04-01 1:28 am

I have a 1064 and I can't find it:

INSERT INTO `soccer_schedule` (`season`, `game_date`, `arrive_time`, `opponent`, `ourscore`, `theirscore`) VALUES ('Spring 2014 U11B','4/26/2014','10:45','HOLTON',0,0);

Here's a similar insert that PHPMyAdmin created.

INSERT INTO `soccer_schedule` (`soccer_season_id`, `season`, `game_date`, `arrive_time`, `opponent`, `ourscore`, `theirscore`) VALUES ('Spring 2014 U11B', '4/5/2014', '10:45', 'HOLTON', 0, 0)

Here's what PHPMyAdmin said:

Error

There is a chance that you may have found a bug in the SQL parser. Please examine your query closely, and check that the quotes are correct and not mis-matched. Other possible failure causes may be that you are uploading a file with binary outside of a quoted text area. You can also try your query on the MySQL command line interface. The MySQL server error output below, if there is any, may also help you in diagnosing the problem. If you still have problems or if the parser fails where the command line interface succeeds, please reduce your SQL query input to the single query that causes problems, and submit a bug report with the data chunk in the CUT section below:----BEGIN CUT----eNotjU1ugzAQhfc+xexoJNfB/ErsKLJCJMAUQ7oERCyClJgISNXehrNwspLQzcx8b57eY1nGMw8CCoEBEUs8cMAF6rhI5Ku+zAjFv+Iz8sAmNjGd92vfokJkwAUG/8CSHMOJrc6vTkEQZjxmYJpEJ9S112FbKI19DyxCCaUoDdOnG3PxzLOIYULUqccPivzk4IFU6FV1TATL8mU+JjmHauybRg7l2Fzk+XGV1TK/VaOsx15VeJmrtr7J8lxP8kX1MHTfspy628b9/d4rqaYNHsPY9MP2mS6y+8fdMp/8qGBijdbEfehUC4ZOLSgo/dCwZu0NZ/8U1pvqnmWvO+RRzhMN61jf/QEQMWQb----END CUT--------BEGIN RAW----

ERROR: C1 C2 LEN: 6 7 167
STR:  

MySQL: 5.5.36-log
USR OS, AGENT, VER: Win CHROME 33.0.1750.154
PMA: 4.1.11
PHP VER,OS: 5.4.23 Linux
LANG: en
SQL: INSERT INTO `soccer_schedule` (`season`, `game_date`, `arrive_time`, `opponent`, `ourscore`, `theirscore`) VALUES ('Spring 2014 U11B','4/26/2014','10:45','HOLTON',0,0)

----END RAW----

SQL query:

INSERT INTO `soccer_schedule` (`season`, `game_date`, `arrive_time`, `opponent`, `ourscore`, `theirscore`) VALUES ('Spring 2014 U11B','4/26/2014','10:45','HOLTON',0,0)

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 'INSERT INTO `soccer_schedule` (`season`, `game_date`, `arrive_time`, `oppon' at line 1 

I'd appreciate any help you can give me.

 

 

 

 

Staff
4,324 Points
2014-04-01 8:55 am
It looks like you have failed to enter a space after the comma when defining your values to insert. Add a space after the comma and it should fix the issue.
n/a Points
2014-04-01 9:39 am

Hi, thanks.  I've since figured it out.

It was those stupid slashes!  

This --> ,'4/26/2014'

Should have been this --> ,'4-26-2014'

 

Thanks for your help

n/a Points
2014-04-10 12:04 pm

HI, I have created a SQL ERD, and from that I am forward engineering to create the tables.  This is the error I am gettting, can you please help me to solve this.  Thanks

 

Executing SQL script in server

 

ERROR: Error 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 ')

 

    REFERENCES `mydb`.`patient` ()

    ON DELETE NO ACTION

    ON UPDATE NO AC' at line 20

 

-- Table `mydb`.`visit`

 

-- -----------------------------------------------------

 

CREATE TABLE IF NOT EXISTS `mydb`.`visit` (

  `visitid` INT UNSIGNED NOT NULL AUTO_INCREMENT,

  `patientid` INT UNSIGNED NOT NULL,

  `visittype` VARCHAR(45) NULL,

  `visitdate` DATE NOT NULL,

  `visittime` TIME NOT NULL,

  `createdby` VARCHAR(45) NOT NULL,

  `datecreated` DATETIME NOT NULL,

  `visitstatus` VARCHAR(20) NOT NULL,

  `visitchangeddate` DATETIME NULL,

  `visitchangedby` VARCHAR(45) NULL,

  `cancelreason` VARCHAR(45) NULL,

  `cancelledby` VARCHAR(45) NULL,

  `cancelleddate` DATETIME NULL,

  PRIMARY KEY (`visitid`),

  CONSTRAINT `patientid`

    FOREIGN KEY ()

    REFERENCES `mydb`.`patient` ()

    ON DELETE NO ACTION

    ON UPDATE NO ACTION)

ENGINE = InnoDB

 

 

SQL script execution finished: statements: 6 succeeded, 1 failed

Staff
2,770 Points
2014-04-10 12:21 pm
Hi @zoey,

In your SQL query, I noticed the following:

FOREIGN KEY () REFERENCES `mydb`.`patient` ()

I believe the query is failing because you're not actually putting in a foreign key. Documentation shows it should look something like this:

FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)

In the example, notice that it is:
FOREIGN KEY (P_Id)
... and not
FOREIGN KEY ()
as your example has.

You may need to enter a value here:
FOREIGN KEY (`here`)

Let us know if that helps or not.
n/a Points
2014-04-10 1:55 pm

 

 

Thanks a lot.  It worked.....Thanks

n/a Points
2014-04-10 2:31 pm

 

Hi, Another table is giving me an error.  Can you please check this one too.  Is there anyway that I can send you the ERD and you can check it and let me know whether its good or not.  Thanks.  I really appreciate it.  

 

Executing SQL script in server

ERROR: Error 1215: Cannot add foreign key constraint

 

-- Table `mydb`.`utox`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `mydb`.`utox` (

  `utoxid` INT UNSIGNED NOT NULL AUTO_INCREMENT,

  `patientid` INT UNSIGNED NOT NULL,

  `providerid` INT UNSIGNED NOT NULL,

  `labid` INT UNSIGNED NOT NULL,

  `labtestid` INT UNSIGNED NOT NULL,

  PRIMARY KEY (`utoxid`),

  INDEX `patientidutox_idx` (`patientid` ASC),

  INDEX `provideridutox_idx` (`providerid` ASC),

  INDEX `labidutox_idx` (`labid` ASC),

  INDEX `labtestidutox_idx` (`labtestid` ASC),

  CONSTRAINT `patientidutoxfk`

    FOREIGN KEY (`patientid`)

    REFERENCES `mydb`.`patient` (`patientid`)

    ON DELETE NO ACTION

    ON UPDATE NO ACTION,

  CONSTRAINT `provideridutoxfk`

    FOREIGN KEY (`providerid`)

    REFERENCES `mydb`.`provider` (`providerid`)

    ON DELETE NO ACTION

    ON UPDATE NO ACTION,

  CONSTRAINT `labidutoxfk`

    FOREIGN KEY (`labid`)

    REFERENCES `mydb`.`laboratory` (`labid`)

    ON DELETE NO ACTION

    ON UPDATE NO ACTION,

  CONSTRAINT `labtestidutoxfk`

    FOREIGN KEY (`labtestid`)

    REFERENCES `mydb`.`labtest` (`labtestid`)

    ON DELETE NO ACTION

    ON UPDATE NO ACTION)

ENGINE = InnoDB

 

 

SQL script execution finished: statements: 19 succeeded, 1 failed

 

 

 

Fetching back view definitions in final form.

 

Nothing to fetch

 

Staff
5,671 Points
2014-04-10 3:01 pm
Hello Zoey,

It looks like you are trying to define multiple FOREIGN KEY entries, so it's tough to say which one exactly is causing the ERROR: Error 1215: Cannot add foreign key constraint problem.

Are you trying to re-create your database tables from a backup of a database? Or are you attempting to create these tables for the very first time?

- Jacob

n/a Points
2014-04-10 3:18 pm

I am creating it the very first time.

Staff
5,671 Points
2014-04-10 3:23 pm
Hello Zoey,

Are you just manually creating the structure of your database, or using some database software to do so? Unfortunately there is a syntax error in your code, so you might want to try not creating the entire table at once, but in chunks until you can find the part that is causing problems.

- Jacob
n/a Points
2014-04-11 10:16 am

Hi, It worked...Thanks for your help.  I created the new schema.

n/a Points
2014-04-10 3:32 pm

I am using MYSQL workbench, So  I have to remove the error tables and add one by one at one time...right?...

Staff
5,671 Points
2014-04-10 3:38 pm
Hello Zoey,

Yes, I'd recommend creating your table in stages so that you are sure that the correct syntax is being used every step of the way.

- Jacob
n/a Points
2014-04-10 3:45 pm

ok Thanks...I will let you know, once I am done.  Thanks

n/a Points
2014-04-11 2:53 pm

Hi, It worked...Thanks for your help.  I created the new schema.

n/a Points
2014-04-11 11:26 am

Hi, from reading your article I have gained a better understanding of why this problem occurs, however my experience is very limited so I have not been able to make the neccassary changes. Could you please look at the error message I am recieving and point me in the right direction? Please note, I have no experience what so ever with databases. Do you also happen to know why this happens? The error has occured when I have moved my database from my live server and tried to import it to my local server. However when I try to import an older version of my database, from a few weeks ago, it works fine?I am an InMotionHosting customer.SQL query:

<!-- PMA-SQL-ERROR -->

    <div class="error"><h1>Error</h1>

<p><strong>SQL query:</strong>

<a href="/support/tbl_sql.php?sql_query=SHOW+TABLE+STATUS+FROM+%60caprac5_wplc%60+LIKE+%27sales_flat_quote_address_item%27&amp;show_query=1&amp;db=caprac5_wplc&amp;table=sales_flat_quote_address_item&amp;token=ed5eb71dc536407f4f31ba33b78723ff"><span class="nowrap"><img src="/support/themes/dot.gif" title="Edit" alt="Edit" class="icon ic_b_edit" /> Edit</span></a>    </p>

<p>

<span class="syntax"><span class="inner_sql"><a href="/support/./url.php?url=http%3A%2F%2Fdev.mysql.com%2Fdoc%2Frefman%2F5.5%2Fen%2Fshow.html&amp;token=ed5eb71dc536407f4f31ba33b78723ff" target="mysql_doc"><span class="syntax_alpha syntax_alpha_reservedWord">SHOW</span></a>  <span class="syntax_alpha syntax_alpha_reservedWord">TABLE</span>  <span class="syntax_alpha syntax_alpha_reservedWord">STATUS</span>  <span class="syntax_alpha syntax_alpha_reservedWord">FROM</span>  <span class="syntax_quote syntax_quote_backtick">`caprac5_wplc`</span>  <a href[...]

 

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 '<!-- PMA-SQL-ERROR -->

    <div class="error"><h1>Error</h1>

<p><strong>SQL quer' at line 1 

 

Staff
10,655 Points
2014-04-11 2:43 pm
Hello Conor,

The problem you're having has to do with the code you're using - it's HTML not SQL. That's why you're getting an immediate syntax error on line 1. MySQL can't parse the HTML code. I'm not sure where you're getting this code, but it doesn't look like it's from a MySQL export. We unfortunately can't provide the code for you. If you need further help with understanding MySQL try going through the tutorials here.

For more specific references concerning exporting/databases, check out our article which will give you references on each topic.

If you have any further questions, please let us know.

Regards,
Arnel C.
n/a Points
2014-04-12 6:49 am

Hi Arnel,

Thank you for the quick reply.

I have read through both the tutorial and the articles you suggested. I now have a better understanding of how the SQL database works and is formatted. One thing I don't understand is whether html can be used in a SQL file at all, or does it always result in an error? I took the time to look through my SQL database file to find any html in it. I found 5 different places where html is used, and then at the very bottom of the SQL file I found the html code that is causing the issue. Here is what the code looks like in the SQL file:

 

<!-- PMA-SQL-ERROR -->

    <div class="error"><h1>Error</h1>

<p><strong>SQL query:</strong>

<a href="/support/tbl_sql.php?sql_query=SHOW+TABLE+STATUS+FROM+%60caprac5_wplc%60+LIKE+%27sales_flat_quote_address_item%27&amp;show_query=1&amp;db=caprac5_wplc&amp;table=sales_flat_quote_address_item&amp;token=ed5eb71dc536407f4f31ba33b78723ff"><span class="nowrap"><img src="/support/themes/dot.gif" title="Edit" alt="Edit" class="icon ic_b_edit" /> Edit</span></a>    </p>

<p>

<span class="syntax"><span class="inner_sql"><a href="/support/./url.php?url=http%3A%2F%2Fdev.mysql.com%2Fdoc%2Frefman%2F5.5%2Fen%2Fshow.html&amp;token=ed5eb71dc536407f4f31ba33b78723ff" target="mysql_doc"><span class="syntax_alpha syntax_alpha_reservedWord">SHOW</span></a>  <span class="syntax_alpha syntax_alpha_reservedWord">TABLE</span>  <span class="syntax_alpha syntax_alpha_reservedWord">STATUS</span>  <span class="syntax_alpha syntax_alpha_reservedWord">FROM</span>  <span class="syntax_quote syntax_quote_backtick">`caprac5_wplc`</span>  <a href="/support/./url.php?url=http%3A%2F%2Fdev.mysql.com%2Fdoc%2Frefman%2F5.5%2Fen%2Fstring-comparison-functions.html%23operator_like&amp;token=ed5eb71dc536407f4f31ba33b78723ff" target="mysql_doc"><span class="syntax_alpha syntax_alpha_reservedWord">LIKE</span></a>  <span class="syntax_quote syntax_quote_single">'sales_flat_quote_address_item'</span></span></span>

</p>

<p>

    <strong>MySQL said: </strong><a href="/support/./url.php?url=http%3A%2F%2Fdev.mysql.com%2Fdoc%2Frefman%2F5.5%2Fen%2Ferror-messages-server.html&amp;token=ed5eb71dc536407f4f31ba33b78723ff" target="mysql_doc"><img src="/support/themes/dot.gif" title="Documentation" alt="Documentation" class="icon ic_b_help" /></a>

</p>

<code>

#2006 - MySQL server has gone away

</code><br />

</div>

 

How is this code added to the SQL file? Is it done by phpMyAdmin scanning the file and noticing an error so including this code. Or is it done when something is changed in my site which causes the database to automatically add this code because the change as caused an error? Or is it some other way?The only changes that I can think of that could have caused a problem, would be when I added a sitemap file or when I added a robots.txt file, however no problems are visible on my sites frontend or backend. Do you think this could have something to do with it?

 

Thanks

Staff
4,324 Points
2014-04-14 8:20 am
From taking a look at what you have here, it looks like something modified your SQL file to format it in a better readable format, but would not be able to directly imported. Could you clarify where you got this exact code? Was it a direct export made from within PHPMyAdmin?
n/a Points
2014-04-14 8:49 am

Thanks for the reply.

 

I found this exact code at the very bottom of the database .sql file, after I had exported it from phpMyAdmin.

 

Yes, I exported the database directly from phpMyAdmin in my InMotionHosting account. I have done this many times before without a problem so I don't think it is my method of exporting that is the problem. However I have recently upgraded from an InMotionHosting shared hosting package (the 'pro' plan) to an InMotionHosting VPS package. Could this have caused any problems? Other than that the only changed I made where to the sitemap.xml file and the .robots.txt file. I do also remember that I added some redirect code to the .htaccess file, which was simply to redirect the homepage url to use http instead or https; the code I added can be seen below.

 

#Redirect your Homepage from HTTPS to HTTPRewriteCond %{HTTPS} onRewriteRule ^$ http://%{HTTP_HOST} [L,R]

 

Thanks

Staff
4,324 Points
2014-04-14 9:35 am
It is very uncommon to see something like this from a PHPMyAdmin export. From what it looks like here, PHPMyAdmin encountered an error in the export and placed it within the SQL file instead of displaying it. If you try to do another export, do you get the same information within the .sql file?
n/a Points
2014-04-14 11:54 am

I did another export and now the code is no longer in the .sql file as it was before. I also successfully imported it to MAMP with no error message, which is strange that it now worked. However, now my actually websites URL is not working! When I try to access my live site, I get an error message saying it can not connect to server 'localhost' and it appears like it is trying to redirect me to my 'local' site! How can this have happened. All I have done is export the database?

Staff
4,324 Points
2014-04-14 12:11 pm
It sounds like at some point you used a backup from your local server and restored it to the live server which would rewrite all of your permalinks if you are using a CMS. What CMS are you using to build your site?
n/a Points
2014-04-14 12:26 pm

Thanks again for the reply.

 

I am using Magento, so when I am moving my site from live to local, or local to live, all I need to change it the 'unsecure URL' and the 'secure URL' to {{base_url}}.

I have also not imported any database to my live server for quite some time, I have only exported it, which makes this very strange. Its like as soon as I have imported the most recent database to MAMP, it had an affect on the live server, which I didn't even think was possible?

 

Thanks

 

 

Staff
4,324 Points
2014-04-14 12:37 pm
Importing your live site's database into your development environment should not affect your live site at all, unless you have something configured within the development site that accessed the live database to change something.

To clarify, when it says that it cannot connect to "localhost", is it a message output by your code and your see your domain appropriately in the address bar, or are you getting the message from your browser and are seeing "localhost" within your address bar?
n/a Points
2014-04-14 12:47 pm

I am seeing 'localhost' within the address bar and the message is from my browser. It also still appears when I turn MAMP off.

Staff
4,324 Points
2014-04-14 1:08 pm
It is being cased by something in your code redirecting your domain to localhost. Could you provide me with the domain that you see this on?
n/a Points
2014-04-15 3:10 am

Jeff, thanks for your help, it seems like the issue has now been resolved.

 

The domain is www.whiteparisluxurycandles.co.uk

 

I still am not 100% sure what was causing the problem. As mentioned before I managed to import the database to MAMP after exporting it a third time from my local server, for some reason this time the error message did not show. Once the site was working in MAMP I found that my live site was now redirecting to my 'localhost', meaning for some reason my live site was now no longer working. I restarted my computer, deleted the live database and then re-imported it. After doing this the live site was working again. I still don't know the why this problem occured but I have a feeling it was down to the following redirect code that I placed in my .htaccess file. Have you heard of anyone experiencing similar problem from redirect code in the .htaccess file, and do you have any advice when working with redirect codes?

 

# Redirect non-www to www:

RewriteCond %{HTTP_HOST} !^www\. [NC]

RewriteRule ^(.*)$ http://www.%{HTTP_HOST}/$1 [R=301,L]

 

 

#Redirect your Homepage from HTTPS to HTTP

RewriteCond %{HTTPS} on

RewriteRule ^$ http://%{HTTP_HOST} [L,R]

 

Thanks again for your help.

Staff
4,324 Points
2014-04-15 9:21 am
This code wouldn't have caused it unless your browser may have cached the .htaccess file from another time when you accessed the local development site and could have possibly carried over to your live site but only for your computer.
n/a Points
2014-04-18 12:22 pm

 

 

Hello. 

  I have this problem, I've been following some tutorial videos and have done as such as this in the videos but in the end I get this problem as not solve it, can you help me please. 

I leave the message on the page. 

 

thanks

 

Error Base de Datos: SQL no válida: CREATE TABLE `broadcastmessages` (`broadcastmessages_id` bigint (20) NO AUTO_INCREMENT NULL, `bigint broadcastmessages_user_id` (20) NOT NULL DEFAULT '0 ', `broadcastmessages_attach_id` bigint (20) NOT NULL DEFAULT '0' , `broadcastmessages_media_id` bigint (20) NOT NULL DEFAULT '0 ', `broadcastmessages_subject` varchar (255) NOT NULL DEFAULT'', texto `broadcastmessages_body` NOT NULL, `broadcastmessages_type` tinyint (4) NOT NULL DEFAULT '0', ` broadcastmessages_delivery_date `datetime NOT NULL DEFAULT '0000-00-00 00:00:00 ',` broadcastmessages_subscriber_id `bigint (20) NOT NULL DEFAULT '0',` broadcastmessages_sent `tinyint (4) NOT NULL DEFAULT '0 ',` broadcastmessages_sign_id ` bigint (20) NOT NULL DEFAULT '0 ', PRIMARY KEY (`broadcastmessages_id`)) TYPE = MyISAM MySQL Error : 1064 (Usted tiene un error en su sintaxis SQL, consulte el manual que corresponde a su versión del servidor MySQL para la sintaxis correcta utilizar cerca de 'TYPE = MyISAM' at line 14) Sesión detuvo.

Staff
5,498 Points
2014-04-18 12:39 pm
Hello edgar,

Thank you for your question. We are happy to help, but will need a little more information, such as a link to the video tutorials you are trying to follow, or more information on what you are trying to accomplish.

Can you provide a link to the error, so we can view it?

If you have any further questions, feel free to post them below.
Thank you,

-John-Paul

Post a Comment

Name:
Email Address:
Comment:
Are you a bot?
Submit

Please note: Your name and comment will be displayed, but we will not show your email address.

Related Questions

Here are a few questions related to this article that our customers have asked:
Ooops! It looks like there are no questions about this page.
Would you like to ask a question about this page? If so, click the button below!
Ask a Question

Need more Help?

Search

Ask the Community!

Get help with your questions from our community of like-minded hosting users and InMotion Hosting Staff.

Current Customers

Chat: Click to Chat Now E-mail: support@InMotionHosting.com
Call: 888-321-HOST (4678) Ticket: Submit a Support Ticket

Not a Customer?

Get web hosting from a company that is here to help. Sign up today!