Recent Topics

update to 6.6.5-stable SQL Problem: Invalid default value for 'iver_edit_datetime'(Errno=1067)

Started by on Nov 27, 2015 – Contents updated: Dec 05, 2015

Nov 27, 2015 14:18    

Sorry to molest the community again but I daringly tried an update as recomended today of
an old B2Evo (5.4.xx not sure impossible to view now DB was 11255 ) to the neweset and I get an error as follows

- Converting comments table... OK.
- Converting post prerendered contents table... OK.
- Converting comment prerendered contents table... OK.
- Converting post versions table...
An unexpected error has occurred!

If this error persists, please report it to the administrator.

Go back to home page
Additional information about this error:

MySQL error!

Invalid default value for 'iver_edit_datetime'(Errno=1067)


Your query:

ALTER TABLE evo_items__version

MODIFY iver_status ENUM('published','community','deprecated','protected','private','review','draft','redirected') COLLATE ascii_general_ci NULL

So I tried to fix the line 90 in _advanced.php with
$date_default_timezone = 'Europe/Paris';

and tried to normalize database with installer but it then fails with a new Errno=1054


Normalizing DB charsets...
Loading module: _core/model/__core.install.php
Loading module: collections/model/_collections.install.php
Loading module: files/model/_files.install.php
Loading module: sessions/model/_sessions.install.php
Loading module: messaging/model/_messaging.install.php
Loading module: maintenance/model/_maintenance.install.php
Normalizing evo_antispam... OK
Normalizing evo_antispam__iprange... OK
Normalizing evo_basedomains... OK
Normalizing evo_bloggroups...
An unexpected error has occurred!

If this error persists, please report it to the administrator.

Go back to home page
Additional information about this error:

MySQL error!

Unknown column 'bloggroup_perm_item_type' in 'evo_bloggroups'(Errno=1054)

Your query:

ALTER TABLE `evo_bloggroups`

      MODIFY bloggroup_perm_poststatuses    set('review','draft','private','protected','deprecated','community','published','redirected') COLLATE ascii_general_ci NOT NULL default '',

      MODIFY bloggroup_perm_item_type       ENUM('standard','restricted','admin') COLLATE ascii_general_ci NOT NULL default 'standard',

      MODIFY bloggroup_perm_edit            ENUM('no','own','lt','le','all') COLLATE ascii_general_ci NOT NULL default 'no',

      MODIFY bloggroup_perm_cmtstatuses     set('review','draft','private','protected','deprecated','community','published') COLLATE ascii_general_ci NOT NULL default '',

      MODIFY bloggroup_perm_edit_cmt        ENUM('no','own','anon','lt','le','all') COLLATE ascii_general_ci NOT NULL default 'no'

Nov 27, 2015 15:23

should I try to manually add the missing column with MYSQL ??

ALTER TABLE evo_bloggroups ADD COLUMN bloggroup_perm_item_type;

Nov 27, 2015 15:33

well that works for one misssing column but next it is missing

bloguser_perm_item_type

Unknown column 'bloguser_perm_item_type' in 'evo_blogusers'(Errno=1054)

Nov 27, 2015 15:53

well obviously there is a more serious bug in the Database conversion utility :

Additional information about this error:

MySQL error!

Data truncated for column 'bloguser_perm_item_type' at row 1(Errno=1265)

Your query:

ALTER TABLE `evo_blogusers`

      MODIFY bloguser_perm_poststatuses    set('review','draft','private','protected','deprecated','community','published','redirected') COLLATE ascii_general_ci NOT NULL default '',

      MODIFY bloguser_perm_item_type       ENUM('standard','restricted','admin') COLLATE ascii_general_ci NOT NULL default 'standard',

      MODIFY bloguser_perm_edit            ENUM('no','own','lt','le','all') COLLATE ascii_general_ci NOT NULL default 'no',

      MODIFY bloguser_perm_cmtstatuses     set('review','draft','private','protected','deprecated','community','published') COLLATE ascii_general_ci NOT NULL default '',

      MODIFY bloguser_perm_edit_cmt        ENUM('no','own','anon','lt','le','all') COLLATE ascii_general_ci NOT NULL default 'no'

Can anybody help please?

