一步一步mysql (二) 之常见问题

问题一： Access denied for user 'drmtest'@'localhost' (using password: YES)

当创建一个用户：grant all on test.* to user1@% identified by "test" ;

此时在远程机器上登录，完全正常；

在本机上登录时：

出现问题如下：

[XXX@localhost ~]$ mysql -udrmtest -p
Enter password: 
ERROR 1045 (28000): Access denied for user 'drmtest'@'localhost' (using password: YES)

原因与解决办法：

上面只指定了远程登录权限，并未开启本地登录权限

mysql> grant all on test.* to drmtest@localhost identified by "test";
Query OK, 0 rows affected (0.12 sec)

再用本地登录OK

问题二：Can't update table 'user_info' in stored function/trigger 

创建一个触发器，如下：

DELIMITER |CREATE TRIGGER test_tgr2 after insert   on test.user_info   for each rowBEGINupdate test.user_info set name="123" where name="auto" ;END|DELIMITER ;

当我执行如下命令时：

mysql> insert test.user_info(name,passwd) values("au","adf");
ERROR 1442 (HY000): Can't update table 'user_info' in stored function/trigger  because it is already used by statement which invoked this stored function/trigger.

原因与解决办法：

这是因为在insert user_info 的时候，该表会被锁上的，所以为出那样情况；对于触发器监控与操作同一张表，还没有找到解决方案，我目前是避开这种情况，操作两个不同一表

DELIMITER |CREATE TRIGGER test_tgr2 after insert  on test.server_info   for each rowBEGINupdate test.user_info set name="123" where name="auto" ;END|DELIMITER ;

问题三：Not allowed to return a result set from a trigger

mysql> source ~/coding/mysql/test.sql;
ERROR 1415 (0A000): Not allowed to return a result set from a trigger

在触发器中加入了一样条语句：desc table1 ;

原因及解决办法：

触发器中不允许有值返回，去掉desc，这个可以用存储过程，后面有介绍

问题四：Trigger in wrong schema

mysql> source ~/coding/mysql/test.sql;
ERROR 1435 (HY000): Trigger in wrong schema

test.sql是创建一个触发器的脚本，内容：监视一个数据库的table1的insert 操作，当发生时，向另一个数据的table2中做修改操作；

原因及

所监视的表不在当前数据库中，需要更换当前数据库

问题五：mysql联合主键的自增问题

网上查看资料的时候，偶然看到一篇文章谈到mysql联合主键自增长的问题，该文章提到：

 

当多列被设为联合主键时，auto_increment属性得列并不是自顾自得一味的增长。
而是配合其他主键的列。
例如：

id和vid为联合主键，vid为auto auto_increment，
当id为1时，vid自动增长；
当id改变为2时，vid重新归1，从1开始增长。

 

     但是我按照该方法测试的时候，却无法设置联合主键vid自增长？

执行命令：ALTER TABLE `test_table` CHANGE `vid` `vid` int(11) not null auto_increment;
提示错误：ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key。

     当时百思不得其解，开始以为是数据库有数据的原因，后来才发现是因为我用的存储引擎是InnoDB的原因。对于AUTO_INCREMENT类型的字段，InnoDB中必须包含只有该字段的索引，但是在MyISAM表中，可以和其他字段一起建立联合索引。

     然后我又建立2张表test1table，test2table用来测试，2张表的结构都是如下三个字段：id，vid，name。id，vid为联合主键，存储引擎都是MyISAM。

不同的是：test1table表是id自增长；test2table是vid自增长。
插入一些数据后发现：
test1table表id只会自增，不会根据vid的值恢复到1；
而test2table表vid则会根据id的值的变化，而恢复到1。
即如果是联合主键第一列（id）自增时，则只会自己自增，不会根据第二列（vid）的值恢复到1。
只有在联合主键第二列（vid），才会出现我们预期的效果，根据第一列的值的变化会将只恢复到1。

结论：
1、要使用mysql联合主键自增，需使用MyISAM作为存储引擎。
2、使用联合主键自增的时候，自增键不能是主键最左的键

参考：http://zhuchao.org/mysql%E8%81%94%E5%90%88%E4%B8%BB%E9%94%AE%E8%87%AA%E5%A2%9E%E9%97%AE%E9%A2%98-24