SELECT by LEFT(col,1)={letter} ?? improvements and sarge update to 4.1
Hello dear all-mighty list :)
I do run mysql in 4.0.24 and have the following very slow query.
select tbl3.colname from tbl
.. (some left joins)
where
left(colname,1)={letter}
the tbl.col has no index nore will it ever get one because auf
the tbl structre. (this would make no sense becaus only 8tsd.)
rows in the table total 82tsd rows have to get selected like
this.
As you might guess the query is much too slow.
Now I have the chance (and time) to do the following.
Doing a seperate table for this on col like
++-+
| id | colnames |
++-+
how would i have to index it? I think colnames should get
a fulltext-field but how would it beccorect? Just one letter
or do the whole field.
colnames type = text (varchar might be ok with 255 chars).
What would you suggest?
==
the other thing is that I would like to update to 4.1.x on
my debian sarge. Has anybody done this yet?
do I have to just: apg-get remove mysql ?
and apg-get install mysql-4.1
wich packages are needed.. will there be much mor performance?
Bon Vibes and Thank you
mathias
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT by LEFT(col,1)={letter} ?? improvements and sarge update to 4.1
Hi, you should have posted 2 threads ;) the other thing is that I would like to update to 4.1.x on my debian sarge. Has anybody done this yet? do I have to just: apg-get remove mysql ? and apg-get install mysql-4.1 just apt-get install mysql-4.1 to get things runs smoothly (which would uninstall the previous mysql, switch if off, install the new one, and start this one again). Still have a look at the options changes between both versions. (should not be a problem though). -- 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: What does this error mean?
Hello. Please, check if the problem exists on the latest release (4.1.13 now). It might be a bug like: http://bugs.mysql.com/bug.php?id=9703 Octavian Rasnita [EMAIL PROTECTED] wrote: Hi, I have tried the following query and it works fine. It takes 11 seconds and this is a little too much, but this is another issue. The problem is that if I delete the following condition from it: a.id_categories=31 The query gives the following error: ERROR 1032 (HY000): Can't find record in '' What can I do to make it work? Thank you. select straight_join sql_calc_found_rows a.hash, a.pre_title, a.title, a.post_title, substring(a.body, 1, 250) as preview, a.hash, a.date, a.time, length(a.body) as size, a.id_categories, n.name as newspaper, sc.category, count(act.id) as visitors, count(aco.hash) as comments from articles a inner join newspapers n on(a.id_newspapers=n.id) inner join sections s on(a.id_sections=s.id and n.id=s.id_newspapers) inner join sections_categories sc on(a.id_categories=sc.id) left join articles_count act on(a.hash=act.hash_articles) left join articles_comments aco on(a.hash=aco.hash_articles) where a.id_categories=31 and a.date between '2005-01-01' and '2005-12-31' and a.active_view=1 group by a.hash order by visitors limit 0,30; Teddy -- 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: Help me....i strucke down...
Hello. This is a line from one of your files which were attached in archive: [EMAIL PROTECTED] Test_pgms]# gcc -c -I/usr/lib/bcc/include ^^ -I/usr/include/mysql createdb.c Are you using Borland C++ includes with gcc?! Ashok Kumar [EMAIL PROTECTED] wrote: [-- text/plain, encoding 8bit, charset: iso-8859-1, 25 lines --] Hi friends, In the previous mail i mentioned abt the error in installing mysql4.1 on Linux9. that is still continuing, so i tried with 4.0. It's installed successfully. Now i'm having some doubts on running mysql client pgms on Linux. My sys config is, 1.Intel P4, Intel 845 Chipset and Intel board. 2.Linux 9.0 3.gcc version GCC 3.2.2 20030222 (Red Hat Linux 3.2.2-5) With this mail i attached the mysql client pgm and as well as the error msg which i'm getting. Pls, help me in this. Thanks and Regards, Ashok. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com [-- application/zip, encoding base64, 79 lines, name: err.zip --] [-- Description: 1535120561-err.zip --] [-- text/plain, encoding 7bit, charset: us-ascii, 5 lines --] -- 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: InnoDB migration between servers
Hello.
When I strace the mysqld process it doesn't do anything, it's waiting
Do you use the official binaries? There're additional InnoDB monitors
mentioned at:
http://dev.mysql.com/doc/mysql/en/innodb-monitor.html
which could provide more information.
When I strace the mysqld process it doesn't do anything, it's waiting
MySQL trace files often help. See:
http://dev.mysql.com/doc/mysql/en/making-trace-files.html
Andreas Unterkircher [EMAIL PROTECTED] wrote:
Hello list,
I'm having some mysterious problems when I try to migrate several
databases from one server to another one. This
databases contain some InnoDB tables - db size is around ~3GB. On both
servers I'm using MySQL version 4.1.12,
the same kernel version, same libc (2.3.2), but different architectures
(source x86, target amd64). Filesystem on both
side are xfs formated (but also tried with reiser ext3).
I tried two ways to transfer the databases between the servers:
*) No running mysqld on both servers (successfully shutdown, no crash).
Copying the whole bunch of datafiles
(/var/lib/mysql) with rsync/scp to the other machine (files are ok,
md5check). Startup - Everything ok
*) Active mysqld, both sides no clients connected. mysqldump (--opt
--single-transaction) from the source server,
copy the dump to the target server (dump is fine, md5check). Import on
the target server - Everything ok
Now I let my clients connecting to the new server.
As soon as there is any data-changing query (INSERT, UPDATE) on one of
the InnoDB tables on the new server,
the query hangs - the state is update. All other queries which also
wants to update some data gets state locked
(like it should be).
Process-List:
mysql show processlist\G
*** 2. row ***
Id: 16
User: sfz.info
Host: lskeletor.:51828
db: db_sfz
Command: Query
Time: 2524
State: update
Info: INSERT INTO 4images_sessionvars
(session_id, sessionvars_name, sessionvars_value)
*** 3. row ***
Id: 34
User: sfz.info
Host: lskeletor:52044
db: db_sfz
Command: Query
Time: 2455
State: Locked
Info: DELETE FROM 4images_sessionvars
WHERE session_id NOT IN ('37b5643b556224e8c6e43e11aa
3 rows in set (0.00 sec)
But SHOW INNODB STATUS doesn't showup these transactions:
mysql show innodb status \G
*** 1. row ***
Status:
=
050726 19:57:16 INNODB MONITOR OUTPUT
=
Per second averages calculated from the last 34 seconds
--
SEMAPHORES
--
OS WAIT ARRAY INFO: reservation count 1163, signal count 1160
Mutex spin waits 1129, rounds 7484, OS waits 220
RW-shared spins 1848, OS waits 922; RW-excl spins 20, OS waits 12
TRANSACTIONS
Trx id counter 0 1797
Purge done for trx's n:o 0 0 undo n:o 0 0
History list length 0
Total number of lock structs in row lock hash table 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, process no 15450, OS thread id 1134426480
MySQL thread id 13, query id 1254 localhost root
show innodb status
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: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (write thread)
Pending normal aio reads: 0, aio writes: 0,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
537 OS file reads, 31672 OS file writes, 5620 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-
INSERT BUFFER AND ADAPTIVE HASH INDEX
-
Ibuf for space 0: size 1, free list len 0, seg size 2, is empty
Ibuf for space 0: size 1, free list len 0, seg size 2,
355 inserts, 355 merged recs, 41 merges
Hash table size 553253, used cells 4527, node heap has 8 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 0 797420905
Log flushed up to 0 797420905
Last checkpoint at 0 797420905
0 pending log writes, 0 pending chkp writes
2464 log i/o's done, 0.00 log i/o's/second
--
BUFFER POOL AND MEMORY
--
Total memory allocated 329850640; in additional pool allocated 2808320
Buffer pool size 16384
Free buffers 0
Database pages 16376
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 701, created 54627, written 91113
0.00 reads/s, 0.00 creates/s, 0.00
Re: Date/Time Problem with V5.0.6 views
Hello. You said that you had created a view, but you continued using Tab_A instead of Tab_A_View in your next queries. Did you want to use view Tab_A_View? On my MySQL 5.0.9 all queries works both with view and original table. See: mysql desc Tab_A; +++--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +++--+-+-+---+ | datetime_field | datetime | NO | | | | | num_field | bigint(20) | NO | | | | +++--+-+-+---+ mysql desc Tab_A_View; +++--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +++--+-+-+---+ | datetime_field | datetime | NO | | -00-00 00:00:00 | | | num_field | bigint(20) | NO | | 0 | | +++--+-+-+---+ mysql Select datetime_field, num_field from Tab_A_View where datetime_field='2005-03-10' andnum_field = 1234; +-+---+ | datetime_field | num_field | +-+---+ | 2005-03-10 00:00:00 | 1234 | | 2005-03-10 00:00:00 | 1234 | +-+---+ mysql Select datetime_field, num_field from Tab_A where datetime_field='2005-03-10' andnum_field = 1234; +-+---+ | datetime_field | num_field | +-+---+ | 2005-03-10 00:00:00 | 1234 | | 2005-03-10 00:00:00 | 1234 | +-+---+ [EMAIL PROTECTED] wrote: Hello everyone, I cannot figure this out. I have a table like the following: Tab_A datetime_field datetime not null, num_field bigint not null I do a SELECT as follows: Select datetime_field, num_field from Tab_A where datetime_field='2005-03-10' and num_field = 1234; I return 2 rows correctly. I then create the view: Create view Tab_A_View as select * from Tab_A; I do a SELECT as follows: Select datetime_field, num_field from Tab_A where datetime_field='2005-03-10' and num_field = 1234; 0 rows returned! HOWEVER, IF I do the query as such: Select datetime_field, num_field from Tab_A where datetime_field='2005-03-10 00:00:00' and num_field = 1234; 2 rows returned correctly. Is there an implementation difference? Regards, George __ 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 -- 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: optimize a sql statement
Hello. May be I'm wrong, but most of the time, your query is spending in sorting results (you can check it with SHOW PROCESSLIST). If you can't change it, you could increase the value of tmp_table_size to use in-memory tables, if you have enough RAM. $ $ [EMAIL PROTECTED] wrote: I analyzed the query plan again. --- 1, 'PRIMARY', 'orders', 'range', 'PRIMARY', 'PRIMARY', '4', '', 348660, 'Using where; Using temporary; Using filesort' 1, 'PRIMARY', 'order_line', 'ref', 'PRIMARY,order_line_ol_i_id,order_line_ol_o_id,order_line_test', 'order_line_ol_o_id', '4', 'tpcw.orders.o_id', 1, 'Using where' 2, 'DEPENDENT SUBQUERY', 'orders', 'ref', 'PRIMARY,orders_o_c_id','orders_o_c_id', '5', 'func', 1, 'Using where; Using index' 2, 'DEPENDENT SUBQUERY', 'order_line', 'ref', 'PRIMARY,order_line_ol_i_id,order_line_ol_o_id,order_line_test', 'order_line_ol_o_id', '4', 'tpcw.orders.o_id', 1, 'Using where' 3, 'SUBQUERY', '', '', '', '', '', '', , 'Select tables optimized away' - In the plan,i find there are 348660 row scan in table orders.And in the sql statement,orders.o_id is be used to join operation and orders.o_c_id be used in the where statement.So i create a index orders_test on table orders(o_id and o_c_id). After create the index.I execute the explain again.Follow is the result: --- 1, 'PRIMARY', 'orders', 'range', 'PRIMARY,orders_test', 'PRIMARY', '4', '', 348660, 'Using where; Using temporary; Using filesort' 1, 'PRIMARY', 'order_line', 'ref', 'PRIMARY,order_line_ol_i_id,order_line_ol_o_id,order_line_test', 'order_line_ol_o_id', '4', 'tpcw.orders.o_id', 1, 'Using where' 2, 'DEPENDENT SUBQUERY', 'orders', 'ref', 'PRIMARY,orders_o_c_id,orders_test', 'orders_o_c_id', '5', 'func', 1, 'Using where; Using index' 2, 'DEPENDENT SUBQUERY', 'order_line', 'ref', 'PRIMARY,order_line_ol_i_id,order_line_ol_o_id,order_line_test', 'order_line_ol_o_id', '4', 'tpcw.orders.o_id', 1, 'Using where' 3, 'SUBQUERY', '', '', '', '', '', '', , 'Select tables optimized away' I find the index order_test be set in the possible_keys but not set in the keys. So i alter the sql statement with use index statement. Follow is the explain output after alter: 1, 'PRIMARY', 'orders', 'range', 'orders_test', 'orders_test', '4', '', 519210, 'Using where; Using index; Using temporary; Using filesort' 1, 'PRIMARY', 'order_line', 'ref', 'PRIMARY,order_line_ol_i_id,order_line_ol_o_id,order_line_test', 'order_line_ol_o_id', '4', 'tpcw.orders.o_id', 1, 'Using where' 2, 'DEPENDENT SUBQUERY', 'orders', 'ref', 'PRIMARY,orders_o_c_id,orders_test', 'orders_o_c_id', '5', 'func', 1, 'Using where; Using index' 2, 'DEPENDENT SUBQUERY', 'order_line', 'ref', 'PRIMARY,order_line_ol_i_id,order_line_ol_o_id,order_line_test', 'order_line_ol_o_id', '4', 'tpcw.orders.o_id', 1, 'Using where' 3, 'SUBQUERY', '', '', '', '', '', '', , 'Select tables optimized away' - Performance maybe much more bad. Now i have two question. One,Are there any probability for optimization performance using this way? Two,I am not able to calculate the cost time with the explain output.I have learned the knowlege about 7.2.2. Estimating Query Performance in help.But i can't understand. thanks From: $ $ [EMAIL PROTECTED] To: [EMAIL PROTECTED] CC: mysql@lists.mysql.com, [EMAIL PROTECTED] Subject: Re: optimize a sql statement Date: Thu, 21 Jul 2005 18:46:32 +0800 Thank you SGreen.But i can'optimize the sql statement like your way because this query must describe in one sql statement.So i think i maybe optimize this sql statement through creating high efficiency index or describe this sql statement in other method. Rewrite this sql statement must in one sql statement. Follow is the original sql statement: - SELECT ol_i_id FROM orders, order_line WHERE orders.o_id = order_line.ol_o_id and o_id0 AND not(order_line.ol_i_id = 5000) AND orders.o_c_id IN (SELECT o_c_id FROM orders, order_line WHERE orders.o_id = order_line.ol_o_id AND orders.o_id (SELECT MAX(o_id)-1 FROM orders) AND order_line.ol_i_id = 5000) GROUP BY ol_i_id ORDER BY SUM(ol_qty)DESC limit 5 - Follow is the original explain output: -- 1, 'PRIMARY', 'orders', 'range',
free MySQL conversion to MSSQL tool
Does anyone know a free tool to convert MySQL to MSSQL Thank you Andrew -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: free MySQL conversion to MSSQL tool
Am Thursday 28 July 2005 11:19 schrieb [EMAIL PROTECTED]: Does anyone know a free tool to convert MySQL to MSSQL mysqldump? Seriously, what do you expect? A tool which transforms mysql files on disk to mssql files? I don't think this exists and would probably be hardly possible. Perhaps there are tools reading from a (mysql) db and writing to another (mssql) database via a programming language. But conversion of database files? I don't think this exists. Stefan Thank you Andrew -- Stefan Kuhn M. A. Cologne University BioInformatics Center (http://www.cubic.uni-koeln.de) Zülpicher Str. 47, 50674 Cologne Tel: +49(0)221-470-7428 Fax: +49 (0) 221-470-7786 My public PGP key is available at http://pgp.mit.edu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: free MySQL conversion to MSSQL tool
So all I can do is copy tables as they already exist? I obviously need to keep the structure in place Andrew Am Thursday 28 July 2005 11:19 schrieb [EMAIL PROTECTED]: Does anyone know a free tool to convert MySQL to MSSQL mysqldump? Seriously, what do you expect? A tool which transforms mysql files on disk to mssql files? I don't think this exists and would probably be hardly possible. Perhaps there are tools reading from a (mysql) db and writing to another (mssql) database via a programming language. But conversion of database files? I don't think this exists. Stefan Thank you Andrew -- Stefan Kuhn M. A. Cologne University BioInformatics Center (http://www.cubic.uni-koeln.de) Zülpicher Str. 47, 50674 Cologne Tel: +49(0)221-470-7428 Fax: +49 (0) 221-470-7786 My public PGP key is available at http://pgp.mit.edu -- 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]
making mysql-client for windows behave
Hi all I installed mysql client on a windows box and created a my.conf file with the following: [client] server=servername user=username When I type the full path of the executable, it reads the conf file, and takes appropriate action. However, when I use the shortcut that is created by the mysql installer, it does not behave properly. It keeps trying to connect to localhost. I wouldn't care less, if not for the mysql icon, which seems to be embedded in the shortcut and cannot be applied elsewhere. With kind regards Andy -- Registered Linux User Number 379093 Now listening to Top! Radio Live Stream: mms://broadcast.ionip.be/topradioHigh amaroK::the Coolest Media Player in the known Universe! Cockroaches and socialites are the only things that can stay up all night and eat anything. Herb Caen -- -- --BEGIN GEEK CODE BLOCK- Version: 3.1 GAT/O/E$ d-(---)+ s:(+): a--(-)? C$(+++) UL$ P-(+)++ L+++$ E---(-)@ W++$ !N@ o? !K? W--(---) !O !M- V-- PS++(+++) PE--(-) Y+ PGP++(+++) t+(++) 5-- X++ R*(+)@ !tv b-() DI(+) D+(+++) G(+) e$@ h++(*) r--++ y--() -- ---END GEEK CODE BLOCK-- -- Check out these few php utilities that I released under the GPL2 and that are meant for use with a php cli binary: http://www.vlaamse-kern.com/sas/ -- pgpguXTBOKmOa.pgp Description: PGP signature
Re: free MySQL conversion to MSSQL tool
[EMAIL PROTECTED] wrote: Does anyone know a free tool to convert MySQL to MSSQL Thank you Andrew phpMyAdmin (http://www.phpmyadmin.net/) version 2.6.3-pl1 has the option to export the data in mssql format (and much others). You need a php enabled web server able to connect to your mysql database. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql v5.0
Hello All! I have a question. I'm using the mysql 4.0 but I want change 5.0 because I would like to use some features. (Subselect etc) Was 5.0 ever used in bigger system? Is 5.0 more realible than 4.0? Where can I find information about critical bugs and errors of 5.0? Thanx! Zoli -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql v5.0
Gyurasits Zoltán wrote: Hello All! I have a question. I'm using the mysql 4.0 but I want change 5.0 because I would like to use some features. (Subselect etc) Maybe some of features you want are already included in 4.1 series, try a search before switch to 5.0 for a production system. Was 5.0 ever used in bigger system? yes it has, configured as replication slave. some thousand of query/hour , some gigs of data. Is 5.0 more realible than 4.0? obviously not, it's still beta software. Also beta software is subject of bigger changes than stable. Where can I find information about critical bugs and errors of 5.0? Search bugs.mysql.com for bugs open on 5.0 (298 atm) http://bugs.mysql.com/search.php?search_for=status=Activeseverity=limit=10order_by=cmd=displaydirection=ASCbug_type=Anyphp_os=phpver=5.0bug_age=0; Thanx! Zoli -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 5.0.10-beta has been released
Hi, MySQL 5.0.10-beta, a new version of the popular Open Source Database Management System, has been released. It includes support for Stored Procedures, Triggers, Views and many other new enhancements. The Community Edition is now available in source and binary form for a number of platforms from our mirror sites via the download pages: http://dev.mysql.com/downloads/mysql/5.0.html Note that not all mirror sites may be up-to-date at this point. If you cannot find this version on a particular mirror, please try again later or choose another download site. This is the sixth published Beta release in the 5.0 series. All attention will continue to be focused on fixing bugs and stabilizing 5.0 for later production release. NOTE: This Beta release, as any other pre-production release, should not be installed on ``production'' level systems or systems with critical data. It is good practice to back up your data before installing any new version of software. Although MySQL has done its best to ensure a high level of quality, protect your data by making a backup as you would for any software beta release. Please refer to our bug database at http://bugs.mysql.com/ for more details about the individual bugs fixed in this version. News from the ChangeLog: Functionality added or changed: * Security improvement: Applied a patch that addresses a zlib data vulnerability that could result in a buffer overflow and code execution. (CAN-2005-2096 (http://cve.mitre.org/cgi-bin/cvename.cgi?name=CAN-2005-2096)) (Bug #11844 (http://bugs.mysql.com/11844)) * The viewing of triggers and trigger metadata has been enhanced as follows: + An extension to the SHOW command has been added: SHOW TRIGGERS can be used to view a listing of triggers. See Section 13.5.4.20, SHOW TRIGGERS Syntax for details. + The INFORMATION_SCHEMA database now includes a TRIGGERS table. See Section 22.1.16, The INFORMATION_SCHEMA TRIGGERS Table for details. (Bug #9586 (http://bugs.mysql.com/9586)) * On Windows, the search path used by MySQL applications for my.ini now includes ..\my.ini (that is, the application's parent directory, and hence, the installation directory). (Bug#10419 (http://bugs.mysql.com/10419)) * Added mysql_get_character_set_info() C API function for obtaining information about the default character set of the current connection. * The bundled version of the readline library was upgraded to version 5.0. * It is no longer necessary to issue an explicit LOCK TABLES for any tables accessed by a trigger prior to executing any statements that might invoke the trigger. (Bug #9581 (http://bugs.mysql.com/9581), Bug #8406 (http://bugs.mysql.com/8406)) * MySQL Cluster: A new -p option is available for use with the ndb_mgmd client. When called with this option, ndb_mgmd prints all configuration data to stdout, then exits. * The namespace for triggers has changed. Previously, trigger names had to be unique per table. Now they must be unique within the schema (database). An implication of this change is that DROP TRIGGER syntax now uses a schema name instead of a table name (schema name is optional and, if omitted, the current schema will be used). Note: When upgrading from a previous version of MySQL 5 to MySQL 5.0.10 or newer, you must drop all triggers before upgrading and re-create them after or DROP TRIGGER will not work after the upgrade. (Bug #5892 (http://bugs.mysql.com/5892)) Bugs fixed: * NDB_MGMDwasleakingfiledescriptors. (Bug #11898 (http://bugs.mysql.com/11898)) * IP addresses not shown in ndb_mgm SHOW command on second ndb_mgmd (or on ndb_mgmd restart). (Bug #11596 (http://bugs.mysql.com/11596)) * Functions that evaluate to constants (such as NOW() and CURRENT_USER() were being evaluated in the definition of a VIEW rather than included verbatim. (Bug #4663 (http://bugs.mysql.com/4663)) * Execution of SHOW TABLES failed to increment the Com_show_tables status variable. (Bug #11685 (http://bugs.mysql.com/11685)) * For execution of a stored procedure that refers to a view, changes to the view definition were not seen. The procedure continued to see the old contents of the view. (Bug #6120 (http://bugs.mysql.com/6120)) * For prepared statements, the SQL parser did not disallow '?' parameter markers immediately adjacent to other tokens, which could result in malformed statements in the binary log. (For example, SELECT * FROM t WHERE? = 1 could become SELECT * FROM t WHERE0 = 1.) (Bug #11299 (http://bugs.mysql.com/11299)) * When two threads compete for the same table, a deadlock could occur if one thread has also a lock on another table through LOCK TABLES and the thread is attempting to remove the table in some manner and the other threadwant
RE: free MySQL conversion to MSSQL tool
Although finding a tool that will automatically transfer files from MySQL to MS SQL format will be hard to do, both will accept txt files that have the CREATE statements and data in SQL. mysqldump will do this for you. http://dev.mysql.com/doc/mysql/en/mysqldump.html Just export the files to an .SQL file and load it into MS SQL. So your command will be something like: shell /path/to/mysql/bin/mysqldump --opt -u [username] -p [password] File_Name.SQL If you want a tool to do it for you, try dbTools software (http://www.dbtools.com.br). It lets you copy tables from one database to another, is PHP/ASP/JSP independent (as it is 3rd party), and has other data management tools. However, you must have a Windows box in order to use it. I do not believe they make a Linux version. HTH, J.R. -Original Message- From: Bastian Balthazar Bux [mailto:[EMAIL PROTECTED] Sent: Thursday, July 28, 2005 6:13 AM To: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Subject: Re: free MySQL conversion to MSSQL tool [EMAIL PROTECTED] wrote: Does anyone know a free tool to convert MySQL to MSSQL Thank you Andrew phpMyAdmin (http://www.phpmyadmin.net/) version 2.6.3-pl1 has the option to export the data in mssql format (and much others). You need a php enabled web server able to connect to your mysql database. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] smime.p7s Description: S/MIME cryptographic signature
Message could not be delivered
The original message was received at Mon, 25 Jul 2005 09:55:55 +0900 from knoware.nl [57.7.174.117] - The following addresses had permanent fatal errors - mysql@lists.mysql.com - Transcript of session follows - ... while talking to host 11.171.104.62: 554 mysql@lists.mysql.com... Message is too large 554 mysql@lists.mysql.com... Service unavailable -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Doubt about query optimization
Eric, Can you send us the actual show indexes from table and explain output that isn't shortend? Thank you for answer my question. Actually, the real query and tables are much more complex than the data in the previous message. A just tryed to optimize the information for you better understand the trouble. I think found what´s happening. A SELECT WHERE city = 1 returns more rows thant a full table scan in the table front (the real states that appear in the front page). So, it seems MySQL choose the second option, once it has less rows to optimize. Thanks, Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
simple query on an indexed col in big table is extremely slow
Hi, there, I am have a hard time figuring out why a simple query is extremely slow. I would greatly appreciate if you can shed some light! The table is in InnoDB: CREATE TABLE `rps_hits` ( `gi` int(10) unsigned NOT NULL default '0', `cddid` int(10) unsigned NOT NULL default '0', `bit_score` float NOT NULL default '0', `evalue` double NOT NULL default '0', `identity` smallint(5) unsigned NOT NULL default '0', `query_from` smallint(5) unsigned NOT NULL default '0', `query_to` smallint(5) unsigned NOT NULL default '0', `hit_from` smallint(5) unsigned NOT NULL default '0', `hit_to` smallint(5) unsigned NOT NULL default '0', `hit_len` smallint(5) unsigned NOT NULL default '0', `align_len` smallint(5) unsigned NOT NULL default '0', `bz_alignment` blob NOT NULL, KEY `gi` (`gi`), KEY `cddid` (`cddid`), KEY `evalue` (`evalue`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 MAX_ROWS=1410065408 AVG_ROW_LENGTH=300 It is a big table with more than 60 million rows, the rps_hits.ibd file is 22 G. All the queries I mention below were run when no other job were running against the database. I did a very simple query against the table: select gi, cddid, evalue from rps_hits where cddid=3161. It took 4 minutes and 29.90 seconds to pull out only 1952 rows. Whereas another simply query on gi select gi, cddid, evalue from rps_hits where gi=393396 pulled out 1532 rows in just 0.09 second. Expalin the above query gave: mysql explain select gi, cddid, evalue from rps_hits where cddid=3161\G *** 1. row *** id: 1 select_type: SIMPLE table: rps_hits type: ref possible_keys: cddid key: cddid key_len: 4 ref: const rows: 1376 Extra: Using where 1 row in set (0.06 sec) It looks OK. Then I did show index from rps_hits, all the index properties for each of the 3 indexes are the same except cardinality. Column gi has a cardinality of 3084286 and cddid has 14. Though a specific select count(distinct cddid) from rps_hits returned 11156. Since 11156 unique cddid is less than 0.01% of the total number of rows in the table, I believed the server decided to do a full table scan (does anyone know the exact percentage number of the total counts that MySQL uses as a criteria when deciding to do a FTS?) I then use use index in the query after I made sure the query and index were no longer in the cache : select gi, cddid, evalue from rps_hits use index (cddid) where cddid=3161. It still took a long time (2 min 59.79 sec) to return the 1952 rows. I also noticed that a simple query on evalue like select gi, cddid, evalue from rps_hits where evalue=1.97906; is also extremely slow even force index was used (5.78 sec for 56 rows). I have not been able to figure out what went wrong. Since the index on gi worked fine, I am just wondering if the slowness is caused by the large size of the table and that the indexes on cddid and evalue were created as second index and third index respectively in create table. However I have another huge table with blob column and with comparable size and number of rows to this rps_hits table, if I searched on the third index, it was very fast. This problem really troubled me and I would greatly appreciate if anyone could give me a hint. Thank you in advance! Regards, Zhe -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Persistent Corruption
MySQL 4.0.16 I am having this annoying persistent corruption issue, and am wondering if anyone has any suggestions. I have two tables that refuse to stay clean. After a myisamchk (below) they show corruption. I run a myisamchk -r, they get fixed, and the next day, they are once again corrupt. Finally, I did a mysqldump, dropped the tables, imported the data from the dump, and the next day - corrupt. I am at a loss, I thought the brute force method should clean it. I dont think it is the applicaiton itself, since there are an identical 24 tables (alphabet) that do not have this issue. Any help would be appreciated - Chris Checking MyISAM file: EmailMessage_c.MYI Data records: 79196 Deleted blocks: 22 myisamchk: warning: 1 clients is using or hasn't closed the table properly - check file-size - check key delete-chain - check record delete-chain - check index reference - check data record references index: 1 - check record links myisamchk: error: Record-count is not ok; is 79197Should be: 79196 myisamchk: warning: Found 457872 deleted space. Should be 459588 myisamchk: warning: Found 79507 partsShould be: 79506 parts MyISAM-table 'EmailMessage_c.MYI' is corrupted Fix it using switch -r or -o Checking MyISAM file: EmailMessage_j.MYI Data records: 39907 Deleted blocks: 91 myisamchk: warning: 1 clients is using or hasn't closed the table properly - check file-size - check key delete-chain - check record delete-chain - check index reference - check data record references index: 1 - check record links myisamchk: error: Record-count is not ok; is 39909Should be: 39907 myisamchk: warning: Found 719032 deleted space. Should be 742328 myisamchk: warning: Found 89 deleted blocks Should be: 91 myisamchk: warning: Found 40195 partsShould be: 40193 parts MyISAM-table 'EmailMessage_j.MYI' is corrupted Fix it using switch -r or -o -- -- please respond to the list .. if you need to contact me direct cgmckeever is the account prupref.com is the domain A href=http://www.prupref.com;Simply Chicago Real Estate/A -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Triggers
Am I reading the documentation correctly. I cannot us a trigger to insert a record into an other table??? I use triggers on other dbms to create an acivity histoy table. Fredrick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Persistent Corruption
Hi, Isn't there some information in the MySQL error log about what might have caused the corruption. Normally this would happen when disk or memory problem occurs. On Thursday 28 July 2005 17:45, Chris McKeever wrote: MySQL 4.0.16 I am having this annoying persistent corruption issue, and am wondering if anyone has any suggestions. I have two tables that refuse to stay clean. After a myisamchk (below) they show corruption. I run a myisamchk -r, they get fixed, and the next day, they are once again corrupt. Finally, I did a mysqldump, dropped the tables, imported the data from the dump, and the next day - corrupt. I am at a loss, I thought the brute force method should clean it. I dont think it is the applicaiton itself, since there are an identical 24 tables (alphabet) that do not have this issue. Any help would be appreciated - Chris Checking MyISAM file: EmailMessage_c.MYI Data records: 79196 Deleted blocks: 22 myisamchk: warning: 1 clients is using or hasn't closed the table properly - check file-size - check key delete-chain - check record delete-chain - check index reference - check data record references index: 1 - check record links myisamchk: error: Record-count is not ok; is 79197Should be: 79196 myisamchk: warning: Found 457872 deleted space. Should be 459588 myisamchk: warning: Found 79507 partsShould be: 79506 parts MyISAM-table 'EmailMessage_c.MYI' is corrupted Fix it using switch -r or -o Checking MyISAM file: EmailMessage_j.MYI Data records: 39907 Deleted blocks: 91 myisamchk: warning: 1 clients is using or hasn't closed the table properly - check file-size - check key delete-chain - check record delete-chain - check index reference - check data record references index: 1 - check record links myisamchk: error: Record-count is not ok; is 39909Should be: 39907 myisamchk: warning: Found 719032 deleted space. Should be 742328 myisamchk: warning: Found 89 deleted blocks Should be: 91 myisamchk: warning: Found 40195 partsShould be: 40193 parts MyISAM-table 'EmailMessage_j.MYI' is corrupted Fix it using switch -r or -o -- -- please respond to the list .. if you need to contact me direct cgmckeever is the account prupref.com is the domain A href=http://www.prupref.com;Simply Chicago Real Estate/A -- Dobromir Velev [EMAIL PROTECTED] http://www.websitepulse.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Persistent Corruption
On 7/28/05, Dobromir Velev [EMAIL PROTECTED] wrote: Hi, Isn't there some information in the MySQL error log about what might have caused the corruption. Normally this would happen when disk or memory problem occurs. thanks for the reply - from the .err file: 050722 5:20:24 read_next: Got error 127 when reading table ./atmail/EmailDatabase_c 050722 5:23:28 read_next: Got error 127 when reading table ./atmail/EmailDatabase_c 050722 5:23:29 read_next: Got error 127 when reading table ./atmail/EmailDatabase_c however, that was days ago, and since i ran the repairs, as well as the dump - reinsert On Thursday 28 July 2005 17:45, Chris McKeever wrote: MySQL 4.0.16 I am having this annoying persistent corruption issue, and am wondering if anyone has any suggestions. I have two tables that refuse to stay clean. After a myisamchk (below) they show corruption. I run a myisamchk -r, they get fixed, and the next day, they are once again corrupt. Finally, I did a mysqldump, dropped the tables, imported the data from the dump, and the next day - corrupt. I am at a loss, I thought the brute force method should clean it. I dont think it is the applicaiton itself, since there are an identical 24 tables (alphabet) that do not have this issue. Any help would be appreciated - Chris Checking MyISAM file: EmailMessage_c.MYI Data records: 79196 Deleted blocks: 22 myisamchk: warning: 1 clients is using or hasn't closed the table properly - check file-size - check key delete-chain - check record delete-chain - check index reference - check data record references index: 1 - check record links myisamchk: error: Record-count is not ok; is 79197Should be: 79196 myisamchk: warning: Found 457872 deleted space. Should be 459588 myisamchk: warning: Found 79507 partsShould be: 79506 parts MyISAM-table 'EmailMessage_c.MYI' is corrupted Fix it using switch -r or -o Checking MyISAM file: EmailMessage_j.MYI Data records: 39907 Deleted blocks: 91 myisamchk: warning: 1 clients is using or hasn't closed the table properly - check file-size - check key delete-chain - check record delete-chain - check index reference - check data record references index: 1 - check record links myisamchk: error: Record-count is not ok; is 39909Should be: 39907 myisamchk: warning: Found 719032 deleted space. Should be 742328 myisamchk: warning: Found 89 deleted blocks Should be: 91 myisamchk: warning: Found 40195 partsShould be: 40193 parts MyISAM-table 'EmailMessage_j.MYI' is corrupted Fix it using switch -r or -o -- -- please respond to the list .. if you need to contact me direct cgmckeever is the account prupref.com is the domain A href=http://www.prupref.com;Simply Chicago Real Estate/A -- Dobromir Velev [EMAIL PROTECTED] http://www.websitepulse.com/ -- -- please respond to the list .. if you need to contact me direct cgmckeever is the account prupref.com is the domain A href=http://www.prupref.com;Simply Chicago Real Estate/A -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Sleeping Processes Timeout?
Hello, Today, I started experiencing some issues on our website with max connections exceeded errors. In looking into this, I found that we had too many sleeping processes. I was not even able to login to the mysql server from a command line. Is there a way to set a timeout for sleeping processes? This is a small example of what I am seeing when I run a 'show processlist' query: +--+--+---+---+-+--+---+ --+ | Id | User | Host | db| Command | Time | State | Info | +--+--+---+---+-+--+---+ --+ | 584 | root | localhost | blogs | Sleep | 915 | | NULL | | 595 | root | localhost | blogs | Sleep | 900 | | NULL | +--+--+---+---+-+--+---+ --+ --- Thank You Jason Williard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sleeping Processes Timeout?
Jason Williard wrote: Hello, Today, I started experiencing some issues on our website with max connections exceeded errors. In looking into this, I found that we had too many sleeping processes. I was not even able to login to the mysql server from a command line. Is there a way to set a timeout for sleeping processes? This is a small example of what I am seeing when I run a 'show processlist' query: +--+--+---+---+-+--+---+ --+ | Id | User | Host | db| Command | Time | State | Info | +--+--+---+---+-+--+---+ --+ | 584 | root | localhost | blogs | Sleep | 915 | | NULL | | 595 | root | localhost | blogs | Sleep | 900 | | NULL | +--+--+---+---+-+--+---+ --+ --- Thank You Jason Williard Jason, Your web application is probably using persistent connections when it does not need them. The best thing to do, if that's the case, would be replace all the mysql_pconnect calls in your application with mysql_connect (or what ever the language-specific function name is). You could also set the wait_timeout value in your my.cnf to something shorter, but this would affect all your applications and does not address the source of your problem. Regards, Devananda vdv -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: making mysql-client for windows behave
Andy Pieters [EMAIL PROTECTED] wrote on 07/28/2005 05:06:46 AM: Hi all I installed mysql client on a windows box and created a my.conf filewith the following: [client] server=servername user=username When I type the full path of the executable, it reads the conf file,and takes appropriate action. However, when I use the shortcut that is created by the mysql installer, it does not behave properly. It keeps trying to connect to localhost. I wouldn't care less, if not for the mysql icon, which seems to be embedded in the shortcut and cannot be applied elsewhere. With kind regards Andy The quick answer is to modify the shortcut icon to use the full path (just as you did through the command line). Right-click the shortcut icon and bring up its properties. Change the Target setting to match your manual command line. Don't forget to use double quotes where appropriate. Actually, you may want to do this to a COPY of the shortcut icon. That way you don't lose any other functionality. BTW - I know you can call the config file anything you want but the default name for Windows systems is my.ini and it goes in the root folder of your windows version. On my machine it's C:\WINNT\ . If you don't want to change your shortcuts but have your client always connect to that server you just need to rename your my.conf file to my.ini and move it into the correct folder. Then MySQL will find it on its own. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Sleeping Processes Timeout?
I had a similar problem while connecting to mysql 4.0.21 throuhg MyODBC 3.51.11. If so you must upgrade mysql to 4.1 or downgrade MyOdbc to 3.51.06 Jason Williard wrote: Hello, Today, I started experiencing some issues on our website with max connections exceeded errors. In looking into this, I found that we had too many sleeping processes. I was not even able to login to the mysql server from a command line. Is there a way to set a timeout for sleeping processes? This is a small example of what I am seeing when I run a 'show processlist' query: +--+--+---+---+-+--+---+ --+ | Id | User | Host | db| Command | Time | State | Info | +--+--+---+---+-+--+---+ --+ | 584 | root | localhost | blogs | Sleep | 915 | | NULL | | 595 | root | localhost | blogs | Sleep | 900 | | NULL | +--+--+---+---+-+--+---+ --+ --- Thank You Jason Williard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Hour counts
Eric Bergen wrote: This does make his code fall under the limitations of unix timestamps. In 30 years or so when we are all retired millionaires ;) some poor intern is going to have to figure out why the hour diff calculation is failing. Long before then we will all be using 64 bit processors and a 64 bit signed integer for the unix timestamps. That will move the problem out about 292 billion years :) -- Chris W Gift Giving Made Easy Get the gifts you want give the gifts they want http://thewishzone.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
migrate Access to MySQL
Hello, I've been looking around for a while. Seems there are many options and tools that can help do it. Guidance and directions are highly welcome. We need to move the contents of a bunch of tables from Access to the existing tables on MySQL. The target tables in MySQL already have some information. So we need some how to map Access fields to MySQL fields. Anybody would like to share experience about how you did your migration and what kind of tool can best meet needs as such? Thanks much in advance, Bing -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Migration from ORACLE 9i to MySQL
I will be migrating Oracle database 9i to Mysql. Do anyone have any experience in doing this?. Please share with us! Thank you very much, V/R, Nguyen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: simple query on an indexed col in big table is extremely slow
Zhe Wang [EMAIL PROTECTED] wrote on 07/28/2005 10:40:08 AM: Hi, there, I am have a hard time figuring out why a simple query is extremely slow. I would greatly appreciate if you can shed some light! The table is in InnoDB: CREATE TABLE `rps_hits` ( `gi` int(10) unsigned NOT NULL default '0', `cddid` int(10) unsigned NOT NULL default '0', `bit_score` float NOT NULL default '0', `evalue` double NOT NULL default '0', `identity` smallint(5) unsigned NOT NULL default '0', `query_from` smallint(5) unsigned NOT NULL default '0', `query_to` smallint(5) unsigned NOT NULL default '0', `hit_from` smallint(5) unsigned NOT NULL default '0', `hit_to` smallint(5) unsigned NOT NULL default '0', `hit_len` smallint(5) unsigned NOT NULL default '0', `align_len` smallint(5) unsigned NOT NULL default '0', `bz_alignment` blob NOT NULL, KEY `gi` (`gi`), KEY `cddid` (`cddid`), KEY `evalue` (`evalue`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 MAX_ROWS=1410065408 AVG_ROW_LENGTH=300 It is a big table with more than 60 million rows, the rps_hits.ibd file is 22 G. All the queries I mention below were run when no other job were running against the database. I did a very simple query against the table: select gi, cddid, evalue from rps_hits where cddid=3161. It took 4 minutes and 29.90 seconds to pull out only 1952 rows. Whereas another simply query on gi select gi, cddid, evalue from rps_hits where gi=393396 pulled out 1532 rows in just 0.09 second. Expalin the above query gave: mysql explain select gi, cddid, evalue from rps_hits where cddid=3161\G *** 1. row *** id: 1 select_type: SIMPLE table: rps_hits type: ref possible_keys: cddid key: cddid key_len: 4 ref: const rows: 1376 Extra: Using where 1 row in set (0.06 sec) It looks OK. Then I did show index from rps_hits, all the index properties for each of the 3 indexes are the same except cardinality. Column gi has a cardinality of 3084286 and cddid has 14. Though a specific select count(distinct cddid) from rps_hits returned 11156. Since 11156 unique cddid is less than 0.01% of the total number of rows in the table, I believed the server decided to do a full table scan (does anyone know the exact percentage number of the total counts that MySQL uses as a criteria when deciding to do a FTS?) I then use use index in the query after I made sure the query and index were no longer in the cache : select gi, cddid, evalue from rps_hits use index (cddid) where cddid=3161. It still took a long time (2 min 59.79 sec) to return the 1952 rows. I also noticed that a simple query on evalue like select gi, cddid, evalue from rps_hits where evalue=1.97906; is also extremely slow even force index was used (5.78 sec for 56 rows). I have not been able to figure out what went wrong. Since the index on gi worked fine, I am just wondering if the slowness is caused by the large size of the table and that the indexes on cddid and evalue were created as second index and third index respectively in create table. However I have another huge table with blob column and with comparable size and number of rows to this rps_hits table, if I searched on the third index, it was very fast. This problem really troubled me and I would greatly appreciate if anyone could give me a hint. Thank you in advance! Regards, Zhe My first idea is to have you run ANALYZE TABLE against your table. Analyze table will update your index statistics (like cardinality). Your index cache may be too small or your the actual index may be too large to accomodate it in memory all at the same time. That means that you are using swap space to store part of your indexes on disk and it may be takeing just that much more time to crawl a paged index compared to one that can reside completely (or mostly) in memory. How many records are returned is only important if you and your server are connected by a slow network or if you are receiving HUGE quantities of data (gigabytes) in your results. The slower the network, the longer it will take to transfer the data from the server to you. However, most modern networks take that factor out of consideration for issues that you are describing. My suspicion is that you memory paging and cache sizes and disk performance are your most likey bottlenecks. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Newb learner question
I am learning MySQL from an older book, and some of the examples it give do not work in MySQL, so I am going to ask for help on those. select cust_contact from Customers where cust_contact like '[JM]%'; returns Empty set (0.00 sec) What is the right way to do this? Likewise: mysql select prod_name from Products where not vend_id = 'DLL01' order by prod_name; Empty set (0.00 sec) Bob Rea Dragon Networks 770-458-1350 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newb learner question
Bob Rea [EMAIL PROTECTED] wrote on 28/07/2005 18:19:34: I am learning MySQL from an older book, and some of the examples it give do not work in MySQL, so I am going to ask for help on those. select cust_contact from Customers where cust_contact like '[JM]%'; returns Empty set (0.00 sec) What is the right way to do this? Likewise: mysql select prod_name from Products where not vend_id = 'DLL01' order by prod_name; Empty set (0.00 sec) You have to give more information about what your database actally contains, and why you expected non-null results from those queries. Are you sure that your customers table contains a customer whose name starts [JM] ? Both commands look perfectly sensible to me. If your tables are small, post the results of Select * from customers ; or Select * from products ; Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: migrate Access to MySQL
Make a select query in Access whose result mimics the table layout of the target table in MySQL. Then export the query as tab-delimited text and import into the MySQL table using LOAD DATA INFILE. On Jul 28, 2005, at 12:32 PM, Bing Du wrote: Hello, I've been looking around for a while. Seems there are many options and tools that can help do it. Guidance and directions are highly welcome. We need to move the contents of a bunch of tables from Access to the existing tables on MySQL. The target tables in MySQL already have some information. So we need some how to map Access fields to MySQL fields. Anybody would like to share experience about how you did your migration and what kind of tool can best meet needs as such? Thanks much in advance, Bing -- 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: migrate Access to MySQL
I've used the exportSQL script (http://www.rot13.org/~dpavlin/projects/sql/exportSQL3.txt) to dump tables out of Access into a file that can be imported by mySQL. Since a lot of my work is porting Access apps to web apps, it comes in very handy. I've also written a really simple VB script that copies the records directly from Access to mySQL via ODBC. It uses DoCmd.TransferDatabase, if you'd like to write your own. I'd be happy to share it. -- Brett - Original Message - From: Bing Du [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, July 28, 2005 9:32 AM Subject: migrate Access to MySQL Hello, I've been looking around for a while. Seems there are many options and tools that can help do it. Guidance and directions are highly welcome. We need to move the contents of a bunch of tables from Access to the existing tables on MySQL. The target tables in MySQL already have some information. So we need some how to map Access fields to MySQL fields. Anybody would like to share experience about how you did your migration and what kind of tool can best meet needs as such? Thanks much in advance, Bing -- 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: Migration from ORACLE 9i to MySQL
Why are you going backwards MySql is an 8th grade toy. -Original Message- From: Nguyen, Phong [mailto:[EMAIL PROTECTED] Sent: Thursday, July 28, 2005 9:42 AM To: mysql@lists.mysql.com Subject: Migration from ORACLE 9i to MySQL I will be migrating Oracle database 9i to Mysql. Do anyone have any experience in doing this?. Please share with us! Thank you very much, V/R, Nguyen -- 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]
general question
Hi, I am using a MySQL database on a web site, and I would like to know what happends if someone searches in the database using a form, but after a few seconds MySQL starts the query, that user hit the Stop button of the browser. Will MySQL continue its searching and also create the cache, or it will stop automaticly? If it will also stop, can I do something to let it continue searching in order to create the cache and the next time another visitor searches for the same thing it will get the results from the cache? Sorry if this is a stupid question and thank you very much. Teddy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: migrate Access to MySQL
Hello, I've been looking around for a while. Seems there are many options and tools that can help do it. Guidance and directions are highly welcome. We need to move the contents of a bunch of tables from Access to the existing tables on MySQL. The target tables in MySQL already have some information. So we need some how to map Access fields to MySQL fields. Anybody would like to share experience about how you did your migration and what kind of tool can best meet needs as such? Thanks much in advance, Bing Thanks all who replied. I had no problem transfering Access tables to MySQL as they are. My problem is we need to munge the data first before they can be loaded into MySQL. I see coding is inevitable. Bing -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Persistent Corruption
I was running into this all the time. I actually had myisamchk running on a regular basis (live) via cron which I think was actually causing the corruption. By chance are you running myisamchk on the live tables?? I ran the REPAIR TABLE... on each table, shut down mysql THEN ran myisamchk again and my 127 errors seem o be gone (so far). What OS are you running BTW? Dan T On Jul 28, 2005, at 8:45 AM, Chris McKeever wrote: MySQL 4.0.16 I am having this annoying persistent corruption issue, and am wondering if anyone has any suggestions. I have two tables that refuse to stay clean. After a myisamchk (below) they show corruption. I run a myisamchk -r, they get fixed, and the next day, they are once again corrupt. Finally, I did a mysqldump, dropped the tables, imported the data from the dump, and the next day - corrupt. I am at a loss, I thought the brute force method should clean it. I dont think it is the applicaiton itself, since there are an identical 24 tables (alphabet) that do not have this issue. Any help would be appreciated - Chris Checking MyISAM file: EmailMessage_c.MYI Data records: 79196 Deleted blocks: 22 myisamchk: warning: 1 clients is using or hasn't closed the table properly - check file-size - check key delete-chain - check record delete-chain - check index reference - check data record references index: 1 - check record links myisamchk: error: Record-count is not ok; is 79197Should be: 79196 myisamchk: warning: Found 457872 deleted space. Should be 459588 myisamchk: warning: Found 79507 partsShould be: 79506 parts MyISAM-table 'EmailMessage_c.MYI' is corrupted Fix it using switch -r or -o Checking MyISAM file: EmailMessage_j.MYI Data records: 39907 Deleted blocks: 91 myisamchk: warning: 1 clients is using or hasn't closed the table properly - check file-size - check key delete-chain - check record delete-chain - check index reference - check data record references index: 1 - check record links myisamchk: error: Record-count is not ok; is 39909Should be: 39907 myisamchk: warning: Found 719032 deleted space. Should be 742328 myisamchk: warning: Found 89 deleted blocks Should be: 91 myisamchk: warning: Found 40195 partsShould be: 40193 parts MyISAM-table 'EmailMessage_j.MYI' is corrupted Fix it using switch -r or -o -- -- please respond to the list .. if you need to contact me direct cgmckeever is the account prupref.com is the domain A href=http://www.prupref.com;Simply Chicago Real Estate/A -- 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: Migration from ORACLE 9i to MySQL
Johnson, Michael [EMAIL PROTECTED] wrote on 07/28/2005 01:56:33 PM: Why are you going backwards MySql is an 8th grade toy. -Original Message- From: Nguyen, Phong [mailto:[EMAIL PROTECTED] Sent: Thursday, July 28, 2005 9:42 AM To: mysql@lists.mysql.com Subject: Migration from ORACLE 9i to MySQL I will be migrating Oracle database 9i to Mysql. Do anyone have any experience in doing this?. Please share with us! Thank you very much, V/R, Nguyen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Maybe the US Air Force has an unlimited budget but the rest of us do not. It seems to me that they powers that be in Nguyen's shop have made a decision (rational or not, you know how some managers are) to move away from a PREMIUM-priced package like 9i to something that can perform comparably to 9i but at a small fraction of the cost. Calling it an 8th grade toy makes you sound uninformed of what MySQL is really capable of. Sure MySQL may have a few fewer bells and whistles than Oracle but if you don't need to rely on all of the gee-whiz and just need fast, stable data storage and retrieval, MySQL is an excellent choice. Besides, most of those fancy things in the premium databases can be duplicated or nearly duplicated using very little client-side code. Of the things that cannot be run in client-side code (I am particularly thinking of stored procedures and triggers) those are coming in 5.0.x. Do you think NASA, Yahoo, and a host of other Fortune 100 companies made a mistake by using MySQL in their production enviroments? I don't. Respectfully, Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Migration from ORACLE 9i to MySQL
I got no experience in migrating Oracle to Mysql, but here might be what you were looking for: http://dev.mysql.com/downloads/migration-toolkit/1.0.html Scott On 7/28/05, Nguyen, Phong [EMAIL PROTECTED] wrote: I will be migrating Oracle database 9i to Mysql. Do anyone have any experience in doing this?. Please share with us! Thank you very much, V/R, Nguyen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Power to people, Linux is here.
Re: Migration from ORACLE 9i to MySQL
Johnson, Michael wrote: MySql is an 8th grade toy. So why are you here? Go haunt an Oracle mailing list. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: general question
Octavian Rasnita [EMAIL PROTECTED] wrote on 07/28/2005 02:18:05 PM: Hi, I am using a MySQL database on a web site, and I would like to know what happends if someone searches in the database using a form, but after a few seconds MySQL starts the query, that user hit the Stop button of the browser. Will MySQL continue its searching and also create the cache, or it will stop automaticly? If it will also stop, can I do something to let it continue searching in order to create the cache and the next time another visitor searches for the same thing it will get the results from the cache? Sorry if this is a stupid question and thank you very much. Teddy Here is what happens with nearly all web requests: 1) The browser (or some other tool) sends a message to a web server to get something. This usually because the user specifically asked for something, because an HTML page has tags in it for other content (like images), or by some other user action or client-side programming. Because you are specifically asking for something from the server, the specially formatted request is called either a Uniform Resource Locator (URL) or, in a more general sense, a Uniform Resource Identifier (URI). 2) the web server receives the URI and begins the process of providing what was requested. In the case of a scripted response, like your example, it make take some time for the server to complete assembling it's response. 3) the server responds with data/the browser receives the data. (Hopefully the browser will know what to do with whatever it asked for.) In your sample scenario, you said that between steps 2 and 3: 2.5) user clicks STOP in the browser. There aren't any messages in most of the internet protocols (HTTP, FTP, GOPHER, WAIS, etc) to cancel a response. The simplest thing for to do was to just ignore the response if it ever came. So to answer your question, the server doesn't know that the user is no longer interested in the information so it continues to process the request to its full and complete resolution. With some servers there are ways to detect if the browser is maintaining an open connection with the server (waiting on a response) but most server-side scripts do not check that status. Because they don't check that status, the script will not detect that the user has hung up waiting on it's response until it is ready to send the actual response data. The query completes, the cache is filled, and whatever effort went into formatting the response is just wasted. Since the user doesn't want it, the response is sent to the bit bucket. Make sense? Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: general question
Octavian, I am using a MySQL database on a web site, and I would like to know what happends if someone searches in the database using a form, but after a few seconds MySQL starts the query, that user hit the Stop button of the browser. Will MySQL continue its searching and also create the cache, or it will stop automaticly? Interactivity between the webserver and the database is server-side. So, when the user clicks on the stop button, it should simply ignore the response client-side. In other words: The server will complete his job and send you the result but your browser will ignore it. It´s just my thoughts. I´m not sure about it, but the logic is this. If it will also stop, can I do something to let it continue searching in order to create the cache and the next time another visitor searches for the same thing it will get the results from the cache? If you use query cache in server side (on database or on your programing language), yes. It should works. If you use cache base on proxy or in the client browser. Once the result was ignored, there is no page to cache. I hope this help you, Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: simple query on an indexed col in big table is extremely slow
Hi, Shawn, Thanks a lot for your reply. Running analyze table didn't help much since this table has not been updated after being built. The table has 3 single indexes. What puzzled me was that the queries against the first index were fast, only slow if against the second or third index. The column types are the same for the first and second index (int(10) unsigned). This is my first time to encounter a slow query on an indexed column. I have another table which has about 750 millions rows, a search against the index column (int(9) unsigned) has lightening speed. Does it mean the key cache on my machine is big enough? Below are the values of the cache variables. Which variable do you think need to be boosted up? Again, thank you very much or your help! +--+--+ | Variable_name| Value| +--+--+ | bdb_cache_size | 8388600 | | binlog_cache_size| 32768| | have_query_cache | YES | | key_cache_age_threshold | 300 | | key_cache_block_size | 1024 | | key_cache_division_limit | 100 | | max_binlog_cache_size| 18446744073709551615 | | query_cache_limit| 1048576 | | query_cache_min_res_unit | 4096 | | query_cache_size | 67108864 | | query_cache_type | ON | | query_cache_wlock_invalidate | OFF | | table_cache | 524288 | | thread_cache_size| 512 | +--+--+ Regards, Zhe [EMAIL PROTECTED] wrote: Zhe Wang [EMAIL PROTECTED] wrote on 07/28/2005 10:40:08 AM: Hi, there, I am have a hard time figuring out why a simple query is extremely slow. I would greatly appreciate if you can shed some light! The table is in InnoDB: CREATE TABLE `rps_hits` ( `gi` int(10) unsigned NOT NULL default '0', `cddid` int(10) unsigned NOT NULL default '0', `bit_score` float NOT NULL default '0', `evalue` double NOT NULL default '0', `identity` smallint(5) unsigned NOT NULL default '0', `query_from` smallint(5) unsigned NOT NULL default '0', `query_to` smallint(5) unsigned NOT NULL default '0', `hit_from` smallint(5) unsigned NOT NULL default '0', `hit_to` smallint(5) unsigned NOT NULL default '0', `hit_len` smallint(5) unsigned NOT NULL default '0', `align_len` smallint(5) unsigned NOT NULL default '0', `bz_alignment` blob NOT NULL, KEY `gi` (`gi`), KEY `cddid` (`cddid`), KEY `evalue` (`evalue`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 MAX_ROWS=1410065408 AVG_ROW_LENGTH=300 It is a big table with more than 60 million rows, the rps_hits.ibd file is 22 G. All the queries I mention below were run when no other job were running against the database. I did a very simple query against the table: select gi, cddid, evalue from rps_hits where cddid=3161. It took 4 minutes and 29.90 seconds to pull out only 1952 rows. Whereas another simply query on gi select gi, cddid, evalue from rps_hits where gi=393396 pulled out 1532 rows in just 0.09 second. Expalin the above query gave: mysql explain select gi, cddid, evalue from rps_hits where cddid=3161\G *** 1. row *** id: 1 select_type: SIMPLE table: rps_hits type: ref possible_keys: cddid key: cddid key_len: 4 ref: const rows: 1376 Extra: Using where 1 row in set (0.06 sec) It looks OK. Then I did show index from rps_hits, all the index properties for each of the 3 indexes are the same except cardinality. Column gi has a cardinality of 3084286 and cddid has 14. Though a specific select count(distinct cddid) from rps_hits returned 11156. Since 11156 unique cddid is less than 0.01% of the total number of rows in the table, I believed the server decided to do a full table scan (does anyone know the exact percentage number of the total counts that MySQL uses as a criteria when deciding to do a FTS?) I then use use index in the query after I made sure the query and index were no longer in the cache : select gi, cddid, evalue from rps_hits use index (cddid) where cddid=3161. It still took a long time (2 min 59.79 sec) to return the 1952 rows. I also noticed that a simple query on evalue like select gi, cddid, evalue from rps_hits where evalue=1.97906; is also extremely slow even force index was used (5.78 sec for 56 rows). I have not been able to figure out what went wrong. Since the index on gi worked fine, I am just wondering if the slowness is caused by the large size of the
Re: Persistent Corruption
On 7/28/05, Dan Tappin [EMAIL PROTECTED] wrote: I was running into this all the time. I actually had myisamchk running on a regular basis (live) via cron which I think was actually causing the corruption. By chance are you running myisamchk on the live tables?? I was - but not after all this crap started, but I still get those errors even after a rebuild I ran the REPAIR TABLE... on each table, shut down mysql THEN ran myisamchk again and my 127 errors seem o be gone (so far). I think I am going to put in a new harddrive and hope for tbe best What OS are you running BTW? RedHat 7.3 Dan T On Jul 28, 2005, at 8:45 AM, Chris McKeever wrote: MySQL 4.0.16 I am having this annoying persistent corruption issue, and am wondering if anyone has any suggestions. I have two tables that refuse to stay clean. After a myisamchk (below) they show corruption. I run a myisamchk -r, they get fixed, and the next day, they are once again corrupt. Finally, I did a mysqldump, dropped the tables, imported the data from the dump, and the next day - corrupt. I am at a loss, I thought the brute force method should clean it. I dont think it is the applicaiton itself, since there are an identical 24 tables (alphabet) that do not have this issue. Any help would be appreciated - Chris Checking MyISAM file: EmailMessage_c.MYI Data records: 79196 Deleted blocks: 22 myisamchk: warning: 1 clients is using or hasn't closed the table properly - check file-size - check key delete-chain - check record delete-chain - check index reference - check data record references index: 1 - check record links myisamchk: error: Record-count is not ok; is 79197Should be: 79196 myisamchk: warning: Found 457872 deleted space. Should be 459588 myisamchk: warning: Found 79507 partsShould be: 79506 parts MyISAM-table 'EmailMessage_c.MYI' is corrupted Fix it using switch -r or -o Checking MyISAM file: EmailMessage_j.MYI Data records: 39907 Deleted blocks: 91 myisamchk: warning: 1 clients is using or hasn't closed the table properly - check file-size - check key delete-chain - check record delete-chain - check index reference - check data record references index: 1 - check record links myisamchk: error: Record-count is not ok; is 39909Should be: 39907 myisamchk: warning: Found 719032 deleted space. Should be 742328 myisamchk: warning: Found 89 deleted blocks Should be: 91 myisamchk: warning: Found 40195 partsShould be: 40193 parts MyISAM-table 'EmailMessage_j.MYI' is corrupted Fix it using switch -r or -o -- -- please respond to the list .. if you need to contact me direct cgmckeever is the account prupref.com is the domain A href=http://www.prupref.com;Simply Chicago Real Estate/A -- 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] -- -- please respond to the list .. if you need to contact me direct cgmckeever is the account prupref.com is the domain A href=http://www.prupref.com;Simply Chicago Real Estate/A -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Total Sum of Monthly Counts for Older MySQL 4.0.22 (Please Help)
Hello. It is possible to obtain your results without temporary tables, but with user variables. For a pity you should execute three queries. With first query you're getting the Sum: select @all_sum:= count( employee_id) from table_employee where employee_hire_date between 2005-01-01 and 2005-4-30; With the second - number of month (this in most cases could be figured out without query - with direct computation in next query, or on client side): select @all_num:= count( distinct month(employee_hire_date)) from table_employee where employee_hire_date between 2005-01-01 and 2005-4-30; And with the last query you should get what you want: select employee_hire_date, count(employee_id), @all_sum as Sum, @all_sum/@all_num as Avg from table_employee where employee_hire_date between 2005-01-01 and 2005-4-30 group by month(employee_hire_date); Henry Chang [EMAIL PROTECTED] wrote: Hello MySQL users, Currently, I use MySQL 4.0.22 and I can do a straightforward count of employees hired for each month. select employee_hire_date, count(employee_id) from table_employee where employee_hire_date between 2005-01-01 and 2005-4-30 group by month(employee_hire_date) Date | Count | 2005-01-01 | 123 | 2005-02-01 | 50| 2005-03-01 | 76| 2005-04-01 | 89| However, I would like to do a grand total of the counts and the averages that would like the below. Date | Count | Sum | Avg | - 2005-01-01 | 123 | 338 | 84.5 | 2005-02-01 | 50| 338 | 84.5 | 2005-03-01 | 76| 338 | 84.5 | 2005-04-01 | 89| 338 | 84.5 | Since my MySQL version is 4.0.22, I am not able to use subquery and I prefer not to use tmp tables. What would be the right query to solve for the grand total sum and average?? Any help would be greatly appreciated!!! Thanks in Advance. Henry -- 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: Persistent Corruption
Hello. MySQL 4.0.16 You run an old MySQL. Upgrade to the latest release sometimes solves such issues. Check if the problem remains with MySQL 4.1.13 (or 4.0.25). Chris McKeever [EMAIL PROTECTED] wrote: MySQL 4.0.16 I am having this annoying persistent corruption issue, and am wondering if anyone has any suggestions. I have two tables that refuse to stay clean. After a myisamchk (below) they show corruption. I run a myisamchk -r, they get fixed, and the next day, they are once again corrupt. Finally, I did a mysqldump, dropped the tables, imported the data from the dump, and the next day - corrupt. I am at a loss, I thought the brute force method should clean it. I dont think it is the applicaiton itself, since there are an identical 24 tables (alphabet) that do not have this issue. Any help would be appreciated - Chris Checking MyISAM file: EmailMessage_c.MYI Data records: 79196 Deleted blocks: 22 myisamchk: warning: 1 clients is using or hasn't closed the table properly - check file-size - check key delete-chain - check record delete-chain - check index reference - check data record references index: 1 - check record links myisamchk: error: Record-count is not ok; is 79197Should be: 79196 myisamchk: warning: Found 457872 deleted space. Should be 459588 myisamchk: warning: Found 79507 partsShould be: 79506 = parts MyISAM-table 'EmailMessage_c.MYI' is corrupted Fix it using switch -r or -o =20 Checking MyISAM file: EmailMessage_j.MYI Data records: 39907 Deleted blocks: 91 myisamchk: warning: 1 clients is using or hasn't closed the table properly - check file-size - check key delete-chain - check record delete-chain - check index reference - check data record references index: 1 - check record links myisamchk: error: Record-count is not ok; is 39909Should be: 39907 myisamchk: warning: Found 719032 deleted space. Should be 742328 myisamchk: warning: Found 89 deleted blocks Should be: 91 myisamchk: warning: Found 40195 partsShould be: 40193 = parts MyISAM-table 'EmailMessage_j.MYI' is corrupted Fix it using switch -r or -o --=20 -- please respond to the list .. if you need to contact me direct cgmckeever is the account prupref.com is the domain A href=3Dhttp://www.prupref.com;Simply Chicago Real Estate/A -- 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: SELECT by LEFT(col,1)={letter} ?? improvements and sarge update to 4.1
Hello.
I'm unable to comment your decision about making a separate table
and how your data will be normalized after it. But, in my opinion,
if you're going to use only one letter for WHERE condition your index
will have a low cardinality (number of unique values) and optimizer
might prefer a full table scan. And indexes won't work with your
WHERE condition because you're using function from a column in
expression, not a column directly. Changing it to ' LIKE {letter}%'
probably can solve this. In your case having index only on first
character in the string makes sense for me.
M.E. Koch [EMAIL PROTECTED] wrote:
Hello dear all-mighty list :)
I do run mysql in 4.0.24 and have the following very slow query.
select tbl3.colname from tbl
.. (some left joins)
where
left(colname,1)={letter}
the tbl.col has no index nore will it ever get one because auf
the tbl structre. (this would make no sense becaus only 8tsd.)
rows in the table total 82tsd rows have to get selected like
this.
As you might guess the query is much too slow.
Now I have the chance (and time) to do the following.
Doing a seperate table for this on col like
++-+
| id | colnames |
++-+
how would i have to index it? I think colnames should get
a fulltext-field but how would it beccorect? Just one letter
or do the whole field.
colnames type = text (varchar might be ok with 255 chars).
What would you suggest?
==
the other thing is that I would like to update to 4.1.x on
my debian sarge. Has anybody done this yet?
do I have to just: apg-get remove mysql ?
and apg-get install mysql-4.1
wich packages are needed.. will there be much mor performance?
Bon Vibes and Thank you
mathias
--
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: MySQL Triggers
Hello. I'm able to insert a record with a trigger to another table at least at 5.0.10. May be these lines from ChangeLog shows new behavior: It is no longer necessary to issue an explicit LOCK TABLES for any tables accessed by a trigger prior to executing any statements that might invoke the trigger. (Bug #9581, Bug #8406) And here is the example of how it works: mysql create table tt(a int); Query OK, 0 rows affected (0.02 sec) mysql create table tb(b int); Query OK, 0 rows affected (0.01 sec) mysql delimiter $$ mysql create trigger ttr before insert on tt for each row begin insert into tb set b = NEW.a; end $$ Query OK, 0 rows affected (0.00 sec) mysql delimiter ; mysql insert into tt set a=1; Query OK, 1 row affected (0.00 sec) mysql select * from tb; +--+ | b| +--+ |1 | +--+ 1 row in set (0.00 sec) And we see the value 1 in table tb which was inserted into table tt. Fredrick Bartlett [EMAIL PROTECTED] wrote: Am I reading the documentation correctly. I cannot us a trigger to insert a record into an other table??? I use triggers on other dbms to create an acivity histoy table. Fredrick -- 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]
MATCH AGAINST with mixed MAX()
I have a table 'companies' and a table 'feedback'. Feedback has rows
with a id_company key that matched the companies.id. Think of it as
the ebay rating system.
I have a simple query that works fine:
SELECT companies.id, MIN(feedback.rating), MAX(feedback.rating),
AVERAGE(feedback.rating)
FROM companies
LEFT JOIN feedback ON companies.id = feedback.id_company
GROUP BY companies.id
I get my results... all is good.
Now I add a MATCH into the loop. I want to do a full text search on
an existing index:
SELECT
MATCH (keywords, company) AGAINST ('foo bar') as rank,
companies.id, MIN(feedback.rating), MAX(feedback.rating), AVERAGE
(feedback.rating)
FROM companies
LEFT JOIN feedback ON companies.id = feedback.id_company
GROUP BY companies.id
and again I get my data... something like:
rankidmin max ave
3.4441055
2.2342000
2.3453011
etc
Now I want to normalize the rank field i.e. rank = rank / MAX(rank):
SELECT
MATCH (keywords, company) AGAINST ('foo bar') / MAX(MATCH (keywords,
company) AGAINST ('foo bar')) as rank,
companies.id, MIN(feedback.rating), MAX(feedback.rating), AVERAGE
(feedback.rating)
FROM companies
LEFT JOIN feedback ON companies.id = feedback.id_company
GROUP BY companies.id
Except now I still get the MATCH values not the MAX = 3.444. I've
narrowed to down to my JOIN / GROUP BY. If I get rid of the join the
MAX works or if I GROUP BY feedback.rating the MAX works but I get
duplicate companies.id results (i.e. with multiple feedback rows).
Can I do this with a single query??
Thanks,
Dan T
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Failure of sql-bench test?
I cd /usr/local/mysql/sql-bench and perl run_all_tests, just to get the following error: - Got error: 'Access denied for user 'root'@'localhost' (using password: NO)' when connecting to DBI:mysql:database=test;host=localhost with user: '' password: '' - By the way, before doing the bench test, I did this operation: /usr/local/mysql/bin/mysqladmin -u root password 'new-password' /usr/local/mysql/bin/mysqladmin -u root -h gso_dev_2.workgroup password 'new-password' Is this the cause? How to let the bench test run? Thanks in advance, Regards, Xu Qiang -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql command line execution
Hi, Is there a secure way of running mysql commands against the db from the command line, or in some kind of secure batch mode, without making the password totally visible? We need to procedurize things like flush tables with read lock, unlock tables etc. Is making the password visible on the command line the only way? Thanks, Jeff -- Jeff Richards Consulting Architect Openwave Systems Asia Pacific +61 415 638757 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql command line execution
You *COULD* include the information in the my.cnf file under the [client] area, something like this: [client] user=bruce password=brucesPassword That would tell the client to use that unless something else is disabled. Of course that needs to be saved in plain text in a plain text file somewhere where people could get to it, so it may not be much of an improvement... However there are several areas that my.cnf can be stored, so there may be some opportunities here... Given that one of the places a valid my.cnf file can exist is the users home directory (where it would be called something like ~.my.cnf and is somewhat harder to see because of the leading dot) you could setup a user specifically for handling such tasks in your server's account management system. Probably avoid making such a user on a network user management system such as LDAP or NIS or anything, but you can build a local account for this user. Assign this user a home directory, and set permissions restrictions on the home directory and the .my.cnf file so other users can't access it. Then you could su to this user and create a crontab to execute your scripts... because you will be this user your mysql command line client would read your .my.cnf file and use that username and password unless told otherwise by the command line calling mysql. That said I stress again... it is still a plain text file and the password is saved in readable text... if you forget to set enough permissions to prevent other users from accessing the file or something you can run into trouble. I wouldn't consider it secure, but it's better than including the password in the scripts all over the place. You other users would need to get into this new phantom users home directory, find the file and read it... because the file is called .my.cnf it won't show on ls unless someone does an ls -a and then only if they have permissions to access that directory - given you will probably give the home directory in question drwx-- permissions only someone logged in as that user (or root) should be able to access the directory and see whats in it, and the file will need otbe readable by the user, so it needs at least - r permission, probably not much more than that. Best Regards, Bruce On Jul 28, 2005, at 7:09 PM, Jeff Richards wrote: Hi, Is there a secure way of running mysql commands against the db from the command line, or in some kind of secure batch mode, without making the password totally visible? We need to procedurize things like flush tables with read lock, unlock tables etc. Is making the password visible on the command line the only way? Thanks, Jeff -- Jeff Richards Consulting Architect Openwave Systems Asia Pacific +61 415 638757 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Failure of sql-bench test?
Xu Qiang wrote: I cd /usr/local/mysql/sql-bench and perl run_all_tests, just to get the following error: - Got error: 'Access denied for user 'root'@'localhost' (using password: NO)' when connecting to DBI:mysql:database=test;host=localhost with user: '' password: '' - Sorry I didn't read the file README in the sql-bench directory before asking the question. Now I run the test with perl run-all_tests --user=mysql, and it can run successfully now. Regards, Xu Qiang -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql command line execution
Jeff Richards wrote: Hi, Is there a secure way of running mysql commands against the db from the command line, or in some kind of secure batch mode, without making the password totally visible? We need to procedurize things like flush tables with read lock, unlock tables etc. Is making the password visible on the command line the only way? Thanks, Jeff No, you can put the mysql user and password in an option file (usually .my.cnf). Of course, you'll make this file readable only by the user running the job. You'll need at least [client] user=mysql_username password=my_password in the file. Actually, you can leave out the user=... line if the mysql user and the unix user are the same. For the details, see the description of option files in the manual http://dev.mysql.com/doc/mysql/en/option-files.html. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Total Sum of Monthly Counts for Older MySQL 4.0.22 (Please Help)
Without knowing more of your requirements, I find seeing the grand total and overall average repeated in every row strange from a usability perspective. Do you really need that, or were you just hoping to get that information out of your query? I'd suggest something like: SET @sum=0, @m=0; SELECT MONTH(employee_hire_date) AS date, @m:[EMAIL PROTECTED] as Month, count(*) AS Count, @sum:[EMAIL PROTECTED](*) AS Sum, ROUND((@sum+count(*))/(@m),1) AS Avg FROM table_employee WHERE employee_hire_date BETWEEN '2005-01-01' AND '2005-4-30' GROUP BY MONTH(employee_hire_date); Date| Month | Count | Sum | Avg | +---+---+-+---+ 2005-01 | 1 | 123 | 123 | 123.0 | 2005-02 | 2 | 50 | 173 | 86.5 | 2005-03 | 3 | 76 | 249 | 83.0 | 2005-04 | 4 | 89 | 338 | 84.5 | The Sum column is a running total, and the Avg column is the average so far. Hence, the grand total and overall average are in the last row. Would that do? Michael Gleb Paharenko wrote: Hello. It is possible to obtain your results without temporary tables, but with user variables. For a pity you should execute three queries. With first query you're getting the Sum: select @all_sum:= count( employee_id) from table_employee where employee_hire_date between 2005-01-01 and 2005-4-30; With the second - number of month (this in most cases could be figured out without query - with direct computation in next query, or on client side): select @all_num:= count( distinct month(employee_hire_date)) from table_employee where employee_hire_date between 2005-01-01 and 2005-4-30; And with the last query you should get what you want: select employee_hire_date, count(employee_id), @all_sum as Sum, @all_sum/@all_num as Avg from table_employee where employee_hire_date between 2005-01-01 and 2005-4-30 group by month(employee_hire_date); Henry Chang [EMAIL PROTECTED] wrote: Hello MySQL users, Currently, I use MySQL 4.0.22 and I can do a straightforward count of employees hired for each month. select employee_hire_date, count(employee_id) from table_employee where employee_hire_date between 2005-01-01 and 2005-4-30 group by month(employee_hire_date) Date | Count | 2005-01-01 | 123 | 2005-02-01 | 50| 2005-03-01 | 76| 2005-04-01 | 89| However, I would like to do a grand total of the counts and the averages that would like the below. Date | Count | Sum | Avg | - 2005-01-01 | 123 | 338 | 84.5 | 2005-02-01 | 50| 338 | 84.5 | 2005-03-01 | 76| 338 | 84.5 | 2005-04-01 | 89| 338 | 84.5 | Since my MySQL version is 4.0.22, I am not able to use subquery and I prefer not to use tmp tables. What would be the right query to solve for the grand total sum and average?? Any help would be greatly appreciated!!! Thanks in Advance. Henry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
