Re: Detect if table exists from within MySQL?

Ryan Stille wrote: If I have to, I could resort to doing another query in my application (SHOW TABLES) and seeing if my table was returned in that list. But I was hoping for a more elegant way to do it, within the single query. Maybe you could use SHOW TABLES LIKE 'your_table';

Re: How to match a binary null in a varchar column???

Richard F. Rebel wrote: do I say REGEXP BINARY "" I have tried \000 \0 as they are common representations for binary null. Have you tried "WHERE your_column LIKE '%\0%'"? That works for me. -- Keith Ivey <[EMAIL PROTECTED]> Smokefree DC http://www.smok

Re: linux timestamp

them. For a one-shot, you can use a query something like SELECT * FROM listings WHERE DATE_FORMAT(FROM_UNIXTIME(your_timestamp),'%W')) = 'Wednesday'; or SELECT * FROM listings WHERE DATE_FORMAT(FROM_UNIXTIME(your_timestamp),'%H')) = '14';

Re: NULL and database size

lumn is 0 -- or the equivalent -- or not). So no, those NULL entries don't take any extra space. -- Keith Ivey <[EMAIL PROTECTED]> Smokefree DC http://www.smokefreedc.org Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: unix timestamp

Scott Gifford wrote: SELECT COUNT(*) AS score FROM downloads WHERE dateline + 3600 >= UNIX_TIMESTAMP() GROUP BY filename ORDER BY score DESC It would be better with WHERE dateline >= UNIX_TIMESTAMP() - 3600 so that it can use an index on dateline. -- Keith Ivey &

Re: Index - max key length is 1024 bytes

ld such an index be useful for? In most circumstance it makes little sense to index more than a prefix of a long VARCHAR or TEXT field, and indexing nine fields at once will only make sense if you use all nine in your query. Maybe you want a FULLTEXT index? -- Keith Ivey <[EMAIL PROTECTED]&

Re: MAX on UNSIGNED INT Column

Jacob S. Barrett wrote: I have a column of type UNSIGNED INT which holds a 32bit counter. When the value of the field exceeds 2147483647 (signed max) the value of MAX on the column returns a negative number. Possibly this bug, fixed in 4.1.12? http://bugs.mysql.com/bug.php?id=9298 -- Keith

Re: Shifting dates

DATETIME, unless you're doing astrology. Sebastian was talking about DATETIME versus Unix timestamp INT. -- Keith Ivey <[EMAIL PROTECTED]> Smokefree DC http://www.smokefreedc.org Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscri

Re: Seriously.. When are we going to get subqueries?!

I'm a little surprised that case-sensitivity is such a big deal. What sort of programmers randomly vary their capitalization from one occurrence of an identifier to the next, and wouldn't people who are so non-detail-oriented be making a lot of typos as well? -- Keith Ivey <[EM

Re: mysqlhotcopy

Jeff McKeon wrote: Am I right in assuming that while mysqlhotcopy is running, nobody else can write to or update the DB? Yes. That's why it's better to run it on a slave. -- Keith Ivey <[EMAIL PROTECTED]> Smokefree DC http://www.smokefreedc.org Washington, DC -- MySQL Gene

Re: How to get the name of the last failed FK constraint

Frank Schröder wrote: The thing that's really a headscratcher for me is why its possible for me to set a name for a constraint if it isn't displayed in an error and I can't get to it. It's useless. Same as setting a name for an index -- it allows you to alter or delete

Re: Select MAX(column1,column2)

4, 8 (because "548" as a string is greater than "1023" or "1127"), but he wants 10, 12, 8. -- Keith Ivey <[EMAIL PROTECTED]> Smokefree DC http://www.smokefreedc.org Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsu

Re: Select MAX(column1,column2)

Scott Klarenbach wrote: Can I select the maximum value across multiple columns? You want the GREATEST() function: http://dev.mysql.com/doc/mysql/en/comparison-operators.html -- Keith Ivey <[EMAIL PROTECTED]> Smokefree DC http://www.smokefreedc.org Washington, DC -- MySQL General M

Re: Efficiently finding a random record

rds that have gaps in the IDs before them will be twice, three times, etc. (depending on the size of the gap), as likely to be selected as records with no preceding gaps. -- Keith Ivey <[EMAIL PROTECTED]> Smokefree DC http://www.smokefreedc.org Washington, DC -- MySQL General Mailing List

Re: difficulty with UCASE and UPPER

ms for the same function) uppercase all letters in the string. They don't care whether there are numbers in it. mysql> select UPPER('abc123def'); ++ | UPPER('abc123def') | ++ | ABC123DEF | +--------+ 1 row i

