Database error when installing the Upper Plugin

[paragonn]

create table {{%upper_cache}} ...Exception: SQLSTATE[HY000]: General error: 3750 Unable to create or change a table without a primary key, when the system variable 'sql_require_primary_key' is set. Add a primary key to the table or unset this variable to avoid this message. Note that tables without a primary key can cause performance problems in row-based replication, so please consult your DBA before changing this setting.
The SQL being executed was: CREATE TABLE upper_cache (
uid varchar(40) NOT NULL UNIQUE,
url text NOT NULL,
urlHash varchar(32) NOT NULL,
headers text NULL DEFAULT NULL,
tags text NOT NULL,
siteId int(11),
dateCreated datetime(0) NOT NULL,
dateUpdated datetime(0) NULL DEFAULT NULL
) ENGINE = InnoDb DEFAULT CHARACTER SET = utf8 DEFAULT COLLATE = utf8_unicode_ci (/vendor/yiisoft/yii2/db/Schema.php:678)

[ostark]

Hi @paragonn

thanks for your report! Please provide more details about your environment, like DB version.

[paragonn]

Sure thing:
Mysql 8
Digital Ocean - Managed Databases

I know that Digital Ocean's Managed Databases do no like tables without a primary key. Has something to do with replication.

[elfacht]

Having the same issue with MySQL 5.7.35 and Craft 3.7.30.1. Always have to delete the table by hand to install it.

> create table {{%upper_cache}} ...Exception: SQLSTATE[42S01]: Base table or view already exists: 1050 Table 'pcms_upper_cache' already exists
The SQL being executed was: CREATE TABLE `pcms_upper_cache` (
	`uid` varchar(40) NOT NULL UNIQUE,
	`url` text NOT NULL,
	`urlHash` varchar(32) NOT NULL,
	`headers` text NULL DEFAULT NULL,
	`tags` text NOT NULL,
	`siteId` int(11),
	`dateCreated` datetime(0) NOT NULL,
	`dateUpdated` datetime(0) NULL DEFAULT NULL
) ENGINE = InnoDb DEFAULT CHARACTER SET = utf8 (/src/cms/vendor/yiisoft/yii2/db/Schema.php:676)
#0 /src/cms/vendor/yiisoft/yii2/db/Command.php(1307): yii\db\Schema->convertException(Object(PDOException), 'CREATE TABLE `p...')
#1 /src/cms/vendor/yiisoft/yii2/db/Command.php(1102): yii\db\Command->internalExecute('CREATE TABLE `p...')
#2 /src/cms/vendor/yiisoft/yii2/db/Migration.php(323): yii\db\Command->execute()
#3 /src/cms/vendor/ostark/upper/src/migrations/Install.php(29): yii\db\Migration->createTable('{{%upper_cache}...', Array)
#4 /src/cms/vendor/craftcms/cms/src/db/Migration.php(52): ostark\upper\migrations\Install->safeUp()
#5 /src/cms/vendor/craftcms/cms/src/db/MigrationManager.php(232): craft\db\Migration->up(true)
#6 /src/cms/vendor/craftcms/cms/src/base/Plugin.php(140): craft\db\MigrationManager->migrateUp(Object(ostark\upper\migrations\Install))
#7 /src/cms/vendor/craftcms/cms/src/services/Plugins.php(535): craft\base\Plugin->install()
#8 /src/cms/vendor/craftcms/cms/src/console/controllers/PluginController.php(80): craft\services\Plugins->installPlugin('upper')
#9 [internal function]: craft\console\controllers\PluginController->actionInstall('upper')
#10 /src/cms/vendor/yiisoft/yii2/base/InlineAction.php(57): call_user_func_array(Array, Array)
#11 /src/cms/vendor/yiisoft/yii2/base/Controller.php(178): yii\base\InlineAction->runWithParams(Array)
#12 /src/cms/vendor/yiisoft/yii2/console/Controller.php(182): yii\base\Controller->runAction('install', Array)
#13 /src/cms/vendor/craftcms/cms/src/console/Controller.php(221): yii\console\Controller->runAction('install', Array)
#14 /src/cms/vendor/yiisoft/yii2/base/Module.php(552): craft\console\Controller->runAction('install', Array)
#15 /src/cms/vendor/yiisoft/yii2/console/Application.php(180): yii\base\Module->runAction('plugin/install', Array)
#16 /src/cms/vendor/craftcms/cms/src/console/Application.php(89): yii\console\Application->runAction('plugin/install', Array)
#17 /src/cms/vendor/yiisoft/yii2/console/Application.php(147): craft\console\Application->runAction('plugin/install', Array)
#18 /src/cms/vendor/yiisoft/yii2/base/Application.php(384): yii\console\Application->handleRequest(Object(craft\console\Request))
#19 /src/cms/craft(22): yii\base\Application->run()
#20 {main}

