Solved

How to alter table in MySQL DB from created value?

Posted on 2016-09-21
7
51 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 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

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

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

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

Suggested Solutions

All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

730 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