I want to create a table in mysql database by loading the local file. I am confused with the auto_increment column (say Run_ID) that I want to set it as primary key. My questions are:
1) Do I need add this Run_ID in my file ready? then this does not make any sense to the auto_increment;
2) Should I ignore this column first and then alter the table later? I actually do not have this column in the file. When I tried to create the table:
Code:
DROP TABLE IF EXISTS tair10_agi;
CREATE TABLE if NOT EXISTS tair10_agi (
Run_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
AGI_Number text(50),
Symbols text(100),
Computational_description text(1000),
Chromosome VARCHAR(10),
Orientation VARCHAR(20),
Start INT,
End INT,
AA_Length INT NOT NULL
);
LOAD DATA
LOCAL INFILE "/home/path/to/my_file/file-cleaned.csv"
INTO TABLE tair10_agi
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n';
I added this column, but there is always problem with loading the local file. Somehow the columns are shifted. I was thinking the problem is with this auto_increment. I must have missed something.
Any explanation is appreciated!
yifangt
I added this column, but there is always problem with loading the local file. Somehow the columns are shifted.
Shifted in what way? What rows were you expecting, and what rows do you get?
auto-increment is generally used for new data, since the automatic increment is difficult to reset -- it's not supposed to be anything but a unique number specific to one table. Setting the number yourself is not a good idea either, it may lead to problems inserting later.
format of the input file and shifted columns in the result.
Thanks!
Here is the head of the input file in tab-delimited format. Please notice that the second column may be empty in some rows and maybe be more than two items separated by comma.
Code:
AGI_Number Symbols Computational_description Chromosome Orientation Start End AA_Length
AT1G75120.1 RRA1 Nucleotide-diphospho-sugar transferase family protein chr1 REVERSE 28197022 28198656 402
AT1G17600.1 Disease resistance protein (TIR-NBS-LRR class) family chr1 REVERSE 6053026 6056572 1049
AT1G51380.1 DEA(D/H)-box RNA helicase family protein chr1 FORWARD 19047960 19049967 392
AT1G77370.1 Glutaredoxin family protein chr1 FORWARD 29073916 29074642 130
AT1G44090.1 ATGA20OX5, GA20OX5 gibberellin 20-oxidase 5 chr1 REVERSE 16760677 16762486 385
And I created the table by:
Code:
DROP TABLE IF EXISTS test_agi;
CREATE TABLE if NOT EXISTS test_agi (
Run_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
AGI_Number text(30),
Symbols text(100),
Computational_description text(1000),
Chromosome VARCHAR(10),
Orientation VARCHAR(20),
Start INT,
End INT,
AA_Length INT NOT NULL
) ;
LOAD DATA
LOCAL INFILE "/home/yifang/20111210-Project-At-Br-Bd-Os-Gm/Blast-Result/At-as-subject/e10/TAIR10_representative_gene_model-cleaned.csv"
INTO
TABLE test_agi
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n';
It can be seen that the columns are shifted and, the AGI_Number, AA_Length are not correctly input (Not showing as my screen is small!). I was thinking the Run_ID is the problem as it is not in the raw data file.
Yes, the primary key can be added by altering the table later, but can it be set here too? Not very experienced with MySQL and lots to learn. Thank you for your education!
I got a issue with running the following script below if I remove the inserts and alter table the tables will install just fine but if I try and do the full thing in one go i get the below error dose any one have any ideas on this problem?
its got me spinning, thanks.
REM
REM List of... (1 Reply)
I have the following method to identify missing primary keys in a MySQL database schema:
USE information_schema;
SELECT xx.table_name
FROM (SELECT table_name, COUNT(*) FROM columns WHERE table_schema = @myDB GROUP BY table_name, column_key) xx
GROUP BY xx.table_name
HAVING COUNT(*) = 1;
I... (0 Replies)
After doing a yum install mysql mysql-server on Fedora 14 I wasn't able to fully install the packages correctly. It installed MySQL 5.1. I was getting the following error when running the:
mysql
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)... (3 Replies)
Hi, I've used the following way to set ssh public key authentication and it is working fine on Solaris 10, RedHat Linux and SuSE Linux servers without any problem. But I got error 'Server refused our key' on Solaris 8 system. Solaris 8 uses SSH2 too. Why? Please help. Thanks.
... (1 Reply)
Lately my attention has been focused on distributed MySQL database replication to other web servers. All was going well, but then I noticed the replication would stop and there were there "Duplicate Primary Key" errors in SHOW SLAVE STATUS;
I started to trace these down and noticed that this... (2 Replies)
01-16-2012
