Planet MySQL

Planet MySQL - https://planet.mysql.com
  1. Dave's MySQL Quiz Number 2 Answers

        This week's MySQL was a great one for novices looking to level up from beginner level SQL development to a higher level.      The problem:  Find the customers with more than one unreturned rented movies that are past their return due date.  You might to look at this for the example of finding an overdue rentals. The answer:First we need to get the customer_id from the customer table.  Then it takes a bit of struggle to get the information on the rental.   It is often easier to write queries by determining the needed output columns, then the 'qualifiers'  or stuff on the right of the WHERE clause before determining what has to be joined to get between the two.The part of the query to find the overdue entries requires the rental date where it and the length of rental time are before the current date.  r.rental_date + INTERVAL f.rental_duration DAY < CURRENT_DATE()Finding the those customers with more than one rental needs to have the count of r.rental_id greater than 1. So besides the rental table, we will need the inventory table to tie between the rental table and the film table.  SQL > select c.customer_id,    sum(count(r.rental_id)) over (partition by c.customer_id) as'total',  r.rental_id, group_concat(f.title order by f.title) as 'titles' from rental r   join inventory i on (r.inventory_id=i.inventory_id)   join film f on i.film_id = f.film_id   join customer c on r.customer_id = c.customer_id   where r.return_date IS NULL   AND r.rental_date + INTERVAL f.rental_duration DAY < CURRENT_DATE() group by c.customer_id  having count(r.rental_id) >  1  order by c.customer_id;+-------------+-------+-----------+----------------------------------------+| customer_id | total | rental_id | titles                                 |+-------------+-------+-----------+----------------------------------------+|          15 |     2 |     13798 | CANDIDATE PERDITION,SMOKING BARBARELLA ||          42 |     2 |     13351 | RIVER OUTLAW,TORQUE BOUND              ||          43 |     2 |     15644 | MOTIONS DETAILS,POLLOCK DELIVERANCE    ||          53 |     2 |     11657 | LAWLESS VISION,PEACH INNOCENT          ||          60 |     2 |     12489 | BOOGIE AMELIE,CHAMBER ITALIAN          ||          75 |     3 |     13534 | LUST LOCK,SLEEPY JAPANESE,TROUBLE DATE ||         107 |     2 |     13079 | BLADE POLISH,CLUB GRAFFITI             ||         155 |     2 |     11496 | CHASING FIGHT,HYDE DOCTOR              ||         163 |     2 |     11754 | HOLES BRANNIGAN,SONS INTERVIEW         ||         175 |     2 |     13161 | DEER VIRGINIAN,PIRATES ROXANNE         ||         208 |     2 |     13719 | CURTAIN VIDEOTAPE,SEATTLE EXPECATIONS  ||         216 |     2 |     11676 | SWEDEN SHINING,WOMEN DORADO            ||         228 |     2 |     12672 | CYCLONE FAMILY,GRAPES FURY             ||         267 |     2 |     12066 | LUST LOCK,PHILADELPHIA WIFE            ||         269 |     2 |     12610 | PRINCESS GIANT,THEORY MERMAID          ||         284 |     2 |     12064 | BERETS AGENT,FRIDA SLIPPER             ||         354 |     2 |     11782 | TITANIC BOONDOCK,TROJAN TOMORROW       ||         361 |     2 |     13298 | HALF OUTFIELD,INSECTS STONE            ||         448 |     2 |     13577 | FAMILY SWEET,STATE WASTELAND           ||         457 |     2 |     12645 | CLEOPATRA DEVIL,GLEAMING JAWBREAKER    ||         516 |     2 |     12130 | FALCON VOLUME,MINORITY KISS            ||         560 |     2 |     12116 | MOVIE SHAKESPEARE,PIANIST OUTFIELD     ||         576 |     2 |     11942 | TITANIC BOONDOCK,VANISHED GARDEN       |+-------------+-------+-----------+----------------------------------------Bonus:  Add the customer nameselect c.customer_id,  concat(c.first_name, ' ', c.last_name) AS 'Customer Name',  sum(count(r.rental_id)) over (partition by c.customer_id) as 'tots',   r.rental_id, group_concat(f.title) from rental r  join inventory i on (r.inventory_id=i.inventory_id)  join film f on i.film_id = f.film_id  join customer c on r.customer_id = c.customer_id  where r.return_date IS NULL  AND r.rental_date + INTERVAL f.rental_duration DAY < CURRENT_DATE()  group by c.customer_id having count(r.rental_id) >  1 order by c.customer_id;All opinions expressed in this blog are those of Dave Stokes who is actually amazed to find anyone else agreeing with him
  2. Data Types in MySQL: Tutorial and Full List with Examples of Data Formats

    In the article, we are going to discuss data types including string, numeric, date and time, spatial, and JSON supported by MySQL. Also, we’ll provide examples of their usage and see how to change a data type for the table column using dbForge Studio for MySQL. Contents What is a Data Type Data Types in […] The post Data Types in MySQL: Tutorial and Full List with Examples of Data Formats appeared first on Devart Blog.
  3. Dave's MySQL Quiz #2

         This week's MySQL uses the Sakila database (details on how to get this data) and this week's quiz is a  great one for those wanting to move from beginner level SQL development to a higher level.  There will be lots of tables to joins.    The problem:  Find the customers with more than one unreturned rented movies that are past their return due date.  You might to look at this for the example of finding an overdue rentals.    You will need to display the customer's ID number, the number of overdue videos, and the names of the videos!  Bonus points for the customer name!An answer will be posted Monday.All opinions expressed in this blog are those of Dave Stokes who is actually amazed to find anyone else agreeing with him
  4. MySQL: Binding the ORM

    My task is to collect performance data about a single query, using PERFORMANCE_SCHEMA (P_S for short) in MySQL, to ship it elsewhere for integration with other data. In a grander scheme of things, I will need to define what performance data from a query I am actually interested in. I will also need to find a way to attribute the query (as seen on the server) to a point in the codebase of the client, which is not always easy when an ORM or other SQL generator is being used. And finally I will need to find a way to view the query execution in the context of the client code execution, because the data access is only a part of the system performance. This is about marking a query so that it can be identified in source and attributed to its origin in the codebase. In my scenario, I have control over the ORM or DAO. I can look at the stackframe, identify the caller of the execute function and put filename and line number or other identifying information into the generated query text. I could also get identifiers (“tracing ids”) from the caller and pass them on, so the query execution can be a child span of the ORM call that made the SQL and ran it. What will work? Comments are stripped from P_S Let’s try comments first. The manual knows three kinds of comments in MySQL: /*... */ C-like comments. #... Shell-like comments. --... Not quite SQL-like comments. With preserved comments I could put identifying information into the query string, and later extract it from P_S. I could use this information to attribute the query and link it to its origin in code. In one connection, I issue mysql [localhost:8025] {msandbox} (kris) > select /* keks */ * from t;m t; ... 8192 rows in set (0.00 sec) The result set is irrelevant, but I put a comment with /*... */ into the query string. In P_S, I find mysql [localhost:8025] {msandbox} (performance_schema) > select sql_text from events_statements_history where thread_id = 47 order by event_id desc limit 1; +------------------+ | sql_text | +------------------+ | select * from t | +------------------+ 1 row in set (0.00 sec) The comment has been stripped, you can still see the double spaces. Old fashioned SQL comments and shell comments seem to die already in the client: mysql [localhost:8025] {msandbox} (kris) > select -- keks -> * from t limit 3; ... 3 rows in set (0.00 sec) mysql [localhost:8025] {msandbox} (kris) > <cursor up> mysql [localhost:8025] {msandbox} (kris) > select * from t limit 3; The same happens with Shell comments: Of course, again, the comment is not in P_S. It is unclear why comments are stripped, and where. Maybe it is a vestige from the statement conditioning that was installed as a pre-stage to the late query cache, bless its rotten soul. There seem to be two mechanisms, one for SQL and Shell comments, and one for C comments: When looking at the client history, the Shell and SQL comments are not recalled by the editor, but the C comment is. This difference in treatment makes some sense, because MySQL uses magic C comments to control statement parsing for compatibility: SELECT /*! 80000 NEW_KEYWORD */ is parsed as a SELECT by MySQL before version 8.0.0 and as SELECT NEW_KEYWORD by MySQL 8.0.0 and higher. This allows mysqldump and other programs to emit SQL code that degrades gracefully on older versions of MySQL. Similar syntax, /*+...*/ is used to control optimizer hints. New: Query attributes Query Attributes are a newfangled thing (8.0.23 or newer) that allow a client to annotate a query. The annotations are preserved in the server and are being made available in some contexts, but they do nothing. The manual explains this: Attributes are defined prior to sending a statement. They exist until the statement ends. While they exist, they can be accessed on the server side. The examples given are exactly my use-case: Transporting identifying information from the client into the sevrer, or injecting control information for a plugin from the client into the server in order to affect query processing in the server. Query Attributes do nothing in the server. The server does not look at them. Query Attributes are supported by the C-API client and the MySQL command line client. Not much support exists elsewhere, yet. The basic exercise to check functions works: mysql [localhost:8025] {msandbox} (mysql) > INSTALL COMPONENT "file://component_query_attributes"; ... mysql [localhost:8025] {msandbox} (mysql) > query_attributes n1 v2 n2 v3 mysql [localhost:8025] {msandbox} (mysql) > select -> mysql_query_attribute_string('n1') AS 'attr 1', -> mysql_query_attribute_string('n2') AS 'attr 2', -> mysql_query_attribute_string('n3') AS 'attr 3'; +--------+--------+--------+ | attr 1 | attr 2 | attr 3 | +--------+--------+--------+ | v2 | v3 | NULL | +--------+--------+--------+ 1 row in set (0.00 sec) The plugin component is also visible in memory map of mysqld: kris@server:~$ grep query_attr /proc/94982/maps 7f7590f5b000-7f7590f5c000 r--p 00000000 fd:00 221893305 /home/kris/opt/mysql/8.0.25/lib/plugin/component_query_attributes.so 7f7590f5c000-7f7590f5d000 r-xp 00001000 fd:00 221893305 /home/kris/opt/mysql/8.0.25/lib/plugin/component_query_attributes.so 7f7590f5d000-7f7590f5e000 r--p 00002000 fd:00 221893305 /home/kris/opt/mysql/8.0.25/lib/plugin/component_query_attributes.so 7f7590f5e000-7f7590f5f000 r--p 00002000 fd:00 221893305 /home/kris/opt/mysql/8.0.25/lib/plugin/component_query_attributes.so 7f7590f5f000-7f7590f60000 rw-p 00003000 fd:00 221893305 /home/kris/opt/mysql/8.0.25/lib/plugin/component_query_attributes.so Where to go from here? I can generate a query in a client I control, and annotate the query with identifying information. In my case this information will be A trace flag. If the query is to be traced, the trace flag will be present. The detault is: The query will not be traced. A set of three identifiers (alphanumeric strings: sha256 MACs, UUIDs or strings representing integer numbers). They are a root id, a parent id and a query id. These identifiers allow be to model a span/parent span relationship in a larger tracing context. I need to find a hook in the server for a plugin. The plugin must run after query execution, but with the execution plan, the query string and the P_S data for the query still present. I am not yet familiar with this, and need to check the current server about what is on offer. Maybe the hook that the audit plugin uses can be repurposed. If the trace flag is set, it will need to access the query attributes the query plan that ran, if possible (Need to check what EXPLAIN FOR CONNECTION does) the information about the query execution that can be gathered from P_S data It needs to transform this information into a single serialized form, for example a JSON string, and then exfil this in a way that does not block the server. The generic way to do this in my environment has in the past been to send a UDP packet to localhost. UDP to localhost is considered non-lossy, limited to 64K and dropping the data if the listener is not present. A file write to an append-only file may also write, if there is a rotation/truncation mechanism. I will then need to take the JSON in my client, transform it some more and send it to a tracing consumer, eg Jaeger, Lightstep, or in my case, Honeycomb. The trace data will there be joined with spans from other components, including spans around the ORM that made the SQL and the code that called into the ORM. This will allow to view the context of the query without having to grep for it, use modern web tools to analyze query execution in the context that generated it and generally unify SQL debugging with other application debugging. This makes the need for specialized “Database Performance Monitoring” (DPM) software go away, at least for individual developers. A DPM can still be useful for operational tasks, but these are usually served by telegrams MySQL collector, Prometheus and Grafana just fine (and these usually scale better than a DPM).
  5. Repoint Replica Servers in MySQL/Percona Server for MySQL 8.0

    When doing migrations or failovers in MySQL, there is usually a need to do a topology change and repoint replica servers to obtain replication data from a different server. For example, given servers {A, B, and C} and the following topology: If you need to repoint C to be a replica of B, i.e: You can follow the next steps: Note: log_replica_updates should be enabled on the soon-to-be primary as it is a prerequisite for chain replication. Note: It is assumed that both replicas only stream from Server A and there are no conflicting replication filters in place that might break replication later on. If Using File/Position-Based Replication: 1) Stop B and C STOP REPLICA; 2) If replicas are multi-threaded, correct MTS gaps and make them single-threaded until all changes are applied. To do so, execute the following commands on BOTH nodes: START REPLICA UNTIL SQL_AFTER_MTS_GAPS; SHOW REPLICA STATUS\G -- repeat this until you see "Replica_SQL_Running: No" STOP REPLICA; SELECT @@global.replica_parallel_workers; -- take note to restore later SET GLOBAL replica_parallel_workers=0; -- disable MTS during the operations 3) Then check which is the node that is more up to date by looking at Relay_Source_Log_File and Exec_Source_Log_Pos. Run on BOTH nodes: SHOW REPLICA STATUS\G # Take note of Relay_Source_Log_File/Exec_Source_Log_Pos from the most up to date node. 4) Sync replicas with UNTIL. Run on the most delayed node with above outputs: START REPLICA UNTIL SOURCE_LOG_FILE='<Relay_Source_Log_File>', SOURCE_LOG_POS=<Exec_Source_Log_Pos>; SHOW REPLICA STATUS\G -- repeat this until you see "Replica_SQL_Running: No" 5) If followed above steps, at this point both replicas should have the exact same data set and should be in sync at the same point in time. # Double check that both replicas are stopped and with the same coords as doing topology changes while replication is ongoing and with diffs coords can cause inconsistencies: SHOW REPLICA STATUS\G # Replica_IO_Running must be “NO” in both replicas # Replica_SQL_Running must be “NO” in both replicas # Relay_Source_Log_File must match in both replicas # Exec_Source_Log_Pos must match in both replicas 6) Get current coordinates from B (new intermediate primary). Execute on B: SHOW MASTER STATUS \G # Take note of File and Position 7) Repoint C to B. Execute on C with coords from previous step: CHANGE REPLICATION SOURCE TO SOURCE_HOST='<ip-address-of-B>', SOURCE_LOG_FILE='<File>', SOURCE_LOG_POS='<Position>'; 8) If you had disabled MTS, you should re-enable here for both B and C; SET GLOBAL replica_parallel_workers=X; -- see output of step 2 for correct value 9) Restart replication normally. Run on both nodes: START REPLICA; If Using GTID-Based Replication: 1) Stop B and C: STOP REPLICA; 2) If replicas are multi-threaded, correct MTS gaps and make them single-threaded until all changes are applied. Run on BOTH nodes: SHOW REPLICA STATUS\G -- repeat this until you see "Replica_SQL_Running: No" STOP REPLICA; SELECT @@global.replica_parallel_workers; -- take note to restore later SET GLOBAL replica_parallel_workers=0; -- disable MTS during the operations 3) Then check which is the node that is more up to date by looking at sequence numbers in Executed_Gtid_Set. Run on BOTH nodes: ​ SHOW REPLICA STATUS\G # Take note of Executed_Gtid_Set with the largest sequence number. If there is a mismatch in the gtid sets it means there were either local writes or writes coming from some other server. In that case you should check data consistency between the servers, for example with pt-table-checksum . Then you need to fix gtid differences by either restoring the replica from scratch or fix errant transactions as explained on this other blogpost 4) Bring up all nodes to the same point in time. Run on node with smallest GTID sequence number; START REPLICA UNTIL SQL_AFTER_GTIDS='<Executed_Gtid_Set>'; SHOW REPLICA STATUS\G -- repeat this until you see "Replica_SQL_Running: No" 5) If followed above steps, at this point both replicas should have the exact same data set and should be in sync at the same point in time. # Double check that both replicas are stopped and with the same coords as doing topology changes while replication is ongoing and with diffs coords can cause inconsistencies: SHOW REPLICA STATUS\G # Replica_IO_Running must be “NO” in both replicas # Replica_SQL_Running must be “NO” in both replicas # Executed_Gtid_Set must match in both replicas 6) Now both replicas have identical data, so you can re-point C to replicate from B. Run on C: CHANGE REPLICATION SOURCE TO SOURCE_HOST='<ip-address-of-B>' 7) If you had disabled MTS, you should re-enable here for both B and C; SET GLOBAL replica_parallel_workers=X; -- see output of step 2 for correct value 8) Restart replication normally. Run on both nodes START REPLICA; Doing the opposite replication change from chain replication (A->B->C) into one primary with two replicas should be simpler: If Using File/Position-Based Replication: 1) Stop replication on B and make sure B is not receiving any write activity: STOP REPLICA; 2) Check current binary log position on B: SHOW MASTER STATUS \G 3) On C check replication until C does catch up with B. On C: SHOW REPLICA STATUS \G # For C to have catch up with B, the following conditions should be met: # “File” from B on step 2) should match Relay_Source_Log_File from 3) # “Position” from B on step2) should match Exec_Source_Log_Pos from 3) # After catchup, both servers will be in sync with the same data set. 4) Check current replication coords from B: SHOW REPLICA STATUS \G # Write down Relay_Source_Log_File and Exec_Source_Log_Pos from B, as we will be using this coords on C 5) Re point C to replicate from A. File and positions used should be the ones taken from B on last step: CHANGE REPLICATION SOURCE TO SOURCE_HOST='<ip-address-of-A>', SOURCE_LOG_FILE='<File>', SOURCE_LOG_POS='<Position>' 6) Restart replication normally. Run on both nodes: START REPLICA; If Using GTID-Based Replication: 1) Stop replication on B and make sure B is not receiving any write activity: STOP REPLICA; 2) Check current binary log position on B: SHOW MASTER STATUS \G 3) On C check replication until C does catch up with B. On C: SHOW REPLICA STATUS \G # For C to have catch up with B, the following conditions should be met: # Executed_Gtid_Set from B step 2) should match Executed_Gtid_Set from 3) # After catchup, both servers will be in sync with the same data set. 4) Re point C to replicate from A: CHANGE REPLICATION SOURCE TO SOURCE_HOST='<ip-address-of-A>' 5) Restart replication normally. Run on both nodes START REPLICA; Conclusion: Doing topology changes might seem hard at first, but with the above procedure, it should be easy and error-free! If you do not want to do the manual approach, then you can consider using tools like Orchestrator which allows for automatic failover and promotions. Percona Distribution for MySQL is the most complete, stable, scalable, and secure, open-source MySQL solution available, delivering enterprise-grade database environments for your most critical business applications… and it’s free to use! Download Percona Distribution for MySQL Today

Upcoming Events

Visitors

We have 16 guests and no members online