deleteMultiple with setLimit doesn't actually set the limit of records to delete.

[Chris Nord]

So I am trying to run a deleteMultiple on a GlideRecord query with a limit. What I am seeing is that setLimit doesn't actually affect deleteMultiple, only the query itself. I find this to be a design flaw since limits do work with SQL delete statements (MySQL :: MySQL 5.6 Reference Manual :: 13.2.2 DELETE Syntax).

What I am trying to do is deleting 45 million rows that remain in sys_audit after I have disabled auditing on the tables and the fields. I wrote this simple script that was intended to run like every 5 minutes via scheduled job, but for debugging purposes this was an "On Demand" job at this moment.

var tables = ["cmdb_ci",
   "cmdb_ci_computer_room",
   "cmdb_ci_datacenter",
   "cmdb_ci_db_catalog",
   "cmdb_ci_db_instance",
   "cmdb_ci_db_mongodb_instance",
   "cmdb_ci_db_mssql_catalog",
   "cmdb_ci_db_mssql_instance",
   "cmdb_ci_db_mysql_instance",
   "cmdb_ci_db_ora_instance",
   "cmdb_ci_db_ora_listener",
   "cmdb_ci_db_postgresql_instance",
   "cmdb_ci_disk",
   "cmdb_ci_disk_partition",
   "cmdb_ci_dns_name",
   "cmdb_ci_file_system",
   "cmdb_ci_group",
   "cmdb_ci_hardware",
   "cmdb_ci_ip_address",
   "cmdb_ci_ip_network",
   "cmdb_ci_ip_phone",
   "cmdb_ci_ip_router",
   "cmdb_ci_ip_switch",
   "cmdb_ci_nas_file_system",
   "cmdb_ci_network_adapter",
   "cmdb_ci_outage",
   "cmdb_ci_print_queue",
   "cmdb_ci_printer",
   "cmdb_ci_rack",
   "cmdb_ci_san",
   "cmdb_ci_service",
   "cmdb_ci_solaris_server",
   "cmdb_ci_storage_device",
   "cmdb_ci_storage_hba",
   "cmdb_ci_storage_pool",
   "cmdb_ci_storage_pool_member",
   "cmdb_ci_storage_server",
   "cmdb_ci_storage_switch",
   "cmdb_ci_storage_volume",
   "cmdb_ci_tomcat_connector",
   "cmdb_ci_unix_server",
   "cmdb_ci_ups",
   "cmdb_ci_vm_instance",
   "cmdb_ci_vm_vmware",
   "cmdb_model"].join();
var set = new GlideRecord("sys_audit");
set.addQuery("tablename", "IN", tables);
set.setLimit(100);
set.deleteMultiple();
gs.log(set.getRowCount());

With this code I am getting these SQL output.

SELECT sys_audit0.`sys_id` FROM sys_audit sys_audit0   WHERE sys_audit0.`tablename` IN ('cmdb_ci' , 'cmdb_ci_computer_room' , 'cmdb_ci_datacenter' , 'cmdb_ci_db_catalog' , 'cmdb_ci_db_instance' , 'cmdb_ci_db_mongodb_instance' , 'cmdb_ci_db_mssql_catalog' , 'cmdb_ci_db_mssql_instance' , 'cmdb_ci_db_mysql_instance' , 'cmdb_ci_db_ora_instance' , 'cmdb_ci_db_ora_listener' , 'cmdb_ci_db_postgresql_instance' , 'cmdb_ci_disk' , 'cmdb_ci_disk_partition' , 'cmdb_ci_dns_name' , 'cmdb_ci_file_system' , 'cmdb_ci_group' , 'cmdb_ci_hardware' , 'cmdb_ci_ip_address' , 'cmdb_ci_ip_network' , 'cmdb_ci_ip_phone' , 'cmdb_ci_ip_router' , 'cmdb_ci_ip_switch' , 'cmdb_ci_nas_file_system' , 'cmdb_ci_network_adapter' , 'cmdb_ci_outage' , 'cmdb_ci_print_queue' , 'cmdb_ci_printer' , 'cmdb_ci_rack' , 'cmdb_ci_san' , 'cmdb_ci_service' , 'cmdb_ci_solaris_server' , 'cmdb_ci_storage_device' , 'cmdb_ci_storage_hba' , 'cmdb_ci_storage_pool' , 'cmdb_ci_storage_pool_member' , 'cmdb_ci_storage_server' , 'cmdb_ci_storage_switch' , 'cmdb_ci_storage_volume' , 'cmdb_ci_tomcat_connector' , 'cmdb_ci_unix_server' , 'cmdb_ci_ups' , 'cmdb_ci_vm_instance' , 'cmdb_ci_vm_vmware' , 'cmdb_model') ORDER BY sys_audit0.`sys_id` limit 0,800

and the following update/deletes which have over 100 sys_id's in them to be deleted. I truncated them for sake of the length of this question.

DELETE FROM sys_audit WHERE sys_audit.`sys_id` IN ('00c8f5d8b4eb42007fcad0c29b1038f7' , '00c8f5d8b4eb42007fcad0c29b1038f6' , .....

UPDATE sys_audit_relation   SET `audit`= NULL WHERE sys_audit_relation.`audit` IN ('00c8f5d8b4eb42007fcad0c29b1038f7' , '00c8f5d8b4eb42007fcad0c29b1038f6' , .....

Just to play around I have tried calling set.query() after setLimit without any luck at all as well.

So from my findings setLimit doesn't affect deleteMultiple at all even though in the past people have recommended it. Is this in intended or a bug? Has anyone else had problems with limiting deleteMultiple.

As discussed about in

Re: Re: How do I bulk delete users?

Re: How to delete 100 or 1000 records at a time in ServiceNow.

Re: Deleting Attachments by Loan State

Re: Deleteing data from table cmdb_ci for condition   "sys_class_name=cmdb_ci"

[kushal Tayade]

This is what i know from the documentation. 

deleteMultiple()
Deletes multiple records that satisfy the query condition.

This method does not delete attachments.

Do not use deleteMultiple() on tables with currency fields. Always delete each record individually. Also, do not use this method with the chooseWindow() or setLimit() methods when working with large tables.

[johnfw]

Hi Kushal,

Could you explain why deleteMultiple() has precedence over setLimit() ?

Is this documented somewhere?

Thanks,

John