Re: Doubt about TINYINT type
On 27 Apr 2004 at 13:59, Ronan Lucio wrote: OK, I understood it, but I didn´t understand why is there an option TINYINT(n) See http://dev.mysql.com/doc/mysql/en/Numeric_types.html | Another extension is supported by MySQL for optionally | specifying the display width of an integer value in | parentheses following the base keyword for the type (for | example, INT(4)). This optional display width specification | is used to left-pad the display of values having a width | less than the width specified for the column. However, the | display width does not constrain the range of values that | can be stored in the column, or the number of digits that | will be displayed for values having a width exceeding that | specified for the column. I haven't found much use for display widths myself, but then different people use MySQL differently. -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: simplifying OR clauses
On 26 Apr 2004 at 14:59, Matthias Eireiner wrote: how can I simplify multiple OR statements in a WHERE clause where I have only one column to which I refer? e.g. SELECT word FROM word_table WHERE id = 1 OR id = 34 OR id = 78 OR id = 8787 OR ... You want the IN operator: http://dev.mysql.com/doc/mysql/en/Comparison_Operators.html#IDX1209 -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: first LIMIT then ORDER
On 23 Apr 2004 at 7:23, Bill Easton wrote: The last suggestion is useful when you do care which entries you get, as you can use one order for limit and another for presentation. For example, if you'd like the LAST 10 rows, but sorted in FORWARD order, you can use something like (select * from HISTORY order by version desc limit 10) order by version; And I thought I'd have to wait for subqueries... One small gotcha that Anders Karlsson pointed out to me through Paul DuBois: This one-query union syntax doesn't allow you to use the ALL keyword after UNION (since the UNION keyword isn't even there). That means it will always eliminate duplicate rows (like DISTINCT). That hasn't come up when I've used it, since I've never been selecting result sets that could contain duplicate rows, but it's something to keep in mind. -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: first LIMIT then ORDER
On 22 Apr 2004 at 12:31, Johan Hook wrote: Assuming you want to order your arbitrary selection you could do something like: (SELECT t.Id FROM tab t LIMIT 10) UNION ALL (SELECT t.Id FROM tab t WHERE 1 0) ORDER BY t.Id You don't even need to include the dummy query. You can do a UNION of one result set. This should work: (SELECT t.Id FROM tab t LIMIT 10) ORDER BY t.Id I wrote this comment on the mysql.com site: It's not documented above, but you can use ORDER BY on a UNION that consists of only one SELECT (and thus doesn't actually include the word UNION). Suppose you want the last 5 entries in a table, but you want them in ascending order. You can use this query: ( SELECT * FROM table_name ORDER BY ranking DESC LIMIT 5 ) ORDER BY ranking; Similarly, you could select the top 10 records from a table ordered by one column and then sort them alphabetically by another column. Now, the fact that the syntax isn't documented may mean that it will disappear, but it's reasonable and useful. -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: NOT NULL column behaves strangely
On 14 Apr 2004 at 17:27, B. Fongo wrote: I expected a warning because of the Token column shouldn't be NULL! It's not NULL. It's the empty string, which is the default value, since you didn't give it a specific default value. See the CREATE TABLE documentation: If no DEFAULT value is specified for a column, MySQL automatically assigns one, as follows. If the column can take NULL as a value, the default value is NULL. If the column is declared as NOT NULL, the default value depends on the column type: [...] * For string types other than ENUM, the default value is the empty string. http://dev.mysql.com/doc/mysql/en/CREATE_TABLE.html -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Managing Very Large Tables
On 30 Mar 2004 at 10:30, Dathan Vance Pattishall wrote: 1) Ensure that the table type is not DYNAMIC but Fixed. = Issue the show table status command. = Look at Row Format = if Row Format != Dynamic the your ok else get rid of varchar type columns = Reason: Your myISAM table can grow only to 4GB then it will run out of space even if your file system allows files to grow past 4GB. There are reasons for using fixed-length rather than dynamic records, but that's not one of them. If you set MAX_ROWS high enough when creating your tables (or alter it later after they get big, though that could take a while), they'll be able to grow past 4 GB even if they're dynamic. -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Problems
On 26 Feb 2004 at 13:22, Eric Scuccimarra wrote: But it appears that new rows are not automatically indexed. Does anyone know about this and if they are not indexed how do I reindex the tables? You're misunderstanding something. When you create an index, all the rows in the table are indexed, and new rows are indexed as they are added. What makes you think they're not? -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MAX_ROWS
On 25 Feb 2004 at 8:35, Tucker, Gabriel wrote: What values of MAX_ROWS and AVG_ROW_LENGTH would I need so that I could limit this table to 3 [or n] number of records? You can't. That's not what MAX_ROWS and AVG_ROW_LENGTH are for. They're only there to allow MySQL to decide how many bytes it needs to use for the pointers into the data file. Unless you're going to be using tables bigger than 4 GB, there's probably no reason for you to worry about those values at all. You can indirectly limit the number of records in a table to 127 or 255 or 32,767 or 65,535 or ... by using an AUTO_INCREMENT primary key that's a TINYINT or UNSIGNED TINYINT or SMALLINT or UNSIGNED SMALLINT ..., but I don't think that's going to help you either. -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Problems
On 25 Feb 2004 at 13:09, Eric Scuccimarra wrote: Select* FROM Table1 as a INNER JOIN Table2 as b ON (a.ID = b.ID or (a.Field1 = b.Field1 and a.Field2 = b.Field2)) WHERE bla bla bla It's hard to know without seeing the indexes and the full WHERE clause, but part of the problem could be that MySQL can't use an index for the join because of the OR. One possibility would be to break in into two queries and use a UNION: ( SELECT * FROM Table1 a INNER JOIN Table2 b ON a.ID = b.ID WHERE condition ) UNION ( SELECT * FROM Table1 a INNER JOIN Table2 b ON a.Field1 = b.Field1 AND a.Field2 = b.Field2 WHERE condition ) ORDER BY whatever; -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: disabling optimizations to identify slow queries
On 24 Feb 2004 at 12:00, Bill Marrs wrote: Actually, I just noticed that even after I restart mysql, the speed stays. That doesn't make any sense, maybe there is some other unknown factor influencing this. Sounds like it's your operating system's caching of the disk reads. -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MAX_ROWS
On 24 Feb 2004 at 22:01, Alison W wrote: Yes: MAX_ROWS is a *guidance* to the system in setting up the table and not a *limit* in any way. Well, it is a limit in one way. MySQL uses it (in MyISAM tables) to calculate the size of the pointer used for positions within the data file. If the data file becomes larger than can be handled by that size of pointer, then you can't add any more records to the table (unless you increase MAX_ROWS or AVG_ROW_LENGTH so that the pointer size is increased). -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: operator OR slows down query?
On 20 Feb 2004 at 10:57, Yun Li wrote: I am having problem using the operator OR. when I test for condition A OR B in a query, the query becomes extensively slow or even fails, even though a test on condition A or B alone gives result right away. See http://www.mysql.com/doc/en/MySQL_indexes.html -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: White Space
On 20 Feb 2004 at 14:55, Dan Nelson wrote: If you need leading and trailing space stripped, you can postprocess the field with the TRIM() function. But note that, unlike similar functions in some languages, the MySQL TRIM() function removes spaces (ASCII 32 characters), not whitespace (which generally includes tabs, carriage returns, and line feeds -- and sometimes vertical tabs, form feeds, or nulls -- along with spaces). -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Temporary table issues. Do I need persistent connections with php?
Even with persistent connections, you can't use a temporary table on a later HTTP connection (Web request), because you don't know that your new HTTP connection will get the same MySQL connection that you used before. It should work to use a temporary table in a series of queries to get data to display on a page in response to a single HTTP request, but if you need to save data through a series of HTTP requests (a session), you'll need normal tables. You can create a table using some unique ID in the name and then drop it when you're finished with it. It will exist temporarily, but it won't be a TEMPORARY table in the MySQL sense. -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Setting PACK_KEYS=1 on existing tables
On 16 Feb 2004 at 12:46, Bill Marrs wrote: Is there a way to set PACK_KEYS=1 on existing tables without doing a dump and load? I didn't find it in the ALTER TABLE syntax. It's there. alter_specification can be table_options, and (looking at the CREATE TABLE syntax) table_options can be a single table_option, and table_option can be PACK_KEYS = 1. So the syntax is just ALTER TABLE table_name PACK_KEYS = 1; -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unique IDs
On 12 Feb 2004 at 22:57, Bill Easton wrote: You can use + 1 instead of + interval 1 second, but it may give different results some day if MySQL changes the precision of timestamp. Actually, that won't work in all cases. If the current timestamp is 20040213114859, then adding 1 (rather than INTERVAL 1 SECOND), gives 20040213114860, which gets converted to 20040213114800 on insert. If you've already used all the timestamps for that minute, then you're never going to get to the next minute (and thus never going to get a usable ID) by repeatedly adding 1. -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unique IDs
Craig Jackson [EMAIL PROTECTED] wrote: I have a very large web app that uses timestamp for unique IDs. Everything was rolling fine until we started getting many users per second, causing some of the unique IDs to not be unique -- users were being assigned the same timestamp. Since the web app is so large we don't want to change the method of assigning IDs as it would create a major project. I don't understand. If you're getting many users per second, and your timestamps have 1-second resolution, how could you possibly solve the problem without changing the method of assigning IDs? Are the many users per second periods just short bursts, and you're really only getting several hundred users per day? If so, I guess you could keep waiting a second and trying the insert again, but that could lead to indefinite delays if traffic gets high. I think you've got to bite the bullet and change the unique ID to something that's actually unique -- even an AUTO_INCREMENT would work. -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unique IDs
On 12 Feb 2004 at 11:57, Craig Jackson wrote: Thanks for the speedy reply and I have already recommended auto_increment for the solution. We do need that quick fix until the problem is fixed. How would I go about making Mysql wait one second between inserts. We only get about 1000 hits per day, but they tend to be concentrated in short time intervals. You'd have to code that logic into your application, using Perl or PHP or C or whatever. Try the insert. If it fails, wait a second and try again with the new timestamp. Repeat until you succeed or until you've gone through some number of tries (at which point you give up). It's ugly, but that's what you asked for. How about converting the column to a BIGINT AUTO_INCREMENT with a new value of, say, 30? Then your old values would still be around with values like 20040212131422. That's also ugly, but not as ugly as the solution with waiting. -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Odd Rounding?
On 23 Jan 2004 at 12:13, Andrew Kuebler wrote: Can anyone explain why: SELECT ROUND(.012345, 5) - .01234 Why doesn't mysql round the 5 up to .01235? How do I get it to round up? I've tried manipulating ceil, floor, round and truncate and I can't seam to find an easy way to do this. This comment from the documentation on TRUNCATE applies to other functions as well: | Note that as decimal numbers are normally not stored as exact | numbers in computers, but as double-precision values, you may be | fooled by the following result: | | mysql SELECT TRUNCATE(10.28*100,0); |- 1027 | | The above happens because 10.28 is actually stored as something | like 10.2799. That's how floating-point numbers work generally, not just in MySQL. Why are you so concerned about it? Presumably your 0.012345 isn't an exact number but a measurement of some sort, so it could just as well be 0.0123449 or 0.0123451, and 0.01234 is perfectly fine as a rounded value. If you do have exact numbers with six decimal places, you're probably better off storing them as some sort of integer and adding the decimal point when necessary for display. -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Something like STR_TO_DATE in 3.23.58?
That's perfect, but in DESC mode, it orders like this: Sep 28, 2003 Oct 05, 2003 Dec 31, 2003 Dec 06, 2003 Jan 14, 2004 Jan 10, 2004 Jan 02, 2004 If you're ordering by three fields and you want all of them to be in descending order you have to put DESC after each of them, not just the last one. ORDER BY MID(datecol,8) DESC, FIELD(LEFT(datecol,3), 'Jan','Feb','Mar','Apr','May','Jun', 'Jul','Aug','Sep','Oct','Nov','Dec') DESC, MID(datecol,5,2) DESC -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mediumtext crash on strings 1MB?
On 5 Jan 2004 at 13:06, Chris Seidel wrote: However, I found my inserts failing when some of my strings exceeded 1 MB (e.g. a string of 1125921 bytes fails, while a string of 1009684 bytes succeeds). What error are you getting? It sounds like you haven't changed max_allowed_packet from the default value of 1M. I believe that earlier versions of MySQL seomtimes gave server went away error in that circumstance rather than packet too large. -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mediumtext crash on strings 1MB?
On 5 Jan 2004 at 14:18, Chris Seidel wrote: Thus I had to reset the max_allowed_packet size to allow for larger packets by restarting the server with /usr/bin/safe_mysqld --defaults-file=/etc/my.cnf --set-variable max_allowed_packet=10M /dev/null 21 To do this I edited the mysqld file in /etc/init.d so that this variable will be set whenever safe_mysqld starts up. I would have thought this could be specified in my.cnf, but it doesn't seem so. What makes you think it can't? I've had this in the [mysqld] section of my my.cnf for ages, and it works fine: set-variable = max_allowed_packet=16M I see now that that syntax is deprecated in MySQL 4.0, so I should at some point change it to max_allowed_packet = 16M -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: = not working?
On 8 Dec 2003 at 11:30, Ed Curtis wrote: I've got an entry in a table where the value is 875. If I run a query on that table with the clause AND sqaurefeet = '$squarefeet' and $squarefeet has a value of say 1000 the row with the squarefeer value of 875 will appear. The column is of type varchar(10) and using the binary flag. If the column is VARCHAR, then the comparison is correct. The string '875' is greater lexically (alphabetically) than the string '1000'. If you want the comparison to be numeric, you should use an integer (or other numeric type) for the column. Failing that, you have to cast the value into a numeric type before comparing them. -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: unixtime update syntax
On 2 Dec 2003 at 7:57, Ron McKeever wrote: I have a db that gets data dumped into it. One of the columns gets unix timestamp data utime. I what to covert that into a datetime column so I can utlize indexes and such. But I still what the unixtime to remain. You can use indexes with a Unix time column about as well as you can with DATETIME. What sort of queries are you wanting to do? Having the extra column may be unnecessary. I believe I have a good way to do this but I'm not sure why it's not converting the date right ( see at bottom ): It seems to be converting the date right. FROM_UNIXTIME() does the opposite of UNIX_TIMESTAMP(). It takes an integer representing a Unix time and converts it to a DATETIME in local time (not GMT). It would be nice if there were a FROM_UNIXTIME_TO_GMT() function, but there isn't. -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: unixtime update syntax
On 2 Dec 2003 at 9:12, Ron McKeever wrote: Thats seems like a bug to me. I would think FROM_UNIXTIME would take a unixtime stamp and covert it to what it is. We know its from GMT A Unix timestamp represents a particular second in time. It doesn't have a time zone associated with it. Yes, the definition of 0 time is based on GMT, but you could just as well say it's the number of seconds since 7 pm on 31 Dec 1969 Eastern Standard Time. The number would be the same. You can represent the time in whatever zone you like; it doesn't change what time you're talking about. DATETIME columns in MySQL are in the local time zone. It wouldn't make sense to convert to a GMT DATETIME, at least not by default. MySQL's handling of time zones leaves something to be desired, but given the way it works the behavior of FROM_UNIXTIME() makes perfect sense. It's not a bug. Avoiding time zone and daylight time issues is the main reason to use Unix time. I don't see the advantage of keeping your time in two different formats. It seems like sticking to one would be simpler. But then I don't know your system. -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Formatted index
On 1 Dec 2003 at 13:41, Kevin Carlson wrote: Does MySQL support formatted indexes such as in the statement below? alter table DateInfo add index monthYear (DATE_FORMAT(updateDate, '%m/%Y')) What sort of queries would that help you with? The only thing I can see is if you were trying to find all records with an update date in March without caring about the year, which seems a strange thing to want. If you did consider the month to be the most important part of the update date, then it would be best to make a separate column for it and make a compound index using it. -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Formatted index
On 1 Dec 2003 at 16:32, Kevin Carlson wrote: select * from DateInfo where DATE_FORMAT(updateDate, '%m/%Y') = '03/2003' I have an index on the updateDate column but it does not seem to be used since I am altering the value with the DATE_FORMAT function in the where clause. How about something like this? SELECT * FROM DateInfo WHERE updateDate BETWEEN '2003-03-01' AND '2003-03-31'; Or if updateDate is a DATETIME column, SELECT * FROM DateInfo WHERE updateDate BETWEEN '2003-03-01 00:00:00' AND '2003-03-31 23:59:59'; -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LIMIT and placeholders
On 29 Oct 2003 at 18:00, Fagyal, Csongor wrote: Any ideas what causes this behaviour? Maybe I have not upgraded my DBI DBD packages properly? Or are LIMIT placeholders no longer supported in DBI with MySQL 4? I don't think the MySQL version is relevant. The change is in DBD::mysql 2.9002, which now assumes all values substituted for placeholders are strings unless specifically told otherwise. Apparently there were situations where quotes were not being added when they should have been, which is dangerous. It's hideously annoying, since it happens even when the variable in question has never been used in anything but a numeric context, but the only place it really matters is in limits. MySQL automatically converts strings to numbers when necessary in most places, but not in limits. The arguments to LIMIT must be integer constants. You have to use bind_param() to identify the parameter as an integer. Rudy Lippan, the author of DBD::mysql, had a message about it on the dbi-users list: http://groups.google.com/groups?selm=Pine.LNX.4.44.0307291622390.14615 -10%40elfride.ineffable.net -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Howto reduce size of MYISAM files after deleting records?
On 28 Oct 2003 at 13:59, Dan Greene wrote: Is there a way to do this on a live running (i.e. production) server? http://www.mysql.com/doc/en/OPTIMIZE_TABLE.html -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: updating records without changing timestamp fields
On 27 Oct 2003 at 21:21, Henning Heil wrote: how can I keep the previously stored value for this field (there are values created before)? Explicitly SET timestamp_column = timestamp_column. See here: http://www.mysql.com/doc/en/DATETIME.html But if you never want the TIMESTAMP column to update automatically, you shouldn't be using TIMESTAMP in the first place. You probably want DATETIME instead. -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Beginner trying to use Unicode with MySQL
On 22 Oct 2003 at 17:57, Kaeru the Frog wrote: I've just started working with MySQL. I'm using MySQL version 4.0.14 on Gentoo Linux. I've been reading chapter 9 (http://www.mysql.com/doc/en/Charset.html) in the documentation but it hasn't helped. The commands 'SHOW CHARACTER SET', 'SHOW COLLATION', 'CREATE DATABASE foobar CHARACTER SET ucs2' all say there is a syntax error. Did you read the part where it said | The features described here are as implemented in MySQL 4.1.1. | (MySQL 4.1.0 has some but not all of these features, and some of | them are implemented differently.) ? -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SHOW CREATE TABLE on one line?
On 13 Oct 2003 at 16:59, Adam Clauss wrote: Well the issue wasn't so much with my program at runtime, but at design time (now). What I am doing is hardcoding the string that creates the table into the program. Program then calls SHOW CREATE TABLE and compares the two strings to see if they are the same. If not, it drops and recreates. What I have to do everytime I add a table and want to hardcode it in, is run SHOW CREATE TABLE myself when I know the table is correct and then copy that string into my program. That copying part is where I am having difficulty. It is taking a while for me to figure out where linebreaks, spaces, etc. are in it. [...] You can hardly expect MySQL to change its behavior to accommodate your particular situation. How many other people do you are think are doing what you're doing? Far fewer than would be annoyed by having the CREATE TABLE statement scrunched into one long line, I'd bet. How about saving the SHOW CREATE TABLE output to a file and keeping that around? Then your program can read the file, check the existing table structure against that, and if it's changed issue a warning message and re-create the file with the new output. Seems a lot better than hand-coding. -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Converting GMT stored data into other zones
On 10 Oct 2003 at 14:12, Graeme B. Davis wrote: BUT, I've run into a problem when you want to GROUP BY DAYOFYEAR(datefield) for example, I want it to do the grouping by EDT and not GMT -- is this possible? You say you've already got the necessary time zone offsets, right? So can you use SELECT DAYOFYEAR( datefield - INTERVAL 4 HOUR ) AS day ... GROUP BY day? -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: small bug in mysqldump
On 29 Sep 2003 at 20:47, Lorenzo Sicilia wrote: when I use mysqldump the database name is write: design-network instead design-network If you have odd characters in your names, then you need to use the --quote-names option on mysqldump, so that it puts backticks around them. -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FULL Text Limitation issue!
On 23 Sep 2003 at 17:07, Imran Aziz wrote: I have come to know that mySQL FULL TEXT search has the limitation of the search phrase to be more then 3 charators. In order to alter the default behavior one has to alter the variable ft_min_word_len. I am running MySQL 3.23.54 and the FULL Text search works fine , but I am unable to alter the variable ft_min_word_len either using the my.cnf file or by altering the variable value on mysql startup. The ft_min_word_len variable wasn't introduced until MySQL 4. See http://www.mysql.com/doc/en/Fulltext_Fine-tuning.html -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Does NULL == ?
On 15 Sep 2003 at 11:47, Brent Baisley wrote: It's almost useless to specify a property as NOT NULL and also set a default value. The only way it would ever be NULL is if you specifically set it to NULL. I think you meant NULL rather than NOT NULL there. -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Order By question
Martin Moss [EMAIL PROTECTED] wrote: SELECT recTran.TransactionID,tr.* FROM Transaction tr LEFT OUTER JOIN ReconciledTransactions recTran ON recTran.TransactionID = tr.TransactionID WHERE tr.ReconciliationID = '8' HAVING recTran.TransactionID IS NULL ORDER BY 'tr.Amount' DESC; You are ordering by a constant: the string tr.Amount. Since the string is the same for every row, you're not actually getting any ordering. What are you trying to accomplish with those quotes? Get rid of them, and you may get the results you want. -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Comparing spaces with LIKE
On 12 Sep 2003 at 8:39, Nelson Azambuja Jr. wrote: I tried the following: SELECT * FROM table WHERE name LIKE '% John %'; But it didn't work. Please explain what didn't work. What were you expecting to happen, and what actually happened? To explain: I don't want the string 'Johnson' to be shown when running the query above. It shouldn't be (unless you've found a bug, which is unlikely). Post the exact query you're using and the results you get. You might also want to look into FULLTEXT indexes: http://www.mysql.com/doc/en/Fulltext_Search.html -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Writing a NULL in a CHAR field
On 11 Sep 2003 at 11:04, Andrew Kuebler wrote: Are BLOB column types the only columns that will let you write a NULL (Hexidecimal 0) and still write beyond that? CHAR, CHAR BINARY, VARCHAR, and VARCHAR BINARY all seem to truncate the string and stop at the first null even if I escape the string being updated. Are you sure it's not your application, rather than MySQL, that's truncating the string. If you select LENGTH(column_name), do you get the full length of the untruncated string? I've just tried it with a VARCHAR and had no problems. If I insert 'abc\0def', the length is 7, and RIGHT(column_name, 3) is 'def'. It's true that the characters after the 0 byte don't show up in the mysql client, but that's not the way I'd actually be using them anyway. What are you using? C? Also, how are you inserting the strings? Are you writing the binary 0 as '\0'? (It's confusing to call the character 'NULL' on a list about databases, since here people expect NULL to mean something completely different.) I don't want to use a BLOB column because I'm only writing 8 characters. extreme waste of space. Not an extreme waste of space. If you use TINYBLOB, it's only 1 extra byte to store the length, so you'll be using 9 bytes instead of 8. Still, it doesn't seem like the best idea. -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query optimizer decision to use index depends on data?
On 11 Sep 2003 at 15:31, Tongprasith, Anan wrote: I think you are right. I try adding and deleting data one by one and found no particular data that will always turn off index in all circumstances. myisamchk --analyze doesn't help. Is there a way to force using index? Yes, 'USE INDEX (index_name)' in the SELECT statement after the table name should do it, though MySQL still won't use the index if it's not useful. I don't think optimizer is smart enough. My table has more than 30,000 rows. The query returns only 6,000 rows. I modify the query's where clause to use indexed column only (to see the ratio between hit and miss on index). It returns 7,000 rows. Yet MySQL refuses to use index. I tried use index in the query and it didn't help. I think you may be confused about indexes. Whether an index is being used or not has no effect on the number of rows returned, just on how fast they're returned. But maybe I'm misunderstanding. Can you post the output of SHOW CREATE TABLE for your table and EXPLAIN for your queries? The answer to your initial question is that, yes, the optimizer is affected by the data in the table. It's not a bug. Otherwise it wouldn't be mouch of an optimizer. It still does make mistakes, though, and USE INDEX should solve the problem in those cases. -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: $sth-rows problem
On 8 Sep 2003 at 14:15, Jack Coxen wrote:
sub run_query {
# print Query: , $statement, \n;
my $sth = $dbh-do($statement)
or die Can't prepare $statement: $dbh-errstr\n;
$numrows = ($sth-rows); # This is where the problem is
print $numrows;
}
This is a DBI question, not a MySQL question. Have a look at the
documentation for the do() method in DBI. It returns the number of
rows, not a statement handle.
--
Keith C. Ivey [EMAIL PROTECTED]
Tobacco Documents Online
http://tobaccodocuments.org
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table is full error
On 4 Sep 2003 at 10:13, Keith Bussey wrote: -rw-rw 1 mysqlmysql4.0G Aug 31 01:01 email_body.MYD Thus if I try and insert one more row I get the error: ERROR 1114: The table 'email_body' is full By default, MyISAM tables use 4-byte pointers to indicate positions in the data file. So if your data file gets bigger than 4 GB (or larger for fixed-length records, but that's not what you have), you get that error: http://www.mysql.com/doc/en/Full_table.html Figure out how many records you're likely to need and do ALTER TABLE email_body MAX_ROWS=whatever; -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table is full error
On 4 Sep 2003 at 10:53, Keith Bussey wrote: Your suggestion seemed to wipe out my rows ! (s'ok I got a backup ;p) Yikes! Are you running into any file system limits? Have you dealt with files larger than 4 GB on that server before with no problems? If not, you may have run into a MySQL bug of some sort. An alternative way to get the table to have 5-byte pointers would be to create the new table (same CREATE TABLE query as for the old structure, except add MAX_ROWS=70 AVG_ROW_LENGTH=2 to the end) and then copy all the records into it: INSERT INTO email_body_NEW SELECT * FROM email_body; Actually, the exact figures for MAX_ROWS and AVG_ROW_LENGTH shouldn't matter, as long as their product is between 2**32 and 2**40 - 1. -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table is full error
On 4 Sep 2003 at 15:31, Keith Bussey wrote: I do have another Database on the same machine which has a table that is a lot bigger (about 9 GIGs) and it wasn't created with any of the special table options suggested by Keith C. Ivey below. Does the other table have fixed-length records (no VARCHAR, TEXT, or BLOB columns)? If so, the numbers for the pointers are in records, not bytes, so the table can reach 2**32 - 1 records before it's full. You can see what the maximum data length is with SHOW TABLE STATUS. For teh email_boy table, do you have any index other than the one for the primary key? The .MYI file isn't getting big, is it? -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table is full error
On 4 Sep 2003 at 16:10, Keith Bussey wrote: The MYI for that table is 1.7M, and no the text field isn't indexed Other table has all fixed-length records except 2 varchar fields Thus, if I understand right, then because the field is text it uses more pointers than a larger table that has no text fields (in my case atleast) ? No, as I understand it, any MyISAM table with dynamic records should give the table full error when it reaches 4 GB, if it was created without specifying MAX_ROWS or AVG_ROW_LENGTH. If your other table doesn't have fixed-length records and was created without specifying either of those options, then I don't know how it got to 9 GB (unless maybe it doesn't have any indexes?). What does the output from SHOW TABLE STATUS and SHOW CREATE TABLE look like for it (not that that's likely to help with solving the problem for your other table)? -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table is full error
On 4 Sep 2003 at 16:54, Keith Bussey wrote: Thanks I will make new tables and transfer the data over =) Just wodnering though, any advice on how to tell what to set AVG_ROW_LENGTH to ? Eh? Isn't that was I was saying 5.5 hours ago? This isn't anything new. Here's the message again in case it got lost somewhere: Begin quoted text On 4 Sep 2003 at 10:53, Keith Bussey wrote: Your suggestion seemed to wipe out my rows ! (s'ok I got a backup ;p) Yikes! Are you running into any file system limits? Have you dealt with files larger than 4 GB on that server before with no problems? If not, you may have run into a MySQL bug of some sort. An alternative way to get the table to have 5-byte pointers would be to create the new table (same CREATE TABLE query as for the old structure, except add MAX_ROWS=70 AVG_ROW_LENGTH=2 to the end) and then copy all the records into it: INSERT INTO email_body_NEW SELECT * FROM email_body; Actually, the exact figures for MAX_ROWS and AVG_ROW_LENGTH shouldn't matter, as long as their product is between 2**32 and 2**40 - 1. End quoted text -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: HAVING column not in select_statement
On 3 Sep 2003 at 15:43, Stefano Fraccaro wrote: I have a query with a column in HAVING clause that not refer any column listed in SELECT statement because I don't need to group by this column and ... this query don't work. It's possible or this feature is planned for the future? Can you give an example of what you're trying to do? If the expression in the HAVING clause isn't an aggregate of some sort, why aren't you using a WHERE clause instead? Also, in MySQL you don't have to group by a column just because you're selecting it (although selecting such insufficiently unspecified may confuse yourself and those who come after you). -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Two many key part specified
On 3 Sep 2003 at 18:59, eli wrote: EXCEPTION java.sql.SQLEXCEPTION:Invalid argument value, message from server Two many key part specified. Max 16 parts allowed. [...] INDEX(ID, Field_1, Field_2,Field_3, Field_23) ) I was supossed the maximum number of index was 32. The maximum number of indexes is not the same as the maximum number of parts for a single index. Somehow I doubt you really want a single index on 24 fields. Having Field_23 at the end of the index, for example, is useless unless you're already specifying all of ID and Field_1 through Field_22 in the query as well. -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table Query taking WAY TO LONG...HELP PLEASE!!!
On 26 Aug 2003 at 19:38, Twibell, Cory L wrote:
I have a query that is inner joined with another table based on
country codes Select distinct Name.* from Name inner join Location on
Location.key = Name.key and Location.cc in ('list of countries
here');
From the message you're getting it seems you're using a heap table.
Why is that? In the documentation it says that for heap tables
Indexes will only be used with = and = (but are VERY fast).
The indexes for heap tables are hash-based, which means they can't be
used for range queries (since the hash values for consecutive keys
won't be consecutive). But if you're using IN in you're query (with
more than one value in the list) then you need to get a range from
the index. Since that's not possible with a heap table, the whole
table must be scanned.
--
Keith C. Ivey [EMAIL PROTECTED]
Tobacco Documents Online
http://tobaccodocuments.org
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Telemobile auto-reply
On 26 Aug 2003 at 14:18, [EMAIL PROTECTED] wrote: Sometimes. I'm not sure why? It looks like some sort of advertisement. Since all these autoresponders got subscribed right about the time of the Sobig outbreak, it would make sense that they were subscribed by the virus. But is there some way to get subscribed by e-mail without replying to a confirmation message? At any rate, I think they're a result of mistakes, not a plot, and the addresses should simply be removed from the list. -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slow results with simple, well-indexed query
On 21 Aug 2003 at 21:38, Jesse Sheidlower wrote: Huh, I was told the exact opposite, that if most of the entries are smaller than the maximum length of the field, you should use an index about the size you expect most entries to be. Why would you ever use a shorter index than the full column length if it led to such performance degradation? It depends on the query (as well as your data). In your case, the only column you're selecting is cw, so if all of cw is in the index MySQL can use the index alone and never has to look at the data file. That speeds things up quite a bit. If you were selecting multiple columns the difference might not be so great. Another point is that you're sorting by cw, and a prefix-based index won't allow you to sort completely. If your queries were mainly selecting by cw rather than sorting by it, a prefix-based index should be fine. -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT SPEEDS......
On 20 Aug 2003 at 15:08, Tom O'Neill (MySQL User) wrote: Is there any difference in speed between the following select statements? SELECT yada,yda FROM test WHERE id IN(1,2,3) OR SELECT yada,yda FROM test WHERE (id =1 or id = 2 or id =3) You can put EXPLAIN in front of your SELECT statement to see how MySQL plans to execute it. Those seem to be treated identically in version 4.0.14, but it's possible that the second one wasn't as optimized in some earlier version. Documentation on EXPLAIN is here: http://www.mysql.com/doc/en/EXPLAIN.html -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query question
On 19 Aug 2003 at 13:17, Jack Lauman wrote: I have two tables, contact_account and contact. contact_account has two fields: contact_id (pk) and account_id contact has a PK of contact_id I to select all the columns in contact where account_id=13 I tried: SELECT * FROM (contacts INNER JOIN account on contact.contact_id = account.account_id) WHERE account.account_id = 13; If you want help, you need to provide the exact queries you're using (copied and pasted, not retyped) and the exact error messages you're getting. If you're not getting an error message, say what result you got and how it differs from what you expected. In this case, you keep changing the names of your tables (contact/ contacts, contact_account/account), so we can't know whether that's the problem or just a distraction. Also, you're joining on contact_id in one table and account_id in the other; presumably you want contact_id in both. -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Optimizing imports
On 15 Aug 2003 at 16:34, Jackson Miller wrote: However I want to know how I can optimize my insert statements to try to speed things up. Have you read this? http://www.mysql.com/doc/en/Insert_speed.html -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Matching escaped strings
On 13 Aug 2003 at 16:17, Rob wrote: ID| Name 1 | Author\'s As you can see, the name value has been escaped. Now, the question is, how do you match on a value that has escaped charaters? I've tried the following SELECT * FROM table WHERE Name = 'Author\'s' The sequence \' in a MySQL string means an apostrophe. What you want is a backslash followed by an apostrophe, so you need to put in two backslashes before it to represent a backslash: SELECT * FROM table WHERE Name = 'Author\\\'s'; Things are a bit more complicated with LIKE, because you want two backslashes in the string you give to LIKE, which means you need to start with four backslashes (there's an extra level of escaping): SELECT * FROM table WHERE Name LIKE 'Author\'s'; -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unix date problems
On 7 Aug 2003 at 16:12, Andy Jackman wrote: or is there some summertime adjustment occurring? You're right the -3600 looks like 1 hour of summertime and our server IS set to BST (1 hour ahead of GMT - sorry I can never figure out if that is -0100 or +0100). However, the same function gave different results a few minutes apart - that's the scary bit. Sorry, I was confused about the sign of the offset. -3600 is what you should get if the server is on BST, which is +0100, not -0100 (and +0100 is what your e-mail date says). That still doesn't explain why the result changed. -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date Calculation
On 8 Aug 2003 at 10:16, Oswaldo Castro wrote: I have two datetime fields on my database. I nedd to return the difference in minutes between them. I tried date_sub, extract(hour_minute from ...) and it does't work SELECT ( UNIX_TIMESTAMP(datetime2) - UNIX_TIMESTAMP(datetime1) ) / 60; (assuming the dates are within the 1970-2037 range). -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unix date problems
On 7 Aug 2003 at 15:27, Andy Jackman wrote: 1) I was investigating the unix_timestamp routine in mysql (version 3.23.46-nt) and for some reason the unix epoch (1-1-1970) was returned with a value of -3600. That's what I'd expect if the time zone was set to -0100, since MySQL assumes the string represents a date-time in the server time zone. Is your server set to GMT, or is there some summertime adjustment occurring? 2) The function from_unixtime(n) wraps on my server after 2^31 seconds. e.g. select from_unixtime(2147483648) returns 1900-01-00 00:00:00. Odd, on mine it gives 1901-12-13 15:45:52, which is the same as FROM_UNIXTIME(-2147483648), which is what I would expect if the argument to FROM_UNIXTIME() is a signed integer. -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database not writting
On 5 Aug 2003 at 12:02, Russ Fineman wrote: Scalar found where operator expected at C:\Inetpub\wwwroot\cgi-bin\memberv2.cgi line 30, near INSERT INTO members VALUES($membername (Missing operator before $membername?) That is not a MySQL problem and has nothing to do with your database. It's a Perl error indicating that your program won't compile because it is syntactically invalid. Fix your Perl syntax and try again. If you're still having trouble with Perl, ask on a Perl-related list, like one of these: http://lists.perl.org/showlist.cgi?name=beginners http://lists.perl.org/showlist.cgi?name=beginners-cgi Note that you can't have unescaped double quotes in the middle of a double-quoted string. Escape them with backslashes, change them to single quotes (which are more normal SQL anyway), or use some other method to include variables in your SQL (such as printf or DBI placeholders). -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Form values are truncated
On 11 Aug 2003 at 8:49, James Johnson wrote: I'm building a member signup form. Fields that contain more than one word are being truncated when being inserted into the MySQL table. It's very unlikely this has anything to do with MySQL or PHP. It sounds like you're missing quotes around the values in your HTML form, so you're ending up with something like this in your HTML: input type=text name=addr1 value=1122 Boogie St. rather than this: input type=text name=addr1 value=1122 Boogie St. (The first two sets of quotes aren't necessary, but it's easier just to get into the habit of always using them.) -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select statement with a field name that is a number?
On 6 Aug 2003 at 12:04, Zach wrote: I'm trying to use a select statement for a table that uses numbers for the field names. Here is the query I've been trying. Please help! SELECT * FROM table_name WHERE '18'=1; (18 is the field name, 1 obviously is the value I'm looking for.) If it's a column name, you should enclose it with ` (backtick), not ' (single quote). Single quotes are for strings, and the string '18' is never going to equal 1. And tell whoever designed the table that using numbers for column names is a remarkably bad idea (unless of course it's your boss). -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unix date problems
On 7 Aug 2003 at 9:47, woody at nfri dot com wrote: While I don't know for sure, my guess is that it would have something to do with 32 bit as the magic number, but also...being that this won't become a problem until mysql select from_unixtime(2147483647); +---+ | from_unixtime(2147483647) | +---+ | 2038-01-18 21:14:07 | It doesn't become a problem until you want to start *using* dates later than that. Depending on your application, that may happen considerably before 2038. For instance, if you need to store retirement dates, a Unix timestamp would be a bad choice even today. Of course you wouldn't need a time for a retirement date, and wouldn't be concerned about time zone or summer adjustments, so a DATE column would be fine, but people do use Unix timestamps for future times as well as past ones. -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: storing large integers properly
On 5 Aug 2003 at 9:49, Eben Goodman wrote: The data type of the field I am storing this info in is a bigint(16) unsigned. It appears that isbns that start with 0 are going in as 9 digit numbers, the 0 is being ignored or stripped. I have experienced this before with integer data types ignoring leading 0s. I'm wondering how to address this? Should I change the field to a varchar or char data type? Yes. Phone numbers, zip codes, Social Security numbers, etc., are generally stored as strings, not integers, since they can have leading 0s and you're not going to do calculations on them. Besides, don't some ISBNs end in 'X'? -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Load data infile issue
On 5 Aug 2003 at 9:37, Montagna, Dan wrote:
$result = mysql_db_query('$sql_id',DELETE FROM tablename) or die
(Invalid DELETE query);
It's helpful to print mysql_error() when you have an error, so you
get a message more specific than Invalid DELETE query. In this
case, you're trying to delete from a database called '$sql_id' --
that is, a dollar sign followed by 'sql_id'. Presumably that's not
your actual database name. Remove the single quotes (so that you're
using the *value* of $sql_id) and try again.
--
Keith C. Ivey [EMAIL PROTECTED]
Tobacco Documents Online
http://tobaccodocuments.org
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Load data infile issue
On 5 Aug 2003 at 11:16, Montagna,Dan [EMAIL PROTECTED] wrote: Thanks Keith, that got me past the first two queries but I'm now getting an Invalid DATA LOAD query . Someone told me that the load data infile option is not always on by default. Is there a piece of code I need to insert earlier in the file to turn on the load local file option? Invalid DATA LOAD query is the message you're printing in your code. You need to print mysql_error() to see the error message MySQL is giving you, which should help in figuring out what's going wrong. If I had to guess, I'd say the problem was with the location of the file (are you giving MySQL the full path?) or with permissions. -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unable to ADD databases
On 4 Aug 2003 at 12:06, Jeffery C. Baldwin wrote: Anyone notice the problem? Even though I get no error.. the sampdb is not being greated. When are you attempting to create it? All you've shown is a GRANT statement, which creates *permissions* for the database (and works even if the database doesn't exist yet). To actually create the database you need to use CREATE DATABASE: http://www.mysql.com/doc/en/CREATE_DATABASE.html -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: calender table - time column?
On 1 Aug 2003 at 17:08, Andrew wrote: Hi did anyone reply to this? It doesn't look like it. Pretend you don't know anything at all about your project and try reading your message. Would you have any idea what sort of answer was wanted? If you want help, you have to explain exactly what's needed and provide details. Otherwise your message is likely to be ignored. I am currently in the process of building a calender/date MySQL table that will be searched for available dates for holiday tours? I want to use a time column as there will be a restriction on places for up to 3 days so this will have to be taken into account. Has anyone done something like this? Cheers Andrew -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to limit COUNT(*)
On 22 Jul 2003 at 9:30, gerald_clark wrote: If you are using MYISAM tables, count(*) does not scan the table to get the count. That's only true if you have no WHERE clause and thus are counting all the rows in the table. That doesn't seem to be what the original poster is doing. -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: utf8 support
On 17 Jul 2003 at 23:03, Yves Goergen wrote: 2. What about fulltext search on UTF8 strings? Unavailable. Eh? Why should this be a special DB feature at all? Correct me if I'm wrong (I didn't have very much contect with fulltexts yet), but can't I just give mysql an UTF8 string to search in an UTF8 column (/data)? Shouldn't this work anyway? (Even if I put UTF8 data in mysql 3.23 by now and just search this?) FULLTEXT indexes are constructed by dividing the text up into words, which requires that MySQL handle the particular character encoding and character set so as to know which characters (or byte sequences) are parts of words and which aren't. Perhaps you're think of the LIKE operator? -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Best practice column type for storing decimal currency amounts?
On 16 Jul 2003 at 16:02, John Hicks wrote: Thanks for the reply, Rudy. My source for the statement that decimal values are stored as strings is: http://www.mysql.com/doc/en/Numeric_types.html : DECIMAL values may very well be stored as strings, but presumably you're going to do some calculations on them as well at some point, and it's likely that not all those calculations are going to occur in MySQL (even assuming MySQL handles DECIMAL calculations exactly). You may have an easier time in your applications doing the calculations on integers. -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How can I display images from a mySQL Database in a web page?
On 15 Jul 2003 at 18:31, Dan Anderson wrote: I think there is a way to insert binary image data in your html, but is there any particular reason you need to do that? Yes, my client's server is running with particularly restrictive PHP safe mode settings and has informed me that dynamic images are a /must have/. :: bangs head against wall. starts to bleed :: Dynamic images don't require inserting binary data into your HTML. (It is possible to use 'data:' URLs to insert arbitrary data into your HTML, but very few broswers support it, and it's not relevant for your purposes). The image and the HTML will come from separate HTTP requests, so all that's necessary is for you to put the appropriate URLs into the HTML and set up a corresponding PHP program to return the data, with the Content-type: image/gif (or whatever) header, and possibly others, depending on what you're trying to do. Except for the process of retrieving the data from the database, this has nothing to do with MySQL, so questions related to HTML, PHP, and HTTP headers should be directed to another list. -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: auto_incement foobar
On 11 Jul 2003 at 15:41, Ray wrote: and after a while someone wanted something that would always be first in the table when ordered, so i added the entry to it and edited the id to be -1. now whenever anything is added to the table it gets an id of 2147483647 how do i fix it so that it start the auto_increment at the correct spot again? Don't try to put nonpositive values into an AUTO_INCREMENT column: | MySQL Version 3.23 will also only work properly if the | AUTO_INCREMENT column only has positive values. Inserting a | negative number is regarded as inserting a very large positive | number. http://www.mysql.com/doc/en/CREATE_TABLE.html Now that you have one, delete it (or change it to a positive number), then find the maximum ID: SELECT MAX(ID) FROM pics2003; and reset the AUTO_INCREMENT counter to one more than that value: ALTER TABLE pics2003 AUTO_INCREMENT = [one more than the MAX]; If you need to sort by something other than the ID, you should introduce a new column to sort by rather than changing the ID values. -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Indexing on replicated databases
On 10 Jul 2003 at 9:39, Jeff McKeon wrote: I've got the following replication situation... A -- B -- C All data changes, updates and adds go into DB-A. I work with DB-C and pull all my queries for reporting from that. Would there be any problem with adding new indexes or different indexes to DB-C than exist in DB-A or B? If you added a unique index, then inserts or updates that succeeded on A and B could fail on C. Other than that, it seems like it should work, but it would complicate recovering from replication problems. But what would be the benefit of doing it rather than adding the indexes to all copies of the tables? Is there a command/query I can issue to show me the indexed columns for tables in DB-C (I inherited these DB's so there's still a lot I don't know about their structure). SHOW CREATE TABLE table_name; SHOW INDEX FROM table_name; -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: What's wrong with this query?
On 10 Jul 2003 at 13:20, Chris Boget wrote:
SELECT certificate.cert_num, master_info.uid
FROM certificate
JOIN master_info ON ( certificate.uid = master_info.uid )
LEFT JOIN endorsements ON ( certificate.cert_num =
endorsements.cert_num ) WHERE certificate.active = 1 AND
certificate.referred = 0 AND certificate.status IN ('O', 'C') AND
endorsements.endorse_mode='cancel' ORDER BY master_info.company,
certificate.bound, certificate.cert_num;
ERROR 1064: You have an error in your SQL syntax near 'ON (
certificate.uid = master_info.uid ) LEFT JOIN endorsements ON (
certificate' at line 1
Plain JOIN doesn't take a join condition (ON). You presumably mean
INNER JOIN. See the documentation:
http://www.mysql.com/doc/en/JOIN.html
--
Keith C. Ivey [EMAIL PROTECTED]
Tobacco Documents Online
http://tobaccodocuments.org
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Max key length error
On 10 Jul 2003 at 16:19, Brian Rivet wrote: Max key length is 500 I have looked everywhere I can think of but I can't find an explanation for what is causing the problem so I can fix it, can anyone help me? Presumably your key length is too long, but since you don't give us any clue about your table structure or indexes (by posting your CREATE TABLE statement, for example) what do you expect us to be able to say? -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Max Key Length Error
On 10 Jul 2003 at 16:33, Brian Rivet wrote: CREATE TABLE `TblUsers` (`UserId` INT(5) UNSIGNED DEFAULT NULL NOT NULL AUTO_INCREMENT, `FirstName` VARCHAR(255) DEFAULT 'unknown' NOT NULL, `LastName` VARCHAR(255) DEFAULT 'unknown' NOT NULL, `UserName` VARCHAR(255) DEFAULT 'unknown' NOT NULL, `UserPass` VARCHAR(255) DEFAULT 'unknown' NOT NULL , PRIMARY KEY (`UserId`), INDEX (`UserId`, `LastName`, `UserName`), UNIQUE (`UserId`, `UserName`)) It says the max key length is 500, I'm not sure what it's referring to, so I don't know what to fix. INDEX (`UserId`, `LastName`, `UserName`) is made up of an INT and two VARCHAR(255)s, which is more than 500 characters. You should use a prefix of the VARCHARs instead of the whole thing: INDEX (UserId, LastName(20), Username(20)) -- rather than 20 you can use whatever number is necessary to distinguish most of your strings (it doesn't matter much). Also, the last index (UNIQUE(UserId, UserName)) is pointless, because UserId is already unique on its own. -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fulltext - stop words!?
On 9 Jul 2003 at 19:07, Peter Engström wrote: I want to search for an e-mail address but I don't get any matches. I assume characters like @ . are ignored (stop words). A stop word is a *word* that is ignored in indexing -- usually things like the and and. What you're talking about is changing the set of characters that are considered to be parts of words -- usually letters and numbers. In order to have e-mail addresses indexed as single words, you would have to define every character that could occur in an e-mail address as a word character. In particular, that would mean . would have to be a word character, and I seriously doubt you would want that. Doing that would mean that searching for numbers wouldn't find this message, which contains numbers. (with a period at the end). E-mail addresses would normally be searched for as phrases, so [EMAIL PROTECTED] would be equivalent to peten714 student liu se. To search for a phrase you need to use Boolean mode. See the documentation: http://www.mysql.com/doc/en/Fulltext_Search.html -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL performance question..
On 9 Jul 2003 at 23:14, Andrew Braithwaite wrote: I'm running a server with a Pentium 133 w/32meg ram, 512 pipeline burst, with a wd 512MB HD and I want to store George Bush in our MySQL database. As far as table definitions are concerned, should I use a BLOB or should I store him on disk and make a reference to the physical location in the MySQL table instead? One way would be to convert him to a text representation following RFC 1437 and store him in a HUMONGOUSTEXT column. There's an example of sending Dan Quayle by e-mail in the RFC: http://www.faqs.org/rfcs/rfc1437.html -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: date format
On 1 Jul 2003 at 11:31, Fabio Bernardo wrote: select (current_date - 1 ) and the result was: (Currentdate -1) --- 20030700 Use this instead: SELECT CURRENT_DATE() - INTERVAL 1 DAY; The documentation for DATE_ADD() and other date and time functions is here: http://www.mysql.com/doc/en/Date_and_time_functions.html -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date query optimization
On 1 Jul 2003 at 9:25, Karl J. Stubsjoen wrote: ROWS: 34,000 + searched explain select a.submitid,a.url,a.submitdate,a.name,a.company,a.address1,a.city,a.sta te,a.z ipcode,a.country,a.email,a.phone,a.keywords,a.title,a.description,a.su bmitte dby from submit as a inner join re_idx as b on a.submitid = b.submitid where year(a.submitdate)=2003 and month(a.submitdate)=7 and dayofmonth(a.submitdate)=1; --and year(a.submitdate)=2003 and month(a.submitdate)=7 and dayofmonth(a.submitdate)15; Notice the 2nd where statement, this is how I typically do my date queries (and it is slow). This is because I might also be searching for a range of dates (as in the commented out and clause above). The normal way to do a search for a range of dates would be ... WHERE a.submitdate BETWEEN '2003-07-01' AND '2003-07-14'; Is that what you're looking for? -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date query optimization
On 1 Jul 2003 at 10:28, Karl J. Stubsjoen wrote: The normal way to do a search for a range of dates would be ... WHERE a.submitdate BETWEEN '2003-07-01' AND '2003-07-14'; Is that what you're looking for? submitdate happens to be a DATETIME field. Your suggested query doesn't pull any results. If it's a DATETIME field, you'll want something like ... WHERE a.submitdate BETWEEN '2003-07-01 00:00:00' AND '2003-07-14 23:59:59'; or ... WHERE a.submitdate = '2003-07-01 00:00:00' AND a.submitdate '2003-07-15 00:00:00'; -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Best Pratices for mySQL Backups in Enterprise
On 28 Jun 2003 at 10:12, Nils Valentin wrote: I understood that the backup done by f.e mysqldump would dump the create statement only for the index - not the actually data fo the index. That would make the backup option create smaller files than lets say if you copy it 1x1 on the OS command line basis f.e with cp. It's true that mysqldump doesn't put the data for the index itself into the dump file. That does not automatically mean that the dump files will be smaller than the .MYD, .MYI, and .frm files combined. The dump file will certainly be larger that the .MYD file -- perhaps much larger if you have lots of non-text columns. The difference may be greater or smaller than the size of the .MYI file. It depends on your data and your indexes. In most cases I'd say that whatever difference there is isn't enough the affect the choice of backup method. -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: The quote ' problem...
On 30 Jun 2003 at 22:43, [EMAIL PROTECTED] wrote: oops, i forgot this one... isn't that parameter binding only available from mysql 4.1 on? at least, when i browse through the php doc, it's part of the improved mysql extension (mysqli), available for mysql 4.1. does anyone know about version 4.0? I don't know about other APIs, but Perl DBI supports placeholders whether the RDBMS itself supports them or not. I certainly used placeholders in DBI back when I was using MySQL 3.23. You don't get the performance benefits of RDBMS-supported placeholders, but you still simplify the escaping of values. -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Best Pratices for mySQL Backups in Enterprise
On 27 Jun 2003 at 14:49, David Brodbeck wrote: FWIW, if size is a problem, mysqldump files compress quite well with gzip. (Lots of repeated text.) The same is true of update logs, if you're keeping those. The same is also true of the original tables (the .MYD files), so I stand by my statement about the size difference (whichever way it happens to go) not being a deciding factor between using mysqldump and copying the files (with mysqlhotcopy or otherwise). -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: fulltext indexing of alphanumeric strings?
On 27 Jun 2003 at 16:30, Daniel Whitener wrote:
an example of this string is V00021 - they all start with v000
[snip]
mysql select ID from emails where body like '%v000%';
[snip]
mysql SELECT ID FROM emails WHERE MATCH(h_subject, body) AGAINST
('v000');
Full text searching searches by word, not by pieces of a word. If
the word is 'V00021' you won't find it by searching for 'V000'.
You can, however, use an asterisk at the end for a wildcard:
SELECT ID FROM emails WHERE MATCH(h_subject, body) AGAINST
('V000*');
Read the documentation here:
http://www.mysql.com/doc/en/Fulltext_Search.html
--
Keith C. Ivey [EMAIL PROTECTED]
Tobacco Documents Online
http://tobaccodocuments.org
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: fulltext indexing of alphanumeric strings?
On 27 Jun 2003 at 16:47, Daniel Whitener wrote:
mysql SELECT ID FROM emails WHERE MATCH(h_subject, body) AGAINST
('V000*'); Empty set (0.00 sec)
any good reason why that wouldn't work?
Sorry, my mistake. The asterisk only works in Boolean mode (which
requires MySQL 4). Try this instead:
SELECT ID FROM emails WHERE MATCH(h_subject, body) AGAINST
('V000*' IN BOOLEAN MODE);
--
Keith C. Ivey [EMAIL PROTECTED]
Tobacco Documents Online
http://tobaccodocuments.org
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Best Pratices for mySQL Backups in Enterprise
On 26 Jun 2003 at 12:24, SAQIB wrote: mysqlhotcopy does your locking for you. So running ./mysqlhostcopy dbase /path/to/backup/dir is perfectly safe while database operations (selct, insert, update etc) are being performed? Yes, but the inserts and updates will all be blocked until after the copying is done. If your database is big enough and active enough that locking it during the copying time is unacceptable, consider setting up a replication server and backing that up instead. -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: sum() problems - I don't understand
On 26 Jun 2003 at 15:07, gerald_clark wrote: The second query contains a join and may have many times more rows in the result set. [snip] ++-+--++--+ | date1 | sum1| sum2 | cod| fromprev | ++-+--++--+ | 29-05-2003 | 436.498 | 10 | SC0001GP | 2666 | ++-+--++--+ [snip] | date1 | sum1 | sum2 | cod | fromprev | numprev | date2 | ++--+--+-+--+-+-- +--- ---+ | 29-05-2003 | 3491.984 | 80 | SC0001GP| 2666 |2666 | 28-05-2003 | ++--+--+-+--+-+-- In fact it appears that for this query there are 8 rows in 'carello' corresponding to each row in 'ordini', so the sums are multiplied by 8. -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Best Pratices for mySQL Backups in Enterprise
On 26 Jun 2003 at 17:16, Ware Adams wrote: mysqldump creates text files containing insert statements that recreate a table and repopulate it with data. They are somewhat portable across database servers and human editable if necessary. They take up less space than the original table because they do not contain indices (only the statements that would create the indices). The dump file will be larger than the MyISAM data file for the original table -- especially if you have many non-text columns (dates, numbers, ENUM columns, etc.). In some cases, when you have large indexes, the index file will be large enough that it and the data file combined will be larger than the dump file, but in some cases it won't be. I wouldn't consider the difference in size, whichever way it goes, to be significant in deciding between backup methods. -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Speed Up Insert Query Results
On 26 Jun 2003 at 17:45, Charles Vos wrote: Could somebody please enlighten me as to why it takes nearly 2 hours to put 8 rows of data into my table? It's not the INSERT that's taking so long -- it's the SELECT. Try the SELECT alone, and it will probably take the same amount of time. Do an EXPLAIN on each of your SELECT queries and compare the use of indexes to see why there's such a difference. -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: can you insert null?
On 26 Jun 2003 at 15:01, danchik wrote: was the field type varchar? because it seems that no default varchars set the NULL or (NULL) as a literal (NULL) not a binary 0 for some reason. You're confusing various meanings of null. NULL in SQL has nothing to do with binary 0 bytes (which would be represented as '\0' in MySQL SQL statements). -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: What's wrong with this query?
On 19 Jun 2003 at 16:18, Chris Boget wrote:
Why isn't the key being used in the c (certificate) table?
SELECT DISTINCT a.uid, a.company, a.firstname, a.lastname
FROM master_info a, logins lsl, logins lc, certificate c WHERE
a.uid = lsl.uid AND lc.parent = lsl.uid AND lc.uid = c.uid AND
c.void 1 AND c.status IN
('CP', 'MC', 'AIC', 'RP', 'VQ', 'NQ', 'LQ', 'RQ', 'NG', 'EP', 'AA',
'AD' ) AND lsl.active = 1 AND lsl.void = 0 ORDER BY company, uid
How do you think the key should be used? You have a key on status,
but you're asking for a wide range of status values. Presumably
MySQL thinks (probably correctly) that using the index to find a
range of status values from 'AA' to 'VQ' is no faster than doing a
full table scan.
I could be missing something, but if so it would help if you could
explain how you expect the key to be used.
--
Keith C. Ivey [EMAIL PROTECTED]
Tobacco Documents Online
http://tobaccodocuments.org
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: NOT NULL ?
On 16 Jun 2003 at 19:29, Becoming Digital wrote: Think of the many cases in which you would not want a field left blank: a customer's last name or zip code, a product's name or id number, a payment amount. Were any of those fields allowed to be NULL, the system of which they are a part could fall on its face. We wouldn't want that, would we? You are using left blank to mean set to NULL, but many people would describe a last name of (the empty string) as blank, and using NOT NULL does nothing to prevent that. Also, if a zip code were allowed to be 'ABCDE', the system would fall on its face just as much, and NOT NULL does nothing there either. I would recommend using NOT NULL for any column where you're not specifically making use of NULL values. It saves complication as well as a bit of space. But it's no substitute for validating the data before inserting it into your table. -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
More anti-spam annoyance
It's not bad enough that we have subscribers with challenge-response systems misconfigured so that they respond to each message from the list. Now one of the list admins is apparently doing something similar. At present rates of growth, it will be interesting to see whether spam or anti-spam responses end up flooding the e-mail system more. --- Forwarded message follows --- Date sent: Tue, 17 Jun 2003 08:42:15 -0600 From: GWAVA [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject:[GWAVA:1a61d810] Source block message notification This is an automated Notification e-mail message from GWAVA. The message you sent, from the address [EMAIL PROTECTED] to [No To Addresses] Concerning: Re: NOT NULL ? Was not delivered because it comes from an e-mail address which is not permitted by the e-mail system in use at the intended recipient's organization. If you are subscribed to a MYSQL Discussion group please ignore this email You are still subscribed to the service. Your email is not getting to one of our MYSQL admins, this is OK. They read up on the group later. If you need to get your emails through this system please email [EMAIL PROTECTED] with the subject line UNBLOCK ADDRESS. In the body of the email. You MUST also INCLUDE the email address you are communicating with. We will the review and make a decision from there. --- End of forwarded message --- -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Now() and time function bug??
On 17 Jun 2003 at 14:05, Lucas Heuman wrote: After I set starttime to now(), anytime I run another update query against the row starttime changes to a new value. You don't give the structure of your table, but it appears that you're using a TIMESTAMP column, which has automatic updating by design: http://www.mysql.com/doc/en/DATETIME.html If you don't want automatic updating, perhaps you should use a DATETIME column instead. -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: again with SELECT
On 13 Jun 2003 at 4:02, gamin wrote: To each colorID you assign a unique prime number (maintain this in a table). For each itemID now you have a unique colorPRODUCT (product of the prime numbers coressponding the various colorIDs for this itemID). To add a colorID to an itemID simply muliply the new prime (coressponding to the colorID being added) to the old colorPRODUCT. In the same way to remove a colorID devide colorPRODUCT by the corressponding prime. And to find the itemID which has a particular set of colors just check for the colorPRODUCT. A more normal way, which wouldn't require such large numbers or the complication of factoring, would be to assign powers of 2 to the colors and add them rather than multiplying them. Or you could use the SET column type in MySQL, which handles the powers of 2 behind the scenes: http://www.mysql.com/doc/en/SET.html -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Turning off column and value reconciliation
On 9 Jun 2003 at 12:13, Campbell D. McCausland wrote: http://forums.devshed.com/t49723/s.htmlGeneral error: Column count doesn't match value count I know what it means, but I need MySQL to turn off this checking. I'm loading legacy data into a new database that has some extra columns and I just want to fill the old columns with the legacy data. It's hard to know since you don't show us any of your code, but it looks like you should add the list of column names to your INSERT or LOAD statement rather than letting the list default to all the columns. -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Turning off column and value reconciliation
On 9 Jun 2003 at 13:16, Campbell D. McCausland wrote: Thank you for your responses. Unfortunately, the sql I'm loading is output from another tool, so I don't get to vote on the format of the insert statements. If it's not possible to just turn the check off, then I guess I'll have to consider writing a perl script or something to add the column specifiers. Another way would be to make a table with the old structure (without the new columns) and import into it, then do a SELECT ... INSERT from it into the real table, specifying the columns. -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: UPDATE doesn't work
On 6 Jun 2003 at 19:00, PaT! wrote: mysql UPDATE ordini SET prezzo = prezzo/1.024, totale = totale/1.024 WHERE numordine1385 AND dataord'2003-06-01'; Query OK, 1649 rows affected (0.10 sec) Rows matched: 1650 Changed: 1649 Warnings: 0 These are the values after the UPDATE mysql SELECT prezzo, totale FROM ordini WHERE numordine=1157; +++ | prezzo | totale | +++ | 6.795 | 6.795 | | 13.835 | 13.835 | | 17.578 | 17.578 | | 62.500 | 62.500 | +++ 4 rows in set (0.02 sec) Why the values didn't update? Your update query has WHERE numordine 1385, but 1157 isn't greater than 1385. Why would you expect the values to be updated? -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with Date Range Query
On 6 Jun 2003 at 20:34, Trevor Sather wrote: mysql SELECT StartDate FROM Events - WHERE TO_DAYS(NOW()) - TO_DAYS(StartDate) = 30; ++ | StartDate | ++ | 2004122600 | | 2003072100 | | 2003080600 | | 2003092600 | | 2003051000 | | 2003063000 | | 2003051700 | | 2003101700 | | 2003050700 | | 2003070700 | | 2003051000 | | 2003051700 | | 2003051000 | | 2003051500 | | 2003062800 | | 2003070600 | | 2003080300 | ++ 17 rows in set (0.01 sec) Most of your dates are in the future, so they'll give negative results for the calculation, which are less than 30. You're getting what you asked for: everything starting from 30 days ago. You said nothing about stopping with today, or wherever you're intending to stop. Perhaps you want to change = 30 to BETWEEN 0 AND 30. -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
