WordPress.org

Forums

MySQL 5 incompatibility caused by TEXT NOT NULL columns (10 posts)

  1. Jan
    Member
    Posted 9 years ago #

    Hi there,
    I'm using Mysql 5.0.15 at home and noticed that WP 2.0 uses tables with columns of the type TEXT NOT NULL yet with no default value.
    This results in a bunch of queries failing. This problem can be solved by giving the columns empty default values.

    This seems to have been mentioned here before: http://wordpress.org/support/topic/51300#post-281554

  2. Mark (podz)
    Support Maven
    Posted 9 years ago #

    It can also be solved by dropping down from Strict mode in mysql

  3. Jan
    Member
    Posted 9 years ago #

    Well, I could, but most people don't have a clue about databases. And strict mode is the default for MySQL 5.

    Also, disabling strict mode would only suppress the symptom, not the cause of the error.

    I created a ticket for this: http://trac.wordpress.org/ticket/2115

    I'm amazed this problem is known at least since beginning of November and hasn't even been recognized since: http://trac.wordpress.org/ticket/1808

  4. RustIndy
    Member
    Posted 9 years ago #

    Jan, the problems with strict mode have been fixed. the 2.0 release should work fine.

  5. Jan
    Member
    Posted 9 years ago #

    Do you mean "fixed just now", or "fixed before and your setup is probably broken" ?

  6. abrazell
    Member
    Posted 9 years ago #

    Jan,

    As in Ryan fixed all known problems the other day.

    Anyone testing with this? Have we fixed all of the problems? I fixed the comment posting problem a couple days ago. That was the last remaining problem that I know of. If there are still issues, please capture the errors and submit a bug.

    Thats from the hackers mailing list an hour ago.

  7. Jan
    Member
    Posted 9 years ago #

    Taking a look at the Tracker and the Repository this hasn't been dealt with yet.

    I have written a small hack/plugin for those who want to test RC3 under MySQL5 right now:

    <?php
    /*
    Plugin Name: MySQL5 compatibility
    Plugin URI:
    Description: Activates MySQL5 compatibility for WP2.0 RC3 by disabling strict mode in MySQL.
    Author: Jan
    Version: 1
    Author URI:
    */
    function mysql_5_compat_on () {
    global $wpdb;
    $sVersion = $wpdb->get_var("select @@version");
    if ( $sVersion{0} == 5 ) $wpdb->query('set sql_mode="";');
    }

    add_action('init', 'mysql_5_compat_on');
    ?>

  8. RustIndy
    Member
    Posted 9 years ago #

    Jan, the RC3 release works under MySQL strict mode. I've installed it from scratch in Apache 2.0.55 with PHP 5.0.5 and MySQL 5.0.16 in strict mode. The SpamKarma2.1 beta will work under strict mode now as well :)

  9. Jan
    Member
    Posted 9 years ago #

    Well, I'm running PHP 5.0.4,I just did a clean install of Mysql 5.0.17 and WP2 RC3.
    After installing I clicked on "Write" and tried to upload an image. This was the result:

    WordPress database error: [Field 'pinged' doesn't have a default value]
    INSERT INTO wp_posts (post_author, post_date, post_date_gmt, post_content, post_title, post_excerpt, post_status, comment_status, ping_status, post_password, post_name, to_ping, post_modified, post_modified_gmt, post_parent, menu_order, post_mime_type, guid) VALUES ('1', '2005-12-22 14:23:37', '2005-12-22 13:23:37', 'PicDesc', 'PicTitle', '', 'attachment', 'open', 'open', '', 'pictitle', '', '2005-12-22 14:23:37', '2005-12-22 13:23:37', '-1135257799', '0', 'image/jpeg', 'http://flowhp./wp-content/uploads/2005/12/Bild(23).jpg')

    WordPress database error: [You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 3]
    SELECT category_id FROM wp_post2cat WHERE post_id =

    WordPress database error: [You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' 1)' at line 2]
    INSERT INTO wp_post2cat (post_id, category_id) VALUES (, 1)

    WordPress database error: [Out of range value adjusted for column 'post_id' at row 1]
    INSERT INTO wp_postmeta (post_id,meta_key,meta_value) VALUES ('','_wp_attached_file','D:\Jan\HTML\pages\kackreiz.net/wp-content/uploads/2005/12/Bild(23).jpg')

    WordPress database error: [Out of range value adjusted for column 'post_id' at row 1]
    INSERT INTO wp_postmeta (post_id,meta_key,meta_value) VALUES ('','_wp_attachment_metadata','a:4:{s:5:\"width\";i:640;s:6:\"height\";i:480;s:14:\"hwstring_small\";s:23:\"height=\'96\' width=\'128\'\";s:4:\"file\";s:70:\"D:\\Jan\\HTML\\pages\\kackreiz.net/wp-content/uploads/2005/12/Bild(23).jpg\";}')

    Warning: Cannot modify header information - headers already sent by (output started at D:\Jan\HTML\pages\kackreiz.net\wp-includes\wp-db.php:102) in D:\Jan\HTML\pages\kackreiz.net\wp-admin\inline-uploading.php on line 99

    The SQL-Mode for this connection was:
    STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION (Default for fresh MySQL Installations).
    All tables are MyISAM

  10. RustIndy
    Member
    Posted 9 years ago #

    Ahhhhh k, guess a couple got missed. I'll do some bitching on #wordpress tonite and try to get it patched. The code fixes for these problems are really easy, and I'm obviously good at bitching :)

Topic Closed

This topic has been closed to new replies.

About this Topic