MySQL Insert error..
Hello,
I am doing programming in C and uses MySQL database. I have got a problem with
inserting values stored in variables to the database. When compiling the code
that I have written the following error message shows up:
connect2.c: In function `main':
connect2.c:19: invalid operands to binary +
The code that I have written is given below. Please give a solution.
Thanks in advance,
Preeth.
#includestdlib.h
#includestdio.h
#includemysql.h
#includestring.h
int main(int argc, char *argv[])
{
MYSQL my_connection;
int res;
char srcip[13]=172.16.4.200;
char destip[13]=172.16.4.220;
int srcport=20;
int destport=30;
mysql_init(my_connection);
if(mysql_real_connect(my_connection,LocalHost,root,master,project,0,NULL,0))
{
printf(Connection Success\n);
/***Insert
Query***/
res=mysql_query(my_connection,INSERT INTO
ids(sip,sport,dip,dport)VALUES('+srcip+',+srcport+,'+destip+',+destport+));
if (!res)
{
printf(Inserted %lu rows\n,(unsigned
long)mysql_affected_rows(my_connection));
}
else
{
fprintf(stderr,Insert error %d:
%s\n,mysql_errno(my_connection),mysql_error(my_connection));
}
mysql_close(my_connection);
}
else
{
fprintf(stderr,Connection Failed\n);
if (mysql_errno(my_connection))
{ fprintf(stderr,Connection error %d:
%s\n,mysql_errno(my_connection),mysql_error(my_connection));
}
}
return EXIT_SUCCESS;
}
/***/
Visual C++ 6.0 and MySQL
Hi, I have to create with Microsoft Visual C++ a program connected to a database. I am thinking about MySQL, but I have never did it. I have tried to located information about API for C++ on www.mysql.com, but I have not success. Can anybody tell me where I can find a good tutorial and examples? Thank you, Vicente Valero
Visual C++ 6.0 and MySQL
Hi, I have to create with Microsoft Visual C++ a program connected to a database. I am thinking about MySQL, but I have never did it. I have tried to located information about API for C++ on www.mysql.com http://www.mysql.com/ , but I have not success. Can anybody tell me where I can find a good tutorial and examples? Thank you, Vicente Valero
Re: Visual C++ 6.0 and MySQL
Vicente Valero wrote: Hi, I have to create with Microsoft Visual C++ a program connected to a database. I am thinking about MySQL, but I have never did it. I have tried to located information about API for C++ on www.mysql.com, but I have not success. Can anybody tell me where I can find a good tutorial and examples? Thank you, Vicente Valero the C api is here : http://dev.mysql.com/doc/mysql/en/c.html the c++ here : http://tangentsoft.net/mysql++/ -- 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: Visual C++ 6.0 and MySQL
Please note though - If you are using VC 6 (as the subjec tline says) then please read the FAQ of msql++ since it says that the library works with 7.1 onwards. - Original Message - From: Philippe Poelvoorde [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, April 18, 2005 6:04 PM Subject: Re: Visual C++ 6.0 and MySQL Vicente Valero wrote: Hi, I have to create with Microsoft Visual C++ a program connected to a database. I am thinking about MySQL, but I have never did it. I have tried to located information about API for C++ on www.mysql.com, but I have not success. Can anybody tell me where I can find a good tutorial and examples? Thank you, Vicente Valero the C api is here : http://dev.mysql.com/doc/mysql/en/c.html the c++ here : http://tangentsoft.net/mysql++/ -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
use mysql and segmentation fault
Hi everyone! I downloaded and installed the mysql 4.1 rpms on my RedHat Enterprise Linux 3 box and found a strange problem. If I invoke the client program by mysql -u root -p'mypassword' and then type use mysql, then segmentation fault happens, but if I invoke the client program by mysql -u root -p'mypassword' -A, then everything is ok. See below for details. [EMAIL PROTECTED] mysql]# mysql -u root -p** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 to server version: 4.1.11-standard Reading history-file /root/.mysql_history Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql use mysql Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A segmentation fault [EMAIL PROTECTED] mysql]# mysql -u root -pphoenix -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 9 to server version: 4.1.11-standard Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql use mysql Database changed mysql Any help? Thanks! Hao Xu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Query-Cache Reset
Hi, Im the administrador of a MySQL Server, it have lots of queries, and now its getting high load of processor, I try to increase the size of MySQL Query Cache, but if I put more than 128 Mb the cache its reseting all the time and the performance is worst. The server have 1,2 Mb of RAM and I can assign 256 or more MB to Query Cache for reduce processor load. My actual my.cnf config is: [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock old_passwords=1 max_connections = 400 key_buffer = 64M myisam_sort_buffer_size = 32M join_buffer_size = 1M read_buffer_size = 4M sort_buffer_size = 4M table_cache = 1024 thread_cache_size = 128 wait_timeout = 1800 connect_timeout = 10 max_allowed_packet = 16M max_connect_errors = 10 query_cache_limit = 1M query_cache_size = 128M query_cache_type = 1 And my show status info: mysql show status; +--++ | Variable_name| Value | +--++ | Aborted_clients | 8378 | | Aborted_connects | 307| | Binlog_cache_disk_use| 0 | | Binlog_cache_use | 0 | | Bytes_received | 114459945 | | Bytes_sent | 1355402553 | | Com_admin_commands | 49554 | | Com_alter_db | 0 | | Com_alter_table | 0 | | Com_analyze | 0 | | Com_backup_table | 0 | | Com_begin| 0 | | Com_change_db| 178087 | | Com_change_master| 0 | | Com_check| 0 | | Com_checksum | 0 | | Com_commit | 0 | | Com_create_db| 0 | | Com_create_function | 0 | | Com_create_index | 0 | | Com_create_table | 0 | | Com_dealloc_sql | 0 | | Com_delete | 49499 | | Com_delete_multi | 0 | | Com_do | 0 | | Com_drop_db | 0 | | Com_drop_function| 0 | | Com_drop_index | 0 | | Com_drop_table | 0 | | Com_drop_user| 0 | | Com_execute_sql | 0 | | Com_flush| 0 | | Com_grant| 0 | | Com_ha_close | 0 | | Com_ha_open | 0 | | Com_ha_read | 0 | | Com_help | 0 | | Com_insert | 49288 | | Com_insert_select| 0 | | Com_kill | 0 | | Com_load | 3 | | Com_load_master_data | 0 | | Com_load_master_table| 0 | | Com_lock_tables | 96 | | Com_optimize | 0 | | Com_preload_keys | 0 | | Com_prepare_sql | 0 | | Com_purge| 0 | | Com_purge_before_date| 0 | | Com_rename_table | 0 | | Com_repair | 0 | | Com_replace | 61263 | | Com_replace_select | 0 | | Com_reset| 0 | | Com_restore_table| 0 | | Com_revoke | 0 | | Com_revoke_all | 0 | | Com_rollback | 0 | | Com_savepoint| 0 | | Com_select | 250306 | | Com_set_option | 2560 | | Com_show_binlog_events | 0 | | Com_show_binlogs | 0 | | Com_show_charsets| 0 | | Com_show_collations | 0 | | Com_show_column_types| 0 | | Com_show_create_db | 102| | Com_show_create_table| 2550 | | Com_show_databases | 10 | | Com_show_errors | 0 | | Com_show_fields | 3006 | | Com_show_grants | 0 | | Com_show_innodb_status | 0 | | Com_show_keys| 0 | | Com_show_logs| 0 | | Com_show_master_status | 0 | | Com_show_new_master | 0 | | Com_show_open_tables | 0 | | Com_show_privileges | 0 | | Com_show_processlist | 0 | | Com_show_slave_hosts | 0 | | Com_show_slave_status| 0 | | Com_show_status | 10 | | Com_show_storage_engines | 0 | | Com_show_tables | 2664 | | Com_show_variables | 0 | | Com_show_warnings| 0 | | Com_slave_start | 0 | | Com_slave_stop | 0 | | Com_truncate | 0 | | Com_unlock_tables| 102| | Com_update | 71170 | | Com_update_multi | 0 | | Connections | 480457 | | Created_tmp_disk_tables | 49082 | | Created_tmp_files
MySQL 5.0.4-beta has been released
Hi, A new version of MySQL Community Edition 5.0.4-beta Open Source database management system has been released. This version now includes support for Stored Procedures, Triggers, Views and many other features. It is now available in source and binary form for a number of platforms from our download pages at http://dev.mysql.com/downloads/ and mirror sites. 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 second Beta release in the 5.0 series. All attention will now 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 open and resolved bugs in this version. Changes in release 5.0.4-beta: Functionality added or changed: * SHOW CREATE TABLE for an INFORMATION_SCHEMA table no longer prints a MAX_ROWS value because the value has no meaning. (Bug #8941) * Invalid DEFAULT values for CREATE TABLE now generate errors. (Bug #5902) * Added --show-table-type option to mysqlshow, to display a column indicating the table type, as in SHOW FULL TABLES. (Bug #5036) * The way the time zone information is stored into the binary log was changed, so that it's now possible to have a replication master and slave running with different global time zones. A drawback is that replication from 5.0.4 masters to pre-5.0.4 slaves is impossible. * Added --with-big-tables compilation option to configure. (Previously it was necessary to pass -DBIG_TABLES to the compiler manually in order to enable large table support.) See section 2.8.2 Typical configure Options for details. * New configuration directives !include and !includedir implemented for including option files and searching directories for option files. See section 4.3.2 Using Option Files for usage. Bugs fixed: * Fixed a Commands out of sync error when two prepared statements for single-row result sets were open simultaneously. (Bug #8880) * Fixed a server crash after a call to mysql_stmt_close() for single-row result set. (Bug #9159) * Fixed server crashes for CREATE TABLE ... SELECT or INSERT INTO ... SELECT when selecting from multiple-table view. (Bug #8703, Bug #9398) * TRADITIONAL SQL mode should prevent inserts where a column with no default value is omitted or set to a value of DEFAULT. Fixed cases where this restriction was not enforced. (Bug #5986) * Fixed a server crash when creating a PRIMARY KEY for a table, if the table contained a BIT column. (Bug #9571) * Warning message from GROUP_CONCAT() did not always indicate correct number of lines. (Bug #8681) * The commit count cache for NDB was not properly invalidated when deleting a record using a cursor. (Bug #8585) * Fixed option-parsing code for the embedded server to understand K, M, and G suffixes for the net_buffer_length and max_allowed_packet options. (Bug #9472) * Selecting a BIT column failed if the binary client/server protocol was used. (Bug #9608) * Fixed a permissions problem whereby information in INFORMATION_SCHEMA could be exposed to a user with insufficient privileges. (Bug #7214) * Now one gets an error if one tries to insert an invalid value via a stored procedure in STRICT mode. (Bug #5907) * Link with libsupc++ on Fedora Core 3 to get language support functions. (Bug #6554) * The value of the CHARACTER_MAXIMUM_LENGTH and CHARACTER_OCTET_LENGTH columns of the INFORMATION_SCHEMA.COLUMNS table must be NULL for numeric columns, but were not. (Bug #9344) * DROP TABLE did not drop triggers that were defined for the table. DROP DATABASE did not drop triggers in the database. (Bug #5859, Bug #6559) * CREATE OR REPLACE VIEW and ALTER VIEW now require the CREATE VIEW and DROP privileges, not CREATE VIEW and DELETE. (DELETE is a row-level privilege, not a table-level privilege.) (Bug #9260) * Some user variables were not being handled with ``implicit'' coercibility. (Bug #9425) * Setting the max_error_count system variable to 0 resulted in a setting of 1. (Bug #9072) * Fixed a collation coercibility problem that caused a union between binary and non-binary columns to fail. (Bug #6519)
Two servers, one InnoDB area?
I'm currently evaluating ways to increase our online storage capacity for our DB engine. The current winner is a massive external SAN/disk array. However, I'm wondering how MySQL will handle failover when it comes to InnoDB tablespaces. The current plan, based on the disk array, is to have each of the DB engine servers attached to the same disk array. Server 1 is 'hot' and has the disk array mounted, and thus creates all of the InnoDB table spaces etc. Should it fail for whatever reason, Server 2 takes over as hot (based on linux-ha), mounts the array, fires up MySQL and keeps on going. I am aware of MySQL cluster, but currently do not have the hardware budget to accomodate the requirements of Cluster. Will the InnoDB engine on Server 2 have any issues with Server 1 creating the table space? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB Performance
The MyIsam storage engine is a non transactional engine and InnoDb is a transactional engine. That is the main difference. So I think the MyIsam engine should be faster. what is transactional mean? - Do you Yahoo!? Yahoo! Small Business - Try our new resources site!
Re: auto-increment by a specific number
StinkyPup wrote: How do I auto-increment by a specific number. For example by 100: You dont. IDData 100 blah blah blah 200 blah blah foo ALTER TABLE PRODUCT AUTO_INCREMENT = 100 doesn't do what I want to do. TIA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Include repeats in query
Martin Gallagher [EMAIL PROTECTED] wrote on 04/16/2005 08:49:35 AM: Hi, I have the following query: SELECT person FROM people WHERE ORDER BY RAND() * (1 / score) LIMIT 1 This returns a person randomly, but the chance of the person being selected is increased with a higher `score`. What I want to do is make a single query that will return 10 results in this fashion. Currently I'm doing: for (i = 1; i = 10; i++) do: SELECT person FROM people WHERE ORDER BY RAND() * (1 / score) LIMIT 1 Which returns a results like: Mike Sam Sam Mike Mike Mike John Sam Mike John This is exactly the result I desire, but programmatically it's not the most efficient way. I'm guessing using 1 query and using the result set is MUCH faster, 1 query... 1 result, instead of 10. I have tried: (SELECT person FROM people WHERE ORDER BY RAND() * (1 / score) LIMIT 1) UNION (X) UNION (X) ... [repeat ten times] This however returns this kind of result: Mike Sam John The UNION query seems to remove the repeats, and because this is a mathematical system this will throw it out. This of course is not what I want :-(. Is there an option that allows repeats? Cheers, - Martin You are correct in saying that a UNION query eliminates repeats. However a UNION ALL does not. Just add the ALL keyword where appropriate and you should get the results you wanted. http://dev.mysql.com/doc/mysql/en/union.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine PS - I think this weighted random choice routine would perform better if you randomly picked a name from a 3 member array in your source code. You can still use the score to adjust the probability of picking and you won't have to do 10 queries to get a 10 name list. You only do a single query and re-use the results. Just my $.02 - SG
Re: InnoDB Performance
see this link http://dev.mysql.com/doc/mysql/en/ansi-diff-transactions.html Reto Eko Budiharto wrote: The MyIsam storage engine is a non transactional engine and InnoDb is a transactional engine. That is the main difference. So I think the MyIsam engine should be faster. what is transactional mean? - Do you Yahoo!? Yahoo! Small Business - Try our new resources site! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Regarding the loading of data usning load data infile
Hi, I had 99,990 records to be loaded into a table which is having unique constraints and foreign-key constraints as below CREATE TABLE `teldir` ( `NAME` varchar(21) default '', `PHONE_NO` varchar(26) default '', `PRIME_VALUE` char(1) default '', `COMMENT_TEXT1` varchar(30) default '', `COMMENT_TEXT2` varchar(30) default '', `DEPARTMENT_ID` int(4) default NULL, `LOCATION_ID` int(4) default NULL, `COMPONENT_ID` int(3) default NULL, `NAME_AND_PHONE_NO` varchar(48) NOT NULL default '', `HI_CASE_IND` int(11) default NULL, `LOW_CASE_IND` int(11) default NULL, `PRIVACY` char(1) default '', `COLLECTED` char(1) default '', `HOMENODE_ID` int(3) NOT NULL default '0', `CLUSTERID` int(3) NOT NULL default '0', `PLID_CABINET` int(3) default NULL, `PLID_SHELF` int(3) default NULL, `PLID_SLOT` int(3) default NULL, `PLID_CIRCUIT` int(3) default NULL, `DEVICE_TYPE` int(2) default NULL, `IDS_ID` varchar(255) default '', `ISIDSMANAGED` char(1) default '', `MACADDRESS` varchar(12) default '', `CESID` varchar(10) default '', `hvgPIN` varchar(8) default '', `tdUID` varchar(38) default '', PRIMARY KEY (`NAME_AND_PHONE_NO`), UNIQUE KEY `TD_PHONE_KEY_IDX` (`PHONE_NO`,`NAME_AND_PHONE_NO`), UNIQUE KEY `TD_KEYS_IDX` (`NAME_AND_PHONE_NO`,`NAME`,`PHONE_NO`), UNIQUE KEY `TD_COMP_KEY_IDX` (`COMPONENT_ID`,`NAME_AND_PHONE_NO`), KEY `COMPONENT_ID` (`COMPONENT_ID`), KEY `HOMENODE_ID` (`HOMENODE_ID`), KEY `TD_COMP_PLID_IDX` (`COMPONENT_ID`,`PLID_CABINET`,`PLID_SHELF`,`PLID_SLOT`,`PLID_CIRCUIT`), KEY `TD_IDSID_IDX` (`IDS_ID`), KEY `TD_IDSUNMGT_IDX` (`ISIDSMANAGED`), CONSTRAINT `FK_TELDIR_COMPONENT` FOREIGN KEY (`COMPONENT_ID`) REFERENCES `component` (`ID`), CONSTRAINT `FK_TELDIR_HOMENODE` FOREIGN KEY (`HOMENODE_ID`) REFERENCES `component` (`ID`) ) TYPE=InnoDB Used load data infile 'teldir.lst' into table teldir. But teldir.lst contains data of 99,990 records (whose fields are separated by tab and rows are separated by newline). To load these many records into teldir table whose structure as above taking around 100 minutes. I.e taking too much of time. If I drop the unique and foreign key constraints it is taking around 25 minutes, which is also large time. Please advise me for a better solution so that the loading of data should be faster. According to the load data standards for innodb it should load 2000 records for second. Please explain me the proper solution for this. Thanks, Narasimha -Original Message- From: Heikki Tuuri [mailto:[EMAIL PROTECTED] Sent: Thursday, April 07, 2005 2:52 PM To: mysql@lists.mysql.com Subject: Re: innodb, log_bin and ib_logfiles Johanne, - Original Message - From: Duhaime Johanne [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Wednesday, April 06, 2005 10:12 PM Subject: innodb, log_bin and ib_logfiles --_=_NextPart_001_01C53ADC.86591B2F Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: quoted-printable I have looked around in the documentation but I do not have a clear idea of log_bin vs ib_lofile for innodb. =20 Regarding only INNODB tables, am I right if I say that:=20 =20 log_bin are the commit transactions yes. The binlog is used to roll-forward from a backup. and it is what is use in an automatic recovery or are to be apply in a manual recovery from a dump (In Oracle they are the Redo.logs) .=20 ib_logfiles are storing the uncommit and commit transactiond and allows the rollback of transactions No. ib_logfiles are a page-level 'physiological' redo log. Used in crash recovery. (In Oracle they are the Rollback segments)? Rollback segments in InnoDB are called 'undo logs'. They are in the ibdata files. They are used in rollback, and in 'consistent read' SELECT. They are not use in the automatic recovery of innodb nor in a manual recovery from a dump. =20 I would appreciate very much if someone can clarify this for me. =20 =20 Johanne Duhaime Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Confidentiality Notice The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain confidential or privileged information. If you are not the intended recipient, please notify the sender at Wipro or [EMAIL PROTECTED] immediately and destroy all copies of this message and any attachments. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL not starting at boot-Fedora Core 3
Hello. Are you able to start MySQL server by this command? service mysqld start I think you should put the correct values for the basedir, datadir, PATH variables at the beginning of the /etc/init.d/mysql file. Mark Sargent [EMAIL PROTECTED] wrote: Hi All, attempting another mysql install, on a 3rd machine. Extracted the tar.gz file to /usr/local/mysql. Followed the install docs here, http://dev.mysql.com/doc/mysql/en/installing-binary.html and successfully ran the MySql grant tables script, 6. If you haven't installed MySQL before, you must create the MySQL grant tables: shell scripts/mysql_install_db --user=mysql I then followed here, http://dev.mysql.com/doc/mysql/en/automatic-start.html to a T, and copied mysql.server to /etc/init.d, and just for good measure, gave it permission to execute, chmod +x /etc/init.d/mysql I then rebooted, to see how things would go at boot, and saw a failed message for mysqld_safe, Apr 16 21:20:01 localhost lsb_log_message: Can't execute ./bin/mysqld_safe failed Can anyone explain why it wouldn't start..? Anything I should be looking for..? I can start the server with ./mysqld_safe from within the bin dir and I can connect as root or mysql. Cheers. /etc/init.dmysql permissions [EMAIL PROTECTED] init.d]# ls -lh mysql -rwxr-xr-x 1 root root 6.2K Apr 16 21:11 mysql Mark Sargent. -- 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: Visual C++ 6.0 and MySQL
Hello. Have you been at: http://dev.mysql.com/doc/mysql/en/cplusplus.html I have to create with Microsoft Visual C++ a program connected to a database. I am thinking about MySQL, but I have never did it. I have tried to located information about API for C++ on www.mysql.com http://www.mysql.com/ , but I have not success. Can anybody tell me where I can find a good tutorial and examples? Thank you,Vicente [EMAIL PROTECTED] wrote: -- 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: order important in grant commands?
Hello. I've reported a bug. See: http://bugs.mysql.com/bug.php?id=9952 Mark M. Ito [EMAIL PROTECTED] wrote: Gleb et al., I've tried something else. Fearing that the problem is due to some interaction with an already rather complicated set of privilege tables, I tried to reproduce the problem starting from a very simple mysql database. I was indeed able to reproduce the problem, but the order of good and bad grants is reversed! That is to say, in my original post, the good scenario was to grant the restricted select privilege to the entire world, and then grant all privileges to the local domain. In this new context, this order is the bad one. See the transcript below. One difference is that the privileges listed when all is granted are enumerated in the show grant report with the simple mysql database rather than being reported as ALL PRIVILEGES. This could be because, in previous posts, the mysql database used on the 4.1.11-standard server that I have been using for these tests was dumped from a server running 4.0.13-standard. (Or not. Dunno.) - Mark Here is the transcript: ==begin quote== claspc2:marki:1026 mysql -uroot mysql Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 152 to server version: 4.1.11-standard Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql revoke all on testdb.* from testuser@%; Query OK, 0 rows affected (0.00 sec) mysql revoke all on testdb.* from testuser@%.jlab.org; Query OK, 0 rows affected (0.00 sec) mysql grant select on testdb.* to testuser@%; Query OK, 0 rows affected (0.00 sec) mysql grant all on testdb.* to testuser@%.jlab.org; Query OK, 0 rows affected (0.00 sec) mysql quit Bye claspc2:marki:1027 mysql -hclaspc2.jlab.org -utestuser testdb Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 153 to server version: 4.1.11-standard Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql show grants for current_user(); +---+ | Grants for [EMAIL PROTECTED] | +---+ | GRANT USAGE ON *.* TO 'testuser'@'%.jlab.org' | | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER ON `testdb`.* TO 'testuser'@'%.jlab.org' | +---+ 2 rows in set (0.00 sec) mysql insert into testdb.testtable set a=7; ERROR 1044 (42000): Access denied for user 'testuser'@'%.jlab.org' to database 'testdb' claspc2:marki:1028 mysql -uroot mysql Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 154 to server version: 4.1.11-standard Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql revoke all on testdb.* from testuser@%; Query OK, 0 rows affected (0.00 sec) mysql revoke all on testdb.* from testuser@%.jlab.org; Query OK, 0 rows affected (0.00 sec) mysql grant all on testdb.* to testuser@%.jlab.org; Query OK, 0 rows affected (0.00 sec) mysql grant select on testdb.* to testuser@%; Query OK, 0 rows affected (0.00 sec) mysql quit Bye claspc2:marki:1029 mysql -hclaspc2.jlab.org -utestuser testdb Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 155 to server version: 4.1.11-standard Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql show grants for current_user(); +---+ | Grants for [EMAIL PROTECTED] | +---+ | GRANT USAGE ON *.* TO 'testuser'@'%.jlab.org'
Re: use mysql and segmentation fault
Hello. If you are able to reproduce this problem on another box with a similar configuration, I suggest you to report a bug. BTW mysql client has some problems already reported, see: http://bugs.mysql.com/bug.php?id=9870 Xu Hao [EMAIL PROTECTED] wrote: Hi everyone! I downloaded and installed the mysql 4.1 rpms on my RedHat Enterprise Linux 3 box and found a strange problem. If I invoke the client program by mysql -u root -p'mypassword' and then type use mysql, then segmentation fault happens, but if I invoke the client program by mysql -u root -p'mypassword' -A, then everything is ok. See below for details. [EMAIL PROTECTED] mysql]# mysql -u root -p** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 to server version: 4.1.11-standard Reading history-file /root/.mysql_history Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql use mysql Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A segmentation fault [EMAIL PROTECTED] mysql]# mysql -u root -pphoenix -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 9 to server version: 4.1.11-standard Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql use mysql Database changed mysql Any help? Thanks! Hao Xu -- 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: Blocking connections
Hello. We don't have 4.1.11 binaries for NetBSD, so this MySQL version wasn't tested enough on this platform. I suggest you to switch to the debugging version and find some clues in the trace files. See: http://dev.mysql.com/doc/mysql/en/debugging-server.html Tim [EMAIL PROTECTED] wrote: I'm having an issue with MySQL 4.1.11 on Netbsd 2.0.2. I had MySQL running 4.0x running under NetBSD 1.6.2, and when I upgraded to the new NetBSD, I went ahead and upgraded mysql as well. For some reason, mysql will only allow one connection at a time and blocks all other connections. New connections don't get a Too Many Connections error, which means that setting max_connections in my.cnf doesn't help. The new connections just hang forever until the first connection closes. Running show processlist via the command list gives: mysql show processlist; ++--+---+--+-+--+- -- 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]
Query How To
Hi list I need to make a Quero that select all fields in wich the date are inferior in 10 days to actual date. Something like this: Actual date: 18-04-2005 Select all fields in which are bigger than: 08-04-2005. How can I do this? Regards Reynier Pérez Mira 3ero. Ing. Informática Entre más inteligente me siento, más me doy cuenta de lo ignorante que soy.
Re: Group By - Is there a way to set which rows values are used for the fields not in the Group By clause?
create table temp select * from viewvisitor order by lastviewtime desc; select app, itemid, ownerid, visitorid, vusername,lastviewtime, sum(viewcount) AS totalcount, itemname from temp where ownerid = 2 GROUP BY concat( app, itemid ) ORDER BY totalcount; or if you only care about max(lastviewtime), you can do this, select app, itemid, ownerid, visitorid, vusername, max(lastviewtime), sum(viewcount) AS totalcount, itemname from viewvisitor where ownerid = 2 GROUP BY concat( app, itemid ) ORDER BY totalcount; Rich Carr wrote: Is there a way to set which rows values are used by the GROUP BY clause for the fields that are not in the GROUP BY clause? In this following select statement the group by and order work but the value of the lastviewtime field is not the value of the most recent datetime row. Is there any way to modify the select statement so that the returned lastviewtime field will have the most recent datetime? select app, itemid, ownerid, visitorid, vusername, lastviewtime, sum(viewcount) AS totalcount, itemname from viewvisitor where ownerid = 2 GROUP BY concat( app, itemid ) ORDER BY totalcount create table viewvisitor ( app char(15), itemidINT UNSIGNED NOT NULL, ownerid INT UNSIGNED NOT NULL, ousername varchar(25), visitorid INT UNSIGNED NOT NULL, vusername varchar(25), vfullname varchar(70), lastviewtime DATETIME NOT NULL, viewcount INT, itemname VARCHAR(40), PRIMARY KEY master(app, visitorid, itemid), INDEX (ownerid), INDEX (lastviewtime), INDEX (viewcount) ); Thanks!!! Rich - Do you Yahoo!? Yahoo! Small Business - Try our new resources site! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: Error 1130 when on local machine, but can connect remotely
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 My mysql server is running on mysql.xxx.edu, for this example. There is a cname so that alias.xxx.edu is mapped to mysql.xxx.edu, so that we can failover to a different server by changing the cname. When I type: mysql -h alias.xxx.edu -pPassword -u username mydatabase I get: ERROR 1130 (0): #HY00Host 'alias' is not allowed to connect to this MySQL server But, when I am on my development machine, and I use the exact same command, it works. Why would it fail when I am on the computer that the server is actually running on? Thanx for any suggestions. We are using Solaris 9, and mysql 4.1.xx - -- Love is mutual self-giving that ends in self-recovery. Fulton Sheen James Black[EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.1 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCY85NikQgpVn8xrARAod6AKCGlDmqIYxHsuyEnwqX50IlW1tIKQCeKkIt fuq5O7az9vUS6vh5hqSKX/s= =yg44 -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Access denied for user: 'apache@localhost' (Using password: NO)
I have a web application running with Apache with PHP 4.1.2 (revision 7.2.6) and MySQL 3.23.58. The access from the PHP code to the MySQL server using the IP of the machine with MySQL, an username and a password, and it works fine. But sometimes we get the MySQL error: Access denied for user: '[EMAIL PROTECTED]' (Using password: NO) All the connections to the database fails for this reason, and we are sure that we are passing correctly the username, password and IP address. If we restart MySQL, then the problems disappears, and it works fine, but we don't know why it happens. The username that we are using to access MySQL is not 'apache', apache, as you can guess, is the name of the user that is running Apache. Thanx in advance, Jorge Cambra _ Descarga gratis la Barra de Herramientas de MSN http://www.msn.es/usuario/busqueda/barra?XAPID=2031DI=1055SU=http%3A//www.hotmail.comHL=LINKTAG1OPENINGTEXT_MSNBH -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query How To
Reynier Perez Mira [EMAIL PROTECTED] wrote on 04/18/2005 10:44:39 AM: Hi list I need to make a Quero that select all fields in wich the date are inferior in 10 days to actual date. Something like this: Actual date: 18-04-2005 Select all fields in which are bigger than: 08-04-2005. How can I do this? Regards Reynier Pérez Mira 3ero. Ing. Informática Entre más inteligente me siento, más me doy cuenta de lo ignorante que soy. First: remember that MySQL uses dates in ISO format (-MM-DD HH:NN:SS.mmm). The date 18-04-2005 is written as '2005-04-18' for MySQL. Second: Assuming you are on v3.23 or newer SELECT ... FROM ... WHERE yourdatefield = CURDATE() - INTERVAL 10 day; will find all of the records from today to 10 days ago. ( http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html) Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Blocking connections
It's looking more like an OS issue than a mysql issue. We downgraded back to 4.0.24 and are having the same problems. I'm going to cross-post this over to the netbsd lists, and see if someone over there has some ideas. Thanks for the help. Tim On Mon, 18 Apr 2005, Gleb Paharenko wrote: Hello. We don't have 4.1.11 binaries for NetBSD, so this MySQL version wasn't tested enough on this platform. I suggest you to switch to the debugging version and find some clues in the trace files. See: http://dev.mysql.com/doc/mysql/en/debugging-server.html Tim [EMAIL PROTECTED] wrote: I'm having an issue with MySQL 4.1.11 on Netbsd 2.0.2. I had MySQL running 4.0x running under NetBSD 1.6.2, and when I upgraded to the new NetBSD, I went ahead and upgraded mysql as well. For some reason, mysql will only allow one connection at a time and blocks all other connections. New connections don't get a Too Many Connections error, which means that setting max_connections in my.cnf doesn't help. The new connections just hang forever until the first connection closes. Running show processlist via the command list gives: mysql show processlist; ++--+---+--+-+--+- -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
wait_timeout
I have the following in the mysqld section of my.cnf: set-variable = wait_timeout=360 everything else in the conf file seems to take but my wait_timeout variable stays at the default of 28800 my version is 4.0.20 for apple-darwin6.8 Any thoughts?
Re: MySQL 5.0.4-beta has been released
On Mon, 18 Apr 2005 13:28:24 +0200 Joerg Bruehe [EMAIL PROTECTED] wrote: Hi, A new version of MySQL Community Edition 5.0.4-beta Open Source database management system has been released. This version now includes support for Stored Procedures, Triggers, Views and many other features. It is now available in source and binary form for a number of platforms from our download pages at http://dev.mysql.com/downloads/ and mirror sites. 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. snip The mirrors seem to have links to the source tarball, but I've downloaded 5 different files and each time I get: # tar zxvf mysql-5.0.4-beta.tar.gz gzip: stdin: not in gzip format tar: Child returned status 1 tar: Error exit delayed from previous errors Something amiss or am I losing it? Thanks, Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mysqladmin bug
Hi, Our database has been running for some time and it had some threads on it that I needed to kill. I did a mysqladmin process list and got the following | 2521285658 | web | 192.168.11.60:2482 | | Sleep | 747 || | | 2521498154 | web | 192.168.11.60:2570 | | Sleep | 335 || | | 2521570862 | web | 192.168.11.60:2600 | | Sleep | 190 || I tried to kill 2521570862 and got an error [EMAIL PROTECTED] mwright]$ mysqladmin kill 2521570862 mysqladmin: kill failed on 2147483647; error: 'Unknown thread id: 2147483647' I think the thread id number is too large for mysqladmin to deal with, 2147483647 is the max size of an int. Marvin Wright Flights Developer Lastminute.com [EMAIL PROTECTED] ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager. This footnote also confirms that this email message has been swept by MIMEsweeper for the presence of computer viruses. www.mimesweeper.com **
Re: MySQL 5.0.4-beta has been released
Hi! Am Mo, den 18.04.2005 schrieb Josh Trutwin um 17:46: On Mon, 18 Apr 2005 13:28:24 +0200 Joerg Bruehe [EMAIL PROTECTED] wrote: [[...]] 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. snip The mirrors seem to have links to the source tarball, but I've downloaded 5 different files and each time I get: # tar zxvf mysql-5.0.4-beta.tar.gz gzip: stdin: not in gzip format tar: Child returned status 1 tar: Error exit delayed from previous errors Something amiss or am I losing it? Hard to tell - for me, it works on our machine: ~/stage/Downloads/MySQL-5.0 tar ztvf mysql-5.0.4-beta.tar.gz | head drwxrwxrwx 503/100 0 2005-04-16 21:10:24 mysql-5.0.4-beta/ drwxrwxrwx 503/100 0 2005-04-16 21:10:06 mysql-5.0.4-beta/bdb/ -rw-r--r-- 503/1001998 2005-04-16 21:01:45 mysql-5.0.4-beta/bdb/Makefile.in ... and it works on my private PC with a package downloaded from Sunsite Europe: http://dev.mysql.com/get/Downloads/MySQL-5.0/mysql-5.0.4-beta.tar.gz/from/http://sunsite.informatik.rwth-aachen.de/mysql/ [EMAIL PROTECTED]:~ tar tzvf /opt/Downloads/mysql-5.0.4-beta.tar.gz | head drwxrwxrwx 503/100 0 2005-04-16 21:10:24 mysql-5.0.4-beta/ drwxrwxrwx 503/100 0 2005-04-16 21:10:06 mysql-5.0.4-beta/bdb/ -rw-r--r-- 503/1001998 2005-04-16 21:01:45 mysql-5.0.4-beta/bdb/Makefile.in ... Are you sure it did not get damaged during transfer, or by your browser? All I can recommend is to try another mirror. HTH, Joerg Bruehe -- Joerg Bruehe, Senior Production Engineer 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]
mysql import or write my own Perl parser
Hi, I am about to create a database and there are a number of files that I need to load into the database. They are tab delimited files. One of the files contains about 4 or 5 columns. I am only interested in the second and the third column right now but I will load the whole table. The values in the second column can occur more than once in the file. As well the values in the third column can occur more than once in the file. Another file that I want to load as a table into the databse only contains two column and one column will be unique while the second column will have duplicate values in the file. My question is when should I use mysqlimport, or load data and when should I write my own Perl parser to help load the table? What criteria would be needed to decide to read a file into a hash? Also, if I decide to use mysqlimport is there anything I should watch out for? thanks! - Post your free ad now! Yahoo! Canada Personals
Re: MySQL 5.0.4-beta has been released
On Mon, 18 Apr 2005 18:04:46 +0200 Joerg Bruehe [EMAIL PROTECTED] wrote: snip Are you sure it did not get damaged during transfer, or by your browser? All I can recommend is to try another mirror. I'm using elinks text browser, which has worked great for this in the past. I tried about 4 different mirrors. I'll keep at it and see if I get a better download. Thx, Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: wait_timeout
Hello. Set the interactive_timeout variable to this value. I recommend you to upgrade to the latest release (4.1.11 now). I have the following in the mysqld section of my.cnf: set-variable = wait_timeout=360 everything else in the conf file seems to take but my wait_timeout variable stays at the default of 28800 my version is 4.0.20 for apple-darwin6.8 Any thoughts? Rob Brooks [EMAIL PROTECTED] wrote: -- 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: re: Error 1130 when on local machine, but can connect remotely
Hello. You should grant access for user 'your_user'@'alias'. See: http://dev.mysql.com/doc/mysql/en/privilege-system.html James Black [EMAIL PROTECTED] wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 My mysql server is running on mysql.xxx.edu, for this example. There is a cname so that alias.xxx.edu is mapped to mysql.xxx.edu, so that we can failover to a different server by changing the cname. When I type: mysql -h alias.xxx.edu -pPassword -u username mydatabase I get: ERROR 1130 (0): #HY00Host 'alias' is not allowed to connect to this MySQL server But, when I am on my development machine, and I use the exact same command, it works. Why would it fail when I am on the computer that the server is actually running on? Thanx for any suggestions. We are using Solaris 9, and mysql 4.1.xx - -- Love is mutual self-giving that ends in self-recovery. Fulton Sheen James Black[EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.1 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCY85NikQgpVn8xrARAod6AKCGlDmqIYxHsuyEnwqX50IlW1tIKQCeKkIt fuq5O7az9vUS6vh5hqSKX/s= =yg44 -END PGP SIGNATURE- -- 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 Query-Cache Reset
Hello. In my opinion, your status variables are not so bad-looking. About a half of your queries are taken from the cache and the Key_reads/Key_read_requests ratio is less than 1%. Check that the system doesn't start swapping with high value of Query Cache size. If you run lots of queries with 'group by' clause, increasing the tmp_table_size variable could help. The high load of processor is suspicious for me. What hardware do you have? Tell the OS and MySQL version as well. Dto. Sistemas de Unitel [EMAIL PROTECTED] wrote: Hi, I=92m the administrador of a MySQL Server, it have lot=92s of queries, = and now it=92s getting high load of processor, I try to increase the size of = MySQL Query Cache, but if I put more than 128 Mb the cache it=92s reseting all = the time=A0and the performance is worst. The server have 1,2 Mb of RAM and I = can assign 256 or more MB to Query Cache for reduce processor load. My actual my.cnf config is: [mysqld] datadir=3D/var/lib/mysql socket=3D/var/lib/mysql/mysql.sock old_passwords=3D1 max_connections =3D 400 key_buffer =3D 64M myisam_sort_buffer_size =3D 32M join_buffer_size =3D 1M read_buffer_size =3D 4M sort_buffer_size =3D 4M table_cache =3D 1024 thread_cache_size =3D 128 wait_timeout =3D 1800=20 connect_timeout =3D 10 max_allowed_packet =3D 16M max_connect_errors =3D 10 query_cache_limit =3D 1M query_cache_size =3D 128M query_cache_type =3D 1 And my show status info: mysql show status; +--++ | Variable_name| Value | +--++ | Aborted_clients | 8378 | | Aborted_connects | 307| | Binlog_cache_disk_use| 0 | | Binlog_cache_use | 0 | | Bytes_received | 114459945 | | Bytes_sent | 1355402553 | | Com_admin_commands | 49554 | | Com_alter_db | 0 | | Com_alter_table | 0 | | Com_analyze | 0 | | Com_backup_table | 0 | | Com_begin| 0 | | Com_change_db| 178087 | | Com_change_master| 0 | | Com_check| 0 | | Com_checksum | 0 | | Com_commit | 0 | | Com_create_db| 0 | | Com_create_function | 0 | | Com_create_index | 0 | | Com_create_table | 0 | | Com_dealloc_sql | 0 | | Com_delete | 49499 | | Com_delete_multi | 0 | | Com_do | 0 | | Com_drop_db | 0 | | Com_drop_function| 0 | | Com_drop_index | 0 | | Com_drop_table | 0 | | Com_drop_user| 0 | | Com_execute_sql | 0 | | Com_flush| 0 | | Com_grant| 0 | | Com_ha_close | 0 | | Com_ha_open | 0 | | Com_ha_read | 0 | | Com_help | 0 | | Com_insert | 49288 | | Com_insert_select| 0 | | Com_kill | 0 | | Com_load | 3 | | Com_load_master_data | 0 | | Com_load_master_table| 0 | | Com_lock_tables | 96 | | Com_optimize | 0 | | Com_preload_keys | 0 | | Com_prepare_sql | 0 | | Com_purge| 0 | | Com_purge_before_date| 0 | | Com_rename_table | 0 | | Com_repair | 0 | | Com_replace | 61263 | | Com_replace_select | 0 | | Com_reset| 0 | | Com_restore_table| 0 | | Com_revoke | 0 | | Com_revoke_all | 0 | | Com_rollback | 0 | | Com_savepoint| 0 | | Com_select | 250306 | | Com_set_option | 2560 | | Com_show_binlog_events | 0 | | Com_show_binlogs | 0 | | Com_show_charsets| 0 | | Com_show_collations | 0 | | Com_show_column_types| 0 | | Com_show_create_db | 102| | Com_show_create_table| 2550 | | Com_show_databases | 10 | | Com_show_errors | 0 | | Com_show_fields | 3006 | | Com_show_grants | 0 | | Com_show_innodb_status | 0 | | Com_show_keys| 0 | | Com_show_logs| 0 | | Com_show_master_status | 0
Re: Access denied for user: 'apache@localhost' (Using password: NO)
Hello. It is strange that refusing message says you're not using passwords. Does the problem disappear after the FLUSH HOSTS statement, instead of restarting MySQL server? Jorge Cambra Aused [EMAIL PROTECTED] wrote: I have a web application running with Apache with PHP 4.1.2 (revision 7.2.6) and MySQL 3.23.58. The access from the PHP code to the MySQL server using the IP of the machine with MySQL, an username and a password, and it works fine. But sometimes we get the MySQL error: Access denied for user: '[EMAIL PROTECTED]' (Using password: NO) All the connections to the database fails for this reason, and we are sure that we are passing correctly the username, password and IP address. If we restart MySQL, then the problems disappears, and it works fine, but we don't know why it happens. The username that we are using to access MySQL is not 'apache', apache, as you can guess, is the name of the user that is running Apache. Thanx in advance, Jorge Cambra _ Descarga gratis la Barra de Herramientas de MSN http://www.msn.es/usuario/busqueda/barra?XAPID=2031DI=1055SU=http%3A//www.hotmail.comHL=LINKTAG1OPENINGTEXT_MSNBH -- 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: Include repeats in query
Shawn, that's perfect, EXACTLY what I was looking for! Gotta take more notice of the reference manual from now on tho. - Martin -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 18 April 2005 14:22 To: Martin Gallagher Cc: mysql@lists.mysql.com Subject: Re: Include repeats in query Martin Gallagher [EMAIL PROTECTED] wrote on 04/16/2005 08:49:35 AM: Hi, I have the following query: SELECT person FROM people WHERE ORDER BY RAND() * (1 / score) LIMIT 1 This returns a person randomly, but the chance of the person being selected is increased with a higher `score`. What I want to do is make a single query that will return 10 results in this fashion. Currently I'm doing: for (i = 1; i = 10; i++) do: SELECT person FROM people WHERE ORDER BY RAND() * (1 / score) LIMIT 1 Which returns a results like: Mike Sam Sam Mike Mike Mike John Sam Mike John This is exactly the result I desire, but programmatically it's not the most efficient way. I'm guessing using 1 query and using the result set is MUCH faster, 1 query... 1 result, instead of 10. I have tried: (SELECT person FROM people WHERE ORDER BY RAND() * (1 / score) LIMIT 1) UNION (X) UNION (X) ... [repeat ten times] This however returns this kind of result: Mike Sam John The UNION query seems to remove the repeats, and because this is a mathematical system this will throw it out. This of course is not what I want :-(. Is there an option that allows repeats? Cheers, - Martin You are correct in saying that a UNION query eliminates repeats. However a UNION ALL does not. Just add the ALL keyword where appropriate and you should get the results you wanted. http://dev.mysql.com/doc/mysql/en/union.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine PS - I think this weighted random choice routine would perform better if you randomly picked a name from a 3 member array in your source code. You can still use the score to adjust the probability of picking and you won't have to do 10 queries to get a 10 name list. You only do a single query and re-use the results. Just my $.02 - SG -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: HELP WITH A DATE QUERY USING 4.0.17
I'm not sure if there is a built-in, but what I usually do for similar things is to use general purpose table to force iteration. E.g. create a table called ITERATE with one column, x, and populate with values 0,1,2,3,4,... -- in your case up to 10 -- and index the column. Then it's easy - I only show the date field here: select DATE_ADD(B.Booking_Start_Date,interval it.x day) from Bookings B,iterate it where B.User_ID = '610' and it.x = 1 and it.x = 10 group by DATE_ADD(B.Booking_Start_Date,interval it.x day) Regards, John -Original Message- From: shaun thornburgh Hi, I have a table called Bookings which holds start times and end times for appointments, these are held in Booking_Start_Date and Booking_End_Date. I have a page on my site that runs a query to produce a grid to show availiability per day for the next ten days for each user of the system. Users work 8.5 hours a day and the query shows how many hours available the user has on that day: SELECT 8.5 - (SUM(((DATE_FORMAT(B.Booking_End_Date, '%k') * 60 ) + DATE_FORMAT(B.Booking_End_Date, '%i')) - ((DATE_FORMAT(B.Booking_Start_Date, '%k') * 60 ) + DATE_FORMAT(B.Booking_Start_Date, '%i'))) / 60) AS Available_Hours FROM Bookings B WHERE B.User_ID = '610' AND NOT ( '2005-04-08' DATE_FORMAT(Booking_Start_Date, %Y-%m-%d) OR '2005-04-08' DATE_FORMAT(Booking_End_Date, %Y-%m-%d) ) The problem here is I have to do this query to produce a result for each cell(date) and then for each user so 100 users = 1000 queries to load the page! Is there a way to produce the result so that I only need one query per user so it groups the result by day for the next ten days? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL/InnoDB-5.0.4 is released
Hi! Greetings to all from the MySQL Users Conference 2005 in Santa Clara! The conference has just kicked off with tutorials, and will last till Thursday. Close to 1000 people are expected to attend the conference. MySQL-5.0 is probably the most important new MySQL release in several years. On the MySQL side, a vast number of new features, like stored procedures and views have been implemented in 5.0. On the InnoDB side, we changed the table format to a more space-saving one, and implemented the 2-phase commit XA protocol. MySQL-5.0.4 is a bugfix release. Since MySQL-5.0.3 introduced a large number of new features, we cannot yet recommend the MySQL 5.0 series for production use. This is a beta release for testing and developing your applications that take advantage of MySQL's new powerful features. InnoDB is the MySQL table type that supports foreign key constraints, transactions, two-phase commit in XA, row-level locking, non-locking consistent read (MVCC), all four SQL-1992 isolation levels of transactions, multiple tablespaces, asynchronous unbuffered disk I/O on Windows, and a non-free hot online backup tool. You can download MySQL-5.0.4 from http://dev.mysql.com/downloads/mysql/5.0.html Bugs fixed: * ENUM and SET columns were treated incorrectly as character strings. This bug did not manifest itself with latin1 collations if there were less than about 100 elements in an enum, but it caused malfunction with UTF-8. Old tables will continue to work. In new tables, ENUM and SET will be internally stored as unsigned integers. (Bug #9526) * Avoid test suite failures caused by a locking conflict between two server instances at server shutdown/startup. This conflict on advisory locks appears to be the result of a bug in the operating system; these locks should be released when the files are closed, but somehow that does not always happen immediately in Linux. (Bug #9381) * True VARCHAR in 5.0.3: InnoDB stored the 'position' of a row wrong in a column prefix primary key index; this could cause MySQL to complain 'ERROR 1032: Can't find record' in an update of the primary key, and also some ORDER BY or DISTINCT queries. (Bug #9314) Upgrading from 4.1: * Starting from 5.0.3, a VARCHAR in MySQL is a 'true' VARCHAR. It remembers the number of spaces that there were at the end of the string. Previously, MySQL at storage trimmed end spaces from a VARCHAR. Tables created with 5.0.3 will remain to have the old VARCHAR semantics, while new tables will have the new semantics. * The sorting order for end-space in TEXT columns for InnoDB and MyISAM tables has changed. Starting from 5.0.3, InnoDB compares TEXT columns as space-padded at the end. If you have a non-unique index on a TEXT column, you should run CHECK TABLE on it, and run OPTIMIZE TABLE if the check reports errors. If you have a UNIQUE INDEX on a TEXT column, you should rebuild the table with OPTIMIZE TABLE. * The sorting order of BINARY and VARBINARY may still change in some 5.0.x version. * There is a bug in the InnoDB sorting order of ENUMs if the collation of the ENUM is not latin1 or if there are more than about 100 different values for the ENUM. This bug is also present in 4.0 and 4.1. * InnoDB Hot Backup 2.0.1 or earlier does not work with MySQL-5.0.3 or later. A new version 2.1 will. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help restoring
I have backed a bugzilla database using: mysqldump -u root -p bugs bugzilla.20050418 and am trying to import it on a new mysql server using mysql -u root -p bugs bugzilla.20050418 I'm getting ERROR at line 84: Line 84 is all garbled text... Working with 3.23.58 ... Any thoughts? -- Patrick Campbell OurVacationStore.com Website Administrator Tel. 602.896.4729
RE: Client Side Query cache
Linux does some sort of page caching automatically and its the reason for there never being any Free memory in a Linux system. So if you read your db via XML and php and develop a page from that using a CSS style sheet Linux will cache it as a Page.(Took us forever to catch that one, something to do with CSS and no one really knows the answer that we have seen) As for a query cache edit your My.cnf and query_cache_type 1 2 = Cache only if script says to(my suggested setting, make your coders code for caching instead of catch all settings) This does cause some grief if the data changed you can see a cached result instead.(Even though your not supposed to). Also if your running in PPC feeds you can't cache those as you will hit their timeouts and get no redirect results. Our PPC XML Feed (http://www.firebasesofware.com) only allows the link to be valid for 3 minutes before your visitor will be redirected to our front door. This is not a traffic grab. Just 94% of cached results are typically fraud clicks so we don't allow the caching of results. We are more cautious than most (they use 5 minutes) only because we have a very high paying feed and it attracts the low life's of the internet world. Thanks Donny Lairson President 29 GunMuse Lane P.O. box 166 Lakewood NM 88254 http://www.gunmuse.com 469 228 2183 -Original Message- From: Mister Jack [mailto:[EMAIL PROTECTED] Sent: Thursday, April 14, 2005 4:49 AM To: mysql@lists.mysql.com Subject: Client Side Query cache Hi, I was wondering if there is any query cache code/lib somewhere to cache certains queries ? I'm always doing the same queries, (and the result never change, so I could spare the round-trip to the server), but caching each tine the data for it is a bit of work. Thanks, for your suggestions -- 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: After upgrade to 5.03beta, mysqld won't start [SOLVED]
On 4/12/2005 9:31 PM John Swartzentruber wrote: On 4/12/2005 8:43 PM Petr Chardin wrote: Hi Jonh, As I said, I couldn't find any error logs that seemed to relate (i.e, had information newer than the upgrade and start failures). I looked into the /etc/init.d/mysql script and tried running mysqlmanager directly. It would not run because of errors. From another message in this newsgroup, I saw that someone else was having similar problems and that removing the items from the [mysql] section of my.conf took care of them. That's what I did, and then the server started. From what I can tell, this appears to be a bug because the [mysql] section should have no affect on the server process and should not prevent mysqlmanager from running. Yes, it is a bug in mysqlmanager and it is fixed in the source repository. Please upgrade to 5.0.4 when it is released. Petr I just wanted to confirm that 5.0.4 does fix the problem as promised. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Running mysql on a headless Windows CE Device
Greetings! I am trying to figure out how I can install mysql on my headless WinCE 5.0 web server device. I've already gotten PHP up and running, my next step is to install mysql, and run phpMyAdmin to allow people to navigate and modify their tables. I've been searching everwhere for mysql binaries for CE, but have found nothing. I saw the 'Embeded mysql' webpages, and filled out the contact form. The lady that called told me it was $65 per license. I'm not sure if we were talking about the same thing. I'm pretty sure mysql is free, because I can download the source code. Why would I pay $65 per unit to add a mysql database to my product. Even Windows CE only costs a few bucks per license. I'd hate to use Microsoft's SQL server, but I feel like i've hit a dead end with mysql. If anyone can offer some advice on how to get mysql running on windows ce, or can point me to some programmers who would be willing to port the source code for me, please contact me. -LiquidIce [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help restoring
Can one of these columns have binary data in it? If so that could explain what the garbled text is. Although it should still export/import correctly. My guess is that somewhere beteween file and file file became corrupted. Try dumping it again. If possible check that table to make sure it's ok. -Eric On 4/18/05, Patrick Campbell [EMAIL PROTECTED] wrote: I have backed a bugzilla database using: mysqldump -u root -p bugs bugzilla.20050418 and am trying to import it on a new mysql server using mysql -u root -p bugs bugzilla.20050418 I'm getting ERROR at line 84: Line 84 is all garbled text... Working with 3.23.58 ... Any thoughts? -- Patrick Campbell OurVacationStore.com Website Administrator Tel. 602.896.4729 -- Eric Bergen [EMAIL PROTECTED] http://www.ebergen.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysqladmin bug
Try enclosing 2521570862 in single quotes on the command line. It's just a guess but it might be bash overflowing that number and not mysqladmin. -Eric On 4/18/05, Marvin Wright [EMAIL PROTECTED] wrote: Hi, Our database has been running for some time and it had some threads on it that I needed to kill. I did a mysqladmin process list and got the following | 2521285658 | web | 192.168.11.60:2482 | | Sleep | 747 || | | 2521498154 | web | 192.168.11.60:2570 | | Sleep | 335 || | | 2521570862 | web | 192.168.11.60:2600 | | Sleep | 190 || I tried to kill 2521570862 and got an error [EMAIL PROTECTED] mwright]$ mysqladmin kill 2521570862 mysqladmin: kill failed on 2147483647; error: 'Unknown thread id: 2147483647' I think the thread id number is too large for mysqladmin to deal with, 2147483647 is the max size of an int. Marvin Wright Flights Developer Lastminute.com [EMAIL PROTECTED] ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager. This footnote also confirms that this email message has been swept by MIMEsweeper for the presence of computer viruses. www.mimesweeper.com ** -- Eric Bergen [EMAIL PROTECTED] http://www.ebergen.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql import or write my own Perl parser
awk is probably the best tool I can think of for cutting columns out
of a text file. Something like
awk -F \\t '{ print $2 , $3 }' my_file
could be used to pick the second and third column out of a file prior
to importing it.
-Eric
On 4/18/05, newbie c [EMAIL PROTECTED] wrote:
Hi,
I am about to create a database and there are a number of files that I need
to load into the database. They are tab delimited files. One of the files
contains about 4 or 5 columns. I am only interested in the second and the
third column right now but I will load the whole table. The values in the
second column can occur more than once in the file.
As well the values in the third column can occur more than once in the file.
Another file that I want to load as a table into the databse only contains two
column and one column will be unique while the second column will have
duplicate values in the file.
My question is when should I use mysqlimport, or load data and when should I
write my own Perl parser to help load the table?
What criteria would be needed to decide to read a file into a hash?
Also, if I decide to use mysqlimport is there anything I should watch out for?
thanks!
-
Post your free ad now! Yahoo! Canada Personals
--
Eric Bergen
[EMAIL PROTECTED]
http://www.ebergen.net
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB Performance
Eko and all, The MyIsam storage engine is a non transactional engine and InnoDb is a transactional engine. That is the main difference. So I think the MyIsam engine should be faster. However, some file systems that have journals are faster than non journalling file system. It's not always that clear cut. I've just switched a number of big customer databases to InnoDB and noone's noticed any difference - if anything it's going faster. DSL ( mysql, sql ) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: optimal number of connections?
The number of connections touches several areas. Fortunately there is very little overhead in creating MySQL connections. Connection pooling hits a few snags in MySQL in the area of session and user variables. Anything set inside a session will stick when the next thread picks up a connection. These bugs are very sneaky to find as they hardly ever crop up during testing. As far as speed goes it really depends on the operating system. MySQL keeps one thread per connection + a few other threads. Linux people like to say that 1.5 threads per cpu is optimal. This is due to Linux (now old) model of N:N threading, one thread in kernel space equals a thread in user space which is also know as light weight processes. NGPT (I think) is slowly coming into play to help eliminate that problem. Since databases by nature are easy on the CPU we really don't have to worry about this much. Since MySQL connections are so light weight and there are risks in connection pooling I recommend not using it. One thing you do need to watch for is making sure the thread cache is big enough so that when your database starts accepting lots of connections you aren't thrashing the CPU creating/destroying threads. This can be checked with show status like 'threads_created'; The short answer is: keep max connections high enough for load, watch the thread cache, don't use connection pooling unless you really know your application. -Eric On 4/15/05, Jeff Drew [EMAIL PROTECTED] wrote: Our Java application has several threads that use JDBC to write to several tables. Currently, we use one JDBC connection. Would performance improve if we used a connection per table? Is a single connection bad for some reason? Since connection pooling is a hot topic, I'm wondering if we're missing out somehow. Thanks Jeff -- Eric Bergen [EMAIL PROTECTED] http://www.ebergen.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Warning (reformulated)
Warnings are usually caused by type mismatches in what you try to put in a column vs what it will actually hold. For example putting a 'asdf' in a char(3) will produce a warning because 'asdf' was truncated to 'asd' Check your column types vs the data you tried to insert. -Eric On 4/15/05, Andy Pieters [EMAIL PROTECTED] wrote: Hi all Realizing maybe my first post wasn't clear enough... How can I determine the cause for a warning. The MySql version is 3.23.58 The query I used was: UPDATE `shop_products` SET `name`='TestProd' , `shopid`='20-302' , `sdesc`='This is a test product' , `ldesc`='Long description for the test product\r\n\r\nIt supports formatting but not objectss\r\n\r\n' , `unit`='piece' , `units`='pieces' , `stock`='10' , `unitprice`='20.0' , `tax`='0' , `min_units`='1' , `max_units`='' , `delivery_inc_unitprice`='0.0' , `delivery_tax`='0' , `delivery_time`='' , `visible`='1' , `active`='' WHERE `id`='1' LIMIT 1; Thank you all With kind regards Andy -- Registered Linux User Number 379093 -- ---BEGIN GEEK CODE BLOCK- Version: 3.12 GAT/O/CM 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/ -- -- -- Eric Bergen [EMAIL PROTECTED] http://www.ebergen.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 4==4something: equal vs identical
The opposite of Paul's cast is: select '4ae'f + 0; That will show you what MySQL ends up with after casting a string to an integer. -Eric On 4/16/05, Paul DuBois [EMAIL PROTECTED] wrote: At 22:18 +0200 4/16/05, Andy Pieters wrote: Hi everone I ran into some situation where MySql selects a row by using the following query: SELECT `id` FROM `shop_products` WHERE `id`=4aef LIMIT 1; ++ | id | ++ | 4 | ++ Granted the field IS of type int but 4 is not identical to 4aef (it may be equal to the eyes of MySql) Isn't there an MySql equivalent of === (test for same+same type) or should I just ignore this. For a comparison of integer with string, the string is converted to number and a numeric comparison is done. '4aef' converts to 4. I suppose you could convert the integer to string instead: WHERE CONCAT(`id`) = 4aef -- 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] -- Eric Bergen [EMAIL PROTECTED] http://www.ebergen.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: zip code search within x miles
You can buy up-to-the-minute zips, or snag TIGER (or gazateer?) data
that's a bit old for free.
It's a 1-1 mapping of zip to long/lat.
The tricky bit is this.
There's about 65,000 zips, even in the out-dated list for free.
Let's say you've got, oh, 2000 records to search through.
You're gonna end up doing a JOIN with:
2,000 X 65,000 == 130,000,000 tuples (records/results).
130 MILLION tuples is *way* too many for your basic $20/month site.
So what you do is break the rules.
That's right, it's rule-breaking time.
First, add a longitude and latitude column to your 2000 record table,
default to NULL.
Next, add a trigger (in MySQL 4.1+ I think) so that any time you change
the country or zip, the latitude/longitude gets looked up in the zips
table, and filled in.
If you can't do triggers (in MySQL pre-4.1) you'll have to arrange your
Busicness Logic such that any time you UPDATE country or zip in your 2000
records table, you reset longitude/latitude to NULL.
Next, write a cron job to snag, say, 10 records from your table WHERE
latitude is NULL in random order.
Lookup the country and zip in your zips table, and get your $latitude and
$longitude for that country/zip then do:
update EXAMPLE set latitude = $latitude, longitude = $longitude where
country = '$country' and zip = '$zip'
Errr, I kinda slipped into PHP there, but you probably know what I mean.
$x is a variable holding the values you looked up.
Run that cron job a whole lot at first, by hand, to get mostly all the
2000 records fixed up with the right long/lat.
The point of this is that *NOW* you only have to search through 2000
records for your distance function, which sure beats a join with 130
million records, eh?
Also, if you just want the NEAREST matches, forget all that complicated
crap about Great Circle distance and expensive trigonometric calculations.
You can just get the zip they want to search by, lookup $longitude and
$latitude for that zip, and then use:
select {application_data_here), abs(latitude - $latitude) + abs(longitude
- $longitude) as closest from ... order by closest
If MySQL won't let you use 'closest' in the where clause, just move the
calculations there.
I've done this before for clients who didn't want to muck with all this.
It's a few hours' work. Call it $200.
PS I'm working on a GLOBAL POSTAL system which will data-gather OSS
long/lat and country/zip for the entire world, rather than the current
mess of having only the US for free, and maybe Canada and Australia, but
not much else is free, and often not even available.
I guess I'd better post here when I launch that. No time-line available yet.
On Fri, April 15, 2005 3:37 pm, Scott Haneda said:
How are sites doing the search by zip and coming up with results within x
miles? Is there some OSS zip code download that has been created for
this?
--
-
Scott HanedaTel: 415.898.2602
http://www.newgeo.com Novato, CA U.S.A.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
Like Music?
http://l-i-e.com/artists.htm
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: zip code search within x miles
I'll share my creative workaround for geographic searches within x miles. This is a theory I developed years ago and have used in live production systems for the last five years. This method to be described is used to produce approximately 100-200k sets of live nearby data per day online. My approach is necessary for ColdFusion application server display of relative nearby geographic data on my very busy website: http://www.pubcrawler.com I use MySQL for the database storage with some MS SQL. First, there are several different formulas readily available as math by which to re-engineer for your particular use. The formulas should be able to be ported to any platform/language given the math functions needed are available. My concept is simple and I believe users are willing to accept some small margin of error. This meaning, a place say a mile or two over further in one direction. Zipcodes are notoriously odd in their adoption in areas. They follow a North to South and East to West incrementation. Certain inferences can simply be made about a high zipcode or a low one belonging to either coast. Nearby zipcodes are minimally useful, other than to say here are other zipcodes (say for instance of a real estate application). The wild goose chase. By all means, you need to reduce the amount of math that goes into preparing a query and say a page in your application. What I do, to avoid pre-compilation - that is making all the possible or common possible results already plotted and thereby pre-planned - is work on establishing the minimums and maximums at four values - these are a min and max for both lat and lon: NW| NE | -- | SW |SE I establish these as the max and mins based on converting the distance (fraction of mile to many miles) to degrees and doing the math to calculate each of the four points. With these four points I can make a very simple and fast comparison where latitude = maxlat and latitude = minlat and longitude = maxlon and longitude = minlon This approach works on a square and gives you an error margin which is the difference of placing the distance exhibited as a circle within the same space exhibited as a square. The error or overlap areas are minimal and for terms of consumer based services will suffice. These areas are triangles with the inward side being an arc. I use this approach to relate okay sized sets of data to one another on the fly (500k restaurants, 6000 beer establishments, 13k bed and breakfasts, 50k hotels, etc.) All the math is and can be done on the fly this way on commodity hardware (800Mhz server) on the application server level. Bringing this to a pure SQL level would increase the speed further. A correlation for nearby hotels with this approach will run in a multi-use environment on average in about four seconds. With further optimization this can be reduce to around 1 second.. This approach is a compromise to more exact math offered elsewhere on the internet. Applying this same thing to apply to the 80k estimated US zipcodes currently would be pie and would run faster just due to the reduced dataset. Zipcode databases are readily available with quarterly subscription updates available today. I believe the US Postal Service even sells a dataset now. Richard Lynch wrote: You can buy up-to-the-minute zips, or snag TIGER (or gazateer?) data that's a bit old for free. It's a 1-1 mapping of zip to long/lat. The tricky bit is this. There's about 65,000 zips, even in the out-dated list for free. Let's say you've got, oh, 2000 records to search through. You're gonna end up doing a JOIN with: 2,000 X 65,000 == 130,000,000 tuples (records/results). 130 MILLION tuples is *way* too many for your basic $20/month site. So what you do is break the rules. That's right, it's rule-breaking time. First, add a longitude and latitude column to your 2000 record table, default to NULL. Next, add a trigger (in MySQL 4.1+ I think) so that any time you change the country or zip, the latitude/longitude gets looked up in the zips table, and filled in. If you can't do triggers (in MySQL pre-4.1) you'll have to arrange your Busicness Logic such that any time you UPDATE country or zip in your 2000 records table, you reset longitude/latitude to NULL. Next, write a cron job to snag, say, 10 records from your table WHERE latitude is NULL in random order. Lookup the country and zip in your zips table, and get your $latitude and $longitude for that country/zip then do: update EXAMPLE set latitude = $latitude, longitude = $longitude where country = '$country' and zip = '$zip' Errr, I kinda slipped into PHP there, but you probably know what I mean. $x is a variable holding the values you looked up. Run that cron job a whole lot at first, by hand, to get mostly all the 2000 records fixed up with the right long/lat. The point of this is that *NOW* you only have to search through
Re: zip code search within x miles
Let's say you've got, oh, 2000 records to search through. You're gonna end up doing a JOIN with: 2,000 X 65,000 == 130,000,000 tuples (records/results). 130 MILLION tuples is *way* too many for your basic $20/month site. I'd say take some easy shortcuts first... like limit the join to the zip code table by the target state (which of course is indexed), then add the one, two or at most three neighboring states, if you're near a border. Or just limit the join to all the neighboring states in one shot). One, two, or three short running queries all in SQL is a whole-lot better than adding un-normalized and redundant fields to the source data table and populating it in a cron job and triggers. Talk about taking the long way around. And if anyone is looking for a datafile with worldwide cities (about 2.7 million records) and their lat/longs (not zips, though), here's a place to download it for free: http://www.maxmind.com/app/worldcities For $50, you can get the addition of population of each city. -Hank mysql, query -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: zip code search within x miles
Applying this same thing to apply to the 80k estimated US zipcodes currently Just for the record, there are about 43,000 distinct US zip codes... and 56,000 zip codes if you double count the zips with multiple city names (when zip codes cross city limits). -Hank mysql, query -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: zip code search within x miles
Hi all, I have done this type of setup before. There is a company that puts out a zipcode file with the Latitude and longitude in it. You can then calculate what's in the distance you are looking for. Scott. -Original Message- From: Robert Dunlop [mailto:[EMAIL PROTECTED] Sent: Friday, April 15, 2005 6:51 PM To: Scott Haneda; MySql Subject: Re: zip code search within x miles I haven't looked into this recently, but as long as 15 years ago there were zip - mile libraries available; I assume similar libraries still exist. The ones I worked with all used only the first 3 digits. Full five digit and nine digit zips get to pretty specific geographic locations, a level of accuracy not needed when telling people what resource exist within x miles. You might try google something like: (zipcode OR zip) distance software library HTH Bob - Original Message - From: Scott Haneda [EMAIL PROTECTED] To: MySql mysql@lists.mysql.com Sent: Friday, April 15, 2005 3:37 PM Subject: zip code search within x miles How are sites doing the search by zip and coming up with results within x miles? Is there some OSS zip code download that has been created for this? -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
