Re: Subquery error
Short-answer: use IN instead of = Long-answer: Your query is kind of weird. I think you want to use IN: SELECT memberid, fullname FROM members WHERE memberid IN (select distinct memberid FROM familymembers) The equals implies an exact match between the top-level, and the sub-query, but I am guessing your sub-query will return more than one row, and that's where your problem lies. Oracle won't let you do it (I haven't worked with anything other than 4.0 in MySQL, so I can't say regarding 4.1) - complains that a single-row-subquery returns more than one row. If you do this in Oracle: select * from listing_status where listing_status_id = (select listing_status_id from listing_status where rownum 2) it will work, as the sub-query returns exactly one row. Not sure how MySQL would handle it if you put a LIMIT on it. But I don't think that's what you are trying to do. (also) David Oracle, I believe, will David David Kagiri wrote: i get an error when i run the query below though SQLyog. SELECT memberid,fullname FROM members WHERE memberid = (select distinct memberid FROM familymembers) the error is Error Code : 1064 You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'select distinct memberid FROM familymembers)' at line 2 (60 ms taken) i use MySQL version is 4.1.7 dosent it support subqueries? __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: AES Encryption
From: Martin For my tests I used the blob field (not varchar or anything else), but compared to your tests, I didn't use MySQL 5.0.6 (as it's still a beta) but I used MySQL 4.1. Maybe that's part of the problem, although I'm not sure. FWIW: I tested it on 4.0.23-standard, with the same results as Gleb found... So it's really quite strange, but so far I solved it by limiting the number of characters by 256 in my application, but ofcourse that's not ideal... 1) Did you try to directly use the output of the aes_encrypt function as input for the aes_decrypt function? 2) You didn't happen to use a TINYBLOB field? (Which of course is limited to storing 255 bytes) Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Does the kernel options MAXSSIZ affect the MySQL max connections on FreeBSD
hi,everyone, I just did a test to find out the MySQL4.1.12 max connections on FreeBSD 5.4 Release and FreeBSD 4.11 Release. The first test, the MAXSSIZ is default(64MB), and MAXSDIZ is 1GB because I have 1GB memory. The second test, I setted the MAXSSIZ=1GB, and the MAXDSIZ is also 1GB. The two test show that the MySQL max connections is the same. The max connections is about 1000. Have anyone kown how to tune the FreeBSD and MySQL to increase the max connections? Best regards, leo huang [EMAIL PROTECTED] 2005-06-17 _ MSN Messenger: http://messenger.msn.com/cn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Does the kernel options MAXSSIZ affect the MySQL max connections on FreeBSD
huang leo : hi,everyone, I just did a test to find out the MySQL4.1.12 max connections on FreeBSD 5.4 Release and FreeBSD 4.11 Release. The first test, the MAXSSIZ is default(64MB), and MAXSDIZ is 1GB because I have 1GB memory. The second test, I setted the MAXSSIZ=1GB, and the MAXDSIZ is also 1GB. The two test show that the MySQL max connections is the same. The max connections is about 1000. Have anyone kown how to tune the FreeBSD and MySQL to increase the max connections? Best regards, leo huang [EMAIL PROTECTED] 2005-06-17 _ MSN Messenger: http://messenger.msn.com/cn mysql show variables like '%max%'; +-+--+ | Variable_name | Value | +-+--+ | bdb_max_lock | 1 | | ft_max_word_len | 84 | | group_concat_max_len | 1024 | | innodb_max_dirty_pages_pct | 90 | | innodb_max_purge_lag | 0 | | max_allowed_packet | 1048576 | | max_binlog_cache_size | 4294967295 | | max_binlog_size | 1073741824 | | max_connect_errors | 10 | | max_connections | 100 | | max_delayed_threads | 20 | | max_error_count | 64 | | max_heap_table_size | 16777216 | | max_insert_delayed_threads | 20 | | max_join_size | 18446744073709551615 | | max_length_for_sort_data | 1024 | | max_relay_log_size | 0 | | max_seeks_for_key | 4294967295 | | max_sort_length | 1024 | | max_tmp_tables | 32 | | max_user_connections | 0 | | max_write_lock_count | 4294967295 | | myisam_max_extra_sort_file_size | 2147483648 | | myisam_max_sort_file_size | 2147483647 | +-+--+ 24 rows in set (0.00 sec) You can see max_connections=100. Change that variable to get max connections you want. Regards, Gu Lei -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysqlhotcopy.
Hello. See: http://dev.mysql.com/doc/mysql/en/problems-with-character-sets.html Miguel Burgos O. wrote: I install the last version of mysql and got this $mysqlhotcopy test File '/usr/share/mysql/charsets/?.conf' not found (Errcode: 2) What happened ? Best regards, Miguel --- E-mail: [EMAIL PROTECTED] . ADVERTENCIA: La informacion contenida en esta transmision, y en cualquier archivo adjunto, es confidencial y no puede ser usada o difundida por personas distintas de su o sus destinatarios. Si usted ha recibido esta transmision por error, por favor notifique inmediatamente al remitente respondiendo por este mismo medio y eliminela de su sistema. DICTUC no se hara responsable de la exactitud y veracidad de la informacion contenida en este mensaje, asi como de su modificacion, copia, divulgacion o reenvio, total o parcial. Su uso no autorizado puede ser sancionado de conformidad con la legislacion nacional. DICTUC transmite sus informaciones oficiales a traves de su pagina en internet: http://www.dictuc.cl -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How can I make permanent timeout variable settings???
Hello. Putting this variables into configuration file works for me. Have you tried slave_net_timeout? Please send the output of SHOW SLAVE STATUS statement. [EMAIL PROTECTED] wrote: Hallo I have some problems loading data from Master. I am using the circular replication procedure for example a-b-c-a. Since I ran out of disk in my main server, my other servers started reporting duplicate entries later. I thought going it from scratch to take the complete data from the Master server to the slave machines. I used the following command. LOAD DATA FROM MASTER after clearing the data folders in the slave machines. But if shows me a timeout error. I then tired to increase the net_read_timeout, net_write_timeout and the wait_timeout. But this doesn't seem to work since I get the same error's. And the settings that I made has not been assigned permanently. When I tried for Show Variables. It shows me the default settings. How can I make my changes permanent until I move my Data from the Master to the Slave machine. Thanks for your suggestions in advance, please. Kind Regards Raj -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: AMD64 Linux MySQL Error 1041 Out of Memory
Hello. MySQL on 64-bit AMD rises lots of questions. There is an Opteron HOWTO at: http://hashmysql.org/index.php?title=Opteron_HOWTO I can't give any advice except sending to the list the output of SHOW STATUS and SHOW PROCESSLIST statements performed when MySQL uses a lot of memory. If you able check your test on MySQL 4.1.12. Joe Kislo [EMAIL PROTECTED] wrote: So we have recently started stress testing Mysql on an Opteron dual CPU machine running Ubuntu Hoary. We are using the 64-bit GCC 4.0.24-standard binary from mysql. The stress test that I'm currently running on it involves inserting a large database (from a mysqldump) from three separate windows (so three imports running simultaneously). The database dump is about 3.7 gigs uncompressed, or 580megs compressed. It was dumped using the following dump parameters: --add-locks --extended-insert --quick --lock-tables --all --disable-keys Each window has as script that creates a database, imports the data, dumps the database, and repeats. After about 12 cycles (each take about an hour) mysql starts spewing these errors: ERROR 1041 at line 195: Out of memory; Check if mysqld or some other process uses all available memory. If not you may have to use 'ulimit' to allow mysqld to use more memory or you can add more swap space I, unfortunately, have not been at the server when this actually happens, however when I come in in the morning top is reporting mysqld taking up between 2.7 gigs and 3.2 gigs of memory. I have had a vmstat running all night, and at no point saw the system run out of swap space (it did over the course of the 15 hours or so, slowly hit swap up for about 60megs out of 2 gigs though). Obviously checking ulimit was my first stop, however I believe MySQLd does it's own setuid... And I'm not sure it uses PAM to get it's initial ulimits. Either way, I do this: su mysql -s /bin/sh sh-3.00$ ulimit -a core file size(blocks, -c) 0 data seg size (kbytes, -d) unlimited file size (blocks, -f) unlimited max locked memory (kbytes, -l) unlimited max memory size (kbytes, -m) unlimited open files(-n) 8192 pipe size (512 bytes, -p) 8 stack size(kbytes, -s) 8192 cpu time (seconds, -t) unlimited max user processes(-u) unlimited virtual memory(kbytes, -v) unlimited Which implies it should be able to alloc quite a bit of memory without problem. So my first question, is it appears to be konking out around 4 gigs of memory. Is there some reason why mysqld can't allocate more than 4gigs? I confirmed I *am* running the 64-bit binary: file /usr/sbin/mysqld /usr/sbin/mysqld: ELF 64-bit LSB executable, AMD x86-64, version 1 (SYSV), for GNU/Linux 2.4.0, dynamically linked (uses shared libs), stripped This system has 4 gigs of memory in it. So if it tried to allocate 4gigs, it would have had to hit swap up harder than 60megs. It seems like mysqld is hitting the 32bit allocation limit, but that doesn't particularly make sense to me. Anybody have their mysqld allocating 4gigs? Anything else I can try here? The second thing is, I have no idea why mysql is taking up so much memory. For the first 5 runs or so, mysql only allocates about 800 megs. Sometime during the night, is when it jumps up in memory.. I don't really understand why if it didn't need 3+ gigs of memory after the first 5 complete runs (x3 of course... since there's 3 running in parallel), it would suddenly need more later. Either way, lets do some math. Mysql is 2.7 gigs this morning, which is about half a gig less than yesterday morning. 2.7 gigs Key buffer: 512m Tmp Table: 128m sort buffer size: 512m join buffer size: 512m query cache: 256m KeyBuffer=512m, I could see that possibly not being returned.. So lets assume 512M there. There are no threads connected at the moment because I have shutdown the test, so tmptable should take up 0, but lets say it didn't return 3x128M (384M). Sort buffer size is 512M, well it may have used that for the alter table activate keys... and never returned it, so 512M there. Join buffer size, not a single select query was used ever, 0M. Query cache, 0M. 3x16M max packet. So I see 512M+384+512M+48M=1.4gigs. I have no idea why mysql is using this much memory... especially after it successfully performs 5 cycles with considerably less. Any ideas? here's the my.cnf [mysqld] user= mysql pid-file= /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port= 3306 basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp language= /usr/share/mysql/english skip-external-locking key_buffer =
Concorrent users
Hi all, just want to know if there is a specific number of concorent users dat can query from a mysql databasee at d same time. - A passion till tomorrow, Opeyemi Obembe | ng.clawz.com - Yahoo! Sports Rekindle the Rivalries. Sign up for Fantasy Football
Re: Concorrent users
'Yemi Obembe [EMAIL PROTECTED] wrote on 17/06/2005 09:40:39: Hi all, just want to know if there is a specific number of concorent users dat can query from a mysql databasee at d same time. MySQL has a configurable limit to the number of simultaneous connections that it can support. See http://dev.mysql.com/doc/mysql/en/server-system-variables.html This can be increased provided you have the system resources to handle the increased number of connections. On a finer grain, MySQL will interleave simultaneous queries, subject to table locking, as they pause requiring disk access. However, once they are performing memory-locked operations, a single query will lock a CPU. On multi-CPU machines, it will generally run queries in parallel on the separate CPUs. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Triggers
Hi,
the syntax for creating a triggers is :
CREATE TRIGGER trigger_name trigger_time trigger_event
ON tbl_name FOR EACH ROW trigger_stmt
Is there any other possibilities than FOR EACH ROW ?
If I do :
CREATE TABLE test(foo varchar(5));
CREATE TRIGGER trigger_name AFTER INSERT
ON test FOR EACH ROW do_something;
INSERT INTO test(foo) VALUES ('a'),('b'),('c');
it would trigger trigger_name for the 3 inserts, right ? Any chance to
trigger trigger_name only once at the end ???
--
Philippe Poelvoorde
COS Trading Ltd.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Triggers
yes there is :
http://www.postgresql.org/docs/7.4/interactive/sql-createtrigger.html
FOR EACH ROW
FOR EACH STATEMENT
This specifies whether the trigger procedure should be fired once
for every row affected by the trigger event, or just once per SQL
statement. If neither is specified, FOR EACH STATEMENT is the default.
Philippe Poelvoorde wrote:
Hi,
the syntax for creating a triggers is :
CREATE TRIGGER trigger_name trigger_time trigger_event
ON tbl_name FOR EACH ROW trigger_stmt
Is there any other possibilities than FOR EACH ROW ?
If I do :
CREATE TABLE test(foo varchar(5));
CREATE TRIGGER trigger_name AFTER INSERT
ON test FOR EACH ROW do_something;
INSERT INTO test(foo) VALUES ('a'),('b'),('c');
it would trigger trigger_name for the 3 inserts, right ? Any chance to
trigger trigger_name only once at the end ???
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Triggers
If that works for MySQL, the official documentation doesn't show it:
http://dev.mysql.com/doc/mysql/en/create-trigger.html
I don't have a copy of the 5.0+ source or I could check the
sql/sql_yacc.yy file to see if the syntax is supported. Anyone else able
to help out here?
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Ruben Oliveira [EMAIL PROTECTED] wrote on 06/17/2005 09:14:48 AM:
yes there is :
http://www.postgresql.org/docs/7.4/interactive/sql-createtrigger.html
FOR EACH ROW
FOR EACH STATEMENT
This specifies whether the trigger procedure should be fired once
for every row affected by the trigger event, or just once per SQL
statement. If neither is specified, FOR EACH STATEMENT is the default.
Philippe Poelvoorde wrote:
Hi,
the syntax for creating a triggers is :
CREATE TRIGGER trigger_name trigger_time trigger_event
ON tbl_name FOR EACH ROW trigger_stmt
Is there any other possibilities than FOR EACH ROW ?
If I do :
CREATE TABLE test(foo varchar(5));
CREATE TRIGGER trigger_name AFTER INSERT
ON test FOR EACH ROW do_something;
INSERT INTO test(foo) VALUES ('a'),('b'),('c');
it would trigger trigger_name for the 3 inserts, right ? Any chance to
trigger trigger_name only once at the end ???
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
InnoDB: Space issues
Hi, One of our servers has a 30GB+ ibdata1 file. We just switched on per table tablespaces and have begun moving tables out into their own files. But I still need to reduce the 30GB+ down to a more reasonable level to free up disk space. This is essential. The only references I can find for this are to use mysqldump to reload the tables. This isn't an option for us due to the size of the dataset and it's 24/7 availability, hence we bought innodb hot backup. And thus we are looking for suggestions. Any advice appreciated. Thanks, James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Wrf files: how can I read them?
Hello, I have found Mysql Performance Tuning Seminar available for download, but I don't know how to see it. I have found something like Webex website, but I don't see any software to download there. Can you help me please? Why is there nothing specified about that type of file on the download page of the seminar? Thank you very much. Asteddy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Wrf files: how can I read them?
Hi, there is link take the free trial on webex site. haven't you seen the seconf button ? Mathias Selon asteddy [EMAIL PROTECTED]: Hello, I have found Mysql Performance Tuning Seminar available for download, but I don't know how to see it. I have found something like Webex website, but I don't see any software to download there. Can you help me please? Why is there nothing specified about that type of file on the download page of the seminar? Thank you very much. Asteddy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Triggers
Hello.
SQL 99 allows action Granularity FOR EACH STATEMENT, and it is default.
But FOR EACH ROW is more common and it seems MySQL supports only second.
When Granualarity FOR EACH STATEMENT trigger action occurs only once, when
FOR EACH ROW - once for each row (3 times in your case).
Philippe Poelvoorde [EMAIL PROTECTED] wrote:
Hi,
the syntax for creating a triggers is :
CREATE TRIGGER trigger_name trigger_time trigger_event
ON tbl_name FOR EACH ROW trigger_stmt
Is there any other possibilities than FOR EACH ROW ?
If I do :
CREATE TABLE test(foo varchar(5));
CREATE TRIGGER trigger_name AFTER INSERT
ON test FOR EACH ROW do_something;
INSERT INTO test(foo) VALUES ('a'),('b'),('c');
it would trigger trigger_name for the 3 inserts, right ? Any chance to
trigger trigger_name only once at the end ???
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Backup database with MyISAM and InnoDB tables together
I'm not clear on best practice to use on a database containing both MyISAM and InnoDB tables. For the MyISAM tables, it seems better to use mysqldump --opt, thus getting the --lock-tables option, but for the InnoDB the --single-transaction is preferred. Since they are mutually exclusive, is there a best practice to get consistent state of the tables when the database dump is performed? Would `mysqldump --opt --skip-lock-tables --single-transaction` be best for a database that is mostly InnoDB tables, but does have a few MyISAM tables? WOuld I be better off locking the database from any updates/inserts, and specifying particular commands for individual tables? Any advice appreciated, including RTFMs with links. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Re: Wrf files: how can I read them?
Thank you, but why has mysql made seminars wich must be seen with a non-free software with a 14 days trial? Is there nothing else to see it? Asteddy Hi, there is link take the free trial on webex site. haven't you seen the seconf button ? Mathias Selon asteddy [EMAIL PROTECTED]: Hello, I have found Mysql Performance Tuning Seminar available for download, but I don't know how to see it. I have found something like Webex website, but I don't see any software to download there. Can you help me please? Why is there nothing specified about that type of file on the download page of the seminar? Thank you very much. Asteddy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Re: Wrf files: how can I read them?
Mysql seminar organizer's should answer. Mathias Selon asteddy [EMAIL PROTECTED]: Thank you, but why has mysql made seminars wich must be seen with a non-free software with a 14 days trial? Is there nothing else to see it? Asteddy Hi, there is link take the free trial on webex site. haven't you seen the seconf button ? Mathias Selon asteddy [EMAIL PROTECTED]: Hello, I have found Mysql Performance Tuning Seminar available for download, but I don't know how to see it. I have found something like Webex website, but I don't see any software to download there. Can you help me please? Why is there nothing specified about that type of file on the download page of the seminar? Thank you very much. Asteddy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Backup database with MyISAM and InnoDB tables together
If you are runing binary log and do a FLUSH LOGS mysqldump --opt --skip-lock-tables MyISAM table names FLUSH LOGS mysqldump --opt --single-transaction INNODB table names You have a recoverable state with the combination of the mysqldump file and the binary log file that was started by the 1st FLUSH LOGS command. The recovered database wil be restored to the date time that the 2nd FLUSH LOGS command was issued instead of the start time of the backup, but you won't have to lock all of your tables and it wil give you a consistent state across a mixed INNODB MyISAM environment. The downside is -you have 3 files to deal with -you have to maintain the table names in the mysqldump commands -you have a small risk of a change ocurring in the MyISAM tables between time the 2nd FLUSH LOGS is executed and the 2nd mysqldump command is executed -Original Message- From: Scott Plumlee [mailto:[EMAIL PROTECTED] Sent: Friday, June 17, 2005 10:21 AM To: mysql@lists.mysql.com Subject: Backup database with MyISAM and InnoDB tables together I'm not clear on best practice to use on a database containing both MyISAM and InnoDB tables. For the MyISAM tables, it seems better to use mysqldump --opt, thus getting the --lock-tables option, but for the InnoDB the --single-transaction is preferred. Since they are mutually exclusive, is there a best practice to get consistent state of the tables when the database dump is performed? Would `mysqldump --opt --skip-lock-tables --single-transaction` be best for a database that is mostly InnoDB tables, but does have a few MyISAM tables? WOuld I be better off locking the database from any updates/inserts, and specifying particular commands for individual tables? Any advice appreciated, including RTFMs with links. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
INSERT statements with columns that don't exist
Hi, I was wondering if there's a way to make mysql not care about (or ignore) columns in an insert statement that don't exist in the table. For example, I have a dump from a table with 10 columns, but the new table has one of the columns dropped.. well restoring from the dump causes errors.. is there a way to ignore the unknown columns? This is something that's going to happen programatically and from time to time. I know in the above example I can create the columns, then drop them.. but I was looking for a simpler solution if there is one. Thanks, Davy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
problem installing 5.7 on alpha
Can anyone help, please, when I tar -xvzf this file I am getting the error Archive contains obsolescent base-64 headers gzip: stdin: invalid compressed data--crc error and installation exits I tried to acquire the Compaq compilers mentioned under platforms in case it is a gcc problem, but the page is history. -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.323 / Virus Database: 267.7.3/15 - Release Date: 14/06/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
InnoDB: Error: tablespace
Hi list, I have the following error in a MySQL server: 050617 09:16:35 mysqld started InnoDB: Error: tablespace size stored in header is 8660992 pages, but InnoDB: the sum of data file sizes is only 6432768 pages 050617 9:16:40 InnoDB: Started /usr/sbin/mysqld-max: ready for connections And here is the background: This server is setting up as a slave server, We configured it via master's full backup, so we copied all the directories of the data bases, ibdata files and its config file almost is equals to the master config file the only differences are the ibdata_path. When the server starts, displays the previous message. I checked the size of the master's ibdata files versus the slave's and both files are the same size. I did a count(*) from all the tables, check table of all the tables and it seems that all the tables are fine. Any suggestions to correct the slave message error. Thanks in advanced and greetings -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Wrf files: how can I read them?
On 6/17/05, asteddy [EMAIL PROTECTED] wrote: Hello, I have found Mysql Performance Tuning Seminar available for download, but I don't know how to see it. Use the WebEx player at http://www.meetingcenter.net/record_play.htm -JP -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Subselect in an Update query
Can anyone tell me how I can make this work or suggest a work around? Update table1 Set field1=(Select field1 From table1 Where field2=Some Value) Where field2 =Another Value; Thanks
Re: Backup database with MyISAM and InnoDB tables together
-Original Message- From: Scott Plumlee [mailto:[EMAIL PROTECTED] Sent: Friday, June 17, 2005 10:21 AM To: mysql@lists.mysql.com Subject: Backup database with MyISAM and InnoDB tables together I'm not clear on best practice to use on a database containing both MyISAM and InnoDB tables. For the MyISAM tables, it seems better to use mysqldump --opt, thus getting the --lock-tables option, but for the InnoDB the --single-transaction is preferred. Since they are mutually exclusive, is there a best practice to get consistent state of the tables when the database dump is performed? Would `mysqldump --opt --skip-lock-tables --single-transaction` be best for a database that is mostly InnoDB tables, but does have a few MyISAM tables? WOuld I be better off locking the database from any updates/inserts, and specifying particular commands for individual tables? Any advice appreciated, including RTFMs with links. Gordon Bruce wrote: If you are runing binary log and do a FLUSH LOGS mysqldump --opt --skip-lock-tables MyISAM table names FLUSH LOGS mysqldump --opt --single-transaction INNODB table names You have a recoverable state with the combination of the mysqldump file and the binary log file that was started by the 1st FLUSH LOGS command. The recovered database wil be restored to the date time that the 2nd FLUSH LOGS command was issued instead of the start time of the backup, but you won't have to lock all of your tables and it wil give you a consistent state across a mixed INNODB MyISAM environment. The downside is -you have 3 files to deal with -you have to maintain the table names in the mysqldump commands -you have a small risk of a change ocurring in the MyISAM tables between time the 2nd FLUSH LOGS is executed and the 2nd mysqldump command is executed Thanks for the tip. I haven't looked into binary logs too much, just learned about them the other day when I had to correct my own mistake and restore a table. Is is best practice to go with tables of all one sort to allow for consistent state when doing backups like this, or are mixed tables the norm in most databases? I went with the InnoDB in order to not have to do row level locking on the tables as transactions were performed and I've been very pleased with the results. I might consider just going with all InnoDB to make it easy, as those are the majority of my tables in this case. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Subselect in an Update query
Ed Reed [EMAIL PROTECTED] wrote on 06/17/2005 01:35:40 PM: Can anyone tell me how I can make this work or suggest a work around? Update table1 Set field1=(Select field1 From table1 Where field2=Some Value) Where field2 =Another Value; Thanks OK, your example must be broken (or it wouldn't be here) and since that's all you posted I have a hard time divining your intent. Would you please provide some sample data and explain what it is you want to do _in words_? I can't speak for everyone but I know it would sure help me figure out what you want to do. Thanks! Shawn Green Database Administrator Unimin Corporation - Spruce Pine
RE: Backup database with MyISAM and InnoDB tables together
There are 3 things [that are exclusivly MyISAM}.
Full Text index
autoincrement column as the last column in a multi column primary key
MERGE tables
Tables which don't require these features can be INNODB tables. We have
a few tables that use these, otherwise we are exclusively INNODB.
There is another way to do backups. It's what we use.
Capture the file names in your database
Extract the .frm files and build select into outfile and coresponding
load data infile statements for each file name {i.e.table}
Sample
select * into outfile '/usr/data/mailprint/day/user.txt' from
mysql.user;
load data infile 'user.txt' ignore into table user;
put the select statements in a file with flush logs before and after the
set of select statements
run the file through a CRON as mysql -h ... filename
Now we have a text file for each table in a directory which we can
zip/tar, move to a different machine snd selectively restore the tables
via the load data commands, restore a single table in a test database
and recover/rebuild a specific tabel in the live database, etc.
Our 4GB database takes 5 minutes to save every night. A full restore
takes 30 minutes including moving the data files to the right place. We
keep 1 month of the nightly copies and day 1 of each month for a year.
We have development, stage and live servers and started doing this to
give us better granularity for selectively synching tables or parts of
tables. {I need these 500 rows from this table to move the dev project
to staging}. It has also been invaluable in the time when a developer
was on the wrong server and inadvertantly corupted an entire column of
the user table. We did not want to take down the site and do a
restore/rollforward to right before the stupid command was executed.
We just needed to fix the data in this one column in one table.
Sorry I started to ramble.
Oh I almost forgot, we also periodically dump the structure with
mysqldump --no-datato capture the structure.
Our table defintitions are relatively stable so we don't do it every
night. You could put it in the cron job to do it with the backup.
-Original Message-
From: Scott Plumlee [mailto:[EMAIL PROTECTED]
Sent: Friday, June 17, 2005 12:36 PM
To: mysql@lists.mysql.com
Subject: Re: Backup database with MyISAM and InnoDB tables together
-Original Message-
From: Scott Plumlee [mailto:[EMAIL PROTECTED]
Sent: Friday, June 17, 2005 10:21 AM
To: mysql@lists.mysql.com
Subject: Backup database with MyISAM and InnoDB tables together
I'm not clear on best practice to use on a database containing both
MyISAM and InnoDB tables. For the MyISAM tables, it seems better to
use
mysqldump --opt, thus getting the --lock-tables option, but for the
InnoDB the --single-transaction is preferred. Since they are
mutually
exclusive, is there a best practice to get consistent state of the
tables when the database dump is performed?
Would `mysqldump --opt --skip-lock-tables --single-transaction` be
best
for a database that is mostly InnoDB tables, but does have a few
MyISAM
tables?
WOuld I be better off locking the database from any updates/inserts,
and
specifying particular commands for individual tables?
Any advice appreciated, including RTFMs with links.
Gordon Bruce wrote:
If you are runing binary log and do a
FLUSH LOGS
mysqldump --opt --skip-lock-tables MyISAM table names
FLUSH LOGS
mysqldump --opt --single-transaction INNODB table names
You have a recoverable state with the combination of the mysqldump
file
and the binary log file that was started by the 1st FLUSH LOGS
command.
The recovered database wil be restored to the date time that the 2nd
FLUSH LOGS command was issued instead of the start time of the backup,
but you won't have to lock all of your tables and it wil give you a
consistent state across a mixed INNODB MyISAM environment.
The downside is
-you have 3 files to deal with
-you have to maintain the table names in the mysqldump commands
-you have a small risk of a change ocurring in the MyISAM
tables between time the 2nd FLUSH LOGS is executed and the 2nd
mysqldump command is executed
Thanks for the tip. I haven't looked into binary logs too much, just
learned about them the other day when I had to correct my own mistake
and restore a table.
Is is best practice to go with tables of all one sort to allow for
consistent state when doing backups like this, or are mixed tables the
norm in most databases? I went with the InnoDB in order to not have to
do row level locking on the tables as transactions were performed and
I've been very pleased with the results. I might consider just going
with all InnoDB to make it easy, as those are the majority of my tables
in this case.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
Re: Wrf files: how can I read them?
asteddy wrote: Thank you, but why has mysql made seminars wich must be seen with a non-free software with a 14 days trial? Is there nothing else to see it? Asteddy We haven't :) You can download a free player at: http://www.webex.com/customercare/downloads-player.html This is not a trial version but is completely free, and is available for Windows, Mac OS 9 and Mac OS X. It's possible also to view them in Linux using a browser plugin (I had to Google a bit for that). The 14-day trial is for those wanting to _host_ a webex seminar. I will make sure that we provide a link to the Webex player on all web seminar signup pages. Hope you enjoy the seminars -- they have been getting extremely popular! Eric -- Eric Braswell Webmaster MySQL AB Cupertino, USA Hi, there is link take the free trial on webex site. haven't you seen the seconf button ? Mathias Selon asteddy [EMAIL PROTECTED]: Hello, I have found Mysql Performance Tuning Seminar available for download, but I don't know how to see it. I have found something like Webex website, but I don't see any software to download there. Can you help me please? Why is there nothing specified about that type of file on the download page of the seminar? Thank you very much. Asteddy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Wrf files: how can I read them?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 asteddy wrote: Thank you, but why has mysql made seminars wich must be seen with a non-free software with a 14 days trial? Is there nothing else to see it? Asteddy Asteddy, I would guess because nothing free has the functionality and ease-of-use that WebEx has, especially when you consider the scale of the audience that attends the seminars _live_, very few solutions free or not work, while WebEx works well? If you know of something that works better, and doesn't require a lot of production to put in place (like writing seminars in flash and rolling out our own call-in numbers, remember we're a database company, not a multimedia production house), why not recommend it? Regards, -Mark - -- Mark Matthews MySQL AB, Software Development Manager - Connectivity www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.6 (GNU/Linux) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCsxdYtvXNTca6JD8RAmU7AJ9XfCxQ2cW4GQs9E1r0fQ9sj/cNhgCdGlwK kI9a2F/uqeyVTgBYAHxv/ro= =iB68 -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Interesting Hardware Article
Anandtech has an interesting article (http://www.anandtech.com/IT/showdoc.aspx?i=2447) on hardware for Linux database servers. Some very interesting conclusions: 1) Moving to 64-bit MySQL on a 64-bit Xeon actually decreases performance by about 12% on average, while an Opteron running 64-bit MySQL gets a 32% performance increase. 2) Innodb scales better (obviously) 3) A server with one CPU that has a dual-core Opteron (the X2 CPUs) is faster than a server with two single-core CPUs. 4) SuSE SLES 9.1 outperforms Gentoo by about 12% I would take Anandtech with a grain of salt - this isn't what they normally do, and I can't verify their benchmarking was reasonably accurate (surprised at the disks they used - one ATA, one SCSI). David. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Interesting Hardware Article
On 18/06/2005, at 4:28 AM, David Griffiths wrote: Anandtech has an interesting article (http://www.anandtech.com/IT/showdoc.aspx?i=2447) on hardware for Linux database servers. Some very interesting conclusions: 1) Moving to 64-bit MySQL on a 64-bit Xeon actually decreases performance by about 12% on average, while an Opteron running 64-bit MySQL gets a 32% performance increase. 2) Innodb scales better (obviously) 3) A server with one CPU that has a dual-core Opteron (the X2 CPUs) is faster than a server with two single-core CPUs. 4) SuSE SLES 9.1 outperforms Gentoo by about 12% I would take Anandtech with a grain of salt - this isn't what they normally do, and I can't verify their benchmarking was reasonably accurate (surprised at the disks they used - one ATA, one SCSI). Umm the benchmarks between XEON and Opteron have been around since the Opteron came out. Typically I would say its been tweaked for a unix environment whereas the XEON has been tweaked for a windoze one ;) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Interesting Hardware Article
probably biased towards AMD. money is a powerful thing, which is why it should be taken with a grain of salt as you stated. i would guess the 12% decrease on 64bit xeon and 32% increase on 64bit opteron is BS. why didn't they try itanium instead? ;) David Griffiths wrote: Anandtech has an interesting article (http://www.anandtech.com/IT/showdoc.aspx?i=2447) on hardware for Linux database servers. Some very interesting conclusions: 1) Moving to 64-bit MySQL on a 64-bit Xeon actually decreases performance by about 12% on average, while an Opteron running 64-bit MySQL gets a 32% performance increase. 2) Innodb scales better (obviously) 3) A server with one CPU that has a dual-core Opteron (the X2 CPUs) is faster than a server with two single-core CPUs. 4) SuSE SLES 9.1 outperforms Gentoo by about 12% I would take Anandtech with a grain of salt - this isn't what they normally do, and I can't verify their benchmarking was reasonably accurate (surprised at the disks they used - one ATA, one SCSI). David. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
can innodb_buffer_pool_size be set 2Gb on FreeBSD?
Have any of you MySQL/FreeBSD cats successfully set innodb_buffer_pool_size 2G without runing into any of the memory allocation problems found on Linux platforms? Although I have been given good advice to migrate to an AMD platform overcome this limitation (and will do so eventually), I am looking for a shorter-term solution to beat this 2Gb malloc limit on a 32-bit machine. Can it be done with FreeBSD? Are there any memory allocation issues when linking to the Linux Thread Library? Are there other FreeBSD tuning measures that need to be taken? Looking forward to hearing from those who have charted this territory. Thanks, Brady -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: can innodb_buffer_pool_size be set 2Gb on FreeBSD?
On Friday 17 June 2005 02:38 pm, Brady Brown wrote: Have any of you MySQL/FreeBSD cats successfully set innodb_buffer_pool_size 2G without runing into any of the memory allocation problems found on Linux platforms? It has nothing to do with linux.. its an x86 thing.. So no.. However, some kernels have things to let you go over, but you get weird results when doing so. Jeff pgpzqaIko4NhJ.pgp Description: PGP signature
Re: alter only an enum label
On 6/16/05, Gabriel B. [EMAIL PROTECTED] wrote:
If i have a table with about 800M records. and one of the fields is a
enum(a, b, c) and i want to change it to enum(a,b,x)
My understanding (such as it is) is that the best way to do this is to add a
second column with enum('a','b','x') and set the value using the integer
value of the first column as follows. (adding 0 casts enum as integer)
ALTER TABLE table ADD new_column enum('a','b','x');
UPDATE table SET new_column = old_column + 0;
ALTER TABLE table DROP old_column;
Execution time is dependent on the speed of the update, but it would need to
read every row.
Re: can innodb_buffer_pool_size be set 2Gb on FreeBSD?
I'll post something I heard about when looking into upgrading Oracle 8i from Windows to Oracle 10g on Linux. To get more memory for the process, you would enable big memory page, and then create an in-memory temp file system; you could then allocate extra memory for a process, and part of it would be swapped out to this temp file system in memory. Red Hat Advanced Server was the OS of choice for those who did it - I played around with it, but couldn't get Oracle to start with larger memory settings (we weren't running on RedHat AS). Maybe you'll have more luck. A good page that talked about this was, http://www.oracle-base.com/articles/Linux/LargeSGAOnLinux.php Good luck. David Jeff Smelser wrote: On Friday 17 June 2005 02:38 pm, Brady Brown wrote: Have any of you MySQL/FreeBSD cats successfully set innodb_buffer_pool_size 2G without runing into any of the memory allocation problems found on Linux platforms? It has nothing to do with linux.. its an x86 thing.. So no.. However, some kernels have things to let you go over, but you get weird results when doing so. Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
simple data GUI editor?
i was wondering if people can recommend a simple Excel like tool for editing data? MySql control center - seems to have limitations (unicode, not in dev anymore) Query browser - have to type raw sql to show/hide columns... ideally i want something with a few more features than either of these, eg list data in a vertical table rather than just horizontal... lookups to other tables but more oriented to lots of interactive editing of the DB data than DB admin. I guess more like an Access GUI... (puts on flame pants) + ideally not very expensive :-) thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: simple data GUI editor?
Well, Actually MS-Access through ODBC should work for you -Original Message- From: D_C [mailto:[EMAIL PROTECTED] Sent: Friday, June 17, 2005 4:28 PM To: mysql@lists.mysql.com Subject: simple data GUI editor? i was wondering if people can recommend a simple Excel like tool for editing data? MySql control center - seems to have limitations (unicode, not in dev anymore) Query browser - have to type raw sql to show/hide columns... ideally i want something with a few more features than either of these, eg list data in a vertical table rather than just horizontal... lookups to other tables but more oriented to lots of interactive editing of the DB data than DB admin. I guess more like an Access GUI... (puts on flame pants) + ideally not very expensive :-) thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: simple data GUI editor?
We use MySQL-Front from Star-Tools GmbH (www.mysqlfront.de)... works pretty much like you have asked. Thanks, Car - Original Message - From: Berman, Mikhail [EMAIL PROTECTED] To: mysql@lists.mysql.com Cc: D_C [EMAIL PROTECTED] Sent: Friday, June 17, 2005 4:36 PM Subject: RE: simple data GUI editor? Well, Actually MS-Access through ODBC should work for you -Original Message- From: D_C [mailto:[EMAIL PROTECTED] Sent: Friday, June 17, 2005 4:28 PM To: mysql@lists.mysql.com Subject: simple data GUI editor? i was wondering if people can recommend a simple Excel like tool for editing data? MySql control center - seems to have limitations (unicode, not in dev anymore) Query browser - have to type raw sql to show/hide columns... ideally i want something with a few more features than either of these, eg list data in a vertical table rather than just horizontal... lookups to other tables but more oriented to lots of interactive editing of the DB data than DB admin. I guess more like an Access GUI... (puts on flame pants) + ideally not very expensive :-) thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.323 / Virus Database: 267.7.5/18 - Release Date: 6/15/2005 -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.323 / Virus Database: 267.7.5/18 - Release Date: 6/15/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Subselect in an Update query
Hi, There is one caveat: It is not currently possible to modify a table and select from the same table in a subquery. this phrase is from http://dev.mysql.com/tech-resources/articles/4.1/subqueries.html solution * create table t as Select field1 From table1 Where field2=Some Value Update table1 Set field1=(Select field1 From t Where field2=Some Value) Where field2 =Another Value; ** mysql select * from upd; +--+--+ | a| b| +--+--+ |1 | one | |1 | two | |2 | one | |2 | two | |1 | Un | +--+--+ 5 rows in set (0.09 sec) mysql create table t as select * from upd where b='Un'; mysql update upd set a=(select a from t where b='Un') where b='one'; mysql select * from upd; +--+--+ | a| b| +--+--+ |1 | one | |1 | two | |1 | one | changed |2 | two | |1 | Un | +--+--+ 5 rows in set (0.00 sec) Mathias Selon Ed Reed [EMAIL PROTECTED]: Can anyone tell me how I can make this work or suggest a work around? Update table1 Set field1=(Select field1 From table1 Where field2=Some Value) Where field2 =Another Value; Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: can innodb_buffer_pool_size be set 2Gb on FreeBSD?
Hi, i don't think so. 2 go is a limit of almsot 32-bits plateform, linux or others. Migrate to 64-bits. Mathias Selon Brady Brown [EMAIL PROTECTED]: Have any of you MySQL/FreeBSD cats successfully set innodb_buffer_pool_size 2G without runing into any of the memory allocation problems found on Linux platforms? Although I have been given good advice to migrate to an AMD platform overcome this limitation (and will do so eventually), I am looking for a shorter-term solution to beat this 2Gb malloc limit on a 32-bit machine. Can it be done with FreeBSD? Are there any memory allocation issues when linking to the Linux Thread Library? Are there other FreeBSD tuning measures that need to be taken? Looking forward to hearing from those who have charted this territory. Thanks, Brady -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Subselect in an Update query
Sorry, I thought it was easy to understand. I wanna update a field in a table with a value from the same field but from a different record of the same table. For example, using the query in my original message, +--+ |RecID |FIELD1 |FIELD2 | +--+ | 1 | ABC | A Value | +--+ | 2 | DEF | Some Value| +--+ | 3 | GHI | Another Value | +--+ | 4 | JKL | More Values | +--+ Update table1 Set field1=(Select field1 From table1 Where field2=Some Value) Where field2 =Another Value; This query should set FIELD1 of Record 3 to 'DEF' +--+ |RecID |FIELD1 |FIELD2 | +--+ | 1 | ABC | A Value | +--+ | 2 | DEF | Some Value| +--+ | 3 | DEF | Another Value | +--+ | 4 | JKL | More Values | +--+ That's it! Should be easy but I get an error that says You can't specify target table 'table1' for update in FROM clause - Thanks [EMAIL PROTECTED] 6/17/05 11:03:40 AM Ed Reed [EMAIL PROTECTED] wrote on 06/17/2005 01:35:40 PM: Can anyone tell me how I can make this work or suggest a work around? Update table1 Set field1=(Select field1 From table1 Where field2=Some Value) Where field2 =Another Value; Thanks OK, your example must be broken (or it wouldn't be here) and since that's all you posted I have a hard time divining your intent. Would you please provide some sample data and explain what it is you want to do _in words_? I can't speak for everyone but I know it would sure help me figure out what you want to do. Thanks! Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: can innodb_buffer_pool_size be set 2Gb on FreeBSD?
pretty interesting. i'll test it for oracle. But the db_cache will be a simple swap file. i don't think it's as good as real memory for dirty lists management. Mathias Selon David Griffiths [EMAIL PROTECTED]: I'll post something I heard about when looking into upgrading Oracle 8i from Windows to Oracle 10g on Linux. To get more memory for the process, you would enable big memory page, and then create an in-memory temp file system; you could then allocate extra memory for a process, and part of it would be swapped out to this temp file system in memory. Red Hat Advanced Server was the OS of choice for those who did it - I played around with it, but couldn't get Oracle to start with larger memory settings (we weren't running on RedHat AS). Maybe you'll have more luck. A good page that talked about this was, http://www.oracle-base.com/articles/Linux/LargeSGAOnLinux.php Good luck. David Jeff Smelser wrote: On Friday 17 June 2005 02:38 pm, Brady Brown wrote: Have any of you MySQL/FreeBSD cats successfully set innodb_buffer_pool_size 2G without runing into any of the memory allocation problems found on Linux platforms? It has nothing to do with linux.. its an x86 thing.. So no.. However, some kernels have things to let you go over, but you get weird results when doing so. Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Subselect in an Update query
Could you accomplish this with an update and self join? Ed Reed wrote: Sorry, I thought it was easy to understand. I wanna update a field in a table with a value from the same field but from a different record of the same table. For example, using the query in my original message, +--+ |RecID |FIELD1 |FIELD2 | +--+ | 1 | ABC | A Value | +--+ | 2 | DEF | Some Value| +--+ | 3 | GHI | Another Value | +--+ | 4 | JKL | More Values | +--+ Update table1 Set field1=(Select field1 From table1 Where field2=Some Value) Where field2 =Another Value; This query should set FIELD1 of Record 3 to 'DEF' +--+ |RecID |FIELD1 |FIELD2 | +--+ | 1 | ABC | A Value | +--+ | 2 | DEF | Some Value| +--+ | 3 | DEF | Another Value | +--+ | 4 | JKL | More Values | +--+ That's it! Should be easy but I get an error that says You can't specify target table 'table1' for update in FROM clause - Thanks [EMAIL PROTECTED] 6/17/05 11:03:40 AM Ed Reed [EMAIL PROTECTED] wrote on 06/17/2005 01:35:40 PM: Can anyone tell me how I can make this work or suggest a work around? Update table1 Set field1=(Select field1 From table1 Where field2=Some Value) Where field2 =Another Value; Thanks OK, your example must be broken (or it wouldn't be here) and since that's all you posted I have a hard time divining your intent. Would you please provide some sample data and explain what it is you want to do _in words_? I can't speak for everyone but I know it would sure help me figure out what you want to do. Thanks! Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Ordinal number within a table
Is there way to return the ordinal position of a value within a table? Let's say I have a table of phone numbers. Over time the table has had additions and deletions. The table has an autonumber ID field. If I sort by the ID field I'd like to know what position the number '555-1212' is in the table. Any thoughts?
Re: Subselect in an Update query
Thanks [EMAIL PROTECTED] 6/17/05 2:03:02 PM Hi, There is one caveat: It is not currently possible to modify a table and select from the same table in a subquery. this phrase is from http://dev.mysql.com/tech-resources/articles/4.1/subqueries.html solution * create table t as Select field1 From table1 Where field2=Some Value Update table1 Set field1=(Select field1 From t Where field2=Some Value) Where field2 =Another Value; ** mysql select * from upd; +--+--+ | a | b | +--+--+ | 1 | one | | 1 | two | | 2 | one | | 2 | two | | 1 | Un | +--+--+ 5 rows in set (0.09 sec) mysql create table t as select * from upd where b='Un'; mysql update upd set a=(select a from t where b='Un') where b='one'; mysql select * from upd; +--+--+ | a | b | +--+--+ | 1 | one | | 1 | two | | 1 | one | changed | 2 | two | | 1 | Un | +--+--+ 5 rows in set (0.00 sec) Mathias Selon Ed Reed [EMAIL PROTECTED] : Can anyone tell me how I can make this work or suggest a work around? Update table1 Set field1=(Select field1 From table1 Where field2=Some Value) Where field2 =Another Value; Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: Subselect in an Update query
Thanks for the reply. What do you mean by 'self join'? Eric Bergen [EMAIL PROTECTED] 6/17/05 4:39:28 PM Could you accomplish this with an update and self join? Ed Reed wrote: Sorry, I thought it was easy to understand. I wanna update a field in a table with a value from the same field but from a different record of the same table. For example, using the query in my original message, +--+ |RecID |FIELD1 | FIELD2 | +--+ | 1 | ABC | A Value | +--+ | 2 | DEF | Some Value | +--+ | 3 | GHI | Another Value | +--+ | 4 | JKL | More Values | +--+ Update table1 Set field1=(Select field1 From table1 Where field2=Some Value) Where field2 =Another Value; This query should set FIELD1 of Record 3 to 'DEF' +--+ |RecID |FIELD1 | FIELD2 | +--+ | 1 | ABC | A Value | +--+ | 2 | DEF | Some Value | +--+ | 3 | DEF | Another Value | +--+ | 4 | JKL | More Values | +--+ That's it! Should be easy but I get an error that says You can't specify target table 'table1' for update in FROM clause - Thanks [EMAIL PROTECTED] 6/17/05 11:03:40 AM Ed Reed [EMAIL PROTECTED] wrote on 06/17/2005 01:35:40 PM: Can anyone tell me how I can make this work or suggest a work around? Update table1 Set field1=(Select field1 From table1 Where field2=Some Value) Where field2 =Another Value; Thanks OK, your example must be broken (or it wouldn't be here) and since that's all you posted I have a hard time divining your intent. Would you please provide some sample data and explain what it is you want to do _in words_? I can't speak for everyone but I know it would sure help me figure out what you want to do. Thanks! Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
5.0.7 Upgrade (from 4.1) on OS X doesn't recognize datadir
So I am attempting a 5.0 upgrade from 4.1 on one of our OS X servers... When attempting to launch mysqld it quits, with this error (showing two from the log files, happens with our build or the MySQL binary): 050617 14:03:46 mysqld started /usr/local/mysql-standard-5.0.7-beta-osx10.3-powerpc/bin/mysqld_safe: line 2: --datadir=/mysqldata2: No such file or directory 050617 14:03:46 mysqld ended 050617 14:07:58 mysqld started /usr/local/mysql-lw64bit-5.0.7-apple-darwin8.1-powerpc/bin/ mysqld_safe64: line 2: --datadir=/mysqldata2: No such file or directory 050617 14:07:58 mysqld ended Needless to say /mysqldata2 is present and accounted for, and has the correct permissions for mysql to be able to read/write data... It is a symlink to another volume, but if I substitute the true path to the volume, I get the same error... I had problems with one version of 4.1 having problems figuring out where to write the log files and it turned out to be an absolute file name issue... eg it treated /logs/binlogs as being relative to the data directory, and not an absolute directory... I resolved that by changing the log file setting to read ../logs/binlogs and it worked fine. Assuming there was a similar problem here I have tried various levels of ../../mysqldata2 to make sure I am escaping from whatever directory it is starting me in, and have failed to get there, after switching up 7 levels, far more than would be needed to get back to root from anywhere in /usr/local/mysql-any-version Any idea how I can get MySQL 5.0 to launch here would be greatly appreciated :-) Best Regards, Bruce -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Ordinal number within a table
In the last episode (Jun 17), Ed Reed said: Is there way to return the ordinal position of a value within a table? Let's say I have a table of phone numbers. Over time the table has had additions and deletions. The table has an autonumber ID field. If I sort by the ID field I'd like to know what position the number '555-1212' is in the table. In Oracle you could simply use the internal rownum column. In MySQL, you can use a user variable in a subquery to keep a row count during the select process: SET @row=0; SELECT (@row:[EMAIL PROTECTED]) AS row, ename, empno FROM emp ORDER BY empno; +-++---+ | row | ename | empno | +-++---+ | 1 | SMITH | 7369 | | 2 | ALLEN | 7499 | | 3 | WARD | 7521 | | 4 | JONES | 7566 | | 5 | MARTIN | 7654 | | 6 | BLAKE | 7698 | | 7 | CLARK | 7782 | | 8 | SCOTT | 7788 | | 9 | KING | 7839 | | 10 | TURNER | 7844 | | 11 | ADAMS | 7876 | | 12 | JAMES | 7900 | | 13 | FORD | 7902 | | 14 | MILLER | 7934 | +-++---+ SET @row=0; SELECT * FROM ( SELECT (@row:[EMAIL PROTECTED]) AS row, ename, empno FROM emp ORDER BY empno ) t WHERE ename='scott'; +-+---+---+ | row | ename | empno | +-+---+---+ | 8 | SCOTT | 7788 | +-+---+---+ -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Ordinal number within a table
From: Ed Reed Is there way to return the ordinal position of a value within a table? Let's say I have a table of phone numbers. Over time the table has had additions and deletions. The table has an autonumber ID field. If I sort by the ID field I'd like to know what position the number '555-1212' is in the table. I'm not sure why you'd want to know this, but it's generally a good idea to abandon the thought that records in a database are stored in a certain order with a position number attached to them. The internal way of storing data differs from engine to engine and you can never be sure that these internals will not be modified in newer releases of MySQL. It's best to think of a table as a collection of records which can be presented in the way you want. The way the database decides to store the data is in many cases not relevant at all; that's the job of the database. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
