Database Primary key problem

Database Primary key problem

by Tony Pattinson -
Number of replies: 9

Moodle version 2.3.3+ PHP 5.3.18 MySQL 5.1.65

After much forum browsing I have "almost" got my moodle site working on the server.

On my local host I have started to develop course categories and sub categories and shortly will start to develop training material to include. Before spending any time on that I want to get the on-line version in line with the local hosted version.

I have uploaded the moodledata directory which includes some css changes I made and both local and remote sites "look" the same. (ynotsailing.com/eCourses)

I used PHPMyadmin to dump a copy of the local database - unzipped as it is relatively small ~1MB. When I tried to import it to the remote site I got the following error message.

SQL query:

-- -- Dumping data for table `mdl_block` -- INSERT INTO `mdl_block` (`id`, `name`, `version`, `cron`, `lastcron`, `visible`) VALUES (1, 'activity_modules', 2012061700, 0, 0, 1), (2, 'admin_bookmarks', 2012061700, 0, 0, 1), (3, 'blog_menu', 2012061700, 0, 0, 1), (4, 'blog_recent', 2012061700, 0, 0, 1), (5, 'blog_tags', 2012061700, 0, 0, 1), (6, 'calendar_month', 2012061700, 0, 0, 1), (7, 'calendar_upcoming', 2012061700, 0, 0, 1), (8, 'comments', 2012061700, 0, 0, 1), (9, 'community', 2012061700, 0, 0, 1), (10, 'completionstatus', 2012061700, 0, 0, 1), (11, 'course_list', 2012061700, 0, 0, 1), (12, 'course_overview', 2012061700, 0, 0, 1), (13, 'course_summary', 2012061700, 0, 0, 1), (14, 'feedback', 2012061700, 0, 0, 0), (15, 'glossary_random', 2012061700, 0, 0, 1), (16, 'html', 2012061700, 0, 0, 1), (17, 'login', 2012061700, 0, 0, 1), (18, 'mentees', 2012061700, 0, 0, 1), (19, 'messages', 2012061700, 0, 0, 1), (20, 'mnet_hosts', 2012061700, 0, 0, 1), (21, 'myprofile', 2012061700, 0, [...]

MySQL said: Documentation

#1062 - Duplicate entry '1' for key 'PRIMARY'

Any ideas as to what I need to do to fix this? All suggestions welcome.

Average of ratings: -
In reply to Tony Pattinson

Re: Database Primary key problem

by Ken Task -
Picture of Particularly helpful Moodlers

Is the database into which you are trying to import a new/blank DB with only engine and collation settings?  Or does it already contain tables?

Error indicates there is an existing table.

'spirit of sharing', Ken

Average of ratings: -
In reply to Ken Task

Re: Database Primary key problem

by Tony Pattinson -

Hi Ken

Thanks for the reply. The database that I'm trying to import to was formed automatically when I used the web servers installation package. I did not "consciously" add anything to it. Having said that I have just taken a look at it using the cpanel MyPHPAdmin and it is fully populated with tables. Should I delete all the tables and then try the inport again?

My knowledge level in these things is just hi enough to be dangerous to myself the s/w always seems to winsmile

Really appreciate your input

Average of ratings: -
In reply to Tony Pattinson

Re: Database Primary key problem

by Ken Task -
Picture of Particularly helpful Moodlers

First, make a backup of the existing DB, the data directory, and the code directory for the online server.  If something goes wrong, you can 'recover' fully by restoring those.

The easiest way at this point might be to backup courses from the local development machine and restore them to the server version.  However, if you want the whole enchilada ...

IF you've not done anything in the online server version that you'd like to keep (course wise), and IF your goal is to have the online server exactly like you developed it on the local version, then remove all tables - leaving the DB empty and void of tables.  This assumes the script installation of the DB set the DB for Innodb as the engine and utf8_general_ci or utf8_unicode as collation.

The sql dump from local you have has that information in it (or should).  That .sql file is a text file and can be opened with any text editor (NotePad or TextEdit will do - as they have search and replace ability).   Work with a copy of the .sql dump ... that way if edits mess up you can start again.

Do a search for ENGINE and see if there are references for ENGINE=InnoDB.  If the sql dump shows something else you'll need to change those references. 

Also check for URL references to http://localhost:8080/

The .sql dump probably has images/files linked using http://localhost:8080/... etc
and you'll have to use the Global Search and Replace cautiously to replace http://localhost:port#/ with http://newhost.somenet.net/ (include the trailing slash).  Do a find first to see what you are finding before replacing.

Once you've made those edits in the copy of the .sql dump, then import that edited .sql file into the empty DB of the online server with whatever tool you use.

Fingers cross (if you did the search and replace correctly), your online site should be accessible and one should be able to login with whatever users you had configured in local.  And, the URL's to images and files should work.

Once successful doing this, highly recommend using backup and restore from local to server and not replicating the process above every time (too prone to human error).  Also run a backup of what you have online before doing anything else and store it off line.

'spirit of sharing', Ken

Average of ratings: Useful (1)
In reply to Ken Task

Re: Database Primary key problem

by Tony Pattinson -

Thanks Ken

I followed your instructions to the letter and I now have both sites almost identical; I say "almost" for 2 reasons.

1. There is a slight difference in the colour scheme but I'm fairly confident that I can fix that using the themes css files once I figure out which parts to change. I'm sure there must be some guidelines in the moodledocs but I have not yet found them. I have been using a trial and error approach which gets results but usually takes much longer.

2. When trying to upload a small jpg file from my desktop I get the following error message

Incorrect pool file content 1004288e955eedeba42823dce243d5c5bed3521d

I get this message when I try to upload to private files and also trying to upload a profile picture. I can upload the file on the local version in both places with no problem. Now here's the curious part I can upload another similar size jpg file from the same desktop to the remote server  with no problem. That would suggest to me that there is a problem with the first jpg file BUT if that is the case why does it upload ok to the localhost version?

If these are the only differences they are obviously not show stoppers but I would be interested in an explanation. Especially if it is indicative of some other non functionality that I am yet to discover.

Once again many thanks for your superbly accurate input; it helped me regain a little of my sanitysmile.

Average of ratings: -
In reply to Tony Pattinson

Re: Database Primary key problem

by Ken Task -
Picture of Particularly helpful Moodlers

I can only guess ... have to remember that what we've done is not necessarily 'standard operating prodedure' for moving from local to server.  I did recommend that perhaps the easiest path was to backup courses on the local system and restore them to the online server.

A brief explanation of what's going on ... I think ...

The file system in MDL 2 docs to read:

http://docs.moodle.org/23/en/Course_files

http://docs.moodle.org/23/en/Working_with_files

MDL 2 now has a document management system which, upon upload of a file, creates a unique hash and renames the file using the hash reference.  If, on the local system, one looks in moodledata/filedir/ one could find (in your case) a 10 folder and contained therein a 04 folder that contains a file with the name of 1004288e955eedeba42823dce243d5c5bed3521d.

Notice the file doesn't show a file name extension.  However, one could deduct the type of file using:

head -n 1 1004288e955eedeba42823dce243d5c5bed3521d

while in an ssh shell located in /moodledata/filedir/10/04/

Since your online server was installed using a script provided by hosting service ... question ... did you upload (via *binary mode* FTP) the entire moodledata directory from the local server replacing the moodledata directory on the server?  If you did not, the references in the DB don't match the hashed directories in /moodledata/filesdir/

While 'correcting' this problem is 'educational' and one would really begin to learn how Moodle works (or doesn't in this case), am wondering if going to 'plan A' (backup courses on the local system, then restoring them to the online system) isn't still the easiest path to get up and running.

I see by your other posting, the DB imported may not have had the character set set to utf8 properly.  Editing an .sql dump is always prone to human error.

DEFAULT CHARSET=utf8

Using whatever tool you have (phpmyadmin) check the character set of the DB for the Moodle.

** Will repeat, think the easiest fix at this point is to backup courses on the local system and restore them to the online system. **

After you get those backups restored and tested, remove the older courses.

Opinion: using a local install of Moodle for courses that will eventually reside in an online/production server can be problematic sometimes (differences in OS and versions and mods/blocks/addons etc.).

Obviously, placing a video on a local server will play very quickly.  When the course is moved to the production server, it won't play at the same speed ... can't ... period.  That's just one example.  Students don't like the experience of the 'world wide wait' - even if good things require some time to 'load up' and play! ;)

Might be best to install a 'sandbox' implementation ON the server and use it for testing/development.  One will be using the same environment and network then.

'spirit of sharing', Ken

Average of ratings: -
In reply to Ken Task

Re: Database Primary key problem

by Tony Pattinson -

Hi Ken

I won't pretend to understand all the contents of your last post but I have tried to add what I hope is useful info.

"Since your online server was installed using a script provided by hosting service ... question ... did you upload (via *binary mode* FTP) the entire moodledata directory from the local server replacing the moodledata directory on the server?  If you did not, the references in the DB don't match the hashed directories in /moodledata/filesdir/"

I used filezilla to upload the moodledata directory from the localhost to the server.

"I see by your other posting, the DB imported may not have had the character set set to utf8 properly."  

The export charset in the local phpMadmin was blank so I set it to utf8 and did another sql dump.

"Editing an .sql dump is always prone to human error."

Based on your earlier information I did not have to do any editing so the human error element should have been avoided wink.

I re-imported the database and all the problems are still there. Curiously enough the only change I notice is that some of the CSS changes I made are now showing up on the server.

Your comment about using a sandbox on the server is appreciated and I will do as much of the development as I can in that way. However there are going to be times when I will have to develop courses off line as I don't always have access to the web.

Am I correct in assuming that once the site is operating correctly on the server I will be able to use the suggested backup and restore process to keep the two versions in synch?

Thanks again for all your input.

Average of ratings: -
In reply to Tony Pattinson

Re: Database Primary key problem

by Ken Task -
Picture of Particularly helpful Moodlers

Wrapping ones mind around the new file system and how it works has proven to be a common difficulty for many ... including me! :|

There was another poster who did something similar as we (ie, uploading the entire moodledata directory).  Had trouble with images afterwards which would be logical as FTP has to choose how to upload files ... binary or text.  The way MDL2 'names' files now there is no extension for FTP apps to use and choose.  From way-back experience during 'dialup days', found that one should always upload as 'binary' and rather than raw files one should put what is to be uploaded in a 'container' to protect files from glitches upon upload ... ie, zip them up.

Once uploaded, unzip and files should be intact.

You might check into permissions and ownerships of files (moodledata directory) ... especially the moodledata/filesdir/ folder and it's contents.

Is there a file in:

/moodledata/filedir/10/04/

(does 10 folder and the 04 folder exist?)

The file 1004288e955eedeba42823dce243d5c5bed3521d is reported in the error.  Since I've read you now have putty and can ssh into the server, try that tip previously shared:

one could deduct the type of file using:

head -n 1 1004288e955eedeba42823dce243d5c5bed3521d

while in an ssh shell located in /moodledata/filedir/10/04/

head is a linux command to show the header of a file.

-n 1 just the first line ... the remainder of the command is the filename.

Most zip files have 'PKZ" on line one (header of the file).  PNG graphics one will see 'PNG'.  If you can ID the file type then one could query the DB or view the mdl_files table and see the 'real human name' of the file as it exist in the table and then go to the course where it's supposed to be located to see if there is a link to it.

BTW, should warn ... in the /moodledata/filesdir/ directory there is a readme.txt file.  Please do read.  It means what it says! ;)

