MySQL Tips and Tricks
— mysql — 2 min read
Last updated : 06/July/2020
Events are in reverse chronological order.
- 2020/07/06 - Lock wait timeout exceeded
- 2020/06/11 - Creating test user with full access on test database
- 2020/06/11 - MySQL commonly used commands
- 2020/06/11 - Virtual columns
- 2020/06/11 - Viewing specfic range of output by using OFFSET
- 2020/06/11 - Output in vertical format
- 2020/04/26 - on duplicate update
- 2020/04/26 - Row change timestamp
- 2020/04/26 - Running MySQL script from windows prompt
- 2020/04/26 - Loading mysql tables
Lock wait timeout exceeded
Got the above error message, when executing an UPDATE statement in MySQL terminal. Upon a bit of analysis found that, a program was abnormally terminated and it had the thread still active, could see in
SHOW PROCESSLIST;. So after killing the thread, UPDATE statement was reexecuted and completed successfully in few seconds.
Creating test user with full access on test database
After creating the user, can be tested by opening a new command prompt,
For python, you may need to use
mysql_native_password, i have written a separate blog post for this.
MySQL commonly used commands
They appear to be normal columns but their values are calculated or derived from other columns and they are not materialized insense they are not stored in disk.
In my usecase, i find them extremely useful when dealing with JSON columns as JSON queries are tend to get lot bigger and complex. So, this virtal column greatly helps. Heres a sample,
Here is a simple JSON query retreving usb > -1
Lets add virtual column, to simplify the query,
Now all we have to do is
Viewing specfic range of output by using OFFSET
Using offset we can review a big result or table part by part in the terminal which doesn't have scrollbars
What below query does is,
- LIMIT 5 : Limits number of rows to be retreived to 5
- OFFSET 5: Retreive rows after skipping 5 rows from the resultset
Output in vertical format
Usually when we are retreiving data from table we expect to be in table format but at certain times, when we want to report or note it down, it will be better if its in 'vertial format'.
on duplicate update
VALUES(col_name) in the ON DUPLICATE KEY UPDATE clause refers to the value of col_name that would be inserted, had no duplicate-key conflict occurred
Above when it says, "2 rows affected", its not two rows, its just value 2, for example,
- if 0, had come, it means nothing is udpated
- 1 means row is inserted
- 2 means row is updated.
Its confusing a bit here but it is how it is.
Row change timestamp
Represents timestamp, when the row was last changed.
Running MySQL script from windows prompt
Below is the load card to truncate and load data into mySQL table
Below windows batch file to execute the above load control card,
FYI : Load card - Its a mainframe term meaning a member(equivalent to file in folder) containing load statement.
Loading mysql tables
Line number and description,
- Input file path, its Windows, so remember its a forward slash
IGNOREignores duplicates while loading into table
- Columns are terminated by pipe symbol(
|) rather than comma(
,) as unitName can contain commas.
- Ignore the header line
- Table Column names as they appear in the delimited file
Updating values while loading the table
SET clause can be used for this purpose, all MySQL functions can be used.