Upgrade MySQL and replication dies
I use Debian Sarge as my Linux distribution on two servers who replicate MySQL. Every time I upgrade the MySQL package my replication dies. Any ideas why? Thanks, Jacob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
CSV storage engine
You could use the CSV table type: http://dev.mysql.com/doc/mysql/en/csv-storage-engine.html Interesting. I just downloaded 4.1.11 - how does one enable this engine? With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: CSV storage engine
From the documentation referred to in the link 14.8. The CSV Storage Engine The CSV storage engine was added in MySQL 4.1.4. This engine stores data in text files using comma-separated-values format. To enable this storage engine, use the --with-csv-storage-engine option to configure when you build MySQL. When you create a CSV table, the server creates a table definition file in the database directory. The file begins with the table name and has an .frm extension. The storage engine also creates a data file. Its name begins with the table name and has a .CSV extension. The data file is a plain text file. When you store data into the table, the storage engine saves it into the data file in CSV format. mysql CREATE TABLE test(i INT, c CHAR(10)) ENGINE = CSV; Query OK, 0 rows affected (0.12 sec) mysql INSERT INTO test VALUES(1,'record one'),(2,'record two'); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql SELECT * FROM test; +--++ | i| c | +--++ |1 | record one | |2 | record two | +--++ 2 rows in set (0.00 sec) If you examine the test.CSV file in the database directory after executing the preceding statements, its contents look like this: 1,record one 2,record two The CSV storage engine does not support indexing. David Logan Database Administrator HP Managed Services 148 Frome Street, Adelaide 5000 Australia +61 8 8408 4273 - Work +61 417 268 665 - Mobile +61 8 8408 4259 - Fax -Original Message- From: Martijn Tonies [mailto:[EMAIL PROTECTED] Sent: Wednesday, 6 April 2005 4:43 PM To: mysql@lists.mysql.com Subject: CSV storage engine You could use the CSV table type: http://dev.mysql.com/doc/mysql/en/csv-storage-engine.html Interesting. I just downloaded 4.1.11 - how does one enable this engine? With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server Upscene Productions http://www.upscene.com -- 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: CSV storage engine
Ah David, From the documentation referred to in the link 14.8. The CSV Storage Engine The CSV storage engine was added in MySQL 4.1.4. This engine stores data in text files using comma-separated-values format. To enable this storage engine, use the --with-csv-storage-engine option to configure when you build MySQL. That much I read ... but are we serious here that the ONLY way to enable this, is to build MySQL yourself? I surely hope not ... :-) I'm using MySQL on Windows - if the above indeed is the case, did anyone build it with the CSV engine enabled? With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: CSV storage engine
Sorry, I don't use windows 8-) except when I have to. All my servers are unix/linux and I can build them at will. I think there are instructions in the manual for building it but I think you would probably have to purchase a C++ compiler or similar. Regards David Logan Database Administrator HP Managed Services 148 Frome Street, Adelaide 5000 Australia +61 8 8408 4273 - Work +61 417 268 665 - Mobile +61 8 8408 4259 - Fax -Original Message- From: Martijn Tonies [mailto:[EMAIL PROTECTED] Sent: Wednesday, 6 April 2005 4:53 PM To: Logan, David (SST - Adelaide); mysql@lists.mysql.com Subject: Re: CSV storage engine Ah David, From the documentation referred to in the link 14.8. The CSV Storage Engine The CSV storage engine was added in MySQL 4.1.4. This engine stores data in text files using comma-separated-values format. To enable this storage engine, use the --with-csv-storage-engine option to configure when you build MySQL. That much I read ... but are we serious here that the ONLY way to enable this, is to build MySQL yourself? I surely hope not ... :-) I'm using MySQL on Windows - if the above indeed is the case, did anyone build it with the CSV engine enabled? With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CSV storage engine
Sorry, I don't use windows 8-) except when I have to. All my servers are unix/linux and I can build them at will. I think there are instructions in the manual for building it but I think you would probably have to purchase a C++ compiler or similar. Exactly the reason why I do NOT want to build it :-) I know MySQL used to be build by everyone ... but IMO, this doesn't make sense no more ... I don't want to build my own binary for a database engine, no matter how detailed the instructions are... I hope for a binary with CSV enabled. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: deadlock with innodb
Gleb Paharenko wrote: Hello. Among other things check that you correctly process lock timeouts. I've check this, but it seems fine. I'm testing it this way with 3 applications on the same computer (2 processors) : - 2 clients applications with an open connection to the DB (which is remote with single processor) are waiting for a event to be trigger by the network. When the event comes they fire the query (BEGIN; SELECT ... FOR UPDATE; do stuff; COMMIT/ROLLBACK ). - 1 supervisor application that send those events to the clients applications (in order to reproduce production conditions). If I send the two event without delay ( send client1; send client2 ) the SELECT .. FOR UPDATE goes through for the _two_ clients at the same time and then cause InnoDB to complain about a deadlock. If i introduce a delay of 20 ms (send client1; sleep(20 ms); send client 2); only one SELECT .. FOR UPDATE goes through, the other one does wait until it's commited/rollback as expected (and so reproduce what I can observe if I do it by hand). The problem is just that if the two SELECT ... FOR UPDATE does arrive at the same time, it throws a deadlock. Well, if that's the expected behaviour, it's fine with me, but I still don't undestand why it does happen. Thanks for your help, Well, I'm sure it's a bug hidden somewhere in my apps, i've check with another connexion and it worked ;) -- Philippe Poelvoorde COS Trading Ltd. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Changed Number
In article [EMAIL PROTECTED], [EMAIL PROTECTED] writes: I have loaded a large *.csv spreadsheet into mysql and one number, the grand total, changes from 16996941 on the Excel spreadsheet to 8388607 in the mysql database. The numbers surrounding this number are correct at all stages. I have reloaded, checked formatting and done various other things without success. Then I went to the text (*.sql) file in which I had dumped the mysql table, manually changed the number to 16996941 and put the text file on the server. However, the number that showed up on the web page table and the number in the database on the server is 8388607. Note that the smaller number is just under half of the larger number. This makes no sense. Any solutions? My crystal ball says that the column type in question is a signed MEDIUMINT. Use a longer INT type. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
DATE TIME
Hello, i need to have the time in this output. in MySQL database phpmyadmin always puts -00-00 i need it to be like this 01-APR-2005 DD-MMM- the time needs to be like this 22:55 HH:MM can somebody help hans -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Upgrade MySQL and replication dies
Hello. Does Debian package preserve a master.info and relay-log.info files? Do you stop slave before the upgrade? What does the 'SHOW SLAVE STATUS' statement report? Jacob Friis Larsen [EMAIL PROTECTED] wrote: I use Debian Sarge as my Linux distribution on two servers who replicate MySQL. Every time I upgrade the MySQL package my replication dies. Any ideas why? Thanks, Jacob -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replacted MERGE table?
Hello. Replication should work with MERGE tables. You may make a test setup and check your scenario. Eric Anderson [EMAIL PROTECTED] wrote: Here's a question for you guys: is it possible to have a replicated table of type MERGE? Scenario: Server: WWW1 Table: A.local_table (MASTER) replicates to: Server: MEMBERS Table: B.www1 (SLAVE) and Server: WWW2 Table: A.local_table (MASTER) replicates to: Server: MEMBERS Table: B.www2 (SLAVE) where B.www1 and B.www2 are type MERGE? Then I could a SELECT on the type merge (B.www1,B.www2,etc)? Possible? The more I look at it, the more complicated it looks. (sigh) -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: something is pegging mysql
Hello. For your situation a slow query log might be suitable. See: http://dev.mysql.com/doc/mysql/en/slow-query-log.html [EMAIL PROTECTED] wrote: It has been a rough day today. I am using mysql with coldfusion. Something somehwere on my site is causing mysql to take up 100% of the cpu. This causes coldfusion to lock. Is there any sort of query log I can look at to see what queries have run in, say, the last hour? If I see what queries are running I could track down the page and either fix a defective query or change it so I am not getting killed. --ja -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to change session values?
Hello. When the server starts, it initializes all global variables to their default values. These defaults may be changed by options specified in option files or on the command line. After the server starts, those global variables that are dynamic can be changed by connecting to the server and issuing a SET GLOBAL var_name statement. To change a global variable, you must have the SUPER privilege. See: http://dev.mysql.com/doc/mysql/en/system-variables.html With regard to the charactor set, session values are not same to global values. How to make session to global values? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Changed Number
Ken Looks like you used a medium int field on the mysql table - if you import a figure that is too big for the medium int to handle - on overflow it places a value of 8388607 into the colum. You need to change to an integer column. Regards Tim hayes -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 06 April 2005 02:31 To: mysql@lists.mysql.com Subject: Changed Number I have loaded a large *.csv spreadsheet into mysql and one number, the grand total, changes from 16996941 on the Excel spreadsheet to 8388607 in the mysql database. The numbers surrounding this number are correct at all stages. I have reloaded, checked formatting and done various other things without success. Then I went to the text (*.sql) file in which I had dumped the mysql table, manually changed the number to 16996941 and put the text file on the server. However, the number that showed up on the web page table and the number in the database on the server is 8388607. Note that the smaller number is just under half of the larger number. This makes no sense. Any solutions? Ken -- 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 taked too much time to execute
hi All, I am trying to fech some data from some interrelated tables. I am using inner joins to relate tables. but mysql takes 7-8 sec to execute the query. What could be the reason of such delays ? I would like to specify that one of the interrelated table contains around 3000 rows. I tried to break the query in saperate spets but it couldn't help me . If anyone has any suggestion reagarding above then please drop me a mail. thanx Arjun Get Your Private, Free E-mail from Indiatimes at http://email.indiatimes.com Buy The Best In BOOKS at http://www.bestsellers.indiatimes.com Bid for for Air Tickets @ Re.1 on Air Sahara Flights. Just log on to http://airsahara.indiatimes.com and Bid Now! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DATE TIME
On Wed, Apr 06, 2005 at 12:06:37PM +0200, Hans Bernard wrote: i need to have the time in this output. in MySQL database phpmyadmin always puts -00-00 i need it to be like this 01-APR-2005 DD-MMM- Use date_format(, %d-%b-%Y %H:%m) in your SELECT? bye, -christian- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DATE TIME
- Original Message - From: Hans Bernard [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, April 06, 2005 6:06 AM Subject: DATE TIME Hello, i need to have the time in this output. in MySQL database phpmyadmin always puts -00-00 i need it to be like this 01-APR-2005 DD-MMM- the time needs to be like this 22:55 HH:MM can somebody help I am 99% sure that you cannot actually store dates or times in the formats that you want. However, I am also 99% sure that you *can* retrieve them in those formats by reformatting them when you retrieve them. I think you will find all of the information you need under functions in the MySQL manual. Unfortunately, the server appears to be down at the moment so I can't give you a link to the appropriate page. Also, I suspect the reason that you are seeing dates of '-00-00' is that you are supplying the dates in the wrong format within your INSERT statements and that MySQL is storing '-00-00' as the default. If you supply the dates in the format MySQL is expecting, you should get back the dates that you actually stored. Rhino -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.9.3 - Release Date: 05/04/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: CSV storage engine
On Wednesday, April 06, 2005 02:30, Martijn Tonies wrote: Sorry, I don't use windows 8-) except when I have to. All my servers are unix/linux and I can build them at will. I think there are instructions in the manual for building it but I think you would probably have to purchase a C++ compiler or similar. Exactly the reason why I do NOT want to build it :-) I know MySQL used to be build by everyone ... but IMO, this doesn't make sense no more ... I don't want to build my own binary for a database engine, no matter how detailed the instructions are... I hope for a binary with CSV enabled. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server Upscene Productions http://www.upscene.com Perhaps you can convince mysql ab to build a windows package similar to the linux max package which includes support for the csv engine along with many other things. -- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DATE TIME
thanks for the hints date_format() solved my problem my select query in php is now the following: $query = SELECT id, calltt, date_format(calldate, '%d-%b-%Y') AS calldate2, date_format(calltime, '%H:%i') AS calltime2,area, problem, solution, assignto, status FROM ticketing ; hans Rhino wrote: - Original Message - From: Hans Bernard [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, April 06, 2005 6:06 AM Subject: DATE TIME Hello, i need to have the time in this output. in MySQL database phpmyadmin always puts -00-00 i need it to be like this 01-APR-2005 DD-MMM- the time needs to be like this 22:55 HH:MM can somebody help I am 99% sure that you cannot actually store dates or times in the formats that you want. However, I am also 99% sure that you *can* retrieve them in those formats by reformatting them when you retrieve them. I think you will find all of the information you need under functions in the MySQL manual. Unfortunately, the server appears to be down at the moment so I can't give you a link to the appropriate page. Also, I suspect the reason that you are seeing dates of '-00-00' is that you are supplying the dates in the wrong format within your INSERT statements and that MySQL is storing '-00-00' as the default. If you supply the dates in the format MySQL is expecting, you should get back the dates that you actually stored. Rhino -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.9.3 - Release Date: 05/04/2005 -- 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/InnoDB-4.1.11 is released
Hi!
MySQL/InnoDB-4.1.11 is a bugfix release of the stable 4.1 branch. This
branch is recommended for production use. There are no important bug fixes
in 4.1.11, for most users there is no need to upgrade from 4.1.10.
InnoDB is the MySQL table type that supports foreign key constraints,
transactions, row-level locking, non-locking consistent read (MVCC), all
four SQL-1992 isolation levels of transactions, multiple tablespaces,
asynchronous unbuffered disk I/O on Windows, and a non-free hot online
backup tool.
Functionality added or changed:
* When MySQL/InnoDB is compiled on Mac OS X 10.2 or earlier, detect the
operating system version at run time and use the fcntl() file flush method
on Mac OS X versions 10.3 and later. Apple had disabled fsync() in Mac OS X
for internal disk drives, which caused corruption at power outages.
* A shared record lock (LOCK_REC_NOT_GAP) is now taken for a matching record
in the foreign key check because inserts can be allowed into gaps.
* Relaxed locking in INSERT...SELECT, single table UPDATE...SELECT and
single table DELETE...SELECT clauses when innobase_locks_unsafe_for_binlog
is used and isolation level of the transaction is not serializable. InnoDB
uses consistent read in these cases for a selected table.
Bugs fixed:
* Fixed a bug introduced in 4.1.9 to the Windows version if you used
innodb_file_per_table. mysqld would stop and complain about Windows error
number 87 in a file operation. (See the Bugs database or the 4.1.9 change
notes about a workaround for that bug in 4.1.9). (Bug #8021)
* Corrected the handling of trailing spaces in the ucs2 character set. (Bug
#7350)
* Use native tmpfile() function on Netware. All InnoDB temporary files are
created under sys:\tmp. Previously, InnoDB temporary files were never
deleted on Netware.
* Fix a race condition that could cause the assertion
space-n_pending_flushes == 0 to fail in fil0fil.c, in fil_space_free(), in
DROP TABLE or in ALTER TABLE.
* ALTER TABLE ... ADD CONSTRAINT PRIMARY KEY ... complained about bad
foreign key definition. (Bug #7831)
* Fix a theoretical hang over the adaptive hash latch in InnoDB if one runs
INSERT ... SELECT ... (binlog not enabled), or a multi-table UPDATE or
DELETE, and only the read tables are InnoDB type, the rest are MyISAM. (Bug
#7879)
* If MySQL wrote to its binlog, but for some reason, trx-update_undo and
@code{trx-insert_undo} were NULL in InnoDB, then trx-commit_lsn was
garbage, and InnoDB could assert in the log flush of
trx_commit_complete_for_mysql(). (Bug #9277)
* If InnoDB cannot allocate memory, keep retrying for 60 seconds before we
intentionally crash mysqld; maybe the memory shortage is just temporary.
* If one used LOCK TABLES, created an InnoDB temp table, and did a
multi-table update where a MyISAM table was the update table and the temp
table was a read table, then InnoDB asserted in row0sel.c because
n_mysql_tables_in_use was 0. Also, we remove the assertion altogether and
just print an error to the .err log if this important consistency check
fails. (Bug #8677)
Best regards,
Heikki Tuuri
Innobase Oy
http://www.innodb.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CSV storage engine
Martijn Tonies wrote: Sorry, I don't use windows 8-) except when I have to. All my servers are unix/linux and I can build them at will. I think there are instructions in the manual for building it but I think you would probably have to purchase a C++ compiler or similar. Exactly the reason why I do NOT want to build it :-) I know MySQL used to be build by everyone ... but IMO, this doesn't make sense no more ... I don't see why. I'm use to compile the library in debug mode, to retrieve the call-stack and the faulty SQL string directly in my IDE. That's where sources are great, you can choose what you want in your binary, engines, features, and so on. I don't want to build my own binary for a database engine, no matter how detailed the instructions are... I hope for a binary with CSV enabled. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server Upscene Productions http://www.upscene.com -- Philippe Poelvoorde COS Trading Ltd. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CSV storage engine
Sorry, I don't use windows 8-) except when I have to. All my servers are unix/linux and I can build them at will. I think there are instructions in the manual for building it but I think you would probably have to purchase a C++ compiler or similar. Exactly the reason why I do NOT want to build it :-) I know MySQL used to be build by everyone ... but IMO, this doesn't make sense no more ... I don't see why. I'm use to compile the library in debug mode, to retrieve the call-stack and the faulty SQL string directly in my IDE. That's where sources are great, you can choose what you want in your binary, engines, features, and so on. I'm not saying that you shouldn't do that ... I'm saying that I don't want to do that. And, I guess, a whole lot of people don't want to set up complete build environments for another product. Let alone build their production server :-) So, do you have a binary with csv engine enabled? With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Strange behavior
At 18:35 01.04.2005, Gleb Paharenko wrote: Hello. I don't have any ideas at least now. But additional information could be helpful. Do you connect from JBoss to the slave or master server? Please use We are conecting to the active mysql (normaly master). SHOW PROCESSLIST to find in what state the server threads waste their time. If you find something interesting send it. Include also the output of SHOW STATUS and SHOW VARIABLES. SHOW STATUS: mysql show status; +++ | Variable_name | Value | +++ | Aborted_clients| 342| | Aborted_connects | 0 | | Bytes_received | 2114765083 | | Bytes_sent | 3521573247 | | Com_admin_commands | 3992 | | Com_alter_table| 2 | | Com_analyze| 0 | | Com_backup_table | 0 | | Com_begin | 0 | | Com_change_db | 119962 | | Com_change_master | 0 | | Com_check | 0 | | Com_commit | 106880 | | Com_create_db | 1 | | Com_create_function| 0 | | Com_create_index | 0 | | Com_create_table | 0 | | Com_delete | 162153 | | Com_delete_multi | 0 | | Com_drop_db| 1 | | Com_drop_function | 0 | | Com_drop_index | 0 | | Com_drop_table | 0 | | Com_flush | 0 | | Com_grant | 0 | | Com_ha_close | 0 | | Com_ha_open| 0 | | Com_ha_read| 0 | | Com_insert | 147742 | | Com_insert_select | 0 | | Com_kill | 0 | | Com_load | 0 | | Com_load_master_data | 0 | | Com_load_master_table | 0 | | Com_lock_tables| 0 | | Com_optimize | 0 | | Com_purge | 0 | | Com_rename_table | 0 | | Com_repair | 0 | | Com_replace| 0 | | Com_replace_select | 0 | | Com_reset | 0 | | Com_restore_table | 0 | | Com_revoke | 0 | | Com_rollback | 3691 | | Com_savepoint | 0 | | Com_select | 9075484| | Com_set_option | 32097 | | Com_show_binlog_events | 0 | | Com_show_binlogs | 5 | | Com_show_create| 0 | | Com_show_databases | 44 | | Com_show_fields| 1284 | | Com_show_grants| 0 | | Com_show_keys | 1219 | | Com_show_logs | 0 | | Com_show_master_status | 0 | | Com_show_new_master| 0 | | Com_show_open_tables | 0 | | Com_show_processlist | 69 | | Com_show_slave_hosts | 4 | | Com_show_slave_status | 0 | | Com_show_status| 22195 | | Com_show_innodb_status | 13030 | | Com_show_tables| 1483 | | Com_show_variables | 56755 | | Com_slave_start| 0 | | Com_slave_stop | 0 | | Com_truncate | 0 | | Com_unlock_tables | 0 | | Com_update | 39024 | | Connections| 45560 | | Created_tmp_disk_tables| 0 | | Created_tmp_tables | 56631 | | Created_tmp_files | 2133 | | Delayed_insert_threads | 0 | | Delayed_writes | 0 | | Delayed_errors | 0 | | Flush_commands | 1 | | Handler_commit | 106883 | | Handler_delete | 1268 | | Handler_read_first | 13902 | | Handler_read_key | 3619254984 | | Handler_read_next | 3479415584 | | Handler_read_prev | 0 | | Handler_read_rnd | 7278832| | Handler_read_rnd_next | 756152091 | | Handler_rollback | 7624 | | Handler_update | 88733 | | Handler_write | 218257589 | | Key_blocks_used| 125| | Key_read_requests
mysqldump
Hi, I have a problem with mysqldump when I want to copy a table from a database. I have mysql Ver 14.7, Distrib 4.1.9, for pc-linux-gnu. I use the following command: mysqldump -h host -u user -ppassword database_name table_name table_name.sql And get the following error message: mysqldump: mysqldump: Couldn't execute '/*!40100 SET @@SQL_MODE= */': Query was empty (1065) Can anyone help me with this problem? Thanks! Regards Anna Henricson Anna Henricson, MSc, PhD student Center for Genomics and Bioinformatics (CGB) Karolinska Institutet S-171 77 Stockholm Sweden Phone: +46 (0)8 524 86030 Fax: +46 (0)8 323950 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql taked too much time to execute
3000 rows are no big deal. 7 to 8 seconds seems long. Most likely, there is either something wrong with your query, or you are missing a needed index. We can't tell you how to improve your query, however, without seeing the query. We can't tell you if the table structure is the problem without seeing it. We can't comment on your indexes without knowing what they are. You have to help us to help you. To learn how mysql sees your query, put EXPLAIN in front of it, as documented in the manual http://dev.mysql.com/doc/mysql/en/explain.html. Then, if you still need help, send us the output of EXPLAIN, as well as the output of SHOW CREATE TABLE tablename for each table used in the query. Michael On Apr 6, 2005, at 5:56 AM, arjun_iet wrote: hi All, I am trying to fech some data from some interrelated tables. I am using inner joins to relate tables. but mysql takes 7-8 sec to execute the query. What could be the reason of such delays ? I would like to specify that one of the interrelated table contains around 3000 rows. I tried to break the query in saperate spets but it couldn't help me . If anyone has any suggestion reagarding above then please drop me a mail. thanx Arjun -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
IIS, ASP, MySQL (was: database pooling problem)
On Monday, Mar 7, 2005, at 16:04 US/Central, [EMAIL PROTECTED] wrote: It would be happy to try to help if I worked in or on either of those platforms (Apache + Java). I am Win32(IIS), ASP (VBScript/JavaScript) using MyODBC as my connection library. Is using MyODBC the accepted way to connect to a MySQL database from within an ASP/VBScript page? Or is there some other way? For example, PHP has a connector that can directly connect to a MySQL database. Regards, - Robert http://www.cwelug.org/downloads Help others get OpenSource software. Distribute FLOSS for Windows, Linux, *BSD, and MacOS X with BitTorrent -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replacted MERGE table?
Yes, but not in the way you're thinking. I believe what you want is one slave with multiple masters, which AFAIK is not supported. == STOP READING NOW UNLESS YOU ARE A GENIUS/MADMAN/IDIOT == That said, I suppose you could theoretically set up multiple servers on your slave machine, where: - All servers are read-only - You set up a slave server for each master - You set up an additional server containing MERGE tables pointing to the tables in the slave servers' datadirs. It's that third one that I'm unsure of. If your application is something like logging, where all your writes are atomic, I think you might be okay. That said, I've never tried something like this, so it could very well be a complete waste of time to try. Also, the usual restrictions on MERGE tables would apply, so you'll run into trouble if you're using AUTO_INCREMENT or UNIQUE keys. The workaround would be to add a column that contains the master id and convert your key to multipart, incorporating that field. Eamon Daly - Original Message - From: Eric Anderson [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, April 05, 2005 4:49 PM Subject: Replacted MERGE table? Here's a question for you guys: is it possible to have a replicated table of type MERGE? Scenario: Server: WWW1 Table: A.local_table (MASTER) replicates to: Server: MEMBERS Table: B.www1 (SLAVE) and Server: WWW2 Table: A.local_table (MASTER) replicates to: Server: MEMBERS Table: B.www2 (SLAVE) where B.www1 and B.www2 are type MERGE? Then I could a SELECT on the type merge (B.www1,B.www2,etc)? Possible? The more I look at it, the more complicated it looks. (sigh) -- 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: any performance increase from using prepared statements
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Are prepared statements cached on the server side? When I create a prepared statement with one connection, and another connection tries to create the same prepared statement, will the cached one be used? I am trying to see when prepared statements would be best used? It seems that it may be an improvement if I am in a tight loop, using the same connection, but may not be as good as dynamically generated queries when using different database connections. Is there any good resources to explain when it is best to use a prepared statement and a dynamically generated statement? Thanx. - -- Love is mutual self-giving that ends in self-recovery. Fulton Sheen James Black[EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.1 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCU/rLikQgpVn8xrARArf5AJ4xp2ra/kbIGEpOEbumfd5MkOYjDwCeKtfc gG6GJ4zSMU/d/XY5GNatYHY= =n7xB -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Re: Temporal databases MySQL
There was a magazine Database Programming Design, now defunct...too much detail for the average IT Manager so it didn't sell well...that did a nice multi-part series on the subject. Including the SQL3 connection. I could only locate a couple of fragments online...it used to be all online if you were a subscriber and had your password...I'll have look around for mine and see if I can locate more info. You'll find some references to and from Rick Snodgrass in the links, most anything he writes I find worth reading. The short of it is, at this point you have to roll your own...you can implement the NOW time slice using a VIEW over a main table(s) where the view`s WHERE clause has as it`s end_date set to a magic value to indicate infinity or current state 1/1/ or 1/1/ if you engine accepts the value. The main table or tables would contain a start_time end_time representing the time slice. For most engines, using multiple indexes concurrently on the same table is not possible so you must either use self joins or store the start and end date in separate tables with a common id. Cluster tables can help significantly when dealing date extraction as long you are not updating the rows and cause significant page splits. This is where separate tables for the start end times would allow you to create two clustered indexes, one for each table. We use this type environment, a little, on our test floor to represent all our running equipment and their current state. Production is mostly concerned with the NOW view of things and engineering tends to be more interested in various times in the past to help make decisions for the future. To speed up the NOW view for production we strategically place triggers and some procs to funnel the state/info from dozens of tables to just one small table (wide but short) representing key data. This table is used directly for many reports and is heavily index to allow good joins back to the source tables when needed. It's hard to get to complicated with this time slice thing and still have your average app developer keep up with everything and use it correctly...so for the most part we wait for full support by the DBMS. Good luck Ed http://www.dbpd.com/vault/9810/temporal.html http://www.dbpd.com/vault/9810snod.html -Original Message- From: news [mailto:[EMAIL PROTECTED] On Behalf Of Daniel BODEA Sent: Tuesday, April 05, 2005 11:11 AM To: mysql@lists.mysql.com Subject: Re: Temporal databases MySQL Hi Shawn, I really meant temporal and not temporary. Temporal as in TSQL2. Databases that on the one hand accumulate all changes to data over time along with accurate time information and on the other hand provide varying degrees of transparency in querying this data based on the theory of instants and aggregated intervals of time. Most of the resources available online are largely academic though. Google : http://www.google.com/search?hl=enq=temporal+database Troels' links has a good temporal databases section : http://troels.arvin.dk/db/rdbms/links/ The TAU Project that has some experimental code for several engines of which MySQL : http://www.cs.arizona.edu/tau/ I need to use this fully in a project that uses MySQL 4.1.latest and in a way that's independent of the structure of tables comprising the application. I'm not looking for TSQL2 implementations for MySQL or other types of esoteric implementations at the SQL level. I was just interested in hearing from people who have used MySQL to implement this model in a production environment and what they could say about both the storage of temporal data and the optimization of queries on past instants and intervals. There are several partially incompatible ways of doing this in a generic relational context but as always, only one is most fit for a given SQL engine and I'm currently asking about it for MySQL. I can't possibly be the first one to push this thing onto MySQL based on production-quality requirements. Thanks, Daniel I am not familiar with the use of the adjective temporal with the word database. To me temporal means of or having to do with time or it's measurement. Could you have meant temporary which means to me non-permanent or transitory in nature.? Even if you had meant temporary, I rarely hear it used as a database design term except when used with the word table as in temporary table. (http://dev.mysql.com/doc/mysql/en/create-table.html) However, if the TAU project is doing research on databases that are displaced or movable through time, this may be something I want to get involved with. What is their URL? Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Chinese word not sort proper in 'order by'
mysql 4.1.x :Latin1, the select resualt is disorder: shoud be axx bxx hhh ... zxx but actual: axx bxx hhh ... zxx Chinese GB code words are chars that their ascii values between 128-255 . Thanks ! Shuming Wang
RE: Chinese word not sort proper in 'order by'
I think they are sorted by how many draws each word has. Zhi Shuming Wang [EMAIL PROTECTED] wrote: mysql 4.1.x :Latin1, the select resualt is disorder: shoud be axx bxx hhh ... zxx but actual: axx bxx hhh ... zxx Chinese GB code words are chars that their ascii values between 128-255 . Thanks ! Shuming Wang __ Switch to Netscape Internet Service. As low as $9.95 a month -- Sign up today at http://isp.netscape.com/register Netscape. Just the Net You Need. New! Netscape Toolbar for Internet Explorer Search from anywhere on the Web and block those annoying pop-ups. Download now at http://channels.netscape.com/ns/search/install.jsp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: IIS, ASP, MySQL (was: database pooling problem)
Robert Citek [EMAIL PROTECTED] wrote on 04/06/2005 10:55:07 AM: On Monday, Mar 7, 2005, at 16:04 US/Central, [EMAIL PROTECTED] wrote: It would be happy to try to help if I worked in or on either of those platforms (Apache + Java). I am Win32(IIS), ASP (VBScript/JavaScript) using MyODBC as my connection library. Is using MyODBC the accepted way to connect to a MySQL database from within an ASP/VBScript page? Or is there some other way? For example, PHP has a connector that can directly connect to a MySQL database. Regards, - Robert http://www.cwelug.org/downloads Help others get OpenSource software. Distribute FLOSS for Windows, Linux, *BSD, and MacOS X with BitTorrent MyODBC is but one way to connect code to server. I am not running ASP.NET servers so I don't use the .NET Connector. There was another project or two (like the .Net Connector) that was written for VB. I found a copy of the source of one of them but all of the projects I have found seem to be abandoned (which means, you are responsible for your own support). The C++ API is packaged with the server) Since I don't have time to update these legacy connectors to keep up with the new server technology (Views, SPROCS, INFORMATION_SCHEMA, 4.1+ password hashing, etc) and I don't have enough time to debug it when I get it wrong (which everyone does), I decided to go with the tested and prepackaged MyODBC. Now, if anyone else has or knows of a product that will work from ASP (not ASP.Net) or VB (not VB.NET) and doesn't require the .NET runtime library to be installed I would love to hear from you. Please respond to the list so that everyone has a chance to get in on it, too. Thanks, Shawn Green Database Administrator Unimin Corporation - Spruce Pine PS. I am sure that if I tried really hard, I could call a compiled version of the C++ API from VB but I don't want to go through the motions of converting all of the APIs function calls into VB declare statements. I have thought about it, but decided against it. Once again, it's a matter of time and support. - S
Performance question
Hi all Suppose that I want to create a table with a column named DETAILS that will contain textual data. Performance-wise, does it matter if I represent this column with, say, a 200-char varchar or a larger type like text or mediumtext but be sure that only textual data smaller than 200 chars is going to be stored in it? Best Regards, -- Behrang Saeedzadeh http://www.jroller.com/page/behrangsa
RE: any performance increase from using prepared statements
Unless something has radically changed since 4.1.2 then prepared statements (at least from c) are 50% slower than executing statements as required. Search for posts by me on the subject from last year. Kevin Cowley Product Development Alchemetrics Ltd SMARTER DATA , FASTER Tel: 0118 902 9000 (swithcboard) Tel: 0118 902 9099 (direct) Web: www.alchemetrics.co.uk Email: [EMAIL PROTECTED] -Original Message- From: James Black [mailto:[EMAIL PROTECTED] Sent: 06 April 2005 16:06 To: 'mysql@lists.mysql.com ' Subject: re: any performance increase from using prepared statements -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Are prepared statements cached on the server side? When I create a prepared statement with one connection, and another connection tries to create the same prepared statement, will the cached one be used? I am trying to see when prepared statements would be best used? It seems that it may be an improvement if I am in a tight loop, using the same connection, but may not be as good as dynamically generated queries when using different database connections. Is there any good resources to explain when it is best to use a prepared statement and a dynamically generated statement? Thanx. - -- Love is mutual self-giving that ends in self-recovery. Fulton Sheen James Black[EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.1 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCU/rLikQgpVn8xrARArf5AJ4xp2ra/kbIGEpOEbumfd5MkOYjDwCeKtfc gG6GJ4zSMU/d/XY5GNatYHY= =n7xB -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] ** ALCHEMETRICS LIMITED (ALCHEMETRICS) Mulberry Park, Fishponds Road, Wokingham, Berkshire, RG41 2GX Tel: +44 (0) 118 902 9000Fax: +44 (0) 118 902 9001 This e-mail is confidential and is intended for the use of the addressee only. If you are not the intended recipient, you are hereby notified that you must not use, copy, disclose, otherwise disseminate or take any action based on this e-mail or any information herein. If you receive this transmission in error, please notify the sender immediately by reply e-mail or by using the contact details above and then delete this e-mail. Please note that e-mail may be susceptible to data corruption, interception and unauthorised amendment. Alchemetrics does not accept any liability for any such corruption, interception, amendment or the consequences thereof. ** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem with make command
After executing de command make I have the next error make: Fatal error: Don't know how to make target `ctype-big5.lo' Current working directory /2ndhd/MYSQL/one/mysql-4.0.21/libmysql_r *** Error code 1 make: Fatal error: Command failed for target `all-recursive' Current working directory /2ndhd/MYSQL/one/mysql-4.0.21 *** Error code 1 make: Fatal error: Command failed for target `all' Help me. Please.
re: stored procedure has very poor performance
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 It would appear, based on my testing, so far, that using stored procedures is some between 25% slower and 10x slower, depending on the test. I am using jdk1.5, on Solaris 8, and mysql 5.0.3. I hope that when 5 comes out of beta that the performance is improved. - -- Love is mutual self-giving that ends in self-recovery. Fulton Sheen James Black[EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.1 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCVCSEikQgpVn8xrARAkaSAJwOaAyQSfKKZXj0a1VNbiegkInKkwCfaEl1 HF3YdJVxevg7r/f6o2vkSBw= =k8yO -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.1.11 has been released
On Apr 5, 2005, at 11:37 PM, Matt Wagner wrote: Hi, A new version of MySQL Community Edition 4.1.11 Open Source database management system has been released. It is now available in source and binary form for a number of platforms from our download pages at http://dev.mysql.com/downloads/ and mirror sites. The good news: mysql compiles cleanly for me (Mac OS X 10.3.8) and passes all tests. snip Bugs fixed: snip * Fixed a bug in my_print_defaults that made it ignore the --defaults-extra-file and --defaults-file options. The bad news: Although the description is wrong (--defaults-file works, --defaults-extra-file does not), I believe this would be bug #9136 http://bugs.mysql.com/bug.php?id=9136. Unless I'm missing something, this has not been fixed. ~: my_print_defaults mysqld --key_buffer=32M ~: my_print_defaults -c /etc/de.cnf mysqld --max_allowed_packet=2M --key_buffer=64M ~: my_print_defaults -e /etc/de.cnf mysqld --key_buffer=32M Enjoy! Matt -- Matt Wagner, Production Engineer MySQL AB, www.mysql.com Northfield, MN, USA Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SELECT, INNER JOIN getting all ancestors of a term
I tried the query defined in the Go Example queries: and added some filtering on t.term_type, t.is_root and t.is_obsolete Finding all ancestors of a term: SELECT p.* FROM graph_path INNER JOIN term AS t ON (t.id = graph_path.term2_id and t.term_type=biological_process) INNER JOIN term AS p ON (p.id = graph_path.term1_id and t.term_type=biological_process) WHERE t.name = 'DNA Helicase activity'; I added to the where clause: and t.is_root=0 and t.is_obsolete=0; I still get in the result: some terms refering to the root name=biological_process or name=physiological_process. Also these are duplicated rows. Should not this query reduce the result to the biological process terms only with no duplicates? Thank you Laurie -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Cannot execute query - Can't find file: (error: 9)
At 10:07 AM 4/5/05, Frank Bax wrote: At 04:27 PM 4/4/05, Frank Bax wrote: Cannot execute query. snip my SQL statement Can't find file: './donor/list_lst.frm' (errno: 9) - - I got the same error last week on a different table. Today I notice that there is a table in another database on same system producing the same error. I attempted to access mysql cli, but it just locked up after entering password. Start/stop mysql and mysql cli at least started but issued errors about some tables even before I entered a command. I decided to reboot and the problem goes away (for a while). MySQL 4.0.20 - OpenBSD 3.6 Forgot to mention a couple of things: 1) The file that mysql complains about does exist. # ls -ltr donor/list*.frm -rw-rw 1 _mysql _mysql 8694 Jan 15 09:43 donor/list_lst.frm 2) When problem recurs (as it did on both databases this morning), the same file in each database is affected each time error appears. 3) In both databases (on same system) it is frm files in the error message. Since OpenBSD 2.8, there is a default limit of 128 open files for daemon processes. Add --open-files-limit=2048 to mysql startup. http://dev.mysql.com/doc/mysql/en/openbsd-2-8.html The suggested changes to /etc/login.conf were not necessary on my system. Does MySQL ever close the file(s) associated with table(s), or once open do they stay open until shutdown? Frank -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Cannot execute query - Can't find file: (error: 9)
According to perror: perror 9 Error code 9: Bad file number This is an operating system error code: http://dev.mysql.com/doc/mysql/en/operating-system-error-codes.html Out of curiosity, have you tried running myisamchk or some CHECK TABLE commands yet? http://dev.mysql.com/doc/mysql/en/table-maintenance.html Cheers, --V Frank Bax wrote: At 10:07 AM 4/5/05, Frank Bax wrote: At 04:27 PM 4/4/05, Frank Bax wrote: Cannot execute query. snip my SQL statement Can't find file: './donor/list_lst.frm' (errno: 9) - - I got the same error last week on a different table. Today I notice that there is a table in another database on same system producing the same error. I attempted to access mysql cli, but it just locked up after entering password. Start/stop mysql and mysql cli at least started but issued errors about some tables even before I entered a command. I decided to reboot and the problem goes away (for a while). MySQL 4.0.20 - OpenBSD 3.6 Forgot to mention a couple of things: 1) The file that mysql complains about does exist. # ls -ltr donor/list*.frm -rw-rw 1 _mysql _mysql 8694 Jan 15 09:43 donor/list_lst.frm 2) When problem recurs (as it did on both databases this morning), the same file in each database is affected each time error appears. 3) In both databases (on same system) it is frm files in the error message. Since OpenBSD 2.8, there is a default limit of 128 open files for daemon processes. Add --open-files-limit=2048 to mysql startup. http://dev.mysql.com/doc/mysql/en/openbsd-2-8.html The suggested changes to /etc/login.conf were not necessary on my system. Does MySQL ever close the file(s) associated with table(s), or once open do they stay open until shutdown? Frank -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: more on slow stored procedure performance
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 For the nams. tables the badge is the primary key, and since the stored procedure and the dynamically generated function generate the same queries, it shouldn't matter as to performance. I am using Solaris 8, JDK1.5, mysql 5.0.3 I haven't tested with prepared statements, but I don't know if I will, as I know there will be a performance hit there, based on past history. It appears that the stored procedure is 4x slower than dynamically generating, to almost 7x slower. Following is the time to do the tests: [junit] Testcase: testDeassignMultiDB took 0.088 sec [junit] Testcase: testDeassignMultiDBStoredProcedures took 0.34 sec [junit] Testcase: testDeassignMultiDBStoredProcedures100Reps took 13.712 sec [junit] Testcase: testDeassignMultiDB100Reps took 2.266 sec The last two tests do the exact same tests 100 times, so I can get a better idea as to numbers. Each test is: deassign assign deassign For the assign functions here are the query that is sent: [junit] INSERT INTO curuse(rid, start, badge, card_type, dept, college, campus, fullname, ip) SELECT 1999,1112812166, n.badge, a.role, a.deptid, a.college, a.campus, concat(na.fname, , na.lname), 0 FROM nams.names na, items i, nams.netids n, nams.affiliations a WHERE i.rid=1999 AND n.netid='jblack' AND na.badge=n.badge AND a.badge=na.badge AND i.status='A' AND a.source='B' LIMIT 1 [junit] UPDATE items SET status='U' WHERE rid=1999 Here is the stored procedure: CREATE PROCEDURE assignItem ( user CHAR(15), rid int, start int) BEGIN INSERT INTO curuse(rid, start, badge, card_type, dept, college, campus, fullname, ip) SELECT rid, start, n.badge, a.role, a.deptid, a.college, a.campus, concat(na.fname, , na.lname), 0 FROM nams.names na, items i, nams.netids n, nams.affiliations a WHERE i.rid=rid AND n.netid=user AND na.badge=n.badge AND a.badge=na.badge AND i.status='A' AND a.source='B' LIMIT 1; UPDATE items SET status='U' WHERE rid=rid; END; For deassign, here is the query: [junit] INSERT INTO transactions(start,badge,card_type,dept,college,campus,finish,lid,itemtype,rid)SELECT c.start, c.badge, c.card_type, c.dept, c.college, c.campus,1112812166, i.lid, i.itemtype,1999 FROM curuse c, items i WHERE i.rid=1999 AND c.rid=1999 [junit] UPDATE items SET status='A' WHERE rid=1999 [junit] DELETE FROM curuse WHERE rid=1999 Here is the stored procedure: CREATE PROCEDURE deassignItem ( rid int, endtime int) BEGIN INSERT INTO transactions(start,badge,card_type,dept,college,campus,finish,lid,itemtype,rid) SELECT c.start, c.badge, c.card_type, c.dept, c.college, c.campus, endtime, i.lid, i.itemtype, rid FROM curuse c, items i WHERE i.rid=rid AND c.rid=rid; UPDATE items set status='A' where rid=rid; DELETE FROM curuse WHERE rid=rid; END; Here are the three main tables that are used: | curuse | CREATE TABLE `curuse` ( `rid` int(11) NOT NULL default '0', `start` int(11) default NULL, `badge` int(11) default NULL, `card_type` char(2) default NULL, `dept` char(3) default NULL, `college` char(2) default NULL, `campus` char(1) default NULL, `fullname` varchar(24) default NULL, `ip` varchar(40) NOT NULL default '', `alive` int(11) default NULL, PRIMARY KEY (`rid`,`ip`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Items that are currently assigned' | | items | CREATE TABLE `items` ( `rid` int(11) NOT NULL auto_increment, `lid` int(11) NOT NULL default '0', `itemtype` char(4) NOT NULL default '', `label` char(12) NOT NULL default '', `status` char(1) NOT NULL default '', `layoutx` int(11) default NULL, `layouty` int(11) default NULL, `theta` int(11) default NULL, PRIMARY KEY (`rid`), UNIQUE KEY `label_ndx` (`label`), KEY `itemtype_ndx` (`itemtype`), KEY `lid_ndx` (`lid`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 | | transactions | CREATE TABLE `transactions` ( `xid` int(11) NOT NULL auto_increment, `start` int(11) NOT NULL default '0', `finish` int(11) NOT NULL default '0', `lid` int(11) NOT NULL default '0', `itemtype` char(4) NOT NULL default '', `rid` int(11) NOT NULL default '0', `badge` int(11) NOT NULL default '0', `card_type` char(2) NOT NULL default '', `dept` char(3) NOT NULL default '', `college` char(2) NOT NULL default '', `campus` char(1) NOT NULL default '', PRIMARY KEY (`xid`), KEY `start_ndx` (`start`), KEY `rank_ndx` (`card_type`), KEY `dept_ndx` (`dept`), KEY `college_ndx` (`college`), KEY `campus_ndx` (`campus`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 | - -- Love is mutual self-giving that ends in self-recovery. Fulton Sheen James Black[EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.1 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCVC08ikQgpVn8xrARApKUAJ4/VMnH3T4cB7gUDYYLf4SZKbe4XwCfQbZ1 5DUJaPRnmNJs170/UpGl3OA= =Iuwr -END PGP SIGNATURE- -- MySQL General Mailing List For list archives:
Re: IIS, ASP, MySQL (was: database pooling problem)
On Wed, 2005-04-06 at 09:55 -0500, Robert Citek wrote: Is using MyODBC the accepted way to connect to a MySQL database from within an ASP/VBScript page? Or is there some other way? For example, PHP has a connector that can directly connect to a MySQL database. I believe it's the only one currently maintained. -- Florin Andrei http://florin.myip.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
innodb, log_bin and ib_logfiles
I have looked around in the documentation but I do not have a clear idea of log_bin vs ib_lofile for innodb. Regarding only INNODB tables, am I right if I say that: log_bin are the commit transactions and it is what is use in an automatic recovery or are to be apply in a manual recovery from a dump (In Oracle they are the Redo.logs) . ib_logfiles are storing the uncommit and commit transactiond and allows the rollback of transactions (In Oracle they are the Rollback segments)? They are not use in the automatic recovery of innodb nor in a manual recovery from a dump. I would appreciate very much if someone can clarify this for me. Johanne Duhaime
Re: Upgrade MySQL and replication dies
Does Debian package preserve a master.info and relay-log.info files? I'll check with some Debian experts. Do you stop slave before the upgrade? No. What does the 'SHOW SLAVE STATUS' statement report? Before that everything is ok, after I don't know now that it is running ok, but I'll check next time. Thanks, Jacob Jacob Friis Larsen [EMAIL PROTECTED] wrote: I use Debian Sarge as my Linux distribution on two servers who replicate MySQL. Every time I upgrade the MySQL package my replication dies. Any ideas why? Thanks, Jacob -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.1.11 has been released
On Apr 6, 2005, at 2:12 PM, Michael Stassen wrote: On Apr 5, 2005, at 11:37 PM, Matt Wagner wrote: Hi, A new version of MySQL Community Edition 4.1.11 Open Source database management system has been released. It is now available in source and binary form for a number of platforms from our download pages at http://dev.mysql.com/downloads/ and mirror sites. The good news: mysql compiles cleanly for me (Mac OS X 10.3.8) and passes all tests. snip Bugs fixed: snip * Fixed a bug in my_print_defaults that made it ignore the --defaults-extra-file and --defaults-file options. The bad news: Although the description is wrong (--defaults-file works, --defaults-extra-file does not), I believe this would be bug #9136 http://bugs.mysql.com/bug.php?id=9136. Unless I'm missing something, this has not been fixed. ~: my_print_defaults mysqld --key_buffer=32M ~: my_print_defaults -c /etc/de.cnf mysqld --max_allowed_packet=2M --key_buffer=64M ~: my_print_defaults -e /etc/de.cnf mysqld --key_buffer=32M Enjoy! Matt -- Matt Wagner, Production Engineer MySQL AB, www.mysql.com Northfield, MN, USA Michael I did a little more digging. Bug #9136 says the fix is in a patch to mysys/default.c referenced in http://lists.mysql.com/internals/23065. Looking at my copy of the source, this patch has NOT been applied to mysql-4.1.11. I went ahead and applied the patch to my 4.1.11 source and re-compiled. This build also passes all tests, and now I get ~: my_print_defaults mysqld --key_buffer=32M ~: my_print_defaults -c /etc/de.cnf mysqld --max_allowed_packet=2M --key_buffer=64M ~: my_print_defaults -e /etc/de.cnf mysqld --key_buffer=32M --max_allowed_packet=2M --key_buffer=64M As you can see, with the patch, my_print_defaults now works as expected. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
I need some help
Hi, I have a problem: I am using mysql 4.0.24 and I need to make some reports from a database: mysql describe events; +-+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-++ | id | int(10) unsigned | | PRI | NULL| auto_increment | | user_id | varchar(55) | | | 0 || | dep_id | int(15) | | | 0 || | event_id | int(15) | | | 0 || | year| int(15) | | | 0 || | day | int(15) | | | 0 || | month | int(15) | | | 0 || | ev_status | int(11) | YES | | 0 || | ev_type | int(11) | YES | | 0 || | ev_priority | int(11) | YES | | 0 || +-+--+--+-+-++ I need something to get: | USER_ID | DEP_ID | EV_STATUS = 0 | EV_STATUS = 1 | EV_STATUS = 2 | EV_STATUS = 3 | EV_STATUS = 4 | EV_STATUS = 5 | EV_STATUS = 6 | user_1 1COUNT COUNT COUNT COUNT COUNT COUNT COUNT user_2 1COUNT COUNT COUNT COUNT COUNT COUNT COUNT user_3 2COUNT COUNT COUNT COUNT COUNT COUNT COUNT . . . The problem is that the way I am getting this now is with a query for each user and for each ev_status and the output is in PHP. Is there a better way to do this ? I am only a beginner in MySQL and want to learn more and improve. Best regards, Cristi
Re: I need some help
- Original Message - From: iNFERNo [EMAIL PROTECTED] To: mysql mysql@lists.mysql.com Sent: Wednesday, April 06, 2005 5:23 PM Subject: I need some help Hi, I have a problem: I am using mysql 4.0.24 and I need to make some reports from a database: mysql describe events; +-+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-++ | id | int(10) unsigned | | PRI | NULL| auto_increment | | user_id | varchar(55) | | | 0 || | dep_id | int(15) | | | 0 || | event_id | int(15) | | | 0 || | year| int(15) | | | 0 || | day | int(15) | | | 0 || | month | int(15) | | | 0 || | ev_status | int(11) | YES | | 0 || | ev_type | int(11) | YES | | 0 || | ev_priority | int(11) | YES | | 0 || +-+--+--+-+-++ I need something to get: | USER_ID | DEP_ID | EV_STATUS = 0 | EV_STATUS = 1 | EV_STATUS = 2 | EV_STATUS = 3 | EV_STATUS = 4 | EV_STATUS = 5 | EV_STATUS = 6 | user_1 1COUNT COUNT COUNT COUNT COUNT COUNT COUNT user_2 1COUNT COUNT COUNT COUNT COUNT COUNT COUNT user_3 2COUNT COUNT COUNT COUNT COUNT COUNT COUNT . . . The problem is that the way I am getting this now is with a query for each user and for each ev_status and the output is in PHP. Is there a better way to do this ? I am only a beginner in MySQL and want to learn more and improve. First of all, congratulations on identifying your version of MySQL and giving the definition of your table. That is an excellent start to getting an answer to your question. Far too many people post here without identifying their MySQL version or giving the definition of their tables, making it very difficult to answer their questions without having to ask many followup questions. Unfortunately, there is at least one problem evident in your table definition. It appears that you don't have any primary key on the table! As a newcomer to MySQL, you may not even know what a primary key is. Do you have any training with data design, particularly normalization? If not, you should definitely find out what a primary key is. In my opinion, which is based on 20+ years with relational databases, choosing a good primary key is vital to having a good database design. If you don't choose good primary keys - or worse, if you don't have any primary keys at all - you are going to have many serious problems with your database. Giving you the answer to your question depends very strongly on what the primary key of your table is. In other words, you really need to choose and define the primary key before anyone can answer your question correctly. Are you able to perform a normalization so that you can choose a primary key for this table? If not, perhaps you can use your favourite search engine to find a tutorial on database design or normalization to learn the technique. Then, once you have chosen a primary key, post again with the full definition of the table, like you did in this post, but this time including a primary key definition. It would also help if you provided a small amount of the data in your table so that we can get a good understanding of the meaning of the data. With that information, I think we can do a good job of helping you work out the SQL that will do what you want to do. I should warn you that if you have no training in normalization of a data model, it may seem difficult and time consuming. In fact, it may take you a couple of days or more to find a good tutorial and for you to work your way through it. That may seem like a lot of effort but, believe me, the time you invest in studying normalization now will pay for itself many many times over in the future as you build databases. You'll also find that you can normalize data models very quickly and often in your head once you get a bit of practice with it. Rhino -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.9.3 - Release Date: 05/04/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
recovery of a very large table?
Hey folks... While we weren't paying attention, one of the tables we were logging to got big. Really big... like over 2 gigs... and then the server crashed and the data became corrupt. 'Course, we'd like that data back... Normal recovery seems to grab 490 rows... but, originally there were some 22 million rows in there. So far, I've tried: myisamchk -f tableName myisamchk -o tableName myisamchk -unpack tableName Same result, every time. Weird, eh? Some corruption is no problem. (We'll take what we can get.) I believe that the table was packed up at some point, but I'm not sure. So... what are my options here? Machine is linux - using ubuntu on the recovery box. -- jon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: I need some help
Rhino wrote: - Original Message - From: iNFERNo [EMAIL PROTECTED] To: mysql mysql@lists.mysql.com Sent: Wednesday, April 06, 2005 5:23 PM Subject: I need some help Hi, I have a problem: I am using mysql 4.0.24 and I need to make some reports from a database: mysql describe events; +-+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-++ | id | int(10) unsigned | | PRI | NULL| auto_increment | | user_id | varchar(55) | | | 0 || | dep_id | int(15) | | | 0 || | event_id | int(15) | | | 0 || | year| int(15) | | | 0 || | day | int(15) | | | 0 || | month | int(15) | | | 0 || | ev_status | int(11) | YES | | 0 || | ev_type | int(11) | YES | | 0 || | ev_priority | int(11) | YES | | 0 || +-+--+--+-+-++ I need something to get: | USER_ID | DEP_ID | EV_STATUS = 0 | EV_STATUS = 1 | EV_STATUS = 2 | EV_STATUS = 3 | EV_STATUS = 4 | EV_STATUS = 5 | EV_STATUS = 6 | user_1 1COUNT COUNT COUNT COUNT COUNT COUNT COUNT user_2 1COUNT COUNT COUNT COUNT COUNT COUNT COUNT user_3 2COUNT COUNT COUNT COUNT COUNT COUNT COUNT . . . The problem is that the way I am getting this now is with a query for each user and for each ev_status and the output is in PHP. Is there a better way to do this ? I am only a beginner in MySQL and want to learn more and improve. First of all, congratulations on identifying your version of MySQL and giving the definition of your table. That is an excellent start to getting an answer to your question. Far too many people post here without identifying their MySQL version or giving the definition of their tables, making it very difficult to answer their questions without having to ask many followup questions. Am I missing something... but isn't id defined as the primary key? On a different topic.. if you want to make your life easy with PHP. Instead of saving, the year, month, day... personally I always simply use an int and save all dates as Unix Timestamps. As long as you are working more or less in this century, you will be fine. That is a personal choice, from someone that has built MANY calendars. As for selecting all of them like that. I question why.. but SELECT user_id, dep_id, IF(ev_status=1,1,0), IF(ev_status=2,10) ... and so on. But I really don't get why you would be doing that Mike . -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: recovery of a very large table?
On Wednesday 06 April 2005 20:05, jon wrote: Normal recovery seems to grab 490 rows... but, originally there were some 22 million rows in there. Seems your data file was corruped too not only the indexes. and probably broke when updating the 491st registry... try use myisamchk -e -e, --extend-check Try to recover every possible row from the data file Normally this will also find a lot of garbage rows; Don't use this option if you are not totally desperate. it could take a very long time to run also... be warned! ;) --rengolin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Question on Composite Index
Dear All, Just to get a better understanding of how indices work in MySQL - If I have a Innodb table with a composite primary key (fld1,fld2,fld3,fld4,fld5), then my understanding is that MySQL optimizes just the leftmost primary key (fld1 in this case). Hence a query like select * from tbl1 where fld2 900 would result in a full table scan even though it's part of the composite key but select * from tbl1 where fld1 900 would be extremely quicker since it would search based on Index pages. Is my understanding correct? If so, how can we get around this issue ? In real-life databases you will always run in cases where you end up making a composite key on table. One possible solution would be to create non-unique, non-primary index on each of fld2,fld3,fld4,fld5 but then the inserts would be horribly slow hence was wondering if I am totally missing a very clean solution to the whole issue. Your kind help would be greatly appreciated! Regards Manoj
OS X Gui?
I am looking for a nice OS X GUI client, can be java for osx that works w/ mysql 5.x. (these don't work MacSQL, CocaMySQL, YouSQL. MySQK admin does work, but I want a bit more, like Maestro for OS X). what should I use? tia, .V -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
5.x client for os x (navicat does not work) what to use?
I am looking for a nice OS X GUI client, can be java for osx that works w/ mysql 5.x. (these don't work MacSQL, CocaMySQL, YouSQL. MySQK admin does work, but I want a bit more, like Maestro for OS X). what should I use? tia, .V -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: I need some help
- Original Message - From: Michael J. Pawlowsky [EMAIL PROTECTED] To: Rhino [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Wednesday, April 06, 2005 7:51 PM Subject: Re: I need some help Rhino wrote: - Original Message - From: iNFERNo [EMAIL PROTECTED] To: mysql mysql@lists.mysql.com Sent: Wednesday, April 06, 2005 5:23 PM Subject: I need some help Hi, I have a problem: I am using mysql 4.0.24 and I need to make some reports from a database: mysql describe events; +-+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-++ | id | int(10) unsigned | | PRI | NULL| auto_increment | | user_id | varchar(55) | | | 0 | | | dep_id | int(15) | | | 0 | | | event_id | int(15) | | | 0 | | | year| int(15) | | | 0 | | | day | int(15) | | | 0 | | | month | int(15) | | | 0 | | | ev_status | int(11) | YES | | 0 | | | ev_type | int(11) | YES | | 0 | | | ev_priority | int(11) | YES | | 0 | | +-+--+--+-+-++ I need something to get: | USER_ID | DEP_ID | EV_STATUS = 0 | EV_STATUS = 1 | EV_STATUS = 2 | EV_STATUS = 3 | EV_STATUS = 4 | EV_STATUS = 5 | EV_STATUS = 6 | user_1 1COUNT COUNT COUNT COUNT COUNT COUNT COUNT user_2 1COUNT COUNT COUNT COUNT COUNT COUNT COUNT user_3 2COUNT COUNT COUNT COUNT COUNT COUNT COUNT . . . The problem is that the way I am getting this now is with a query for each user and for each ev_status and the output is in PHP. Is there a better way to do this ? I am only a beginner in MySQL and want to learn more and improve. First of all, congratulations on identifying your version of MySQL and giving the definition of your table. That is an excellent start to getting an answer to your question. Far too many people post here without identifying their MySQL version or giving the definition of their tables, making it very difficult to answer their questions without having to ask many followup questions. Am I missing something... but isn't id defined as the primary key? You are absolutely right; I'm sorry, I missed the primary key designation. (I was expecting the primary key definition after the last column definition which, now that I think about it, was just plain wrong!) On a different topic.. if you want to make your life easy with PHP. Instead of saving, the year, month, day... personally I always simply use an int and save all dates as Unix Timestamps. As long as you are working more or less in this century, you will be fine. That is a personal choice, from someone that has built MANY calendars. As for selecting all of them like that. I question why.. but SELECT user_id, dep_id, IF(ev_status=1,1,0), IF(ev_status=2,10) ... and so on. But I really don't get why you would be doing that I'm afraid I'm a bit confused too: if there really is just one row per ID, what would that row look like? There would have to be a single value for ev_status so wouldn't you just report that value? What is there to count? A small bit of sample data would make it easier to visualize what you want and why you want it. Rhino -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.9.3 - Release Date: 05/04/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
