AnsweredAssumed Answered

cold backup - mysqldump 'not unique table/alias'

Question asked by cooljames on Oct 6, 2011
Hello.

I've been using alfresco 3.4.d for a few weeks now - it's been excellent. I'm running it on ubuntu 11.04 with the default tomcat installation, and with the mysql instance that the installer created for me too in it's walkthrough.

I'm having troubles with the cold backup procedure. It's not complicated - it ran okay last week.

First, I run ./ctl.sh stop tomcat <– this stops alfresco, and this script is in the alfresco-3.4.d/tomcat/scripts folder - this works well.

I then run mysqldump -u root -p –all-databases > /home/adminuser/dump5.sql <– this produces the following in my terminal.

adminuser@webserver:~$ mysqldump -u root -p –all-databases > /home/adminuser/dump3.sql
Enter password:
mysqldump: Got error: 1066: Not unique table/alias: 'jbpm_action' when using LOCK TABLES

I've confirmed this table is in the alfresco database by passing in show tables; after logging in with mysql -u root -p alfresco

This didn't happen last time - and I don't really know what could of gone wrong here. I've tried giving the host a restart, but I get the same error. /Share and /Alfresco are both working perfectly, I just can't back them up.

I've been trying to find some kinda mysql database rebuild - but I can't find anything like this in a forum/google search.

I'm planning to move the backup .tar.gz and .sql file to another host and try out version 4.0 community.

Any help is much appreciated, and I'm happy to share my [limited] experience so far if anyone is starting out and needs a hand.

Thanks.

UPDATE : After more google searching, I found : http://forums.mysql.com/read.php?28,243338,243338 <– this guy is having the same problem, but didn't seem to get to the bottom of it.

UPDATE 2: I checked an old system image of alfresco that I had, and the uppercase tables arn't there. This is a 'ls -l' of my current alfresco database.

