MySQL 8.0.17 breaks a bunch of queries

[irokin]

MEMBER is now a reserved word which breaks a bunch of queries involving columns named member. Why anyone thought that was a good idea to include in a patch release I have no idea. Only way to fix right now (without finding all the instances of queries involving member and quoting them with backticks) is to dump all your tables and downgrade to 8.0.16.

 

https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-17.html#mysqld-8-0-17-feature

[Daniel F]

We have fixed this for an upcoming release.

[All Astronauts]

Well this is annoying... <looks at own apps...>

[irokin]

If you want a quick way of seeing if you're affected, when logged in as an admin you'll see an error along the lines of "Template core/front/global/updateWarning is throwing an error." (assuming the template you're using hasn't removed the update notification). Alternatively if you login to the Admin CP you'll see a similar error that will prevent you accessing anything.

[bfarber]

This will be fixed in our upcoming 4.4.6 maintenance release (for our core software - we can't speak as to any third party code).

[xdrox]

I confirm. DO NOT, I repeat, DO NOT upgrade to Mysql 8, you'll regret it for hours.

[Adriano Faria]

On 7/31/2019 at 12:23 PM, bfarber said:

we can't speak as to any third party code

Maybe this could be posted by IPS on Contributors Chat so devs can plan updates to their resources. 

[b416]

10 hours ago, xdrox said:

I confirm. DO NOT, I repeat, DO NOT upgrade to Mysql 8, you'll regret it for hours.

I disagree. Yes, mysql 8.0.17 breaks things so don't upgrade to that version, but previous versions are Ok. 

I am running my forum on Percona 8.0.15 for months now, it's flawless. I also tried the original MySQL 8, same thing (before 8.0.17).

 

[bfarber]

On 8/10/2019 at 9:59 PM, Adriano Faria said:

Maybe this could be posted by IPS on Contributors Chat so devs can plan updates to their resources. 

Done

[irokin]

Still getting a few bad queries after 4.4.6

SELECT * FROM `ibf_core_post_before_registering` AS `core_post_before_registering` WHERE member=1234

system/Member/Member.php(4254): IPS\_Member->_processPostBeforeRegistering(NULL)

and

applications/core/modules/front/discover/popular.php(449): IPS\Db\_Select->setKeyField('member')

And another unrelated one in gallery in a compiled template along the lines of

image_= IS NULL

 

[newbie LAC]

4 hours ago, irokin said:

Still getting a few bad queries after 4.4.6

SELECT * FROM `ibf_core_post_before_registering` AS `core_post_before_registering` WHERE member=1234

system/Member/Member.php(4254): IPS\_Member->_processPostBeforeRegistering(NULL)

and

applications/core/modules/front/discover/popular.php(449): IPS\Db\_Select->setKeyField('member')

Fixed in 4.4.6 (104039)
 

4 hours ago, irokin said:

And another unrelated one in gallery in a compiled template along the lines of

Need more info. Record from System Logs

[bfarber]

On 9/1/2019 at 8:30 PM, irokin said:

And another unrelated one in gallery in a compiled template along the lines of

image_= IS NULL

 

This doesn't look like a MySQL 8 issue on the surface - I'd bet you have some Gallery data in your database that isn't quite right, but a full stacktrace would be needed.

[irokin]

On 9/2/2019 at 3:08 PM, newbie LAC said:

Fixed in 4.4.6 (104039)

I can still see it unquoted in my 4.4.6 update bundle

$where = $secret ? array( 'member=? OR secret=?', $this->member_id, $secret ) : array( 'member=?', $this->member_id );

 

9 hours ago, bfarber said:

I'd bet you have some Gallery data in your database that isn't quite right

Very possible

[newbie LAC]

4 hours ago, irokin said:

I can still see it unquoted in my 4.4.6 update bundle

Download files from client area

Where are 3 versions of 4.4.6

4.4.6 - 104038
4.4.6 - 104039
4.4.6 - 104040

[b416]

Hi, 

Is it safe now to upgrade to MySQL 8.0.17 with IPS 4.4.7 ?   On my test site it seems to be working, but did only a couple of elementary tests...

 

[bfarber]

It should be, yes. If you encounter any issues we will treat them as bugs and correct them for the next maintenance release. To that end, I think there are 1 or 2 outlier MySQL 8.0.17 issues corrected for 4.4.8 (things that you would only hit in specific scenarios and won't really affect your day to day use of the software).

[b416]

Well, I did it in production yesterday and so far so good.  I am using the Percona flavor.