Re: Which AMD Dual Core Processor?
mos wrote: AMD Athlon 64X2 3800+ Dual Core S939 Manchester (2x512K cache) AMD Athlon 64X2 4200+ Dual Core S939 Manchester (2x512k cache) AMD Athlon 64X2 4400+ Dual Core S939 Toledo (2x1MB cache) AMD Athlon 64X2 4600+ Dual Core S939 Manchester (2x512k cache) I would think, as a blind guess, that the Toledo processor (twice the cache) would be the hands-down winner in this list. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Will SELECT COUNT(*) FROM TABLE ever be cheap on INNODB?
[EMAIL PROTECTED] wrote: If I understand the InnoDB engine correctly, I don't see how they could speed it up unless they start tracking how many records belong to each active version within a database. But one thing you can do to speed it up somewhat is to do a COUNT(PK_column) (rather than COUNT(*)) if you have a column that is a primary key for the table - that's the same thing semantically, and involves searching an index rather than the data records themselves, which should involve less I/O. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: insert subquery
[EMAIL PROTECTED] wrote:
Well, Gordon, looks like you missed the thread. ;-)
Maybe, but his solution is actually pretty close to what the OP wanted
Instead of
INSERT INTO Table1 (...) VALUES ('val1', 'val2', 'val3', ...) something
you do
INSERT INTO Table1 (...)
SELECT 'val1', 'val2', 'val3', ...
FROM Table2 WHERE Table2.keycol = 'valtocheck'
Example:
[...CSIMain/csi] 27. mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.14-nt
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql use test
Database changed
mysql create table table1(v1 varchar(20), v2 varchar(20));
Query OK, 0 rows affected (0.18 sec)
mysql create table table2(k1 varchar(20) primary key, v1 varchar(20));
Query OK, 0 rows affected (0.08 sec)
mysql insert into table2 values('1', 'v1');
Query OK, 1 row affected (0.04 sec)
mysql insert into table2 values('2', 'v2');
Query OK, 1 row affected (0.04 sec)
NOW, you want to insert some set of values only if some value (one of
the ones being inserted, or something else in this example) is in table2:
mysql insert into table1 select 's1', 's2' from table2 where k1 = '0';
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql insert into table1 select 's1', 's2' from table2 where k1 = '1';
Query OK, 1 row affected (0.04 sec)
Records: 1 Duplicates: 0 Warnings: 0
Voila!
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Regarding NULL and '' (null string) treatment in MYSQL
Peter Brawley wrote: Perhaps Oracle also has such a setting too. MySQL doesn't. As a matter of fact, Oracle goes the other way in that if you store '' into a VARCHAR field, it actually stores a NULL there. But it's inconsistent in that doesn't consider a NULL varchar column to be = '' (a literal '', I mean; or even a PL/SQL variable assigned a '' value). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Regarding Connection Context
[EMAIL PROTECTED] wrote: A runtime context, usually simply called a context, is a handle to a an area in client memory which contains zero or more connections, zero or more cursors, their inline options (such as MODE, HOLD_CURSOR, RELEASE_CURSOR, SELECT_ERROR, and so on.) and other additional state information. Ah. So then what you're talking about isn't even SQL as such, but a Pro/C++ feature that manages all this for you. (I don't remember anything like this in PL/SQL, anyway, and I haven't used Pro/C++). For one thing, MySQL doesn't even have CURSOR for now (any cursors, anywhere), so you couldn't even fake this with MySQL. Porting to MySQL may be quite a challenge for you. The first thing would be to try to wean yourself from the precompiler by coding explicit operations in native C (ODBC) or Java (JDBC). Then, isolate your use of CURSORs, etc., and try to emulate them in MySQL by fetching all the results and managing the cursors yourself in a compatibility layer. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Regarding Connection Context
[EMAIL PROTECTED] wrote: CONTEXT USE Example: Do *you* really understand what this feature is supposed to do? Can you explain it to us in (low-level) detail? No, MySQL doesn't have a feature like this. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Solaris 10 performance improvements??
Jon Hancock wrote: However, I can't consider using it unless MySQL performs well. Any ideas or personal experience with MySQL 4.1.x on Solaris 10? Well, for one thing, your workload is unique, so the only way you can make your decision *is* for you to actually *consider* it. Install Solaris 10, and Linux (your preferred flavor) on two identical boxes (or on one box, with dual boot), and compare performance on an actual workload. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: keep field names unique across database?
[EMAIL PROTECTED] wrote: The second camp, as is described in the article, are more data-oriented. This practice was created in the original days of databases and programming design where ALL variables, including table names and field names, were global and needed to be absolutely unique. It's not just that. Part of this convention is that when you have foreign-key fields, you name them the same in both the defining table and the referring table. I.e. if you have a COMPANY table with CompanyID, CompanyName, etc., and an EMPLOYEE table, it can have a CompanyID that's now obviously (sic) related to COMPANY.CompanyID (you'd mark it as a FOREIGN KEY in EMPLOYEE). It also makes JOINs a little easier to write and read, because you'd be doing FROM COMPANY c, EMPLOYEE e WHERE ... AND c.CompanyID = e.CompanyID ... and so on - looking at the column names immediately gives you a clue about the key relationships.. It's a style thing that I've seen a lot of, and there's nothing wrong with this. As you say, it's data-oriented, but is really a naming-style issue. If you were following the object-oriented (quote-unquote!) style, the above query may be harder to read (assuming that you used names like just ID and Name in COMPANY): FROM COMPANY c, EMPLOYEE e WHERE ... AND c.ID = e.CompanyID -- 'uh, what's a c? Oh, yeah, a COMPANY.' ... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Brainstorming' time!
Scott Hamm wrote: Ok. I'm looking into alternatives. I'm trying to figure out an alternative to mysql exporting into xls file. Gosh, what's wrong with CSV files? Surely Access can export a classic CSV file format with fields separated by commas and enclosed by ? Then you can just use LOAD DATA INFILE file.csv INTO TABLE whatever FIELDS TERMINATED BY ',' ENCLOSED BY ''; Of course, you still have the problem of creating the appropriate equivalent tables in mysql, but I'm sure you can dump the schema in Access and massage the output to create the schema in MySQL. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: STD()
[EMAIL PROTECTED] wrote: Is this correct?...how MySQL calculate this?... Yes. Std. Dev = Math.sqrt(sum((val[i]-avg)**2) / count(i)); Verified with a trivial program. mysql select * from temp; +--+ | cal | +--+ | 00029.98 | | 00029.95 | | 00029.89 | | 00029.84 | | 00029.78 | | 00029.81 | | 00029.84 | | 00029.28 | +--+ 8 rows in set (0.00 sec) mysql mysql select std(cal) from temp; +--+ | std(cal) | +--+ | 0.205240 | +--+ 1 row in set (0.06 sec) mysql R.B.Roa Traffic Management Engineer PhilCom Corporation Tel.No. (088) 858-1028 Mobile No. (0919) 30856267 - 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 - 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: A sure way to start/connect mysql under Linux
Gelu wrote: 3.Go to in /etc/init.d and make 2(two) symbolic link from mysqld script to ... - K12mysqld - OS kill the daemon when shutdown. - S12mysqld - OS start the daemon on boot. ...in rc3.d directory (if your OS boot on text mode) or rc5.d directory (if your OS boot with graphical login). The only quibble I have with this is that the K and S numbers should be approximately (100-X) in relation to each other. Both K scripts and S scripts get run in directory sequence (i.e. from K00 to K99 when shutting down or switching to another runlevel, and S00 to S99 when starting up). So if your process should be one of the earliest started, it should be one of the last to shut down, and vice versa. For DB products, typical convention is to use S95 and K05, or something along those lines (i.e. start late, kill early). -- Shankar. - 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
NULL timestamps not possible?
I tried setting a TIMESTAMP column (nullable, not first timestamp in that table) in mysql 3.23.38) to NULL, but it seems to get the value 00. Is it not possible to set a timestamp column to NULL? -- Shankar. - 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: NULL timestamps not possible?
Shankar Unni wrote:
I tried setting a TIMESTAMP column (nullable, not first timestamp in
that table) in mysql 3.23.38) to NULL, but it seems to get the value
00. Is it not possible to set a timestamp column to NULL?
It's even worse: if you explicitly insert the value NULL (as in the keyword
null), it inserts now() instead.
Try:
create table foo (t timestamp, u timestamp);
insert into foo(t) values('');
// inserts in both t and u.
insert into foo(t,u) values (null,null);
// inserts the value of now() into both t and u.
Is this a bug, or a feature I missed in the documentation?
--
Shankar.
-
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: NULL timestamps not possible?
Paul DuBois wrote:
Feature you missed. Have a look here:
http://www.mysql.com/doc/D/A/DATETIME.html
Thanks. I wonder how I missed that.
Of course, the page lies somewhat: it says that if you omit the column in
an insert, it should get set to now(), but the following example shows it
doesn't - notice that u is omitted in the insert, but gets set to 0
instead of now().
create table foo (t timestamp, u timestamp);
insert into foo(t) values('');
// inserts in both t and u.
Not that I want to get into an aha! Gotcha! thing, of course.
P.S. The reason I went with TIMESTAMP instead of DATETIME is for storage
efficiency (I'm logging millions of events into a table), but at the same
time, I'm logging different kinds of events to the table, and some have
additional timestamps that are only applicable for those variants (which is
why I would like to set the column to NULL for the other cases).
Anyway, sigh!.
Thanks,
--
Shankar.
-
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: TIMESTAMP not acting as I'd like
Victoria Reznichenko wrote: recu small problem. I have a table set up like so. It has a number of entries recu that were added on a certain date, I use TIMESTAMP to keep track of the date. recu mysql UPDATE deerfield SET version = '2.1' WHERE product = 'WinGate LITE'; recu and all of the dates of the filed that match the where clause updated to recu today's date. How can I avoid this? Is there a problem with the set-up of recu the timestamp itself. It is a particular feature of timestamp type. You should use another type of data. You can read info about date, time and timestamp types at: http://www.mysql.com/doc/D/A/DATETIME.html MySQL only does this to the first TIMESTAMP column in a table, so the usual other solution is to have a last_modified column in the table right at the head (before any other timestamps). This gives you two benefits: an automatic timestamping of the modifications, and you can use the TIMESTAMP type safely for other timestamp values. - 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
Timestamp function formatting bug?
With MySQL 3.23.38: If you have a TIMESTAMP column with zero values, then SELECT ts from table; returns 00 But SELECT min(ts) from table; returns 0 This causes the JDBC driver to fall over when getting the timestamp value from this query. Isn't this a formatting bug? min() and max() should inherit the same type and formatting as their underlying data type, no? -- Shankar. - 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
Any way to hint a particular index for a query?
(Using mysql 3.23.38 - commercial version) I have a table that has two indexes: one on just creationTime (iv_alert_creation_ix), and one on (state, creationTime) (iv_alert_state_ix). When I issue this query: explain select uuid from iv_alert where state = 1 and creationTime = '2002020100' and creationTime = '2002030100' order by state, creationTime desc; mysql says (output munged to fit): table iv_alert type range possible_keys iv_alert_creation_ix, iv_alert_state_ix key iv_alert_creation_ix key_len 4 ref NULL rows 1 Extra where used; using filesort Why doesn't it use the iv_alert_state_ix key? Is there any way to make it do so? There can be lots of entries in that time range, but only a few with state. Of course, I'm using a synthetic database to test the query, and it currently doesn't have that many rows - is the explain output influenced by the actual data present, or is it just using a fixed heuristic? Thanks, -- Shankar. - 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: Any way to hint a particular index for a query?
Steven Roussey wrote: Any way to hint a particular index for a query? From the manual (http://www.mysql.com/doc/J/O/JOIN.html) Thanks. I missed that. Unfortunately, as I was afraid, this is a very MySQL-specific way of doing it. But then, hints always are DB-specific, so that's not too different.. (I have to target my product on multiple databases, so I have to keep my queries simple so as to make it portable). What helped, of course, was creating *realistic* a data set where the data matching state = 1 was only 10% of the total rows - it now uses the state index, as expected. So I think I'm ok, after all. Thanks for the pointers! -- Shankar. - 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: buggy round()
[obfilter: mysql database] Roger Baklund wrote: * Benjamin Pflugmann Think the other way around: If the other option is to have no floating point type at all, a not perfect one may be good enough for many cases. I agree, to some extent, but a growing number of users are 'normal people', not coders/programmers... sql has this nasty 'human language'-like syntax, which may seem as an invite for non-nerds... they wouldn't understand rtfm even if you spelled it out... ;o) Plus, you often *do* want to use fractions, or even rational numbers, without using floating-point. Yes, there's a distinction. Fractions (fixed-point) are a common numerical type used in financial and other computations. Oracle, and many other databases, provide a fixed-point numeric type (e.g. Oracle's NUMBER(digits,precision) type) that's carried around in a decimal or BCD-like representation, and has software libraries to do exact arithmetic (inasmuch as you can do exact arithmetic with fixed-point types). Rational numbers are a whole different story, of course (how do you represent 1/3 exactly?), so I'm not going to go down that path.. But MySQL could definitely benefit with implementing a fixed-point, BCD-encoded numeric data type with software arithmetic libraries. It would ease the pain for a lot of programmers, and those who value execution speed over numerical accuracy can use the binary (floating-point) types. (Suggested syntax: DECIMAL (digits, precision). The existing DECIMAL type can, I think, safely be hijacked for this.) -- Shankar. - 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: -p password
alias mysql='mysql -u root -pbig_secret' Another, more reliable way, is to use the long-form options and say mysql --user=root --password=big_secret -- Shankar. - 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: 3.23.49 fails to compile under RH Linux w/2.4.16 kernel
Robert La Ferla wrote: /usr/include/bits/mathinline.h: In function `float log1pf (float)': /usr/include/bits/mathinline.h:540: Internal error: Segmentation fault. Please submit a full bug report. See URL:http://bugzilla.redhat.com/bugzilla/ for instructions. So? Do what that says: go to http://bugzilla.redhat.com/bugzilla, and file a bug report with the details you posted here. Oh, you should also attach to the report exactly what kernel version you have (was it Red Hat's 2.4.16 RPM, or one you built on your own?), what gcc version, how they can get the test case (the mysql-3.23.49 source download pointer), and let them investigate.. -- Shankar. - 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: PostgreSQL
Sinisa Milivojevic wrote: Shankar Unni writes: And one big reason not: no native Windows port. Or Mac port (though that has probably changed with OS/X - anyone working on that?). When you are saying that there is no native Windows or OS X port, what were you referring to ?? Oh, PostgreSQL, of course. I've been using the MySQL Windows port for a year now.. And Alok Dhir jested: But you do trust a database running on Windows? [;-)] As much as I trust a database running on any native OS (Solaris, Linux, Windows..). It's the cross-OS emulation that makes me nervous.. Cygwin is a technological marvel, but have you seen all the things it has to do? It's a mini-OS in itself, with a lot less testing than Linux or Windows.. -- Shankar. - 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 - 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: blob field
[redirecting to mysql list because of general interest] Yoram Naim wrote: Can some one send me a code sample (C , CPP ) How can I copy file data to MYSQL BLOB field but not in one piece . Is this even possible? If so, I'm sure that the JDBC driver could use such a feature to stream large blobs to the database (currently, in 3.23, we're stuck with the 16MB single-packet limitation). -- Shankar. - 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 - 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: PostgreSQL
Sinisa Milivojevic wrote: Shankar Unni writes: And one big reason not: no native Windows port. Or Mac port (though that has probably changed with OS/X - anyone working on that?). When you are saying that there is no native Windows or OS X port, what were you referring to ?? Oh, PostgreSQL, of course. I've been using the MySQL Windows port for a year now.. And Alok Dhir jested: But you do trust a database running on Windows? [;-)] As much as I trust a database running on any native OS (Solaris, Linux, Windows..). It's the cross-OS emulation that makes me nervous.. Cygwin is a technological marvel, but have you seen all the things it has to do? It's a mini-OS in itself, with a lot less testing than Linux or Windows.. -- Shankar. - 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: blob field
[redirecting to mysql list because of general interest] Yoram Naim wrote: Can some one send me a code sample (C , CPP ) How can I copy file data to MYSQL BLOB field but not in one piece . Is this even possible? If so, I'm sure that the JDBC driver could use such a feature to stream large blobs to the database (currently, in 3.23, we're stuck with the 16MB single-packet limitation). -- Shankar. - 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: PostgreSQL
Alok K. Dhir wrote: That said, depending on your requirements, there are still compelling reasons to choose PostgreSQL - subselects, triggers, user defined types, etc. And one big reason not: no native Windows port. Or Mac port (though that has probably changed with OS/X - anyone working on that?). Cygwin is dandy, but I definitely don't trust a database that uses cygwin as an emulation layer underneath for basic file accesses.. -- Shankar. - 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: Suggestion re: floating point comparison (was Re: Problem with where clause)
[ database mysql query ] Sinisa Milivojevic wrote: On Fri, 01 Feb 2002 15:21:07 -0800 Steve Edberg [EMAIL PROTECTED] wrote: ...perhaps a NEAR function could be added; as a config file or compile-time option, you could define an accuracy range. Say, ./config --with-epsilon=0.0001 This is doable of course, but current code does not use any factor. The above variant would slow down many operations, while making a few faster. You can always add a UDF to do this - it's not often that anyone would want to do indexed lookups of FP quantities. If you do care about indexed lookups, you shouldn't be using an inexact type like float anyway. However, core gurus: this is a good segue to lobby for implementing a fixed-point numeric type, like Oracle and other databases do. Such fixed-point types are usually stored as BCD, and there are several good BCD manipulation libraries out there. Or the expression evaluation can convert BCD into unpacked decimal for a little more speed. Doing such a type would save *lots* of effort for people trying to do financial data manipulation in MySQL, and will save the almost monthly FAQ of how do I represent my monetary data :-). -- Shankar. - 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: Problem with where clause
[ database query mysql ] Jim Dickenson wrote: Am I to assume that based on your response that one should never use a float field type if you ever want to select the data? No, that wasn't quite what he said. You can certainly select on a float field, as long as you perform a meaningful operation. For instance, if you want to check for equality, then instead of val = 10.1, you have to check for abs(val-10.1) 0.1 (or whatever your tolerance for inequality is). Simple greater-than or less-than comparisons are always, of course, OK. Also, because of the equality problem, a unique index on a floating-point quantity may not be unique: 12.29 and 12.31 may both get entered in the database, and both display as 12.3. -- Shankar. - 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: Does MySQL work with VMWare?
Joel Wickard wrote: why not run cygwin? I run postgres on my win2k box through cygwin. It would be less resource intensive. Or even better, run the native Windows port. It's quite a good one. -- Shankar. [ database table query sql ] - 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: most appropriate data type for currency?
Michael Collins wrote: Using MySQL 3.23.47, is the best data type for small currency float(4) ? This is for prices of items in a shopping cart for example t-shirts. What is the difference in using float vs decimal? I know I don't need double. Float has accuracy problems at large ranges. A typical float has a fixed number of so-called significant digits. For 32-bit IEEE floats, that's about 6. Which means that the moment you cross $1 or so, you lose accuracy in the last place (the cents). The bigger the number, the more the inaccuracy. The *best* type is (depending on the largest quantity you want to deal with) either INTEGER or BIGINT. BIGINT is best. You want to represent either the cents value, or preferably, the centi-cents value if you want to keep your accuracy to 0.01 cents (i.e. you'd represent $130.31 as 1303100). This allows you to control the rounding exactly, and preserve all significant places in the data (important for currency!). It's a teeny bit more work, but is easily abstracted with a simple high-level data type. -- Shankar. - 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: Confused About JDBC Driver
[database,sql,query,table] Mark Matthews wrote: Or download version 2.0.11 released today which fixes that bug (as far as I can tell): Absolutely. Quick work, indeed. I've already switched over to 2.0.11 and it's been smooth. Thanks for the fantastic support! -- Shankar. - 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: most appropriate data type for currency?
[database,sql,query,table] Shankar Unni wrote: Float has accuracy problems at large ranges. A typical float has a fixed number of so-called significant digits. For 32-bit IEEE floats, that's about 6. Of course, in the interests of fairness, I should note that typical doubles (64-bit IEEE) have about 15 digits of accuracy, which are generally enough, as long as you never exceed 10**13 dollars (i.e. no US govt budget work :-)). You only have to worry about precise rounding with doubles. -- Shankar (PS Of course, given the typical accuracy with which budget numbers are tossed around, one would suspect that our fine reps use floats to manipulate them..) - 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: Confused About JDBC Driver
Paul DuBois wrote: I think that MM.MySQL used to be packaged as a tar file, but not is distributed as a JAR. Use the newer one, you'll be better off. And do as the filename indicates: un-jar it. You'll end up with a directory that contains the actual driver file plus a bunch of other stuff like the source code. Put the driver JAR file (mm.mysql-2.0.10) in your CLASSPATH. That's right: Do the following in a temp directory: jar xvf mm.mysql-2.0.10-you-must-unjar-me.jar This will create a directory called mm.mysql-2.0.10. Inside that, you'll find a mm.mysql-2.0.10.jar file, which is what you need to put in your classpath (you can move that file wherever you like it). However, I recommend using version 2.0.8 for now, if you use BLOBs a lot - 2.0.10 has a subtle BLOB reading bug that causes queries to throw IOExceptions with certain values. You can get all the old back-versions from http://sourceforge.net/project/showfiles.php?group_id=15923 -- Shankar. - 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: Off-topic: AOL Red Hat???
[ filter fodder: mysql database - is this case-sensitive?] Erv Young wrote: It seems to me that if AOL Time Warner had not only Netscape, but also the whole Linux-Apach-MySQL-PHP brigade in their hands, they would indeed have a powerful armamentarium for challenging Microsoft. I am not currently a Linux or MySQL user. Quite obviously. What do you mean by have Linux in the same sentence as have Netscape? Do you mean if AOLTW *owned* Linux, Apache, MySQL, etc.? Look - they already have Apache, Linux, Tomcat, MySQL, PHP, PostgreSQL, Emacs, the entire GNU project, and just about anything else you can imagine, and have had it for years. All the components for the foundations of a .NET-competitor, if they want to make one. As much as any of *us* have all of these. These components are free to use and redistribute, and AOL is free to add value to these and redistribute them according to the terms of their licenses. Like RedHat is currently doing. The only reason AOL *might* be interested in RedHat (after all, this is not official yet) is the *experience* that RedHat brings with them in putting together and supporting commercial Linux distributions. Not Linux itself. Maybe in regards to MySQL itself - AOL *may* perhaps someday be interested in a commercial DB offering, and might want to buy MySQL. But why? RedHat already has a commercial pgsql-based database product (Red Hat Database). -- Shankar. - 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: Porting from MS SQL to MySQL
Bob Hall wrote: We've requested a database from different companies, and specifically said we wanted MySQL or PostgreSQL because of the open source angle and we're a library. One company offered MS SQL as the platform and said that they can later on port it to MySQL. For this they wanted 18 000 euro. Now, what I want to knowis, how easy is it to port a (fairly complicated) database from MS SQL to MySQL? It can't be work worth 18 000 euro, now can it? Looking at it strictly from your point of view, I can think of only two reasons to accept the bid. There may or may not be good reasons for the amount bid (quite independent of whether it's worth it): If the current implementation uses a lot of MSSQL-specific features, it may be quite a chore to port it to MySQL. It would be even more of a chore if you would like the product to keep using whatever advanced features it uses in MSSQL, but also keep working with MySQL (i.e. no code fork). -- Shankar. - 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: Problem Upgrading
Islam, Sharif wrote: I had 3.23.41 installed. It came with Rh7.2. I had some mistakes in initial start up . So I thought i would reinstall it. I downloaded the rpm for 3.23.47. And ran the rpm installation. You got the 3.23.47 RPM from MySQL's site, I presume? That RPM is not an upgrade for a RedHat RPM (RedHat packages MySQL differently from the MySQL AB folks - something to do with the Linux file system layout standard). The names are also different - mysql for Redhat vs MySQL for the MySQL image. You'll have to: * rpm -e the Red Hat package (mysql). * Do another rpm -Uvh of the MySQL package to be sure that no common component got blasted. * Set up the /etc/init.d stuff if needed to start mysql at system boot. -- Shankar. - 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
Dump blobs using the mysql command line
Is there any way to hex-dump a BLOB column using a Select statement on the mysql command line? The documentation for the hex() function says that hex(abc) should print 616263 But in fact it prints 0. (on 3.23.36 and 3.23.43, the two versions I have installed - the former on RedHat 7.1, and the latter on Windows2K). Even if it worked, would it work on an entire BLOB column? Even better, is there any way to export just a single BLOB column of a single row to a binary file, unadorned by CSV or HTML guff? P.S. I also tried the following commands: (echo connect mydb; ; \ echo select blobcol from table where id=1 ) |\ mysql -s OUT This works, but prints nulls as the string \ followed by 0. If I also specify -r for raw, it truncates at the first null instead of printing everything.) Thanks, -- Shankar. - 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: Stored Procedure
[ obfilter: mysql database ] Sinisa Milivojevic wrote: Cafetechno writes: When The Stored Procedure Capability will be included in mySQL Take a look at myperl on http://freshmeat.net Interesting start, of course. What would be nice is to support a proper create or replace procedure/function type syntax with a body in Perl or Java. Java functions would either need to use JDBC to perform queries and access results (painful), or need a preprocessor like SQLJ to convert high-level SQL syntax to JDBC statements. Perl would need a module like Perl::DBI. But this is definitely a major undertaking, worthy of a project of its own.. -- Shankar. - 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: select 10 of each
Roger Baklund wrote: hi there.. no solution from me, but I'm also interested in this type of query (although I only want 1 for each lang) If you only want one row for each lang you can do it using GROUP BY... SELECT * FROM table GROUP BY lang Whoa. Watch out. You can omit columns from a GROUP BY (even for MySQL's extension) only if those are otherwise unique for the columns that are grouped. In this example, this query can return pretty unpredictable stuff (as I found out myself yesterday when I accidentally left out a non-unique column in a query..) There is no reliable way to do what Christian wants except to do one query per value that he wants 10 rows for, with each query set to LIMIT 10. -- Shankar. - 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: Detecting OS through MySQL
Michael Widenius wrote: Hi! Brent Is there any way to detect the Operating System that a MySQL server is Brent running on using SQL or DBI function calls only? Sorry no. But this is would be a good thing to have and we shall add this on our TODO. Especially since MySQL has different table-name-case-changing behavior on Windows and Unixen - so the result of a create table MixedCaseName will be different on different platforms.. (It's useful if you have code that tries to grok the existing schema to see if it is present and OK, or needs an install or upgrade). -- Shankar. - 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: SOFTWARE TESTERS DEVELOPERS CONTRACT TO HIRE (fwd)
rc wrote: anyone know why i'm getting these spam emails over this list Because it had the magic word sql, which the filter checks.. -- Shankar. - 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: Help Password Problems!!
Gary Wheeler wrote: I've done a fool thing and changed the root password to mysql server, and evidently miss typed, because now it will not let me in. How can I get back into mysql to change this?? Please help as this is a production server!! See the standard documentation page at http://www.mysql.com/doc/R/e/Resetting_permissions.html -- Shankar. - 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: PCAnywhere like linux client - mysql data files administration
X-obligatory-filter-fodder: database,sql,query,table Joe Ellis wrote: i believe your talking about VNC. http://www.uk.research.att.com/vnc/ Jack A. Fobel wrote: I remember seeing a program awhile back that acts like pcanywhere or terminal services, to connect to a linux or windows box. Anyone remember the name? To connect to Windows machines, VNC works just like pcAnywhere (though it puts a lot more strain on the server you're connecting to if you have a lot of DOS/NT command windows open on its display - pcAnywhere does a better job with command windows). You get to see the desktop on the remote machine, and manipulate applications on it. However, when connecting to Linux and other Unix boxes, you don't get to actually see the contents of DISPLAY :0. Instead, you get a whole fresh new display (:1), on which you can start your own clients. So basically, you can interact with the system, but not with the applications running on the existing default desktop on the system. It's quicker and less CPU-intensive for the remote system for you to just telnet in to the system, instead of VNC'ing in. The only time VNC helps is if you need to run a GUI application on the target system, and you don't have an X server handy on your desktop, but do have a browser (i.e. you are on a Windows or Mac box without an X server). (PS There are free Windows-based X servers available at Tucows - http://www.tucows.com). -- Shankar. - 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: Bug in Foreign key constraint
Heikki Tuuri wrote: Foreign keys do not work if you do an ALTER TABLE. I have to add a note to the manual that they do not work even if you do an ALTER TABLE to the referenced table. Are you saying that you can't add columns to a table (to grow the schema incrementally between software versions) without losing all your foreign-key constraints? Or having to dump and restore databases each time? Would it work to drop and recreate all the foreign-key constraints after each alter table? -- Shankar. - 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 round problem
Attila Soki wrote: select round(9.065,2) ++ | round(9.065,2) | ++ | 9,06 | ++ why not 9,07 ?? Most C compilers today defer things like round() to the floating-point unit on the CPU. Most CPUs these days implement IEEE754 as their FP standard, and IEEE 754 defines several rounding modes that the processor can be set to to define the behavior of the round operation. The default (usually) is round to nearest even (which is what the others have pointed out). You can also set it to round towards zero, round away from zero, or round towards minus infinity. The application is supposed to know what behavior it prefers, and set the default rounding behavior on the FPU by using a specific control instruction. The operating systems are supposed to cooperate by saving and restoring the process' FP rounding preference when restarting the process after an interrupt. Needless to say, there's a fair amount of confusion on this front, and certainly no portable solution, though I believe the latest C standard (C99) has some APIs to control this. The only other choice is to never depend on the FPU rounding, but always call a software FP round routine to perform your rounding, if it's that important. MySQL hasn't done this (and neither have any of the other DB packages I've used recently). -- Shankar. - 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: need help with mysql rpms
Raymond Norton wrote: error: failed dependencies: MySQL conflicts with mysql-3.23.36-1 MySQL-server conflicts with mysql-server-3.23.36-1 Yeah, the MySQL rpms are incompatible with the mysql rpms (which are done by RedHat). Apparently the two camps each think the other's file system layout and package organization is weird :-). My last question on the topic never brought out a response from anyone concerned.. Unfortunately, the solution isn't as simple as just delete mysql* and install MySQL*, because RedHat has other packages that depend on the mysql RPMs, so you have to nuke those, too, and there aren't any replacement packages for those at the MySQL site. The best solutions for you, if you don't feel like dealing with the problems, are: 1. Go ahead and delete the rest of the dependent packages of mysql (like php-mysql-4.0.4pl1-9), and rebuild those from source if you need them, OR 1a. Remove just the mysql-* packages, but use the --nodeps option to disable the dependency checks for the other packages that depend on mysql. OR 2. Upgrade using the latest RedHat-build RPMs (the latest one I could find is the one with the RH 7.2 distribution, which is 3.23.41. Depending on how recent your libc, etc., are, this may well work with your distribution.. -- Shankar. - 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: Random
Rob@TH wrote: Hmm still nothing :/ Any other possibilities? [ Selecting a random entry from a database ] Generally this is a hard problem. Ordering by rand() is really wasteful because the DB has to select *all* entries, order them, and then pick one. There are more efficient solutions available if you know the domain of the problem well. E.g. if you use an auto-increment column as a primary key, you can do something like (two steps here): select max(pk), min(pk) from table; in your app, select a random value between the two select * from table where pk = randval - fudge and pk = randval + fudge limit 1; (This fudge is to handle the case of holes in the primary key sequencing). It works relatively satisfactorily as a randomizer, unless you are interested in mathematically precise random distributions (:-/). -- Shankar. - 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] can you have a no-default column; error if unspecified?
Bennett Haselton wrote: NOT NULL doesn't mean that the column doesn't have a default, it just means that the default is not null. In other, more conventional (:-)), databases, NOT NULL means simply NOT NULL, and implies nothing about defaults - that's a MySQL-ism. (Thus, a NOT NULL column with no default should give an error if you insert without specifying a value for that column, since the default DEFAULT should be NULL). It's possible to compile MySQL with a special *compile-time* flag (see the flag -DDONT_USE_DEFAULT_FIELDS in http://www.mysql.com/doc/B/u/Bugs.html and http://www.mysql.com/doc/c/o/configure_options.html). And I'm glad to see that this behavior is listed as a bug rather than as a feature :-), which gives me hope that someday it will be tackled (to at least make it a run-time configurable option rather than a compile-time forced option). -- Shankar. - 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
lower_case_table_names not working?
OK, so I'm on a slightly older version for this experiment (3.23.36).. I tried restarting mysqld with the line set=lower_case_table_names=1 in my my.cnf (on RedHat 7.1), and have a table called ipaddr in my database (the files are ipaddr.frm, ipaddr.MYD and ipaddr.MYI). (I have verified with show variables that this setting has been accepted). However, the following query still fails: select * from IpAddr; (says ERROR 1146: Table 'test.IpAddr' doesn't exist). Am I misunderstanding what this option does? I'm trying to port some code from Oracle that's very careless about the case of table names, and I need it to work in MySQL on Linux.. Thanks in advance, -- Shankar. - 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: source command problem
sherzodR wrote: Well, Paul, i think he means using source in an .sql script. Yes you can, Shankar. I used it several times for several reasons :) And u can envoke your sql script the same as you use your other scripts. [EXAMPLE] [...] -- in source.sql file: source query.sql # or \. query.sql [/EXAMPLE] Thanks - that last one (using \.) worked. Using source doesn't. In the above example, if you had the line source query.sql in source.sql, then you can't do % mysql mysql source source.sql ERROR 1064: You have an error in your SQL syntax near 'source query.sql' on line 1. But using \. query.sql works. So that lets me get on with the scripts.. Thanks again. -- Shankar. - 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
RPM packaging
Who has set up the RPM packaging for the current MySQL-generated RPMs? It's very different from the RedHat-generated packaging, and makes it impossible to just upgrade the package in place. Of course, one could just delete the RedHat package and then install the MySQL-generated package, but that has several problems: * The MySQL-generated package doesn't have the init.d and logrotate.d scripts that tie in neatly with the RedHat init and logrotate packages. * Files install in places that contravene the File System Standard (e.g. documents in /usr/doc instead of /usr/share/doc) Also (probably providentially), since the names are case-sensitive, MySQL is not recognized as being the same as mysql. Would it be possible to repackage MySQL based on the Red Hat packaging format? I.e. start with a recent (7.2) distribution (they have 3.23.41, I believe), and use that as the RPM file for the new distributions? Using the same name? (That would mean calling the RPM mysql instead of MySQL). I'm sure they would also appreciate sharing the effort in maintaining this file.. -- Shankar. - 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
source command problem
database,sql,query,table I notice that source is only accepted at the *command line*. This means that I can't source a script that sources another file. I guess source is implemented in some special way that prevents it from being recursively used? Is there a way to do what I want (have scripts call other scripts)? Thx, -- Shankar. - 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: About huge numbers
The answer, I guess, is don't use DECIMAL. I understand now the need for a fraction (someone pointed out that Euro calculations must be done in upto 4 decimal places with rounding). All I can say is that: (a) you multiply the amounts by 1 (so as to deal with integers), and (b) Split each amount into 3 32-bit numbers, or 2 64-bit numbers, and store each of these in 3 or 2 columns. (c) Use some high-level data type that abstracts large exact numbers (if you're using Java, java.math.BigInteger or java.math.BigDecimal qualifies, but it's a bit of a pain to construct a BigInteger from an int[] array). Alternatively, if you're using Java throughout, you can start with BigDecimals, and do the following: * To write into the database: - extract the scale, and the unscaled BigInteger, from the BigDecimal. - Convert the BigInteger to a byte array. - store the bigdecimal as a byte array (TINYBLOB, or a hex string in a CHAR(N) column) by calling BigInteger.toByteArray(); * To read it back, read in the byte array and scale, and do new BigDecimal(new BigInteger(bytearray), scale) -Original Message- From: Gyulay Gabor [mailto:[EMAIL PROTECTED]] Sent: Thursday, November 15, 2001 11:16 PM To: Shankar Unni Cc: [EMAIL PROTECTED] Subject: Re: About huge numbers On Thu, 15 Nov 2001, Shankar Unni wrote: Thank you for your answers. Generally, you don't want to store currencies in floating point, anyway, and it's unfortunate that MySQL implements DECIMAL as floating point rather than a variable-length BCD (which is exact). Yes. This is strange, because we would like to convert our applications from foxpro. It seems, we have to store these numbers as floating point, because we have to handle all currencies the same way. However, I tried a sanity check on that number: taking a value of the order of 100 trillion (say, US dollars - this would be of the order of 50x the amount of the current US annual budget), converting it to, say, Turkish Lire (15x), I get a number that barely exceeds the signed 64-bit limit, and definitely fits in an unsigned 64-bit number. For Italian lire, the number is a hundred times smaller, and should be well within reach of 64-bit computation. Are you absolutely *sure* you can't stick to a 64-bit number? Are you really dealing with hundreds of quadrillions of US dollars? We would like to prepare the system to handle extreme values without errors. To store the annual money circulation of an huge Hungarian company, we must use 12+2 digits at least 123.456.789.012,12 HUF and the system should convert it to ITL, USD etc. This way it exceeds the system's limit, and we cannot use integers, because we need to store fractions too. If we would multiply by 1, we would get this: (Right now there is no real demand to count with these numbers, but what if it occurs in the future...) mysql create table try (money decimal(20,2)); mysql insert into try values (1234567890123456.12); mysql select * from try; +-+ | money | +-+ | 1234567890123456.00 | +-+ 1 row in set (0.00 sec) Of course this is the biggest number we plan to handle, and this is the worst case we would like to convert, and this could be only a subconscious fear. Thank you again, Gyulay Gabor ps. I tried this under foxpro, and got the same result. So the problem existed in our previous system, and we haven't even know about it. - 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: how to handle circular references if DB enforces ref. integrity
Kristian Köhntopp wrote: Bennett Haselton wrote: The problem is, how would you add a new bus and a new driver to the database? Whichever one is added first, you're going to get an error because its counterpart doesn't exist yet, violating referential integrity. I was under the impression that databases enforced referential integrity only on commit to allow precisely for such scenarios as you describe, but I may be wrong. It depends on the database. Anyway, there are several ways to do this. If both the referring columns *MUST* be non-NULL as well (in addition to having a foreign key), that makes it a bit tough, and you'll have to depend on the fact (or setting) that the database only enforces referential integrity at the commit. Alternatively, you can make one of the fields NULLable (i.e. don't put a NOT NULL constraint on it - say, BUS.DRIVER_ID. Heck, even DRIVER.BUS_ID can be NULLable - there's nothing to say that a driver is always behind the wheel :-) ). Then, it's perfectly safe to insert a BUS (with DRIVER_ID=null), insert a DRIVER with BUS_ID=null, and later, set either column to point to the other table. Back to the first situation: another elegant solution is to move the relationship outside both tables. E.g. you have a BUS table, and DRIVER table, and a BUS_DRIVER relationship table. If you want to associate a driver with a bus, you insert a row in BUS, one in DRIVER, and a row in BUS_DRIVER with BUS_ID and DRIVER_ID set appropriately to the rows just inserted. This does not violate any constraints, and is portable across DBs. The only drawback is that there's no way to enforce that for every BUS, there's a row in the BUS_DRIVER table. You can use JOINs to determine the relationships. -- Shankar. - 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: About huge numbers
Gyulay Gabor wrote: The problem is that I need to store numbers with lot more than 16 decimal digits - e.g. 1234567890123456789012345.12 [...] The reason is why we need this that there're several currencies (like italian lire) which requires this kind of precision. Excellent answer from Carl. Generally, you don't want to store currencies in floating point, anyway, and it's unfortunate that MySQL implements DECIMAL as floating point rather than a variable-length BCD (which is exact). However, I tried a sanity check on that number: taking a value of the order of 100 trillion (say, US dollars - this would be of the order of 50x the amount of the current US annual budget), converting it to, say, Turkish Lire (15x), I get a number that barely exceeds the signed 64-bit limit, and definitely fits in an unsigned 64-bit number. For Italian lire, the number is a hundred times smaller, and should be well within reach of 64-bit computation. Are you absolutely *sure* you can't stick to a 64-bit number? Are you really dealing with hundreds of quadrillions of US dollars? -- Shankar. - 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: order by, group by
Christan Andersson wrote: lets say that I have this table.. articles(id,language,name,description) what I would like to do is retrieve 1 row per unique id in the chosen language select * from articles where language='en'; that is quite simple, unfourtunally, not all articles have the description or name written in english and the above query would not return these articles... PROBLEM is what I get. however doing a select * from articles group by (id); will return all articles even if they do not have an english translation, BUT here is the problem.. which language will be the one I recieve? Svedish? english? French? There seems to be a little confusion here regarding grouping and ordering. What do you mean by the one I receive? Do you only expect one? Or do you expect articles sorted by language in some order? It looks like what you are *REALLY* trying to do is to sort by ID first, collecting all the articles of one ID together, and within those groups, sort by a language. No? You really want: select * from articles ORDER BY id, language ... GROUPing is the act of collecting rows together using collection functions like COUNT(), MAX(), etc., based on a grouping criterion. E.g. if you wanted a result of count of articles for each article ID, you'd do something like select count(*) from articles GROUP BY id; By definition, such a query returns *one* row for each *distinct* value represented by the set of group-by keys. Any columns that you have in your select statement that are not collection expressions must be in the GROUP BY clause. If this is not obvious to you, read an introductory database book for a good explanation of these basic concepts.. So why don't we do an ORDER before a GROUP? The problem (or fact) here is that grouping is inherently an order-destroying operation. When you group columns, you are inherently sorting and coalescing the data, but using buckets determined by the group-by keys. The result is one row per bucket that has data. It's meaningless to sort before the group-by, because the group-by *is* a sort of another kind. *After* you finish grouping the results, you can re-sort the groups based on whatever criteria you pick. -- Shankar. - 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: order by, group by
Christan Andersson wrote: what I really want is the following.. let say that the table (id,language,name,description) where id,language is the primary key so that 1 id can have several languages the data in the database looks like this 1'en''blue circle''this is a blue ' 1'no''bla cirkel' 'dette er ei bla cirkel' 2'en''green leaf' 'this is a green leaf from a tree' 2'sv''grönt löv' 'detta är ett grönt löv från ett träd' if the language priority is en,sv,no the selectwould return the following 1'en''blue circle''this is a blue ' 2'en''green leaf' 'this is a green leaf from a tree' if however the language prority was sv,no,en the select should return the following 1'no''bla cirkel' 'dette er ei bla cirkel' 2'sv''grönt löv' 'detta är ett grönt löv från ett träd' Ah. That's quite different. You can't do this using GROUP BY either, by the way. You'd have to compute some value column based on the language and the preference list such that the first choice language evaluates to 1, the second choice language evaluates to 2, etc. (See the CASE function under Control Flow functions). You'd then need to do one select with group-by to get the MIN of this value for each article number, which will give you the article ID and the mapped value that's the least, for each article, which you can shove into a temporary table. You then have to join that against the whole table to get the rest of the details. Or something along those lines. Or else, just order by such a map function, and use software logic to only select the first returned value for each article number, which may well be faster.. -- Shankar. - 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: Beginner SQL Question
Steve Meyers wrote: There are no subselects in MySQL (yet). However, they're usually a bad idea anyway, which is why it's never been such a big rush to get them in. Sometimes (just sometimes), there is no way to do it except with a subselect. For instance, we have a table where we get record updates of a 'file' (filename, last-update time, handle to where this version of the file contents are kept). The way to get the most recent version of each file is select filename, received_time, handle from file_updates where (filename, received_time) in (select unique filename, max(received_time) from file_updates group by orig_file_path); This is elegant and reasonably efficient (as long as there's an index on (filename, received_time)). There is no equivalent join or outer join statement that can express this. The temporary-table solution is somewhat MySQL-specific. The only *portable* way to do this across DBs is to also maintain some other boolean flag (latest), keep that correctly updated as new updates come in, and search on that (which is what we'll be doing in the interests of efficiency). -- Shankar. - 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: Beginner SQL Question
Nguyen Trong Phuc wrote: we can use subselect now with MySQL-Max version. I think not. I know it failed with 3.23.43, when I tried it just now (mysqld-max). Regarding encrypt(), the manual does say that encrypt() returns NULL on OSes where the crypt() function is not available. True. However, there's a nice GPLed version of crypt() that comes as part of GLIBC which can surely be incorporated into the now-GPL'ed MySQL distribution (thus making it OS-independent).. -- Shankar. - 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: New fork of MySQL
Steve Meyers wrote: The MySQL source is under the GPL. Any fork must also be under the GPL. You may sell your forked MySQL, but you must also provide the source code. Is it really, now? What are the rules about bundling now? If we distribute a (standalone) copy of MySQL with our product, does that expose our product to the GPL? Or is it just like distributing a copy of Emacs with your OS? (I.e. as long as you make the source available, it doesn't automatically GPL the rest of your OS?) What if the product is designed to work with many databases, but we want to distribute MySQL only as a default database (i.e. it doesn't depend on MySQL for its functionality - it's merely a convenience)? Does that change the GPL liability on our product? Who can answer these questions? -- Shankar. - 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: OT: Default forced on MySQL;
DownloadFAST.com wrote: [...] why don't you try PostgreSQL instead? My understanding is it is much slower. No kidding? Why don't you fork the PostGreSQL codebase and apply your special assembly talents to speed it up by 50% or even 60%? - 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
BUG: Need option to disable tty check in mysql client
Please treat this as a fairly urgent problem. It's simple to fix, but the effects of the bug are a severe loss of functionality under Win32. Several options in the mysql client (--batch, --silent, etc.) have no corresponding options to reverse that behavior (e.g. --no-batch, --no-silent, etc.), which is generally accepted option style. Compounded with this is the fact that the mysql client does a check on isatty(0) and isatty(1), and if it fails this check, it automatically forces the client into batch, silent, quit-on-error mode. Two things need to be done: (a) There needs to be an option to disable the TTY check in the client. This is most important when running mysql under Emacs (using sql-mysql) on non-Unix platforms (which don't have pseudo-ttys). How about --assume-tty? (b) Also, the check of isatty(0), etc., should be done *AFTER* the option checks, not before, and should be disabled if the option above is set. Finally, as a matter of style, (c) Options like --batch, --silent, etc., that toggle boolean states, all need to have opposite options of the form --no-batch, --no-silent, etc. It's enough to implement these as long-form-only. These changes are actually fairly trivial to implement, and I would have been glad to submit a patch for this, except that the Win32 port of MySQL needs Visual C++ to compile, which I don't have access to. Thanks much in advance, -- Shankar Unni. - 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
BUG: Need option in client to disable TTY check (and other anti-options)
Please treat this as a fairly urgent problem. It's simple to fix, but the effects of the bug are a severe loss of functionality under Win32. Several options in the mysql client (--batch, --silent, etc.) have no corresponding options to reverse that behavior (e.g. --no-batch, --no-silent, etc.), which is generally accepted option style. Compounded with this is the fact that the mysql client does a check on isatty(0) and isatty(1), and if it fails this check, it automatically forces the client into batch, silent, quit-on-error mode. Two things need to be done: (a) There needs to be an option to disable the TTY check in the client. This is most important when running mysql under Emacs (using sql-mysql) on non-Unix platforms (which don't have pseudo-ttys). How about --assume-tty? (b) Also, the check of isatty(0), etc., should be done *AFTER* the option checks, not before, and should be disabled if the option above is set. Finally, as a matter of style, (c) Options like --batch, --silent, etc., that toggle boolean states, all need to have opposite options of the form --no-batch, --no-silent, etc. It's enough to implement these as long-form-only. These changes are actually fairly trivial to implement, and I would have been glad to submit a patch for this, except that the Win32 port of MySQL needs Visual C++ to compile, which I don't have access to. Thanks much in advance, -- Shankar Unni. - 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: How to turn beeping off
Paul DuBois wrote: At 4:34 PM -0500 10/30/01, Benjamin J Pracht wrote: Does anyone know how to turn that annoying beeping off in the text mode utilities such as mysql.exe? I don't believe mysql.exe has ever beeped at me. What are you doing when it beeps at you? Whenever there's an error. Try mysql bogus.sql where bogus.sql has a syntax error. On Windows, this is excruciating, as it emits a 1-second beep for each error. (a PC-speaker console beep). -- Shankar. - 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: Generate random, unique value...
Dana Holt writes: Can I automatically generate a random, unique, integer value in a certain range when inserting data into a column using SQL? Random? Like how random? * Math.random() random? And still unique? That's really tough.. * Or just you don't care, but not sequential random? * Or just unique, and you don't care if it's sequential or not, but you just don't want to be bothered to start from where you left off, or worry about other client hosts random? Generally, you'd want to use UUIDs for the simplest case of this, but the standard UUID algorithms (DCE, etc.) generate 128-bit UUIDs (and so are best used as CHAR(36) (with the standard DCE representation) or CHAR(32) if you simply encode the whole thing as a single hex string. Or use two 64-bit INTs as the primary key, and split the 128-bit key among them. You can fake 64-bit UUIDs for limited scenarios (like within your own intranet, etc.) by cooking up an ID based on, e.g., 16 bits of IP address, 32 bits of a start time (of the database), and 16 bits of counter (where you carry over any counter overflows into the start timestamp part). Vary the parameters, of course, based on your intranet situation.. Or some such method.. - 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: Length limit of 500 on primary keys?
Steve Meyers wrote: In a previous message, I failed to mention one of the main reasons you would NOT want to use a 500-character primary key. MySQL uses a key buffer to keep as much index information in memory as possible. The longer the key, the less info it can keep in memory, and the more often it will have to swap to disk. If your key doesn't fit in the key buffer, my tests have shown that there is a HUGE performance loss. That's a very good point - thanks for bringing it up. I'll bring it to the attention of the table's owner. Thanks for the ammo.. (Fortunately, this table isn't very big..) -- Shankar. - 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: Length limit of 500 on primary keys?
Steve Meyers wrote: The problem is that he has it as a primary key, so he wants it to be unique as well as indexed. The best solution (and MUCH MUCH MUCH more efficient) would be to hash each of the four columns, and create a primary key on that. Integer keys are much faster and memory- efficient than string keys. Granted, but there's still the problem that the hash may not be unique, thus defeating the purpose of the primary key. I really need a longer primary key. Why is there a limit in the first place, and if there *is* a limit, why is it not configurable at runtime or database creation time? -- Shankar. - 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
[Fwd: Problems running mysql client under Emacs on Win32]
I'm resending this to the general list, since I received absolutely no reply at all on the Win32 list, and I'm hoping someone here is familiar enough with the Win32 port to comment.. Original Message Subject: Problems running mysql client under Emacs on Win32 Date: Sat, 20 Oct 2001 14:51:20 -0700 From: Shankar Unni [EMAIL PROTECTED] Organization: IntruVert Networks, Inc. To: [EMAIL PROTECTED] The mysql client program has this feature that if it's run with its standard input not bound to a TTY, it automatically switches to batch mode. Normally, when Emacs runs a process as a child, it'll set up a pseudo-tty to the child, which thinks it's talking to a regular TTY, and all is OK. Unfortunately, on Windows, this doesn't work, because there is no native pseudo-tty mechanism, so mysql runs in batch mode (no prompt, exit on first error, ...) Also unfortunately, there is no switch available to disable this automatic switch to batch mode. Does anyone have a workaround, or a patch, for this? -- Shankar UnniIntruVert Networks, Inc. [EMAIL PROTECTED] (408) 434-8311 - Please check http://www.mysql.com/Manual_chapter/manual_toc.html; before posting. To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, send a message to the address shown in the List-Unsubscribe header of this message. If you cannot see it, e-mail [EMAIL PROTECTED] instead. - 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
Length limit of 500 on primary keys?
Why is there a limit of 500 bytes on the primary key? (MySQL-Max-3.23.43 on WinNT). Is there a way to raise this limit? We have some tables with columns that are VARCHAR(200), and need to make a primary key based on combinations of these columns (in some cases, upto 4 columns), and MySQL complains for all of these table definitions that Specified key was too long. Max key length is 500. -- Shankar Unni. - 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: Lat/Long storage?
NUMBER(8,5). You'll need around 5 digits after the decimal point. (Think GPS accuracy == 10 meters for high-grade, 100 m for dumbed-down civilian use. At the equator, that's 0.9 or 0.0009 degrees respectively. At higher latitudes, that'd be correspondingly less.) If you value accuracy, DON'T USE FLOAT. Float has only 6-7 digits of accuracy, which, if you take out the 3 digits before the ., leave you with only 3 decimal places after (== roughly 1 km - too rough). Use Double (which would be 8 characters), or use number (8,5) or number (7,4), which are approximately the same size. The latter are easier to work with for display purposes, the former for computation purposes. Take your pick. If you use NUMBER(#,#), you should make sure that internally, you convert them directly to and from double to preserve the accuracy of the numbers. -Original Message- From: Nelson Goforth [mailto:[EMAIL PROTECTED]] Sent: Monday, June 25, 2001 2:03 PM To: [EMAIL PROTECTED] Subject: Lat/Long storage? I need to store latitude and longitude in a MySQL table. It needs to allow human entry. Do you have a preferred storage technique for these numbers? Nelson Goforth --
RE: Nulls appear not be respected
Mark Stosberg [EMAIL PROTECTED] writes: Tonu, thanks for the explanation. I still prefer the way Postgres handles it: Mark= select * from t where c2 = null; c1 | c2 ---+ 1 | (1 row) Postgres is incorrect in doing this. At least, it's way non-standard. NULL is *NOT* a value. It's an absence of a value, and doing *any* comparisons with NULL is invalid (the result must always be NULL, even if you say foo = NULL). The only operator you can use is IS NULL (or the opposite IS NOT NULL), which simply says whether the column has a value or not. -- Shankar. P.S. This is certainly the interpretation *I* have seen all DBs put on NULL comparisons. Oracle, MySQL and MSSQL in particular.
RE: UNIX_TIMESTAMP converts to CURRENT_DATE on a second call
Sinisa writes: - timestamp is saved in datetime format - first timestamp column is silently updated on each UPDATE What this means, of course, is that when you want to deal with your own TIMESTAMP data, you need to maintain some sort of sacrificial column (e.g. LAST_UPDATED TIMESTAMP) that can be subjected to this automatic update (and must be the first TIMESTAMP column in your table!), so that MySQL leaves your other real TIMESTAMP columns alone. Though really, this behavior should be an *option* (preferably a *per-table* option) in MySQL, not an automatic feature (i.e. assuming that any TIMESTAMP column must be a last-updated type column data). TIMESTAMP is an ANSI type with its own defined semantics, and it's perfectly valid to expect that the user may want to maintain their own timestamp data without MySQL making any assumptions about it.. -- Shankar.
RE: round and computer science
Dennis Nichols [EMAIL PROTECTED] writes: At 4/13/01 03:42 AM, Peter Holm wrote: mysql select floor(23.49 + 0.5); = 23 mysql select floor(23.499 + 0.5); = 24 Why are there different results? Apparently the closest (most accurate) expression of the first constant (23.49) as a floating point number using binary arithmetic is less than 23.5 while the closest expression of the second constant (23.499) is 23.5 or greater. This has to do with IEEE floating-point format. Both these are maintained internally as "double"s (as C programs usually do). An IEEE double is 64 bits long, with a 48-bit mantissa (the fractional part). 2^(-48) == 3*10^-15 (== basically, around 16 decimal digits of precision, with the 16th digit being approximate). Now, if you look at 23.49 (the first number), it has 16 digits (don't forget the part before the period - it's *significant* digits we're talking about here, since the format normalizes numbers so that the binary part before the period is always "1"). The second number has 17 digits, so the string-to-number converter tries to get the 16-digit number that closest approximates the 17-digit number (in this case, rounding it up to 23.5). (OK, the *real* nitpickers, back off :-). I'm just doing approximate math here, dredging up ancient memories of life in compiler and CPU-land..) -- Shankar Unni. [EMAIL PROTECTED]
RE: Transactions in MySql
Uh, before you take his head off, consider that most people using MySQL are not in a position to build MySQL themselves (or trust whatever comes out of "./configure --whatever_options; make"). And the default binary distribution does not include BDB or InnoBase for any platform (that's coming soon, but is still in process..). So telling them to RTFM doesn't help, because even if they do, it won't give them transactions. MySQL seems to ignore TABLE TYPE specifications if it isn't compiled for that table type (i.e. if you take the default distribution and create a table with TYPE=BDB, it just quietly ignores that spec and creates a MyISAM table, leading to much confusion for the uninitiated - shouldn't it at least give a warning about "unknown table type"??). So until we have such binary distributions that support BDB and InnoBase, it's probably more appropriate to point out to them that they have to build their own distribution with BDB and/or InnoBase, and *then* tell them to continue on and RTFM.. -Original Message- From: Gerald Clark [mailto:[EMAIL PROTECTED]] Sent: Monday, April 09, 2001 6:19 AM To: Marco Baldacchini Cc: [EMAIL PROTECTED] Subject: Re: Transactions in MySql Marco Baldacchini wrote: MySql support transactions? The OLE-DB provider support transactions? Call the method Begintrans on a connection object (ADO) return an error!!! And you read the manual, right? Transaction support depends on the version of MySQL you are running, and the table type created. Read the manual, then ask questions.
Binary distribution with BDB and InnoBase?
Sorry to keep harping on this, but back when 3.23.34 was released, there was a line in the release notes that claimed that binary distributions with BDB and InnoBase would be tackled "next week". Has there been any progress on this front? I'm most interested in the Win32 binary distribution, since that seems to be built in a non-standard way (i.e. instead of using configure, etc., it uses Visual C++ project files, etc.) -- Shankar Unni[EMAIL PROTECTED] (408) 434-8311
RE: Best way to generate laser-printed reports from mysql
[EMAIL PROTECTED] writes: I am wondering how most users generate reports from mysql that they can print on a laser printer (e.g. invoices)? [...] I guess some people user something like Crystal Reports via ODBC, but I wonder what most people do??? Use Crystal Reports :-)? Depends on (a) how professional you need the reports to be, and (b) how much you can afford.. But more seriously, you *could* create a layout for "tbl" (part of GNU "groff", available for all POSIX platforms and even NT via the cygwin port), and generate output using that template: .TS H page-header specs .TH other header specs. header lines body lines-- generate this using MySQL .TE And then run all this through "tbl" followed by "groff". Haven't tried this myself in this particular combination, but having used tbl in the past, I don't see any major conceptual problems with it. It's free, but needs some elbow grease to get it to work.. A "tbl" reference is at http://www.primate.wisc.edu/software/troffcvt/tbl.html. -- Shankar Unni [EMAIL PROTECTED]
RE: Second Request - Limit Filed Input
I don't see implementation of "CHECK" CONSTRAINTS in the TODO list (e.g.
CONSTRAINT val_ck check (val = 0 and val =5)
Is this planned?
I do see FOREIGN KEY constraints on the 4.0 list, so there is a way to do
this kind of checking once this is implemented:
* Create an associated "range-check" table with a primary key column, and
add rows for the values you want to allow in your table column.
* Put a foreign key constraint on your column to point at that table.
This will at least ensure that you get a constraint violation if you attempt
to insert something illegal.
This works only for small ranges or other enumeration types (integer or
string). E.g.
create table check_phase_number (phase integer);
insert into check_phase_number values (1),(2),(3),(4),(5);
create table check_state_abbrev (state char(2));
insert into check_state_abbrev values ('AK'),('AL'),('CA'), ...
('VT'),('WA');
create table my_state_migration (
phase integer constraint phase_fk foreign key references
check_phase_number(phase),
state char(2) constraint state_fk foreign key references
check_state_abbrev(state),
...
);
-Original Message-
From: Cal Evans [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, March 20, 2001 8:27 AM
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: RE: Second Request - Limit Filed Input
you can use either an ENUM or a SET. Other than that, no.
Cal
http://www.calevans.com
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, March 20, 2001 10:05 AM
To: [EMAIL PROTECTED]
Subject: Second Request - Limit Filed Input
Is there a way in MySQL to limit the values that a (numeric) field can hold.
For example if I want a given field to only have the values 1 - 10 can I do
this and can it be done in the definition of the field when the table is
created?
-
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
BDB and transactions
After the vitriolic debates I've seen between the PostgreSQL fanatics and the MySQL defenders, I thought I'd ask for clarification on the support for transactions that's supposedly provided by the use of BDB tables: Having used Oracle for a while (and given my understanding of how transactions work there), does using BDB guarantee that: * DML that's not explicitly COMMITted will be automatically ROLLedBACK if the connection is terminated prematurely? * Other connections will continue to see a perfectly consistent snapshot of the DB from before the start of the transaction, as long as the transaction is not committed? * If the DB server process crashes in the middle of a transaction, then on restart, it will present a view of the DB without any of the effects of uncommitted transactions? And *with* all the effects of all committed transactions (needless to say)? * How about locking? While a transaction is not committed, how much of the tables that it modifies is automatically locked against updates by other connections? The whole table? A page of the table? A row (dare I hope?)? * (To labor a point) If a connection dies in the middle of a transaction holding locks, will it automatically roll them changes and free the locks and unblock any other operations waiting on that lock? Just checking.. Thanks! -- Shankar Unni[EMAIL PROTECTED] (408) 434-8311
