Newbie question about index (why are they not updating?)
I've created a table with several indexes as follows: $query = CREATE TABLE `data_raw` ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, run_id VARCHAR(20) DEFAULT 'error_internal' NOT NULL, time_run DATETIME DEFAULT '-00-00 00:00:00' NOT NULL, ...clip. PRIMARY KEY (id), INDEX x_run_id (run_id), INDEX x_comp_code (comp_code), INDEX x_time_run (time_run), INDEX x_url (url) ) COMMENT = 'Raw data samples' ; $query executed with php mysql_query. The table is created OK. I add many records to the table with $query = INSERT INTO data_raw( id, run_id, time_run, time_sample, comp_code, url, url_index, err_number, err_desc, err_src ) VALUES( 0, '$run_id', '$time_run', '$ts', '$data[1]', '$data[2]', $data[3], $data[4], '$data[5]', '$data[6]' ); Data is added OK. But, the indexes are not updated! Running myPHPAdmin shows: Indexes: Documentation Keyname Type Cardinality Action Field PRIMARY PRIMARY 12932 Edit Drop id x_run_id INDEX None Edit Drop run_id x_comp_code INDEX None Edit Drop comp_code x_time_run INDEX None Edit Drop time_run x_url INDEX None Edit Drop url Access is slow. If I do an ALTER TABLE ... ADD INDEX ... indexes are updated and subsequent INSERTS update the indexes. I'm sure I'm doing something simple wrong but need a clue as to what? Any help much appreciated.
InnoDB status: why do I see MyISAM requests?
Just curious... While running SHOW INNODB STATUS, the list of transactions for each sessions part shows queries that are only related to MyISAM tables. Is it the expected behavior? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysqlcc
Hi Robin Have you checked you have INSERT authorities to the database as the user you connected as? I have mysqlcc 0.9.2-beta (on Win2k) connecting to a mySQL 4.0.20 (Linux) database and it's been working fine for months. Graham -Original Message- From: Robin Lynn Frank [mailto:[EMAIL PROTECTED] Sent: 11 December 2004 01:23 To: MySQL Mailing List Subject: mysqlcc I'm obviously missing something. I can use mysqlcc to change data in a row of a table and save it. But, if I try to insert a row, add data and save it, it never gets saved. What am I overlooking? -- /\ ASCII RIBBON Robin Lynn Frank \ / CAMPAIGN Director of Operations X AGAINST Paradigm-Omega, LLC / \ HTML MAILhttp://www.paradigm-omega.com/ = Spambots visit http://paradigm-omega.net/cgi-bin/custmail.cgi = Don't mind me. I am having a bad (day|week|month|year). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Are these variables healthy?
Hi, I am running an instance of MySQL 4.0.16, mostly using MyISAM tables on a 18G 10kRPM SCSI drive w/ ext3, the server having 2G RAM and a 2.8G HT P4, on a RedHat 9 install. Would some of you experts be so kind to take a look at the variables I have, and tell me if anything is wrong? I think the server is using a little too much I/O, but I am not sure... maybe I could tune something? my.cnf (only the relevant parts): skip-locking wait_timeout = 1800 interactive_timeout = 1800 max_connections=300 key_buffer=32M max_allowed_packet=1M table_cache=1024 sort_buffer=2M record_buffer=1M myisam_sort_buffer_size=4M thread_cache=4 log=/var/log/mysqld-query.log log-slow-queries=/var/log/mysqld-slow.log tmpdir=/tmp/ query_cache_limit=128k query_cache_size=16M query_cache_type=1 STATUS: Threads: 51 Questions: 967003 Slow queries: 2 Opens: 135 Flush tables: 1 Open tables: 125 Queries per second avg: 81.016 SHOW STATUS: +--+---+ | Variable_name| Value | +--+---+ | Aborted_clients | 1406 | | Aborted_connects | 1 | | Bytes_received | 7285 | | Bytes_sent | 319972773 | | Com_admin_commands | 668953| | Com_alter_table | 1 | | Com_analyze | 0 | | Com_backup_table | 0 | | Com_begin| 0 | | Com_change_db| 4675 | | Com_change_master| 0 | | Com_check| 0 | | Com_commit | 0 | | Com_create_db| 0 | | Com_create_function | 0 | | Com_create_index | 0 | | Com_create_table | 0 | | Com_delete | 211 | | Com_delete_multi | 0 | | Com_drop_db | 0 | | Com_drop_function| 0 | | Com_drop_index | 0 | | Com_drop_table | 0 | | Com_flush| 0 | | Com_grant| 0 | | Com_ha_close | 0 | | Com_ha_open | 0 | | Com_ha_read | 0 | | Com_insert | 17890 | | Com_insert_select| 0 | | Com_kill | 0 | | Com_load | 0 | | Com_load_master_data | 0 | | Com_load_master_table| 0 | | Com_lock_tables | 1 | | Com_optimize | 0 | | Com_purge| 0 | | Com_rename_table | 0 | | Com_repair | 0 | | Com_replace | 64| | Com_replace_select | 0 | | Com_reset| 0 | | Com_restore_table| 0 | | Com_revoke | 0 | | Com_rollback | 0 | | Com_savepoint| 0 | | Com_select | 162423| | Com_set_option | 0 | | Com_show_binlog_events | 0 | | Com_show_binlogs | 0 | | Com_show_create | 0 | | Com_show_databases | 28| | Com_show_fields | 3926 | | Com_show_grants | 0 | | Com_show_keys| 20| | Com_show_logs| 0 | | Com_show_master_status | 0 | | Com_show_new_master | 0 | | Com_show_open_tables | 0 | | Com_show_processlist | 0 | | Com_show_slave_hosts | 0 | | Com_show_slave_status| 0 | | Com_show_status | 4 | | Com_show_innodb_status | 0 | | Com_show_tables | 140 | | Com_show_variables | 0 | | Com_slave_start | 0 | | Com_slave_stop | 0 | | Com_truncate | 0 | | Com_unlock_tables| 0 | | Com_update | 31543 | | Connections | 14178 | | Created_tmp_disk_tables | 0 | | Created_tmp_tables | 8708 | | Created_tmp_files| 0 | | Delayed_insert_threads | 0 | | Delayed_writes | 0 | | Delayed_errors | 0 | | Flush_commands | 1 | | Handler_commit | 0 | | Handler_delete | 178 | | Handler_read_first | 37| | Handler_read_key | 25041603 | | Handler_read_next| 83330028 | | Handler_read_prev| 28| | Handler_read_rnd | 533774| | Handler_read_rnd_next| 121555664 | | Handler_rollback | 131 | | Handler_update | 5826935 | | Handler_write| 352980| |
mysqldump + debug option not working
Hi, I am running MySQL (4.0.15 max log) mysqldump (version 9.09 Distrib 4.0.16) on Linux platform. For some strange reasons, I am unable to use the debug option with mysqldump. I have tried the following switch with no luck: -# , --debug, --debug = 'd:t:o,debug.log' , -# ='d:t:o:debug.log' My net aim is to write the debug log to an output file (debug.log in above example). Whichever option I use above, I am unable to generate the debug log. Am I missing something very obvious?? TIA Manoj -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump + debug option not working
At 11:19 +0900 12/13/04, ManojSW wrote: Hi, I am running MySQL (4.0.15 max log) mysqldump (version 9.09 Distrib 4.0.16) on Linux platform. For some strange reasons, I am unable to use the debug option with mysqldump. I have tried the following switch with no luck: -# , --debug, --debug = 'd:t:o,debug.log' , -# ='d:t:o:debug.log' My net aim is to write the debug log to an output file (debug.log in above example). Whichever option I use above, I am unable to generate the debug log. Am I missing something very obvious?? Was MySQL compiled with debugging support? -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump + debug option not working
I am not sure. I installed it from an RPM version long time back (not sure if that RPM was compilied with debugging support). Is there anyway to check If I am running a debugging supported version?? Apologies if this is a FAQ. Manoj - Original Message - From: Paul DuBois [EMAIL PROTECTED] To: ManojSW [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Monday, December 13, 2004 11:29 AM Subject: Re: mysqldump + debug option not working At 11:19 +0900 12/13/04, ManojSW wrote: Hi, I am running MySQL (4.0.15 max log) mysqldump (version 9.09 Distrib 4.0.16) on Linux platform. For some strange reasons, I am unable to use the debug option with mysqldump. I have tried the following switch with no luck: -# , --debug, --debug = 'd:t:o,debug.log' , -# ='d:t:o:debug.log' My net aim is to write the debug log to an output file (debug.log in above example). Whichever option I use above, I am unable to generate the debug log. Am I missing something very obvious?? Was MySQL compiled with debugging support? -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump + debug option not working
Got it. I did a mysql --help and saw that debug-info is set to FALSE...so I need to compile MySQL with debugging support. Thanks for all the help!! Cheers Manoj - Original Message - From: Paul DuBois [EMAIL PROTECTED] To: ManojSW [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Monday, December 13, 2004 11:29 AM Subject: Re: mysqldump + debug option not working At 11:19 +0900 12/13/04, ManojSW wrote: Hi, I am running MySQL (4.0.15 max log) mysqldump (version 9.09 Distrib 4.0.16) on Linux platform. For some strange reasons, I am unable to use the debug option with mysqldump. I have tried the following switch with no luck: -# , --debug, --debug = 'd:t:o,debug.log' , -# ='d:t:o:debug.log' My net aim is to write the debug log to an output file (debug.log in above example). Whichever option I use above, I am unable to generate the debug log. Am I missing something very obvious?? Was MySQL compiled with debugging support? -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
how escape special in a field
Hi, I find there seems problem with this select statement when there is a special character inside the table. i.e. select concat(field_1, ',', field_2) as name where ... field_2 actually is something like 'George, Banson' ( with a comma in between ) i guess this , destroy the SQL syntax. Is there any function for protecting this situation? Thanks for your help. Regards, CHAN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
update a row in a table from another row within the same table
Hi listers, using mysql 4.1.7 on SuSE Linux 9.1. I have a table containing config parameters for a web based application. this table has one particular row with the main key of example. whenever a new customer joins this application, the registration application copies this example row and creates a new row in the same table with the main key identifying the new customer. this is done using php: i select the example row and insert it into the same table changing the main key. last week a new table entry gots corrupt, and i would have urgently needed a possibility to copy some columns (not all) from the example row into the new customer's row within the same table. I thought, that this is easily done using UPDATE ... SELECT ..., but after failing and searching quite a bit I found the last line in the UPDATE-syntax documentation saying: Currently, you cannot update a table and select from the same table in a subquery. Also, there is no example of UPDATE ... SELECT in the docu. Therefore, I very much assume, that the UPDATE ... SELECT does not work at all in 4.1.7. I mean, I could have achieved it doing some php programming. But this is not what SQL is considered for. does anybody of you guys know a (sequence of) SQL statement(s), which would update some columns in a row with the values retrieved from another row withing the same table? or is this just impossible with 4.1.7? or am I just too stupid? thanks very much for your attention. schlubediwup -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
update a row in a table from another row within the same table
Hi listers, using mysql 4.1.7 on SuSE Linux 9.1. I have a table containing config parameters for a web based application. this table has one particular row with the main key of example. whenever a new customer joins this application, the registration application copies this example row and creates a new row in the same table with the main key identifying the new customer. this is done using php: i select the example row and insert it into the same table changing the main key. last week a new table entry gots corrupt, and i would have urgently needed a possibility to copy some columns (not all) from the example row into the new customer's row within the same table. I thought, that this is easily done using UPDATE ... SELECT ..., but after failing and searching quite a bit I found the last line in the UPDATE-syntax documentation saying: Currently, you cannot update a table and select from the same table in a subquery. Also, there is no example of UPDATE ... SELECT in the docu. Therefore, I very much assume, that the UPDATE ... SELECT does not work at all in 4.1.7. I mean, I could have achieved it doing some php programming. But this is not what SQL is considered for. does anybody of you guys know a (sequence of) SQL statement(s), which would update some columns in a row with the values retrieved from another row withing the same table? or is this just impossible with 4.1.7? or am I just too stupid? thanks very much for your attention. schlubediwup -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
