WordPress.org

Make WordPress Core

Opened 2 years ago

Closed 2 years ago

Last modified 22 months ago

#19748 closed defect (bug) (fixed)

dbDelta fails for capital prefixed tables

Reported by: netweblogic Owned by: nacin
Milestone: 3.4 Priority: normal
Severity: major Version: 1.5
Component: Database Keywords: has-patch commit
Focuses: Cc:

Description

Probably a linux/Windows mysql version issue since not all sites do this, but on some instances if your tables are using capitals, mySQL will fail to recognize a create statement should be an update of a present table.

Reason is because dbDelta checks lower cased names of the table to see if it should create or update a table.

Offending line is /wp-admin/includes/upgrade.php line 1471

Changing this

$cqueries[trim( strtolower($matches[1]), '`' )] = $qry;

to

$cqueries[trim($matches[1], '`' )] = $qry;

and it works. Is there a reason why dbDelta forces a lowercase lookup for present tables on the DB?

Attachments (1)

19748.patch (1.6 KB) - added by SergeyBiryukov 2 years ago.

Download all attachments as: .zip

Change History (12)

comment:1 kurtpayne2 years ago

  • Cc kpayne@… added
  • Keywords dev-feedback added
  • Version changed from 3.3.1 to 1.5

This line was introduced in r1575 and updated in r10948, but strtolower was always there. There's no explanation of why strtolower was used. Database tables are stored as files and not cased properly in windows. The author probably expected that table names would always be lower cased.

Are you running into this error by setting the $table_prefix in your wp-config.php file to a string with upper case letters?

comment:2 netweblogic2 years ago

I'm the author of the Events Manager plugin. Some users with capital prefixes as you say do have this problem.

Turning on WP_DEBUG shows that dbDelta is trying to create a table because searching lowercase for capped tables returns nothing.

I'm pretty sure this'd affect any plugin using dbDelta.

comment:3 SergeyBiryukov2 years ago

  • Milestone changed from Awaiting Review to 3.4

comment:4 SergeyBiryukov2 years ago

  • Component changed from General to Database

SergeyBiryukov2 years ago

comment:5 SergeyBiryukov2 years ago

  • Keywords has-patch added

comment:6 j.conti2 years ago

  • Cc j.conti@… added

Yes,

Today we had to release a second WangGuard version in few hours because dbDelta breaks the upgrade in databases with capital letters in a prefix.

comment:7 nacin2 years ago

We might need to end up doing a case-insensitive comparison then, all the way down the board, which might mean doing a SHOW TABLES LIKE rather than the DESCRIBE we added in 3.3.

comment:8 stephenh19882 years ago

  • Cc stephenh1988 added

comment:9 nacin2 years ago

  • Keywords commit added; dev-feedback removed

I'm fine with a straight commit to remove the strtolower(). It was clearly an inaccurate assumption made many years ago.

comment:10 nacin2 years ago

  • Owner set to nacin
  • Resolution set to fixed
  • Status changed from new to closed

In [20704]:

Do not force table names to be lowercase in dbDelta, as this breaks sites using capital letters in table prefixes. props netweblogic. fixes #19748.

comment:11 DigiproveDevelopment22 months ago

Since the fix, plugin authors will now find that dbDelta will no longer find tables it had created earlier. So now authors will need to figure out how to exist successfully in 3.4+ as well as earlier versions.

We were badly caught out as until recently we had not realised that our plugin simply didn't work for many users. I think a bug of this severity should be reflected in the already-sparse online documentation, in this case an advisory about wp prefix and also in dbDelta.

Note: See TracTickets for help on using tickets.