Re: query performance
At 07:10 PM 1/23/2004, Larry Brown wrote: I have a db that had some 20,000 records or so in it. I have a query to find out how many jobs have been input during the current day. To add them I ran the following query... "select count(idnumber) from maintable where inputdatetime > '$date 00:00:00' and client='smith'" $date is the current date in CCYY-MM-DD fashion and the query runs. However it seems fairly slow. I have now added some 100,000+ records from a merge I performed and now it takes a really long time. Is there a better way to query this that will take a load off the machine? The only key in the table is the idnumber. I don't really know anything about how keys help or when to/not to use them other than their being a necessity for an auto_increment field. TIA Larry Larry, Add two indexes, one for InputDateTime and another for Client. You should read up on MySQL. Try Paul Dubois book "MySQL 2nd Edition" because starts off really easy with stuff like this and by the time you're done, you're an expert. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Postponing Integrity Checking...
On Fri, 23 Jan 2004, Paul DuBois wrote: >At 17:32 -0800 1/23/04, Matthew Bogosian wrote: >>Although this does the trick (kind of), this just turns off integrity >>checking for that session, right? When I turn it back on, any >>statement that would have failed but didn't is still in a failed >>state. In other > >Right. You're not supposed to use it in order to do something that can >screw up your tables. But I *can*, and that's the point. I want to use the database to protect me from myself (or my company's application from its DBAs, etc.). >I guess I don't see the point of what you're trying to do. If you >think you may be doing something that violates a foreign key >constraint, *and* you want to roll it back if so, then just let the >error occur within the transaction and roll it back if it does. Yes, I want all the constraints to be maintained. But the change I want to make violates one of them, but only temporarily. I'm sorry for not conveying what I mean more clearly. Here's a simpler question. Given: CREATE TABLE test_parent ( id INTEGER NOT NULL PRIMARY KEY ) TYPE = INNODB; CREATE TABLE test_child ( id INTEGER NOT NULL PRIMARY KEY, parent_id INTEGER NOT NULL, INDEX (parent_id), FOREIGN KEY (parent_id) REFERENCES test_parent (id) ON DELETE CASCADE ) TYPE = INNODB; INSERT INTO test_parent VALUES (1); INSERT INTO test_child VALUES (50, 1); How do I execute the following UPDATE statements such that I can ensure that all integrity constraints are maintained upon the completion of the last one? UPDATE test_parent SET id = 6 WHERE id = 1; UPDATE test_child SET parent_id = 6 WHERE parent_id = 1; >>words, I could screw up and so something like: >> >> SET FOREIGN_KEY_CHECKS = 0; >> BEGIN; >> UPDATE test_parent SET id = 6 WHERE id = 1; >> UPDATE test_child SET parent_id = 782 WHERE parent_id = 1; >> COMMIT; >> SET FOREIGN_KEY_CHECKS = 1; >> >>MySQL wouldn't complain in this case, I'd just have a child row who's >>parent ID pointed to a non-existent parent (ID 782). Ideally, upon >>reaching the COMMIT there'd be some way for me to have an error (and >>have the transaction be rolled back). Here's what happens now: > >That doesn't make sense. Once you commit, you've committed. You can't >commit and then roll back. Sorry, I was using the word "commit" to mean transaction barrier. What I meant was to indicate that I am done with the transaction with a desire to commit the changes, but only if the integrity constraints were maintained. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Postponing Integrity Checking...
At 17:32 -0800 1/23/04, Matthew Bogosian wrote: Although this does the trick (kind of), this just turns off integrity checking for that session, right? When I turn it back on, any statement that would have failed but didn't is still in a failed state. In other Right. You're not supposed to use it in order to do something that can screw up your tables. I guess I don't see the point of what you're trying to do. If you think you may be doing something that violates a foreign key constraint, *and* you want to roll it back if so, then just let the error occur within the transaction and roll it back if it does. words, I could screw up and so something like: SET FOREIGN_KEY_CHECKS = 0; BEGIN; UPDATE test_parent SET id = 6 WHERE id = 1; UPDATE test_child SET parent_id = 782 WHERE parent_id = 1; COMMIT; SET FOREIGN_KEY_CHECKS = 1; MySQL wouldn't complain in this case, I'd just have a child row who's parent ID pointed to a non-existent parent (ID 782). Ideally, upon reaching the COMMIT there'd be some way for me to have an error (and have the transaction be rolled back). Here's what happens now: That doesn't make sense. Once you commit, you've committed. You can't commit and then roll back. BEGIN; UPDATE test_parent SET id = 6 WHERE id = 1; -- errors ... Here's what I'd like to happen: -- made up syntax BEGIN POSTPONE FOREIGN_KEY_CHECKS UNTIL COMMIT; UPDATE test_parent SET id = 6 WHERE id = 1; -- doesn't error UPDATE test_child SET parent_id = 782 WHERE parent_id = 1; COMMIT; -- now it errors (complaining about failed constraint) and rolls -- back Or: -- made up syntax BEGIN POSTPONE FOREIGN_KEY_CHECKS UNTIL COMMIT; UPDATE test_parent SET id = 6 WHERE id = 1; -- doesn't error UPDATE test_child SET parent_id = 6 WHERE parent_id = 1; COMMIT; -- no error since constraint is maintained As you can see, I want to postpone checking until the end of the transaction, not eliminate it altogether. While setting FOREIGN_KEY_CHECKS to 0 gets me half-way there with a simple case (so long as I'm careful), is there a way that is closer to what I've described above? I'm open to the idea of having the decision be made at table-creation time (as opposed to on a per-session basis), if that's possible. For example: CREATE TABLE test_child ( id INTEGER NOT NULL PRIMARY KEY, parent_id INTEGER NOT NULL, INDEX (parent_id), -- more made up syntax FOREIGN KEY (parent_id) REFERENCES test_parent (id) ON DELETE CASCADE POSTPONE FOREIGN_KEY_CHECKS UNTIL COMMIT ) TYPE = INNODB; Or something like that. Of course if auto-commit is on, then the behavior would be unchanged. --Matt On Fri, 23 Jan 2004, Paul DuBois wrote: At 15:52 -0800 1/23/04, Matthew Bogosian wrote: ... I'm trying to change the ID of one of the rows in one table and one of the rows which refer to it in another table: BEGIN; UPDATE test_parent SET id = 6; UPDATE test_child SET parent_id = 6; COMMIT; ... Is there a way to temporarily postpone integrity checking until the end of the transaction? ... Try: SET FOREIGN_KEY_CHECKS = 0; >http://www.mysql.com/doc/en/InnoDB_foreign_key_constraints.html -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com MySQL Users Conference: April 14-16, 2004 http://www.mysql.com/uc2004/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Need help with a SELECT statement across 3 tables
Isn't it great when you've got this pretty picture in your head about what you want SQL to do for you, but aren't sure how to write it down? I've got 3 tables, they are: server - a table that tracks all of our servers, including os, where they are, access details, etc update_track - a table that tracks updates that need to be applied to servers. server_update - a table that tracks what updates have been applied to what servers. An update_track entry contains a update_id, the primary key, a description, the afflicted OS, and the bugtraq ID associated with the update. So there's multiple entries for each bugtraq id for the OS's we support. The server_update table contains update_id, server_id, and a datestamp for when the update was applied. What I want to do is be able to list all servers that do not have all updates applied, based on OS. So we have to take each update_id, compare the update OS against a server entry OS, and decide if it matches, then check the server_update table to see if the update is applied already. I imagine that it would be sorted by server_id, then update_id. Has anyone done anything similar before, and can help me out? TIA Brandon Ewing -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Postponing Integrity Checking...
Although this does the trick (kind of), this just turns off integrity checking for that session, right? When I turn it back on, any statement that would have failed but didn't is still in a failed state. In other words, I could screw up and so something like: SET FOREIGN_KEY_CHECKS = 0; BEGIN; UPDATE test_parent SET id = 6 WHERE id = 1; UPDATE test_child SET parent_id = 782 WHERE parent_id = 1; COMMIT; SET FOREIGN_KEY_CHECKS = 1; MySQL wouldn't complain in this case, I'd just have a child row who's parent ID pointed to a non-existent parent (ID 782). Ideally, upon reaching the COMMIT there'd be some way for me to have an error (and have the transaction be rolled back). Here's what happens now: BEGIN; UPDATE test_parent SET id = 6 WHERE id = 1; -- errors ... Here's what I'd like to happen: -- made up syntax BEGIN POSTPONE FOREIGN_KEY_CHECKS UNTIL COMMIT; UPDATE test_parent SET id = 6 WHERE id = 1; -- doesn't error UPDATE test_child SET parent_id = 782 WHERE parent_id = 1; COMMIT; -- now it errors (complaining about failed constraint) and rolls -- back Or: -- made up syntax BEGIN POSTPONE FOREIGN_KEY_CHECKS UNTIL COMMIT; UPDATE test_parent SET id = 6 WHERE id = 1; -- doesn't error UPDATE test_child SET parent_id = 6 WHERE parent_id = 1; COMMIT; -- no error since constraint is maintained As you can see, I want to postpone checking until the end of the transaction, not eliminate it altogether. While setting FOREIGN_KEY_CHECKS to 0 gets me half-way there with a simple case (so long as I'm careful), is there a way that is closer to what I've described above? I'm open to the idea of having the decision be made at table-creation time (as opposed to on a per-session basis), if that's possible. For example: CREATE TABLE test_child ( id INTEGER NOT NULL PRIMARY KEY, parent_id INTEGER NOT NULL, INDEX (parent_id), -- more made up syntax FOREIGN KEY (parent_id) REFERENCES test_parent (id) ON DELETE CASCADE POSTPONE FOREIGN_KEY_CHECKS UNTIL COMMIT ) TYPE = INNODB; Or something like that. Of course if auto-commit is on, then the behavior would be unchanged. --Matt On Fri, 23 Jan 2004, Paul DuBois wrote: >At 15:52 -0800 1/23/04, Matthew Bogosian wrote: >> >>... >> >>I'm trying to change the ID of one of the rows in one table and one of >>the rows which refer to it in another table: >> >> BEGIN; >> UPDATE test_parent SET id = 6; >> UPDATE test_child SET parent_id = 6; >> COMMIT; >> >>... >> >>Is there a way to temporarily postpone integrity checking until the >>end of the transaction? >> >>... > >Try: > >SET FOREIGN_KEY_CHECKS = 0; > >http://www.mysql.com/doc/en/InnoDB_foreign_key_constraints.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
query performance
I have a db that had some 20,000 records or so in it. I have a query to find out how many jobs have been input during the current day. To add them I ran the following query... "select count(idnumber) from maintable where inputdatetime > '$date 00:00:00' and client='smith'" $date is the current date in CCYY-MM-DD fashion and the query runs. However it seems fairly slow. I have now added some 100,000+ records from a merge I performed and now it takes a really long time. Is there a better way to query this that will take a load off the machine? The only key in the table is the idnumber. I don't really know anything about how keys help or when to/not to use them other than their being a necessity for an auto_increment field. TIA Larry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Replication Madness
Thanks for the reply. I agree with your first solution but I feel this is very limiting. I would like be able to connect with a dns name with assurance that it will succeed. I think this is a reasonable feature to request. I have, for example, a DNS Round Robin used for load sharing. It is essencial here that the DNS name authenticates. For clarity below is the error message I receive when the system goes down. The connections were made perfectely well with dns prior to the mysterious switch - I guess the question remains why is it switching? 040119 12:01:16 Slave I/O thread: error reconnecting to master '[EMAIL PROTECTED]:3336': Error: 'Host '111.111.11.11' is not allowed to connect to this MySQL server' errno: 1130 retry-time: 60 retries: 86400 (Numbers and letter changed intentionally.) -Original Message- From: Mikael Fridh To: DePhillips, Michael P; [EMAIL PROTECTED] Sent: 1/23/2004 6:50 PM Subject: Re: Replication Madness I'm not sure I quite follow you here. I think you mean that when the slave connects to the master, sometimes the master does not resolve the address the slave has - thus failing because you don't have grants for the slave's IP address.. Generally I think it's a bad idea to be dependent on a dns lookup, the grant should be for the proper Ip adress(es) instead. A name server look up always have the risk of failing (unless it's set to retry endlessly) You could try a worse solution - put the slave's IP address in the HOSTS file on the master. That way (IF the OS reads the host file before it queries a name server) it will always identify the slave's hostname. But like I said, that's the bad solution I think... Mikael - Original Message - From: "DePhillips, Michael P" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Friday, January 23, 2004 11:00 PM Subject: Replication Madness > Hi Folks > > > > Every now and again one of my slaves try's to connect to its master via its > ip address as opposed to its dns name. This causes a problem because the ip > address does not have credentials on the master, therefore, connection > refused ->replication ends ->databases out of sync-> angry users-> we all > know the rest. The switch is random both temporally and machine wise. Are > there any ideas on why this is happening or what needs to be done to prevent > it? > > > > Perhaps this is a bug and should be reported as such? > > > > Thanks > > Michael > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Fulltext search
when when when will it be available for innodb ? -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Saturday, January 24, 2004 11:43 AM To: Sidar LC.; [EMAIL PROTECTED] Subject: Re: Fulltext search At 18:34 -0600 1/23/04, Sidar LC. wrote: >How can I implement fulltext search engine on InnoDB and MySQL 5. You can't. FULLTEXT indexes are supported only for MyISAM tables. http://www.mysql.com/doc/en/Fulltext_Search.html -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com MySQL Users Conference: April 14-16, 2004 http://www.mysql.com/uc2004/ -- 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]
optimizer: GROUP BY unnessesary slow using ANSI notation for redundant group by's.
Hi ! For my forum system, I use the following query to generate the main-overview: (using 4.0.16-log) select f1.id as BoardId, f1.name as Board, f1.more as BoardDesc, f2.id as AreaId, f2.name as Area, f2.more as AreaDesc, count(distinct f3.id) as ThemenCount, count(distinct m1.ctime) as MessageCount, max(m1.ctime) as LastMessageStamp from forum as f1, forum as f2 left join forum as f3 on (f3.rid = f2.id) left join forum_msg as m1 on (m1.fid = f3.id) where f1.rid = 0 and f2.rid = f1.id group by AreaId -- note ANSI: group by AreaId, Area, AreaDesc order by BoardId, AreaId; ANSI requires to use "group by AreaId, Area, AreaDesc" instead of "group by AreaId" (which is a documented MySQL shortcut against this redundancy) but the ANSI notation is ~4 times slower. This performance penalty is really unnessesary because the optimizer could detect this kind of redundancy in many cases, especially this simple case because "group by f2.id" generates clearly the same results as "group by f2.id, f2.name, f2.more" does. I think it's really *bad* to allow the non-ANSI shortcut and to not detect the ANSI-notation as redundant! shame on you :) :) -- ciao - Stefan "aclocal - emit a warning if -ac kernel is used. " Stefan TrabyLinux/ia32 office: +49-721-3523165 Mathystr. 18-20 V/8 Linux/alpha cell: +XX-XXX-XXX 76133 Karlsruhe Linux/sparc http://graz03.kwc.at Germany Linux/arm mailto:[EMAIL PROTECTED] Europe Linux/mips mailto:[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fulltext search
At 18:34 -0600 1/23/04, Sidar LC. wrote: How can I implement fulltext search engine on InnoDB and MySQL 5. You can't. FULLTEXT indexes are supported only for MyISAM tables. http://www.mysql.com/doc/en/Fulltext_Search.html -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com MySQL Users Conference: April 14-16, 2004 http://www.mysql.com/uc2004/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Postponing Integrity Checking...
At 15:52 -0800 1/23/04, Matthew Bogosian wrote: I searched the archives and the manual for an answer to this, but I haven't found an answer. I have several InnoDB tables: CREATE TABLE test_parent ( id INTEGER NOT NULL PRIMARY KEY ) TYPE = INNODB; CREATE TABLE test_child ( id INTEGER NOT NULL PRIMARY KEY, parent_id INTEGER NOT NULL, INDEX (parent_id), FOREIGN KEY (parent_id) REFERENCES test_parent (id) ON DELETE CASCADE ) TYPE = INNODB; INSERT INTO test_parent VALUES (1); INSERT INTO test_child VALUES (50, 1); I'm trying to change the ID of one of the rows in one table and one of the rows which refer to it in another table: BEGIN; UPDATE test_parent SET id = 6; UPDATE test_child SET parent_id = 6; COMMIT; But with statement-scoped referential integrity checking in place, I obviously can't do that (it fails on the first UPDATE statement). Is there a way to temporarily postpone integrity checking until the end of the transaction? If not temporarily, then is there a way to define one of the tables to postpone integrity checking until the end of the XA? (By the way, I'm using 4.0.13.) Thanks in advance for any assistance! Try: SET FOREIGN_KEY_CHECKS = 0; http://www.mysql.com/doc/en/InnoDB_foreign_key_constraints.html -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com MySQL Users Conference: April 14-16, 2004 http://www.mysql.com/uc2004/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ResultSet
Can stored procedure return a result set for my java program? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fulltext search
How can I implement fulltext search engine on InnoDB and MySQL 5. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
In replication, can you have a slave act as a master to a different slave?
Here is my scenario: I have 3 linux boxes - 2 with mysql 4.0.16 and 1 with 4.0.17. I have a 4.0.16 master (sys1) replicating a database (db1) that is roughly 1.5GB to a slave system (sys2) running 4.0.17. For the sake of a formal representation I'll say that (sys1,db1) --> (sys2,db1) represents this relationship. On sys2 we run an application that inspects the replication of db1 and maintains information in a different database (db2). I would like sys2 to replicate db2 to our third system (sys3) which happens to be running version 4.0.16. (sys2,db2) --> (sys3,db2). In this situation, can system sys2, who is a slave to sys1 for db1, be a master for db2 to a slave system sys3? If this is possible could someone help me by inspecting my my.cnf files and tell me why the second step (sys2,db2) --> (sys3,db2) does not replicate? Here are pieces of the my.cnf files that should be relevant. If someone can tell me what I'm doing wrong or has some suggestions I would greatly appreciate it. my.cnf from sys1: == [mysqld] log-bin bind-address = 192.168.1.2 server-id=1 log-bin=sys1-bin log-bin-index=sys-bin.index binlog-do-db=db1 set-variable = max_binlog_size=20M my.cnf from sys2: == [mysqld] log-bin bind-address = 192.168.1.3 server-id=2 master-host=192.168.1.2 master-user=sys2 master-password=sys2password master-port=3307 master-connect-retry=60 replicate-do-db=db1 log-warnings log-slave-updates report-host=192.168.1.3 report-port=3307 log-bin=sys2-bin log-bin-index=sys2-bin.index binlog-do-db=db2 set-variable=max_binlog_size=20M my.cnf from sys3: == bind-address=192.168.1.4 server-id=3 master-host=192.168.1.3 master-user=sys3 master-password=sys3password master-port=3307 replicate-do-db=db2 log-warnings log-slave-updates master-connect-retry=60 report-host=192.168.1.4 report-port=3307
Re: Select help
- Original Message - From: "Mike Mapsnac" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Friday, January 23, 2004 11:42 PM Subject: Select help > Hello > > I want to select from the table sum of logins for each day. Here's one way to do it. SELECT SUBSTRING(last_login, 1, 10) AS day, login_count FROM table GROUP BY day ORDER BY day ASC; > For example: > Date Logins > 2004-01-22 10 > 2004-01-23 12 > > Any ideas if such select is possible? > > +--+--+ > | Field| Type | > +--+--+ > | login_count | int(4) | > | last_login | datetime | > Mikael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select help
Hi, > I want to select from the table sum of logins for each day. Would this help: mysql> select date_format(your_date_column, "%Y-%m-%d"), count(*) -> from your_table -> group by date_format(your_date_column, "%Y-%m-%d"); Take care, Aleksandar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Postponing Integrity Checking...
I searched the archives and the manual for an answer to this, but I haven't found an answer. I have several InnoDB tables: CREATE TABLE test_parent ( id INTEGER NOT NULL PRIMARY KEY ) TYPE = INNODB; CREATE TABLE test_child ( id INTEGER NOT NULL PRIMARY KEY, parent_id INTEGER NOT NULL, INDEX (parent_id), FOREIGN KEY (parent_id) REFERENCES test_parent (id) ON DELETE CASCADE ) TYPE = INNODB; INSERT INTO test_parent VALUES (1); INSERT INTO test_child VALUES (50, 1); I'm trying to change the ID of one of the rows in one table and one of the rows which refer to it in another table: BEGIN; UPDATE test_parent SET id = 6; UPDATE test_child SET parent_id = 6; COMMIT; But with statement-scoped referential integrity checking in place, I obviously can't do that (it fails on the first UPDATE statement). Is there a way to temporarily postpone integrity checking until the end of the transaction? If not temporarily, then is there a way to define one of the tables to postpone integrity checking until the end of the XA? (By the way, I'm using 4.0.13.) Thanks in advance for any assistance! --Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication Madness
I'm not sure I quite follow you here. I think you mean that when the slave connects to the master, sometimes the master does not resolve the address the slave has - thus failing because you don't have grants for the slave's IP address.. Generally I think it's a bad idea to be dependent on a dns lookup, the grant should be for the proper Ip adress(es) instead. A name server look up always have the risk of failing (unless it's set to retry endlessly) You could try a worse solution - put the slave's IP address in the HOSTS file on the master. That way (IF the OS reads the host file before it queries a name server) it will always identify the slave's hostname. But like I said, that's the bad solution I think... Mikael - Original Message - From: "DePhillips, Michael P" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Friday, January 23, 2004 11:00 PM Subject: Replication Madness > Hi Folks > > > > Every now and again one of my slaves try's to connect to its master via its > ip address as opposed to its dns name. This causes a problem because the ip > address does not have credentials on the master, therefore, connection > refused ->replication ends ->databases out of sync-> angry users-> we all > know the rest. The switch is random both temporally and machine wise. Are > there any ideas on why this is happening or what needs to be done to prevent > it? > > > > Perhaps this is a bug and should be reported as such? > > > > Thanks > > Michael > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Embedded mySQL
Hello, For embedded mySQL server there is a restriction that You cannot set this up as a master or a slave (no replication). To support replication using embedded mySQL is there any steps that I can follow (e.g., by modifying portion of the source code etc.)? Pls. advise Regds, Rajarshi Chaudhuri
Select help
Hello I want to select from the table sum of logins for each day. For example: Date Logins 2004-01-22 10 2004-01-23 12 Any ideas if such select is possible? +--+--+ | Field| Type | +--+--+ | login_count | int(4) | | last_login | datetime | _ Rethink your business approach for the new year with the helpful tips here. http://special.msn.com/bcentral/prep04.armx -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Does the MySQL packet limitation still exist?
Hello, Does the packet limitation of 16MB still exist for the client/server protocol in MySQL? I am trying to upload files greater than 16MB from a php/apache interface, with no success. Files < 15MB are saved just fine into the database. The datatype on the field is longblob. From the mysql cli I can load files into the database that are greater than 16MB with no problem. I am running MySQL on a redhat 7.3 box with the following MySQL rpms installed: MySQL-server-4.0.17-0 MySQL-client-4.0.17-0 MySQL-Max-4.0.17-0. I am running apache 1.3.29 and php 4.3.2 installed. The server has 128MB of RAM MySQL is starting from /etc/init.d/mysql with the following in the startup script: $bindir/mysqld_safe --datadir=$datadir --pid-file=$pid_file --max_allowed_packet=64M -O key_buffer=192M -O table_cache=128 --log_bin=peerreview-binary-update-log --max_binlog_size=256M & When running: `mysqladmin VARIABLES -h hostname -p|grep allowed` I get: max_allowed_packet 67107840 The maximum post size in my php.ini file is: 32MB The maximum uploadable file size is: 32MB According to http://www.mysql.com/doc/en/Packet_too_large.html this limit should be raised beyond 16MB with MySQL 4.01+. At the very least I should be able to save a file of size 32MB from the php interface, but I cannot. What else would be causing this limitation? Cheers, Aaron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication Madness
Hi Folks Every now and again one of my slaves try's to connect to its master via its ip address as opposed to its dns name. This causes a problem because the ip address does not have credentials on the master, therefore, connection refused ->replication ends ->databases out of sync-> angry users-> we all know the rest. The switch is random both temporally and machine wise. Are there any ideas on why this is happening or what needs to be done to prevent it? Perhaps this is a bug and should be reported as such? Thanks Michael
[BUG] SHOW INDEX bug for fulltext indexes in MySQl 4.0.17
Here's a recipe: create table foo (foo text, bar text); create fulltext index foo on foo (foo, bar); mysql> show index from foo; +---++--+--+-+---+-+--++--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +---++--+--+-+---+-+--++--++-+ | foo | 1 | foo |1 | foo | A | NULL |1 | NULL | YES | FULLTEXT | | | foo | 1 | foo |2 | bar | A | NULL |1 | NULL | YES | FULLTEXT | | +---++--+--+-+---+-+--++--++-+ Sub_part should be NULL for both of these columns. The same thing happens for a single column fulltext index. -dave /*=== House Absolute Consulting www.houseabsolute.com ===*/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
If Else statement
I am trying write a shell script to check to see if a record exists and if it does, update information, and if it doesn't insert information. Is there a way to do an if else statement in MySql? I'm stuck on how to do this. I don't want to write it in perl. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
CASE after the WHERE
It used to be that I used SQL for basic stuff and did the rest in my code. Now I'm starting to learn some of the cooler features of SQL and I'm trying to figure out what I can move into a query and what I'll still need my code to do thing. In trying this conversion, I've run across something and I'm wondering if this is legal in SQL/MySQL. This query is obviously wrong but is there a way I can do the same thing? SELECT * FROM certificate WHERE active = 1 AND bound = 1 AND void = 0 AND premium_due <> 0 CASE WHEN '' != '' THEN AND payment_ref_num LIKE '%001%' AND payment_ref_num != '' WHEN '' != '' THEN AND uid = 'adt001' WHEN '' != '10747' THEN AND cert_num LIKE '%10747%' END; NB: for the "WHEN '' != ''" part of the query, once side (or the other) of the operand would have data, supplied by my code, in the quotes. If the variables aren't set, the value won't be part of the operation and as such, that particular CASE would resolve. Thanks for any help anyone can provide. I'm just trying to broaden my horizons. :p thnx, Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Installation Query
Greetings. If these questions were answered before I apologize in advance. I am trying to install MySQL on a new server that I got. Server is Red Hat 7.2 (not my choice), Dual P4 Xeon 2.8, 6 GB, 36GB Raid 5. Few questions I have: 1. What installation is recommended on such system. Binary, RPM, or compile from the source? If I compile from the source, will the compiled binary recognize and take advantage of the dual CPUs or I am just wasting my time? Also once installing from the source do I need to download and install the client, libraries and developer package just like I need in the RPM installation? Does that apply also to the binary installation. 2. I tried installing using the rpms, for some reason I get. DBI: connection failed cannot find mysql.sock (2) in web min. Checked that all the Perl modules are installed properly. After doing some research it seems that ln -s /tmp/mysql.sock /var/lib/mysql/mysql.sock Corrected it. And I have to apply. ln -s /tmp/mysql.sock /var/lib/mysql/mysql.sock Every time I stop and restart the MySQL server. Can anyone suggest how to correct this? I may be missing something basics, any pointers will be greatly appreciated. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
(Left) Join and Union
Can you UNION 2 queries and use the result to JOIN with another table? (SELECT tableA.name, tableC.company FROM tableA) UNION (SELECT tableB.name FROM tableB) LEFT JOIN tableC ON tableA.name = tableC.name; This doesn't work. But I don't know if it isn't working because I have the wrong syntax or if it's simply not possible with SQL/MySQL. thnx, Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Release 4.1.1
Hi Matt Can you let me know when is the version 4.1.1 expected to release (complete)? If its not confidential and you know something about it. And I could use mysql with websphere studio 5.1 and its running fine Thanks and Regards Saurabh Sharma Fidelity Brokerage Technology ' 617-563-2662 (W) 617-481-2437 (H) -Original Message- From: Matt Fuller [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 14, 2004 7:22 PM To: Sharma, Saurabh Subject: RE: Problem while installing MySQL, etc. Hi Saurabh, To tell you the truth, I have never used WebSphere Studio. I see it's for building web services with J2EE. I did a quick search for MySQL under the support section of their website. I found an article about which databases are supported, but I do not have an IBM id to view it. You might take a look at it. My instinct would tell me that you probably could, but I'm not sure. HTH Matt At 05:12 PM 1/14/2004, you wrote: >Hi Matt > I am facing a problem. Can I make mysql as Datasource in WebShpere Studio >Application Developer >I am struck. I will appreciate your help. > >Thanks and Regards >Saurabh Sharma > >Fidelity Brokerage Technology >' 617-563-2662 (W) > 617-481-2437 (H) > > > >-Original Message- >From: Matt Fuller [mailto:[EMAIL PROTECTED] >Sent: Wednesday, January 07, 2004 11:06 AM >To: Sharma, Saurabh; [EMAIL PROTECTED] >Cc: [EMAIL PROTECTED] >Subject: Re: Problem while installing MySQL, etc. > > >Sharma and Terry, > >I believe MySQL only supplies setup.exe files with the versions that have >production status. However, if you download the appropriate .zip file from >the website, then that will contain all the files that you need. Simply >extract it to the default location (C:\mysql in Windows or \usr\local, I >believe, in Linux). If you are upgrading, then make sure you back up your >old installation as to not override your data. > >HTH, >Matt > >At 09:25 AM 1/7/2004, Sharma, Saurabh wrote: > > >Hi > > I am trying to install MySQL for practice on my PC (Windows XP). I have > > all the administrative rights > >I downloaded the zip file mysql-4.1.1a-alpha.zip from the downloads. > >I extracted the zip file in a temporary folder. > >The manual says run setup.exe for installing on Windows but I couldn't > >find this file in the temporary folder. > >Can you help me on this regard > > > >Thanks and Regards > >Saurabh Sharma > > > >Fidelity Brokerage Technology > > > >-- > >MySQL General Mailing List > >For list archives: http://lists.mysql.com/mysql > >To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > >At 09:43 AM 1/7/2004, Terry Riley wrote: > >I have downloaded the correct windows zip file, but unlike previous > >Windows downloads, this does not include a setup.exe file, but hundreds of > >other (source?) files. > > > >Yes, I have RTFM, which merely tells me to extract to a temporary > >directory the run the setup.exe file, which is non-existent. > > > >How can I upgrade from 4.0.17, please? Or even start again from scratch > >with 4.1.1? > > > >Regards > >Terry Riley > > > > > >-- > >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]
JOIN, GROUP BY & INDEX
Hi, I have some question on JOIN and INDEX usage on GROUP BY. Hope you can help me. thanks! 1. If there is any performance differenct for join from a small table to a big table and join from a big table to a small table? 2. If index on group by column order, will it have any query performance gain? 3. If having WHERE clause using exact order of the index column be faster than WHERE clause using only first column of the index (not exact order o the index column). Is there any web site have more detail of how mysql using index? thanks! HHT -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CREATE TABLE
Hi, > Error: > ERROR 1005: Can't create table > './tamiyausa/user_shipping_info.frm' (errno: > 150) C:\mysql\bin>perror 150 Error code 150: Unknown error 150 = Foreign key constraint is incorrectly formed Look like your foreign keys are not properly defined. Do both tables exist? And the fields you are referencing? Take care, Aleksandar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: data file too big
On Fri, 23 Jan 2004, Mikhail Entaltsev wrote: > Date: Fri, 23 Jan 2004 09:34:52 +0100 > From: Mikhail Entaltsev <[EMAIL PROTECTED]> > To: Gregory Newby <[EMAIL PROTECTED]>, Asif Iqbal <[EMAIL PROTECTED]> > Cc: [EMAIL PROTECTED] > Subject: Re: data file too big > > > I believe that this will flush those logs: > > > > mysql> reset master; Is there any chance of loosing any data if I do that. Its a production machine with over 200 thousand customer tickets > > No.. It won't shrink any of InnoDB datafiles: > > > > 101Mib_logfile0 > > > 101Mib_logfile1 > > > 1.9Gibdata1 > > > 1.5Gibdata2 > > It could help if you have some MySQL binary logs: > -bin.001 > ... > -bin.00N I don't. Is there any other suggestions ? > > Best regards, > Mikhail. > > - Original Message - > From: "Gregory Newby" <[EMAIL PROTECTED]> > To: "Asif Iqbal" <[EMAIL PROTECTED]> > Cc: <[EMAIL PROTECTED]> > Sent: Thursday, January 22, 2004 11:32 PM > Subject: Re: data file too big > > > > I believe that this will flush those logs: > > > > mysql> reset master; > > > > -- Greg > > > > On Thu, Jan 22, 2004 at 05:23:07PM -0500, Asif Iqbal wrote: > > > Hi All > > > > > > My data file has all these files > > > > > > (root)@webrt:/usr/local/mysql/data# du -sh * > > > 25K ib_arch_log_00 > > > 3.0Kib_arch_log_02 > > > 3.0Kib_arch_log_04 > > > 101Mib_logfile0 > > > 101Mib_logfile1 > > > 1.9Gibdata1 > > > 1.5Gibdata2 > > > 2.0Kmy.cnf > > > 70K mysql > > > 2.0Knewdb > > > 39M rt3 > > > 12K test > > > 67K webrt.err > > > 1.0Kwebrt.pid > > > > > > Is there anyway I can remove some of them so I can get some space back ? > > > > > > I am using mysql -4.0.13 on Solaris 8 > > > > > > -- > > > Asif Iqbal > > > PGP Key: 0xE62693C5 KeyServer: pgp.mit.edu > > > There's no place like 127.0.0.1 > > > > > > -- > > > 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] > > > > > > -- Asif Iqbal PGP Key: 0xE62693C5 KeyServer: pgp.mit.edu There's no place like 127.0.0.1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL replication & performance questions
Hi, I have gone through the MySQL documentation and FAQs. But still I have a few questions - 1. Is there a performance comparison between MySQL Pro and MaxDB? Is MaxDB much better than MySQL? 2. Does MaxDB also has a C/C++ interface? 3. Is replication supported by MaxDB? How fast is MySQL replication? Can I use this replication feature for real-time purpose? 4. How good is the fault-tolerance mechanism wrt replication - do you have some stats/numbers for that? 5. Is any of the current customers (with professional license) used MySQL in their mission-critical applications? Regds, Rajarshi Chaudhuri Genesys Telecomm.
Re: anybody used prepared statements in 4.1 succesfully?
I get same the problem with any row.
this statement returns 1 row with the stable build but returns 0 when
using the nightly one.
Select P_ID,R_Date from performances Where R_Date = ? and P_ID = 171576
Resultset with stable build looks like..
P_IDR_Date
171576 2002-02-02
Column Information For - mynndb.performances
Field Type Collation NullKey Default
Extra Privileges Comment
- -- -- --
-- --- ---
P_ID int(10) NULL PRI 0
select,insert,update,references
H_ID int(10) NULL MUL 0
select,insert,update,references
R_ID int(10) NULL MUL 0
select,insert,update,references
H_Age int(10) NULL YES MUL (NULL)
select,insert,update,references
R_Datedate NULL MUL -00-00
select,insert,update,references
Comments varchar(255) latin1_swedish_ci YES (NULL)
select,insert,update,references
Weightint(10) NULL YES (NULL)
select,insert,update,references
HeadGear char(1) latin1_swedish_ci
select,insert,update,references
FinPosint(10) NULL YES (NULL)
select,insert,update,references
Jock_ID int(10) NULL YES (NULL)
select,insert,update,references
BtnByPrev doubleNULL YES (NULL)
select,insert,update,references
BtnByWinner doubleNULL YES (NULL)
select,insert,update,references
Jock_All int(10) NULL YES (NULL)
select,insert,update,references
Draw int(10) NULL YES (NULL)
select,insert,update,references
SFint(10) NULL YES (NULL)
select,insert,update,references
RHR int(10) NULL YES (NULL)
select,insert,update,references
ORating int(10) NULL YES (NULL)
select,insert,update,references
Odds doubleNULL YES (NULL)
select,insert,update,references
RaceFormPlus int(10) NULL YES (NULL)
select,insert,update,references
PrevPerform int(10) NULL YES (NULL)
select,insert,update,references
TrainerID int(10) NULL MUL 0
select,insert,update,references
DaysSinceRun int(10) NULL YES (NULL)
select,insert,update,references
Index Information For - mynndb.performances
---
Table Non_unique Key_name Seq_in_index Column_name Collation
Cardinality Sub_part Packed NullIndex_type Comment
-- - --- -
--- -- -- -- ---
performances 0 P_ID 1 P_ID A
371759(NULL) (NULL) BTREE
performances 0 R_H_ID1 R_ID A
(NULL)(NULL) (NULL) BTREE
performances 0 R_H_ID2 H_ID A
371759(NULL) (NULL) BTREE
performances 1 R_Date1 R_Date A
1936(NULL) (NULL) BTREE
performances 1 H_Age 1 H_AgeA
15(NULL) (NULL) YES BTREE
performances 1 TrainerID 1 TrainerIDA
2528(NULL) (NULL) BTREE
performances 1 H_ID 1 H_ID A
41306(NULL) (NULL) BTREE
DDL Information For - mynndb.performances
-
Table Create Table
---
question regarding MAX() and INSERT
I would like to do the following:
INSERT INTO tt (a,i) VALUES ('text',MAX(i)+1);
This doesn't work b/c MAX() isn't allowed here. Does
anyone know how I can still achieve this with ONE
query?
Thanks.
Dan
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
CREATE TABLE
Help! I am totally stumped. Why won't this execute successfully? CREATE TABLE user_shipping_info ( user_id INT UNSIGNED NOT NULL, as_acct VARCHAR(8) NOT NULL, email_address VARCHAR(40), company VARCHAR(50), phone_number VARCHAR(15), fax_number VARCHAR(15), address_1 VARCHAR(40), address_2 VARCHAR(40), address_3 VARCHAR(40), address_4 VARCHAR(40), city VARCHAR(25), state_abbreviation CHAR(2), zip VARCHAR(12), country VARCHAR(25), UNIQUE INDEX idx_uniq (user_id, as_acct), FOREIGN KEY (user_id) REFERENCES `user`(id) ON DELETE CASCADE ON UPDATE CASCADE, INDEX idx_state_abbreviation (state_abbreviation), FOREIGN KEY (state_abbreviation) REFERENCES state(state_abbreviation) ON DELETE SET NULL ON UPDATE CASCADE ) TYPE=INNODB; Error: ERROR 1005: Can't create table './tamiyausa/user_shipping_info.frm' (errno: 150) Thanks in advance! Jer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Bind bind bind
Hi , Is everybody have ever made bind to column and for where coluse in one ? select colname from table where wcol=? colname is binded to one attay and the ? is binded to an other... My server (4.1) is dump all the time) Thnaks Gergő -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Odd Rounding?
Hi Andrew Consider using NUMERIC or DECIMAL to maintain precision. >From the manual: The NUMERIC and DECIMAL types are implemented as the same type by MySQL, as permitted by the SQL-92 standard. They are used for values for which it is important to preserve exact precision, for example with monetary data. When declaring a column of one of these types the precision and scale can be (and usually is) specified; for example: salary DECIMAL(5,2) Regards, Matt -- That's how floating-point numbers work generally, not just in MySQL. Why are you so concerned about it? Presumably your 0.012345 isn't an exact number but a measurement of some sort, so it could just as well be 0.0123449 or 0.0123451, and 0.01234 is perfectly fine as a rounded value. If you do have exact numbers with six decimal places, you're probably better off storing them as some sort of integer and adding the decimal point when necessary for display. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.562 / Virus Database: 354 - Release Date: 1/16/2004 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: stored proc containing subquery crashes mysqld-nt.exe
On Fri, 23 Jan 2004, Rob Kemmer wrote: > Hi, MySQL Team! > > I've downloaded and successfully installed v5.0 win2k, and am happily using stored > procs, but seem to be encountering problems with subqueries in stored procs. When I > run a stored proc containing a subquery, the first pass works, but a second > consecutive call to the proc crashes the server. > > Other than that, I haven't encountered any other problems. This sounds very similiar to bug #2460: http://bugs.mysql.com/bug.php?id=2460 cheers, Tobias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Odd Rounding?
On 23 Jan 2004 at 12:13, Andrew Kuebler wrote: > Can anyone explain why: > > SELECT ROUND(.012345, 5) > -> .01234 > > Why doesn't mysql round the 5 up to .01235? How do I get it to round > up? I've tried manipulating ceil, floor, round and truncate and I > can't seam to find an easy way to do this. This comment from the documentation on TRUNCATE applies to other functions as well: | Note that as decimal numbers are normally not stored as exact | numbers in computers, but as double-precision values, you may be | fooled by the following result: | | mysql> SELECT TRUNCATE(10.28*100,0); |-> 1027 | | The above happens because 10.28 is actually stored as something | like 10.2799. That's how floating-point numbers work generally, not just in MySQL. Why are you so concerned about it? Presumably your 0.012345 isn't an exact number but a measurement of some sort, so it could just as well be 0.0123449 or 0.0123451, and 0.01234 is perfectly fine as a rounded value. If you do have exact numbers with six decimal places, you're probably better off storing them as some sort of integer and adding the decimal point when necessary for display. -- Keith C. Ivey <[EMAIL PROTECTED]> Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Odd Rounding?
Hi, I have email from Georg Richter about this problem in my MySQL archive: From: "Georg Richter" <[EMAIL PROTECTED]> Hi, >From http://www.mysql.com/doc/en/Mathematical_functions.html: Note that the behaviour of ROUND() when the argument is half way between two integers depends on the C library implementation. Some round to the nearest even number, always up, always down, or always toward zero. If you need one kind of rounding, you should use a well-defined function like TRUNCATE() or FLOOR() instead. Regards Georg Mikhail. - Original Message - From: "Andrew Kuebler" <[EMAIL PROTECTED]> To: "'David Brodbeck'" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Friday, January 23, 2004 6:33 PM Subject: RE: Odd Rounding? > Actually, no, it rounds to .01233. > > > -Original Message- > > From: Andrew Kuebler [mailto:[EMAIL PROTECTED] > > > Can anyone explain why: > > > > SELECT ROUND(.012345, 5) > > -> .01234 > > > > Why doesn't mysql round the 5 up to .01235? How do I get it > > to round up? > > Oftentimes standard practice is to round up if the digit before the 5 is > odd, and round down if it's even. (This averages out the bias you > otherwise > get with 5/4 rounding.) To see if this is what you're seeing, try > rounding > 0.012335 to 5 places...I bet it'll round up to 0.01234. > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Odd Rounding?
Actually, no, it rounds to .01233. > -Original Message- > From: Andrew Kuebler [mailto:[EMAIL PROTECTED] > Can anyone explain why: > > SELECT ROUND(.012345, 5) > -> .01234 > > Why doesn't mysql round the 5 up to .01235? How do I get it > to round up? Oftentimes standard practice is to round up if the digit before the 5 is odd, and round down if it's even. (This averages out the bias you otherwise get with 5/4 rounding.) To see if this is what you're seeing, try rounding 0.012335 to 5 places...I bet it'll round up to 0.01234. -- 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: Odd Rounding?
> -Original Message- > From: Andrew Kuebler [mailto:[EMAIL PROTECTED] > Can anyone explain why: > > SELECT ROUND(.012345, 5) > -> .01234 > > Why doesn't mysql round the 5 up to .01235? How do I get it > to round up? Oftentimes standard practice is to round up if the digit before the 5 is odd, and round down if it's even. (This averages out the bias you otherwise get with 5/4 rounding.) To see if this is what you're seeing, try rounding 0.012335 to 5 places...I bet it'll round up to 0.01234. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Removing sub-query
Hi there, I'm using MySQL 4.0.15 on RedHat 9.0. I've got problem for which I can't figure out how to do it efficiently without using a subquery. 4.1.0 isn't really an option since this is going into a stable environment. The table is like this. +--+--++ | revision | id | name | +--+--++ |4 | 80 | filea | |5 | 85 | filea | |8 | 77 | fileb | |9 | 73 | filec | +--+--++ I want to group by name, and get the id of the highest revision for each file. So the query should return the following results: +--+ | id | +--+ | 85 | | 77 | | 73 | +--+ Is there any way of doing this efficiently without needing a subquery and upgrade to MySQL 4.1.0? Thanks, Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: temporary table in 4.0.17 on Windows W2000 and NT4 from client running xp
Hi, 1) Try to reproduce the situation with pure myscl client (command-line client). 2) There is no any MySQL parameter that change life time of temporary tables. Only two parameter that defines life time for connection to mysql: - interactive_timeout The number of seconds the server waits for activity on an interactive connection before closing it. An interactive client is defined as a client that uses the CLIENT_INTERACTIVE option to mysql_real_connect(). See also wait_timeout. - wait_timeout The number of seconds the server waits for activity on a not interactive connection before closing it. On thread startup SESSION.WAIT_TIMEOUT is initialized from GLOBAL.WAIT_TIMEOUT or GLOBAL.INTERACTIVE_TIMEOUT depending on the type of client (as defined by the CLIENT_INTERACTIVE connect option). See also interactive_timeout. But by default they are quite big: 28800 seconds. Mikhail. - Original Message - From: "Massimo Petrini" <[EMAIL PROTECTED]> To: "Mikhail Entaltsev" <[EMAIL PROTECTED]> Sent: Friday, January 23, 2004 5:11 PM Subject: Re: temporary table in 4.0.17 on Windows W2000 and NT4 from client running xp > 1) yes, I am sure because the problem is reproducible also with a sql , > without to use the msaccess; if you use mysqlfront you can create the table, > insert data, view data. But if you move your point of view on another table > you lost the connection. > 2) to connect a temporary table in mysql from msaccess, it is necessary to > create the table on server not temporary, connect the table from msaccess, > drop the table on server; this is necessary only once; after use a sql > statement from msaccess, via sql pass through to create the temporary. Now > youn can insert data and use as a normal table. When you close the msaccess > session the temporary table is dropped immediately; otherwise if you leave > the session msaccess opened, but not in use, after some minutes the table is > dropped. I think there is a variable to set in my.ini, but I don't > understand which is the correct name . > > So I don't understand. > > - Original Message - > From: "Mikhail Entaltsev" <[EMAIL PROTECTED]> > To: "Massimo Petrini" <[EMAIL PROTECTED]> > Cc: <[EMAIL PROTECTED]> > Sent: Thursday, January 22, 2004 2:16 PM > Subject: Re: temporary table in 4.0.17 on Windows W2000 and NT4 > > > > 2 questions: > > > > 1. Are you sure that client or someone between client and MySQl server > > doesn't close connection? > > Because > > > the client have still the result of the query on the screen > > > > is not a proof that connection is still active. > > > > 2. How do you link your msaccess application with MySQL server? > > Are you sure that your application or someone between doesn't use > > connections pool? > > > > Mikhail. > > > > - Original Message - > > From: "Massimo Petrini" <[EMAIL PROTECTED]> > > To: "Mikhail Entaltsev" <[EMAIL PROTECTED]> > > Cc: <[EMAIL PROTECTED]> > > Sent: Thursday, January 22, 2004 1:35 PM > > Subject: Re: temporary table in 4.0.17 on Windows W2000 and NT4 > > > > > > > We have a server running NT4 with mysql 4.017 (with the my.ini as in > > > previuous mail). In the network the client (the single pc ) run msaccess > > > application linked to mysql database. In such case we use a temporary > > table, > > > created as follow > > > > > > CREATE TEMPORARY TABLE wrk_AlzateIntervalli(Lotto CHAR(10) NOT > NULL,Indice > > > CHAR(3) NOT NULL,NMisura SMALLINT NOT NULL,IndiceIntv CHAR(1) NOT > > > NULL,ValMin REAL NULL,ValMax REAL NULL, PRIMARY KEY (Lotto, Indice, > > NMisura, > > > IndiceIntv)); > > > > > > The statement sql is lunched from the client to the server; it create a > > > temporary table used only from the client creator. > > > Is clear for you ? > > > Thanks > > > > > > > > > - Original Message - > > > From: "Mikhail Entaltsev" <[EMAIL PROTECTED]> > > > To: "Massimo Petrini" <[EMAIL PROTECTED]> > > > Cc: <[EMAIL PROTECTED]> > > > Sent: Thursday, January 22, 2004 11:12 AM > > > Subject: Re: temporary table in 4.0.17 on Windows W2000 and NT4 > > > > > > > > > > > no; because the client have still the result of the query on the > > screen > > > > and > > > > > on the server the thread is still existent in sleep mode. > > > > > > > > could you explain in details what is your system configuration? > > > > what is the client of MySQL server? > > > > > > > > > > > > - Original Message - > > > > From: "Massimo Petrini" <[EMAIL PROTECTED]> > > > > To: "Mikhail Entaltsev" <[EMAIL PROTECTED]> > > > > Cc: <[EMAIL PROTECTED]> > > > > Sent: Thursday, January 22, 2004 11:08 AM > > > > Subject: Re: temporary table in 4.0.17 on Windows W2000 and NT4 > > > > > > > > > > > > > no; because the client have still the result of the query on the > > screen > > > > and > > > > > on the server the thread is still existent in sleep mode. > > > > > > > > > > - Original Message - > > > > > From: "Mikhail Entaltsev" <[EMAIL PROTECTED]> > > > > > To: "Massimo P
Odd Rounding?
Can anyone explain why: SELECT ROUND(.012345, 5) -> .01234 Why doesn't mysql round the 5 up to .01235? How do I get it to round up? I've tried manipulating ceil, floor, round and truncate and I can't seam to find an easy way to do this. I read in the manual that this behavior depends on the C library implementation, but how do you change it? To me, this is not normal behavior. Can anyone help? Thanks in advance... Best Regards, Andrew -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Restoring database doesn't restore all tables
I could use some help with restoring a backup. I have a backup file, generated using the mysqldump command. This file is approximately one gigabyte in size. I use the following command to restore this backup file: mysql -u root DevDBs < New.all.aca When the command is finished executing, I check my DevDBs database, and it has been updated, but not all tables that I'm expecting to be restored have been restored. Further, the DevDBs directory has only about 250 megabytes of data, rather than the one gigabyte I am expecting. Any help would be appreciated. Danny
Re: anybody used prepared statements in 4.1 succesfully?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Brian Power wrote: > yeah, I tried the new connector. but I'm getting strange results when > using a date field, > > > My sql looks like.. > > Where R_Date < ? and R_Date > ? and H_ID <> ? > > My code looks like... > > perparedStmt.setDate(1,new java.sql.Date(myDate.getTime())); > > R_Date looks like... > R_Date date Not null > > It doesn't crash, but it doesn't return the records either.When I use > the stable connector/j it works fine (but of course, it's not really > doing a prepared statement in the background) > > Also just at the point where I execute the statement, I get strange text > output to stdout, something like "0 0 0 0 0 8 0 0 0 ..." The output is from some debug code that has now been disabled. What does your table look like (i.e. schema, and the minimum amount of data needed to reproduce the issue)? I haven't yet seen a bug like this. The JDBC compliance testsuite we use, as well as our own unit and regression tests cover quite a bit of java.sql.Date functionality with prepared statements, and they all work. It looks like we might be working with a corner case here, so I'll need to see exactly what data causes this to happen in your case. -Mark - -- Mr. Mark Matthews MySQL AB, Software Development Manager, J2EE and Windows Platforms Office: +1 708 557 2388 www.mysql.com Want to swim with the dolphins? (April 14-16, 2004) http://www.mysql.com/uc2004/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFAEUPgtvXNTca6JD8RAi66AKCSKHkC5T4BPI/lx3qkdBuTbeW/HACdGWbt /FFQi5dEqHbGCWTNWh7Gzpo= =XWrG -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Order by problem
The following might help, but will certainly be quite slow: SELECT ... ORDER BY ABS(SUBSTRING(field, 4)); A better (and faster) solution will probably be indexing the records with a numeric field, as usual. Fred, Doesn't MySQL always physically sort the rows and not use the index to obtain row order? Do you (or anyone else listening) know of a way to get MySQL to use the index for sorting instead of physically sorting the rows? TIA Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Order by problem
At 06:49 AM 1/23/2004, Sagar C Nannapaneni wrote: Hi all, I have an ID field in my database...it reads like this ASS1 ASS23 ASS4 ASS10 ASS6 . . . when i'm retrieving the data by taking ORDER BY clause it is sorting like this ASS1 ASS10 ASS23 ASS4 ASS6 means its only sorting by the 4 the character. i want the sorting to be done like the following ASS1 ASS4 ASS6 ASS10 ASS23 Solutions are greatly appreciated Sagar, You can also try: select * from table order by cast(substr(id,4) as unsigned) Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
strange unique index behaviour on null values
hi all, I have a very strange problem with some indexes. It is a unique index on a column containing null data. when I do a query like select * from xxx where yyy is null I get only one row (the first) but I know there is more. recentely, this table switched to innodb and we changed the server. I have the same problem on all databases on this server and I think it worked before innodb and server switch. It is a mysql * 4.0.16 on a debian box. the unique index is on yyy and when I remove this index, I get the result I want. also if I had conditions, I don't get any results. can somebody help? thanks for your help. Best Regards, mo * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Update through API C
mmm I've deleted a select before the update function
-- SELECT --
// usiamo mysql_real_query al posto di mysql_query xche' la prima e'
piu' veloce
if (mysql_real_query(mp_cnn, c_query, strlen(c_query)) != 0)
return (-1);
else
{
mp_res = mysql_use_result(mp_cnn); // resultset
row = mysql_fetch_row(mp_res); // analizzo la riga e salvo i
dati dentro row
}
--
-- UPDATE --
if (mysql_real_query(mp_cnn, c_query, strlen(c_query)) != 0)
return (-1);
else
cout << mysql_affected_rows(mp_cnn) << endl; // mi ritorna quante
righe ho modificato
cout << c_query << endl;
return (0);
--
and now the update is right...
o-:
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SELECT statement
I'm attempting to collate a webpage showing results by various football teams in various cup competitions, and am trying to minimise the number of selects as best I can. What I'm trying to get out in one statement is the number of home matches played by Burnley, how many they've won, drawn and lost, and the totals goals scored by them and against them. In an ideal world it would be something like the following, but I know this will not work: SELECT COUNT(*) AS matchesplayed, COUNT(result='H') AS homewins, COUNT(result='D') AS drawngames, COUNT(result='A') AS awaywins, SUM(homescore) AS homegoalsscored, SUM(awayscore) AS awaygoalsscored FROM matchstats WHERE (homeTeam = 'Burnley') AND (competition = 'F.A.Cup') Effectively it's like a grouping, but the goals columns are calculations of the entire number of matches. I can achieve it with two statements, but wondered if there was a way of combining the two? Many thanks Gary -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Permission Problem in OS X
I'm not sure how much help I'll be since I have never had a problem running MySQL on OSX in the two years I have been using it on OSX. First, you should not need to use sudo or the root account for anything you do with MySQL except maybe installing it. Second, I would always specify the account name when you are trying to log into MySQL. For instance, try this: mysqladmin -u root password or mysql -u root -p to login as a client. That is, if you have an account setup in MySQL called root. The example you gave in your email is think working because I'm pretty sure it's trying to execute as a guest since no account is specified. I don't use any graphical interfaces to start and stop MySQL on OSX, but I know some people who have and have had problems. I've talked a few of them through removing the graphical tools and telling them how to start things from the command line. Feel free to email on or off line for further troubleshooting. On Jan 22, 2004, at 9:17 PM, Dietrich Speer wrote: I am having a problem with accessing the mysql database in a new MySQL installation on OS X Panther. This installation came with the original OS, and some configuration was done right after installation. I have previously installed MySQL on Linux and Win and have never encountered this problem. After being unable to log into MySQL at all, I decided to reset the MySQL root password. I did everything according to the book (several of them): - kill mysqld by killing .pid - restarting mysqld --skip-grant-tables - reset password for root by using MySQL client - Flush Privileges - restart mysqld the normal way. Over the past 2 days I have done this many many times. In between the steps I always check the processes to confirm that the previous step took. With the grant tables skipped I am able to access the mysql db just fine. After flushing privileges and restarting without skipping I cannot connect to the mysql db with either of the usr/pwd combinations I just created. I have tried back and forth - with a my.cnf file in /etc and without. With the client password set in that file and without. I am doing everything with the root password in OS X enabled but logged in as a non-root user. Where It's needed I am using sudo. Is this where the problem is? the password for OS X root and MySQL are different, and the MySQL password does not contain any reserved letters. It strikes me that, after going through all of this, and as a non root user, the graphical MySQL Manager refuses to stop the database without giving me an error message. # mysqladmin -u password has not worked in any scenario, whatever I do. I am getting really sick of this and have exhausted my ideas after going through several manuals. Can somebody please help! Thanks! Dietrich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- 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]
Stored Procedure and the procs.db column
Hi all, I'm investigating the Stored Procedures in MySQL 5, and I'm wondering... I've been looking through the "procs" table in "mysql" - what does column "db" do? It stays "null" when I defined a procedure. And how can you get a list of available procedures? SHOW PROCEDURE doesn't seem to work? With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Order by problem
Sagar C Nannapaneni wrote: ASS1 ASS23 ASS4 ASS10 ASS6 . . when i'm retrieving the data by taking ORDER BY clause it is sorting like this ASS1 ASS10 ASS23 ASS4 ASS6 means its only sorting by the 4 the character. No, it's not sorted by the first four characters but it's sorted lexicographically (string-like). The following might help, but will certainly be quite slow: SELECT ... ORDER BY ABS(SUBSTRING(field, 4)); A better (and faster) solution will probably be indexing the records with a numeric field, as usual. Greetz Fred -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Update through API C
Hi, I've wrote this lines of code mysql_query(mp_cnn, "UPDATE pg_info SET x=3, y=5 WHERE pg_id=0"); but when I controll my db the record isn't change. Why? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Update through API C
Hi, I've wrote this lines of code mysql_query(mp_cnn, "UPDATE pg_info SET x=3, y=5 WHERE pg_id=0"); but when I controll my db the record isn't change. Why? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqldump and --opt
HI all, I am trying to use mysqldump for a new DB: entered: mysqldump --opt -uroot -p dbname > dbBak Output mysqldump: Got error: 1044: Access denied for user: '[EMAIL PROTECTED]' to database 'mdirect' when using LOCK TABLES Then I logon as root (without specifing a db) - works fine Entered grant lock tables on *.* to 'root'@'localhost'; Output ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) Any ideas on this please * This email and any attachments are confidential and intended for the sole use of the intended recipient(s).If you receive this email in error please notify [EMAIL PROTECTED] and delete it from your system. Any unauthorized dissemination, retransmission, or copying of this email and any attachments is prohibited. Euroconex does not accept any responsibility for any breach of confidence, which may arise from the use of email. Please note that any views or opinions presented in this email are solely those of the author and do not necessarily represent those of the Company. This message has been scanned for known computer viruses. * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Order by problem
Martijn Tonies wrote: Hi, == I have an ID field in my database...it reads like this ASS1 ASS23 ASS4 ASS10 ASS6 when i'm retrieving the data by taking ORDER BY clause it is sorting like this ASS1 ASS10 ASS23 ASS4 ASS6 means its only sorting by the 4 the character. i want the sorting to be done like the following == No, it means it's sorting by alphabet, not by the 4th character. ASS10 comes after ASS1, makes perfect sense. == ASS1 ASS4 ASS6 ASS10 ASS23 Solutions are greatly appreciated == What you want, is that the sorting acts like to ignore "ASS" and use the number behind it as an integer. Perhaps you can cut off the first 3 characters, cast the rest to an Integer and order by that? SELECT * FROM tablename ORDER BY ((SUBSTRING(columnToSort FROM 4)) + 0) as numberpart -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Order by problem
Hi, == I have an ID field in my database...it reads like this ASS1 ASS23 ASS4 ASS10 ASS6 when i'm retrieving the data by taking ORDER BY clause it is sorting like this ASS1 ASS10 ASS23 ASS4 ASS6 means its only sorting by the 4 the character. i want the sorting to be done like the following == No, it means it's sorting by alphabet, not by the 4th character. ASS10 comes after ASS1, makes perfect sense. == ASS1 ASS4 ASS6 ASS10 ASS23 Solutions are greatly appreciated == What you want, is that the sorting acts like to ignore "ASS" and use the number behind it as an integer. Perhaps you can cut off the first 3 characters, cast the rest to an Integer and order by that? With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Order by problem
Hi all, I have an ID field in my database...it reads like this ASS1 ASS23 ASS4 ASS10 ASS6 . . . when i'm retrieving the data by taking ORDER BY clause it is sorting like this ASS1 ASS10 ASS23 ASS4 ASS6 means its only sorting by the 4 the character. i want the sorting to be done like the following ASS1 ASS4 ASS6 ASS10 ASS23 Solutions are greatly appreciated thanks in adavance /sagar
Largest OLTP on MySQL?
Who has the world's largest OLTP running on MySQL? Appreciate URL containing details. Thanks. Hassan
Mysql does io writes when working with temp tables
We are running a portal site using mysql and I have been trying to sqeeze some more performance out of our 4-CPU Linix 2.4.20 intel box. Our content is all read-only and we use in-memory temp tables a lot in our queries. I was watching vmstat as I was issuing some queries and I noticed that almost every operation results in some amount of writes. Swap space has been off. For example commands like thes all result in a small number of writes to disk. CREATE TEMPORARY TABLE TMP_TABLE_0 TYPE=HEAP; TRUNCATE TABLE TMP_TABLE_0; INSERT TMP_TABLE_0 SELECT DISTINCT pid, sid FROM SOME_TABLE; Here is the output of vmstat 1. procs memory swap io system cpu r b w swpd free buff cache si sobibo incs us sy id 0 0 0 0 153560 27668 63522800 0 0 10415 0 0 100 0 0 0 0 153560 27668 63522800 0 0 10613 0 0 100 0 0 0 0 153560 27668 63522800 012 11254 0 0 100 0 0 0 0 153560 27668 63522800 0 0 10411 0 0 100 0 0 0 0 153560 27668 63522800 0 0 10413 0 0 100 Note the 12 blocks written in the middle. Between 10-50 blocks get written for all of these operations on temp tables. I have a feeling this is OS related, but swap is off. I am quite sure that the writes are to disk. iostat shows the same problem and show the device being written to as /dev/sda - scsi. Any clues at to what might be happening? What is mysql or OS writing? In my opinion all of the queries above should be in-memory and result only in reads. Thanks, -Emile -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: anybody used prepared statements in 4.1 succesfully?
yeah, I tried the new connector. but I'm getting strange results when using a date field, My sql looks like.. Where R_Date < ? and R_Date > ? and H_ID <> ? My code looks like... perparedStmt.setDate(1,new java.sql.Date(myDate.getTime())); R_Date looks like... R_Date date Not null It doesn't crash, but it doesn't return the records either.When I use the stable connector/j it works fine (but of course, it's not really doing a prepared statement in the background) Also just at the point where I execute the statement, I get strange text output to stdout, something like "0 0 0 0 0 8 0 0 0 ..." It works when i don't use date fields. Do you think this will be fixed in the next release release ? Maybe I'm doing somthing wrong, who knows. From: Mark Matthews <[EMAIL PROTECTED]> To: Brian Power <[EMAIL PROTECTED]> CC: [EMAIL PROTECTED] Subject: Re: anybody used prepared statements in 4.1 succesfully? Date: Thu, 22 Jan 2004 09:49:35 -0600 -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Brian Power wrote: > anybody used prepared statements in 4.1 succesfully? > > > Has anybody used prepared statements in mySQL 4.1 succesfully. By > succesfull I mean quicker ! > > I have some prepared statement in my app and they take just as long the > second++ time as they do hte first. My querys may need to be optimized > (i just moved over from Access), but even so, shouldn't they be quicker > the second time around. > > > Im using java and Connector/J 3.0 You need to use Connector/J _3.1_ to get server-side prepared statement functionality. I suggest using the nightly snapshots until 3.1.1 is released (which should be later this week). 3.1.1 (as well as the latest nightly snapshots) also includes support for CallableStatements when using MySQL-5.0.0 and newer. See http://downloads.mysql.com/snapshots.php Regards, -Mark - -- Mr. Mark Matthews MySQL AB, Software Development Manager, J2EE and Windows Platforms Office: +1 708 557 2388 www.mysql.com Want to swim with the dolphins? (April 14-16, 2004) http://www.mysql.com/uc2004/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFAD/EPtvXNTca6JD8RApjrAJoCfb9DlLrtWAXH8XQIBEtwcZQNWQCgw9WS i+NBEfX6gTUHyGZ+K7LkVtc= =heEx -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ Add photos to your e-mail with MSN 8. Get 2 months FREE*. http://join.msn.com/?page=features/featuredemail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL listen on one IP only
* Andrew Boothman <[EMAIL PROTECTED]> [2004-01-23 03:28 +0100]: > I've got a box that has several IP addresses assigned to it, but I'd > like MySQL to listen on just one of those. You can add "bind-address=YOURIPADDRESS" to the [mysqld]-section of my.cnf and then restart mysqld. See http://www.mysql.com/doc/en/Server_options.html for a list of configuration options like this. But this might not be safe enough. On some operating systems, arbitrary users can still run a fake mysqld (Trojan horse) listening on *:3306/tcp. At first view, this might not look dangerous, for the horse will only receive connections on the other interfaces (e.g. lo), while connections to YOURIPADDRESS go the the original mysqld. But: If your original mysqld closes its service for some reason (e.g. logrotate), 1.) the horse will receive connections on the YOURIPADRESS interface too, and thus get to know your mysql-users' passwords. 2.) the original mysqld might not come up again, unless it sets the SO_REUSEADDR socket option, which might not be the case for your binary distribution of mysqld. Afaik, there is no native way to tell mysqld to listen on multiple (but not all) interfaces. Maybe it's a solution to bind mysqld to localhost, forward a low port (that is: port number <1024) of the YOURIPADDRESS interface to it and let your clients connect to that low port. This would at least require the horse to have root privileges. -- Johannes Franken MySQL Professional mailto:[EMAIL PROTECTED] http://www.jfranken.de/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Comparing Table IDs
select t2.* from TableTwo t2 left join TableOne t1 on (t2.ID = t1.ID) where t1.ID is NULL - Original Message - From: "Phillip S. Baker" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Friday, January 23, 2004 7:26 AM Subject: Comparing Table IDs > Greetings all, > > I have 3-4 tables of members on a website. > However over time I have table one that lists 14000 records and table two > lists 14121 records. > What kind of SQL call would I put out to compare the two tables and find > out which records in table two do not have a corresponding ID number in > table one. > Make sense?? > > Thanks > > Phillip > > > -- > 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: data file too big
> I believe that this will flush those logs: > > mysql> reset master; No.. It won't shrink any of InnoDB datafiles: > > 101Mib_logfile0 > > 101Mib_logfile1 > > 1.9Gibdata1 > > 1.5Gibdata2 It could help if you have some MySQL binary logs: -bin.001 ... -bin.00N Best regards, Mikhail. - Original Message - From: "Gregory Newby" <[EMAIL PROTECTED]> To: "Asif Iqbal" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Thursday, January 22, 2004 11:32 PM Subject: Re: data file too big > I believe that this will flush those logs: > > mysql> reset master; > > -- Greg > > On Thu, Jan 22, 2004 at 05:23:07PM -0500, Asif Iqbal wrote: > > Hi All > > > > My data file has all these files > > > > (root)@webrt:/usr/local/mysql/data# du -sh * > > 25K ib_arch_log_00 > > 3.0Kib_arch_log_02 > > 3.0Kib_arch_log_04 > > 101Mib_logfile0 > > 101Mib_logfile1 > > 1.9Gibdata1 > > 1.5Gibdata2 > > 2.0Kmy.cnf > > 70K mysql > > 2.0Knewdb > > 39M rt3 > > 12K test > > 67K webrt.err > > 1.0Kwebrt.pid > > > > Is there anyway I can remove some of them so I can get some space back ? > > > > I am using mysql -4.0.13 on Solaris 8 > > > > -- > > Asif Iqbal > > PGP Key: 0xE62693C5 KeyServer: pgp.mit.edu > > There's no place like 127.0.0.1 > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
