www.bortolotto.eu

Newsfeeds
Planet MySQL
Planet MySQL - https://planet.mysql.com

  • MySQL Connection Status with MySQL Shell
    MySQL Shell offer many features to make life easier for DBAs and developers. In this post we discuss how to view the details of the connection to a MySQL instance.

  • The curse of MySQL warnings
    MySQL warnings are an anti-pattern when it comes to maintaining data integrity. When the information retrieved from a database does not match what was entered, and this is not identified immediately, this can be permanently lost. MySQL by default for several decades until the most recent versions enabled you to insert incorrect data, or insert data that was then truncated, or other patterns that resulted in failed data integrity. Very few applications considered handling warnings as errors, and there is a generation of software products that have never informed the developers that warnings were occurring. The most simplest example is: CREATE SCHEMA IF NOT EXISTS warnings; USE warnings; CREATE TABLE short_name( id INT UNSIGNED NOT NULL AUTO_INCREMENT, name VARCHAR(20) NOT NULL, PRIMARY KEY(id) ); INSERT INTO short_name (name) VALUES ('This name is too long and will get truncated'); ERROR 1406 (22001): Data too long for column 'name' at row 1 This is what you expect would happen. In many, many applications IT DOES NOT. For almost 20 years the default setting was to support possible data corruption If you used an older version without setting up a more strict SQL_MODE from the default you end up with. INSERT INTO short_name (name) VALUES ('This Name is too long and will get truncated'); Query OK, 1 row affected, 1 warning (0.00 sec) SELECT * FROM short_name; +----+----------------------+ | id | name                 | +----+----------------------+ |  1 | This name is too lon | +----+----------------------+ 1 row in set (0.00 sec) Only if you run SHOW WARNINGS and after the actual SQL statement would you know? There is no other way to find this information in any logs. There is no way to mysql> SHOW WARNINGS; +---------+------+-------------------------------------------+ | Level | Code | Message | +---------+------+-------------------------------------------+ | Warning | 1265 | Data truncated for column 'name' at row 1 | +---------+------+-------------------------------------------+ 1 row in set (0.00 sec) Numerous other examples can shock a customer when, after some time, expected data in a production is lost and unretrievable. If you came from a more strict RDBMS background, or you tuned your MySQL installation or uncovered this and many other poor defaults, you would have improved your data integrity with and improved SQL_MODE. So MySQL warnings are bad? No, they are ideal when used appropriately. However, the next critical dilemma occurs. Warnings are valuable when used to identify important characteristics of an SQL statement that a developer or database administrator should be aware of. However, the only way to retrieve these warnings is from the application making the connection to the database at each statement, and generally, these warnings are just lost. Here are some examples of warnings that are important for the engineering team that define criteria such as deprecation notices, which are important for production database upgrades. SELECT JSON_MERGE('["a"]','["b"]'); Warning (Code 1287): 'JSON_MERGE' is deprecated and will be removed in a future release. Please use JSON_MERGE_PRESERVE/JSON_MERGE_PATCH instead SELECT ST_GeomFromWKB(Point(0, 0)); Warning: (3195) st_geometryfromwkb(geometry) is deprecated and will be replaced by st_srid(geometry, 0) in a future version. Use st_geometryfromwkb(st_aswkb(geometry), 0) instead. SELECT DATE('2024-01-01 10:00:00') Warning (Code 4096): Delimiter ' ' in position 11 in datetime value '2024-01-01 10:00:00' at row 1 is superfluous and is deprecated. Please remove. SELECT BINARY 'a' = 'A' Warning (Code 1287): 'BINARY expr' is deprecated and will be removed in a future release. Please use CAST instead You definitely want to know about these, collect them (hard), add them to your backlog, and don’t leave it until its too late in the I can’t upgrade my database to have to address. If you want to know about these, collect them (hard), add them to your backlog, and don’t leave it until it’s too late for a critical last-minute upgrade to my database to have to address. There are also warnings that should be collected and used for performance verification, which apply to running systems. I wanted to show one specific example uncovered during testing of a MySQL upgrade to version 8.0. Warning (Code 3170): Memory capacity of 8388608 bytes for 'range_optimizer_max_mem_size' exceeded. Range optimization was not done for this query. In fact, this warning occurs in MySQL 5.7, but the customer never knew because they did not look at the warnings. How many other SQL statements in your application produce warnings now? How can you find this out? It was rather easy to create a reproducible test case but what now? Do you set range_optimizer_max_mem_size=0 Do you set to the value you need, which you can identify with SELECT * FROM performance_schema.memory_summary_by_thread_by_event_name WHERE thread_id=PS_CURRENT_THREAD_ID() AND event_name='memory/sql/test_quick_select'\G Do you need to modify your optimizer_switch settings? Do you try something else? Do you refactor your application? Do you just leave it as is? When you want to consider several different options, which one works best for this query? What about the impact on your entire production workload? Knowing statistically which is the best choice for your full workload and under various conditions is the optimal output, but how? Next BaseLine was built to perform experiments comparing changes to your data, configuration, and infrastructure to validate the next version of your product statistically performs better than your current version across all of your application at different workloads. Next BaseLine also provides numerous benefits for a major database upgrade, so I’ve focussed on getting these capabilities to customers quicker to save money. It provides the benefit of detecting SQL statements that produce errors in the next MySQL version, enabling you to categorize and prioritize areas of your application that must be corrected. It also captures important information about the performance and quality of the data from your MySQL queries; this also can help in identifying the most critical aspects of your application to invest engineering time and mitigate risk in your database upgrade plan. It can also collect warning messages such as these discussed when considering migrating from MySQL 5.7 to MySQL 8, or it can just find them with your current application. What is your pain point with MySQL database upgrades? What are you doing right now to help reduce this additional budget spend? Join our private beta program now to find out more. Next BaseLine Helping to create a better and faster next version of your data-driven product

  • Is Oracle Finally Killing MySQL?
    Some 15 years ago, when Oracle acquired Sun, and hence MySQL, there was a lot of talk on the internet over when Oracle was going to “Kill MySQL”.  Multiple theories were circled around, ranging from killing MySQL altogether, so there is less competition to proprietary Oracle, or just killing it as an open source project, […]

  • GCache and Record-Set Cache Encryption in Percona XtraDB Cluster – Part One
    Keeping Your Data Safe: An Introduction to Data-at-Rest Encryption in Percona XtraDB Cluster. Data breaches are a major concern today. Whether you’re a business owner or value your privacy, the thought of your sensitive information being exposed is unsettling. Fortunately, data-at-rest encryption can help. Think of it as a sophisticated lock that scrambles your data, […]

  • How to Install LOMP Stack (OpenLiteSpeed, MySQL, and PHP) on Ubuntu 24.04
    OpenLiteSpeed is a lightweight and open-source version of the LiteSpeed Server developed by LiteSpeed Technologies. It supports Apache Rewrite rules, HTTP/2 and HTTP/3, and TLS v1.3 and QUIC protocols. This tutorial will show you how to install an OpenLiteSpeed Server on Ubuntu 24.04.