My production site is down and I am loosing ccontacts to possible clients every hour!
why the hell did I update ---- mistake to update !
never touch a running system!

Nov 27, 2015 20:51

Just an advice, if you're running a critical service in which downtime = money loss, there are two golden rules that must be followed:

  1. Make a full backup of the site and make sure that you know how to quickly restore it if something went wrong.
  2. Run test upgrades in alternative/development/local servers.

After a failed upgrade, directly touching the database is almost a lottery. You may fix the problem in a single step, but it may also be a pain full of errors for missing, duplicated, etc fields.

You can go back to your 5.0.4 site and open the table evo_items__version using phpMyAdmin, or similar. Find the field iver_edit_datetime, click on the change link and set its default value to 2000-01-01 00:00:00. Then, try the upgrade again.

Nov 27, 2015 21:06

Why the heck are you using version 6.6.5 when 6.6.6 is available? (6.6.6 actually includes fixes for the datetime issues).

Restore your latest working backup and upgrade to v6.6.6 again.

Nov 27, 2015 22:57

I used the 2nd newest version because I think it might have less bugs!
Now I did as adviced and restored my backups and installed 6.6.6
the changes are minimal to my point of view.

"6.6.6 actually includes fixes for the datetime issues" looks like not yet functional!

the errormessage is now:


No default time zone is set. Please open PHP.ini and set the value of 'date.timezone' (Example: date.timezone = Europe/Paris) or open /conf/_advanced.php and set the value of $date_default_timezone (Example: $date_default_timezone = 'Europe/Paris';)
Uprade in progress
Upgrading b2evolution...
Checking files...
Preparing to install /.htaccess in the base folder... Already installed.

Upgrading data in existing b2evolution database...
Loading module: _core/model/__core.install.php
Loading module: collections/model/_collections.install.php
Loading module: files/model/_files.install.php
Loading module: sessions/model/_sessions.install.php
Loading module: messaging/model/_messaging.install.php
Loading module: maintenance/model/_maintenance.install.php
Checking DB schema version... 11300 : OK.
Upgrading cron tasks table...

An unexpected error has occurred!
If this error persists, please report it to the administrator.

Go back to home page

Additional information about this error:
MySQL error!

Unknown column 'ctsk_controller' in 'evo_cron__task'(Errno=1054)

Your query:

ALTER TABLE evo_cron__task

      CHANGE COLUMN ctsk_controller ctsk_key varchar(50) COLLATE ascii_general_ci NOT NULL AFTER ctsk_repeat_after,

      CHANGE COLUMN ctsk_name ctsk_name varchar(255) null COMMENT "Specific name of this task. This value is set only if this job name was modified by an admin user"

Nov 27, 2015 23:12

PHP Version
5.2.17
MySQL Version
5.5.46

aktuelle IP Ihres Accounts
85.13.143.19 / v079111.kasserver.com

zusätzliche PHP-CGI Versionen, nutzbar durch Dateiendung oder per [.htaccess]
PHP 5.2.17
.php5 .php52
PHP 5.3.28
.php53
PHP 5.4.42
.php54
PHP 5.5.26
.php55
PHP 5.6.13
.php56

Nov 27, 2015 23:15

I am working all day on this update now already

Read me
Installer
Your site
b2evolution CCMS
No default time zone is set. Please open PHP.ini and set the value of 'date.timezone' (Example: date.timezone = Europe/Paris) or open /conf/_advanced.php and set the value of $date_default_timezone (Example: $date_default_timezone = 'Europe/Paris';)
Conversion in progress
Normalizing DB charsets...
Loading module: _core/model/__core.install.php
Loading module: collections/model/_collections.install.php
Loading module: files/model/_files.install.php
Loading module: sessions/model/_sessions.install.php
Loading module: messaging/model/_messaging.install.php
Loading module: maintenance/model/_maintenance.install.php
Normalizing evo_antispam... OK
Normalizing evo_antispam__iprange... OK
Normalizing evo_basedomains... OK
Normalizing evo_bloggroups... OK
Normalizing evo_blogs... OK
Normalizing evo_blogusers...
An unexpected error has occurred!
If this error persists, please report it to the administrator.

Go back to home page

Additional information about this error:
MySQL error!

