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]
RE: getting error with mysql_fix_privilege_tables
Steve, Below is output from the mysql_fix_privilege_tables script: You can safely ignore all 'Duplicate column' and 'Unknown column' errors because these just mean that your tables are already up to date. I would not worry about the Duplicate Key error, because that is likely the same case as Duplicate column. Clint From: scohen [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: getting error with mysql_fix_privilege_tables Date: Tue, 18 Jan 2005 17:40:24 -0500 (EST) When I run mysql_fix_privilege_tables it tells me to ignore a lot of errors. But it doesn't tell me about this error: ERROR 1061 at line 5: Duplicate key name 'Grantor' Can I ignore this? What is it from? Thanks, Steve -- 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: sub query is extermely slow
Sam, Can you send the output of the following: #explain your query\G Clint From: sam wun [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: sub query is extermely slow Date: Wed, 19 Jan 2005 20:02:37 +0800 Hi list, The following sql statement takes 3 mintues to complete the query. How can I improve its speed? select DISTINCT i.basename from inventory i, transaction t, customer c where i.prodcode = t.prodcode and c.custcode = t.custcode and i.basename is not NULL and i.prodname is not NULL and ((date(t.date) = 2004-01-01 and date(t.date) = 2004-01-31) and i.basename IN (select DISTINCT ii.basename from inventory ii, transaction tt, customer cc where ii.prodcode = tt.prodcode and cc.custcode = tt.custcode and ii.basename is not NULL and ii.prodname is not NULL and(date(tt.date) = 2005-01-01 and date(tt.date) = 2005-01-31))) order by i.basename 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: sub query is extermely slow
Sam, Can you send the following information: When was the last time 'analyze table table_name' (inventory, transaction, customer) was executed? OS: MySQL Version: Available Ram: Output from 'SHOW CREATE TABLE table_name' (inventory, transaction, and customer): Output from SHOW VARIABLES LIKE '%buffer%';: Clint From: sam wun [EMAIL PROTECTED] To: Clint Edwards [EMAIL PROTECTED] CC: mysql@lists.mysql.com Subject: Re: sub query is extermely slow Date: Wed, 19 Jan 2005 20:39:41 +0800 Clint Edwards wrote: Sam, Can you send the output of the following: #explain your query\G Thanks for the suggestion, here is the output of the explain query: mysql explain select DISTINCT i.basename from inventory i, transaction t, customer c where i.prodcode = t.prodcode and c.custcode = t.custcode and i.basename is not NULL and i.prodname is not NULL and ((date(t.date) = 2004-01-01 and date(t.date) = 2004-01-31) and i.basename IN (select DISTINCT ii.basename from inventory ii, transaction tt, customer cc where ii.prodcode = tt.prodcode and cc.custcode = tt.custcode and ii.basename is not NULL and ii.prodname is not NULL and(date(tt.date) = 2005-01-01 and date(tt.date) = 2005-01-31))) order by i.basename; +++---++---+--+-+--+--+--+ | id | select_type| table | type | possible_keys | key | key_len | ref | rows | Extra | +++---++---+--+-+--+--+--+ | 1 | PRIMARY| c | index | PRIMARY | PRIMARY | 32 | NULL | 317 | Using index; Using temporary; Using filesort | | 1 | PRIMARY| t | ref| custcode,prodcode | custcode | 32 | datacube.c.custcode | 36 | Using where | | 1 | PRIMARY| i | eq_ref | PRIMARY | PRIMARY | 32 | datacube.t.prodcode |1 | Using where | | 2 | DEPENDENT SUBQUERY | cc| index | PRIMARY | PRIMARY | 32 | NULL | 317 | Using index; Using temporary | | 2 | DEPENDENT SUBQUERY | tt| ref| custcode,prodcode | custcode | 32 | datacube.cc.custcode | 36 | Using where | | 2 | DEPENDENT SUBQUERY | ii| eq_ref | PRIMARY | PRIMARY | 32 | datacube.tt.prodcode |1 | Using where | +++---++---+--+-+--+--+--+ 6 rows in set (0.01 sec) Clint From: sam wun [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: sub query is extermely slow Date: Wed, 19 Jan 2005 20:02:37 +0800 Hi list, The following sql statement takes 3 mintues to complete the query. How can I improve its speed? select DISTINCT i.basename from inventory i, transaction t, customer c where i.prodcode = t.prodcode and c.custcode = t.custcode and i.basename is not NULL and i.prodname is not NULL and ((date(t.date) = 2004-01-01 and date(t.date) = 2004-01-31) and i.basename IN (select DISTINCT ii.basename from inventory ii, transaction tt, customer cc where ii.prodcode = tt.prodcode and cc.custcode = tt.custcode and ii.basename is not NULL and ii.prodname is not NULL and(date(tt.date) = 2005-01-01 and date(tt.date) = 2005-01-31))) order by i.basename 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: sub query is extermely slow
Sam, Can you create an index on transaction.date, then run your query again? If that is not better send me the output of 'explain query' again. This index may not be a good idea, depending on how many transaction are in the table on a specified date. Clint From: sam wun [EMAIL PROTECTED] To: Clint Edwards [EMAIL PROTECTED] CC: mysql@lists.mysql.com Subject: Re: sub query is extermely slow Date: Wed, 19 Jan 2005 22:05:58 +0800 Clint Edwards wrote: Sam, Can you send the following information: When was the last time 'analyze table table_name' (inventory, transaction, customer) was executed? Hi, here is the result of the analyze command: mysql analyze table inventory,transaction, customer; +--+-+--+--+ | Table| Op | Msg_type | Msg_text | +--+-+--+--+ | datacube.inventory | analyze | status | OK | | datacube.transaction | analyze | status | OK | | datacube.customer| analyze | status | OK | +--+-+--+--+ 3 rows in set (0.83 sec) OS: MySQL Version: Mysql 5.0 Available Ram: 254RAM Output from 'SHOW CREATE TABLE table_name' (inventory, transaction, and customer): Output from SHOW VARIABLES LIKE '%buffer%';: mysql SHOW CREATE TABLE inventory; +---+---+ | Table | Create Table | +---+---+ | inventory | CREATE TABLE `inventory` ( `prodcode` varchar(32) NOT NULL default '', `qty` decimal(9,2) NOT NULL default '0.00', `lastupdatedate` date NOT NULL default '-00-00', `prodname` varchar(32) default 'UNKNOWN', `basename` varchar(32) default 'UNKNOWN', `vendorname` varchar(50) default 'UNKNOWN', `cost` decimal(9,2) NOT NULL default '0.00', PRIMARY KEY (`prodcode`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +---+---+ 1 row in set (0.39 sec) mysql SHOW CREATE TABLE transaction; +-++ | Table | Create Table
Re: mysql.sock file exists, but still cannot connect to server
The postmaster is a Postgres daemon, and pg_shadow is the user table for postgres. Clint From: Michael Stassen [EMAIL PROTECTED] To: Alex Majora [EMAIL PROTECTED] CC: mysql@lists.mysql.com Subject: Re: mysql.sock file exists, but still cannot connect to server Date: Wed, 19 Jan 2005 17:42:34 -0500 Alex Majora wrote: I've upgraded to mysql 4.1.9 on Mac OS X 10.3.7, but now things won't start up. In the console I get these messages: The database cluster will be initialized with locale C. creating directory /var/db/RemoteManagement/RMDB/rmdb.data... ok creating directory /var/db/RemoteManagement/RMDB/rmdb.data/base... ok creating directory /var/db/RemoteManagement/RMDB/rmdb.data/global... ok creating directory /var/db/RemoteManagement/RMDB/rmdb.data/pg_xlog... ok creating directory /var/db/RemoteManagement/RMDB/rmdb.data/pg_clog... ok creating template1 database in /var/db/RemoteManagement/RMDB/rmdb.data/base/1... ok creating configuration files... ok initializing pg_shadow... ok enabling unlimited row size for system tables... ok initializing pg_depend... ok creating system views... ok loading pg_description... ok creating conversions... ok setting privileges on built-in objects... ok vacuuming database template1... ok copying template1 to template0... ok Success. You can now start the database server using: What is this? These messages do not appear to have come from mysql. There's something you haven't told us. What is RemoteManagement/RMDB? Is this something you run by hand, or something automated? /System/Library/CoreServices/RemoteManagement/rmdb.bundle/bin/postmaster -D /var/db/RemoteManagement/RMDB/rmdb.data or /System/Library/CoreServices/RemoteManagement/rmdb.bundle/bin/pg_ctl -D /var/db/RemoteManagement/RMDB/rmdb.data -l logfile start LOG: database system was shut down at 2005-01-19 12:36:29 PST LOG: checkpoint record is at 0/71524 LOG: redo record is at 0/71524; undo record is at 0/0; shutdown TRUE LOG: next transaction id: 1744; next oid: 16766 LOG: database system is ready ERROR: To use passwords, you have to revoke permissions on pg_shadow so normal users cannot read the passwords. Try 'REVOKE ALL ON pg_shadow FROM PUBLIC'. Have you tried doing what it says here? MySQL seems to be running: 649 ?? S 0:00.02 /System/Library/CoreServices/RemoteManagement/rmdb.bundle/bin/postmaster -D /var/db/RemoteManagement/RMDB/rmdb.data That doesn't appear to be mysql. When I run MySQLManager, I get this error message: 2005-01-19 12:37:34.328 MySQLInfoTool[667] Initial attempt at db installation failed, probably due to bad hostname; trying again with force option. ERROR: 1064 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 '' at line 1 That certainly seems to be a message (syntax error) from mysql. 050119 12:37:34 Aborting 050119 12:37:34 /usr/libexec/mysqld: Shutdown Complete Not sure why, but mysqld just shut down. When I type mysql at the command line, I get: ERROR 2002: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) Are you sure mysqld is running? You got a shutdown message right before this. ...yet the file exists: srwxrwxrwx 1 mysql wheel0 19 Jan 12:35 mysql.sock Well, that should mean mysqld is running. Two things: 1) Use ps to make sure there's a mysqld process (ps -aux | grep mysqld). 2) Check the permissions on /tmp (actually, /private/tmp under OS X). Some OS X updates have changed /tmp permissions in the past. Any ideas about what to do? I've reinstalled the mySQL package, but it didn't change things. Thanks! Alex Michael -- 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: Rollback and INSERT_ID() or LAST_INSERT_ID()
Andre, I would recommend a table for recovering id's that are lost due to rollback. Before you actually rollback, take the generated ID and push it into this table. Then change the way you acquire id's on insert. You will want to check to see if this table has an ID before you auto_increment the table you are inserting the record into. This should be a little less resource intensive than to put all data into temporary tables. Clint From: Joerg Bruehe [EMAIL PROTECTED] To: mysql@lists.mysql.com CC: Andre Matos [EMAIL PROTECTED], Paul DuBois [EMAIL PROTECTED] Subject: Re: Rollback and INSERT_ID() or LAST_INSERT_ID() Date: Tue, 18 Jan 2005 11:08:40 +0100 Hi! Am Di, den 18.01.2005 schrieb Paul DuBois um 3:53: At 21:27 -0500 1/17/05, Andre Matos wrote: Thanks Eric, but I can let it increment because I cannot have a gave in the numbers. I think I will need to use MAX() in this case. Using MAX() won't guarantee that you won't have gaps. What you're describing cannot be achieved in the general case. Consider this scenario: - Transaction T1 begins, generates an AUTO_INCREMENT value n. - Transaction T2 begins, generates an AUTO_INCREMENT value n+1. - Transaction T2 commits. - Transaction T1 rolls back. You now have a gap at value n, and a used value of n+1. MAX() at this point returns n+1, not n, so that won't help you reuse n. With more than two transactions running simultaneously, each of which can roll back or commit, the situation becomes more complex. IMO, Andre's only chance is to code his transactions in such a way that they need not rollback (only do so if the whole system stops). One way that comes to my mind is to accumulate all data in some temporary table, using some other value as ID (or in application variables), and only after the final yes, do it confirmation transfer them to the true tables with the auto-increment ID. In future releases, stored procedures might be another way to ensure all actions are grouped without an intervening parallel rollback. If your concurrency requirements are low and you can stand wait time, you could keep the next ID in an application-controlled table, locked from its retrieval to a final increment at transaction commit; but I agree these low requirements are unusual. As an alternative, a rollback might create a dummy record using that ID which acts as a placeholder, maybe with a remark user rollback or similar - if that is permissible in the application. Might be worth reconsidering whether you really require no gaps. It's generally better to try to design an application not to have that dependency. Paul, while I agree with that preference, I know that sometimes there is no choice. As an example, some German bookkeeping regulation requires you to use dense booking numbers (without gap). So I know of a software project that used _descending_ numbers because they were faster to generate in their environment than ascending ones. (This does not solve the rollback issue, of course.) Regards, Joerg -- Joerg Bruehe, Senior Production Engineer MySQL AB, www.mysql.com Are you MySQL certified? www.mysql.com/certification -- 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]
