Re: mysql naming convention

need to do that. I suppose it just proves that, when it comes to coding practices, Your Mileage *Will* Vary. :-) James Harvard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: mysql naming convention

from that I agree with Douglas Sims that what is most readable is best. select user_email, user_address, user_postcode from users where user_id = %d; # Hmm select email, address, postcode from users where user_id = %d; # Less typing and more readable. James Harvard -- MySQL General Mailing Lis

Re: MySQL lock tables - bug or not?

Thanks for your reply. I repeated your test with the same results on 4.1.21 (database in question is on 4.1). I'll give the ISP another kick and see what they have to say. So there's no other reason why an ISP might not want to grant LOCK TABLES in a shared hosting environment? Thanks, James

MySQL lock tables - bug or not?

in the bugs db, nor is it listed in the manual as a side effect of granting 'lock tables' permissions. Does anyone know if it is a bug or not? Does anyone know whether LOCK TABLES really is a security risk in a shared server / multi-user environment? TIA, James Harvard -- MySQL

Re: MySQL 5.0.22 and show columns bug?

OK, fair enough. In that case I would think that filing a report on bugs.mysql.com would be your best way forward. At 8:32 am + 7/7/06, SciBit MySQL Team wrote: >While you are not wrong, James, is the length member suppose to denote the >maximum length of data contained in result's specified

Re: MySQL 5.0.22 and show columns bug?

Although I know nothing about C I imagine this is because the 'type' column can contain all the possible values from an ENUM or SET field. James Harvard At 10:30 am + 6/7/06, SciBit MySQL Team wrote: >Since a couple of recent stable versions back (and more recently, MySQL &g

RE: New to the group

7;t intend to write such an essay on this! Hope it is of some use. James Harvard >Ok, so you don't want info on databases, but on which language to use > to build a web site? You must sit down and determine what the site will be > used for and what features you *must hav

Re: MySQL Clustering

replication (multi-master replication) with >failover. > >Read the article >> > > http://dev.mysql.com/tech-resources/articles/advanced-mysql-replication.html HTH, James Harvard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: How do I get off this list that I do not remember joining in the first place!!!!

At 11:55 am +1200 26/5/06, Phil Robbins wrote: >I've read the notice AND tried to unsubscribe TWICE. I still get the mail. http://lists.mysql.com/troubleshoot.php HTH, James Harvard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:

Re: Fulltext search for term 'c++'

I think the '+' will not get into the index in the first place. So, there's probably no way to get a search to use the index. However if you want a consistant query format for your search you could probably get the correct result by wrapping the search term in double quotes: select f1 from t1 w

Re: Determine version of *.frm, *.MYD and *.MYI

> I know it a 4.1... But as I compiled it my self it is not so easy to figure > it out And it might take a while to trial-n-error all 4.1.x You should be able to see from the change log / version history pages in the manual which minor version releases introduced incompatible changes to the

Re: Problems with Mysql 5 and Visual Basic 5

I think you will need to give the list some more information! What error message do you get? Is it an error when running SQL queries, or a problem connecting to the database? James Harvard At 9:22 am -0300 24/4/06, Gabriel Mahiques wrote: >I migrated to mysql 5 but the applications with Vis

Re: Fulltext 3 letter words

indexes on any tables with a full-text index. Also, note the warning about using myisamchk further down the manual page. HTH James Harvard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: How to Verify Replication Status?

Never used it, but this might help: http://dev.mysql.com/doc/refman/5.0/en/checksum-table.html James Harvard At 5:00 pm -0700 19/4/06, Robinson, Eric wrote: >I have master-slave replication working fine. However, I worry about the >possibility of the master and slave accidentally getting

Re: Table Size

ase that was introduced in v5. http://dev.mysql.com/doc/refman/5.0/en/information-schema.html HTH, James Harvard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: drop table that doesn't exist

Not a db setting - the DROP statement itself. http://dev.mysql.com/doc/refman/5.0/en/drop-table.html HTH, James Harvard At 1:56 pm -0700 21/3/06, ChadDavis wrote: >I'm runnning a script that creates a few tables. I have line that drops the >tables before the creation of the tables j

Re: Accountability with MySQL

le with no matching rows. James Harvard At 11:27 am + 16/3/06, [EMAIL PROTECTED] wrote: >As someone totally unread in the theory of databases, that seems unduly >puritanical. I assume that what Date would propose is that you have another >table (related by master key) in which,

Re: Accountability with MySQL

But (in at least some situations) is not appropriate to record that you know that you don't have a value? I think the words of Donald Rumsfeld are appropriate here: "There are known knowns. These are things we know that we know. There are known unknowns. That is to say, there are things that we

Re: Accountability with MySQL

e to use >in many, many situations. It's not often you can say that two people are quite literally arguing about nothing! James Harvard (... being flippant because the actual arguments started going over my head about half-a-dozen posts ago...) -- MySQL General Mailing List For list archiv

Re: Accountability with MySQL

are *not* the same thing, the outcome (the absence of a value) *is* the same. So you're both right. Sort of. :-) James Harvard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Very large from

-JS browsers - i.e. they get the whole form on one page) James Harvard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Inner join with left join

