MySQL

As many know, I’ve adopted Learning SQL by Alan Beaulieu as a core reference for my database class. Chapter 7 in the book focuses on data generation, manipulation, and conversion. The last exercise question in my check of whether they read the ... [More] chapter and played with some of the discussed functions is: Use one or more temporal function to write a query that convert the ’29-FEB-2024′ string value into a default MySQL date format. The result should display: +--------------------+ | mysql_default_date | +--------------------+ | 2029-02-20 | +--------------------+ 1 row in set, 1 warning (0.00 sec) If you’re not familiar with the behavior of MySQL functions, this could look like a difficult problem to solve. If you’re risk inclined you would probably try the STR_TO_DATE function but if you’re not risk inclined the description of the %m specifier might suggest you don’t have SQL builtin to solve the problem. I use the problem to teach the students how to solve problems in SQL queries. The first step requires putting the base ’29-FEB-2024′ string value into a mystringcolumn in the strings table, like: DROP TABLE IF EXISTS strings; CREATE TABLE strings (mystring VARCHAR(11)); SELECT 'Insert' AS statement; INSERT INTO strings (mystring) VALUES ('29-FEB-2024'); The next step requires creating a query with: A list of parameters in a Common Table Expression (CTE) A CASE statement to filter results in the SELECT-list A CROSS JOIN between the strings table and params CTE The query would look like this resolves the comparison in the CASE statement through a case insensitive comparison: SELECT 'Query' AS statement; WITH params AS (SELECT 'January' AS full_month UNION ALL SELECT 'February' AS full_month) SELECT s.mystring , p.full_month , CASE WHEN SUBSTR(s.mystring,4,3) = SUBSTR(p.full_month,1,3) THEN STR_TO_DATE(REPLACE(s.mystring,SUBSTR(s.mystring,4,3),p.full_month),'%d-%M-%Y') END AS converted_date FROM strings s CROSS JOIN params p; and return: +-------------+------------+----------------+ | mystring | full_month | converted_date | +-------------+------------+----------------+ | 29-FEB-2024 | January | NULL | | 29-FEB-2024 | February | 2024-02-29 | +-------------+------------+----------------+ 2 rows in set (0.00 sec) The problem with the result set, or derived table, is the CROSS JOIN. A CROSS JOIN matches every row in one table with every row in another table or derived table from prior joins. That means you need to add a filter in the WHERE clause to ensure you only get matches between the strings and parameters, like the modified query: WITH params AS (SELECT 'January' AS full_month UNION ALL SELECT 'February' AS full_month) SELECT s.mystring , p.full_month , CASE WHEN SUBSTR(s.mystring,4,3) = SUBSTR(p.full_month,1,3) THEN STR_TO_DATE(REPLACE(s.mystring,SUBSTR(s.mystring,4,3),p.full_month),'%d-%M-%Y') END AS converted_date FROM strings s CROSS JOIN params p WHERE SUBSTR(s.mystring,4,3) = SUBSTR(p.full_month,1,3); It returns a single row, like: +-------------+------------+----------------+ | mystring | full_month | converted_date | +-------------+------------+----------------+ | 29-FEB-2024 | February | 2024-02-29 | +-------------+------------+----------------+ 1 row in set (0.00 sec) However, none of this is necessary because the query can be written like this: SELECT STR_TO_DATE('29-FEB-2024','%d-%M-%Y') AS mysql_date; It returns: +------------+ | mysql_date | +------------+ | 2024-02-29 | +------------+ 1 row in set (0.00 sec) That’s because the STR_TO_DATE() function with the %M specifier resolves all months with three or more characters. Three characters are required because both Mar and May, and June and July can only be qualified by three characters. If you provide less than three characters of the month, the function returns a null value. Here’s a complete test case that lets you discover all the null values that may occur with two few characters: /* Conditionally drop the table. */ DROP TABLE IF EXISTS month, param; /* Create a table. */ CREATE TABLE month ( month_name VARCHAR(9)); /* Insert into the month table. */ INSERT INTO month ( month_name ) VALUES ('January') ,('February') ,('March') ,('April') ,('May') ,('June') ,('July') ,('August') ,('September') ,('October') ,('November') ,('December'); /* Create a table. */ CREATE TABLE param ( month VARCHAR(9) , needle VARCHAR(9)); /* Conditionally drop the procedure. */ DROP PROCEDURE IF EXISTS read_string; DROP PROCEDURE IF EXISTS test_month_name; /* Reset the execution delimiter to create a stored program. */ DELIMITER $$ /* Create a procedure. */ CREATE PROCEDURE read_string(month_name VARCHAR(9)) BEGIN /* Declare a handler variable. */ DECLARE display VARCHAR(17); DECLARE evaluate VARCHAR(17); DECLARE iterator INT DEFAULT 1; DECLARE partial VARCHAR(9); /* Read the list of characters. */ character_loop:LOOP /* Print the character list. */ IF iterator > LENGTH(month_name) THEN LEAVE character_loop; END IF; /* Assign substring of month name. */ SELECT SUBSTR(month_name,1,iterator) INTO partial; SELECT CONCAT('01-',partial,'-2024') INTO evaluate; /* Print only the strings too short to identify as the month. */ IF STR_TO_DATE(evaluate,'%d-%M-%Y') IS NULL THEN INSERT INTO param ( month, needle ) VALUES ( month_name, partial ); END IF; /* Increment the counter. */ SET iterator = iterator + 1; END LOOP; END; $$ /* Create a procedure. */ CREATE PROCEDURE test_month_name() BEGIN /* Declare a handler variable. */ DECLARE display VARCHAR(17); DECLARE evaluate VARCHAR(17); DECLARE iterator INT DEFAULT 1; DECLARE partial VARCHAR(9); DECLARE month_name VARCHAR(9); /* Declare a handler variable. */ DECLARE fetched INT DEFAULT 0; /* Cursors must come after variables and before event handlers. */ DECLARE month_cursor CURSOR FOR SELECT m.month_name FROM month m; /* Declare a not found record handler to close a cursor loop. */ DECLARE CONTINUE HANDLER FOR NOT FOUND SET fetched = 1; /* Open cursor and start simple loop. */ OPEN month_cursor; cursor_loop:LOOP /* Fetch a record from the cursor. */ FETCH month_cursor INTO month_name; /* Place the catch handler for no more rows found immediately after the fetch operations. */ IF fetched = 1 THEN /* Fetch the partial strings that fail to find a month. */ SELECT * FROM param; /* Leave the loop. */ LEAVE cursor_loop; END IF; /* Call the subfunction because stored procedures do not support nested loops. */ CALL read_string(month_name); END LOOP; END; $$ /* Reset the delimter. */ DELIMITER ; CALL test_month_name(); It returns the list of character fragments that fail to resolve English months: +---------+--------+ | month | needle | +---------+--------+ | January | J | | March | M | | March | Ma | | April | A | | May | M | | May | Ma | | June | J | | June | Ju | | July | J | | July | Ju | | August | A | +---------+--------+ 11 rows in set (0.02 sec) As always, I hope this helps those looking to open the hood and check the engine. [Less]

