Author Topic: Broken blacklist table (Read 4768 times)

Tichy · « **on:** November 26, 2007, 01:33:56 am »

The blacklist module contains a bad SQL statement on table creation. There is missing a comma:

Quote

CREATE TABLE blacklist (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(30) NOT NULL,
noteid INT NOT NULL,
expire INT UNSIGNED DEFAULT 0,
INDEX expire (expire))");

On new bots, there will be no blacklist table created and the bot spams (as posted by Phan):

Code: [Select]

MySQL error (# 1) on query: SELECT * FROM ccbot_blacklist WHERE expire > 0 AND expire < 1195581650
Table '****_aobot.ccbot_blacklist' doesn't exist

Fixing just the create statement is not enough for new bots already started with the broken statement, the will never create a new table. So the attached patch defines a new table version and tries to create the table on upgrading from the last one. The new table update order is:

New bots : 3
Broken 0.4.3 bots: 2 -> 3
Pre 0.4.3 bots : (0->)1->3

Only broken 0.4.3 bots should do an upgrading on the old table version 2 by creating the missing table.

Temar · « **Reply #1 on:** November 26, 2007, 05:13:38 am »

The table should try to create even if setting was saved
this is from 0.5.0 but im asuming it same

Code: [Select]

  elseif (mysql_num_rows(mysql_query("SHOW TABLES LIKE '%blacklist'"))==0)
  {
   //in some cases the setting does exist even if the table does not so create the table and change the version
   $this -> new_table();
   $this -> bot -> log("BLACKLIST", "CREATE", "Created a table version 2.");
   $this -> bot -> settings -> save("Blacklist", "table_version", 2);
  }

Tichy · « **Reply #2 on:** November 26, 2007, 09:55:34 am »

This is a very bad idea to do it this way. I have multiple bots running on the same database (so they share at least the whois table). Every bot has his own blacklist table. If I run a 0.5 bot on the database once or have older bots already created a blacklist table, no missing tables for other bots will be created. To do it this way, there must not be an LIKE statement used.

But I think this is not the right way todo it, since the MySQL class should abstract the database interface.

Alreadythere · « **Reply #3 on:** November 26, 2007, 10:27:29 am »

Backported the trunk version for the moment, I just made sure that the full tablename is used in the like statement.

Alreadythere · « **Reply #4 on:** November 26, 2007, 01:15:06 pm »

Streamlined the updating a bit.

Most current version, save as .php into modules/

Temar · « **Reply #5 on:** November 26, 2007, 07:01:57 pm »

how come this module if done so diferently?
all other module have a Create Table at top
and an update function if needed

Alreadythere · « **Reply #6 on:** November 26, 2007, 07:07:12 pm »

Ask Glara, he wrote it.

clashbot · « **Reply #7 on:** January 27, 2008, 01:28:12 am »

hey, probably a dumb questions, but how do I install the patch, got the bot up and running, and I am getting the spam about the blacklist table not there...but not sure what I need to do with the patch file.

Temar · « **Reply #8 on:** January 27, 2008, 05:50:32 am »

Just remove the Module
and use !ban
it is already set as removed and wont be in next release

Alreadythere · « **Reply #9 on:** January 27, 2008, 08:53:09 pm »

Quote from: clashbot on January 27, 2008, 01:28:12 am

hey, probably a dumb questions, but how do I install the patch, got the bot up and running, and I am getting the spam about the blacklist table not there...but not sure what I need to do with the patch file.

The file got renamed in the SVN. Try the link below.

Most current version, save as Blacklist.php into modules/