MySQL not writing to database

Question

I followed along with the Digitalocean LEMP stack tutorial and everything works perfectly. My app is running smoothly, but for some reason it can’t seem to write to my database. It reads and even updates the database without problems, but whenever I try to take any action that should write to the database it doesn’t work. It connects to the database, executes the query and returns the necessary data, but nothing has been written to the database.

I’ve followed the tutorial to the tee on Ubuntu 20.04. But then I also tried giving the MySQL user full privileges on the database, but still nothing. I’ve removed the user and added a new one, gave privileges, flushed privileges, and restarted MySQL too. Still nothing.

I thought I’d enable the MySQL error logging to see what errors pop up, but non of the tutorials I found worked.

Does anyone perhaps know what could be the issue here? And if not, is there a working/updated tutorial on Digitalocean that will help me to enable MySQL error logging so I can try and figure it out myself?

Bobby · Answer

Hello, I would recommend making sure that you’ve granted all privileges to the new user that you’ve created: GRANT ALL ON <^>example_database<^>.* TO '<^>example_user<^>'@'%'; If this still does not help, can you share an example of the problematic code snippet that you are using the insert data into the database? Regards,Bobby

KFSys · Answer

Here are the usual stuff to check when in such situations The issue you’re describing—successful reads but failed writes to the database—indicates a deeper issue that may not be directly related to MySQL permissions. Here’s a step-by-step guide to diagnose and resolve the issue: 1. Check MySQL User Permissions Even though you’ve already given full privileges to the user, confirm it explicitly using the following commands: SHOW GRANTS FOR 'your_user'@'localhost'; Ensure you see something like: GRANT ALL PRIVILEGES ON your_database.* TO 'your_user'@'localhost'; If you don’t see the necessary privileges, reapply them: GRANT ALL PRIVILEGES ON your_database.* TO 'your_user'@'localhost'; FLUSH PRIVILEGES; 2. Verify SQL Mode Certain SQL modes can prevent writes, such as STRICT_TRANS_TABLES. Check the current SQL mode: SHOW VARIABLES LIKE 'sql_mode'; If STRICT_TRANS_TABLES or similar is set, it may reject writes that violate schema constraints. You can modify it temporarily for testing: SET GLOBAL sql_mode = ''; To make it permanent, edit /etc/mysql/my.cnf or /etc/mysql/mysql.conf.d/mysqld.cnf and add: [mysqld] sql_mode="" Restart MySQL: sudo systemctl restart mysql 3. Check Autocommit and Transactions If your application uses transactions but does not commit them, writes will not persist. Test this by running a simple query in your application to explicitly commit a transaction: BEGIN; INSERT INTO your_table (column1, column2) VALUES ('value1', 'value2'); COMMIT; If this works, ensure your application is committing transactions where required. 4. Enable MySQL General Query Log To see all queries executed by your application, enable the general query log. This can help you identify the issue with the SQL queries. Temporary Method: SET GLOBAL general_log = 'ON'; SET GLOBAL general_log_file = '/var/log/mysql/general.log'; Permanent Method: Edit /etc/mysql/mysql.conf.d/mysqld.cnf: [mysqld] general_log = 1 general_log_file = /var/log/mysql/general.log Restart MySQL: sudo systemctl restart mysql Check the logs for write queries and errors: sudo tail -f /var/log/mysql/general.log 5. Enable MySQL Error Logging MySQL logs errors to the error log file by default. To ensure logging is enabled, check your MySQL configuration: Locate your error log file: sudo cat /etc/mysql/mysql.conf.d/mysqld.cnf | grep log_error It should point to a file, such as /var/log/mysql/error.log. If the file doesn’t exist, add the following to /etc/mysql/mysql.conf.d/mysqld.cnf: [mysqld] log_error = /var/log/mysql/error.log Restart MySQL: sudo systemctl restart mysql Monitor the error log for issues: sudo tail -f /var/log/mysql/error.log 6. Application Debugging Ensure your application is not silently failing. Common causes include: Validation rules in the application rejecting data before executing the query. Query failures due to constraints like foreign key relationships or unique indexes. Misconfigured database drivers. Enable debug mode in your application to capture detailed error messages: For PHP/Laravel: Set APP_DEBUG=true in .env.

Become a contributor for community

DigitalOcean Documentation

Resources for startups and AI-native businesses

The developer cloud

Start building today

Report this

MySQL not writing to database

Related questions

Mysql Data disappears intermittently

Droplet: Docker => laravel (mysql) + astro app [unusable]

docker laravel app with astro frontend

Can I create a managed MySQL cluster running a recent version?

how to connect digital ocean app platform app with cpanel mysql database?

What’s the best way to handle downtime during software deployments?

Where to Find Slow Query Logs in Managed Database (Already Enabled)?

Search index command in pimcore 10.5.15 production gives error.

Query taking 1 minute to run

Fullstack Architecture Deployments Recomendations

Mysql CPU alerts and max_execution_time

I can't install MySQL on the server

Report this

MySQL not writing to database

Related questions

Mysql Data disappears intermittently

Droplet: Docker => laravel (mysql) + astro app [unusable]

docker laravel app with astro frontend

Can I create a managed MySQL cluster running a recent version?

how to connect digital ocean app platform app with cpanel mysql database?

What’s the best way to handle downtime during software deployments?

Where to Find Slow Query Logs in Managed Database (Already Enabled)?

Search index command in pimcore 10.5.15 production gives error.

Query taking 1 minute to run

Fullstack Architecture Deployments Recomendations

Mysql CPU alerts and max_execution_time

I can't install MySQL on the server