Re: 2 GB limit reached
I've had no problems with MySQL RAID, and the performance is excellent as long as you have your indices set up properly. One caveat I might add is that MySQL RAID does not apply to index files (.MYI). If your index files are going to grow 4 GB, you'll need to consider alternate filesystems anyway. For your reference, I'm running v3.23.42. My database has one primary MERGE table which collates the data from 12 month-specific tables. Each of those month-specific tables uses MySQL RAID with the following options: MAX_ROWS=20 PACK_KEYS=1 RAID_TYPE=striped RAID_CHUNKS=32 RAID_CHUNKSIZE=256000 There are currently over 42 million entries in the MERGE table, occupying over 54 GB of disk. There are also various smaller lookup tables, etc. -- coop The 2gig limit is a problem that I'm going to hit fairly shortly - perhaps someone with a little more knowledge can tell me what the performance will be like using mysql's raid rather than OS large file support? Also where can one find good information about linux large file support - on my slackware 8, 2.4.17, ext2 testbox I can create 4 gig files using dd but mysql failed to create a table greater than that size (not quite sure why it's 4gig rather than 2gig - suggests something's working :). - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: timestamp additional info
Don't reference the timestamp column at all in your INSERT (or future
UPDATE) statements and the timestamp should update just fine on its own.
i.e.
INSERT INTO Owners (NameID,ProductsKey,RegNum)
VALUES ('$NameID','1','$RegNumc');
BTW, you cannot change the default for a timestamp column - it is always
NULL (which displays as '00').
HTH,
--
coop
On Fri, 2001-12-14 at 15:16, Steve Osborne wrote:
Timestamp additional info:
INSERT INTO Owners (NameID,ProductsKey,RegNum,ProdRegDate)
VALUES ('$NameID','1','$RegNumc','NULL');
ProdRegDate is the field that I want to timestamp. (Again, I've tried
passing '', NULL, and 'NULL').
Steve Osborne
Database Programmer
Chinook Multimedia Inc.
[EMAIL PROTECTED]
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Selecting boundary dates for a given week using date/time functions?
Hey all, I'm hoping someone can help me out with a little date/time problem I'm having. I've got a database full of timestamps that I want to group together based on their week. I can use the WEEK() function or DATE_FORMAT() to convert the timestamp to a week number, but I'd rather display the boundary dates for the week because I don't feel the week number is very informative. e.g. Given the timestamp '2004095959' (a Wednesday), I would like to output '2001-11-12 to 2001-11-18' (which is Monday to Sunday). Is there a way using the date/time functions of MySQL to make this conversion in my SELECT statement? I haven't been able to figure out a way to get MySQL to use a week number to generate these boundary dates. I'm assuming the final SQL will look something like: SELECT CONCAT(function_to_get_monday, ' to ', function_to_get_sunday)... Any help is appreciated. -- coop - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Selecting boundary dates for a given week using date/timefunctions?
Perfect. Thanks, Anvar. -- coop On Sun, 2001-11-25 at 22:20, Anvar Hussain K.M. wrote: Hi Chris, The following query should work for you. select date_sub(date_col, interval weekday(date_col) day), date_add(date_col, interval 6-weekday(date_col) day) from table_name Anvar. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MERGE tables and INSERT_METHOD
Has anyone else tried to set the INSERT_METHOD for MERGE tables? It keeps throwing a syntax error for me. Following the docs verbatim (http://www.mysql.com/doc/M/E/MERGE.html), here's what I get: output mysql CREATE TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY, message CHAR(20)); Query OK, 0 rows affected (0.02 sec) mysql CREATE TABLE t2 (a INT AUTO_INCREMENT PRIMARY KEY, message CHAR(20)); Query OK, 0 rows affected (0.00 sec) mysql INSERT INTO t1 (message) VALUES (Testing),(table),(t1); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql INSERT INTO t2 (message) VALUES (Testing),(table),(t2); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql CREATE TABLE total (a INT NOT NULL, message CHAR(20), KEY(a)) TYPE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST; ERROR 1064: You have an error in your SQL syntax near 'INSERT_METHOD=LAST' at line 1 end_output I checked the release notes/changelog, but there's no mention of the INSERT_METHOD being broken or fixed. I'm running MySQL version 3.23.42-log on RH 7.1. -- coop - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Newbie: Can't get GRANT to work
http://www.mysql.com/doc/P/r/Privilege_changes.html If you modify the grant tables manually (using INSERT, UPDATE, etc.), you should execute a FLUSH PRIVILEGES statement or run mysqladmin flush-privileges or mysqladmin reload to tell the server to reload the grant tables. Otherwise your changes will have no effect until you restart the server. If you change the grant tables manually but forget to reload the privileges, you will be wondering why your changes don't seem to make any difference! HTH, -- coop On Wed, 2001-11-14 at 13:29, KEVIN ZEMBOWER wrote: I'm certain I'm making a simple mistake here, but can't find it. Here's what I'm executing, using mysql 3.23.43-log: [I'm root when I run this] #mysql -p mysql mysql grant all on zope.* to zope identified by xxx; Query OK, 0 rows affected (0.00 sec) mysql select * from user where user=zope; +--+--+--+-+-+-+-+-+---+-+---+--+---++-+++ | Host | User | password | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | +--+--+--+-+-+-+-+-+---+-+---+--+---++-+++ | %| zope | 34577362486f3680 | N | N | N | N | N | N | N | N | N| N | N | N | N | N | +--+--+--+-+-+-+-+-+---+-+---+--+---++-+++ 1 row in set (0.00 sec) mysql My questions: 1. Why didn't this work? 2. If it had worked, would I have seen something other than zero records affected in response to the GRANT command? I search the recent messages on GRANT and found the suggestion to run mysql_fix_privilege_tables. It seemed to run correctly, but didn't change anything. Thanks for helping this newbie. -Kevin Zembower - E. Kevin Zembower Unix Administrator Johns Hopkins University/Center for Communications Programs 111 Market Place, Suite 310 Baltimore, MD 21202 410-659-6139 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Timestamp allways zero zero
See below. On Wed, 2001-11-14 at 14:40, Jason Wong wrote: The above can be re-written as: //En record for hvert navnedomene $sql = CREATE TABLE domains ( dNbr INTEGER UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, dName CHAR(25) NOT NULL, payPrYr SMALLINT DEFAULT 0 NOT NULL, nextPayDay DATE NULL, members SMALLINT DEFAULT 0 NOT NULL, noPayMembers SMALLINT DEFAULT 0 NOT NULL, timeChanged TIMESTAMP (14), timeCreated TIMESTAMP (14), INDEX domain(dName)); Which IMHO is clearer. [deletia] Note you probably DON'T want timeCreated to be a TIMESTAMP field as everytime to update the record and do not specify a value for it, it's value will be changed to the time at which the record was updated. You should probably be using a DATETIME field for this. If you're using a timestamp field then there is no need to specify a value for it when using INSERT, UPDATE or REPLACE. That's not entirely true. If there are multiple TIMESTAMP columns in a table, MYSQL will only update the first TIMESTAMP column it finds with the current date/time when INSERT-ing or UPDATE-ing. Other TIMESTAMP columns are left unchanged. Given the table structure above, only the timeChanged field will be updated, which I believe is the intended behavior. HTH, -- coop - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
