RE: Authenticating a User to MySQL
Hi Asad, Do not put a space between the -p and password. You don't have to put the password in on the command line as the mysql client will prompt you for it. Regards David Logan Database Administrator HP Managed Services 148 Frome Street, Adelaide 5000 Australia +61 8 8408 4273 - Work +61 417 268 665 - Mobile +61 8 8408 4259 - Fax -Original Message- From: Asad Habib [mailto:[EMAIL PROTECTED] Sent: Friday, 18 March 2005 6:18 PM To: mysql@lists.mysql.com Subject: Authenticating a User to MySQL I just installed MySQL 4.1 on Mac OS X Panther Client. I am now having trouble authenticating users(root, admin) to MySQL. I am able to start MySQL but not using a specific user name and password. When I type ./mysql in /usr/local/mysql/bin, I am able to access MySQL. However, I have no privileges this way. However, when I try the following: ./mysql -u root -p password I get an error that states ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) Any help would be greatly appreciated. Thanks. - Asad -- 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: Problem: Slow LOAD FILE performance with innodb
Heikki Tuuri schrieb: Creating the indexes after the import will only slow down the operation. MySQL recreates the whole table at CREATE INDEX. That's new to me, but good to know (always this urban legends...). Does that only apply to InnoDB or to MyISAM too? Regards, A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help with a JOIN query please
Hi, I have ( among others ) three tables in my database: Claims, Expenses and Mileage. A claim can contain many expense entries and many mileage entries. I am using the follwing query to show the total expenses and mileage per claim for a particulare user: SELECT C.*, SUM(E.Amount) AS Amount, SUM(M.Mileage) AS Mileage FROM Claims C LEFT JOIN Expenses E ON E.Claim_ID = C.Claim_ID LEFT JOIN Mileage M ON M.Claim_ID = C.Claim_ID WHERE C.Claimant_ID = '1' GROUP BY C.Claim_ID The problem is if there are two mileage entries and one expense entry the expense total is doubled (and vice versa), can some explain this to me please? Thanks for your help. TABLE DEFINITIONS: mysql desc Claims; +-+-+--+-+-++ | Field | Type| Null | Key | Default | Extra | +-+-+--+-+-++ | Claim_ID| int(11) | | PRI | NULL| auto_increment | | Claimant_ID | int(11) | | | 0 || | Description | varchar(50) | | | || | Status | varchar(50) | YES | | Open|| | Submission_Date | datetime| YES | | NULL|| | Approval_Date | datetime| YES | | NULL|| | Approver_ID | int(11) | YES | | NULL|| +-+-+--+-+-++ 7 rows in set (0.00 sec) mysql desc Expenses; +-+---+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+---+--+-+-++ | Expense_ID | int(11) | | PRI | NULL| auto_increment | | Claim_ID| int(11) | YES | | NULL| | | Description | varchar(50) | YES | | NULL| | | Expense_Category_ID | int(11) | YES | | NULL| | | Insertion_Date | date | YES | | NULL| | | Project_ID | int(11) | YES | | NULL| | | Amount | decimal(10,2) | YES | | NULL| | | Rate_ID | int(11) | YES | | NULL| | | Supplier_ID | int(11) | YES | | NULL| | | Receipt | varchar(10) | YES | | NULL| | | Receipt_Date| varchar(10) | YES | | NULL| | | VAT_Receipt | varchar(10) | YES | | NULL| | | VAT_Amount | decimal(10,2) | YES | | NULL| | +-+---+--+-+-++ 13 rows in set (0.00 sec) mysql desc Mileage; ++--+--+-+++ | Field | Type | Null | Key | Default| Extra | ++--+--+-+++ | Mileage_ID | int(11) | | PRI | NULL | auto_increment | | Claim_ID | int(11) | | | 0 || | Project_ID | int(11) | | | 0 || | Insertion_Date | date | | | -00-00 || | Description| varchar(255) | | ||| | Start_Mileage | int(11) | | | 0 || | End_Mileage| int(11) | | | 0 || | Mileage| int(11) | | | 0 || ++--+--+-+++ 8 rows in set (0.00 sec) mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem: Slow LOAD FILE performance with innodb
Andreas, - Original Message - From: Andreas Ahlenstorf [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Friday, March 18, 2005 11:47 AM Subject: Re: Problem: Slow LOAD FILE performance with innodb Heikki Tuuri schrieb: Creating the indexes after the import will only slow down the operation. MySQL recreates the whole table at CREATE INDEX. That's new to me, but good to know (always this urban legends...). Does that only apply to InnoDB or to MyISAM too? I think for MyISAM, the fastest way to load is to 'disable indexes', and 'enable' them again. This is relatively recent. Please consult the manual. For most other database brands, index creation after the load is the fastest way. That will be true for InnoDB-5.1 also. Regards, A. Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php Order MySQL Network from http://www.mysql.com/network/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
show all running queries on linux
Hey list, How can I see the running queries on a linux comp? Thx Reinhart
Re: help on query/group by
Hi Shawn, Thank you very much, I'm impressed by the time you took to answer me, and the quality of the reply!!! I forwarded the answer to my friend. I'm wondering, I knew the mechanism of temporary tables, but as I've never used it I was trying the left join way. Here is a summary of my questions: - why using inner join here?is there any difference with using a left join?I thought using a left join would decrease the number of results. - do you know why without group by my query was running very fast and become so slow with the group by?when it does a group by it's scanning the whole table or an other reason? - I don't know if his version of mysql supports subqueries, but I was wondering if it is possible to replace the temporary tables by subqueries and keeping the same efficiency (my friend told me he would like to have only one sql query). Once again thank you very much for your help, I will give temporary tables an other chance!!! Melanie From: [EMAIL PROTECTED] To: mel list_php [EMAIL PROTECTED] CC: mysql@lists.mysql.com Subject: Re: help on query/group by Date: Wed, 16 Mar 2005 13:52:44 -0500 I have a favorite technique for improving the results of queries , like this, which involve fairly large JOINed tables. It's a form of divide-and-conquer in that you pre-compute what you can then make the JOINS you need to finish up the results. I agree that the AND s2.syn LIKE '%' in the ON clause of the second JOIN is worthless and can be eliminated however I recognize this as a common pattern for a two-term search and it may be harder to eliminate that clause than at first glance. This is how I would speed things up, Your friend really has 3 types of searches possible: a) search by drug name only b) search by protein name only c) search by both drug name and protein name Since the c) is the more complex situation, I will model it. It's almost trivial to clip out the unnecessary parts to make the other two queries. If I typed everything correctly, you should be able to cut and paste the whole thing into the MySQL client and have it execute. ### begin## CREATE TEMPORARY TABLE tmpSynDrug (key nameID) SELECT nameID, max(syn) as drugSyn FROM synonyms WHERE syn LIKE 'a%' GROUP BY nameID; CREATE TEMPORARY TABLE tmpSynProt (key nameID) SELECT nameID, max(syn) as protSyn FROM synonyms WHERE syn LIKE 'a%' GROUP BY nameID; CREATE TEMPORARY TABLE tmpMatch (key sentID) SELECT m.sentID, m.drugID, m.protID, tsd.drugSyn, tsp.protSyn FROM matches m INNER JOIN tmpSynDrugs tsd ON tsd.nameID = m.drugID INNER JOIN tmpSynProt tsp ON tsp.nameID = m.protID; # what we should have now is a nice small table that meets most of the # original query criteria. Now to summarize by publication by # joining through the sentence table SELECT tm.drugID, tm.protID, tm.drugSyn, tm.protSyn, COUNT(DISTINCT s.pmid) as publications FROM tmpMatch tm INNER JOIN sentence s ON s.id = tm.sentID GROUP BY 1,2,3,4 ; # I used a shortcut in the GROUP BY, I referenced the columns # by their positions and not by their names #Now that we have the data we wanted we can cleanup after ourselves: DROP TABLE tmpMatch, tmpSynProt, tmpSynDrug; end # By minimizing the number of records that needs to be JOINed at each stage of the query, we keep things moving along. This technique is very useful for queries whose JOIN products are somewhere in the hundreds of billions or records or more (which yours easily is). If you didn't want the names to be representative, but listed, you would change the first two queries to be like: CREATE TEMPORARY TABLE tmpSynDrug (key nameID) SELECT nameID, syn as drugSyn FROM synonyms WHERE syn LIKE 'a%'; If you didn't need names at all I would just say: CREATE TEMPORARY TABLE tmpSynDrug (key nameID) SELECT DISTINCT nameID FROM synonyms WHERE syn LIKE 'a%' and modify the other queries to not look for the name columns. HTH, Shawn Green Database Administrator Unimin Corporation - Spruce Pine == Original message ==from mel list_php [EMAIL PROTECTED] 03/16/2005 12:45 PM == Hi, A friend of mine asked me to have a look at one of his query, and I'm stuck Here was his query: SELECT drugID, protID, COUNT(DISTINCT pmid), MAX(s1.syn) AS o1, MAX(s2.syn) AS o2 FROM matches INNER JOIN synonyms AS s1 ON drugID=s1.nameID AND s1.syn LIKE 'a%' INNER JOIN synonyms AS s2 ON protID=s2.nameID AND s2.syn LIKE '%' INNER JOIN sentence ON sentID=id GROUP BY drugID, protID ORDER BY o1, o2 LIMIT 601 and this is his goal: The idea is quite simple: The table called 'matches' contains triples drugID, protID, sentID indicating a co-occurence of a drug and a protein in a sentence. The user of course searches for either drug name or protein name or both. In the above query, the user wants everything for all drugs starting with 'a'. The MAX() calls more or less arbitrarily choose one of the many names
Re: Help with a JOIN query please
Hello shaun, May be with subselects you can do what you want: SELECT C.*,(select SUM(E.Amount) from Expenses E where E.Claim_ID = C.Claim_ID)AS Amount, (select SUM(M.Mileage) from Mileage M where M.Claim_ID = C.Claim_ID) AS Mileage FROM Claims C WHERE C.Claimant_ID = '1' st Hi, st I have ( among others ) three tables in my database: Claims, Expenses and st Mileage. A claim can contain many expense entries and many mileage entries. st I am using the follwing query to show the total expenses and mileage per st claim for a particulare user: st SELECT C.*, SUM(E.Amount) AS Amount, SUM(M.Mileage) AS Mileage st FROM Claims C st LEFT JOIN Expenses E ON E.Claim_ID = C.Claim_ID st LEFT JOIN Mileage M ON M.Claim_ID = C.Claim_ID st WHERE C.Claimant_ID = '1' st GROUP BY C.Claim_ID st The problem is if there are two mileage entries and one expense entry the st expense total is doubled (and vice versa), can some explain this to me st please? st Thanks for your help. st TABLE DEFINITIONS: mysql desc Claims; st +-+-+--+-+-++ st | Field | Type| Null | Key | Default | Extra | st +-+-+--+-+-++ st | Claim_ID| int(11) | | PRI | NULL| auto_increment | st | Claimant_ID | int(11) | | | 0 | st | Description | varchar(50) | | | | st | Status | varchar(50) | YES | | Open| st | Submission_Date | datetime| YES | | NULL| st | Approval_Date | datetime| YES | | NULL| st | Approver_ID | int(11) | YES | | NULL| st +-+-+--+-+-++ st 7 rows in set (0.00 sec) mysql desc Expenses; st +-+---+--+-+-++ st | Field | Type | Null | Key | Default | Extra st | st +-+---+--+-+-++ st | Expense_ID | int(11) | | PRI | NULL| st auto_increment | st | Claim_ID| int(11) | YES | | NULL| st | st | Description | varchar(50) | YES | | NULL| st | st | Expense_Category_ID | int(11) | YES | | NULL| st | st | Insertion_Date | date | YES | | NULL| st | st | Project_ID | int(11) | YES | | NULL| st | st | Amount | decimal(10,2) | YES | | NULL| st | st | Rate_ID | int(11) | YES | | NULL| st | st | Supplier_ID | int(11) | YES | | NULL| st | st | Receipt | varchar(10) | YES | | NULL| st | st | Receipt_Date| varchar(10) | YES | | NULL| st | st | VAT_Receipt | varchar(10) | YES | | NULL| st | st | VAT_Amount | decimal(10,2) | YES | | NULL| st | st +-+---+--+-+-++ st 13 rows in set (0.00 sec) mysql desc Mileage; st ++--+--+-+++ st | Field | Type | Null | Key | Default| Extra | st ++--+--+-+++ st | Mileage_ID | int(11) | | PRI | NULL | auto_increment | st | Claim_ID | int(11) | | | 0 | st | Project_ID | int(11) | | | 0 | st | Insertion_Date | date | | | -00-00 | st | Description| varchar(255) | | || st | Start_Mileage | int(11) | | | 0 | st | End_Mileage| int(11) | | | 0 | st | Mileage| int(11) | | | 0 | st ++--+--+-+++ st 8 rows in set (0.00 sec) mysql -- Best regards, Krasimir_Slaveykov, 18 Ìàðò 2005 ã., 12:54:56 mailto: [EMAIL PROTECTED] [EMAIL PROTECTED] |-| |/ * * *** * ** /| | *** *** *** *** *** *** ** *** //| |/// *** *** * * *** **** *** ///| |// *** *** *** *** *** *** ** ****** | |/ *** *** *** * *** /| |--- www.office1.bg --| -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Ms Sql unique identifier field
Hello. You should think about AUTO_INCREMENT. See: http://dev.mysql.com/doc/mysql/en/example-auto-increment.html [snip] Hi! i have a question: FIELD IN MS SQL SERVER FiledName: uiAutID DataType: unique identifier DefaultValue: newid() how i can describe the some column in MySQL? if isn't possible, how can i solve my problem? P.S.: is necessary for us, use unique identifier data type because the datas must be exchanged with others companies and we need garantee unique identification of all the informations. Maurizio, Sponda [snip] -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Authenticating a User to MySQL
Hello. See: http://dev.mysql.com/doc/mysql/en/access-denied.html Asad Habib [EMAIL PROTECTED] wrote: I just installed MySQL 4.1 on Mac OS X Panther Client. I am now having trouble authenticating users(root, admin) to MySQL. I am able to start MySQL but not using a specific user name and password. When I type ./mysql in /usr/local/mysql/bin, I am able to access MySQL. However, I have no privileges this way. However, when I try the following: ./mysql -u root -p password I get an error that states ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) Any help would be greatly appreciated. Thanks. - Asad -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Ms Sql unique identifier field
AUTO_INCREMENT is not the same as newid(). Frankly, something like newid() would be very useful in MySQL. -- Chris. - Original Message - From: Gleb Paharenko [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, March 17, 2005 5:53 PM Subject: Re: Ms Sql unique identifier field Hello. You should think about AUTO_INCREMENT. See: http://dev.mysql.com/doc/mysql/en/example-auto-increment.html [snip] Hi! i have a question: FIELD IN MS SQL SERVER FiledName: uiAutID DataType: unique identifier DefaultValue: newid() how i can describe the some column in MySQL? if isn't possible, how can i solve my problem? P.S.: is necessary for us, use unique identifier data type because the datas must be exchanged with others companies and we need garantee unique identification of all the informations. Maurizio, Sponda [snip] -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- 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]
Which one of these two queries would be fastest?
Assuming everything is properly indexed. (1) INSERT INTO my_table_1 (field1, field2) SELECT DISTINCT field1, field2 FROM my_table_2; or (2) INSERT IGNORE INTO my_table_1 (field1, field2) SELECT field1, field2 FROM my_table_2; Is there anything to say about this in a general sense, or does it all depend on the data in question? Thanks, JP -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: lost connection DURING query?
Hi; I'm using Apache 2.0 + MySQL 4.1 + PHP 4.3.10 for a site. The database server and web server are on different machines. I get this Error lost connection DURING query when I try to access some of the web Pages. I have been through this thread. http://lists.mysql.com/mysql/181322 I tried the some of the solutions they won't help. Do I have to add the database server also the /etc/host of the web server?. So that reverse DNS lookup works both ways. I don't have super user access to the web server. Regards, Mukund Neharkar. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: show all running queries on linux
Reinhart Viane wrote: How can I see the running queries on a linux comp? If you have a fairly standard config, with a log file defined -- prompt grep '^log=' /etc/my.cnf | sed 's/log=//g' | xargs tail -f If there's no log file in /etc/my.cnf, start by putting one there :-) HTH! -- Hassan Schroeder - [EMAIL PROTECTED] Webtuitive Design === (+1) 408-938-0567 === http://webtuitive.com dream. code. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replicating InnoDB tables in new database
Hi, I have tried the following process in order to try and replicate a database with InnoDB files: 1. created a new database in PHPMyAdmin 2. via command line, copied all the .frm files from the old database directory into the new database directory 3. changed all the ownership and permissions 4. restarted mysql The database is now recognised in PHPMyAdmin, but when I click on any of the tables I get the message cannot find [table].InnoDB. Originally, the tables in the source database were MyISAM and then converted to InnoDB. I tried renaming one of the [table].frm files to [table].InnoDB, but now this does not show up on the table list. I tried the described method as I have done this before with MyISAM tables successfully. What am I missing? Or is a completely invalid way to move the database? Would a server restart fix it? Thanks Andy Hall. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Ms Sql unique identifier field
There is the UUID() function (added in 4.1.2). http://dev.mysql.com/doc/mysql/en/miscellaneous-functions.html However you cannot make a field's DEFAULT value a function (yet) so you will need to use UUID() in your INSERT and UPDATE statements where appropriate. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Chris Knipe [EMAIL PROTECTED] wrote on 03/18/2005 06:19:46 AM: AUTO_INCREMENT is not the same as newid(). Frankly, something like newid() would be very useful in MySQL. -- Chris. - Original Message - From: Gleb Paharenko [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, March 17, 2005 5:53 PM Subject: Re: Ms Sql unique identifier field Hello. You should think about AUTO_INCREMENT. See: http://dev.mysql.com/doc/mysql/en/example-auto-increment.html [snip] Hi! i have a question: FIELD IN MS SQL SERVER FiledName: uiAutID DataType: unique identifier DefaultValue: newid() how i can describe the some column in MySQL? if isn't possible, how can i solve my problem? P.S.: is necessary for us, use unique identifier data type because the datas must be exchanged with others companies and we need garantee unique identification of all the informations. Maurizio, Sponda [snip] -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- 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]
Optimize JOIN for more speed speed
Dear MySQL-ians, I am running a query on our database that join several tables. All tables have 2421500 rows and can be joined based on their id. SELECT a.id, a.B_00_29, b.R_00_29, c.NIR_00_29, d.SWIR_00_29 FROM vgt.B as a INNER JOIN vgt.R as b using (id) INNER JOIN vgt.NIR as c using (id) INNER JOIN vgt.SWIR as d using (id) INNER JOIN vgt.geo_1000 as f using (id) INNER JOIN vgt.v_ecoclim as g using (id) INNER JOIN vgt.STATUS as h using (id) WHERE f.X_coord 1545 AND f.X_coord 1570 AND f.Y_coord 201 AND f.Y_coord 223 AND h.STATUS_00_29 11 AND( g.v_lowreb = 9 OR g.v_lowreb = 20) AND g.v_landcov= 1 The query however takes a lot of time. Therefore I was wondering if anyone had suggestions to query more efficiently. Thanx in advance! Kind regards, Stef -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Re: lost connection DURING query?
Hi , You can try one simple thing. Wherever in ur code u try to run mysql_query(), check the last error value using mysql_errno(). If the value is 2013, then connect to the mysql server again and try running mysql_query again. I hope this solution works for u. Regards, Sapna On Fri, 18 Mar 2005 Neharkar,Mukund wrote : Hi; I'm using Apache 2.0 + MySQL 4.1 + PHP 4.3.10 for a site. The database server and web server are on different machines. I get this Error lost connection DURING query when I try to access some of the web Pages. I have been through this thread. http://lists.mysql.com/mysql/181322 I tried the some of the solutions they won't help. Do I have to add the database server also the /etc/host of the web server?. So that reverse DNS lookup works both ways. I don't have super user access to the web server. Regards, Mukund Neharkar. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help on query/group by
Responses embedded below mel list_php [EMAIL PROTECTED] wrote on 03/18/2005 05:57:29 AM: Hi Shawn, Thank you very much, I'm impressed by the time you took to answer me, and the quality of the reply!!! I forwarded the answer to my friend. I'm wondering, I knew the mechanism of temporary tables, but as I've never used it I was trying the left join way. Here is a summary of my questions: - why using inner join here?is there any difference with using a left join?I thought using a left join would decrease the number of results. The primary difference between a LEFT JOIN and an INNER JOIN is that with an INNER JOIN matching records MUST exist in both tables before they are considered for evaluation by the WHERE clause. You usually retrieve MORE records with a LEFT JOIN than an INNER JOIN but that depends on your data, too. In no case can an LEFT JOIN return fewer records than an INNER JOIN, all other conditions being equal. - do you know why without group by my query was running very fast and become so slow with the group by?when it does a group by it's scanning the whole table or an other reason? The GROUP BY clause requests that the engine make another processing pass through the records that satisfy your WHERE clause conditions in order to aggregate records according to the columns you specified. It's that second pass and the processing that occurs within it that makes a grouped query slower to finish than an ungrouped one. (NOTE: Some ungrouped query results are so large that a grouped result may actually be _useful_ sooner due to less data transfer between the server and your application) - I don't know if his version of mysql supports subqueries, but I was wondering if it is possible to replace the temporary tables by subqueries and keeping the same efficiency (my friend told me he would like to have only one sql query). I have found very few cases where subqueries outperformed temp (or special-purpose, permanent) tables especially when working with larger amounts of data. Of course, subquery performance varies according to the nature of the subquery (can it be evaluated just once or does it have to have to be evaluated for each and every row of the result), the complexity of the subquery, and the hardware your server is on. The only way to know for sure is to develop a subquery version of this query and test it with your/their hardware. Once again thank you very much for your help, I will give temporary tables an other chance!!! Melanie You are most welcome! Shawn Green Database Administrator Unimin Corporation - Spruce Pine BIG snip
Re: Replicating InnoDB tables in new database
Andy Hall [EMAIL PROTECTED] wrote on 18/03/2005 12:06:30: Hi, I have tried the following process in order to try and replicate a database with InnoDB files: 1. created a new database in PHPMyAdmin 2. via command line, copied all the .frm files from the old database directory into the new database directory 3. changed all the ownership and permissions 4. restarted mysql The database is now recognised in PHPMyAdmin, but when I click on any of the tables I get the message cannot find [table].InnoDB. Originally, the tables in the source database were MyISAM and then converted to InnoDB. I tried renaming one of the [table].frm files to [table].InnoDB, but now this does not show up on the table list. I tried the described method as I have done this before with MyISAM tables successfully. What am I missing? Or is a completely invalid way to move the database? No, this is a completely invaild way to to copy InnoDB files. What you previously did wit MyISDAM files was orbably to copy the .FRM (table descriptor) file AND ALSO the .MYD (table data) and .MYI (Indexes) files. This works for MyISAM, since each table is stored separately. However, thei does not work for InnoDB tables, which are stored in a very different fashion. InnoDB files are stored, all together, in files called ibdata*. Yo cannot split separate tables. As far as I know, there is no file fiddling way of doing what you wish to achieve. You need, I guess, the InnoDB Hot Backup tool - see http://www.innodb.com. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 5.0.2 alpha crashes with Query Browser
that pretty much explains why it's been happening ;) Thanks :) On Thu, 17 Mar 2005 22:34:08 +0100, Martijn Tonies [EMAIL PROTECTED] wrote: Francisco, Maybe it's my setup... but: whenever I try to login to use the mySQL query browser, it instantlly turns off the 5.0.2 alpha mySQL instance (stops running). the error I get is: mysqld-max-nt.exe - Application Error the instruciton at 0x00538d34 referenced memory at 0x007f9000. The memory could not be read. Running Win2k pro w/ 512mb of ram. 5.0.2 will crash on pretty much everything. It does seem that starting the server works, but that's about it :-) With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server Upscene Productions http://www.upscene.com -- -Francisco http://pcthis.blogspot.com | PC news with out the jargon! http://sqlthis.blogspot.com | Tsql and More... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with a JOIN query please
shaun thornburgh [EMAIL PROTECTED] wrote on 03/17/2005 06:46:22 PM: Hi, I have ( among others ) three tables in my database: Claims, Expenses and Mileage. A claim can contain many expense entries and many mileage entries. I am using the follwing query to show the total expenses and mileage per claim for a particulare user: SELECT C.*, SUM(E.Amount) AS Amount, SUM(M.Mileage) AS Mileage FROM Claims C LEFT JOIN Expenses E ON E.Claim_ID = C.Claim_ID LEFT JOIN Mileage M ON M.Claim_ID = C.Claim_ID WHERE C.Claimant_ID = '1' GROUP BY C.Claim_ID The problem is if there are two mileage entries and one expense entry the expense total is doubled (and vice versa), can some explain this to me please? Thanks for your help. TABLE DEFINITIONS: mysql desc Claims; +-+-+--+-+-++ | Field | Type| Null | Key | Default | Extra | +-+-+--+-+-++ | Claim_ID| int(11) | | PRI | NULL| auto_increment | | Claimant_ID | int(11) | | | 0 | | | Description | varchar(50) | | | | | | Status | varchar(50) | YES | | Open| | | Submission_Date | datetime| YES | | NULL| | | Approval_Date | datetime| YES | | NULL| | | Approver_ID | int(11) | YES | | NULL| | +-+-+--+-+-++ 7 rows in set (0.00 sec) mysql desc Expenses; +-+---+--+-+- ++ | Field | Type | Null | Key | Default | Extra | +-+---+--+-+- ++ | Expense_ID | int(11) | | PRI | NULL| auto_increment | | Claim_ID| int(11) | YES | | NULL| | | Description | varchar(50) | YES | | NULL| | | Expense_Category_ID | int(11) | YES | | NULL| | | Insertion_Date | date | YES | | NULL| | | Project_ID | int(11) | YES | | NULL| | | Amount | decimal(10,2) | YES | | NULL| | | Rate_ID | int(11) | YES | | NULL| | | Supplier_ID | int(11) | YES | | NULL| | | Receipt | varchar(10) | YES | | NULL| | | Receipt_Date| varchar(10) | YES | | NULL| | | VAT_Receipt | varchar(10) | YES | | NULL| | | VAT_Amount | decimal(10,2) | YES | | NULL| | +-+---+--+-+- ++ 13 rows in set (0.00 sec) mysql desc Mileage; ++--+--+-+++ | Field | Type | Null | Key | Default| Extra | ++--+--+-+++ | Mileage_ID | int(11) | | PRI | NULL | auto_increment | | Claim_ID | int(11) | | | 0 | | | Project_ID | int(11) | | | 0 | | | Insertion_Date | date | | | -00-00 | | | Description| varchar(255) | | || | | Start_Mileage | int(11) | | | 0 | | | End_Mileage| int(11) | | | 0 | | | Mileage| int(11) | | | 0 | | ++--+--+-+++ 8 rows in set (0.00 sec) mysql It's happening because you are joining two child tables at once. Maybe a sequence of diagrams will help. This is what happens within the DB engine whenever you make a JOIN. Since you were nice enough to post your table structures (THANK YOU!!!) I will use your tables and problem query as examples. Something simple: SELECT ... FROM Claims C LEFT JOIN Expenses E ON E.Claim_ID = C.Claim_ID; Step one of any query is to identify all of your source data. In this case, we are combining the records of two tables so that it behaves like a single larger table. This internally created, virtual table represents all possible combinations of rows that satisfy the ON conditions of your JOIN clauses. INTERNAL VIRTUAL TABLE A +---+-+ | all columns of Claims | all columns of Expenses | +---+-+ | Claims row 1 | all null values | +---+-+ | Claims row 2 | Expenses row 246| +---+-+ | Claims row 2 | Expenses row 248| +---+-+ | Claims row 2 |
Help with LIKE
Dear list, I need some help on this, I have a DB with one table The table (woorden) contains 1 field (woord) varchar(255) What i would like to be able to do is. SELECT * FROM woorden WHERE string LIKE % woord % so i need the fields in the table which fit into the string i submit. example: The string is: housewife and the result i would like to have is: house wife I hope somebody can help me. Greetings, Richard Mevers -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Different Tables in different drives and directories?
I have a need *ideally* to take the MySQL tables that I have and put certain ones in other directories and/or drives. I'd like to put certain tables within a client sub directory and thereby provide access to download the raw files and do such on a user by user basis by placing the tables belonging to them within their directory structure. Does anyone know of any way MySQL can be made to do such? -Paris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help on query/group by
Hi again, Thanks for the explanation about the join and the group by. I wanted to test your query (almost a simple copy/paste :-)) ). The first 2 queries are ok, but the third one still is too long : mysql CREATE TEMPORARY TABLE tmpSynDrug( KEY ( nameID (20)) ) SELECT nameID, max( syn ) AS drugSyn - FROM synonyms - WHERE syn - LIKE 'a%' - GROUP BY nameID; Query OK, 9693 rows affected (1.07 sec) Records: 9693 Duplicates: 0 Warnings: 0 mysql mysql CREATE TEMPORARY TABLE tmpSynProt (key (nameID(20))) - SELECT nameID, max(syn) as protSyn - FROM synonyms - WHERE syn LIKE 'a%' - GROUP BY nameID; Query OK, 9693 rows affected (1.03 sec) Records: 9693 Duplicates: 0 Warnings: 0 mysql mysql CREATE TEMPORARY TABLE tmpMatch( KEY ( sentID) ) SELECT m.sentID, m.drugID, m.protID, tsd.drugSyn, tsp.protSyn - FROM matches m - INNER JOIN tmpSynDrug tsd ON tsd.nameID = m.drugID - INNER JOIN tmpSynProt tsp ON tsp.nameID = m.protID; I've tried once this morning (GMT time), it ran for one hour nothing I killed mysql (btw, how can I kill only one query when mysql freezes?I aborted but then mysqladmin shutdown didn't work anymore..) I thought it was maybe because I have other heavy stuff running, but I tried a second time now and it's been running for 2 hours now, with almost nothing else on the desktop. So apparently the join between matches and the other tables is still too heavy Any idea? From: [EMAIL PROTECTED] To: mel list_php [EMAIL PROTECTED] CC: mysql@lists.mysql.com Subject: Re: help on query/group by Date: Fri, 18 Mar 2005 09:14:02 -0500 Responses embedded below mel list_php [EMAIL PROTECTED] wrote on 03/18/2005 05:57:29 AM: Hi Shawn, Thank you very much, I'm impressed by the time you took to answer me, and the quality of the reply!!! I forwarded the answer to my friend. I'm wondering, I knew the mechanism of temporary tables, but as I've never used it I was trying the left join way. Here is a summary of my questions: - why using inner join here?is there any difference with using a left join?I thought using a left join would decrease the number of results. The primary difference between a LEFT JOIN and an INNER JOIN is that with an INNER JOIN matching records MUST exist in both tables before they are considered for evaluation by the WHERE clause. You usually retrieve MORE records with a LEFT JOIN than an INNER JOIN but that depends on your data, too. In no case can an LEFT JOIN return fewer records than an INNER JOIN, all other conditions being equal. - do you know why without group by my query was running very fast and become so slow with the group by?when it does a group by it's scanning the whole table or an other reason? The GROUP BY clause requests that the engine make another processing pass through the records that satisfy your WHERE clause conditions in order to aggregate records according to the columns you specified. It's that second pass and the processing that occurs within it that makes a grouped query slower to finish than an ungrouped one. (NOTE: Some ungrouped query results are so large that a grouped result may actually be _useful_ sooner due to less data transfer between the server and your application) - I don't know if his version of mysql supports subqueries, but I was wondering if it is possible to replace the temporary tables by subqueries and keeping the same efficiency (my friend told me he would like to have only one sql query). I have found very few cases where subqueries outperformed temp (or special-purpose, permanent) tables especially when working with larger amounts of data. Of course, subquery performance varies according to the nature of the subquery (can it be evaluated just once or does it have to have to be evaluated for each and every row of the result), the complexity of the subquery, and the hardware your server is on. The only way to know for sure is to develop a subquery version of this query and test it with your/their hardware. Once again thank you very much for your help, I will give temporary tables an other chance!!! Melanie You are most welcome! Shawn Green Database Administrator Unimin Corporation - Spruce Pine BIG snip _ Express yourself with cool new emoticons http://www.msn.co.uk/specials/myemo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Help with LIKE
Maybe you better do something like: SELECT * FROM woorden WHERE % woord % LIKE string -Oorspronkelijk bericht- Van: Mevershosting.nl [mailto:[EMAIL PROTECTED] Verzonden: vrijdag 18 maart 2005 16:11 Aan: mysql@lists.mysql.com Onderwerp: Help with LIKE Dear list, I need some help on this, I have a DB with one table The table (woorden) contains 1 field (woord) varchar(255) What i would like to be able to do is. SELECT * FROM woorden WHERE string LIKE % woord % so i need the fields in the table which fit into the string i submit. example: The string is: housewife and the result i would like to have is: house wife I hope somebody can help me. Greetings, Richard Mevers -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.7.3 - Release Date: 15/03/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 5.0.2 alpha crashes with Query Browser
imminent Dictionary imminent (m'-nnt) adj. About to occur; impending: in imminent danger. - Original Message - From: "Lily Wei" [EMAIL PROTECTED] To: [EMAIL PROTECTED]; "Francisco Tapia" [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Thursday, March 17, 2005 11:47 AM Subject: RE: 5.0.2 alpha crashes with Query Browser When will 5.0.3 come out?Thanks,Lily-Original Message-From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: Thursday, March 17, 2005 10:17 AMTo: Francisco TapiaCc: mysql@lists.mysql.comSubject: Re: 5.0.2 alpha crashes with Query BrowserLots of users report such instabilities in the 5.0.2-alpha build for Windows. We went back to 5.0.1.PB-Francisco Tapia wrote:Maybe it's my setup... but:whenever I try to login to use the mySQL query browser, it instantllyturns off the 5.0.2 alpha mySQL instance (stops running). the error Iget is:mysqld-max-nt.exe - Application Errorthe instruciton at "0x00538d34" referenced memory at "0x007f9000". Thememory could not be "read".Running Win2k pro w/ 512mb of ram. -- No virus found in this outgoing message.Checked by AVG Anti-Virus.Version: 7.0.308 / Virus Database: 266.7.3 - Release Date: 3/15/2005-- MySQL General Mailing ListFor list archives: http://lists.mysql.com/mysqlTo unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]-- MySQL General Mailing ListFor list archives: http://lists.mysql.com/mysqlTo unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: show all running queries on linux
In the last episode (Mar 18), Reinhart Viane said: How can I see the running queries on a linux comp? SHOW [FULL] PROCESSLIST. And you don't need to be running Linux; it's a standard mysql command. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Erroneus column using MAX() and GROUP BY
You could probably use a subquerry to backtrack the clienthistory_id. SELECT v.clienthistory_id, (SELECT MAX(historyvlan_time), historyvlan_vlan FROM pe_historyvlan as v join pe_clienthistory using (clienthistory_id) GROUP BY historyvlan_vlan order by historyvlan_vlan, historyvlan_time desc FROM ... WHERE MAX(historyvlan_time)=historyvlan_time AND historyvlan_vlan=historyvlan_vlan This could work if historyvlan_time and historyvlan_vlan can be treated as dual primary keys, but I am still new to MySQL and this might need to be tweaked to work right. I hope it helps, or gets you moving in a better direction. Elton Clark Project Engineer IMET Corporation 82 Walker Lane, Suite 100 Newtown, PA 18940 215-860-6081 x5 [EMAIL PROTECTED] -Original Message- From: Michael Stassen [mailto:[EMAIL PROTECTED] Sent: Thursday, March 17, 2005 12:17 AM To: Daevid Vincent Cc: mysql@lists.mysql.com Subject: Re: Erroneus column using MAX() and GROUP BY You've misunderstood how GROUP BY and MAX() work. GROUP BY divides your data into groups, and aggregate functions such as MAX() tell you something about each group, but they *do not* return *rows* from your table. Consider the following example rows in a larger table: cat val1 val2 4 1 1 4 5 3 4 7 2 4 3 4 4 7 1 Now consider the query SELECT cat, MIN(val1), MAX(val1), MIN(val2), MAX(val2) FROM mytable GROUP BY cat; I think it should be easy to see that for the group where cat is 4, I'll get the following result: +-+---+---+---+---+ | cat | MIN(val1) | MAX(val1) | MIN(val2) | MAX(val2) | +-+---+---+---+---+ | 4 | 1 | 7 | 1 | 4 | +-+---+---+---+---+ Which row is that in my table? You see? Even if I only asked for MAX(val1), there are 2 rows with the max value of 7. AS I said before, we get information about each group, but not rows from the table. Other systems wouldn't even allow your query, because clienthistory_id is neither an aggregate function nor a grouped column. MySQL allows this as a convenience, but you are warned not to use columns whose values are not unique per group, as you will get random (first found, I believe) results. See the manual for more http://dev.mysql.com/doc/mysql/en/group-by-hidden-fields.html. Fortunately, yours is such a frequently asked question, that the manual has a page describing three solutions. See http://dev.mysql.com/doc/mysql/en/example-maximum-column-group-row.html. Michael Daevid Vincent wrote: I have this table: mysql select historyvlan_time, historyvlan_vlan, v.clienthistory_id from pe_historyvlan as v join pe_clienthistory using (clienthistory_id) order by historyvlan_vlan, historyvlan_time desc; +--+--+--+ | historyvlan_time | historyvlan_vlan | clienthistory_id | +--+--+--+ | 0503011446 |4 | 55 | -- | 0503011440 |4 | 54 | | 0502181640 |4 | 29 | | 0502181638 |4 | 26 | | 0502181508 |4 | 24 | | 0503021500 |5 | 73 | -- | 0503011808 |6 | 71 | -- | 0503011452 |6 | 56 | | 0502181626 |6 | 25 | | 0502181640 |7 | 28 | -- | 0503011805 |8 | 70 | -- | 0503011801 |8 | 68 | | 0503011731 |8 | 61 | | 0503011730 |8 | 60 | +--+--+--+ 14 rows in set (0.00 sec) I am trying to find the id and vlan for the most recent time: mysql select max(historyvlan_time), historyvlan_vlan, v.clienthistory_id from pe_historyvlan as v join pe_clienthistory using (clienthistory_id) group by historyvlan_vlan order by historyvlan_vlan, historyvlan_time desc; +---+--+--+ | max(historyvlan_time) | historyvlan_vlan | clienthistory_id | +---+--+--+ | 0503011446|4 | 24 | -- | 0503021500|5 | 73 | | 0503011808|6 | 25 | -- | 0502181640|7 | 28 | | 0503011805|8 | 60 | --
MySQL SNMP OIDs
Are there SNMP OIDs available I can use to query MySQL qps, inserts ps, etc? Luis -- GnuPG Key fingerprint = 113F B290 C6D2 0251 4D84 A34A 6ADD 4937 E20A 525E pgp7JOjsc3R2E.pgp Description: PGP signature
MySQL SNMP OIDs
[ This is my second e-mail, the first I forgot to CC me. Please CC me, I am not subscribed ] Are there SNMP OIDs avialble to get MySQL qps, inserts ps, etc? Luis -- GnuPG Key fingerprint = 113F B290 C6D2 0251 4D84 A34A 6ADD 4937 E20A 525E pgppLI1O5jxLy.pgp Description: PGP signature
update a field with multiple value
Hi, I am trying to to update one field with multiple value. I tried with regulare update command syntax does not work. How to update a field with multiple value. regular update syntax is this, UPDATE variableInfo SET variable='A' WHERE variable is null; but what I want to do is UPDATE variableInfo SET variable='A, B' WHERE variable is null; when I use that command, mysql does not understand the syntax. How to update a field with multiple value or with an array? +-+ + variable + +-+ + + + + +-+ but I want to update this column into +-+ + variable + +-+ + + + A, B + +-+ thank you in advance. - Do you Yahoo!? Yahoo! Small Business - Try our new resources site!
Re: long PHP mysql_connect times
On Mar 18, 2005, at 4:16 AM, Daniel Hawker wrote: Curiouser and curiouser said Alice... When you say *it seems to be the first connect* do you mean in a page, ie the first connect/disconnect takes ages but then any subsequent connects are fine) Exactly. If I use pconnect the first one takes on average 5 seconds. The remain ones are 0 because they are already open. mysql_connect takes 5 seconds for each one. Equally the fact that you can run the SQL queries locally (with no latency) and that PHP and HTML pages (with no SQL queries contained) run fine, would seem to insinuate the problem is with how PHP communicates with MySQL, rather than an underlying problem with either MySQL or PHP/Apache. You could check your my.cnf and php.ini files and see if they have corrupted themselves or similar. May be worth checking out the mysql.sock location and its privileges. It may be finding it hard to locate/create the sock file and hence is taking a long time to do the first query. Restating apache resets the issue i.e. the next pconnects takes 5 seconds. Quitting the browser has no effect. I am currently testing now to see if there is a time out issue i.e. if I wait 5 minutes will the delay reappear? Dan T -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: update a field with multiple value
Eko Budiharto [EMAIL PROTECTED] wrote on 18/03/2005 16:54:09: Hi, I am trying to to update one field with multiple value. I tried with regulare update command syntax does not work. How to update a field with multiple value. regular update syntax is this, UPDATE variableInfo SET variable='A' WHERE variable is null; but what I want to do is UPDATE variableInfo SET variable='A, B' WHERE variable is null; when I use that command, mysql does not understand the syntax. How to update a field with multiple value or with an array? +-+ + variable + +-+ + + + + +-+ but I want to update this column into +-+ + variable + +-+ + + + A, B + +-+ MySQL does not support arrays of data in one field. You cannot enter multiple entries into a numeric field. You could, of course, enter it as a string, but this is regarded as very bad practice. Most users would inquire why you need to do this, and suggest that you should be reconsidering your table design if you need this sort of facility. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
stopping server from pid file error
# mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql STOPPING server from pid file /var/lib/mysql/mymachine.pid 050318 11:01:31 mysqld ended [EMAIL PROTECTED] mysql]# ls -la /var/lib/mysql total 40 drwxrwxr-x 4 mysql mysql 4096 Mar 18 10:54 . drwxr-xr-x 22 root root 4096 Feb 25 14:22 .. drwxrwx--x 2 mysql mysql 4096 Feb 25 14:22 mysql drwxrwxr-x 2 mysql mysql 4096 Feb 25 14:22 test -rw-rw 1 mysql mysql 1953 Mar 18 11:01 mymachine.err There's no mymachine.pid. The permission on /var/lib/mysql seems to be right. I am using Fedora core 3. Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replicating InnoDB tables in new database
On Mar 18, 2005, at 7:06 AM, Andy Hall wrote: Hi, I have tried the following process in order to try and replicate a database with InnoDB files: 1. created a new database in PHPMyAdmin 2. via command line, copied all the .frm files from the old database directory into the new database directory 3. changed all the ownership and permissions 4. restarted mysql The database is now recognised in PHPMyAdmin, but when I click on any of the tables I get the message cannot find [table].InnoDB. Originally, the tables in the source database were MyISAM and then converted to InnoDB. I tried renaming one of the [table].frm files to [table].InnoDB, but now this does not show up on the table list. I tried the described method as I have done this before with MyISAM tables successfully. What am I missing? Or is a completely invalid way to move the database? Would a server restart fix it? As someone else mentioned this won't work with InnoDB. InnoDB uses .frm files for table descriptions along with ibdata files and it's own log files. If you want to do something like this by moving files you could: 1. shut down mysqld cleanly 2. copy db directories, .frm files, all ibdata files and all InnoDB log files (not mysql binary logs, InnoDB logs) to the new machine 3. set ownership and permissions 4. start mysqld on new machine Or you could us InnoDB hot backup tool, but in that case you still need to copy the .frm files and database directories. This is all best described in the InnoDB manual (if you're using it you should read the entire thing as it handles a lot of things differently than MyISAM) and the MySQL replication section of it's manual. Good luck, Ware -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help on query/group by
mel list_php [EMAIL PROTECTED] wrote on 03/18/2005 10:35:30 AM: Hi again, Thanks for the explanation about the join and the group by. I wanted to test your query (almost a simple copy/paste :-)) ). The first 2 queries are ok, but the third one still is too long : mysql CREATE TEMPORARY TABLE tmpSynDrug( KEY ( nameID (20)) ) SELECT nameID, max( syn ) AS drugSyn - FROM synonyms - WHERE syn - LIKE 'a%' - GROUP BY nameID; Query OK, 9693 rows affected (1.07 sec) Records: 9693 Duplicates: 0 Warnings: 0 mysql mysql CREATE TEMPORARY TABLE tmpSynProt (key (nameID(20))) - SELECT nameID, max(syn) as protSyn - FROM synonyms - WHERE syn LIKE 'a%' - GROUP BY nameID; Query OK, 9693 rows affected (1.03 sec) Records: 9693 Duplicates: 0 Warnings: 0 mysql mysql CREATE TEMPORARY TABLE tmpMatch( KEY ( sentID) ) SELECT m.sentID, m.drugID, m.protID, tsd.drugSyn, tsp.protSyn - FROM matches m - INNER JOIN tmpSynDrug tsd ON tsd.nameID = m.drugID - INNER JOIN tmpSynProt tsp ON tsp.nameID = m.protID; I've tried once this morning (GMT time), it ran for one hour nothing I killed mysql (btw, how can I kill only one query when mysql freezes?I aborted but then mysqladmin shutdown didn't work anymore..) I thought it was maybe because I have other heavy stuff running, but I tried a second time now and it's been running for 2 hours now, with almost nothing else on the desktop. So apparently the join between matches and the other tables is still too heavy Any idea? snip OK, I reviewed what you have posted so far and I found a performance killer. On the table matches, the columns protID and drugID are declared as text. This is bad for searching as you can only index the first portion of any text column. Those columns should be declared as CHAR or VARCHAR or better yet, some integer value. If all 3 columns in the matches table are integers (INT or BIGINT, preferably UNSIGNED) then this becomes a fixed-width table and lookups become exceedingly fast. All indexes on those columns also become number-based and numeric comparisons occur *much* faster than string comparisons. I very rarely use non-numeric primary keys for just this reason. You mentioned there were indexes on the table and provided the output of DESC for the table but DESC does a very poor job of actually describing indexes. I prefer the output of SHOW CREATE TABLE \G as it gives me a complete table creation statement(Use /G and not ; to eliminate a lot of excess formatting in the output). Can you generate that for me, please? I practically guarantee that if we re-tool that table (including the indexes), our query times will drop like rocks. You can kill a single query through the commands SHOW [FULL] PROCESSLIST (to identify the # of the process you want to kill) and KILL # (using the # you just looked up). This usually drops the connection to the client running the query you killed, too (so be prepared to reconnect). http://dev.mysql.com/doc/mysql/en/show-processlist.html http://dev.mysql.com/doc/mysql/en/kill.html I just realized that we are only querying for the search conditions DRUG like 'a%' AND PROTIEN like 'a%'. If we wanted to do the OR of that (DRUG like ... OR Protien like ...) we could use a UNION query to generate tmpMatch CREATE TEMPORARY TABLE tmpMatch (key sentID) (SELECT m.sentID, m.drugID, m.protID, tsd.drugSyn, tsp.protSyn FROM matches m INNER JOIN tmpSynDrugs tsd ON tsd.nameID = m.drugID INNER JOIN tmpSynProt tsp ON tsp.nameID = m.protID) UNION (SELECT m.sentID, m.drugID, m.protID, tsd.drugSyn, tsp.protSyn FROM matches m INNER JOIN tmpSynDrugs tsd ON tsd.nameID = m.drugID INNER JOIN tmpSynProt tsp ON tsp.nameID = m.protID); Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: update a field with multiple value
Eko Budiharto [EMAIL PROTECTED] wrote on 03/18/2005 11:54:09 AM: Hi, I am trying to to update one field with multiple value. I tried with regulare update command syntax does not work. How to update a field with multiple value. regular update syntax is this, UPDATE variableInfo SET variable='A' WHERE variable is null; but what I want to do is UPDATE variableInfo SET variable='A, B' WHERE variable is null; when I use that command, mysql does not understand the syntax. How to update a field with multiple value or with an array? +-+ + variable + +-+ + + + + +-+ but I want to update this column into +-+ + variable + +-+ + + + A, B + +-+ thank you in advance. Assuming that your column is declared as a SET datatype (as that is the only multivalue type MySql supports), you would update that column with the command you gave EXCEPT you would remove the space between the comma and B. UPDATE variableinfo set variable ='A,B' WHERE variable is null; See: http://dev.mysql.com/doc/mysql/en/set.html for more examples. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: long PHP mysql_connect times
Restating apache resets the issue i.e. the next pconnects takes 5 seconds. Quitting the browser has no effect. I am currently testing now to see if there is a time out issue i.e. if I wait 5 minutes will the delay reappear? FWIW, this sounds like a slow-to-respond DNS resolver. Are Apache and MySQL on seperate machines, by any chance? Or, alternatively, are you connecting locally on the machine using the machine's DNS name? E.g.: mysql_connect(foo.example.com, ...) instead of mysql_connect(localhost, ...) Can you try this: Whatever name you are using in mysql_connect(), run this: $ host foo.example.com Then, take the IP that gives you and do the same: $ host 1.2.3.4 Mainly you're looking for the delay in this second step. -- Jeremy Cole Technical Yahoo - MySQL (Database) Geek -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: long PHP mysql_connect times [Resolved]
Thanks Jeremy! It was the lack of a reverse DNS entry. I had the host resolved to an IP but now reverse arpa entry. I added the DNS PTR record and viola! I would have never though of / figured that one out. Dan T On Mar 18, 2005, at 10:59 AM, Jeremy Cole wrote: Restating apache resets the issue i.e. the next pconnects takes 5 seconds. Quitting the browser has no effect. I am currently testing now to see if there is a time out issue i.e. if I wait 5 minutes will the delay reappear? FWIW, this sounds like a slow-to-respond DNS resolver. Are Apache and MySQL on seperate machines, by any chance? Or, alternatively, are you connecting locally on the machine using the machine's DNS name? E.g.: mysql_connect(foo.example.com, ...) instead of mysql_connect(localhost, ...) Can you try this: Whatever name you are using in mysql_connect(), run this: $ host foo.example.com Then, take the IP that gives you and do the same: $ host 1.2.3.4 Mainly you're looking for the delay in this second step. -- Jeremy Cole Technical Yahoo - MySQL (Database) Geek -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: long PHP mysql_connect times [Resolved]
Hi Dan, It was the lack of a reverse DNS entry. I had the host resolved to an IP but now reverse arpa entry. I added the DNS PTR record and viola! Excellent. Also FWIW, you're not gaining anything by disconnecting after each query in your PHP code. Connect once at the beginning of the script, disconnect once at the end. All the extraneous connect/disconnect puts extra, unnecessary load on the database server. I would have never though of / figured that one out. Nobody knows... the troubles I've seen... :) Regards, Jeremy -- Jeremy Cole Technical Yahoo - MySQL (Database) Geek -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: stopping server from pid file error
On Fri, Mar 18, 2005 at 11:02:50AM -0600, sharif islam wrote: # mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql STOPPING server from pid file /var/lib/mysql/mymachine.pid 050318 11:01:31 mysqld ended [EMAIL PROTECTED] mysql]# ls -la /var/lib/mysql total 40 drwxrwxr-x 4 mysql mysql 4096 Mar 18 10:54 . drwxr-xr-x 22 root root 4096 Feb 25 14:22 .. drwxrwx--x 2 mysql mysql 4096 Feb 25 14:22 mysql drwxrwxr-x 2 mysql mysql 4096 Feb 25 14:22 test -rw-rw 1 mysql mysql 1953 Mar 18 11:01 mymachine.err There's no mymachine.pid. The permission on /var/lib/mysql seems to be right. I am using Fedora core 3. have a look at mymachine.err In most cases you'll find the reason there. -- -- Katoob Main Developer, Arabbix Maintainer. GNU/Linux registered user #224950 Proud Egyptian GNU/Linux User Group www.eglug.org Admin. Life powered by Debian, Homepage: www.foolab.org -- Don't send me any attachment in Micro$oft (.DOC, .PPT) format please Read http://www.gnu.org/philosophy/no-word-attachments.html Preferable attachments: .PDF, .HTML, .TXT Thanx for adding this text to Your signature signature.asc Description: Digital signature
Converting Integer values to date type
Hello,
I have a table with separate integer values for the day, month and year.
I would like to group them all under one field of type date.
I tried a few commands but I haven't captured the right syntax yet.
so if the field names are dayVal,monthVal and YearVal in talbeDummy
I am doing the following
select str_to_date(DayVal.MonthVal.YearVal,'%d.%m.%Y') from tableDummy;
I also tried :
select cast('YearVal-MonthVal-DayVal' AS date) from tblDiagnostic;
In both cases I had syntax errors one just flat out didn't run and the
second one gave me Null values in the table.
I also converted the integer values to varchar and that didn't help either.
This is got to be simple to do.
Thanks in advance.
--
-Mahmoud Badreddine
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ERROR 2013: Lost connection to MySQL server during query
I've tried looking up this error code on www.mysql.org via Google. I'm not sure it matters but if you look closely at the SQL below, you can see it is working on file page_path.tab4. It has already successfully loaded files page_path.tab1 thru page_path.tab3 I seriously could use a CLUE on what needs to be changed to avoid this error. TIA! Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 183 to server version: 3.23.58-max Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql LOAD DATA CONCURRENT LOCAL INFILE '../data/mysql/initial_20050318_102517/page_path.tab4' - REPLACE INTO TABLE page_path - FIELDS TERMINATED BY '^' OPTIONALLY ENCLOSED BY '' LINES TERMINATED BY '\n' - (PAGE_PATH_ID,NAME,DATE_CREATED); ERROR 2013: Lost connection to MySQL server during query mysql [EMAIL PROTECTED]:/b/martgen/mysql/bingrep -i large show-variables.log large_files_support ON [EMAIL PROTECTED]:/b/martgen/mysql/binuname -a Linux sdb2.hitbox.com 2.4.21-4.ELsmp #1 SMP Fri Oct 3 17:52:56 EDT 2003 i686 i686 i386 GNU/Linux [EMAIL PROTECTED]:/b/martgen/mysql/bin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Ghost Table
I've spent the last few days modifying my website, and
when I published my files online, one of my tables was
knocked out of commission. It was really bizarre -
everything between the tbody/tbody tags
disappeared online. In other words, I didn't just lose
everything related to PHP/MySQL, I couldn't even see
tr or td in the source code.
I thought it was a HTML validation error or a problem
with one of my PHP scripts, but I haven't found any
smoking gun. Then I pasted a database table from
another page onto the problem page, and it works just
fine.
So copied the good table, then replaced the table and
field names with names from the problem table - and it
doesn't work.
So I suspect there's either something really weird
going on with this particular table - counties - or
there's some sort of conflict between MySQL verions.
My host hasn't upgraded to the latest MySQL. However,
that hasn't been a problem for me before, except that
I have to create tables independently due to that
collation stuff.
I put both tables online at
http://www.geoworld.org/na/usa/az/counties2/
...and I appended the source code below. (Notice that
I included error_reporting(E_ALL);)
Again, both tables work fine locally, but I'm now
getting undefined index errors online and wonder if
the latest MySQL version requires a different syntax
in my script. Any tips?
Thanks.
?php
$mycode = 'us-az';
$mytopic = 'cou';
[DATABASE CONNECTION]
echo 'head';
error_reporting(E_ALL);
?
/head
body
?php
$colors = array( '#eee', '', '#cfc', '' );
$n=0;
$size=count($colors);
$result = mysql_query('select count(*) from
weatherna');
if (($result) (mysql_result ($result , 0) 0)) {
// continue here with the code that starts
//$res = mysql_query (SELECT * FROM type.
} else {
die('Invalid query: ' . mysql_error());
}
{
$res = mysql_query (SELECT * FROM weatherna WHERE
weatherna.IDArea = '$mycode')
or die (mysql_error());
echo 'table class=tabweather id=tabtemp
style=float: left; margin-right: 25px;
trtd class=tdmonth id=tdjan
colspan=2Jan/tdtd class=tdmonth id=tdfeb
colspan=2Feb/tdtd class=tdmonth id=tdmar
colspan=2Mar/tdtd class=tdmonth id=tdapr
colspan=2Apr/td/tr';
//!-- BeginDynamicTable --
$rowcounter=0;
while ($row = mysql_fetch_array ($res)) {
$c=$colors[$rowcounter++%$size];
echo tr style=\background-color:$c\.
$_SERVER['PHP_SELF'] .'?id='. $row['IDArea'] .
td. $row['JanHot'] .deg;/tdtd.
$row['JanCold'] .deg;/tdtd. $row['FebHot']
.deg;/tdtd. $row['FebCold'] .deg;/tdtd.
$row['MarHot'] .deg;/tdtd. $row['MarCold']
.deg;/tdtd. $row['AprHot'] .deg;/tdtd
class='tdcold'. $row['AprCold']
.deg;/td/tr\n;
}
}
?
/table
Cool Table
?php
$colors = array( '#eee', '', '#cfc', '' );
$n=0;
$size=count($colors);
$result = mysql_query('select count(*) from
counties');
if (($result) (mysql_result ($result , 0) 0)) {
// continue here with the code that starts
//$res = mysql_query (SELECT * FROM type.
} else {
die('Invalid query: ' . mysql_error());
}
{
$res = mysql_query (SELECT * FROM counties WHERE
counties.seat = 'Lafayette')
or die (mysql_error());
echo 'table
trtdSeat/tdtdArea/td/tr';
//!-- BeginDynamicTable --
$rowcounter=0;
while ($row = mysql_fetch_array ($res)) {
$c=$colors[$rowcounter++%$size];
echo 'XXX';
echo mysql_num_rows($res);
echo tr style=\background-color:$c\
td. $row['seat'] ./tdtd. $row['area']
./td/tr\n;
}
}
?
/table
/body
/html
__
Do you Yahoo!?
Make Yahoo! your home page
http://www.yahoo.com/r/hs
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Converting Integer values to date type
In the last episode (Mar 18), Mahmoud Badreddine said: Hello, I have a table with separate integer values for the day, month and year. I would like to group them all under one field of type date. I tried a few commands but I haven't captured the right syntax yet. so if the field names are dayVal,monthVal and YearVal in talbeDummy I am doing the following select str_to_date(DayVal.MonthVal.YearVal,'%d.%m.%Y') from tableDummy; CONCAT(DayVal, ., MonthVal, ., YearVal) -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Ghost Table
Please ignore this thread; I suddenly stumbled over the solution, even if I can't explain it. :) __ Do you Yahoo!? Yahoo! Small Business - Try our new resources site! http://smallbusiness.yahoo.com/resources/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Converting Integer values to date type
I got it working , thank you.
I only could do what I wanted to do in multisteps however. I couldn't
figure out the nested querying , and it's bugging me.
Here's how I issued it.
mysql update tableDummy set newDate=(select
str_to_date('(concat(DayVal,.,MonthVal,.,YearVal))','%d.%m.%Y'));
And although I got an error, it did give all zero values for the
date(e.g -00-00)
no big deal now that I have what I want, but if someone can pick out
my error, would be greatly apreciated.
On Fri, 18 Mar 2005 13:31:14 -0600, Dan Nelson [EMAIL PROTECTED] wrote:
In the last episode (Mar 18), Mahmoud Badreddine said:
Hello,
I have a table with separate integer values for the day, month and year.
I would like to group them all under one field of type date.
I tried a few commands but I haven't captured the right syntax yet.
so if the field names are dayVal,monthVal and YearVal in talbeDummy
I am doing the following
select str_to_date(DayVal.MonthVal.YearVal,'%d.%m.%Y') from tableDummy;
CONCAT(DayVal, ., MonthVal, ., YearVal)
--
Dan Nelson
[EMAIL PROTECTED]
--
-Mahmoud Badreddine
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replicating InnoDB tables in new database
Hello. See: http://dev.mysql.com/doc/mysql/en/moving.html Andy Hall [EMAIL PROTECTED] wrote: Hi, I have tried the following process in order to try and replicate a database with InnoDB files: 1. created a new database in PHPMyAdmin 2. via command line, copied all the .frm files from the old database directory into the new database directory 3. changed all the ownership and permissions 4. restarted mysql The database is now recognised in PHPMyAdmin, but when I click on any of the tables I get the message cannot find [table].InnoDB. Originally, the tables in the source database were MyISAM and then converted to InnoDB. I tried renaming one of the [table].frm files to [table].InnoDB, but now this does not show up on the table list. I tried the described method as I have done this before with MyISAM tables successfully. What am I missing? Or is a completely invalid way to move the database? Would a server restart fix it? Thanks Andy Hall. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: show all running queries on linux
Hello. See: http://dev.mysql.com/doc/mysql/en/show-processlist.html How can I see the running queries on a linux comp? Reinhart Viane [EMAIL PROTECTED] wrote: -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: update a field with multiple value
Hello. but what I want to do is UPDATE variableInfo SET variable='A, B' WHERE variable is null; when I use that command, mysql does not understand the syntax. How to update a field with multiple value or with an array? Multiple values could hold the SET columns, But I think that this is not exactly what you want. See: http://dev.mysql.com/doc/mysql/en/set.html Does MySQL server produce an error on your update statement? The syntax looks correct. Eko Budiharto [EMAIL PROTECTED] wrote: [-- text/plain, encoding 7bit, charset: us-ascii, 35 lines --] Hi, I am trying to to update one field with multiple value. I tried with regulare update command syntax does not work. How to update a field with multiple value. regular update syntax is this, UPDATE variableInfo SET variable='A' WHERE variable is null; but what I want to do is UPDATE variableInfo SET variable='A, B' WHERE variable is null; when I use that command, mysql does not understand the syntax. How to update a field with multiple value or with an array? +-+ + variable + +-+ + + + + +-+ but I want to update this column into +-+ + variable + +-+ + + + A, B + +-+ thank you in advance. - Do you Yahoo!? Yahoo! Small Business - Try our new resources site! -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with LIKE
Hi Mevers,
SELECT * FROM woorden WHERE string LIKE % woord %
It's not going to be efficient (at all) but you can do:
SELECT * FROM woorden WHERE foo LIKE CONCAT('%', woord, '%')
Regards,
Jeremy
--
Jeremy Cole
Technical Yahoo - MySQL (Database) Geek
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql/snort/webmin/permissions
Hi All,
coupla questions. 1stly, I can access mysql from webmin and start it,
along with being able to start/stop with service mysql start/stop from
cli. I'm also following this tutorial, by Patrick Harper,
http://216.239.57.104/search?q=cache:GHt0q9MF1coJ:www.infosecwriters.com/text_resources/pdf/snort_base_fc3.pdf+snort+on+fedora+3hl=enclient=firefox-a
for snort/php/mysql/FC3/apache/ssl
and wanna know why I can't access mysql from the prompt with mysql -h
mysql -p. All I get is,
[EMAIL PROTECTED] pcre-5.0]# mysql -h mysql -p
-bash: mysql: command not found
I want to be able to do the following from this tutorial,
mysql SET PASSWORD FOR [EMAIL PROTECTED]('password');
Query OK, 0 rows affected (0.25 sec)
mysql create database *snort*;
Query OK, 1 row affected (0.01 sec)
mysql grant INSERT,SELECT on root.* to [EMAIL PROTECTED];
Query OK, 0 rows affected (0.02 sec)
mysql SET PASSWORD FOR
[EMAIL PROTECTED]('password_from_snort.conf');
Query OK, 0 rows affected (0.25 sec)
mysql grant CREATE, INSERT, SELECT, DELETE, UPDATE on *snort*.* to
[EMAIL PROTECTED];
Query OK, 0 rows affected (0.02 sec)
mysql grant CREATE, INSERT, SELECT, DELETE, UPDATE on *snort*.* to *snort*;
Query OK, 0 rows affected (0.02 sec)
mysql exit
bye
I used webmin to create a new DB, snort, and gave permission to Username
snort to create, insert, delete, select and update from host
permissions. Is it the same as above..? 1st time to use
webmin/mysql/snort/apache. Cheers.
Mark Sargent.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [MySQL] mysql/snort/webmin/permissions
Mark Sargent wrote: [EMAIL PROTECTED] pcre-5.0]# mysql -h mysql -p -bash: mysql: command not found You don't have mysql in root's path. Once you fix that little problem, you should be all set. -- H | I haven't lost my mind; it's backed up on tape somewhere. + Ashley M. Kirchner mailto:[EMAIL PROTECTED] . 303.442.6410 x130 IT Director / SysAdmin / WebSmith . 800.441.3873 x130 Photo Craft Imaging . 3550 Arapahoe Ave. #6 http://www.pcraft.com . . .. Boulder, CO 80303, U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
