Re: auto-increment question
Now I'm confused the auto-increment number reverted back to one (1) after I truncated the tables in one of the DB's. I suppose as long as the number is unique within the objects I'm making selections, updates...ect in - who cares. On Sun, 23 Jan 2005 02:18:33 -0500, leegold [EMAIL PROTECTED] said: I have two different databases they both have an auto-increment PK field and while they are different databases with different names, they do have tables with the same names. What I find is that the auto-crement integer number remembers what it is across these databases. So if I insert in the 1st DB and the number is 24, then I insert in the 2nd DB the number will be 25. I would of thought that the number would be fresh and independent between the DBs. Could you explain? -- 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]
create indexes for temporary table
Hi,
I want to create indexes for temporary created tables in perl dbi.
The following is perl code that I tried, but perl dbi seems not allow
the syntax:
Unable to execute our query PastSales:You have an error in your SQL
syntax; check the manual that corresponds to your MySQL server version
for the right syntax to use near 'alter table tmp_pastsales add
index(salescode,basename,prodcode);' at line 1 Unable to execute our
query Sales:You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use
near 'alter table tmp_sales add index(salescode,basename,prodcode);'
at line 1
This is the actual perl code I use:
$tmp_sql = qq{alter table tmp_pastsales add
index(salescode,basename,prodcode);};
$sth_tmp = $dbh-prepare($tmp_sql) or print Unable to prep our
query:.$dbh-errstr.\n;
$sth_tmp-execute or print Unable to execute our query
PastSales:.$dbh-errstr.\n;
Thanks
Sam
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: show temporary table
Sam, You can use 'show tables' with a like clause if you prefix your temporary tables with a string such as 'tmp_'. See the manual for syntax: http://dev.mysql.com/doc/mysql/en/show-tables.html Clint From: sam wun [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: show temporary table Date: Sun, 23 Jan 2005 15:47:22 +0800 Hi, How can I see all temporary tables that created by Create Temporary Table command in mysql 5.01? thanks Sam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: create indexes for temporary table
Sam,
Remove the double quotes and semi-colon out of this statement:
$tmp_sql = qq{alter table tmp_pastsales add
index(salescode,basename,prodcode)};
Clint
From: sam wun [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Subject: create indexes for temporary table
Date: Sun, 23 Jan 2005 18:49:51 +0800
Hi,
I want to create indexes for temporary created tables in perl dbi.
The following is perl code that I tried, but perl dbi seems not allow the
syntax:
Unable to execute our query PastSales:You have an error in your SQL syntax;
check the manual that corresponds to your MySQL server version for the
right syntax to use near 'alter table tmp_pastsales add
index(salescode,basename,prodcode);' at line 1 Unable to execute our query
Sales:You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near
'alter table tmp_sales add index(salescode,basename,prodcode);' at line 1
This is the actual perl code I use:
$tmp_sql = qq{alter table tmp_pastsales add
index(salescode,basename,prodcode);};
$sth_tmp = $dbh-prepare($tmp_sql) or print Unable to prep our
query:.$dbh-errstr.\n;
$sth_tmp-execute or print Unable to execute our query
PastSales:.$dbh-errstr.\n;
Thanks
Sam
--
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]
Query Help
I have two tables: DB 1: Table A: Userid: Dept: DB 2: Table B: Userid: Dept: Location: How would I query from DB 1 Table A for the Dept if I want to use that value for DB 2’s Dept? Both DB’s and tables have the same Userid. Does not work: Use 2; Select A.Dept from A where B.Userid = ‘sam’; I have to do the query using db 2. I hope this is not to dump of a question. Thanks. Thanks, Rob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: force configure to not use -lcrypt
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Alex S Moore wrote: | On Solaris 8, how can I force configure to not use /usr/lib/libcrypt.so | without renaming that file? I changed config.h to undefine | HAVE_LIBCRYPT, but configure just puts it back to defined and changes | the Makefiles to include -lcrypt. | I'm curious why you're interested in removing -lcrypt? Do you intend to replace it with another algorithm? Or are you just trying to minimize size/processing, etc.? Wren -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.0 (Darwin) iD8DBQFB89CMA/qR4Uok1vQRAmEAAKD2FaVYFeE7+ed8seHhKIo/I/C5dgCbBmoz DZ3WDxMxVfA6/JafzJH7rUU= =WSsP -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
system requirements for MySQL db requiring large selects
We are looking for buying a new server for running MySQL database. The database is around 50-70G and individual tables run to 5 - 15 GB. There wont be any frequent updates instead we need maximum select performance. There will be multiple table joins to perform our query. I hope your experience will help us find a suitable server for our need. Information on what hardware to use including how many computers, processor, ram, hard drive spec would be helpful. Budget is not a constrain but performance (select) need to be high. I have heard MySQL is the best for fast and large selects, but is it worth looking at other database like Oracle? Thanks TM __ Do you Yahoo!? Yahoo! Mail - Easier than ever with enhanced search. Learn more. http://info.mail.yahoo.com/mail_250 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Help
rmck wrote: I have two tables: DB 1: Table A: Userid: Dept: DB 2: Table B: Userid: Dept: Location: How would I query from DB 1 Table A for the Dept if I want to use that value for DB 2's Dept? Both DBs and tables have the same Userid. Does not work: Use 2; Select A.Dept from A where B.Userid = 'sam'; I have to do the query using db 2. I hope this is not to dump of a question. Thanks. Thanks, Rob I don't entirely understand what you really want, but, in general, you put the db name in front of the table name when you want to reference a table in a different db. Given your sample query, I can't see why you wouldn't simply USE 1; SELECT Dept FROM A WHERE Userid = 'Sam'; but if you are determined to stay with db 2, you can do it like this: USE 2; SELECT Dept FROM 1.A WHERE Userid = 'Sam'; Perhaps what you really want is to join the two tables. Then you'd do something like USE 2; SELECT B.Userid, 1.A.Dept FROM 1.A JOIN B ON 1.A.Userid = b.Userid; or perhaps SELECT 2.B.Userid, 1.A.Dept FROM 1.A JOIN 2.B ON 1.A.Userid = 2.B.Userid; which should work regardless of the current db. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INNER JOIN across multiple tables appear very slow.
sam wun wrote: sam wun wrote: Hi, Can anyone tell me how to optimize the following sql statement? $sql_1 = SELECT t.prodcode 'Product Code', t.prodname 'Product Name', ROUND(avg(t.salesvolume),2) 'PastSales Quantity', ROUND(avg(tt.salesvolume),2) 'Sales Quantity', ROUND(avg(t.netsales),2) 'PastSales', ROUND(avg(tt.netsales),2) 'Sales', Round(((avg(t.netsales) - avg(tt.netsales))/avg(tt.netsales))*100,2) 'SalesDiff', Round(((avg(t.salesvolume) - avg(tt.salesvolume))/avg(tt.salesvolume))*100,2) 'QtyDiff' FROM customer c INNER JOIN transaction t ON c.custcode = t.custcode AND date(t.date) = '$past_date_period_startdate' AND date(t.date) = '$past_date_period_enddate' AND c.salescode = '$salescode_param' INNER JOIN transaction tt ON c.custcode = tt.custcode AND date(tt.date) = '$input_date_period_startdate' AND date(tt.date) = '$input_date_period_enddate' AND c.salescode = '$salescode_param' AND t.prodcode = tt.prodcode INNER JOIN inventory i ON i.prodcode = t.prodcode AND i.prodcode = tt.prodcode AND i.basename = '$basename_param' WHERE i.prodname is not NULL AND i.basename is not NULL GROUP BY c.salescode, i.basename, t.prodcode ORDER BY SalesDiff desc; Thanks Sam I see several potential problems: 1) The biggest fixable problem is the use of the date() function. You should never use a function of a column value in a WHERE clause, if you can avoid it, because that prevents the use of an index on that column to help choose rows. In this case, the date() function isn't even needed, so even if there is no index on t.date, or the optimizer doesn't choose to use it, you are still forcing 4 unnecessary function calls per row. In other words, change AND date(t.date) = '$past_date_period_startdate' AND date(t.date) = '$past_date_period_enddate' to AND t.date = '$past_date_period_startdate' AND t.date = '$past_date_period_enddate' or the equivalent AND t.date BETWEEN '$past_date_period_startdate' AND '$past_date_period_enddate' 2) In general, you should'nt put things in the JOIN condition which are not part of the JOIN criteria. For example, the condition c.salescode = '$salescode_param' is a restriction on which rows in c to consider, not a condition of the JOIN to t or tt, so it belongs in the WHERE clause. 3) You have a complex GROUP BY based on a column from each table, and an ORDER BY based on a calculated value. That rules out using an index to do the grouping or ordering. Hence the dreaded Using temporary; Using filesort in your explain output. This appears unavoidable in your case. 4) You have redundant conditions on your JOIN to i. Since t.prodcode = tt.prodcode for every row of your results (because you require this in the join of t to tt), the two conditions i.prodcode = t.prodcode and i.prodcode = tt.prodcode are equivalent. The optimizer is probably smart enough to notice that, but it will have less work to do if you pick one and delete the other. 5) Because you require i.basename = '$basename_param', i.basename cannot be NULL, so there is no need for the conditrion i.basename is not NULL. 6) Are you sure you need i.prodname is not NULL? If you can be certain that all rows in inventory with non NULL prodcode and basename have non NULL prodnames, you could leave that out. I can't assume you don't need it based only on the rest of your query, so I'll leave it in, for now. Based on the above, I'd suggest you change your query to: SELECT t.prodcode 'Product Code', t.prodname 'Product Name', ROUND(avg(t.salesvolume),2) 'PastSales Quantity', ROUND(avg(tt.salesvolume),2) 'Sales Quantity', ROUND(avg(t.netsales),2) 'PastSales', ROUND(avg(tt.netsales),2) 'Sales', ROUND(((avg(t.netsales)-avg(tt.netsales))/avg(tt.netsales))*100,2) AS 'SalesDiff', ROUND(((avg(t.salesvolume)-avg(tt.salesvolume))/avg(tt.salesvolume))*100,2) AS 'QtyDiff' FROM customer c JOIN transaction t ON c.custcode = t.custcode JOIN transaction tt ON c.custcode = tt.custcode AND t.prodcode = tt.prodcode JOIN inventory i ON i.prodcode = t.prodcode WHERE c.salescode = '$salescode_param' AND t.date BETWEEN '$past_date_period_startdate' AND '$past_date_period_enddate' AND tt.date BETWEEN '$input_date_period_startdate' AND '$input_date_period_enddate' AND i.basename = '$basename_param' AND i.prodname is not NULL GROUP BY c.salescode, i.basename, t.prodcode ORDER BY SalesDiff desc; Hi, here is the result from the explain command on the query: snip +---++---+--+-+-+--+--+ | table | type | possible_keys | key | key_len | ref | rows | Extra|
Re: Error with Storage engine
You can look up error codes with perror. : perror 28 Error code 28: No space left on device You've run out of disk space. Michael sam wun wrote: Hi, I was trying to optimize a sql (for INNER JOIN) by spliting the sql into 2 temporary tables. But I got error with the following sql: mysql CREATE TEMPORARY TABLE pastsales_tab_3141604663377652915968 SELECT c.salescode,c.type, c.custcode, i.basename, i.vendorname, t.salesvolume, t.netsales, t.prodcode, i.prodname from inventory i INNER JOIN transaction t ON i.prodcode = t.prodcode AND date(t.date) = '2003-01-23' AND date(t.date) = '2004-01-23' INNER JOIN customer c ON c.custcode = t.custcode ; ERROR 1030 (HY000): Got error 28 from storage engine How can I correct this error? I musing mysql5.01 Thanks Sam. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: force configure to not use -lcrypt
On Sun, 23 Jan 2005 11:27:56 -0500 J. Wren Hunt [EMAIL PROTECTED] wrote: I'm curious why you're interested in removing -lcrypt? Do you intend to replace it with another algorithm? Or are you just trying to minimize size/processing, etc.? The call to Sun Solaris 8 and 9 functions in libcrypt.so is not working and crashes mysqld. The Sun library libcrypt.so is a symlink to libcrypt_i.so or libcrypt_d.so, depending on your setup. Correct me if I am wrong, but the use of the functions that can be called by whatever -lcrypt is needed for is also internal to mysql. I patched configure.in to comment the check for the crypt library and that at least stops the crashes. Now, I need to know if I am ok or have lost valuable function. Thanks, Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: force configure to not use -lcrypt
On Sun, 23 Jan 2005 11:27:56 -0500 J. Wren Hunt [EMAIL PROTECTED] wrote: | I'm curious why you're interested in removing -lcrypt? Do you intend to replace it with another algorithm? Or are you just trying to minimize size/processing, etc.? After further reading of what I disable, I see that I have created an unacceptable situation. 'SELECT ENCRYPT(hello);' returns NULL. So, I am back into this problem and looking for answers. The encrypt function works fine on Solaris x86 and crashes so hard on Solaris 8,9 sparc that mysqld will not run. BTW, building version 4.1.9. I had the same problem with 4.0.23a. Thanks, Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
problem starting mysqld-max
i'm having problem starting mysqld-max, when i use command line it just didn't start with no error message. I've shutdown the firewall, i'm using XP, and mysql 4.01 how can i get what have caused this it ran well before but after i install firewall utilites it began like this thanks -- Chenri J Taman Palem Lestari B18 - 19A (021) 926 68651 - Esia jadi bisa SMS -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: problem starting mysqld-max
i've restart the machine and it run ok thanx On Mon, 24 Jan 2005 07:42:41 +0700, Chenri [EMAIL PROTECTED] wrote: i'm having problem starting mysqld-max, when i use command line it just didn't start with no error message. I've shutdown the firewall, i'm using XP, and mysql 4.01 how can i get what have caused this it ran well before but after i install firewall utilites it began like this thanks -- Chenri J Taman Palem Lestari B18 - 19A (021) 926 68651 - Esia jadi bisa SMS -- Chenri J Taman Palem Lestari B18 - 19A (021) 926 68651 - Esia jadi bisa SMS -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Previous date problem
-- Forwarded message -- From: Chenri [EMAIL PROTECTED] Date: Mon, 24 Jan 2005 08:22:01 +0700 Subject: To: mysql@lists.mysql.com i have a price table no date price limit 01 1/1/05 5000 100 02 1/2/05 5010 100 03 1/3/05 5020 60 in order to input a record the price must be checked with the previous date price and limit if the price entry is consecutive every day and can use date-1 but the problem is when i want to enter a price 3 days after, like this record: no date price limit 04 1/6/05 5010 100 so it will be like this no date price limit 01 1/1/05 5000 100 02 1/2/05 5010 100 03 1/3/05 5020 60 ** 04 1/6/05 5010 100 ** there are 3 days difference but how do make a query to check for record no 04 with 03 how do i retrieved the 1/3/05 date and the price i only ha -- Chenri J Taman Palem Lestari B18 - 19A (021) 926 68651 - Esia jadi bisa SMS -- Chenri J Taman Palem Lestari B18 - 19A (021) 926 68651 - Esia jadi bisa SMS -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INNER JOIN across multiple tables appear very slow.
Hi Michael, Thank you for being so kind to explain the problem to me. Michael Stassen wrote: sam wun wrote: sam wun wrote: Hi, Can anyone tell me how to optimize the following sql statement? $sql_1 = SELECT t.prodcode 'Product Code', t.prodname 'Product Name', ROUND(avg(t.salesvolume),2) 'PastSales Quantity', ROUND(avg(tt.salesvolume),2) 'Sales Quantity', ROUND(avg(t.netsales),2) 'PastSales', ROUND(avg(tt.netsales),2) 'Sales', Round(((avg(t.netsales) - avg(tt.netsales))/avg(tt.netsales))*100,2) 'SalesDiff', Round(((avg(t.salesvolume) - avg(tt.salesvolume))/avg(tt.salesvolume))*100,2) 'QtyDiff' FROM customer c INNER JOIN transaction t ON c.custcode = t.custcode AND date(t.date) = '$past_date_period_startdate' AND date(t.date) = '$past_date_period_enddate' AND c.salescode = '$salescode_param' INNER JOIN transaction tt ON c.custcode = tt.custcode AND date(tt.date) = '$input_date_period_startdate' AND date(tt.date) = '$input_date_period_enddate' AND c.salescode = '$salescode_param' AND t.prodcode = tt.prodcode INNER JOIN inventory i ON i.prodcode = t.prodcode AND i.prodcode = tt.prodcode AND i.basename = '$basename_param' WHERE i.prodname is not NULL AND i.basename is not NULL GROUP BY c.salescode, i.basename, t.prodcode ORDER BY SalesDiff desc; Thanks Sam I see several potential problems: 1) The biggest fixable problem is the use of the date() function. You should never use a function of a column value in a WHERE clause, if you can avoid it, because that prevents the use of an index on that column to help choose rows. In this case, the date() function isn't even needed, so even if there is no index on t.date, or the optimizer doesn't choose to use it, you are still forcing 4 unnecessary function calls per row. In other words, change AND date(t.date) = '$past_date_period_startdate' AND date(t.date) = '$past_date_period_enddate' to AND t.date = '$past_date_period_startdate' AND t.date = '$past_date_period_enddate' or the equivalent AND t.date BETWEEN '$past_date_period_startdate' AND '$past_date_period_enddate' Yes, I am totally agree with this change. I m still new to mysql/sql statement. I never realised the date field can be used for comparison without using date. 2) In general, you should'nt put things in the JOIN condition which are not part of the JOIN criteria. For example, the condition c.salescode = '$salescode_param' is a restriction on which rows in c to consider, not a condition of the JOIN to t or tt, so it belongs in the WHERE clause. 3) You have a complex GROUP BY based on a column from each table, and an ORDER BY based on a calculated value. That rules out using an index to do the grouping or ordering. Hence the dreaded Using temporary; Using filesort in your explain output. This appears unavoidable in your case. This clears my question about how to use JOIN. The using temporary may be caused by the clause create temporary table in other sql statement that created before execute the sql statement as written above. I was trying to break down the entire operation into sub-queires so that it produce sub-recordset faster and then JOIN with these recordset later. I m not sure whether using temporary tables will also speed thing up. I will drop the idea of using temporary table if that doesn't help much. I guess I can change the group by with only one column rather than 3 columns, and the result still the same since salescode and basename are input by the user, so this query only display the recordset of the product(code/name) base on value of salescode and the basename. 4) You have redundant conditions on your JOIN to i. Since t.prodcode = tt.prodcode for every row of your results (because you require this in the join of t to tt), the two conditions i.prodcode = t.prodcode and i.prodcode = tt.prodcode are equivalent. The optimizer is probably smart enough to notice that, but it will have less work to do if you pick one and delete the other. 5) Because you require i.basename = '$basename_param', i.basename cannot be NULL, so there is no need for the conditrion i.basename is not NULL. 6) Are you sure you need i.prodname is not NULL? If you can be certain that all rows in inventory with non NULL prodcode and basename have non NULL prodnames, you could leave that out. I can't assume you don't need it based only on the rest of your query, so I'll leave it in, for now. You are right, as I have made a changed to my table definition, basename and prodcname will never be NULL, its defult value is UNKNOWN. Based on the above, I'd suggest you change your query to: SELECT t.prodcode 'Product Code', t.prodname 'Product Name', ROUND(avg(t.salesvolume),2) 'PastSales Quantity', ROUND(avg(tt.salesvolume),2) 'Sales Quantity', ROUND(avg(t.netsales),2) 'PastSales',
Can't drop index.
Hi, I created an index on a foreign in a table before. I need to drop this index. but I got the following error: mysql alter table transaction drop index prodcode; ERROR 1025 (HY000): Error on rename of './datacube/#sql-30e8_3' to './datacube/transaction' (errno: 150) mysql mysql show index from transaction; +-++-+--+-+---+-+--++--++-+ | Table | Non_unique | Key_name| Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +-++-+--+-+---+-+--++--++-+ | transaction | 0 | PRIMARY |1 | transcode | A | 161361 | NULL | NULL | | BTREE | | | transaction | 1 | custcode|1 | custcode| A | 810 | NULL | NULL | | BTREE | | | transaction | 1 | prodcode|1 | prodcode| A | 911 | NULL | NULL | | BTREE | | | transaction | 1 | date|1 | date| A |1014 | NULL | NULL | | BTREE | | | transaction | 1 | netsales|1 | netsales| A | 14669 | NULL | NULL | | BTREE | | | transaction | 1 | salesvolume |1 | salesvolume | A | 197 | NULL | NULL | | BTREE | | | transaction | 1 | custcode_2 |1 | custcode| A | 139 | NULL | NULL | | BTREE | | | transaction | 1 | custcode_2 |2 | date| A | 26893 | NULL | NULL | | BTREE | | +-++-+--+-+---+-+--++--++-+ 8 rows in set (0.02 sec) mysql show index from inventory; +---+++--+-+---+-+--++--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +---+++--+-+---+-+--++--++-+ | inventory | 0 | PRIMARY|1 | prodcode| A |2377 | NULL | NULL | | BTREE | | | inventory | 1 | basename |1 | basename| A | 30 | NULL | NULL | YES | BTREE | | | inventory | 1 | vendorname |1 | vendorname | A | 27 | NULL | NULL | YES | BTREE | | +---+++--+-+---+-+--++--++-+ 3 rows in set (0.01 sec) mysql show innodb status; = 050124 10:50:54 INNODB MONITOR OUTPUT = Per second averages calculated from the last 11 seconds -- SEMAPHORES -- OS WAIT ARRAY INFO: reservation count 1372, signal count 1344 Mutex spin waits 2173, rounds 23570, OS waits 375 RW-shared spins 889, OS waits 444; RW-excl spins 661, OS waits 553 LATEST FOREIGN KEY ERROR 050124 10:49:59 Error in foreign key constraint of table `datacube/transaction`: there is no index in the table which would contain the columns as the first columns, or the data types in the table do not match to the ones in the referenced table. Constraint: , CONSTRAINT `transaction_ibfk_2` FOREIGN KEY (`prodcode`) REFERENCES `inventory` (`prodcode`) ON DELETE CASCADE TRANSACTIONS Trx id counter 0 3143526 Purge done for trx's n:o 0 3143526 undo n:o 0 0 Total number of lock structs in row lock hash table 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 3143514, not started, OS thread id 167162880 MySQL thread id 3, query id 69 localhost root SHOW INNODB STATUS FILE I/O I/O thread 0 state: waiting for i/o request (insert buffer thread) I/O thread 1 state: waiting for i/o request (log thread) I/O thread 2 state: waiting for i/o request (read thread) I/O thread 3 state: waiting for i/o request (write thread) Pending normal aio reads: 0, aio writes: 0, ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 0 79139 OS file reads, 100705 OS file writes, 6249 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s - INSERT BUFFER AND ADAPTIVE HASH INDEX
Complicated Query
Hello. I'm looking for some help on a complicated query. I have data in 5 different tables I want displayed on this page. The query I have now is $query = SELECT Shows.Season_RID AS SEASON_NUM, Shows.Show_Name AS NAME, Shows.Show_RID AS SHOWX, Season.Season_Name AS SEASON, cast.Role AS CAST, production_team.Title AS TITLE FROM osc1_company, production_team, Season, Shows, cast WHERE osc1_company.First_Name='$fname' AND osc1_company.Last_Name='$lname' AND osc1_company.RID = cast.RID AND osc1_company.RID = production_team.RID AND production_team.Show_RID=Shows.Show_RID AND Shows.Season_RID = Season.Season_RID AND Shows.Show_RID = cast.Show_RID ORDER BY Season.Season_RID ; This works, but only if a title and a role exists for the company member. Basically, I want to display the season name from tbl Season, then the show title from tbl Shows, then the roles from cast and titles from production_team for the company member. The table Season has Season_Name and Season_RID. The table Shows has Season_RID, Show_RID, and Show_Name. osc1_company has First_Name, Last_Name, and RID. Cast has RID, Role, Show_RID. Production_team has RID, title, Show_RID. Any help folks could give would be hugely appreciated. Been working on this for quite a while now. Thanks Ron
