Joined delete where records may or may not exist

Hello, mysql 4.0.18-standard-log (yeah, I know, I need to update, the date format changes are a nightmare :-)) I have two tables, cart, and products, I need to do this style delete: First, I need to join the two tables on the cart.product_id = products.id and delete those records where

Re: Server shutdown in progress

Hello. Please, provide the output of 'SHOW FULL PROCESSLIST'. Check if the problem disappears if you restart the server. Test this issue on the latest release, use an official binaries. [EMAIL PROTECTED] wrote: Hi all, I have a weird problem with MySQL 4.1 on a Debian stable

Re: Joined delete where records may or may not exist

Hi, I don't know if it is same but I think I had similar problem. Try this: DELETE master_tbl,detail_tbl FROM master_tbl LEFT JOIN detail_tbl ON master_tbl.ID=detail_tbl.ID WHERE . HTH, Dusan - Original Message - From: Scott Haneda [EMAIL PROTECTED] To: MySql

Problems with indexes on Date/DateTime fields

Hi all We are having a weird problem with some queries which are not using some indexes in date fields. Query-1 SELECT [field list] FROM tableX WHERE dateField = [any date expression or constant value] Query-2 SELECT [field list] FROM tableX WHERE dateField = [any date expression or constant

RE: Problems with indexes on Date/DateTime fields

Hi Aftab Thanks for your prompt answer. Yes, second query is doing full scan. I don't understand why a change in the operator can make the parser think a full scan will be better than use the index. Nevertheless we know the best option is to use the index, unfortunately we can not use FORCE

RE: Problems with indexes on Date/DateTime fields

Thanks Aftab We already think of that option as possible solution but I was just wondering why is Mysql changing the logic when the operator changes. I would like to know if there is any problem which cause Mysql to not use date indexes at least you use the = operator, because if that is the

RE: Lost Connection executing query

The fact that on your Mac installation you re-imported your table data makes me think it's an index efficiency issue. I have seen MySQL just lose the connection if the join becomes massively big. On re-import, the indexes would have been rebuilt and your query would be able to take advantage of

Re: Problems with indexes on Date/DateTime fields

Javier Diaz wrote: I would like to know if there is any problem which cause Mysql to not use date indexes at least you use the = operator, because if that is the case we will need to re-visit a few queries If you do a select instead of a delete, will the index be used? (You can check

Re: testing for connectivity to database

Hello. I'm trying to initialize to a database Does this mean that you're unable to connect to MySQL Server? What error message does mysql command line client report ? You said you were able to telnet to 3307 port - have you seen the MySQL protocol messages (usually seems like a garbage

Re: Understanding Unicode

Hello. Start your research from here: http://dev.mysql.com/doc/refman/5.0/en/charset.html Gary Huntress wrote: Hi, I have db users from all over the world. I only understand english. This is rarely a problem since I almost never need to look at users data, but every

Re: character_set_client in v4.1

Hello. How can I change character_set_client=greek than to latin? Have you tried set character_set_client='greek'? See: http://dev.mysql.com/doc/refman/5.0/en/charset-connection.html and the old Mysql-Front can't connect with tables after As far as I know fresh MySQL-Front should

Re: Capturing snapshots of content

Hello. But I will need to parse the dump output to append every single CREATE and INSERT statement to be the new table name. Does mysql cater for this or will I have to write a parse script to do this for me? Probaly instead of parsing the INSERT and CREATE statements, it would be

RE: Problems with indexes on Date/DateTime fields

Hi Jigal Thanks a lot for your answer. Sorry for the confusion about DELETE and SELECT. What we are trying to optimize are some DELETE statements, it was just that while investigating we found this behaviour of Mysql not using some date indexes if we change from using = operator to use = or =

SELECT help.

Can someone help me write a query to tell me the customer numbers (C_NO) of those who've had more than 4 transactions but none in the last 6 months? | transactions_table | | ID|C_NO|DATE | AMOUT| |2901| 387|2003-10-09|

Re: SELECT DISTINCT uses index but is still slow

0.01 seconds is so fast that I wonder if that's actually because the query cache is storing the query. Do you have query cache enabled? James At 6:35 am + 5/1/06, C.R.Vegelin wrote: Hi James, I have found similar - slowdown - effects for queries. However, it is not always clear what causes

Re: SELECT help.

- Original Message - From: Richard Reina [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, January 05, 2006 10:29 AM Subject: SELECT help. Can someone help me write a query to tell me the customer numbers (C_NO) of those who've had more than 4 transactions but none in

triggers on 5.0.17 -- definer not fully qualified

After upgrading to 5.0.17, the triggers on one of my instances now break replication with a definer is not fully qualified error. I set the DEFINER in the CREATE TRIGGER statement to CURRENT_USER (i.e., [EMAIL PROTECTED]), but the error still occurs. If I try setting the DEFINER to any other

Re: SELECT help.

3.23.54 Thanks. Rhino [EMAIL PROTECTED] wrote: - Original Message - From: Richard Reina To: Sent: Thursday, January 05, 2006 10:29 AM Subject: SELECT help. Can someone help me write a query to tell me the customer numbers (C_NO) of those who've had more than 4 transactions

Re: SELECT help.

Try this: SELECT c_no , SUM(1) as total_tx , SUM(if(`date` = now() - interval 6 month,1,0)) as recent_tx FROM transactions_table GROUP BY c_no HAVING total_tx 4 and recent_tx = 0; Shawn Green Database Administrator Unimin Corporation - Spruce Pine Rhino [EMAIL PROTECTED] wrote on

Re: SELECT help.

Richard Reina wrote: Can someone help me write a query to tell me the customer numbers (C_NO) of those who've had more than 4 transactions but none in the last 6 months? transactions_table | ID | C_NO |DATE | AMOUT | | 2901 | 387 | 2003-10-09 | 23.00 | Obviously my

Re: Problems with indexes on Date/DateTime fields

Javier Diaz wrote: EXPLAIN SELECT * FROM process_times WHERE date = date_sub(now(), INTERVAL 2 day) possible_keysdate_idx,date_proc_idx/possible_keys key(NULL)/key rows10778561/rows EXPLAIN SELECT * FROM process_times WHERE date = date_sub(now(), INTERVAL 2 day)

Re: SELECT help.

This should work: select c_name, count(t1.id) as t_count from customers c inner join transactions t1 on c.c_no = t1.c_no left join transactions t2 on c.c_no = t2.c_no and t2.date '2005-06-05' where t2.id is null group by c.c_no having t_count 4; There may be more efficient way of doing this

Strange insert

Hi all, I have an one insert statement in my hub page. For some reason, and just only on this link, I get two inserts happening about 2 seconds apart. I looked all over my code for a second insert statment, but it is just that one. Why should it be recording two inserts? This is what my

Re: an backup syntax error

wangxu wrote: Follow is my preform and result: - mysql backup table ht_detail to '/'; +++--+--+ | Table | Op | Msg_type | Msg_text

Re: Strange insert

I assume you did not intend to post to this list, but if you did then this is a problem with your application code, not with MySQL, so I'm afraid this list is not the best place to ask. James Harvard At 11:19 am -0600 5/1/06, Ngim wrote: Hi all, I have an one insert statement in my hub page.

RE: Strange insert

Sorry I apologize, it has to do something with my apache. -Original Message- From: James Harvard [mailto:[EMAIL PROTECTED] Sent: Thursday, January 05, 2006 11:46 AM To: Ngim Cc: mysql@lists.mysql.com Subject: Re: Strange insert I assume you did not intend to post to this list, but

Help with a SELECT query

Below is a query I'm trying to create and the sql for the table I'm pulling the information out of. The query is definitely not the best query out there especially since I'm still pretty new with sql. I know there has to be a better way of getting the information I want. A little background.

RE: Help...I am desparate

No...One machine eventually worked on port 1000, and another I changed the port back to the standard port 3306. I have run into this before and don't know what makes it finally run. My security folks asked me to run on port 1000 or another port less than 1024. Fortunately the machine they

Re: SELECT help.

Richard, Can someone help me write a query to tell me the customer numbers (C_NO) of those who've had more than 4 transactions but none in the last 6 months? Something like this? SELECT c_no, COUNT(c_no) AS cnt FROM transactions_table WHERE NOT EXISTS ( SELECT c_no FROM

MySQL database design documentation

Hi, I'm sure this is a stupid question, but I haven't been able to find it myself. Surely there must be a free PHP utility to web-administrate a MySQL database? I use CocoaMySQL (http://cocoamysql.sourceforge.net/) on my own Mac, but it isn't suitable for online databases. Can anyone lead me

RE: MySQL database design documentation

Hello, Two admin tools to check out if you haven't already... PHP, you can try PHPMyAdmin - http://www.phpmyadmin.net/home_page/index.php Non-PHP, try MySQL's GPL MySQL Administrator - http://dev.mysql.com/downloads/administrator/index.html However, they too may not be suitable for remote

Re: Help...I am desparate

I suppose you could set the suid bit on mysqld binary. But that itself will be a security risk. I am not sure why your security folks are insisting on ports below 1024. I hope they realize anything running under 1024 must be run as root. And anything running root can pose a serious security risk.

How come this update does not work??

How come this doesn't work? Wp_photos.photo = IMG_1234.JPG Pixelpost_pixelpost.headline = /this/path/to/directory/IMG_1234.JPG So I need to just match the latter-bit of the file. update pixelpost_pixelpost,wp_posts,wp_photos set

4.1.16: updates not using index prefixes

FYI, 4.1.16 appears not to be using prefixes of compound indexes when doing updates. Reverting to 4.1.15, or adding an index consisting of only the desired field, restores reasonable behavior. I have added feedback to a possibly-related bug, http://bugs.mysql.com/bug.php?id=15935, but wanted to

join question

Hi All, I have a question for clearer brains than mine. I would like to join two tables,. There may be many possible joins in table B to table A, but I only want to join one row from B to table A - the row with the closest, but lesser date. TABLE A Row Id date 1 46 3 Jan 7

Re: join question

http://dev.mysql.com/doc/refman/4.1/en/example-maximum-column-group-row.html I'd translate it to your example, but it's bed-time here in England! HTH, James Harvard At 11:42 pm + 5/1/06, Terry Spencer wrote: I have a question for clearer brains than mine. I would like to join two tables,.

Getting # of days until expiration

I have a DATE field that includes a date sometime within the past 30 days. I'm trying to show the number of days until the expiration date, which is 30 days in the future from the date in the field. I've tried a bunch of permutations of something like this: select (30 - SUBDATE(CURDATE() -

Re: Getting # of days until expiration

I have a DATE field that includes a date sometime within the past 30 days. I'm trying to show the number of days until the expiration date, which is 30 days in the future from the date in the field. I've tried a bunch of permutations of something like this: select (30 - SUBDATE(CURDATE() -

~75% performance drop upgrading from Mysql 4.0 to 5.0

Here are the timings: 64-bit 5.0 Single Thread 1:00:12.17 total (~76% slower) 64-bit 4.1 Single Thread 41:38.07 total (~20% slower) 64-bit 4.0 Single Thread 34:50.23 total I have been trying to get a stable configuration for a 64-bit mysql on ubuntu for the past 6-8 months, and have

Re: Getting # of days until expiration

Brian, I'm trying to show the number of days until the expiration date, which is 30 days in the future from the date in the field. DATEDIFF( DATE_ADD( datefield, INTERVAL 30 DAY), NOW() ) PB - Brian Dunning wrote: I have a DATE field that includes a date sometime within the past 30

Re: Lost Connection executing query

On 1/4/06, Tripp Bishop [EMAIL PROTECTED] wrote: We did recently upgrade the server from 4.0.40 to 5.0.15 and we did not dump the tables and reimport them. On the MAC we did do a dump and reimport. I wonder if that could be the cause of this problem. I had forgetten about that important

Re: Getting # of days until expiration

Thanks Peter, that appears to be exactly what I'm looking for, but it still gives an error and I've been through it with a fine-toothed comb, tried different versions, parens, etc. Here is the exact SQL statement I'm using, with your suggestion: select accounts.username,

mysqldump: Got errno 32 on write ?

mysqldump: Got errno 32 on write -- any ideas? I think it may be mysqldump is not run as user, because dump dir is 0700?? -- Anthony Ettinger Signature: http://chovy.dyndns.org/hcard.html

RE: mysqldump: Got errno 32 on write ?

Hi, Are you running mysqldump through into a pipe eg: into tar or similar? I mention this because : test1=perror 32 System error: 32 = Broken pipe Regards --- ** _/ ** David Logan *** _/ ***

replication with ssl

Hello, I am trying to ensure replication between master and slave is using ssl. I followed the instructions in section 6.4 of the reference guide and simply added REQUIRE SSL to the end of the grant statement when creating the replication user. Replication works when I don't require ssl,

Re: mysqldump: Got errno 32 on write ?

Turns out I forgot the gzip filename, only had | gzip filename. But the real problem here is I'm getting packet loss and loosing my connection, which is probably why I get that error 32. On 1/5/06, Anthony Ettinger [EMAIL PROTECTED] wrote: I am, I'm running a remote command mysqldump | gzip

Re: mysqldump: Got errno 32 on write ?

I am, I'm running a remote command mysqldump | gzip some file. On 1/5/06, Logan, David (SST - Adelaide) [EMAIL PROTECTED] wrote: Hi, Are you running mysqldump through into a pipe eg: into tar or similar? I mention this because : test1=perror 32 System error: 32 = Broken pipe Regards

Re: mysqldump: Got errno 32 on write ?

Anthony Ettinger wrote: Turns out I forgot the gzip filename, only had | gzip filename. But the real problem here is I'm getting packet loss and loosing my connection, which is probably why I get that error 32. On 1/5/06, Anthony Ettinger [EMAIL PROTECTED] wrote: I am, I'm running a

Re: Getting # of days until expiration

Brian Dunning wrote: Thanks Peter, that appears to be exactly what I'm looking for, but it still gives an error and I've been through it with a fine-toothed comb, tried different versions, parens, etc. Here is the exact SQL statement I'm using, with your suggestion: select

Problem with datetime value

Hi All, I got a problem with a query that involved datetime field. the table structure goes something like this : CREATE TABLE `price_log` ( `Item` char(20) NOT NULL default '', `Started` datetime NOT NULL default '-00-00