Unknown column 'bloguser_perm_item_type' in 'evo_blogusers'(Errno=1054)

Your query:

ALTER TABLE `evo_blogusers`

      MODIFY bloguser_perm_poststatuses    set('review','draft','private','protected','deprecated','community','published','redirected') COLLATE ascii_general_ci NOT NULL default '',

      MODIFY bloguser_perm_item_type       ENUM('standard','restricted','admin') COLLATE ascii_general_ci NOT NULL default 'standard',

      MODIFY bloguser_perm_edit            ENUM('no','own','lt','le','all') COLLATE ascii_general_ci NOT NULL default 'no',

      MODIFY bloguser_perm_cmtstatuses     set('review','draft','private','protected','deprecated','community','published') COLLATE ascii_general_ci NOT NULL default '',

      MODIFY bloguser_perm_edit_cmt        ENUM('no','own','anon','lt','le','all') COLLATE ascii_general_ci NOT NULL default 'no'

Nov 27, 2015 23:33

unfortunatedly seems my Database is now fu*** up by the broken SQL

Installer for version 5.2.2-stableb2evolution
After install: Blogs · Admin Current installation: Install menu · PHP info
No default time zone is set. Please open PHP.ini and set the value of 'date.timezone' (Example: date.timezone = Europe/Paris) or open /conf/_advanced.php and set the value of $date_default_timezone (Example: $date_default_timezone = 'Europe/Paris';)


Upgrading b2evolution...

Checking files...

Preparing to install .htaccess ... ERROR!
Failed to copy files!

Upgrading data in existing b2evolution database...

Loading module: _core/model/__core.install.php
Loading module: collections/model/_collections.install.php
Loading module: files/model/_files.install.php
Loading module: sessions/model/_sessions.install.php
Loading module: messaging/model/_messaging.install.php
Loading module: maintenance/model/_maintenance.install.php
Checking DB schema version... 11440 :

An unexpected error has occurred!

If this error persists, please report it to the administrator.

Go back to home page

Additional information about this error:

This version is too recent! We cannot downgrade to the version you are trying to install..

Nov 27, 2015 23:37

pls can I know : what B2Evo Version uses Database 11440
I don't mind using an old B2evo anymore, but I need to get it working some how!

Nov 28, 2015 00:27

I used the 2nd newest version because I think it might have less bugs!

The latest version's (in this case 6.6.6) purpose is always to fix the bugs of the previous on (6.6.5). Why would the previous one have less bugs?

Also the error you show now with upgrading to 6.6.6 is no longer related to datetime fiels issues. So it has been fixed. (The fact that your php.ini is missing a timezone is an unrelated problem of your server).

pls can I know : what B2Evo Version uses Database 11440

Download the releases and look into /conf/_application.php . You will see the DB version each uses.



Now, your successive copy/paste messages of the upgrader seem to indicate you have been acting carelessly about the upgrade procedure. Your situation is detailed here: http://b2evolution.net/man/unexpected-error-during-upgrade

Nov 28, 2015 01:38

Finally I solved the problem myself with the old systematic try and error system :)

After installing various versions I found one that works!
By installing the older 6.5.0-stable veröffentlicht am 24.06.15 the database bug dissapears
and I could even sucessfully run the database charset upgrade process!

sorry Francois,... something is not ok with 6.6.6 .

Nov 28, 2015 05:24

Do you want to send us an export of your DB so we can try it out?

Nov 28, 2015 12:25

Yes of course I could do that... but the whole database also contains 3 more B2Evos with other prefix and is rather big.

I just tried to update from the working 6.5.0 to 6.6.6 and it broke again!

This is b2evolution version 6.6.6-stable.

You cannot use the application before you finish configuration and installation.

Database schema is not up to date!
You have schema version «11440», but we would need «11486».
Please use the installer to finish your configuration/installation now.

when I try to update it says :

No default time zone is set. Please open PHP.ini and set the value of 'date.timezone' (Example: date.timezone = Europe/Paris) or open /conf/_advanced.php and set the value of $date_default_timezone (Example: $date_default_timezone = 'Europe/Paris';)
Normalizing DB charsets...
We have detected the following tables as not using UTF-8:

