why in procedure truncate table do not reset auto_increment?
*hi everyone: * I've some puzzle with the following test: CREATE TABLE `demo` ( `id` int(11) NOT NULL auto_increment, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 ; INSERT INTO demo VALUES(100); delimiter // create procedure test() DETERMINISTIC begin TRUNCATE table demo; show table status like 'demo'; END// delimiter ; when call test() I got auto_increment=101, why in procedure TRUNCATE table demo do not reset auto_increment? any help much appreciate !
Re: Using "DROP USER" in a stored procedure
On Jan 7, 2008 2:34 AM, Shawn Green <[EMAIL PROTECTED]> wrote: > > Eddie Cornejo wrote: > > I'm writing a cleanup script to remove database items created by my > > application. One of the things I would like to remove are all user > > accounts created through my application... This is proving to be > > harder than it sounds. > > There are two bits of information you do not seem to have. First is that > the DROP USER command only takes string literals as its parameter, it is > not engineered to take variables (either declared or user) as its parameter. Thank you for your reply. After some other searching I found that CREATE USER has the same issue as outlined in MySQL bug 19584 reported in May 2006 http://bugs.mysql.com/bug.php?id=19584 It seems that it was accepted as a feature request that has not yet been implemented. > The second piece of information is that you do not need to run a DROP > USER command to delete user accounts. If you have sufficient > permissions, you can edit the `mysql`.`user` table directly and just > DELETE those rows you want to eliminate. Any changes you make will not > become visible to the server until after you either restart the daemon > or issue a FLUSH PRIVILEGES command. Yeah. This suffers from two points First, DROP USER abstracts the concept of dropping a user from how it's implemented. This is advantageous as I don't know (nor really should I care) where this user's id has been used. So I shouldn't have to worry whether he has specific permissions in tables_priv or procs_priv (or some other table_priv implemented in the next version of mysql). DROP USER should handle all this for me nicely, and playing around with the tables directly just means that I'll have to keep supporting my code as mysql develops. Secondly it appears that FLUSH PRIVILEGES cannot be called from a stored function - but is quite valid in a stored procedure. http://dev.mysql.com/doc/refman/5.0/en/routine-restrictions.html "For stored functions (but not stored procedures), the following additional statements or operations are disallowed: * FLUSH statements." This restriction is also true for stored procedures called from stored functions. So my current solution (as inelegant as it may be) is to have a stored procedure call my stored function that manually modifies the mysql.user table, scan through all other (known) table_priv tables, then returns, whereby my stored procedure does a flush and then does a select to indicate success. Yuck. > Does this give you enough information for you to automate your table > maintenance? Yes! Thank you very much. I was hoping I had missed something in the use of DROP USER or stored routine variables, but it appears that it simply isn't possible to use them the way I wanted. I'm looking forward to the implementation of the feature requested in bug 19584, but its been almost two years now so I don't think it's high on the list of feature requests. Regards, Eddie Cornejo > > -- > Shawn Green, Support Engineer > MySQL Inc., USA, www.mysql.com > Office: Blountville, TN > __ ___ ___ __ > / |/ /_ __/ __/ __ \/ / >/ /|_/ / // /\ \/ /_/ / /__ > /_/ /_/\_, /___/\___\_\___/ > <___/ > Join the Quality Contribution Program Today! > http://dev.mysql.com/qualitycontribution.html > > -- Eddie Cornejo -BEGIN GEEK CODE BLOCK- Version: 3.12 GIT d? s: a- C+++ UL+++ P++ L++ E- W+ N- o K- w++ O M-- V PS+ PE Y PGP++ t 5 X+ R tv-- b+ DI D++ G e++ h r+++ y+++ --END GEEK CODE BLOCK-- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Search for column value in a string variable?
OK, never mind. I finally found the 'locate' function. I knew it had to be there somewhere! -- Barry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fast relevance sorting of full text search results
Urms wrote:
I'm using pretty standard approach to sorting search results by relevancy:
SELECT DISTINCT product_name,
MATCH (keywords) AGAINST ('CONSOLIDATED* 16* bearing*' IN BOOLEAN MODE) AS
rate
FROM _TT
WHERE MATCH ( keywords ) AGAINST ('CONSOLIDATED* 16* bearing*' IN BOOLEAN
MODE ) >0
ORDER BY rate DESC
It works fine as long as the quantity of results is not big. Once the
quantity is about 50,000 and more (I have a very big database) the query
starts working way too slow. Total number of records is about 4 million. It
takes about 2 sec when there are 50,000 records in the result but at the
same time it takes only about 0.006 sec without ORDER BY clause.
I understand that ORDER BY is time consuming but maybe someone knows a
different way to have sorting by relevancy.
Thanks in advance!
I think it's your SELECT DISTINCT that is slowing you down. For each new
row being considered for inclusion to your result set, you are asking
the engine to compare that row against all other rows you already have
in the set. So what's happening is that you are doing a longer and
longer linear search the larger your datasets become.
One option is to cache your results in a temporary table then
de-duplicate your results from there.
Another option is to create a temporary table with a UNIQUE key on the
columns you want to remain unique and use an INSERT IGNORE. Because of
the UNIQUE key (or PRIMARY KEY if that's your choice) you will be doing
an indexed search of all values rather than a linear search through the
entire list. This would look something like:
CREATE TEMPORARY TABLE tmpFT_results (
product_name
, rate
, primary key (product_name)
) ENGINE = MEMORY;
INSERT IGNORE tmpFT_results
SELECT product_name,
MATCH (keywords) AGAINST ('CONSOLIDATED* 16* bearing*' IN BOOLEAN MODE) AS
rate
FROM _TT
WHERE MATCH ( keywords ) AGAINST ('CONSOLIDATED* 16* bearing*' IN BOOLEAN
MODE ) >0;
SELECT product_name, rate
FROM tmpFT_results
ORDER BY rate DESC;
Sure it's three separate steps but it's tuned to the process you are
trying to perform. The SELECT DISTINCT processing has no idea that you
only need to keep the values of product_name distinct as we would hope
the `rate` component may be duplicated.
If there is the possibility of different `rate` results for the same
product_name value then you may also want to use the temporary table
method to somehow weight (sum or average comes to mind) the match values
across all responses before returning the results.
I hope these ideas help your performance and search accuracy.
Best wishes,
--
Shawn Green, Support Engineer
MySQL Inc., USA, www.mysql.com
Office: Blountville, TN
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /
/ /|_/ / // /\ \/ /_/ / /__
/_/ /_/\_, /___/\___\_\___/
<___/
Join the Quality Contribution Program Today!
http://dev.mysql.com/qualitycontribution.html
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Using "DROP USER" in a stored procedure
Eddie Cornejo wrote: I really hope I'm not overlooking something simple... I'm writing a cleanup script to remove database items created by my application. One of the things I would like to remove are all user accounts created through my application... This is proving to be harder than it sounds. I should note that it's a mysql script I plan to have the administrator run as 'mysql -u root -p < uninstall.txt' - therefore I'm limited in what I can do (to some degree) After unsuccessfully trying to solve this through a variety of other means, I've come to the conclusion that if I record all of the usernames I create in a table, then I might be able to iterate over that table and drop my users. This means creating a stored procedure temporarily for the sole purpose of getting the benefits of CURSORs.. So I've come up with this: DELIMITER // CREATE PROCEDURE deleteAllUsers() DETERMINISTIC MODIFIES SQL DATA BEGIN DECLARE name VARCHAR(12); DECLARE done INT DEFAULT 0; DECLARE allUsers CURSOR FOR SELECT username FROM user WHERE active = TRUE; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; OPEN allUsers; REPEAT FETCH allUsers INTO name; DROP USER name; UNTIL done END REPEAT; CLOSE allUsers; END; // DELIMITER ; It looks simple enough, however DROP USER tries to drop 'name' rather than the next username in the list :( I feel like I'm overlooking something really simple - however for the life of me I can't figure it out. Is it possible to use DROP USER with a variable name in a stored procedure? If not, is there some way to drop a bunch of users given a table of usernames? Perhaps this problem has only ever been tackled using a higher level language - but I was hoping to solve it in a little mysql script. Thanks. Hope you have a great 2008! There are two bits of information you do not seem to have. First is that the DROP USER command only takes string literals as its parameter, it is not engineered to take variables (either declared or user) as its parameter. http://dev.mysql.com/doc/refman/5.0/en/drop-user.html One way that people have tried to work around this kind of limitation is through the use of prepared statements. However our prepared statement interface does not support the preparation of DROP USER statements http://dev.mysql.com/doc/refman/5.0/en/sqlps.html The second piece of information is that you do not need to run a DROP USER command to delete user accounts. If you have sufficient permissions, you can edit the `mysql`.`user` table directly and just DELETE those rows you want to eliminate. Any changes you make will not become visible to the server until after you either restart the daemon or issue a FLUSH PRIVILEGES command. http://dev.mysql.com/doc/refman/5.0/en/flush.html Does this give you enough information for you to automate your table maintenance? -- Shawn Green, Support Engineer MySQL Inc., USA, www.mysql.com Office: Blountville, TN __ ___ ___ __ / |/ /_ __/ __/ __ \/ / / /|_/ / // /\ \/ /_/ / /__ /_/ /_/\_, /___/\___\_\___/ <___/ Join the Quality Contribution Program Today! http://dev.mysql.com/qualitycontribution.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Inter-version client/server compatibility
Charles Jardine wrote: To what extent can MySQL clients interact with servers of different versions? In particular, if I link an Apache PHP module with the client libraries for MySQL version 5.0.51, will I be able to use it to connect to a version 4.1.22 server? I ask this because I am running a web hosting service, and have a number of different users using MySQL 4.1 servers via PHP from Apache. I need to help them to convert their servers to MySQL 5. If the mixed-version set-up described above is OK, I could conveniently upgrade them all to the same new PHP module _before_ upgrading the servers individually. If the mixed-version set-up is not OK, I have a more challenging task. I have to keep the clients and servers in step. P.S. I am more familiar with Oracle than with MySQL. I know that an Oracle 10 client can be used with with servers going back at least to Oracle 8. Our client-server protocol has not changed much, if at all, in a long while. You should be quite safe using a 5.x-based client library to talk to a 4.1 and even a 3.x server. -- Shawn Green, Support Engineer MySQL Inc., USA, www.mysql.com Office: Blountville, TN __ ___ ___ __ / |/ /_ __/ __/ __ \/ / / /|_/ / // /\ \/ /_/ / /__ /_/ /_/\_, /___/\___\_\___/ <___/ Join the Quality Contribution Program Today! http://dev.mysql.com/qualitycontribution.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Embedding MySQL
Octavian Rasnita wrote: Hi, I want to embed MySQL and install it with a freeware application I make. I hope it is legally to do this. Please tell me where can I found more information about how can I do this. The app will run under Windows. Thank you. Octavian Please refer to http://www.mysql.com/oem/licensing.html and http://www.mysql.com/company/legal/licensing/ . If you have any additional questions, you may contact MySQL directly for additional information. Please see the contact links on those pages for various options. -- Shawn Green, Support Engineer MySQL Inc., USA, www.mysql.com Office: Blountville, TN __ ___ ___ __ / |/ /_ __/ __/ __ \/ / / /|_/ / // /\ \/ /_/ / /__ /_/ /_/\_, /___/\___\_\___/ <___/ Join the Quality Contribution Program Today! http://dev.mysql.com/qualitycontribution.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: table join trouble
Hi Edward, (see below) Edward Corbett wrote: Hi, I am trying to join a bunch of tables together. I want a row for each "learner", and on the row, I want some "user", "centre", "client" and "centreManager" information if there is any. Thus, I am trying to outer join from the "learner" table to 4 other tables. The query I have so far is below but it is returning 0 rows where I know that learner 215 exists and it should return a line with the "l1" information on it. Any help would be very much appreciated. select `vPamUser`.`initials`, l1.`learnerId`, l1.`usedName`, l1.`fullFirstNames`, l1.`surname`, `vPamCentre`.`centreName`, `vPamCentreManager`.`managerName`, `vPamCentreManager`.`managerUsedName`, `vPamClient`.`clientShortName` from `vPamLearner` l1 left join vPamUser on l1.`assessorId` = `vPamUser`.`userId`, `vPamLearner` l2 left join vPamCentre on l2.`centreId` = `vPamCentre`.`centreId`, `vPamLearner` l3 left join vPamClient on l3.`clientId` = `vPamClient`.`clientId`, `vPamLearner` l4 left join vPamCentreManager on l4.`centreManagerId` = `vPamCentreManager`.`centreManagerId` where l1.`learnerId` = 215 and l1.learnerId = l2.learnerId and l1.learnerId = l3.learnerId and l1.learnerId = l4.learnerId ; You only needed to reference your `vPamLearner` table once and use no commas like this: SELECT `vPamUser`.`initials`, l1.`learnerId`, l1.`usedName`, l1.`fullFirstNames`, l1.`surname`, `vPamCentre`.`centreName`, `vPamCentreManager`.`managerName`, `vPamCentreManager`.`managerUsedName`, `vPamClient`.`clientShortName` FROM `vPamLearner` l1 LEFT JOIN vPamUser ON l1.`assessorId` =`vPamUser`.`userId` LEFT JOIN vPamCentre ON l1.`centreId` = `vPamCentre`.`centreId` LEFT JOIN vPamClient ON l1.`clientId` = `vPamClient`.`clientId` LEFT JOIN vPamCentreManager ON l1.`centreManagerId` = `vPamCentreManager`.`centreManagerId` WHERE l1.`learnerId` = 215 Each new JOIN looks at the tables that preceded it in the statement as potential join targets. There is no need to create a self-join of the parent table for each child table you want to join. This works for both inner and outer joins. In reference to your follow-up post, I do not understand how removing the status reference from this query would have solved the problem unless there were a few additional elements to this query you also left out. Please let me know if this streamlined format improves your query speed and result quality. -- Shawn Green, Support Engineer MySQL Inc., USA, www.mysql.com Office: Blountville, TN __ ___ ___ __ / |/ /_ __/ __/ __ \/ / / /|_/ / // /\ \/ /_/ / /__ /_/ /_/\_, /___/\___\_\___/ <___/ Join the Quality Contribution Program Today! http://dev.mysql.com/qualitycontribution.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