As many know, I’ve adopted Learning SQL by Alan Beaulieu as a core reference for my database class. Chapter 7 in the book focuses on data generation, manipulation, and conversion. The last exercise question in my check of whether they read the ... [More] chapter and played with some of the discussed functions is: Use one or more temporal function to write a query that convert the ’29-FEB-2024′ string value into a default MySQL date format. The result should display: +--------------------+ | mysql_default_date | +--------------------+ | 2024-02-29 | +--------------------+ 1 row in set, 1 warning (0.00 sec) If you’re not familiar with the behavior of MySQL functions, this could look like a difficult problem to solve. If you’re risk inclined you would probably try the STR_TO_DATE function but if you’re not risk inclined the description of the %m specifier might suggest you don’t have SQL builtin to solve the problem. I use the problem to teach the students how to solve problems in SQL queries. The first step requires putting the base ’29-FEB-2024′ string value into a mystringcolumn in the strings table, like: DROP TABLE IF EXISTS strings; CREATE TABLE strings (mystring VARCHAR(11)); SELECT 'Insert' AS statement; INSERT INTO strings (mystring) VALUES ('29-FEB-2024'); The next step requires creating a query with: A list of parameters in a Common Table Expression (CTE) A CASE statement to filter results in the SELECT-list A CROSS JOIN between the strings table and params CTE The query would look like this resolves the comparison in the CASE statement through a case insensitive comparison: SELECT 'Query' AS statement; WITH params AS (SELECT 'January' AS full_month UNION ALL SELECT 'February' AS full_month) SELECT s.mystring , p.full_month , CASE WHEN SUBSTR(s.mystring,4,3) = SUBSTR(p.full_month,1,3) THEN STR_TO_DATE(REPLACE(s.mystring,SUBSTR(s.mystring,4,3),p.full_month),'%d-%M-%Y') END AS converted_date FROM strings s CROSS JOIN params p; and return: +-------------+------------+----------------+ | mystring | full_month | converted_date | +-------------+------------+----------------+ | 29-FEB-2024 | January | NULL | | 29-FEB-2024 | February | 2024-02-29 | +-------------+------------+----------------+ 2 rows in set (0.00 sec) The problem with the result set, or derived table, is the CROSS JOIN. A CROSS JOIN matches every row in one table with every row in another table or derived table from prior joins. That means you need to add a filter in the WHERE clause to ensure you only get matches between the strings and parameters, like the modified query: WITH params AS (SELECT 'January' AS full_month UNION ALL SELECT 'February' AS full_month) SELECT s.mystring , p.full_month , CASE WHEN SUBSTR(s.mystring,4,3) = SUBSTR(p.full_month,1,3) THEN STR_TO_DATE(REPLACE(s.mystring,SUBSTR(s.mystring,4,3),p.full_month),'%d-%M-%Y') END AS converted_date FROM strings s CROSS JOIN params p WHERE SUBSTR(s.mystring,4,3) = SUBSTR(p.full_month,1,3); It returns a single row, like: +-------------+------------+----------------+ | mystring | full_month | converted_date | +-------------+------------+----------------+ | 29-FEB-2024 | February | 2024-02-29 | +-------------+------------+----------------+ 1 row in set (0.00 sec) However, none of this is necessary because the query can be written like this: SELECT STR_TO_DATE('29-FEB-2024','%d-%M-%Y') AS mysql_date; It returns: +------------+ | mysql_date | +------------+ | 2024-02-29 | +------------+ 1 row in set (0.00 sec) That’s because the STR_TO_DATE() function with the %M specifier resolves all months with three or more characters. Three characters are required because both Mar and May, and June and July can only be qualified by three characters. If you provide less than three characters of the month, the function returns a null value. Here’s a complete test case that lets you discover all the null values that may occur with two few characters: /* Conditionally drop the table. */ DROP TABLE IF EXISTS month, param; /* Create a table. */ CREATE TABLE month ( month_name VARCHAR(9)); /* Insert into the month table. */ INSERT INTO month ( month_name ) VALUES ('January') ,('February') ,('March') ,('April') ,('May') ,('June') ,('July') ,('August') ,('September') ,('October') ,('November') ,('December'); /* Create a table. */ CREATE TABLE param ( month VARCHAR(9) , needle VARCHAR(9)); /* Conditionally drop the procedure. */ DROP PROCEDURE IF EXISTS read_string; DROP PROCEDURE IF EXISTS test_month_name; /* Reset the execution delimiter to create a stored program. */ DELIMITER $$ /* Create a procedure. */ CREATE PROCEDURE read_string(month_name VARCHAR(9)) BEGIN /* Declare a handler variable. */ DECLARE display VARCHAR(17); DECLARE evaluate VARCHAR(17); DECLARE iterator INT DEFAULT 1; DECLARE partial VARCHAR(9); /* Read the list of characters. */ character_loop:LOOP /* Print the character list. */ IF iterator > LENGTH(month_name) THEN LEAVE character_loop; END IF; /* Assign substring of month name. */ SELECT SUBSTR(month_name,1,iterator) INTO partial; SELECT CONCAT('01-',partial,'-2024') INTO evaluate; /* Print only the strings too short to identify as the month. */ IF STR_TO_DATE(evaluate,'%d-%M-%Y') IS NULL THEN INSERT INTO param ( month, needle ) VALUES ( month_name, partial ); END IF; /* Increment the counter. */ SET iterator = iterator + 1; END LOOP; END; $$ /* Create a procedure. */ CREATE PROCEDURE test_month_name() BEGIN /* Declare a handler variable. */ DECLARE month_name VARCHAR(9); /* Declare a handler variable. */ DECLARE fetched INT DEFAULT 0; /* Cursors must come after variables and before event handlers. */ DECLARE month_cursor CURSOR FOR SELECT m.month_name FROM month m; /* Declare a not found record handler to close a cursor loop. */ DECLARE CONTINUE HANDLER FOR NOT FOUND SET fetched = 1; /* Open cursor and start simple loop. */ OPEN month_cursor; cursor_loop:LOOP /* Fetch a record from the cursor. */ FETCH month_cursor INTO month_name; /* Place the catch handler for no more rows found immediately after the fetch operations. */ IF fetched = 1 THEN /* Fetch the partial strings that fail to find a month. */ SELECT * FROM param; /* Leave the loop. */ LEAVE cursor_loop; END IF; /* Call the subfunction because stored procedures do not support nested loops. */ CALL read_string(month_name); END LOOP; END; $$ /* Reset the delimter. */ DELIMITER ; CALL test_month_name(); It returns the list of character fragments that fail to resolve English months: +---------+--------+ | month | needle | +---------+--------+ | January | J | | March | M | | March | Ma | | April | A | | May | M | | May | Ma | | June | J | | June | Ju | | July | J | | July | Ju | | August | A | +---------+--------+ 11 rows in set (0.02 sec) There are two procedures because MySQL doesn’t support nested loops and uses a single-pass parser. So, the first read_string procedure is the inner loop and the second test_month_name procedure is the outer loop. I wrote a follow-up to this post because of a reader’s question about not cleaning up the test case. In the other post, you will find a drop_table procedure that lets you dynamically drop the param table created to store the inner loop procedure’s results. As always, I hope this helps those looking to open the hood and check the engine. [Less]