At 5:08 pm -0800 22/2/06, Scott Haneda wrote: >I think we are close, thanks >ERROR 1120: Cross dependency found in OUTER JOIN. Examine your ON >conditions > > SELECT p.id, p.prod_name, sum(oi.quantity) as qty >> FROM Products p >> LEFT JOIN orders as o >> ON (p.id = oi.product_id) Maybe

Re: Query Complexity Limit Question [Newbie Question]

l heavily load the >system if you are not careful. Yes indeed. You need to think carefully about what indices you need on your tables. As has already been said, EXPLAIN SELECT is your friend! James Harvard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscr

Re: Unclear about key_len in EXPLAIN output

7;s not very clear what the relationship between a multi-part key and key_len is. From what the manual says I initially assumed that key_len simply represented the number off key parts used. I don't think a manual suffers from things being spelt out more clearly! James Harvard -- MySQL

Re: Unclear about key_len in EXPLAIN output

'That other statement' that I quoted is actually just part of the paragraph from the manual that you quoted! My point was that it doesn't say what units the key length is given in or explain _how_ to determine the number of parts used. However the more I think about it the more I see that it mu

Re: Differences between numbers of rows in tables

exact number of rows someone could still add another 20 rows in the second after you run SHOW TABLE STATUS. James Harvard At 5:54 pm -0700 1/2/06, Dan Trainor wrote: >So, now this has made me think here. If 'SHOW TABLE STATUS' only shows an >"estimate" of the number of

Unclear about key_len in EXPLAIN output

t correct? TIA, James Harvard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Sudden Mysql Crashes - table damage?

First of all I would check for file system corruption. IIRC there is a command line tool for repairing MyISAM tables, so you can use that without needing the MySQL database server. I'm sure there are full details in the manual: http://dev.mysql.com/doc/refman/5.0/en/ Good luck! James Ha

Re: Support between MySQL and PHP

MySQL versions >= 4.1 use a new, more secure authentication protocol. Probably the version of PHP you are using does not support it. Ah, here it is: http://dev.mysql.com/doc/refman/5.0/en/password-hashing.html HTH, James Harvard At 1:18 pm -0600 29/1/06, Philip R. Thompson wrote: >

Re: DB Tables on separate hardisks

nks-to-tables.html HTH, James Harvard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: UK Postcodes

is inflated, monopoly pricing which can only be hindering UK businesses from developing localised on-line services. James Harvard At 4:00 pm -0500 24/1/06, Rhino wrote: >You'd think that the people who run the post office in the UK - British >Telecom?? - would have had a number of en

Re: Troubles installing MySQL5 via Darwin Ports

cannot be read/written to), then download the appropriate installer from mysql.com and let the list know what problems you encounter with that. Good luck, James Harvard >On Jan 24, 2006, at 5:13 AM, James Harvard wrote: >>Is there a reason why you're using Darwin Ports and not the

Re: simple load query

Your FIELDS clause is not in the right place: http://dev.mysql.com/doc/refman/5.0/en/load-data.html HTH, James Harvard At 7:06 pm -0500 17/1/06, kalin mintchev wrote: > whats the problem with this: >load data infile 'stores.txt' into table useyourcash_sports_us (chain, >store,

