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:

//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);
}

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:

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;

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?

hadoob024

OK. So I'm thinking that the alias thing was my problem before, because if I implement the following changes:

$lock_query = "LOCK TABLES ofrearchive WRITE, ofrelistings WRITE";

to:

$lock_query = "LOCK TABLES ofrearchive WRITE, ofrearchive AS ofrearchive1 WRITE, ofrelistings WRITE, ofrelistings AS ofrelistings1 WRITE";

and:

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

to:

$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";

I don't get that error anymore. However, I tried that and now this is the error I'm getting:

Column count doesn't match value count at row 1

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