Sometimes you hear from some new developers that MySQL only makes case insensitive string comparisons. One of my students showed me their test case that they felt proved it: SELECT STRCMP('a','A') WHERE 'a' = 'A'; Naturally, it returns 0, which ... [More] means: The values compared by the STRCMP() function makes a case insensitive comparison, and The WHERE clause also compares strings case insensitively. As a teacher, you’re gratified that the student took the time to build their own use cases. However, in this case I had to explain that while he was right about the STRCMP() function and the case insensitive comparison the student used in the WHERE clause was a choice, it wasn’t the only option. The student was wrong to conclude that MySQL couldn’t make case sensitive string comparisons. I modified his sample by adding the required BINARY keyword for a case sensitive comparison in the WHERE clause: SELECT STRCMP('a','A') WHERE BINARY 'a' = 'A'; It returns an empty set, which means the binary comparison in the WHERE clause is a case sensitive comparison. Then, I explained while the STRCMP() function performs a case insensitive match, the REPLACE() function performs a case sensitive one. Then, I gave him the following expanded use case for the two functions: SELECT STRCMP('a','A') AS test1 , REPLACE('a','A','b') AS test2 , REPLACE('a','a','b') AS test3; It returns: +-------+-------+-------+ | test1 | test2 | test3 | +-------+-------+-------+ | 0 | a | b | +-------+-------+-------+ 1 row in set (0.00 sec) The behavior of one function may be different than another as to how it compares strings, and its the developers responsibility to make sure they understand its behavior thoroughly before they use it. The binary comparison was a win for the student since they were building a website that needed that behavior from MySQL. As always, I hope tidbits like this save folks time using MySQL. [Less]

