Question

How to add skeleton database to dedicated database using app platform

Posted January 22, 2021 582 views
Initial Server SetupDigitalOcean Managed MySQL DatabaseDigitalOcean App Platform

Hi All,

I’m completely lost with this. Our app (php inhouse custom code)uses the composer library byjg/migration library so we use a skeleton mysql file (which contains empty tables and a few predefined values for some of the tables), the file for this was created by phpmyadmin on mysql8. However when this is triggered i get this error:

Fatal error: Uncaught PDOException: 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. in /workspace/vendor/byjg/anydataset-db/src/DbPdoDriver.php:206 Stack trace: #0 /workspace/vendor/byjg/anydataset-db/src/DbPdoDriver.php(206): PDOStatement->execute() #1 /workspace/vendor/byjg/migration/src/Database/MySqlDatabase.php(43): ByJG\AnyDataset\Db\DbPdoDriver->execute() #2 /workspace/vendor/byjg/migration/src/Migration.php(197): ByJG\DbMigration\Database\MySqlDatabase->createVersion() #3 /workspace/version/make.php(15): ByJG\DbMigration\Migration->reset() #4 {main} thrown in /workspace/vendor/byjg/anydataset-db/src/DbPdoDriver.php on line 206

I then looked into the issue and did an export of the skeleton database using the command line, this added the primary key element into the table definitions (instead of after them as the phpmyadmin output had done). I got the exact same error. I checked that all tables have a primary key and they all do. i used SET SESSION sqlrequireprimary_key = 1 (and also 0) and the same error.

I need to get something working so can see if the app platform is suitable for our app so i then thought I’ll try the command line, the file is on the server so can just do the sql command I can get from the dedicated database settings but i couldn’t work out how to get into this (console says sql not installed (and can’t install)

Lastly i tried to configure mysql workbench but couldn’t work out how to connect to the server.

Surely there must be some simple way we can import a mysql database skelton into the app so it can run, ideally the same skelton database structure and common data.

I have heard that laravel users have this exact same error when running artisan migrations but have not found any solution to this.

Has anyone got this to work?

thanks

These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.

×
Submit an Answer
2 answers

đź‘‹ @vivattido

This is a known issue between DigitalOcean and some PHP projects. There is a historical thread which I’ll link below that has several solutions which might work for you:

sqlrequireprimary_key Causes Tables With String Primary Key To Fail #33238

As far as connecting MySQL workbench, you can find the connection details in your DigitalOcean cloud dashboard on details. Make sure you use the public ones.

Hi crashoverride,

Thanks for the link, I had read this but didn’t pay to much attention as it seemed more specific to laravel, however looking again I noticed a few things to try that i haven’t yet so fingers crossed.

Let you know how it goes.

  • So I simplified my base.sql to use a single table with a view to adding each one as it’s own script using byjg/migration but again i get the same error (using SET SESSION sqlrequireprimary_key = 0; or 1) this is the sql I’m using in this test.

    BEGIN;
    -- MySQL dump 10.13  Distrib 8.0.22, for Linux (x86_64)
    --
    -- Host: localhost    Database: mydb
    -- ------------------------------------------------------
    -- Server version   8.0.22-0ubuntu0.20.04.3
    SET SESSION sql_require_primary_key = 0;
    /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
    /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
    /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
    /*!50503 SET NAMES utf8mb4 */;
    /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
    /*!40103 SET TIME_ZONE='+00:00' */;
    /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
    /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
    /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
    /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
    
    --
    -- Table structure for table `activity_logs`
    --
    
    DROP TABLE IF EXISTS `activity_logs`;
    /*!40101 SET @saved_cs_client     = @@character_set_client */;
    /*!50503 SET character_set_client = utf8mb4 */;
    CREATE TABLE `activity_logs` (
                                     `id` int NOT NULL AUTO_INCREMENT PRIMARY KEY,
                                     `user_id` int NOT NULL,
                                     `role` int NOT NULL,
                                     `company_id` int NOT NULL DEFAULT '0',
                                     `group_id` int NOT NULL,
                                     `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
                                     `href` varchar(255) NOT NULL,
                                     `href_id` int NOT NULL,
                                     `log_type` varchar(16) NOT NULL,
                                     `description` varchar(1023) NOT NULL,
                                     `is_active` int DEFAULT '1'
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
    /*!40101 SET character_set_client = @saved_cs_client */;
    
    
    /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
    
    /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
    /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
    /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
    /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
    /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
    /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
    /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
    
    -- Dump completed on 2021-01-21 16:01:33
    COMMIT;
    

    My thinking now is to add another droplet which is connected to the dedicated database and install phpmyadmin on it and using this. not what one would expect for an app platform but if it is the only way i can add the basic empty database then that is the only way…