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:
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.
And I created the table by:
Then I check the table:
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)