Re: Expression Confusion?

If you think you do, you need to examine the values in the table more carefully. Perhaps the item value has a newline at the end or a space at the beginning or something else not immediately visible. Try selecting LENGTH() or HEX() of a column to see whether it's what you expect. -- Keith

Re: Slave stuck at "registering"

ike this: [blank line] 4 [master hostname] [user] [password] 3306 60 -- Keith Ivey <[EMAIL PROTECTED]> Smokefree DC http://www.smokefreedc.org Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Slave stuck at "registering"

ime" reaches a little over 500, and then disappears. -- Keith Ivey <[EMAIL PROTECTED]> Smokefree DC http://www.smokefreedc.org Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Slave stuck at "registering"

ient on the slave, I can connect fine to the master, and vice versa. Also, PHP and Perl programs on the slave use databases on the master with no problems. What would replication require that the normal client-server communication doesn't? The master is running 4.0.22 on FreeBSD and the

Re: IN giving me a fit

Scott Purcell wrote: I am in the docs trying to use the IN (13.1.8.3. Subqueries with ANY, IN, and SOME). Version: mysql Ver12.21 distrib 4.0.15 Win95/Win98(i32) Look at http://dev.mysql.com/doc/mysql/en/subqueries.html Subqueries aren't supported until 4.1. -- Keith Ivey <[EMAIL P

Re: Index problem ?

on just date1 or on (date1, machine). The second would allow MySQL to do that query from the index alone, without referring to the data file, so it should be much faster than what you're doing now. -- Keith Ivey <[EMAIL PROTECTED]> Smokefree DC http://www.smokefreedc.org Washington,

Re: zip code search within x miles

and from zip_code. -- Keith Ivey <[EMAIL PROTECTED]> Smokefree DC http://www.smokefreedc.org Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: FW: GWAVA Sender Notification (Spam)

because of the capital letters and multiple exclamation points in the subject line -- which means the person who posted the original complaining message probably got the same sort of bounce. -- Keith Ivey <[EMAIL PROTECTED]> Smokefree DC http://www.smokefreedc.org Washington, DC -- MySQL

Re: "Repairing Data" packing with zeros

'^[A-Z]{3}[0-9]{1,3}$'; -- Keith Ivey <[EMAIL PROTECTED]> Smokefree DC http://www.smokefreedc.org Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: a very tricky string extraction

LOCATE('Tested this', log)-1))+1, 8) FROM test WHERE LOCATE('Tested this', log); -- Keith Ivey <[EMAIL PROTECTED]> Smokefree DC http://www.smokefreedc.org Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: a very tricky string extraction

