保留字错误

liyihongcug

ERROR 1064 (42000): You have an error in your SQL syntax
I try to run the following query. INSERT INTO sections (order,edit,remove,section,type)             VALUES('blah','blah','blah','blah','blah');In response I got the the message:

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 'order,edit,remove,section,type) VALUES('blah','blah','blah','blah','blah')' at line 1.

Any idea to fix ERROR 1064 (42000): You have an error in your SQL syntax;?
Answer No: 51
Your above query contains reserved words in it those are causing error 1064 (42000). ORDER is a reserved word in MySQL and in standard SQL as well. If the column name in the table is ORDER, then put backquotes (`) around the word ORDER when using it as a column name. For Example:INSERT INTO sections (`order`,edit,remove,section,type)             VALUES('blah','blah','blah','blah','blah');Best practice: Don't use reserved words for object (table, column,) or to use backquotes beside every column you used like:INSERT INTO sections (`order`,`edit`,`remove`,`section`,`type`)             VALUES('blah','blah','blah','blah','blah');

liyihongcug

解决思路
配置文件加
default-character-set=utf8
character-set-server=utf8
default-collation=utf8_unicode_ci

之后导mysqldump -c -------以加这个
LOCK TABLES `a_copy` WRITE;
INSERT INTO `a_copy` (`userName`, `mon`, `val`, `dd`, `atime`, `id`) VALUES ('a','1',NULL,'02','2010-12-01 13:48:15',1),('a','1',NULL,'3O','2010-12-02 13:48:20',2),('b','2',NULL,'o2','2010-12-03 13:48:24',3),('b','2',NULL,'o','2010-12-05 13:48:28',4),('11枚',NULL,NULL,NULL,'2010-12-06 13:48:32',5),('110',NULL,NULL,NULL,'2010-11-30 13:48:36',6),('11o',NULL,NULL,NULL,'2010-12-08 13:48:40',7);
这样没有问题

不是流氓

or Example:INSERT INTO sections (`order`,edit,remove,section,type)             VALUES('blah','blah','blah','blah','blah')

不是有错误提示了么？

liyihongcug

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to usPosted by wildfeed on July 26, 2007 at 2:52pm
Project: Mailing List Manager
Version: master
Component: Code
Category: bug report
Priority: normal
Assigned: Unassigned
Status: closed (fixed)
Jump to:
Most recent comment

Description
Using module version 1.12 through 1.17, Drupal 5.1, PHP 5.1.6, mysql 5.0.19:

I get the following error when I click on mailing lists tab in user profile

user warning: 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 address = 'color.art@mac.com' LIMIT 1' at line 2 query: SELECT 1 FROM WHERE address = 'color.art@mac.com' LIMIT 1 in /Applications/MAMP/htdocs/includes/database.mysql.inc on line 172.

Trying to subscribe to a mailing list gets the following

* user warning: 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 address = 'color.art@mac.com' LIMIT 1' at line 2 query: SELECT 1 FROM WHERE address = 'color.art@mac.com' LIMIT 1 in /Applications/MAMP/htdocs/includes/database.mysql.inc on line 172.
* user warning: 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 address = 'color.art@mac.com' LIMIT 1' at line 2 query: SELECT 1 FROM WHERE address = 'color.art@mac.com' LIMIT 1 in /Applications/MAMP/htdocs/includes/database.mysql.inc on line 172.
* user warning: 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 address = 'color.art@mac.com'' at line 1 query: SELECT 1 FROM WHERE address = 'color.art@mac.com' in /Applications/MAMP/htdocs/includes/database.mysql.inc on line 172.
* user warning: 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 '( hash, address ) VALUES ( '40', 'color.art@mac.com' )' at line 1 query: INSERT INTO ( hash, address ) VALUES ( '40', 'color.art@mac.com' ) in /Applications/MAMP/htdocs/includes/database.mysql.inc on line 172.
* user warning: Table 'wildbase._slog' doesn't exist query: INSERT INTO _slog ( tai, address, fromline, edir) VALUES ( '2007-07-26 09:53:25', 'color.art@mac.com', 'color.art@mac.com', '+') in /Applications/MAMP/htdocs/includes/database.mysql.inc on line 172.

Login or register to post comments
Comments
#1Posted by jerdavis on February 23, 2008 at 3:56am
Status: active » fixed

Some changes were just committed to HEAD which may fix this, also a new development version was generated which should show up shortly. If you can still reproduce the behavior above with this version please let us know.
Login or register to post comments #2Posted by Anonymous (not verified) on March 8, 2008 at 4:12am
Status: fixed » closed (fixed)

Automatically closed -- issue fixed for two weeks with no activity.
Login or register to post comments #3Posted by kwamecg on October 19, 2008 at 11:00am
Title: Error in SQL sytax » You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to us

$wball=mysql_query("SELECT * FROM node_revisions,term_node where node_revisions.vid=term_node.vid AND term_node.tid=3 ORDER BY timestamp DESC limit 6");

this is the query that is giving me the error....
Login or register to post comments #4Posted by HEROESgifted on July 23, 2010 at 2:44pm
How do I fix this error message?

user warning: 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 ' events.field_date_value FROM node n INNER JOIN signup s ON s.nid = n.nid LEFT J' at line 1 query: SELECT n.nid, n.title, n.type, s.status AS signup_status, COUNT(s_l.nid) AS signup_total, s.close_signup_limit AS signup_close_signup_limit, courses.field_date_value, date., events.field_date_value FROM node n INNER JOIN signup s ON s.nid = n.nid LEFT JOIN signup_log s_l ON s.nid = s_l.nid LEFT JOIN content_field_date `courses` ON courses.vid = n.vid LEFT JOIN `date` ON date.vid = n.vid LEFT JOIN content_field_date `events` ON events.vid = n.vid GROUP BY n.nid, n.title, signup_status, signup_close_signup_limit ORDER BY n.title ASC LIMIT 0, 25 in /home2/heroesgi/public_html/modules/signup/signup/includes/admin.signup_administration.inc on line 76.
Login or register to post comments #5Posted by milziv on July 24, 2010 at 11:29am
Hi there,

I have the same error message...any workaround for this issue? Much appreicated.

user warning: 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 '= 424 ) count_alias' at line 3nquery: SELECT COUNT(*) FROM (SELECT send.sid AS sid FROM send send WHERE .nid = 424 ) count_alias in /www/itsmforg/itsmfSerbia/docroot/drupal/sites/all/modules/views/includes/view.inc on line 745.

user warning: 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 '= 424 LIMIT 0, 20' at line 8nquery: SELECT send.sid AS sid, send.timestamp AS send_timestamp, name, uid, send.subject AS send_subject, send.message AS send_message FROM send send WHERE .nid = 424 LIMIT 0, 20 in /www/itsmforg/itsmfSerbia/docroot/drupal/sites/all/modules/views/includes/view.inc on line 771.

Thanks
Login or register to post comments

liyihongcug

反复测试 字符集问题

windows下安装mysql 5.1后在事件查看器里有如下错误信息

‘–default-character-set’ is deprecated and will be removed in a future release. Please use ‘–character-set-server’ instead.
这句话的意思是”default-character-set”在未来版本里将不再使用，而是使用”character-set-server’”来替代，根据提示，我们在my.ini里修改信息，

把两处default-character-set全部替换为character-set-server，这样就不会有错误提示了。

liyihongcug

http://www.mysqlab.net/blog/        这个质量很高

http://imysql.cn/node/485
ALTER  TABLE table_name RENAME TO new_table_name

例如 ALTER  TABLE admin_user RENAME TO a_user

http://chenxu.yo2.cn/articles/in ... e-distribution.html -------源码安装

liyihongcug

MYSQL数据库导入SQL文件出现乱码如何解决
时间:2010-07-28 16:31来源:草根站长 作者:编辑整理 责任编辑: 雨儿 我要投稿
导入的方法: mysqluse test; mysql source c:/test.sql 导入数据时，如果目标数据库或表是UTF-8字符集的，而导入SQL中有中文，可能在最终结果中出现乱码，此时只需在导入的SQL文件第一行加入如下内容即可。 /*!40101 SET NAMES utf8 */; 以下内容转载: 使用
　

导入的方法:

mysql>use test;
mysql> source c:/test.sql

导入数据时，如果目标数据库或表是UTF-8字符集的，而导入SQL中有中文，可能在最终结果中出现乱码，此时只需在导入的SQL文件第一行加入如下内容 即可。
/*!40101 SET NAMES utf8 */;

以下内容转载:

使用SQLyog工具导出MySQL数据时，当数据量大时，导出 不会错误，但导入时会出 现错误，比如mysql数据库导入SQL文件时出现乱码。
使用命令行导入被导出的数据时会出现如下这类的错误：
ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version
for the right syntax to use /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_BSD SET_CLIENT */’ at line 1
这是因为命令行模式下不能认出SQL文 件格式造成，可以将SQL文件另存为UTF-8 NO BOM格式，然后进行导入。
另外在导入数据时，如果目标数据库或表是UTF-8字符集的， 而导入SQL中有中文，可能在最终结果中出现乱码，此时只需在导入的SQL文件第一行加入如下内容即可。
/*!40101 SET NAMES utf8 */;

意 只能合并文本文件， 二进制格式的文件只能借助特殊工具，而且要看二进制文件是什么格式的
合并文本文件的方法

cat file1.out file2.out > file3.out

liyihongcug

echo '/*!40101 SET NAMES utf8 */;' | cat - a.log > b.txt
  这里 必须 b 的名字 跟a不同
否则 b会覆盖a。
导致b只有一条语句
*!40101 SET NAMES utf8 */

make up
常见的数据库错误的解决方法！
这里所说的是MySQL Query Error提示错误的解决方法，部分是完全可以自己动手解决的，没有多大必要一遍遍问。除非是常规方法不能解决的~
下面是常见安装插件会出现的数据库错误信息提示：

CODEuplicate column name 'allowrank'
升级数据偶尔这样吧，表示这个表已经存在。但是如果是安装的话不影响下面的步骤。插件编写过程出现这个问题，就换个表名吧。
CODE:Error: Duplicate entry 'aa2HV2' for key 1
Errno.: 1062
一般是键值被重复输入了，导致的重复错误，可以重新建表来解决这个问题。

CODE:Error: Unknown system variable 'NAMES'
Errno.: 1193
导入数据时候容易出现的。打开SQL文件
1、删除SET NAMES'gbk'
2、找到ENGINE=MYISAM DEFAULT CHARSET=gbk，替换为：TYPE=MYISAM
3、找到ENGINE=HEAP DEFAULT CHARSET=gbk，替换为：TYPE=HEAP
4、character set gbk collate gbk_bin，删除。
5、重新恢复即可。

CODE: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 'DEFAULT CHARSET=gbk' at line 1
Errno.: 1064
一般是编码不符，打开安装文件，看见数据库升级代码，有'DEFAULT CHARSET=gbk'的就可以删除了。

CODE: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 's XXXXX
Errno.: 1064
比较棘手的问题，一般是数据库代码写入时类型冲突了，如浮点写了汉字就会这样，检查自己的设置，是否该写数字的写了英文或者汉字。如果在插件编写过程中遇到的，就检查数据库语法的正确性。
如果是数据导入，可以用restore.php导入可避免。

CODE:Error: Unknown column 'ff.threadperms' in 'field list'
这种最好解决，一般看见Unknown column提示的，表示你的数据表少了这个表，手动创建即可解决。

CODE:Error: Access denied for user: 'UNKNOWN_USER@localhost' (Using password: YES)
这种问题不知道说什么好~数据库密码用户填错了都还不清楚，咨询空间商。

CODE:Can't DROP 'aaaallowrank'; check that column/key exists
一般是反安装容易出现的，表示该表不存在。其实也没有问题了，不存在的话其实也无须反安装了。

CODE:Error: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (gbk_chinese_ci,COERCIBLE) for operation '='
Errno.: 1267
数据库字符集不符所导致的，请到PHPMYADMIN把数据表的字符集修改下吧。
CODEiscuz! info: MySQL Query Error
User: admin
Time: 2006-4-19 12:32pm
Script: /bbs2/index.php
SQL: DELETE FROM cdb_subscriptions WHERE lastpost<'1145421168'-7776000
Error: Unknown column 'lastpost' in 'where clause'
Errno:1054
An error report has been dispatched to our administrator.

原因：subscriptions表缺少lastpost字段
解决方法：在phpMyAdmin里或DZ论坛后台“升级数据库”输入下面语句

ALTER TABLE cdb_subscriptions ADD lastpost int( 10 ) UNSIGNED NOT NULL DEFAULT '0' AFTER tid;
CODE:连接出错信息1251:
#1251 - Client does not support authentication protocol requested by server; consider upgrading MySQL client
解决方法:
mysql> UPDATE mysql.user SET Password = OLD_PASSWORD('密码') WHERE Host = 'localhost(主机名)或IP' AND User = '数据库用户名';
mysql> FLUSH PRIVILEGES;
mysql> use mysql
mysql> update user set password=old_password('cnscn');
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 1
Current database: mysql

Query OK, 5 rows affected (0.01 sec)
Rows matched: 5 Changed: 5 Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

CODEiscuz! info: MySQL Query Error

User: imoker
Time: 2006-10-17 12:45pm
Script: /bbs/viewthread.php

SQL: SELECT expiration, keywords, relatedthreads FROM cdb_relatedthreads WHERE tid='3613'
Error: Can't find file: 'cdb_relatedthreads.MYI' (errno: 2)
Errno.: 1017
Similar error report has beed dispatched to administrator before.

错误分析：服务器非法关机，有可能会对部分数据库造成一定影响。

一般运行会报错
**.MYD不能打开。
Can't find file: '***.MYI'

解决方法：用你下载的论坛压缩包里面utilities目录下的repair.php修复一下
具体方法：上传repair.php到论坛根目录下，然后在浏览器运行
http://你的论坛地址/repair.php
一般点击第一个链接“repair.php?html=1&check=1”就可以修复了,
如果不能修复可以尝试点击第二个连接“repair.php?check=1&iterations=5”

就可以了。

温馨提示：修复完表操作以后一定记得要从服务器删除这个文件repair.php
自己的整理，10月17日更新，还有补充的就说明下吧。1005：创建表失败
1006：创建数据库失败
1007：数据库已存在，创建数据库失败
1008：数据库不存在，删除数据库失败
1009：不能删除数据库文件导致删除数据库失败
1010：不能删除数据目录导致删除数据库失败
1011：删除数据库文件失败
1012：不能读取系统表中的记录
1020：记录已被其他用户修改
1021：硬盘剩余空间不足，请加大硬盘可用空间
1022：关键字重复，更改记录失败
1023：关闭时发生错误
1024：读文件错误
1025：更改名字时发生错误
1026：写文件错误
1032：记录不存在
1036：数据表是只读的，不能对它进行修改
1037：系统内存不足，请重启数据库或重启服务器
1038：用于排序的内存不足，请增大排序缓冲区
1040：已到达数据库的最大连接数，请加大数据库可用连接数
1041：系统内存不足
1042：无效的主机名
1043：无效连接
1044：当前用户没有访问数据库的权限
1045：不能连接数据库，用户名或密码错误
1048：字段不能为空
1049：数据库不存在
1050：数据表已存在
1051：数据表不存在
1054：字段不存在
1065：无效的SQL语句，SQL语句为空
1081：不能建立Socket连接
1114：数据表已满，不能容纳任何记录
1116：打开的数据表太多
1129：数据库出现异常，请重启数据库
1130：连接数据库失败，没有连接数据库的权限
1133：数据库用户不存在
1141：当前用户无权访问数据库
1142：当前用户无权访问数据表
1143：当前用户无权访问数据表中的字段
1146：数据表不存在
1147：未定义用户对数据表的访问权限
1149：SQL语句语法错误
1158：网络错误，出现读错误，请检查网络连接状况
1159：网络错误，读超时，请检查网络连接状况
1160：网络错误，出现写错误，请检查网络连接状况
1161：网络错误，写超时，请检查网络连接状况
1062：字段值重复，入库失败
1169：字段值重复，更新记录失败
1177：打开数据表失败
1180：提交事务失败
1181：回滚事务失败
1203：当前用户和数据库建立的连接已到达数据库的最大连接数，请增大可用的数据库连接数或重启数据库
1205：加锁超时
1211：当前用户没有创建用户的权限
1216：外键约束检查失败，更新子表记录失败
1217：外键约束检查失败，删除或修改主表记录失败
1226：当前用户使用的资源已超过所允许的资源，请重启数据库或重启服务器
1227：权限不足，您无权进行此操作
1235：MySQL版本过低，不具有本功能

本文作者:
本文地址：http://www.bhcode.net/article/20110330/17555.html