Export grid rows - doesn't export all selected rows

User, date Message
Written by lemon_juice
7 months ago
Category: Import/Export
95 posts since Tue, 29 Jun 10 		I have come across a problem when trying to export rows from one of my tables using "export grid rows". I go to the Data tab, select rows (about 200-300 - the problem seems to occur with larger selections), choose "Export grid rows" with these options:

- Copy to clipboard
- Output format: SQL INSERTs
- Row Selection: selection
- Options: Include column names & Include auto increment column

The result is that only 1 row from the selection is exported and it seems to be picked up randomly. Also, after the exporting my original selection changes so that only that one row is selected. The same happens when I try exporting to a file. In other words I am not able to export any significant number of selected rows.

I have seen this problem only on one of my tables so maybe it's dependent on the structure. Here is the table structure:


CREATE TABLE `sklep_produkty` (
`prod_id` MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT,
`producent_id` MEDIUMINT(8) UNSIGNED NULL DEFAULT NULL,
`prod_name` VARCHAR(255) NOT NULL COLLATE 'utf8_polish_ci',
`prod_name_edit` VARCHAR(255) NULL DEFAULT NULL COMMENT 'nazwa do edycji, moze zawierac dodatkowe znaczniki; NULL jesli taka sama jak prod_name' COLLATE 'utf8_polish_ci',
`prod_web_name` VARCHAR(200) NULL DEFAULT NULL COMMENT 'nazwa do adresu' COLLATE 'ascii_general_ci',
`price` DECIMAL(10,2) NULL DEFAULT NULL,
`prev_price` DECIMAL(10,2) NULL DEFAULT NULL COMMENT 'cena przekreslona',
`price_text` VARCHAR(50) NULL DEFAULT NULL COMMENT 'tekst w miejsce ceny, jesli nie ma ceny' COLLATE 'utf8_polish_ci',
`price_rent1` DECIMAL(10,2) NULL DEFAULT NULL COMMENT 'cena wypozyczenia na 1 dzien',
`price_rent2` DECIMAL(10,2) NULL DEFAULT NULL COMMENT 'cena wypozyczenia na 3 dni',
`show_price_range` TINYINT(1) NOT NULL DEFAULT '0' COMMENT 'czy pokazywac zakres cen wersji na liscie produktow',
`price_promotion` VARCHAR(200) NULL DEFAULT NULL COMMENT 'info o promocji cenowej' COLLATE 'utf8_polish_ci',
`price_promotion_link` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8_polish_ci',
`in_set` TEXT NULL COMMENT 'opis: w zestawie' COLLATE 'utf8_polish_ci',
`main_features` TEXT NULL COMMENT 'najwazniejsze cechy' COLLATE 'utf8_polish_ci',
`no_main_features_label` TINYINT(1) NOT NULL DEFAULT '0' COMMENT 'nie pokazuj opisu "najwazniejsze cechy"',
`short_desc` TEXT NULL COMMENT 'krotki opis' COLLATE 'utf8_polish_ci',
`long_desc` TEXT NULL COLLATE 'utf8_polish_ci',
`param` TEXT NULL COMMENT 'dane techniczne' COLLATE 'utf8_polish_ci',
`param_type` ENUM('list','specs','no_list') NOT NULL DEFAULT 'list' COMMENT 'sposob wyswietlania danych technicznych' COLLATE 'utf8_polish_ci',
`no_param_label` TINYINT(1) NOT NULL DEFAULT '0' COMMENT 'nie pokazuj opisu "dane techniczne" (nieuzywane)',
`show_main_features` TINYINT(1) NOT NULL DEFAULT '1' COMMENT 'pokazuj "najwazniejsze cechy" obok zdjecia',
`show_in_set` TINYINT(1) NOT NULL DEFAULT '0' COMMENT 'pokazuj "w zestawie" obok zdjecia',
`show_param` TINYINT(1) NOT NULL DEFAULT '0' COMMENT 'pokazuj "dane techniczne" obok zdjecia',
`big_pic_margin_x` TINYINT(3) UNSIGNED NULL DEFAULT NULL,
`big_pic_margin_y` TINYINT(3) UNSIGNED NULL DEFAULT NULL,
`hidden_flag` TINYINT(1) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'czy ukryty, wartosc do ustawiania w adminie',
`hidden` TINYINT(1) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'czy faktycznie ukryty; wartosc wyliczana z hidden_flag uwzgledniajac tez hidden_flag w kategorii i w kategoriach nadrzednych',
`ready` TINYINT(1) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'produkt gotowy (opracowany w adminie, czekajacy na dodanie ceny, itp.)',
`added_time` DATETIME NULL DEFAULT NULL,
`modified_time` DATETIME NULL DEFAULT NULL,
`to_correct` TINYINT(1) NOT NULL DEFAULT '0' COMMENT 'zawiera dane do poprawki',
`availability_id` TINYINT(3) UNSIGNED NOT NULL,
`auto_availability` TINYINT(1) NOT NULL DEFAULT '1' COMMENT 'przy wlaczonej automatycznej dostepnosci produkt ma dost. jak w availability_id, jesli go nie ma w magazynie, inaczej jest dostepny',
`availability_status` TINYINT(3) UNSIGNED NOT NULL DEFAULT '1' COMMENT '[POLE DO WYWALENIA] 0=na zamowienie jesli nie ma w mag.; 1=dostepnosc wg magazynu; bit 128=reczna dostepnosc',
`movie_link` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8_polish_ci',
`movie_win_width` SMALLINT(5) UNSIGNED NULL DEFAULT NULL,
`movie_win_height` SMALLINT(5) UNSIGNED NULL DEFAULT NULL,
`prod_name_sort` MEDIUMINT(9) NOT NULL COMMENT 'kolumna do sortowania naturalnego wg prod_name',
PRIMARY KEY (`prod_id`),
UNIQUE INDEX `prod_web_name` (`prod_web_name`),
INDEX `hidden` (`hidden`),
INDEX `ready` (`ready`),
INDEX `fk_sklep_produkty_sklep_producenci1` (`producent_id`),
INDEX `fk_sklep_produkty_sklep_dostepnosci1` (`availability_id`),
CONSTRAINT `fk_sklep_produkty_sklep_dostepnosci1` FOREIGN KEY (`availability_id`) REFERENCES `sklep_dostepnosci` (`availability_id`) ON UPDATE CASCADE ON DELETE NO ACTION,
CONSTRAINT `fk_sklep_produkty_sklep_producenci1` FOREIGN KEY (`producent_id`) REFERENCES `sklep_producenci` (`producent_id`) ON UPDATE CASCADE ON DELETE NO ACTION
)
COLLATE='utf8_polish_ci'
ENGINE=InnoDB
AUTO_INCREMENT=6127;
Written by ansgar
7 months ago
3975 posts since Fri, 07 Apr 06 		Cannot confirm - selection is kept on that table here. Though I'm unable to reproduce the situation exactly as I don't have the row data from your table, and I don't have the foreign key tables here. But that should make no difference in the data grid. Having 10 rows selected keeps the selection, and exports everything fine.