This post is the twelfth one of a series of articles on extending MySQL with the Component Infrastructure: Extending MySQL using the Component Infrastructure – part 1Extending MySQL using the Component Infrastructure – part 2: building the ... [More] serverExtending MySQL using the Component Infrastructure – part 3: component servicesExtending MySQL using the Component Infrastructure – part 4: error loggingExtending MySQL using the Component Infrastructure – part 5: privilegesExtending MySQL using the Component Infrastructure – part 6: functionsExtending MySQL using the Component Infrastructure – part 7: messages to usersExtending MySQL using the Component Infrastructure – part 8: linking a third party libraryExtending MySQL using the Component Infrastructure – part 9: adding a new functionExtending MySQL using the Component Infrastructure – part 10: status variablesExtending MySQL using the Component Infrastructure – part 11: performance_schema tableExtending MySQL using the Component Infrastructure – part 12: instrument your codeWe already saw multiple services to create our component. Now, our virus scan component is complete and works as expected. However, when you create extension to the MySQL Server, it’s also a good practice to add instrumentation to your code. This will help you to understand what is your code doing and how it performs in MySQL. This is exactly what we will add today to our component. The new code is on the branch par12 of this GitHub repository: https://github.com/lefred/mysql-component-viruscan. We starts by replacing the mutex we used for our table in Performance_Schema (see part 11) from native_mutex_t to mysql_mutex_t. And we will use REQUIRES_MYSQL_MUTEX_SERVICE_PLACEHOLDER (line 61 of scan.h) . We also need to add it in the requirements of our component (line 485 of scan.cc) Then we define the new mutex key and information that will be used in MySQL Performance Schema on line 52 to 56 of scan.cc: PSI_mutex_key key_mutex_virus_data = 0; PSI_mutex_info virus_data_mutex[] = { {&key_mutex_virus_data, "virus_scan_data", PSI_FLAG_SINGLETON, PSI_VOLATILITY_PERMANENT, "Virus scan data, permanent mutex, singleton."} }; Something very important is to not forget the registration of the mutex (this was not required with the native_mutex_t type) when initializing the component. Otherwise nothing will work as expected. This is exactly what we do in viruscan_service_init() on line 372 of scan.cc: mysql_mutex_register("virus_scan", virus_data_mutex, 1); We will add a “bug” that when triggered we will spend 5 seconds waiting with the lock acquired to create some contention in case of another session tries to also scan data triggering that bug at the same time. This fake bug is triggered by the string “bug-struck” and it’s coded from line 214 to 221 in scan.cc: //just a fake bug if (strcmp(data, "bug-stuck") == 0) { mysql_mutex_lock(&LOCK_virus_data); LogComponentErr(INFORMATION_LEVEL, ER_LOG_PRINTF_MSG, "Generating a bug to spend time in mutex"); my_sleep(5000000); // 5.0s mysql_mutex_unlock(&LOCK_virus_data); } We also print a message in MySQL’s error log. Let’s see all this in action in performance_schema tables setup_instruments and events_waits_history: session 1And meanwhile into another session: session 2Summary If you are looking for what can be instrumented this is the current list of available instrumentation: PFS_PSI_LIST. We have now covered most of the main component services to create that can be used to create our own component. I hope you enjoyed this journey and I’m curious to see the components you will created. Please share them with me ! And as usual, enjoy MySQL and happy coding ! [Less]

