error on slave.
Hi All, I set this parameters on master slave SET GLOBAL log_bin_trust_function_creators = 1; Then i applied a script that creates procedure and function. It got created on master, but on slave i am getting below error and procedure and function are not getting created on slave. select ROUTINE_NAME,ROUTINE_TYPE,ROUTINE_SCHEMA from routines; Empty set (0.00 sec) 'This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)' on query. Default database: 'reports1'. Query: 'CREATE [EMAIL PROTECTED] FUNCTION `next_day`(dt date, dn varchar(10)) RETURNS date
Re: Selecting rows by DATE ranges
On Monday 25 June 2007 10:32, Mogens Melander wrote: Looks like you have datetime fields makeing 2007-01-01 00:00:01 2007-01-01. Or use SELECT ... WHERE CONVERT(date,DATE)2007-01-01... if 'date' is a DATETIME field. Also using BETWEEN on date-ranges might help. On Sun, June 24, 2007 02:13, Miguel Cardenas wrote: Hello list I found a little problem with an application am developing, in particular creating reports by DATE ranges. Examples: select ... where date2007-01-01; returns all records where date is greater (and equal inclusive) to 2007-01-01 select ... where date=2007-01-01; returns all records where date is greater/equal to 2007-01-01 the and = have the same effect select ... where date2007-01-01 and date2007-01-20; returns all records where date is greater/equal to 2007-01-01 and less than 2007-01-20 *** DOES NOT RETURN RECORDS FROM DAY *20* select ... where date2007-01-01 and date=2007-01-20; returns all records where date is greater/equal to 2007-01-01 and less than 2007-01-20 although I'm using = *** DOES NOT RETURN RECORDS FROM DAY *20* My doubts are: 1. how can I retrieve rows with a date... NOT INCLUDING the day of the specified date, I mean apply a strict GREATHER THAN 2. how can I retrieve rows with date=... INCLUDING the day of the specified date. currently I have to do a date=date1 and date=date2+1day I need to retrieve rows in this way dateX date=X dateX date=X dateX and dateY date=X and dateY date=X and date=Y dateX and date=Y and so... didn't find a function to specify ranges of dates and the LESS THAN/EQUAL operator does not include the last day, so my reports with = are done by adding one day but don't like to use it this way since it could be confusing and generate errors on reports. Thanks for any comment, Miguel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- Later Mogens Melander +45 40 85 71 38 +66 870 133 224 -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Selecting rows by DATE ranges
Looks like you have datetime fields makeing 2007-01-01 00:00:01 2007-01-01. Also using BETWEEN on date-ranges might help. On Sun, June 24, 2007 02:13, Miguel Cardenas wrote: Hello list I found a little problem with an application am developing, in particular creating reports by DATE ranges. Examples: select ... where date2007-01-01; returns all records where date is greater (and equal inclusive) to 2007-01-01 select ... where date=2007-01-01; returns all records where date is greater/equal to 2007-01-01 the and = have the same effect select ... where date2007-01-01 and date2007-01-20; returns all records where date is greater/equal to 2007-01-01 and less than 2007-01-20 *** DOES NOT RETURN RECORDS FROM DAY *20* select ... where date2007-01-01 and date=2007-01-20; returns all records where date is greater/equal to 2007-01-01 and less than 2007-01-20 although I'm using = *** DOES NOT RETURN RECORDS FROM DAY *20* My doubts are: 1. how can I retrieve rows with a date... NOT INCLUDING the day of the specified date, I mean apply a strict GREATHER THAN 2. how can I retrieve rows with date=... INCLUDING the day of the specified date. currently I have to do a date=date1 and date=date2+1day I need to retrieve rows in this way dateX date=X dateX date=X dateX and dateY date=X and dateY date=X and date=Y dateX and date=Y and so... didn't find a function to specify ranges of dates and the LESS THAN/EQUAL operator does not include the last day, so my reports with = are done by adding one day but don't like to use it this way since it could be confusing and generate errors on reports. Thanks for any comment, Miguel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- Later Mogens Melander +45 40 85 71 38 +66 870 133 224 -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
can I optimize this query?
Hey all, I have 2 tables: Profiles(id). Relationships(id,friend_id,befriender_id). friend_id and befriender_id represent profiles ids. I want to find all the profiles that are neither friends neither befrienders with a given profile. this is the query I use with profile id=1: select * from profiles where profiles.id not in (SELECT profiles.id FROM profiles INNER JOIN relationships ON profiles.id = relationships.befriender_id WHERE (relationships.friend_id = 1 )) and profiles.id not in (SELECT profiles.id FROM profiles INNER JOIN relationships ON profiles.id = relationships.friend_id WHERE (relationships.befriender_id = 1 )); is there a better, faster way to do so? thanx in advance Pat -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Blob data
Ratheesh, If you still want to place you blob data in the database, seperate out the blob storage to a seperate table. This will help you alleviate few of your performance and maintenance problems. ~Alex On 6/22/07, Steve Edberg [EMAIL PROTECTED] wrote: At 12:11 PM +0530 6/22/07, Ratheesh K J wrote: Hello All, I want a clarification. Whe run a forum wherein people send messages with/without attachments. Attachments may contain images, documents etc.. We are actually storing the attachment in a blob column. Sometimes the attachments are big. And today the table size has grown to 40 GB. This has created a headache for any maintanance task, backup, restoration. etc. I want to know whether this is the right approach. Or should we actually store the attachments in directories and just stiore the attachment path in the database. Kindly suggest the best approach so that I can reduce the database size. Thanks in advance Yes, storing files - especially non-textual files - in the file system instead of the database is generally considered the best practice. At one point I had created a document management system that stored everything in the database as you are doing; my rationale was that it allowed me to manage permissions using the existing database permissions, and to back up the whole database using mysqldump, vs mysqldump + doing a tar of the files. However, I abandoned this approach for the following reasons: (1) Storing non-plaintext items (eg; pictures) in the database makes it bigger and slower without added value - you can't (at least not yet, or in the foreseeable future) do a meaningful search on a blob. (2) It becomes more difficult to split storage out onto multiple filesystems; eg, leaving the database files in /var/database, putting the documents themselves into /home/docmanager, etc. (3) It makes queries on the commandline unwieldy; if you have a blob field, doing a select * to check a record's contents can dump a lot of garbage on the screen. (4) It can make doing incremental backups more difficult; if the documents themselves are relatively static, but the document metadata stored in the database is very dynamic, it becomes simple to do a compact daily database dump + a weekly document directory backup (for example) if the files are not in the database. What I do is create a unique SHA1 hash when a file is uploaded (eg; sha1(rand()). The original filename and the 40-character hash are stored in the database, and the document is stored in the filesystem using the hash as the filename. I can optionally compress and encrypt the document as well, storing the encryption key in the database. This gives (for me) adequate document security. An additional advantage is that you can take advantage of the filesystem tree if you have a large number of documents. For example, if a document hash is 'f0118e9bd2c4fb29c64ee03abce698b8', you can store the file in the directory tree f0/11/8e/f0118e9bd2c4fb29c64ee03abce698b8 (extending to as many levels as you feel necessary). By keeping the number of files per directory fairly small, file retrieval becomes relatively fast. As the hashes approximate a random distribution, you should always have a close-to-balanced tree. Lastly, I store a hash of the document itself in the database as well. This allows me to detect if duplicate files are uploaded, and to determine if a previously-uploaded file has been corrupted in some way. steve -- +--- my people are the people of the dessert, ---+ | Steve Edberghttp://pgfsun.ucdavis.edu/ | | UC Davis Genome Center[EMAIL PROTECTED] | | Bioinformatics programming/database/sysadmin (530)754-9127 | + said t e lawrence, picking up his fork + -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Birthday format
Hello,
we have a simple query to calculate someones birthday:
SELECT (TO_DAYS(CURDATE()) - TO_DAYS('1952-10-06')) / 365;
= 54.75
Now is there away, using SQL, to remove the .75 without rounding off. we just
want the '54' value. We can do it easy enough with our Perl programming, but was
wondering if this can be accomplished within the actual query itself.
TIA,
Mike(mickalo)Blezien
===
Thunder Rain Internet Publishing
Providing Internet Solution that Work
http://www.thunder-rain.com
===
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Birthday format
we have a simple query to calculate someones birthday:
SELECT (TO_DAYS(CURDATE()) - TO_DAYS('1952-10-06')) / 365;
= 54.75
Now is there away, using SQL, to remove the .75 without rounding off. we
just want the '54' value. We can do it easy enough with our Perl
programming, but was wondering if this can be accomplished within the
actual query itself.
Wouldn't this work
SELECT FLOOR((TO_DAYS(CURDATE()) - TO_DAYS('1952-10-06')) / 365 ) AS age
? What version of mysql are you using?
thnx,
Chris
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Birthday format
Chris,
- Original Message -
From: Chris Boget [EMAIL PROTECTED]
To: Mike Blezien [EMAIL PROTECTED]; MySQL List
mysql@lists.mysql.com
Sent: Monday, June 25, 2007 2:15 PM
Subject: Re: Birthday format
we have a simple query to calculate someones birthday:
SELECT (TO_DAYS(CURDATE()) - TO_DAYS('1952-10-06')) / 365;
= 54.75
Now is there away, using SQL, to remove the .75 without rounding off. we just
want the '54' value. We can do it easy enough with our Perl programming, but
was wondering if this can be accomplished within the actual query itself.
Wouldn't this work
SELECT FLOOR((TO_DAYS(CURDATE()) - TO_DAYS('1952-10-06')) / 365 ) AS age
? What version of mysql are you using?
thnx,
Chris
Ok, this worked fine, thanks. We're using Ver., 4.1.22
Mike
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Birthday format
mysql SELECT FLOOR(54.75);
+--+
| FLOOR(54.75) |
+--+
| 54 |
+--+
1 row in set (0.05 sec)
Regards,
Jerry Schwartz
The Infoshop by Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032
860.674.8796 / FAX: 860.674.8341
www.the-infoshop.com
www.giiexpress.com
www.etudes-marche.com
-Original Message-
From: Mike Blezien [mailto:[EMAIL PROTECTED]
Sent: Monday, June 25, 2007 2:48 PM
To: MySQL List
Subject: Birthday format
Hello,
we have a simple query to calculate someones birthday:
SELECT (TO_DAYS(CURDATE()) - TO_DAYS('1952-10-06')) / 365;
= 54.75
Now is there away, using SQL, to remove the .75 without
rounding off. we just
want the '54' value. We can do it easy enough with our Perl
programming, but was
wondering if this can be accomplished within the actual query itself.
TIA,
Mike(mickalo)Blezien
===
Thunder Rain Internet Publishing
Providing Internet Solution that Work
http://www.thunder-rain.com
===
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Birthday format
thanks, worked prefectly,
Mike
- Original Message -
From: Jerry Schwartz [EMAIL PROTECTED]
To: 'Mike Blezien' [EMAIL PROTECTED]; 'MySQL List'
mysql@lists.mysql.com
Sent: Monday, June 25, 2007 3:01 PM
Subject: RE: Birthday format
mysql SELECT FLOOR(54.75);
+--+
| FLOOR(54.75) |
+--+
| 54 |
+--+
1 row in set (0.05 sec)
Regards,
Jerry Schwartz
The Infoshop by Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032
860.674.8796 / FAX: 860.674.8341
www.the-infoshop.com
www.giiexpress.com
www.etudes-marche.com
-Original Message-
From: Mike Blezien [mailto:[EMAIL PROTECTED]
Sent: Monday, June 25, 2007 2:48 PM
To: MySQL List
Subject: Birthday format
Hello,
we have a simple query to calculate someones birthday:
SELECT (TO_DAYS(CURDATE()) - TO_DAYS('1952-10-06')) / 365;
= 54.75
Now is there away, using SQL, to remove the .75 without
rounding off. we just
want the '54' value. We can do it easy enough with our Perl
programming, but was
wondering if this can be accomplished within the actual query itself.
TIA,
Mike(mickalo)Blezien
===
Thunder Rain Internet Publishing
Providing Internet Solution that Work
http://www.thunder-rain.com
===
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Birthday format
I might be wrong - but isn't that going to mess up on or around the birthday
because of leap years ?
I know its more complex - but something like :
select year(curdate())-year(1952-06-24)-
(dayofyear(curdate())dayofyear(1952-06-24)) age;
might work better...
On Monday 25 June 2007 21:07, Mike Blezien wrote:
thanks, worked prefectly,
Mike
- Original Message -
From: Jerry Schwartz [EMAIL PROTECTED]
To: 'Mike Blezien' [EMAIL PROTECTED]; 'MySQL List'
mysql@lists.mysql.com
Sent: Monday, June 25, 2007 3:01 PM
Subject: RE: Birthday format
mysql SELECT FLOOR(54.75);
+--+
| FLOOR(54.75) |
+--+
| 54 |
+--+
1 row in set (0.05 sec)
Regards,
Jerry Schwartz
The Infoshop by Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032
860.674.8796 / FAX: 860.674.8341
www.the-infoshop.com
www.giiexpress.com
www.etudes-marche.com
-Original Message-
From: Mike Blezien [mailto:[EMAIL PROTECTED]
Sent: Monday, June 25, 2007 2:48 PM
To: MySQL List
Subject: Birthday format
Hello,
we have a simple query to calculate someones birthday:
SELECT (TO_DAYS(CURDATE()) - TO_DAYS('1952-10-06')) / 365;
= 54.75
Now is there away, using SQL, to remove the .75 without
rounding off. we just
want the '54' value. We can do it easy enough with our Perl
programming, but was
wondering if this can be accomplished within the actual query itself.
TIA,
Mike(mickalo)Blezien
===
Thunder Rain Internet Publishing
Providing Internet Solution that Work
http://www.thunder-rain.com
===
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
Mike Aubury
Aubit Computing Ltd is registered in England and Wales, Number: 3112827
Registered Address : Murlain Business Centre, Union Street, Chester, CH1 1QP
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Birthday format
- Original Message -
From: Mike Aubury [EMAIL PROTECTED]
To: mysql@lists.mysql.com; Mike Blezien [EMAIL PROTECTED]
Cc: Jerry Schwartz [EMAIL PROTECTED]
Sent: Monday, June 25, 2007 3:44 PM
Subject: Re: Birthday format
I might be wrong - but isn't that going to mess up on or around the birthday
because of leap years ?
I know its more complex - but something like :
select year(curdate())-year(1952-06-24)-
(dayofyear(curdate())dayofyear(1952-06-24)) age;
might work better...
Kwel ... this works just as well.
Thx's
Mike
On Monday 25 June 2007 21:07, Mike Blezien wrote:
thanks, worked prefectly,
Mike
- Original Message -
From: Jerry Schwartz [EMAIL PROTECTED]
To: 'Mike Blezien' [EMAIL PROTECTED]; 'MySQL List'
mysql@lists.mysql.com
Sent: Monday, June 25, 2007 3:01 PM
Subject: RE: Birthday format
mysql SELECT FLOOR(54.75);
+--+
| FLOOR(54.75) |
+--+
| 54 |
+--+
1 row in set (0.05 sec)
Regards,
Jerry Schwartz
The Infoshop by Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032
860.674.8796 / FAX: 860.674.8341
www.the-infoshop.com
www.giiexpress.com
www.etudes-marche.com
-Original Message-
From: Mike Blezien [mailto:[EMAIL PROTECTED]
Sent: Monday, June 25, 2007 2:48 PM
To: MySQL List
Subject: Birthday format
Hello,
we have a simple query to calculate someones birthday:
SELECT (TO_DAYS(CURDATE()) - TO_DAYS('1952-10-06')) / 365;
= 54.75
Now is there away, using SQL, to remove the .75 without
rounding off. we just
want the '54' value. We can do it easy enough with our Perl
programming, but was
wondering if this can be accomplished within the actual query itself.
TIA,
Mike(mickalo)Blezien
===
Thunder Rain Internet Publishing
Providing Internet Solution that Work
http://www.thunder-rain.com
===
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
Mike Aubury
Aubit Computing Ltd is registered in England and Wales, Number: 3112827
Registered Address : Murlain Business Centre, Union Street, Chester, CH1 1QP
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Proxy 0.5.0 has been released
Dear MySQL users, We are proud to present a new kid in the block: the MySQL Proxy 0.5.0 alpha. MySQL Proxy is a simple program that sits between your client and MySQL server(s) that can monitor, analyze or transform their communication. Its flexibility allows for a wide variety of use cases, including: * load balancing * failover * query analysis * query filtering and modification * ... and many more We welcome and appreciate your feedback, bug reports, bug fixes, patches etc. You'll find preliminary documentation and plans on our project page on MySQL Forge: http://forge.mysql.com/wiki/MySQL_Proxy The MySQL Proxy 0.5.0 alpha release is now available in source and binary form for a number of platforms from our download pages at http://dev.mysql.com/downloads/mysql-proxy/0.5.html and mirror sites. Note that not all mirror sites may be up-to-date; if you can't find this version on a mirror, please try again later or choose another download site. Cheers, Jan -- Jan Kneschke, Enterprise Tools, Senior Software Dev, MySQL GmbH D-81373 München Radlkoferstr. 2, GF: Kaj Arnö - HRB München 162140 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Blob data
Ratheesh K J wrote: I want to know whether this is the right approach. Or should we actually store the attachments in directories and just stiore the attachment path in the database. Databases are designed to handle arbitrarily large numbers of rows of structured data, where each datum is small and roughly the same size as all others of its kind. Storing arbitrary data in a BLOB column is tantamount to trying to turn the database into a file system. If you want a file system, use a file system. Microsoft's been promising a database-backed file system for something like 15 years now. Maybe it's because they don't write software all that well up there in Redmond. Or maybe it's because this is one of those ideas that sounds good on paper but doesn't work out so well in practice. I'm betting on the latter. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
