Recent Topics

Deleting a draft post I get an MSQL error message

Started by on Jun 23, 2015 – Contents updated: Jul 23, 2015

Jun 23, 2015 22:07    

b2e 6.4.4
PHP Built On Linux 2.6.18-498.el5.lve0.8.80PAE #1 SMP Fri Dec 19 10:41:15 EST 2014 i686
Database Version 5.1.61
Database Collation utf8_general_ci
PHP Version 5.4.42
Web Server Apache
WebServer to PHP Interface cgi-fcgi

*******
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!

The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay(Errno=1104)

Your query: Delete object from db with were condition

DELETE evo_items__item, T_postcats1, T_items__version2, T_slug3, T_items__itemtag4, T_items__item_settings5, T_items__subscriptions6, T_items__prerendering7, T_users__postreadstatus8

FROM evo_items__item
LEFT JOIN evo_postcats AS T_postcats1 ON T_postcats1.postcat_post_ID = post_ID
LEFT JOIN evo_items__version AS T_items__version2 ON T_items__version2.iver_itm_ID = post_ID
LEFT JOIN evo_slug AS T_slug3 ON T_slug3.slug_itm_ID = post_ID
LEFT JOIN evo_items__itemtag AS T_items__itemtag4 ON T_items__itemtag4.itag_itm_ID = post_ID
LEFT JOIN evo_items__item_settings AS T_items__item_settings5 ON T_items__item_settings5.iset_item_ID = post_ID
LEFT JOIN evo_items__subscriptions AS T_items__subscriptions6 ON T_items__subscriptions6.isub_item_ID = post_ID
LEFT JOIN evo_items__prerendering AS T_items__prerendering7 ON T_items__prerendering7.itpr_itm_ID = post_ID
LEFT JOIN evo_users__postreadstatus AS T_users__postreadstatus8 ON T_users__postreadstatus8.uprs_post_ID = post_ID
WHERE post_ID IN ( 102 )

*******

Jun 23, 2015 23:19

@saunders I'm not able to reproduce this issue. Can you please check the value of these MySQL variables: SQL_BIG_SELECTS and
MAX_JOIN_SIZE.

Also, can you copy&paste this query directly in your phpMyAdmin (or similar) and tell us how is it going:


DELETE evo_items__item, T_postcats1, T_items__version2, T_slug3, T_items__itemtag4, T_items__item_settings5, T_items__subscriptions6, T_items__prerendering7, T_users__postreadstatus8

FROM evo_items__item
LEFT JOIN evo_postcats AS T_postcats1 ON T_postcats1.postcat_post_ID = post_ID
LEFT JOIN evo_items__version AS T_items__version2 ON T_items__version2.iver_itm_ID = post_ID
LEFT JOIN evo_slug AS T_slug3 ON T_slug3.slug_itm_ID = post_ID
LEFT JOIN evo_items__itemtag AS T_items__itemtag4 ON T_items__itemtag4.itag_itm_ID = post_ID
LEFT JOIN evo_items__item_settings AS T_items__item_settings5 ON T_items__item_settings5.iset_item_ID = post_ID
LEFT JOIN evo_items__subscriptions AS T_items__subscriptions6 ON T_items__subscriptions6.isub_item_ID = post_ID
LEFT JOIN evo_items__prerendering AS T_items__prerendering7 ON T_items__prerendering7.itpr_itm_ID = post_ID
LEFT JOIN evo_users__postreadstatus AS T_users__postreadstatus8 ON T_users__postreadstatus8.uprs_post_ID = post_ID
WHERE post_ID IN ( 102 )

(you should get the same result)

Thanks.

Jun 24, 2015 16:59

I got the same error message after having set the query direktly in my PhpMyAdmin. (I asked for the values but have not got an answer yet by my host)
Should I generate a totally new DB and import the data to check whether this is a DB failure by updating?
(Although testing the DB and optimizing shows, that all should be fine).

Notice
When I updated from 5.2.2 to 6.4.4 I had to run an extra DB update getting a "ok" afterwards".
While updating there is a shift to innoDB annouced. But this is a normal MySQL DB. Why does this happen?

Thanks and Regards, Will

Jun 25, 2015 16:44

By putting this line before the Command in PhpMyAdmin the process rund without error
'SET SESSION SQL_BIG_SELECTS=1' und 'SET SESSION MAX_JOIN_SIZE=17000000'

Jun 25, 2015 16:49

When running the update process I get this message with a list of Alter Table lines and with "Update failed".
When continuing with DB actualization there is no further error message - but I am in doubt, whether the update really was done. Repeating the Update Process the same procedure starts at the beginning as if there had never been a db update before.

Jun 26, 2015 00:02

So you click on the button at the bottom of your screenshot and let it run, then if you try to upgrade again, you get the exact same screen again?

Also, have you tried running the "DB normalization" tool?

Jun 26, 2015 01:13

Yes, when I let it run again I get the same screen again.
I did run "DB normalization" tool

Jun 26, 2015 05:43

All this with b2evolution version 6.5.0?