evo_plugin_captcha_img_14_data
evo_plugin_captcha_img_14_trackbacks_wl
evo_plugin_captcha_qstn_18_ip_question
Proceed with normalization procedure!Cancel

and then again the old problem:

No default time zone is set. Please open PHP.ini and set the value of 'date.timezone' (Example: date.timezone = Europe/Paris) or open /conf/_advanced.php and set the value of $date_default_timezone (Example: $date_default_timezone = 'Europe/Paris';)
Conversion in progress
Normalizing DB charsets...
Loading module: _core/model/__core.install.php
Loading module: collections/model/_collections.install.php
Loading module: files/model/_files.install.php
Loading module: sessions/model/_sessions.install.php
Loading module: messaging/model/_messaging.install.php
Loading module: maintenance/model/_maintenance.install.php
Normalizing evo_antispam... OK
Normalizing evo_antispam__iprange... OK
Normalizing evo_basedomains... OK
Normalizing evo_bloggroups... OK
Normalizing evo_blogs... OK
Normalizing evo_blogusers...
An unexpected error has occurred!
If this error persists, please report it to the administrator.

Go back to home page

Additional information about this error:
MySQL error!

Unknown column 'bloguser_perm_item_type' in 'evo_blogusers'(Errno=1054)

Your query:

ALTER TABLE `evo_blogusers`

      MODIFY bloguser_perm_poststatuses    set('review','draft','private','protected','deprecated','community','published','redirected') COLLATE ascii_general_ci NOT NULL default '',

      MODIFY bloguser_perm_item_type       ENUM('standard','restricted','admin') COLLATE ascii_general_ci NOT NULL default 'standard',

      MODIFY bloguser_perm_edit            ENUM('no','own','lt','le','all') COLLATE ascii_general_ci NOT NULL default 'no',

      MODIFY bloguser_perm_cmtstatuses     set('review','draft','private','protected','deprecated','community','published') COLLATE ascii_general_ci NOT NULL default '',

      MODIFY bloguser_perm_edit_cmt        ENUM('no','own','anon','lt','le','all') COLLATE ascii_general_ci NOT NULL default 'no'

Nov 28, 2015 12:30

here the database backup file from
spanienumzug.com/b2evolution/_backup/2015-11-28-12-12-34/

Edited by @mgsolipa: I deleted the file because it may contain sensitive data. Download link already sent to @fplanque by private message.

Nov 28, 2015 13:48

I just tried 6.6.0 it also fails !
going back to 6-5-0 solves the problem

Nov 28, 2015 18:34

@ingeniero I noticed that you have 5 table prefixes in your db, when I tried to upgrade bra_ and evo_ to 6.6.6, they failed. The rest of them ran as supposed, even the normalization tool.

The first prefix stopped at checkpoint 11460, missing a field that was supposed to be created at 11360. The second prefix failed in the checkpoint 11450 trying to create a field that was already created.

Both issues are associated with a corrupt state of the database that seems to be product of an upgrade crash.

Question #1: in which one of all your tables set are you getting the upgrade error at this moment?
Question #2: do you have any other backup? Maybe from before the bra_ and evo_ upgrade latest attempts?

Nov 30, 2015 14:15

Question #1:
As you likely have noticed by my prior postings the only relevant tables for this update Bug are the tables starting with EVO_

Question #2: I always shared the databases between several B2 Evos - is that a problem now with new versions?
It is long time that I updated "bra" ... "evo " is recently updated, maybe I can find an old backup

My hosting contract has a limited number of databases. I don't want to do any more updates, (at least not over 6.5.0)
because it costs so much time and frustration, so the other B2 Evos better stay old and unapdated.

The fact that the database backup contains more tables of other B2EVO insrtallation of older versions
should not affect the update as they have diferent prefixes and B"evo would probally ignore these tables.... or NOT ??

Thank you for deleting the backup file from here, I felt a bit unconfortable ...

Dec 05, 2015 14:00

@ingeniero

I always shared the databases between several B2 Evos - is that a problem now with new versions?

No, there is no problem with use a single database for different sites, the tables prefix concept remains untouched on recent versions of b2evolution.

I sent you a private message with the result of my upgrade tests. Please check it.

Regards!


Form is loading...

b2evolution – This forum is powered by b2evolution CMS, a complete engine for your website.