Tag Archives: mysql

Understanding dates and time in MySQL

TL;DR: Set your server timezone and connection time zone to both ‘+00:00’ and use datetime instead of timestamp when you want to store dates outside the range supported by timestamp (‘1970-01-01 00:00:01.000000’ to ‘2038-01-19 03:14:07.999999′). Do’s when using datetime: Add a suffix … Continue reading

Posted in Software | Tagged | Leave a comment

MySQL Foreign Keys

I used to think that the foreign key column has to be unique [ref] but that is not the case. Consider the tables below where we have a parent -> child -> grandchild relationship. A competition has teams and a … Continue reading

Posted in Software | Tagged | Leave a comment

Transactions and Locks in MySQL

Refer: https://stackoverflow.com/questions/61212438/why-do-we-need-to-lock-a-mysql-table-during-select-when-beginning-a-transaction/ TL;DR: MySQL docs are wrong Transactions act like a try-catch block. They don’t provide protection against concurrency. If something fails in a transaction, it can be rolled back to undo pending operations that were not committed. To handle … Continue reading

Posted in Software | Tagged | Leave a comment