I apologize for being so nervous, but before I use phpMyAdmin to backup, I am concerned about its charset collation in my database interface. It does not seem to be utf8.
As you will see from the enclosed screen shots, my databases seem to be in latin1_swedish_ci.
Am I simply misreading the phpMyAdmin page? Or..? How do I avoid the problems that so many forum users have reported with other charset collations than the utf8_unicode?
Please let me know if I am worrying unnecessarily, or if there is a way to ensure the correct utf8 setting before I create problems.
Thank you very much for your kind patience.
EDIT: This came from Mauno in the General Forum
It's amazing how these Fantastico installs still use latin charsets - and continue to mess all updates etc later...
There are two different things that matter:
(Backup) The DEFAULT collation and char set of your whole database BEFORE you install anything - and the content of different tables that you try to change afterward.
The easiest solution is usually to create a fresh database with correct collation and charset or alter collation and charset of empty database with sql (the other tab in phpMyAdmin) command like
ALTER DATABASE moodle DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER DATABASE moodle DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
or with phpmyadmin.
If the start is correct all data that goes to database gets correct collation and charset automatically using default values.
You could as well drop all current tables, make sure that collation is some unicode collation and charset is utf8 and install standard moodle package from http://download.moodle.org/ to some folder inside your htdocs. Even with CPanel or FTP you can delete old folders created with fantastico install (moodledata or uploaddata) and reinstall moodle in a way that can be later upgraded and modified without Fantastico. And to the latest available stable version (current 1.9.7+), not any repaired Fantastico version...
Or if you want to continue with Fantastico install you could take full backup of tables (export tables with phpMyadmin) and restore the mysql dump file back using utf8 charset (import tables with phpMyAdmin)...or alter each table separately if you want...
ALTER DATABASE `moodle` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
should work fine, but not this may not necessarily affect an individual table. I know for some databases what you do to the database may not always apply to the tables.
So for for each table in the `moodle` database you may want to try: ALTER TABLE `mdl_courses` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci; The first may or may not work, for tables, but the second certainly will.
Perhaps Mauno is right, better to do a dump and rebuild the database correctly then restore.
Most versions of phpMyAdmin and MySQL allow both moodle and `moodle` (or what ever the name is with or without backticks) but some old versions may requre even separate commands for character set and collation with backticks - these can all be given from SQL tab in phpMyAdmin - or you can change the collation of database from Operations tab in phpMyAdmin.
I don't see this as a big problem in Fantastico installs IF sites are using only latin characters - but most sites do use unicode characters and other languages in addition to english.
There are some tiny risks in taking backups if site is old (with some 3rd party activities) or big and it's good to check that saved backup sql file has all the tables included.
Database itself has default collation and character set. Each table can have different collation and character set. Each field inside table can have different collation and character set. The mysql dump file (backup file) can be utf8 or latin and the commands inside mysql backup file may try to restore data in certain collation or charset.
I was pulling my hair two years ago with these Fantastico database issues because some sites were in real trouble and some people even lost their sites when mysql dump file did not have all tables included but basicly if you have a full and tested (checked) backup file of your site and you can use tools like phpMyAdmin it does not matter if the first trial fails and shows question marks in the place of some unicode letters. Changes can be reverted, tables of database can be dropped and imported back with correct character set selection (Import screen in phpMyAdmin - Character set of the file under title "File to import" - this is the character set of this file itself...confusing again). Or with some other tiny tricks - see the examples and comment by John Hobson in http://moodle.org/mod/forum/discuss.php?d=88889 (he modified the backup file in text editor before importing it back to database).
And even in the worst case it is possible to use commands like
ALTER TABLE `mdl_assignment` CHARACTER SET utf8 COLLATE utf8_unicode_ci;
or a little longer
ALTER TABLE `mdl_assignment` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
for each table or click Structure tab in phpMyAdmin and edit separately each table and change collation of fields that are using wrong collation (takes a lot of time this way - several hundreds of clicks)...
In any case the ideal situation is that database has default unicode collation and utf8 character set before fresh install without Fantastico but that's another story and even longer than this one...
Aarggh. In December 2008, I first installed Moodle on webhost Machighway. I used the Eric Hagley video tutorial, taking 7 hours. Then Machighway emailed me and said I did it wrong (I installed in a subdomain) and I should have used Fantastico. I was so naive. I hadn't read the forums, and I obeyed Machighway.
I now see that Fantastico is like heroin. Once you start, it's cruelly hard to get clean. That's why Chad at Machighway was suddenly so brusque when I asked for help backing up and restoring. He knew that a manual backup of a Fantastico site was like a lifeboat with holes in it.
But I do not mind "hundreds of clicks." My 1.9.3 Moodle is not old, and has few imported activities--a bit of code inserted in four HotPotatoes quizzes, and ten short videos, all meticulously backed up by me on my home computer. I am slightly compulsive about organization and backing up.
1. I will take a phpMyAdmin backup and check that every database includes all 198 tables: that's just a matter of reading the backup file's list; I don't have to open the table and look at the code?
Then I will clean up the collation of each table in that backup.
2. I don't know where to type the "commands" that everyone uses. So I will try to correct the collation through phpMyAdmin (188.8.131.52). It has a tab called Variables that shows Server variables and settings, showing a patchwork of utf8 and latin-swedish. (All these tabs in MyAdmin are just reports of facts about my database tables, right? They cannot be reset to edit the files.)
3. When phpMyAdmin lists the database's tables, the collation for each is shown as utf-8. But the "sum line" at the bottom of the list--and that line alone--shows the collation as latin1_swedish_ci. ??
4. In phpMyAdmin, when I open a database, I see the Structure tab. After I create my backup databases, how do I use that tab to reset the collation for each table in my clean backup?
I cannot thank you enough for your great kindness!
Advice would be welcome.
3. You might need to click the name of your database from left panel first to see the other tabs - unless the version of your Cpanel is old or has restricted use of some tabs - see attached image...
2. Through SQL tab and press GO when you have added the command to SQL textarea
1. You can check that all tables are included from the sql file (backup file) with some texteditor (from your Mac or PC) - it's a long file but start from the end - last table in that file...
In practise it may be possible to upgrade your moodle also with Fantastico (it is still old and "tailored" version) and it is still possible to change structure later or move to latest standard version of moodle 1.9.7+ now or later or have several versions of moodle running on the same site (different folders) or create a new database and make testing there begfore dropping any tables from your current install or use a local moodle for testing (both on Windows or Mac) etc...
Ah - you changed the question before I answered to 3...
Can you add a screenshot of your "sum line" - I will try to follow...
If all tables are already utf-8 / unicode and only default collation of database is is latin1_swedish you don't need to alter tables at all - but you could change the default collation of database with that single command
ALTER DATABASE `moodle` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci
or by changing collation from Operations tab...
If you need to edit tables click the name of editable table and Edit button (Hover over button might be Change)
Anyway you can take backups from whole database or single tables - they don't break anything - just use such names for your backups that you know when they are taken
About Command Line: To see if I understand: to run a command line, I go to phpMyAdmin, select the database, click on SQL, click on boxes to the left of files I want the command to change, type the command in the box, and press GO?
And... see if I understand your most recent screenshot. It shows how I can click on a table name on the left, and open up its information grid. You've highlighted "name" but I am not sure why?
Sorry to be so dense.
My phpMyAdmin highlights the selected tab or list item automatically - that screenshot just tells that you can edit any property of any table...
I can't see your images (from your local computer) but attached the SQL tab example...you can run SQL queries without clicking any tables - just enter the command(s) to box and Go...
The size of attached images should be less than 100kb...
Thank you. I have a sceenshot for you, plus three questions.
Thank you, I see how to run the command line on ALL the tables in a database. If I run it on a backup of a database, I will have "globally" changed all tables to utf8.
Question #1: After I backup the databases, how do I get phpMyAdmin to show them? Or will it automatically show its own backups (appropriately named by me, of course)?
I have reformatted the screenshot to a jpg of slightly under 100kB. Of course, it's only the bottom of the full list of tables; but I assure you that every table in the list is marked utf8. Only the "sum line" shows latinswedish.
I've been pondering the alternatives you have mentioned. If I can find the Moodle I long ago installed in that subdomain, if Machighway didn't maliciously delete it, I would have a clean, empty install of 1.9.3. Which leads me to...
QUESTION #2 : Is it risky to start playing with a subdomain instance of Moodle on my same server? Could the server start to confuse that test site with my production site? Is a subdomain a sufficient "firewall"?
Alternatively, I'm lifelong friends with two DBA's: one for university physics dept, one for Pepsico. They are way more conservative than Moodlers; they are appalled that I would start messing with code in my databases. But their private website (PC, whereas mine is Mac) has lots of room and they've offered to create a backup and test site. Trouble is, they use commercial software, not the open source stuff; so mySQL and php are unfamiliar to them.
Maybe I should leave things as they are for now. And perhaps work with my friends to build a test site in 1.9.7, and start loading my "utf-cleaned-up" backups into it later this year.
There is another utility on cPanel, called Backup Wizard. It backs up parts of my site (home directory, and both databases, plus E-mail stuff. Is it a good idea to use Backup Wizard?
So you don't have to worry about tables - just switch the default collation of database to utf8_unicode_ci from operations tab (see attachment - you can see the command after pushing Go button) or with command
ALTER DATABASE `camsemsc_mdle1` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
(if you add new activities/tables and no collation is defined mysql will use default collation and you might get some latin tables later with 3rd party activities...)
Playing with subdomains can sometimes lead to strange situations but they can be fixed as well. It is relatively easy to create a test environment to local PC or Mac with full installer packages http://download.moodle.org/windows/ or http://download.moodle.org/macosx/ or move for example course backups from your current sites to local test moodle (course restore feature is in course menu of moodle) and it is not very hard to download and unzip the standard version of moodle 1.9.7+ from http://download.moodle.org/ for example to another folder on your site (rename new moodle = the folder where files of moodle are for example to testmoodle), create a new database with phpMyAdmin or if you can use only one database give your test moodle a different prefix to database (when you install it - instead of default mdl_) and use a different folder for moodledata (Fantastico installs use usually uploaddata but it may have changed...)
If all this sounds too unfamiliar let it be as it is now but take the backups and make sure that you selected Save as File option in taking backups and have the backups in your local hard disk - and it would be good to have one copy of moodledata as well in a safe place (you can download files from your moodledata folder with some ftp program like FileZilla to your hard disk) just in case that your site might crash.
Some people take only course backups from all courses and if something goes badly wrong course backups can be restored to any same or newer version of moodle.
I have never used a Backup Wizard but I guess that it takes backups as well and might have some custom features for CPanel users (where and how to store backups...?)
Oh yes - forgot the first question - you don't see the "backups" in phpMyAdmin, backup renders files that you save either to your server or to your local hard disc and you can import the files (commands from mysql dump files) back to database ( = restore data to database) either to the same database or to some other database on the same site or on your local test box...
Many, many thanks! As we say here in the States, "You the man!"
My local computer is iMac, 2.66 GHz speed and over 3 GB of available memory. So I'm going to build a test site on it. You have given me plenty of information, and I can figure out the rest. With a good test site as backup (plus a complete Site backup on a CD at a friend's house), I will be able to "un-Fant" my production site.
As to Course backups, Moodle documentation was perfectly clear about them, and I've set my system to take automatic backups all along. The forums taught me to create a shell course, and I've rehearsed a Restore from Course backup that worked perfectly.
I do seriously hope that the Moodle gurus will update and clarify the "Site backup" page in docs.moodle.org.
My system runs Mac OS 10.5.9. I assume you are recommending the Mac installation package from the Moodledocs Admin Installation page:
I have not tried to install any of these packages to Mac environment but it should not matter for test environment if you choose Mamp or Xampp, pre-configured or self-configured. Both can be modified later - for new administrators full installer packages may be the easier start and installing different packages separately gives on the other hand good practise for changing settings etc.
From two available pre-built packages in http://download.moodle.org/macosx/
Moodle 1.9.7+ Moodle4Mac 1.9.7 with XAMPP 1.7.2a is using
XAMPP for Mac OS X 1.7.2a, 2009/08/17
Apache 2.2.12, MySQL 5.1.37, PHP 5.3.0, Perl 5.10.0, ProFTPD 1.3.2a, phpMyAdmin 184.108.40.206, OpenSSL 0.9.8k, GD 2.0.35, Freetype 2.3.5, libjpeg 6b, libpng 1.2.32, libungif-4.1.4, zlib 1.2.3, expat 2.0.1, Ming 0.4.2, Webalizer 2.01-10, pdf class 009e, mod_perl 2.0.4, SQLite 3.6.3, gdbm-1.8.3, libxml-2.7.2, libxslt-1.1.24, openldap-2.3.43, imap-2004g, gettext-0.16.1, libmcrypt-2.5.8, mhash-0.9.9, zziplib-0.13.48, bzip2-1.0.5, freetds-0.64
and Moodle 1.9.7+ Moodle4Mac 1.9.7 with MAMP 1.8.3 is using
with included programs & libraries
PHP 4.4.9 & 5.2.10
APC 3.0.19 & APC 3.1.2
phpMyAdmin 220.127.116.11 & phpMyAdmin 18.104.22.168
Zend Optimizer 3.3.3
YAZ 3.0.47 & PHP/YAZ 1.0.14
For test environment both of these should be suitable...XAMPP version Moodle4Mac 1.9.7 with XAMPP 1.7.2a has newer versions of programs included...
Really? I can do that??
Could I submit a draft set of instructions to someone like you to edit?
Anybody can login to Docs and contribute - see http://docs.moodle.org/en/MoodleDocs:Guidelines_for_contributors
These instructions on Docs and forums are often full of small items here and there so it is really valuable if people like you can collect such things that you have found difficult for new users - teachers or administrators - to Docs...
Thank you. However, it will be months before I have enough experience to contribute to the documentation.
All I've done so far is to backup using cPanel modules such as phpMyAdmin, BackupWizard, and Transmit, an FTP client. I don't even know how to restore.
I relish such a contribution as a writing project; but I would not venture
to advise others until I am experienced.
have some more info about course backup and course restore which may be easier to use in everyday use...
Saving backup file (Exporting sql dump file) is also pretty easy but downloading all moodledata files and folders with ftp may take some time...
More info about FileZilla FTP Client here.
Thanks for doing a lot of heavy lifting on the page currently called Site Backup for Low-tech Users. I saw Helen's twitter and the "Low-tech" got my attention. I do not like to be on the code side of the interface and appreciated your perspective.
I tried to rearrange the page and tried not change the essence of your thoughts. I hope you will review it and please feel free to edit my edits or bluntly make comments on the talk page. It took me 6 months to get up enough nerve to do an edit of something that did not work for me in MoodleDocs. I am still as wet behind the ears now, but more comfortable in having the community straighten me out as required
Best wishes and looking forward to more contributions from you,