If you did a fresh installation on a new Ubuntu (~18.04) version, you may find that it is not possible to log in to MySQL with its
root user, unless you do so as Ubuntu’s
root user. Here’s how to fix it: Continue reading “Fixing not being able to log in to fresh MySQL installation on Ubuntu”
If you use (You should!) MySQL Prepared Statements, you may not be able to see how the final MySQL queries looks like, within your application, with all the bindings put into place. This is how to view them: Continue reading “Enabling Global MySQL Log in Ubuntu”
You can use MySQL’s “ON DUPLICATE KEY UPDATE” to insert and update rows when your query faces a “Duplicate Key” error.
Today at work, I had to create a SQL query that’d insert some rows to a MySQL table. The rows are not user generated, they are dynamically created through several SQL queries. The results of the queries should be entered to a separate table, unless if they’re already inserted.
Imagine there’s a table called “library_lending”, which looks like the following.
CREATE TABLE `library_lending` (
`id` INT NOT NULL AUTO_INCREMENT ,
`user_name` VARCHAR(45) NOT NULL ,
`date` DATE NOT NULL ,
`book_name` VARCHAR(45) NOT NULL ,
PRIMARY KEY (`id`) ,
UNIQUE INDEX `user_date` (`user_name` ASC, `date` ASC) );
Please note that we have created a “Unique Index” (“Unique Key”) called “user_date” that combined both “user_name” and “date” fields. MySQL won’t allow inserting two rows that has same values for “user_name” and “date” fields. In other words, the above table is designed to keep records of people who’re borrowing books from a library, and this table won’t allow anyone to borrow more than one book in a day, because only one record can be inserted with same “user_name” and “date”. Continue reading “[Today I Learned] Insert and Update rows with MySQL “ON DUPLICATE KEY UPDATE””