mysql - 插入..从存储过程中选择删除的列上出现错误

Question

MySQL 版本 5.5.27

更新2:很确定这是一个很大的测试，从头开始进行了具有相同行为的测试。 MySQL 网站上存在漏洞，并将更新结果。

更新:如果我从存储过程中的临时表中删除该列，它可以正常工作。仅当我用手放下色谱柱时才会出现此问题。

以下行在存储过程中失败，并出现错误:

mysql> insert into player_record_week select * from pr_temp;

mysql> call insert_agg_week('2013-02-26');
ERROR 1054 (42S22): Unknown column 'prod_gamestats.pr_temp.total_battles' in 'field list'

如果我手动运行它，它就可以工作。

mysql>  insert into player_record_week select * from pr_temp;
Query OK, 211708 rows affected (2.23 sec)
Records: 211708  Duplicates: 0  Warnings: 0

如果我将total_battles 列保留在临时表中，它将失败并出现有关列计数不匹配的错误。表 pr_temp 是通过复制player_record_week 表创建的。

| insert_agg_week |          | CREATE PROCEDURE     `insert_agg_week`(insert_week date)
begin
delete from player_record_week where stat_week = insert_week;
delete from player_tank_record_week where stat_week = insert_week;
delete from player_ratings_week_agg where stat_week = insert_week;
delete from player_ratings_week_per where stat_week = insert_week;
delete from global_week where stat_week = insert_week;
delete from global_tank_week where stat_week = insert_week;
insert into player_record_week select * from pr_temp;
insert into player_tank_record_week select * from ptr_temp;
insert into player_ratings_week_agg select * from pratings_temp_agg;
insert into player_ratings_week_per select * from pratings_temp_per;
insert into global_week select * from global_week_temp;
insert into global_tank_week select * from global_tank_week_temp;
end | utf8                 | utf8_general_ci      | latin1_swedish_ci  |

如果我保留额外的列，存储过程将因列计数不匹配而出错。

如果我删除额外的列，存储过程会因引用我删除的列而出错:

mysql> call insert_agg_week('2013-02-26');
ERROR 1054 (42S22): Unknown column 'prod_gamestats.pr_temp.total_battles' in 'field list'

如果我手动运行命令，它运行不会出现问题:

mysql> delete from player_record_week where stat_week = '2013-02-26';
Query OK, 0 rows affected (0.00 sec)

mysql> delete from player_tank_record_week where stat_week = '2013-02-26';
Query OK, 0 rows affected (0.00 sec)

mysql> delete from player_ratings_week_agg where stat_week = '2013-02-26';
Query OK, 0 rows affected (0.52 sec)

mysql> delete from player_ratings_week_per where stat_week = '2013-02-26';
Query OK, 0 rows affected (0.09 sec)

mysql> delete from global_week where stat_week = '2013-02-26';
Query OK, 0 rows affected (0.00 sec)

mysql> delete from global_tank_week where stat_week = '2013-02-26';
Query OK, 0 rows affected (0.00 sec)

mysql>  insert into player_record_week select * from pr_temp;
Query OK, 211708 rows affected (2.23 sec)
Records: 211708  Duplicates: 0  Warnings: 0

mysql>  insert into player_tank_record_week select * from ptr_temp;
Query OK, 1618240 rows affected (25.61 sec)
Records: 1618240  Duplicates: 0  Warnings: 0

mysql>  insert into player_ratings_week_agg select * from pratings_temp_agg;
Query OK, 211708 rows affected (1.86 sec)
Records: 211708  Duplicates: 0  Warnings: 0

mysql>  insert into player_ratings_week_per select * from pratings_temp_per;
Query OK, 41648 rows affected (0.39 sec)
Records: 41648  Duplicates: 0  Warnings: 0

mysql>  insert into global_week select * from global_week_temp;
Query OK, 21 rows affected (0.00 sec)
Records: 21  Duplicates: 0  Warnings: 0

mysql>  insert into global_tank_week select * from global_tank_week_temp;
Query OK, 257 rows affected (0.00 sec)
Records: 257  Duplicates: 0  Warnings: 0

这是两个表:

