Solved

How to alter table in MySQL DB from created value?

Posted on 2016-09-21
7
53 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

Creating and Managing Databases with phpMyAdmin in cPanel.
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

687 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