Magento is an Open-Source e-commerce platform written in PHP using multiple other PHP frameworks such as Laminas and Symphony. Magento source code is distributed under Open Software License v3.0. Deploying Magento is not always easy as it often requires registration. However the source code is also available on GitHub...

In this post, I describe you how to deploy WordPress on OCI using MySQL Database Service. I wrote some Terraform recipes that you can find on my GitHub repository...

It is essential to upgrade MySQL to the most recent version. Do you believe it’s tough to test and upgrade to a newer version? For a variety of reasons, including new features, performance advantages, bug corrections, and so on, databases with ... [More] obsolete versions are vulnerable. Major version upgrades, on the other hand, can be problematic if they haven’t been extensively tested with your application, as the procedure may break it, prevent it from functioning properly, or result in performance concerns. Let’s go through a few useful tools that can assist you with MySQL upgrades. pt-upgrade The tool helps you run application SELECT queries and generates reports on how each query pattern performs on the servers across the different versions of MySQL we tested. pt-query-digest We need to gather all application queries by activating the slow log for a day or a few hours that covers the majority of the queries, however, the slow log will be enormous, and applying them will take time, thus the pt-query-digest tool can assist in query digest preparation for upgrade testing. pt-config-diff  The tool aids in determining the differences in MySQL settings between files and server variables. This allows us to compare the upgraded version to the previous version, allowing us to validate the configuration differences.pt-config-diff h= h= pt-upgrade Testing Requirements and Steps Let’s go over the requirements and steps for testing application queries with pt-upgrade. For testing purposes, we require and suggest having two servers that meet production specs and are connected to the same network. For example, to test MySQL 5.7 and MySQL 8, we build the two instances, one with MySQL 5.7 and the other with MySQL 8, both from a recent production backup Replication Compatibility Test for the Production Data Set up replication for both test nodes for a day, replicating from the production primary to see if the replication works from the lower current production version to the newer version, i.e. covering the actual application workload. Before we begin pt-upgrade testing, it’s important to stop replication on both test nodes at the same binary log position to confirm the data in both nodes are identical. The High-Level Plan Will be as Follows: Install MySQL on the two test nodes. The current MySQL version will be on one node, while the target version will be on the other. Production data needs to be restored to the test nodes, Percona XtraBackup can be used to backup and restore the backup from the production node to the test nodes. As part of the replication compatibility test as aforesaid, set up replication for both test nodes for a day i.e. covering the actual workload, replicating from the production primary. The slow log can be used to collect all queries from the production nodes using long_query_time = 0 and log_slow_rate_limit =1. We need to gather all application queries by activating the slow log for a day or a few hours, which should cover the majority of the application queries. However, in most cases, the slow log will be massive, and applying them using the pt-upgrade will take time, so the pt-query-digest tool can help with query digest preparation for pt-upgrade testing. In the digest, for example, we can take a max of 50 samples per query. Note: When processing large slow logs, the tool may use some memory, so keep an eye on it if you're running it on production servers. pt-query-digest --sample 50 --no-report --output slowlog > .out If any below session variables are found in the digested slow log, it is likely to be checked out and removed so that the queries can continue to run as they are. SET SQL_SAFE_UPDATES=...,SQL_SELECT_LIMIT=...,MAX_JOIN_SIZE=.... Before starting the pt-upgrade tests, to ensure that the data on both test nodes is identical, stop replication on both test nodes at the same binary log position. Readonly Test All queries will be played back using the pt-upgrade tool in read-only mode a couple of times on test nodes logging the results. We can discard the first run’s results because this is just to warm up the Innodb Buffer pool, and execute the same pt-upgrade command again.$ pt-upgrade h=Test1 h=Test2 --max-examples=1 .out 1> pt-upgrade_results.out 2> pt-upgrade_results.err Read Write Test All queries will be played back in read-write mode enabling the –no-read-only option once on the test nodes, logging the results.$ pt-upgrade h=Test1 h=Test2 --no-read-only --max-examples=1 .out 1> pt-upgrade_results_RW.out 2> pt-upgrade_results_RW.errRead-only and read-write test reports are now available to evaluate and isolate queries that are slower, return more/fewer rows, or return different rows altogether, and return an error or warning. We can implement fixes based on the report to address the issues raised by pt-upgrade testing. This may require MySQL setup changes, query optimization, query rewriting, schema changes, index additions or revisions, and so on. [Less]

