Recent content in Mysql on Die wunderbare Welt von Isotopp
Regional Indicator Symbols in Unicode are the codes starting at U+1F1E6 to U+1F1FF . If you combine two of them in a valid ISO-3166-1 alpha-2 codes, they produce the flag corresponding to that code. We want a function flag_emoji() that takes such a two-letter code and emits the appropriate Unicode codepoint: SELECT flag_emoji('GB') AS gb, flag_emoji('us') AS us, flag_emoji('de') AS de \G gb: 🇬🇧…
I’ve hacked together a horrible thing in Python, and made it available in mysql-release-notes on GitHub. It’s a Python project (done with uv ) that downloads all MySQL release notes, dumps them into a release_notes folder, and then parses them, pushing everything into a database. It uses SQLAlchemy and mysqlclient to connect to the database. It generates a schema (not preserving any data), and…
Sometimes, MySQL throws an error like this: Host ‘…’ is blocked because of many connection errors Unblock with ‘mysqladmin flush-hosts’ This typically means that MySQL has blocked a host after too many connection errors. The usual fix is to run: mysqladmin flush-hosts or mysql > FLUSH HOSTS ; /* deprecated since 8.0.23; invalid in 8.4 */ mysql > TRUNCATE TABLE…
Account password rotation is often a regulatory requirement. For that very reason, MySQL allows you to have multiple passwords on a single account. I have written about this in Rotating Accounts or Passwords? . Of course, if something is a regulatory requirement, you also need to prove that you are meeting it. So Percona has a nice article Tracking Dual Passwords in MySQL which shows how can (not)…
In IRC libera:#mysql somebody asked about the performance of SHOW TABLES LIKE 'name' in MySQL 8.0, which was slow for them, but fast in 5.7. They pointed to a forum article from 2022 that had a similar problem with a select -statement on I_S.TABLES . Both cases had in common a large number of tables in a single schema. The person in IRC had around 40k tables, the person in the forum article 500k…
Dear Kris, We have a number of old MySQL instances, version 5.5 and 5.6. We want to upgrade them to a current version of MySQL. The databases are between 0.5 TB to 8 TB in size. Unfortunately, using rsync on a stopped MySQL instance is not an option because the versions are too different, and the new version seems unable to read the binary data. I could use a dump (multi-threaded), but importing a…
In libera:#mysql a user has been running MySQL in a docker container with mysql:latest . This container got automatically upgraded to MySQL 9.0.0, an innovation release. Part of the 9.0 release is the removal of the mysql_native_password plugin, which has been deprecated since 8.0. The user now can no longer login to their database. They have no backup and no replica. We recreate the problem from…
A support question: Somebody had a WordPress installation, in which a table had entries with an id column that contained multiple entries with 0 . The table was supposed to undergo a schema change where id becomes an actual primary key, and auto_increment . They needed to find all rows WHERE id=0 and assign them unique id values. Here is a test table: mysql > create table b ( id integer not null ,…
When talking about storage performance, we often hear three terms that are important in describing storage performance. They are bandwidth latency I/O operations per second (IOPS) When you talk to storage vendors and cloud providers, they will gladly provide you with numbers on bandwidth and IOPS, but latency numbers will be hard to come by. To evaluate storage, especially for MySQL, you need just…
There is a really nice article by Pep Pla, over at the Percona blog about fragmentation in MySQL InnoDB tablespaces, which you should read. The article discusses “fragmentation” of data in tables, which happens in a way similar to how it happens in filesystems. InnoDB stores data by default in tablespaces, which by default are a file per table. These files are subject to the…
Back in October last year, I had been speaking to a few long-time friends at SeveralNines for a podcast. The recording is now out, and you can listen to it at this location or in the Podcast Player of your choice. Booking.com, Part 1 - Running data infrastructure at an Enterprise scale Booking.com, Part 2 - How Booking.com built their own Sovereign DBaaS at scale
Given a table named tbl with one million entries, we want to select a random row from this table, fast. Our table definition looks like this: create table tbl ( id INTEGER NOT NULL , d VARCHAR ( 200 ) NOT NULL , INDEX ( id ) ); Dense id space We can generate some test data using a recursive CTE: mysql > set cte_max_recursion_depth = 100000 ; mysql > insert into tbl -> with recursive c ( n , u ) as…
This text exists mainly so that I paste the URL into the #mysql channel in Libera IRC. The mysqldump tools allows you to convert a MySQL database server or individual schemas back to SQL. You are left with a script that is supposed to be loadable into a target server and gives you back the full database, including all objects in it. You can read that SQL as a script into an empty server to create…
For the last 15 years, one popular way to persist large amounts of data has been Hadoop, if you needed them persisted in such a way that you can still process them. Of course, from a database point of view, brute forcing a result by scanning compressed CSV files in parallel, and then building giant distributed Hash Joins is not very elegant. But two facts were true in 2006 influenced…
In Of Stars and Snowflakes we have been looking at the “normal Form” for Data Warehouses/BI structures, and how it differs from normal forms used in transactional systems. In ETL from a Django Model we looked at one implementation of a classical offline DWH with a daily load. The normal BI structure is a fact table, in which an object identifier (the one we collect facts about) is…
Change Data Capture is a way to capture, well, events from a system that describe how the data in the system changed. For a system that does business transactions that may be at the lowest level Create, Update, or Delete of entities or relationships. Systems that emit this kind of events are called Entity Services and are kind of the lowest level of events that you can have in such a system. In…
Continued from last weeks article on data warehouses. At work, I was tasked with building a capacity model for data center growth. The basic assumption of these things is often that the future behaves similarly to the past, so the future predicted capacity model is somehow an extension of past growth. I needed old server usage data, and was indeed able to find that in one of our systems, called…
A sample system When you have an Online Transactional Database, you have to record transactions at some point in time. That means you get a table with time dimension in your OLTP system. Consider for example a system that records Reservations. Users exist and can reserve Things to use, for a day. You probably get a structure such as this: In an OLTP database, a reservation is a (resid, userid,…
Where I work, we run bare-metal databases on non-redundant local storage. That is, a database is a very cheap frontend blade server. It has 2 CPUs, with 8 cores/16 threads each. It contains 128 GB of memory, 2 or 4 TB of local NVME and it has a 10 GBit/s network interface. It costs around 120 to 150 Euro per month to run for 5 years, including purchase price and all datacenter costs. It AWS terms,…
When asking for help in Libera Chat , in the #mysql channel, people will ask you to use the mysql command line client. They will also point you to dbfiddle.uk for asking questions. Specifically, when using phpMyAdmin, you will get hate. Why is that? When asking for help, it is almost impossible to help a GUI user, because they will need to paste screenshots in order to document what they did. The…