'MySQL upgrade causing unexpected results on simple WHERE clauses

I'm upgrading my Laravel app to use MySQL 8.0.23 from 8.0.20, and I see an issue with WHERE clauses that produce unexpected results.

SELECT * FROM guilds 
    WHERE platform_id = 1 
    AND platform_server_id = '407254666900930563';

Running ^ directly on the MySQL server produces the record I'm looking for (regardless of whether 40725... is an int or a string). However, using it through Laravel's Eloquent Query Builder, it is not finding the record. Here's the eloquent code.

// Won't find it if it's a string, will if it's an int
$serverId = '407254666900930563';
Server::where('platform_id', 1)
    ->where('platform_server_id', $serverId)
    ->first()

The data is here:

enter image description here

I've verified in the DB that platform_id does indeed = 1 (verified by running the SQL directly).

Here's my MySQL config on the Laravel side.

'charset' => 'utf8mb4',
'collation' => 'utf8mb4_unicode_ci',
'prefix' => '',
'prefix_indexes' => true,
'strict' => true,
'engine' => null,
'options' => extension_loaded('pdo_mysql') ? array_filter([
    PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
]) : [],
'modes'  => [
    /**
     * Disabled because of a group by we need
     */
    //'ONLY_FULL_GROUP_BY',

    'STRICT_TRANS_TABLES',
    'NO_ZERO_IN_DATE',
    'NO_ZERO_DATE',
    'ERROR_FOR_DIVISION_BY_ZERO',
    'NO_ENGINE_SUBSTITUTION',
]

I've looked through MySQL changelogs and am not seeing what could be causing this. Does anyone have any ideas about what I should investigate?

CREATE TABLE statement is:

CREATE TABLE `guilds` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `platform_id` bigint unsigned NOT NULL,
  `name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `platform_server_id` bigint unsigned NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `guilds_platform_id_platform_server_id_unique` (`platform_id`,`platform_server_id`),
  KEY `guilds_platform_server_id_index` (`platform_server_id`),
) ENGINE=InnoDB AUTO_INCREMENT=691420 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
phpmysqllaravellaravel-query-builder


Solution 1:[1]

I would suggest 2 ways to try, in both cases I recommend you to use the where array syntax because is more flexible:

Model query

Server::where([
  'platform_id'=> 1,
  'platform_server_id' => $serverId
])->first();

Or more explicit "where"

Server::where([
  ['platform_id', '=', 1],
  ['platform_server_id', '=', $serverId]
)->first();

DB query builder

// table 'servers' or 'guilds', as I see above
DB::table('guilds')->where([
  'platform_id'=> 1,
  'platform_server_id' => $serverId
])->first();

Or more explicit "where"

// table 'servers' or 'guilds', as I see above
DB::table('guilds')->where([
  ['platform_id', '=', 1],
  ['platform_server_id', '=', $serverId]
])->first();

Extra suggestions

Mysql changelog

Here I found the mysql 8.0.23 changelog for you to check.

Laravel Tinker you can use the command php artisan tinker, that allows you to "write pure" code in the terminal and execute it, you can try the queries I mentioned above and also try other without refactoring your code.

Copy and paste this to try: Server::where([['platform_id', '=', 1],['platform_server_id', '=', 407254666900930563])->first();

Solution 2:[2]

These don't look the same:

407254666900930563

Is platform_server_id a BIGINT or a VARCHAR?

Also, I see inconsistencies in quoting the value.

Here's a guess: When Laravel runs the query without quotes, it sees a number, converts it to DOUBLE. But a Double cannot hold more than about 16 significant digits. You seem to need all 18.

Please use the real numbers -- BIGINT also has a limitation that these numbers are close to.

Please provide SHOW CREATE TABLE; there are other details that may be relevant.

Solution 3:[3]

A few months ago we had to add "doctrine/dbal" to our "require" list in composer.json to fix some strange stuff. You could try that by running this:

composer require doctrine/dbal

Solution 4:[4]

The declaration of the id and the query you use is confused. The platform_server_id in the create table statement is bigint, but you try to compare with a string or as you mentioned integer:

`platform_server_id` bigint unsigned NOT NULL

$serverId = '407254666900930563';
Server::where('platform_id', 1)

You can not compare these values directly without conversion as you see in this answer: https://stackoverflow.com/a/72072468/7996624

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2
Solution 3 Magmatic
Solution 4 István Borsányi

Related Questions