Re: Kill DELETE Query
Thanks for the confirmation. sangprabv sangpr...@gmail.com http://www.petitiononline.com/froyo/ On Dec 17, 2010, at 1:31 PM, Ananda Kumar wrote: No...it will not. On Fri, Dec 17, 2010 at 11:26 AM, Willy Mularto sangpr...@gmail.com wrote: Thanks for the reply. I used non stored procedure approach. Another question is if I kill the process will it crash the table? Thanks. sangprabv sangpr...@gmail.com http://www.petitiononline.com/froyo/ On Dec 17, 2010, at 12:06 PM, Ananda Kumar wrote: If u have used a stored proc to delete the rows, and commting freqently, then the kill will happen faster. If you have just used delete from table_name where condition, then it would take toot much time to rollback all the deleted but not commited rows. Regards anandkl On Fri, Dec 17, 2010 at 8:37 AM, Willy Mularto sangpr...@gmail.com wrote: Hi List, I run a delete query to delete around 1 million rows in innodb table, It's been hours and still unfinish. Is it safe to kill that delete query process while the table is also inserting and updating other rows? Thanks. sangprabv sangpr...@gmail.com http://www.petitiononline.com/froyo/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=anan...@gmail.com
Re: Password Reset
Change password statements should show up in the binary logs, too, in some form or other. On Thu, Dec 16, 2010 at 9:58 PM, Alejandro Bednarik alejand...@olx.comwrote: SQL injection? Check Apache or whatever log files. On Thu, Dec 16, 2010 at 4:17 PM, Gary gp...@paulgdesigns.com wrote: I recieved a call from a client saying the web site did not work, turns out the database password was reset, and not by me. In looking in the DB after the PW was reset, I could find nothing out of place, although frankly I was not sure what to look for. Is this indicitive of an attack? Is this something to worry about? I had (or so I assumed) plenty of protections on the files, including one of the more popular anit-spam/injection attack systems. Any guidance on this would be appriciated. -- Gary __ Information from ESET Smart Security, version of virus signature database 5708 (20101216) __ The message was checked by ESET Smart Security. http://www.eset.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=alejand...@olx.com -- Infrastructure Team OLX Inc. Buenos Aires - Argentina Phone : 54.11.4775.6696 Mobile : 54.911.50436059 Email: alejand...@olx.com -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
mysqldumpslow
Is it possible to run mysqldumpslow on a remote host? -Partha -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Access to MySQL
On 17/12/2010, at 9:02 AM, Jerry Schwartz wrote: I have to move the back-end of an Access application to MySQL, and I’ve run into one issue that I haven’t been able to solve yet. The Access database stores dates as text in a “/mm/dd” format. The problem is that the default value is a formula that generates the current date, formatted as text. In Access, it looks like '=Format$(Now(),\/mm/dd\)' This construct is used throughout the table definitions. Is there any alternative to setting the default to something else (NULL, for example) and moving the “default” into the application code? That would be a significant PITA. If a 32-bit date range is enough, then you can use the timestamp data type. That supports having the current time as the default value. See also http://dev.mysql.com/doc/refman/5.1/en/timestamp.html Regards, Jesper
Re: Access to MySQL
What about this? date_format(now(), %Y/%m/%d) -- João Cândido de Souza Neto Jerry Schwartz je...@gii.co.jp escreveu na mensagem news:011201cb9d6c$eccc1e60$c6645b...@co.jp... I have to move the back-end of an Access application to MySQL, and I've run into one issue that I haven't been able to solve yet. The Access database stores dates as text in a /mm/dd format. The problem is that the default value is a formula that generates the current date, formatted as text. In Access, it looks like '=Format$(Now(),\/mm/dd\)' This construct is used throughout the table definitions. Is there any alternative to setting the default to something else (NULL, for example) and moving the default into the application code? That would be a significant PITA. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: mailto:je...@gii.co.jp je...@gii.co.jp Web site: http://www.the-infoshop.com/ www.the-infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Access to MySQL
-Original Message- From: Jesper Wisborg Krogh [mailto:my...@wisborg.dk] Sent: Friday, December 17, 2010 6:07 AM To: Jerry Schwartz Cc: 'MySQL' Subject: Re: Access to MySQL On 17/12/2010, at 9:02 AM, Jerry Schwartz wrote: I have to move the back-end of an Access application to MySQL, and I've run into one issue that I haven't been able to solve yet. The Access database stores dates as text in a /mm/dd format. The problem is that the default value is a formula that generates the current date, formatted as text. In Access, it looks like '=Format$(Now(),\/mm/dd\)' This construct is used throughout the table definitions. Is there any alternative to setting the default to something else (NULL, for example) and moving the default into the application code? That would be a significant PITA. If a 32-bit date range is enough, then you can use the timestamp data type. That supports having the current time as the default value. See also http://dev.mysql.com/doc/refman/5.1/en/timestamp.html [JS] Unfortunately, I have to keep that field as a text field. Also, a timestamp would change every time a record is updated and you can only have one per record. Thanks for trying. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.com Regards, Jesper -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Access to MySQL
-Original Message- From: João Cândido de Souza Neto [mailto:j...@consultorweb.cnt.br] Sent: Friday, December 17, 2010 6:11 AM To: mysql@lists.mysql.com Subject: Re: Access to MySQL What about this? date_format(now(), %Y/%m/%d) [JS] I don't think you can use anything but a constant as a default value. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.com -- João Cândido de Souza Neto Jerry Schwartz je...@gii.co.jp escreveu na mensagem news:011201cb9d6c$eccc1e60$c6645b...@co.jp... I have to move the back-end of an Access application to MySQL, and I've run into one issue that I haven't been able to solve yet. The Access database stores dates as text in a /mm/dd format. The problem is that the default value is a formula that generates the current date, formatted as text. In Access, it looks like '=Format$(Now(),\/mm/dd\)' This construct is used throughout the table definitions. Is there any alternative to setting the default to something else (NULL, for example) and moving the default into the application code? That would be a significant PITA. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: mailto:je...@gii.co.jp je...@gii.co.jp Web site: http://www.the-infoshop.com/ www.the-infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=je...@gii.co.jp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Access to MySQL
Hi Jerry, On 12/17/2010 09:34, Jerry Schwartz wrote: -Original Message- From: Jo�o C�ndido de Souza Neto [mailto:j...@consultorweb.cnt.br] Sent: Friday, December 17, 2010 6:11 AM To: mysql@lists.mysql.com Subject: Re: Access to MySQL What about this? date_format(now(), %Y/%m/%d) [JS] I don't think you can use anything but a constant as a default value. You are correct with one exception that was already mentioned earlier: the TIMESTAMP storage type. from http://dev.mysql.com/doc/refman/5.1/en/create-table.html ### The DEFAULT clause specifies a default value for a column. With one exception, the default value must be a constant; it cannot be a function or an expression. This means, for example, that you cannot set the default for a date column to be the value of a function such as NOW() or CURRENT_DATE. The exception is that you can specify CURRENT_TIMESTAMP as the default for a TIMESTAMP column. See Section 10.3.1.1, “TIMESTAMP Properties”. ### However, nothing says you can't use a function or other computation in a TRIGGER to set the default value to an empty column of a new row to whatever you wanted it to be. http://dev.mysql.com/doc/refman/5.1/en/create-trigger.html I know it's a workaround but it will keep the default value management out of your application and inside the database. -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
A better REPAIR TABLE for myisam tables (or for upgrading tables)
I've posted a similar post in the past -- but there I was mucking around with blank index files and frm files to fool myisamchk into repairing a table. But now I think I've come across a much better and more efficient way to do a REPAIR Table in order to upgrade my database tables from Mysql 4.1.x to 5.5.8. All this comes from the fact that REPAIR TABLE does not rebuild the table indexes like myisamchk does, which is very unfortunate. Sure, REPAIR TABLE works great for small tables, but if you have any tables of larger size (millions of records or more, with multiple indexes), REPAIR TABLE can take hours or days to do a simple repair/upgrade. And in most cases, applications just can't be down for that long during an upgrade cycle (not everyone runs a huge shop with multiple dev/test/upgrade/production servers). So here is what I have done, and propose this as a better REPAIR TABLE for MYISAM tables (in pseudo code): 1. Retrieve the original CREATE TABLE DDL with show create table SOURCE 2. Modify DDL to change the table name to a new target table, let's call it TARGET 3. Execute new DDL to create empty TARGET table 4. Run 'myisamchk -r --keys-used=0 TARGET' (to disable all index keys on new table) 5. flush tables; lock table SOURCE read, TARGET write; 6. insert into TARGET select * From SOURCE; 7. flush tables; unlock tables; 8. 'myisamchk -prqn TARGET' (repair to re-enable all keys, do not modify MYD table, use sorting, in parallel) 9. rename tables to replace SOURCE with TARGET I've written a PHP script to do exactly this, and it works beautifully. My source tables are mysql 4.1.x tables, and the target tables are now fully 5.5 compliant (verified with mysqlcheck --check-upgrade). The best part is that for tables with 50 million short rows, it ran in 7 minutes, and a table with 30 million rows, it ran in 4 minutes. I'm now running it on a table with over 200 million rows, and I expect it to take an hour or so... but in all cases, doing a REPAIR TABLE on any of these large tables would take days to complete. So why can't the REPAIR TABLE command do something like this in the background for large MYISAM tables? -Hank
RE: Access to MySQL
-Original Message- From: Shawn Green (MySQL) [mailto:shawn.l.gr...@oracle.com] Sent: Friday, December 17, 2010 10:35 AM To: Jerry Schwartz Cc: j...@consultorweb.cnt.br; mysql@lists.mysql.com Subject: Re: Access to MySQL Hi Jerry, On 12/17/2010 09:34, Jerry Schwartz wrote: -Original Message- From: Jo?o C?ndido de Souza Neto [mailto:j...@consultorweb.cnt.br] Sent: Friday, December 17, 2010 6:11 AM To: mysql@lists.mysql.com Subject: Re: Access to MySQL What about this? date_format(now(), %Y/%m/%d) [JS] I don't think you can use anything but a constant as a default value. You are correct with one exception that was already mentioned earlier: the TIMESTAMP storage type. from http://dev.mysql.com/doc/refman/5.1/en/create-table.html ### The DEFAULT clause specifies a default value for a column. With one exception, the default value must be a constant; it cannot be a function or an expression. This means, for example, that you cannot set the default for a date column to be the value of a function such as NOW() or CURRENT_DATE. The exception is that you can specify CURRENT_TIMESTAMP as the default for a TIMESTAMP column. See Section 10.3.1.1, TIMESTAMP Properties. ### However, nothing says you can't use a function or other computation in a TRIGGER to set the default value to an empty column of a new row to whatever you wanted it to be. http://dev.mysql.com/doc/refman/5.1/en/create-trigger.html I know it's a workaround but it will keep the default value management out of your application and inside the database. [JS] Thanks. I did have that in the back of my mind, but to be honest I never used a trigger. I'll have to think about this. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.com -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=je...@gii.co.jp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
max allowed packets on Mac
Hi, I found this page on the wiki regarding max allowed packets. http://dev.mysql.com/doc/refman/5.0/en/packet-too-large.html However, on my Mac OSX when I try to run the following from terminal window I get Permission Denied shell mysqld --max_allowed_packet=16M And if I try to run as sudo but I again get Permission Denied even though I am logged in as Admin on my machine. So I tried to chmod 777 on mysqld but again received an error: chmod: Unable to change file mode on mysqld: Operation not permitted Any help on how I can rectify this would be appreciated. Thanks, Greg -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: A better REPAIR TABLE for myisam tables (or for upgrading tables)
Sorry... One small correction to my above post.. 'FLUSH TABLES' should be issued between steps 8 and 9. My 200+ million record table completed in 71 minutes. -Hank mysql; query;
Re: max allowed packets on Mac
I'm not nuts about deploying MySQL on macs, but I think you need to assume root. As your mac admin user, try $ sudo su - and give your admin password when asked. You should now be root and can expect permissions to behave as you would expect. - michael dykman On Fri, Dec 17, 2010 at 12:16 PM, g...@noiseunit.com g...@noiseunit.com wrote: Hi, I found this page on the wiki regarding max allowed packets. http://dev.mysql.com/doc/refman/5.0/en/packet-too-large.html However, on my Mac OSX when I try to run the following from terminal window I get Permission Denied shell mysqld --max_allowed_packet=16M And if I try to run as sudo but I again get Permission Denied even though I am logged in as Admin on my machine. So I tried to chmod 777 on mysqld but again received an error: chmod: Unable to change file mode on mysqld: Operation not permitted Any help on how I can rectify this would be appreciated. Thanks, Greg -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=mdyk...@gmail.com -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
