<
From version < 25.1 >
edited by Vincent Massol
on 2012/10/02
To version < 27.1 >
edited by Vincent Massol
on 2012/10/03
>
Change comment: There is no comment for this version

Summary

Details

Page properties
Content
... ... @@ -12,28 +12,6 @@
12 12  If you use MySQL with default engine MyISAM, you will most likely corrupt your database.** We highly recommend using a storage engine such as InnoDB which supports transactions.
13 13  {{/warning}}
14 14  
15 -If you have a MyISAM database and wish to migrate to InnoDB you could use/adapt this script:
16 -
17 -{{code}}
18 -#!/bin/bash
19 -
20 -db=sugarcrm
21 -to_character_set=utf8
22 -to_collation=utf8_bin
23 -
24 -mysql_cmd="mysql -u root"
25 -
26 -TBL_LIST=$($mysql_cmd -N -s -r -e "use $db;show tables;")
27 -
28 -for tbl_name in $TBL_LIST;
29 -do
30 -$mysql_cmd -e "alter table $db.$tbl_name convert to character set $to_character_set collate $to_collation;"
31 -done
32 -
33 -echo "Here the result of the operation:"
34 -$mysql_cmd -e "USE $db;SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, COLLATION_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=DATABASE();"
35 -{{/code}}
36 -
37 37  == MySQL versions older than 5.0 ==
38 38  
39 39  XWiki does not fully work with MySQL versions 4.x or lower, due to several limitations of the way the SQL standards are implemented in MySQL, limited support for non-latin1 encodings, the flaky integration of Hibernate and MySQL 4, and other things. Most of the application works fine, but there are some parts that cannot be easily fixed, so if you must use MySQL 4.x, you're doing it on your own. Seriously, MySQL 4 is pretty old and buggy, you should consider upgrading.
... ... @@ -88,6 +88,64 @@
88 88  {{/info}}
89 89  )))
90 90  
69 += Tips =
70 +
71 +== Convert a database from latin1 (or collation utf8_ci) to utf8/utf8_bin ==
72 +
73 +{{code}}
74 +#!/bin/bash
75 +
76 +db=xwiki
77 +to_character_set=utf8
78 +to_collation=utf8_bin
79 +
80 +mysql_cmd="mysql -u root"
81 +
82 +TBL_LIST=$($mysql_cmd -N -s -r -e "use $db;show tables;")
83 +
84 +for tbl_name in $TBL_LIST;
85 +do
86 +$mysql_cmd -e "alter table $db.$tbl_name convert to character set $to_character_set collate $to_collation;"
87 +done
88 +
89 +echo "Here the result of the operation:"
90 +$mysql_cmd -e "USE $db;SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, COLLATION_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=DATABASE();"
91 +{{/code}}
92 +
93 +== Convert from MyISAM to InnoDB ==
94 +
95 +{{code}}
96 +#!/bin/bash
97 +
98 +MYSQL_COMMAND=mysql
99 +TO_ENGINE=INNODB
100 +
101 +DATABASES=$(mysql -N -s -r -e 'show databases'|grep -v ^information_schema$|grep -v ^mysql$)
102 +
103 +
104 +for db in $DATABASES
105 +do
106 +
107 +echo "Working on database $db..."
108 +echo ""
109 +
110 +TABLES=$(mysql -N -s -r -e "show tables from $db;")
111 +
112 +for tb in $TABLES
113 +do
114 +
115 +$MYSQL_COMMAND -e "ALTER TABLE $db.$tb ENGINE = $TO_ENGINE;"
116 +
117 +done
118 +
119 +$MYSQL_COMMAND -e "SELECT table_name,Engine,table_collation FROM information_schema.tables WHERE table_schema = DATABASE();"
120 +
121 +echo ""
122 +echo ""
123 +
124 +done
125 +{{/code}}
126 +
91 91  = Troubleshooting =
92 92  
93 93  == Unable to login to MySQL Console ==

Get Connected