Knowing the first and last day of a given month can help you figure out other information relevant and important data. You can determine these values using select MySQL date functions. Learn how in this Medium post I’m resharing here for any ... [More] interested readers… Get tailored articles with Refind delivered each day in your inbox. Refind is part of my daily reading habit. Make it part of yours by using my referral link. At no additional cost to you, I will be eligible for a premium subscription with more sign-ups from my link. “The essence of the web, every morning in your inbox. Subscribe for free“ The Newsletter for PHP and MySQL Developers MySQL has many Date functions available to help you process temporal data types and values. Sometimes you have to use specific date functions together to get the information you need if existing functionality is lacking in the built-in suite of functions. Visit the post, Determine the First and Last Day of a Month with MySQL, to learn how to use several of the available Date functions for this specific information. Similar Reading Enjoy any of these related articles and please share them with others who would enjoy them as well: 5 PHP Date Functions and Their MySQL EquivalentsPHP date function and common formatsHow to find the first and last day of a month with Oracle SQL Date Functions – Medium cross post Self-Promotion: If you enjoy the content written here, by all means, share this blog and your favorite post(s) with others who may benefit from or like it as well. Since coffee is my favorite drink, you can even buy me one if you would like! Are you a Medium member? If so, receive an email notification each time I publish a blog post if you prefer the Medium platform. Not a member? No worries! Use my sign-up link (I will get a commission at no extra cost to you) and join. I really enjoy reading all the great content there and I know you will too!!! Thank you for reading this post and the content here on Digital Owl’s Prose. Be sure and check out my free weekly newsletter OpenLampTech, the newsletter for PHP/MySQL developers. Support my content and blog by donating to my tip jar. Thank you! Tip Jar Like what you have read? See anything incorrect? Please comment below and thank you for reading!!! A Call To Action! Thank you for taking the time to read this post. I truly hope you discovered something interesting and enlightening. Please share your findings here, with someone else you know who would get the same value out of it as well. Visit the Portfolio-Projects page to see blog posts/technical writing I have completed for clients. To receive email notifications (Never Spam) from this blog (“Digital Owl’s Prose”) for the latest blog posts as they are published, please subscribe (of your own volition) by clicking the ‘Click To Subscribe!’ button in the sidebar on the homepage! (Feel free at any time to review the Digital Owl’s Prose Privacy Policy Page for any questions you may have about: email updates, opt-in, opt-out, contact forms, etc…) Be sure and visit the “Best Of” page for a collection of my best blog posts. Josh Otwell has a passion to study and grow as a SQL Developer and blogger. Other favorite activities find him with his nose buried in a good book, article, or the Linux command line. Among those, he shares a love of tabletop RPG games, reading fantasy novels, and spending time with his wife and two daughters. Disclaimer: The examples presented in this post are hypothetical ideas of how to achieve similar types of results. They are not the utmost best solution(s). The majority, if not all, of the examples provided, are performed on a personal development/learning workstation environment and should not be considered production quality or ready. Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own. How can I help you? Are you thinking of starting up a blog? I use WordPress for my blog. Let’s both save money on the plans offered. Grab a Gmail HTML Email Signature template from my Etsy shop and make your emails pop and stand out. Need hosting for your next web application or WordPress site? I use and highly recommend Hostinger. They have great pricing and service.I enjoy reading Refind: The essence of the web, every morning in your inbox. Subscribe for free. Help me get a premium subscription by signing up yourself with my referral link.Grab a free pack of mobile Creator wallpapers.Just getting started or wanting to learn MySQL? Find out about my premium blog posts and MySQL Beginner Series here.The Newsletter for PHP and MySQL Developers Disclosure: Some of the services and products links in this post are affiliate links. At no additional cost to you, should you make a purchase by clicking through one of them, I will receive a commission. The post Find the first and last day of a month with MySQL – Medium repost appeared first on Digital Owl's Prose. [Less]