Re: Query optimization

It might be a good idea if you could post the results of an EXPLAIN SELECT ... for a fast query and a slow query along with their actual SQL statements. James Harvard At 11:37 am -0800 12/1/06, Tripp Bishop wrote: >I've got a question regarding optimizing a query. -- MySQL General

Re: Geographical advice

dius of 3. Therefore you search WHERE x BETWEEN 3 AND 9 AND y BETWEEN 5 AND 11. I'm not certain but I think MySQL should be able to used a combined index of (x,y) for that. As you probably know you can use EXPLAIN SELECT to check whether MySQL is using an index. HTH, James Harvard At 12:01 p

Re: query with vars

You should be able to join the tables like this: select p1.id /* and other cols */ from people1 p1 inner join people2 p2 on p1.Lname = p2.Lname and left(p1.Fname, 3) = left(p2.Fname, 3) group by p1.id; HTH, James Harvard >I have two independently built tables of people. I am trying to ma

Re: join question

http://dev.mysql.com/doc/refman/4.1/en/example-maximum-column-group-row.html I'd translate it to your example, but it's bed-time here in England! HTH, James Harvard At 11:42 pm + 5/1/06, Terry Spencer wrote: >I have a question for clearer brains than mine. I would like to joi

Re: Strange insert

I assume you did not intend to post to this list, but if you did then this is a problem with your application code, not with MySQL, so I'm afraid this list is not the best place to ask. James Harvard At 11:19 am -0600 5/1/06, Ngim wrote: >Hi all, > >I have an one insert state

Re: SELECT help.

ay of doing this though, if your tables are very large. This might work too: select c_name, count(t.id) as t_count, max(t.date) as t_latest from customers c inner join transactions t on c.c_no = t.c_no group by c.c_no having t_count > 4 and t_latest < '2005-06-05'; HTH, James

Re: SELECT DISTINCT uses index but is still slow

y_2 bigint(20) UNSIGNED DEFAULT NULL, >>c_value bigint(20) UNSIGNED NOT NULL DEFAULT 0, >>c_quantity_1 bigint(20) UNSIGNED NOT NULL DEFAULT 0, >>c_quantity_2 bigint(20) UNSIGNED DEFAULT NULL, >>port_id tinyint(3) UNSIGNED NOT NULL DEFAULT 0, >>KEY date_id (date_id), >>KEY country_id (country_id), >>KEY comcode_id (comcode_id,date_id) >>) ENGINE=MyISAM ROW_FORMAT=FIXED; >> >>TIA, >>James Harvard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Inconsistent rows returned & examined in slow query log

the subsequent queries were not dealt with by the query cache. Any ideas? Thanks, James Harvard # Time: 060103 9:45:12 # [EMAIL PROTECTED]: lasso[lasso] @ localhost [127.0.0.1] # Query_time: 86 Lock_time: 0 Rows_sent: 12 Rows_examined: 6733255 select sql_cache dates.date_month from data_gb_e

SELECT DISTINCT uses index but is still slow

ULT 0, c_quantity_2 bigint(20) UNSIGNED DEFAULT NULL, port_id tinyint(3) UNSIGNED NOT NULL DEFAULT 0, KEY date_id (date_id), KEY country_id (country_id), KEY comcode_id (comcode_id,date_id) ) ENGINE=MyISAM ROW_FORMAT=FIXED; TIA, James Harvard -- MySQL General Mailing List For list archives: ht

RE: Changing types on the fly in select queries?

gt; 3) OR (year = 2007 AND period = 3 AND week > 1) ; Can't remember off the top of my head if that would be able to use an index on (year,period,week) though. Anyone? Good luck, James Harvard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Strange behavior with integer unsigned type...

IGINT (64 bit number), and IIRC MySQL uses 64 bit maths. FWIW my preferred web app middleware - Lasso - does the same thing (only with signed 64 bit numbers). HTH, James Harvard At 11:17 pm +0200 23/12/05, Gleb Paharenko wrote: >On both 4.1.16 and 5.0.17 I've got the same res

Re: Do I need to tweak my server variables for this SELECT statement?

