[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

How to alter table in MySQL DB from created value?

Posted on 2016-09-21
7
Medium Priority
?
62 Views
Last Modified: 2016-09-22
I have an old and new database with different config as follows.

Old -`id` varchar(36) CHARACTER SET latin1 NOT NULL,

New - `id` varchar(36) COLLATE utf8_unicode_ci NOT NULL,

Can I alter the old to be like new and if so how?

Something like?:
  alter table networks id COLLATE utf8_unicode_ci;
0
Comment
Question by:mickt
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 23

Assisted Solution

by:Ferruccio Accalai
Ferruccio Accalai earned 2000 total points
ID: 41808936
You can use for the entire database
ALTER DATABASE databasename CHARACTER SET utf8 COLLATE utf8_unicode_ci;
and for the table
ALTER TABLE tablename CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Remember to make a backup before!
0
 

Author Comment

by:mickt
ID: 41808966
Thanks Ferruccio

Hit the following; think I may end up in a downward spiral shortly.

Cannot change column 'id': used in a foreign key constraint
0
 
LVL 23

Expert Comment

by:Ferruccio Accalai
ID: 41808979
Thats because there's a foreign key in another table referenced to a column from this, and there the column ( and so the key)  is expected as Latin1
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 

Author Comment

by:mickt
ID: 41808997
I guess there is no simple solution here and each has to be manually tweaked?
0
 
LVL 23

Accepted Solution

by:
Ferruccio Accalai earned 2000 total points
ID: 41809018
0
 

Author Comment

by:mickt
ID: 41809132
Getting there; I'll look into this error tomorrow.

Warning: Using a password on the command line interface can be insecure.
ERROR 1118 (42000) at line 57: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 14000. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
0
 

Author Comment

by:mickt
ID: 41810282
Thanks for your help Ferruccio.
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
In this article, we’ll look at how to deploy ProxySQL.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question