openhub.net
Black Duck Software, Inc.
News
Analyzed
1 day
ago.
based on code collected
1 day
ago.
|
Posted
over 2 years
ago
by
Oracle MySQL Group
The MySQL January 2022 releases are now available.
|
|
Posted
over 2 years
ago
by
MySQL Performance Blog
The most common issue when using row-based replication (RBR) is replication lag due to the lack of Primary keys.
The problem is that any replicated DML will do a full table scan for each modified row on the replica. This bug report explains it more
... [More]
in-depth: https://bugs.mysql.com/bug.php?id=53375
For example, if a delete is executed on the following table definition:CREATE TABLE `joinit` (
`i` int NOT NULL,
`s` varchar(64) DEFAULT NULL,
`t` time NOT NULL,
`g` int NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
With this amount of rows:mysql> select count(*) from joinit;
+----------+
| count(*) |
+----------+
| 1048576 |
+----------+
The delete being:mysql> flush status ;
mysql> delete from joinit where i > 5 and i < 150;
Query OK, 88 rows affected (0.04 sec)
mysql> show status like '%handler%';
+----------------------------+---------+
| Variable_name | Value |
+----------------------------+---------+
| Handler_commit | 2 |
| Handler_delete | 1 |
…
| Handler_read_rnd_next | 1048577 |
…It can be seen that the delete on the Primary requires a full table scan (Handler_read_rnd_next matches row amount + 1) to delete 88 rows.
The additional problem is that each of the rows being deleted will be recorded in the binary log individually like this:#220112 18:29:05 server id 1 end_log_pos 3248339 CRC32 0xdd9d1cb2 Delete_rows: table id 106 flags: STMT_END_F
### DELETE FROM `test2`.`joinit`
### WHERE
### @1=6
### @2='764d302b-73d5-11ec-afc8-00163ef3b519'
### @3='18:28:39'
### @4=27
### DELETE FROM `test2`.`joinit`
### WHERE
### @1=7
### @2='764d30bc-73d5-11ec-afc8-00163ef3b519'
### @3='18:28:39'
### @4=5
…
{88 items}Which will result in 88 full table scans on the replica, and hence the performance degradation.
For these cases, the recommendation is to add a primary key to the table, but sometimes adding a PK might not be easy because:
There are no existing columns that could be considered a PK.
Or adding a new column (as the PK) is not possible as it might impact queries from a 3rd party tool that we have no control over (or too complex to fix with query rewrite plugin).
The solution is to use MySQL/Percona Server for MySQL 8 and add an invisible column!
Adding a new column (named “newc”) invisible as a primary key can be done with the following line:ALTER TABLE joinit ADD COLUMN newc INT UNSIGNED NOT NULL AUTO_INCREMENT INVISIBLE PRIMARY KEY FIRST;Note, adding a PK is an expensive operation that requires a table rebuild as shown here.
After adding an invisible PK, the table will look like this:CREATE TABLE `joinit` (
`newc` int NOT NULL AUTO_INCREMENT /*!80023 INVISIBLE */,
`i` int NOT NULL,
`s` varchar(64) DEFAULT NULL,
`t` time NOT NULL,
`g` int NOT NULL,
PRIMARY KEY (`newc`)
) ENGINE=InnoDB AUTO_INCREMENT=1048576 DEFAULT CHARSET=latin1
Deleting a row now will be recorded in the binary log like this:### DELETE FROM `test`.`joinit`
### WHERE
### @1=1048577
### @2=1
### @3='string'
### @4='17:23:04'
### @5=5
# at 430
#220112 17:24:56 server id 1 end_log_pos 461 CRC32 0x826f3af6 Xid = 71
COMMIT/*!*/;Where @1 is the first column ( the PK in this case) which the replica can use to find the matching row without having to do a full table scan.
The operation executed on the replica would be similar to the following which requires only one scan to find the matching row:mysql> flush status ;
Query OK, 0 rows affected (0.01 sec)
mysql> delete from joinit where newc = 1048578;
Query OK, 1 row affected (0.00 sec)
mysql> show status like '%handler%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Handler_commit | 2 |
…
| Handler_read_key | 1 |
…
| Handler_read_rnd_next | 0 |
…
Also as the name suggests, an invisible column won’t show nor it needs to be referenced when doing operations over the table, i.e:mysql> select * from joinit limit 2;
+---+--------------------------------------+----------+----+
| i | s | t | g |
+---+--------------------------------------+----------+----+
| 2 | ecc6cbed-73c9-11ec-afc8-00163ef3b519 | 17:06:03 | 58 |
| 3 | ecc7d9bb-73c9-11ec-afc8-00163ef3b519 | 17:06:03 | 56 |
+---+--------------------------------------+----------+----+
2 rows in set (0.00 sec)
mysql> insert into joinit values (4, "string", now(), 5);
Query OK, 1 row affected (0.01 sec)
But if needed, the new column (newc) can be fetched if explicitly queried:mysql> select newc, i, s, t, g from joinit limit 2;
+------+---+--------------------------------------+----------+----+
| newc | i | s | t | g |
+------+---+--------------------------------------+----------+----+
| 1 | 2 | ecc6cbed-73c9-11ec-afc8-00163ef3b519 | 17:06:03 | 58 |
| 2 | 3 | ecc7d9bb-73c9-11ec-afc8-00163ef3b519 | 17:06:03 | 56 |
+------+---+--------------------------------------+----------+----+
2 rows in set (0.00 sec)
What If…?
What if MySQL automatically detects that the PK is missing for InnoDB tables and adds the invisible PK?
Taking into account that an internal six bytes PK is already added when the PK is missing, it might be a good idea to allow the possibility of making the PK visible if you need to.
This means that when you execute this CREATE TABLE statement:CREATE TABLE `joinit` (
`i` int NOT NULL,
`s` varchar(64) DEFAULT NULL,
`t` time NOT NULL,
`g` int NOT NULL,
PRIMARY KEY (`newc`)
) ENGINE=InnoDB AUTO_INCREMENT=1048576 DEFAULT CHARSET=latin1
Will be automatically translated to:CREATE TABLE `joinit` (
`newc` int NOT NULL AUTO_INCREMENT /*!80023 INVISIBLE */,
`i` int NOT NULL,
`s` varchar(64) DEFAULT NULL,
`t` time NOT NULL,
`g` int NOT NULL,
PRIMARY KEY (`newc`)
) ENGINE=InnoDB AUTO_INCREMENT=1048576 DEFAULT CHARSET=latin1
And then we can execute this command:ALTER TABLE joint ALTER COLUMN newc SET VISIBLE;To make it visible.
Conclusion
Missing primary keys is a problem for scaling databases, as replication will require a full table scan for each updated/delete row, and the more data the more lag.
Adding a PK might not be always possible because of 3rd party tools or restrictions, but adding an invisible primary key will do the trick and have the benefits of adding a PK without impacting syntax and operations from 3rd party clients/tools. What will be awesome is to make MySQL able to detect the missing PK, add it automatically, and change it to visible if you need to. [Less]
|
|
Posted
over 2 years
ago
by
Frederic Descamps
This post is the nine one of a series of articles on extending MySQL with the Component Infrastructure, the list above will be updated as new articles are published:
Extending MySQL using the Component Infrastructure – part 1Extending MySQL using
... [More]
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 functionIf you have use the Component we created during part 8, you may have noticed that on the error log (not the one in performance_schema), there was the following message if your virus database was not up to date:
Once the Component and the ClamAV engine are loaded, even if the virus database is updated (freshclam), the virus database used will still be the previous one. We then need to call again reload_engine().
We will create now a new user function that we can use to reload the ClamAV engine after having upgraded the virus database. We could also detect this automatically using cl_statchkdir() every time we scan data, but I find such method too expansive.
I prefer that the System Engineer in charge of upgrading the Virus Database will also reload the engine in MySQL.
So let start by creating a new user function called virus_reload_engine() on line 335:
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);
SERVICE_TYPE(log_builtins) * log_bi;
SERVICE_TYPE(log_builtins_string) * log_bs;
static const char *SCAN_PRIVILEGE_NAME = "VIRUS_SCAN";
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)
{
sprintf(buf, "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)
{
sprintf(buf, "cannot create clamav engine: %s", cl_strerror(rv));
LogComponentErr(ERROR_LEVEL, ER_LOG_PRINTF_MSG, buf);
cl_engine_free(engine);
}
sprintf(buf, "clamav engine loaded with signatureNum %d from %s", signatureNum, signatureDir);
LogComponentErr(INFORMATION_LEVEL, ER_LOG_PRINTF_MSG, buf);
return signatureNum;
}
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) {
strcpy(outp, "clean: no virus found");
} else {
strcpy(outp, result.virus_name);
sprintf(buf, "Virus found: %s !!", result.virus_name);
LogComponentErr(ERROR_LEVEL, ER_LOG_PRINTF_MSG, buf);
}
*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) {
sprintf(outp, "ERROR: this function doesn't require any parameter !");
*length = strlen(outp);
return const_cast(outp);
}
sprintf(outp, "No need to reload ClamAV engine");
if(cl_statchkdir(&signatureStat) == SIGNATURE_CHANGE) {
signatureNum = reload_engine();
cl_statfree(&signatureStat);
cl_statinidir(cl_retdbdir(), &signatureStat);
sprintf(outp, "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…");
int rv;
rv = cl_init(CL_INIT_DEFAULT);
char buf[1024];
if (CL_SUCCESS != rv) {
sprintf(buf, "can't initialize libclamav: %s", cl_strerror(rv));
LogComponentErr(ERROR_LEVEL, ER_LOG_PRINTF_MSG, buf);
} else {
// Print the version of ClamAV engine
sprintf(buf, "ClamAV %s intialized", cl_retver());
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);
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),
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
This new function calls udf_impl::virusreload_udf defined on line 253 (the init and deinit are also defined on lines 236 and 249). We already the details about this in part 6.
Let’s try our new feature after having compiled our new component and run mtr:
Perfect, the engine is indeed reloaded only if new signatures are present on the system.
We can also see the messages we created in error log:
Our component is improving and is already perfectly usable. On the next article we will learn how to add status variables.
Stay tuned and as usual enjoy MySQL and enjoy coding ! [Less]
|
|
Posted
over 2 years
ago
by
Chandan Kumar
Let's walk through on how to migrate sakila database from Azure for MySQL into Oracle Cloud MySQL Database Service(MDS). For the migration ,let's use MySQL Shell Utility features 1. Dump Schema Utility a. This will help us to export the
... [More]
schema of Azure for MySQL Instance into Oracle Cloud Object Storage. 2. Load Dump Utility a. This is help us to Import the schema from Object Storage to MySQL Database Service(MDS) at Oracle Cloud . More info:- https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities-dump-instance-schema.html How does Migration Work ? Suppose you wanted to do lift-shift of database called “Sakila” , so “Schema Dump” utility will export sakila database from sources(in this example ,source is AZURE Cloud) to OCI(oracle cloud infrastructure) object storage. Then “Load Dump” utility will import directly to MySQL Database Service running in Oracle Cloud as which is managed service of MySQL. Below diagram I have made for clear understanding…What do we needed handy ? 1. MySQL Shell 8.0.x Version . 2. Azure for MySQL Up and Running. 3. MySQL database service(MDS) Up and Running. 4. Install OCI CLI on Azure VM Machine. 5. Install OCI CLI on OCI(Oracle Cloud Compute Instance) 6. local_infile variables must be ON for destination machine. How to Launch MySQL database service (MDS) ? Below is my another blog will help us to create MDS instance:- https://mysqlsolutionsarchitect.blogspot.com/ Additional Details Azure VM details Azure for MySQL Details Oracle Cloud Compute Instance Details MDS Details Public IP address:- 20.198.96.xx SSH PPK file User: azureuser Database Name:- Sakila End point IP:- azuremysql-dbs.mysql.database.azure.com User:- admin123 Port: 3306 No of Tables: 23 Public IP Address: 140.238.227.xx SSH ppk file User:- opc MDS Private IP Address:- 10.0.0.xx MySQL Username:- admin MySQL Port: 3306 First Install OCI CLI in Azure compute instance bash -c "$(curl -L https://raw.githubusercontent.com/oracle/oci-cli/master/scripts/install/install.sh)" exec -l $SHELL cd /root/bin/ oci setup config >>follow the steps cat /root/.oci/oci_api_key_public.pem Paste the o/p of public key into OCI console->user settingsà paste Command to Export the backup from Azure for MySQL to OCI Object Storage Open MySQL Shell and connect to Azure compute instance(VM) then execute below commands Connect azure for mysql Instance mysqlsh -hazuremysql-dbs.mysql.database.azure.com -uadmin123@azuremysql-dbs -pWelcome1! [root@chandan-vm oltp_legacy]# mysqlsh -hazuremysql-dbs.mysql.database.azure.com -uadmin123@azuremysql-dbs -pWelcome1! MySQL Shell 8.0.23 Copyright (c) 2016, 2021, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type '\help' or '\?' for help; '\quit' to exit. WARNING: Using a password on the command line interface can be insecure. Creating a session to 'admin123%[email protected]' Fetching schema names for autocompletion... Press ^C to stop. Your MySQL connection id is 64630 Server version: 8.0.15 Source distribution No default schema selected; type \use to set one. MySQL azuremysql-dbs.mysql.database.azure.com:3306 ssl JS > Check for Compatibility issues and gather the problems in advance before migration by using DRYRUN MySQL azuremysql-dbs.mysql.database.azure.com:3306 ssl JS > util.dumpSchemas(["sakila"], "sakilaDBdump", {dryRun: true, ocimds: true}) MySQL azuremysql-dbs.mysql.database.azure.com:3306 ssl JS > util.dumpSchemas(["sakila"], "sakilaDBdump", {dryRun: true, ocimds: true}) Acquiring global read lock Global read lock acquired Gathering information - done All transactions have been started Locking instance for backup NOTE: Backup lock is not supported in MySQL 5.7 and DDL changes will not be blocked. The dump may fail with an error or not be completely consistent if schema changes are made while dumping. Global read lock has been released Checking for compatibility with MySQL Database Service 8.0.23 NOTE: MySQL Server 5.7 detected, please consider upgrading to 8.0 first. You can check for potential upgrade issues using util.checkForServerUpgrade(). ERROR: Procedure sakila.film_not_in_stock - definition uses DEFINER clause set to user `admin123`@`%` which can only be executed by this user or a user with SET_USER_ID or SUPER privileges (fix this with 'strip_definers' compatibility option) ERROR: Procedure sakila.rewards_report - definition uses DEFINER clause set to user `admin123`@`%` which can only be executed by this user or a user with SET_USER_ID or SUPER privileges (fix this with 'strip_definers' compatibility option) ERROR: Procedure sakila.film_in_stock - definition uses DEFINER clause set to user `admin123`@`%` which can only be executed by this user or a user with SET_USER_ID or SUPER privileges (fix this with 'strip_definers' compatibility option) ERROR: View sakila.nicer_but_slower_film_list - definition uses DEFINER clause set to user `admin123`@`%` which can only be executed by this user or a user with SET_USER_ID or SUPER privileges (fix this with 'strip_definers' compatibility option) ERROR: View sakila.sales_by_film_category - definition uses DEFINER clause set to user `admin123`@`%` which can only be executed by this user or a user with SET_USER_ID or SUPER privileges (fix this with 'strip_definers' compatibility option) ERROR: View sakila.actor_info - definition uses DEFINER clause set to user `admin123`@`%` which can only be executed by this user or a user with SET_USER_ID or SUPER privileges (fix this with 'strip_definers' compatibility option) ERROR: View sakila.film_list - definition uses DEFINER clause set to user `admin123`@`%` which can only be executed by this user or a user with SET_USER_ID or SUPER privileges (fix this with 'strip_definers' compatibility option) ERROR: View sakila.sales_by_store - definition uses DEFINER clause set to user `admin123`@`%` which can only be executed by this user or a user with SET_USER_ID or SUPER privileges (fix this with 'strip_definers' compatibility option) ERROR: View sakila.staff_list - definition uses DEFINER clause set to user `admin123`@`%` which can only be executed by this user or a user with SET_USER_ID or SUPER privileges (fix this with 'strip_definers' compatibility option) ERROR: View sakila.customer_list - definition uses DEFINER clause set to user `admin123`@`%` which can only be executed by this user or a user with SET_USER_ID or SUPER privileges (fix this with 'strip_definers' compatibility option) Compatibility issues with MySQL Database Service 8.0.23 were found. Please use the 'compatibility' option to apply compatibility adaptations to the dumped DDL. Util.dumpSchemas: Compatibility issues were found (RuntimeError) util.dumpSchemas(["sakila"], "sakilaDBdump", {dryRun: true, ocimds: true, "compatibility": ["strip_definers", "strip_restricted_grants"]}) FIX:- MySQL azuremysql-dbs.mysql.database.azure.com:3306 ssl JS > util.dumpSchemas(["sakila"], "sakilaDBdump", {dryRun: true, ocimds: true, "compatibility": ["strip_definers", "strip_restricted_grants"]}) Acquiring global read lock Global read lock acquired Gathering information - done All transactions have been started Locking instance for backup NOTE: Backup lock is not supported in MySQL 5.7 and DDL changes will not be blocked. The dump may fail with an error or not be completely consistent if schema changes are made while dumping. Global read lock has been released Checking for compatibility with MySQL Database Service 8.0.23 NOTE: MySQL Server 5.7 detected, please consider upgrading to 8.0 first. You can check for potential upgrade issues using util.checkForServerUpgrade(). Writing DDL for table `sakila`.`inventory` Writing DDL for table `sakila`.`rental` Writing DDL for table `sakila`.`category` Writing DDL for table `sakila`.`address` Writing DDL for table `sakila`.`city` MySQL azuremysql-dbs.mysql.database.azure.com:3306 ssl JS > MySQL azuremysql-dbs.mysql.database.azure.com:3306 ssl JS >util.dumpSchemas(["sakila"],"sakilaDBdump",{threads:30,"osBucketName": "chandanBucket", "osNamespace": "idazzjlcjqzj", "ocimds": "true","ociConfigFile":"/root/.oci/config", "compatibility": ["strip_definers", "strip_restricted_grants"]}) Output MySQL azuremysql-dbs.mysql.database.azure.com:3306 ssl JS > util.dumpSchemas(["sakila"],"sakilaDBdump",{threads:30,"osBucketName": "chandanBucket", "osNamespace": "idazzjlcjqzj", "ocimds": "true","ociConfigFile":"/root/.oci/config", "compatibility": ["strip_definers", "strip_restricted_grants"]}) Acquiring global read lock Global read lock acquired Gathering information - done All transactions have been started Locking instance for backup Data dump for table `sakila`.`payment` will be written to 1 file Data dump for table `sakila`.`film_text` will be written to 1 file Data dump for table `sakila`.`film_actor` will be written to 1 file Data dump for table `sakila`.`rental` will be written to 1 file Data dump for table `sakila`.`inventory` will be written to 1 file Data dump for table `sakila`.`category` will be written to 1 file Data dump for table `sakila`.`address` will be written to 1 file Data dump for table `sakila`.`city` will be written to 1 file 1 thds dumping - 100% (46.27K rows / ~46.27K rows), 1.61K rows/s, 96.26 KB/s uncompressed, 0.00 B/s compressed Duration: 00:00:01s Schemas dumped: 1 Tables dumped: 16 Uncompressed data size: 2.92 MB Compressed data size: 557.10 KB Compression ratio: 5.2 Rows written: 46273 Bytes written: 557.10 KB Average uncompressed throughput: 1.87 MB/s Average compressed throughput: 356.39 KB/s MySQL azuremysql-dbs.mysql.database.azure.com:3306 ssl JS > You may cross check whether DUMP has been exported to Oracle Cloud Object Storage or not Import Dump file into MySQL Database Service (MDS), from OCI Object Storage First is to install OCI CLI by following below web linkshttps://docs.oracle.com/en-us/iaas/Content/API/SDKDocs/cliinstall.htm For import into a MySQL DB Service (MDS), the MySQL Shell instance where you run the dump loading utility must be installed on an Oracle Cloud Infrastructure Compute instance that has access to the MySQL DB System.Let’s connect Oracle Cloud Compute Instance ,a nd check whether MDS Instance you are able to connect or not ?How to connect Compute Instance- open Putty and Public IP into it , see below screen shot.How to Deploy MDS Instance ? Below is my another blog which will help us to get through.https://mysqlsolutionsarchitect.blogspot.com/2020/09/how-to-launch-mysql-database-service.htmlLet’s Connect MDS instance , sudo su root mysql -h10.0.1.3 -uadmin –pXXXXX mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.00 sec) Connect MySQL Shell to Import # mysqlsh -h10.0.1.3 -uadmin -pWelcome1!DRY RUN scripts:- MySQL 10.0.1.3:33060+ ssl JS >util.loadDump("sakilaDBdump",{dryRun: true, osBucketName: "chandanBucket", osNamespace: "idazzjlcjqzj","ociConfigFile":"/root/.oci/config"}) OUTPUT MySQL 10.0.1.3:33060+ ssl JS > util.loadDump("sakilaDBdump",{dryRun: true, osBucketName: "chandanBucket", osNamespace: "idazzjlcjqzj","ociConfigFile":"/root/.oci/config"}) Loading DDL and Data from OCI ObjectStorage bucket=chandanBucket, prefix='sakilaDBdump' using 4 threads. Opening dump... dryRun enabled, no changes will be made. Target is MySQL 8.0.23-u2-cloud. Dump was produced from MySQL 8.0.15 Fetching dump data from remote location... Fetching 16 table metadata files for schema `sakila`... Checking for pre-existing objects... Executing common preamble SQL Executing DDL script for schema `sakila` [Worker001] Executing DDL script for `sakila`.`city` [Worker003] Executing DDL script for `sakila`.`category` [Worker002] Executing DDL script for `sakila`.`sales_by_store` (placeholder for view) [Worker003] Executing DDL script for `sakila`.`staff_list` (placeholder for view) [Worker000] Executing DDL script for `sakila`.`actor_info` (placeholder for view) Executing common postamble SQL No data loaded. 0 warnings were reported during the load. MySQL 10.0.1.3:33060+ ssl JS > Script to Import DUMP file from Object Storage to MySQL Database System util.loadDump("sakilaDBdump",{osBucketName: "chandanBucket", osNamespace: "idazzjlcjqzj","ociConfigFile":"/root/.oci/config"}) OUTPUT MySQL 10.0.1.3:33060+ ssl JS > util.loadDump("sakilaDBdump",{osBucketName: "chandanBucket", osNamespace: "idazzjlcjqzj","ociConfigFile":"/root/.oci/config"}) Loading DDL and Data from OCI ObjectStorage bucket=chandanBucket, prefix='sakilaDBdump' using 4 threads. Opening dump... Target is MySQL 8.0.23-u2-cloud. Dump was produced from MySQL 8.0.15 Fetching dump data from remote location... Fetching 16 table metadata files for schema `sakila`... [Worker000] sakila@payment@@0.tsv.zst: Records: 16049 Deleted: 0 Skipped: 0 Warnings: 0 Executing common postamble SQL 15 chunks (46.27K rows, 2.92 MB) for 15 tables in 1 schemas were loaded in 2 sec (avg throughput 1.46 MB/s) 0 warnings were reported during the load. MySQL 10.0.1.3:33060+ ssl JS > Finally Import Has been done successfully.Let’s verify The results MySQL 10.0.1.3:33060+ ssl JS > \sql Switching to SQL mode... Commands end with ; MySQL 10.0.1.3:33060+ ssl SQL > show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sakila | | sys | +--------------------+ 5 rows in set (0.0020 sec) MySQL 10.0.1.3:33060+ ssl SQL > use sakila; Default schema set to `sakila`. Fetching table and column names from `sakila` for auto-completion... Press ^C to stop. MySQL 10.0.1.3:33060+ ssl sakila SQL > select count(*) from city; +----------+ | count(*) | +----------+ | 600 | +----------+ 1 row in set (0.0025 sec) MySQL 10.0.1.3:33060+ ssl sakila SQL > show tables; +----------------------------+ | Tables_in_sakila | +----------------------------+ | actor | | actor_info | | address | | category | | city | | country | | customer | | customer_list | | film | | film_actor | | film_category | | film_list | | film_text | | inventory | | language | | nicer_but_slower_film_list | | payment | | rental | | sales_by_film_category | | sales_by_store | | staff | | staff_list | | store | +----------------------------+ 23 rows in set (0.0019 sec) MySQL 10.0.1.3:33060+ ssl sakila SQL > Conclusion:-Migration happened successfully!!!MySQL Shell utility makes MySQL easier to use, by providing an interactive MySQL client supporting SQL, Document Store, JavaScript & Python interface with support for writing custom extensions. MySQL Database Service is a fully managed database service that enables organizations to deploy cloud-native applications using the world's most popular open source database. It is 100% developed, managed and supported by the MySQL Team. [Less]
|
|
Posted
over 2 years
ago
by
Frederic Descamps
This post is the eight one of a series of articles on extending MySQL with the Component Infrastructure, the list above will be updated as new articles are published:
Extending MySQL using the Component Infrastructure – part 1Extending MySQL using
... [More]
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 libraryAs explained in post 1, our component will use libclamav. That’s why we need to link it to our component.
The very first thing, we will include clamav.h in our scan.h (line 34):
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
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 SERVICE_TYPE(log_builtins) * log_bi;
extern SERVICE_TYPE(log_builtins_string) * log_bs;
view raw
scan.h
hosted with ❤ by GitHub
No we also need to use clamav in our code. I added required structures and variables from lines 46 to 63.
A function called to load the ClamAV virus engine, reload_engine() on line 106. and on line 147, a function that scans the data sent as parameter:
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);
SERVICE_TYPE(log_builtins) * log_bi;
SERVICE_TYPE(log_builtins_string) * log_bs;
static const char *SCAN_PRIVILEGE_NAME = "VIRUS_SCAN";
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)
{
sprintf(buf, "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)
{
sprintf(buf, "cannot create clamav engine: %s", cl_strerror(rv));
LogComponentErr(ERROR_LEVEL, ER_LOG_PRINTF_MSG, buf);
cl_engine_free(engine);
}
sprintf(buf, "clamav engine loaded with signatureNum %d from %s", signatureNum, signatureDir);
LogComponentErr(INFORMATION_LEVEL, ER_LOG_PRINTF_MSG, buf);
return signatureNum;
}
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) {
strcpy(outp, "clean: no virus found");
} else {
strcpy(outp, result.virus_name);
sprintf(buf, "Virus found: %s !!", result.virus_name);
LogComponentErr(ERROR_LEVEL, ER_LOG_PRINTF_MSG, buf);
}
*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…");
int rv;
rv = cl_init(CL_INIT_DEFAULT);
char buf[1024];
if (CL_SUCCESS != rv) {
sprintf(buf, "can't initialize libclamav: %s", cl_strerror(rv));
LogComponentErr(ERROR_LEVEL, ER_LOG_PRINTF_MSG, buf);
} else {
// Print the version of ClamAV engine
sprintf(buf, "ClamAV %s intialized", cl_retver());
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 */
}
return result;
}
static mysql_service_status_t viruscan_service_deinit() {
mysql_service_status_t result = 0;
cl_engine_free(engine);
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),
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
The first function that load the engine (reload_engine()) is called when our component is loaded in viruscan_service_init() on line 261.
The second function, scan_data(), is called when we use our function viruscan_udf(), on line 223 and we add in the output buffer (outp) if we found virus or not.
In case we find a virus, we also add a message in error log (line 229).
We also fixed the previous warnings we had during compilation (line 218 and 219).
On line 289, we free the ClamAV engine when the component is uninstalled.
We can compile and test the code:
Oups… we got a undefined_symbol error !
This is because we also nee to tell the compiler to link clamav libraries. We add it to CmakeLists.txt as below:
DISABLE_MISSING_PROFILE_WARNING()
MYSQL_ADD_COMPONENT(viruscan
scan.cc
MODULE_ONLY
TEST_ONLY
LINK_LIBRARIES clamav
)
After the change we can run cmake and make again.
Let’s test it, you will notice that installing the component takes time (+/- 15 sec on my system)… this is because it load the virus database:
The ClamAV engine version is returned on line 256 and displayed on line 257 to error log. This is another example with a more recent version:
Now let’s test the scan of data:
On the next article we will add a second function to our component to be able to reload the Clamav Virus Engine in case it gets updated (you usually refresh your virus database more often than you restart your MySQL Server).
Keep coding and as usual, enjoy MySQL ! [Less]
|
|
Posted
over 2 years
ago
by
MySQL Performance Blog
Recently we published the first part (m5, m5a, m6g) and the second part (C5, C5a, C6g) of research regarding comparing Graviton ARM with AMD and Intel CPU on AWS. We selected general-purpose EC2 instances with the same configurations (amount of vCPU
... [More]
in the first part). In the second part, we compared compute-optimized EC2 instances with the same conditions. The main goal was to see the trend and make a general comparison of CPU types on the AWS platform only for MySQL. We didn’t set the goal to compare the performance of different CPU types. Our expertise is in MySQL performance tuning. We share research “as is” with all scripts, and anyone interested could rerun and reproduce it. All scripts, raw logs and additional plots are available on GitHub:
(arm_cpu_comparison_m5, csv_file_with_all_data_m5,
arm_cpu_comparison_c5, csv_file_with_all_data_c5,
arm_cpu_comparison_m6, csv_file_with_all_data_m6).
We were happy to see the reactions from our Percona Blog readers to our research. And we are open to any feedback. If anyone has any ideas on updating our methodology, we would be happy to correct it.
This post is a continuation of research based on our interest in general-purpose EC2 (and, of course, because we saw that our audience wanted to see it). The main inspiration for this research was the feedback of our readers that we compared different generations of instances, especially old AMD instances (m5a.*), and compared it with the latest Graviton instances (m6g.*). Additionally, we also decided to use the latest Intels instances (m6i.*) too.
Today, we will talk about (AWS) the latest general-purpose EC2: M6i, M6a, M6g (complete list in appendix).
Short Conclusion:
In most cases for m6i, m6g, and m6a instances, Intel shows better performance in throughput for MySQL read transactions. However, AMD instances are pretty close to Intel’s results.
Sometimes Intel could show a significant advantage — more than almost 200k rps (almost 45% better) than Graviton. However, AMD’s gap wasn’t as significant as in previous results. Unfortunately, we compared Graviton with others. So we didn’t concentrate on comparing AMD with Intel.
If we could say in a few words: m6i instances (with Intel) are better in their class than other m6a, m6g instances (in performance for MySql). And this advantage starts from 5%-10% and could be up to 45% compared with other CPUs.
But Gravitons instances are still cheaper
Details, or How We Got Our Short Conclusion:
Disclaimer:
Tests were run on M6i.* (Intel) , M6a.* (AMD), M6g.*(Graviton) EC2 instances in the US-EAST-1 region. (List of EC2 see in the appendix). It was selected using only the same class of instances without additional upgrades. The main goal is to take the same instances with only differences in CPU types and identify their performance for MySQL.
Monitoring was done with Percona Monitoring and Management (PMM).
OS: Ubuntu 20.04 LTS
Load tool (sysbench) and target DB (MySQL) installed on the same EC2 instance.
Oracle MySQL Community Server — 8.0.26-0 — installed from official packages (it was installed from Ubuntu repositories).
Load tool: sysbench — 1.0.18
innodb_buffer_pool_size=80% of available RAM
Test duration is five minutes for each thread and then 90 seconds cool down before the next iteration.
Tests were run four times independently (to smooth outliers / to have more reproducible results). Then results were averaged for graphs. Also, graphs show min and max values that were during the test, which shows the range of variance.
We are going to use the “high-concurrency” scenario definition for scenarios when the number of threads would be bigger than the number of vCPU. And “low-concurrent” scenario definition with scenarios where the number of threads would be less or equal to a number of vCPU on EC2.
We are comparing MySQL behavior on the same class of EC2, not CPU performance.
We got some feedback regarding our methodology, and we would update it in the next iteration, with a different configuration, but for this particular research we leave previous to have possibility compare “apples to apples”.
The post is not sponsored by any external company. It was produced using only Percona resources. We do not control what AWS uses as CPU in their instances, we only operate with what they offer.
Test Case:
Prerequisite:
To use only CPU (without disk and network) we decided to use only read queries from memory. To do this we did the following actions.
1. Create DB with 10 tables with 10 000 000 rows each tablesysbench oltp_read_only --threads=10 --mysql-user=sbtest --mysql-password=sbtest --table-size=10000000 --tables=10 --db-driver=mysql --mysql-db=sbtest prepare2. Load all data to LOAD_buffer sysbench oltp_read_only --time=300 --threads=10 --table-size=1000000 --mysql-user=sbtest --mysql-password=sbtest --db-driver=mysql --mysql-db=sbtest runTest:
3. Run in a loop for same scenario but different concurrency THREAD (1,2,4,8,16,32,64,128) on each EC2 sysbench oltp_read_only --time=300 --threads=${THREAD} --table-size=100000 --mysql-user=sbtest --mysql-password=sbtest --db-driver=mysql --mysql-db=sbtest run
Results:
Result reviewing was split into four parts:
For “small” EC2 with 2, 4, and 8 vCPU
For “medium” EC2 with 16 and 32 vCPU
For “large” EC2 with 48 and 64 vCPU
For all scenarios to see the overall picture.
There would be four graphs for each test:
Throughput (queries per second) that EC2 could perform for each scenario (number of threads)
Latency 95 percentile that EC2 could perform for each scenario, (number of threads)
Relative comparing Graviton and Intel, Graviton, and AMD
Absolute comparing Graviton and Intel, Graviton and AMD
Validation that all load goes to the CPU, not to DISK I/O or network, was done also using PMM (Percona Monitoring and Management).
pic 0.1. OS monitoring during all test stages
Result for EC2 with 2, 4, and 8 vCPU:
Plot 1.1. Throughput (queries per second) for EC2 with 2, 4 and 8 vCPU for scenarios with 1,2,4,8,16,32,64,128 threads
Plot 1.2. Latencies (95 percentile) during the test for EC2 with 2, 4 and 8 vCPU for scenarios with 1,2,4,8,16,32,64,128 threads
Plot 1.3.1 Percentage comparison Graviton and Intel CPU in throughput (queries per second) for EC2 with 2, 4 and 8 vCPU for scenarios with 1,2,4,8,16,32,64,128 threads
Plot 1.3.2 Percentage comparison Graviton and AMD CPU in throughput (queries per second) for EC2 with 2, 4 and 8 vCPU for scenarios with 1,2,4,8,16,32,64,128 threads
Plot 1.4.1. Numbers comparison Graviton and Intel CPU in throughput (queries per second) for EC2 with 2, 4 and 8 vCPU for scenarios with 1,2,4,8,16,32,64,128 threads
Plot 1.4.2. Numbers comparison Graviton and Intel CPU in throughput (queries per second) for EC2 with 2, 4 and 8 vCPU for scenarios with 1,2,4,8,16,32,64,128 threads
OVERVIEW:
Based on plot 1.1. We could say that EC2 with Intel hasn’t an absolute advantage compared with Graviton and AMD.
Especially Intel and AMD, showing an advantage a little bit over – 20% over Graviton.
In numbers, it is over five thousand and more requests per second.
AMD showed better results for two vCPU instances.
And it looks like in M6 class of Gravitons CPUs show the worst result compared with others.
Result for EC2 with 16 and 32 vCPU:
Plot 2.1. Throughput (queries per second) for EC2 with 16 and 32 vCPU for scenarios with 1,2,4,8,16,32,64,128 threads
Plot 2.2. Latencies (95 percentile) during the test for EC2 with 16 and 32 vCPU for scenarios with 1,2 4,8,16,32,64,128 threads
Plot 2.3.1 Percentage comparison Graviton and Intel CPU in throughput (queries per second) for EC2 with 16 and 32 vCPU for scenarios with 1,2,4,8,16,32,64,128 threads
Plot 2.3.2 Percentage comparison Graviton and AMD CPU in throughput (queries per second) for EC2 with 16 and 32 vCPU for scenarios with 1,2,4,8,16,32,64,128 threads
Plot 2.4.1. Numbers comparison Graviton and Intel CPU in throughput (queries per second) for EC2 with 16 and 32 vCPU for scenarios with 1,2,4,8,16,32,64,128 threads
Plot 2.4.2. Numbers comparison Graviton and AMD CPU in throughput (queries per second) for EC2 with 16 and 32 vCPU for scenarios with 1,2,4,8,16,32,64,128 threads
OVERVIEW:
Plot 2.1 shows that Intel vCPU is more performance efficient. AMD is the second, and Graviton is the third.
According to plots 2.3.1 and 2.3.2, Intel is better than Graviton up to 30 % and AMD is better than Graviton up to 20%. Graviton has some exceptional performance advantage over AMD in some scenarios. But with this configuration and this instance classes, it is an exception according to the plot 2.3.2 scenarios for 8 and 16 concurrent threads.
In real numbers, Intel could execute up to 140 k read transactions more than Graviton CPUs, and AMD could read more than 70 k read transactions than Graviton. (plot 2.1. , plot 2.4.1.)
In most cases, AMD and Intel are better than Graviton EC2 instances (plot 2.1, plot 2.3.2, plot 2.4.2).
Result for EC2 with 48 and 64 vCPU:
Plot 3.1. Throughput (queries per second) for EC2 with 48 and 64 vCPU for scenarios with 1,2,4,8,16,32,64,128 threads
Plot 3.2. Latencies (95 percentile) during the test for EC2 with 48 and 64 vCPU for scenarios with 1,2 4,8,16,32,64,128 threads
Plot 3.3.1 Percentage comparison Graviton and Intel CPU in throughput (queries per second) for EC2 with 48 and 64 vCPU for scenarios with 1,2,4,8,16,32,64,128 threads
Plot 3.3.2 Percentage comparison Graviton and AMD CPU in throughput (queries per second) for EC2 with 48 and 64 vCPU for scenarios with 1,2,4,8,16,32,64,128 threads
Plot 3.4.1. Numbers comparison Graviton and Intel CPU in throughput (queries per second) for EC2 with 48 and 64 vCPU for scenarios with 1,2,4,8,16,32,64,128 threads
Plot 3.4.2. Numbers comparison Graviton and AMD CPU in throughput (queries per second) for EC2 with 48 and 64 vCPU for scenarios with 1,2,4,8,16,32,64,128 threads
OVERVIEW:
For “Large” instances, Intel is still better than other vCPU. AMD was still in second place, except when Graviton stole some cases. (plot 3.1.)
According to plot 3.3.1. Intel showed an advantage over Graviton up to 45%. On the other hand, AMD showed an advantage over Graviton up to 20% in the same case.
There were two cases when Graviton showed some better results, but it is an exception.
In real numbers: Intel could generate over 150k-200k read transactions more than Graviton. And AMD could execute more than 70k – 130k read transactions than Graviton.
Full Result Overview:
Plot 4.1.1. Throughput (queries per second) – bar plot for EC2 with 2, 4, 8, 16, 32, 48 and 64 vCPU for scenarios with 1,2,4,8,16,32,64,128 threads
Plot 4.1.2. Throughput (queries per second) – line plot for EC2 with 2, 4, 8, 16, 32, 48 and 64 vCPU for scenarios with 1,2,4,8,16,32,64,128 threads
Plot 4.2.1. Latencies (95 percentile) during the test – bar plot for EC2 with 2, 4, 8, 16, 32, 48 and 64 vCPU for scenarios with 1,2,4,8,16,32,64,128 threads
Plot 4.2.2. Latencies (95 percentile) during the test – line plot for EC2 with 2, 4, 8, 16, 32, 48 and 64 vCPU for scenarios with 1,2,4,8,16,32,64,128 threads
Plot 4.3.1 Percentage comparison Graviton and Intel CPU in throughput (queries per second) for EC2 with 2, 4, 8, 16, 32, 48 and 64 vCPU for scenarios with 1,2,4,8,16,32,64,128 threads
Plot 4.3.2 Percentage comparison Graviton and AMD CPU in throughput (queries per second) for EC2 with 2, 4, 8, 16, 32, 48 and 64 vCPU for scenarios with 1,2,4,8,16,32,64,128 threads
Plot 4.4.1. Numbers comparison Graviton and Intel CPU in throughput (queries per second) for EC2 with 2, 4, 8, 16, 32, 48 and 64 vCPU for scenarios with 1,2,4,8,16,32,64,128 threads
Plot 4.4.2. Numbers comparison Graviton and AMD CPU in throughput (queries per second) for EC2 with 2, 4, 8, 16, 32, 48 and 64 vCPU for scenarios with 1,2,4,8,16,32,64,128 threads
Plot 4.5.1. Percentage comparison INTEL and AMD CPU in throughput (queries per second) for EC2 with 2, 4, 8, 16, 32, 48 and 64 vCPU for scenarios with 1,2,4,8,16,32,64,128 threads
Plot 4.5.2. Numbers comparison INTEL and AMD CPU in throughput (queries per second) for EC2 with 2, 4, 8, 16, 32, 48 and 64 vCPU for scenarios with 1,2,4,8,16,32,64,128 threads
Final Thoughts
We compare general-purpose EC2 (m6i, m6a, m6g) instances from the AWS platform and their behavior for MySQL.
In these competitions, Graviton instances (m6g) do not provide any competitive results for MySQL.
There was some strange behavior. AMD and Intel showed their best performance when loaded (in the number of threads) are equal numbers of vCPU. According to plot 4.1.2. We could see some jump in performance when the load becomes the same as the amount of vCPU. This point was hard to see on the bar chart. But this is very interesting. However, Graviton worked more slightly without any “jumps”, and that’s why it showed exceptionally better results in some scenarios with AMD.
Last point. Everyone wants to see an AMD vs Intel comparison. Plot 4.5.1 and 4.5.2. The result – Intel is better in most cases. And AMD was better only in one case with 2 vCPU. So the advantage of Intel compared with AMD could rise up to 96% for “large instances” (in some cases). It is unbelievable. But in most cases, this advantage is that Intel could run in 30% more MySql read transactions than AMD.
It is still an open question regarding the economic efficiency of all this EC2. We would research this topic and answer this question a little bit later.
APPENDIX:
List of EC2 used in research:
CPU type
Cpu info:
Model name
EC2
Memory GB
Amount vCPU
EC2 price per hour (USD)
AMD
AMD EPYC 7R13 Processor 2650 MHz
m6a.large
8
2
$0.0864
AMD
m6a.xlarge
16
4
$0.1728
AMD
m6a.2xlarge
32
8
$0.3456
AMD
m6a.4xlarge
64
16
$0.6912
AMD
m6a.8xlarge
128
32
$1.3824
AMD
m6a.12xlarge
192
48
$2.0736
AMD
m6a.16xlarge
256
64
$2.7648
Graviton
ARMv8 AWS Graviton2 2500 MHz
m6g.large
8
2
$0.077
Graviton
m6g.xlarge
16
4
$0.154
Graviton
m6g.2xlarge
32
8
$0.308
Graviton
m6g.4xlarge
64
16
$0.616
Graviton
m6g.8xlarge
128
32
$1.232
Graviton
m6g.12xlarge
192
48
$1.848
Graviton
m6g.16xlarge
256
64
$2.464
Intel
Intel(R) Xeon(R) Platinum 8375C CPU @ 2.90GHz
m6i.large
8
2
$0.096000
Intel
m6i.xlarge
16
4
$0.192000
Intel
m6i.2xlarge
32
8
$0.384000
Intel
m6i.4xlarge
64
16
$0.768000
Intel
m6i.8xlarge
128
32
$1.536000
Intel
m6i.12xlarge
192
48
$2.304000
Intel
m6i.16xlarge
256
64
$3.072000
my.cnf:
[mysqld]
ssl=0
performance_schema=OFF
skip_log_bin
server_id = 7
# general
table_open_cache = 200000
table_open_cache_instances=64
back_log=3500
max_connections=4000
join_buffer_size=256K
sort_buffer_size=256K
# files
innodb_file_per_table
innodb_log_file_size=2G
innodb_log_files_in_group=2
innodb_open_files=4000
# buffers
innodb_buffer_pool_size=${80%_OF_RAM}
innodb_buffer_pool_instances=8
innodb_page_cleaners=8
innodb_log_buffer_size=64M
default_storage_engine=InnoDB
innodb_flush_log_at_trx_commit = 1
innodb_doublewrite= 1
innodb_flush_method= O_DIRECT
innodb_file_per_table= 1
innodb_io_capacity=2000
innodb_io_capacity_max=4000
innodb_flush_neighbors=0
max_prepared_stmt_count=1000000
bind_address = 0.0.0.0
[client] [Less]
|
|
Posted
over 2 years
ago
by
Joshua Otwell
Guess what? OpenLampTech, the newsletter for PHP/MySQL developers, has a new issue full of great curated content for your reading experience this week. Come on by and read the publication!
Get tailored articles with Refind delivered each day
... [More]
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
This latest newsletter issue has a wide range of PHP/MySQL articles covering:
UNION queries in Laravel WordPress book roundup Explaining the EXPLAIN command in MySQLMySQL triageCodeIgniter 4 YouTube playlistMuch more…Get your WordPress website or web application up and running with a hosting plan over on Hostinger. I use Hostinger hosting for all of my personal web development applications and projects. The pricing tiers are some of the best in the industry and the service is unparallel to any other. Get your hosting now using my referral link. At absolutely no extra cost to you, I will receive a commission should you purchase one of Hostinger’s hosting plans through this link. Thank you!
Visit any (or all) of these OpenLampTech deep-dive featured issues from the archives
OpenLampTech issue #3 – 5 MySQL String Functions You Should Know OpenLampTech issue #7 – MySQL Metadata with CodeIgniter 4Are 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!!!
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 OpenLampTech issue #9 appeared first on Digital Owl's Prose. [Less]
|
|
Posted
over 2 years
ago
by
Joshua Otwell
Guess what? OpenLampTech, the newsletter for PHP/MySQL developers, has a new issue full of great curated content for your reading experience this week. Come on by and read the publication!
Get tailored articles with Refind delivered each day
... [More]
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
This latest newsletter issue has a wide range of PHP/MySQL articles covering:
UNION queries in Laravel WordPress book roundup Explaining the EXPLAIN command in MySQLMySQL triageCodeIgniter 4 YouTube playlistMuch more…Get your WordPress website or web application up and running with a hosting plan over on Hostinger. I use Hostinger hosting for all of my personal web development applications and projects. The pricing tiers are some of the best in the industry and the service is unparallel to any other. Get your hosting now using my referral link. At absolutely no extra cost to you, I will receive a commission should you purchase one of Hostinger’s hosting plans through this link. Thank you!
Visit any (or all) of these OpenLampTech deep-dive featured issues from the archives
OpenLampTech issue #3 – 5 MySQL String Functions You Should Know OpenLampTech issue #7 – MySQL Metadata with CodeIgniter 4Are 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!!!
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.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 OpenLampTech issue #9 appeared first on Digital Owl's Prose. [Less]
|
|
Posted
over 2 years
ago
by
Daniel Nichter
Editors and technical reviewers suggested that I cover how to configure MySQL query metrics in chapter 1 of Efficient MySQL Performance, but I deferred because it was out of scope for the book, which focuses on engineers using MySQL, not DBAs.
As
... [More]
such, there’s only a note in chapter 1 that says: “Ask your DBA or read the MySQL manual.”
But I’ll cover the topic here because that’s what this blog post series is for: behind the book. [Less]
|
|
Posted
over 2 years
ago
by
Frederic Descamps
This post is the seventh post of a series of articles on extending MySQL with the Component Infrastructure, the list above will be updated as new articles are published:
Extending MySQL using the Component Infrastructure – part 1Extending MySQL
... [More]
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 usersIn the previous post, we coded the check for the right privilege to be able to run our function. We were able to log a message in error log but the user didn’t receive any message.
Additionally, logging a message in error log is only recommended when we want an admin to see it, now we want to user to get the message.
This is exactly what we gonna fix in this article.
As usual, the component infrastructure provides a service we can use to also print an error to the user: the MySQL Runtime Error Service included in mysql/components/services/mysql_runtime_error_service.h.
Let’s modify our scan.h to include that file and add the required service place holder:
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
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 SERVICE_TYPE(log_builtins) * log_bi;
extern SERVICE_TYPE(log_builtins_string) * log_bs;
view raw
scan.h
hosted with ❤ by GitHub
This is what we did on lines 29 and 44.
Now we can also update scan.cc to require that service and update our viruscan_udf function to use that service using the right error message (ER_SPECIFIC_ACCESS_DENIED_ERROR) to replace the message in error log on line 132 (you can find those message in include/mysqld_ername.h):
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);
SERVICE_TYPE(log_builtins) * log_bi;
SERVICE_TYPE(log_builtins_string) * log_bs;
static const char *SCAN_PRIVILEGE_NAME = "VIRUS_SCAN";
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;
namespace udf_impl {
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 *initid, UDF_ARGS *args, char *outp,
unsigned long *length, char *is_null, char *error) {
MYSQL_THD thd;
mysql_service_mysql_current_thread_reader->get(&thd);
if(!have_virus_scan_privilege(thd)) {
mysql_error_service_printf(
ER_SPECIFIC_ACCESS_DENIED_ERROR, 0,
SCAN_PRIVILEGE_NAME);
return 0;
}
strcpy(outp, "we do nothing yet");
*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…");
// 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 */
}
return result;
}
static mysql_service_status_t viruscan_service_deinit() {
mysql_service_status_t result = 0;
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),
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
Let’s test it after compiling the new code and running mtr again (see part 2):
Excellent, this is exactly what we were looking for.
Extra
On lines 91 and 92 we get the security context. I would like to show that from that security context we can for example get the user and host:
Security_context_handle ctx = nullptr;
mysql_service_mysql_thd_security_context->get(thd, &ctx);
MYSQL_LEX_CSTRING user;
MYSQL_LEX_CSTRING host;
mysql_service_mysql_security_context_options->get(ctx, "priv_user",
&user);
mysql_service_mysql_security_context_options->get(ctx, "priv_host",
&host);
Conclusion
In this article we saw how we can return messages to the user using the appropriate component service.
The next article will cover how to use libclamav from our component.
Stay tuned, happy coding and enjoy MySQL ! [Less]
|
- Languages
- Cost Estimates
- Security
Code Data
- Commits
- Contributors
SCM Data
- Users
- Ratings & Reviews
- User & Contributor Locations