Announcement

Collapse
No announcement yet.

Error converting to InnoDB

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Error converting to InnoDB

    Based on the discussion in this thread, "How to automatically Clear Cache" I decided to test converting my tables from MyISAM to InnoDB on my test site and on the user table received the following error

    Invalid default value for 'passworddate'

    Shown below is the output of Describe user; I'm assuming once I correct passworddate I'll have the same issue with birthday_search. Can you let me know what the correct default value for these fields should be?


    PHP Code:
    +-----------------------+----------------------+------+-----+------------+----------------+
    Field                 Type                 Null Key | Default    | Extra          |
    +-----------------------+----------------------+------+-----+------------+----------------+
    userid                int(10unsigned     NO   PRI NULL       auto_increment |
    usergroupid           smallint(5unsigned NO   MUL 0          |                |
    membergroupids        varchar(250)         | NO   |     |            |                |
    displaygroupid        smallint(5unsigned NO   |     | 0          |                |
    username              varchar(100)         | NO   MUL |            |                |
    passworddate          date                 NO   |     | 0000-00-00 |                |
    email                 varchar(100)         | NO   MUL |            |                |
    styleid               smallint(5unsigned NO   |     | 0          |                |
    parentemail           varchar(50)          | NO   |     |            |                |
    homepage              varchar(100)         | NO   |     |            |                |
    icq                   varchar(20)          | NO   |     |            |                |
    aim                   varchar(20)          | NO   |     |            |                |
    yahoo                 varchar(32)          | NO   |     |            |                |
    msn                   varchar(100)         | NO   |     |            |                |
    skype                 varchar(32)          | NO   |     |            |                |
    google                char(32)             | NO   |     |            |                |
    status                mediumtext           YES  |     | NULL       |                |
    showvbcode            smallint(5unsigned NO   |     | 0          |                |
    showbirthday          smallint(5unsigned NO   |     | 2          |                |
    usertitle             varchar(250)         | NO   |     |            |                |
    customtitle           smallint(6)          | NO   |     | 0          |                |
    joindate              int(10unsigned     NO   MUL 0          |                |
    daysprune             smallint(6)          | NO   |     | 0          |                |
    lastvisit             int(10unsigned     NO   |     | 0          |                |
    lastactivity          int(10unsigned     NO   MUL 0          |                |
    lastpost              int(10unsigned     NO   |     | 0          |                |
    posts                 int(10unsigned     NO   MUL 0          |                |
    reputation            int(11)              | NO   |     | 10         |                |
    reputationlevelid     int(10unsigned     NO   |     | 1          |                |
    timezoneoffset        varchar(4)           | NO   |     |            |                |
    pmpopup               smallint(6)          | NO   |     | 0          |                |
    avatarid              smallint(6)          | NO   |     | 0          |                |
    avatarrevision        int(10unsigned     NO   |     | 0          |                |
    profilepicrevision    int(10unsigned     NO   |     | 0          |                |
    options               int(10unsigned     NO   |     | 167788559  |                |
    privacy_options       mediumtext           YES  |     | NULL       |                |
    notification_options  int(10unsigned     NO   |     | 1073741818 |                |
    birthday              varchar(10)          | NO   MUL |            |                |
    birthday_search       date                 NO   MUL 0000-00-00 |                |
    maxposts              smallint(6)          | NO   |     | -1         |                |
    startofweek           smallint(6)          | NO   |     | 1          |                |
    ipaddress             varchar(45)          | NO   |     |            |                |
    referrerid            int(10unsigned     NO   MUL 0          |                |
    languageid            smallint(5unsigned NO   |     | 0          |                |
    emailstamp            int(10unsigned     NO   |     | 0          |                |
    threadedmode          smallint(5unsigned NO   |     | 0          |                |
    emailnotification     smallint(5unsigned NO   |     | 0          |                |
    pmtotal               smallint(5unsigned NO   |     | 0          |                |
    pmunread              smallint(5unsigned NO   |     | 0          |                |
    importuserid          bigint(20)           | NO   |     | 0          |                |
    adminoptions          int(10unsigned     NO   |     | 0          |                |
    lastpostid            int(10unsigned     NO   |     | 0          |                |
    sigpicrevision        int(10unsigned     NO   |     | 0          |                |
    ipoints               int(10unsigned     NO   |     | 0          |                |
    infractions           int(10unsigned     NO   |     | 0          |                |
    warnings              int(10unsigned     NO   |     | 0          |                |
    infractiongroupids    varchar(255)         | NO   |     |            |                |
    infractiongroupid     smallint(5unsigned NO   |     | 0          |                |
    profilevisits         int(10unsigned     NO   |     | 0          |                |
    friendcount           int(10unsigned     NO   |     | 0          |                |
    friendreqcount        int(10unsigned     NO   |     | 0          |                |
    vmunreadcount         int(10unsigned     NO   |     | 0          |                |
    vmmoderatedcount      int(10unsigned     NO   |     | 0          |                |
    socgroupinvitecount   int(10unsigned     NO   |     | 0          |                |
    socgroupreqcount      int(10unsigned     NO   |     | 0          |                |
    pcunreadcount         int(10unsigned     NO   |     | 0          |                |
    pcmoderatedcount      int(10unsigned     NO   |     | 0          |                |
    gmmoderatedcount      int(10unsigned     NO   |     | 0          |                |
    assetposthash         varchar(32)          | NO   |     |            |                |
    fbuserid              varchar(255)         | NO   MUL NULL       |                |
    fbjoindate            int(10unsigned     NO   |     | 0          |                |
    fbname                varchar(255)         | NO   |     | NULL       |                |
    logintype             enum('vb','fb')      | NO   |     | vb         |                |
    fbaccesstoken         varchar(255)         | NO   |     | NULL       |                |
    newrepcount           smallint(5unsigned NO   |     | 0          |                |
    bloggroupreqcount     int(10unsigned     NO   |     | 0          |                |
    showblogcss           int(11)              | NO   |     | 1          |                |
    panjo_selling         tinyint(1)           | YES  |     | NULL       |                |
    token                 varchar(255)         | NO   |     |            |                |
    scheme                varchar(100)         | NO   |     |            |                |
    secret                varchar(100)         | NO   |     |            |                |
    autosubscribe         smallint(5unsigned NO   |     | 0          |                |
    privacyconsent        tinyint(4)           | NO   MUL 0          |                |
    privacyconsentupdated int(10unsigned     NO   |     | 0          |                |
    eustatus              tinyint(4)           | NO   |     | 0          |                |
    +-----------------------+----------------------+------+-----+------------+----------------+ 

    If it matters,
    MySQL 5.7.22
    PHP 7.1.18
    ​​​​​​​vBulletin 5.4.2

  • #2
    You need to change the default value to a valid date like 1900-01-01 or allow invalid dates on your MySQL Server.

    https://dev.mysql.com/doc/refman/8.0..._invalid_dates

    Turning of Strict Mode on your MySQL server will also work around this problem.

    Created a Bug Report for this - https://tracker.vbulletin.com/vbulle...sues/VBV-18727
    Last edited by Wayne Luke; Thu 16 Aug '18, 10:51am.
    Translations provided by Google.

    Wayne Luke
    The Rabid Badger - a vBulletin Cloud demonstration site.
    vBulletin 5 API - Full / Mobile
    Vote for your favorite feature requests and the bugs you want to see fixed.

    Comment


    • #3
      Here's what I ended up with after determining that once the default was fixed for the user table, there still 1200+ users with bad data in the birthday_search field. I'm assuming if the same issue occured with the password date, it could be fixed in the same way.

      PHP Code:

      alter table user
        modify column passworddate date not null 
      default '1900-01-01',
        
      modify column birthday_search date not null default '1900-01-01';

      update user set birthday_search '1901-01-01' where DATE_FORMAT(birthday_search,'%Y-%m-%d') = '0000-00-00'

      Comment

      widgetinstance 262 (Related Topics) skipped due to lack of content & hide_module_if_empty option.
      Working...
      X