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
Solved

How to alter table in MySQL DB from created value?

Posted on 2016-09-21
7
48 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
  • 4
  • 3
7 Comments
 
LVL 22

Assisted Solution

by:Ferruccio Accalai
Ferruccio Accalai earned 500 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 22

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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

Author Comment

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

Accepted Solution

by:
Ferruccio Accalai earned 500 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (http://www.sqlite.org/), A SQLite Tidbit: Quick Numbers Table Generation (http://www.experts-exchange.com/A_3570.htm…
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used.

766 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