Posts

The MySQL SQL Error 1064 Blues

Image
          Writing error messages is an art. Sadly, most of us lack talent in that area. One of the prime examples that you see with SQL User Interfaces is the MySQL SQL Error 1064. SQL Error [1064] [42000]: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select @var' at line 2 Error position: line: 1 Explicit and clear, it ain't.  I am using DBeaver for this example, but the issue can be reproduced on MySQL Workbench, Toad, and many other tools.  In the following, an environment variable is set on the first line, and then a SELECT is used to return that variable. However, we receive a response of 1064.  So what happened?  While both lines were input to the console, only the second line was executed. When we ask the server about @var, it has no knowledge of it and replies with the 1064 Error. "But we typed it in!" Yeah, we typed it in, but the set was not e...
Post a Comment
Read more

MySQL Entity Relationship Maps With DBeaver

Image
    Even the most experienced database professionals are known to feel a little anxious when peering into an unfamiliar database. Hopefully, they will inspect how the data is normalized and how the various tables are combined to answer complex queries.  Entity Relationship Maps (ERM) provide a visual overview of how tables are related and can document the structure of the data.     The Community Edition of  DBeaver  can easily provide an ERM. First, connect to the database. Right-click on 'Tables' and then select 'View Diagram'.      The ERM is displayed.   And it gets better! Are you not sure how two tables are joined? Click on the link between the tables, and the names of the columns you want to use in your JOIN statement are highlighted. Conclusion     Exploring an unfamiliar database can be daunting. Entity Relationship Maps provide a way to navigate the territory, and  DBeaver  is a fantastic tool for w...
Post a Comment
Read more

Database Administration Query Repository

 I have started a repository of handy SQL Queries for Database Administration, which I have collected over the years. Finding unused or duplicate indexes or queries without indexes and other information can speed up response times. Knowing statistics like cache hit rates can aid in planning upgrades.  I am currently populating this repo as I run across the queries as part of my 'spring cleaning. ' I welcome your participation if you have a handy bit of SQL for an open-source database you'd like to share. The repo is at https://github.com/davestokes/HandySQL , and I will be adding to it over the next few months. If you have a question that you would like to see addressed, please don't hesitate to let me know. I will try to accommodate you. 
Post a Comment
Read more

Does Artificial Intelligence Query MySQL Better Than You?

Image
 The signal-to-noise ratio in the hype for Artificial Intelligence is incredibly high. There are many claims that AI will replace software developers. Many years ago, developers were told to find other jobs as a new programming language was designed to be written by nonprogrammers, replacing expensive coders. The Common Business Orientated Language, or COBOL, was a big hit and is still very popular in some sectors. But it did not replace programmers. But can an AI write Structured Query Language (SQL) as well for use with MySQL, or better than you?  I decided to use the MySQL World and Sakila databases because they have been around for decades. Nearly everyone who has had tangential exposure to MySQL has used them, and their tables are easy to understand. These tried and true schemas have been used for documentation, blogs, webinars, and demos for years, so I had little doubt that some of that had been scraped and fed into the AI. I randomly picked Grok as the AI and will try ...
Post a Comment
Read more

Tracking MySQL Query Plans Over Time Part One

 Did you ever need to determine the performance of a query over time? One of the problems with database query optimizations is that the underlying data is constantly churning. Add in an increase in the number of users, expanding server demand use, and other items that impact your query. What was optimized is no longer performing as expected. But how do you tell if there is a change in the query plan or the query performance? Well, the first step is gathering the needed information. MySQL added a JSON format option to the output of the EXPLAIN command quite a while ago. More recently, Oracle added the feature of saving the output into a variable (see https://dev.mysql.com/doc/refman/9.0/en/mysql-nutshell.html and https://dev.mysql.com/blog-archive/explain-into-and-explain-for-schema-in-mysql-81-and-82 ). This provides a way to access the data in that variable and plop it into a database. The saved result can be compared to current or other past responses for analysis.  mysql...
Post a Comment
Read more