Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?
Sven Köhler wrote: I was very disappointed by Interbase/Firebird. It seemed to me like a MS-Access: a database-engine that works on regular files Firebird seems simple, but it doesn't mean it's inferior or [intentionally] crippled like MS-Access. SQL server also works on "regular files" (db is stored as single files) and I believe FB is comparable to SQL server. In fact, I personally hate the fact that InnoDB can't work on "regular files" (db is not stored on single files or single directories). -- dave -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Money data type in MySQL?
What do people recommend for storing money amounts? I've seen people use NUMERIC(18,3) and other use NUMERIC(18,4). Which one is more appropriate and why? This is considering various existing currencies, some having low rates (like IDR, in which you can have large amount up to hundreds of trillions) and some high rates (like USD, in which you can have small amount like 0.1 cent). Are there places/industries which involve values lower than 0.1 cent? And what about 'factor' field in currency conversion table? Should I use FLOAT, or DOUBLE (do we need 15-16 digit precision?) or NUMERIC (exact numbers). The factor should range between 1E-3 (e.g. converting IDR to USD) to 1E4 (e.g. converting IDR to pounds/euros). -- dave -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Storing binary data (string with nulls/ASCII 0 in them) in CHAR/VARCHAR?
David Garamond wrote: insert into t1 values (3,char(32)); Record 3, 5, and 7 is rejected due to duplicate value. ++--+--+ | id | length(code) | code | ++--+--+ | 1 |0 | | | 2 |1 | 0| | 4 |2 | 0 0 | | 6 |2 | 32 0 | ++--+--+ 4 rows in set (0.00 sec) It seems the string in CHAR/VARCHAR is stored as null-terminated ala C. But why is record 3 rejected too? Ugh, never mind. I forgot about the automatic trailing blanks removal feature. Well, since trailing blanks are always removed, it means CHAR/VARCHAR could never store binary data then... Is there a 128-bit datatype planned? It would be handy to store GUID/UUID or IPv6 addresses. -- dave -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Storing binary data (string with nulls/ASCII 0 in them) in CHAR/VARCHAR?
Can CHAR/VARCHAR store strings with nul (ASCII 0) in them? It seems it can't. [I've used CHAR(2), VARCHAR(2), CHAR(2) BINARY, VARCHAR(2) BINARY]. === create table t1 ( id int not null primary key, code varchar(2) not null, unique(code)); insert into t1 values (1,''); insert into t1 values (2,char(0)); insert into t1 values (3,char(32)); insert into t1 values (4,concat(char(0),char(0))); insert into t1 values (5,concat(char(0),char(32))); insert into t1 values (6,concat(char(32),char(0))); insert into t1 values (7,concat(char(32),char(32))); select id, length(code), if(length(code)=0, '', if(length(code)=1, ord(code), concat(ord(substring(code,1,1)),' ',ord(substring(code,2,1))) ) ) as code from t1; === Record 3, 5, and 7 is rejected due to duplicate value. ++--+--+ | id | length(code) | code | ++--+--+ | 1 |0 | | | 2 |1 | 0| | 4 |2 | 0 0 | | 6 |2 | 32 0 | ++--+--+ 4 rows in set (0.00 sec) It seems the string in CHAR/VARCHAR is stored as null-terminated ala C. But why is record 3 rejected too? If I want to store 128-bit MD5 hash as a primary key, what would be the most compact way of storing them? -- dave -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
String functions for use in GROUP clause?
We can use SUM(), AVG(), COUNT(), etc. in GROUP clause. These are all
numeric functions. Is it possible to use string functions as well? I
know MIN() & MAX() can be asciibetical for string fields, but wouldn't
this be very handy/practical?
SELECT division,
G_CONCAT(", ",first_name) as nicks,
COUNT(*) as num_nicks
FROM employees
GROUP BY division;
will return:
division nicks num_nicks
- -
sales John, David, Billy 3
accounting David, Mickey 2
techsupp Alice, Bob, Dennis, John4
r&dLisa1
test 0
Other aggregate string functions might be G_MD5(), G_MAKE_SET(),
G_SPAM_RATING() :-)
Aggregate functions for sets might also be nifty to have sometimes.
--
dave
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Custom/conditional grouping?
Bruce Feist wrote: David Garamond <[EMAIL PROTECTED]> wrote: CREATE TABLE `payments` ( `date` date NOT NULL, `payee` varchar(255), `amount` double ) The 'payments' table records the amount of money that should be paid to each person every month. But the actual cheque is only given when the total accumulated amount has reached $50 or more for that person. I want to list the amount of money that needs to be paid by cheques. Can I do this with in pure SQL (instead of having to create logic in programming language)? SELECT payee, amount FROM payments GROUP BY payee HAVING amount > 49.995 Not exactly what I want. Actually I need to do a SUM of 'amount' per payee and per 'period', where period is one or more months. If in one month a person has not collected >= $50, then it will be accumulated and only paid when the amount has been sufficient. Another example: Suppose 'bruce' makes $30 in July, $80 in August, and $40 in Sep. Normally I would pay bruce's July earning in Aug. But seeing that bruce hasn't earned $50 in July, I hold his earning until Sep. In Sep I pay him Jul & Aug's earnings ($30+$80 = $110). Note that I will not be paying bruce's Sep earning in Oct, since in Sep bruce only earns $40. I will need to see whether in Oct bruce makes >= $10, in which case I'll need to pay him in Nov. So 'period' can differ depending on payee and amount. I hope I'm explaining it more clearly. Beware using 'double' for currency; it gives roundoff errors. Thanks for the tip. I do use DECIMAL for currencies/money. Sorry for the inappropriate type in the example. -- dave -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Custom/conditional grouping?
Haydies wrote: That was an amazingly vage question. Yup, sorry for that. Accidentally sent :) -- dave -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Custom/conditional grouping?
Victoria Reznichenko wrote: David Garamond <[EMAIL PROTECTED]> wrote: Can I do this with in pure SQL (instead of having to create logic in programming language)? What do you mean "Custom/conditional grouping"? Please, be more detailed. Sorry, please refer to my other post. The first email was accidentally sent. -- dave -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Custom/conditional grouping? (repost)
[Sorry for the accidental premature sending in the first post...]
CREATE TABLE `payments` (
`date` date NOT NULL,
`payee` varchar(255),
`amount` double
)
INSERT INTO payments VALUES ('2003-05-01','david',38);
INSERT INTO payments VALUES ('2003-06-02','david',13.4);
INSERT INTO payments VALUES ('2003-07-03','david',11.4);
INSERT INTO payments VALUES ('2003-08-04','david',5);
INSERT INTO payments VALUES ('2003-09-05','david',158.02);
INSERT INTO payments VALUES ('2003-05-09','lizzy',40.66);
INSERT INTO payments VALUES ('2003-06-08','lizzy',7.77);
INSERT INTO payments VALUES ('2003-07-07','lizzy',246);
INSERT INTO payments VALUES ('2003-08-06','lizzy',54);
INSERT INTO payments VALUES ('2003-09-10','lizzy',30);
The 'payments' table records the amount of money that should be paid to
each person every month. But the actual cheque is only given when the
total accumulated amount has reached $50 or more for that person. So for
example, 'lizzy' receives cheques in July (in May she only has $40.66,
and in June only $48.43 [40.66+7.77]) and August ($54). September
payment is not actually paid yet to her, she will have to wait until
October or November and so on until the amount has reached $50.
I want to list the amount of money that needs to be paid by cheques.
Can I do this with in pure SQL (instead of having to create logic in
programming language)?
Thanks in advance,
--
dave
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Custom/conditional grouping?
Can I do this with in pure SQL (instead of having to create logic in programming language)? Thanks in advance, -- dave -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Slowing brute force attack
Is there an option for mysqld to add a sleep() call after each failed connection request, before reporting to the client (to slow down brute force attack)? I glanced at the manual and there doesn't seem to be one. If there isn't, would it be a good idea? If yes, I could probably submit a patch. This feature should be trivial to implement, I think. -- dave -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mail daemons that uses database (mysql)
I'm just wondering if there is an MTA/MDA/IMAPD/Exchange-like app/other mail-related daemons that stores mail messages in a relational database like MySQL? Regards, -- dave -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: my_raid_seek assertion failed in mysqld log
David Garamond wrote: On Fri, Apr 04, 2003 at 01:22:39PM +0700, David Garamond wrote: i found this on my server log: mysqld-max: raid.cc:160: my_off_t my_raid_seek(int, long long unsigned int, int, int): Assertion `pos != (~(my_off_t) 0)' failed. and then mysqld shuts down. i start it again but after a short while the same error appears and mysqld stops again. what does this indicate? a disk failure? Oh, good. It's not just the machines at Yahoo, then. I haven't looked into it much yet, but we had a machine hit that a few times. That made me realize that I had been building our MySQL servers with raid support. We don't have any need for it, so I've removed it. But clearly something is funky with the raid code. I've yet to figure out a way to reproduce the bug. Well, I have't tried very hard either... Any chance you can? If so, getting it fixed shouldn't be a problem. we are using 4.0.12, binary RPMs provided at mysql.com. the machine got rebooted and reiserfsck shows some errors. i guess we'll be replacing the disk with another one for now... after the filesystem is clean, mysqld is still behaving the same. since we could not afford to have any more downtime, i downgraded the installation to 3.23.xx (it's 3.23.54a, not the latest but the RPM files were lying around so i just used them). the system's been running nicely since then. so i guess it's probably the 4.0.x code. that's all i could say for now. -- dave -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: my_raid_seek assertion failed in mysqld log
Jeremy Zawodny wrote: On Fri, Apr 04, 2003 at 01:22:39PM +0700, David Garamond wrote: i found this on my server log: mysqld-max: raid.cc:160: my_off_t my_raid_seek(int, long long unsigned int, int, int): Assertion `pos != (~(my_off_t) 0)' failed. and then mysqld shuts down. i start it again but after a short while the same error appears and mysqld stops again. what does this indicate? a disk failure? Oh, good. It's not just the machines at Yahoo, then. I haven't looked into it much yet, but we had a machine hit that a few times. That made me realize that I had been building our MySQL servers with raid support. We don't have any need for it, so I've removed it. But clearly something is funky with the raid code. I've yet to figure out a way to reproduce the bug. Well, I have't tried very hard either... Any chance you can? If so, getting it fixed shouldn't be a problem. we are using 4.0.12, binary RPMs provided at mysql.com. the machine got rebooted and reiserfsck shows some errors. i guess we'll be replacing the disk with another one for now... -- dave -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
my_raid_seek assertion failed in mysqld log
i found this on my server log: mysqld-max: raid.cc:160: my_off_t my_raid_seek(int, long long unsigned int, int, int): Assertion `pos != (~(my_off_t) 0)' failed. and then mysqld shuts down. i start it again but after a short while the same error appears and mysqld stops again. what does this indicate? a disk failure? -- dave -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
can 4.0.x be used to replace 3.23.xx _now_?
i read in several places (like in recent osnews.com newspiece and some other prior postings to this list) that mysql 4.0.x is supposed to be "stable" now. but the front page of mysql.com still lists 4.0.5 under "development". so does the mysql developers suggest that mysql is still "unstable" or "non-production" yet? i'd love to use some 4.x features, but i am reluctant of migrating my max-3.23.53a installations over to max-4.0.5 because of this. is migration recommended/acceptable now? (yes, i know the "if it ain't broken, don't fix it" saying. but i could use some of the new features of 4.x...) -- dave - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: rounding behaviour
David Garamond wrote: mysql> select 1/29; +--+ | 0.03 | +--+ mysql> select 100.0*1/29; ++ | 3.448 | ++ mysql> select 1/29*100.0; ++ | 3.45 | ++ sorry for the previous post, misleading problem statement. the exact problem for me is: mysql decides to automatically round numbers for me (which in some cases are undesirable, because i need the full precision floating point number from mysql and then format/process that number in my program). using ROUND() seems to be the solution for this, but i still wonder whether mysql should decides number rounding automatically in the absence of ROUND(). -- dave PS: mysql 3.23.49a-Max, linux on i386 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
rounding behaviour
hi, mysql> select 1/29; +--+ | 1/29 | +--+ | 0.03 | +--+ 1 row in set (0.00 sec) mysql> select 100.0*1/29; ++ | 100.0*1/29 | ++ | 3.448 | ++ 1 row in set (0.00 sec) mysql> select 1/29*100.0; ++ | 1/29*100.0 | ++ | 3.45 | ++ 1 row in set (0.00 sec) i am slightly puzzled by mysql's behaviour in the first case. this seems to be a float division, but why does mysql rounds it to two digits after decimal? if this is supposed to be an integer division, wouldn't it be better/more predictable for mysql to return 0? this behaviour could cause subtle problem/errors because people really didn't expect this kind of behaviour. for example, i just found out today, after weeks of operation, that my sql expression: SELECT ...,if(sum(CLICKS),sum(IMPS)/sum(CLICKS)*100.0,0) as CTR FROM T generates CTR that are rounded to two digits after decimal (yes, IMPS and CLICKS are integer fields). however, after i change the expression to this: SELECT ...,if(sum(CLICKS),100.0*sum(IMPS)/sum(CLICKS),0) as CTR FROM T all is well. -- dave - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQl db as filesystem.
Jan Steinman wrote: > Today's "modern" operating systems really stopped evolving in the 80's. Many ideas >like database filesystems never really got a chance to show their utility. isn't the next windows operating system (longhorn) supposed to have a new filesystem that's based on SQL Server? i also read that Microsoft had long contemplated about doing this, even before Windows XP/2000... -- dave - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
