Fixing not being able to log in to fresh MySQL installation on Ubuntu

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”

[Today I Learned] Insert and Update rows with MySQL “ON DUPLICATE KEY UPDATE”

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””

WebIssues: Easier Issue Tracking

When we started developing software systems at Sanmark Solutions, we always felt the need to have a proper issue tracking system. So I read about, downloaded, installed and tested many of the well reputed issue tracking systems such as BugZilla, MantisBT and few others. But finally WebIssues was the solution! Continue reading “WebIssues: Easier Issue Tracking”