;t apply (except for the bit about breaking it into columns if you're doing it regularly). -- Keith Ivey <[EMAIL PROTECTED]> Smokefree DC http://www.smokefreedc.org Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: a very tricky string extraction

doing regularly, rather than a one-time conversion, you should put that data into a proper table, with date, initials, and description as columns. If you're determined to do it, you'll need a bunch of applications of LOCATION() and SUBSTRING(), and probably IF(). -- Kei

Re: ROW_SIZE or something alike

length and Index_length values from SHOW TABLE STATUS, divided by the number of rows. http://dev.mysql.com/doc/mysql/en/show-table-status.html -- Keith Ivey <[EMAIL PROTECTED]> Smokefree DC http://www.smokefreedc.org Washington, DC -- MySQL General Mailing List For list archives: http://lists.my

Re: Optimising COUNT()

thout a WHERE clause is optimized with MyISAM tables to return the number of rows in the table very quickly. The same would not be true of COUNT(column_name). -- Keith Ivey <[EMAIL PROTECTED]> Smokefree DC http://www.smokefreedc.org Washington, DC -- MySQL General Mailing List For list arc

Re: replication errors

have been warned. http://dev.mysql.com/doc/mysql/en/replication-options.html -- Keith Ivey <[EMAIL PROTECTED]> Smokefree DC http://www.smokefreedc.org Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: safe way of replication?

hreads or from users with the SUPER privilege. This can | be useful to ensure that a slave server accepts no updates | from clients. | | This option is available as of MySQL 4.0.14. -- Keith Ivey <[EMAIL PROTECTED]> Smokefree DC http://www.smokefreedc.org Washington, DC -- MySQL General Mailing Li

Re: skip-name-resolve

IST" are shown as IP addresses or hostnames. -- Keith Ivey <[EMAIL PROTECTED]> Smokefree DC http://www.smokefreedc.org Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Auto Escape characters

uld MySQL know which quotes you intended to escape? If what you're asking for were possible, there'd be no need for escaping in the first place. -- Keith Ivey <[EMAIL PROTECTED]> Smokefree DC http://www.smokefreedc.org Washington, DC -- MySQL General Mailing List For list archive

Re: What is the max length of IN() function?

27;, 'aaa', 'aab', [...], 'aaaz')" (with lots of entries, all between 'a' and 'ab', or another small range). MySQL can use a range of the index for the second, even though there are lots of entries, but not for the first, which may fo

Re: Why does dropping indexes takes such a long time?

changed since then? -- Keith Ivey <[EMAIL PROTECTED]> Smokefree DC http://www.smokefreedc.org Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Best way to store numeric data?

these look more like measurements, so they're not exact in the first place, and any inexactness in calculations is fine as long as it's below the error in the measurements. -- Keith Ivey <[EMAIL PROTECTED]> Smokefree DC http://www.smokefreedc.org Washington, DC -- MySQL General

Re: Why MySQL is very slow in dropping indexes?

a special algorithm that is much faster than inserting keys one by one, so disabling keys before performing bulk insert operations should give a considerable speedup." You want to DISABLE, not DROP, the keys. -- Keith Ivey <[EMAIL PROTECTED]> Smokefree DC http://www.smokefreedc

Re: Checking max_allowed_packet from PHP during runtime?

" and "Value". If you're just interested in max_allowed_packet, you can eliminate the part of the result set you won't be using by changing the query to SHOW VARIABLES LIKE 'max_allowed_packet'; and then looking at $row['Value'] for the one ro

Re: What changed in 4.1.7 to break DBD::Mysql

If it was 4.1.0 or earlier, then this might be useful reading: http://dev.mysql.com/doc/mysql/en/Password_hashing.html -- Keith Ivey <[EMAIL PROTECTED]> Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: NULL values from LOAD DATA infile

hen reading the file back in, you |should not use fixed-row format. -- Keith Ivey <[EMAIL PROTECTED]> Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: bug or feature, <> 'blah' does NOT work with null records

database/postgresql/manual/functions-comparison.html -- Keith Ivey <[EMAIL PROTECTED]> Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Ignore a single query in replication

n? I realize that you excluded that as a possible solution in your initial message, but that would be the normal way to do it. Without knowing why that doesn't work for you it's hard to give an answer that might. Do you not have control over the server configuration? -- Keith Ivey &l

Re: Ignore a single query in replication

are being replicated, then how would the slave remain in sync with the master if it didn't replicate them? -- Keith Ivey <[EMAIL PROTECTED]> Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: (if !update then insert) sequence - result Duplicate key :(

index, the old record is deleted before the new record is inserted Read what you quoted. The old record is *deleted* if it exists, and then a new record is inserted. So he wouldn't be able to get the incremented count. -- Keith Ivey <[EMAIL PROTECTED]> Washington, DC -- MySQL General M

