Re: Can't Access Via Webmin or PhpMyAdmin.
Mark Sargent wrote: Hi All, I can start mysql with the following cmd, /etc/rc.d/init.d/mysql start although, I had to change bindir=./bin to bindir=/usr/local/mysql/bin to get it to work. I can access the DB with, mysql -h localhost -u root -p successfully, but, when trying to access via phpmyadmin or webmin it doesn't work. I've set the config files to what I believe are correct. phpmyadmin error below, Error *MySQL said: *Documentation http://dev.mysql.com/doc/mysql/en/Error-returns.html | #2002 - Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) ||Now, I've got user as root and no password, as I haven't set 1 yet. Why am I getting this.? Anyone seen this before.? With webmin, I get this displayed, |*MySQL is not running on your system - database list could not be retrieved. *Click this button to start the MySQL database server on your system with the command /etc/rc.d/init.d/mysql start. This Webmin module cannot administer the database until it is started. even though the server is running, confirmed with the following, [EMAIL PROTECTED] ~]# ps -uxwww | grep mysql Warning: bad syntax, perhaps a bogus '-'? See /usr/share/doc/procps-3.2.3/FAQ root 4640 0.0 0.2 4180 1116 ?S14:19 0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/lib/mysql/localhost.localdomain.pid root 4771 0.0 0.0 1580 124 pts/2R+ 14:36 0:00 grep mysql Is anyone on here running webmin to manage mysql..? I'm curious how you got it to run..? If I stop the server via cli and then click the start mysql server button on the webmin page, it goes back to the same message, but, the server is started. I changed the config module page to reflect my installation. What have I missed, perhaps..? Cheers. Mark Sargent. Hi All, below is the config of webmin mysql module, *Path to mysqlshow command* /usr/bin/mysqlshow *Path to mysqladmin command*/usr/local/mysql/bin/mysqladmin *Path to mysql command* /usr/local/mysql/bin/mysql *Path to mysqldump command* /usr/local/mysql/bin/mysqldump *Path to mysqlimport command* /usr/local/mysql/bin/mysqlimport *Command to start MySQL server* /etc/rc.d/init.d/mysql start *Command to stop MySQL server* /etc/rc.d/init.d/mysql stop *Path to MySQL shared libraries directory* *Path to MySQL databases directory* /var/lib/mysql I hope that helps. Cheers. Mark Sargent. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
order by version number
Hi, in my table I have one varchar(20) column where I store version number. Version looks like: 1.1.2 1.2.1 1.10.3 It is possible order this column in natural order (1.2 before 1.10)? Stano. -- Stanislav Paka programtor, www skupina KIOS s.r.o. tel: 033 / 794 00 18 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: using if in select statement
Hi, you can continue playing. It's a true game :o) Best Regards Mathias FATENE Hope that helps *This not an official mysql support answer -Original Message- From: James Black [mailto:[EMAIL PROTECTED] Sent: vendredi 29 avril 2005 02:20 To: 'mysql@lists.mysql.com ' Subject: re: using if in select statement -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 My boss and I were playing with using select statements, and we can actually execute subqueries as an option if the result is true or false. Is this expected behavior, or is it something that may be fixed in a revision, before I begin to depend on it being acceptable behavior. Thanx. - -- Corruptisima republica plurimae leges. [The more corrupt a republic, the more laws.] Tacitus from Annals III, 116AD Blogs: http://jamesruminations.blogspot.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.0 (MingW32) iD8DBQFCcX2xJ/zyYkX46joRAgiVAJ9rw9BRPuT164/4wpYlHJbdj+x1agCcCbKG fM7SPPMIo6QSWijniegUM9A= =wK54 -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: order by version number
Hi Stano,
there was a response by Michael Stassen on the list about a year ago on
this problem, which I hope he doesn't mind if I repeat below.
/Johan
Version numbers?
CREATE TABLE ss (version VARCHAR(13));
INSERT INTO ss VALUES ('1'),('1.1'),('1.2.1.2'),('1.10.1'),('1.2'),
('1.4.1'),('2.1'),('2.2.1.2'),('2.10.1'),('2.4.1');
If each part is no larger than 255, you can leverage INET_ATON() to do
what you want (up to the 4th part). The trick is making each of these
look like an IP first by using CONCAT to add '0.0.0' to make sure every
row has at least 4 parts, then SUBSTRING_INDEX to pull out just the
first 4 parts.
SELECT version FROM ss
ORDER BY INET_ATON(SUBSTRING_INDEX(CONCAT(version,'.0.0.0'),'.',4));
+-+
| version |
+-+
| 1 |
| 1.1 |
| 1.2 |
| 1.2.1.2 |
| 1.4.1 |
| 1.10.1 |
| 2.1 |
| 2.2.1.2 |
| 2.4.1 |
| 2.10.1 |
+-+
10 rows in set (0.00 sec)
Now, I must point out that because we are sorting on a function of the
column, rather than on the column itself, we cannot use an index on the
column to help with the sort. In other words, the sorting will be
relatively slow.
One solution would be to separate the parts into separate columns, each
of which could then be an appropriately sized integer (TINYINT,
perhaps). You could use 0 or NULL for the missing parts, as you see fit
(NULLS preserve the output format you specified, e.g. 1.1, but
complicate matching).
CREATE TABLE ss2 (v1 TINYINT UNSIGNED, v2 TINYINT UNSIGNED,
v3 TINYINT UNSIGNED, v4 TINYINT UNSIGNED,
KEY version_idx (v1,v2,v3,v4));
INSERT INTO ss2 VALUES (1,NULL,NULL,NULL),(1,1,NULL,NULL),(1,2,1,2),
(1,10,1,0),(1,2,0,0),(1,4,1,0),(2,1,0,0),(2,2,1,2),
(2,10,1,NULL),(2,4,1,NULL);
Glue the parts together with CONCAT_WS() when you select them, and sort
by all 4 parts:
SELECT CONCAT_WS('.',v1,v2,v3,v4) version FROM ss2 ORDER BY v1,v2,v3,v4;
+--+
| version |
+--+
| 1|
| 1.1 |
| 1.2.0.0 |
| 1.2.1.2 |
| 1.4.1.0 |
| 1.10.1.0 |
| 2.1.0.0 |
| 2.2.1.2 |
| 2.4.1|
| 2.10.1 |
+--+
10 rows in set (0.00 sec)
In this case, the multicolumn index on the 4 parts will be used to sort.
Another option would be to use INET_ATON() when storing the values.
CREATE TABLE ss3 (v INT UNSIGNED, KEY version_idx (v));
In this case, you'd have to use 0 for missing parts.
INSERT INTO ss3 VALUES (INET_ATON('1.0.0.0')), (INET_ATON('1.1.0.0')),
(INET_ATON('1.2.1.2')), (INET_ATON('1.10.1.0')),
(INET_ATON('1.2.0.0')), (INET_ATON('1.4.1.0')),
(INET_ATON('2.1.0.0')), (INET_ATON('2.2.1.2')),
(INET_ATON('2.10.1.0')),(INET_ATON('2.4.1.0'));
Use INET_NTOA() when selecting to display dotted numbers, but sort by
the values already in the column.
SELECT INET_NTOA(v) version FROM ss3 ORDER BY v;
+--+
| version |
+--+
| 1.0.0.0 |
| 1.1.0.0 |
| 1.2.0.0 |
| 1.2.1.2 |
| 1.4.1.0 |
| 1.10.1.0 |
| 2.1.0.0 |
| 2.2.1.2 |
| 2.4.1.0 |
| 2.10.1.0 |
+--+
10 rows in set (0.00 sec)
As with the second version, the index on v will be used for the sort.
Finally, starting with mysql 4.1.2, INET_ATON() will assume 0s for
missing parts, so long as you have at least two parts. That is, 1.1
will automatically be treated as 1.1.0.0, 1.0 as 1.0.0.0, and 1.2.3 as
1.2.3.0 (but 1 will be treated as 0.0.0.1). With 4.1.2 then, the above
could be simplified slightly.
Michael
Stano Paska wrote:
Hi,
in my table I have one varchar(20) column where I store version number.
Version looks like:
1.1.2
1.2.1
1.10.3
It is possible order this column in natural order (1.2 before 1.10)?
Stano.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: order by version number
Hi, select a from versions order by substring_index(a,'.',-2); Best Regards Mathias FATENE Hope that helps *This not an official mysql support answer -Original Message- From: Stano Paska [mailto:[EMAIL PROTECTED] Sent: vendredi 29 avril 2005 08:21 To: mysql@lists.mysql.com Subject: order by version number Hi, in my table I have one varchar(20) column where I store version number. Version looks like: 1.1.2 1.2.1 1.10.3 It is possible order this column in natural order (1.2 before 1.10)? Stano. -- Stanislav Paka programtor, www skupina KIOS s.r.o. tel: 033 / 794 00 18 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: order by version number
On Fri, 29 Apr 2005 08:20:58 +0200, Stano Paska [EMAIL PROTECTED] wrote: Hi, in my table I have one varchar(20) column where I store version number. Version looks like: 1.1.2 1.2.1 1.10.3 It is possible order this column in natural order (1.2 before 1.10)? Stano. Hello Stano, What about divide 1 text column into 3 integer columns? SELECT * FROM table ORDER BY MajorVersion, MinorVersion, Release Dusan -- Dusan Kolesar Helsinska 19 040 13 Kosice Slovakia e-mail : [EMAIL PROTECTED] ICQ# : 160507424 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Select Query!
Hi, Would this be the best way, if I was to run query to obtain first 2 records of each category from the database. The database could contain literally 100s of 1000s of records. select t1.field1, t2.field2 as CommonFld from table1 t1, table2 t2 where t1.keyfield = t2.keyfield and t1.field1 = 'AAA' limit 2 UNION select t1.field1, t2.field2 as CommonFld from table1 t1, table2 t2 where t1.keyfield = t2.keyfield and t1.field1 = 'BBB' limit 2 ORDER BY CommonFld Desc regards Send instant messages to your online friends http://uk.messenger.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql-standar aix 4.3.3 powerpc binary download / INSTALLATION PROBLEM
after gunzipping and untarring i get to point in instructions to run script mysql_install_db i get the following error: # cd /convert # cd mysql # scripts/mysql_install_db exec(): 0509-036 Cannot load program ./bin/my_print_defaults because of the foll owing errors: Symbol resolution failed for my_print_defaults because: Symbol stpcpy (number 52) is not exported from dependent module /usr/lib/libc.a(shr.o). Symbol isinf (number 53) is not exported from dependent module /usr/lib/libc.a(shr.o). Examine .loader section symbols with the 'dump -Tv' command. Neither host '?' nor 'localhost' could be looked up with ./bin/resolveip Please configure the 'hostname' command to return a correct hostname. If you want to solve this at a later stage, restart this script with the --force option # in need of hand holding, first timeer on mysql. heres the mysqlbug output. Submitter-Id: submitter ID Originator: Organization: organization of PR author (multiple lines) MySQL support: [none | licence | email support | extended email support ] Synopsis: synopsis of the problem (one line) Severity: [ non-critical | serious | critical ] (one line) Priority: [ low | medium | high ] (one line) Category: mysql Class: [ sw-bug | doc-bug | change-request | support ] (one line) Release: mysql-4.1.11-standard (MySQL Community Edition - Standard (GPL)) C compiler: VisualAge C++ Professional / C for AIX Compiler, Version 6 C++ compiler:VisualAge C++ Professional / C for AIX Compiler, Version 6 Environment: machine, os, target, libraries (multiple lines) System: AIX concord1 3 4 000C55BD4C00 Some paths: /usr/bin/make Compilation info: CC='xlc_r' CFLAGS='-ma -O2 -qstrict -qoptimize=2 -qmaxmem=819 2' CXX='xlC_r' CXXFLAGS='-ma -O2 -qstrict -qoptimize=2 -qmaxmem=8192' LDFLAGS ='' ASFLAGS='' LIBC: lrwxrwxrwx 1 bin bin 19 Sep 07 2004 /lib/libc.a - /usr/ccs/li b/libc.a lrwxrwxrwx 1 bin bin 19 Sep 07 2004 /usr/lib/libc.a - /usr/cc s/lib/libc.a Configure command: ./configure '--prefix=/usr/local/mysql' '--localstatedir=/usr /local/mysql/data' '--libexecdir=/usr/local/mysql/bin' '--with-comment=MySQL Com munity Edition - Standard (GPL)' '--with-extra-charsets=complex' '--with-server- suffix=-standard' '--enable-thread-safe-client' '--enable-local-infile' '--with- named-z-libs=no' '--disable-shared' '--with-readline' '--with-embedded-server' ' --with-archive-storage-engine' '--with-innodb' 'CC=xlc_r' 'CFLAGS=-ma -O2 -qstri ct -qoptimize=2 -qmaxmem=8192' 'CXXFLAGS=-ma -O2 -qstrict -qoptimize=2 -qmaxmem= 8192' 'CXX=xlC_r' # # Yours Sincerely, Damian McMenamin Analyst Programmer Melbourne Australia Cell: (61)040-0064107 Email: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Efficient select/insert
On 4/26/05, Jonathan Mangin [EMAIL PROTECTED] wrote:
I would like to select several rows from one table
and insert them into another nearly identical table
using Perl/DBI:
my @array = $q-param(); # HTML checkboxes
foreach my $element (@array) {
my $sql = select col2, col3, col4 from table1
where col1 = ?;
my $sth = $dbh-prepare($sql);
$sth-execute($element) or die $sth-errstr();
my @row = $sth-fetchrow_array;
$sql = insert table2 (col1, col2, col3, col4)
values (NULL, ?, ?, ?);
$sth = $dbh-prepare($sql);
$sth-execute($row[0], $row[1], $row[2]) or die $sth-errstr();
}
Is this efficient db interaction, or is there a better way?
This is 3.23 but can upgrade if necessary.
Is there any reason you didn't just simply do:
INSERT INTO table2 (col1,col2,col3,col4) SELECT NULL, col2, col3 col4
FROM table1;
...which is what SQL was made for in a first place. :) If there is a reason,
then I am sorry, I just didn't see it in the mail. I just don't understand
why sometimes people go at lengths to reimplement SQL in other
languages like perl.
Regards,
Dawid
I've seen things that you people wouldn't believe, I watched perl making
SELECT on two tables, then join the results using perl's hash keys.
All these moments will be lost, like tears in the rain... ;)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Good book on MySQL 5.0
Can anyone please suggest a good book for MySQL 5.0 . Also can anyone suggest how far it will be reliable if I use MySQL 5.0 to change my database from Oracle to Mysql. As I have a lot of Stored Procs for migration. It will also be very helpful if someone can give an approximate date when the stable version of MySql 5.0 is going to come out. Thank you sujay Sujay Koduri Project Trainee Andale INDIA pvt Ltd Bangalore -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
new insallation of mysql not starting
I have just upgraded a mySQL installation on a Soalri 2.8 box for the source. It compiled and installed without error. The startup script redirect STDOUT STDERR into /dev/null so this is what I did ... /usr/bin/mysqld_safe --datadir=/data/mysql-data/ --pid-file=/data/mysql-data/mysql.pid Starting mysqld daemon with databases from /data/mysql-data/ STOPPING server from pid file /data/mysql-data/romulus.pid 050429 11:19:13 mysqld ended Anyone have any ideas what the code 050429 means? And where are the log files? Thanks Andy This e-mail is private and may be confidential and is for the intended recipient only. If misdirected, please notify us by telephone and confirm that it has been deleted from your system and any copies destroyed. If you are not the intended recipient you are strictly prohibited from using, printing, copying, distributing or disseminating this e-mail or any information contained in it. We use reasonable endeavours to virus scan all e-mails leaving the Company but no warranty is given that this e-mail and any attachments are virus free. You should undertake your own virus checking. The right to monitor e-mail communications through our network is reserved by us.
RE: new insallation of mysql not starting
Whoops - that wasn't an error code (thanks Prasanna!) Here is the log file.. 050429 11:32:08 mysqld started InnoDB: The first specified data file ./ibdata1 did not exist: InnoDB: a new database to be created! 050429 11:32:09 InnoDB: Setting file ./ibdata1 size to 10 MB InnoDB: Database physically writes the file full: wait... 050429 11:32:10 InnoDB: Log file ./ib_logfile0 did not exist: new to be created InnoDB: Setting log file ./ib_logfile0 size to 5 MB InnoDB: Database physically writes the file full: wait... 050429 11:32:10 InnoDB: Log file ./ib_logfile1 did not exist: new to be created InnoDB: Setting log file ./ib_logfile1 size to 5 MB InnoDB: Database physically writes the file full: wait... InnoDB: Doublewrite buffer not found: creating new InnoDB: Doublewrite buffer created InnoDB: Creating foreign key constraint system tables InnoDB: Foreign key constraint system tables created 050429 11:32:12 InnoDB: Started; log sequence number 0 0 050429 11:32:13 [ERROR] Fatal error: Can't open privilege tables: Table 'mysql.host' doesn't exist 050429 11:32:13 mysqld ended Thanks Andy -Original Message- From: Andy Ford [mailto:[EMAIL PROTECTED] Sent: Friday, April 29, 2005 12:26 PM To: Mysql (E-mail) Subject:new insallation of mysql not starting I have just upgraded a mySQL installation on a Soalri 2.8 box for the source. It compiled and installed without error. The startup script redirect STDOUT STDERR into /dev/null so this is what I did ... /usr/bin/mysqld_safe --datadir=/data/mysql-data/ --pid-file=/data/mysql-data/mysql.pid Starting mysqld daemon with databases from /data/mysql-data/ STOPPING server from pid file /data/mysql-data/romulus.pid 050429 11:19:13 mysqld ended Anyone have any ideas what the code 050429 means? And where are the log files? Thanks Andy This e-mail is private and may be confidential and is for the intended recipient only. If misdirected, please notify us by telephone and confirm that it has been deleted from your system and any copies destroyed. If you are not the intended recipient you are strictly prohibited from using, printing, copying, distributing or disseminating this e-mail or any information contained in it. We use reasonable endeavours to virus scan all e-mails leaving the Company but no warranty is given that this e-mail and any attachments are virus free. You should undertake your own virus checking. The right to monitor e-mail communications through our network is reserved by us. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
full-text search
I have a TEXT field in my db (4.0) that has lists of IP numbers; can a full-text search be done for IP numbers? If so could you show me an example query? Thanks Rob
full-text search
I have a TEXT field in my db (4.0) that has lists of IP numbers; can a full-text search be done for IP numbers? If so could you show me an example query? Thanks Rob
Re: mysql top 2 rows for each group
I don't have any PK in this table, but If you like one, I can add another column for PK, like, ++---+ | id | seqno | ++---+ | 1 | 00122 | | 2 | 00123 | | 3 | 00123 | | 4 | 00123 | | 5 | 00336 | | 6 | 00346 | | 7 | 00349 | | 8 | 00427 | | 9 | 00427 | | 10 | 00427 | ++---+--+ I searched on internet before, there is some solution like this, set @temp1:=0 set @temp2:=0 update test set item=if([EMAIL PROTECTED],@temp1:[EMAIL PROTECTED], @temp2:= seqno); but this logic is not correct, does anyone know how to solve this one? [EMAIL PROTECTED] wrote: Jay Blanchard [EMAIL PROTECTED] wrote on 04/28/2005 04:24:23 PM: [snip] I have question about how I can get top 2 rows for each group. like I have table test | seqno | +---+ | 00122 | | 00123 | | 00123 | | 00123 | | 00336 | | 00346 | | 00349 | | 00427 | | 00427 | | 00427 | +---+--+ I like have +---+--+ | seqno | item | +---+--+ | 00122 | 1 | | 00123 | 1 | | 00123 | 2 | | 00123 |3 | | 00336 | 1 | | 00346 | 1 | | 00349 | 1 | | 00427 | 1 | | 00427 | 2 | | 00427 |3 | +---+--+ Then I can have select * from test where item 3 to find all top 2 rows. [/snip] I think you want ... SELECT DISTINCT(seqno) FROM test ORDER BY seqno LIMIT 2 I think that will result in only two rows total, not two per group. Vivian? What is the PK for your table? What value or combination of values uniqely identifies each row of your source table? It can't be seqno as you already demonstrated that there are duplicate values in that column. I may have an idea but I need to know more about your data. Posting the results of SHOW CREATE TABLE xxx\G for your source table would be ideal. Thanks, Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql top 2 rows for each group
Hi, have you read my answer yesterday ? Mathias Selon Vivian Wang [EMAIL PROTECTED]: I don't have any PK in this table, but If you like one, I can add another column for PK, like, ++---+ | id | seqno | ++---+ | 1 | 00122 | | 2 | 00123 | | 3 | 00123 | | 4 | 00123 | | 5 | 00336 | | 6 | 00346 | | 7 | 00349 | | 8 | 00427 | | 9 | 00427 | | 10 | 00427 | ++---+--+ I searched on internet before, there is some solution like this, set @temp1:=0 set @temp2:=0 update test set item=if([EMAIL PROTECTED],@temp1:[EMAIL PROTECTED], @temp2:= seqno); but this logic is not correct, does anyone know how to solve this one? [EMAIL PROTECTED] wrote: Jay Blanchard [EMAIL PROTECTED] wrote on 04/28/2005 04:24:23 PM: [snip] I have question about how I can get top 2 rows for each group. like I have table test | seqno | +---+ | 00122 | | 00123 | | 00123 | | 00123 | | 00336 | | 00346 | | 00349 | | 00427 | | 00427 | | 00427 | +---+--+ I like have +---+--+ | seqno | item | +---+--+ | 00122 | 1 | | 00123 | 1 | | 00123 | 2 | | 00123 |3 | | 00336 | 1 | | 00346 | 1 | | 00349 | 1 | | 00427 | 1 | | 00427 | 2 | | 00427 |3 | +---+--+ Then I can have select * from test where item 3 to find all top 2 rows. [/snip] I think you want ... SELECT DISTINCT(seqno) FROM test ORDER BY seqno LIMIT 2 I think that will result in only two rows total, not two per group. Vivian? What is the PK for your table? What value or combination of values uniqely identifies each row of your source table? It can't be seqno as you already demonstrated that there are duplicate values in that column. I may have an idea but I need to know more about your data. Posting the results of SHOW CREATE TABLE xxx\G for your source table would be ideal. Thanks, Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqld just ending...
After almost a year of being up, my RH9 server lost power and rebooted. When it came back up, mysql was not running. I went in and ran safe_mysql to start it back up (I also tried the init.d script) but it failed to start. No errors, nothing in the log. After some investigation, it appears that the .pid file is never being created, so the safe_mysql ends immediately. The only entry in the log or error files in the a notice that mysqld ended, and that's it. The only clue I have is several month ago I was playing around with the mysql user and gave it a password. I can't imagine how that would cause a problem, but it's the only thing I can think of. If this is the problem, I'm not sure how to get the mysql user back to the default state. Is the problem the mysql user? All the permission for all the database directories are set correctly. I'm am puzzled that mysqld just ends, not messages. Any clues? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help with stored procedures in MySQL
Hi Sreedhar, all! madderla sreedhar wrote: --- Joerg Bruehe [EMAIL PROTECTED] wrote: Hi! [[...]] The manual states clearly: Stored procedures and functions are a new feature in MySQL version 5.0. (At the proper place: 19 Stored Procedures and Functions) Sreedhar, if you have a follow-up question, please direct it to the list, so that all others cam also participate / profit. presently iam workig with mysql 4.1 and you wrote that stored procedures and fuctions are new feature in mysql 5.0 , which i doesn't have. Is there any alternative solution to create procedures and fuctions in 4.1 if not where can i get the newer version of the mysql5.0 downloadable version. The alternative is to do without stored procedures and to code that in your application. For 5.0, visit the download page and select a mirror close to you: http://dev.mysql.com/downloads/mysql/5.0.html HTH, Joerg -- Joerg Bruehe, Senior Production Engineer MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql top 2 rows for each group
I did like this, look good, anyone can try it. set @a:=0; set @b:=0; update test set item=if([EMAIL PROTECTED], @a:[EMAIL PROTECTED], if(@b:=seqno, @a:=1, 0)); Vivian Wang wrote: I don't have any PK in this table, but If you like one, I can add another column for PK, like, ++---+ | id | seqno | ++---+ | 1 | 00122 | | 2 | 00123 | | 3 | 00123 | | 4 | 00123 | | 5 | 00336 | | 6 | 00346 | | 7 | 00349 | | 8 | 00427 | | 9 | 00427 | | 10 | 00427 | ++---+--+ I searched on internet before, there is some solution like this, set @temp1:=0 set @temp2:=0 update test set item=if([EMAIL PROTECTED],@temp1:[EMAIL PROTECTED], @temp2:= seqno); but this logic is not correct, does anyone know how to solve this one? [EMAIL PROTECTED] wrote: Jay Blanchard [EMAIL PROTECTED] wrote on 04/28/2005 04:24:23 PM: [snip] I have question about how I can get top 2 rows for each group. like I have table test | seqno | +---+ | 00122 | | 00123 | | 00123 | | 00123 | | 00336 | | 00346 | | 00349 | | 00427 | | 00427 | | 00427 | +---+--+ I like have +---+--+ | seqno | item | +---+--+ | 00122 | 1 | | 00123 | 1 | | 00123 | 2 | | 00123 |3 | | 00336 | 1 | | 00346 | 1 | | 00349 | 1 | | 00427 | 1 | | 00427 | 2 | | 00427 |3 | +---+--+ Then I can have select * from test where item 3 to find all top 2 rows. [/snip] I think you want ... SELECT DISTINCT(seqno) FROM test ORDER BY seqno LIMIT 2 I think that will result in only two rows total, not two per group. Vivian? What is the PK for your table? What value or combination of values uniqely identifies each row of your source table? It can't be seqno as you already demonstrated that there are duplicate values in that column. I may have an idea but I need to know more about your data. Posting the results of SHOW CREATE TABLE xxx\G for your source table would be ideal. Thanks, Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: No error / warning when data is truncated on insertion into mysql
I wish I could upgrade - believe me I would love to but the product we use is supposedly not compatible with any higher version than 4.0.23 :-(. Is there any workaround at all - even something that is known to be a bit performance intensive as our application is not that heavy!! Thanks a TON for your answers. Anoop On 4/28/05, Paul DuBois [EMAIL PROTECTED] wrote: At 19:39 -0400 4/28/05, Anoop kumar V wrote: I mean: Should I enable something like 'strict checking' / verbose so that MYSQLcomplains and refrains from inserting truncated data in the tables?? Yes, but you'll need MySQL 5.0 to do it. http://dev.mysql.com/doc/mysql/en/server-sql-mode.html Thanks, Anoop On 4/28/05, Anoop kumar V [EMAIL PROTECTED] wrote: Thank you, But the show warnings does not seem to work on my prompt. I am using mysql SELECT VERSION(); +---+ | VERSION() | +---+ | 4.0.23-nt | +---+ And even if it did and does how will that reflect in my tomcat logs?? I mean I want a way where without user interaction any such warnings are recorded somewhere.. Also why does it insert at all - I just checked with Sybase and previously MS Sql server - both display a very visible error message and DO NOT insert the data (although the bug filled MS SQL server said data may have been truncated when data was not even inserted!!) Should I enable somethink like strict checking so that MSSQL complains and refrains from inserting truncated data in the tables?? Thanks and r, Anoop Kumar V. On 4/28/05, mathias fatene [EMAIL PROTECTED] wrote: Hi, I think you shoul dcatch the show warnings command cause in mysql client you see the number of warnings. Data are even truncated according to the limit of the type (tinyint, int ...). Example : mysql create table toto(a tinyint,b char(5)); Query OK, 0 rows affected (0.06 sec) mysql insert into toto values (500,'Long text'); Query OK, 1 row affected, 2 warnings (0.02 sec) It's said here that i have 2 warnings. mysql show warnings - ; +-+--+-- + | Level | Code | Message | +-+--+-- + | Warning | 1264 | Data truncated; out of range for column 'a' at row 1 | | Warning | 1265 | Data truncated for column 'b' at row 1 | +-+--+-- + 2 rows in set (0.00 sec) mysql select * from toto; +--+--+ | a | b | +--+--+ | 127 | Long | my 500 is also truncated +--+--+ 1 row in set (0.00 sec) Best Regards Mathias FATENE Hope that helps *This not an official mysql support answer -Original Message- From: Anoop kumar V [mailto: [EMAIL PROTECTED] Sent: vendredi 29 avril 2005 00:21 To: mysql@lists.mysql.com Subject: No error / warning when data is truncated on insertion into mysql I am using MySQL and SQL server with Tomcat. Our application writes into both databases (mysql and ms sql server) at once based on some data collected from an end user. Now if the end user enters more data (characters) than the column can hold, the data obviously gets truncated. But the surprising thing is that although MS SQL server sends a warning message to tomcat (seen on the tomcat console) that data may have been truncated - MySQL does not show any warning message (I would have expected an error actually) as the data in the column is not what the data was intended to be. (Actually MS SQL shows the error and does not even insert the data...) Does MySQL not care or maybe I need to activate some option in MySQL like verbose or stict checking etc... It just truncated and inserted the data with no warning / error or any hassle!! how can i force mysql to check for such inconsistencies and report?? -- Thanks and best regards, Anoop -- Thanks and best regards, Anoop -- Thanks and best regards, Anoop -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com http://www.mysql.com -- Thanks and best regards, Anoop
Re: full-text search
Ron McKeever [EMAIL PROTECTED] wrote on 29/04/2005 14:09:38: I have a TEXT field in my db (4.0) that has lists of IP numbers; can a full-text search be done for IP numbers? Unfortunately not, because Fulltext regards the dots as terminators. The IP address 192.168.32.2 will therefore be keyed as the four words 192, 168, 32, and 2. Since some of these fall below the minimum word length, they will be ignored. However, you might look at the functions INET_NTOA() and INET_ATON() which convert between the string and 32-bit binary representations of an IP address. http://dev.mysql.com/doc/mysql/en/miscellaneous-functions.html Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
replication and switching master
Hi! I have setup replication with one master and one slave. What do I have to think about in order to minimize hassle and downtime to switch this setup to a new master? My thought was to setup the new master as a slave, and then switch to the new master. Is this a nice way of doing it? -- patrik_wallstrom-foodfight-[EMAIL PROTECTED]+46-733173956 `- http://www.gnuheter.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql top 2 rows for each group
Vivian, Mathias was correct. I will be building on Mathias's answer by using your PK for your original data table (since you neglected to name it I will call it source_table) WITH THE ASSUMPTION that you cannot change your data structure and that you already have a PK on the table: Create a temporary table that counts how many records have each seqno (as Mathias said yesterday, this works because of a MySQL extension to auto_increment behavior and it only worked for him with a MyISAM table.) CREATE TEMPORARY TABLE tblSeqSort ( seqno int , seqcounter int auto_increment , id int , PRIMARY KEY (seqno, seqcounter) , UNIQUE (id) ); Then populate it in a particular order (the requirement of first two implies there is some kind of intrinsic order. I will sort by the ID value due to the lack of any other information about your data) INSERT tblSeqSort (seqno, id) SELECT seqno, id FROM source_table ORDER BY seqno, id; Then, just as Mathias demonstrated yesterday, you can get at just the first two records of each sequence by doing this SELECT st.id, st.seqno, other fields from source_table FROM source_table st INNER JOIN tblSeqSort sort ON sort.id = st.id AND sort.seqcounter =2; The drawback to this process is that you need to update your sequencing table each time you update your source_table or you could miss out on your most recently added source_table records. That can make this type of query time consuming and it may not be appropriate for many situations. It also requires more maintenance on your part. Mathias's solution (the simple solution) was to add the sequencing number to your original table. That way your data stays in sequence and the simple query is just what he proposed. This statement worked on a test table I have: ALTER TABLE source_table DROP PRIMARY KEY, ADD COLUMN seqcounter int auto_increment, ADD PRIMARY KEY (seqno, seqcounter); That makes your query: SELECT source_table field list FROM source_table WHERE seqcounter =2; Shawn Green Database Administrator Unimin Corporation - Spruce Pine [EMAIL PROTECTED] wrote on 04/29/2005 09:31:23 AM: Hi, have you read my answer yesterday ? Mathias Selon Vivian Wang [EMAIL PROTECTED]: I don't have any PK in this table, but If you like one, I can add another column for PK, like, ++---+ | id | seqno | ++---+ | 1 | 00122 | | 2 | 00123 | | 3 | 00123 | | 4 | 00123 | | 5 | 00336 | | 6 | 00346 | | 7 | 00349 | | 8 | 00427 | | 9 | 00427 | | 10 | 00427 | ++---+--+ I searched on internet before, there is some solution like this, set @temp1:=0 set @temp2:=0 update test set item=if([EMAIL PROTECTED],@temp1:[EMAIL PROTECTED], @temp2:= seqno); but this logic is not correct, does anyone know how to solve this one? [EMAIL PROTECTED] wrote: Jay Blanchard [EMAIL PROTECTED] wrote on 04/28/2005 04:24:23 PM: [snip] I have question about how I can get top 2 rows for each group. like I have table test | seqno | +---+ | 00122 | | 00123 | | 00123 | | 00123 | | 00336 | | 00346 | | 00349 | | 00427 | | 00427 | | 00427 | +---+--+ I like have +---+--+ | seqno | item | +---+--+ | 00122 | 1 | | 00123 | 1 | | 00123 | 2 | | 00123 |3 | | 00336 | 1 | | 00346 | 1 | | 00349 | 1 | | 00427 | 1 | | 00427 | 2 | | 00427 |3 | +---+--+ Then I can have select * from test where item 3 to find all top 2 rows. [/snip] I think you want ... SELECT DISTINCT(seqno) FROM test ORDER BY seqno LIMIT 2 I think that will result in only two rows total, not two per group. Vivian? What is the PK for your table? What value or combination of values uniqely identifies each row of your source table? It can't be seqno as you already demonstrated that there are duplicate values in that column. I may have an idea but I need to know more about your data. Posting the results of SHOW CREATE TABLE xxx\G for your source table would be ideal. Thanks, Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqld just ending...
Ron, I have run into the issue you described in the past. Do the following: touch path_to_mysql/name_of_pid chown mysql:mysql path_to_mysql/name_of_pid Hope this helps. Quoting Ron Gilbert [EMAIL PROTECTED]: After almost a year of being up, my RH9 server lost power and rebooted. When it came back up, mysql was not running. I went in and ran safe_mysql to start it back up (I also tried the init.d script) but it failed to start. No errors, nothing in the log. After some investigation, it appears that the .pid file is never being created, so the safe_mysql ends immediately. The only entry in the log or error files in the a notice that mysqld ended, and that's it. The only clue I have is several month ago I was playing around with the mysql user and gave it a password. I can't imagine how that would cause a problem, but it's the only thing I can think of. If this is the problem, I'm not sure how to get the mysql user back to the default state. Is the problem the mysql user? All the permission for all the database directories are set correctly. I'm am puzzled that mysqld just ends, not messages. Any clues? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] James Nobis Web Developer Academic Superstore 223 W. Anderson Ln. Suite A110, Austin, TX 78752 Voice: (512) 450-1199 x453 Fax: (512) 450-0263 http://www.academicsuperstore.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqld just ending...
Hello. Check that you don't have problems with filesystem. Start mysqld directly, specify the corresponding variables in the command line. If it doesn't produce errors switch to the debug version of the server and use the trace files to find where the problem is. Ron Gilbert [EMAIL PROTECTED] wrote: After almost a year of being up, my RH9 server lost power and rebooted. When it came back up, mysql was not running. I went in and ran safe_mysql to start it back up (I also tried the init.d script) but it failed to start. No errors, nothing in the log. After some investigation, it appears that the .pid file is never being created, so the safe_mysql ends immediately. The only entry in the log or error files in the a notice that mysqld ended, and that's it. The only clue I have is several month ago I was playing around with the mysql user and gave it a password. I can't imagine how that would cause a problem, but it's the only thing I can think of. If this is the problem, I'm not sure how to get the mysql user back to the default state. Is the problem the mysql user? All the permission for all the database directories are set correctly. I'm am puzzled that mysqld just ends, not messages. Any clues? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: purge log fails
Hello. See: http://dev.mysql.com/doc/mysql/en/crashing.html Marten Lehmann [EMAIL PROTECTED] wrote: MySQL could die during your query. What is in error log? Oh my god, it's really dieing. I haven't looked in the error log before, because I though, it's just this connection that got lost. This is the error-log output: mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=402653184 read_buffer_size=2093056 max_used_connections=24 max_connections=1000 threads_connected=2 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 290904 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=0xa3500490 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Cannot determine thread, fp=0xbe5fe748, backtrace may not be correct. Stack range sanity check OK, backtrace follows: 0x80b2589 0x82cb328 0x808f73b 0x808ce7e 0x80db3d5 0x80ca97b 0x80c45e1 0x80c4226 0x80c3a1d 0x82c6c21 0x82f916a New value of fp=(nil) failed sanity check, terminating stack trace! Please read http://dev.mysql.com/doc/mysql/en/Using_stack_trace.html and follow instructions on how to resolve the stack trace. Resolved stack trace is much more helpful in diagnosing the problem, so please do resolve it Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd-query at 0xa68e960 = purge master logs before (select adddate(current_timestamp(), interval -4 day)) thd-thread_id=106601 The manual page at http://www.mysql.com/doc/en/Crashing.html contains information that should help you find out what is causing the crash. Number of processes running now: 0 050428 21:44:17 mysqld restarted 050428 21:44:17 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... 050428 21:44:18 InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 0 1226476. InnoDB: Doing recovery: scanned up to log sequence number 0 1226476 InnoDB: Last MySQL binlog file position 0 79, file name ./vm23-bin.000102 050428 21:44:18 InnoDB: Flushing modified pages from the buffer pool... 050428 21:44:18 InnoDB: Started; log sequence number 0 1226476 /usr/mysql/mysql-4.1.11/libexec/mysqld: ready for connections. Version: '4.1.11-log' socket: '/tmp/mysql.sock' port: 3306 Source distribution Regards Marten -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ORDERing/GROUPing or both??
If I have a table with fields ID, NAME, VALUE and they may be populated like 1, SHAWN, APPLE 2, TOM, BANANA 3, SHAWN, BANANA 4, JACK, GRAPES 5, TOM, APPLE 6, SHAWN, GRAPES and I want to query all records showing only the most recent records for each person Any thoughts? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Question from a new user:MySQL vs MS SQLserver merges
I am new to MySQL. Please excuse my ignorance if this question has been previously discussed. I was not able to find an answer to my question by searching the archives. I have MySQL installed on a dedicated AMD-64 computer with the MS XP Pro operating system. MS-SQLServer is installed on a 32-bit machine with a slower clock speed. I am running the free binary distribution of MySQL for testing purposes and have been generally impressed with MySQL's comparative performance in all but one area. I am finding that both inner and outer merges take substantially longer with MySQL than with the SQLServer using the same basic code. In one application a left outer merge between a file with 600,000 records and a file with 4,500,000 records took about 10-15 minutes with SQLServer and 11 hours with MySQL. I am repeatedly having similar experiences with both inner and outer merges. Is there any way I can try to improve MySQL's performance with respect to merges? Needless to say, unless I can improve MySQL's performance, I will not be converting to MySQL at this time. Thanks
Date validation using mysql
Is it possible to do a date field validation using an sql query. Its like we have an html field- its a free form text field and the end user should type in a valid date, of course in a predefined format only (MM/dd/yyy). I tried using javascript but either it works on some browsers and not in others or the user can just disable javascript in eth browser. SO I was hoping that I can use an sql query (some function in mysql) which will return true or false based on whether the date is in the future, valid (considering leap years etc..) and in the predefined format. Is this possible - is it a viable thing to do - all I want is a foolproof way of validating the date input field. -- Thanks and best regards, Anoop
Re: ORDERing/GROUPing or both??
How do you determine most recent? Is it based on the highest ID number for a person or some datetime value you didn't show us? This is another form of the groupwise maximum problem described here: http://dev.mysql.com/doc/mysql/en/example-maximum-column-group-row.html If after reading the docs, you still can't make it work. Come back to the list with your attempt and tell us what it did and didn't provide for you. Someone will be more than happy to help. 'Shawn Green Database Administrator Unimin Corporation - Spruce Pine Cummings, Shawn (GNAPs) [EMAIL PROTECTED] wrote on 04/29/2005 10:52:34 AM: If I have a table with fields ID, NAME, VALUE and they may be populated like 1, SHAWN, APPLE 2, TOM, BANANA 3, SHAWN, BANANA 4, JACK, GRAPES 5, TOM, APPLE 6, SHAWN, GRAPES and I want to query all records showing only the most recent records for each person Any thoughts? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date validation using mysql
Anoop kumar V [EMAIL PROTECTED] wrote on 04/29/2005 11:00:11 AM: Is it possible to do a date field validation using an sql query. Its like we have an html field- its a free form text field and the end user should type in a valid date, of course in a predefined format only (MM/dd/yyy). I tried using javascript but either it works on some browsers and not in others or the user can just disable javascript in eth browser. SO I was hoping that I can use an sql query (some function in mysql) which will return true or false based on whether the date is in the future, valid (considering leap years etc..) and in the predefined format. Is this possible - is it a viable thing to do - all I want is a foolproof way of validating the date input field. -- Thanks and best regards, Anoop What the user cannot disable and what you have full control over is the page that receives the request from the user (the page that processes the form the user submits). Use that page to validate your user's submission and either kick the error(s) back to the user (possibly allowing them to re-enter the information) or move on to generating the results (assuming everything was OK). It's a wise idea to validate any user-provided information anyway before sending it to the server. That way you have the ability to head off a SQL server error (or SQL injection attack) before it ever happens. It not only keeps your server more responsive (it doesn't have to spend the time tell you it was bad data) but you have greater control over the entire process. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Question from a new user:MySQL vs MS SQLserver merges
jatwood [EMAIL PROTECTED] wrote on 28/04/2005 22:35:45: I am new to MySQL. Please excuse my ignorance if this question has been previously discussed. I was not able to find an answer to my question by searching the archives. I have MySQL installed on a dedicated AMD-64 computer with the MS XP Pro operating system. MS-SQLServer is installed on a 32-bit machine with a slower clock speed. I am running the free binary distribution of MySQL for testing purposes and have been generally impressed with MySQL's comparative performance in all but one area. I am finding that both inner and outer merges take substantially longer with MySQL than with the SQLServer using the same basic code. In one application a left outer merge between a file with 600,000 records and a file with 4,500,000 records took about 10-15 minutes with SQLServer and 11 hours with MySQL. I am repeatedly having similar experiences with both inner and outer merges. Is there any way I can try to improve MySQL's performance with respect to merges? Needless to say, unless I can improve MySQL's performance, I will not be converting to MySQL at this time. You need to post the results of EXPLAIN your select statement together with the structures of your tables, including indexes. This sort of performance dropoff is usually due to inappropriate indexes, and can be solved by adding or changing indexes. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date validation using mysql
So is there a function in mysql that I can call to validate dates?? Or do I need to build it? Thanks, Anoop On 4/29/05, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Anoop kumar V [EMAIL PROTECTED] wrote on 04/29/2005 11:00:11 AM: Is it possible to do a date field validation using an sql query. Its like we have an html field- its a free form text field and the end user should type in a valid date, of course in a predefined format only (MM/dd/yyy). I tried using javascript but either it works on some browsers and not in others or the user can just disable javascript in eth browser. SO I was hoping that I can use an sql query (some function in mysql) which will return true or false based on whether the date is in the future, valid (considering leap years etc..) and in the predefined format. Is this possible - is it a viable thing to do - all I want is a foolproof way of validating the date input field. -- Thanks and best regards, Anoop What the user cannot disable and what you have full control over is the page that receives the request from the user (the page that processes the form the user submits). Use that page to validate your user's submission and either kick the error(s) back to the user (possibly allowing them to re-enter the information) or move on to generating the results (assuming everything was OK). It's a wise idea to validate any user-provided information anyway before sending it to the server. That way you have the ability to head off a SQL server error (or SQL injection attack) before it ever happens. It not only keeps your server more responsive (it doesn't have to spend the time tell you it was bad data) but you have greater control over the entire process. Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- Thanks and best regards, Anoop
Re: Date validation using mysql
You could try checkdate()... chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Can someone explain???? strange query!!
I did two query, can someone explain what happened? First query: mysql select * from customer_billing where first_name=shad; +--+---++-+---+---+- -+---+--+--+--+- -+---++-+-+- -+-++ | customer_num | title | first_name | middle_name | last_name | email | dayphone | evenphone | address1 | Address2 | address3 | address4 | city | state_province | country | CompanyName | pkey | zipcode | bldgID | +--+---++-+---+---+- -+---+--+--+--+- -+---++-+-+- -+-++ | NULL | NULL | NULL | Tempe | Arizona| USA | NULL| D5BC55546AC74547EE497D4F559607DF | 85281 | NULL | +--+---++-+---+---+- -+---+--+--+--+- -+---++-+-+- -+-++ 1 row in set (0.00 sec) It returned 1 row, and the first_name is NULL along with some other fields. How did it find that row when the fields are NULL? Second query: mysql select first_name, customer_num from customer_billing where first_name=shad; ++--+ | first_name | customer_num | ++--+ | Shad | 101706 | ++--+ 1 row in set (0.01 sec) Now these query is the same except for the '*'? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Seeking advice on currency type
Hello, I am seeking to create a table to hold prices for products. I am not sure what type would be best. According to the docs, I have many choices, dec, double, float, etc. Can anyone give me an idea, of which is the proper choice for handling US currency like so: 1500.99 Thanks, Scott K Purcell | Developer | VERTIS | 555 Washington Ave. 4th Floor | St. Louis, MO 63101 | 314.588.0720 Ext:1320 | [EMAIL PROTECTED] | http://www.vertisinc.com Vertis is the premier provider of targeted advertising, media, and marketing services that drive consumers to marketers more effectively. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date validation using mysql
You can, sort of. You can use a type of query like this: select if(now()'2005-04-28', 1, 0); Which will return a 1 or a 0 if the date is greater than the current date/time. But that's a very weak comparison, prone to error, since the date must be in the MySQL readable format. So while you could use MySQL to validate a date, I wouldn't. Use javascript validation only as a user interface convenience. Never rely on in for validating input to your database. Always assume the data you are receiving did not come from something you made. You should use the programming or scripting language (i.e. PHP, Perl) you are using, to validate the data and format it properly. On Apr 29, 2005, at 11:00 AM, Anoop kumar V wrote: Is it possible to do a date field validation using an sql query. Its like we have an html field- its a free form text field and the end user should type in a valid date, of course in a predefined format only (MM/dd/yyy). I tried using javascript but either it works on some browsers and not in others or the user can just disable javascript in eth browser. SO I was hoping that I can use an sql query (some function in mysql) which will return true or false based on whether the date is in the future, valid (considering leap years etc..) and in the predefined format. Is this possible - is it a viable thing to do - all I want is a foolproof way of validating the date input field. -- Thanks and best regards, Anoop -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Can someone explain???? strange query!!
[snip] I did two query, can someone explain what happened? First query: mysql select * from customer_billing where first_name=shad; mysql select first_name, customer_num from customer_billing where first_name=shad; Now these query is the same except for the '*'? [/snip] http://dev.mysql.com/doc/mysql/en/string-syntax.html What happens if you try this... select * from customer_billing where first_name='shad' -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date validation using mysql
Anoop snip You could try checkdate()... /snip Apologies for erroneous advice - I am evidently suffering from list psychosis... Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date validation using mysql
No problem - I followed up and found out that this function is not available. THought probably you were referring to a later version of Mysql.. anyways... We use Java - and maybe I could use that - I was just wondering if I could help reinventing something already there. Thanks so much for the suggestions/ Anoop On 4/29/05, Chris Ramsay [EMAIL PROTECTED] wrote: Anoop snip You could try checkdate()... /snip Apologies for erroneous advice - I am evidently suffering from list psychosis... Chris -- Thanks and best regards, Anoop
Re: Date validation using mysql
Hi Anoop,
In order to validate a date I am using next query:
select date_format(date_sub(date_add('yourdate', interval 1 day), interval
1 day),'%Y%m%d') = date_format('yourdate','%Y%m%d');
It will give you 1 if date is valid.
Best regards,
Mikhail.
- Original Message -
From: Anoop kumar V [EMAIL PROTECTED]
To: Chris Ramsay [EMAIL PROTECTED]; mysql@lists.mysql.com
Sent: Friday, April 29, 2005 6:10 PM
Subject: Re: Date validation using mysql
No problem - I followed up and found out that this function is not
available. THought probably you were referring to a later version of Mysql..
anyways...
We use Java - and maybe I could use that - I was just wondering if I could
help reinventing something already there.
Thanks so much for the suggestions/
Anoop
On 4/29/05, Chris Ramsay [EMAIL PROTECTED] wrote:
Anoop
snip
You could try checkdate()...
/snip
Apologies for erroneous advice - I am evidently suffering from list
psychosis...
Chris
--
Thanks and best regards,
Anoop
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can someone explain???? strange query!!
On Fri, Apr 29, 2005 at 10:46:16AM -0500, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: I did two query, can someone explain what happened? Not with what you included: | customer_num | title | first_name | middle_name | last_name | email | dayphone | evenphone | address1 | Address2 | address3 | address4 | city | state_province | country | CompanyName | pkey | zipcode | bldgID | 19 columns. | NULL | NULL | NULL | Tempe | Arizona| USA | NULL| D5BC55546AC74547EE497D4F559607DF | 85281 | NULL | 10 columns, presumably the line with address and zipcode, not the line with first_name and so forth. How did it find that row when the fields are NULL? I believe it isn't. Do the query with \G instead of ; on the end so that you see what values are in which columns. -Rich -- Rich Lafferty --+--- Ottawa, Ontario, Canada| Save the Pacific Northwest Tree Octopus! http://www.lafferty.ca/|http://zapatopi.net/treeoctopus.html [EMAIL PROTECTED] ---+--- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
create table with foreign key
Hello, I have this simple table of unique items. CREATE TABLE ITEM ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, manufacturer_id varchar(50), ) TYPE=InnoDB; Foreach item, I can have 1 to many assets. So I am trying to use this: CREATE TABLE ITEM_ASSET_REL ( id INT, asset_id VARCHAR(10), FOREIGN KEY(id) references ITEM(id) on DELETE CASCADE ) TYPE=InnoDB; But I get the error 150. Now if I make the 'item_asset_rel' id UNIQUE, then it is happy. But that defeats my purpose of having many asset_id to one item. Can anyone please, please help. Thanks, Scott K Purcell | Developer | VERTIS | 555 Washington Ave. 4th Floor | St. Louis, MO 63101 | 314.588.0720 Ext:1320 | [EMAIL PROTECTED] | http://www.vertisinc.com Vertis is the premier provider of targeted advertising, media, and marketing services that drive consumers to marketers more effectively. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date validation using mysql
Woww - this is great - I think this is exactly what I wanted!!
Thanks a Ton Mikhail. :-)
Anoop
On 4/29/05, Mikhail Entaltsev [EMAIL PROTECTED] wrote:
Hi Anoop,
In order to validate a date I am using next query:
select date_format(date_sub(date_add('yourdate', interval 1 day),
interval
1 day),'%Y%m%d') = date_format('yourdate','%Y%m%d');
It will give you 1 if date is valid.
Best regards,
Mikhail.
- Original Message -
From: Anoop kumar V [EMAIL PROTECTED]
To: Chris Ramsay [EMAIL PROTECTED]; mysql@lists.mysql.com
Sent: Friday, April 29, 2005 6:10 PM
Subject: Re: Date validation using mysql
No problem - I followed up and found out that this function is not
available. THought probably you were referring to a later version of
Mysql..
anyways...
We use Java - and maybe I could use that - I was just wondering if I could
help reinventing something already there.
Thanks so much for the suggestions/
Anoop
On 4/29/05, Chris Ramsay [EMAIL PROTECTED] wrote:
Anoop
snip
You could try checkdate()...
/snip
Apologies for erroneous advice - I am evidently suffering from list
psychosis...
Chris
--
Thanks and best regards,
Anoop
--
Thanks and best regards,
Anoop
RE: Can someone explain???? strange query!! THANKS!!
Ahh okay the column was cut off or something \G shows it! Thanks!! -Original Message- From: Rich Lafferty [mailto:[EMAIL PROTECTED] Sent: Friday, April 29, 2005 10:28 AM To: mysql@lists.mysql.com Subject: Re: Can someone explain strange query!! On Fri, Apr 29, 2005 at 10:46:16AM -0500, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: I did two query, can someone explain what happened? Not with what you included: | customer_num | title | first_name | middle_name | last_name | email | dayphone | evenphone | address1 | Address2 | address3 | address4 | city | state_province | country | CompanyName | pkey | zipcode | bldgID | 19 columns. | NULL | NULL | NULL | Tempe | Arizona| USA | NULL| D5BC55546AC74547EE497D4F559607DF | 85281 | NULL | 10 columns, presumably the line with address and zipcode, not the line with first_name and so forth. How did it find that row when the fields are NULL? I believe it isn't. Do the query with \G instead of ; on the end so that you see what values are in which columns. -Rich -- Rich Lafferty --+--- Ottawa, Ontario, Canada| Save the Pacific Northwest Tree Octopus! http://www.lafferty.ca/|http://zapatopi.net/treeoctopus.html [EMAIL PROTECTED] ---+--- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: create table with foreign key
Re-read the section on foreign keys (http://dev.mysql.com/doc/mysql/en/innodb-foreign-key-constraints.html). It clearly says (in the second and third bullets) that you must index the columns on both ends of a FK. Add an index to ITEM_ASSET_REL that contains id as its first term then you will be able to setup the FK relationship. If you are doing it from scratch, here is what the CREATE TABLE would look like: CREATE TABLE ITEM_ASSET_REL ( id INT, asset_id VARCHAR(10), KEY(id), FOREIGN KEY(id) references ITEM(id) on DELETE CASCADE ) TYPE=InnoDB; And, if you have another table of assets (called ASSET) and this table is part of a many-to-many relationship (because each asset can be assigned to more than one item at a time): CREATE TABLE ITEM_ASSET_REL ( id INT, asset_id VARCHAR(10), KEY(id), KEY(asset_id), FOREIGN KEY(asset_id) references ASSET(id) on DELETE CASCADE FOREIGN KEY(id) references ITEM(id) on DELETE CASCADE ) TYPE=InnoDB; Make sense? Shawn Green Database Administrator Unimin Corporation - Spruce Pine Scott Purcell [EMAIL PROTECTED] wrote on 04/29/2005 12:32:56 PM: Hello, I have this simple table of unique items. CREATE TABLE ITEM ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, manufacturer_id varchar(50), ) TYPE=InnoDB; Foreach item, I can have 1 to many assets. So I am trying to use this: CREATE TABLE ITEM_ASSET_REL ( id INT, asset_id VARCHAR(10), FOREIGN KEY(id) references ITEM(id) on DELETE CASCADE ) TYPE=InnoDB; But I get the error 150. Now if I make the 'item_asset_rel' id UNIQUE, then it is happy. But that defeats my purpose of having many asset_id to one item. Can anyone please, please help. Thanks, Scott K Purcell | Developer | VERTIS | 555 Washington Ave. 4th Floor | St. Louis, MO 63101 | 314.588.0720 Ext:1320 | [EMAIL PROTECTED] | http://www.vertisinc.com Vertis is the premier provider of targeted advertising, media, and marketing services that drive consumers to marketers more effectively. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: create table with foreign key
Hi, I think it's because you added a comma after the second line in the create table item. So this table was not created. manufacturer_id varchar(50), must be manufacturer_id varchar(50)) When done, i had no problem to create my FK (4.1.11) Mathias Selon Scott Purcell [EMAIL PROTECTED]: Hello, I have this simple table of unique items. CREATE TABLE ITEM ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, manufacturer_id varchar(50), ) TYPE=InnoDB; Foreach item, I can have 1 to many assets. So I am trying to use this: CREATE TABLE ITEM_ASSET_REL ( id INT, asset_id VARCHAR(10), FOREIGN KEY(id) references ITEM(id) on DELETE CASCADE ) TYPE=InnoDB; But I get the error 150. Now if I make the 'item_asset_rel' id UNIQUE, then it is happy. But that defeats my purpose of having many asset_id to one item. Can anyone please, please help. Thanks, Scott K Purcell | Developer | VERTIS | 555 Washington Ave. 4th Floor | St. Louis, MO 63101 | 314.588.0720 Ext:1320 | [EMAIL PROTECTED] | http://www.vertisinc.com Vertis is the premier provider of targeted advertising, media, and marketing services that drive consumers to marketers more effectively. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question from a new user:MySQL vs MS SQLserver merges
It sounds like you either have incorrect indexes or you didn't adjust the MySQL configuration variables (i.e. key_buffer, join_buffer_size, read_buffer_size) appropriately for your data set size and what your query is doing (i.e. full table scan). SHOW STATUS can help you figure out what needs to be adjusted, like Key_reads vs. Key_read_requests ratio. On Apr 28, 2005, at 5:35 PM, jatwood wrote: I am new to MySQL. Please excuse my ignorance if this question has been previously discussed. I was not able to find an answer to my question by searching the archives. I have MySQL installed on a dedicated AMD-64 computer with the MS XP Pro operating system. MS-SQLServer is installed on a 32-bit machine with a slower clock speed. I am running the free binary distribution of MySQL for testing purposes and have been generally impressed with MySQL's comparative performance in all but one area. I am finding that both inner and outer merges take substantially longer with MySQL than with the SQLServer using the same basic code. In one application a left outer merge between a file with 600,000 records and a file with 4,500,000 records took about 10-15 minutes with SQLServer and 11 hours with MySQL. I am repeatedly having similar experiences with both inner and outer merges. Is there any way I can try to improve MySQL's performance with respect to merges? Needless to say, unless I can improve MySQL's performance, I will not be converting to MySQL at this time. Thanks -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question from a new user:MySQL vs MS SQLserver merges
Hi, If you're in a transactionnal env., and have a test env. of mysql with myisam tables, and want to compare, you will not have what you want. Myisam tables have only table locking. sql server 2000 and Yukon have row locking mecanism. So you have to have innodb storage type. Also, if your selects must sort data and do it on disk because the max size of memory tmp table is not enough, you will have very slow queries. Look at mysql parameters, change your table storage to innodb, try to have the same memory requirements for the two databases, and then compare. You can begin by the scripts in the sql-bench directory under mysql installation, play them on sql-server and mysql. When you can compare, do it with your own scripts. And then you will know ! Mathias Selon [EMAIL PROTECTED]: jatwood [EMAIL PROTECTED] wrote on 28/04/2005 22:35:45: I am new to MySQL. Please excuse my ignorance if this question has been previously discussed. I was not able to find an answer to my question by searching the archives. I have MySQL installed on a dedicated AMD-64 computer with the MS XP Pro operating system. MS-SQLServer is installed on a 32-bit machine with a slower clock speed. I am running the free binary distribution of MySQL for testing purposes and have been generally impressed with MySQL's comparative performance in all but one area. I am finding that both inner and outer merges take substantially longer with MySQL than with the SQLServer using the same basic code. In one application a left outer merge between a file with 600,000 records and a file with 4,500,000 records took about 10-15 minutes with SQLServer and 11 hours with MySQL. I am repeatedly having similar experiences with both inner and outer merges. Is there any way I can try to improve MySQL's performance with respect to merges? Needless to say, unless I can improve MySQL's performance, I will not be converting to MySQL at this time. You need to post the results of EXPLAIN your select statement together with the structures of your tables, including indexes. This sort of performance dropoff is usually due to inappropriate indexes, and can be solved by adding or changing indexes. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Need help w/ re-starting Replication
I set up replication and it was working fine but I re-created my master database (droped and created empty tables) and now my replication doesn't work. I checked the status of master and slave and they think they are in sync but when I add a new record to master I don't see it in slave. It used to work fine. Is there anyway to clean the binary log files and positions master and slave use and start the sync process again? Thanks, -Jalil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
syntax errors with ENGINE=MYISAM... and NOT NULL default CURRENT_TIMESTAMP ...
running: mysql Ver 12.22 Distrib 4.0.23, for Win95/Win98(i32) downloaded from www.devside.net/web/servers/free/download. When I run a script with the following commands I get sql syntax errors: Create table 'test' ( 'helper' varchar(22) ) ENGINE=MYISAM DEFAULT CHARSET=latin1; I get a sql error telling me to check the syntax for this version. I modified the line to ENGINE=MYISAM CHARSET=latin1; no sql errors. Next snytax error I get is: Create table 'testime' ( 'teatime' timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, 'val' char(22) NOT Null); I modified the line to Create table 'testime' ( 'teatime' timestamp NOT NULL default 'CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP', 'val' char(22) NOT Null); no sql errors. Question 1: How do I check my syntax against the version and distribution that I am running? Question 2: I thought on update was a reference definition and required a reference to a parent table and I did think CURRENT_STAMP was a reference action? Thank you, Raymond
Re: Seeking advice on currency type
Scott Purcell wrote: I am seeking to create a table to hold prices for products. I am not sure what type would be best. According to the docs, I have many choices, dec, double, float, etc. Can anyone give me an idea, of which is the proper choice for handling US currency like so: 1500.99 I can't imagine any reason to use a type other than DECIMAL for a currency value. YMMV! -- Hassan Schroeder - [EMAIL PROTECTED] Webtuitive Design === (+1) 408-938-0567 === http://webtuitive.com dream. code. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqld just ending...
Found the problem. For some reason, error messages where being sent to hohup.out, once I found that I discovered I was using an option in my.cnf that is not supported on 3.23 (I use 4.1 on my other server). All is well now. Thanks for the help Ron On Apr 29, 2005, at 6:47 AM, Gleb Paharenko wrote: Hello. Check that you don't have problems with filesystem. Start mysqld directly, specify the corresponding variables in the command line. If it doesn't produce errors switch to the debug version of the server and use the trace files to find where the problem is. Ron Gilbert [EMAIL PROTECTED] wrote: After almost a year of being up, my RH9 server lost power and rebooted. When it came back up, mysql was not running. I went in and ran safe_mysql to start it back up (I also tried the init.d script) but it failed to start. No errors, nothing in the log. After some investigation, it appears that the .pid file is never being created, so the safe_mysql ends immediately. The only entry in the log or error files in the a notice that mysqld ended, and that's it. The only clue I have is several month ago I was playing around with the mysql user and gave it a password. I can't imagine how that would cause a problem, but it's the only thing I can think of. If this is the problem, I'm not sure how to get the mysql user back to the default state. Is the problem the mysql user? All the permission for all the database directories are set correctly. I'm am puzzled that mysqld just ends, not messages. Any clues? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: IN giving me a fit
Scott Purcell wrote: I am in the docs trying to use the IN (13.1.8.3. Subqueries with ANY, IN, and SOME). Version: mysql Ver12.21 distrib 4.0.15 Win95/Win98(i32) Look at http://dev.mysql.com/doc/mysql/en/subqueries.html Subqueries aren't supported until 4.1. -- Keith Ivey [EMAIL PROTECTED] Smokefree DC http://www.smokefreedc.org Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Pessimistic Record Locking
Hello, I'm using MySQL 5.0.3 Beta, and I'm hoping for some general tips/experience you guys may have had regarding optomistic vs pessimistic locking in a web app (PHP 5.0.3) I'm more of a windows programmer, and I've always implemented pessimistic over optomistic as it's much more professional and attractive to the end user. The problem as you know, is that web development makes pessimistic locking much more difficult, because of the user closing the browser, and a bunch of other factors I can't control. Question: which type of locking do you usually implement in your web apps, and do you do it at a DB level or in your application layer? Any thoughts on a custom locking scheme (ie, a lock table that is written to with a user id and record id and timestamp)? Other solutions/suggestions are greatly appreciated. Thanks in advance. Scott. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Pessimistic Record Locking
Hi, Try to have optimistic locking in the database server (row level locking a.k.a. innodb storage), et let your transactions managed by the server. Any line of code like lock table will generate a very bad web application performances. You can add connection pooling if you want to manage total number of users. Best Regards Mathias FATENE Hope that helps *This not an official mysql support answer -Original Message- From: Scott Klarenbach [mailto:[EMAIL PROTECTED] Sent: vendredi 29 avril 2005 20:28 To: My SQL Subject: Pessimistic Record Locking Hello, I'm using MySQL 5.0.3 Beta, and I'm hoping for some general tips/experience you guys may have had regarding optomistic vs pessimistic locking in a web app (PHP 5.0.3) I'm more of a windows programmer, and I've always implemented pessimistic over optomistic as it's much more professional and attractive to the end user. The problem as you know, is that web development makes pessimistic locking much more difficult, because of the user closing the browser, and a bunch of other factors I can't control. Question: which type of locking do you usually implement in your web apps, and do you do it at a DB level or in your application layer? Any thoughts on a custom locking scheme (ie, a lock table that is written to with a user id and record id and timestamp)? Other solutions/suggestions are greatly appreciated. Thanks in advance. Scott. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: IN giving me a fit
Hi, This is just what you're looking for : select item.id, item.name from item,item_cat_rel where item.id =item_cat_rel.id and item_cat_rel.cat_id = 5; +--+--+ | id | name | +--+--+ | 5000 | Triple Cage Hook | +--+--+ 1 row in set (0.01 sec) Joisn are better than subqueries. Best Regards Mathias FATENE Hope that helps *This not an official mysql support answer -Original Message- From: Scott Purcell [mailto:[EMAIL PROTECTED] Sent: vendredi 29 avril 2005 20:14 To: mysql@lists.mysql.com Subject: IN giving me a fit Hello, I am in the docs trying to use the IN (13.1.8.3. Subqueries with ANY, IN, and SOME). Version: mysql Ver12.21 distrib 4.0.15 Win95/Win98(i32) I have two tables that are InnoDB types and I am trying to do simple IN but it argues with my syntax. mysql select id, name - from item where id IN (select id from item_cat_rel where cat_id = 5); ERROR 1064: You have an error in your SQL syntax. Check the manual that corresp onds to your MySQL server version for the right syntax to use near 'select id fr om item_cat_rel where cat_id = 5)' at line 2 mysql Why? Here are the tables. CREATE TABLE ITEM ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, manufacturer_id varchar(50), name varchar(255), ) TYPE=InnoDB; insert into ITEM (id, manufacturer_id, name, description, short_desc, height, width, diameter, pounds, price, discount, quantity) values (5000, '9.90151', 'Triple Cage Hook', 'Solid wrought iron, is sculpted by hand into twisted cage hooks to hold your coats, bathrobes, towels and hats.', 'Triple Cage Hook', 9, 18.5, 4.5, 6, 35.00, 5, 1); CREATE TABLE ITEM_CAT_REL ( id INT, cat_id INT NOT NULL, key(id), FOREIGN KEY (id) references ITEM(id) on DELETE CASCADE ) TYPE=InnoDB; INSERT INTO ITEM_CAT_REL (id, cat_id) values (5000, 5); INSERT INTO ITEM_CAT_REL (id, cat_id) values (5000, 6); Scott K Purcell | Developer | VERTIS | 555 Washington Ave. 4th Floor | St. Louis, MO 63101 | 314.588.0720 Ext:1320 | [EMAIL PROTECTED] | http://www.vertisinc.com Vertis is the premier provider of targeted advertising, media, and marketing services that drive consumers to marketers more effectively. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: IN giving me a fit
Your problem is that you are trying to do an 'IN' subquery in MySQL V4.0.x. Subqueries don't become available in MySQL until V4.1.x. You'll need to upgrade to V4.1 (or V5.0) to get that functionality. Rhino - Original Message - From: Scott Purcell [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, April 29, 2005 2:14 PM Subject: IN giving me a fit Hello, I am in the docs trying to use the IN (13.1.8.3. Subqueries with ANY, IN, and SOME). Version: mysql Ver12.21 distrib 4.0.15 Win95/Win98(i32) I have two tables that are InnoDB types and I am trying to do simple IN but it argues with my syntax. mysql select id, name - from item where id IN (select id from item_cat_rel where cat_id = 5); ERROR 1064: You have an error in your SQL syntax. Check the manual that corresp onds to your MySQL server version for the right syntax to use near 'select id fr om item_cat_rel where cat_id = 5)' at line 2 mysql Why? Here are the tables. CREATE TABLE ITEM ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, manufacturer_id varchar(50), name varchar(255), ) TYPE=InnoDB; insert into ITEM (id, manufacturer_id, name, description, short_desc, height, width, diameter, pounds, price, discount, quantity) values (5000, '9.90151', 'Triple Cage Hook', 'Solid wrought iron, is sculpted by hand into twisted cage hooks to hold your coats, bathrobes, towels and hats.', 'Triple Cage Hook', 9, 18.5, 4.5, 6, 35.00, 5, 1); CREATE TABLE ITEM_CAT_REL ( id INT, cat_id INT NOT NULL, key(id), FOREIGN KEY (id) references ITEM(id) on DELETE CASCADE ) TYPE=InnoDB; INSERT INTO ITEM_CAT_REL (id, cat_id) values (5000, 5); INSERT INTO ITEM_CAT_REL (id, cat_id) values (5000, 6); Scott K Purcell | Developer | VERTIS | 555 Washington Ave. 4th Floor | St. Louis, MO 63101 | 314.588.0720 Ext:1320 | [EMAIL PROTECTED] | http://www.vertisinc.com Vertis is the premier provider of targeted advertising, media, and marketing services that drive consumers to marketers more effectively. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.11.0 - Release Date: 29/04/2005 -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.11.0 - Release Date: 29/04/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Pessimistic Record Locking
Scott Klarenbach [EMAIL PROTECTED] wrote on 04/29/2005 02:28:25 PM: Hello, I'm using MySQL 5.0.3 Beta, and I'm hoping for some general tips/experience you guys may have had regarding optomistic vs pessimistic locking in a web app (PHP 5.0.3) I'm more of a windows programmer, and I've always implemented pessimistic over optomistic as it's much more professional and attractive to the end user. The problem as you know, is that web development makes pessimistic locking much more difficult, because of the user closing the browser, and a bunch of other factors I can't control. Question: which type of locking do you usually implement in your web apps, and do you do it at a DB level or in your application layer? Any thoughts on a custom locking scheme (ie, a lock table that is written to with a user id and record id and timestamp)? Other solutions/suggestions are greatly appreciated. Thanks in advance. Scott. I don't use record locking until it's time to actually do something to the data. Even then it depends on what I am doing. As Mathias already suggested, InnoDB works better for this because it can lock individual rows and has full transactional support. In my webapp the users do mostly reads (which do not require locks) and few edits. In order to prevent another user from editing the same record that someone else is already editing, I have added a field to those table that require concurrency checking and fill in that field with the application-login of the user requesting to edit the record. It looks something like this: UPDATE datatable SET mtxEditor = 'user_id' WHERE pkid = AND mtxEditor is null; SELECT mtxEditor FROM datatable WHERE pkid = ; If I get a match, then I allow the user to navigate to the edit web page, otherwise they get the view web page and a popup saying that the record is already being edited by insert name here. That way I don't have 2 users trying to make concurrent changes and the second or later users are told who has that record open so they can check with that person to see if they are done or if they just forgot to save their changes. Now, if the user exits the page manually (the page gets the onunload event) or decides to cancel their edit, I request a page that cleares the mtxEditor field. That works something like this: UPDATE datatable SET mtxEditor = null WHERE pkid = AND mtxEditor ='userid'; When it comes time to apply the effects of the edit, I check the mtxEditor field one more time to make sure that nobody has hijacked the page or that the user didn't navigate away (causing their edit lock to go away) and come back (a user can't update the record unless they are the one editing it). It's not perfect but it works remarkably well for the application-level locking I need to provide. One enhancement to this would be to provide a sunset timer. When a user is assigned as the editor set a datetimefield to 10 or 20 minutes from NOW(). If the user hasn't submitted their updates by then, they have to re-request to edit the page. That way, in case someone manages to leave the page without tripping the onunload event (which would trigger the reset of the mtxEditor field) you still have a way of unlocking the record for the next user. BTW, I use Hungarian notation only if a field is used more like a variable than the other data-related fields. In this case the 'mtx' is my shorthand for 'mutex' because that field is acting as a mutual exclusion flag to be read by the application. The name of the application user editing a record adds no useful value to the rest of the data stored on the table (if I listed the properties of the object being stored in the table, the editing user isn't one of them. Am I making sense?). It's generally a BAD idea to lock any rows for longer than what is absolutely necessary to complete the transaction. That means you should not lock a row (at the database level) and wait for a user to update it before you release the lock. To do that completely ruins your database concurrency. Move that kind of lock into the application layer and manage your user-vs-user concurrency from there. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Clarification on innodb status message
Does anyone know what the various lock modes mean in the show innodb status reports? We are investigating a dead-lock issue, and see lock_mode x and lock_mode s. We have transaction that locks a record in mode x, and is appears to be waiting for a lock on the same record in mode s - by the same transaction. Any info would be helpful. Thanks, Scott AMi Entertainment -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
tables locked for no apparent reason
I'm running MySQL 4.0.14 and it's locking tables for everything including counting records and select statements. I don't use any lock statements. Has anyone else encountered this? Any suggestions? Thanks, Jeff __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Re: Pessimistic Record Locking
Thanks Shawn, that helps a lot. I like your general idea of handling it at application level. I guess my main concern w/ web apps and locking the record (even at app level) is orphaned locks, resulting from browser closes or other events that don't go through the normal channels of updating or cancelling the update. So, if you set a lock to have a timeout of say 15 minutes, and the user WAS simply taking longer than that 15 minutes, how do you handle that when they try and save it? I was thinking, just go ahead with the commit if the record hasn't been locked in the meantime by another user, otherwise, prompt them saying it's now been locked by another user and your changes won't be saved. But this is the same user inconvenience caused by optomistic locking. Thanks, Scott. On 4/29/05, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Scott Klarenbach [EMAIL PROTECTED] wrote on 04/29/2005 02:28:25 PM: Hello, I'm using MySQL 5.0.3 Beta, and I'm hoping for some general tips/experience you guys may have had regarding optomistic vs pessimistic locking in a web app (PHP 5.0.3) I'm more of a windows programmer, and I've always implemented pessimistic over optomistic as it's much more professional and attractive to the end user. The problem as you know, is that web development makes pessimistic locking much more difficult, because of the user closing the browser, and a bunch of other factors I can't control. Question: which type of locking do you usually implement in your web apps, and do you do it at a DB level or in your application layer? Any thoughts on a custom locking scheme (ie, a lock table that is written to with a user id and record id and timestamp)? Other solutions/suggestions are greatly appreciated. Thanks in advance. Scott. I don't use record locking until it's time to actually do something to the data. Even then it depends on what I am doing. As Mathias already suggested, InnoDB works better for this because it can lock individual rows and has full transactional support. In my webapp the users do mostly reads (which do not require locks) and few edits. In order to prevent another user from editing the same record that someone else is already editing, I have added a field to those table that require concurrency checking and fill in that field with the application-login of the user requesting to edit the record. It looks something like this: UPDATE datatable SET mtxEditor = 'user_id' WHERE pkid = AND mtxEditor is null; SELECT mtxEditor FROM datatable WHERE pkid = ; If I get a match, then I allow the user to navigate to the edit web page, otherwise they get the view web page and a popup saying that the record is already being edited by insert name here. That way I don't have 2 users trying to make concurrent changes and the second or later users are told who has that record open so they can check with that person to see if they are done or if they just forgot to save their changes. Now, if the user exits the page manually (the page gets the onunload event) or decides to cancel their edit, I request a page that cleares the mtxEditor field. That works something like this: UPDATE datatable SET mtxEditor = null WHERE pkid = AND mtxEditor ='userid'; When it comes time to apply the effects of the edit, I check the mtxEditor field one more time to make sure that nobody has hijacked the page or that the user didn't navigate away (causing their edit lock to go away) and come back (a user can't update the record unless they are the one editing it). It's not perfect but it works remarkably well for the application-level locking I need to provide. One enhancement to this would be to provide a sunset timer. When a user is assigned as the editor set a datetimefield to 10 or 20 minutes from NOW(). If the user hasn't submitted their updates by then, they have to re-request to edit the page. That way, in case someone manages to leave the page without tripping the onunload event (which would trigger the reset of the mtxEditor field) you still have a way of unlocking the record for the next user. BTW, I use Hungarian notation only if a field is used more like a variable than the other data-related fields. In this case the 'mtx' is my shorthand for 'mutex' because that field is acting as a mutual exclusion flag to be read by the application. The name of the application user editing a record adds no useful value to the rest of the data stored on the table (if I listed the properties of the object being stored in the table, the editing user isn't one of them. Am I making sense?). It's generally a BAD idea to lock any rows for longer than what is absolutely necessary to complete the transaction. That means you should not lock a row (at the database level) and wait for a user to update it before you release the lock. To do that completely ruins
Sum of a Count
Is there a way to group a field together with COUNT() and run a SUM() or AVG() on that? I've looked all over and I can't see a way to combine them, but maybe there is a trickier way? Eric Jensen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Seeking advice on currency type
In article [EMAIL PROTECTED], Hassan Schroeder [EMAIL PROTECTED] writes: Scott Purcell wrote: I am seeking to create a table to hold prices for products. I am not sure what type would be best. According to the docs, I have many choices, dec, double, float, etc. Can anyone give me an idea, of which is the proper choice for handling US currency like so: 1500.99 I can't imagine any reason to use a type other than DECIMAL for a currency value. A reason could be performance. Storing cent values in an INT field is more efficient. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ONLY_FULL_GROUP_BY too strict on MySQL 4.1.11
I'm using MySQL 4.1.11 with sql-mode=ONLY_FULL_GROUP_BY set in my.cnf. This disallows things like SELECT col1, col2, sum(col1) FROM tbl GROUP BY col1 as it should, and it allows SELECT col1, sum(col1) FROM tbl GROUP BY col1 but it also disallows SELECT col1, sum(col1) + 1 FROM tbl GROUP BY col1 which is perfectly legal SQL AFAIK. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Pessimistic Record Locking
I could set the user's session timeout to be the same duration as the record lock timeout...that way, in any event where the user's lock would have expired, he would have to log back into the system anyway...but this may be inconvenient as well, as I know a lot of user's could be idle for some time, and would be annoyed if they had to log back in every time... On 4/29/05, Scott Klarenbach [EMAIL PROTECTED] wrote: Thanks Shawn, that helps a lot. I like your general idea of handling it at application level. I guess my main concern w/ web apps and locking the record (even at app level) is orphaned locks, resulting from browser closes or other events that don't go through the normal channels of updating or cancelling the update. So, if you set a lock to have a timeout of say 15 minutes, and the user WAS simply taking longer than that 15 minutes, how do you handle that when they try and save it? I was thinking, just go ahead with the commit if the record hasn't been locked in the meantime by another user, otherwise, prompt them saying it's now been locked by another user and your changes won't be saved. But this is the same user inconvenience caused by optomistic locking. Thanks, Scott. On 4/29/05, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Scott Klarenbach [EMAIL PROTECTED] wrote on 04/29/2005 02:28:25 PM: Hello, I'm using MySQL 5.0.3 Beta, and I'm hoping for some general tips/experience you guys may have had regarding optomistic vs pessimistic locking in a web app (PHP 5.0.3) I'm more of a windows programmer, and I've always implemented pessimistic over optomistic as it's much more professional and attractive to the end user. The problem as you know, is that web development makes pessimistic locking much more difficult, because of the user closing the browser, and a bunch of other factors I can't control. Question: which type of locking do you usually implement in your web apps, and do you do it at a DB level or in your application layer? Any thoughts on a custom locking scheme (ie, a lock table that is written to with a user id and record id and timestamp)? Other solutions/suggestions are greatly appreciated. Thanks in advance. Scott. I don't use record locking until it's time to actually do something to the data. Even then it depends on what I am doing. As Mathias already suggested, InnoDB works better for this because it can lock individual rows and has full transactional support. In my webapp the users do mostly reads (which do not require locks) and few edits. In order to prevent another user from editing the same record that someone else is already editing, I have added a field to those table that require concurrency checking and fill in that field with the application-login of the user requesting to edit the record. It looks something like this: UPDATE datatable SET mtxEditor = 'user_id' WHERE pkid = AND mtxEditor is null; SELECT mtxEditor FROM datatable WHERE pkid = ; If I get a match, then I allow the user to navigate to the edit web page, otherwise they get the view web page and a popup saying that the record is already being edited by insert name here. That way I don't have 2 users trying to make concurrent changes and the second or later users are told who has that record open so they can check with that person to see if they are done or if they just forgot to save their changes. Now, if the user exits the page manually (the page gets the onunload event) or decides to cancel their edit, I request a page that cleares the mtxEditor field. That works something like this: UPDATE datatable SET mtxEditor = null WHERE pkid = AND mtxEditor ='userid'; When it comes time to apply the effects of the edit, I check the mtxEditor field one more time to make sure that nobody has hijacked the page or that the user didn't navigate away (causing their edit lock to go away) and come back (a user can't update the record unless they are the one editing it). It's not perfect but it works remarkably well for the application-level locking I need to provide. One enhancement to this would be to provide a sunset timer. When a user is assigned as the editor set a datetimefield to 10 or 20 minutes from NOW(). If the user hasn't submitted their updates by then, they have to re-request to edit the page. That way, in case someone manages to leave the page without tripping the onunload event (which would trigger the reset of the mtxEditor field) you still have a way of unlocking the record for the next user. BTW, I use Hungarian notation only if a field is used more like a variable than the other data-related fields. In this case the 'mtx' is my shorthand for 'mutex' because that field is acting as a mutual exclusion flag to be read by the application. The name of the application user
Is this picayune??
Hi, Normalizing some tables. Does it make much difference to MySQL whether I separate minor, related data into tables for a main account (MemberAccount) such as: MemberAccount then emails, religion, hobbies, education, etc. I'll explain. On the other hand, I could put all the information in one member account by leaving a couple extra fields for each heading: member.email.work, member.email.home, member.email.other member.religion.type, member.religion.name, member.religion.subgroup e.g. Christian, Assemblies of God, charismatic or Jewish, Orthodox, Hasidic etc. OR placing the emails, religion, hobbies, etc in separate tables with linking fields in MemberAccount. How will this affect performance? Will the separate files save on server cache memory? Increase it? For queries, updates, deletes, inserts? Is there a good source for DB design tips pointers for MySQL? Many thanks, Ken Wagner
Re: Seeking advice on currency type
Harald Fuchs wrote: I can't imagine any reason to use a type other than DECIMAL for a currency value. A reason could be performance. Storing cent values in an INT field is more efficient. Are you saying that storing and/or retrieving a DECIMAL value takes appreciably more time than an INTEGER? -- Hassan Schroeder - [EMAIL PROTECTED] Webtuitive Design === (+1) 408-938-0567 === http://webtuitive.com dream. code. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Newbie Q - Re: Copying databases
I am having to operating MySQL 4.0.18, I had heared that I could copy a db folder out of the mysql/data folder to the same folder on a different machine running its own local host server and that server would have a copy of the database. HOWEVER, o some occasions this seemed to work Ok, and on others I could use db, show tables etc, but on select I got a failure (which unfortunately I have not copied sorry) something to do with finding? lt;tablenamegt;.innodb. Now as far as I know both servers were at the same version and both had innodb enabled and I THNK I quit both clients before copying. Is this a valid way to copy a db or only sometimes. Should I really always use mysqldump. Although I am suspecting given the amount of data this may make for a v large sql file? Can some-one advise this newbie, thanks, Andrew H
Re: Seeking advice on currency type
At 15:24 -0700 4/29/05, Hassan Schroeder wrote: Harald Fuchs wrote: I can't imagine any reason to use a type other than DECIMAL for a currency value. A reason could be performance. Storing cent values in an INT field is more efficient. Are you saying that storing and/or retrieving a DECIMAL value takes appreciably more time than an INTEGER? Before MySQL 5, DECIMAL values are stored as strings, so operations are less efficient than for integers. In MySQL 5, the representation of DECIMAL has changed to binary format, so it's more efficient than before. It's probably safe to say that integer is still a bit more efficient, but the difference between integer and DECIMAL will be less than before. Details: http://dev.mysql.com/doc/mysql/en/precision-math-decimal-changes.html -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: ONLY_FULL_GROUP_BY too strict on MySQL 4.1.11
Hi, MySQL is a very fast, multi-threaded, multi-user and robust SQL (Structured Query Language) database server. What's New in This Release: Functionality added or changed: . ONLY_FULL_GROUP_BY no longer is included in the ANSI composite SQL mode. (Bug #8510) . mysqld_safe will create the directory where the UNIX socket file is to be located if the directory does not exist. T... [ read more about MySQL ] http://www.softpedia.com/progDownload/MySQL-for-Windows-Download-2668.ht ml Best Regards Mathias FATENE Hope that helps *This not an official mysql support answer -Original Message- From: news [mailto:[EMAIL PROTECTED] On Behalf Of Harald Fuchs Sent: samedi 30 avril 2005 00:00 To: mysql@lists.mysql.com Subject: ONLY_FULL_GROUP_BY too strict on MySQL 4.1.11 I'm using MySQL 4.1.11 with sql-mode=ONLY_FULL_GROUP_BY set in my.cnf. This disallows things like SELECT col1, col2, sum(col1) FROM tbl GROUP BY col1 as it should, and it allows SELECT col1, sum(col1) FROM tbl GROUP BY col1 but it also disallows SELECT col1, sum(col1) + 1 FROM tbl GROUP BY col1 which is perfectly legal SQL AFAIK. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Seeking advice on currency type
Here are datatypes and sizes. The problem can be storage. http://dev.mysql.com/doc/mysql/en/storage-requirements.html Best Regards Mathias FATENE Hope that helps *This not an official mysql support answer -Original Message- From: Hassan Schroeder [mailto:[EMAIL PROTECTED] Sent: samedi 30 avril 2005 00:25 To: mysql@lists.mysql.com Subject: Re: Seeking advice on currency type Harald Fuchs wrote: I can't imagine any reason to use a type other than DECIMAL for a currency value. A reason could be performance. Storing cent values in an INT field is more efficient. Are you saying that storing and/or retrieving a DECIMAL value takes appreciably more time than an INTEGER? -- Hassan Schroeder - [EMAIL PROTECTED] Webtuitive Design === (+1) 408-938-0567 === http://webtuitive.com dream. code. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Newbie Q - Re: Copying databases
http://dev.mysql.com/doc/mysql/en/backup.html It's better to backup with tools. You will be sure that tables are FULL-locked. Best Regards Mathias FATENE Hope that helps *This not an official mysql support answer -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: samedi 30 avril 2005 00:30 To: mysql@lists.mysql.com Subject: Newbie Q - Re: Copying databases I am having to operating MySQL 4.0.18, I had heared that I could copy a db folder out of the mysql/data folder to the same folder on a different machine running its own local host server and that server would have a copy of the database. HOWEVER, o some occasions this seemed to work Ok, and on others I could use db, show tables etc, but on select I got a failure (which unfortunately I have not copied sorry) something to do with finding? lt;tablenamegt;.innodb. Now as far as I know both servers were at the same version and both had innodb enabled and I THNK I quit both clients before copying. Is this a valid way to copy a db or only sometimes. Should I really always use mysqldump. Although I am suspecting given the amount of data this may make for a v large sql file? Can some-one advise this newbie, thanks, Andrew H -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sum of a Count
I'm not entirely sure I know what you're asking when you say that you want to group a field together with COUNT(). If you mean that you want to be able to count() and sum() or avg() the groups, then the answer is yes. For example, let's say that you have an Employee table with one row per employee. Each row also contains a department number, indicating the department for which the employee works. In that case: select deptno, count(*), sum(salary), avg(bonus) from Employee group by deptno will return one row for each department number that exists in the table. Each result set row will contain a department number, the number of employees who belong to that department, the total salary paid out to all employees in that department, and the average bonus paid out to all employees in that department. If this isn't what you wanted, please clarify the question and someone else will probably try to answer the clarified question. Rhino - Original Message - From: Eric Jensen [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, April 29, 2005 5:38 PM Subject: Sum of a Count Is there a way to group a field together with COUNT() and run a SUM() or AVG() on that? I've looked all over and I can't see a way to combine them, but maybe there is a trickier way? Eric Jensen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.11.0 - Release Date: 29/04/2005 -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.11.0 - Release Date: 29/04/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Seeking advice on currency type
Paul DuBois wrote: Before MySQL 5, DECIMAL values are stored as strings, so operations are less efficient than for integers. In MySQL 5, the representation of DECIMAL has changed to binary format, so it's more efficient than before. It's probably safe to say that integer is still a bit more efficient, but the difference between integer and DECIMAL will be less than before. Are you referring to operations in the sense of comparing two values in a SQL statement? versus simply retrieving or inserting a value? Either way, do you have a feel (or access to any studies) for the impact of using DECIMAL instead of INTEGER for currency? I'd just wonder if it's enough to make up for all the required conversions going on in the app layer :-) -- Hassan Schroeder - [EMAIL PROTECTED] Webtuitive Design === (+1) 408-938-0567 === http://webtuitive.com dream. code. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Newbie Q - Re: Copying databases
Yes I had thought I should use tools to copy, but I have had problems with mysqldump. I successfully created a dump file, but when I execute this file on an empty database I got: C:\xampp\xampp\mysql\bingt;mysql -uroot db2 lt; test1.txtERROR 1064 at line 54: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'on date NOT NULL default '-00-00', PRIMARY KEY (staff,shrefering I presume to CREATE TABLE shift ( staff char(20) NOT NULL default '', shift char(1) NOT NULL default '', client char(20) default NULL, role char(1) default NULL, on date NOT NULL default '-00-00', PRIMARY KEY (staff,shift,on),... etc Could it be that the version of mysqldump that has been installed is not in step with the server or are there some issues with SQL generated by mysqldump. I have MySQL 4.0.18 and the mysqldump is : mysqldump Ver 9.10 Distrib 4.0.17, for Win95/Win98 (i32)By Igor Romanenko, Monty, Jani Sinisa This is not an installation I have control over. Soryy to be a pain, any ideas? Andrew H
Re: Need help w/ re-starting Replication
On Fri, 29 Apr 2005 [EMAIL PROTECTED] wrote: I set up replication and it was working fine but I re-created my master database (droped and created empty tables) and now my replication doesn't work. I checked the status of master and slave and they think they are in sync but when I add a new record to master I don't see it in slave. It used to work fine. Is there anyway to clean the binary log files and positions master and slave use and start the sync process again? Have you checked the slave to see if it dropped and created the table? Does 'SHOW SLAVE STATUS\G' show any errors? Atle - Flying Crocodile Inc, Unix Systems Administrator -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MyISAM and foreign key constraints?
Hi, MySQL docs claim at: http://dev.mysql.com/doc/mysql/en/ansi-diff-foreign-keys.html that At a later stage, foreign key constraints will be implemented for MyISAM tables as well. Does anyone know what the timescale is? Thanks, Jacek -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqldump generates invalid code
I backup a db using mysqldump, but when I come to execute the sql file on an empty db I get this C:\xampp\xampp\mysql\bingt;mysql -uroot db2 lt; test3.txtERROR 1005 at line 54: Can't create table '.\db2\shift.frm' (errno: 150) C:\xampp\xampp\mysql\bingt; I have tried to fix the foreign key constraint (error 150), but to no avail, I cannot see what is wrong with it nd anyway if I created this in the same mysql, then why doesn't it generate code that is valid? Here's the create that it fails on: CREATE TABLE shift ( staff char(20) NOT NULL default '', shift char(1) NOT NULL default '', client char(20) default NULL, role char(1) default NULL, on date NOT NULL default '-00-00', PRIMARY KEY (staff,shift,on), KEY staff (staff,role), KEY shift (shift), KEY client (client), KEY role (role), CONSTRAINT `shift_ibfk_1` FOREIGN KEY (`staff`, `role`) REFERENCES `staffrole` (`uniquename`, `role`), CONSTRAINT `shift_ibfk_2` FOREIGN KEY (`shift`) REFERENCES `shifttype` (`shift`), CONSTRAINT `shift_ibfk_3` FOREIGN KEY (`client`) REFERENCES `client` (`uniquename`), CONSTRAINT `shift_ibfk_4` FOREIGN KEY (`staff`) REFERENCES `staff` (`uniquename`), CONSTRAINT `shift_ibfk_5` FOREIGN KEY (`role`) REFERENCES `roletype` (`role`)) TYPE=InnoDB; I've tried back ticks on all the `on` column references, but still get same error. I have MySQL 4.0.18 and the mysqldump is :mysqldump Ver 9.10 Distrib 4.0.17, for Win95/Win98 Is this the problem? If so where do I get the right version of mysqldump? Later ones may not be bkwds compat. Can any-one help? Andrew H
Re: why NOT NULL in PRIMARY key??
Jigal van Hemert [EMAIL PROTECTED] writes: Because the SQL standard says so. A true observation, but still no explanation or reason why ;-P MySQL doesn't follow the standard in every situation, so that's not an excuse... (no offense!) There must be a good reason other than because our ancestors always did it this way. Let's look at it from a pure logic point of view. Given the table: create table a ( b int not null, c int null primary_key(b,c) ); With values: 1 null 1 null Logically these are unique records under the standard proviso that null != null. Yet how could I uniquely identify the first row to delete that row? Brad Eacker ([EMAIL PROTECTED]) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
