Problem with a purge procedure in a table | Amministrazione, Gestione, Backup

Topic: Pubblico - Composto da 4 Posts di 2 Utenti.

12 Agosto, 2010 13:50	#1
zorrosam Utente Registrato: Aug, 2010 Posts: 2 Offline	Hi guys! i have to implement a procedure for purge daily a table of a database. The script works correctly from phpmyadmin ... logged like root. DELETE FROM snmptt WHERE traptime < date_add(sysdate(), interval -7 day); The database is use for store tha trap snmp connected to nagios (SNMPTT). My idea is purge daily the db and mantain the last week records. The problem is that when i run the script i obtain this error : SCRIPT : # delete records older than 7 days use snmpttdslam; DELETE FROM snmptt WHERE traptime < date_add(sysdate(), interval -7 day); OPTIMIZE TABLE `snmptt`; exit ERROR : Delete records old more than one week snmptt tables from snmpttdslam database... ERROR 1175 (HY000) at line 1: You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column +--------------------+----------+----------+----------+ \| Table \| Op \| Msg_type \| Msg_text \| +--------------------+----------+----------+----------+ \| snmpttdslam.snmptt \| optimize \| status \| OK \| +--------------------+----------+----------+----------+ I can't change the primary key in the db ... so ... what can i do? please provide me more info! thanks sam

12 Agosto, 2010 16:14	#2
g2d Moderatore Registrato: Jul, 2008 Posts: 867 Offline	Hi zorrosam, Welcome to MySQL Italia, Italian community of MySQL. We are honored to welcome you as our user. Sorry for our English, you are the first not italian user. I'm documenting your problem, but the error code 1175 it's not recognized by my mysql. What is your MySQL Version ? if you run OPTIMIZE TABLE `snmptt`; from PhpMyAdmin what is the result? Let us know Thank you algweb Un tempo ero algweb ora sono g2d

13 Agosto, 2010 07:30	#3
zorrosam Utente Registrato: Aug, 2010 Posts: 2 Offline	Yo man, [url=http://www.mysqlfaqs.net/mysql-faqs/Client-Server-Commands/What-does---safe-updates-option-in-mysql]What does --safe-updates option in mysql?[/url] Answer No: 196 It's possible to inadvertently issue statements that modify many rows in a table or that return extremely large result sets. The --safe-updates option helps prevent these problems. The option is particularly useful for people who are just learning to use MySQL. --safe-updates has the following effects: UPDATE and DELETE statements are allowed only if they include a WHERE clause that specifically identifies which records to update or delete by means of a key value, or if they include a LIMIT clause. Output from single-table SELECT statements is restricted to no more than 1,000 rows unless the statement includes a LIMIT clause. Multiple-table SELECT statements are allowed only if MySQL will examine no more than 1,000,000 rows to process the query. I have found the solution ... i have three different choices ... the firs one and maybe the easieast is disable the safe updates in my sql installation ... and it works! Probably the second and the third options are : * Create a key on `traptime` * Add a column that already has a key to your where clause ... in any case than you for your answer and ... w l'opensource! zorro

13 Agosto, 2010 07:30

zorrosam
Utente

Registrato: Aug, 2010
Posts: 2
Offline

Yo man,

[url=http://www.mysqlfaqs.net/mysql-faqs/Client-Server-Commands/What-does---safe-updates-option-in-mysql]What does --safe-updates option in mysql?[/url]

Answer No: 196

It's possible to inadvertently issue statements that modify many rows in a table or that return extremely large result sets. The --safe-updates option helps prevent these problems. The option is particularly useful for people who are just learning to use MySQL. --safe-updates has the following effects:

UPDATE and DELETE statements are allowed only if they include a WHERE clause that specifically identifies which records to update or delete by means of a key value, or if they include a LIMIT clause.
Output from single-table SELECT statements is restricted to no more than 1,000 rows unless the statement includes a LIMIT clause.
Multiple-table SELECT statements are allowed only if MySQL will examine no more than 1,000,000 rows to process the query.

I have found the solution ... i have three different choices ... the firs one and maybe the easieast is disable the safe updates in my sql installation ... and it works! Probably the second and the third options are : * Create a key on `traptime` * Add a column that already has a key to your where clause ... in any case than you for your answer and ... w l'opensource! zorro

13 Agosto, 2010 07:37	#4
g2d Moderatore Registrato: Jul, 2008 Posts: 867 Offline	Hi zorrosam, I think you could limit to add the key on traptime, the safe--updates option is very useful in many other cases. how many rows do you purge in the traptime table ? over 1000 ? Thanks algweb Un tempo ero algweb ora sono g2d

Condividi su:

Loggati o Registrati per replicare