[wishmaster]

Hi, guys.

I have transaction which delete rows in multiple queries.

$this->db->trans_begin()

...
Here I need a query that trying delete some rows and if the operation of delete this rows would be with error due to FK constraint, just goes to the next query without aborting (rolling back) the base transaction.
....
others queries
......
$this->db->trans_complete()

Any ideas?

[dave friend]

Are you using InnoDB tables? MyISAM does not support transactions.

[wishmaster]

It is InnoDB. I just do not know how to implement above logic.

[dave friend]

AFAIK, there isn't a way to exclude queries from being tracked once transactions begin.

The CI documentation shows a way to disable transactions but he example given will not work.

The example shows this

PHP Code:

$this->db->trans_off();

$this->db->trans_start();
$this->db->query('AN SQL QUERY...');
$this->db->trans_complete(); 


Again THIS WON'T WORK.
Once trans_off() is called the calls to both trans_start() and trans_complete() will immediately return FALSE and that is all.

CI does not provide a method that reverses a call to trans_off(). I'm not sure it matters because I'm not sure that MySql is capable of "pausing" a transaction session once it is started.

[wishmaster]

Here is a solution stackoverflow.com

Delete "bad rows" (or orphan rows) method :-).

[Narf]

(01-18-2018, 08:09 AM)wishmaster Wrote: Hi, guys.

I have transaction which delete rows in multiple queries.

$this->db->trans_begin()

...
Here I need a query that trying delete some rows and if the operation of delete this rows would be with error due to FK constraint, just goes to the next query without aborting (rolling back) the base transaction.
....
others queries
......
$this->db->trans_complete()

Any ideas?

I bolded the important parts because it's otherwise unclear what you want.

What you describe is the behavior when you're not inside a transaction ...

[wishmaster]

There is one table and ID from this one may be in ONE or MORE rows in the second table (FK relationship). So if I delete row in the second table, this operation will delete rows from either both tables or error occur. Therefore I need situation where each delete operation finishes without error, just either delete if possible or bypass error (due to FK constraint) and delete rows in the second row only.

[dave friend]

(01-20-2018, 12:10 AM)wishmaster Wrote: There is one table and ID from this one may be in ONE or MORE rows in the second table (FK relationship). So if I delete row in the second table, this operation will delete rows from either both tables or error occur. Therefore I need situation where each delete operation finishes without error, just either delete if possible or bypass error (due to FK constraint) and delete rows in the second row only.

I understand that you want to ignore errors due to a FK constraint. There is no way for errors inside a transaction to be ignored.

What is not clear is if you want to delete the row(s) in the FK related table(s) so that the primary row can be deleted without error.

I also don't understand what "delete rows in the second row only" means. What is "the second row"?

[wishmaster]

The first table (in my above notation):

Code:

fitem_name_1 | CREATE TABLE `fitem_name_1` (
 `fin_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `fin_name` varchar(512) NOT NULL,
 PRIMARY KEY (`fin_id`),
 KEY `fin_name` (`fin_name`(200))
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8

The second table:

Code:

| fitem_1 | CREATE TABLE `fitem_1` (
 `fi_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 ......
 `fi_name_id` int(10) unsigned NOT NULL,
 `fi_name_inner_id` int(10) unsigned DEFAULT NULL,
........
 `fi_credit` decimal(11,2) unsigned NOT NULL,
 PRIMARY KEY (`fi_id`),
.......
 KEY `fitem_1_ibfk_4` (`fi_name_id`),
 KEY `fitem_1_ibfk_6` (`fi_name_inner_id`),
 CONSTRAINT `fitem_1_ibfk_4` FOREIGN KEY (`fi_name_id`) REFERENCES `fitem_name_1` (`fin_id`),
 CONSTRAINT `fitem_1_ibfk_6` FOREIGN KEY (`fi_name_inner_id`) REFERENCES `fitem_name_inner_1` (`fini_id`)
) ENGINE=InnoDB AUTO_INCREMENT=134 DEFAULT CHARSET=utf8 |

Vary thanks for your help, but as I said I have found the solution. Just delete "orphan" rows in the first table on each invoking delete() method.