tuff later, of course, but my-huge.cnf is a better starting point than my.cnf. James Harvard At 6:45 am -0800 23/12/05, Grant Giddens wrote: >I think that the reason the original query is so slow is that I don't have >enough RAM allocated to mysql. When the original query takes pla

Re: Reporting tools for summary data

? My client sells (well, they will in ten days time) various trade-flow reports from their web site, so it's all web-based. I use Lasso http://www.omnipilot.com/ as my middleware. Regards, James Harvard -- MySQL General Mailing List For list archives: http://lists.mysql.com

Re: Are primary keys essential?

In hindsight my thread title was misleading - sorry. Should have been "are primary keys _always_ essential?". JH -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Are primary keys essential?

for stuff like destination country, trade commodity category etc., but they are _not_ themselves referenced by any other table. Therefore I have not yet found, nor do I envisage finding, any use for an arbitrary auto_increment primary key. So why would/might I need a PK at all? TIA, James Harv

Are primary keys essential?

! Quicker for me, that is - sorry! TIA, James Harvard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Reason for Auto-increment primary keys?

icant. Heh, 12 years ago I woudn't have worried about shaving 40 MB off a 2 GB file, because 40 MB _was_ my hard disc! So presumably as the years go by increasing processor power and storage speed & size will mean we will no longer have to compromise on purity of db design to get acceptabl

Re: Do I need to tweak my server variables for this SELECT statement?

That's why I suggested simply using the supplied my-huge.cnf config file, because I assume it was contructed by some MySQL uber-guru who does know how much RAM to allocate to each variable! James Harvard > Like I mentioned before, I am tweaking the .cnf files by blind trial and >

Re: Do I need to tweak my server variables for this SELECT statement?

two tables? Also if you enable the slow query log, it tells you the actual number of rows examined - might be useful if you continue to have trouble. HTH & good luck, James Harvard >For the first time, I'm working with a really large database. I have 1 > SQL statement that b

Re: How to avoid sorting sorted tables ?

compare it with the last time the table was ordered. HTH, James Harvard At 9:29 am + 19/12/05, C.R.Vegelin wrote: >Now I use: ALTER TABLE t ORDER BY a, b, c, d, e, f; >This works fine, but takes about 13 minutes for 6 million rows. >Without inserts, deletes or other sorts, this PK o

Re: How to Square a number?

Maybe I'm missing something, but can't you just multiply the number by itself? select (3 * 3); select (int_col * int_col) as squared from table_name; James H At 5:36 pm -0600 18/12/05, mos wrote: >How do I square a number in MySQL 4.1? I thought it would be something simple >like: >select 3**2

Re: fulltext search

hypenated words. ... MATCH (col_name) AGAINST ('"s-au"' IN BOOLEAN MODE) ... HTH, James Harvard At 5:26 pm +0200 18/12/05, Octavian Rasnita wrote: >Please tell me how can I configure MySQL 5 in order to be able to search >(using fulltext indexes) for combined words like

Re: is there any BEEP command ?

Well it works on Mac OS X! >While there is no beep command, you can possibly get it to beep by selecting >the character code for a terminal beep (this is what the mysql client uses >internally) > >Try out: > >SELECT char(7); -- MySQL General Mailing List For list archives: http://lists.mysql.c

Re: Transactions (not rolling back on error)

Hi Cory - nice to see a fellow Lasso user here! I've not use transactions myself but I think you might be having a problem with autocommit. http://dev.mysql.com/doc/refman/5.0/en/commit.html HTH, James Harvard At 12:44 am -0700 15/12/05, Cory @ SkyVantage wrote: >I have a transaction

Re: [OT-ish] Hardware for MySQL server

Thanks for all the feedback on this. Is there any received wisdom on whether 1 dual core processor is better than 2 'normal' processors? Also, is there any advantage to SCSI over SATA? TIA, James Harvard -- MySQL General Mailing List For list archives: http://lists.mysql.co

[OT-ish] Hardware for MySQL server

Can anyone recommend any server brands available in the UK, or UK based companies that will build servers, supporting 3 discs (2 RAID & 1 for the OS)? Many thanks, James Harvard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]