And you're sure that you have more than one row selected via ctrl+mouseclick, or ctrl+A?
Written by lemon_juice
7 months ago
95 posts since Tue, 29 Jun 10 		Yes, I am sure I am selecting the rows correctly. Foreign keys make no difference here. I can send you sample data of 300 rows which is enough to reproduce the problem. How can I send it to you? Do you have an email for this purpose?
Written by ansgar
7 months ago
3975 posts since Fri, 07 Apr 06 		Yes, email is linked in the footer here on heidisql.com.
Written by lemon_juice
7 months ago
95 posts since Tue, 29 Jun 10 		Did you get my email? I sent you the data yesterday.
Written by ansgar
7 months ago
3975 posts since Fri, 07 Apr 06 		Yes, got it. Give me one day or two, being a bit busy currently with work stuff.
Written by lemon_juice
7 months ago
95 posts since Tue, 29 Jun 10 		All right, thanks!
Written by ansgar
7 months ago
3975 posts since Fri, 07 Apr 06 		Have your file imported now. But cannot confirm what you described above. Hm, anything noticable in the SQL log when you right click the selected rows?
Written by lemon_juice
7 months ago
95 posts since Tue, 29 Jun 10 		It's strange this doesn't happen for you, I've just imported the same data to an empty test database and the problem occurs every time I try it. The log doesn't say much but there is a small difference between the problematic case when I select all 300 rows and the case when I select only first 3 rows (when a small number of rows are selected then everything works well).