[L3P3]

Having the same issue with MySQL 5.7.35 and Craft 3.7.30.1. Always have to delete the table by hand to install it.

> create table {{%upper_cache}} ...Exception: SQLSTATE[42S01]: Base table or view already exists: 1050 Table 'pcms_upper_cache' already exists
The SQL being executed was: CREATE TABLE `pcms_upper_cache` (
	`uid` varchar(40) NOT NULL UNIQUE,
	`url` text NOT NULL,
	`urlHash` varchar(32) NOT NULL,
	`headers` text NULL DEFAULT NULL,
	`tags` text NOT NULL,
	`siteId` int(11),
	`dateCreated` datetime(0) NOT NULL,
	`dateUpdated` datetime(0) NULL DEFAULT NULL
) ENGINE = InnoDb DEFAULT CHARACTER SET = utf8 (/src/cms/vendor/yiisoft/yii2/db/Schema.php:676)
#0 /src/cms/vendor/yiisoft/yii2/db/Command.php(1307): yii\db\Schema->convertException(Object(PDOException), 'CREATE TABLE `p...')
#1 /src/cms/vendor/yiisoft/yii2/db/Command.php(1102): yii\db\Command->internalExecute('CREATE TABLE `p...')
#2 /src/cms/vendor/yiisoft/yii2/db/Migration.php(323): yii\db\Command->execute()
#3 /src/cms/vendor/ostark/upper/src/migrations/Install.php(29): yii\db\Migration->createTable('{{%upper_cache}...', Array)
#4 /src/cms/vendor/craftcms/cms/src/db/Migration.php(52): ostark\upper\migrations\Install->safeUp()
#5 /src/cms/vendor/craftcms/cms/src/db/MigrationManager.php(232): craft\db\Migration->up(true)
#6 /src/cms/vendor/craftcms/cms/src/base/Plugin.php(140): craft\db\MigrationManager->migrateUp(Object(ostark\upper\migrations\Install))
#7 /src/cms/vendor/craftcms/cms/src/services/Plugins.php(535): craft\base\Plugin->install()
#8 /src/cms/vendor/craftcms/cms/src/console/controllers/PluginController.php(80): craft\services\Plugins->installPlugin('upper')
#9 [internal function]: craft\console\controllers\PluginController->actionInstall('upper')
#10 /src/cms/vendor/yiisoft/yii2/base/InlineAction.php(57): call_user_func_array(Array, Array)
#11 /src/cms/vendor/yiisoft/yii2/base/Controller.php(178): yii\base\InlineAction->runWithParams(Array)
#12 /src/cms/vendor/yiisoft/yii2/console/Controller.php(182): yii\base\Controller->runAction('install', Array)
#13 /src/cms/vendor/craftcms/cms/src/console/Controller.php(221): yii\console\Controller->runAction('install', Array)
#14 /src/cms/vendor/yiisoft/yii2/base/Module.php(552): craft\console\Controller->runAction('install', Array)
#15 /src/cms/vendor/yiisoft/yii2/console/Application.php(180): yii\base\Module->runAction('plugin/install', Array)
#16 /src/cms/vendor/craftcms/cms/src/console/Application.php(89): yii\console\Application->runAction('plugin/install', Array)
#17 /src/cms/vendor/yiisoft/yii2/console/Application.php(147): craft\console\Application->runAction('plugin/install', Array)
#18 /src/cms/vendor/yiisoft/yii2/base/Application.php(384): yii\console\Application->handleRequest(Object(craft\console\Request))
#19 /src/cms/craft(22): yii\base\Application->run()
#20 {main}

I am sure this is not the same issue. In our case, the table already exists. It is not about primary key or similar.

[ostark]

Thanks @L3P3

[L3P3]

I think this issue here is not related with my fix. You should better leave it open. Or have you looked into it a bit more?

[ostark]

@L3P3
Oops, you are right!
It does not solve the problem with the missing PK - I just had a look at the most recent comments.
I think all uid needs is a PRIMARY KEY constraint instead of just UNIQUE.