A couple interesting articles

Article 1:

WHY UBER ENGINEERING SWITCHED FROM POSTGRES TO MYSQL
https://eng.uber.com/mysql-migration/

Article 2:

It’s the data, stupid: Why database admins are more important than ever
Specialized databases, cloud, and DevOps expand, not eliminate, role of the DBA.

I thought this was a great article. See the link for the source. I've copied the text for posterity...

http://arstechnica.com/information-technology/2016/07/its-the-data-stupid-why-database-admins-are-more-important-than-ever/

SEAN GALLAGHER - 7/27/2016, 6:00 AM

It may not be all about the tables anymore, but the DBA role is still essential—even if the person doing it doesn't have the title.
Michael Mandiberg
The Rise of Specialized Databases

Terabyte terror: It takes special databases to lasso the Internet of Things
Attacking your monolithic database with a swarm—a whole swarm!
Power tools: Sorting through the crowded specialized database toolbox
To SQL or NoSQL? That’s the database question
View more stories
20
For those of us who have been in the information technology realm for too long, the title "database administrator" conjures up very specific images. We picture someone pulling hair out over issues with backups or snapshots not happening, schemas growing out of control, capacity plans blown up by new application demands, sluggish queries, and eternal performance tuning.

That old-school role of the DBA still exists in some places, particularly large enterprises where giant database clusters still rule the data center. But virtualization, cloud data storage, micro-services, the "DevOps" approach to building and running applications, and a number of other factors have significantly changed how organizations store and manage their data. Many of the traditional roles of the DBA seem to be moot in the shiny, happy world promised by the new generation of databases.

"NoSQL" databases don't require a pre-defined schema, and many have replication built in by default. Provisioning new servers can be reduced to clicking a few radio buttons and check boxes on a webpage. Development teams just point at a cloud data store such as Amazon Web Services' Simple Storage Service (S3) and roll. And even relational database vendors such as Oracle, Microsoft, and IBM are pushing customers toward data-as-a-service (DaaS) models that drastically simplify considerations about hardware and availability.

You might expect this to mean that DBAs' jobs are getting easier. If so, your expectations would be wrong.

"I think [DBAs'] roles have become much more complex," said Chris Lalonde, vice president and GM of Data at Rackspace. "While there is definitely more automation and tooling, the counter to that is that many of the newer technologies are less mature and require more care and feeding. I would say that many of the traditional tasks of DBAs still exist today or need to exist."

In fact, all these great new database technologies highlight the data professional, whether that person is called a DBA, data architect, data engineer, or, in some cases, data scientist. "Data is even more important today," said Kenny Gorman, a database veteran and co-founder of the real-time data service company Eventador. "Businesses used to rely on databases to be sound, run smoothly, and give good reporting. But now, data actually makes you more competitive, and there are more job titles working with data and more technologies that use it. And the database professional is at the core of that."

One step forward...

Non-relational platforms offered a promise to reduce the workload of DBAs, and in some ways they do. Ravi Mayuram, senior vice president of products and engineering at CouchBase, compared the shift in what DBAs have to do to how driving a car has changed over the years: once upon a time, "to drive one you had to essentially be an engineer, and when something went wrong you needed to pull to the side of the road and get under the hood." Now most things take care of themselves, he said, "but I can't do anything myself to fix it."

Databases such as MongoDB and CouchBase, while not relational, support SQL queries, and they have other aspects that make them approachable to experienced DBAs. But they also allow for "dynamic deployment decisions, which you couldn't do with relational systems," Mayuram claimed. "Adding new data structures used to require a schema change and downtime."

Data as a Service has been embraced for "a fraction of what companies do," Mayuram said. "Most companies don't have mission critical information in the cloud."
While a big relational database system requires an understanding of everything about the hardware and software stack, "the next generation of DBAs will be less involved in that," Mayuram explained. "There will be a requirement for a DBA—someone who is more database intensive, but not exclusively," to focus on tasks like capacity planning. The DBA of the future will need to know when to provision more servers and when to retire them.

