06 November 2015, 0:10

Hi everyone and nice to meet you all!

We need to update Mahara from a very old version (1.8.1) to the new one (15.10). I know that some users had problems even updating 1.8.0 to 1.8.1, so I'm afraid there will be lot of issues, since around two years have passed from one version to another! That's why I installed the new version on a test server, and I'll make all the tests on this server before updating the official one.

I did FTP and Mysql backup and I'm trying to import the old db into the new one but something goes wrong. The first error I get is:

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

If I'm not wrong it's because my admin (the only user I have on Mahara 15.10) has the same ID of someone who is memorized in the old db.

The Mahara 15.10 admin is useless at this point, since the admins I need are the same who I had on the old version. How to fix it? Thanks in advice and sorry for my english, it's not my main language.

06 November 2015, 3:02

Hi,

I think this issue is because of the zero value in the `id` column on the `usr` table you are importing. I get this frequently. The solution that works for me with MySQL is below :

SET @@session.sql_mode = 
 CASE WHEN @@session.sql_mode NOT LIKE '%NO_AUTO_VALUE_ON_ZERO%' 
 THEN CASE WHEN LENGTH(@@session.sql_mode)>0
 THEN CONCAT_WS(',',@@session.sql_mode,'NO_AUTO_VALUE_ON_ZERO') 
 ELSE 'NO_AUTO_VALUE_ON_ZERO' 
 END
 ELSE @@session.sql_mode 
 END

Source: Fail Safe 

It is possible to upgrade from 1.8.1 to 15.10 as I've done it recently from a 1.7.X install. I didn't apply every release of each series just the first release and then the most recent one at the end.

Hope this helps.

Kevin

06 November 2015, 7:35

Hi Kevin and thank you very much for your reply. I'm happy to hear the update phase may be easier than I expected :)

I tryed your solution but it didn't work.

The problem seems to be on the `activity_queue` table:


INSERT INTO `activity_queue` (`id`, `type`, `data`, `ctime`) VALUES
(1, 3, 'O:8:"stdClass":1:{s:4:"view";s:4:"2276";}', '2015-07-26 23:23:41'),
(2, 3, 'O:8:"stdClass":1:{s:4:"view";s:4:"2274";}', '2015-07-27 12:02:31'),
(3, 3, 'O:8:"stdClass":1:{s:4:"view";s:4:"1388";}', '2015-07-27 12:42:24'),
(4, 3, 'O:8:"stdClass":1:{s:4:"view";s:4:"1393";}', '2015-07-27 12:44:56'),
(5, 3, 'O:8:"stdClass":1:{s:4:"view";s:4:"1393";}', '2015-07-27 12:50:15'),
(6, 3, 'O:8:"stdClass":1:{s:4:"view";s:4:"1393";}', '2015-07-27 12:50:33'),
(7, 3, 'O:8:"stdClass":1:{s:4:"view";s:4:"2892";}', '2015-07-27 13:12:41'),
(8, 3, 'O:8:"stdClass":1:{s:4:"view";s:4:"2273";}', '2015-07-27 14:12:52'),
(9, 3, 'O:8:"stdClass":1:{s:4:"view";s:4:"2284";}', '2015-07-27 16:06:53'),
(10, 3, 'O:8:"stdClass":1:{s:4:"view";s:4:"2142";}', '2015-07-27 17:20:11'),
(11, 3, 'O:8:"stdClass":1:{s:4:"view";s:4:"1524";}', '2015-07-30 14:47:13'),
(12, 3, 'O:8:"stdClass":1:{s:4:"view[...]

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

This is what I have in my activity_queue table (Mahara 15.10). I have only 1 row and the ID is 6, the same ID shown in the error message.

Since I have only 1 user (admin) on my Mahara 15.10, do you think remove that user could solve the problem?

06 November 2015, 9:24

Hi

We upgraded our system earlier in July from 1.8 to 15.04. The direct upgrade didn't work. I had to break the upgrade process into two parts:

- from 1.8.3 to 1.10.4

- from 1.10.4 to 15.04.1

I have referred to the forum post:

https://mahara.org/interaction/forum/topic.php?id=7225#post29024

It's not exactly relevant in terms of the issue, but it does indicate that there could be issues with direct upgrade.

Hope this helps.

Cheers,

Shen

06 November 2015, 9:54

Hi Shen, I'm collecting as much infos as I can to avoid most of the problems during the setup phase. Your answer is more than useful, thank you :)

06 November 2015, 23:23

Well, I removed the ID 6 row from activiry_queue and now I'm facing the next error.

This time #1062 - Duplicate entry '1' for key 'PRIMARY' is on activity_type table.

INSERT INTO `activity_type` (`id`, `name`, `admin`, `delay`, `plugintype`, `pluginname`) VALUES
(1, 'maharamessage', 0, 0, NULL, NULL),
(2, 'usermessage', 0, 0, NULL, NULL),
(3, 'watchlist', 0, 1, NULL, NULL),
(4, 'viewaccess', 0, 1, NULL, NULL),
(5, 'contactus', 1, 1, NULL, NULL),
(6, 'objectionable', 1, 1, NULL, NULL),
(7, 'virusrepeat', 1, 1, NULL, NULL),
(8, 'virusrelease', 1, 1, NULL, NULL),
(9, 'institutionmessage', 0, 0, NULL, NULL),
(10, 'groupmessage', 0, 1, NULL, NULL),
(11, 'feedback', 0, 0, 'artefact', 'comment'),
(12, 'newpost', 0, 1, 'interaction', 'forum');

What to do now? Do I need to check one by one which tables I need to fill and which tables I can remove from the sql file?  I hope not, the sql file have around 57k SQL lines xD

07 November 2015, 0:40

Hi Whirl,

can I just check, when you are importing your MySQL dump is the database empty and creating the tables and/or was the dump produced with the "Drop existing tables"? 

Kevin

07 November 2015, 1:24

Hi Kevin and thank you for your answer,

I was trying to import the old dump into the new "non empty database".

Probably I get it now: do I need to keep new tables structure (erasing all the content) and then import the old dump? in this case only insert query will be executed, so everything should work, right?

Edit: Ok I think I'm wrong, this is an error message I get if I try to truncate all the tables of my new db

TRUNCATE `activity_type`

#1701 - Cannot truncate a table referenced in a foreign key constraint (`dbname1510`.`activity_queue`, CONSTRAINT `actiqueu_typ_fk` FOREIGN KEY (`type`) REFERENCES `dbname1510`.`activity_type` (`id`))

07 November 2015, 2:23

Hi whirl,

Ah right this makes sense now. 

I'll assume that you have a safe copy of your 1.8.1 site both code and DB and data folder.

What I would do is this 

1. Create a dump of your existing DB with table creation and data and "Drop tables" on just incase you need to import more than once.
2. Create a new empty DB
3. (Optional but maybe worth it) Run the code to permit Zero auto increments for the session.
4. Import the dump into the DB.
5. Put a clean copy of of the version of Maharah that you want to upgrade to in the correct place for the url you are using.
6. Copy the config.php from your old setup or set up a new one with the new DB details
7. Copy any 3rd party plugins and themes over.
8. Access the site and it should ask to upgrade Mahara.
9. Cross fingers. ;)

Kevin

07 November 2015, 3:26

Thank you Kevin, I'll follow this steps and I hope luck will be on my side :D