Jun 26, 2015 10:51

The Screenshot shows, that while updating the system tries to alter tables into innoDB. But the DB is MyISAM
Why does this happen and may this be the reason why the update of the DB does not persist?

Jun 27, 2015 14:53

Why disabled? Is InnoDB obligatory? I am using MySQL MyISAM DB but not InnoDB.
I have been on board of b2e since version 2.9 and have always used MyISAM DB and had never ever problems while updating in regard of InnoDB.
My web host offers InnoDB but no mixed servrices (InnoDB for some Tables and MyISAM for others).
Is this critical in 6.x?

Jun 27, 2015 23:00

Yes InnoDB is critical (and this is not new to 6.x). You can't have Transactions on MyISAM. b2evolution is NOT designed to function properly without Transactions. (Again, this is not new to 6.x) If you managed to do it without transactions so far, you're lucky but your DB is probably full of inconsistencies that may degrade performance and might create bugs from time to time.

I don't understand why you can't mix MyISAM & InnoDB. I have never seen that. Again I would be very interested in knowing who your webhost is.

Do you have to choose between MyISAM and InnoDB when creating your DB?? Does it end up on a different server depending on what you choose?

Any info about this hosting architecture will be very appreciated so we can document it for other users who may have the same issue.

Thank you.

Jun 27, 2015 23:50

Thanks Francoise, also for your patience.
My webhost is http://bit.ly/1LCaCyg with this specifications: http://bit.ly/1Hn6vU2. For InnoDB an additional fee of 1,80 EUR is accounted for each DB used.
It is a Austria based webhost. Years ago I transferred my web presence to a host with a local place of jurisdiction. I had some troubles before with an foreign webhost because of a lack of service uptimes.

Sry for using bit.ly but the name of my host is on your blacklist :(

Jun 30, 2015 00:15

Wow, I guess they do something weird in the background. After reading this post, I tried to install / upgrade b2evo disabling InnoDB support and it failed every time since version 3.3.3 :S

How does your site work so far?

Maybe they automatically ignore the section "ENGINE = innodb" of the sql sentences (if somehting like that is possible). So, if you didn't manually replace them before, the question is: Why is it failing now?

Jul 01, 2015 18:47

Thank you for the info.

We really cannot make b2evolution work on MyISAM. It's not powerful enough and will not keep your data safe. We would have to spend many hours to make our product less robust than it is with InnoDB. It would not make sense.

Jul 01, 2015 19:16

@mgsolipa @fplanque that's really strange. I have used b2e since years only by MyISAM tables. The performance was ok and there had no data been corrupted. But nevertheless I wouldn't ask for a b2e MyISAM Fork :-)
I will try to find out why my installation runs without any problems although it should not work. My host assures that there was no work around. It is a standard MySQL Database without InnoDB usage and with no specific compensations.
I tried to install a new instance of b2e and a new DB - no problems.
My be because of my adapted .htaccess. Using the standard .htaccess I get an error page. I added some lines to .htaccess to improve the performance, but without having InnoDB workarounds in mind.

Jul 02, 2015 02:42

Previous versions of b2evolution just assumed that when it created a table in InnoDB it was created in InnoDB. Newer versions actually check that when a table is created, everything is created as it should. And that is why it complains.

It doesn't mean that your b2evolution was working fine on MyISAM before. It only means that you did not see any major issue so far. It's easy to not run into problems if you are the only admin of your site and traffic is moderate. But your DB is probably full of junk.

Jul 02, 2015 08:18

Thanks for information and support. let us close that thread. I will shift to InnoDB or move to another host.
b2e is my favorite CMS. It is awesome and I thank you for your work.
Regards

Jul 08, 2015 14:22

Hello again,

I set up an InnoDB and tried to import my data. I get this error
/*!40101 SET NAMES utf8mb4 */; MySQL meldet: Dokumentation #1115 - Unknown character set: 'utf8mb4'

When searching for utf8mb4 in my old DB I get no hit. So is there an efficient way to to get the transfer done?

Thanks and regards
Conrad

Jul 11, 2015 11:03

@saunders what version of MySQL are you using?

Maybe you should just check your your SQL file and replace all the [b]utf8mb4[/b] appearances with [b]utf8[/b].

Jul 11, 2015 14:33

@mgsolipa all tables of the dump have utf8_general_ci. I could not find utf8mb4. The problem seems to be caused by the MySQL InnoDB. (I set up a test DB MyISAM and there was no problem Importing the dump". But in the MySQL InnoDB the command "SET NAMES utf8mb4" leads to that error message.

MySQL-Version: 5.1.73-log
PHP version: 5.4.42

Jul 13, 2015 17:28

Solved - in the dump I commented out /*!40101 SET NAMES utf8mb4 */ - than import was no problem. Than I reran the DB update.
No it works.
Thanks for your support.

Jul 14, 2015 17:31

@saunders: so, then this wasn't true: [quote=]I could not find utf8mb4[/quote] :D

Nice to now your issue is already solved.


Form is loading...

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