/opt/alfresco-3.4.d/mysql/data/alfresco$ ls -l
total 1416
~other files and stuff~
-rwxrwxrwx 1 mysql mysql  8754 2011-09-19 11:12 avm_stores.frm
-rwxrwxrwx 1 mysql mysql  8646 2011-09-19 11:12 avm_version_layered_node_entry.frm
-rwxrwxrwx 1 mysql mysql  8832 2011-09-19 11:12 avm_version_roots.frm
-rwxrwxrwx 1 mysql mysql    61 2011-09-19 11:08 db.opt
-rwxrwxrwx 1 mysql mysql 13504 2011-09-19 11:12 jbpm_action.frm
-rw-rw—- 1 root  root  13504 2011-09-29 20:07 JBPM_ACTION.frm
-rwxrwxrwx 1 mysql mysql  8642 2011-09-19 11:12 jbpm_bytearray.frm
-rw-rw—- 1 root  root   8642 2011-09-29 20:07 JBPM_BYTEARRAY.frm
-rwxrwxrwx 1 mysql mysql  8644 2011-09-19 11:12 jbpm_byteblock.frm
-rw-rw—- 1 root  root   8644 2011-09-29 20:07 JBPM_BYTEBLOCK.frm
-rwxrwxrwx 1 mysql mysql  8888 2011-09-19 11:12 jbpm_comment.frm
-rw-rw—- 1 root  root   8888 2011-09-29 20:07 JBPM_COMMENT.frm
-rwxrwxrwx 1 mysql mysql  8700 2011-09-19 11:12 jbpm_decisionconditions.frm
-rw-rw—- 1 root  root   8700 2011-09-29 20:07 JBPM_DECISIONCONDITIONS.frm
-rwxrwxrwx 1 mysql mysql  8752 2011-09-19 11:12 jbpm_delegation.frm
-rw-rw—- 1 root  root   8752 2011-09-29 20:07 JBPM_DELEGATION.frm
-rwxrwxrwx 1 mysql mysql  8846 2011-09-19 11:12 jbpm_event.frm
-rw-rw—- 1 root  root   8846 2011-09-29 20:07 JBPM_EVENT.frm
-rwxrwxrwx 1 mysql mysql  8922 2011-09-19 11:12 jbpm_exceptionhandler.frm
-rw-rw—- 1 root  root   8922 2011-09-29 20:07 JBPM_EXCEPTIONHANDLER.frm
-rwxrwxrwx 1 mysql mysql 13494 2011-09-19 11:12 jbpm_job.frm
-rw-rw—- 1 root  root  13494 2011-09-29 20:07 JBPM_JOB.frm
-rwxrwxrwx 1 mysql mysql 18634 2011-09-19 11:12 jbpm_log.frm
-rw-rw—- 1 root  root  14538 2011-09-29 20:07 JBPM_LOG.frm
-rwxrwxrwx 1 mysql mysql  8724 2011-09-19 11:12 jbpm_moduledefinition.frm
-rw-rw—- 1 root  root   8724 2011-09-29 20:07 JBPM_MODULEDEFINITION.frm
-rwxrwxrwx 1 mysql mysql  8776 2011-09-19 11:12 jbpm_moduleinstance.frm
-rw-rw—- 1 root  root   8776 2011-09-29 20:07 JBPM_MODULEINSTANCE.frm
-rwxrwxrwx 1 mysql mysql 13436 2011-09-19 11:12 jbpm_node.frm
-rw-rw—- 1 root  root   9340 2011-09-29 20:07 JBPM_NODE.frm
-rwxrwxrwx 1 mysql mysql  8690 2011-09-19 11:12 jbpm_pooledactor.frm
-rw-rw—- 1 root  root   8690 2011-09-29 20:07 JBPM_POOLEDACTOR.frm
-rwxrwxrwx 1 mysql mysql  8818 2011-09-19 11:12 jbpm_processdefinition.frm
-rw-rw—- 1 root  root   8818 2011-09-29 20:07 JBPM_PROCESSDEFINITION.frm
-rwxrwxrwx 1 mysql mysql  8894 2011-09-19 11:12 jbpm_processinstance.frm
-rw-rw—- 1 root  root   8894 2011-09-29 20:07 JBPM_PROCESSINSTANCE.frm
-rwxrwxrwx 1 mysql mysql  8872 2011-09-19 11:12 jbpm_runtimeaction.frm
-rw-rw—- 1 root  root   8872 2011-09-29 20:07 JBPM_RUNTIMEACTION.frm
-rwxrwxrwx 1 mysql mysql  8840 2011-09-19 11:12 jbpm_swimlane.frm
-rw-rw—- 1 root  root   8840 2011-09-29 20:07 JBPM_SWIMLANE.frm
-rwxrwxrwx 1 mysql mysql  8762 2011-09-19 11:12 jbpm_swimlaneinstance.frm
-rw-rw—- 1 root  root   8762 2011-09-29 20:07 JBPM_SWIMLANEINSTANCE.frm
-rwxrwxrwx 1 mysql mysql  8624 2011-09-19 11:12 jbpm_taskactorpool.frm
-rw-rw—- 1 root  root   8624 2011-09-29 20:07 JBPM_TASKACTORPOOL.frm
-rwxrwxrwx 1 mysql mysql  8630 2011-09-19 11:12 jbpm_taskcontroller.frm
-rw-rw—- 1 root  root   8630 2011-09-29 20:07 JBPM_TASKCONTROLLER.frm
-rwxrwxrwx 1 mysql mysql 13428 2011-09-19 11:12 jbpm_task.frm
-rw-rw—- 1 root  root  13428 2011-09-29 20:07 JBPM_TASK.frm
-rwxrwxrwx 1 mysql mysql  9462 2011-09-19 11:12 jbpm_taskinstance.frm
-rw-rw—- 1 root  root   9462 2011-09-29 20:07 JBPM_TASKINSTANCE.frm
-rwxrwxrwx 1 mysql mysql  9180 2011-09-19 11:13 jbpm_token.frm
-rw-rw—- 1 root  root   9180 2011-09-29 20:07 JBPM_TOKEN.frm
-rwxrwxrwx 1 mysql mysql  8684 2011-09-19 11:13 jbpm_tokenvariablemap.frm
-rw-rw—- 1 root  root   8684 2011-09-29 20:07 JBPM_TOKENVARIABLEMAP.frm
-rwxrwxrwx 1 mysql mysql  8838 2011-09-19 11:13 jbpm_transition.frm
-rw-rw—- 1 root  root   8838 2011-09-29 20:07 JBPM_TRANSITION.frm
-rwxrwxrwx 1 mysql mysql  8856 2011-09-19 11:13 jbpm_variableaccess.frm
-rw-rw—- 1 root  root   8856 2011-09-29 20:07 JBPM_VARIABLEACCESS.frm
-rwxrwxrwx 1 mysql mysql 13316 2011-09-19 11:13 jbpm_variableinstance.frm
-rw-rw—- 1 root  root   9220 2011-09-29 20:07 JBPM_VARIABLEINSTANCE.frm

So, it seems the uppercase ones are generally smaller, so I guess they're not being used, they are just copies that were created. My plan is just to write an SQL query that will remove all these dodgey looking upper case form files. I'll let you know how I get on :)

Outcomes