Question

How to add skeleton database to dedicated database using 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 sql_require_primary_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


Submit an answer

This textbox defaults to using Markdown to format your answer.

You can type !ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link!

Sign In or Sign Up to Answer

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.

Want to learn more? Join the DigitalOcean Community!

Join our DigitalOcean community of over a million developers for free! Get help and share knowledge in Q&A, subscribe to topics of interest, and get courses and tools that will help you grow as a developer and scale your project or business.

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.

👋 @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:

sql_require_primary_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.