When I right click on the selection of 300 rows then the log is filled with such statements:


SELECT `prod_id`, `producent_id`, `prod_name`, `prod_name_edit`, `prod_web_name`, `price`, `prev_price`, `price_text`, `price_rent1`, `price_rent2`, `show_price_range`, `price_promotion`, `price_promotion_link`, `in_set`, `main_features`, `no_main_features_label`, `short_desc`, `long_desc`, `param`, `param_type`, `no_param_label`, `show_main_features`, `show_in_set`, `show_param`, `big_pic_margin_x`, `big_pic_margin_y`, `hidden_flag`, `hidden`, `ready`, `added_time`, `modified_time`, `to_correct`, `availability_id`, `auto_availability`, `availability_status`, `movie_link`, `movie_win_width`, `movie_win_height`, `prod_name_sort` FROM `sklep_produkty_copy` WHERE `prod_id`=178;
/* Result #1 fetched. */



There seems to be one line like this for each row starting with prod_id=1 and finishing at prod_id=300. After the export is done then only 1 rows remains selected and this is logged:

SHOW CREATE TABLE `test`.`sklep_produkty_copy`;
/* Result #1 fetched. */
/* Ping server ... */
/* Fetching list of collations ... */



And this is the difference - when the export is successful (when only 3 rows are selected) then the above last segment of lines doesn't appear in the log - simply the export dialog window closes and nothing is written in the log - and the selection of 3 rows remains. I hope this can be of any help.

I'm using rev #4208 on Win 7 32-bit with AMD Athlon XP 64 X2 4000+ and 2GB RAM.
Written by ansgar
7 months ago
3975 posts since Fri, 07 Apr 06 		Eh, just tried again and now it happens here exactly as you describe... I think I did not select 300 but less rows yesterday. Could be related to the unprecise InnoDB row count. Will check.
Written by ansgar
7 months ago
3975 posts since Fri, 07 Apr 06 		No, looks like some rows make HeidiSQL believe it has to reload them. prod_id 43 and 44 for example do that each time you right click these.
Written by ansgar
7 months ago
3975 posts since Fri, 07 Apr 06 		Also, when you hover your mouse over the "param" column in row 43 and 44, you get an endless looping SELECT in the log.

Funny thing. Well, HeidiSQL loads text columns with a LEFT(col, 256) in order to keep results small, and tries to load full contents on demand. And that's where I have some bug.
Written by ansgar
7 months ago
3975 posts since Fri, 07 Apr 06 		Fixed in r4209
Written by lemon_juice
7 months ago
95 posts since Tue, 29 Jun 10 		Thanks, it works very well now!
Written by ansgar
7 months ago
3975 posts since Fri, 07 Apr 06 		Thank *you* for such a precise reproduction recipe. It's a pleasure to fix such bugs if you get immediate detailed feedback. In contrast to reports like "Export copy does not work. Please help!!" or so. Well, just a example, I get such reports quite often. Sometimes it's funny, but you know - fun stops at some point...
Written by lemon_juice
7 months ago
95 posts since Tue, 29 Jun 10 		Well, I'm a programmer myself and I know how tricky it can be for a developer to fix bugs which are obscure and activate themselves only in certain rare circumstances. Casual users don't realize this and often think that sending a general statement like "X doesn't work" is enough, I think it's because people who don't program think that programming is much easier and simpler than it is in reality!
 

Please login to leave a reply, or register at first.