Not only do I share the weekly OpenLampTech newsletter, but I’m also publishing some articles outside of the publication, directly to my Substack page. I’m resharing the last 2 I’ve posted over on my Substack for any interested readers… Do you ... [More] need to learn MySQL? Just starting and confused about how to query a table and get the data you need? I am creating premium MySQL Beginners content for this specific reason. To help those who want to learn the basics of MySQL but don’t know where to start. Learn more about the premium blog posts as I develop and release them. Tell me more! MySQL SHOW TABLES – Two Variations This post is an excerpt from some premium content I am creating here on my blog for beginners wanting to learn MySQL. Read the full substack article: MySQL SHOW TABLES – 2 Variations. Related: MySQL Beginners Series — The SELECT statement. MySQL ROW_NUMBER() with CASE Expression This post is another one I had a lot of fun writing because I learned so much while putting it together. I’m covering how to collapse multiple rows into a single row per distinct group. The ROW_NUMBER() window function is key here and is center stage in this article. Read the full substack article: MySQL ROW_NUMBER() Window Function with CASE Expression. OpenLampTech is the newsletter for PHP/MySQL developers. Get the best original and curated content delivered directly to your inbox each week for free. Help build a great community by signing up today. The Newsletter for PHP and MySQL Developers How can I help you? Are you thinking of starting up a blog? I use WordPress for my blog. Let’s both save money on the plans offered. Grab a Gmail HTML Email Signature template from my Etsy shop and make your emails pop and stand out. Need hosting for your next web application or WordPress site? I use and highly recommend Hostinger. They have great pricing and service.I enjoy reading Refind: The essence of the web, every morning in your inbox. Subscribe for free. Help me get a premium subscription by signing up yourself with my referral link.Grab a free pack of mobile Creator wallpapers.Just getting started or wanting to learn MySQL? Find out about my premium blog posts and MySQL Beginner Series here.Disclosure: Some of the services and products links in this post are affiliate links. At no additional cost to you, should you make a purchase by clicking through one of them, I will receive a commission. If you found value in this post, you can show your appreciation and buy me a coffee. It is my favorite drink! The Newsletter for PHP and MySQL Developers The post OpenLampTech MySQL Extras – Substack Writing appeared first on Digital Owl's Prose. [Less]