That sort of dynamic scalability is what has driven the adoption of cloud-based data services based on specialized databases and “Data-as-a-Service” schemes (either built in-house or hosted with a vendor). In either case, provisioning services can take care of setting up hardware, network, and storage. In theory, the DBA focuses on figuring out when applications will need more database capacity. "This is a DevOps sort of role, dealing with dynamic provisioning—it's a slightly different profile," Mayuram said. "With more efficiency, they'll need a fraction less of DBA skills, but that requires them to be more capacity planners and understand the development side better."

For those unfamiliar with the term, DevOps is a practice now used widely in Web and service development. It describes application development teams working in collaboration with IT operations staff to continually improve performance, automation, and scalability of software and systems. The DevOps approach has been a major driver of the adoption of NoSQL databases and other non-traditional data storage and query technologies. DevOps has driven the development of Data-as-a-Service—largely because of the need to automate the scaling up and down of database capacity. But even in the purely relational world, the shift toward turning databases into a cloud service is reducing the need (and the ability) for DBAs to have fine-grain control over hardware configuration.

So far, Data as a Service has been embraced for "a fraction of what companies do," Mayuram said. "Most companies don't have mission critical information in the cloud." Early adopters, he noted, are taking a hybrid approach, with some creating internally hosted DaaS platforms based on cloud computing platforms within their own data centers. But other companies are largely keeping their critical relational systems as they are and using cloud approaches for new projects. "They still have DBAs taking care of existing apps and have DevOps teams handling database deployment in a micro services environment—services that don't need to be in a relational system," Mayuram explained.

With companies preserving their relational databases and increasingly needing to bridge the gap between the old and the new, things got more complex instead of simpler. And even when organizations completely outsource the applications that have typically placed the biggest demands on DBAs, they're still left with the need for some sort of data professional to make sense of what they've gotten themselves into.

The database schema for MediaWiki, the platform used by Wikipedia. Some specialized databases don't explicitly require schemas, but the schema lives on in other ways--and understanding data structures remains important.
Enlarge / The database schema for MediaWiki, the platform used by Wikipedia. Some specialized databases don't explicitly require schemas, but the schema lives on in other ways--and understanding data structures remains important.
Timo Tijhof
The news of death greatly exaggerated

In December 2013, Kenny Gorman wrote an article provocatively titled, "The Database Administrator is dead." But he concluded that article with the declaration, "Long live the DBA."

"The point of that piece was to say that the DBA is still important," Gorman told Ars recently. As a long-time Oracle database administrator and data architect at companies that included PayPal and eBay, Gorman found himself diving into MongoDB at Shutterfly and becoming a NoSQL believer. In the article, which he wrote while chief architect at the data-as-a-service provider ObjectRocket, he noted, "most of our customers don’t have DBAs on staff." But that didn't mean the job had gone away.

"As we move to the cloud," Gorman explained, "with data services, micro-services, the whole 'serverless' movement (services like Amazon Web Services' Lambda and Google Cloud Functions), the whole data landscape is continuing to evolve. And it has changed the role of the DBA—it's no longer the guy who manages the Oracle server in the data center for a particular company. Now there's database storage technologies that exist all over the cloud in various forms that they have to manage."

Even though many of these new database technologies automate much of what DBAs used to do, it doesn't mean that there's a reduction in DBAs' workload. "I believe automation has reduced the need for traditional Ops folks as they help scale the hardware and therefore the volume of queries," Lalonde said. "But there aren't many tools around finding and fixing slow queries or picking the best shard keys," he explained. "I believe automation allows for larger scale with fewer resources, but ultimately you still need an expert around."

Gorman believes the complexity of the new data environment is making DBAs' jobs even harder than before—not easier. That's in part because DBAs can't be as specialized as they once were. "I ran database servers for PayPal and eBay way back in the day," he explained, "and we had one or two data technologies, not fifty. If you knew Oracle, you could probably figure out Microsoft SQL Server—those technologies are complementary." That's not the case anymore, Gorman asserted. "These days, you have to understand the difference between Elasticsearch, Hadoop, [Apache] Kafka, and Oracle and how they are different and why is one better for a particular use case at hand than another."