Have you tried backing up a course from the local system and then restoring it to the online server yet?  Please do.  That is the method you will be using if you continue to develop locally.

Also, MDL2 now has a trashdir in /moodledata/.  It acts like a trash can on a PC/Mac only it's triggered by the cron job.  Think the default for emptying the trash is ever 4 days.

You can force the running of the cron job via web browser (logged on as admin level user) or via command line as root user:

cd /moodle/admin/cli/

php cron.php

That errant link a 'non-existent' hash named file might go away if one runs cron and gets the trash emptied. ;)

'spirit of sharing', Ken

Average of ratings: -
In reply to Tony Pattinson

Re: Database Primary key problem

by Howard Miller -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers

"The database that I'm trying to import to was formed automatically when I used the web servers installation package"


What exactly does that mean? It doesn't really answer Ken's question. I'm guessing you used some package to install *Moodle* which, of course, means the database wasn't empty and hence your problem.

Average of ratings: -
In reply to Howard Miller

Re: Database Primary key problem

by Tony Pattinson -

Hi Howard

You are correct. I did use a package to upload moodle to the remote server. It was provided by my hosting company. In hindsight I would have probably been better off if I had done it manually. As always hindsight is always 20/20 wink.

As you can see from my reply to Ken I now do have both sites almost identical. The database primary key problem is resolved.

Average of ratings: -