[ensembl-dev] views on the new schema

Andy Yates ayates at ebi.ac.uk
Fri Nov 18 11:17:50 GMT 2011


Hi Michael,

Sorry I didn't make myself clear the first time. If you are renaming a database via direct file manipulation e.g.

mv /mysqldatafiles/my_original_db /mysqldatafile/new_dbname

This will break the views as the view SQL held in the backing frm files have the name of the database they were created in. Using mysqldump does not suffer from this issue since it creates SQL lacking database references so long as the referenced database is the same as the current database. 

That's what I meant by mysqldump being safe for renames.

Andy

On 18 Nov 2011, at 11:04, Michael Paulini wrote:

> On 18/11/11 10:42, Andy Yates wrote:
>> Hi Michael,
>> 
>> This is an issue if you're moving schemas from databases where the users are inconsistent. My suggestion would be to make the users consistent (at least in name). There is also a known issue if you move a view from one schema to another e.g. doing a database rename by moving the data files from one directory to another. This should not be a problem if you are using mysqldump to do renames
>> 
>> Andy
> If you use mysqldump it dumps the user that created the view into the create statement of the table (so it creates it with the same user). Therefore renaming databases on the same server is fine, moving them between servers potentially not (unless you have the same username set up on all involved servers).
> 
> M
> 
>> On 18 Nov 2011, at 09:55, Michael Paulini wrote:
>> 
>>> Hia,
>>> 
>>> just to share some experience of the new e65 schema:
>>> if you load a dump of a ensembl database with a view onto a MySQL server that doesn't contain the user that created the view, it will fail.
>>> So I made it a habit to pipe the SQL dumps through a
>>> s/ensadmin/MY_USERNAME/
>>> to prevent that.
>>> 
>>> Michael
>>> 
>>> _______________________________________________
>>> Dev mailing list    Dev at ensembl.org
>>> List admin (including subscribe/unsubscribe): http://lists.ensembl.org/mailman/listinfo/dev
>>> Ensembl Blog: http://www.ensembl.info/
>> ---
>> Andrew Yates                   Ensembl Core Software Project Leader
>> EMBL-EBI                       Tel: +44-(0)1223-492538
>> Wellcome Trust Genome Campus   Fax: +44-(0)1223-494468
>> Cambridge CB10 1SD, UK         http://www.ensembl.org/
>> 
> 
> 
> 
> -- 
> The Wellcome Trust Sanger Institute is operated by Genome Research Limited, a charity registered in England with number 1021457 and a company registered in England with number 2742969, whose registered office is 215 Euston Road, London, NW1 2BE. 

---
Andrew Yates                   Ensembl Core Software Project Leader
EMBL-EBI                       Tel: +44-(0)1223-492538
Wellcome Trust Genome Campus   Fax: +44-(0)1223-494468
Cambridge CB10 1SD, UK         http://www.ensembl.org/





More information about the Dev mailing list