Recent Topics

B2evo 5.1.0 beta upgrade MySQL Error 1067 / utf8 encoding problem

Started by on May 12, 2014 – Contents updated: Jul 24, 2014

May 12, 2014 10:51    

Sorry if beta bugs are to be posted elsewhere, just wanted to let you know. I upgraded to beta which I usually never do because it had such a necessary feature for me - multiblog site! This solves my theming issue more or less.

Just wanted to let you know, when I ran the installer on 5.0.7 (fresh installation, not an operational site yet, just trying to figure out the engine and build a design) and chose "Upgrade my installation" I got a MySQL error:

Code

Произошла неожиданная ошибка! [Unexpected error]
 
Если эта ошибка не исчезает, пожалуйста, сообщите администратору.
 
Вернуться к домашней странице
Дополнительная информация о данной ошибке: [Additional Information]
 
MySQL error!
 
Invalid default value for 'grp_perm_blogs'(Errno=1067)
 
Your query:
 
ALTER TABLE evo_groups
 
      MODIFY grp_perm_blogs enum('user','viewall','editall') COLLATE ascii_bin NOT NULL default 'user',
 
      MODIFY grp_perm_xhtmlvalidation VARCHAR(10) COLLATE ascii_bin NOT NULL default 'always',
 
      MODIFY grp_perm_xhtmlvalidation_xmlrpc  VARCHAR(10) COLLATE ascii_bin NOT NULL default 'always',
 
      MODIFY grp_perm_stats enum('none','user','view','edit') COLLATE ascii_bin NOT NULL default 'none'

As far as I understand, the problem was with ascii_bin part. My table was already created in Unicode the first time but after the partial upgrade I noticed some of my tables went cp1251 (which would be wrong for Russian).

I then chose the "upgrade my DB to UTF8" and the process went smoothly, upgrade finished.

But this kind of error (if it was already UTF8 and the upgrader messes it up) could be a problem for many users, so I guess it should be fixed.

There was another encoding related problem, I think it could be connected, so, sorry if it's the wrong idea to put them into the same theme. I didn't make the screenshot and now it's all upgraded but I'll describe it:

My site was in Russian. When first opening the installer/upgrader it attempted to show me its page in Russian except there were all question marks ???????? ????? ????? - like this. In the list of other encodings, all other languages were question marks too except for their code names (en_US) EXCEPT for French i.e. Français (FR) which was showing correctly.
So I chose French. Then Russian again, then the page displayed normally in Cyrillic (Russian).

Also easily fixed but could be confusing for many and I wonder if it wasn't connected with the fact I was already in UTF8 and the installer attempted ASCII although I could be completely off-track here. But still.

May 12, 2014 10:57

Oups, I was too fast to say. The DB Upgrade procedure went well but my site still shows

Code

This is b2evolution version 5.1.0-beta-6.
 
You cannot use the application before you finish configuration and installation.
 
Database schema is not up to date!
 
You have schema version «11220», but we would need «11235».
 
Please use the installer to finish your configuration/installation now.

Running the upgrade again didn't help - all the same results.
I.e. Upgrade DB to utf8 finishes fine but the site isn't operational
While Upgrade b2evo installation stops with MySQL error 1067 at the same operation as above.
I'm stuck. :(

May 12, 2014 11:04

This is my actual evo_groups table properties
evo_groups
Column Type Null Default Comments
grp_ID int(11) No
grp_name varchar(50) No
grp_perm_blogs enum('user', 'viewall', 'editall') No user
grp_perm_bypass_antispam tinyint(1) No 0
grp_perm_xhtmlvalidation varchar(10) No always
grp_perm_xhtmlvalidation_xmlrpc varchar(10) No always
grp_perm_xhtml_css_tweaks tinyint(1) No 0
grp_perm_xhtml_iframes tinyint(1) No 0
grp_perm_xhtml_javascript tinyint(1) No 0
grp_perm_xhtml_objects tinyint(1) No 0
grp_perm_stats enum('none', 'user', 'view', 'edit') No none
Indexes
Keyname Type Unique Packed Column Cardinality Collation Null Comment
PRIMARY BTREE Yes No grp_ID 6 A No

May 13, 2014 05:07

Hello @wildcat,

After having written a first comment, I re-tested and got partially different results, but I'm still unable to reproduce the issue exactly as you described.

As far as I understand, the problem was with ascii_bin part. My table was already created in Unicode the first time but after the partial upgrade I noticed some of my tables went cp1251 (which would be wrong for Russian).

I thought that the key of the problem was in the line above, so I tested several options and the only way that I found to achieve something like that was setting the database collation to cp1251_bin (or cp1251_general) before the first install, but the upgrade ran smooth, even the UTF8 upgrade ran flawless and the site is fully operational. Actually, I also got the ?????? screen before the update which is not normal, so let wait for the developers opinion due to b2evo is heavily tested in Russian and maybe they could give us better pointers on this.

