I'm using PHP and PDO to connect to a remote RDS instance running MySQL. Previously it was running MySQL 5.7. I upgraded it to MySQL 8.0.15. After I did that, all of my PHP code that was written without an explicit schema name stopped working.
For reference I'm connecting to pdo using the dbname attribute which points to a valid schema. I've also done use schema_name; on my upgraded database.
Here is the code to connect using PDO:
$database = new PDO('mysql:host=<HOST>;dbname=cool;charset=<CHARSET>, <USERNAME>, <PASSWORD>);
Now, if I have a table named groups in my cool schema and I execute the following code:
$sql = 'SELECT * FROM groups';
$sth = $database->prepare($sql);
$sth->execute();
I used to have no issues in MySQL 5.7, and this code would execute. However, after the RDS upgrade, I'm getting the following error:
Warning: PDOStatement::execute(): SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'groups' at line 1 in ...
And the query itself fails. But, if I change the code to the following:
$sql = 'SELECT * FROM cool.groups';
$sth = $database->prepare($sql);
$sth->execute();
I get no error, and everything is returned upon fetching it. I've been talking to AWS support who has assured me that nothing is wrong on their end and that despite some settings not being carried over from the upgrade (namely the DB Name was randomly changed), that there is no issue.
Is there a bug with PDO? After this upgrade, any queries that did not explicitly specify a schema are breaking and this is a giant legacy code base, which is a major issue. We also can't go back to our old MySQL version.
