[ensembl-dev] mysqlimport error

David Rhee david.rhee at einstein.yu.edu
Fri Aug 1 13:34:39 BST 2014


Hi Andy,

Dropping the table AND restarting the MySQL server did the trick! I did the following. Drop the table. Stop the server. Restart the desktop (not a dedicated server so why not for good measures). Start the server. Load the table. Load the data. Like you suggested, not clearing the cached table defs may have been the culprit here. Thanks again for your help!

Best,
David 
________________________________________
From: dev-bounces at ensembl.org [dev-bounces at ensembl.org] on behalf of Andy Yates [ayates at ebi.ac.uk]
Sent: Thursday, July 31, 2014 10:45 AM
To: Ensembl developers list
Subject: Re: [ensembl-dev] mysqlimport error

Hi David,

If my reading of the issues on the internet are correct it suggests that the backing MYD disagrees with the table definition that MySQL holds. So truncating the table data and reloading will cause the same issue to re-appear. I would also suggest after dropping the table restarting your MySQL server if possible. That way any cached table defs will have been cleared from the mysqld process.

Good luck and let us know how you get on,

Andy

------------
Andrew Yates - Ensembl Support Coordinator
European Bioinformatics Institute (EMBL-EBI)
European Molecular Biology Laboratory
Wellcome Trust Genome Campus
Hinxton
Cambridge CB10 1SD
Tel: +44-(0)1223-492538
Fax: +44-(0)1223-494468
http://www.ensembl.org/

On 31 Jul 2014, at 15:11, David Rhee <david.rhee at einstein.yu.edu> wrote:

> Hi Andy,
>
> Yes, I believe the files do seem intact - checking checksums do indicate its kosher. I've tried dropping the table data and reloading multiple times without success but I have not tried delete/recreate and loading the data yet. I will try that next and hopefully get back with some good news. Thanks for the suggestions!
>
> Best,
> David
>
> ________________________________________
> From: dev-bounces at ensembl.org [dev-bounces at ensembl.org] on behalf of Andy Yates [ayates at ebi.ac.uk]
> Sent: Thursday, July 31, 2014 4:06 AM
> To: Ensembl developers list
> Subject: Re: [ensembl-dev] mysqlimport error
>
> Hi David,
>
> Sorry to hear you're having trouble with this. Whilst I've not seen this problem before myself I believe it is caused by a corrupt MyISAM table. This can happen due to a system or mysqld crash. I'm sure the files are not corrupt as we have loaded them into our own MySQL instance here at the EBI (though you may want to check checksums to make sure your transfer was good). Also your command looks fine to me.
>
> Have you dropping the compressed_genotype_region, recreating it and then attempting a re-load?
>
> Cheers,
>
> Andy
>
> ------------
> Andrew Yates - Ensembl Support Coordinator
> European Bioinformatics Institute (EMBL-EBI)
> European Molecular Biology Laboratory
> Wellcome Trust Genome Campus
> Hinxton
> Cambridge CB10 1SD
> Tel: +44-(0)1223-492538
> Fax: +44-(0)1223-494468
> http://www.ensembl.org/
>
> On 30 Jul 2014, at 20:26, David Rhee <david.rhee at einstein.yu.edu> wrote:
>
>> Hi all,
>>
>> I'm getting the following error when I try to load compressed_genotype_region table on homo_sapiens_variation_75_37 database.
>>
>>> mysqlimport -u username -p --fields_escaped_by=\\ homo_sapiens_variation_75_37 -v -d -L compressed_genotype_region.txt
>>> Enter password:
>>> Connecting to localhost
>>> Selecting database homo_sapiens_variation_75_37
>>> Deleting the old data from table compressed_genotype_region
>>> Loading data from LOCAL file: /data/compressed_genotype_region.txt into compressed_genotype_region
>>> mysqlimport: Error: 1034, Wrong bytesec:   0-  0-  0 at 163760275876; Skipped, when using table: compressed_genotype_region
>>
>> System : Ubuntu 14.04
>> MySQL : 5.5.38-0ubuntu0.14.04.1
>> Ensembl : release 75
>>
>> Is this due to corruption on file itself or some sort of encoding issue? I was able to successfully load homo_sapiens_core_75_37 and homo_sapiens_funcgen_75_37 databases using the generic command "mysqlimport -u username -p --fields_escaped_by=\\ Database Name -L *.txt".
>> For homo_sapiens_variation_75_37, I was able to successfully load all tables except  compressed_genotype_var table which I'm also getting a similar Wrong bytesec error.
>>
>> Thanks in advance!
>>
>> David
>> _______________________________________________
>> Dev mailing list    Dev at ensembl.org
>> Posting guidelines and subscribe/unsubscribe info: http://lists.ensembl.org/mailman/listinfo/dev
>> Ensembl Blog: http://www.ensembl.info/
>
>
> _______________________________________________
> Dev mailing list    Dev at ensembl.org
> Posting guidelines and subscribe/unsubscribe info: http://lists.ensembl.org/mailman/listinfo/dev
> Ensembl Blog: http://www.ensembl.info/
>
> _______________________________________________
> Dev mailing list    Dev at ensembl.org
> Posting guidelines and subscribe/unsubscribe info: http://lists.ensembl.org/mailman/listinfo/dev
> Ensembl Blog: http://www.ensembl.info/


_______________________________________________
Dev mailing list    Dev at ensembl.org
Posting guidelines and subscribe/unsubscribe info: http://lists.ensembl.org/mailman/listinfo/dev
Ensembl Blog: http://www.ensembl.info/




More information about the Dev mailing list