MYD files deleted
Hello All, I'm using mysql version 3.23.47 as a database in an embedded device. In the startup scripts for the database I have it run : myisamchk -o -s /usr/local/var/data/*/*.MYI (to fix any possible problems) with safe_mysqld --datadir=/usr/local/var/data (to start the database) On one occasion, during testing I came across an issue where if I poweroff the machine while the database is starting it seems to DELETE many of the MYD files, including host.MYD, user.MYD, along with data MYD files. Of course this makes the database dead. Its not clear to me if its the myisamchk, safe_mysqld, or something else that is actually killing these files. I'm not concerned with retrieving this data, but I do want to find exactly what, and why this is happening and implement a fix. I should note that I am able to re-create the problem if the timing is correct. A possible low tech solution will include copying the MYD files (most of my data is stored in innodb and was not harmed) before starting the database and restoring them from the backup if they are missing. Of course, I would prefer a more proper solution. I'd appreciate knowing if anyone else has had this problem and what version you're using, even if a solution hasn't been found. Thanks, Eric Mayers Software Engineer Captus Networks - 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
encode invalid characters for mysql in c++
I'm looking for a function that will take a string or char* of 256bit ascii (or other stuff) and convert it into something that can be inserted into a varchar type in mysql. Then when the data gets pulled out it would be converted back. I know I need to convert quotes and some control characters. Is there such a function in the mysqlc++ libraries or somewhere that I can use for this? What is done for binary files such as images? Thanks, Eric - 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: SubSelect Problem
Alex, I believe you'll find that MySQL does not support subselects. This is why you're getting the error message. I suggest you consult the SELECT syntax manual page (http://www.mysql.com/doc/S/E/SELECT.html) to see what you can do with selects. You could try to convert this into a join, or break the operation into your application. Eric Mayers Software Engineer I Captus Networks -Original Message- From: Alex Speed [mailto:[EMAIL PROTECTED]] Sent: Monday, March 18, 2002 4:13 PM To: [EMAIL PROTECTED] Subject: SubSelect Problem SELECT textid, title, description from structure where textid in (select subtopicid from subtopic where topicid='Top'); this gives an error at the opening bracket.. I am assuming that mysql realises that structure.textid=subtopic.subtopicid ...any ideas on getting this working? thanks for any help! Alex - 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
myismchk messages (too small length)
I'm running myisamchk (when the database is not running) after a dirty shutdown and getting what appear to be errors it doesn't fix... bash-2.04# ./myisamchk -o -s -e -f /usr/local/var/ccdata/*/*.MYI Found block with too small length at 56452; Skipped When I run it again, I get the same message.. bash-2.04# ./myisamchk -o -s -e -f /usr/local/var/ccdata/*/*.MYI Found block with too small length at 56452; Skipped Can someone tell me what this means? Why can't myisamchk can't fix this (is it a problem?)? How can I fix it? I'm trying to set it up to fix itself if possible. Thanks, Eric Mayers Software Engineer I Captus Networks sql! - 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
max_data_length?
Why is the max_data_length portion of Show table status ... for innodb tables null? Is there a way to get this value? I want to use this and data_length to display a % of space used statistic. Eric Mayers Software Engineer I Captus Networks sql,query - 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: max_data_length?
Okay, this is clearly not what I want. I'm looking for a method to see how much space a table is using compared to the total amount of space available. Is there a way to do this (with innodb tables)? Eric Mayers -Original Message- From: Keith C. Ivey [mailto:[EMAIL PROTECTED]] Sent: Monday, February 25, 2002 11:33 AM To: [EMAIL PROTECTED] Subject: Re: max_data_length? On 25 Feb 2002, at 10:03, Eric Mayers wrote: Why is the max_data_length portion of Show table status ... for innodb tables null? Is there a way to get this value? I want to use this and data_length to display a % of space used statistic. I think you're misunderstanding what the max_data_length number means. It has nothing to do with an amount of space available. At least for MyISAM tables, it seems to be based entirely on the size of the pointers used in the indexes. Thus in general it's 4 GB (for 4-byte pointers), except for tables that have had MAX_ROWS and AVG_ROW_LENGTH set specifically to use pointers larger or smaller than that, or for tables with fixed-length rows (in which the pointers indicate row numbers rather than byte numbers, so the max table sizes are multiplied by the row length). Filter fodder: sql, query -- Keith C. Ivey [EMAIL PROTECTED] Washington, DC - 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: InnoDB question
Oganes, It sounds like what you want is row-level-locking. This is a feature of InnoDB tables. It allows users to write to a table while other users are reading from the same table. Of course, they cannot read and write the same rows simultaniously. Have you tried to do this and had some problem? If so you need to be much more specific about the problem you're encountering. So, to answer your question: You can already do this. Eric Mayers Software Engineer I Captus Networks -Original Message- From: Demirchyan Oganes-AOD098 [mailto:[EMAIL PROTECTED]] Sent: Monday, February 25, 2002 2:34 PM To: '[EMAIL PROTECTED]' Subject: RE: InnoDB question Hello everyone, I have this database, I'm using InnoDB type tables. I wanted to know the following: How can I manipulate the tables, so that db supports multiple users trying to access the same table for writing or reading purposes at the same time. Thanks, Oganes Demirchyan Motorola Life Science 757 S.Raymond Pasadena, CA 91105 Tel: 626-584-5900 email: [EMAIL PROTECTED] -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Monday, February 25, 2002 2:26 PM To: Demirchyan Oganes-AOD098 Subject: Re: InnoDB question Your message cannot be posted because it appears to be either spam or simply off topic to our filter. To bypass the filter you must include one of the following words in your message: sql,query If you just reply to this message, and include the entire text of it in the reply, your reply will go through. However, you should first review the text of the message to make sure it has something to do with MySQL. Just typing the word MySQL once will be sufficient, for example. You have written the following: Hello everyone, I have this database, I'm using InnoDB type tables. I wanted to know the following: How can I manipulate the tables, so that db supports multiple users trying to access the same table for writing or reading purposes at the same time. Thanks, Oganes Demirchyan Motorola Life Science 757 S.Raymond Pasadena, CA 91105 Tel: 626-584-5900 email: [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 - 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: Beginner needs help
Bob, This is just a guess, but looking at http://www.mysql.com/doc/I/n/Insert_speed.html it appears that the way to do multiple inserts looks like: INSERT INTO a VALUES (1,23),(2,34),(4,33); a(col1, col2).. Perhaps multiple inserts requires that you specify all columns in the order that the table was defined? I suggest you try: INSERT into book values ( null, #booknum '1-56592-434-7', #isbn 'MySQL mSQL',#title 'Yarger', #authlname 'Randy Jay', #authfname O'Reilly,#publisher null,#pubdate null, #dewey null, #lcnum null,#checkin null,#checkout null #duedate ), values( null, '0-312-25313-3', 'Coup de Grace', 'Borthwick', 'J S', St Martin's Minotaur, null, '813.54-dc21', 'PS3552.O756 C68 2000' null, null, null ); -Original Message- From: Bob Rea [mailto:[EMAIL PROTECTED]] Sent: Monday, February 25, 2002 3:34 PM To: [EMAIL PROTECTED] Subject: Beginner needs help I'm getting a syntax error when I try to insert more two sets of values into a table. I have looked at the manual on the insert statement, and don't see what is wrong. Can someone tell me. Here's the info. mysql describe book; +---+-+--+-+-++ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-++ | booknum | int(20) | | PRI | NULL| auto_increment | | isbn | varchar(14) | YES | | NULL| | | title | varchar(50) | YES | | NULL| | | authlname | varchar(20) | YES | | NULL| | | authfname | varchar(20) | YES | | NULL| | | publisher | varchar(20) | YES | | NULL| | | pubdate | date| YES | | NULL| | | dewey | varchar(20) | YES | | NULL| | | lcnum | varchar(20) | YES | | NULL| | | checkin | date| YES | | NULL| | | checkout | date| YES | | NULL| | | duedate | date| YES | | NULL| | +---+-+--+-+-++ 12 rows in set (0.00 sec) Here's the attempt: mysql INSERT into book( - isbn, - title, - authlname, - authfname, - publisher, - pubdate, - dewey, - lcnum - ) - values ( - '1-56592-434-7', - 'MySQL mSQL', - 'Yarger', - 'Randy Jay', - O'Reilly, - null, - null, - null - ), - values( - '0-312-25313-3', - 'Coup de Grace', - 'Borthwick', - 'J S', - St Martin's Minotaur, - null, - '813.54-dc21', - 'PS3552.O756 C68 2000' - ); ERROR 1064: You have an error in your SQL syntax near 'values( '0-312-25313-3', 'Coup de Grace', 'Borthwick', 'J S', St Martin's Minot' at line 21 TIA -- Bob Rea ** On the side of the box, under 'System Requirements', it said 'Requires Windows 95 or better'. So I installed Linux. ** [EMAIL PROTECTED] http://home.earthlink.net/~sfpetard/ - 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: Bugs and more Bugs.
Joedilson, the syntax for specifying a password to mysqladmin is --password=my_password, or -p if you want it to prompt. (Notice that it says, Using password: NO .. thats because it doesn't realize you're trying to use a password). You also need to specify the password when you're using mysql .. so.. /usr/local/mysql/bin/mysql --password=my_password mysql_create.sql In general I suggest you take a look at mysqladmin --help | more and mysql --help | more Having the applications themselves answer your questions will be much faster than a mailing list. =) Eric Mayers Software Engineer I Captus Networks -Original Message- From: Joedilson B. Azevedo [mailto:[EMAIL PROTECTED]] Sent: Friday, February 22, 2002 3:08 PM To: [EMAIL PROTECTED] Subject: Bugs and more Bugs. Hi, I installed the BD mysql and I am some problems. # /usr/local/mysql/bin/safe_mysqld It's ok. Here: # mysqladmin password my_password /usr/local/mysql/bin/mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user: 'root@localhost' (Using password: NO)' And when I try do tables in mysql /usr/local/mysql/bin/mysql mysql_create.sql ERROR 1045: Access denied for user: 'root@localhost' (Using password: NO) What I do? Release: mysql-3.22.32 (Source distribution) Environment: System: Linux name_of_machine 2.2.16-22 #1 Tue Aug 22 16:49:06 EDT 2000 i686 unknown Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/2.96/specs gcc version 2.96 2731 (Red Hat Linux 7.0) Compilation info: CC='gcc' CFLAGS='' CXX='c++' CXXFLAGS='' LDFLAGS='' Configure command:./configure --prefix=/usr/local/mysql Thank you for you help. Joedilson - Um fracasso na vida nao representa o fim da utilidade - - 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: off-line development tool?
I suggest you install a MySQL Database on a local machine and do development there. When you want to push this stuff to the remote machine use mysqldump to pull the contents out of the local database. I don't think it would be appropriate for this but you might consider database replication if you want it to be a more automatic process. Eric Mayers Software Engineer I Captus Networks -Original Message- From: Marco Bleeker [mailto:[EMAIL PROTECTED]] Sent: Friday, February 22, 2002 5:59 PM To: [EMAIL PROTECTED] Subject: off-line development tool? Hello, I am a beginning MySQL and PHP user. I will run a MySQL database on a remote hosting server, and I have only limited access to the database at that server. I want to populate my database off-line at my home machine, and then upload the whole thing (100s of lengthy entries) to the server. But I don't have direct access there. So I suppose I'll have to do something like exporting my data to a text file, upload the text file to the root of my domain at the server and re-create the database from there. I don't think I can run any tool on the server; I work with .php files there. So my question is: how would people normally go about this task. Would I need to write a script for in- and export of data, or would there perhaps be a handy tool that takes this out of my hands? Thanks, Marco Bleeker, Amsterdam _ MSN Foto's is de eenvoudigste manier om je foto's te delen en af te drukken: http://photos.msn.nl/Support/WorldWide.aspx - 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
innodb table status : Why is Max_data_length NULL?
I have two InnoDB tables using a pool of space (the combined size of the data files may change). I want to limit each to using half the amount of space (or split it in some manner .. 60%/40%, etc). So far I've done this with a daemon that runs all the time deleting appropriate rows when the table nears capacity (I have it looking at the Data_length and Index_length columns of Show table status like 'tablename' and compairing the sum to the max that I've hardcoded).. But of course this requires that magic number in there. I'd like to be able to pull the max out of the database somehow, which brings me to my question.. Why is max_data_length null? And is there a way I can get to this value? Is there a better/different way to do this? Thanks, Eric Mayers Software Engineer I Captus Networks query. - 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: Does delete from .. where (condition) use an index?
Heikki, Here is what you asked for: mysql EXPLAIN SELECT * FROM Syslog USE INDEX(ds_index) WHERE datestamp = 20020213185230; ++--+---+--+-+--+--+ + | table | type | possible_keys | key | key_len | ref | rows | Extra | ++--+---+--+-+--+--+ + | Syslog | ALL | ds_index | NULL |NULL | NULL | 4719 | where used | ++--+---+--+-+--+--+ + 1 row in set (0.00 sec) And here is a single InnoDB Monitor output: = 020214 9:07:25 INNODB MONITOR OUTPUT = -- SEMAPHORES -- OS WAIT ARRAY INFO: reservation count 85376, signal count 83573 Mutex spin waits 107640, rounds 1033422, OS waits 1 RW-shared spins 34894, OS waits 17557; RW-excl spins 34957, OS waits 34400 TRANSACTIONS Trx id counter 0 10816708 Purge done for trx's n:o 0 475530 undo n:o 0 0 Total number of lock structs in row lock hash table 13009 ---TRANSACTION 0 10816707, OS thread id 876555, not started, runs or sleeps MySQL thread id 205, query id 12109132 localhost root INSERT INTO logs.Syslog set caid='630', datestamp=NULL, message='4187481481 1481418748 418748 418748 ---TRANSACTION 0 5633944, OS thread id 1445901 updating or deleting, active, runs or sleeps, has 13010 lock struct(s), undo log entries 650408 MySQL thread id 344, query id 6056015 localhost root updating DELETE FROM logs.Syslog where (datestamp = (20020213190141 + (3600))) FILE I/O I/O thread 0 state: waiting for i/o request I/O thread 1 state: waiting for i/o request I/O thread 2 state: waiting for i/o request I/O thread 3 state: waiting for i/o request Pending normal aio reads: 0, aio writes: 0, ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 1 1164459 OS file reads, 1266570 OS file writes, 100823 OS fsyncs 23.25 reads/s, 27.88 writes/s, 2.06 fsyncs/s - INSERT BUFFER - Ibuf for space 0: size 518, free list len 272, seg size 791, 9152117 inserts, 8984010 merged recs, 675487 merges --- LOG --- Log sequence number 0 4190770295 Log flushed up to 0 4190657162 Last checkpoint at 0 4178363488 1 pending log writes, 0 pending chkp writes 52481 log i/o's done, 0.88 log i/o's/second -- BUFFER POOL AND MEMORY -- Total memory allocated 33029216; in additional pool allocated 269312 Free list length 121 LRU list length 856 Flush list length 805 Buffer pool size 1024 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 1256311, created 255856, written 1602244 24.31 reads/s, 3.88 creates/s, 36.00 writes/s Buffer pool hit rate 994 / 1000 -- ROW OPERATIONS -- 1 queries inside InnoDB; main thread: flushing log Number of rows inserted 10806106, updated 0, deleted 806508, read 6850315 181.44 inserts/s, 0.00 updates/s, 11.69 deletes/s, 11.69 reads/s END OF INNODB MONITOR OUTPUT Eric -Original Message- From: Heikki Tuuri [mailto:[EMAIL PROTECTED]] Sent: Wednesday, February 13, 2002 5:59 PM To: Eric Mayers; [EMAIL PROTECTED] Subject: Re: Does delete from .. where (condition) use an index? Eric, print what EXPLAIN SELECT * FROM Syslog USE INDEX(ds_index) WHERE ... says. Please also show what the InnoDB monitor prints. Regards, Heikki -Original Message- From: Eric Mayers [EMAIL PROTECTED] To: Heikki Tuuri [EMAIL PROTECTED]; [EMAIL PROTECTED] [EMAIL PROTECTED] Date: Thursday, February 14, 2002 3:44 AM Subject: RE: Does delete from .. where (condition) use an index? Heikki, mysql EXPLAIN SELECT * FROM Syslog WHERE datestamp = 2002021310712; ++--+---+--+-+--+ -+- ---+ | table | type | possible_keys | key | key_len | ref | rows| Extra | ++--+---+--+-+--+ -+- ---+ | Syslog | ALL | ds_index | NULL |NULL | NULL | 2204932 | where used | ++--+---+--+-+--+ -+- ---+ 1 row in set (0.00 sec) How can I force it to use the index? (the indexes look like: mysql show index from Syslog; +++--+--+ -+- --+-+--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Comment | +++--+--+ -+- --+-+--++-+ | Syslog | 1 | ds_index |1 | datestamp | A |5893 | NULL | NULL | | | Syslog | 1 | ca_index |1 | caid
select then delete everything selected
Hi List, I need to select a set of stuff from the database and then delete exactly the same stuff. I've formed two query statements and I'm tacking modifications to their ends to be consistent, but I'm not sure if with a limit clause the database will delete the same set of contents selected? Here's what I'm doing (in pesudocode): query = SELECT * FROM log where id in (idlist) ; dquery = DELETE FROM log where id in (idlist) ; (if test1) querymod = AND message like '%include%' ; (if test2) querymod .= AND message not (like '%exclude%') ; (if test3) querymod .= LIMIT 1000; query = query + querymod; dquery = dquery + querymod; mysql_query(query); fetch_and_prepare_results(); (if delete_shown) mysql_query(dquery); display_results(); .. So, in this case is there any way I can guarantee that the items deleted are the same ones that get displayed? Is there any way I can do this without including a unique identifier column and linking the delete statement into that? Thanks, Eric Mayers Software Engineer I Captus Networks - 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: data type bigint(20)
John, The number in the parenthsis tells mysql how to format the column when it goes to display it.. Here is the relevant section of http://www.mysql.com/doc/N/u/Numeric_types.html: Another extension is supported by MySQL for optionally specifying the display width of an integral value in parentheses following the base keyword for the type (for example, INT(4)). This optional width specification is used to left-pad the display of values whose width is less than the width specified for the column, but does not constrain the range of values that can be stored in the column, nor the number of digits that will be displayed for values whose width exceeds that specified for the column. When used in conjunction with the optional extension attribute ZEROFILL, the default padding of spaces is replaced with zeroes. For example, for a column declared as INT(5) ZEROFILL, a value of 4 is retrieved as 4. Note that if you store larger values than the display width in an integer column, you may experience problems when MySQL generates temporary tables for some complicated joins, as in these cases MySQL trusts that the data did fit into the original column width. Eric -Original Message- From: John D. Kirkpatrick [mailto:[EMAIL PROTECTED]] Sent: Thursday, February 14, 2002 3:27 PM To: [EMAIL PROTECTED] Subject: Re: data type bigint(20) MySQL Gurus, I'm trying to figure out what the number after the type means for numbers. I noticed in someone's code that for the ID field they used bigint(20). bigint I thought was fixed at 8 bytes. Does this override the size? 20 bytes??? 20 bits? Thanks John - 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
Delete oldest data
I'm using MySQL-Max-3.23 and I need to keep a table from exceding a certain size. I plan on running a cron job that checks the size of the table and if its close to the max, executes a delete statement. The problem I have is that 3.23 doesn't support delete from .. order by .. -- this was added in MySQL 4.x .. Ideally I'd use DELETE FROM table ORDER BY datestamp ASC LIMIT 1, but of course this doesn't work. How are people doing this in 3.23.xx? Thanks, Eric Mayers - 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
InnoDB, MySQL 3.23 inserts and deletes
I'm using MySQL-Max-3.23 and have an InnoDB table that's getting hit with lots of inserts .. I'm pushing them as fast as I can to strain the box and getting 200-300/sec. The table consists of a timestamp (timestamp(14)) and a message (char(255)). I need to keep the table from exceding a certain size and need it to keep only the newest records (fifo-like). I plan on running a cron job that checks the size of the table (with show table status, looking at the Data_length field) and if its close to the max, executes a delete statement to remove some chunk of the oldest records. I'm having a few issues.. First is that 3.23 doesn't support delete from .. order by .. -- this was added in MySQL 4.x .. Ideally I'd use DELETE FROM table ORDER BY datestamp ASC LIMIT 1, but of course this wont work. How are people doing this in 3.23.xx? So, as an alternative I'm grabbing the min(timestamp), adding some value (an hour for example) and executing delete from table where timestamp = ($min_timestamp + (60 * 60)). This would be okay, but it doesn't work -- I get a lock timeout error message after a long wait. While there delete is running, the InnoDB Monitor indicates that the thread running the delete transaction builds up a lot of lock structs (upwards of 700) and a lot of undo log entries (upwards of 35000) before it fails. I should point out that during the delete there are no selects or updates, just insertions. While its running (before it fails), the monitor indicates that there are ~200 inserts/sec and between 0 and 60 deletes/sec (this matches reads/sec .. why?) -- so obviously this isn't going to work. I need to be able to delete at least as fast as I can insert. Maybe relevant : I have ...trx_commit=0 in my.cnf The other problem is that after the delete I need to see if enough has been removed, but the Data_length field seems to fluctuate inconsistently -- is there a better way to get a reading of how much data is used (or how much space is left)? What is the relationship between deleting and inserting? It seems like there is a common semaphore or lock but I don't understand what the relationship is? Thanks, Eric Mayers Software Engineer - 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: Does delete from .. where (condition) use an index?
Heikki, mysql EXPLAIN SELECT * FROM Syslog WHERE datestamp = 2002021310712; ++--+---+--+-+--+-+- ---+ | table | type | possible_keys | key | key_len | ref | rows| Extra | ++--+---+--+-+--+-+- ---+ | Syslog | ALL | ds_index | NULL |NULL | NULL | 2204932 | where used | ++--+---+--+-+--+-+- ---+ 1 row in set (0.00 sec) How can I force it to use the index? (the indexes look like: mysql show index from Syslog; +++--+--+-+- --+-+--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Comment | +++--+--+-+- --+-+--++-+ | Syslog | 1 | ds_index |1 | datestamp | A |5893 | NULL | NULL | | | Syslog | 1 | ca_index |1 | caid| A |9730 | NULL | NULL | | +++--+--+-+- --+-+--++-+ 2 rows in set (6.90 sec) ) Thanks, Eric -Original Message- From: Heikki Tuuri [mailto:[EMAIL PROTECTED]] Sent: Wednesday, February 13, 2002 5:34 PM To: [EMAIL PROTECTED] Subject: Re: Does delete from .. where (condition) use an index? Eric, MySQL is very pessimistic about key accesses: it assumes many of them cause a random disk read. Please print EXPLAIN SELECT * FROM Syslog WHERE datestamp ... to see how it accesses the table. I have tuned the optimization in 3.23.48 so that it would favor index searches more often. Please try also with 3.23.48. Regards, Heikki Innobase Oy Eric Mayers wrote in message ... I haven't been able to find details about how/if MySQL (InnoDB) optimizes deletes. Does it use indexes? Can I force it to use an index? My table is defined as: CREATE TABLE Syslog( id int(11) not null, datestamp timestamp(14), message char(255), KEY ds_index(datestamp), KEY id_index(id) ) type=InnoDB; There are about 15 mil rows, and I'm just trying to delete a small portion of them (0.05% perhaps). My delete looks like: DELETE FROM Syslog WHERE datestamp = 2002021310712; (the magic number there comes from SELECT (min(datestamp) + 30) AS min FROM Syslog in a previous query). .. and its taking a very long time.. watching the innodb monitor I'm seeing a large number of reads/s (29000) and a small number of deletes/s (10) which makes me believe its not using the index.. Any way to improve this? Eric Mayers Software Engineer I - 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: Hello! Date question....
Jim,=20 What you're asking about is the timestamp data type. =20 Look at : http://www.mysql.com/doc/D/A/DATETIME.html In a nutshell, if you assign 'null' to a timestamp column it will put in value of 'NOW()'. Eric Mayers Software Engineer -Original Message- From: Jim Hatridge [mailto:[EMAIL PROTECTED]] Sent: Friday, February 08, 2002 9:50 AM To: [EMAIL PROTECTED] Subject: Hello! Date question =20 Hi all... =20 This is my first post to the list. I've been working on MySQL=20 now for about a=20 week and really like it. I have a question that I can't find=20 in any of the=20 books or how-tos. =20 When I add a line to my customer's table I need it to put=20 today's date in the=20 indate column. Is there anyway to do this like the auto for numbers? - 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: Checking the Table Size
Satish: You can see how much space is used (among other things) with a query like: show table status from database like 'Syslog'; Increasing the maximum table size is a little more complicated and depends on what OS and table type you're using. Eric -Original Message- From: Satish Santhakumar [mailto:[EMAIL PROTECTED]] Sent: Friday, February 08, 2002 2:21 PM To: [EMAIL PROTECTED] Subject: Checking the Table Size =20 =20 Hi guys, =20 I am not able to insert into the MySQL database anymore. I think it has reached the table size. How do i check the size to see if it has reached the table size? Also let me know how to increase the table size in case it is reached the limit. Thanks, Satish =20 =3D=3D=3D=3D=3D The only person in the world who is enviable is one who does=20 not envy others __ Satish Santhakumar Graduate Student IIT, Chicago and Computer Programmer University of Chicago. =20 __ Do You Yahoo!? Send FREE Valentine eCards with Yahoo! Greetings! http://greetings.yahoo.com =20 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) =20 To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail=20 [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php =20 =20 - 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
Static or Dynamic rows -- which is faster?
I have a large data set (15 mil rows) consisting of a datetime column and a char(255) column. I seem to recall seeing something about performance benefits for using static length rows with MyISAM tables, and I've heard some argument for using dynamic length rows (e.g., use varchar rather than char) in InnoDB tables. So for performance sake (assuming no index can be used), which is better for these table types? I can imagine that using dynamic length would be better in the case of an IO bottleneck, but maybe there is an efficiency gain from knowing where the records start and stop ahead of time..? Thanks,=20 Eric uugh. sql. - 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: Clear table contents
Odhiambo, Give this a try, DELETE from relay_ip where UNIX_TIMESTAMP(rei_ts) (UNIX_TIMESTAMP(NOW()) - (10 * 60)); Eric Mayers Software Engineer Captus Networks -Original Message- From: Odhiambo Washington [mailto:[EMAIL PROTECTED]] Sent: Sunday, February 10, 2002 11:22 PM To: [EMAIL PROTECTED] Subject: Clear table contents Hello gurus, Please allow me to request for help with the table below. mysql select * from relay_ip; +-+++- +-+ | rei_aid | rei_uname | rei_domain | rei_ip | rei_ts | +-+++- +-+ | 1 | wash | alligator.wananchi.com | 62.8.64.4 | 2002-02-08 18:17:36 | | 2 | wash | alligator.wananchi.com | 62.8.64.108 | 2002-02-08 20:36:51 | | 3 | wash | alligator.wananchi.com | 62.8.64.108 | 2002-02-08 20:42:27 | | 4 | wash | alligator.wananchi.com | 62.8.64.108 | 2002-02-09 13:03:27 | | 5 | wash | alligator.wananchi.com | 62.8.64.108 | 2002-02-09 13:03:28 | | 6 | [EMAIL PROTECTED] | freebsd.co.ke | 62.8.64.108 | 2002-02-11 09:46:43 | | 7 | [EMAIL PROTECTED] | freebsd.co.ke | 62.8.64.108 | 2002-02-11 09:47:27 | +-+++- +-+ I am looking for a way to clear each record that is older than 10 minutes by referring to the timestamp. Something that I can run from cron. tia -Wash S y s t e m s A d m i n. -- Odhiambo Washington [EMAIL PROTECTED]The box said 'Requires Wananchi Online Ltd. www.wananchi.com Windows 95, NT, or better,' Tel: 254 2 313985-9 Fax: 254 2 313922 so I installed FreeBSD. GSM: 254 72 743 223 GSM: 254 733 744 121 This sig is McQ! :-) ++ No problem is so formidable that you can't just walk away from it. - 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 - 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: Hello! Date question....
Jim, What you're asking about is the timestamp data type. Look at : http://www.mysql.com/doc/D/A/DATETIME.html In a nutshell, if you assign 'null' to a timestamp column it will put in value of 'NOW()'. Eric Mayers Software Engineer -Original Message- From: Jim Hatridge [mailto:[EMAIL PROTECTED]] Sent: Friday, February 08, 2002 9:50 AM To: [EMAIL PROTECTED] Subject: Hello! Date question Hi all... This is my first post to the list. I've been working on MySQL now for about a week and really like it. I have a question that I can't find in any of the books or how-tos. When I add a line to my customer's table I need it to put today's date in the indate column. Is there anyway to do this like the auto for numbers? - 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: MySQL won't start...
Joe, When you start mysql (presumably with safe_mysqld), you must tell it where your datafiles are. Try bin/safe_mysqld --datadir=/var/lib/mysql/mysql Eric -Original Message- From: FISHER,JOE (Non-HP-Roseville,ex1) [mailto:[EMAIL PROTECTED]] Sent: Friday, February 08, 2002 11:01 AM To: '[EMAIL PROTECTED]' Subject: MySQL won't start... I have 2 SuSE Linux 7.2 machines at home... These boxes have completely different hardware in each... But the O/S on each, contains virtually all of the same applications... On the first machine, MySQL started right up, and has been working correctly ever since... On the second machine, MySQL will NOT start... It tries to start, but keeps aborting... Here are the only messages that I can seem to find: 020126 18:01:29 mysqld started 020126 18:01:29 /usr/sbin/mysqld: Table 'mysql.host' doesn't exist 020126 18:01:29 mysqld ended 020126 18:04:56 mysqld started 020126 18:04:57 /usr/sbin/mysqld: Can't find file: './mysql/host.frm' (errno: 13) 020126 18:04:57 mysqld ended 020126 18:07:31 mysqld started 020126 18:07:31 /usr/sbin/mysqld: Can't find file: './mysql/host.frm' (errno: 13) Each time I try to restart, I get the above messages... If anyone can point me to some specific log files, that would help to debug this problem further, I would much appreciate it... my.cnf is locate in /etc, as it's supposed to be... host.frm exists, but it exists in the following location: (I'm thinking that something in /usr/sbin/mysqld isn't pointing to the file...) /var/lib/mysql/mysql/host.frm Is there a configuration file someplace, that I can modify to point to this host.frm file? If so, what is the correct command, including syntax? TIA Joe F. - 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: Checking the Table Size
Satish: You can see how much space is used (among other things) with a query like: show table status from database like 'Syslog'; Increasing the maximum table size is a little more complicated and depends on what OS and table type you're using. Eric -Original Message- From: Satish Santhakumar [mailto:[EMAIL PROTECTED]] Sent: Friday, February 08, 2002 2:21 PM To: [EMAIL PROTECTED] Subject: Checking the Table Size Hi guys, I am not able to insert into the MySQL database anymore. I think it has reached the table size. How do i check the size to see if it has reached the table size? Also let me know how to increase the table size in case it is reached the limit. Thanks, Satish = The only person in the world who is enviable is one who does not envy others __ Satish Santhakumar Graduate Student IIT, Chicago and Computer Programmer University of Chicago. __ Do You Yahoo!? Send FREE Valentine eCards with Yahoo! Greetings! http://greetings.yahoo.com - 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
increasing InnoDB search performance
I have an application that stores a large (up to 6gb) database in an InnoDB table. I'm using an InnoDB table to have concurrent inserts and selects and to get beyond the 2gb limitation. I have to allow read-only remote database connections and cannot enforce that users use any kind of special query to access the database (e.g., implementing my own fulltext-like index and using the index at an application layer). The content consists of log entries which are each under 255 characters long. I'd like to know what I can do to improve the response time of the database under these restrictions. So I have a few questions: Would adding ram help? I can't imagine that an operation that requires searching 6gb (a disk bandwidth or processing bottleneck problem) would be improved much by adding ram.. ? Can I do anything (short of hardware changes) to increase performance? And long term question: I've noticed that a fulltext index feature doesn't appear on the InnoDB todo list. From my (limited) research, it looks like this is a feature a lot of people would find very useful. Is this a long term goal, or has it been excluded for some reason? Thanks Eric Mayers Software Engineer - 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
Starting MySQL (w/ InnoDB) for the firs time
Hi Everyone, I'm using MySQL with 5 2gb InnoDB data files in a system that an end user will install. My problem is that the first time InnoDB starts up after installation it takes a long time for it to build its data files. The delay is annoying (any ways to make this faster?) but acceptable, but the problem is that during system startup I start the server backgrounded, sleep a few seconds and start a daemon that relies on the database being ready. When its starting the first time the server isn't there to connect to and the daemon flops. So finally getting to my question: How can I tell when the database is ready to go (preferably from a shell script)? And an unrelated question, is there a guide to setting up a minimal MySQL installation? Thanks, Eric Mayers Software Engineer Captus Networks - 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
Overwrite oldest data?
I'm designing a system that will parse and store log files in a MySQL-Max-2.23 database (the log table will be innodb). I have a requirement that as the database fills towards maximum capacity that it writes over the oldest records rather than dropping new records. I'd like to know if this is something that can be handled by the database itself? Otherwise it will be up to the application. Has anyone written code to do this? Is it best to delete a chunk of old records when its full then continue inserting, or would something like: UPDATE logs SET col=data, date=newdate ORDER BY date DESC LIMIT 1 be better once the table was at capacity? (would this work at all?) Thanks, Eric Mayers Software Engineer Captus Networks - 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
Details of table read locking?
In a query like: SELECT * from log_data where log_data.id = other_table.id; Will the database lock both the log_data and assets tables, or just the log_data table? More generally, are locks applied to any table related to the query, or just data being selected? Thanks, Eric Mayers - 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
Inserts during a long select?
I have a large amount of date that I need to select from in order to generate reports (might take 20 minutes). I also have data continuously being inserted.. I don't mind if the select results doesn't contain the recently added rows (or it can -- I don't care). I've searched the archive and found some promising leads.. (relevant snippets quoted below) There is one expection, later 3.23 versions under special conditions can insert and select concurrently, but this is done in such a way as to avoid dirty reads. and Note that this patch will allow you to have concurrent inserts as selects are running, but only if there is no holes after deleted rows in the able. In this case the insert changes to a normal 'locked' insert until all holes are filled. I have alread updated the online manual about this! I just can't figure out what the syntax is (or patch I need to apply) in order to do this. Any help in this area would be appreciated. Thanks, Eric Mayers database (spam filter got me the first time) - 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
Table size limitations: Error Handling and Raid 0
My question is two-fold, first regarding error handing of table size limitations, and secondly performance and other implications of raid striping. I'm building a system with an integrated MySQL database and there is a potential for filling up tables to the maximum file size for the OS I'm using (linux 2.2.x kernel, ext2 fs, 2gb max file size). I'd like to provide users with a graceful error-handling mechanism, essentially telling them: The database is full. You must remove stuff before you can add more. Along with that, a % used number would be nice. Is there a method (through a mysql query) of determining how much space the database is taking up? It seems like the alternative is looking directly at the file system's record. Secondly, to get past the 2gb limit I'm considering using Raid Striping on a single partition. I'm curious if people have done this and what the performance implications are (how much worse is 2, 3, 10, 20 files than one file), and does doing this successfully avoid the 2gb limit (or is there some other limiting factor?) What is the next limiting factor beyond the file system's 2gb limit given unlimited storage? [that one is just curiosity] Thanks, Eric Mayers Software Engineer Captus Networks - 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
Combine data from multiple tables
I have three tables, each with a single column, data. I'd like to form a query that will combine the table columns into a common result column. When I use the query: SELECT * from tab1, tab2, tab3 I get: +-++---+ | data| data | data | +-++---+ | VAL1| VAL2 | VAL3 | +-++---+ But what I want is: +-+ | data| +-+ | VAL1| | VAL2| | VAL3| +-+ Is this possible? BTW, I'm using MySQL 3.23.35 Thanks, Eric - 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
Best text query implementation?
Hi Everbody, I'd like to know how people are forming text searches with MySQL. I'd like to be able to support a query something like: (login AND root) OR (invalid login AND tty*) a code snippet that could turn a query like this into a MySQL Regular expression or other query of some kind would be ideal. I'm not able to use MySQL 4.0.1 FULLTEXT binary mode searches because I can't use alpha code at this point. Thanks, Eric Mayers Software Engineer Captus Networks - 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: 2 GB limit reached
Dennis, You may want to look into using InnoDB tables. I believe InnoDB tables are immune to the 2gb limit (which usually comes from the filesystem). Also, InnoDB claims that the innodb tables are faster than MyISAM tables in some cases. See www.innodb.com or http://www.mysql.com/doc/I/n/InnoDB_overview.html for further detail. Eric Mayers -Original Message- From: Dennis [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 08, 2002 3:31 PM To: [EMAIL PROTECTED] Subject: RE: 2 GB limit reached We have a database that seems to have grown too large, and now any operation fails on it. How can we fix this? Dennis - 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: duplicating collumn
You'll likely want to use INSERT INTO .. WHERE statement to populate a new third table based on the content of the two you've mentioned, joining on the ID. Look at http://www.mysql.com/doc/I/N/INSERT_SELECT.html for details on the syntax and usage. Eric Mayers -Original Message- From: P.Agenbag [mailto:[EMAIL PROTECTED]] Sent: Thursday, January 03, 2002 2:58 PM To: mysql Subject: duplicating collumn Hi I have two tables, one contains a persons name and an ID number. The other table contains the ID number and another field. Is there a way of moving this collumn to the first table to corrlate with the ID's, ie, table 1 must now contains name, ID and another field that is specific to the person. The two tables are not chronological ie. the 1st entry in the one table is not nescessarily the first in the second table... - 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
Need to mask out data
The following is a boiled down version of what I want to provide. I'd appreciate any ideas. I believe views would be idea, but as mysql doesn't have views I'm looking for a work-around. I have a table that consists of an id and a text field like so: ID TEXT 2 sample text for group 2 2 more sample text for group 2 3 sample text for group 3 3 more sample text for group 3 I have another table listing usernames and groups they have access to like so: USERNAMEGROUPS bob 2 frank 3 cindy 2,3 roger I want to be able to provide each user access to only the groups specified for him/her. The appropriate data needs to be available to each user connecting to the database remotely. Most likely users would use myodbc on a windows box to access their data (it need only be readable). I imagine views would be ideal to solve this problem: I would only give each user access to a view, and it would form the visible data. The only thing I've thought of that can simulate this is to form a separate table (or database) for each user, and when the data table is updated, each users table is updated as well, as appropriate. This seems like an awful solution, especially as the number of users grows. Any suggestions? Eric Mayers [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
