Database Error: Column ‘mail_id’ cannot be null

  • Resolved Rafael

    (@jordh)


    7 years, 4 months ago

    Hello,

    two days ago, WP Mail Logging stopped logging any new Mail sent by our website. I did some testing and got these messages in my debug.log file:

    WordPress Database Error Column 'mail_id' cannot be null for query INSERT INTO 'wp_wpml_mails ('mail_id', 'timestamp', 'host', 'receiver', 'subject','message', 'headers', 'attachments', 'error', 'plugin_version') VALUES (NULL, '2019-01-10 13:50:03', [...]

    It appears that the value for ‘mail_id’ gets lost somewhere before attempting to insert the logged mail into the database.

    On our staging environment, I have tried switching to default themes (Twenty Nineteen and Twenty Fifteen) and deactivating all plugins except for WP Mail Logging, WP Mail SMTP and Check Email (to generate test mails). The issue persists.

    The only thing that changed around the time as the logging stopped was that I updates WPML (Worpress Multilingual) to its latest version, but the error occurs even when WPML is deactivated.

    The site runs on WordPress 5.0.3 (5.0.2 at the time logging stopped working) and the latest version of all plugins mentioned above.

    Regards,
    Rafael

    • This topic was modified 7 years, 4 months ago by Rafael.
    • This topic was modified 7 years, 4 months ago by Rafael.
    • This topic was modified 7 years, 4 months ago by Rafael. Reason: Formatting
    • This topic was modified 7 years, 4 months ago by Rafael.
    • This topic was modified 7 years, 4 months ago by Rafael. Reason: Formatting again, the ` symbol within the code messes it all up
    • This topic was modified 7 years, 4 months ago by Rafael.
Viewing 5 replies - 1 through 5 (of 5 total)
  • No3x

    (@no3x)

    7 years, 4 months ago

    Hi @jordh,

    It appears that the value for ‘mail_id’ gets lost somewhere before attempting to insert the logged mail into the database.

    NULL ist ok for the insert – it tells the RDBMS to figure out an id. In the case of the plugin it’s an autoincrement integer.
    So that’s not the issue for a “stable” installation of the plugin. Did you use a DB migration tool? I think the database schema changed somehow and introduced the issue by accident.

    I use WPML(WP Mail Logging) here and there as abbreviation – obiously there is a clash with Worpress Multilingual. But I’m not aware of any issues with it yet.

    • This reply was modified 7 years, 4 months ago by No3x.
    • This reply was modified 7 years, 4 months ago by No3x.
    Thread Starter Rafael

    (@jordh)

    7 years, 4 months ago

    I took a look at the wp_wpml_mails table in phpMyAdmin. The mail_id column has the type int(11). Is that correct?

    By the way, the last value for mail_id has been 4727 and was entered around two days ago.

    While I did a database migration when I mirrored the site to our staging environment yesterday, I did so by hand (exporting an .sql file, deleting all existing tables in the staging database and importing the previously exported file). The error however occurs on both versions of the site (I generated the above mentioned error log on the live site).

    We’ve been using WP Mail Logging alongside WordPress Multilingual since December 2017; there haven’t been any compatibility issues so far.

    No3x

    (@no3x)

    7 years, 4 months ago

    A normal INSERT looks like:

    INSERT INTO 'wp_wpml_mails'('mail_id','timestamp','host','receiver','subject','message','headers','attachments','error','plugin_version')
VALUES (NULL, '2019-01-10 14:39:05', '172.17.0.32', '[email protected]', 'Subject', 'Content', 'From: \"B\" <[email protected]>\r\n', '', NULL, '1.8.5')

    So it’s pretty much the same as yours. This tells us that the error must be in the database scheme.

    int(11) – INT is okay, 11 limits the number of digits when viewing the data via cli (very unimportant for the issue). The max ID is 2147483647.
    The column is created during the installation as:
    'mail_id' INT NOT NULL AUTO_INCREMENT. You should be able to verify this in phpmyadmin.

    • This reply was modified 7 years, 4 months ago by No3x.
    Thread Starter Rafael

    (@jordh)

    7 years, 4 months ago

    I am attempting to manually add the Auto_Increment attribute to the column, it looks good when I press SQL preview:

    ALTER TABLE 'wp_wpml_mails' CHANGE 'mail_id' 'mail_id' INT(11) NOT NULL AUTO_INCREMENT;

    But when I try to save the changes, I get the following error:

    #1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key

    No other column has the Auto_Increment attribute. So do I have to set the mail_id column as a key as well? And where do I do that?

    Sorry to ask all these questions, I am a beginner at best when it comes to databases…

    Thread Starter Rafael

    (@jordh)

    7 years, 4 months ago

    I managed to manually fix the table by adding an index to mail_id first:

    ALTER TABLE 'wp_wpml_mails' ADD INDEX('mail_id');

    Then I was able to add the Auto_Increment attribute:

    ALTER TABLE 'wp_wpml_mails' CHANGE 'mail_id' 'mail_id' INT(11) NOT NULL AUTO_INCREMENT;

    Now the table accepts the requests again and new mails are properly logged.

    Thank you for pointing me in the right direction!

    Regards,
    Rafael

Viewing 5 replies - 1 through 5 (of 5 total)

The topic ‘Database Error: Column ‘mail_id’ cannot be null’ is closed to new replies.