ip range lookup
I have a table that maps ip-ranges to countries: each record consists of 2 ip numbers (unsigned int's) and the country in which all ip's between those two are located. How should I setup the table to have fast ip lookups ? Making a primary key of the 2 ip's and doing a 'select .. between ip1 and ip2' doesn't use the index: mysql explain select * from ipcountry where 123456789 between ip1 and ip2; +---+--+---+--+-+--+---+ -+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+--+---+--+-+--+---+ -+ | ipcountry | ALL | NULL | NULL |NULL | NULL | 58229 | Using where | +---+--+---+--+-+--+---+ -+ Should I add a helper column - f.e. a column that contains the 'leftmost' byte of the ip - and index that ? Thanks, Willem Bison -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Commands out of sync using PHP
I'm getting lots of random Commands out of sync; You can't run this command now errors when calling mysql_query(). I'm pretty sure this has started after upgrading to PHP 4.2.3. The only way to stop it is restarting apache. I can't see any pattern in the errors: it happens on different tables, long/slow queries etc. Once the error starts occuring, it happens with about 1/3 of all queries. mysql client api: 3.23.39 mysql server: 3.23.44 apache 1.3.26 php: 4.2.3 red-hat 7.3 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
varchar to char in table with several varchar's
I have a table with several columns of type 'varchar'. How can I change all columns to fixed width char's ? Doing a 'alter' from varchar to char has no effect since the column is changed back to varchar. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Converting from myisam to InnoDB
I'm trying to switch from myisam to InnoDB. I've edited my.cnf (see below). When I try to start mysql-max: [root@db1 mysql]# safe_mysqld --skip-bdb Starting mysqld-max daemon with databases from /home/dbclnbs/mysql/ 011203 15:00:00 mysqld ended Error log: 011203 14:59:54 mysqld started InnoDB: Warning: operating system error number 13 in a file operation. InnoDB: Cannot continue operation. 011203 15:00:00 mysqld ended [root@db1 mysql]# ls -l /home/dbclnbs/mysql/ total 428 drwxrwxr-x2 mysqlmysql4096 Dec 2 15:37 innodb Here's part of my.cnf: innodb_data_file_path = cb1:3000M innodb_data_home_dir = /home/dbclnbs/mysql/innodb innodb_log_group_home_dir = /home/dbclnbs/mysql/innodb innodb_log_arch_dir = /home/dbclnbs/mysql/innodb set-variable = innodb_mirrored_log_groups=1 set-variable = innodb_log_files_in_group=3 set-variable = innodb_log_file_size=5M set-variable = innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit=1 innodb_log_archive=0 set-variable = innodb_buffer_pool_size=512M set-variable = innodb_additional_mem_pool_size=2M set-variable = innodb_file_io_threads=4 Why the permission error ? (RH 7.2, mysql 3.23.46) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Update locking table forever
My site regularly gets extremely slow. When I check the running queries with 'show processlist;' I get this: update XYZ set ... where id=... select ... from XYZ where id=... select ... from XYZ where id=... select ... from XYZ where id=... [...100's of similar selects...] in other words: a single update is locking a table until all threads are used up. How is this possible ? The server is working on one single simple query (the update), the other queries are waiting. What is the server doing ? The problem occurs with different tables XYZ. The tables aren't very large and the indices are simple. I would very much like to hear how I can solve this. My site is getting unacceptably slow and I dont really know what to do. I thought of experimenting with LOW/HIGH priority but decided not to do it because it doesn't make sense to me in this particular case. mysql: 3.23.39 (rpm) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Blocked because of connection errors
When we increase the number of connections our 3.22.25 server 'hangs': mysql show status; No connection. Trying to reconnect... ERROR 1129: Host 'xxx is blocked because of many connection er rors. Unblock with 'mysqladmin flush-hosts' ERROR: Can't connect to the server This happens when we increase MaxClients on the Apache webserver from 200 to 220 resulting in more queries to the db-server. After a few hours the db-server hangs with the above error. DB and apache server run on different machines. Apache talks with mysql through PHP 4.0.41/ZendOptimizer/Cache. The max connections on the db-server is set to 400. Linux. More info (after restarting mysql): mysql show status; +--+--+ | Variable_name| Value| +--+--+ | Aborted_clients | 0| | Aborted_connects | 671 | | Created_tmp_tables | 31067| | Delayed_insert_threads | 0| | Delayed_writes | 0| | Delayed_errors | 0| | Flush_commands | 1| | Handler_delete | 221 | | Handler_read_first | 23 | | Handler_read_key | 1413111 | | Handler_read_next| 26516553 | | Handler_read_rnd | 4318261 | | Handler_update | 85123| | Handler_write| 182257 | | Key_blocks_used | 15641| | Key_read_requests| 6039569 | | Key_reads| 50309| | Key_write_requests | 3213 | | Key_writes | 3005 | | Max_used_connections | 222 | | Not_flushed_key_blocks | 0| | Not_flushed_delayed_rows | 0| | Open_tables | 63 | | Open_files | 6| | Open_streams | 0| | Opened_tables| 6547 | | Questions| 361413 | | Running_threads | 220 | | Slow_queries | 6| | Uptime | 2048 | +--+--+ 30 rows in set (0.00 sec) Anyone have a clue whats going on ? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