Re: (if !update then insert) sequence - result Duplicate key :(

INSERT IGNORE INTO sometable SET keyfield = '$key', count = 0; UPDATE sometable SET count = count + 1 WHERE keyfield = '$key'; -- Keith Ivey <[EMAIL PROTECTED]> Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Sanity Check : Error in sql

ble, but you're not showing us the SQL statement that gives the error. -- Keith Ivey <[EMAIL PROTECTED]> Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Modulo Arithmetic... for negative numbers

Richard Dyce wrote: mod(3-weekday(curdate()),7); But MySQL doesn't seems happy to give back negative numbers: What about changing it to MOD( 10 - WEEKDAY( CURDATE(), 7 ) ) to avoid the negative numbers? -- Keith Ivey <[EMAIL PROTECTED]> Washington, DC -- MySQL General Mailing Li

Re: Using SUM in a special way

klugy (having to add it in and then subtract Col1 to fix it), but it seems to work. Hmm, if you change the order of the result columns you can avoid the kluge: SELECT Col2, @total := IF(@prev = Col1, @total + Col2, Col2), @prev := Col1 FROM table_name ORDER BY Col1; -- Keith Ivey <[EMAIL P

Re: Escaped BLOB data in XML

ing binary data, so people generally use Base64 encoding (or occasionally some other method of encoding binary data in ASCII). The XML parser isn't going to be able to return the raw binary data -- you'll have to decode it. -- Keith Ivey <[EMAIL PROTECTED]> Washington, DC -- MySQL Ge

Re: load data infile question

tuation, though. -- Keith Ivey <[EMAIL PROTECTED]> Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Enum or Int

as a special error | value. This string can be distinguished from a ``normal'' empty string by the | fact that this string has the numerical value 0. See http://dev.mysql.com/doc/mysql/en/ENUM.html -- Keith Ivey <[EMAIL PROTECTED]> Washington, DC -- MySQL General Mailing List Fo

Re: Select statement inbetween unix timestamp ranges

LECT FROM_UNIXTIME(time) FROM srvlog WHERE time BETWEEN 1080948600 AND 1080997876; -- Keith Ivey <[EMAIL PROTECTED]> Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: INSERT if record NOT EXISTS

stname), just do INSERT IGNORE INTO tb (first_name, lastname) VALUES ('Jack', 'Doe'); But how are you planning to handle multiple people named Jack Doe? -- Keith Ivey <[EMAIL PROTECTED]> Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Between Operator

ct sorting). Then manipulate the strings to produce what you're used to when it comes time to display them (that, or have two columns: one for display and one for sorting). -- Keith Ivey <[EMAIL PROTECTED]> Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Optimizing selects based on date functions.

icular year should work: SELECT * FROM my_table WHERE dt BETWEEN '2004-06-01 00:00:00' AND '2004-06-31 23:59:59'; But that's apparently not what you want. Your needs are a little unusual, so they will require a table structure that's a little unusual. -- Keith Ivey

Re: INDEX DESC

something you can depend on, and it has nothing to with the index. If you want a specific order (ascending or descending), you have to specify it in an "ORDER BY" clause. -- Keith Ivey <[EMAIL PROTECTED]> Washington, DC -- MySQL General Mailing List For list archives: http:

Re: Some BLOB help please.

ompress in your application before inserting and after selecting (using PHP's gzcompress() and gzuncompress(), for example). -- Keith Ivey <[EMAIL PROTECTED]> Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://l

Re: "show table status" problem

SHOW_TABLE_STATUS.html You want SHOW TABLE STATUS FROM jeandatabase; or maybe SHOW TABLE STATUS LIKE 'jean'; -- Keith Ivey <[EMAIL PROTECTED]> Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Very Strange data corruption

y of looking at things, but it's not the MySQL philosophy, and that's one of the things that makes MySQL fast. -- Keith Ivey <[EMAIL PROTECTED]> Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]