Monitor Reply

Support

SQL error on MySQL8

Permalink Browser Info Environment March 18, 2024 at 5:09 PM

I got this error on MySQL8:

Exception Occurred: /path/to/concrete/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php:90 An exception occurred while executing 'RENAME TABLE groups TO groups_tmp;':
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 TO groups_tmp' at line 1 (0)

I fixed it by wrapping table names with ` to sanitize Groups.

public function getFixScriptRows($appendCorrect = false, $lowerToUpper = true)
    {
        $names = $this->getDatabaseTableNames(true);
        $currentTables = $this->getCurrentTables();
        $rows = array();
        foreach ($names as $tbl) {
            $migrateTbl = $lowerToUpper ? strtolower($tbl) : $tbl;
            $tbl = !$lowerToUpper ? strtolower($tbl) : $tbl;
            if ($appendCorrect || ($key = array_search($migrateTbl, $currentTables)) !== false) {
                $tmpName = $migrateTbl . "_tmp";
                array_push($rows, "RENAME TABLE `" . $migrateTbl . "` TO `" . $tmpName . "`;");
                if ($appendCorrect || ($key2 = array_search($tbl, $currentTables)) !== false) {
                    array_push($rows, "DROP TABLE IF EXISTS `" . $tbl . "`;");
                    unset($currentTables[$key2]);
                }

Viewing 15 lines of 21 lines. View entire code block.

    public function getFixScriptRows($appendCorrect = false, $lowerToUpper = true)
    {
        $names = $this->getDatabaseTableNames(true);
        $currentTables = $this->getCurrentTables();
        $rows = array();
        foreach ($names as $tbl) {
            $migrateTbl = $lowerToUpper ? strtolower($tbl) : $tbl;
            $tbl = !$lowerToUpper ? strtolower($tbl) : $tbl;
            if ($appendCorrect || ($key = array_search($migrateTbl, $currentTables)) !== false) {
                $tmpName = $migrateTbl . "_tmp";
                array_push($rows, "RENAME TABLE `" . $migrateTbl . "` TO `" . $tmpName . "`;");
                if ($appendCorrect || ($key2 = array_search($tbl, $currentTables)) !== false) {
                    array_push($rows, "DROP TABLE IF EXISTS `" . $tbl . "`;");
                    unset($currentTables[$key2]);
                }
                array_push($rows, "RENAME TABLE `" . $tmpName . "` TO `" . $tbl . "`;");
                unset($currentTables[$key]);
            }
        }
 
        return $rows;
    }

Type:	Discussion
Status:	New

concrete5 Environment Information

# concrete5 Version
Core Version - 8.5.15
Version Installed - 8.5.15
Database Version - 20220319043123

# Database Information
Version: 8.0.33
SQL Mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

# concrete5 Packages
Advanced Event List (0.0.2), Advanced HTML Block (0.9), Block Developer (1.1.1), Block Finder (0.9.2), Clear Clipboard (1.0.0), Custom Confirm Form (0.0.2), Database Migration (1.0.0), GDPR (1.8.2), Nestable Manual Nav (1.2.2), Open Graph Tags Lite (2.1.3), Page Redirect (2.0), Page Selector Attribute (2.0), Ruby Converter (0.1)

# concrete5 Overrides

# concrete5 Cache Settings
Block Cache - On
Overrides Cache - On
Full Page Caching - Off
Full Page Cache Lifetime - Every 6 hours (default setting).

# Server Software
nginx/1.25.4

# Server API
fpm-fcgi

# PHP Version
7.4.33

# PHP Extensions
bcmath, blackfire, bz2, calendar, cgi-fcgi, Core, ctype, curl, date, dba, dom, exif, FFI, fileinfo, filter, ftp, gd, gmp, hash, iconv, imagick, intl, json, libxml, mbstring, mysqli, mysqlnd, openssl, pcntl, pcre, PDO, pdo_mysql, pdo_pgsql, pdo_sqlite, pgsql, Phar, posix, readline, redis, Reflection, session, shmop, SimpleXML, soap, sockets, sodium, SPL, sqlite3, standard, sysvmsg, sysvsem, sysvshm, tokenizer, xml, xmlreader, xmlwriter, zip, zlib

# PHP Settings
max_execution_time - 30
log_errors_max_len - 1024
max_file_uploads - 20
max_input_nesting_level - 64
max_input_time - -1
max_input_vars - 1000
memory_limit - 128M
post_max_size - 8M
upload_max_filesize - 20M
mbstring.regex_retry_limit - 1000000
mbstring.regex_stack_limit - 100000
mysqli.max_links - Unlimited
mysqli.max_persistent - Unlimited
pcre.backtrack_limit - 1000000
pcre.recursion_limit - 100000
pgsql.max_links - Unlimited
pgsql.max_persistent - Unlimited
redis.pconnect.connection_limit - 0
session.cache_limiter - <i>no value</i>
session.gc_maxlifetime - 7200
soap.wsdl_cache_limit - 5
unserialize_max_depth - 4096

Browser User-Agent String

Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/122.0.0.0 Safari/537.36

Hide Post Content

This will replace the post content with the message: "Content has been removed by an Administrator"

Hide Content

Database Migration

Support

SQL error on MySQL8

Issue Reply

concrete5 Environment Information

Browser User-Agent String

Hide Post Content

Request Refund

Code

Delete Post