Because of the pace of change in data storage and query technology, it's not even clear what a database is anymore. And many of the technologies being passed off to data professionals, regardless of their title, bear little resemblance to anything they've worked with before.

"Our careers evolved from managing systems and storage around databases," Gorman said. "Oracle was clearly a database. But these days, the very notion of what a database is has changed. Like, is Hadoop a database?" At ObjectRocket, Gorman built data services around MongoDB. "That's pretty clearly a database," he said, "but our new startup is based on [Apache] Kafka—is that a database?" (Kafka is a data broker that provides streams of data from query "subscriptions" for real-time applications.) "Well, it has properties of a database. It's meant to shuffle data, real-time data. So, it's this crazy evolution where we don't even know if a data product or data infrastructure is really a database anymore. The waters are so muddy. Now it's just really data systems—they all have their own nuances and components."

Some things haven’t changed, even with the new technologies. "Optimizing queries and moving data around have not gone away, and neither has the need to monitor and maintain these databases," said Lalonde. "And those 'schema-less' databases really do have schemas as it turns out—they're just more loosely defined."

As a result, Lalonde explained, the DBAs "have to have the same skills they've always had. Obviously modern DBAs have to be more flexible, understand a breadth of technologies, and work well in agile environments. What we look for in general is someone who really understands database fundamentals, because understanding those fundamentals translates across technologies well."

What's a DBA, anyway?

The shift in data technologies and how they're deployed hasn't just added more work onto the DBA's role—it has also redefined who fills it. With more of the operational tasks around databases shifting toward the operations side of "DevOps," the DBA role is much more closely linked to the application development process. And the skills usually associated with DBAs are now much more important across the development and operations teams.

"I believe automation has reduced the need for traditional Ops folks as they help scale the hardware and therefore the volume of queries... I believe automation allows for larger scale with fewer resources, but ultimately you still need an expert around."
"I think the roles of the developer, the DevOps guys, and data guy—maybe it's a data engineer, maybe a DBA, maybe a data scientist—those roles have to cope with a myriad of new technologies," said Gorman. "Each of those technologies has their own spectrum of maturity, features, and capabilities." And that means that each of those roles now requires at least some of the skills of a DBA.
Whoever ends up in the DBA role for these systems doesn't just need to have a general understanding of them—they need a much more nuanced understanding of what's going on inside their systems than they may have ever needed with relational databases. Just as the behavior of SQL queries can be tuned to some degree for each relational database, getting the best performance out of newer non-relational systems requires DBAs to have a deep understanding of their inner workings.

"How a database 'behaves' is highly dependent on a few choices that the developer of the database makes at the lowest levels," Lalonde said. "If you know what those choices are and how this particular database has made those choices, then you can get a good idea for how it will behave, generally speaking."

That's a level of familiarity that was once the domain of the most experienced database administrators and programmers. But just as data has become increasingly decentralized, it has spread the demands of the DBA role throughout the IT organization. Given how much time many people spend managing their personal stack of structured and unstructured data, we may all very well be DBAs at this point.

Percona Server failure after upgrade attempt

I was upgrading a MySQL server from MySQL 5.1 to MySQL 5.5 and then to Percona Server 5.6 on several replication slaves (in place upgrade). The upgrade to MySQL 5.5 worked fine but whenever I tried to upgrade to Percona Server 5.6, I kept seeing these assertion errors in the log file. It didn't happen every server but on several of them.