Percona XtraBackup delivers the xbcloud binary – an auxiliary tool to allow users to upload backups to different cloud providers directly. Today we are glad to announce the support of the Azure Blob storage to xbcloud. This new feature will allow you ... [More] to upload/download backups to Azure Blob storage. You would need an Azure storage account and access key. Check out this blog post on how to get one through the Azure portal. This new functionality is available in the release of Percona XtraBackup 8.0.27 and will be available in Percona XtraBackup 2.4.25. You can install xbcloud and XtraBackup via the percona-release package configuration tool. See the Percona Documentation for further details. The Following Options are Added in xbcloud: Option Details –azure-storage-account Azure storage account. –azure-container-name Azure container name. –azure-access-key Azure access key. –azure-endpoint Azure cloud storage endpoint. –azure-tier-class Azure cloud tier for backup. Hot|Cool|ArchiveXbcloud doesn’t set the default value and the default value depends on the account tier. See azure documentation for more details. –azure-development-storageOnly to test your backup against azurite emulator To run against azurite emulator  It can work with the default credentials provided by azurite. For example, it uses http://127.0.0.1:10000 as the default endpoint, which can be overwritten by –azure-endpoint. Users can also  provide –azure-access-key,  –azure-storage-account, –azure-container-name. Environment Variables The following environment variables are recognized. xbcloud maps them automatically to corresponding parameters applicable to azure storage. AZURE_STORAGE_ACCOUNT AZURE_CONTAINER_NAME AZURE_ACCESS_KEY AZURE_ENDPOINT AZURE_STORAGE_CLASS Examples of the xbcloud Commands Backup to xbcloud xtrabackup –backup –stream=xbstream –target-dir= $TARGET_DIR | xbcloud put backup_name –azure-storage-account=pxbtesting –azure-access-key=$AZURE_KEY –azure-container-name=test –storage=azure Restore Backup from xbcloud xbcloud get backup_name  –azure-storage-account=$STORAGE_ACCOUNT –azure-access-key=$AZURE_KEY –azure-container-name=test –storage=azure –parallel=10 2>download.log | xbstream -x -C restore  Delete Backup from xbcloud xbcloud delete backup_name –azure-storage-account=$STORAGE_ACCOUNT –azure-access-key=$AZURE_KEY –azure-container-name=test –storage=azure Shortcut Restore export AZURE_ACCESS_KEY=$ACCESS_KEYexport AZURE_STORAGE_ACCOUNT=$STORAGE_ACCOUNTxbcloud get azure://test/backup_name … You can use all existing options for xbcloud like parallel, max-retries. See more details xbcloud documentation and help section. [Less]