MySQL Forums
Forum List  »  Newbie

Re: autoincrement id missing when i do load data local into file
Posted by: Wane cooper
Date: December 29, 2010 09:23PM

Thanks for you response
This is the only table in which i came across this problem
Here is the show create table

CREATE TABLE `tbl_chart_preset_vitals_growth_percentiles` (
`id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`patient_type` enum('Infants','Adults') NOT NULL,
`gender_id` tinyint(3) unsigned NOT NULL,
`vital_types_id` tinyint(3) unsigned NOT NULL,
`age` float NOT NULL,
`p3` varchar(15) NOT NULL,
`p5` varchar(15) NOT NULL,
`p10` varchar(15) NOT NULL,
`p25` varchar(15) NOT NULL,
`p50` varchar(15) NOT NULL,
`p75` varchar(15) NOT NULL,
`p85` varchar(15) NOT NULL,
`p90` varchar(15) NOT NULL,
`p95` varchar(15) NOT NULL,
`p97` varchar(15) NOT NULL,
PRIMARY KEY (`id`),
KEY `fk_gender_id` (`gender_id`),
KEY `fk_vital_types_id` (`vital_types_id`),
CONSTRAINT `tbl_chart_preset_vitals_growth_percentiles_ibfk_1` FOREIGN KEY (`gender_id`) REFERENCES `tbl_preset_gender` (`id`),
CONSTRAINT `tbl_chart_preset_vitals_growth_percentiles_ibfk_2` FOREIGN KEY (`vital_types_id`) REFERENCES `tbl_chart_preset_vital_types` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1788 DEFAULT CHARSET=latin1

Sample of the csv file...and no the csv does not contain the column for the autoincrement id.

Here is the upload command

load data local infile '/home/micro/bmiagerev.csv' into table tbl_chart_preset_vitals_growth_percentiles fields terminated by '|' ignore 1 lines (patient_type,vital_types_id,gender_id,Age,P3,P5,P10,P25,P50,P75,P85,P90,P95,P97);

patient_type|vital_types_id|Sex|Agemos|P3|P5|P10|P25|P50|P75|P85|P90|P95|P97
Adults|1|1|24|14.52095|14.73732|15.09033|15.74164|16.57503|17.55719|18.16219|18.60948|19.33801|19.85986
Adults|1|1|24.5|14.50348|14.71929|15.07117|15.71963|16.54777|17.52129|18.11955|18.56111|19.27890|19.79194
Adults|1|1|25.5|14.46882|14.68361|15.03336|15.67634|16.49443|17.45135|18.03668|18.46730|19.16466|19.66102
Adults|1|1|26.5|14.43460|14.64843|14.99620|15.63403|16.44260|17.38384|17.95700|18.37736|19.05567|19.53658
Adults|1|1|27.5|14.40083|14.61379|14.95969|15.59268|16.39224|17.31871|17.88047|18.29125|18.95187|19.41849
Adults|1|1|28.5|14.36755|14.57969|14.92385|15.55226|16.34334|17.25593|17.80704|18.20892|18.85317|19.30665
Adults|1|1|29.5|14.33478|14.54615|14.88866|15.51275|16.29584|17.19546|17.73667|18.13031|18.75949|19.20097
Adults|1|1|30.5|14.30257|14.51319|14.85414|15.47414|16.24972|17.13726|17.66932|18.05538|18.67078|19.10132
Adults|1|1|31.5|14.27093|14.48084|14.82027|15.43639|16.20495|17.08130|17.60495|17.98408|18.58695|19.00761
Adults|1|1|32.5|14.23989|14.44909|14.78707|15.39951|16.16150|17.02753|17.54351|17.91635|18.50792|18.91973
Adults|1|1|33.5|14.20948|14.41798|14.75453|15.36345|16.11933|16.97592|17.48496|17.85215|18.43363|18.83758
Adults|1|1|34.5|14.17972|14.38750|14.72264|15.32822|16.07843|16.92645|17.42927|17.79143|18.36400|18.76106
Adults|1|1|35.5|14.15063|14.35767|14.69142|15.29379|16.03876|16.87907|17.37639|17.73414|18.29895|18.69006
Adults|1|1|36.5|14.12223|14.32851|14.66086|15.26016|16.00030|16.83376|17.32627|17.68022|18.23842|18.62449
Adults|1|1|37.5|14.09453|14.30002|14.63096|15.22731|15.96304|16.79048|17.27889|17.62963|18.18231|18.56425


I am not sure what you mean by corresponding results..i guess this is what you meant..sorry if i am wrong

load data local infile '/home/micro/bmiagerev.csv' into table tbl_chart_preset_vitals_growth_percentiles fields terminated by '|' ignore 1 lines (patient_type,vital_types_id,gender_id,Age,P3,P5,P10,P25,P50,P75,P85,P90,P95,P97);
Query OK, 438 rows affected (0.16 sec)
Records: 438 Deleted: 0 Skipped: 0 Warnings: 0

load data local infile '/home/micro/lenageinf.csv' into table tbl_chart_preset_vitals_growth_percentiles fields terminated by '|' ignore 1 lines (patient_type,vital_types_id,gender_id,Age,P3,P5,P10,P25,P50,P75,P90,P95,P97);
Query OK, 74 rows affected, 1 warning (0.05 sec)
Records: 74 Deleted: 0 Skipped: 0 Warnings: 0

load data local infile '/home/micro/wtage.csv' into table tbl_chart_preset_vitals_growth_percentiles fields terminated by '|' ignore 1 lines (patient_type,vital_types_id,gender_id,Age,P3,P5,P10,P25,P50,P75,P90,P95,P97);
Query OK, 436 rows affected, 1 warning (0.08 sec)
Records: 436 Deleted: 0 Skipped: 0 Warnings: 0

load data local infile '/home/micro/wtageinf.csv' into table tbl_chart_preset_vitals_growth_percentiles fields terminated by '|' ignore 1 lines (patient_type,vital_types_id,gender_id,Age,P3,P5,P10,P25,P50,P75,P90,P95,P97);
Query OK, 76 rows affected, 1 warning (0.01 sec)
Records: 76 Deleted: 0 Skipped: 0 Warnings: 0

load data local infile '/home/micro/statage.csv' into table tbl_chart_preset_vitals_growth_percentiles fields terminated by '|' ignore 1 lines (patient_type,vital_types_id,gender_id,Age,P3,P5,P10,P25,P50,P75,P90,P95,P97);
Query OK, 436 rows affected, 1 warning (0.07 sec)
Records: 436 Deleted: 0 Skipped: 0 Warnings: 0

Please ignore the warnings in 4 of the load data commands..i am not uploading a the data for the column p85

There is another thing i noticed.... the total count of the records are

select count(*) from tbl_chart_preset_vitals_growth_percentiles;
+----------+
| count(*) |
+----------+
| 1460 |
+----------+

however the autoincrement id in show create table is way beyond the count(*).

In order to correct this problem i take a single csv dump after i load each file and then upload it back as a single file into the table.

Hope this info helps you find the mistake i am making or the bug in the software.

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.