MySQL

 MySQL 8.0.28 version is GA!!! , ready to use in production.Below is high level overview , more can be viewed from the weblink.[1] Disabling Audit Logging for all connecting and connected sessionsSET GLOBAL audit_log_disable = ... [More] true;Setting audit_log_disable to true disables the audit log plugin. The plugin is re-enabled when audit_log_disable is set back to false, which is the default setting.[2]- [Under Performance Schema] A new statement metric, CPU_TIME, is now available, enabling you to measure the CPU time spent on a query.This is helpful to troubleshoot performance of SQL statements specially hardware resource utilize by the SQL statement , below is the there table you can use to monitor the bottlenecks[3]- InnoDB now supports ALTER TABLE ... RENAME COLUMN operations using ALGORITHM=INSTANTOperations that support ALGORITHM=INSTANT only modify metadata in the data dictionary. No exclusive metadata locks are taken on the table during preparation and execution phases of the operation, and table data is unaffected, making the operations instantaneous. If not specified explicitly, ALGORITHM=INSTANT is used by default by DDL operations that support it.[4]- Replication: When the PAD_CHAR_TO_FULL_LENGTH SQL mode was enabled on a replica server, trailing spaces could be added to a replication channel’s name in the replication metadata repository tables, resulting in errors in replication operations that identified the channel using that data. The issue has now been fixed in MySQL 8.0 by using VARCHAR for character columns, and in MySQL 5.7 by disabling the SQL mode when reading from those tables. Thanks to Brian Yue for the contribution. (Bug #33213841)[5.]Producing a per-table dump using mysqldump in MySQL 5.7 and 8.0 requires a longer execution time compared to MySQL 5.6. This is because the information_schema.files table, which is queried for information on log file groups by mysqldump, contains information about InnoDB data files as well as NDB data files from MySQL 5.7.In MySQL 8.0 , the issue has been fixed by rewriting the query to select only the appropriate data files. In MySQL 5.7, Information Schema tables do not have indexes, so a full table scan is still required. (Bug #29210990, Bug #93875)More info:-  https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-28.html [Less]

 MySQL Server version  8.0.28 was launched a few days ago and without a lot a fanfare. Which is a shame and I wish we had more of a splash for every release.  The release notes are available here. I would like to highlight some of the changes.There ... [More] are over a hundred bug fixes and big thanks to Hope Lee, Casa Zhang, Øystein Grøvlen, Song Zhibai, Facebook, Hobert Lu, Brian Yue, Zhai Weixiang, Murakami Kohei,  Venkatesh Prasad Venugopal, and the Tencent team.The HighlightsThis release does not have a lot of 'wow' new features but shows a lot of solid work in evolving the product. The new audit_log_disable system variable permits disabling audit logging for all connecting and connected sessions.The output from EXPLAIN FORMAT=TREE was ex-encoded ranges for multi-valued indexes Now, hex-encoding is used only for string types having a binary character set. Strings with non-binary character sets are now printed  as plain text, with escaping for any special characters. Now, hex-encoding is used only for string types having a binary character set. Strings with non-binary character sets are now printed in EXPLAIN FORMAT=TREE output as plain text, with escaping for any special characters. The shortcuts ASCII for CHARACTER SET latin1 and UNICODE for CHARACTER SET ucs2 are now deprecated, and you should expect their removal in a future version of MySQL. Using either of these now raises a warning; use CHARACTER SET instead.The character sets ucs2, macoman, macce, dec, and hp4  along with all of their collations, are now deprecated, and subject to removal in a subsequent release of MySQL. You should use utf8mb4 instead of any of the character sets just listed.The GnuPG build key used to sign MySQL downloadable packages has been updated. The previous GnuPG build key is set to expire on 2022-02-16. For information about verifying the integrity and authenticity of MySQL downloadable packages using GnuPG signature checking, or to obtain a copy of our public GnuPG build key, (Yup, it took a little while to get the new key out there when '28 was first published -- appologies!)A new Performance Schema statement metric, CPU_TIME, is now available, enabling you to measure the CPU time spent on a query.The number of distinct windows which can appear in a given SELECT is now limited to 127. The number of distinct windows is the sum of the named windows and the implicit windows specified as part of any window function's OVER clause. In order to use a large number of windows, it may be necessary to increase the value of the thread_stack server system variable. (Not sure just how you count the number of implicit windows!)    UPDATE from Justin Swanhart - I was reading your latest blog post.  In a query each window function that uses OVER which does not use a named window is an implicit window.  If all of your window functions are framed the same they can share a named window, and you can have basically unlimited window functions.  But if you use OVER for each of them, then you are limited to 127.So, please download this new version ASAP.All opinions expressed in this blog are those of Dave Stokes who is actually amazed to find anyone else agreeing with him [Less]

 MySQL Server version  8.0.28 was launched a few days ago and without a lot a fanfare. Which is a shame and I wish we had more of a splash for every release.  The release notes are available here. I would like to highlight some of the changes.There ... [More] are over a hundred bug fixes and big thanks to Hope Lee, Casa Zhang, Øystein Grøvlen, Song Zhibai, Facebook, Hobert Lu, Brian Yue, Zhai Weixiang, Murakami Kohei,  Venkatesh Prasad Venugopal, and the Tencent team.The HighlightsThis release does not have a lot of 'wow' new features but shows a lot of solid work in evolving the product. The new audit_log_disable system variable permits disabling audit logging for all connecting and connected sessions.The output from EXPLAIN FORMAT=TREE was ex-encoded ranges for multi-valued indexes Now, hex-encoding is used only for string types having a binary character set. Strings with non-binary character sets are now printed  as plain text, with escaping for any special characters. Now, hex-encoding is used only for string types having a binary character set. Strings with non-binary character sets are now printed in EXPLAIN FORMAT=TREE output as plain text, with escaping for any special characters. The shortcuts ASCII for CHARACTER SET latin1 and UNICODE for CHARACTER SET ucs2 are now deprecated, and you should expect their removal in a future version of MySQL. Using either of these now raises a warning; use CHARACTER SET instead.The character sets ucs2, macoman, macce, dec, and hp4  along with all of their collations, are now deprecated, and subject to removal in a subsequent release of MySQL. You should use utf8mb4 instead of any of the character sets just listed.The GnuPG build key used to sign MySQL downloadable packages has been updated. The previous GnuPG build key is set to expire on 2022-02-16. For information about verifying the integrity and authenticity of MySQL downloadable packages using GnuPG signature checking, or to obtain a copy of our public GnuPG build key, (Yup, it took a little while to get the new key out there when '28 was first published -- appologies!)A new Performance Schema statement metric, CPU_TIME, is now available, enabling you to measure the CPU time spent on a query.The number of distinct windows which can appear in a given SELECT is now limited to 127. The number of distinct windows is the sum of the named windows and the implicit windows specified as part of any window function's OVER clause. In order to use a large number of windows, it may be necessary to increase the value of the thread_stack server system variable. (Not sure just how you count the number of implicit windows!)So, please download this new version ASAP.All opinions expressed in this blog are those of Dave Stokes who is actually amazed to find anyone else agreeing with him [Less]

Backups are a key part of a disaster recovery strategy, making sure you can continue or restore your business in case of an unwanted event with your data. We always work on trying to improve Percona XtraBackup reliability, always favoring ... [More] consistency, attempting to make unwanted outcomes be noticed as earlier as possible in the process. Enabling –strict by Default As of the upcoming release of 8.0.27, XtraBackup will no longer accept invalid parameters. Since the beginning of the times, validation of parameters has been a difficult task for XtraBackup as it mixes server-side and XtraBackup only parameters. Starting at Percona XtraBackup 8.0.7 we implemented PXB-1493 which added –strict option defaulting to false. This option issues a warning for each parameter/option that XtraBackup does not recognize.  Having this as a warning is not good enough for a few reasons: It’s easy for it to go unnoticed as we mostly never read all the lines from the output of the backup, paying attention only to the end of the backup to validate if it completed ok. Having it as a warning doesn’t prevent us from shooting ourselves in the foot. As an example, when applying a –prepare on an incremental backup, if we mistype –apply-log-only parameter on an incremental backup that is not the last one, we will be executing the rollback phase making it rollback in-fly transactions that might have been completed on the next incremental backup. And there are more. With all the above in mind, we have decided to enable –strict mode by default. What Does it Mean For You? From the 8.0.27 release going forward, you might see XtraBackup failing in case you use an invalid option, either from the [xtrabackup] group of your configuration file or passed as an argument on the command line.For the next few releases, we will still allow you to change back to the old behavior by passing the parameter –skip-strict, –strict=0, or –strict=OFF. In the future, –strict will be the sole option, and disabling it will be forbidden. [Less]

In this tutorial, we’ll show you how to upgrade/update MySQL to a newer version. This tutorial was written and tested for Ubuntu. Recommended reading: How to install MySQL on Ubuntu Before we begin Before we begin with our actual tutorial, this is what you need (to know): As of writing, the latest MySQL release is […] Source

MySQL is a proven, reliable database that is quick to set up easy to maintain, and has outstanding performance. However, that performance can usually be improved with the judicious use of the following tips that are all easy to implement...

I’m always trying different programming exercises in order to learn and grow as a Developer. One of my favorite learning exercises is porting over from one SQL dialect to another, as they all have their own individual features. Having to hack ... [More] together or mirror non-existent functionality really challenges my thinking, therefore enabling growth and improvement in my query skills. In this post, I share reproducing the same query results using MySQL for queries I first learned of/discovered that were covered using Oracle SQL and specific implementation features… Image by José Augusto Camargo from Pixabay 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! The Newsletter for PHP and MySQL Developers Credit and Sources I found great inspiration, learning, understanding, and the idea for this blog post based on this YouTube video: Sample Data The final query results we want are based on the present data in a table, ‘important_events’ having this fictisious data: SELECT * FROM important_events; Notice there are 5 random dates from the same month and year, with an event name and event comment. The goal is to provide results for every day of the entire month, including those days that do not have a row in table ‘important_events’. 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!!! The final results should be as those shown below: Information needed The information we need in order to retrieve the query results are: The first day of the month based on the month value in the column ‘event_date’ The last day of the month based on the month value in the column ‘event_date’ The total number of days in the month based on the month value in the column ‘event_date’In determining the first and last day of the respective month, we should use the minimum and maximum ‘event_date’ values currently stored in the ‘important_events’ table. We can easily get these values using both the MIN() and MAX() aggregate functions targeting the ‘event_date’ column: SELECT MIN(event_date) AS min_date, MAX(event_date) AS max_dateFROM important_events; Using the minimum and maximum date values, we calculate the first and last day of the month and the number of total days in the target month using specific MySQL Date functions. That query might look as the next example: SELECT DATE_ADD(MIN(event_date), INTERVAL - DAY(MIN(event_date)) + 1 DAY) AS mnth_first_day,LAST_DAY(MAX(event_date)) AS mnth_last_day,LAST_DAY(MAX(event_date)) - DATE_ADD(MIN(event_date), INTERVAL - DAY(MIN(event_date)) + 1 DAY) + 1 AS days_diffFROM important_events; MySQL does have a native LAST_DAY() date function we use to get the last day of the month. However, there is no FIRST_DAY() date function in MySQL (at the time of writing) so we have to do a little extra work and calculate it ourselves using the expression: DATE_ADD(MIN(event_date), INTERVAL - DAY(MIN(event_date)) + 1 DAY) If you would like to support my blog and content, please consider tossing some spare change in my Tip Jar. Thank you so much! Tip Jar Recursive Common Table Expression Oracle SQL has a CONNECT BY clause but there is no such native clause like it in MySQL as of the current writing. To replicate this functionality, for the purposes of this particular query, we can use a Recursive Common Table Expression (CTE). For easier handling, I’ll store the number of days of the month in a session variable using the SET keyword: SET @num_days := '';SELECTLAST_DAY(MAX(event_date)) - DATE_ADD(MIN(event_date), INTERVAL - DAY(MIN(event_date)) + 1 DAY) + 1 INTO @num_daysFROM important_events;SELECT @num_days; I can then use the ‘@num_days’ variable in a recursive CTE as shown below: The ‘n_dte’ CTE returns an incrementing set of INTEGER values equal to the total number of days in the target month (essentially 1 row for each day): We can now add each individual incrementing INTEGER value to the 1st day of the month date value and return a calendar day for each day of the month. The query to produce this information might look like the below example: SELECT DATE_ADD(MIN(event_date), INTERVAL - DAY(MIN(event_date)) + 1 DAY) AS mnth_first_day,n,DATE_ADD(DATE_ADD(MIN(event_date), INTERVAL - DAY(MIN(event_date)) + 1 DAY), INTERVAL n - 1 DAY) AS all_days_in_mnthFROM important_eventsJOIN n_dtegroup by nORDER BY n ASC; The Newsletter for PHP and MySQL Developers I’ve constrained the output rows for better readability shown in the below screenshot: Note: Rows output constrained to image size Multiple Common Table Expressions We can have multiple CTE’s in the same WITH clause by separating them with commas, so I’ll include this query as its own CTE in addition to the already established, ‘n_dte’: Together, our 2 CTE’s look like this: Multiple CTE’s are the foundation of our query.LEFT JOIN – Final Results Set Now we simply need to LEFT JOIN the ‘all_mnth_days’ CTE to  the ‘important_events’ table on the 2 date columns: SELECTamd.all_days_in_mnth AS event_date, IFNULL(ie.event_name, 'No Event') AS event_name, IFNULL(ie.event_comment, 'No Event Comment') AS event_commentFROM all_mnth_days AS amdLEFT JOIN important_events AS ieON ie.event_date = amd.all_days_in_mnth; And we have the desired, final query results: I won’t lay claim that this is the best, or only, or most performant way to solve this particular query in MySQL. Only that this is how I figured out how to retrieve this particular data. I’d love to know your thoughts, suggestions, and feedback. If you would’ve used a different approach or other MySQL functionality, please share in the comments below so that myself and other readers can learn and broaden our own query chops. If you have any questions or see any mistakes in the code, please let me know via the comments. Constructive comments help me provide accurate blog posts and are much appreciated. Thank you for reading! 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. 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.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 Fill in missing Date ranges using MySQL appeared first on Digital Owl's Prose. [Less]

I’m always trying different programming exercises in order to learn and grow as a Developer. One of my favorite learning exercises is porting over from one SQL dialect to another, as they all have their own individual features. Having to hack ... [More] together or mirror non-existent functionality really challenges my thinking, therefore enabling growth and improvement in my query skills. In this post, I share reproducing the same query results using MySQL for queries I first learned of/discovered that were covered using Oracle SQL and specific implementation features… Image by José Augusto Camargo from Pixabay 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! The Newsletter for PHP and MySQL Developers Credit and Sources I found great inspiration, learning, understanding, and the idea for this blog post based on this YouTube video: Sample Data The final query results we want are based on the present data in a table, ‘important_events’ having this fictisious data: SELECT * FROM important_events; Notice there are 5 random dates from the same month and year, with an event name and event comment. The goal is to provide results for every day of the entire month, including those days that do not have a row in table ‘important_events’. 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!!! The final results should be as those shown below: Information needed The information we need in order to retrieve the query results are: The first day of the month based on the month value in the column ‘event_date’ The last day of the month based on the month value in the column ‘event_date’ The total number of days in the month based on the month value in the column ‘event_date’In determining the first and last day of the respective month, we should use the minimum and maximum ‘event_date’ values currently stored in the ‘important_events’ table. We can easily get these values using both the MIN() and MAX() aggregate functions targeting the ‘event_date’ column: SELECT MIN(event_date) AS min_date, MAX(event_date) AS max_dateFROM important_events; Using the minimum and maximum date values, we calculate the first and last day of the month and the number of total days in the target month using specific MySQL Date functions. That query might look as the next example: SELECT DATE_ADD(MIN(event_date), INTERVAL - DAY(MIN(event_date)) + 1 DAY) AS mnth_first_day,LAST_DAY(MAX(event_date)) AS mnth_last_day,LAST_DAY(MAX(event_date)) - DATE_ADD(MIN(event_date), INTERVAL - DAY(MIN(event_date)) + 1 DAY) + 1 AS days_diffFROM important_events; MySQL does have a native LAST_DAY() date function we use to get the last day of the month. However, there is no FIRST_DAY() date function in MySQL (at the time of writing) so we have to do a little extra work and calculate it ourselves using the expression: DATE_ADD(MIN(event_date), INTERVAL - DAY(MIN(event_date)) + 1 DAY) If you would like to support my blog and content, please consider tossing some spare change in my Tip Jar. Thank you so much! Tip Jar Recursive Common Table Expression Oracle SQL has a CONNECT BY clause but there is no such native clause like it in MySQL as of the current writing. To replicate this functionality, for the purposes of this particular query, we can use a Recursive Common Table Expression (CTE). For easier handling, I’ll store the number of days of the month in a session variable using the SET keyword: SET @num_days := '';SELECTLAST_DAY(MAX(event_date)) - DATE_ADD(MIN(event_date), INTERVAL - DAY(MIN(event_date)) + 1 DAY) + 1 INTO @num_daysFROM important_events;SELECT @num_days; I can then use the ‘@num_days’ variable in a recursive CTE as shown below: The ‘n_dte’ CTE returns an incrementing set of INTEGER values equal to the total number of days in the target month (essentially 1 row for each day): We can now add each individual incrementing INTEGER value to the 1st day of the month date value and return a calendar day for each day of the month. The query to produce this information might look like the below example: SELECT DATE_ADD(MIN(event_date), INTERVAL - DAY(MIN(event_date)) + 1 DAY) AS mnth_first_day,n,DATE_ADD(DATE_ADD(MIN(event_date), INTERVAL - DAY(MIN(event_date)) + 1 DAY), INTERVAL n - 1 DAY) AS all_days_in_mnthFROM important_eventsJOIN n_dtegroup by nORDER BY n ASC; The Newsletter for PHP and MySQL Developers I’ve constrained the output rows for better readability shown in the below screenshot: Note: Rows output constrained to image size Multiple Common Table Expressions We can have multiple CTE’s in the same WITH clause by separating them with commas, so I’ll include this query as its own CTE in addition to the already established, ‘n_dte’: Together, our 2 CTE’s look like this: Multiple CTE’s are the foundation of our query.LEFT JOIN – Final Results Set Now we simply need to LEFT JOIN the ‘all_mnth_days’ CTE to  the ‘important_events’ table on the 2 date columns: SELECTamd.all_days_in_mnth AS event_date, IFNULL(ie.event_name, 'No Event') AS event_name, IFNULL(ie.event_comment, 'No Event Comment') AS event_commentFROM all_mnth_days AS amdLEFT JOIN important_events AS ieON ie.event_date = amd.all_days_in_mnth; And we have the desired, final query results: I won’t lay claim that this is the best, or only, or most performant way to solve this particular query in MySQL. Only that this is how I figured out how to retrieve this particular data. I’d love to know your thoughts, suggestions, and feedback. If you would’ve used a different approach or other MySQL functionality, please share in the comments below so that myself and other readers can learn and broaden our own query chops. If you have any questions or see any mistakes in the code, please let me know via the comments. Constructive comments help me provide accurate blog posts and are much appreciated. Thank you for reading! 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. 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. The post Fill in missing Date ranges using MySQL appeared first on Digital Owl's Prose. [Less]

Today we are already at the post number ten of the series of articles related to extending MySQL with the Component Infrastructure, the list above will be updated as new articles are published: Extending MySQL using the Component Infrastructure – ... [More] part 1Extending MySQL using the Component Infrastructure – part 2: building the 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 variablesToday we will keep improving our component by adding some status variables. What I have in mind is to add the following status variables: clamav_signatures (integer): the amount of signatures loadedclamav_engine_version (varchar): the ClamAV Engine Versionvirus_found (integer): counter of detected virusesOnce again, we will use a MySQL Component Service to handle our status variables: the Status Variable Registration service. In scan.h we will add the required include file and the required service placeholder, lines 30 and 49: This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode characters Show hidden characters /* Copyright (c) 2017, 2022, Oracle and/or its affiliates. All rights reserved. This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License, version 2.0, as published by the Free Software Foundation. This program is also distributed with certain software (including but not limited to OpenSSL) that is licensed under separate terms, as designated in a particular file or component or in included license documentation. The authors of MySQL hereby grant you an additional permission to link the program and your derivative works with the separately licensed software that they have included with MySQL. This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License, version 2.0, for more details. You should have received a copy of the GNU General Public License along with this program; if not, write to the Free Software Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA */ #define LOG_COMPONENT_TAG "viruscan" #include #include /* LogComponentErr */ #include /* Errors */ #include #include #include #include #include #include #include #include #include #include extern REQUIRES_SERVICE_PLACEHOLDER(log_builtins); extern REQUIRES_SERVICE_PLACEHOLDER(log_builtins_string); extern REQUIRES_SERVICE_PLACEHOLDER(dynamic_privilege_register); extern REQUIRES_SERVICE_PLACEHOLDER(udf_registration); extern REQUIRES_SERVICE_PLACEHOLDER(mysql_udf_metadata); extern REQUIRES_SERVICE_PLACEHOLDER(mysql_thd_security_context); extern REQUIRES_SERVICE_PLACEHOLDER(global_grants_check); extern REQUIRES_SERVICE_PLACEHOLDER(mysql_current_thread_reader); extern REQUIRES_SERVICE_PLACEHOLDER(mysql_runtime_error); extern REQUIRES_SERVICE_PLACEHOLDER(status_variable_registration); extern SERVICE_TYPE(log_builtins) * log_bi; extern SERVICE_TYPE(log_builtins_string) * log_bs; view raw scan.h hosted with ❤ by GitHub Now we can modify the code of our component, scan.cc. We start as usually by adding the service placeholder (line 39) and we add the status_variable_registration service to the required services on line 424: This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode characters Show hidden characters /* Copyright (c) 2017, 2022, Oracle and/or its affiliates. All rights reserved. This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License, version 2.0, as published by the Free Software Foundation. This program is also distributed with certain software (including but not limited to OpenSSL) that is licensed under separate terms, as designated in a particular file or component or in included license documentation. The authors of MySQL hereby grant you an additional permission to link the program and your derivative works with the separately licensed software that they have included with MySQL. This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License, version 2.0, for more details. You should have received a copy of the GNU General Public License along with this program; if not, write to the Free Software Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA */ #define LOG_COMPONENT_TAG "viruscan" #define NO_SIGNATURE_CHANGE 0 #define SIGNATURE_CHANGE 1 #include REQUIRES_SERVICE_PLACEHOLDER(log_builtins); REQUIRES_SERVICE_PLACEHOLDER(log_builtins_string); REQUIRES_SERVICE_PLACEHOLDER(dynamic_privilege_register); REQUIRES_SERVICE_PLACEHOLDER(udf_registration); REQUIRES_SERVICE_PLACEHOLDER(mysql_udf_metadata); REQUIRES_SERVICE_PLACEHOLDER(mysql_thd_security_context); REQUIRES_SERVICE_PLACEHOLDER(global_grants_check); REQUIRES_SERVICE_PLACEHOLDER(mysql_current_thread_reader); REQUIRES_SERVICE_PLACEHOLDER(mysql_runtime_error); REQUIRES_SERVICE_PLACEHOLDER(status_variable_registration); SERVICE_TYPE(log_builtins) * log_bi; SERVICE_TYPE(log_builtins_string) * log_bs; static const char *SCAN_PRIVILEGE_NAME = "VIRUS_SCAN"; static unsigned int signature_status = 0; static unsigned int virusfound_status = 0; static char clamav_version[10] = ""; static SHOW_VAR viruscan_status_variables[] = { {"viruscan.clamav_signatures", (char *)&signature_status, SHOW_INT, SHOW_SCOPE_GLOBAL}, {"viruscan.clamav_engine_version", (char *)&clamav_version, SHOW_CHAR, SHOW_SCOPE_GLOBAL}, {"viruscan.virus_found", (char *)&virusfound_status, SHOW_INT, SHOW_SCOPE_GLOBAL}, {nullptr, nullptr, SHOW_LONG, SHOW_SCOPE_GLOBAL} }; struct scan_result scan_data(const char *data, size_t data_size); /* * Holds the data of a virus scan */ struct scan_result { int return_code; const char *virus_name; long unsigned int scanned; }; /* * Global variable to access the ClamAV engine */ struct cl_engine *engine = NULL; char *signatureDir; struct cl_stat signatureStat; class udf_list { typedef std::list udf_list_t; public: ~udf_list() { unregister(); } bool add_scalar(const char *func_name, enum Item_result return_type, Udf_func_any func, Udf_func_init init_func = NULL, Udf_func_deinit deinit_func = NULL) { if (!mysql_service_udf_registration->udf_register( func_name, return_type, func, init_func, deinit_func)) { set.push_back(func_name); return false; } return true; } bool unregister() { udf_list_t delete_set; /* try to unregister all of the udfs */ for (auto udf : set) { int was_present = 0; if (!mysql_service_udf_registration->udf_unregister(udf.c_str(), &was_present) || !was_present) delete_set.push_back(udf); } /* remove the unregistered ones from the list */ for (auto udf : delete_set) set.remove(udf); /* success: empty set */ if (set.empty()) return false; /* failure: entries still in the set */ return true; } private: udf_list_t set; } * list; unsigned int reload_engine() { unsigned int signatureNum = 0; int rv; if (engine != NULL) { cl_engine_free(engine); } engine = cl_engine_new(); memset(&signatureStat, 0, sizeof(struct cl_stat)); signatureDir = const_cast(cl_retdbdir()); cl_statinidir(signatureDir, &signatureStat); /* * Load the signatures from signatureDir, we use only the default dir */ rv = cl_load(signatureDir, engine, &signatureNum, CL_DB_STDOPT); char buf[1024]; if (CL_SUCCESS != rv) { snprintf(buf, 1024, "failure loading clamav databases: %s", cl_strerror(rv)); LogComponentErr(ERROR_LEVEL, ER_LOG_PRINTF_MSG, buf); } rv = cl_engine_compile(engine); if (CL_SUCCESS != rv) { snprintf(buf, 1024, "cannot create clamav engine: %s", cl_strerror(rv)); LogComponentErr(ERROR_LEVEL, ER_LOG_PRINTF_MSG, buf); cl_engine_free(engine); } snprintf(buf, 1024, "clamav engine loaded with signatureNum %d from %s", signatureNum, signatureDir); LogComponentErr(INFORMATION_LEVEL, ER_LOG_PRINTF_MSG, buf); signature_status = signatureNum; return signatureNum; } int register_status_variables() { if (mysql_service_status_variable_registration->register_variable( (SHOW_VAR *)&viruscan_status_variables)) { LogComponentErr(ERROR_LEVEL, ER_LOG_PRINTF_MSG, "Failed to register status variable"); return 1; } LogComponentErr(INFORMATION_LEVEL, ER_LOG_PRINTF_MSG, "Status variable(s) registered"); return 0; } int unregister_status_variables() { if (mysql_service_status_variable_registration->unregister_variable( (SHOW_VAR *)&viruscan_status_variables)) { LogComponentErr(ERROR_LEVEL, ER_LOG_PRINTF_MSG, "Failed to unregister status variable"); return 1; } LogComponentErr(INFORMATION_LEVEL, ER_LOG_PRINTF_MSG, "Status variable(s) unregistered"); return 0; } namespace udf_impl { struct scan_result scan_data(const char *data, size_t data_size) { struct scan_result result = {0, "", 0}; cl_fmap_t *map; map = cl_fmap_open_memory(data, data_size); /* scan file descriptor */ static struct cl_scan_options cl_scan_options; memset(&cl_scan_options, 0, sizeof(struct cl_scan_options)); cl_scan_options.parse |= ~0; /* enable all parsers */ cl_scan_options.general |= CL_SCAN_GENERAL_ALLMATCHES; result.return_code = cl_scanmap_callback(map, NULL, &result.virus_name, &result.scanned, engine, &cl_scan_options, NULL); cl_fmap_close(map); return result; } bool have_virus_scan_privilege(void *opaque_thd) { // get the security context of the thread Security_context_handle ctx = nullptr; if (mysql_service_mysql_thd_security_context->get(opaque_thd, &ctx) || !ctx) { LogComponentErr(ERROR_LEVEL, ER_LOG_PRINTF_MSG, "problem trying to get security context"); return false; } if (mysql_service_global_grants_check->has_global_grant( ctx, SCAN_PRIVILEGE_NAME, strlen(SCAN_PRIVILEGE_NAME))) return true; return false; } const char *udf_init = "udf_init", *my_udf = "my_udf", *my_udf_clear = "my_clear", *my_udf_add = "my_udf_add"; static bool viruscan_udf_init(UDF_INIT *initid, UDF_ARGS *, char *) { const char* name = "utf8mb4"; char *value = const_cast(name); initid->ptr = const_cast(udf_init); if (mysql_service_mysql_udf_metadata->result_set( initid, "charset", const_cast(value))) { LogComponentErr(ERROR_LEVEL, ER_LOG_PRINTF_MSG, "failed to set result charset"); return false; } return 0; } static void viruscan_udf_deinit(__attribute__((unused)) UDF_INIT *initid) { assert(initid->ptr == udf_init || initid->ptr == my_udf); } const char *viruscan_udf(UDF_INIT *, UDF_ARGS *args, char *outp, unsigned long *length, char *is_null, char *error) { MYSQL_THD thd; mysql_service_mysql_current_thread_reader->get(&thd); struct scan_result result; char buf[1024]; if(!have_virus_scan_privilege(thd)) { mysql_error_service_printf( ER_SPECIFIC_ACCESS_DENIED_ERROR, 0, SCAN_PRIVILEGE_NAME); *error = 1; *is_null = 1; return 0; } result = scan_data(args->args[0], args->lengths[0]); if (result.return_code == 0) { strncpy(outp, "clean: no virus found", *length); } else { strncpy(outp, result.virus_name, *length); snprintf(buf, 1024, "Virus found: %s !!", result.virus_name); LogComponentErr(ERROR_LEVEL, ER_LOG_PRINTF_MSG, buf); virusfound_status++; } *length = strlen(outp); return const_cast(outp); } static bool virusreload_udf_init(UDF_INIT *initid, UDF_ARGS *, char *) { const char* name = "utf8mb4"; char *value = const_cast(name); initid->ptr = const_cast(udf_init); if (mysql_service_mysql_udf_metadata->result_set( initid, "charset", const_cast(value))) { LogComponentErr(ERROR_LEVEL, ER_LOG_PRINTF_MSG, "failed to set result charset"); return false; } return 0; } static void virusreload_udf_deinit(__attribute__((unused)) UDF_INIT *initid) { assert(initid->ptr == udf_init || initid->ptr == my_udf); } const char *virusreload_udf(UDF_INIT *, UDF_ARGS *args, char *outp, unsigned long *length, char *is_null, char *error) { MYSQL_THD thd; mysql_service_mysql_current_thread_reader->get(&thd); unsigned int signatureNum = 0; if(!have_virus_scan_privilege(thd)) { mysql_error_service_printf( ER_SPECIFIC_ACCESS_DENIED_ERROR, 0, SCAN_PRIVILEGE_NAME); *error = 1; *is_null = 1; return 0; } if (args->arg_count > 0) { snprintf(outp, *length, "ERROR: this function doesn't require any parameter !"); *length = strlen(outp); return const_cast(outp); } snprintf(outp, *length, "No need to reload ClamAV engine"); if(cl_statchkdir(&signatureStat) == SIGNATURE_CHANGE) { signatureNum = reload_engine(); cl_statfree(&signatureStat); cl_statinidir(cl_retdbdir(), &signatureStat); snprintf(outp, *length, "ClamAV engine reloaded with new virus database: %d signatures", signatureNum); } *length = strlen(outp); return const_cast(outp); } } /* namespace udf_impl */ static mysql_service_status_t viruscan_service_init() { mysql_service_status_t result = 0; log_bi = mysql_service_log_builtins; log_bs = mysql_service_log_builtins_string; LogComponentErr(INFORMATION_LEVEL, ER_LOG_PRINTF_MSG, "initializing…"); register_status_variables(); int rv; rv = cl_init(CL_INIT_DEFAULT); char buf[1024]; if (CL_SUCCESS != rv) { snprintf(buf, 1024, "can't initialize libclamav: %s", cl_strerror(rv)); LogComponentErr(ERROR_LEVEL, ER_LOG_PRINTF_MSG, buf); } else { // Print the version of ClamAV engine strncpy(clamav_version, cl_retver(), sizeof(clamav_version)-1); snprintf(buf, 1024, "ClamAV %s intialized", clamav_version); LogComponentErr(INFORMATION_LEVEL, ER_LOG_PRINTF_MSG, buf); } struct cl_engine *cl_engine_new(void); reload_engine(); // Registration of the privilege if (mysql_service_dynamic_privilege_register->register_privilege(SCAN_PRIVILEGE_NAME, strlen(SCAN_PRIVILEGE_NAME))) { LogComponentErr(ERROR_LEVEL, ER_LOG_PRINTF_MSG, "could not register privilege 'VIRUS_SCAN'."); result = 1; } else { LogComponentErr(INFORMATION_LEVEL, ER_LOG_PRINTF_MSG, "new privilege 'VIRUS_SCAN' has been registered successfully."); } list = new udf_list(); if (list->add_scalar("virus_scan", Item_result::STRING_RESULT, (Udf_func_any)udf_impl::viruscan_udf, udf_impl::viruscan_udf_init, udf_impl::viruscan_udf_deinit)) { delete list; return 1; /* failure: one of the UDF registrations failed */ } if (list->add_scalar("virus_reload_engine", Item_result::STRING_RESULT, (Udf_func_any)udf_impl::virusreload_udf, udf_impl::virusreload_udf_init, udf_impl::virusreload_udf_deinit)) { delete list; return 1; /* failure: one of the UDF registrations failed */ } return result; } static mysql_service_status_t viruscan_service_deinit() { mysql_service_status_t result = 0; cl_engine_free(engine); unregister_status_variables(); if (mysql_service_dynamic_privilege_register->unregister_privilege(SCAN_PRIVILEGE_NAME, strlen(SCAN_PRIVILEGE_NAME))) { LogComponentErr(ERROR_LEVEL, ER_LOG_PRINTF_MSG, "could not unregister privilege 'VIRUS_SCAN'."); result = 1; } else { LogComponentErr(INFORMATION_LEVEL, ER_LOG_PRINTF_MSG, "privilege 'VIRUS_SCAN' has been unregistered successfully."); } if (list->unregister()) return 1; /* failure: some UDFs still in use */ delete list; LogComponentErr(INFORMATION_LEVEL, ER_LOG_PRINTF_MSG, "uninstalled."); return result; } BEGIN_COMPONENT_PROVIDES(viruscan_service) END_COMPONENT_PROVIDES(); BEGIN_COMPONENT_REQUIRES(viruscan_service) REQUIRES_SERVICE(log_builtins), REQUIRES_SERVICE(log_builtins_string), REQUIRES_SERVICE(dynamic_privilege_register), REQUIRES_SERVICE(mysql_udf_metadata), REQUIRES_SERVICE(udf_registration), REQUIRES_SERVICE(mysql_thd_security_context), REQUIRES_SERVICE(global_grants_check), REQUIRES_SERVICE(mysql_current_thread_reader), REQUIRES_SERVICE(mysql_runtime_error), REQUIRES_SERVICE(status_variable_registration), END_COMPONENT_REQUIRES(); /* A list of metadata to describe the Component. */ BEGIN_COMPONENT_METADATA(viruscan_service) METADATA("mysql.author", "Oracle Corporation"), METADATA("mysql.license", "GPL"), METADATA("mysql.dev", "lefred"), END_COMPONENT_METADATA(); /* Declaration of the Component. */ DECLARE_COMPONENT(viruscan_service, "mysql:viruscan_service") viruscan_service_init, viruscan_service_deinit END_DECLARE_COMPONENT(); /* Defines list of Components contained in this library. Note that for now we assume that library will have exactly one Component. */ DECLARE_LIBRARY_COMPONENTS &COMPONENT_REF(viruscan_service) END_DECLARE_LIBRARY_COMPONENTS view raw scan.cc hosted with ❤ by GitHub Then on line 46, 47 and 48, we create 3 variables that will contain the value of the new status variables. We also create an array containing the information about the status variables, like their name, their type and their scope. Then we use that array to register our status variables on line 160 (and we unregister on line 170). Finally, we assign values to the variables as we need (lines 156, 266 and 347). You can also notice that I changed all sprintf() and strcpy() to use functions limiting the size to avoid overflow: snprintf()and strncpy(). Let’s test our freshly new created status variables: Great ! Today we learned how to created status variables using the MySQL Component Infrastructure. In the next article, we will see how to add a Performance_Schema table for our component. I hope you enjoy this series and as usual, happy coding ! [Less]

A year ago, I blogged about An Unprivileged User can crash your MySQL Server.  At the time, I explained how to protect yourself against this problem.  A few weeks ago, I revisited this vulnerability in a follow-up post in which I explained the fix ... [More] , claimed that the MySQL 5.7 default configuration for Group Replication is still problematic, and explained a tuning to avoid the vulnerability.  In [Less]