Re: Too many aliases
2011/08/03 12:46 +0200, Rik Wasmus But the main thing is it helps to distinguish tables in joins having the same table more then once (and of course results from subqueries etc.): SELECT first.* FROM tablename first LEFT JOIN tablename second ONfirst.some_id = second.some_id AND first.id != second.id WHERE second.id IS NULL Well, yes, here it is needful. But it seems to me from most of the examples that people here post, that they have the idea that it is the style always to use one-letter aliases, whether it is helpful or not. Now I do not do this, but I often see examples where a field for one purpose has in one table one name, and in another table another, slightly different, name, and then, too, I see alias used, although, in this case, no table name at all is needed. (I like to use the same field name in all tables where it has the same function.) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Too many aliases
I agree. I use the same column name in all tables where it has the same function - but I consistently add a suffix or prefix. And yes, it is the old fashion way David. -Original Message- From: h...@tbbs.net [mailto:h...@tbbs.net] Sent: Thursday, August 04, 2011 8:26 AM To: r...@grib.nl Cc: mysql@lists.mysql.com Subject: Re: Too many aliases 2011/08/03 12:46 +0200, Rik Wasmus But the main thing is it helps to distinguish tables in joins having the same table more then once (and of course results from subqueries etc.): SELECT first.* FROM tablename first LEFT JOIN tablename second ONfirst.some_id = second.some_id AND first.id != second.id WHERE second.id IS NULL Well, yes, here it is needful. But it seems to me from most of the examples that people here post, that they have the idea that it is the style always to use one-letter aliases, whether it is helpful or not. Now I do not do this, but I often see examples where a field for one purpose has in one table one name, and in another table another, slightly different, name, and then, too, I see alias used, although, in this case, no table name at all is needed. (I like to use the same field name in all tables where it has the same function.) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=dle...@us.univision.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: very large import
On 8/3/2011 20:36, Nuno Tavares wrote: The following page has some nice interesting stuff, assuming you have a reasonable configuration in place (innodb_buffer_pool, etc[1]) http://download.oracle.com/docs/cd/E17952_01/refman-5.5-en/optimizing-innodb-bulk-data-loading.html ... The same content is also available here: http://dev.mysql.com/doc/refman/5.5/en/optimizing-innodb-bulk-data-loading.html It may be an easier address to reach. -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Too many aliases
-Original Message- From: David Lerer [mailto:dle...@us.univision.com] Sent: Wednesday, August 03, 2011 10:25 AM To: mysql@lists.mysql.com Subject: RE: Too many aliases I rarely use aliases (unless rarely required in self-join queries). Yes, the column names may be longer this way, but easy to refer to and easy to communicate (by specifying a table number). I wonder what others think about it. [JS] Back when I was trying to fit as much code on a punch-card as possible, the languages only supported short names, and the terminals ran at 10cps, every keystroke was precious. Now I routinely pay the price of extra keystrokes for readability: not just with things such as column names, but with parentheses and the like as well. Like you, I only use aliases when necessary. If I want brain-teasers, I'll do a crossword puzzle. But that's just me. 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.giiresearch.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
strange warnings after upgrade...
We just upgraded our mysql from 5.0.32 on debian lenny, to 5.1.49 on debian squish. I wasn't told that it was doing an incremental version upgrade, i was under the impression it was just going from 5.0.32 to 5.0.8x. Anyways, I am getting some weird issues now, that is filling up the syslog, and is looking for some insight on why??? Aug 4 10:07:17 --machine-- mysqld: 110804 10:07:17 [Warning] Statement may not be safe to log in statement format. Statement: UPDATE `freeradius`.`online_account` SET Aug 4 10:07:17 --machine-- mysqld: ^I^I^I^I^I`update_time`=NOW() Aug 4 10:07:17 --machine-- mysqld: ^I^I^I^I^I#, `type`='U' Aug 4 10:07:17 --machine-- mysqld: ^I^I^I^I^I, `dup_ip`='N' Aug 4 10:07:17 --machine-- mysqld: ^I^I^I^IWHERE `yearmonth`= NAME_CONST('v_yearmonth',201108) Aug 4 10:07:17 --machine-- mysqld: ^I^I^I^I^IAND `radacct_id`= NAME_CONST('v_radacct_id',358954) Aug 4 10:07:17 --machine-- mysqld: ^I^I^I^I^IAND `customer_id`= NAME_CONST('v_customer_id',0) Aug 4 10:07:17 --machine-- mysqld: ^I^I^I^I^IAND `customer_account_id`= NAME_CONST('v_customer_account_id',0) Aug 4 10:07:17 --machine-- mysqld: ^I^I^I^I^IAND `user_name`= NAME_CONST('v_user_name',_latin1'usern...@domain.net' COLLATE 'latin1_swedish_ci') Aug 4 10:07:17 --machine-- mysqld: ^I^I^I^I^IAND `framed_ip_address`= NAME_CONST('v_framed_ip_address' Aug 4 10:07:18 --machine-- mysqld: 110804 10:07:18 [Warning] Statement may not be safe to log in statement format. Statement: UPDATE `user` SET `last_refresh`=UNIX_TIMESTAMP() WHERE `user_id`='19' LIMIT 1 that is a direct pull from the syslog, i haven't changed anything (except the machine name and the email address it was adding) the first one, is called from a stored procedure, the second one, is called from my php script. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: very large import
Hi, I am new to MySQL and this listserv. I have a couple of questions about the recommendations at the link given below. 1) If autocommit is turned off during the large load, could this cause the underlying InnoDB log to grow too big? 2) If the unique checks and foreign key checks are turned off during the large load, are the checks run for the newly loaded data when they are turned back on? If not, what kind of errors would occur later if the loaded data contained duplicate keys or non-existent foreign keys? Kay Rozeboom Information Technology Enterprise Iowa Department of Administrative Services Telephone: 515.281.6139 Fax: 515.281.6137 Email: kay.rozeb...@iowa.gov -Original Message- From: a.sm...@ukgrid.net [mailto:a.sm...@ukgrid.net] Sent: Wednesday, August 03, 2011 3:25 PM To: supr_star Cc: mysql@lists.mysql.com Subject: Re: very large import Quoting supr_star suprstar1...@yahoo.com: Is there any way to speed up this process? by disabling indexes or something? I can't afford to be down for 3 more days... First stop, the mysql documentation: http://dev.mysql.com/doc/refman/5.1/en/innodb-tuning.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Too many aliases
Well, while we're on the subject of SQL style, can anyone tell me why I'm always seeing people prefixing the name of a table with something like tbl? For example: create table tblCUSTOMERS ( ... ); Seems to me that you should probably know that CUSTOMERS is a table, or is it just me? Looking forward to your input. Mike. On Thursday 04 August 2011 6:43:55 am David Lerer wrote: I agree. I use the same column name in all tables where it has the same function - but I consistently add a suffix or prefix. And yes, it is the old fashion way David. -Original Message- From: h...@tbbs.net [mailto:h...@tbbs.net] Sent: Thursday, August 04, 2011 8:26 AM To: r...@grib.nl Cc: mysql@lists.mysql.com Subject: Re: Too many aliases 2011/08/03 12:46 +0200, Rik Wasmus But the main thing is it helps to distinguish tables in joins having the same table more then once (and of course results from subqueries etc.): SELECT first.* FROM tablename first LEFT JOIN tablename second ONfirst.some_id = second.some_id AND first.id != second.id WHERE second.id IS NULL Well, yes, here it is needful. But it seems to me from most of the examples that people here post, that they have the idea that it is the style always to use one-letter aliases, whether it is helpful or not. Now I do not do this, but I often see examples where a field for one purpose has in one table one name, and in another table another, slightly different, name, and then, too, I see alias used, although, in this case, no table name at all is needed. (I like to use the same field name in all tables where it has the same function.) -- Take care and have fun, Mike Diehl. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Too many aliases
http://en.wikipedia.org/wiki/Hungarian_notation On Thu, Aug 4, 2011 at 9:41 AM, Mike Diehl mdi...@diehlnet.com wrote: Well, while we're on the subject of SQL style, can anyone tell me why I'm always seeing people prefixing the name of a table with something like tbl? For example: create table tblCUSTOMERS ( ... ); Seems to me that you should probably know that CUSTOMERS is a table, or is it just me? Looking forward to your input. Mike. On Thursday 04 August 2011 6:43:55 am David Lerer wrote: I agree. I use the same column name in all tables where it has the same function - but I consistently add a suffix or prefix. And yes, it is the old fashion way David. -Original Message- From: h...@tbbs.net [mailto:h...@tbbs.net] Sent: Thursday, August 04, 2011 8:26 AM To: r...@grib.nl Cc: mysql@lists.mysql.com Subject: Re: Too many aliases 2011/08/03 12:46 +0200, Rik Wasmus But the main thing is it helps to distinguish tables in joins having the same table more then once (and of course results from subqueries etc.): SELECT first.* FROM tablename first LEFT JOIN tablename second ONfirst.some_id = second.some_id AND first.id != second.id WHERE second.id IS NULL Well, yes, here it is needful. But it seems to me from most of the examples that people here post, that they have the idea that it is the style always to use one-letter aliases, whether it is helpful or not. Now I do not do this, but I often see examples where a field for one purpose has in one table one name, and in another table another, slightly different, name, and then, too, I see alias used, although, in this case, no table name at all is needed. (I like to use the same field name in all tables where it has the same function.) -- Take care and have fun, Mike Diehl. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=joh...@pixelated.net -- - Johnny Withers 601.209.4985 joh...@pixelated.net
Re: strange warnings after upgrade...
Your binary logging is set to statement format - the default, and the only one available in older versions. Current versions also support row-based logging. These formats have to do with safe replication, I recommend you check the release notes and the online documentation for more information. Debian mysql packages are patched to log to syslog instead of mysql.log; and since you upgraded to a version that supports both binlog formats, it is now warning you that some statements may cause inconsistencies in replication. If you're not using your binary logs for anything, I suggest you turn them off, as they are a slight performance hit. If you are using them, you may want to switch to row-based or maybe mixed logging format, as detailed in the online documentation; which will get rid of those messages. - Original Message - From: Steve Staples sstap...@mnsi.net To: mysql@lists.mysql.com Sent: Thursday, 4 August, 2011 4:17:50 PM Subject: strange warnings after upgrade... We just upgraded our mysql from 5.0.32 on debian lenny, to 5.1.49 on debian squish. I wasn't told that it was doing an incremental version upgrade, i was under the impression it was just going from 5.0.32 to 5.0.8x. Anyways, I am getting some weird issues now, that is filling up the syslog, and is looking for some insight on why??? Aug 4 10:07:17 --machine-- mysqld: 110804 10:07:17 [Warning] Statement may not be safe to log in statement format. Statement: UPDATE `freeradius`.`online_account` SET Aug 4 10:07:17 --machine-- mysqld: ^I^I^I^I^I`update_time`=NOW() Aug 4 10:07:17 --machine-- mysqld: ^I^I^I^I^I#, `type`='U' Aug 4 10:07:17 --machine-- mysqld: ^I^I^I^I^I, `dup_ip`='N' Aug 4 10:07:17 --machine-- mysqld: ^I^I^I^IWHERE `yearmonth`= NAME_CONST('v_yearmonth',201108) Aug 4 10:07:17 --machine-- mysqld: ^I^I^I^I^IAND `radacct_id`= NAME_CONST('v_radacct_id',358954) Aug 4 10:07:17 --machine-- mysqld: ^I^I^I^I^IAND `customer_id`= NAME_CONST('v_customer_id',0) Aug 4 10:07:17 --machine-- mysqld: ^I^I^I^I^IAND `customer_account_id`= NAME_CONST('v_customer_account_id',0) Aug 4 10:07:17 --machine-- mysqld: ^I^I^I^I^IAND `user_name`= NAME_CONST('v_user_name',_latin1'usern...@domain.net' COLLATE 'latin1_swedish_ci') Aug 4 10:07:17 --machine-- mysqld: ^I^I^I^I^IAND `framed_ip_address`= NAME_CONST('v_framed_ip_address' Aug 4 10:07:18 --machine-- mysqld: 110804 10:07:18 [Warning] Statement may not be safe to log in statement format. Statement: UPDATE `user` SET `last_refresh`=UNIX_TIMESTAMP() WHERE `user_id`='19' LIMIT 1 that is a direct pull from the syslog, i haven't changed anything (except the machine name and the email address it was adding) the first one, is called from a stored procedure, the second one, is called from my php script. -- 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