Re: InnoDB Questions
MySQL doesn't work. I tried to modified the line: innodb_data_file_path = ibdata1:10M:autoextend to innodb_data_file_path = ibdata1:500M or innodb_data_file_path = ibdata1:500M:autoextend or innodb_data_file_path = ibdata1:500M;ibdata2:1000M:autoextend they all gave me the same error below. InnoDB: Error: data file ./ibdata1 is of a different size InnoDB: than specified in the .cnf file! InnoDB: Could not open data files 031105 9:42:56 Can't init databases 031105 09:42:56 mysqld ended The last one really does the matter!! That's if I run out of the space on the current directory, I won't be able to put another file anywhere else!? Leo Nitin wrote: You're right, it wont decrease the physical size, but only free up the space within file to optimize the tablespace, in case, you want to check the size of this data file, you can remove autoextend from: innodb_data_file_path = ibdata1:10M:autoextend and specify the size limit in the place of 10M, but i guess, if you specify the size to less than 790M (which is the current size of your datafile), to say 500M and the space is free in that file, it will resize it. That's the behaviour of Oracle datafiles (believe me, i'm oracle certified!). try it, and let me know as i dont have my database on innodb yet. 'Tablespace is part of your database. database consists of at least one tablespace. it's basically used to restrict users from seeing other user's data. like, you can assign a tablespace to a user and none else (ofcourse other than root) can see the data. For more info, have a look at: http://www.mysql.com/doc/en/InnoDB_File_space.html Enjoy Nitin - Original Message - From: Leo Huang [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, November 04, 2003 8:00 PM Subject: Re: InnoDB Questions -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Thank you very much for your reply, Nitin. I did read the Adding_and_removing in the manual, but it says Currently you cannot remove a datafile from InnoDB. To decrease the size of your database you have to use `mysqldump' to dump all your tables, create a new database, and import your tables to the new database. It does reduce the size of the database(similar to optimize for MyISAM), but it doesn't reduce the size of the file. Could you explain a bit what is a tablespace? Thanks, Leo /etc/my.cnf: [mysqld] datadir=/var/lib/mysql set-variable=max_connections=300 innodb_data_file_path = ibdata1:10M:autoextend default-table-type=InnoDB set-variable = innodb_buffer_pool_size=512M set-variable = innodb_additional_mem_pool_size=10M set-variable = innodb_log_file_size=10M set-variable = innodb_log_files_in_group=3 set-variable = innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit=1 [mysql.server] user=mysql basedir=/usr [safe_mysqld] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid - - Original Message - From: Nitin [EMAIL PROTECTED] To: Leo Huang [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, November 05, 2003 1:01 AM Subject: Re: InnoDB Questions Hello, first things first, you cann't resize your datafiles without shutting down your database. if it's ok with you, have a look at http://www.mysql.com/doc/en/Adding_and_removing.html you may want to have a look at you my.cnf file, stored in mysql data dir or in /etc dir, for the default options specified there fo the datafile with: innodb_data_file_path Yes, you can add data file, just add another entry to above option. option entry is self-explainatory. At last, ibdata1, ibdata2 are actual data files used to store actual data. one or more of these files are attached to one tablespace and one file cant span across tablespaces. ib_logfile0, ib_logfile1 are log files, which are used to log sql statements applied to database. these files are used to restore data in case of any crash or mishap. for further info, have a look at: http://www.mysql.com/doc/en/InnoDB_start.html Enjoy Nitin - Original Message - From: Leo Huang [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, November 04, 2003 6:28 PM Subject: InnoDB Questions Hello, I have a few questions about InnoDB. I am new to InnoDB, and just converted my MyISAM tables into InnoDB a few days ago. I notice that it generates these files -rw-rw1 mysqlmysql2560 Nov 2 13:07 ib_arch_log_00 -rw-rw1 mysqlmysql790626304 Nov 4 20:44 ibdata1 -rw-rw1 mysqlmysql10485760 Nov 4 20:44 ib_logfile0 -rw-rw1 mysqlmysql10485760 Nov 4 20:44 ib_logfile1 -rw-rw1 mysqlmysql10485760 Nov 3 00:02 ib_logfile2 But how can those files work together? In InnoDB documentation, it suggests to add another file ibdata2 to get higher performance. Can I do that now, after
Re: temporary tables
[EMAIL PROTECTED] wrote: I am having trouble creating temp tables. An error message keeps coming up saying that @localhost does not exist. (or something like that) can anyone help. Could you show us exactly error message? Does user have CREATE TEMPORARY TABLES privilege? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [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: another insert select question
Jason Joines [EMAIL PROTECTED] wrote: Table employees: | idnumber | email | phone | address | Table webusers: - | idnumber | userid | website | - Table employees is completely populated. Table webusers has the idnumber and website fields completely populated. What query can I use to insert the email address from each persones record in the employees table into the userid field of their corresponding record in the webusers table? If I've got you right you need UPDATE, not INSERT .. SELECT. UPDATE webuser, employees SET webuser.userid=employees.email WHERE webuser.idnumber=employees.idnumber; The above UPDATE statement will work from v4.0.4. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [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]
Mysql Stoping
Hi All Was wondering if someone could shef a bit of light on whats happening, as i keep loosing the connection to the mysql server, and i get the following error: mysqld dead but subsys locked Thanks in advance Cheers Trevor
Re: Mysql Stoping
Hi Trevor, I suggest you to compile and reinstall MySQL from the source distribution. A suggested option is CFLAGS=-O3 CXX=gcc CXXFLAGS=-O3 -felide-constructors -fno-exceptions \ -fno-rtti ./configure --prefix=/usr/local/mysql --enable-assembler \ --with-mysqld-ldflags=-all-static For more information, please refer to http://www.mysql.com/doc/en/Installing_source.html If you are running a RH Linux try to modify the --prefix=/usr, so you can use the RH scripts, and don't forget to set up something like --datadir This is my configure options, hope it helps. --prefix=/usr --datadir=/var/lib/mysql --with-innodb --with-extra-charsets=complex --enable-thread-safe-client --enable-local-infile --enable-assembler --disable-shared --with-client-ldflags=-all-static --with-mysqld-ldflags=-all-static --quiet Cheers, Leo Trevor wrote: Hi All Was wondering if someone could shef a bit of light on whats happening, as i keep loosing the connection to the mysql server, and i get the following error: mysqld dead but subsys locked Thanks in advance Cheers Trevor -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem with DELETE USING
Hi, I'm a newbie when it comes to mySQL. I have the following command. DELETE FROM tblTest2 USING tblTest2,tblTest WHERE tblTest.Name = tblTest2.Name AND tblTest.URL = tblTest2.URL AND tblTest.Comment = tblTest2.Comment I get the following error: You have an error in your SQL syntax near 'USING tblTest2,tblTest WHERE tblTest.Name = tblTest2.Name AND tblTest.URL = tblT' at line 1 I want to delete rows from tblTest2 where there are similarities in tblTest. In fact using a subselect or something. Could anyone help me please, Thx
Re: LOAD DATA hangs
4) ALTER TABLE ENABLE KEYS - Puts a full load on the CPU, but neither index nor table size seem to change, even after waiting for an hour. Based on smaller data sets, I'd expect the index to reach something close to 4 GB. Update: After several hours, the index file started growing, although very slowly. After another 21 hours or so the indexes were complete. The final index file size is 4.43 GB. I currently have the following options set: key_buffer_size=64M myisam_sort_buffer_size=64MB bulk_insert_buffer_size=64MB Any suggestions what else could be tried in order to get MySQL to scale? Any important options I overlooked? Might InnoDB be faster? -- Eric Jain -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql memory usage
Sorry, but I disagree :/ I always used 250MB of key buffer, and MySQL never allocates more than 50MB, in my database. Read buffer is only allocated when full scans are done. Join buffer is allocated when there are joins without index use. Sort buffer is allocated when needed, and etc... Alexis P.S.: you can test it easily, doing specific queries for each case. -Original Message- From: Jeremy Zawodny [mailto:[EMAIL PROTECTED] Sent: terça-feira, 4 de Novembro de 2003 23:51 To: Alexis Guia Cc: 'Benjamin KRIEF'; [EMAIL PROTECTED] Subject: Re: mysql memory usage On Tue, Nov 04, 2003 at 10:09:01AM -, Alexis Guia wrote: Hi, I think that MyISAM uses the key buffer only if needed. The same happens with almost all the other buffers (read buffer, sort buffer, etc.). True, but there's a subtle difference between uses and allocates. If you tell MySQL that it has 16GB for a key_buffer, it'll allocate 16GB even if it only ever uses 28KB. The same is true of several (probably all?) other buffers. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 51 days, processed 1,925,645,484 queries (428/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with DELETE USING
Bamelis, The error message doesn't seem to match your SQL... It only shows up to 'AND tblTest.URL = tblT' but your SQL is 'AND tblTest.Comment = tblTest2.Comment' Is that a problem?? An example from MySQL manual is 'DELETE FROM t1,t2 USING t1,t2,t3 WHERE t1.id=t2.id AND t2.id=t3.id' Leo Bamelis Steve wrote: Hi, I'm a newbie when it comes to mySQL. I have the following command. DELETE FROM tblTest2 USING tblTest2,tblTest WHERE tblTest.Name = tblTest2.Name AND tblTest.URL = tblTest2.URL AND tblTest.Comment = tblTest2.Comment I get the following error: You have an error in your SQL syntax near 'USING tblTest2,tblTest WHERE tblTest.Name = tblTest2.Name AND tblTest.URL = tblT' at line 1 I want to delete rows from tblTest2 where there are similarities in tblTest. In fact using a subselect or something. Could anyone help me please, Thx -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with DELETE USING
Bamelis Steve [EMAIL PROTECTED] wrote: I'm a newbie when it comes to mySQL. I have the following command. DELETE FROM tblTest2 USING tblTest2,tblTest WHERE tblTest.Name = tblTest2.Name AND tblTest.URL = tblTest2.URL AND tblTest.Comment = tblTest2.Comment I get the following error: You have an error in your SQL syntax near 'USING tblTest2,tblTest WHERE tblTest.Name = tblTest2.Name AND tblTest.URL = tblT' at line 1 I want to delete rows from tblTest2 where there are similarities in tblTest. In fact using a subselect or something. What version of MySQL server do you use? The above DELETE syntax is supported from v4.0.2. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [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]
Viruses from the list
I use an email-adress for this list only and since my first posting a few days ago I got viruses, while I didn't before. VIRUS FROM mamo @ hvd.healthnet.lu (W32/[EMAIL PROTECTED]) VIRUS FROM kawamoto @ wave.tky.plala.or.jp ([EMAIL PROTECTED]) VIRUS FROM sales @ vulcanford.ca (W32/[EMAIL PROTECTED])/[EMAIL PROTECTED]) Wake up people, it was time enough to update virus-definitions. Clean your pcs. I will disable my email-adress soon. Al -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem with LIKE/REGEXP
Hello everyone! I have a table with a MEDIUMBLOB column: CREATE TABLE mytab ( oid INTEGER ... odata MEDIUMBLOB ); in the mediumblob field i store an encoded string (bytes). I would like to perform regular expression searches on this field here is an example that works (produces matches): SELECT oid FROM mytab WHERE odata LIKE [EMAIL PROTECTED]\ngmosx%; here is an example that doesnt work (no matches returned): SELECT oid FROM mytab WHERE odata LIKE [EMAIL PROTECTED]\017athens1234%; the \017 character seems to be the problem since the following produces matches: SELECT oid FROM mytab WHERE odata LIKE [EMAIL PROTECTED]_athens1234%; I tried using a MEDIUMTEXT and/or REGEXP but without success! Any idea how to do string matches on string-encoded binary data? Thanks in advance for any info! George Moschovitis -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with DELETE USING
Wednesday, November 05, 2003, 1:46:13 PM, Bamelis Steve wrote: BS Hi Victoria, BS I just found a manual for my version. BS Seems that 'USING' doesn't exist in this version. BS But I really need to be able to delete rows from my source table with BS specific criteria from my destination table. BS Is there a work around for this? In v3.23 you can't do it with one query. Use programming language to retrieve rows with SELECT statement and then DELETE these rows from tblTest2. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [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: Viruses from the list
Al Bogner, Thanks for you info. Yes, I got quite a few as well. About Microsoft update stuff etc. But I think emails with viruses are quite common, my mail server captures around 2,000 emails with virus everyday. Also this is an old virus(relatively speaking), so it should be fine, I think. Leo Al Bogner wrote: I use an email-adress for this list only and since my first posting a few days ago I got viruses, while I didn't before. VIRUS FROM mamo @ hvd.healthnet.lu (W32/[EMAIL PROTECTED]) VIRUS FROM kawamoto @ wave.tky.plala.or.jp ([EMAIL PROTECTED]) VIRUS FROM sales @ vulcanford.ca (W32/[EMAIL PROTECTED])/[EMAIL PROTECTED]) Wake up people, it was time enough to update virus-definitions. Clean your pcs. I will disable my email-adress soon. Al -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: log information in MySQL
Jon Miller [EMAIL PROTECTED] wrote: I need a way to log every message that MySQL generates. I have the following in the /etc/my.cnf file: [mysqld] port=3309 datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock log-bin=/var/log/mysql.log bind-address=192.168.0.15 log=/var/log/mysqlquery.log [mysql.server] user=mysql basedir=/var/lib [safe_mysqld] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid Also in /etc/syslog.conf I have an entry: # Log Mysql messages mysqld.*/var/log/mysqld.log Not sure if this will work. Any suggesstions? And what is the problem? Log files are not created or they are empty or what? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [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: InnoDB Questions
Hi, From my experience, once you have created a datafile it doesn't work when you try to make it larger. If you dont care about the data thats already in it then delete the current ibdata files and restart mysql. If you do then put the size back to how it was, dump the data, shutdown the server, change the cfg file and remove the ibdata file. At startup it should create the new larger file, then you can import the dumped data. Marvin. -Original Message- From: Leo Huang [mailto:[EMAIL PROTECTED] Sent: 05 November 2003 07:40 To: Nitin Cc: [EMAIL PROTECTED] Subject: Re: InnoDB Questions MySQL doesn't work. I tried to modified the line: innodb_data_file_path = ibdata1:10M:autoextend to innodb_data_file_path = ibdata1:500M or innodb_data_file_path = ibdata1:500M:autoextend or innodb_data_file_path = ibdata1:500M;ibdata2:1000M:autoextend they all gave me the same error below. InnoDB: Error: data file ./ibdata1 is of a different size InnoDB: than specified in the .cnf file! InnoDB: Could not open data files 031105 9:42:56 Can't init databases 031105 09:42:56 mysqld ended The last one really does the matter!! That's if I run out of the space on the current directory, I won't be able to put another file anywhere else!? Leo Nitin wrote: You're right, it wont decrease the physical size, but only free up the space within file to optimize the tablespace, in case, you want to check the size of this data file, you can remove autoextend from: innodb_data_file_path = ibdata1:10M:autoextend and specify the size limit in the place of 10M, but i guess, if you specify the size to less than 790M (which is the current size of your datafile), to say 500M and the space is free in that file, it will resize it. That's the behaviour of Oracle datafiles (believe me, i'm oracle certified!). try it, and let me know as i dont have my database on innodb yet. 'Tablespace is part of your database. database consists of at least one tablespace. it's basically used to restrict users from seeing other user's data. like, you can assign a tablespace to a user and none else (ofcourse other than root) can see the data. For more info, have a look at: http://www.mysql.com/doc/en/InnoDB_File_space.html Enjoy Nitin - Original Message - From: Leo Huang [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, November 04, 2003 8:00 PM Subject: Re: InnoDB Questions -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Thank you very much for your reply, Nitin. I did read the Adding_and_removing in the manual, but it says Currently you cannot remove a datafile from InnoDB. To decrease the size of your database you have to use `mysqldump' to dump all your tables, create a new database, and import your tables to the new database. It does reduce the size of the database(similar to optimize for MyISAM), but it doesn't reduce the size of the file. Could you explain a bit what is a tablespace? Thanks, Leo /etc/my.cnf: [mysqld] datadir=/var/lib/mysql set-variable=max_connections=300 innodb_data_file_path = ibdata1:10M:autoextend default-table-type=InnoDB set-variable = innodb_buffer_pool_size=512M set-variable = innodb_additional_mem_pool_size=10M set-variable = innodb_log_file_size=10M set-variable = innodb_log_files_in_group=3 set-variable = innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit=1 [mysql.server] user=mysql basedir=/usr [safe_mysqld] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid - - Original Message - From: Nitin [EMAIL PROTECTED] To: Leo Huang [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, November 05, 2003 1:01 AM Subject: Re: InnoDB Questions Hello, first things first, you cann't resize your datafiles without shutting down your database. if it's ok with you, have a look at http://www.mysql.com/doc/en/Adding_and_removing.html you may want to have a look at you my.cnf file, stored in mysql data dir or in /etc dir, for the default options specified there fo the datafile with: innodb_data_file_path Yes, you can add data file, just add another entry to above option. option entry is self-explainatory. At last, ibdata1, ibdata2 are actual data files used to store actual data. one or more of these files are attached to one tablespace and one file cant span across tablespaces. ib_logfile0, ib_logfile1 are log files, which are used to log sql statements applied to database. these files are used to restore data in case of any crash or mishap. for further info, have a look at: http://www.mysql.com/doc/en/InnoDB_start.html Enjoy Nitin - Original Message - From: Leo Huang [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, November 04, 2003 6:28 PM Subject: InnoDB Questions Hello, I have a few questions about InnoDB. I am new to InnoDB, and just converted my MyISAM tables into InnoDB a few days ago. I
Re: Problem with LIKE/REGEXP
Hi, What db type do u use? If I'm right InnoDB doesn't support Free text search... bye Hans At 14:29 5-11-03, you wrote: Hello everyone! I have a table with a MEDIUMBLOB column: CREATE TABLE mytab ( oid INTEGER ... odata MEDIUMBLOB ); in the mediumblob field i store an encoded string (bytes). I would like to perform regular expression searches on this field here is an example that works (produces matches): SELECT oid FROM mytab WHERE odata LIKE [EMAIL PROTECTED]\ngmosx%; here is an example that doesnt work (no matches returned): SELECT oid FROM mytab WHERE odata LIKE [EMAIL PROTECTED]\017athens1234%; the \017 character seems to be the problem since the following produces matches: SELECT oid FROM mytab WHERE odata LIKE [EMAIL PROTECTED]_athens1234%; I tried using a MEDIUMTEXT and/or REGEXP but without success! Any idea how to do string matches on string-encoded binary data? Thanks in advance for any info! George Moschovitis -- 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: Problem with LIKE/REGEXP
Here is some additional information: I tried with version 3.23 and version 4.0 And I am using MyISAM tables. This is not a full text search an there is no index on the column. any ideas? George Moschovitis -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Viruses from the list
Hi, Since joining the list I'm getting about 6 virus emails a day. Fortunately the firewall is stripping them for me and just sending me alerts telling me the virus and the sender of the mail e.g. The message senders were [EMAIL PROTECTED] [EMAIL PROTECTED] The message title was latest internet security pack The message date was 4 Nov 2003 09:58:03 +0100 The virus or unauthorised code identified in the email is /var/qmail/queue/split/0/attach/422738_7X_PM10_EMS_MA-X=2DMSDOWNLOAD__instal ler355.exe Found the W32/[EMAIL PROTECTED] virus !!! I think it is unfortunate, and people should be aware of this, but it's just one of those things that happen... oh well! Thanks, Mike -Original Message- From: Leo Huang [mailto:[EMAIL PROTECTED] Sent: 05 November 2003 12:39 To: Al Bogner Cc: [EMAIL PROTECTED] Subject: Re: Viruses from the list Al Bogner, Thanks for you info. Yes, I got quite a few as well. About Microsoft update stuff etc. But I think emails with viruses are quite common, my mail server captures around 2,000 emails with virus everyday. Also this is an old virus(relatively speaking), so it should be fine, I think. Leo Al Bogner wrote: I use an email-adress for this list only and since my first posting a few days ago I got viruses, while I didn't before. VIRUS FROM mamo @ hvd.healthnet.lu (W32/[EMAIL PROTECTED]) VIRUS FROM kawamoto @ wave.tky.plala.or.jp ([EMAIL PROTECTED]) VIRUS FROM sales @ vulcanford.ca (W32/[EMAIL PROTECTED])/[EMAIL PROTECTED]) Wake up people, it was time enough to update virus-definitions. Clean your pcs. I will disable my email-adress soon. Al -- 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]
Converting chararacters greater ASCII 122 (no - nao)
I have to convert names with characters greater than ASCII 122 - z to use it in a select statement: Is there a way to do it, like select field, function(field) as converted Examples: não - nao você - voce cabeça - cabeca É Possibile - E Possibile N` Aniré - N Anire Sé Mañana - Se Manana Ärger - Aerger a.s.o Could you give me some hints, please? Al -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
OS X
After a fruitless search for MySqlCC for Mac OS X, I downloaded and built qt and MySqlCC. However, I ran into some errors on the CC build. Am I duplicating effort here? I know that we are a small market but it is a form of Unix and if I could get some assistance I would like to give this a try. Ed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL sub-query error.
Good Morning everyone: I need help figuring out what is wrong with this query. I have tried to use the LEFT JOIN, but it doesn't solve the purpose. This is what I am trying to run: SELECT group_id,name,'SELECTED' FROM groups WHERE group_id = (SELECT group_id FROM user_groups WHERE userid= 1) UNION Select group_id,name,' ' FROM groups WHERE group_id != (SELECT group_id FROM user_groups WHERE userid= 1) ORDER by group_id = I get this error: You have an error in your SQL syntax near 'SELECT group_id FROM user_groups WHERE userid= 1) union select group_id,name I have run the sub queries individually, and they work. Can anyone help ? I have tried various combinations..but each time I get the same error. Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: another insert select question
Egor Egorov wrote: Jason Joines [EMAIL PROTECTED] wrote: Table employees: | idnumber | email | phone | address | Table webusers: - | idnumber | userid | website | - Table employees is completely populated. Table webusers has the idnumber and website fields completely populated. What query can I use to insert the email address from each persones record in the employees table into the userid field of their corresponding record in the webusers table? If I've got you right you need UPDATE, not INSERT .. SELECT. UPDATE webuser, employees SET webuser.userid=employees.email WHERE webuser.idnumber=employees.idnumber; The above UPDATE statement will work from v4.0.4. You've got it right, I was way off trying to use an INSERT instead of an UPDATE. These tables were an example where I was trying to get the syntax right before using it on the real thing. I thought I had to use two tables to make it work. The real thing is one table called on a 3.23.48 server. One of it's fields is an email address (email) and one is the userid. The primary key is idnumber. I need to populate the userid field from the email address field. I can get the userid using: SELECT substring(per_email_address,1,instr(per_email_address,'@')-1) from employees; but I haven't been able to get the results into the userid field. Any ideas? Thanks, Jason === -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: OS X
[EMAIL PROTECTED] wrote: After a fruitless search for MySqlCC for Mac OS X, I downloaded and built qt and MySqlCC. However, I ran into some errors on the CC build. Am I duplicating effort here? I know that we are a small market but it is a form of Unix and if I could get some assistance I would like to give this a try. I haven't tried it, but I did come across this the other day: http://www.pogma.com/blosxom.pl/2003/Aug/17#back Looks like it requires Fink. Also, you might try CocoaMySQL which works well: http://cocoamysql.sourceforge.net/ --Ware Adams -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Table in Memory
I have a large table which I like to store into memory . Table looks like Spid_1__0 (recordname varchar(20) primary key, data blob not null ) what is the best way todo this in mysql
Re: Table in Memory
Maybe look at using a HEAP table? Load it on startup from a datasource.. On Wed, 5 Nov 2003, Arnoldus Th.J. Koeleman wrote: I have a large table which I like to store into memory . Table looks like Spid_1__0 (recordname varchar(20) primary key, data blob not null ) what is the best way todo this in mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL process increasing problem..
Hi, Problem is, Mysql server in our production machine keeps spawning child processes and it reaches to a level where mysql hangs and the only solution is to restart our mysql server. I have checked all the databases using mysqlcheck command and every table is OK now. Recently, MySQL server suffered from low disk space and hence some of the tables got corrupted. We have repaired them using mysqlcheck -r option. Can this be a Reason of the mysqld process spawning problem ??. Some help will really save me as this is a production machine. We have MySQL 3.23.x running and Following is more technical details that you may need to think upon: (1) HERE IS THE PARTIAL OUTPUT OF 'TOP' COMMAND: 2:52pm up 1 day, 22:34, 2 users, load average: 0.30, 0.09, 0.03 134 processes: 133 sleeping, 1 running, 0 zombie, 0 stopped CPU states: 0.9% user, 2.7% system, 0.0% nice, 96.3% idle Mem: 1028316K av, 1015484K used, 12832K free, 0K shrd, 180292K buff Swap: 522072K av,4096K used, 517976K free 470328K cached PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND 6097 mysql 9 0 23004 22M 1528 S 0.0 2.2 0:00 mysqld 6099 mysql 8 0 23004 22M 1528 S 0.0 2.2 0:00 mysqld 6100 mysql 9 0 23004 22M 1528 S 0.0 2.2 0:00 mysqld 6105 mysql 9 0 23004 22M 1528 S 0.0 2.2 0:03 mysqld 6108 mysql 9 0 23004 22M 1528 S 0.0 2.2 0:00 mysqld 6109 mysql 9 0 23004 22M 1528 S 0.0 2.2 0:00 mysqld (2) HERE IS THE PARTIAL LIST OF PROCESSES OUT OF TOTAL 94 AS A RESULT OF 'SHOW PROCESSLIST' COMMAND.REST OTHER PROCESSES ARE ALSO SIMILAR: idUser HostDatabase Command Time Status SQL-query --- 131 u125 localhost db125 Sleep 6243 --- --- 139 csha localhost db_cshaw_com Sleep 2494 --- --- 140 tanb localhost db_tban_com Sleep 1985 --- --- 141 tanb localhost db_tban_com Sleep 2006 --- --- 142 tanb localhost db_tban_com Sleep 1977 --- --- 143 tanb localhost db_tban_com Sleep 1948 --- --- 144 tanb localhost db_tban_com Sleep 1939 --- --- 145 tanb localhost db_tban_com Sleep 1410 --- --- 146 tanb localhost db_tban_com Sleep 1394 --- --- 147 dbpr localhost dbpropertydb Sleep 1092 --- --- 154 root localhost mysql Query 0 --- SHOW PROCESSLIST (3) HERE IS THE 'extended-status' of MySQL: +--+--+ | Variable_name| Value| +--+--+ | Aborted_clients | 1| | Aborted_connects | 0| | Bytes_received | 6271826 | | Bytes_sent | 18499758 | | Connections | 158 | | Created_tmp_disk_tables | 105 | | Created_tmp_tables | 1174 | | Created_tmp_files| 0| | Delayed_insert_threads | 0| | Delayed_writes | 0| | Delayed_errors | 0| | Flush_commands | 1| | Handler_delete | 283 | | Handler_read_first | 618 | | Handler_read_key | 598750 | | Handler_read_next| 600043 | | Handler_read_prev| 0| | Handler_read_rnd | 20400| | Handler_read_rnd_next| 21962199 | | Handler_update | 1035 | | Handler_write| 22201| | Key_blocks_used | 15582| | Key_read_requests| 1403805 | | Key_reads| 85703| | Key_write_requests | 5742 | | Key_writes | 4974 | | Max_used_connections | 88 | | Not_flushed_key_blocks | 0| | Not_flushed_delayed_rows | 0| | Open_tables | 64 | | Open_files | 131 | | Open_streams | 0| | Opened_tables| 20961| | Questions| 63778| | Select_full_join | 1288 | | Select_full_range_join | 0| | Select_range | 0| | Select_range_check | 0| | Select_scan | 7348 | | Slave_running| OFF | | Slave_open_temp_tables | 0| | Slow_launch_threads | 0| | Slow_queries | 0| | Sort_merge_passes| 0| | Sort_range | 0| | Sort_rows| 20400| | Sort_scan| 1405 | | Table_locks_immediate| 49707| | Table_locks_waited | 1| | Threads_cached | 0| | Threads_created | 157 | | Threads_connected| 88 | | Threads_running | 1| | Uptime | 18794| +--+--+ Thanks..,
an update select question
I have a table called employees on a 3.23.48 server. One of it's fields is an email address (email) and one is the userid. The primary key is idnumber. I need to populate the userid field from the email address field. I can get the userid using: SELECT substring(per_email_address,1,instr(per_email_address,'@')-1) from employees; but I haven't been able to get the results into the userid field. Any ideas? Thanks, Jason === -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Delphi Six w ADO MySQL - Query 2003048.
Greetings,
I am a very new newbie to MySQL and to OOP, Delphi, ODBC, ADO, and Access,
etc etc.
I have a very basic project using OOP, Delphi (6), ODBC, ADO Data Aware
Components, Access and MySQL(4.1).
In fact I have two projects, identical except that one connects via odbc to
Access and the other to MySQL.
Briefly:
The project connects with TADOConnection, TADOTable and TDataSource, and
consists of two tab sheets, one with a DBGrid and DBNavigator1 (Read only),
and the other with an assortment of DBEdits, a DBMemo and a DbNavigator2,
this with full read/write options.
Using the DbNavigator 'post' button, Attempts to write to Access succeed
where attempts to write to MySQL fail, all other things being equal. The
MySQL error return states:
Check the manual that corresponds to your MySQL server version for the
right syntax to use near ','detail' SET '('
So far as I can see, all MySQL permissions exist for read/write access to
the DB.
My confusion arises from the fact that to give entry level experience, I
chose data aware controls which I presumed would not need coding to function
at a basic level. This works with Access but apparently not with MySQL.
Clearly I am missing something fundamental regarding differences between
MySQL's and Access's interactions with data aware components.
Can anyone suggest what it is that I am missing and how can I coax my
project to write to MySQL?
My Ref is Query 2003048.
With thanks in anticipation,
john
John Barrington
[EMAIL PROTECTED]
+27 11 6489876
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.528 / Virus Database: 324 - Release Date: 16/10/03
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: OS X
Download http://cocoamysql.sourceforge.net/ It's really great. Painless install. I spent hours trying to get mysqlcc to install and I was ending up playing the one library short game where it seems like if you install just one more thing it will work, but then it never does. cocoamysql installed fast and easy. It is still missing a few features but I guess they are coming soon. --ja On Wed, 5 Nov 2003 [EMAIL PROTECTED] wrote: After a fruitless search for MySqlCC for Mac OS X, I downloaded and built qt and MySqlCC. However, I ran into some errors on the CC build. Am I duplicating effort here? I know that we are a small market but it is a form of Unix and if I could get some assistance I would like to give this a try. Ed -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: an update select question
update employees set userid=substring(. Jason Joines wrote: I have a table called employees on a 3.23.48 server. One of it's fields is an email address (email) and one is the userid. The primary key is idnumber. I need to populate the userid field from the email address field. I can get the userid using: SELECT substring(per_email_address,1,instr(per_email_address,'@')-1) from employees; but I haven't been able to get the results into the userid field. Any ideas? Thanks, Jason === -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL process increasing problem..
Those are threads, not processes. Each connection gets a thread. Many connections waiting for disk space will give you this problem. Jobs PHP Workshop wrote: Hi, Problem is, Mysql server in our production machine keeps spawning child processes and it reaches to a level where mysql hangs and the only solution is to restart our mysql server. I have checked all the databases using mysqlcheck command and every table is OK now. Recently, MySQL server suffered from low disk space and hence some of the tables got corrupted. We have repaired them using mysqlcheck -r option. Can this be a Reason of the mysqld process spawning problem ??. Some help will really save me as this is a production machine. We have MySQL 3.23.x running and Following is more technical details that you may need to think upon: (1) HERE IS THE PARTIAL OUTPUT OF 'TOP' COMMAND: 2:52pm up 1 day, 22:34, 2 users, load average: 0.30, 0.09, 0.03 134 processes: 133 sleeping, 1 running, 0 zombie, 0 stopped CPU states: 0.9% user, 2.7% system, 0.0% nice, 96.3% idle Mem: 1028316K av, 1015484K used, 12832K free, 0K shrd, 180292K buff Swap: 522072K av,4096K used, 517976K free 470328K cached PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND 6097 mysql 9 0 23004 22M 1528 S 0.0 2.2 0:00 mysqld 6099 mysql 8 0 23004 22M 1528 S 0.0 2.2 0:00 mysqld 6100 mysql 9 0 23004 22M 1528 S 0.0 2.2 0:00 mysqld 6105 mysql 9 0 23004 22M 1528 S 0.0 2.2 0:03 mysqld 6108 mysql 9 0 23004 22M 1528 S 0.0 2.2 0:00 mysqld 6109 mysql 9 0 23004 22M 1528 S 0.0 2.2 0:00 mysqld (2) HERE IS THE PARTIAL LIST OF PROCESSES OUT OF TOTAL 94 AS A RESULT OF 'SHOW PROCESSLIST' COMMAND.REST OTHER PROCESSES ARE ALSO SIMILAR: id User Host Database Command Time Status SQL-query --- 131 u125 localhost db125 Sleep 6243 --- --- 139 csha localhost db_cshaw_com Sleep 2494 --- --- 140 tanb localhost db_tban_com Sleep 1985 --- --- 141 tanb localhost db_tban_com Sleep 2006 --- --- 142 tanb localhost db_tban_com Sleep 1977 --- --- 143 tanb localhost db_tban_com Sleep 1948 --- --- 144 tanb localhost db_tban_com Sleep 1939 --- --- 145 tanb localhost db_tban_com Sleep 1410 --- --- 146 tanb localhost db_tban_com Sleep 1394 --- --- 147 dbpr localhost dbpropertydb Sleep 1092 --- --- 154 root localhost mysql Query 0 --- SHOW PROCESSLIST (3) HERE IS THE 'extended-status' of MySQL: +--+--+ | Variable_name| Value| +--+--+ | Aborted_clients | 1| | Aborted_connects | 0| | Bytes_received | 6271826 | | Bytes_sent | 18499758 | | Connections | 158 | | Created_tmp_disk_tables | 105 | | Created_tmp_tables | 1174 | | Created_tmp_files| 0| | Delayed_insert_threads | 0| | Delayed_writes | 0| | Delayed_errors | 0| | Flush_commands | 1| | Handler_delete | 283 | | Handler_read_first | 618 | | Handler_read_key | 598750 | | Handler_read_next| 600043 | | Handler_read_prev| 0| | Handler_read_rnd | 20400| | Handler_read_rnd_next| 21962199 | | Handler_update | 1035 | | Handler_write| 22201| | Key_blocks_used | 15582| | Key_read_requests| 1403805 | | Key_reads| 85703| | Key_write_requests | 5742 | | Key_writes | 4974 | | Max_used_connections | 88 | | Not_flushed_key_blocks | 0| | Not_flushed_delayed_rows | 0| | Open_tables | 64 | | Open_files | 131 | | Open_streams | 0| | Opened_tables| 20961| | Questions| 63778| | Select_full_join | 1288 | | Select_full_range_join | 0| | Select_range | 0| | Select_range_check | 0| | Select_scan | 7348 | | Slave_running| OFF | | Slave_open_temp_tables | 0| | Slow_launch_threads | 0| | Slow_queries | 0| | Sort_merge_passes| 0| | Sort_range | 0| | Sort_rows| 20400| | Sort_scan| 1405 | | Table_locks_immediate| 49707| | Table_locks_waited | 1| | Threads_cached | 0| | Threads_created | 157 | | Threads_connected| 88 | | Threads_running |
RE: an update select question
IF I understand you correctly, you should be able to do this: UPDATE employees SET userid=substring(per_email_address,1,instr(per_email_address,'@')-1); Chris -Original Message- From: news [mailto:[EMAIL PROTECTED] Behalf Of Jason Joines Sent: Wednesday, November 05, 2003 9:14 AM To: [EMAIL PROTECTED] Subject: an update select question I have a table called employees on a 3.23.48 server. One of it's fields is an email address (email) and one is the userid. The primary key is idnumber. I need to populate the userid field from the email address field. I can get the userid using: SELECT substring(per_email_address,1,instr(per_email_address,'@')-1) from employees; but I haven't been able to get the results into the userid field. Any ideas? Thanks, Jason === -- 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]
END THIS THREAD: RE: Viruses from the list
I've been on this list since 1997 and have probably received hundreds or thousands of viruses in that time. The only impact I've ever noticed is that people who use MUAs that allow viruses to interact with their OS will mention it to the list when they occur. If you have such an MUA, I agree with Mike: accept it or find an MUA that only does things related to mail and not a bunch of other things like forwarding viruses (should only forward manually or via your MTA). Consider a different mailer, or update your virus definitions at all times. Either way, it doesn't affect anyone who don't run windows and esp. Look Out!!! MUA. Nuf said? Thanks, Van sql create table, mysqladmin drop mysql = Linux rocks!!! http://www.dedserius.com = On Wed, 5 Nov 2003, Michael McTernan wrote: Hi, Since joining the list I'm getting about 6 virus emails a day. Fortunately the firewall is stripping them for me and just sending me alerts telling me the virus and the sender of the mail e.g. The message senders were [EMAIL PROTECTED] [EMAIL PROTECTED] The message title was latest internet security pack The message date was 4 Nov 2003 09:58:03 +0100 The virus or unauthorised code identified in the email is /var/qmail/queue/split/0/attach/422738_7X_PM10_EMS_MA-X=2DMSDOWNLOAD__instal ler355.exe Found the W32/[EMAIL PROTECTED] virus !!! I think it is unfortunate, and people should be aware of this, but it's just one of those things that happen... oh well! Thanks, Mike -Original Message- From: Leo Huang [mailto:[EMAIL PROTECTED] Sent: 05 November 2003 12:39 To: Al Bogner Cc: [EMAIL PROTECTED] Subject: Re: Viruses from the list Al Bogner, Thanks for you info. Yes, I got quite a few as well. About Microsoft update stuff etc. But I think emails with viruses are quite common, my mail server captures around 2,000 emails with virus everyday. Also this is an old virus(relatively speaking), so it should be fine, I think. Leo Al Bogner wrote: I use an email-adress for this list only and since my first posting a few days ago I got viruses, while I didn't before. VIRUS FROM mamo @ hvd.healthnet.lu (W32/[EMAIL PROTECTED]) VIRUS FROM kawamoto @ wave.tky.plala.or.jp ([EMAIL PROTECTED]) VIRUS FROM sales @ vulcanford.ca (W32/[EMAIL PROTECTED])/[EMAIL PROTECTED]) Wake up people, it was time enough to update virus-definitions. Clean your pcs. I will disable my email-adress soon. Al -- 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: an update select question
gerald_clark wrote: update employees set userid=substring(. Jason Joines wrote: I have a table called employees on a 3.23.48 server. One of it's fields is an email address (email) and one is the userid. The primary key is idnumber. I need to populate the userid field from the email address field. I can get the userid using: SELECT substring(per_email_address,1,instr(per_email_address,'@')-1) from employees; but I haven't been able to get the results into the userid field. Any ideas? Thanks, Jason === Thank you. UPDATE employees SET userid=substring(email,1,instr(email,'@')-1); is exactly what I needed. Jason === -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL sub-query error.
I don't think Sub queries like the = and != operators as groupid will never equal the result of a multi row subquery, but it can be IN a list of results. Just to check, you are using 4.1+ right? I think this alteration would work: SELECT group_id,name,'SELECTED' FROM groups WHERE group_id IN (SELECT group_id FROM user_groups WHERE userid= 1) UNION Select group_id,name,' ' FROM groups WHERE group_id NOT IN (SELECT group_id FROM user_groups WHERE userid= 1) ORDER by group_id = That being said, I think you can do it without Subqueries with this query: SELECT group_id, name, IF(1=userid,'SELECTED',' ') FROM groups ORDER BY group_id Chris -Original Message- From: Geeta Rajaraman [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 05, 2003 7:03 AM To: [EMAIL PROTECTED] Subject: MySQL sub-query error. Good Morning everyone: I need help figuring out what is wrong with this query. I have tried to use the LEFT JOIN, but it doesn't solve the purpose. This is what I am trying to run: SELECT group_id,name,'SELECTED' FROM groups WHERE group_id = (SELECT group_id FROM user_groups WHERE userid= 1) UNION Select group_id,name,' ' FROM groups WHERE group_id != (SELECT group_id FROM user_groups WHERE userid= 1) ORDER by group_id = I get this error: You have an error in your SQL syntax near 'SELECT group_id FROM user_groups WHERE userid= 1) union select group_id,name I have run the sub queries individually, and they work. Can anyone help ? I have tried various combinations..but each time I get the same error. Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Additive UPDATE
Héctor Villafuerte D. wrote: Hi all! I need to perform what I've called an additive UPDATE. The logic is the next: (1) There's a historic table (HISTORY) with two fields: mysql create table history (ID char(7) primary key, VAL int(12)); (2) There's a new table everyday (TODAY) with exactly the same structure as HISTORY (ID and VAL). (3) I need to feed HISTORY with the values found in TODAY in an additive way. I think that the pseudocode would be like this: * IF TODAY.id EXISTS IN HISTORY.id - THEN UPDATE HISTORY.val = HISTORY.val + TODAY.val * ELSE UPDATE HISTORY.id = TODAY.id, HISTORY.val = TODAY.val ... you see now why I called it an additive UPDATE? :) Is there a way to perform this with just MySQL or do I need to combine it with a programming language? Thanks in advance, Hector Ok, here's what I've done so far... maybe someone could find this useful :) The magic is in understanding JOIN's! The main reason why I installed MySQL 4.1.0-alpha was because of sub-selects (since I had no idea they where special cases of JOIN's and they are slower than JOIN's too!) So, I'll try to use REPLACE later (so I don't have to query the last UNION SELECT). Of course, any comments are welcome! Hector # [mysql_localhost] Query Window # Connection: mysql_localhost # Host: localhost # Saved: 2003-11-05 11:45:25 # # Query: # select a.tel, a.telefb, a.rutaentran, a.rutasalien, a.minutos + b.minutos as total # from grp_oper_hist as a join grp_oper_hoy as b using(tel, telefb, rutaentran, rutasalien) # union # select a.* from grp_oper_hoy as a left join grp_oper_hist as b using(tel, telefb, rutaentran, rutasalien)where b.minutos is null # union # select a.* from grp_oper_hist as a left join grp_oper_hoy as b using(tel, telefb, rutaentran, rutasalien)where b.minutos is null # 'tel','telefb','rutaentran','rutasalien','total' '000','120','PCS27LI','PAR37UO','4' '000','122','PCS27LI','CEN47UO','2' '000','123','PCS27LI','GDV57UO','6' '0006429','123','BELL7CI','GDV57UO','3' '000','110','PCS27LI','PAR37UO','3' '287','120','BELL7CI','PAR37UO','13' '287','123','BELL7CI','GDV57UO','2' '0002407','123','PCS27LI','GDV57UO','3' '0003076','123','BELL7CI','GDV57UO','2' '0006429','123','PCS27LI','GDV57UO','1' '0009210','122','BELL7CI','CEN47UO','1' . -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: another insert select question
Jason Joines wrote: Egor Egorov wrote: Jason Joines [EMAIL PROTECTED] wrote: Table employees: | idnumber | email | phone | address | Table webusers: - | idnumber | userid | website | - Table employees is completely populated. Table webusers has the idnumber and website fields completely populated. What query can I use to insert the email address from each persones record in the employees table into the userid field of their corresponding record in the webusers table? If I've got you right you need UPDATE, not INSERT .. SELECT. UPDATE webuser, employees SET webuser.userid=employees.email WHERE webuser.idnumber=employees.idnumber; The above UPDATE statement will work from v4.0.4. You've got it right, I was way off trying to use an INSERT instead of an UPDATE. These tables were an example where I was trying to get the syntax right before using it on the real thing. I thought I had to use two tables to make it work. The real thing is one table called on a 3.23.48 server. One of it's fields is an email address (email) and one is the userid. The primary key is idnumber. I need to populate the userid field from the email address field. I can get the userid using: SELECT substring(per_email_address,1,instr(per_email_address,'@')-1) from employees; but I haven't been able to get the results into the userid field. Any ideas? Thanks, Jason === Turns out I need UPDATE SET UPDATE employees SET userid=substring(email,1,instr(email,'@')-1); Solution provided to me in the an update select question thread. Jason === -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB and raw tablespace
No, files can be bigger than 2GB. In OSX prior to Panther there is a 2GB per-process memory limit though. Then again, on anything other than the PowerMac G5 this doesn't matter because the G5 is the only Mac that can hold more than 2GB of RAM. - Gabriel On Tuesday, November 4, 2003, at 04:42 PM, Chris Nolan wrote: 2GB limit? On MacOS X? On almost every OS I've played with lately, the file size limit is massive - as in far beyond what disc capacity today will allow. Does MacOS X have a 2GB limit? Regards, Chris On Wed, 5 Nov 2003 04:03 am, Mark Lubratt wrote: On Tuesday, November 4, 2003, at 10:25 AM, Harald Fuchs wrote: In article [EMAIL PROTECTED], Mark Lubratt mark dot lubratt at indeq dot com writes: I'm considering this option to keep database maintenance to a minimum (running out of tablespace issues). That way, InnoDB already owns all the disk space and I don't have to continually be adding tablespace files. Huh? What's wrong with :autoextend? :autoextend works great until the 2GB file limit is reached. Then you have to add another autoextending tablespace file. If I can just make a large raw tablespace, then I don't have to bother with adding additional tablespace files every so often. -- 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]
Delphi MySQL
Hello lists,
Hello list,
This is my third re-mail on this topic.
I am a very new newbie to MySQL and to OOP, Delphi, ODBC, ADO, and Access,
etc etc.
I have a very basic project using OOP, Delphi (6), ODBC, ADO Data Aware
Components, Access and MySQL(4.1).
In fact I have two projects, identical except that one connects via odbc to
Access and the other to MySQL.
Briefly:
The project connects with TADOConnection, TADOTable and TDataSource, and
consists of two tab sheets, one with a DBGrid and DBNavigator1 (Read only),
and the other with an assortment of DBEdits, a DBMemo and a DbNavigator2,
this with full read/write options.
Attempts to write to Access succeed where attempts to write to MySQL fail,
all other things being equal. The MySQL error return states:
Check the manual that corresponds to your MySQL server version for the
right syntax to use near ','detail' SET '('
So far as I can see, all MySQL permissions exist for read/write access to
the DB.
My confusion arises from the fact that to give entry level experience, I
chose data aware controls which I presumed would not need coding to function
at a basic level. This works with Access but apparently not with MySQL.
Clearly I am missing something fundamental regarding differences between
MySQL's and Access's interactions with data aware components.
Can anyone suggest what it is that I am missing and how can I coax my
project to write to MySQL?
Please can you quote my Ref 'Query 2003048'.
With thanks in anticipation,
john
John Barrington
[EMAIL PROTECTED]
+27 11 6489876
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.528 / Virus Database: 324 - Release Date: 16/10/03
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can dbx update Tables asynchronous, like ADO.
If you are referring to the dbx database abstraction layer extension for PHP, it is literally just a C-level wrapper for the database extensions it supports. If PHP's MySQL extension supports asynchronous table updates, then dbx will. What exactly are these asynchronous table updates anyways? - Gabriel On Wednesday, November 5, 2003, at 12:53 AM, sowee wrote: Can dbx update Tables asynchronous, like ADO. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
switch from gemini to innodb bottlnecks
I've recently switched from GEMINI to INNODB, to be able to get continued support and upgrades of MySQL. I'm running into to some major performance issues. The section in the MySQL manual is not very thorough on optimizing INNODB. I'm getting two problems, either all my queries pile up, or a 'show processlist' shows 50 sleeping processes that should be hard at work, either way everything comes to a crawl. The database server is a dedicated 2CPU pIII 2.6Ghz, 2GB RAM, with a 14 hardrive (10k) Raid5 array. Pretty much every query uses indexed fields, so things should be fast, I get the impression that there is a locking issue going on, but as I understood it, INNDOB is supposed to do row level locking? I haven't had this problem with GEMINI in the past. Can anyone give me some improvement suggestions? = 031105 10:51:32 INNODB MONITOR OUTPUT = Per second averages calculated from the last 2 seconds -- SEMAPHORES -- OS WAIT ARRAY INFO: reservation count 3528797, signal count 2864755 --Thread 1105938 has waited at btr0cur.c line 390 for 1.00 seconds the semaphore: S-lock on RW-latch at 92b43f74 created in file buf0buf.c line 444 a writer (thread id 1105938) has reserved it in mode exclusive number of readers 0, waiters flag 1 Last time read locked in file btr0cur.c line 390 Last time write locked in file buf0buf.c line 1404 --Thread 1134643 has waited at btr0cur.c line 390 for 0.00 seconds the semaphore: S-lock on RW-latch at 92fa9b84 created in file buf0buf.c line 444 a writer (thread id 167960) has reserved it in mode exclusive number of readers 0, waiters flag 1 Last time read locked in file btr0cur.c line 390 Last time write locked in file buf0buf.c line 1404 --Thread 1069100 has waited at btr0cur.c line 390 for 0.00 seconds the semaphore: S-lock on RW-latch at 92b3c734 created in file buf0buf.c line 444 a writer (thread id 1294399) has reserved it in mode exclusive number of readers 0, waiters flag 1 Last time read locked in file btr0cur.c line 390 Last time write locked in file buf0buf.c line 1404 --Thread 1294399 has waited at btr0cur.c line 390 for 0.00 seconds the semaphore: S-lock on RW-latch at 92b3c734 created in file buf0buf.c line 444 a writer (thread id 1294399) has reserved it in mode exclusive number of readers 0, waiters flag 1 Last time read locked in file btr0cur.c line 390 Last time write locked in file buf0buf.c line 1404 --Thread 1282108 has waited at btr0cur.c line 390 for 0.00 seconds the semaphore: S-lock on RW-latch at 922d78f4 created in file buf0buf.c line 444 a writer (thread id 1282108) has reserved it in mode exclusive number of readers 0, waiters flag 1 Last time read locked in file btr0sea.c line 753 Last time write locked in file buf0buf.c line 1404 Mutex spin waits 96547444, rounds 309622046, OS waits 1589531 RW-shared spins 3471561, OS waits 1434935; RW-excl spins 1022533, OS waits 145621 TRANSACTIONS *** LOTS OF STUFF HERE *** FILE I/O I/O thread 0 state: waiting for i/o request (insert buffer thread) I/O thread 1 state: waiting for i/o request (log thread) I/O thread 2 state: doing file i/o (read thread) I/O thread 3 state: waiting for i/o request (write thread) Pending normal aio reads: 192, aio writes: 0, ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 0 3135978 OS file reads, 80521 OS file writes, 41212 OS fsyncs 4 pending preads, 0 pending pwrites 223.89 reads/s, 72228 avg bytes/read, 1.00 writes/s, 1.00 fsyncs/s - INSERT BUFFER AND ADAPTIVE HASH INDEX - Ibuf for space 0: size 31, free list len 29, seg size 61, 110608 inserts, 100175 merged recs, 14569 merges Hash table size 4980539, used cells 665918, node heap has 702 buffer(s) 7813.59 hash searches/s, 3283.86 non-hash searches/s --- LOG --- Log sequence number 18 429778985 Log flushed up to 18 429778985 Last checkpoint at 18 429608118 0 pending log writes, 0 pending chkp writes 22237 log i/o's done, 2.00 log i/o's/second -- BUFFER POOL AND MEMORY -- Total memory allocated 1470181283; in additional pool allocated 9935488 Buffer pool size 76800 Free buffers 0 Database pages 76084 Modified db pages 168 Pending reads 131 Pending writes: LRU 0, flush list 0, single page 0 Pages read 16098598, created 12247, written 85799 1923.08 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 977 / 1000 -- ROW OPERATIONS -- 8 queries inside InnoDB, 48 queries in queue Main thread process no. 1225, id 28680, state: sleeping Number of rows inserted 292454, updated 132038, deleted 139429, read 359279518 0.50 inserts/s, 0.50 updates/s, 1.00 deletes/s, 12631.68 reads/s END OF INNODB MONITOR OUTPUT my.cnf #
Benchmark differences: Mac OS X - Linux
Hi everyone, I recently ran the MySQL benchmark suite on a Dual 1 GHz G4 running Mac OS X Server 10.2.8, and an 800 MHz Intel machine running SuSE Linux 8.0. Both installations used the same my.cnf file. The results are comparable in all benchmarks except one: the 'insert'. In that one, the Mac is more than twice as slow. Below are the benchmark results for both machines, and the my.cnf I used. I was wondering if there is something I can do, configuration-wise, to do something about those very slow 'inserts' (and 'updates') on the Mac? Thanks in advance for any insight, Jan Pieter Kunst Benchmark results: Dual 1GHz G4/Mac OS X Client 10.2/MySQL 3.23.57: === alter-table: Total time: 186 wallclock secs ATIS: Total time: 37 wallclock secs big-tables: Total time: 78 wallclock secs connect: Total time: 57 wallclock secs create: Total time: 118 wallclock secs insert: Total time: 6811 wallclock secs select: Total time: 1340 wallclock secs wisconsin: Total time: 21 wallclock secs ... snip ... insert 137.00 insert_duplicates 23.00 insert_key 3099.00 insert_many_fields10.00 insert_select_1_key4.00 insert_select_2_keys 6.00 ... snip ... update_big93.00 update_of_key180.00 update_of_key_big 29.00 update_of_primary_key_many_keys 478.00 update_with_key 228.00 update_with_key_prefix22.00 800 Mhz Intel/SuSE Linux 8.0/MySQL 3.23.57: === alter-table: Total time: 230 wallclock secs ATIS: Total time: 36 wallclock secs big-tables: Total time: 32 wallclock secs connect: Total time: 89 wallclock secs create: Total time: 90 wallclock secs insert: Total time: 2895 wallclock secs select: Total time: 1502 wallclock secs wisconsin: Total time: 20 wallclock secs ... snip insert 190.00 insert_duplicates 39.00 insert_key 140.00 insert_many_fields10.00 insert_select_1_key5.00 insert_select_2_keys 7.00 ... snip update_big33.00 update_of_key 35.00 update_of_key_big 22.00 update_of_primary_key_many_keys 28.00 update_with_key 178.00 update_with_key_prefix44.00 These are hopefully relevant parts of the my.cnf I used: [mysqld] skip-locking set-variable = key_buffer=64M set-variable = max_allowed_packet=1M set-variable = table_cache=256 set-variable = sort_buffer=4M set-variable = net_buffer_length=8K set-variable = record_buffer=1M set-variable = thread_cache=8 set-variable = myisam_sort_buffer_size=8M log-bin server-id = 1 [mysqldump] quick set-variable = max_allowed_packet=16M [mysql] no-auto-rehash [isamchk] set-variable = key_buffer=20M set-variable = sort_buffer=20M set-variable = read_buffer=2M set-variable = write_buffer=2M [myisamchk] set-variable = key_buffer=20M set-variable = sort_buffer=20M set-variable = read_buffer=2M set-variable = write_buffer=2M -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Delphi MySQL
Hello lists,
This amends my third re-mail on this topic.
Sorry about that: A point to clarify is that the writes are done using the
update button of the DbNavigator.
I am a very new newbie to MySQL and to OOP, Delphi, ODBC, ADO, and Access,
etc etc.
I have a very basic project using OOP, Delphi (6), ODBC, ADO Data Aware
Components, Access and MySQL(4.1).
In fact I have two projects, identical except that one connects via odbc to
Access and the other to MySQL.
Briefly:
The project connects with TADOConnection, TADOTable and TDataSource, and
consists of two tab sheets, one with a DBGrid and DBNavigator1 (Read only),
and the other with an assortment of DBEdits, a DBMemo and a DbNavigator2,
this with full read/write options.
Attempts to write to Access succeed where attempts to write to MySQL fail,
all other things being equal. The MySQL error return states:
Check the manual that corresponds to your MySQL server version for the
right syntax to use near ','detail' SET '('
So far as I can see, all MySQL permissions exist for read/write access to
the DB.
My confusion arises from the fact that to give entry level experience, I
chose data aware controls which I presumed would not need coding to function
at a basic level. This works with Access but apparently not with MySQL.
Clearly I am missing something fundamental regarding differences between
MySQL's and Access's interactions with data aware components.
Can anyone suggest what it is that I am missing and how can I coax my
project to write to MySQL?
Please can you quote my Ref 'Query 2003048'.
With thanks in anticipation,
john
John Barrington
[EMAIL PROTECTED]
+27 11 6489876
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.528 / Virus Database: 324 - Release Date: 16/10/03
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Benchmark differences: Mac OS X - Linux
I'm not entirely sure what to do about the slow insert results, they are the slowest part no matter how you configure it, it seems. I've attached some benchmark results I ran on a dual 2GHz G5 for comparison. Both MyISAM and InnoDB. Here are the insert results though: MyISAM: insert: Total time: 1437 wallclock secs (432.84 usr 358.12 sys + 0.00 cusr 0.00 csys = 790.96 CPU) InnoDB: insert: Total time: 3270 wallclock secs (440.53 usr 361.82 sys + 0.00 cusr 0.00 csys = 802.35 CPU) - Gabriel Benchmark DBD suite: 2.14 Date of test:2003-10-17 11:15:04 Running tests on:Darwin 7.0.0 Power Macintosh Arguments: Comments: Limits from: Server version: MySQL 4.0.15aIMAX G5 debug log Optimization:None Hardware:PowerMac G5 2 x 2GHz , 4GB PC3200 RAM (8x256MB Kingston HyperX), 160GB SATA HD alter-table: Total time: 69 wallclock secs ( 0.01 usr 0.03 sys + 0.00 cusr 0.00 csys = 0.04 CPU) ATIS: Total time: 27 wallclock secs ( 9.31 usr 9.93 sys + 0.00 cusr 0.00 csys = 19.24 CPU) big-tables: Total time: 37 wallclock secs ( 8.10 usr 20.83 sys + 0.00 cusr 0.00 csys = 28.93 CPU) connect: Total time: 182 wallclock secs (46.25 usr 54.08 sys + 0.00 cusr 0.00 csys = 100.33 CPU) create: Total time: 197 wallclock secs ( 5.71 usr 2.65 sys + 0.00 cusr 0.00 csys = 8.36 CPU) insert: Total time: 3270 wallclock secs (440.53 usr 361.82 sys + 0.00 cusr 0.00 csys = 802.35 CPU) select: Total time: 222 wallclock secs (44.41 usr 32.55 sys + 0.00 cusr 0.00 csys = 76.96 CPU) transactions: Test skipped because the database doesn't support transactions wisconsin: Total time: 28 wallclock secs ( 2.99 usr 2.73 sys + 0.00 cusr 0.00 csys = 5.72 CPU) All 9 test executed successfully Totals per operation: Operation seconds usr sys cpu tests alter_table_add 19.000.000.000.00 20 alter_table_drop 17.000.010.000.01 20 connect 10.005.431.697.12 1 connect+select_1_row 15.006.522.579.09 1 connect+select_simple 12.006.181.878.05 1 count 39.000.060.000.06 100 count_distinct 1.000.190.030.221000 count_distinct_big26.009.11 10.75 19.86 120 count_distinct_group 2.001.060.601.661000 count_distinct_group_on_key0.000.230.070.301000 count_distinct_group_on_key_parts 2.001.070.621.691000 count_distinct_key_prefix 0.000.140.100.241000 count_group_on_key_parts 2.001.250.381.631000 count_on_key 31.007.592.98 10.57 50100 create+drop 57.001.570.712.28 1 create_MANY_tables38.001.050.471.52 1 create_index 16.000.000.000.00 8 create_key+drop 61.001.920.562.48 1 create_table 0.000.000.000.00 31 delete_all_many_keys 171.000.000.010.01 1 delete_big 0.000.000.000.00 1 delete_big_many_keys 171.000.000.010.01 128 delete_key 5.000.240.320.56 1 delete_range 25.000.000.000.00 12 drop_index16.000.000.000.00 8 drop_table 1.000.000.000.00 28 drop_table_when_MANY_tables 36.000.680.381.06 1 insert 271.009.92 10.35 20.27 350768 insert_duplicates 18.002.303.075.37 10 insert_key 371.005.263.318.57 10 insert_many_fields 7.000.290.040.332000 insert_select_1_key7.000.000.000.00 1 insert_select_2_keys 12.000.000.000.00 1 min_max 12.000.000.010.01 60 min_max_on_key22.00 12.484.77 17.25 85000 multiple_value_insert 19.000.290.030.32 10 order_by_big 67.00 26.36 33.24 59.60 10 order_by_big_key 66.00 27.64 33.13 60.77 10 order_by_big_key2 65.00 26.27 34.13 60.40 10 order_by_big_key_desc 66.00 27.11 33.51 60.62 10 order_by_big_key_diff 64.00 26.85
Re: Deadlock
Christophe, - Original Message - From: Christophe Lombart [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Tuesday, November 04, 2003 10:45 PM Subject: Deadlock Hello, I'm using MySQL 4.0.15 standard - InnoDB and I got a deadlock. I don't see why ! How Can I find the reason ? It seems to occurs on delete statments. this transaction wants to get a lock: DELETE FROM SLIDE_REVISION_PREDECESSOR WHERE REVISION_ID = 101 but this trx: update SLIDE_URI set PARENT_URI_ID = 1 where URI_ID = 101 holds a shared lock on the page supremum: test/SLIDE_REVISION_PREDECESSOR index PRIMARY trx id 0 120070 lock mode S Record lock, heap no 1 RECORD: info bits 0 0: len 9; hex 73757072656d756d00; asc supremum.;; Why does the second trx have an S-lock? Have you used SELECT ... LOCK IN SHARE MODE; or are there FOREIGN KEY constraints which reference SLIDE_REVISION_PREDECESSOR? Your transactions set quite many locks: 27 lock struct(s), 43 lock struct(s), Can you cut your transactions into smaller pieces, so that they would keep less locks? Do you have good indexes for the WHERE conditions? This bug fix in 4.0.16 may have relevance: Fixed a bug: contrary to what was said in the manual, in a locking read InnoDB set two record locks if a unique exact match search condition was used on a multi-column unique key. For a single column unique key it worked right. Regards, Christophe Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - hot backup tool for InnoDB which also backs up MyISAM tables Order MySQL technical support from https://order.mysql.com/ Here is my InnoDB status : = Per second averages calculated from the last 51 seconds -- SEMAPHORES -- OS WAIT ARRAY INFO: reservation count 539, signal count 189 Mutex spin waits 800, rounds 10240, OS waits 328 RW-shared spins 370, OS waits 188; RW-excl spins 35, OS waits 23 LATEST DETECTED DEADLOCK 031104 21:39:32 *** (1) TRANSACTION: TRANSACTION 0 119492, ACTIVE 7 sec, process no 3109, OS thread id 188446 starting index read mysql tables in use 1, locked 1 LOCK WAIT 27 lock struct(s), heap size 2496, undo log entries 42 MySQL thread id 38, query id 12022 localhost 127.0.0.1 root updating DELETE FROM SLIDE_REVISION_PREDECESSOR WHERE REVISION_ID = 101 *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 192 n bits 72 table test/SLIDE_REVISION_PREDECESSOR index PRIMARY trx id 0 119492 lock_mode X waiting Record lock, heap no 1 RECORD: info bits 0 0: len 9; hex 73757072656d756d00; asc supremum.;; *** (2) TRANSACTION: TRANSACTION 0 120070, ACTIVE 4 sec, process no 3095, OS thread id 131089 starting index read, thread declared inside InnoDB 500 mysql tables in use 1, locked 1 43 lock struct(s), heap size 5504, undo log entries 21 MySQL thread id 24, query id 12050 localhost 127.0.0.1 root Updating update SLIDE_URI set PARENT_URI_ID = 1 where URI_ID = 101 *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 0 page no 192 n bits 72 table test/SLIDE_REVISION_PREDECESSOR index PRIMARY trx id 0 120070 lock mode S Record lock, heap no 1 RECORD: info bits 0 0: len 9; hex 73757072656d756d00; asc supremum.;; *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 45 n bits 312 table test/SLIDE_URI index PRIMARY trx id 0 120070 lock_mode X locks rec but not gap waiting Record lock, heap no 245 RECORD: info bits 0 0: len 4; hex 8065; asc ...e;; 1: len 6; hex 0001d2c4; asc ..;; *** WE ROLL BACK TRANSACTION (2) TRANSACTIONS Trx id counter 0 120984 Purge done for trx's n:o 0 120974 undo n:o 0 0 Total number of lock structs in row lock hash table 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 120878, not started, process no 3112, OS thread id 200720 MySQL thread id 41, query id 16537 localhost 127.0.0.1 root ---TRANSACTION 0 120764, not started, process no 3111, OS thread id 196640 MySQL thread id 40, query id 16625 localhost 127.0.0.1 root ---TRANSACTION 0 120916, not started, process no 3110, OS thread id 192543 MySQL thread id 39, query id 17002 localhost 127.0.0.1 root ---TRANSACTION 0 120809, not started, process no 3108, OS thread id 184349 MySQL thread id 37, query id 16538 localhost 127.0.0.1 root ---TRANSACTION 0 120898, not started, process no 3107, OS thread id 180252 MySQL thread id 36, query id 17001 localhost 127.0.0.1 root ---TRANSACTION 0 120920, not started, process no 3106, OS thread id 176155 MySQL thread id 35, query id 17004 localhost 127.0.0.1 root ---TRANSACTION 0 120052, not started, process no 3105, OS thread id 172058 MySQL thread id 34, query id 11808 localhost 127.0.0.1 root ---TRANSACTION 0 120070, not started, process no 3095, OS thread id 131089 MySQL thread id 24, query id
Re: Benchmark differences: Mac OS X - Linux
I'd be curious what the specs of the hard drives are. Using the stock drives in the Mac means you are using a drive that's about average (2MB cache, 7200RPM). I would assume they are both ATA/IDE drives. But I would guess the bottleneck is the drive. Try running top when you are running your tests to see where the bottleneck is. OSX is also a work in progress, I/O is a big area that Apple is improving on. I thought I remember reading that 10.2 was just reaching the throughput you would get in OS9. I'd be curious what kind of numbers Panther shows. Once I get my xServe setup, just arrived, I'll try running some tests myself. On Wednesday, November 5, 2003, at 01:57 PM, Jan Pieter Kunst wrote: Hi everyone, I recently ran the MySQL benchmark suite on a Dual 1 GHz G4 running Mac OS X Server 10.2.8, and an 800 MHz Intel machine running SuSE Linux 8.0. Both installations used the same my.cnf file. The results are comparable in all benchmarks except one: the 'insert'. In that one, the Mac is more than twice as slow. Below are the benchmark results for both machines, and the my.cnf I used. I was wondering if there is something I can do, configuration-wise, to do something about those very slow 'inserts' (and 'updates') on the Mac? -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: switch from gemini to innodb bottlnecks
Nihal, FILE I/O ... 223.89 reads/s, 72228 avg bytes/read -- BUFFER POOL AND MEMORY -- ... Pending reads 131 ... 1923.08 reads/s, 0.00 creates/s, 0.00 writes/s it is disk-read-bound. It is reading a whopping 1900 pages per second! That is 30 MB/s. Do you have many SELECT COUNT(*) FROM tablename queries? InnoDB scans the whole table to get the row count of a table. If the tables do not change often, and you run the SELECT COUNT(*) in the AUTOCOMMIT=1 mode, then the MySQL query cache will help. [mysqld] set-variable=query_cache_size=256M set-variable=query_cache_limit=2M set-variable=query_cache_type=1 In 4.1.1, the query cache works also in the AUTOCOMMIT=0 mode. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - hot backup tool for InnoDB which also backs up MyISAM tables Order MySQL technical support from https://order.mysql.com/ - Original Message - From: Nihal [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Wednesday, November 05, 2003 8:38 PM Subject: switch from gemini to innodb bottlnecks I've recently switched from GEMINI to INNODB, to be able to get continued support and upgrades of MySQL. I'm running into to some major performance issues. The section in the MySQL manual is not very thorough on optimizing INNODB. I'm getting two problems, either all my queries pile up, or a 'show processlist' shows 50 sleeping processes that should be hard at work, either way everything comes to a crawl. The database server is a dedicated 2CPU pIII 2.6Ghz, 2GB RAM, with a 14 hardrive (10k) Raid5 array. Pretty much every query uses indexed fields, so things should be fast, I get the impression that there is a locking issue going on, but as I understood it, INNDOB is supposed to do row level locking? I haven't had this problem with GEMINI in the past. Can anyone give me some improvement suggestions? = 031105 10:51:32 INNODB MONITOR OUTPUT = Per second averages calculated from the last 2 seconds -- SEMAPHORES -- OS WAIT ARRAY INFO: reservation count 3528797, signal count 2864755 --Thread 1105938 has waited at btr0cur.c line 390 for 1.00 seconds the semaphore: S-lock on RW-latch at 92b43f74 created in file buf0buf.c line 444 a writer (thread id 1105938) has reserved it in mode exclusive number of readers 0, waiters flag 1 Last time read locked in file btr0cur.c line 390 Last time write locked in file buf0buf.c line 1404 --Thread 1134643 has waited at btr0cur.c line 390 for 0.00 seconds the semaphore: S-lock on RW-latch at 92fa9b84 created in file buf0buf.c line 444 a writer (thread id 167960) has reserved it in mode exclusive number of readers 0, waiters flag 1 Last time read locked in file btr0cur.c line 390 Last time write locked in file buf0buf.c line 1404 --Thread 1069100 has waited at btr0cur.c line 390 for 0.00 seconds the semaphore: S-lock on RW-latch at 92b3c734 created in file buf0buf.c line 444 a writer (thread id 1294399) has reserved it in mode exclusive number of readers 0, waiters flag 1 Last time read locked in file btr0cur.c line 390 Last time write locked in file buf0buf.c line 1404 --Thread 1294399 has waited at btr0cur.c line 390 for 0.00 seconds the semaphore: S-lock on RW-latch at 92b3c734 created in file buf0buf.c line 444 a writer (thread id 1294399) has reserved it in mode exclusive number of readers 0, waiters flag 1 Last time read locked in file btr0cur.c line 390 Last time write locked in file buf0buf.c line 1404 --Thread 1282108 has waited at btr0cur.c line 390 for 0.00 seconds the semaphore: S-lock on RW-latch at 922d78f4 created in file buf0buf.c line 444 a writer (thread id 1282108) has reserved it in mode exclusive number of readers 0, waiters flag 1 Last time read locked in file btr0sea.c line 753 Last time write locked in file buf0buf.c line 1404 Mutex spin waits 96547444, rounds 309622046, OS waits 1589531 RW-shared spins 3471561, OS waits 1434935; RW-excl spins 1022533, OS waits 145621 TRANSACTIONS *** LOTS OF STUFF HERE *** FILE I/O I/O thread 0 state: waiting for i/o request (insert buffer thread) I/O thread 1 state: waiting for i/o request (log thread) I/O thread 2 state: doing file i/o (read thread) I/O thread 3 state: waiting for i/o request (write thread) Pending normal aio reads: 192, aio writes: 0, ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 0 3135978 OS file reads, 80521 OS file writes, 41212 OS fsyncs 4 pending preads, 0 pending pwrites 223.89 reads/s, 72228 avg bytes/read, 1.00 writes/s, 1.00 fsyncs/s - INSERT BUFFER AND ADAPTIVE HASH INDEX - Ibuf for space 0: size
Re: InnoDB and raw tablespace
On Wed, Nov 05, 2003 at 12:08:29PM +1100, Chris Nolan wrote: To my knowledge, ext2 does have the [2GB filesize] limitation but ext3 does not. ext2 does not have this limitation. It was never a limitation of the filesystem, only kernel/glibc. On 64bit architectures ext2 has been handling large files for the past eight(?) years. On 32 bit architectures the kernel and libc have been handling large files on ext2 for at least two years. I hate to keep posting the same thing to this list, but I keep seeing the same misinformation that ext2 can't handle large files. It can. Cheers, --Pete -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
INNODB flush holdup
OK, I've got another quandary, thanks for the help on the last one, I'm working on it. | 15203 | root | web:36810 | ez2000 | Query | 137 | Sorting result| SELECT from table_a | | 15235 | root | web:36849 | ez2000 | Query | 67 | Sorting result| SELECT from table_a | | 15244 | root | db:32779 | NULL | Refresh | 42 | Flushing tables | NULL| | 15246 | root | runque1:3220 | ez2000 | Query | 40 | Waiting for table | insert into table_a | | 15247 | root | runque1:3221 | ez2000 | Sleep | 2| | NULL | | 15250 | root | web:36863 | ez2000 | Query | 34 | Waiting for table | SELECT from table_a | | 15251 | root | web:36864 | ez2000 | Query | 34 | Waiting for table | select from table_b | What would cause the flush tables to take so long? Have I made my log files to large? Does the flush lock all queries until complete? And do any prior queries have to finish before it will start? Sorry, I'm new to INNODB, thanks in advance for the help. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Unicode with MySQL4.1
Hi, I have installed XFree86 4.3 and started xterm with xterm -fn -Misc-Fixed-Medium-R-SemiCondensed--13-120-75-75-C-60-ISO10646-1 Then started mysql with mysql --default-character-set=utf8 Set LC_CTYPE to en_US.UTF-8 on my SuSE 8 machine. Still I am not able to display Unicode characters. I tried select convert(_ucs2 0x0400 using utf8); +--+ | convert(_ucs2 0x213e using utf8) | +--+ | ?| +--+ 1 row in set (0.01 sec) This is working though, select convert(_ucs2 0x00D4 using utf8); +--+ | convert(_ucs2 0x00D4 using utf8) | +--+ | Ô| +--+ 1 row in set (0.01 sec) I appreciate your help Thanks, Srinivas = Its what inside that makes the difference -- Lance Armstrong __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Upgrading help please
Good day List, I have just upgraded from 3.23 -4.0.16 I downloaded all the RPM's and then ran rpm -U *.rpm It did all that it was supposed to do, and then told me to use the /usr/bin/mysql_fix_privilege_tables script which I did got horrid errors ERROR 2002: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) So I specified mysql_fix_privilege_tables root_password Same Errors (2002) Tried the other method that is on the mysql manual mysql_fix_privilege_tables --password=root_password Same error 2002. now Unfortunately I am unable to even connect to mysql with either mysqladmin or the client. MySQL-bench-4.0.16-0.i386.rpm MySQL-client-4.0.16-0.i386.rpm MySQL-devel-4.0.16-0.i386.rpm MySQL-embedded-4.0.16-0.i386.rpm MySQL-Max-4.0.16-0.i386.rpm MySQL-server-4.0.16-0.i386.rpm MySQL-shared-4.0.16-0.i386.rpm MySQL-shared-compat-4.0.16-0.i386.rpm is a list of the RPMS that I downloaded and ran. Any ideas and help would be most appreciated. Thank you Andrew Message sent using UebiMiau 2.7.2 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mysqk Connections problems
HI All I was wondering if anyone could tell me how to get around this problems Error: could not connect to the database. It's possible the database itself is just not working at the moment. The admin should also check that the database details have been correctly specified in config.php Database host: localhost Database name: Database user: It seems that the database is loosing connections This has all started happening since going from Redhat 8 to Redhat 9, which also means going from mysql version: 3.23.52 to version: 3.23.56. Cheers Trevor
Re: InnoDB and raw tablespace
If I recall correctly, the G5, the mighty PowerPC 970, is used by Apple just as Windows currently uses the mighty Hammer series from AMD - as a souped up 32-bit processor. Regards, Chris Gabriel Ricard wrote: No, files can be bigger than 2GB. In OSX prior to Panther there is a 2GB per-process memory limit though. Then again, on anything other than the PowerMac G5 this doesn't matter because the G5 is the only Mac that can hold more than 2GB of RAM. - Gabriel On Tuesday, November 4, 2003, at 04:42 PM, Chris Nolan wrote: 2GB limit? On MacOS X? On almost every OS I've played with lately, the file size limit is massive - as in far beyond what disc capacity today will allow. Does MacOS X have a 2GB limit? Regards, Chris On Wed, 5 Nov 2003 04:03 am, Mark Lubratt wrote: On Tuesday, November 4, 2003, at 10:25 AM, Harald Fuchs wrote: In article [EMAIL PROTECTED], Mark Lubratt mark dot lubratt at indeq dot com writes: I'm considering this option to keep database maintenance to a minimum (running out of tablespace issues). That way, InnoDB already owns all the disk space and I don't have to continually be adding tablespace files. Huh? What's wrong with :autoextend? :autoextend works great until the 2GB file limit is reached. Then you have to add another autoextending tablespace file. If I can just make a large raw tablespace, then I don't have to bother with adding additional tablespace files every so often. -- 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: InnoDB and raw tablespace
How about we just all agree that SCO's OSes can't handle large files, and therefore should all be avoided in favour of completely superior OSes, like FreeBSD, OpenBSD, Linux, NetBSD and DOS 2.11 Regards, Chris Pete Harlan wrote: On Wed, Nov 05, 2003 at 12:08:29PM +1100, Chris Nolan wrote: To my knowledge, ext2 does have the [2GB filesize] limitation but ext3 does not. ext2 does not have this limitation. It was never a limitation of the filesystem, only kernel/glibc. On 64bit architectures ext2 has been handling large files for the past eight(?) years. On 32 bit architectures the kernel and libc have been handling large files on ext2 for at least two years. I hate to keep posting the same thing to this list, but I keep seeing the same misinformation that ext2 can't handle large files. It can. Cheers, --Pete -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Upgrading help please
Andrew, I had the same problem and had to stop and restart the servers. Give that a shot. brian On Wed, 2003-11-05 at 17:19, Andrew wrote: Good day List, I have just upgraded from 3.23 -4.0.16 I downloaded all the RPM's and then ran rpm -U *.rpm It did all that it was supposed to do, and then told me to use the /usr/bin/mysql_fix_privilege_tables script which I did got horrid errors ERROR 2002: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) So I specified mysql_fix_privilege_tables root_password Same Errors (2002) Tried the other method that is on the mysql manual mysql_fix_privilege_tables --password=root_password Same error 2002. now Unfortunately I am unable to even connect to mysql with either mysqladmin or the client. MySQL-bench-4.0.16-0.i386.rpm MySQL-client-4.0.16-0.i386.rpm MySQL-devel-4.0.16-0.i386.rpm MySQL-embedded-4.0.16-0.i386.rpm MySQL-Max-4.0.16-0.i386.rpm MySQL-server-4.0.16-0.i386.rpm MySQL-shared-4.0.16-0.i386.rpm MySQL-shared-compat-4.0.16-0.i386.rpm is a list of the RPMS that I downloaded and ran. Any ideas and help would be most appreciated. Thank you Andrew Message sent using UebiMiau 2.7.2 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mysql on panther
Hi guys, I just was given the OK to play with a new server running Mac OS X (Panther) When I give the command (it is recommended to use mysqld_safe instead of mysqld in some website) *--- # /usr/bin/mysqld_safe Starting mysqld daemon with databases from /var/mysql 031105 13:09:40 mysqld ended *--- I look at the .err file in /var/mysql/ and it looks like this: * 031105 13:09:38 mysqld started 031105 13:09:38 InnoDB: Started 031105 13:09:38 Fatal error: Can't open privilege tables: Table 'mysql.host' doesn't exist 031105 13:09:38 Aborting 031105 13:09:38 InnoDB: Starting shutdown... 031105 13:09:40 InnoDB: Shutdown completed 031105 13:09:40 /usr/libexec/mysqld: Shutdown Complete 031105 13:09:40 mysqld ended * I search for the my.cnf and I do not find it, but I know that Mac OS X puts things in different places. Any ideas? Thanks :-) Nestor A. Florez -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL sub-query error.
If it's below 4.0 then you can't use subqueries at all, subqueries were (will be) introduced in 4.1. That query is just a standard query, but the third item will return the value of 'SELECTED' if the userid is equal to 1 and ' ' otherwise. Chris -Original Message- From: Geeta Rajaraman [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 05, 2003 10:00 AM To: Chris Subject: Re: MySQL sub-query error. See..I changed it = only coz the IN wouldn't work. I am guessing its a MySQL version issue. I am using a version below 4.0. I am curious about the second version of my query you wrote ? Can you elaborate ? Chris wrote: I don't think Sub queries like the = and != operators as groupid will never equal the result of a multi row subquery, but it can be IN a list of results. Just to check, you are using 4.1+ right? I think this alteration would work: SELECT group_id,name,'SELECTED' FROM groups WHERE group_id IN (SELECT group_id FROM user_groups WHERE userid= 1) UNION Select group_id,name,' ' FROM groups WHERE group_id NOT IN (SELECT group_id FROM user_groups WHERE userid= 1) ORDER by group_id = That being said, I think you can do it without Subqueries with this query: SELECT group_id, name, IF(1=userid,'SELECTED',' ') FROM groups ORDER BY group_id Chris -Original Message- From: Geeta Rajaraman [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 05, 2003 7:03 AM To: [EMAIL PROTECTED] Subject: MySQL sub-query error. Good Morning everyone: I need help figuring out what is wrong with this query. I have tried to use the LEFT JOIN, but it doesn't solve the purpose. This is what I am trying to run: SELECT group_id,name,'SELECTED' FROM groups WHERE group_id = (SELECT group_id FROM user_groups WHERE userid= 1) UNION Select group_id,name,' ' FROM groups WHERE group_id != (SELECT group_id FROM user_groups WHERE userid= 1) ORDER by group_id = I get this error: You have an error in your SQL syntax near 'SELECT group_id FROM user_groups WHERE userid= 1) union select group_id,name I have run the sub queries individually, and they work. Can anyone help ? I have tried various combinations..but each time I get the same error. 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: Benchmark differences: Mac OS X - Linux
FYI, I've found 'iostat' to be quite useful in monitoring the drive transfer rates while benchmarking in OSX. Then again, in Panther all you really need to do is pop open Activity Monitor (formerly Process Viewer) which now has some nifty graphing for system status: cpu, disk, ram activity and usage, etc. - Gabriel On Wednesday, November 5, 2003, at 03:21 PM, Brent Baisley wrote: I'd be curious what the specs of the hard drives are. Using the stock drives in the Mac means you are using a drive that's about average (2MB cache, 7200RPM). I would assume they are both ATA/IDE drives. But I would guess the bottleneck is the drive. Try running top when you are running your tests to see where the bottleneck is. OSX is also a work in progress, I/O is a big area that Apple is improving on. I thought I remember reading that 10.2 was just reaching the throughput you would get in OS9. I'd be curious what kind of numbers Panther shows. Once I get my xServe setup, just arrived, I'll try running some tests myself. On Wednesday, November 5, 2003, at 01:57 PM, Jan Pieter Kunst wrote: Hi everyone, I recently ran the MySQL benchmark suite on a Dual 1 GHz G4 running Mac OS X Server 10.2.8, and an 800 MHz Intel machine running SuSE Linux 8.0. Both installations used the same my.cnf file. The results are comparable in all benchmarks except one: the 'insert'. In that one, the Mac is more than twice as slow. Below are the benchmark results for both machines, and the my.cnf I used. I was wondering if there is something I can do, configuration-wise, to do something about those very slow 'inserts' (and 'updates') on the Mac? -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- 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: Mysql on panther
Are you using Panther client or server? my.cnf is usually in /etc. Have you run mysql_install_db yet? - Gabriel On Wednesday, November 5, 2003, at 05:57 PM, Nestor Florez wrote: Hi guys, I just was given the OK to play with a new server running Mac OS X (Panther) When I give the command (it is recommended to use mysqld_safe instead of mysqld in some website) *--- # /usr/bin/mysqld_safe Starting mysqld daemon with databases from /var/mysql 031105 13:09:40 mysqld ended *--- I look at the .err file in /var/mysql/ and it looks like this: * 031105 13:09:38 mysqld started 031105 13:09:38 InnoDB: Started 031105 13:09:38 Fatal error: Can't open privilege tables: Table 'mysql.host' doesn't exist 031105 13:09:38 Aborting 031105 13:09:38 InnoDB: Starting shutdown... 031105 13:09:40 InnoDB: Shutdown completed 031105 13:09:40 /usr/libexec/mysqld: Shutdown Complete 031105 13:09:40 mysqld ended * I search for the my.cnf and I do not find it, but I know that Mac OS X puts things in different places. Any ideas? Thanks :-) Nestor A. Florez -- 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]
Removing MySQL from OS X Jaguar
I've installed the latest MySQL from www.mysql.com to my computer, but decided later to try the Complete series of web tools to get access to Apache v2. Complete Apache v2 and Complete PHP is installed, but I have to remove the original install of MySQL to install the Complete MySQL package. As you can guess, I have not figured out how to do this yet. I used the package version, not the tarball. Any suggestions? Thanks! Brian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
how see list of a table's indexes?
Hi Group, Please forgive me if this has an obvious answer, but I couldn't find it in the MySQL v4.1 manual --- how can I see what indexes (and which columns are indexed) for a table? Thanks! Holly
Re: Removing MySQL from OS X Jaguar
G'day Brian I've installed the latest MySQL from www.mysql.com to my computer, but decided later to try the Complete series of web tools to get access to Apache v2. Complete Apache v2 and Complete PHP is installed, but I have to remove the original install of MySQL to install the Complete MySQL package. try: http://www.entropy.ch/software/macosx/mysql/remove-old-mysql.html cheers kim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how see list of a table's indexes?
Hi Holly, SHOW INDEX FROM table; or SHOW CREATE TABLE table; are 2 different ways to see indexes -- in different formats. For reference: http://www.mysql.com/doc/en/Show_database_info.html Hope that helps. Matt - Original Message - From: Holly Chamberlain To: [EMAIL PROTECTED] Sent: Wednesday, November 05, 2003 7:02 PM Subject: how see list of a table's indexes? Hi Group, Please forgive me if this has an obvious answer, but I couldn't find it in the MySQL v4.1 manual --- how can I see what indexes (and which columns are indexed) for a table? Thanks! Holly -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with LIKE/REGEXP
Hi George, What are you actually trying to match with \017? As far as I know, it's treating the \0 part as a NUL byte and trying to match that. Are you trying to match a NUL byte? Or are you trying to match ASCII 17 or something? http://www.mysql.com/doc/en/String_syntax.html Matt - Original Message - From: George Moschovitis To: [EMAIL PROTECTED] Sent: Wednesday, November 05, 2003 7:29 AM Subject: Problem with LIKE/REGEXP Hello everyone! I have a table with a MEDIUMBLOB column: CREATE TABLE mytab ( oid INTEGER ... odata MEDIUMBLOB ); in the mediumblob field i store an encoded string (bytes). I would like to perform regular expression searches on this field here is an example that works (produces matches): SELECT oid FROM mytab WHERE odata LIKE [EMAIL PROTECTED]\ngmosx%; here is an example that doesnt work (no matches returned): SELECT oid FROM mytab WHERE odata LIKE [EMAIL PROTECTED]\017athens1234%; the \017 character seems to be the problem since the following produces matches: SELECT oid FROM mytab WHERE odata LIKE [EMAIL PROTECTED]_athens1234%; I tried using a MEDIUMTEXT and/or REGEXP but without success! Any idea how to do string matches on string-encoded binary data? Thanks in advance for any info! George Moschovitis -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql memory usage
Hi, In every instance I've seen, MySQL always allocates the amount you set for key_buffer at server startup even if it never comes close to being *used*. (It shouldn't be doing malloc()s or whatever for that on the fly. :-)) Same thing for query_cache_size. Now about join, read, and sort buffers, that's correct that they're only allocated when doing those specific operations. BUT, the thing I'm not clear on is whether the amount you have set is allocated all at once or as needed up to the specified size. Of course, if the whole size is needed, it would be faster to allocate it all at once. But if not. Maybe I should ask on the Internals list sometime. Matt - Original Message - From: Alexis Guia Sent: Wednesday, November 05, 2003 5:30 AM Subject: RE: mysql memory usage Sorry, but I disagree :/ I always used 250MB of key buffer, and MySQL never allocates more than 50MB, in my database. Read buffer is only allocated when full scans are done. Join buffer is allocated when there are joins without index use. Sort buffer is allocated when needed, and etc... Alexis P.S.: you can test it easily, doing specific queries for each case. -Original Message- From: Jeremy Zawodny Sent: terça-feira, 4 de Novembro de 2003 23:51 To: Alexis Guia Cc: 'Benjamin KRIEF'; [EMAIL PROTECTED] Subject: Re: mysql memory usage On Tue, Nov 04, 2003 at 10:09:01AM -, Alexis Guia wrote: Hi, I think that MyISAM uses the key buffer only if needed. The same happens with almost all the other buffers (read buffer, sort buffer, etc.). True, but there's a subtle difference between uses and allocates. If you tell MySQL that it has 16GB for a key_buffer, it'll allocate 16GB even if it only ever uses 28KB. The same is true of several (probably all?) other buffers. Jeremy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: how see list of a table's indexes?
Thank you! That's just what I was looking for. -Original Message- From: Matt W [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 05, 2003 5:34 PM To: Holly Chamberlain; [EMAIL PROTECTED] Subject: Re: how see list of a table's indexes? Hi Holly, SHOW INDEX FROM table; or SHOW CREATE TABLE table; are 2 different ways to see indexes -- in different formats. For reference: http://www.mysql.com/doc/en/Show_database_info.html Hope that helps. Matt - Original Message - From: Holly Chamberlain To: [EMAIL PROTECTED] Sent: Wednesday, November 05, 2003 7:02 PM Subject: how see list of a table's indexes? Hi Group, Please forgive me if this has an obvious answer, but I couldn't find it in the MySQL v4.1 manual --- how can I see what indexes (and which columns are indexed) for a table? Thanks! Holly -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table in Memory
Hi, HEAP tables don't currently support TEXT/BLOB columns. My answer about storing the table in memory: don't bother. If you have enough free RAM to use to put the table in memory, the OS will already do it for you after it's accessed. Thus, reading the table (after the first access) should be as fast as putting it on a RAM disk or whatever people might suggest. Matt - Original Message - From: [EMAIL PROTECTED] To: Arnoldus Th.J. Koeleman Cc: [EMAIL PROTECTED] Sent: Wednesday, November 05, 2003 11:03 AM Subject: Re: Table in Memory Maybe look at using a HEAP table? Load it on startup from a datasource.. On Wed, 5 Nov 2003, Arnoldus Th.J. Koeleman wrote: I have a large table which I like to store into memory . Table looks like Spid_1__0 (recordname varchar(20) primary key, data blob not null ) what is the best way todo this in mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql memory usage
On Wed, Nov 05, 2003 at 11:30:46AM -, Alexis Guia wrote: Sorry, but I disagree :/ I always used 250MB of key buffer, and MySQL never allocates more than 50MB, in my database. Read buffer is only allocated when full scans are done. Join buffer is allocated when there are joins without index use. Sort buffer is allocated when needed, and etc... I'm confused. Exactly which of my statements are you disagreeing with? Jeremy P.S.: you can test it easily, doing specific queries for each case. -Original Message- From: Jeremy Zawodny [mailto:[EMAIL PROTECTED] Sent: terça-feira, 4 de Novembro de 2003 23:51 To: Alexis Guia Cc: 'Benjamin KRIEF'; [EMAIL PROTECTED] Subject: Re: mysql memory usage On Tue, Nov 04, 2003 at 10:09:01AM -, Alexis Guia wrote: Hi, I think that MyISAM uses the key buffer only if needed. The same happens with almost all the other buffers (read buffer, sort buffer, etc.). True, but there's a subtle difference between uses and allocates. If you tell MySQL that it has 16GB for a key_buffer, it'll allocate 16GB even if it only ever uses 28KB. The same is true of several (probably all?) other buffers. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 51 days, processed 1,925,645,484 queries (428/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 53 days, processed 1,974,835,485 queries (430/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL process increasing problem..
Hi, Yes, in top, those are threads not processes, as Gerald already said. Not that it really matters. :-) Your connections in PROCESSLIST are sleeping (e.g. idle). What are the clients? It looks like you may be using persistent connections with a Web app. If you want to get rid of the sleeping threads (which shouldn't be much harm, except for a bit of memory usage), don't use persistent connections, or lower the wait_timeout and/or interactive_timeout server variables to have MySQL disconnect idle clients sooner. Hope that helps. Matt - Original Message - From: Jobs PHP Workshop To: [EMAIL PROTECTED] Sent: Wednesday, November 05, 2003 11:08 AM Subject: MySQL process increasing problem.. Hi, Problem is, Mysql server in our production machine keeps spawning child processes and it reaches to a level where mysql hangs and the only solution is to restart our mysql server. I have checked all the databases using mysqlcheck command and every table is OK now. Recently, MySQL server suffered from low disk space and hence some of the tables got corrupted. We have repaired them using mysqlcheck -r option. Can this be a Reason of the mysqld process spawning problem ??. Some help will really save me as this is a production machine. We have MySQL 3.23.x running and Following is more technical details that you may need to think upon: (1) HERE IS THE PARTIAL OUTPUT OF 'TOP' COMMAND: 2:52pm up 1 day, 22:34, 2 users, load average: 0.30, 0.09, 0.03 134 processes: 133 sleeping, 1 running, 0 zombie, 0 stopped CPU states: 0.9% user, 2.7% system, 0.0% nice, 96.3% idle Mem: 1028316K av, 1015484K used, 12832K free, 0K shrd, 180292K buff Swap: 522072K av,4096K used, 517976K free 470328K cached PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND 6097 mysql 9 0 23004 22M 1528 S 0.0 2.2 0:00 mysqld 6099 mysql 8 0 23004 22M 1528 S 0.0 2.2 0:00 mysqld 6100 mysql 9 0 23004 22M 1528 S 0.0 2.2 0:00 mysqld 6105 mysql 9 0 23004 22M 1528 S 0.0 2.2 0:03 mysqld 6108 mysql 9 0 23004 22M 1528 S 0.0 2.2 0:00 mysqld 6109 mysql 9 0 23004 22M 1528 S 0.0 2.2 0:00 mysqld (2) HERE IS THE PARTIAL LIST OF PROCESSES OUT OF TOTAL 94 AS A RESULT OF 'SHOW PROCESSLIST' COMMAND.REST OTHER PROCESSES ARE ALSO SIMILAR: id User Host DatabaseCommand Time Status SQL-query -- -- --- 131 u125 localhost db125 Sleep 6243 --- --- 139 csha localhost db_cshaw_com Sleep 2494 --- --- 140 tanb localhost db_tban_com Sleep 1985 --- --- 141 tanb localhost db_tban_com Sleep 2006 --- --- 142 tanb localhost db_tban_com Sleep 1977 --- --- 143 tanb localhost db_tban_com Sleep 1948 --- --- 144 tanb localhost db_tban_com Sleep 1939 --- --- 145 tanb localhost db_tban_com Sleep 1410 --- --- 146 tanb localhost db_tban_com Sleep 1394 --- --- 147 dbpr localhost dbpropertydb Sleep 1092 --- --- 154 root localhost mysql Query 0 --- SHOW PROCESSLIST (3) HERE IS THE 'extended-status' of MySQL: +--+--+ | Variable_name| Value| +--+--+ | Aborted_clients | 1| | Aborted_connects | 0| | Bytes_received | 6271826 | | Bytes_sent | 18499758 | | Connections | 158 | | Created_tmp_disk_tables | 105 | | Created_tmp_tables | 1174 | | Created_tmp_files| 0| | Delayed_insert_threads | 0| | Delayed_writes | 0| | Delayed_errors | 0| | Flush_commands | 1| | Handler_delete | 283 | | Handler_read_first | 618 | | Handler_read_key | 598750 | | Handler_read_next| 600043 | | Handler_read_prev| 0| | Handler_read_rnd | 20400| | Handler_read_rnd_next| 21962199 | | Handler_update | 1035 | | Handler_write| 22201| | Key_blocks_used | 15582| | Key_read_requests| 1403805 | | Key_reads| 85703| | Key_write_requests | 5742 | | Key_writes | 4974 | | Max_used_connections | 88 | | Not_flushed_key_blocks | 0| | Not_flushed_delayed_rows | 0| | Open_tables | 64 | | Open_files | 131 | | Open_streams | 0| | Opened_tables| 20961| | Questions| 63778| | Select_full_join | 1288 | | Select_full_range_join | 0| | Select_range | 0| | Select_range_check | 0
Re: mysql memory usage
On Wed, Nov 05, 2003 at 07:10:41PM -0600, Matt W wrote: Hi, In every instance I've seen, MySQL always allocates the amount you set for key_buffer at server startup even if it never comes close to being *used*. (It shouldn't be doing malloc()s or whatever for that on the fly. :-)) Same thing for query_cache_size. Yes. Now about join, read, and sort buffers, that's correct that they're only allocated when doing those specific operations. Yes, as documented in the fine manual. BUT, the thing I'm not clear on is whether the amount you have set is allocated all at once or as needed up to the specified size. Of course, if the whole size is needed, it would be faster to allocate it all at once. But if not. I'm 95% sure it's allocated all at once. But a quick scan of the source would reveal the truth. :-) Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 53 days, processed 1,974,935,882 queries (430/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Removing MySQL from OS X Jaguar
Any package that is installed in OSX leaves a receipt behind. Look in /Library/Receipts/. Each package leaves a directory in there. In each package's directory is a .bom file. Usually it's something like /Library/Receipts/PackageName.pkg/Contents/Archive.bom or /Library/Receipts/PackageName.pkg/Contents/Resources/Archive.bom. Find the .bom file an then do 'lsbom -f BomFile.bom' and it will list all of the files it installed on your system. - Gabriel On Wednesday, November 5, 2003, at 07:55 PM, Brian Futrell wrote: I've installed the latest MySQL from www.mysql.com to my computer, but decided later to try the Complete series of web tools to get access to Apache v2. Complete Apache v2 and Complete PHP is installed, but I have to remove the original install of MySQL to install the Complete MySQL package. As you can guess, I have not figured out how to do this yet. I used the package version, not the tarball. Any suggestions? Thanks! Brian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INNODB flush holdup
Hi, I don't know what your previous problem was, but I don't think it affects my answer. :-) This isn't specific to InnoDB. Yes, when you FLUSH TABLES, all new queries wait for that to complete (as indicated by Waiting for table in PROCESSLIST). And the tables can't all be flushed (closed) until all queries complete that were running (the 2 Sorting result queries in your case). Why are those 2 queries running for over 1 and 2 minutes? Must be examining many rows and/or not indexed properly (if there's a WHERE that could use an index)... Hope that helps. Matt - Original Message - From: Nihal To: [EMAIL PROTECTED] Sent: Wednesday, November 05, 2003 3:14 PM Subject: INNODB flush holdup OK, I've got another quandary, thanks for the help on the last one, I'm working on it. | 15203 | root | web:36810 | ez2000 | Query | 137 | Sorting result| SELECT from table_a | | 15235 | root | web:36849 | ez2000 | Query | 67 | Sorting result| SELECT from table_a | | 15244 | root | db:32779 | NULL | Refresh | 42 | Flushing tables | NULL| | 15246 | root | runque1:3220 | ez2000 | Query | 40 | Waiting for table | insert into table_a | | 15247 | root | runque1:3221 | ez2000 | Sleep | 2| | NULL | | 15250 | root | web:36863 | ez2000 | Query | 34 | Waiting for table | SELECT from table_a | | 15251 | root | web:36864 | ez2000 | Query | 34 | Waiting for table | select from table_b | What would cause the flush tables to take so long? Have I made my log files to large? Does the flush lock all queries until complete? And do any prior queries have to finish before it will start? Sorry, I'm new to INNODB, thanks in advance for the help. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA hangs
Hi Eric, I think for ALTER TABLE ... ENABLE KEYS, myisam_sort_buffer_size is all that matters. Not sure about key_buffer... But myisam_sort_buffer is, AFAIK, only used during Repair by sort (in PROCESSLIST), not Repair with keycache. What did yours say during the 21 hours? It may change from sort to keycache after awhile -- something to do with the size of the indexes or something. Not exactly sure about the criteria. No idea about InnoDB, but I would guess that it'd be slower? Does DISBALE/ENABLE KEYS even do anything with InnoDB, or just MyISAM? Hmm. Matt - Original Message - From: Eric Jain To: mysql-general [EMAIL PROTECTED] Sent: Wednesday, November 05, 2003 5:11 AM Subject: Re: LOAD DATA hangs 4) ALTER TABLE ENABLE KEYS - Puts a full load on the CPU, but neither index nor table size seem to change, even after waiting for an hour. Based on smaller data sets, I'd expect the index to reach something close to 4 GB. Update: After several hours, the index file started growing, although very slowly. After another 21 hours or so the indexes were complete. The final index file size is 4.43 GB. I currently have the following options set: key_buffer_size=64M myisam_sort_buffer_size=64MB bulk_insert_buffer_size=64MB Any suggestions what else could be tried in order to get MySQL to scale? Any important options I overlooked? Might InnoDB be faster? -- Eric Jain -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Slow query log setting
Hello, mysql, I have set the slow query parameter in the my.cnf as the following. [safe_mysqld] err-log=/var/log/mysqld.log log-slow-queries=/var/log/mysqlslow.log pid-file=/var/run/mysqld/mysqld.pid After I check the status of mysql today,we found 6 slow query occur. But there is no log in /var/log/mysqlslow.log,why? any idea appreciated. Mysql Version 4.0.12. Best regards. MaFai [EMAIL PROTECTED] 2003-11-06 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Storing utf8 text
Hello list, (B (BI'm writing a small utility in PHP to archive email messages in MySQL, (Bso that I can search through them with full-text indexing. In order to (Bhandle all the various charsets, I was simply converting all text to (Butf8 (using mb_convert_encoding()) before storing it in the database. (B (BI hadn't even considered the charset issue in the database itself until (BI was looking through the MySQL online manual for something else and (Bran across the Unicode chapter. (B (BI did a little experimenting with my current db (MySQL 4.0.14 on Red (BHat 9, character_set=latin1). It will allow me to insert records (Bcontaining unicode characters such as $B'6(B (cyrillic capital letter EF) (Binto varchar and text fields, and select them back out with no problem. (B (BI have a couple of questions about this behavior. (B 1) If I continue to do this, is it possible that MySQL could lose (Bsome wacky characters? (B 2) Do non-latin1 characters muck with searching or sorting at all? (BHopefully, they are just ignored... (B 3) Will there be any issues with my tables when upgrading to MySQL (B4.1? Ideally, searching and sorting would just pick up the unicode (Bcharacters, as I'll set my character_set=utf8. (B (BMaybe there are some other issues I'm not aware of as well... any (Binsight would be appreciated. (B (BTIA, (BBen (B (B (B-- (BMySQL General Mailing List (BFor list archives: http://lists.mysql.com/mysql (BTo unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
trouble replicating
Hello, I'm having trouble with replication. The binary logs arrive in the slave directory, but don't get inserted into the database. I'm just a beginner at SQL mysql, in case it isn't obvious from the following. The replication is limited to a database called mipradius by a slave config 'replicate-do-db=myradius', though the master sends updates for all databases. There's nothing in the slave mysql.err after ... 031106 14:56:38 Slave I/O thread: connected to master '[EMAIL PROTECTED]:3306', replication started in log 'xswirz0101-bin.004' at position 7838142 The master (UltraSPARC called xswirz0101 aka hass) is running mysql-standard-4.0.15-sun-solaris2.8-sparc. The slave (PC called castor) is running 4.0.16 from the current debian unstable. SHOW SLAVE STATUS\G *** 1. row *** Master_Host: xswirz0101.iplab.trl.telstra.com.au Master_User: repl Master_Port: 3306 Connect_retry: 60 Master_Log_File: xswirz0101-bin.004 Read_Master_Log_Pos: 8612228 Relay_Log_File: castor-relay-bin.001 Relay_Log_Pos: 774135 Relay_Master_Log_File: xswirz0101-bin.004 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_do_db: myradius Replicate_ignore_db: Last_errno: 0 Last_error: Skip_counter: 0 Exec_master_log_pos: 8612228 Relay_log_space: 774135 1 row in set (0.00 sec) Thanks for any help, Brian Keck -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Delphi MySQL
Briefly:
The project connects with TADOConnection, TADOTable and TDataSource, and
consists of two tab sheets, one with a DBGrid and DBNavigator1 (Read
only),
and the other with an assortment of DBEdits, a DBMemo and a DbNavigator2,
this with full read/write options.
Attempts to write to Access succeed where attempts to write to MySQL
fail,
all other things being equal. The MySQL error return states:
Check the manual that corresponds to your MySQL server version for the
right syntax to use near ','detail' SET '('
So far as I can see, all MySQL permissions exist for read/write access to
the DB.
My confusion arises from the fact that to give entry level experience, I
chose data aware controls which I presumed would not need coding to
function
at a basic level. This works with Access but apparently not with MySQL.
Clearly I am missing something fundamental regarding differences between
MySQL's and Access's interactions with data aware components.
Can anyone suggest what it is that I am missing and how can I coax my
project to write to MySQL?
Please can you quote my Ref 'Query 2003048'.
I need more information to help you solve this problem:
1. Did you use default setting of MyODBC?What version? Show me your
connection string.
2. TAdotable:what kind of locktype used?
Try to save use delphi Dataset command like:
TAdotable1.UpdateBacth(); //if you use ltBacthOptimistic
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