mysql> describe pr_temp;
+------------------------+-----------------------+------+-----+---------+-------+
| Field                  | Type                  | Null | Key | Default | Extra |
+------------------------+-----------------------+------+-----+---------+-------+
| player_id              | int(10) unsigned      | NO   | PRI | NULL    |       |
| stat_week              | date                  | NO   | PRI | NULL    |       |
| insert_date            | date                  | NO   |     | NULL    |       |
| tier_weight            | float                 | YES  |     | 0       |       |
| tier_weight_adj        | float                 | YES  |     | 0       |       |
| battles_count          | int(10) unsigned      | YES  |     | NULL    |       |
| wins                   | mediumint(8) unsigned | YES  |     | NULL    |       |
| losses                 | mediumint(8) unsigned | YES  |     | NULL    |       |
| survived_battles       | mediumint(8) unsigned | YES  |     | NULL    |       |
| spotted                | mediumint(8) unsigned | YES  |     | NULL    |       |
| hits_percents          | tinyint(3) unsigned   | YES  |     | NULL    |       |
| capture_points         | mediumint(8) unsigned | YES  |     | NULL    |       |
| damage_dealt           | int(10) unsigned      | YES  |     | NULL    |       |
| frags                  | mediumint(8) unsigned | YES  |     | NULL    |       |
| dropped_capture_points | mediumint(8) unsigned | YES  |     | NULL    |       |
| max_xp                 | smallint(5) unsigned  | YES  |     | NULL    |       |
| battle_avg_xp          | smallint(5) unsigned  | YES  |     | NULL    |       |
| xp                     | int(10) unsigned      | YES  |     | NULL    |       |
| integrated_rating      | smallint(5) unsigned  | YES  |     | NULL    |       |
| efficiency             | smallint(6)           | YES  |     | NULL    |       |
| efficiency_adj         | smallint(6)           | YES  |     | NULL    |       |
| wn7                    | smallint(6)           | YES  |     | NULL    |       |
| wn7_adj                | smallint(6)           | YES  |     | NULL    |       |
+------------------------+-----------------------+------+-----+---------+-------+
23 rows in set (0.00 sec)

mysql> describe player_record_week;
+------------------------+-----------------------+------+-----+---------+-------+
| Field                  | Type                  | Null | Key | Default | Extra |
+------------------------+-----------------------+------+-----+---------+-------+
| player_id              | int(10) unsigned      | NO   | PRI | NULL    |       |
| stat_week              | date                  | NO   | PRI | NULL    |       |
| insert_date            | date                  | NO   |     | NULL    |       |
| tier_weight            | float                 | YES  |     | 0       |       |
| tier_weight_adj        | float                 | YES  |     | 0       |       |
| battles_count          | int(10) unsigned      | YES  |     | NULL    |       |
| wins                   | mediumint(8) unsigned | YES  |     | NULL    |       |
| losses                 | mediumint(8) unsigned | YES  |     | NULL    |       |
| survived_battles       | mediumint(8) unsigned | YES  |     | NULL    |       |
| spotted                | mediumint(8) unsigned | YES  |     | NULL    |       |
| hits_percents          | tinyint(3) unsigned   | YES  |     | NULL    |       |
| capture_points         | mediumint(8) unsigned | YES  |     | NULL    |       |
| damage_dealt           | int(10) unsigned      | YES  |     | NULL    |       |
| frags                  | mediumint(8) unsigned | YES  |     | NULL    |       |
| dropped_capture_points | mediumint(8) unsigned | YES  |     | NULL    |       |
| max_xp                 | smallint(5) unsigned  | YES  |     | NULL    |       |
| battle_avg_xp          | smallint(5) unsigned  | YES  |     | NULL    |       |
| xp                     | int(10) unsigned      | YES  |     | NULL    |       | 
| integrated_rating      | smallint(5) unsigned  | YES  |     | NULL    |       |
| efficiency             | smallint(6)           | YES  |     | NULL    |       |
| efficiency_adj         | smallint(6)           | YES  |     | NULL    |       |
| wn7                    | smallint(6)           | YES  |     | NULL    |       |
| wn7_adj                | smallint(6)           | YES  |     | NULL    |       |
+------------------------+-----------------------+------+-----+---------+-------+
23 rows in set (0.00 sec)

Answer 1

MySQL 中的错误，已在 5.6.6 中修复；初始过程运行后元数据不会更新。

http://bugs.mysql.com/bug.php?id=32868

错误报告中的解决方法是刷新缓存:创建或替换 View tmpview AS SELECT 1;