2016-08-03 15:57:15 10110 [Warning] InnoDB: Resizing redo log from 2*65536 to 2*131072 pages, LSN=25951778491874
2016-08-03 15:57:15 10110 [Warning] InnoDB: Starting to delete and rewrite log files.
2016-08-03 15:57:16 10110 [Note] InnoDB: Setting log file ./ib_logfile101 size to 2048 MB
InnoDB: Progress in MB: 100 200 300 400 500 600 700 800 900 1000 1100 1200 1300 1400 1500 1600 1700 1800 1900 2000
2016-08-03 15:57:20 10110 [Note] InnoDB: Setting log file ./ib_logfile1 size to 2048 MB
InnoDB: Progress in MB: 100 200 300 400 500 600 700 800 900 1000 1100 1200 1300 1400 1500 1600 1700 1800 1900 2000
2016-08-03 15:57:26 10110 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
2016-08-03 15:57:26 10110 [Warning] InnoDB: New log files created, LSN=25951778491916
2016-08-03 15:57:26 7fc770a50740  InnoDB: Assertion failure in thread 140494565082944 in file pars0pars.cc line 865
InnoDB: Failing assertion: sym_node->table != NULL
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
21:57:26 UTC - mysqld got signal 6 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help
diagnose the problem, but since we have already crashed,
something is definitely wrong and this may fail.
Please help us make Percona Server better by reporting any
bugs at http://bugs.percona.com/

key_buffer_size=67108864
read_buffer_size=131072
max_used_connections=0
max_threads=100001
thread_count=0
connection_count=0
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 217676709 K  bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 0 thread_stack 0x40000
/usr/sbin/mysqld(my_print_stacktrace+0x20)[0x893c80]
/usr/sbin/mysqld(handle_fatal_signal+0x348)[0x655d48]
/lib64/libpthread.so.0(+0xfeb0)[0x7fc76f5bfeb0]
/lib64/libc.so.6(gsignal+0x37)[0x7fc76ea2f237]
/lib64/libc.so.6(abort+0x148)[0x7fc76ea30578]
/usr/sbin/mysqld[0x9882ca]
/usr/sbin/mysqld[0x989946]
/usr/sbin/mysqld(_Z7yyparsev+0xb39)[0xa97a79]
/usr/sbin/mysqld[0x98af5e]
/usr/sbin/mysqld[0x98e1c3]
/usr/sbin/mysqld[0x9aed1b]
/usr/sbin/mysqld[0x9b075a]
/usr/sbin/mysqld[0x969f96]
/usr/sbin/mysqld[0x9d3697]
/usr/sbin/mysqld[0x92e6fa]
/usr/sbin/mysqld(_Z24ha_initialize_handlertonP13st_plugin_int+0x41)[0x5a9cd1]
/usr/sbin/mysqld[0x6d6e20]
/usr/sbin/mysqld(_Z11plugin_initPiPPci+0x915)[0x6db165]
/usr/sbin/mysqld[0x5a0234]
/usr/sbin/mysqld(_Z11mysqld_mainiPPc+0x385)[0x5a3565]
/lib64/libc.so.6(__libc_start_main+0xf5)[0x7fc76ea1ba65]
/usr/sbin/mysqld[0x598075]
You may download the Percona Server operations manual by visiting
http://www.percona.com/software/percona-server/. You may find information
in the manual which will help you identify the cause of the crash.

The error message is obscure, after testing several different paths, I finally found one that worked.

Things I tried:

• shutting down with innodb_fast_shutdown  = 0
• Running check/repair on all tables
• Wiped out data and restored a copy of the data from the master.

What worked:

• innodb_fast_shutdown = 0 on MySQL 5.5.
• Deleted the innodb log files.
• Started MySQL again.
• Shutdown again with the fast shutdown, still on MySQL 5.5.
• Upgraded MySQL to Percona Server 5.6, using basic same my.cnf file.
• Started Percon Server 5.6. Then made changes to the my.cnf file and restarted again.

MySQL COALESCE() function

When I first became a DBA, I had a hard time understanding what the COALESCE function did and why you would ever use it.

If you read the explanation from documentations, it seems pretty straightforward:

