Hi everybody. I just joined since
[EMAIL PROTECTED] had the
closest hits for my question via google
(entries from Oct. 2000). That list seems
to no longer exist and this seems to be the
most specific applicable list.
My problem:
I get the following MySQL error using Perl DBI.
Error:
Duplicate entry '2002-09-24' for key 3
While Executing:
insert into AlcatelSC_port_perf
(node_id, shf_no, slot_no, port_no, circ_no, filename,
Col_Date, Col_Time, Class, Stat_ID,
span, Poll_Method, Value, Error_ID, roll_Error_ID) VALUES
(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
For Values:
(26, 1, 8, 1, 0, 2002092461, 2002/09/24, 15:01:36, 6, 1083, 15, 1, 0, 0, )
Perl Says:
DBD::mysql::st execute failed: Duplicate entry '2002-09-24' for key 3 at
../NEN/PNIMS/LoadStat.pm line 234.
Problem is, key 3 ain't unique:
mysql> show index from AlcatelSC_port_perf;
+---------------------+------------+----------+--------------+-------------+
-----------+-------------+----------+--------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name |
Collation | Cardinality | Sub_part | Packed | Comment |
+---------------------+------------+----------+--------------+-------------+
-----------+-------------+----------+--------+---------+
| AlcatelSC_port_perf | 0 | portin | 1 | stat_id |
A | 207 | NULL | NULL | |
| AlcatelSC_port_perf | 0 | portin | 2 | node_id |
A | 63213 | NULL | NULL | |
| AlcatelSC_port_perf | 0 | portin | 3 | shf_no |
A | 63213 | NULL | NULL | |
| AlcatelSC_port_perf | 0 | portin | 4 | slot_no |
A | 83106 | NULL | NULL | |
| AlcatelSC_port_perf | 0 | portin | 5 | port_no |
A | 203283 | NULL | NULL | |
| AlcatelSC_port_perf | 0 | portin | 6 | circ_no |
A | 204756 | NULL | NULL | |
| AlcatelSC_port_perf | 0 | portin | 7 | filename |
A | 28256363 | NULL | NULL | |
| AlcatelSC_port_perf | 1 | fname | 1 | filename |
A | 256 | NULL | NULL | |
| AlcatelSC_port_perf | 1 | dtin | 1 | Col_Date |
A | 5 | NULL | NULL | |
+---------------------+------------+----------+--------------+-------------+
-----------+-------------+----------+--------+---------+
9 rows in set (0.00 sec)
In fact, key 3 consisting of col_date is extremely non_unique:
mysql> select col_date, count(*) from AlcatelSC_port_perf
-> group by col_date order by col_date;
+------------+----------+
| col_date | count(*) |
+------------+----------+
| NULL | 265795 |
| 2002-09-20 | 9263700 |
| 2002-09-22 | 10412618 |
| 2002-09-23 | 8196439 |
| 2002-09-24 | 117811 |
+------------+----------+
5 rows in set (2 min 36.82 sec)
I've successfully inserted 28256363 records using the same
INSERT statement shown above with varying values in the place
holders, which leads to my last remaining hunch on the cause
of the problem.
Is 28 million records just stupid in this situation?
I'm fine on available disk space, but the MYD file for this
table is ~2Gig while the MYI file is ~9Gig which seems like a lot.
On the other hand, this seems like a strange way for the table to
tell me it's too big and now corrupt.
If you don't have technical advice, maybe you have advice on
where I can take this question, whether I should try to register
this as a bug or take a reality pill and resign myself to either
working with less data in one table or paying for a system that
can handle it.
Thanks;
Jeremy
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
