How can I see what character set my MediaWiki's mysql database is using?
The following tip from http://www.mediawiki.org/wiki/Manual:Backing_up_a_wiki#Character_set did not work for me:
"You can see which character set your tables are using with a statement like SHOW CREATE TABLE text. The last line will include a DEFAULT CHARSET clause."
Here's my attempt to do that:
--------------------------------------------------
[roger@cpanel68 ~]$ mysql -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 5295221 Server version: 5.0.45-community-log MySQL Community Edition (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> SHOW CREATE TABLE text -> ---------------------------------------------------
I know little about mysql. Do you see what I did wrong?
I'm worried that my database might be in character set "latin1" and that mysqldump may corrupt my backup database by auto-converting it to "UTF-8". I don't know what character set the database actually is in and don't want to mess it up setting it to "latin1" if it is not already that.
How can I check the character set of my MediaWiki's mysql database (it is on a Redhat Linux server)?
Thanks! Roger * MediaWiki: 1.9.2 * PHP: 5.2.6 (cgi) * MySQL: 5.0.45-community-log
Hi,
Am Mon, 23 Jun 2008 23:30:06 -0700 schrieb roger@rogerchrisman.com:
How can I see what character set my MediaWiki's mysql database is using?
[..]
mysql> SHOW CREATE TABLE text ->
you have to check for the correct name of your table. For me it is wiki_text => "SHOW CREATE TABLE wiki_text"
Regards, Matthias
Also, make sure you end your statement with ";" or "\G"...
mysql> SHOW CREATE TABLE text\G
If you are using MySQL 5, then you can get the information you want from the information_schema tables...
SELECT table_collation FROM information_schema.tables WHERE table_name = 'text' AND table_schema = 'database_name'
Be sure to change the "database_name" above to the name of the database you are using. If you want to see the collation from all tables for the wiki, just leave off the part of the WHERE statement that specifies the table.
Andrew
-----Original Message----- From: mediawiki-l-bounces@lists.wikimedia.org [mailto:mediawiki-l-bounces@lists.wikimedia.org] On Behalf Of Matthias Korn Sent: Tuesday, June 24, 2008 3:30 AM To: mediawiki-l@lists.wikimedia.org Subject: Re: [Mediawiki-l] backup advice wanted
Hi,
Am Mon, 23 Jun 2008 23:30:06 -0700 schrieb roger@rogerchrisman.com:
How can I see what character set my MediaWiki's mysql database is using?
[..]
mysql> SHOW CREATE TABLE text ->
you have to check for the correct name of your table. For me it is wiki_text => "SHOW CREATE TABLE wiki_text"
Regards, Matthias
-- Rock in China http://wiki.rockinchina.com/ http://www.rockinchina.com/
_______________________________________________ MediaWiki-l mailing list MediaWiki-l@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/mediawiki-l
-----Original Message----- From: mediawiki-l-bounces@lists.wikimedia.org [mailto:mediawiki-l-bounces@lists.wikimedia.org] On Behalf Of Matthias Korn Sent: Tuesday, June 24, 2008 3:30 AM To: mediawiki-l@lists.wikimedia.org Subject: Re: [Mediawiki-l] backup advice wanted
Hi,
Am Mon, 23 Jun 2008 23:30:06 -0700 schrieb roger@rogerchrisman.com:
How can I see what character set my MediaWiki's mysql database is using?
[..]
mysql> SHOW CREATE TABLE text ->
Just to make sure, you are putting a semi-colon (;) at the end of your MySQL statements right?
I have no prior experience invoking mysql queries on the command line. Now (I think) I know to use a semicolon (;) on the end of last line in a query. Thank you for mentioning that.
The below mysql session worked for me and seems to indicate my MediaWiki database (roger_wgdb) is character encoded "latin1_swedish_ci". How odd.
Is "latin1_swedish_ci" normal or default for MediaWiki? (I was expecting "latin1" because my MediaWiki is in English on a Dathorn.com virtual Web hosting server in USA and "latin1" is indicated by status in the bottom part of my mysql session, copied below. This MediaWiki was born on a FreeBSD box, moved to Dreamhost.com virtual Web hosting, then here to Dathorn.com virtual Web hosting. All in USA but that first, FreeBSD, box may have set the database character encoding of my wiki born on it, and that box is no longer available to check status on. So I appear to be stuck in character encoding "latin1_swedish_ci", right?
So, should I specify --default-character-set=latin1_swedish_ci in my mysqldump backup commands?
=== Here's my mysql session indicating "latin1_swedish_ci" ===
mysql> SELECT table_collation -> FROM information_schema.tables -> WHERE table_schema = 'roger_wgdb'; +-------------------+ | table_collation | +-------------------+ | latin1_swedish_ci | | latin1_swedish_ci | | latin1_swedish_ci | | latin1_swedish_ci | | latin1_swedish_ci | | latin1_swedish_ci | | latin1_swedish_ci | | latin1_swedish_ci | | latin1_swedish_ci | | latin1_swedish_ci | | latin1_swedish_ci | | latin1_swedish_ci | | latin1_swedish_ci | | latin1_swedish_ci | | latin1_swedish_ci | | latin1_swedish_ci | | latin1_swedish_ci | | latin1_swedish_ci | | latin1_swedish_ci | | latin1_swedish_ci | | latin1_swedish_ci | | latin1_swedish_ci | | latin1_swedish_ci | | latin1_swedish_ci | | latin1_swedish_ci | | latin1_swedish_ci | | latin1_swedish_ci | | latin1_swedish_ci | | latin1_swedish_ci | | latin1_swedish_ci | | latin1_swedish_ci | | latin1_swedish_ci | | latin1_swedish_ci | | latin1_swedish_ci | | latin1_swedish_ci | +-------------------+ 35 rows in set (0.86 sec)
mysql> status; -------------- mysql Ver 14.12 Distrib 5.0.45, for pc-linux-gnu (i686) using readline 5.0
Connection id: 14978 Current database: Current user: roger@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.0.45-community-log MySQL Community Edition (GPL) Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: latin1 Db characterset: latin1 Client characterset: latin1 Conn. characterset: latin1 UNIX socket: /var/lib/mysql/mysql.sock Uptime: 1 hour 42 min 24 sec
Threads: 1 Questions: 375766 Slow queries: 20 Opens: 3775 Flush tables: 1 Open tables: 3676 Queries per second avg: 61.160 --------------
mysql>
=== End mysql session ===
Thanks! Roger * MediaWiki: 1.9.2 * PHP: 5.2.6 (cgi) * MySQL: 5.0.45-community-log
mediawiki-l@lists.wikimedia.org