"MySQL COALESCE() function returns the first non-NULL value of a list, or NULL if there are no non-NULL values." (lifted from here: http://www.w3resource.com/mysql/comparision-functions-and-operators/coalesce-function.php)

It wasn't too long time until I was writing a query for a database I had created myself where I needed the COALESCE function. I created a table that would pull data from a beta environment and a prod environment. I knew that when I was pulling beta data the prod values would always be NULL and when pulling the prod values, the beta values would always be NULL. So I needed to COALESCE the columns I was pulling in.


-- Example data:

CREATE DATABASE IF NOT EXISTS `beta`;

DROP TABLE IF EXISTS `beta`.`shards`;
CREATE TABLE `beta`.`shards` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `shard_name` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
   PRIMARY KEY (`id`),
  KEY `idx_shard_name` (`shard_name`)
) ENGINE=InnoDB CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `beta`.`shards` ( `shard_name`) VALUES ('db2');


CREATE DATABASE IF NOT EXISTS `prod`;

DROP TABLE IF EXISTS `prod`.`shards`;
CREATE TABLE `prod`.`shards` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `shard_name` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
   PRIMARY KEY (`id`),
  KEY `idx_shard_name` (`shard_name`)
) ENGINE=InnoDB CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `prod`.`shards` ( `shard_name`) VALUES ('db1');

CREATE DATABASE IF NOT EXISTS `analysis`;

DROP TABLE IF EXISTS `analysis`.`data_pull`;
CREATE TABLE `analysis`.`data_pull` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `external_id` varchar(70) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `schema_name` varchar(70) COLLATE utf8_unicode_ci NOT NULL,
  `date` date NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