In the meantime, please try to start all the test over again and let us know any extra detail that could be useful.

Thanks.

May 17, 2014 19:50

Thank you for replying.

I don't understand why COLLATE should be ascii_bin while the database is in utf8?
What's the point in upgrading it to utf8 if the installer writes down something else?
I'm not even sure I should have been upgrading the db since it was already in utf8
But I want to emphasis that it has no effect on the upgrader whether I run this db upgrade 1st or not.
Only error comes up faster (1st time there was a long list of successful upgrades, now it's short.

I'm not sure what else to do because I get the same results all over again every time. Except starting over the site.

I wonder what are the estimates on a release of this new version.
I really would like to start my blog instead of having to dig into mysterious technical issues I don't really understand so well.

P.S. The groups table upgrade is really the issue but I cannot understand why and how to get past it!

May 17, 2014 20:34

OK, I started over with version 5.0.8 (empty, no test blogs created) via installers reset.
Then I ran the upgrade procedure to 5.1.0 beta again and got the same error again.
I am on a shared hosting so if the problem is somewhere in the server config, I cannot help it.
Here's the server info:
Apache 2.2.25
MySQL 5.5.32-cll-lve
PHP 5.2.17
Since I already erased everything, will go try the fresh install of the beta now to see what happens.
I would really appreciate some comment of the developers. I really really want to start the blog in b2evo despite all the plugins I will need lacking so I'd have to patch them in myself (like Facebook and Vkontakte, having those would really help popularise the engine).

May 17, 2014 21:20

Since your very first post, people in the b2evo staff are performing several test trying to reproduce this issue. The result: no success.

This appears to be a very localized issue in your server environment, we are even thinking in a mysql bug or something up to the server side, because, as I said before, one of our main developers is Russian and he doesn't heard something like this before. However, the ticket related is still open, so we will let you know about any progress.

I would like to ask, why are you installing a stable version and then upgrading to the beta release? Could you start working directly with version 5.1.0?

Regards!

May 17, 2014 21:49

OK, this is really NOT an upgrading problem. Tried fresh install and encountered the same error:

Code

Создание таблиц b2evolution... [CREATING DB TABLES]
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
Created table «evo_groups»
Произошла неожиданная ошибка! [UNEXPECTED ERROR]
 
Если эта ошибка не исчезает, пожалуйста, сообщите администратору.
 
Вернуться к домашней странице
Дополнительная информация о данной ошибке: [ADDITIONAL INFORMATION]
 
MySQL error!
 
Invalid default value for 'grp_perm_blogs'(Errno=1067)
 
Your query:
 
CREATE TABLE evo_groups (
 
      grp_ID                           int(11) NOT NULL auto_increment,
 
      grp_name                         varchar(50) NOT NULL default '',
 
      grp_perm_blogs                   enum('user','viewall','editall') COLLATE ascii_bin NOT NULL default 'user',
 
      grp_perm_bypass_antispam         TINYINT(1) NOT NULL DEFAULT 0,
 
      grp_perm_xhtmlvalidation         VARCHAR(10) COLLATE ascii_bin NOT NULL default 'always',
 
      grp_perm_xhtmlvalidation_xmlrpc  VARCHAR(10) COLLATE ascii_bin NOT NULL default 'always',
 
      grp_perm_xhtml_css_tweaks        TINYINT(1) NOT NULL DEFAULT 0,
 
      grp_perm_xhtml_iframes           TINYINT(1) NOT NULL DEFAULT 0,
 
      grp_perm_xhtml_javascript        TINYINT(1) NOT NULL DEFAULT 0,
 
      grp_perm_xhtml_objects           TINYINT(1) NOT NULL DEFAULT 0,
 
      grp_perm_stats                   enum('none','user','view','edit') COLLATE ascii_bin NOT NULL default 'none',
 
      PRIMARY KEY grp_ID (grp_ID)
 
    ) ENGINE = innodb DEFAULT CHARSET = utf8

++ It must be a bug in MySQL perhaps or something (there was a similar one http://bugs.mysql.com/bug.php?id=14280).
Should I contact my hosting support for this too?

May 20, 2014 23:10

@wildcat It looks like a MySQL issue indeed, there is no way for us to reproduce this issue as you describe it. Could you please make this test? (if you haven't already done it): create a new database with character set: 'utf8' and collation: 'utf8_general_ci' and try install the 5.1.0-beta version into that database.

Jun 11, 2014 13:47

I cannot create the database with suc properties because cPanel has a separate tool and it creates in cp1251 by default.
However my database is already in utf8, probably due to previous running of utf8 upgrade tool by b2evo. See picture:

I could manually create the database with such properties just in case with PHPMyAdmin.
However such database is not seen by cPanel and I have no other way to assign user privileges.
Therefore purely for test reasons I tried this new DB with my main MySQL user credentials but I got the same result:
Invalid default value for 'grp_perm_blogs'(Errno=1067)

I did some tests trying to create any table with enum rows and user specified default value. They all failed.
I have found two similar bugs for MySQL from way back but none of them match exactly:
http://bugs.mysql.com/bug.php?id=20108 (2006) This one had ut8_turkish encoding/collate problem only which isn't my case
http://bugs.mysql.com/bug.php?id=44368 (2009) the closest, claimed as fixed in 5.1.something, NO WAY with any collate method

@mgsolipa could you be kind enough to tell me which MySQL version you have? May be I can get my hoster to upgrade.
I have also wrote a report to MySQL bugs http://bugs.mysql.com/bug.php?id=72958 but that's useless anyway without my hoster updating it.

Jun 11, 2014 14:14

Oh, holy crackers! It just worked when I removed the COLLATE ascii_bin from the initial command!
I mean I executed just that one command causing errors. Where do I change the entire install script?
Or... what was the reason for this ascii_bin in the first place?

Jun 11, 2014 16:41

OK, I blindly removed all COLLATE ascii_bin that I found in any files and it installed. Working for now. Phew!

I would like to hear however what am I risking by this. And I do not understand why a petname or file name should be compared as ASCII (just something I noticed) btw. Perhaps it was an overkill? Why indeed make utf8 database then?

Jul 18, 2014 21:26

I persuaded my hoster to move me to another server with a different MySQL version 5.5.32-cll-lve

Then I upgraded to b2evo 5.1.1 beta without problems. Well... almost!
The Site settings under Sturcture in Admin panel wouldn't save, namely the Site Name would become empty every time and the default blog would change apparently but not factually. Not to mention my subdomains didn't work any more.

Well, I decided to start over... bump, here it goes again!

Создание таблиц b2evolution...
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
Created table «evo_groups»
Произошла неожиданная ошибка!

Если эта ошибка не исчезает, пожалуйста, сообщите администратору.

Вернуться к домашней странице
Дополнительная информация о данной ошибке:

MySQL error!

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

Your query:

CREATE TABLE evo_groups (

grp_ID int(11) NOT NULL auto_increment,

grp_name varchar(50) NOT NULL default '',

grp_perm_blogs enum('user','viewall','editall') COLLATE ascii_bin NOT NULL default 'user',

grp_perm_bypass_antispam TINYINT(1) NOT NULL DEFAULT 0,

grp_perm_xhtmlvalidation VARCHAR(10) COLLATE ascii_bin NOT NULL default 'always',

grp_perm_xhtmlvalidation_xmlrpc VARCHAR(10) COLLATE ascii_bin NOT NULL default 'always',

grp_perm_xhtml_css_tweaks TINYINT(1) NOT NULL DEFAULT 0,

grp_perm_xhtml_iframes TINYINT(1) NOT NULL DEFAULT 0,

grp_perm_xhtml_javascript TINYINT(1) NOT NULL DEFAULT 0,

grp_perm_xhtml_objects TINYINT(1) NOT NULL DEFAULT 0,

grp_perm_stats enum('none','user','view','edit') COLLATE ascii_bin NOT NULL default 'none',

PRIMARY KEY grp_ID (grp_ID)

) ENGINE = innodb DEFAULT CHARSET = utf8

I am really desperate!

Jul 21, 2014 17:11

Guys, since my hosters had the default MySQL encoding to cp1251, they offered to move to another server. My sites have just changed countries! Default encoding utf8mb4 now but the problem is still the same! The only solution to remove manually the COLLATE ascii_bin from all the installer scripts. Therefore a question: is there any justified reason for it to be ascii_bin and not like everything else??? PLS

Jul 22, 2014 23:24

@wildcat in my case it's kind of frustrating not being able to reproduce your issue.

There are reasons indeed to define those fields as ascii_bin. I guess that the devs could explain it much clearer than me, I'll ask :D

However, if you give a try to the GitHub version (https://github.com/b2evolution/b2evolution), you will find that the most of those fields have been changed to ascii_general_ci. Maybe this change could finally make your site to work.

Just to add a little more information, you said this:

The only solution to remove manually the COLLATE ascii_bin from all the installer scripts.

What collation do you use to make your site to work properly?

Regards!

Jul 23, 2014 01:43

@mgsolipa wrote earlier:

The only solution to remove manually the COLLATE ascii_bin from all the installer scripts.

What collation do you use to make your site to work properly?

Regards!

I don't use anything. And the site doesn't actually work.

I mean, I simply REMOVED this instruction (therefore they ingehit table collation) to be able to install the engine. Otherwise same error persists (1067).

But now I have other issues with 5.1.1 - I guess I should create separate bug reports or try that GitHUB version although I have to yet figure out how to grab it from there.

I'm seriously starting to think I should take an easier way like you know, competition. I can't even start the damned blog, let alone add all the functionality I need that is missing even as plugins. :(

Jul 24, 2014 01:04

I am happy to inform you that the GitHub version (yesterday's) did the trick - I did a clean reinstall and it doesn't have the same problem any more. Sadly, the other problems persist.


Form is loading...

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