单独数据文件的外键创建中止,但数据文件变短了

显示全部楼层 · 2012-5-21 10:19:41

单独数据文件的外键创建中止,但数据文件变短了

mysql> alter table lineitem add constraint lineitem_pk primary key (l_orderkey,l_linenumber);
Ctrl-C -- sending "KILL QUERY 1" to server ...
Ctrl-C -- query aborted.
ERROR 1317 (70100): Query execution was interrupted
mysql> select count(*) from lineitem;
+----------+
| count(*) |
+----------+
| 59986052 |
+----------+
1 row in set (18.84 sec)
[root@redflag11012501 tpch2]# ll l*
-rw-rw---- 1 mysql mysql98304 06-10 16:28 [email protected]
-rw-rw---- 1 mysql mysql 9226 06-10 15:20 lineitem.frm
-rw-rw---- 1 mysql mysql 9353297920 06-10 15:34 lineitem.ibd
[root@redflag11012501 tpch2]# ll
总计 13146076
-rw-rw---- 1 mysql mysql 8850 06-10 16:16 customer.frm
-rw-rw---- 1 mysql mysql301989888 06-10 16:16 customer.ibd
-rw-rw---- 1 mysql mysql 65 06-10 15:15 db.opt
-rw-rw---- 1 mysql mysql98304 06-10 16:28 [email protected]
-rw-rw---- 1 mysql mysql 9226 06-10 15:20 lineitem.frm
-rw-rw---- 1 mysql mysql 9353297920 06-10 15:34 lineitem.ibd
-rw-rw---- 1 mysql mysql 8692 06-10 16:11 nation.frm
-rw-rw---- 1 mysql mysql98304 06-10 16:11 nation.ibd
-rw-rw---- 1 mysql mysql 8928 06-10 16:23 orders.frm
-rw-rw---- 1 mysql mysql 2000683008 06-10 16:27 orders.ibd
-rw-rw---- 1 mysql mysql 8874 06-10 16:11 part.frm
-rw-rw---- 1 mysql mysql335544320 06-10 16:12 part.ibd
-rw-rw---- 1 mysql mysql 8748 06-10 16:17 partsupp.frm
-rw-rw---- 1 mysql mysql 1430257664 06-10 16:20 partsupp.ibd
-rw-rw---- 1 mysql mysql 8648 06-10 16:11 region.frm
-rw-rw---- 1 mysql mysql98304 06-10 16:11 region.ibd
-rw-rw---- 1 mysql mysql 9226 06-10 16:28 #sql-67f0_1.frm
-rw-rw---- 1 mysql mysql 8804 06-10 16:17 supplier.frm
-rw-rw---- 1 mysql mysql 26214400 06-10 16:17 supplier.ibd
[root@redflag11012501 tpch2]# df
文件系统
1K-块已用可用已用% 挂载点
/dev/mapper/VolGroup00-LogVol00

2453568422068032 120117295% /
/dev/sda1
101086 12734 8313314% /boot
tmpfs
46104576197302842637429243% /dev/shm
/dev/mapper/vg0-datalv

774092544 525946240 20882470472% /user1
[root@redflag11012501 tpch2]# ll l*
-rw-rw---- 1 mysql mysql523010048 06-10 16:39 [email protected]
-rw-rw---- 1 mysql mysql 9226 06-10 15:20 lineitem.frm
-rw-rw---- 1 mysql mysql 9353297920 06-10 15:34 lineitem.ibd
[root@redflag11012501 tpch2]# ll l*
-rw-rw---- 1 mysql mysql889192448 06-10 16:40 [email protected]
-rw-rw---- 1 mysql mysql 9226 06-10 15:20 lineitem.frm
-rw-rw---- 1 mysql mysql 9353297920 06-10 15:34 lineitem.ibd
[root@redflag11012501 tpch2]# ll l*
-rw-rw---- 1 mysql mysql 4332716032 06-10 16:43 [email protected]
-rw-rw---- 1 mysql mysql 9226 06-10 15:20 lineitem.frm
-rw-rw---- 1 mysql mysql 9353297920 06-10 15:34 lineitem.ibd
[root@redflag11012501 tpch2]# ll l*
-rw-rw---- 1 mysql mysql 7499415552 06-10 16:45 [email protected]
-rw-rw---- 1 mysql mysql 9226 06-10 15:20 lineitem.frm
-rw-rw---- 1 mysql mysql 9353297920 06-10 15:34 lineitem.ibd
[root@redflag11012501 tpch2]# ll l*
-rw-rw---- 1 mysql mysql 9226 06-10 16:28 lineitem.frm
-rw-rw---- 1 mysql mysql 8959033344 06-10 16:47 lineitem.ibd

千问 · 2012-5-21 10:19:41

