Known bug that bit me in the ....

[CamaroLT]

I wrote a tool that monitors some of the machines on our network, parses and then dumps the data into a MySQL table. However, there was a problem in MySQL 4 in which you can't have a field varchar thats greater than 255 characters. I didn't know this at the time I wrote the tool, and sometimes the reports being processed will come back with a string that is longer than 255 characters.

So now I'm sitting with a MASSIVE table (For what its worth, this table should only have a few thousand rows, maybe 10k rows at most. I'm now staring at a row count of over 207k rows.

mysql> describe tbl_reports;
+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| ReportID   | int(11)      |      | PRI | NULL    | auto_increment |
| ReportText | varchar(255) | YES  |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+

The code does a check to see if the reported string already exists in this table. If it does, it remembers the ID and then inserts appropriate numbers into another table. If it doesn't, it creates a new row in this table, gets the ID, and resumes.

However, the bug in my code was that when I did a comparison between ReportText (Max of 255 chars) and the string > 255 chars, the code would ALWAYS insert the data. Bad.

I've corrected the code that does the checking, and its functioning fine. However, I'm still stuck with a tonne of messages that need to be removed.

What I have come up with so far is this:

mysql> select Counts,ReportID from (select count(*) Counts,ReportID, left(ReportText,50) from tbl_reports group by ReportText) t where Counts>1;
+--------+----------+
| Counts | ReportID |
+--------+----------+
|  30184 |   933036 |
|  94433 |   889878 |
|    523 |  1014495 |
|  23446 |   819814 |
|  54800 |   834688 |
+--------+----------+
5 rows in set (3.46 sec)

So I have the 5 groups of messages that bombed out.

To get the list of dupe messages that match 933036:

select ReportID from (select ReportID from tbl_reports where ReportText=(select ReportText from tbl_reports where ReportID=933036) and ReportID<>933036) e;

However, when I do this:

delete from tbl_reports where ReportID in (select ReportID from tbl_reports where ReportText=(select ReportText from tbl_reports where ReportID=933036) and ReportID<>933036);     
ERROR 1093 (HY000): You can't specify target table 'tbl_reports' for update in FROM clause

How can I get around this problem?

 

[DonQuichote]

SELECT ReportText INTO @ReportText FROM tbl_reports WHERE ReportID=933036;
DELETE FROM tbl_reports WHERE ReportID<>933036 AND ReportText=@ReportText;

Make sure you do this in one connection (don't close the connection between these two statements), otherwise The variable @ReportText is reset to NULL.

+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)

 

[ingresman]

You could try dumping the database and deleting the rows using PHP or something and then reloading.
Deleting with SQL always looks cool but can be a bit evil especialy if you have a logging mechanisim.
Do you know what type of table it is , presumee MYSQL rather than innodb.

 

[CamaroLT]

@Ingresman:
I seem to not have access to information_schema to pull up that information, even as root, so I can't answer that question.

The delete worked on the test box, so now its a matter of backing up, then applying the change on the production.

-=*=-=*=-=*=-=*=-=*=-=*=-=*=-=*=-=*=-=*=-=*=
NEVER send the boss to do a techs job