Error archiving tables in database

[hadoob024]

Hello.  I'm working on a script that takes records older than 30 days from the main table and puts them into the archive table.  Here's the section of code giving me problems:

[CODE]
//We first lock both the main listings table and the archive table so
//that we can lower the chances of corrupting the database.
$lock_query = "LOCK TABLES ofrearchive WRITE, ofrelistings WRITE";
$lock_result = mysql_query($lock_query);
if (!$lock_result)
{
$problemtext = mysql_error($connresult);
trigger_error($problemtext, E_USER_ERROR);
}


//Moving records older than 30 days from main table into archive table
$insert_query = "INSERT INTO ofrearchive
    SELECT * FROM ofrelistings LEFT JOIN ofrearchive USING(uniqueid)
    WHERE ofrearchive.uniqueid IS NULL AND DATEDIFF(ofrelistings.dateadded, now())>30";
$insert_result = mysql_query($insert_query);
if (!$insert_result)
{
$problemtext = mysql_error($connresult);
trigger_error($problemtext, E_USER_ERROR);
}
[/CODE]


When this executes, it gets passed the LOCK statement just fine (because I don't get any error messages), however, it seems that the INSERT statement is causing this error:
"Table 'ofrearchive' was not locked with LOCK TABLES"


Any thoughts on what could be causing this?  Thanks!

[hadoob024]

OK.  I think I see where this problem might be coming from.  I just came across this in the MySQL docs:

[quote]
Also, you cannot use a locked table multiple times in a single query. Use aliases instead, in which case you must obtain a lock for each alias separately.

mysql> LOCK TABLE t WRITE, t AS t1 WRITE;
mysql> INSERT INTO t SELECT * FROM t;
ERROR 1100: Table 't' was not locked with LOCK TABLES
mysql> INSERT INTO t SELECT * FROM t AS t1;
[/quote]


I think I'm getting the same error message as from this example.  I guess my question is that since I use the tablename 'ofrearchive' four times in that INSERT query, do I need to create a new alias for each time I need to use it in the query?

[shoz]

[quote author=hadoob024 link=topic=114297.msg465062#msg465062 date=1163017079]
OK.  I think I see where this problem might be coming from.  I just came across this in the MySQL docs:

[quote]
Also, you cannot use a locked table multiple times in a single query. Use aliases instead, in which case you must obtain a lock for each alias separately.

mysql> LOCK TABLE t WRITE, t AS t1 WRITE;
mysql> INSERT INTO t SELECT * FROM t;
ERROR 1100: Table 't' was not locked with LOCK TABLES
mysql> INSERT INTO t SELECT * FROM t AS t1;
[/quote]


I think I'm getting the same error message as from this example.  I guess my question is that since I use the tablename 'ofrearchive' four times in that INSERT query, do I need to create a new alias for each time I need to use it in the query?
[/quote]

You'll only create the alias in the FROM clause of the SELECT once and refer to the table using that alias throughout the SELECT, unless you're using a different query from the one you posted.

[code]
LEFT JOIN ofrarchive AS a ON a.column = ... WHERE a.column ...
[/code]

[hadoob024]

Cool.  OK.  So I change:

[code]$lock_query = "LOCK TABLES ofrearchive WRITE, ofrelistings WRITE";[/code]

to:

[code]$lock_query = "LOCK TABLES ofrearchive WRITE, ofrearchive AS ofrearchive1 WRITE, ofrelistings WRITE, ofrelistings AS ofrelistings1 WRITE";[/code]


and:

[code]$insert_query = "INSERT INTO ofrearchive
    SELECT * FROM ofrelistings LEFT JOIN ofrearchive USING(uniqueid)
    WHERE ofrearchive.uniqueid IS NULL AND DATEDIFF(ofrelistings.dateadded, now())>30";[/code]

to:

[code]$insert_query = "INSERT INTO ofrearchive
    SELECT * FROM ofrelistings LEFT JOIN ofrearchive AS ofrearchive1 USING(uniqueid)
    WHERE ofrearchive1.uniqueid IS NULL AND DATEDIFF(ofrelistings.dateadded, now())>30";[/code]

right?  Well I tried that and now this is the error I'm getting:

[quote]Column count doesn't match value count at row 1[/quote]

I can't even figure out what this error is referring to.

[shoz]

[quote]
[code]$insert_query = "INSERT INTO ofrearchive
    SELECT * FROM ofrelistings LEFT JOIN ofrearchive AS ofrearchive1 USING(uniqueid)
    WHERE ofrearchive1.uniqueid IS NULL AND DATEDIFF(ofrelistings.dateadded, now())>30";[/code]

right?  Well I tried that and now this is the error I'm getting:

[quote]Column count doesn't match value count at row 1[/quote]

I can't even figure out what this error is referring to.
[/quote]

It's referring to the number of columns in the result of the SELECT compared to the number of columns in the table being inserted into.

If ofrelistings has the same table definition as ofrearchive, then you should only need to change the column list.
[code]
SELECT ofrelistings.* ...
[/code]

[hadoob024]

Yup.  They have identical table definitions except that 'uniqueid' in ofrelistings is an int that's set to auto-increment, but in ofrearchive, 'uniqueid' is just an int. 

So you're saying to change:
[code]SELECT * FROM ofrelistings LEFT JOIN...[/code]

to:
[code]SELECT ofrelistings.* FROM ofrelistings LEFT JOIN...[/code]


Is that right?  I've never seen that before, but I'll give it a shot.

Edit:
OK.  I tried this, but I'm still getting the same error.  Any thoughts?

[shoz]

If you're still getting the "Column count" error then I have to assume ofrelistings does not have the same number of columns as ofrearchive.

Do a DESCRIBE on both tables and verify that they both have the same number of columns.

eg.
[code]
DESCRIBE ofrearchive
[/code]

[hadoob024]

Hmmm.  Weird.  I don't see it.  Here're the result of doing a DESCRIBE on both tables:

DESCRIBE ofrearchive:
Field                Type  Null              Key  Default  Extra
uniqueid             int(10) unsigned   PRI       0
firstname             varchar(30)      
lastname           varchar(30)      
emailaddress            varchar(50)      
phonenumber        varchar(14)      
companyname       varchar(30)      
askingprice           int(10) unsigned           0
listingtype             tinyint(2) unsigned           0
location             varchar(20)      
city                       varchar(30)      
listingdescription   text      
picture             varchar(4)      
dateadded           date                             0000-00-00



DESCRIBE ofrelistings:
Field                Type  Null              Key  Default  Extra
uniqueid             int(10) unsigned   PRI       NULL     auto_increment
firstname             varchar(30)      
lastname             varchar(30)      
emailaddress           varchar(50)      
phonenumber         varchar(14)      
companyname         varchar(30)      
askingprice             int(10) unsigned           0
listingtype             tinyint(2) unsigned           0
location             varchar(20)      
city                       varchar(30)      
listingdescription     text      
picture               varchar(4)      
dateadded           date                     0000-00-00



I still don't see it.

[shoz]

Post the current script you're using. You shouldn't be getting the "Column Count" error as long as the query is

[code]
INSERT INTO ofrearchive SELECT ofrelistings.* FROM ofrelistings ....
[/code]

What version of MYSQL are you using btw.

[hadoob024]

Hehe... You're gonna laugh, but I forgot to do "SELECT ofrelistings.*" and just did "SELECT *".  That step seems to work now.

However, it's still not doing what I want it to do.  Here's my INSERT statement:
[code]$insert_query = "INSERT INTO ofrearchive
SELECT ofrelistings.* FROM ofrelistings LEFT JOIN ofrearchive AS ofrearchive1 USING(uniqueid)
WHERE ofrearchive1.uniqueid IS NULL AND DATEDIFF(ofrelistings.dateadded, now())>30";
$insert_result = mysql_query($insert_query);[/code]


It's supposed to move records older than 30 days from main table into archive table, but I ran the script and nothing got inserted into the archive table. 

[shoz]

Change the order of the arguments to DATEDIFF

[code]
DATEDIFF(NOW(), ofrelistings.dateadded)
[/code]

http://dev.mysql.com/doc/refman/4.1/en/date-and-time-functions.html

[hadoob024]

Dude.  You're a freakin' genius!!!  I don't know how I keep missing these little things.  Sweet.  I just gotta check and make sure that it actually moved the appropriate records, although it looks like it did.  Do you see anything that might be wrong?  The syntax seems right, it seems like it moved all records older than 30 days from the main table to the archive table.

I'm gonna try out the next section:
[code]//deleting records older than 30 days from main table
$delete_query = "DELETE ofrelistings.* FROM ofrelistings INNER JOIN ofrearchive USING(uniqueid)";
$delete_result = mysql_query($delete_query);
if (!$delete_result)
{
$problemtext = "Error DELETE-ing information from main table in Cron job.\r\n".mysql_error($connresult);
trigger_error($problemtext, E_USER_ERROR);
}
[/code]


This section of code's supposed to then delete those records that are older than 30 days from the main table.

[hadoob024]

Sweet!  Everything else worked.  In case someone's looking for an example.  Here's mine.  It basically takes records older than 30 days, and copies them into an archive table.  It then deletes records that match both tables from the main table.  It then runs a query to see if any records match both tables, if they don't then the previous 2 transactions ran properly.  And finally, we delete records older than 60 days from the archive table.

[code]//We first lock both the main listings table and the archive table so that
//we can lower the chances of corrupting the database.  In addition, we have
//to create a couple of aliases for our tables and lock those as well.
$lock_query = "LOCK TABLES ofrearchive WRITE, ofrearchive AS ofrearchive1 WRITE, ofrelistings WRITE, ofrelistings AS ofrelistings1 WRITE";
$lock_result = mysql_query($lock_query);
if (!$lock_result)
{
$problemtext = "Error LOCK-ing db tables and aliases.\r\n".mysql_error($connresult);
trigger_error($problemtext, E_USER_ERROR);
}
else
$summary .= "LOCK-ed both tables and aliases. \r\n";



//Copying records older than 30 days from main table into archive table.
$insert_query = "INSERT INTO ofrearchive
SELECT ofrelistings.* FROM ofrelistings LEFT JOIN ofrearchive AS ofrearchive1 USING(uniqueid)
WHERE ofrearchive1.uniqueid IS NULL AND DATEDIFF(now(), ofrelistings.dateadded)>30";
$insert_result = mysql_query($insert_query);
if (!$insert_result)
{
$problemtext = "Error INSERT-ing information into archive table in Cron job.\r\n".mysql_error($connresult);
trigger_error($problemtext, E_USER_ERROR);
}
else
$summary .= "INSERT-ed records older than 30 days from main table into archive table. \r\n";



//Deleting records older than 30 days from main table
$delete_query = "DELETE ofrelistings.* FROM ofrelistings INNER JOIN ofrearchive USING(uniqueid)";
$delete_result = mysql_query($delete_query);
if (!$delete_result)
{
$problemtext = "Error DELETE-ing information from main table in Cron job.\r\n".mysql_error($connresult);
trigger_error($problemtext, E_USER_ERROR);
}
else
$summary .= "DELETE-d records older than 30 days from the main table. \r\n";



//Verify that the previous transactions worked.  If it did, then this
//query should return nothing.
$select_query = "SELECT * FROM ofrelistings INNER JOIN ofrearchive USING(uniqueid)";
$select_result = mysql_query($select_query);
if (!mysql_num_rows($select_result))
$summary .= "Verified that transaction ran correctly because this query returned an empty set. r\n";
else
{
$problemtext = "Error completing the backing up of information from main table to archive table in Cron job.\r\n".mysql_error($connresult);
trigger_error($problemtext, E_USER_ERROR);
}


//DELETE records older than 60 days from the archive table.
$deletearchive_query = "DELETE ofrearchive.* FROM ofrearchive WHERE DATEDIFF(now(), ofrearchive.dateadded)>60";
$deletearchive_result = mysql_query($deletearchive_query);
if (!$deletearchive_result)
{
$problemtext = "Error DELETE-ing information from archive table in Cron job.\r\n".mysql_error($connresult);
trigger_error($problemtext, E_USER_ERROR);
}
else
$summary .= "DELETE-d records older than 30 days from the archive table. \r\n";




//UNLOCK-ing both tables so users can access them again
$unlock_query = "UNLOCK TABLES";
$unlock_result = mysql_query($unlock_query);
if (!$unlock_result)
{
$problemtext = "Error UNLOCK-ing db tables.\r\n".mysql_error($connresult);
trigger_error($problemtext, E_USER_ERROR);
}
else
$summary .= "UNLOCK-ed both DB tables. \r\n";
[/code]