Mail_Queue MDB2 Error: no such field

[stephenhait]

I'm trying to run the example scripts that came with the Mail_Queue package.
If I specify the container type of MDB2 I get the following error:

MDB2 Error: no such field

If I specify the container type as DB, then the message is inserted correctly into the mail_queue table. I don't mind using DB as the container type but it looks like using DB has been deprecated in favor of using MDB2.

Does anyone know what causes this type of error and how I might be able to get more info on where to look to resolve it?

The error occurs when executing the line to add a message to the queue:
$mail_queue->put( $from, $to, $hdrs, $body );

I've set up $mail_queue like this:
$mail_queue =& new Mail_Queue($db_options, $mail_options);

And I've set up $db_options like this:
$db_options['type'] = 'db'; // this fails when set to mdb2
$db_options['dsn'] = 'mysql://myusername:mypassword@myserver/mydatabase';
$db_options['mail_table'] = 'mail_queue';

[mark]

$error->getDebugInfo() in addition to $error->getMessage() (<= this is what you're likely using to get the "no such field" error message) should provide you with more helpful information about the error.

[stephenhait]

Thanks for the suggestion.

I see the error I described when I have the following code at the beginning of my add_message.php script:

ini_set('display_errors', 1);
error_reporting(E_ALL);
require_once 'PEAR.php';
PEAR::setErrorHandling(PEAR_ERROR_DIE);

If I remove this code, there is no indication that there is a problem, however the message is never added to the mail_queue table.

I have tried the following:
$mail_queue->put( $from, $to, $hdrs, $body );
if (PEAR::isError($mail_queue)) {
die($mail_queue->getMessage());
} else {
print ("no mail_queue error<br>\n");
}

AND
$mail_queue->put( $from, $to, $hdrs, $body );
if (PEAR::isError($mail_queue)) {
die($mail_queue->getDebugInfo());
} else {
print ("no mail_queue error<br>\n");
}

In both of these cases, the message I see is "no mail_queue error" even though there apparently is an error since the row is never appended.

If I leave the "error_reporting" code in at the top of the page, I don't see anything after 'MDB2 Error: no such file' displays since processing apparently stops after the message is displayed.

I apologize that I'm pretty inexperienced with PEAR (and PHP, for that matter) so it's quite likely I'm not approaching this properly.

Any other ideas? I have determined that the MDB2 module works by creating a simple script to pull a single row from a table and display it.

And, again, if I specify the DB connector as opposed to the MDB2 connector, everything works properly.

[mark]

"PEAR::setErrorHandling(PEAR_ERROR_DIE);" causes the stop of execution immediately when an error occurs (using PHP's die() function). In many cases the quoted error handling is enough for debugging problems, but in some cases -- especially in combination with MDB2 -- some more (or better: other) actions are necessary.

Your idea of using

[stephenhait]

Thanks! That was a huge help!

I fixed the error reporting as you suggested and now get a MUCH more informative message:

Cannot create id in: mail_queue - FILE: /usr/home/username/pear/lib/Mail/Queue/Container/mdb2.php, LINE: 232

I looked at this section of mdb2.php and compared it with the same area of db.php and noticed that db.php (which works) looks like this:
$id = $this->db->nextId($this->sequence);
if (empty($id) || PEAR::isError($id)) {
return new Mail_Queue_Error(MAILQUEUE_ERROR,
$this->pearErrorMode, E_USER_ERROR, __FILE__, __LINE__,
'Cannot create id in: '.$this->sequence);
}

AND mdb2.php (which doesn't work) looks like this:
$id = $this->db->nextID($this->sequence);
if (empty($id) || PEAR::isError($id)) {
return new Mail_Queue_Error(MAILQUEUE_ERROR,
$this->pearErrorMode, E_USER_ERROR, __FILE__, __LINE__,
'Cannot create id in: '.$this->sequence);

At least one difference is that db.php has nextId (lowercase D) while mdb2.php refers to nextID (upper case D). Could this be enough to cause a problem? Looking further I came across this line:
- fixed bug #9065: MDB2 uses nextID(), not nextId()
in /pear/tmp/package.xml. I don't know if this is actually related but I tried changing this to 'nextId' in mdb2.php and running the script again. Unfortunately, I still get the same (more detailed) error message.

So thanks for helping me learn something helpful about debugging but I still seem to be having the same problem. I really appreciate your assistance on this! Any other ideas?

[mark]

The method names (nextId() vs. nextID()) shouldn't be the problem, as PHP would generate a clear error message indicating a missing function.

I guess that the user that you're using in the database connection doesn't have permissions to create a new table. MDB2 (and also DB) use an own table for generating (and emulating) sequences. If this sequence table doesn't exists, MDB2 tries to create the table, but needs the permission to do it, of course.

[stephenhait]

I understand there's a 'sequence' table used by Mail_Queue. It's called mail_queue_seq. I had already created this as per the example and, in fact, it seems to function properly (when using the DB container) and its sequence id number is incremented whenever a new message is added to the mail_queue table. The mail_queue_seq.id value is kept the same as the highest value of mail_queue.id

I tried dropping the mail_queue_seq table and running with the DB and MDB2 connectors to see if an attempt was made to create the sequence table. In this case, both DB and MDB2 threw the same error - 'Cannot create id in: mail_queue...'.

Next, after creating an empty mail_queue_seq table in both cases, I ran DB and then MDB2. DB worked fine; MDB2 failed as before.

So it doesn't look like the error is from not being able to create a new sequence table in the database. If the sequence table isn't there, both DB and MDB2 fail. If it does exist, DB succeeds but MDB2 fails.

I'm sure you have more pressing things to do than help troubleshoot this problem and I can apparently skip using MDB2 in favor of the deprecated DB container. However, if you have any more ideas, I'd love to hear them. And thanks for your help so far.

[mark]

Okay, next try.

The problem seems to be in this line in the put() function of the MDB2 container of Mail_Queue:

[stephenhait]

Wow - that was another good call! I added the code you described right after the nextid call in mdb2.php and this is what came back:

MDB2 Error: no such field, _doquery: [Error message: Could not execute statement] [Last executed query: INSERT INTO mail_queue_seq (sequence) VALUES (NULL)] [Native code: 1054] [Native message: Unknown column 'sequence' in 'field list']

The mail_queue_seq table has a single column named 'id', type int(10) NOTNULL. No column exists in the table named 'sequence'.

Does this help? Curiouser and curiouser.

[stephenhait]

I figured it out. And thanks so much for all your help.

It turns out it had to do with the table structure for the sequence table, mail_queue_seq and the differences between how the DB and MDB2 containers work. Why they are different is a mystery to me.

When using the DB container, the table mail_queue_seq only needs a single column, 'id', which is an auto-increment PK. When a message is added to the queue, any rows in the sequence table are deleted and then a new row is added and the auto-increment value of 'id' is then used for the non-auto-increment 'id' column in the queue table, mail_queue. So at any time there is only ever a single row in the sequence table which contains the highest value for 'id' thus far.

When using the MDB2 container, however, mail_queue_seq requires an additional column named 'sequence' which allows NULLS. When a message is added to the queue, a new row is added to the sequence table with the column 'sequence' set to NULL. This has a similar result to what happens when using DB - there is now a row containing the next highest value for 'id' that can be used when inserting into mail_queue. The difference is that any previous rows in mail_queue_seq are allowed to remain along with the "dummy" column, 'sequence' which always contains NULL.

So in order to use MDB2, I had to modify the sequence table in a way I haven't seen documented anywhere. Whether one way of handling the sequence numbers is better than the other, I couldn't say. Perhaps you have a thought on that. Personally, I don't know why they don't just use an auto-increment PK in mail_queue and do away with the secondary sequence table altogether.

Anyway, thanks again for your assistance and if you have any other thoughts on this, I'd be glad to hear them. Also, since you've proved to be such a valuable helper on this wild goose chase, I'd hope that you might respond again when I inevitibly need more PEAR/PHP help.

[mark]

I can only add that DB and MDB2 are able to create the sequence tables themselves, and therefore they will be right if they are created with the package that is used later (i.e. created with MDB2 and used with MDB2). I'm not sure why they have a different scheme, but there surely is a reason.

To your other question: autoincrementing is a unique feature of MySQL. For portability reasons, DB and MDB2 use the sequence table. This makes switching the DBMS very easy, without having to bother with DBMS-specific features.

[stephenhait]

[mark]

[stephenhait]

I resolved the permissions issue; sorry to bother you with that last question. Because of that I had misunderstood a good bit about what was going on and the "fixes" I'd tried were misguided.

Originally, the user running the script had only read/write access but no permissions to create a table. Because of that, the sequence table was never automatically created. I changed that so the user running the script has create (and drop) table permissions, too and the tables are created correctly now when using either DB and MDB2. Once this is working properly, I may change the user back to one having more limited permissions.

The only difference now between how DB and MDB2 functions (at least from the narrow perspective of how the sequence table is handled) is sequence table's single column name. The auto-increment PK in mail_queue_seq is named 'id' when created by DB and 'sequence' when created by MDB2.

I was confused by the example that came with the package which made no mention of the sequence table being created automatically. It also included a CREATE TABLE statement for the sequence table with 'id' as the name of the only column. The same example described how it would work with both DB and MDB2 and even had MDB2 as the default connector. Unfortunately, MDB2 would not work properly with the sequence table created in the example.

Thanks so much!

[mark]

If there is an example for usage with MDB2 that shows the wrong column name, please file a bug report for the Mail_Queue package. If it is a general example, you might also file a bug report requesting that a note is added (e.g. "example for usage with DB").