RE: How to become a DBA on MySQL
Jerry, Do you know of any reputable online classes that you could point me to? Keith On Tue, 2010-07-13 at 14:25 -0400, Jerry Schwartz wrote: You might want to see if there are any online classes that could get you started. I usually start by reading a big, thick book -- but I've found that spending a week or two on an introductory-level online class gives me a big head start. Modern documentation tends to be lacking (IMNSHO). All too many reference books are written on the principle of If you know exactly what you want to do, here's roughly how to do it. You can wander around for a long time not knowing what a feature is for. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.com -Original Message- From: Claudio Nanni [mailto:claudio.na...@gmail.com] Sent: Tuesday, July 13, 2010 1:56 PM To: alba.albetti Cc: mysql Subject: Re: How to become a DBA on MySQL Reference is everywhere, but you need a sort of starting point. In your case I would say get this bookhttp://www.amazon.com/MySQL-4th-Paul- DuBois/dp/0672329387/ref=sr_1_1?ie=UTF8s=booksqid=1279043056sr=1-1 and read: - Part III - Part IV-D (quick read) You will benefit from Part I as well as developer. In my opinion this book is clear and linear, good to learn MySQL. When you will be working as DBA keep this onehttp://oreilly.com/catalog/9780596003067 in the drawer. Claudio 2010/7/13 alba.albetti alba.albe...@libero.it Hi, I'd like to know which are the main tasks for a DBA, so in addition of the on-line tutorial of MySQL, is there anyone telling me whether there exists some tutorial (better if in PDF) where it's possible to undestand and learn all you need for managing the MySQL DB as DBA? I mean I'd like to read something explaining what are datafiles, tablespaces, ... and what usually a DBA is asked to do (creating databases, starting up and shutting down the database, managing the database's storage structures, making database backups and performing recovery, monitoring and tuning performance, ...) In other words I'd like to read how to manage a DB as DBA, because I've always worked only as developer and not as DBA. Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=claudio.na...@gmail.com -- Claudio Keith J. Clark Business ManagerOwner The BookwormWaterloo Hosting Quality Used Books Complete Web Hosting Provider www.k-wbookworm.com www.waterloohosting.com sa...@k-wbookworm.com sa...@waterloohosting.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: WHERE clause from AS result
You can use an Alias in ORDER BY but not in WHERE clauses. Keith On Thu, 2010-06-10 at 16:38 -0400, Steven Staples wrote: Ok, I have done it before, where I have used the AS result in an ORDER BY, but now, I can't figure out why I can't use it in a WHERE clause? SELECT `email`, (SELECT CONCAT(`phone_pref`, '-', `phone_suff`) FROM `pnums` WHERE `id`=`usertable`.`id`) AS pnum FROM `usertable` WHERE pnum LIKE '555-12%'; It gives me this error: Error Code : 1054 Unknown column 'pnum' in 'where clause' Any ideas? Steven Staples Keith J. Clark Business ManagerOwner The BookwormWaterloo Hosting Quality Used Books Complete Web Hosting Provider www.k-wbookworm.com www.waterloohosting.com sa...@k-wbookworm.com sa...@waterloohosting.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Count Query question
Bob, Here are a few rows of my data: 17462, 0, '0929998596', '/GraphicNovels/0929998596.jpg', '8.5000', '2010-05-12 19:02:47', '2008-10-01 00:00:00', '2008-10-01 00:00:00', '0.50', 1, 1, 7429, 0, '1', 17461, 1, '1561481912', '/Cooking/1561481912.jpg', '3.', '2010-05-12 19:00:17', '2008-10-01 00:00:00', '2008-10-01 00:00:00', '0.50', 1, 1, 7428, 0, '1', Here is the query I ran: SELECT date(products_date_available) as Date, COUNT(products_quantity) as 'Titles Available', SUM(products_quantity) as 'Books Available' FROM products WHERE products_quantity 0 GROUP BY date(products_date_available); And I only got back two rows from over 2 years of daily entries: '2008-01-01', 3327, '3736' '2008-10-01', 2739, '2904' I'm not sure I understand where I'm going wrong. Keith On Wed, 2010-05-12 at 21:54 -0500, Bob Cole wrote: Kevin: I assumed the following data: products_id products_date_available products_quantity 112010-05-01 1 112010-05-02 0 112010-05-03 3 112010-05-04 3 112010-05-05 3 112010-05-06 1 112010-05-07 0 112010-05-08 3 112010-05-09 3 112010-05-10 3 112010-05-11 3 112010-05-12 3 222010-05-01 1 222010-05-02 2 222010-05-03 0 222010-05-04 3 222010-05-05 3 222010-05-06 1 222010-05-07 0 222010-05-08 3 222010-05-09 0 222010-05-10 3 222010-05-11 3 222010-05-12 3 332010-05-01 1 332010-05-02 2 332010-05-03 3 332010-05-04 3 332010-05-05 3 332010-05-06 0 332010-05-07 0 332010-05-08 3 332010-05-09 3 332010-05-10 0 332010-05-11 3 332010-05-12 3 and used the following query: SELECT products_date_available, COUNT(products_quantity), SUM(products_quantity) FROM products WHERE products_quantity 0 GROUP BY products_date_available and got the following results: products_date_available COUNT SUM 2010-05-01 00:00:00 3 3 2010-05-02 00:00:00 2 4 2010-05-03 00:00:00 2 6 2010-05-04 00:00:00 3 9 2010-05-05 00:00:00 3 9 2010-05-06 00:00:00 2 2 2010-05-08 00:00:00 3 9 2010-05-09 00:00:00 2 6 2010-05-10 00:00:00 2 6 2010-05-11 00:00:00 3 9 2010-05-12 00:00:00 3 9 One line for each day except that 2010-05-07 is missing because each product had 0 quantity on that day. For example, on 2010-05-01, there were 3 products (each with a quantity of 1) for a total quantity of 3. I wonder if I am representing your situation correctly. What am I missing? Bob On May 12, 2010, at 8:00 PM, Keith Clark wrote: Hi Bob, No, actually it does not. I'm looking for the count of items. From your query example I only get two rows. This table has over 2 1/2 years of daily sales data. Maybe I'm not stating my question correctly...h Thanks for responding though, greatly appreciated. Keith On Wed, 2010-05-12 at 19:46 -0500, Bob Cole wrote: Keith: Does this work? SELECT products_date_available, COUNT(products_quantity) FROM products WHERE products_quantity 0 GROUP BY products_date_available Hope this helps, Bob On May 12, 2010, at 3:06 PM, Keith Clark wrote: On Wed, 2010-05-12 at 10:13 -0400, Keith Clark wrote: Chris, Here is my full table definition: CREATE TABLE `products` ( `products_id` int(15) NOT NULL AUTO_INCREMENT, `products_quantity` int(4) NOT NULL, `products_model` varchar(15) NOT NULL DEFAULT '', `products_image` varchar(64) DEFAULT NULL, `products_price` decimal(15,4) DEFAULT NULL, `products_date_added` timestamp NULL DEFAULT CURRENT_TIMESTAMP, `products_last_modified` datetime DEFAULT '2008-10-01 00:00:00', `products_date_available` datetime DEFAULT '2008-10-01 00:00:00', `products_weight` decimal(5,2) DEFAULT '0.50', `products_status` tinyint(1) NOT NULL DEFAULT '1', `products_tax_class_id` int(11) DEFAULT '1', `manufacturers_id` int(11) DEFAULT NULL, `products_ordered` int(11) DEFAULT '0', `products_format` varchar(20) DEFAULT NULL, `abebooks_price` decimal(15,4) DEFAULT NULL, PRIMARY KEY (`products_id`,`products_model`), UNIQUE KEY `products_model` (`products_model`), KEY `idx_products_date_added` (`products_date_added`), KEY `manufacturers_id` (`manufacturers_id`) ) ENGINE=MyISAM AUTO_INCREMENT=17418 DEFAULT CHARSET=latin1 So, I'd like to create a report that grouped by products_date_available, counts all records before products_date_available with a products_quantity0. I
Count Query question
I'm trying to produce a report that will tell me how many products were available with a Quantity0 before a certain date, and have that ordered by date. Table: Date Quantity Result desired DateQuantity Available May 1 5000 May 2 5050 May 3 5075 Thanks, Keith -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Count Query question
Chris, Here is my full table definition: CREATE TABLE `products` ( `products_id` int(15) NOT NULL AUTO_INCREMENT, `products_quantity` int(4) NOT NULL, `products_model` varchar(15) NOT NULL DEFAULT '', `products_image` varchar(64) DEFAULT NULL, `products_price` decimal(15,4) DEFAULT NULL, `products_date_added` timestamp NULL DEFAULT CURRENT_TIMESTAMP, `products_last_modified` datetime DEFAULT '2008-10-01 00:00:00', `products_date_available` datetime DEFAULT '2008-10-01 00:00:00', `products_weight` decimal(5,2) DEFAULT '0.50', `products_status` tinyint(1) NOT NULL DEFAULT '1', `products_tax_class_id` int(11) DEFAULT '1', `manufacturers_id` int(11) DEFAULT NULL, `products_ordered` int(11) DEFAULT '0', `products_format` varchar(20) DEFAULT NULL, `abebooks_price` decimal(15,4) DEFAULT NULL, PRIMARY KEY (`products_id`,`products_model`), UNIQUE KEY `products_model` (`products_model`), KEY `idx_products_date_added` (`products_date_added`), KEY `manufacturers_id` (`manufacturers_id`) ) ENGINE=MyISAM AUTO_INCREMENT=17418 DEFAULT CHARSET=latin1 So, I'd like to create a report that grouped by products_date_available, counts all records before products_date_available with a products_quantity0. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Count Query question
On Wed, 2010-05-12 at 10:13 -0400, Keith Clark wrote: Chris, Here is my full table definition: CREATE TABLE `products` ( `products_id` int(15) NOT NULL AUTO_INCREMENT, `products_quantity` int(4) NOT NULL, `products_model` varchar(15) NOT NULL DEFAULT '', `products_image` varchar(64) DEFAULT NULL, `products_price` decimal(15,4) DEFAULT NULL, `products_date_added` timestamp NULL DEFAULT CURRENT_TIMESTAMP, `products_last_modified` datetime DEFAULT '2008-10-01 00:00:00', `products_date_available` datetime DEFAULT '2008-10-01 00:00:00', `products_weight` decimal(5,2) DEFAULT '0.50', `products_status` tinyint(1) NOT NULL DEFAULT '1', `products_tax_class_id` int(11) DEFAULT '1', `manufacturers_id` int(11) DEFAULT NULL, `products_ordered` int(11) DEFAULT '0', `products_format` varchar(20) DEFAULT NULL, `abebooks_price` decimal(15,4) DEFAULT NULL, PRIMARY KEY (`products_id`,`products_model`), UNIQUE KEY `products_model` (`products_model`), KEY `idx_products_date_added` (`products_date_added`), KEY `manufacturers_id` (`manufacturers_id`) ) ENGINE=MyISAM AUTO_INCREMENT=17418 DEFAULT CHARSET=latin1 So, I'd like to create a report that grouped by products_date_available, counts all records before products_date_available with a products_quantity0. I don't think I'm asking this question properly. For every date in products_date_available in the table, I'd like to know the count of items available with products_quantity0 up until that date. So if there are 500 days in the table, there should be 500 rows in the report. Each showing the products available as of that date in time. I hope that clarifies it. I can write a query to do so for each individual date, just not a report for all dates at the same time. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Count Query question
Hi Bob, No, actually it does not. I'm looking for the count of items. From your query example I only get two rows. This table has over 2 1/2 years of daily sales data. Maybe I'm not stating my question correctly...h Thanks for responding though, greatly appreciated. Keith On Wed, 2010-05-12 at 19:46 -0500, Bob Cole wrote: Keith: Does this work? SELECT products_date_available, COUNT(products_quantity) FROM products WHERE products_quantity 0 GROUP BY products_date_available Hope this helps, Bob On May 12, 2010, at 3:06 PM, Keith Clark wrote: On Wed, 2010-05-12 at 10:13 -0400, Keith Clark wrote: Chris, Here is my full table definition: CREATE TABLE `products` ( `products_id` int(15) NOT NULL AUTO_INCREMENT, `products_quantity` int(4) NOT NULL, `products_model` varchar(15) NOT NULL DEFAULT '', `products_image` varchar(64) DEFAULT NULL, `products_price` decimal(15,4) DEFAULT NULL, `products_date_added` timestamp NULL DEFAULT CURRENT_TIMESTAMP, `products_last_modified` datetime DEFAULT '2008-10-01 00:00:00', `products_date_available` datetime DEFAULT '2008-10-01 00:00:00', `products_weight` decimal(5,2) DEFAULT '0.50', `products_status` tinyint(1) NOT NULL DEFAULT '1', `products_tax_class_id` int(11) DEFAULT '1', `manufacturers_id` int(11) DEFAULT NULL, `products_ordered` int(11) DEFAULT '0', `products_format` varchar(20) DEFAULT NULL, `abebooks_price` decimal(15,4) DEFAULT NULL, PRIMARY KEY (`products_id`,`products_model`), UNIQUE KEY `products_model` (`products_model`), KEY `idx_products_date_added` (`products_date_added`), KEY `manufacturers_id` (`manufacturers_id`) ) ENGINE=MyISAM AUTO_INCREMENT=17418 DEFAULT CHARSET=latin1 So, I'd like to create a report that grouped by products_date_available, counts all records before products_date_available with a products_quantity0. I don't think I'm asking this question properly. For every date in products_date_available in the table, I'd like to know the count of items available with products_quantity0 up until that date. So if there are 500 days in the table, there should be 500 rows in the report. Each showing the products available as of that date in time. I hope that clarifies it. I can write a query to do so for each individual date, just not a report for all dates at the same time. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=bobc...@earthlink.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
order by numeric value
I have the following statement: select chart_of_accounts.accountname as Account, concat('$',format(coalesce(sum(sales_journal_entries.debit),0),2)) as Debit, concat('$',format(coalesce(sum(sales_journal_entries.credit),0),2)) as Credit, concat('$',format(coalesce(sum(sales_journal_entries.credit),0)-coalesce(sum(sales_journal_entries.debit),0),2)) as Balance from sales_journal_entries left join sales_journal on sales_journal.journalID=sales_journal_entries.journalID left join chart_of_accounts on chart_of_accounts.accountID=sales_journal_entries.accountID where sales_journal.date '2008-12-31' and sales_journal.date '2010-01-01' group by sales_journal_entries.accountID order by Balance asc; and I'd like the output to be sorted by the Balance according to the numberic value, but it is sorting by the string result. I tried abs(Balance) but I get the following error: 1247 Reference 'Balance' not supported (reference to group function) I'm not sure I understand the error. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: order by numeric value
On Wed, 2010-04-28 at 00:18 +0200, Carsten Pedersen wrote: Keith Clark skrev: I have the following statement: select chart_of_accounts.accountname as Account, concat('$',format(coalesce(sum(sales_journal_entries.debit),0),2)) as Debit, concat('$',format(coalesce(sum(sales_journal_entries.credit),0),2)) as Credit, concat('$',format(coalesce(sum(sales_journal_entries.credit),0)-coalesce(sum(sales_journal_entries.debit),0),2)) as Balance from sales_journal_entries left join sales_journal on sales_journal.journalID=sales_journal_entries.journalID left join chart_of_accounts on chart_of_accounts.accountID=sales_journal_entries.accountID where sales_journal.date '2008-12-31' and sales_journal.date '2010-01-01' group by sales_journal_entries.accountID order by Balance asc; and I'd like the output to be sorted by the Balance according to the numberic value, but it is sorting by the string result. I tried abs(Balance) but I get the following error: 1247 Reference 'Balance' not supported (reference to group function) I'm not sure I understand the error. Balance is the result of a string operation (concat), and abs is a numeric function that won't work on strings. You should add a field to the result with the numerical value of Balance, and then sort on that. / Carsten You cannot CONVERT at the order by statement? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: order by numeric value
But I'd prefer not to see the extra sorting field. Keith On Tue, 2010-04-27 at 15:49 -0700, Gavin Towey wrote: That won't work on a value like $1.00 select CAST('$1.00' as decimal(8,2)); +---+ | CAST('$1.00' as decimal(8,2)) | +---+ | 0.00 | +---+ 1 row in set, 1 warning (0.00 sec) +-+--++ | Level | Code | Message| +-+--++ | Warning | 1292 | Truncated incorrect DECIMAL value: '$1.00' | +-+--++ 1 row in set (0.00 sec) It would have to be something like: select CAST(REPLACE('$1.00','$','') as decimal(8,2)) ; +---+ | CAST(REPLACE('$1.00','$','') as decimal(8,2)) | +---+ | 1.00 | +---+ 1 row in set (0.00 sec) Which in that case, it's better to just select balance without the dollar sign and order on that column. Regards, Gavin Towey -Original Message- From: DaWiz [mailto:da...@dawiz.net] Sent: Tuesday, April 27, 2010 3:46 PM To: Keith Clark; mysql@lists.mysql.com Subject: Re: order by numeric value Try order by CAST(Balance as decimal(8,2)) asc; Cast will work in the order by. Glenn Vaughn - Original Message - From: Keith Clark keithcl...@k-wbookworm.com To: mysql@lists.mysql.com Sent: Tuesday, April 27, 2010 3:52 PM Subject: order by numeric value I have the following statement: select chart_of_accounts.accountname as Account, concat('$',format(coalesce(sum(sales_journal_entries.debit),0),2)) as Debit, concat('$',format(coalesce(sum(sales_journal_entries.credit),0),2)) as Credit, concat('$',format(coalesce(sum(sales_journal_entries.credit),0)-coalesce(sum(sales_journal_entries.debit),0),2)) as Balance from sales_journal_entries left join sales_journal on sales_journal.journalID=sales_journal_entries.journalID left join chart_of_accounts on chart_of_accounts.accountID=sales_journal_entries.accountID where sales_journal.date '2008-12-31' and sales_journal.date '2010-01-01' group by sales_journal_entries.accountID order by Balance asc; and I'd like the output to be sorted by the Balance according to the numberic value, but it is sorting by the string result. I tried abs(Balance) but I get the following error: 1247 Reference 'Balance' not supported (reference to group function) I'm not sure I understand the error. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=my...@dawiz.net This message contains confidential information and is intended only for the individual named. If you are not the named addressee, you are notified that reviewing, disseminating, disclosing, copying or distributing this e-mail is strictly prohibited. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not accept liability for any loss or damage caused by viruses or errors or omissions in the contents of this message, which arise as a result of e-mail transmission. [FriendFinder Networks, Inc., 220 Humbolt court, Sunnyvale, CA 94089, USA, FriendFinder.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: order by numeric value
On Wed, 2010-04-28 at 08:57 +1000, Jesper Wisborg Krogh wrote: On Wed, 28 Apr 2010 08:53:57 Keith Clark wrote: But I'd prefer not to see the extra sorting field. You don't need to select a field in order to be able to order by it. So select chart_of_accounts.accountname as Account, concat('$',format(coalesce(sum(sales_journal_entries.debit),0),2)) as Debit, concat('$',format(coalesce(sum(sales_journal_entries.credit),0),2)) as Credit, concat('$',format(coalesce(sum(sales_journal_entries.credit),0)-coalesce(sum(sales_journal_entries.debit),0),2)) as Balance from sales_journal_entries left join sales_journal on sales_journal.journalID=sales_journal_entries.journalID left join chart_of_accounts on chart_of_accounts.accountID=sales_journal_entries.accountID where sales_journal.date '2008-12-31' and sales_journal.date '2010-01-01' group by sales_journal_entries.accountID order by coalesce(sum(sales_journal_entries.credit),0)-coalesce(sum(sales_journal_entries.debit),0) asc; should do the trick. Jesper Perfectamundo! I thought there would have been a more elegant way but this works just fine. Thanks. Keith -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Nested inserts possible?
I have two tables and I have data to insert into both at the same time, but the second table depends on an ID that is auto created in the first table. I'm not sure exactly how to accomplish this. Table_One Table_One_Index_ID Data_One Date_Two Table_Two Table_Two_Index_ID Table_One_Index_ID Data_Three Data_Four -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Nested inserts possible?
Johan, That seems to be the ticket. Thanks! Keith On Fri, 2010-03-12 at 16:54 +0100, Johan De Meersman wrote: Have a look at last_insert_id(). On Fri, Mar 12, 2010 at 3:48 PM, Keith Clark keithcl...@k-wbookworm.com wrote: I have two tables and I have data to insert into both at the same time, but the second table depends on an ID that is auto created in the first table. I'm not sure exactly how to accomplish this. Table_One Table_One_Index_ID Data_One Date_Two Table_Two Table_Two_Index_ID Table_One_Index_ID Data_Three Data_Four -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Edit Stored Procedures
I'm trying to edit one of my stored procedures using MySQL Query Browser and this is what I get back as a listing when I select Edit All Procedures: DROP PROCEDURE IF EXISTS `kwbo4418_2008bookworminventory`.`UpdateStock` $$ /*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$ (null) $$ /*!50003 SET SESSION sql_mo...@temp_sql_mode */ $$ I used to edit it in this fashion just fine, and I know the procedure is there on the server, as we run it many times a day successfully. Keith -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org