说错,应该是主键,看来过程是
创建临时表
从原表读数据一边创建树
完成后，原表改名
临时表改名
删除原表

千问 · 2012-5-21 10:19:41

[root@redflag11012501 tpch2]# ll
总计 14572780
-rw-rw---- 1 mysql mysql 8850 06-10 16:16 customer.frm
-rw-rw---- 1 mysql mysql301989888 06-10 16:16 customer.ibd
-rw-rw---- 1 mysql mysql 65 06-10 15:15 db.opt
-rw-rw---- 1 mysql mysql 9226 06-10 15:20 lineitem.frm
-rw-rw---- 1 mysql mysql 9353297920 06-10 15:34 lineitem.ibd
-rw-rw---- 1 mysql mysql 8692 06-10 16:11 nation.frm
-rw-rw---- 1 mysql mysql98304 06-10 16:11 nation.ibd
-rw-rw---- 1 mysql mysql 1363148800 06-10 16:26 [email protected]
-rw-rw---- 1 mysql mysql 8928 06-10 15:38 orders.frm
-rw-rw---- 1 mysql mysql 2097152000 06-10 15:41 orders.ibd
-rw-rw---- 1 mysql mysql 8874 06-10 16:11 part.frm
-rw-rw---- 1 mysql mysql335544320 06-10 16:12 part.ibd
-rw-rw---- 1 mysql mysql 8748 06-10 16:17 partsupp.frm
-rw-rw---- 1 mysql mysql 1430257664 06-10 16:20 partsupp.ibd
-rw-rw---- 1 mysql mysql 8648 06-10 16:11 region.frm
-rw-rw---- 1 mysql mysql98304 06-10 16:11 region.ibd
-rw-rw---- 1 mysql mysql 8928 06-10 16:23 #sql-67f0_1.frm
-rw-rw---- 1 mysql mysql 8804 06-10 16:17 supplier.frm
-rw-rw---- 1 mysql mysql 26214400 06-10 16:17 supplier.ibd
[root@redflag11012501 tpch2]# ll o*
-rw-rw---- 1 mysql mysql 2097152000 06-10 15:41 [email protected]
-rw-rw---- 1 mysql mysql 8928 06-10 15:38 orders.frm
-rw-rw---- 1 mysql mysql 2000683008 06-10 16:27 orders.ibd
[root@redflag11012501 tpch2]# ll
总计 13145964
-rw-rw---- 1 mysql mysql 8850 06-10 16:16 customer.frm
-rw-rw---- 1 mysql mysql301989888 06-10 16:16 customer.ibd
-rw-rw---- 1 mysql mysql 65 06-10 15:15 db.opt
-rw-rw---- 1 mysql mysql 9226 06-10 15:20 lineitem.frm
-rw-rw---- 1 mysql mysql 9353297920 06-10 15:34 lineitem.ibd
-rw-rw---- 1 mysql mysql 8692 06-10 16:11 nation.frm
-rw-rw---- 1 mysql mysql98304 06-10 16:11 nation.ibd
-rw-rw---- 1 mysql mysql 8928 06-10 16:23 orders.frm
-rw-rw---- 1 mysql mysql 2000683008 06-10 16:27 orders.ibd
-rw-rw---- 1 mysql mysql 8874 06-10 16:11 part.frm
-rw-rw---- 1 mysql mysql335544320 06-10 16:12 part.ibd
-rw-rw---- 1 mysql mysql 8748 06-10 16:17 partsupp.frm
-rw-rw---- 1 mysql mysql 1430257664 06-10 16:20 partsupp.ibd
-rw-rw---- 1 mysql mysql 8648 06-10 16:11 region.frm
-rw-rw---- 1 mysql mysql98304 06-10 16:11 region.ibd
-rw-rw---- 1 mysql mysql 8804 06-10 16:17 supplier.frm
-rw-rw---- 1 mysql mysql 26214400 06-10 16:17 supplier.ibd
[root@redflag11012501 tpch2]# ll

千问 · 2012-5-21 10:19:41

中止的操作难以理解
好像不是直接删除临时表

千问 · 2012-5-21 10:19:41

数据字典更新了？
mysql> use tpch2;
No connection. Trying to reconnect...
mysql: Unknown OS character set 'GB18030'.
mysql: Switching to the default character set 'latin1'.
Connection id:1
Current database: *** NONE ***
Database changed
mysql> alter table lineitem add constraint lineitem_pk primary key (l_orderkey,l_linenumber);
ERROR 1068 (42000): Multiple primary key defined

千问 · 2012-5-21 10:19:41

mysql> select count(*) from lineitem;
+----------+
| count(*) |
+----------+
| 59986052 |
+----------+
1 row in set (18.84 sec)
再执行以下，看数据量是否有少，innodb引擎一般都没问题的，对于那个临时文件#....frm 没有来得及处理，一般情况下ctrl+c是会处理的

