BUG: Innodb, Assertion failure in file log0log.c with 4.0.!7
There is a MySQl/Innodb bug with Mysql-Max 4.0.17 on mac osx 10.3 panther. A google search showed a similar problem with another platform (I think it was windows). I am running 4.0.14 without any problems. After upgrading to 4.0.17, copying the data directory from 4.0.14 to 4.0.17 and then starting mysqld I get: --- 031222 22:05:51 mysqld started InnoDB: Error: log file group too small for innodb_thread_concurrency 031222 22:05:51 InnoDB: Assertion failure in thread 2684396012 in file log0log.c line 856 InnoDB: Failing assertion: log_calc_max_ages() InnoDB: We intentionally generate a memory trap. InnoDB: Send a detailed bug report to [EMAIL PROTECTED] mysqld got signal 10; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. - Reverting back to 4.0.14 works fine and I get no error messages. Best regards, --j __ Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing. http://photos.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
JDBC driver: Buggy for DatabaseMetaData.getImported/Exported keys ?
Hi: I am using Connector/J 3.0.2 against MySQL 3.23.52-max. My default table type is set to InnoDB (since I always use innodb). The following methods are acting strangely: - DatabaseMetaData.getImportedKeys() - DatabaseMetaData.getExportedKeys() Often these methods return partial information and sometimes no information. Here's a simple test harness that illustrates the issue. --- set up test tables [cut-n-paste this] CREATE TABLE UserStatus ( id TINYINT NOT NULL AUTO_INCREMENT, typeCHAR(50), PRIMARY KEY (id) ); CREATE TABLE UserType ( id TINYINT NOT NULL AUTO_INCREMENT, typeCHAR(20) NOT NULL, PRIMARY KEY (id) ); CREATE TABLE Locations ( id INTEGER NOT NULL AUTO_INCREMENT, name VARCHAR(30), department VARCHAR(50), PRIMARY KEY (id) ); CREATE TABLE Users ( id INTEGER AUTO_INCREMENT, locationID INTEGER DEFAULT NULL, userTypeID TINYINT NOT NULL, statusID TINYINT NOT NULL, username VARCHAR(50) NOT NULL UNIQUE, PRIMARY KEY (id), FOREIGN KEY (statusID) REFERENCES UserStatus(id), INDEX(statusID) , FOREIGN KEY (locationID) REFERENCES Locations(id), INDEX(locationID) , FOREIGN KEY (userTypeID) REFERENCES UserType(id), INDEX(userTypeID) ); CREATE TABLE Groups ( idINTEGER NOT NULL AUTO_INCREMENT, name VARCHAR(50) NOT NULL, PRIMARY KEY (id) ); CREATE TABLE UsersAndGroups( userID INTEGER NOT NULL, groupIDINTEGER NOT NULL, dateAdded TIMESTAMP, PRIMARY KEY (userID, groupID) ); end test tables - Create the above tables in a test database. Make sure you specify innodb as the default table type. (otherwise hack the above to add tabletype=innodb for each table). Now, after connecting to this database, call getImportedKeys and getExportedKeys thru your java code. Specify the Users table. Findings: 1. ImportedKeys Expected-to-see: statusID, locationID, userTypeID Actual: statusID 2. ExportedKeys Expected-to-see: UsersAndGroups Actual: empty I am trying to write an automated tool that examines a database and generated a java dbobjects layer. It's quite possible that I am missing something here but it's also possible that the driver is buggy because the expected and actual values diverge significantly. Best regards, [EMAIL PROTECTED] __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: JDBC J/Connect driver is seriously slow against InnoDB
Mark: Once of the great things about MySQL is the prompt attention and response on this list. Thanks for replying to this email so quickly. Read on... --- Mark Matthews [EMAIL PROTECTED] wrote: j.random.programmer wrote: Hi: Thought I'd mention this (using the latest dev J/connect): A) DatabaseMetaData.getColumns() does not return the last 4 columns (SCOPE_*) but it should according to the API spec I'll look into fixing this right away. You should write Sun as well, as their JDBC compliance testsuite doesn't look for this :( It's kind of a moot point anyway because those SCOPE_* columns don't really do anything as far as I can tell. But hey, you may as well be complete. B) InnoDB, on their benchmark page, say that inserting 100,000 rows into the DB is about 5 seconds. http://www.innodb.com/bench.html Can you point out where it says this? When I look at the benchmark page, I see that 100,000 inserts take 25 seconds, which JDBC can pretty much match (read on for more). Quoted (from the page in question): InnoDBMyISAM 100 000 inserts25 s. 40 s. 100 000 selects on primary key57 s. 58 s. 100 000 selects on secondary key 68 s. 95 s. True. I mistakenly mis-remembered this figure. The right figure *is* 25s. I am finding this to be more like 300-400 seconds. Try this: I think something is broken with your network, or your machine, read on for more ... I would double-check your network. I just ran your code here on my desktop runnin Linux, which is known not to have the fastest java support, to my test server (100 mbit private network), and it takes 30 seconds, which is 5 seconds longer than 100,000 inserts using DBI (which is using native code to access the database, btw), but is not long enough for HotSpot to actually optimize the code, either. You are right. It looks like some sort of network problem. I tried this on a seperate set of 2 machines both on a different network. The driver was running on a windows 2000 box (dual 1 GB) hitting the mysql box (dual xeon, 1 GB, linux) over a 100 Mbit line. This test now took about 55 seconds. I have a feeling that switching to 4.x would make things even faster, seeing as you are getting 30 seconds with a slower box. Are you using 4.x internally for your testing ? Best regards, --j __ Do you Yahoo!? HotJobs - Search new jobs daily now http://hotjobs.yahoo.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
JDBC J/Connect driver is seriously slow against InnoDB
Hi:
Thought I'd mention this (using the latest dev
J/connect):
A)
DatabaseMetaData.getColumns() does not return the last
4 columns (SCOPE_*) but it should according to the
API spec
B)
InnoDB, on their benchmark page, say that inserting
100,000 rows into the DB is about 5 seconds.
http://www.innodb.com/bench.html
I am finding this to be more like 300-400 seconds.
Try this:
CREATE TABLE T1
(A INT NOT NULL AUTO_INCREMENT, B INT, PRIMARY KEY(A))
TYPE=INNODB;
CREATE TABLE T2
(A INT NOT NULL, B INT, PRIMARY KEY(A)) TYPE=INNODB;
Now run the java driver (source shown at the end of
this
message).
This takes a long time. The innodb bench URL mentioned
above uses perl DBI has a test driver, the JDBC
*should* be as fast if I am not missing anything (but
it's not). Once it's finally done, if you now say
(from
the mysql client):
mysql insert into T2 select * from T1;
Query OK, 107825 rows affected (5.23 sec)
Records: 107825 Duplicates: 0 Warnings: 0
Note, this takes about 5 seconds, which shows that
the slowdown is not at the DB level but at the JDBC
driver level (mysql client even sets auto commit
to true by default, and it still takes 5 seoonds).
So why does the JDBC driver take so long ? I am
accessing the mysql machine over a private 100Mbps
connection so I don't think it's the network either.
Best regards,
--j
java driver --
import java.sql.*;
import java.util.*;
import java.io.*;
public class insertTiming {
/* Change these as appropriate */
static String user=CHANGE_ME;
static String password=CHANGE_ME;
static String url=CHANGE_ME;
public static void main(String[] args) throws
Exception
{
Class.forName(com.mysql.jdbc.Driver);
Connection con =
DriverManager.getConnection(url,user,password);
Statement stmt = con.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
/*
Make sure table T1 has been created prior to this
SQL command:
CREATE TABLE T1 (A INT NOT NULL AUTO_INCREMENT, B INT,
PRIMARY KEY(A)) TYPE=INNODB;
*/
con.setAutoCommit(false);
String sql =
INSERT INTO T1 VALUES (null, '1234567890');
for (int n = 0; n 10; n++) {
stmt.execute(sql);
}
con.commit();
} //~main
} //~class
--
__
Do you Yahoo!?
HotJobs - Search new jobs daily now
http://hotjobs.yahoo.com/
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: InnoDB 3.23.52, foreign keys and update/cascade problem
Hi all: I'm seeing a strange problem updating a field if that field is referenced as a FK in another table. Consider: CREATE TABLE A ( id INTEGER NOT NULL AUTO_INCREMENT, name VARCHAR(20), PRIMARY KEY (id) ); CREATE TABLE B ( id INTEGER NOT NULL AUTO_INCREMENT, AID INTEGER, name VARCHAR(20), PRIMARY KEY (id), INDEX(AID) , FOREIGN KEY (AID) REFERENCES A (id) ON UPDATE CASCADE ); mysql insert into A values (null, 'one'), (null, 'two'); mysql select * from A; ++--+ | id | name | ++--+ | 1 | one | | 2 | two | ++--+ 2 rows in set (0.00 sec) mysql insert into B values (null, 1, 'hello'), (null, 1, 'world'); mysql select * from B; ++--+---+ | id | AID | name | ++--+---+ | 1 |1 | hello | | 2 |1 | world | ++--+---+ 2 rows in set (0.00 sec) AND HERE IS THE PROBLEM: mysql update A set id = 5 where id = 1; ERROR 1217: Cannot delete a parent row: a foreign key constraint fails Note, the innodb manual says: If you defined ON DELETE CASCADE or SET NULL and updated the referenced key in the parent row, InnoDB deleted or updated the child row. This is now changed to conform to SQL-92: you get the error 'Cannot delete parent row'. --- NOTE, There is no 'ON DELETE' constraint in my example so what on earth is going on ? Can anyone explain why an update is not getting cascaded ? I mean, that's the whole POINT of the update constraint. What am I doing wrong ? Best regards, --j __ Do you Yahoo!? New DSL Internet Access from SBC Yahoo! http://sbc.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
InnoDB 3.23.52, foreign keys and update/cascade problem
Hi all: I'm seeing a strange problem updating a field if that field is referenced as a FK in another table. Consider: CREATE TABLE A ( id INTEGER NOT NULL AUTO_INCREMENT, name VARCHAR(20), PRIMARY KEY (id) ); CREATE TABLE B ( id INTEGER NOT NULL AUTO_INCREMENT, AID INTEGER, name VARCHAR(20), PRIMARY KEY (id), INDEX(AID) , FOREIGN KEY (AID) REFERENCES A (id) ON UPDATE CASCADE ); mysql insert into A values (null, 'one'), (null, 'two'); mysql select * from A; ++--+ | id | name | ++--+ | 1 | one | | 2 | two | ++--+ 2 rows in set (0.00 sec) mysql insert into B values (null, 1, 'hello'), (null, 1, 'world'); mysql select * from B; ++--+---+ | id | AID | name | ++--+---+ | 1 |1 | hello | | 2 |1 | world | ++--+---+ 2 rows in set (0.00 sec) AND HERE IS THE PROBLEM: mysql update A set id = 5 where id = 1; ERROR 1217: Cannot delete a parent row: a foreign key constraint fails Note, the innodb manual says: If you defined ON DELETE CASCADE or SET NULL and updated the referenced key in the parent row, InnoDB deleted or updated the child row. This is now changed to conform to SQL-92: you get the error 'Cannot delete parent row'. --- NOTE, There is no 'ON DELETE' constraint in my example so what on earth is going on ? Can anyone explain why an update is not getting cascaded ? I mean, that's the whole POINT of the update constraint. What am I doing wrong ? Best regards, --j __ Do you Yahoo!? New DSL Internet Access from SBC Yahoo! http://sbc.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
A question about mm.mysql and the GPL
Hi: Firstly, I'd like to congratulate both MySQL AB and Mr. Mathews on making the JDBC driver an officialy supported download. I have a query though. Newer versions of the mm.mysql driver (now called connector/j) will be released *not* under the LGPL but under the GPL. Now if I am using the mysql database as a web site back end, the web site is totally independent of the database and cannot be considered a derived work of the mysql database. (keep in mind, the notion of derived work is *very* hazy under the GPL). But if the JDBC driver itself is GPL'ed and I am using the driver in my web site's jsp/servlet code, then it's concievable that the entire web site's contents and code become GPL'ed too. Of course, I wouldn't want to GPL by web site/code just because I am using a driver (keep in mind, using, *not* extending or customizing). Well, the question is, does in fact the use of the JDBC driver GPL my java server pages ? That is, does the entire web site become a derived work ? Any offical response from either Mr. Mathews of MySQL AB would be appreciated... Best regards, --j __ Do You Yahoo!? HotJobs - Search Thousands of New Jobs http://www.hotjobs.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
mysql daemon started asynchronously prevents logout - why ?
Hi all: I am running mysql-max 3.23.51 on linux 2.4.x and my shell is bash. The 'huponexit' shell option is off. The mysqld (mysql-max daemon) is probably not important for this discussion, any other daemon would do as well. Firstly, I can start mysqld (or other programs) asynchronously by saying: # mysqld [1] 858 # bin/mysqld: ready for connections # jobs [1]+ Running bin/mysqld # It's quite interesting that if I now say 'fg', the process comes up in the foreground but after that I cannot type anything (ctrl-z or bg have no affect). The daemon is trapping everything. But let's say, I don't say 'fg', instead, I say logout. # logout Well, I am now stuck, am *not* logged out, and the shell does not accept any more commands. I can only logout if I kill the mysqld process from another shell (in another console). At *that* point, I see some 'mysql shutting down' messages and then I finally logout. I see the exact same behavior even if I originally start the daemon by saying: # nohup mysqld I was wondering, is this normal behavior ? I mean if I have a async process running in the background, (started either with '' or 'nohup'), then I should be able to exit the shell by saying 'logout' right ? How can a asynch daemon prevent me from logging out ? 'logout' should kill mysqld (whether that's desirable is another story) but why/how does mysqld prevent a logout in the first place ? Anyone have an explanation ? Best regards, --j __ Do You Yahoo!? Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Attn: MySQL AB: we need 3.23.5x NOW !
If any folks from MySQL AB are reading this: I need to go into production *today* with 3.23.5x. I am using InnoDB heavily (and need the newer fixes). 3.23.50 hasn't even been released yet (the pre-release version is not stable) and based on posts on this list, we know that there are several bug fixes and versions after 3.23.50 (for example 3.23.52 is rumored to exist). Can someone *please* pick up the dropped ball on the release schedule and run with it ? It's not such a big deal for ISAM table users but it's a very big deal for InnoDB tables... Best regards, --j __ Do You Yahoo!? Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Attn: MySQL AB: we need 3.23.5x NOW !
We're running a custom-compiled 3.23.51 (or .52-pre, [..] Why not build your own? Hmm. The latest source on the pre-release page is 3.23.50 and it's not clear whether the stability issue is a bug in the code, the compiler or the runtime libs. I can send you a binary that works for us. But you'd really need to do some testing locally. Very nice of you to offer. I may have to take you up on it if MySQL AB does not release a stable version in the next month or so. Right now, I've already deployed with 3.23.50 unstable. I'll wait another month and see what happens. I'm guessing that you're not a paid support customer of MySQL? Nope. I'm quite cheap that way :-] Best regards, --j __ Do You Yahoo!? Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
gcc for newer mysql versions - what's needed exactly ?
Hi all: Can someone (who knows this sort of thing) give a layman's introduction to the interplay between various gcc versions and various mysql versions (3.49, 3.50, 4.01 etc) ? I have some linux boxes and don't really want to have to install/use gcc 3.x (because then I have the hassle of multiple gcc's on my system, as I need 2.95.x for other purposes). I don't really know gcc too well so if I download the *binary* versions of the upcoming mysql 3.50 or even 4.x, what exactly would I have to install on my system, gcc-wise ? Thanks and best regards, [EMAIL PROTECTED] __ Do You Yahoo!? LAUNCH - Your Yahoo! Music Experience http://launch.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Innodb tables lose foreign keys after creating an index...
--- Heikki Tuuri [EMAIL PROTECTED] wrote: Rick, sorry, it is not mentioned in the manual that MySQL performs a CREATE INDEX by doing an ALTER TABLE. And ALTER TABLE has the feature (= documented bug) that it removes foreign key definitions. Heikki: Is there a fix planned for the alter table/foreign key issue ? Best regards, [EMAIL PROTECTED] __ Do You Yahoo!? Yahoo! Sports - sign up for Fantasy Baseball http://sports.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
innodb, auto increment columns and gaps in the sequence - how to handle ?
Hi all: I have various mysql tables (innodb type) where I am inserting values in auto increment columns. The value inserted into the auto increment columns is null which automatically means that the table handler inserts the next higher value in that column. Problem is, many of these updates are transactional and sometimes these transactions are rolled back. This leaves gaps in the auto increment column (the auto increment counter does not rollback even if the transaction is rolled back). This looks unsightly and it also means I have to unnecessarily create auto increment columns with data sizes much larger than they have to be. So for example, if I get 10,000 inserts+rollbacks, my auto increment column will be 10,001 the next time a row is inserted (even if there was only 1 row to begin with). What is the best way to prevent such gaps from happening, especially in transactional tables like innodb ? Any practical suggestions off the top of anyone's head ? (Is everyone here declaring their auto increment columns as int or bigint to get around the fact that auto increment is *always* monotonically increasing, regardless of transaction rollback ?) Best regards, [EMAIL PROTECTED] __ Do You Yahoo!? Send FREE Valentine eCards with Yahoo! Greetings! http://greetings.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
innodb, auto increment columns and gaps in the sequence - how to handle ?
Hi all: I have various mysql tables (innodb type) where I am inserting values in auto increment columns. The value inserted into the auto increment columns is null which automatically means that the table handler inserts the next higher value in that column. Problem is, many of these updates are transactional and sometimes these transactions are rolled back. This leaves gaps in the auto increment column (the auto increment counter does not rollback even if the transaction is rolled back). This looks unsightly and it also means I have to unnecessarily create auto increment columns with data sizes much larger than they have to be. So for example, if I get 10,000 inserts+rollbacks, my auto increment column will be 10,001 the next time a row is inserted (even if there was only 1 row to begin with). What is the best way to prevent such gaps from happening, especially in transactional tables like innodb ? Any practical suggestions off the top of anyone's head ? (Is everyone here declaring their auto increment columns as int or bigint to get around the fact that auto increment is *always* monotonically increasing, regardless of transaction rollback ?) Best regards, [EMAIL PROTECTED] __ Do You Yahoo!? Send FREE Valentine eCards with Yahoo! Greetings! http://greetings.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
SQL/Design -- how to use sub entities/type discriminators ?
Hi all:
Consider a hierarchy of users. All users have certain
properties (they all have a name and date-of-birth).
Additionally, based on their type, users have more
properties. So a graduate user may have information
regarding the year and school of graduation, a
professor user may have information about the classes
they teach etc.
A intuitive way (for me at least) to model this is to
create a table per type and add a special type
discriminator field that points to the next special
table for that type. So, I have a User table that
holds information common to all users and then
specialized tables such as GradUser, UndergradUser
etc., that hold additional specific information about
that particular kind of user.
Table User has columns:
--userid
--name
--birthdate
--usertype
Table GradUser has columns:
--userid
--school
Table ProfessorUser has columns:
--userid
--classes
and Table UserType has 2 entries:
usertypetable description
1 GradUser type for grad
2 ProfUser type for prof's
Taking a concrete example, say one
user-record (shown vertically) looks
like:
Table: User
Column Value
userid 123
nameMolly Millions
usertype1
Table: GradUser
id 123
school Hard-Knocks
So based on the usertype ('1' in the above example), I
have to get the remaining information for Molly from
GradUser. If the usertype had been '2', I would
then
have had to get the remaining information from the
ProfUser table instead.
Note, I can't do a simply join between User and
GradUser (using 123), because I don't know the
name of the table (GradUser or ProfUser) until
I have read the usertype field in the User table.
So what I really need to do is:
-Read a record from User
-Read it's corresponding type.
-Based on the type, choose another
table and read additional information.
MySQL does not have sub-queries but does allow me to
select into temporary tables easily.
What's the most efficient SQL to read a given user's
entire information (common + specific information) ?
I
want to minimize traffic between my client and the
server so I would ideally speaking, like to do this
all
on the database side itself. Essentially I need a
if .. then type of logic on the *server*. So in
psuedo-code:
if (usertype = '1') then join user and graduser
else if (usertype = '2') then join user and profuser
etc..
Best regards,
[EMAIL PROTECTED]
__
Do You Yahoo!?
Send FREE video emails in Yahoo! Mail!
http://promo.yahoo.com/videomail/
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
