Row before and after?
I'm trying to find the first row before and the first row after a specific row. Essentially I want to do these two queries, and get each row. SELECT * FROM Users WHERE UserId = userId ORDER BY UserId DESC LIMIT 1; SELECT * FROM Users WHERE UserId = userId ORDER BY UserId LIMIT 1; Is there any way to combine this into one query? OFFSET doesn't allow a negative number, which is essentially what I want. Thanks, Waynn
Re: About innodb's max_rows attribute.
I'm sorry to mistake the wrong storage engine. On Wed, Nov 12, 2008 at 3:52 PM, Moon's Father [EMAIL PROTECTED]wrote: Hi. I got an error when I use alter statement to modify an innodb's table structure. The error no is 1114 and the detail information is ERROR 1114 (HY000): The table '#sql-4c0_1' is full. Here is my table's structure. mysql show create table t1; +---+--- ---+ | Table | Create Table | +---+--- ---+ | t1| CREATE TABLE `t1` ( `a` int(11) NOT NULL AUTO_INCREMENT, `message` char(20) DEFAULT NULL, PRIMARY KEY (`a`) ) ENGINE=MyISAM AUTO_INCREMENT=9437185 DEFAULT CHARSET=latin1 MAX_ROWS=100 | +---+--- ---+ 1 row in set (0.02 sec) I just want to know how to affect a innodb table's behavior when I added a max_rows option to it. I read the document but she said this option didn't matter. mysql alter table t1 modify message varchar(20) not null; ERROR 1114 (HY000): The table '#sql-4c0_1' is full mysql alter table t1 max_rows = 10; Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql alter table t1 modify message varchar(20) not null; Query OK, 4437184 rows affected (1 min 32.93 sec) Records: 4437184 Duplicates: 0 Warnings: 0 -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: Row before and after?
Whoops, just realized I made a mistake in the examples. What I'm really looking for is these two queries: SELECT * FROM Users WHERE UserId *userid*; SELECT * FROM Users WHERE UserId *userid*; Waynn On Wed, Nov 12, 2008 at 12:14 AM, Waynn Lue [EMAIL PROTECTED] wrote: I'm trying to find the first row before and the first row after a specific row. Essentially I want to do these two queries, and get each row. SELECT * FROM Users WHERE UserId = userId ORDER BY UserId DESC LIMIT 1; SELECT * FROM Users WHERE UserId = userId ORDER BY UserId LIMIT 1; Is there any way to combine this into one query? OFFSET doesn't allow a negative number, which is essentially what I want. Thanks, Waynn
Re: Row before and after?
Select the UserId one less, and then ORDER ASC LIMIT 3. Assuming your UserId's are sequential, it's easy, given userID X SELECT * FROM Users WHERE UserId = X-1 ORDER BY UserId ASC LIMIT 3; If they're not sequential due to deletions, etc, it becomes a bigger problem. You could do a subquery, but that would only be marginally faster than two queries. Sorry if I'm not more creative in the morning. :) -Micah On 11/12/2008 01:10 AM, Waynn Lue wrote: Whoops, just realized I made a mistake in the examples. What I'm really looking for is these two queries: SELECT * FROM Users WHERE UserId *userid*; SELECT * FROM Users WHERE UserId *userid*; Waynn On Wed, Nov 12, 2008 at 12:14 AM, Waynn Lue [EMAIL PROTECTED] wrote: I'm trying to find the first row before and the first row after a specific row. Essentially I want to do these two queries, and get each row. SELECT * FROM Users WHERE UserId = userId ORDER BY UserId DESC LIMIT 1; SELECT * FROM Users WHERE UserId = userId ORDER BY UserId LIMIT 1; Is there any way to combine this into one query? OFFSET doesn't allow a negative number, which is essentially what I want. Thanks, Waynn
Re: Row before and after?
Micah, I'm trying to find the first row before and the first row after a specific row Here's one way: drop table if exists t; create table t(userid int, data int); insert into t values(1,10),(3,20),(6,30),(8,50),(10,60), (13,80); -- retrieve rows just before and just after userid=8: select t.* from t join ( select (select max(userid) from t where userid8) as prev, (select min(userid) from t where userid8) as next from t where userid=8 ) a on t.userid=a.prev or t.userid=a.next; ++--+ | userid | data | ++--+ | 6 | 30 | | 10 | 60 | ++--+ PB - Micah Stevens wrote: Select the UserId one less, and then ORDER ASC LIMIT 3. Assuming your UserId's are sequential, it's easy, given userID X SELECT * FROM Users WHERE UserId = X-1 ORDER BY UserId ASC LIMIT 3; If they're not sequential due to deletions, etc, it becomes a bigger problem. You could do a subquery, but that would only be marginally faster than two queries. Sorry if I'm not more creative in the morning. :) -Micah On 11/12/2008 01:10 AM, Waynn Lue wrote: Whoops, just realized I made a mistake in the examples. What I'm really looking for is these two queries: SELECT * FROM Users WHERE UserId *userid*; SELECT * FROM Users WHERE UserId *userid*; Waynn On Wed, Nov 12, 2008 at 12:14 AM, Waynn Lue [EMAIL PROTECTED] wrote: I'm trying to find the first row before and the first row after a specific row. Essentially I want to do these two queries, and get each row. SELECT * FROM Users WHERE UserId = userId ORDER BY UserId DESC LIMIT 1; SELECT * FROM Users WHERE UserId = userId ORDER BY UserId LIMIT 1; Is there any way to combine this into one query? OFFSET doesn't allow a negative number, which is essentially what I want. Thanks, Waynn No virus found in this incoming message. Checked by AVG - http://www.avg.com Version: 8.0.175 / Virus Database: 270.9.0/1777 - Release Date: 11/9/2008 9:53 AM
Single query possible?
Hi there, I'm just trying to solve a little problem: There are two tables (simplified): CREATE TABLE article (ID int, Title varchar, ...other_data ); CREATE TABLE author (ID int,name varchar,article_ID int,...other_data ); Now I have a lot of articles. They have one or more autors, e.g. INSERT INTO article (ID,Title) VALUES (123,'test article'), (124,'another article'), (125,'third article'); INSERT INTO author (ID,name,article_ID) VALUES (1,'peter', 123),(2,'paul', 123),(3,'mary',123); INSERT INTO author (ID,name,article_ID) VALUES (4,'peter', 124),(5,'paul',124); INSERT INTO author (ID,name,article_ID) VALUES (6,'peter', 125),(7,'mary',125),(8,'tom',125); I try to build a query to do the following: find all articles where 'peter' and 'mary' are among the authors; or find all articles where 'peter' is an author but 'tom' not; etc. If there would be no possibility to do that, how could I redesign my tables? Best regards, Frank -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Row before and after?
In the last episode (Nov 12), Waynn Lue said: I'm trying to find the first row before and the first row after a specific row. Essentially I want to do these two queries, and get each row. SELECT * FROM Users WHERE UserId = userId ORDER BY UserId DESC LIMIT 1; SELECT * FROM Users WHERE UserId = userId ORDER BY UserId LIMIT 1; Is there any way to combine this into one query? OFFSET doesn't allow a negative number, which is essentially what I want. How about joining them with a UNION? Two queries for the round-trip cost of one! -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Server Setup Question
Hello all, I was wondering if anyone had any good insight into running the 32 bit and 64 bit versions of MySQL? We are going to be using a replication setup within my organization very shortly. We intend to a have at least one master (writable) DB and several (let's say 3 for this excersise ) read-only DB's. One suggestion that I got was to use 64 bit version of MySQL so that we can make better use of our servers memory as we are using servers that have 16 - 32 GB of RAM. Does anyone have any info on whether or not using the 64 Bit version is a good idea given the setup described above? Would I need to run the 64 bit version on all the servers or just the master, etc? Any help would be great. Thanks in advance, Shain -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Server Setup Question
Thanks a lot for all the infolooks like we will put the 64 bit plan into place. Does anyone know of any good links to information on tuning Linux before a MySQL install? I see that there are lot of MySQL tuning guides...but I assume there are things I can do to Linux that will help with performance as well... Thanks, Shain Daniel P. Brown wrote: On Wed, Nov 12, 2008 at 12:35 PM, Shain Miley [EMAIL PROTECTED] wrote: Hello all, I was wondering if anyone had any good insight into running the 32 bit and 64 bit versions of MySQL? We are going to be using a replication setup within my organization very shortly. We intend to a have at least one master (writable) DB and several (let's say 3 for this excersise ) read-only DB's. [snip!] Would I need to run the 64 bit version on all the servers or just the master, etc? Any help would be great. I would highly recommend running the 64-bit version on all systems if it's feasible from an infrastructure standpoint. One of my customers has a cluster that I manage where the RAM ranges from 16-24GB per machine, and I have 64-bit setups on each. They use replication as well, for the record. There may be some issues with read/write/seek times on a 32-bit machine as opposed to a 64-bit. The 32-bit may seem to lag, which can cause issues with replication under heavy loads. And, of course, filesizes and memory barriers do exist (and are being rather easily-reached now). However, if you're strictly asking about how it interfaces from one MySQL server to the next, it's no problem. MySQL couldn't care less if it's compiled for i586 or x86_64; that's only in how it relates to the OS on which it's installed, not how it interacts with sibling systems. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Server Setup Question
I have no links but the best way of tuning linux is to only install what you actually need. Standard installer like to install all kinds of deamons and other programms you might not need on db server ... On 11/12/08 2:20 PM, Shain Miley [EMAIL PROTECTED] wrote: Thanks a lot for all the infolooks like we will put the 64 bit plan into place. Does anyone know of any good links to information on tuning Linux before a MySQL install? I see that there are lot of MySQL tuning guides...but I assume there are things I can do to Linux that will help with performance as well... Thanks, Shain Daniel P. Brown wrote: On Wed, Nov 12, 2008 at 12:35 PM, Shain Miley [EMAIL PROTECTED] wrote: Hello all, I was wondering if anyone had any good insight into running the 32 bit and 64 bit versions of MySQL? We are going to be using a replication setup within my organization very shortly. We intend to a have at least one master (writable) DB and several (let's say 3 for this excersise ) read-only DB's. [snip!] Would I need to run the 64 bit version on all the servers or just the master, etc? Any help would be great. I would highly recommend running the 64-bit version on all systems if it's feasible from an infrastructure standpoint. One of my customers has a cluster that I manage where the RAM ranges from 16-24GB per machine, and I have 64-bit setups on each. They use replication as well, for the record. There may be some issues with read/write/seek times on a 32-bit machine as opposed to a 64-bit. The 32-bit may seem to lag, which can cause issues with replication under heavy loads. And, of course, filesizes and memory barriers do exist (and are being rather easily-reached now). However, if you're strictly asking about how it interfaces from one MySQL server to the next, it's no problem. MySQL couldn't care less if it's compiled for i586 or x86_64; that's only in how it relates to the OS on which it's installed, not how it interacts with sibling systems. - Confidentiality Notice: The following mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information. The recipient is responsible to maintain the confidentiality of this information and to use the information only for authorized purposes. If you are not the intended recipient (or authorized to receive information for the intended recipient), you are hereby notified that any review, use, disclosure, distribution, copying, printing, or action taken in reliance on the contents of this e-mail is strictly prohibited. If you have received this communication in error, please notify us immediately by reply e-mail and destroy all copies of the original message. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SqlYog question
I'm trying to get used to visually mapping out my db design using the SqlYog SchemaDesigner. Once I have the PK/FK setup, how can I export that design to SQL code to create the tables, etc?? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Server Setup Question
[EMAIL PROTECTED] (Shain Miley) writes: Hello all, I was wondering if anyone had any good insight into running the 32 bit and 64 bit versions of MySQL? We are going to be using a replication setup within my organization very shortly. We intend to a have at least one master (writable) DB and several (let's say 3 for this excersise ) read-only DB's. One suggestion that I got was to use 64 bit version of MySQL so that we can make better use of our servers memory as we are using servers that have 16 - 32 GB of RAM. Yes, use the x86_64 bit version as you won't be limited in memory by the 32-bit architecture. MySQL seems to work pretty well with the 32-bit version but using it with more than 4GB of RAM is going to be a problem. The 64-bit version doesn't have any trouble with 32GB (not tried more). Simon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Row before and after?
-Original Message- From: Waynn Lue [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 12, 2008 3:14 AM To: MySQL List Subject: Row before and after? I'm trying to find the first row before and the first row after a specific row. Essentially I want to do these two queries, and get each row. SELECT * FROM Users WHERE UserId = userId ORDER BY UserId DESC LIMIT 1; SELECT * FROM Users WHERE UserId = userId ORDER BY UserId LIMIT 1; Is there any way to combine this into one query? OFFSET doesn't allow a negative number, which is essentially what I want. [JS] What do you mean by the row before and the row after? Are you going by userId, or by physical row position (essentially undefined)? Would SELECT * FROM Users WHERE UserID = originalID - 1 OR UserID = originalID + 1; do what you want? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Server Setup Question
On Wed, Nov 12, 2008 at 12:35 PM, Shain Miley [EMAIL PROTECTED] wrote: Hello all, I was wondering if anyone had any good insight into running the 32 bit and 64 bit versions of MySQL? We are going to be using a replication setup within my organization very shortly. We intend to a have at least one master (writable) DB and several (let's say 3 for this excersise ) read-only DB's. [snip!] Would I need to run the 64 bit version on all the servers or just the master, etc? Any help would be great. I would highly recommend running the 64-bit version on all systems if it's feasible from an infrastructure standpoint. One of my customers has a cluster that I manage where the RAM ranges from 16-24GB per machine, and I have 64-bit setups on each. They use replication as well, for the record. There may be some issues with read/write/seek times on a 32-bit machine as opposed to a 64-bit. The 32-bit may seem to lag, which can cause issues with replication under heavy loads. And, of course, filesizes and memory barriers do exist (and are being rather easily-reached now). However, if you're strictly asking about how it interfaces from one MySQL server to the next, it's no problem. MySQL couldn't care less if it's compiled for i586 or x86_64; that's only in how it relates to the OS on which it's installed, not how it interacts with sibling systems. -- /Daniel P. Brown http://www.parasane.net/ [EMAIL PROTECTED] || [EMAIL PROTECTED] Ask me about our current hosting/dedicated server deals! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
com_* status variables seem to reset in mysql 5
I have been trying to compute query cache utilization in mysql 5 but cannot because the com_select status variable is always 1 when I start a new mysql session. This probably holds for all the com_* variables and maybe others, but I've only been working with com_select. They're supposed to be cumulative and reset only when you explicitly reset status or bounce the server. Here's an example, showing the tail end of a test mysql session showing the value of com_select when I exited, and the value a few seconds later when I began a new mysql session. This was on my own test server, no one else was on to reset status. It repeats every time I try it: ### BEGIN SESSION mysql show status like 'com_select'; -- show status like 'com_select' -- +---+---+ | Variable_name | Value | +---+---+ | Com_select| 4 | +---+---+ 1 row in set (0.01 sec) mysql quit Bye mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 94774 Server version: 5.0.45-community-log MySQL Community Edition (GPL) Reading history-file /home/jlyons/.mysql_history Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql show status like 'com_select'; -- show status like 'com_select' -- +---+---+ | Variable_name | Value | +---+---+ | Com_select| 1 | +---+---+ 1 row in set (0.01 sec) mysql ### END SESSION Note how com_select is 4 in the first session, then is reset to 1. I tried this on Linux RHEL, Linux RH 5 community (as shown here) and a Windows mysql 5 platform. Heres' the script I ran: drop table if exists t; create table t (x serial); # put some data in insert into t values (null); insert into t values (null); insert into t values (null); insert into t values (null); # create some selects select * from t where x = 1; select * from t where x = 2; select * from t where x = 3; select * from t where x = 4; select * from t where x = 1; select * from t where x = 1; select * from t where x = 1; select * from t where x = 1; select * from t where x = 1; select * from t where x = 1; show status like 'qcache_hits'; show status like 'com_select'; When I ran the same script on a Windows mysql 4 version, the value of com_select persisted over the login, which is what it should. Is this a bug in mysql 5? Is something set incorrectly in my config file that would cause this (I can't find anything)? Thanks for any help. -- Jim Lyons Web developer / Database administrator http://www.weblyons.com
Incorrect value for default-time-zone
Hi. I got an error message when I adjust the system variable named default-time-zone.I don't how to adjust this variable. Hope to get help here.Thanks. Here is my error log. 081113 15:24:43 InnoDB: Started; log sequence number 0 423199200 081113 15:24:43 [Note] Recovering after a crash using mysql-bin 081113 15:24:43 [Note] Starting crash recovery... 081113 15:24:43 [Note] Crash recovery finished. 081113 15:24:43 [ERROR] Fatal error: Illegal or unknown default time zone 'GMT +08:00' 081113 15:26:57 InnoDB: Started; log sequence number 0 423199200 081113 15:26:57 [Note] Recovering after a crash using mysql-bin 081113 15:26:57 [Note] Starting crash recovery... 081113 15:26:57 [Note] Crash recovery finished. 081113 15:26:58 [ERROR] Fatal error: Illegal or unknown default time zone '08:00' 081113 15:27:16 InnoDB: Started; log sequence number 0 423199200 081113 15:27:16 [Note] Recovering after a crash using mysql-bin 081113 15:27:16 [Note] Starting crash recovery... 081113 15:27:16 [Note] Crash recovery finished. 081113 15:27:16 [ERROR] Fatal error: Illegal or unknown default time zone 'GMT' -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
