Facebook  Youtube 
Planet MySQL
Planet MySQL - https://planet.mysql.com

  • Upcoming Webinar 8/22: Advanced Techniques to Profile and Visualize with Flame Graphs
    Please join Percona Principal Support Engineer Marcos Albe as he presents his talk “Flame Graphs 201″ on Thursday, August 22th, 2019 at 11:00 AM PDT (UTC-7). Register Now Visualizing profiling information can be a very powerful tool for performance diagnostics, especially if we zoom right into the problem. Flame graphs were developed for this purpose and we use them on a daily basis at Percona to successfully solve complex performance issues. In this presentation, attendees will learn advanced techniques for profiling with performance and visualizing those profiles with flame graphs, as well as other assorted tricks that the flame graph scripts allow us to do. If you can’t attend, sign up anyways we’ll send you the slides and recording afterward.

  • Comparing Replication Technologies for MySQL Clustering: Part 1
    Overview The Skinny Clustering provides high availability and disaster recovery, along with the ability to read-scale both locally and globally. Some clusters even provide active/active capabilities, which others have a single master. Real time database replication is a must for clustering and other key business purposes, like reporting. There are a number of replication technologies available for MySQL, and some are even bundled into various solutions. When choosing a replication methodology, it is paramount to understand just how the data moves from source to target. In this blog post, we will examine how asynchronous, synchronous, and “semi-synchronous” replication behave when used for clustering. Also, we will explore how replication affects database performance and data availability. Asynchronous Replication Fast and Flexible Asynchronous replication (async for short) is used by MySQL natively as well as by our own Tungsten Replicator. With async, all replication is decoupled from the database. Specifically, replication is a background process that reads events from the binary log on disk and makes those events available to the slaves upon request. There is no change to how the application works, and thus is quick and easy to implement. Also, async does not slow down the response from MySQL to the application. With asynchronous replication, you have: The least impact to application performance of any replication type, because replication is handling database events separately in the background by reading the binary logs. The best (and really the only) choice for WAN replication because the application does not need to wait for confirmation from each slave before proceeding – this delay is prohibitive over long distances due to the simple physics of the speed of light creating delays in transit (Round-Trip-Time or RTT). The ability to deploy databases at geo-scale, serving applications that need high performance globally. Replication is de-coupled from your application, so slow WAN links do not imapct application performance. A chance of data lag on the replication slaves, which means that the slaves are not completely up-to-date with the master. In the event of a failure, there is a chance the newly-promoted slave would not have the same data as the master. The risk of data loss when promoting a slave to master per the above point. There are a number techniques to mitigate data loss which will be discussed in another blog post. Synchronous Replication Slow and Steady Used by Galera and its variants, synchronous replication (sync for short) addresses the above data loss issues by guaranteeing that all transactions are committed on all nodes at database commit. Synchronous replication will wait until all nodes have the transaction committed before providing a response to the application, as opposed to asynchronous replication which occurs in the background after a commit to the master. With the sync method, you can be sure that if you commit a transaction, that transaction will be committed on every node. With synchronous replication, you have: The most significant application lag of any type of replication, because your application must wait for all nodes in the cluster to commit the transaction too. Per above, it is complicated to implement over wide-area or slow networks, due to the almost prohibitive application lag, as it waits for transactions to be committed on remote databases. Transaction commit on all nodes, guaranteed. No slave data lag, as slaves are always up to date by definition. No chance of a data loss window in the event of a failover. Possibility to have multiple masters in a local cluster using a process called “certification”; certification will process transactions in order or, in the event of a conflict, raise an error. Semi-Synchronous Replication The New Guy MySQL group replication introduces “semi-synchronous” in an attempt to merge the advantages of both asynchronous and semi-synchronous replication. With semi-synchronous replication (semi-sync for short), transactions are committed on the master, transferred to at least one slave node but NOT NECESSARILY committed. At this point, control is handed back to the application and commits to the slaves are handled in the background. Compared to synchronous replication, applications can potentially be more responsive since they receive control back sooner (though it is not as fast as async). Also, compared to async, there is less chance of data loss and potentially less replication lag. With semi-synchronous replication, you have: Application response that is faster than synchronous replication, but slower than asynchronous replication The potential for a data loss window that is smaller than with asynchronous replication, but still larger than with synchronous replication. Configuration is more complex than with asynchronous replication, since it is not decoupled from the master database. A relatively new technology which is not yet widely deployed. It remains to be seen if semi-sync will be a viable solution for production workloads. Why does Tungsten Clustering choose Asynchronous Replication? The Wrap-Up Tungsten Clustering uses the Tungsten Replicator, leveraging asynchronous replication, so that complex clusters can be deployed at geo scale without modifying or impacting applications or database servers. When deploying over wide area networks, asynchronous replication is the best and usually only option to protect application performance. Also, even over fast LAN’s, for write-intensive workloads, asynchronous replication is the best choice because the bottom-line impact to the application is minimized. Look out for Part 2 of this blog, that will dive into how different replication technologies impact cluster behavior in day-to-day operations (such as failover, local and global replication breaks, zero downtime, maintenance updates, etc.). For more information about Tungsten clusters, please visit https://docs.continuent.com If you would like to speak with one of our team, please feel free to reach out here: https://www.continuent.com/contact

  • MySQL 8.0 Flow Control in Group Replication
    We are well aware that MySQL Group Replication is one of the faster evolving clustering Technology for MySQL. Flow Control plays a key factor in Group Replication performance and data integrity . In this blog I am going to explain about the Flow Control mechanism and How it has evolved in MySQL 8 ? What is Flow Control ? MySQL Group Replication / Native Async replication needs binary logs to get the data flow across the servers. What makes the difference ? In the MySQL Group Replication we are trying to achieve the Synchronous replication with the help of a Flow Control mechanism and transaction acknowledgments ( certification ). Without Flow Control, the MySQL Group Replication is asynchronous replication ? Yes, consistency is lost. Lets us consider We have three nodes ( GR1, GR2, GR3 ) . Gr1 is the master and and other two servers ( GR2, GR3 ) are the appliers . Suddenly, your master node (GR1) is getting more write loads from application. But, your applier servers are not able to catch-up the concurrent writes ( may be, the applier servers having less configurations OR network bandwidth is low between the nodes ) from master servers . So, our applier servers will keep lagging . In this case , the master server slows down the writes for the slave (GR2 / GR3) appliers to be up to date with master node (GR1) . This mechanism is called Flow Control . MySQL dev official document, Group Replication ensures that a transaction only commits after a majority of the members in a group have received it and agreed on the relative order between all transactions that were sent concurrently. This approach works well if the total number of writes to the group does not exceed the write capacity of any member in the group. If it does and some of the members have less write throughput than others, particularly less than the writer members, those members can start lagging behind of the writers. How Flow Control works ? MySQL group replication flow control mainly depends on two factors .The controlling variables are GR certifier queue size ( group_replication_certifier_threshold ) GR applier queue size ( group_replication_applier_threshold ) Group replication have inbuilt health checks , the collect the stats from the group members . The collected stats will be share to other group members periodically . The monitoring mechanism will calculate the below stats from the group members . certifier queue size replication applier queue size total number of transactions certified total number of remote transactions applied in the member total number of local transactions Based on the metrics collected from all the servers in the group, a throttling mechanism kicks in and decides whether to limit the rate a member is able to execute/commit new transactions. The group capacity will be calculated based on the lowest capacity of all the members in the group . Flow Control RepresentationGroup Replication MySQL 5.7 vs MySQL 8 We all know, the Group Replication is introduced at MySQL 5.7 . But, there is a lot of improvements in MySQL 8 ( Particularly MySQL 8.0.2 ) . Below I have mentioned the important key features, which introduced in MYSQL 8 ( MySQL 8.0.2 ) . Also, I am adding the complete list of variables ( both MySQL 5.7 & MySQL 8 ) which involving in the MySQL Flow control mechanism . Can allow the cluster to cach up the backlog under flow control based on the hold percent ( group_replication_flow_control_hold_percent ) Can define, When the group quota should be released, when flow control is no longer needed to throttle the writer members ( group_replication_flow_control_release_percent ) Can control the lowest/highest flow control quota that can be assigned to a member ( group_replication_flow_control_max_commit_quota, group_replication_flow_control_min_quota ) MySQL 5.7 group_replication_flow_control_applier_threshold group_replication_flow_control_certifier_threshold group_replication_flow_control_mode MySQL 8 group_replication_flow_control_hold_percent group_replication_flow_control_max_commit_quota group_replication_flow_control_member_quota_percent group_replication_flow_control_min_quota group_replication_flow_control_min_recovery_quota group_replication_flow_control_period group_replication_flow_control_release_percent Understanding the Flow Control mechanism and its related variables will be helpful for effective tuning of Group Replication cluster . Hope this blog will helps someone who is learning the MySQL flow control in GR . At Mydbops, We are keep testing the new things on MySQL and related tools, will be coming back with a new blog soon. Photo by Jani Brumat on Unsplash

  • Shinguz: MariaDB and MySQL Character Set Conversion
    Contents Introduction Character Sets Steps to convert Character Set to utf8mb4 Analyzing the Server Analyzing the Application and the Clients Preparation of the Server Settings and the Application Convert Tables to utf8mb4 Testing of new Character Set MySQL Pump MySQL Master/Slave Replication for Character Set conversion MySQL Shell, mysqlsh Upgrade Checker Utility Introduction Recently we had a consulting engagement where we had to help the customer to migrate from latin1 Character Set to utf8mb4 Character Set. In the same MySQL consulting engagement we considered to upgrade from MySQL 5.6 to MySQL 5.7 as well [ Lit. ]. We decided to split the change in 2 parts: Upgrading to 5.7 in the first step and converting to uft8mb4 in the second step. There were various reasons for this decision: 2 smaller changes are easier to control then one big shot. We assume that in 5.7 we experience less problems with utf8mb4 because the trend given by MySQL was more towards utf8mb4 in 5.7 than in MySQL 5.6. So we hope to hit less problems and bugs. For Upgrading see also MariaDB and MySQL Upgrade Problems Remark: It makes possibly also sens to think about Collations before starting with the conversion! Character Sets Historically MariaDB and MySQL had the default Character Set latin1 (Latin-1 or ISO-8859-1) which was sufficient for most of the western hemisphere. But as technology spreads and demands increase other cultures want to have their characters represented understandably as well. So Unicode standard was invented. And MariaDB and MySQL applied this standard as well. The original MariaDB/MySQL utf8(mb3) implementation was not perfect or complete so they implemented utf8mb4 as a super set of utf8(mb3). So at least since MariaDB/MySQL version 5.5 latin1, utf8 and utf8mb4 are available. The current MySQL 5.7 utf8mb4 implementation should cover Unicode 9.0.0: SQL> SELECT * FROM information_schema.character_sets WHERE character_set_name LIKE 'utf8%' OR character_set_name = 'latin1'; +--------------------+----------------------+----------------------+--------+ | CHARACTER_SET_NAME | DEFAULT_COLLATE_NAME | DESCRIPTION | MAXLEN | +--------------------+----------------------+----------------------+--------+ | latin1 | latin1_swedish_ci | cp1252 West European | 1 | | utf8 | utf8_general_ci | UTF-8 Unicode | 3 | | utf8mb4 | utf8mb4_general_ci | UTF-8 Unicode | 4 | +--------------------+----------------------+----------------------+--------+ The default Character Set up to MariaDB 10.4 and MySQL 5.7 was latin1. In MySQL 8.0 the default Character Set has changed to utf8mb4. There are no signs so far that MariaDB will take the same step: SQL> status -------------- mysql Ver 8.0.16 for linux-glibc2.12 on x86_64 (MySQL Community Server - GPL) Connection id: 84 Current database: Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 8.0.16 MySQL Community Server - GPL Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: utf8mb4 Db characterset: utf8mb4 Client characterset: utf8mb4 Conn. characterset: utf8mb4 UNIX socket: /var/run/mysqld/mysql-3332.sock Uptime: 3 days 47 min 13 sec So we see a general trend from latin1 to utf8(mb3) to utf8mb4 technically and business wise (aka globalization). For the DBA this means sooner or later we have to think about a conversion of all tables of the whole database instance (all tables of all schemata) to utf8mb4! Steps to convert Character Set to utf8mb4 Analyzing the Server First of all one should analyze the system (O/S, database instance and client/application). On the server we can run the following command to verify the actual used and supported Character Set: # locale LANG=en_GB.UTF-8 LANGUAGE= LC_CTYPE="en_GB.UTF-8" LC_NUMERIC="en_GB.UTF-8" LC_TIME="en_GB.UTF-8" LC_COLLATE="en_GB.UTF-8" LC_MONETARY="en_GB.UTF-8" LC_MESSAGES="en_GB.UTF-8" LC_PAPER="en_GB.UTF-8" LC_NAME="en_GB.UTF-8" LC_ADDRESS="en_GB.UTF-8" LC_TELEPHONE="en_GB.UTF-8" LC_MEASUREMENT="en_GB.UTF-8" LC_IDENTIFICATION="en_GB.UTF-8" LC_ALL= On the MariaDB/MySQL database instance we check the current server configuration and the session configuration with the following commands: SQL> SHOW SESSION VARIABLES WHERE Variable_name LIKE 'character_set\_%' OR Variable_name LIKE 'collation%'; +--------------------------+-------------------+ | Variable_name | Value | +--------------------------+-------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | latin1 | | character_set_system | utf8 | | collation_connection | utf8_general_ci | | collation_database | latin1_swedish_ci | | collation_server | latin1_swedish_ci | +--------------------------+-------------------+ SQL> SHOW GLOBAL VARIABLES WHERE Variable_name LIKE 'character_set\_%' OR Variable_name LIKE 'collation%'; +--------------------------+-------------------+ | Variable_name | Value | +--------------------------+-------------------+ | character_set_client | latin1 | | character_set_connection | latin1 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | latin1 | | character_set_server | latin1 | | character_set_system | utf8 | | collation_connection | latin1_swedish_ci | | collation_database | latin1_swedish_ci | | collation_server | latin1_swedish_ci | +--------------------------+-------------------+ These configuration variables are for Client/Server communication: character_set_client, character_set_connection and character_set_results. These for Server configuration: character_set_server and character_set_database (deprecated in MySQL 5.7). And these for System internals and File System access: character_set_system and character_set_filesystem. Sometimes we see customers using the Logon Trigger init_connect to force clients for a specific Character Set: SQL> SHOW GLOBAL VARIABLES LIKE 'init_connect'; +---------------+------------------+ | Variable_name | Value | +---------------+------------------+ | init_connect | SET NAMES latin1 | +---------------+------------------+ The SET NAMES command sets the character_set_client, character_set_connection and character_set_results session variables. [ Lit. ] Analyzing the Application and the Clients Similar steps to analyze the Application and Clients should be taken. We want to answer the following questions: Support of utf8 of Application/Client O/S (Windows)? Support of utf8 of Web Server (Apache (AddDefaultCharset utf-8), Nginx, IIS, ...) Version of programming language (Java, PHP (5.4 and newer?), ...) Version of MariaDB and MySQL Connectors (JDBC (5.1.47 and newer?), ODBC (5.3.11 and newer?), mysqli/mysqlnd (⋝7.0.19?, ⋝7.1.5?), ...) Application code (header('Content-Type: text/html; charset=utf-8');, <?xml version="1.0" encoding="UTF-8"?>, <meta http-equiv="Content-Type" content="text/html; charset=utf-8">, <form accept-charset="utf-8">, htmlspecialchars($str, ENT_NOQUOTES, "UTF-8"), $mysqli->set_charset('utf8mb4');, mbstring [ Lit. ], etc. See also: Configuring Application Character Set and Collation If you do not have your Application under control your DBA can help you to find out with the General Query Log (SET GLOBAL general_log = 1;) what is going on: 190815 19:03:00 12 Connect root@localhost on 12 Query select @@version_comment limit 1 12 Query SET NAMES latin1 12 Query SET NAMES utf8 190815 19:05:24 12 Quit or with some queries on the PERFORMANCE_SCHEMA: -- Works since MySQL 5.6/MariaDB 10.0 SQL> SELECT t.thread_id, t.processlist_id, t.processlist_user, t.processlist_host, t.processlist_db , sca.attr_name, sca.attr_value FROM performance_schema.threads AS t JOIN performance_schema.session_connect_attrs AS sca ON sca.processlist_id = t.processlist_id WHERE t.processlist_user IS NOT NULL AND t.thread_id = 103 ; +-----------+----------------+------------------+------------------+----------------+----------------------------------+---------------------+ | thread_id | processlist_id | processlist_user | processlist_host | processlist_db | attr_name | attr_value | +-----------+----------------+------------------+------------------+----------------+----------------------------------+---------------------+ | 103 | 78 | replication | localhost | NULL | _os | linux-glibc2.12 | | 103 | 78 | replication | localhost | NULL | _client_name | libmysql | | 103 | 78 | replication | localhost | NULL | _pid | 29269 | | 103 | 78 | replication | localhost | NULL | program_name | mysqld | | 103 | 78 | replication | localhost | NULL | _platform | x86_64 | | 103 | 78 | replication | localhost | NULL | _client_replication_channel_name | NULL | | 103 | 78 | replication | localhost | NULL | _client_role | binary_log_listener | | 103 | 78 | replication | localhost | NULL | _client_version | 5.7.26 | +-----------+----------------+------------------+------------------+----------------+----------------------------------+---------------------+ -- Works since MySQL 5.7 only SELECT t.thread_id, t.processlist_id, t.processlist_user, t.processlist_host, t.processlist_db , vbt.variable_name, vbt.variable_value FROM performance_schema.threads AS t JOIN performance_schema.variables_by_thread AS vbt ON vbt.thread_id = t.thread_id WHERE t.processlist_user IS NOT NULL AND (vbt.variable_name like 'charac%' OR vbt.variable_name LIKE 'coll%') AND t.thread_id = 103 ; +-----------+----------------+------------------+------------------+----------------+--------------------------+-------------------+ | thread_id | processlist_id | processlist_user | processlist_host | processlist_db | variable_name | variable_value | +-----------+----------------+------------------+------------------+----------------+--------------------------+-------------------+ | 103 | 78 | replication | localhost | NULL | character_set_client | latin1 | | 103 | 78 | replication | localhost | NULL | character_set_connection | latin1 | | 103 | 78 | replication | localhost | NULL | character_set_database | latin1 | | 103 | 78 | replication | localhost | NULL | character_set_filesystem | binary | | 103 | 78 | replication | localhost | NULL | character_set_results | latin1 | | 103 | 78 | replication | localhost | NULL | character_set_server | latin1 | | 103 | 78 | replication | localhost | NULL | collation_connection | latin1_swedish_ci | | 103 | 78 | replication | localhost | NULL | collation_database | latin1_swedish_ci | | 103 | 78 | replication | localhost | NULL | collation_server | latin1_swedish_ci | +-----------+----------------+------------------+------------------+----------------+--------------------------+-------------------+ Preparation of the Server Settings and the Application To have a better control of the impact of some changes we decided to do some changes on the Application first: Application is setting the Character Set properly itself ($mysqli->set_charset('utf8mb4') [ Lit. ]). In the same step also sql_mode can be set by the application so we can use the defaults on server side in the future. Apache and PHP are configured to support UTF-8. After this step init_connect, character_set_server and character_set_database can be changed to utf8mb4 on the Server and --skip-character-set-client-handshake can be removed at the same time [ Lit. ] Convert Tables to utf8mb4 First we checked and converted the default Character Set of the Schemata/Databases: SQL> SELECT schema_name, default_character_set_name, default_collation_name FROM information_schema.schemata WHERE schema_name NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys') ; +-------------+----------------------------+------------------------+ | schema_name | default_character_set_name | default_collation_name | +-------------+----------------------------+------------------------+ | focmm | latin1 | latin1_swedish_ci | | test | latin1 | latin1_swedish_ci | | foodmart | latin1 | latin1_swedish_ci | | erp | latin1 | latin1_swedish_ci | | world | latin1 | latin1_swedish_ci | +-------------+----------------------------+------------------------+ Converting the Schemata is done as follows: SQL> ALTER DATABASE ... DEFAULT CHARACTER SET utf8mb4; which is a fast operation. To convert the tables there are many different possibilities we considered: The easy one: ALTER TABLE ... CONVERT TO CHARACTER SET utf8mb4; The possibly faster one: dump/restore with sed 's/DEFAULT CHARSET=latin1/DEFAULT CHARSET=utf8mb4/' The possibly fastest one: drop Secondary Indexes, dump/restore with sed, create Secondary Indexes again (mysqlpump). The automated one: With Perconas pt-online-schema-change [ Lit. ] or Facebooks OnlineSchemaChange OSC [ Lit. ] The most elegant but not supported one: Master/Slave Replication. Already with converting the first table we run into a problem: SQL> ALTER TABLE test CONVERT TO CHARACTER SET utf8mb4; ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes. This table had a Primary Key of a length of more than 255 bytes and this is not possible with the old InnoDB Antelope File Format: SQL> SHOW GLOBAL VARIABLES WHERE Variable_name LIKE 'innodb_file_format%' OR Variable_name LIKE 'innodb_large%'; +--------------------------+----------+ | Variable_name | Value | +--------------------------+----------+ | innodb_file_format | Antelope | | innodb_file_format_check | ON | | innodb_file_format_max | Antelope | | innodb_large_prefix | OFF | +--------------------------+----------+ So we have to find out first, which tables are still in old Antelope File Format: SQL> SELECT table_schema , CASE WHEN row_format = 'Dynamic' THEN 'Barracuda' WHEN row_format = 'Compressed' THEN 'Barracuda' WHEN row_format = 'Compact' THEN 'Antelope' WHEN row_format = 'Redundant' THEN 'Antelope' END AS 'file_format' , COUNT(*) FROM information_schema.tables WHERE engine = 'InnoDB' AND table_schema NOT IN ('information_schema', 'sys', 'mysql') GROUP BY table_schema, file_format ; +--------------+-------------+----------+ | table_schema | file_format | count(*) | +--------------+-------------+----------+ | foodmart | Barracuda | 23 | | test | Barracuda | 1 | | world | Antelope | 2 | | world | Barracuda | 1 | +--------------+-------------+----------+ Then we could covert the table correctly forcing the Barracuda File format: SQL> ALTER TABLE test CONVERT TO CHARACTER SET utf8mb4, ROW_FORMAT=dynamic; Testing of new Character Set The last but most important step is to test the changes. Here we recommend to do as many as possible different tests: MySQL CLI: mysql phpMyAdmin MySQL Workbench Other GUIs Your Application Especially test careful data with umlauts (öäüÄÖÜß), foreign Characters (Turkish, Cyrillic, CJK Characters) and Emojis. Good Candidates are: Lastname, City or free text fields like Comment. Some aid you also can get from here: My character encoding seems to be wrong. How can I fix it? Pile of Poo Emoji MySQL Pump mysqlpump — A Database Backup Program This utility is currently not available for MariaDB. If works for MySQL 5.7 and newer and does NOT support MySQL 5.6. So we cannot use it for the Upgrade Process from MySQL 5.6 to 5.7. Newest MySQL Releases contain Bug fixes and even new Features in mysqlpump so we can assume it is still supported and actively maintained. Recent releases contain some fixes for trivial bugs so we can assume mysqlpump is not widely used yet and not as mature yet as mysqldump. An alternative product would be MyDumper from Domas@Facebook (Lauchpad, GitHub). Interesting features are: Parallel dumping of databases. Secondary Index restore separated from Table Restore. Missing features are: Does not support MariaDB and MySQL 5.6 and earlier. No binary log position is stored with backup (--master-data) but only GTID? So we have to use/enable GTID based Replication. First test: # mysqlpump --user=root --all-databases --single-transaction --triggers --routines --events --hex-blob > /tmp/mysqlpump_backup.sql Dump progress: 1/4 tables, 0/87 rows Dump progress: 41/46 tables, 789469/6956116 rows Dump progress: 41/46 tables, 1608219/6956116 rows Dump progress: 45/46 tables, 2358412/6956116 rows Dump progress: 45/46 tables, 3437912/6956116 rows Dump progress: 45/46 tables, 4493662/6956116 rows Dump progress: 45/46 tables, 5177662/6956116 rows Dump progress: 45/46 tables, 5568662/6956116 rows Dump progress: 45/46 tables, 6216162/6956116 rows Dump progress: 45/46 tables, 6716662/6956116 rows Dump completed in 9860 milliseconds ALTER TABLE `foodmart`.`account` ADD KEY `i_account_parent` (`account_parent_id`); ALTER TABLE `foodmart`.`category` ADD KEY `i_category_parent` (`category_parent_id`); ALTER TABLE `foodmart`.`employee` ADD KEY `i_employee_department_id` (`department_id`); ALTER TABLE `foodmart`.`employee` ADD KEY `i_employee_store_id` (`store_id`); ALTER TABLE `foodmart`.`employee` ADD KEY `i_employee_supervisor_id` (`supervisor_id`); ALTER TABLE `foodmart`.`expense_fact` ADD KEY `i_expense_store_id` (`store_id`); ALTER TABLE `foodmart`.`expense_fact` ADD KEY `i_expense_account_id` (`account_id`); ALTER TABLE `foodmart`.`expense_fact` ADD KEY `i_expense_time_id` (`time_id`); Test against MySQL 5.6: Server version is not compatible. Server version should be 5.7.8 or above. MySQL Master/Slave Replication for Character Set conversion Replication of Columns Having Different Data Types should work since MySQL 5.1.21. General rule when Statement Based Replication is used: If the query would work executed directly on the Slave it should also work on the Slave with Replication! So utf8mb4 on the Slave should also be possible with SBR. In MySQL 5.5.3 Attribute Promotion and Attribute Demotion was introduced with LOSSY and NON_LOSSY slave_type_conversions. But: Replication between columns using different character sets is not supported! Some tests: MasterSQL> INSERT INTO test VALUES (NULL, 'öäüÄÖÜß', NULL); MasterSQL> SELECT * FROM test; +----+----------------+---------------------+ | id | data | ts | +----+----------------+---------------------+ | 1 | öäüÄÖÜß | 2019-08-15 19:48:48 | | 2 | öäüÄÖÜß | 2019-08-15 19:50:00 | | 3 | öäüÄÖÜß | 2019-08-15 19:52:29 | +----+----------------+---------------------+ SlaveSQL> SHOW SLAVE STATUS\G ... Last_SQL_Errno: 1677 Last_SQL_Error: Column 1 of table 'test.test' cannot be converted from type 'varchar(255(bytes))' to type 'varchar(1020(bytes) utf8mb4)' ... SlaveSQL> SELECT * FROM test; +----+----------------+---------------------+ | id | data | ts | +----+----------------+---------------------+ | 1 | öäüÄÖÜß | 2019-08-15 19:48:48 | | 2 | öäüÄÖÜß | 2019-08-15 19:50:00 | +----+----------------+---------------------+ See also: Why is varchar(255) not varchar(255)? And: MasterSQL> SET SESSION binlog_format=STATEMENT; MasterSQL> INSERT INTO test VALUES (NULL, 'öäüÄÖÜß', NULL); SlaveSQL> SELECT * FROM test; +----+----------------+---------------------+ | id | data | ts | +----+----------------+---------------------+ | 1 | öäüÄÖÜß | 2019-08-15 19:48:48 | | 2 | öäüÄÖÜß | 2019-08-15 19:50:00 | | 4 | öäüÄÖÜß | 2019-08-15 19:54:16 | +----+----------------+---------------------+ MySQL Shell, mysqlsh Upgrade Checker Utility Since MySQL 5.7 we have a new MySQL Utility, the MySQL Shell (mysqlsh). The Upgrade Checker Utility in MySQL Shell will replace mysql_upgrade in MySQL 8.0. So we wanted to know if it is of any use for converting Character Set to utf8mb4? You can use the Upgrade Checker Utility to check MySQL 5.7 server instances for compatibility errors and issues for upgrading. But: The Upgrade Checker Utility does not support checking MySQL Server instances 5.6 an earlier. MySQL Shell 8.0 is highly recommended for use with MySQL Server 8.0 and 5.7. Please upgrade to MySQL Shell 8.0. [ Lit. ] # ./mysqlsh root@localhost:3319 JS > util.checkForServerUpgrade() The MySQL server at localhost:3319, version 5.6.42-log - MySQL Community Server (GPL), will now be checked for compatibility issues for upgrade to MySQL 8.0.17... Util.checkForServerUpgrade: Detected MySQL server version is 5.6.42, but this tool requires server to be at least at version 5.7 (LogicError) JS > util.checkForServerUpgrade('root@') The MySQL server at localhost:3309, version 5.7.26-log - MySQL Community Server (GPL), will now be checked for compatibility issues for upgrade to MySQL 8.0.17... ... 2) Usage of db objects with names conflicting with reserved keywords in 8.0 No issues found 3) Usage of utf8mb3 charset Warning: The following objects use the utf8mb3 character set. It is recommended to convert them to use utf8mb4 instead, for improved Unicode support. More information: https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-utf8mb3.html test.test.data - column's default character set: utf8 4) Table names in the mysql schema conflicting with new tables in 8.0 No issues found ... Errors: 0 Warnings: 1 Notices: 0 No fatal errors were found that would prevent an upgrade, but some potential issues were detected. Please ensure that the reported issues are not significant before upgrading. JS > util.checkForServerUpgrade('root@', {'targetVersion':'5.7.27'}) The MySQL server at, version 5.7.26-log - MySQL Community Server (GPL), will now be checked for compatibility issues for upgrade to MySQL 5.7.27... Util.checkForServerUpgrade: This tool supports checking upgrade to MySQL server versions 8.0.11 to 8.0.17 (LogicError) Because latin1 tables are still supported in MySQL 8.0 the Upgrade Checker Utility does not complain or do anything for latin1 tables. So it is not of any use for the utf8mb4 conversion task except you already have some old utf8(mb3) tables. Taxonomy upgrade extras:  character set conversion upgrade latin1 utf8 utf8mb4 replication mysqlsh mysqlpump

  • Apache on Fedora 30
    There was an option during the Fedora 30 Workstation installation to add the Apache Web Server, but you need to set it to start automatically. Unfortunately, there was no option to install PHP, which I thought odd because of how many web developers learn the trade first on PHP with a LAMP (Linux, Apache, MySQL, Perl/PHP/Python) stack. You see how to fix that shortcoming in this post and how to install and test PHP, mysqli, and pdo to support MySQL 8. Before you do that make sure you install MySQL 8. You can find my prior blog post on that here. You set Apache to start automatically, on the next boot of the operating system, with the following command: chkconfig httpd on It creates a symbolic link: Created symlink /etc/systemd/system/multi-user.target.wants/httpd.service → /usr/lib/systemd/system/httpd.service. However, that command only starts the Apache server the next time you boot the server. You use the following command as the root user to start the Apache server: apachectl start You can verify the installation with the following command as the root user: ps -ef | grep httpd | grep -v grep It should return: root 5433 1 0 17:03 ? 00:00:00 /usr/sbin/httpd -DFOREGROUND apache 5434 5433 0 17:03 ? 00:00:00 /usr/sbin/httpd -DFOREGROUND apache 5435 5433 0 17:03 ? 00:00:00 /usr/sbin/httpd -DFOREGROUND apache 5436 5433 0 17:03 ? 00:00:00 /usr/sbin/httpd -DFOREGROUND apache 5437 5433 0 17:03 ? 00:00:00 /usr/sbin/httpd -DFOREGROUND apache 5438 5433 0 17:03 ? 00:00:00 /usr/sbin/httpd -DFOREGROUND apache 5442 5433 0 17:03 ? 00:00:00 /usr/sbin/httpd -DFOREGROUND and, then verify the listening port with the following command as the root user: netstat -tulpn | grep :80 It should return the following when both the Apache server is listening on port 80 and the Oracle multi-protocol server is listening on port 8080: tcp6 0 0 :::80 :::* LISTEN 119810/httpd tcp6 0 0 :::8080 :::* LISTEN 1403/tnslsnr You can also enter the following URL in the browser to see the Apache Test Page: http://localhost It should display the test page, like this: You can also create a hello.htm file in the /var/www/html directory to test the ability to read an HTML file. I would suggest the traditional hello.htm file: Hello World! You can call it by using this URL in the browser: http://localhost/hello.htm It should display the test page, like this: Now, let’s install PHP. You use the following command as a privileged user, which is one found in the sudoer’s list: yum install -y php Display detailed console log → Last metadata expiration check: 0:37:02 ago on Fri 16 Aug 2019 11:03:54 AM MDT. Dependencies resolved. ============================================================================= Package Arch Version Repository Size ============================================================================= Installing: php x86_64 7.3.8-1.fc30 updates 2.8 M Installing dependencies: nginx-filesystem noarch 1:1.16.0-3.fc30 updates 11 k php-cli x86_64 7.3.8-1.fc30 updates 4.3 M php-common x86_64 7.3.8-1.fc30 updates 1.1 M Installing weak dependencies: php-fpm x86_64 7.3.8-1.fc30 updates 1.5 M Transaction Summary ============================================================================= Install 5 Packages Total download size: 9.6 M Installed size: 43 M Downloading Packages: (1/5): nginx-filesystem-1.16.0-3.fc30.noarch 34 kB/s | 11 kB 00:00 (2/5): php-common-7.3.8-1.fc30.x86_64.rpm 1.1 MB/s | 1.1 MB 00:00 (3/5): php-7.3.8-1.fc30.x86_64.rpm 2.0 MB/s | 2.8 MB 00:01 (4/5): php-fpm-7.3.8-1.fc30.x86_64.rpm 2.2 MB/s | 1.5 MB 00:00 (5/5): php-cli-7.3.8-1.fc30.x86_64.rpm 1.7 MB/s | 4.3 MB 00:02 ----------------------------------------------------------------------------- Total 3.0 MB/s | 9.6 MB 00:03 Running transaction check Transaction check succeeded. Running transaction test Transaction test succeeded. Running transaction Preparing : 1/1 Installing : php-common-7.3.8-1.fc30.x86_64 1/5 Installing : php-cli-7.3.8-1.fc30.x86_64 2/5 Running scriptlet: nginx-filesystem-1:1.16.0-3.fc30.noarch 3/5 Installing : nginx-filesystem-1:1.16.0-3.fc30.noarch 3/5 Installing : php-fpm-7.3.8-1.fc30.x86_64 4/5 Running scriptlet: php-fpm-7.3.8-1.fc30.x86_64 4/5 Installing : php-7.3.8-1.fc30.x86_64 5/5 Running scriptlet: php-7.3.8-1.fc30.x86_64 5/5 Running scriptlet: php-fpm-7.3.8-1.fc30.x86_64 5/5 Verifying : nginx-filesystem-1:1.16.0-3.fc30.noarch 1/5 Verifying : php-7.3.8-1.fc30.x86_64 2/5 Verifying : php-cli-7.3.8-1.fc30.x86_64 3/5 Verifying : php-common-7.3.8-1.fc30.x86_64 4/5 Verifying : php-fpm-7.3.8-1.fc30.x86_64 5/5 Installed: php-7.3.8-1.fc30.x86_64 php-fpm-7.3.8-1.fc30.x86_64 nginx-filesystem-1:1.16.0-3.fc30.noarch php-cli-7.3.8-1.fc30.x86_64 php-common-7.3.8-1.fc30.x86_64 Complete! Before you test the installation of PHP in a browser, you must restart the Apache HTTP Server. You can do that with the following command as a privileged user: sudo apachectl restart After verifying the connection, you can test it by creating the traditional info.php program file in the /var/www/http directory. The file should contain the following: It should display the PHP Version 7.3.8 web page, which ships with Fedora 30: The next step shows you how to install mysqli and pdo with the yum utility. While it’s unnecessary to check for the older mysql library (truly deprecated), its good practice to know how to check for a conflicting library before installing a new one. Also, I’d prefer newbies get exposed to using the yum utility’s shell environment. You start the yum shell, as follows: yum shell With the yum shell, you would remove a mysql package with the following command: > remove php-mysql The command will remove the package or tell you that there is no package to remove. Next, you install the php-mysqli package with this command: install php-mysqli You will then be prompted to confirm the installation of the php-mysqli library. Finally, you exit the yum shell with this command: > quit If you want to see the whole interactive shell, click on the link below. Display detailed console log → Last metadata expiration check: 0:53:05 ago on Fri 16 Aug 2019 11:03:54 AM MDT. > remove php-mysql No match for argument: php-mysql No packages marked for removal. > install php-mysqlnd > run ============================================================================= Package Architecture Version Repository Size ============================================================================= Installing: php-mysqlnd x86_64 7.3.8-1.fc30 updates 195 k Installing dependencies: php-pdo x86_64 7.3.8-1.fc30 updates 91 k Transaction Summary ============================================================================= Install 2 Packages Total download size: 286 k Installed size: 1.4 M Is this ok [y/N]: y Downloading Packages: (1/2): php-pdo-7.3.8-1.fc30.x86_64.rpm 136 kB/s | 91 kB 00:00 (2/2): php-mysqlnd-7.3.8-1.fc30.x86_64.rpm 183 kB/s | 195 kB 00:01 ----------------------------------------------------------------------------- Total 24 kB/s | 286 kB 00:11 Running transaction check Transaction check succeeded. Running transaction test Transaction test succeeded. Running transaction Preparing : 1/1 Installing : php-pdo-7.3.8-1.fc30.x86_64 1/2 Installing : php-mysqlnd-7.3.8-1.fc30.x86_64 2/2 Running scriptlet: php-mysqlnd-7.3.8-1.fc30.x86_64 2/2 Verifying : php-mysqlnd-7.3.8-1.fc30.x86_64 1/2 Verifying : php-pdo-7.3.8-1.fc30.x86_64 2/2 Installed: php-mysqlnd-7.3.8-1.fc30.x86_64 php-pdo-7.3.8-1.fc30.x86_64 Last metadata expiration check: 0:53:54 ago on Fri 16 Aug 2019 11:03:54 AM MDT. > quit Leaving Shell The downloaded packages were saved in cache until the next successful transaction. You can remove cached packages by executing 'dnf clean packages'. You need to restart the Apache HTTP listener for these changes to take place, which you do with the same command as shown earlier: sudo apachectl restart I wrote the mysqli_check.php script to verify installation of both the mysqli and pdo libraries. The full code should be put in a mysqli_check.php file in the /var/www/html directory for testing. Static Query Object Sample