这种情况下，你再执行OPTIMIZE LOCAL TABLE tablename; 重新整理下表吧
生产环境，做alter 操作的时候，切忌ctrl+c 这么做......特别是有复制

千问 · 2012-5-21 10:19:41

修复之后，对于那个临时文件只有自己手工删除了rm -f "#....frm"

千问 · 2012-5-21 10:19:41

原帖由〇〇于 2011-6-10 17:08 发表
数据字典更新了？
mysql> use tpch2;
No connection. Trying to reconnect...
mysql: Unknown OS character set 'GB18030'.
mysql: Switching to the default character set 'latin1'.
Connection id:1
Current database: *** NONE ***
Database changed
mysql> alter table lineitem add constraint lineitem_pk primary key (l_orderkey,l_linenumber);
ERROR 1068 (42000): Multiple primary key defined

这个信息看，你的mysqld服务应该是重新启动过....

千问 · 2012-5-21 10:19:41

是的，我原本想清理一下内存，重新创建的...

千问 · 2012-5-21 10:19:41

貌似数据也没有丢失，主键也生效了
mysql> use tpch2;
No connection. Trying to reconnect...
mysql: Unknown OS character set 'GB18030'.
mysql: Switching to the default character set 'latin1'.
Connection id:1
Current database: *** NONE ***
Database changed
mysql> alter table lineitem add constraint lineitem_pk primary key (l_orderkey,l_linenumber);
ERROR 1068 (42000): Multiple primary key defined
mysql> source /root/qall_my1-16.sql
+--------------+--------------+--------------+------------------+--------------------+----------------------+-----------+--------------+----------+-------------+
| l_returnflag | l_linestatus | sum_qty| sum_base_price | sum_disc_price | sum_charge | avg_qty | avg_price| avg_disc | count_order |
+--------------+--------------+--------------+------------------+--------------------+----------------------+-----------+--------------+----------+-------------+
| A
| F
| 377518399.00 |566065727797.25 |537759104278.0656 |559276670892.116819 | 25.500975 | 38237.151009 | 0.050007 |14804077 |
| N
| F
| 9851614.00 | 14767438399.17 | 14028805792.2114 | 14590490998.366737 | 25.522448 | 38257.810660 | 0.049973 |385998 |
| N
| O
| 750440706.00 | 1125264325266.58 | 1068995512488.7987 | 1111768538120.324852 | 25.498259 | 38233.907986 | 0.050000 |29431057 |
| R
| F
| 377732830.00 |566431054976.00 |538110922664.7677 |559634780885.086257 | 25.508385 | 38251.219274 | 0.049997 |14808183 |
+--------------+--------------+--------------+------------------+--------------------+----------------------+-----------+--------------+----------+-------------+
4 rows in set (4 min 22.77 sec)
+-----------+--------------------+---------------+-----------+----------------+------------------------------------------+-----------------+-----------------------------------------------------------------------------------------------------+
| s_acctbal | s_name
| n_name| p_partkey | p_mfgr | s_address
| s_phone | s_comment
|
+-----------+--------------------+---------------+-----------+----------------+------------------------------------------+-----------------+-----------------------------------------------------------------------------------------------------+
| 9997.03 | Supplier#000021474 | UNITED STATES |746452 | Manufacturer#3 | B1WCuHtuY5Lm4bj,S
| 34-502-253-8876 | posits are. regular braids about th
|
| 9996.13 | Supplier#000048428 | CANADA|873403 | Manufacturer#2 | Z,m5TgvRq7oG4rsmvnE9Bn nemmJff54FkFAgk | 13-611-820-5223 | eodolites along the ideas de
|
| 9994.95 | Supplier#000036836 | BRAZIL| 1436835 | Manufacturer#5 | HJF6WNqa,jTyW8LiX0dU2dmN8YmR tg
| 12-420-313-3017 | eful, special instructions. ironic, blithe theodolites nag quickly pending requests. quiet, even ac |
| 9994.83 | Supplier#000052301 | CANADA|202296 | Manufacturer#5 | tEE5cTg1Xv3tOKaAhfMfCDfybQFISdtt8V | 13-437-784-3548 | re always instructions. ideas must have to detec
|
| 9992.28 | Supplier#000060858 | ARGENTINA | 1010837 | Manufacturer#3 | JY6ikmxTyvQlWD1nhvSygO6QptwWc9YG3vylw| 11-719-537-8037 | ithely idle deposits detect furiously about the even package
|
| 9990.67 | Supplier#000090925 | UNITED STATES | 1640892 | Manufacturer#1 | oFEr9GkQpXQw72 W4Y2DPLmQvIAGHPXMSF1H3p | 34-606-851-5651 | ly even requests. even courts sleep slyly busily final ideas. carefully sly theodolites
|
| 9989.90 | Supplier#000011269 | BRAZIL|686262 | Manufacturer#4 | V,4bksZDdE1Ux3YhH1UE0SeGZjudbbVvb,H3eA 3 | 12-917-310-3275 | egular accounts after the furiously express requests doubt above the slyly
|
| 9989.46 | Supplier#000037658 | UNITED STATES |462645 | Manufacturer#5 | SEh9v7C6WXZo855C5hu6sbkpRni72No
| 34-919-304-8360 | the instructions are furiously even requests.
|
| 9988.34 | Supplier#000023569 | ARGENTINA | 48568 | Manufacturer#3 | EZlxYBTNi,J1HLwEjPUhrlgnB IB2Ale | 11-410-115-9353 | lly pending requests boost furiously. deposits across the fluffily unusual sentiments detect acr|
| 9987.04 | Supplier#000015601 | PERU
| 1890582 | Manufacturer#5 | 2UvlYtOSEoYOCYrgeWxjeIn7NLnx
| 27-993-727-8949 | ronic platelets. final asymptotes affix. silent packages ca
|
+-----------+--------------------+---------------+-----------+----------------+------------------------------------------+-----------------+-----------------------------------------------------------------------------------------------------+
10 rows in set (2.95 sec)
+------------+-------------+-------------+----------------+
| l_orderkey | revenue | o_orderdate | o_shippriority |
+------------+-------------+-------------+----------------+
| 21679558 | 451192.3341 | 1995-01-17|
0 |
| 57978848 | 450250.4648 | 1995-02-19|
0 |
| 24295076 | 437186.7777 | 1995-01-05|
0 |
| 23679299 | 428116.4351 | 1995-02-28|
0 |
| 492164 | 423270.2092 | 1995-02-19|
0 |
| 35312039 | 422403.5320 | 1995-03-05|
0 |
| 820962 | 418407.3101 | 1995-02-11|
0 |
| 26731940 | 417950.7043 | 1995-02-27|
0 |
| 36065633 | 417908.8665 | 1995-02-19|
0 |
| 51262022 | 413274.3434 | 1995-02-11|
0 |
+------------+-------------+-------------+----------------+
10 rows in set (45.07 sec)
+-----------------+-------------+
| o_orderpriority | order_count |
+-----------------+-------------+
| 1-URGENT|105597 |
| 2-HIGH
|105754 |
| 3-MEDIUM|105026 |
| 4-NOT SPECIFIED |104819 |
| 5-LOW |105309 |
+-----------------+-------------+
5 rows in set (15.09 sec)
+------------+----------------+
| n_name | revenue|
+------------+----------------+
| MOROCCO| 540038731.8977 |
| KENYA| 536052927.7305 |
| ETHIOPIA | 520384942.7652 |
| MOZAMBIQUE | 520109603.6710 |
| ALGERIA| 519498315.2213 |
+------------+----------------+
5 rows in set (51.07 sec)
+-----------------+
| revenue |
+-----------------+
| 2014944457.3560 |
+-----------------+
1 row in set (33.49 sec)
+----------------+----------------+--------+----------------+
| supp_nation| cust_nation| l_year | revenue|
+----------------+----------------+--------+----------------+
| ROMANIA| UNITED KINGDOM | 1995 | 529821664.1735 |
| ROMANIA| UNITED KINGDOM | 1996 | 541319833.0274 |
| UNITED KINGDOM | ROMANIA| 1995 | 528819804.2166 |
| UNITED KINGDOM | ROMANIA| 1996 | 525485490.0643 |
+----------------+----------------+--------+----------------+
4 rows in set (1 min 8.78 sec)
+--------+------------+
| o_year | mkt_share|
+--------+------------+
| 1995 | 0.03780949 |
| 1996 | 0.03851161 |
+--------+------------+
2 rows in set (1 min 13.86 sec)
+-----------+--------+----------------+
| nation| o_year | sum_profit |
+-----------+--------+----------------+
| ALGERIA | 1998 | 352822350.0693 |
| ALGERIA | 1997 | 596671435.2478 |
| ALGERIA | 1996 | 602177243.0192 |
| ALGERIA | 1995 | 606831210.8801 |
| ALGERIA | 1994 | 595415097.2158 |
| ALGERIA | 1993 | 601347225.0045 |
| ALGERIA | 1992 | 594128993.7166 |
| ARGENTINA | 1998 | 366005512.6697 |
| ARGENTINA | 1997 | 614815785.9445 |
| ARGENTINA | 1996 | 621181784.9130 |
+-----------+--------+----------------+
10 rows in set (4 min 36.39 sec)