Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.

Search & replace text - heavily buggy

BubikolRamios's profile image BubikolRamios posted 4 years ago in General Permalink

Please do test on attached table & data

1.CTRL+F 2.text to find: "_1" 3.check replace "" 4.replace all

Observe sql-s created, no logic that I can see in any of those

UPDATE `test`.`test` SET `f`='foo' WHERE  `f`='foo_1';
/* SQL Error (1062): Duplicate entry 'foo' for key 'Index 1' */
UPDATE `test`.`test` SET `f`='foo' WHERE  `f`='foo_1';
/* SQL Error (1062): Duplicate entry 'foo' for key 'Index 1' */
UPDATE `test`.`test` SET `f`='faa' WHERE  `f`='foo_1' AND `f`='faa_1';

Besides, no smart way to leave table data tab, 'duplicate' keeps popping up. I think I already mentioned once: "Search & replace text" should have "ignore duplicates" option.

You can't break this search & replace, imagine 50 rows or hell more, " WHERE f='foo_1' AND f='faa_1'" tends to get longer and longer. if there are more columns in table. I think for each row in table with 10 columns, updates generated are +/- 10 !

CREATE TABLE IF NOT EXISTS `test` (
  `f` varchar(50) COLLATE utf8_slovenian_ci NOT NULL DEFAULT '0',
  `tttt` varchar(50) COLLATE utf8_slovenian_ci DEFAULT NULL,
  UNIQUE KEY `Index 1` (`f`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_slovenian_ci;


DELETE FROM `test`;

INSERT INTO `test` (`f`, `tttt`) VALUES
    ('foo', '1'),
    ('foo_1', '1'),
    ('faa', '2'),
    ('faa_1', '2'),
    ('fbb_1', '2'),
    ('fbb', '2');
ansgar's profile image ansgar posted 4 years ago Permalink

Yes, the replace logic seems quite buggy. I see these issues:

  1. Message dialogs with errors such as "Duplicate entry" do not break the whole operation. The whole replace operation should just break when some error happens during the UPDATE.
  2. UPDATE queries are malformed (2 WHERE clauses for the same column). This is most likely due to previous errors, and is probably fixed when issue 1. gets fixed
  3. On UPDATE errors, the not updated row keeps its red triangle in its modified cells, even when the replace loop has gone over to the next row. This should also be fixed when issue 1. gets fixed.

I can't promise I get some "Ignore duplicates" checkbox into the replace dialog. The problem is here that "Duplicate entry" is only one of several possible error messages from the server. Imagine someone else has modified the table in your back, and the value HeidiSQL displays does not even exist. You would get some other error then ("0 rows updated"). Or imagine the connection is broken suddenly.

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