DROP TABLE IF EXISTS `analysis`.`schema_sizes`;
CREATE TABLE `analysis`.`schema_sizes` (
  `id` bigint(10) unsigned NOT NULL AUTO_INCREMENT,
  `schema_name` varchar(100) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT 'database name',
  `total_size_mb` decimal(12,3) unsigned NOT NULL COMMENT 'total size of data and index size calculated using is schema. Calculated independently of the the data_size column and index size column',
  `date` date NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `analysis`.`schema_sizes` (`id`, `schema_name`, `total_size_mb`, `date`) VALUES
(NULL, 'db1', '100','2016-07-22'),
(NULL, 'db2', '200','2016-07-22');

-- Example Query:

SELECT
COALESCE(c.id,c2.id) as external_id,
ss.schema_name,
ss.date
FROM schema_sizes ss
LEFT JOIN prod. shards c ON (ss.schema_name = c.shard_name)
LEFT JOIN beta. shards c2 ON (ss.schema_name = c2.shard_name)
GROUP BY ss.schema_name, ss.date;

This example is kinda simple but it the end results shows how the COALESCE pulls the id values from the beta and prod shard table so that I don't end up with NULL values in the external_id column.

Finding pesky utf8 data in your latin1 tables

For whatever reason, sometimes you get utf8 data written to a table/column that doesn't support it. It might get double encoded, turn into mojibake (garbled text), etc. These might break your application or cause weirdness. Finding these values can be a pain. Here is a way to do.

CREATE TABLE `test` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

insert into `test` (name) values ('x'), (unhex('c2a3'));


SELECT
name,
length(`name`) as ln,
char_length(`name`) as cl,
length(CONVERT(`name` USING utf8)) as u_ln,
char_length(CONVERT(`name` USING utf8)) as u_cl,
hex(name) as hx,
CONVERT(`name` USING utf8) as u_nm,
hex(CONVERT(`name` USING utf8)) as h_nm
FROM test;

After running this query you will notice that the length is the same for latin characters when it counted using length and still the same after converting to utf8. However, for utf8 data you will notice that the length changes after being converted to utf8. This makes it easy to identify where you have utf8 data. 

With this understanding, you can add a WHERE clause to only show the rows where there are differences like this:

SELECT
name,
length(`name`) as ln,
char_length(`name`) as cl,
length(CONVERT(`name` USING utf8)) as u_ln,
char_length(CONVERT(`name` USING utf8)) as u_cl,
hex(name) as hx,
CONVERT(`name` USING utf8) as u_nm,
hex(CONVERT(`name` USING utf8)) as h_nm
FROM test
WHERE length(`name`) <> length(CONVERT(`name` USING utf8));

TokuDB vs InnoDB

I had heard a great deal about TokuDB the last couple years and I've been dying to get a good use case for it. Ever since Percona bought TokuDB and included it in their distribution, I've been excited to test it.

I have a table that stores millions of rows and hundreds of thousands to a million more get added every day. I thought it would be a good candidate for TokuDB. As a test, I installed Percona Server 5.6.25 and then installed the TokuDB plugins. Everything was working fine up to that point. My test VM has 4 GB of RAM and 4 CPU. I left all the default TokuDB settings and let it automatically take 2 GB RAM (50% of RAM) for the TokuDB buffer pool. I changed all the tables from InnoDB to TokuDB and then lowered the InnoDB buffer pool. I'm no expert in TokuDB tuning so I wasn't really sure what to change to make it perform better.

My process for loading data into the large table involves collecting stats from thousands of data sources and then importing all those data sources into thousands of tiny staging tables and then merging all that data into a single table. Every night when my job to import that data into the staging tables would kick off, the mysql test instance with TokuDB would use up all the RAM and then crash. When I would re-start the instance, the TokuDB recovery would take several minutes and then it would happen again. I didn't have a lot of time to figure out what was going so I switched all my tables back to InnoDB and re-ran the tests and didn't have any problems. I thought TokuDB would perform better "out of the box" and with the default settings but not so.

I've read several of Baron Schwartz's Blog entries and he praises InnoDB for how reliable it is and how well it works. At this point, I don't know what I need to do to make TokuDB work better but it appears that creating thousands of tiny tables (each with only a few hundred or a few thousand rows) that are all TokuDB storage engine isn't a good use case. It seems the frequent dropping and creating of thousands of tiny TokuDB tables might cause RAM problems. I wish I had more time to dig deeper.

Killing lots of linux processes from the command line

I run a lot of bash scripts from the crontab to automate database tasks. Sometimes my bash scrips get carried away and don't finish. When this happens I need to kill them. I use pkill.

How to kill processes from the command line that match a patters:

pkill -f my_pattern

Thank you to this post:
http://stackoverflow.com/questions/8987037/how-to-kill-all-processes-with-a-given-partial-name

Two ways to convert to utf8

I'm in the middle of a project to converts lots of legacy tables from latin1 to utf8. These are two ways of converting specific tables to utf8. These commands are the same for utf8mb4, just change the CHARACTER SET and COLLATE for utf8mb4.

Option 1:

       

-- Change the schema default to utf8

ALTER DATABASE databaseA CHARACTER SET utf8 COLLATE utf8_unicode_ci;



-- Convert the defaults for the table to utf8

ALTER TABLE tableA CHARACTER SET = utf8, COLLATE = utf8_unicode_ci;



-- Convert specific column(s) to utf8

ALTER TABLE tableA MODIFY value VARCHAR(40) CHARACTER SET utf8 COLLATE utf8_unicode_ci;



       
 

What is good about this option is it will preserve other character sets on the same table. Maybe you need to keep latin1 on a specific column or you want to have a case sensitive column that uses some special collation, doing the change like this will not touch those other columns.

Option 2:

       

-- Change the schema default to utf8

ALTER DATABASE databaseA CHARACTER SET utf8 COLLATE utf8_unicode_ci;



-- Convert the table and all columns in it to utf8

ALTER TABLE tableA CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;


       
 

This option converts all columns to utf8. This is nice because it will catch all columns so you don't have to do each one separately. However, if you wanted to keep a specific column a specific collation then you would want to use Option 1.


For an excellent primer on utf8 and MySQL this is a great source:

http://mysql.rjweb.org/doc.php/charcoll