Re: why donn't work mysql_real_connect
mm wrote:
Dear friends,
This is my first cpp module working with mySQL.
It looks like the error is located in
MYSQL *m=mysql_real_connect(myDB, host, user, passwd, ...
There is a way to find out step by step which parameters is wrong?
I am working with Fedora core 3
and MySQL 3.23.58
Thanks,
MT
++ CPP module +++
#include /usr/include/mysql/mysql.h
int modulMySQL() {
printf(modulMySQL 01\n); fflush(stdout);
// MYSQL *mysql_init(MYSQL *mysql)
// ===
MYSQL *myDB;
MYSQL *mysql_init(myDB);
printf(modulMySQL 02\n); fflush(stdout);
// MYSQL *mysql_real_connect(MYSQL *mysql, const char *host, const char
// *user, const char *passwd, const char *db, unsigned int port,
// const char *unix_socket, unsigned long client_flag)
//
char *host=NULL, user[]=myname, passwd[]=mypass;
char db[]=ro;
unsigned int port=3306;
const char *unix_socket=NULL;
unsigned long client_flag=0;
MYSQL *m=mysql_real_connect(myDB, host, user, passwd,
db, port, unix_socket, client_flag);
http://dev.mysql.com/doc/mysql/en/mysql-real-connect.html
both your host _and_ unix_socket are null, this could not work if I
understand well the doc. Use either 127.0.0.1 for host if networking
is enable in mysql, or the full path to the unix socket (defaut
/var/lib/mysql/mysql.sock)
--
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 4.x Error
Hi, Im using : mysqlclient10-3.23.58-6 ,mysql-server-4.1.11-2,mysql-4.1.11-2 On fedora 3. when i run the following command its gives Error: [EMAIL PROTECTED] etc]$ /etc/init.d/mysqld start touch: cannot touch `/var/log/mysqld.log': Permission denied chmod: changing permissions of `/var/log/mysqld.log': Operation not permitted Initializing MySQL database: mkdir: cannot create directory `/var/lib/mysql/mysql': Permission denied chmod: cannot access `/var/lib/mysql/mysql': No such file or directory mkdir: cannot create directory `/var/lib/mysql/test': Permission denied chmod: cannot access `/var/lib/mysql/test': No such file or directory Installing all prepared tables /usr/libexec/mysqld: error while loading shared libraries: libssl.so.5: cannot open shared object file: No such file or directory Installation of system tables failed! Examine the logs in /var/lib/mysql for more information. You can also try to start the mysqld daemon with: /usr/libexec/mysqld --skip-grant You can use the command line tool /usr/bin/mysql to connect to the mysql database and look at the grant tables: shell /usr/bin/mysql -u root mysql mysql show tables Try 'mysqld --help' if you have problems with paths. Using --log gives you a log in /var/lib/mysql that may be helpful. The latest information about MySQL is available on the web at http://www.mysql.com Please consult the MySQL manual section: 'Problems running mysql_install_db', and the manual section that describes problems on your OS. Another information source is the MySQL email archive. Please check all of the above before mailing us! And if you do mail us, you MUST use the /usr/bin/mysqlbug script! [FAILED] why ? the above error is comming..? When i try to remove libssl.so.4 from my system and install libssl.so.5 in the system ,the system does not start.As libssl.so.4 is required. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 4.1.x with php-4.3.x
Mathias wrote: You have the choice between this : http://dev.mysql.com/doc/mysql/en/application-password-use.html and this : The optimal solution when migrating to MySQL 4.1+ from a previous version is to upgrade to PHP 5 (if you're not using it already) and rewrite any code accessing MySQL using the mysqli extension, which is more secure and provides a much better API. much better API. If you don't use the multi-statement-feature, there's absolutely no point upgrading, all the more so you can have both of them compiled with PHP. -- 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]
altering pointer size reveals unexpected result
I ran into a table full situation a couple of days ago, but didn't spot it till last night. I read the advice about altering the pointer-size: http://dev.mysql.com/doc/mysql/en/full-table.html but I neglected to read Jason Collisons comment about backing up my data first. I lost about 50mill records too ... It's not a major disaster, but I'd like to know if it is intended behaviour? /Per Jessen, Zürich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: load balancer and cluster
thanks for the reply. but this solution its imposible because my applications are done allready, or perhaps the applications are not mine. i need a transparent balancer, and i dont anderstand how is possible thet it does not exists. grettings d2clon Gary Richardson wrote: You're probably best to implement it as a layer in your application. You could put a layer around DBI (or whatever programming API you're using) to retry the query on another node if it fails. On 6/23/05, d2clon [EMAIL PROTECTED] wrote: (sorry for my english) hello people: i want to answer if do exists any mysql solution that implements load balancer and redundancy between 'application layer' and 'mysqld layer' i will try to explain me: i want to implement a completed mysql cluster system, and the documentation is very good, but i see a problem: we need to look the picture in this page: http://dev.mysql.com/doc/mysql/en/mysql-cluster-overview.html in this picture we can see that the 'storage layer' implements correctly redundance and load balancer feature, because the 'SQL layer' ask for data to a 'storage layer' but not to a one particular node. but the applications in the 'application layer' do the petitions to the 'SQL layer' directly to a particular node, and them: what happen if a node in the 'SQL layer' is off? how i can offer a load balancer feature between 'application layer' and 'SQL layer'. i searched a lot on the web but i only founded out answers but not solutions.. i also see this thread in this mail list: http://lists.mysql.com/search.php?q=load+balancerlist=m=abegin=60 but i did not found an answer in this thread. any help? thanks a lot fernando guillen (aka d2clon) -- 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: ORDER by Question
Hi this,among other answers, can be done : mysql select * from names; +--+ | name | +--+ | | | The | | | | The | | | +--+ 5 rows in set (0.02 sec) mysql select * from names order by replace(name,'The ',''); +--+ | name | +--+ | | | The | | | | | | The | +--+ 5 rows in set (0.00 sec) Hmm... Disanvantage of such way is replacing of 'The ' substring in *ANY* place of field, not just at the beginning of it. :( Look here: mysql SELECT * FROM names; ++ | name | ++ | | | The | | | | The Yeti | | The | | Xylophone | | Zyxel | ++ 7 rows in set (0.00 sec) mysql SELECT * FROM names ORDER BY REPLACE(name,'The ',''); ++ | name | ++ | | | The | | | | Xylophone | | The Yeti | --- must be earlier :) | Zyxel | | The | ++ 7 rows in set (0.00 sec) -- wbr, sergey v. spivak sergey#spivak.kiev.ua zlob-uanic/eunic/ripe -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Max Connections of MySQL on Linux
Hello. Check your results with official binaries. Set max_connections variable to big enough value. Combinations of different versions of compilers and glibc sometimes could give unpredictable results. huang leo [EMAIL PROTECTED] wrote: Hi, everyone: I had done a test on Linux2.6. I got the max connections of 1079 when I complied the MySQL with static link. But I got the max connections of 7159 when I complied the MySQL with dynamic link. Why has so much difference between the static link and dynamic link? Has anybody know it? $$ Best regards, leo huang 2005-06-27 _ $$$ MSN Hotmail$ http://www.hotmail.com -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innodb Performance Measurement
Hello. A lot of statistics you could get from 'SHOW INNODB STATUS'. For example 'FILE I/O', 'INSERT BUFFER AND ADAPTIVE HASH INDEX', 'BUFFER POOL AND MEMORY' could be helpful. See: http://dev.mysql.com/doc/mysql/en/innodb-monitor.html Manoj [EMAIL PROTECTED] wrote: Greetings, I am using MySQL 4.0.24 and all my tables use InnoDB as default engine. I was interested in finding out the performance of my Buffer space. How can i do it?. If I were to use MyISQM tables, I could have looked at the parameters Key_read_request key_reads to find out the hit rate but am not aware of how to do the same for Innodb hence any help would be appreciated. Cheers Manoj -- 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 4.x Error
Hello. You have several problems and they're should be resolved step by step. First fix the problems with permissions. Usually they're solved by changing the owner of mysql database to mysql user. Check what's wrong with /var/log/mysqld.log. It might be necessery to create it manually. Why are you removing libssl.so.4. I think these libraries could exists both in the system. plaza c [EMAIL PROTECTED] wrote: Hi, Im using : mysqlclient10-3.23.58-6 ,mysql-server-4.1.11-2,mysql-4.1.11-2 On fedora 3. when i run the following command its gives Error: [EMAIL PROTECTED] etc]$ /etc/init.d/mysqld start touch: cannot touch `/var/log/mysqld.log': Permission denied chmod: changing permissions of `/var/log/mysqld.log': Operation not permitt= ed Initializing MySQL database: mkdir: cannot create directory `/var/lib/mysql/mysql': Permission denied chmod: cannot access `/var/lib/mysql/mysql': No such file or directory mkdir: cannot create directory `/var/lib/mysql/test': Permission denied chmod: cannot access `/var/lib/mysql/test': No such file or directory Installing all prepared tables /usr/libexec/mysqld: error while loading shared libraries: libssl.so.5: cannot open shared object file: No such file or directory Installation of system tables failed! Examine the logs in /var/lib/mysql for more information. You can also try to start the mysqld daemon with: /usr/libexec/mysqld --skip-grant You can use the command line tool /usr/bin/mysql to connect to the mysql database and look at the grant tables: shell /usr/bin/mysql -u root mysql mysql show tables Try 'mysqld --help' if you have problems with paths. Using --log gives you a log in /var/lib/mysql that may be helpful. The latest information about MySQL is available on the web at http://www.mysql.com Please consult the MySQL manual section: 'Problems running mysql_install_db= ', and the manual section that describes problems on your OS. Another information source is the MySQL email archive. Please check all of the above before mailing us! And if you do mail us, you MUST use the /usr/bin/mysqlbug script! [FAILED] ---= - why ? the above error is comming..? When i try to remove libssl.so.4 from my system and install=20 libssl.so.5 in the system ,the system does not start.As libssl.so.4 is required. -- 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: altering pointer size reveals unexpected result
Hello. Your situation looks very rare. I didn't found complains in bug database and archives. If you are able to reproduce this situation the bug report would be helpful. Per Jessen [EMAIL PROTECTED] wrote: I ran into a table full situation a couple of days ago, but didn't spot it till last night. I read the advice about altering the pointer-size: http://dev.mysql.com/doc/mysql/en/full-table.html but I neglected to read Jason Collisons comment about backing up my data first. I lost about 50mill records too ... It's not a major disaster, but I'd like to know if it is intended behaviour? /Per Jessen, Z$rich -- 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: connection error from c application
Hello. Send an example of your file and your table's definition (use SHOW CREATE TABLE for this purpose). Please, next time send your replies to the list. Hope you're doing great today. Back to my question last week, I've been encountering this problem of loading my file using the load data local infile command for a particular text file. I cannot determine the error in my sql codes since i find no problem loading other text file into the database. mysql load data local infile 'syslog5.txt' into table log4 fields terminated by ' \t' lines terminated by '\n'; Query OK, 2 rows affected, 513 warnings (0.03 sec) Records: 20 Deleted: 0 Skipped: 18 Warnings: 505 I'm not familiar with this warnings myself. I can only load the first line in the syslog5.txt and the rest are ignored. From the two rows only the first line is stored and the rest are ignored. The result of my select statement from the log4 table includes all null value on the first row and the value of the second line for the second row. Would appreciate if you can teach me how to debug the warning or maybe enlighten me on the cause of the problem. Thanks for all your help. Elizabeth On 6/24/05, Gleb Paharenko [EMAIL PROTECTED] wrote: Hello. What 'SHOW WARNINGS' reports? Elizabeth Bonifacio [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: Decimal comma in input
Hallo Anoop, Op 22 Jun 05 schreef Anoop kumar V aan Jigal van Hemert [EMAIL PROTECTED],: AkV Alternatively, you can parse the text files using application logic AkV (java, c++, etc) and then after extracting (and cleaning) your data AkV insert them into mysql. I had been thinking about that, but that would be a cowardly way out :) As I'm very new to [My]SQL, I prefer to use its own methods as much as possible. Groetjes, Hans. jdh punt beekhuizen bij duinheks punt xs4all punt nl --- GoldED+/LNX 1.1.5/040412 * Origin: The Wizard is using MBSE/Linux (2:280/1018) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Decimal comma in input
Hallo Jigal, Op 22 Jun 05 schreef Jigal van Hemert aan [EMAIL PROTECTED]: JvH What about reading the data into the table and storing the JvH 'amount' in varchar for now. Then you can run an update query in JvH UPDATE `table` SET `amount`= REPLACE( `v_amount`, ',' , '.'), JvH `v_amount` = NULL WHERE `v_amount` IS NOT NULL; Perfect! Thank you very much. Groetjes, Hans. jdh punt beekhuizen bij duinheks punt xs4all punt nl --- GoldED+/LNX 1.1.5/040412 * Origin: The Wizard is using MBSE/Linux (2:280/1018) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Preventing duplicates with load data
Hello, How can I prevent duplicate entries when I fill the data base with load data? I tried ignore, but that has no effect. Probably I'm something very elementary, but I'm still learning... Regards, Hans. jdh dot beekhuizen at duinheks dot xs4all dot nl --- GoldED+/LNX 1.1.5/040412 * Origin: The Wizard is using MBSE/Linux (2:280/1018) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ORDER by Question
Hi, you didn't give an alternative, but i've forgotten just a '^' : mysql SELECT * FROM names ORDER BY REPLACE(name,'The ',''); ++ | name | ++ | | | The | | The | | | | | | | | Xylophone | | The Yeti | | Zyxel | | The | | | | The | ++ 12 rows in set (0.00 sec) mysql mysql mysql mysql SELECT * FROM names ORDER BY REPLACE(name,'^The ',''); ++ | name | ++ | | | | | | | | | The Yeti | Rigth order | Xylophone | | Zyxel | | The | | The | | The | | The | | | ++ 12 rows in set (0.00 sec) Mathias Selon Sergey Spivak [EMAIL PROTECTED]: Hi this,among other answers, can be done : mysql select * from names; +--+ | name | +--+ | | | The | | | | The | | | +--+ 5 rows in set (0.02 sec) mysql select * from names order by replace(name,'The ',''); +--+ | name | +--+ | | | The | | | | | | The | +--+ 5 rows in set (0.00 sec) Hmm... Disanvantage of such way is replacing of 'The ' substring in *ANY* place of field, not just at the beginning of it. :( Look here: mysql SELECT * FROM names; ++ | name | ++ | | | The | | | | The Yeti | | The | | Xylophone | | Zyxel | ++ 7 rows in set (0.00 sec) mysql SELECT * FROM names ORDER BY REPLACE(name,'The ',''); ++ | name | ++ | | | The | | | | Xylophone | | The Yeti | --- must be earlier :) | Zyxel | | The | ++ 7 rows in set (0.00 sec) -- wbr, sergey v. spivak sergey#spivak.kiev.ua zlob-uanic/eunic/ripe -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Hope that helps :o) Mathias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Newbie mysql windows-linux prob
Hi, Am a newbie to mysql. I am trying to deploy mysql database server on a windows machine and update this database from a remote linux machine(client) periodically. The connection to mysql server in windows is failing when i run the client program at mysql_real_connect (). Am using mysql-5.0.7-beta-win32 in windows XP. However the same client(hostname alone changed) is able to communicate and create database when i run the mysql server on linux machine. Am using MySQL-devel-5.0.7-0.i386.rpm in linux. Another question: I use the following command to compile the client application. gcc connect1.c -o mycli -I /usr/include/mysql/ -L/usr/lib/mysql -lmysqlclient -lz -lcrypt -lnsl -lm Is it possible to port these libraries to ECOS RTOS so that the above client app(mycli) can run on a embedded device(IXP425 board) ? Any comments are highly appreciable. Thanks prashanth
Re: ORDER by Question
Mathias wrote: you didn't give an alternative, but i've forgotten just a '^' : mysql SELECT * FROM names ORDER BY REPLACE(name,'^The ',''); No, sorry -- that doesn't work at all; REPLACE takes a string, not a regex. Look at your example below: 'The ' should be after ''; '' should be before 'The '. And so on. ++ | name | ++ | | | | | | | | | The Yeti | Rigth order | Xylophone | | Zyxel | | The | | The | | The | | The | | | ++ Quick test: SELECT REPLACE(name,'^The ','woohoo') FROM names; -- :-) -- Hassan Schroeder - [EMAIL PROTECTED] Webtuitive Design === (+1) 408-938-0567 === http://webtuitive.com dream. code. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ORDER by Question
Right, i have all my attention on the The Yeti order, and didn't see the rest. This is the right structure including The in the middle : mysql SELECT * FROM names ORDER BY case when substring(name,1,3)='The' then REPLACE(name,'The ','') - else name end; ++ | name | ++ | | | | | The | | The | | | | | | The Yeti | | Xylophone | | Zyxel | | woohoo | | The | | The | | | ++ 13 rows in set (0.02 sec) Hope that's better Mathias Selon Hassan Schroeder [EMAIL PROTECTED]: Mathias wrote: you didn't give an alternative, but i've forgotten just a '^' : mysql SELECT * FROM names ORDER BY REPLACE(name,'^The ',''); No, sorry -- that doesn't work at all; REPLACE takes a string, not a regex. Look at your example below: 'The ' should be after ''; '' should be before 'The '. And so on. ++ | name | ++ | | | | | | | | | The Yeti | Rigth order | Xylophone | | Zyxel | | The | | The | | The | | The | | | ++ Quick test: SELECT REPLACE(name,'^The ','woohoo') FROM names; -- :-) -- Hassan Schroeder - [EMAIL PROTECTED] Webtuitive Design === (+1) 408-938-0567 === http://webtuitive.com dream. code. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Hope that helps :o) Mathias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: load balancer and cluster
thanks gary: of course this is the perfect solution, and it will be a very good open source project, but: how is it posible that the mysql team has not implemented this proxy solution all ready? i am not a lazy guy, of course if a need to implement this i will do, but i prefer a mysql's solution becouse i want the mysql's products guaranty. thanks fernando (aka d2clon) Gary Richardson wrote: What about writing a proxy server for each of your application nodes? It probably wouldn't be hard to fake the mysql protocol and proxy to an active server.. You could have a listener on each of your application nodes bound to port 3306. On 6/27/05, d2clon [EMAIL PROTECTED] wrote: thanks for the reply. but this solution its imposible because my applications are done allready, or perhaps the applications are not mine. i need a transparent balancer, and i dont anderstand how is possible thet it does not exists. grettings d2clon Gary Richardson wrote: You're probably best to implement it as a layer in your application. You could put a layer around DBI (or whatever programming API you're using) to retry the query on another node if it fails. On 6/23/05, d2clon [EMAIL PROTECTED] wrote: (sorry for my english) hello people: i want to answer if do exists any mysql solution that implements load balancer and redundancy between 'application layer' and 'mysqld layer' i will try to explain me: i want to implement a completed mysql cluster system, and the documentation is very good, but i see a problem: we need to look the picture in this page: http://dev.mysql.com/doc/mysql/en/mysql-cluster-overview.html in this picture we can see that the 'storage layer' implements correctly redundance and load balancer feature, because the 'SQL layer' ask for data to a 'storage layer' but not to a one particular node. but the applications in the 'application layer' do the petitions to the 'SQL layer' directly to a particular node, and them: what happen if a node in the 'SQL layer' is off? how i can offer a load balancer feature between 'application layer' and 'SQL layer'. i searched a lot on the web but i only founded out answers but not solutions.. i also see this thread in this mail list: http://lists.mysql.com/search.php?q=load+balancerlist=m=abegin=60 but i did not found an answer in this thread. any help? thanks a lot fernando guillen (aka d2clon) -- 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]
Enhance a query
Hi Everybody, I am using MySql 4.0.21 standard version. I have a profile table with the structure in similar manner: mysql select A0,A1,A14,A15,A19,Split from PROFILE where A1=100; +---+-+-+-+-+--+ | A0| A1 | A14 | A15 | A19 | Split| +---+-+-+-+-+--+ | 10005 | 100 | 0 | 0 | 1 | OLD | | 10006 | 100 | 0 | 0 | 1 | OLD | | 20005 | 100 | 0 | 0 | 1 | OLD | | 30005 | 100 | 0 | 0 | 1 | OLD | +---+-+-+-+-+--+ In this table, I would like to get a single row for value A1=100 and A14=0,A15=0 and A19=1. I am currently using Limit 1 in the select query. Note: My data is not based on A0 data. If the table runs into millions of rows, is it a good suggestion to use Limit. Or Is there any alternative? Thanks, Harish -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Enhance a query
Harish Gabbita [EMAIL PROTECTED] wrote on 06/27/2005 10:17:48 AM: Hi Everybody, I am using MySql 4.0.21 standard version. I have a profile table with the structure in similar manner: mysql select A0,A1,A14,A15,A19,Split from PROFILE where A1=100; +---+-+-+-+-+--+ | A0| A1 | A14 | A15 | A19 | Split| +---+-+-+-+-+--+ | 10005 | 100 | 0 | 0 | 1 | OLD | | 10006 | 100 | 0 | 0 | 1 | OLD | | 20005 | 100 | 0 | 0 | 1 | OLD | | 30005 | 100 | 0 | 0 | 1 | OLD | +---+-+-+-+-+--+ In this table, I would like to get a single row for value A1=100 and A14=0,A15=0 and A19=1. I am currently using Limit 1 in the select query. Note: My data is not based on A0 data. If the table runs into millions of rows, is it a good suggestion to use Limit. Or Is there any alternative? Thanks, Harish Two options: Don't ask for the A0 or Split columns and use DISTINCT. Wrap A0 and split columns with one of the aggregate functions and use GROUP BY. SELECT DISTINCT A1,A14,A15,A19 FROM PROFILE where A1=100; -- or -- SELECT MIN(A0),A1,A14,A15,A19,MAX(Split) FROM PROFILE where A1=100 GROUP BY A1,A14,A15,A19; -- an alternative GROUP BY declaration -- using just the columns' positions SELECT MIN(A0),A1,A14,A15,A19,MAX(Split) FROM PROFILE where A1=100 GROUP BY 2,3,4,5; Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: ORDER by Question
Mathias wrote: This is the right structure including The in the middle : mysql SELECT * FROM names ORDER BY case when substring(name,1,3)='The' then REPLACE(name,'The ','') else name end; ? all of which produces exactly the same result as: SELECT * FROM names ORDER BY TRIM(LEADING The FROM name); But I guess when simplicity just won't do... :-) -- Hassan Schroeder - [EMAIL PROTECTED] Webtuitive Design === (+1) 408-938-0567 === http://webtuitive.com dream. code. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
FULLTEXT INDEX support for ucs2
Hi, I would like to know if the support for fulltext index on ucs2-columns will be added and if so when. BR /Kenneth Lindh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ORDER by Question
Selon Hassan Schroeder [EMAIL PROTECTED]: Mathias wrote: This is the right structure including The in the middle : mysql SELECT * FROM names ORDER BY case when substring(name,1,3)='The' then REPLACE(name,'The ','') else name end; ? all of which produces exactly the same result as: SELECT * FROM names ORDER BY TRIM(LEADING The FROM name); But I guess when simplicity just won't do... :-) -- Hassan Schroeder - [EMAIL PROTECTED] Webtuitive Design === (+1) 408-938-0567 === http://webtuitive.com dream. code. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] yes, your method is simplier and i never said the opposite. We learn from each other :o) good ! Hope that helps :o) Mathias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Calculate LONG/LAT from ZIP+4
Brian Dunning [EMAIL PROTECTED] writes: Dude, that's more than 5 years old. Yup, but it's worked fine for every application I've used it for. ZIP codes don't tend to get moved around very often, and when they do it's not very far... ScottG. [...] As I said below, you can download a free database from the US Census Bureau. Specifically, the data here: http://www.census.gov/tiger/tms/gazetteer/zcta5.txt contains ZIP codes and lat/lon information. I've not used that data, but I've used this file from 1999 with good results: http://www.census.gov/geo/www/tiger/zip1999.html [...] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Calculate LONG/LAT from ZIP+4
Changes average about 10 a month, in my experience: new ZIP codes and split ZIP codes, plus quite a few that go away. So you are working with a database that's probably around 2% wrong. I've never seen a *free* source of current accurate data: the USPS charges a lot for this, so generally the only place to get it reasonably cheap is a reseller like the one I posted that does high volume. There are numerous old databases all over the web like the one you posted, but as I've found in every case, they're too wrong for commercial use. What's $15 to a real business that needs their app to give correct results. :) If anyone does know a free source of CURRENT data (updated at least monthly) please post it - just cuz I can't find one doesn't mean it's not out there! :) On Jun 27, 2005, at 8:21 AM, Scott Gifford wrote: Brian Dunning [EMAIL PROTECTED] writes: Dude, that's more than 5 years old. Yup, but it's worked fine for every application I've used it for. ZIP codes don't tend to get moved around very often, and when they do it's not very far... ScottG. [...] As I said below, you can download a free database from the US Census Bureau. Specifically, the data here: http://www.census.gov/tiger/tms/gazetteer/zcta5.txt contains ZIP codes and lat/lon information. I've not used that data, but I've used this file from 1999 with good results: http://www.census.gov/geo/www/tiger/zip1999.html [...] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Preventing duplicates with load data
At 08:11 AM 6/27/2005, you wrote: Hello, How can I prevent duplicate entries when I fill the data base with load data? I tried ignore, but that has no effect. Probably I'm something very elementary, but I'm still learning... Regards, Hans. Hans, Ignore/Replace will only work on Unique keys and I bet your key is not unique. If you make it unique, then Ignore will keep the existing value, or Replace will replace the existing row with the new row. Mike jdh dot beekhuizen at duinheks dot xs4all dot nl --- GoldED+/LNX 1.1.5/040412 * Origin: The Wizard is using MBSE/Linux (2:280/1018) -- 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: question about field length for integer
Eko Budiharto [EMAIL PROTECTED] wrote on 06/26/2005 11:02:30 AM: Hi, is there anyway that I can have more than 20 digits for integer (bigInt)? If not, what I can use for database index? BIGINT UNSIGNED can range from 0 to 18446744073709551615 (http://dev.mysql.com/doc/mysql/en/numeric-types.html) Are you actually saying that you have a database with more than 1.8e+19 records in it? I don't think you do. I think you are combining several pieces of information into something that looks like a number and it's exceeding the storage limits of even BIGINT. What you have is actually a good idea but you are physically limited by the capacity of the column types available. In this case if you cannot create all of your key values so that they look like numbers smaller than 18446744073709551615, it can't fit into a BIGINT UNSIGNED column. You do have some options: a) change the way you create your server keys so that they fit in the value allowed b) use a character-based column to store your server key values c) use some other value to identify your servers (IP address, for example) d) create a table of server keys: CREATE TABLE server ( ID int auto_increment , name varchar(25) not null , ip int unsigned , ... (any other fields you could define to describe this server) , PRIMARY KEY (ID) , UNIQUE(name) ) Then, refer to your servers using server.id instead of your composited key. e) ...? (I am sure there are more ideas from others on the list) To answer your literal question: No, MySQL cannot store integer values that contain more than 20 digits. Sorry! Shawn Green Database Administrator Unimin Corporation - Spruce Pine
RE: question about field length for integer
If you really need more than 20 digits of accuracy and can move to 5.0.3 + you can use the Decimal data type without losing precision. This is out of Chapter 23. Precision Math The maximum value of 64 for M means that calculations on DECIMAL values are accurate up to 64 digits. This limit of 64 digits of precision also applies to exact-value numeric literals, so the maximum range of such literals is different from before. (Prior to MySQL 5.0.3, decimal values could have up to 254 digits. However, calculations were done using floating-point and thus were approximate, not exact.) This change in the range of literal values is another possible source of incompatibility for older applications. Values for DECIMAL columns no longer are represented as strings that require one byte per digit or sign character. Instead, a binary format is used that packs nine decimal digits into four bytes. This change to DECIMAL storage format changes the storage requirements as well. Storage for the integer and fractional parts of each value are determined separately. Each multiple of nine digits requires four bytes, and the leftover digits require some fraction of four bytes. For example, a DECIMAL(18,9) column has nine digits on each side of the decimal point, so the integer part and the fractional part each require four bytes. A DECIMAL(20,10) column has 10 digits on each side of the decimal point. Each part requires four bytes for nine of the digits, and one byte for the remaining digit. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, June 27, 2005 10:34 AM To: Eko Budiharto Cc: mysql@lists.mysql.com Subject: Re: question about field length for integer Eko Budiharto [EMAIL PROTECTED] wrote on 06/26/2005 11:02:30 AM: Hi, is there anyway that I can have more than 20 digits for integer (bigInt)? If not, what I can use for database index? BIGINT UNSIGNED can range from 0 to 18446744073709551615 (http://dev.mysql.com/doc/mysql/en/numeric-types.html) Are you actually saying that you have a database with more than 1.8e+19 records in it? I don't think you do. I think you are combining several pieces of information into something that looks like a number and it's exceeding the storage limits of even BIGINT. What you have is actually a good idea but you are physically limited by the capacity of the column types available. In this case if you cannot create all of your key values so that they look like numbers smaller than 18446744073709551615, it can't fit into a BIGINT UNSIGNED column. You do have some options: a) change the way you create your server keys so that they fit in the value allowed b) use a character-based column to store your server key values c) use some other value to identify your servers (IP address, for example) d) create a table of server keys: CREATE TABLE server ( ID int auto_increment , name varchar(25) not null , ip int unsigned , ... (any other fields you could define to describe this server) , PRIMARY KEY (ID) , UNIQUE(name) ) Then, refer to your servers using server.id instead of your composited key. e) ...? (I am sure there are more ideas from others on the list) To answer your literal question: No, MySQL cannot store integer values that contain more than 20 digits. Sorry! Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: altering pointer size reveals unexpected result
Gleb Paharenko wrote: Hello. Your situation looks very rare. I didn't found complains in bug database and archives. If you are able to reproduce this situation the bug report would be helpful. Yeah, well - first I'd need to reproduce the 50mill records :-) This will take maybe a month, but I'll try to provoke the situation again. Hmm, or I guess I could just fill it up with random records. Let me have a look. /Per Jessen, Zürich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: question about field length for integer
[EMAIL PROTECTED] wrote on 27/06/2005 16:33:44: Are you actually saying that you have a database with more than 1.8e+19 records in it? I don't think you do. If you were to add records at the rate of a million a second, which is, I think, beyond the capabilities of any foreseeable future hardware and software, it would still take half a million years to add that number of records. It is therefore fairly easy to deduce that the OP has not got, and will not have within any of our lifetimes, a database that big. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem with a TIME calculation - Can I do this?
I have a table that has the following structure: mysql describe time_sheet; +--+---+--+-+ ++ | Field| Type | Null | Key | Default| Extra | +--+---+--+-+ ++ | tdate| date | | | 2000-01-01 || | callslip | text | | | || | customer | text | | | || | time_in | time | | | 00:00:00 || | time_out | time | | | 00:00:00 || | item_sold| text | YES | | NULL || | amount | decimal(10,2) | YES | | NULL || | citem_sold | text | YES | | NULL || | camount | decimal(10,2) | YES | | NULL || | amount_collected | decimal(10,2) | YES | | NULL || | commision| decimal(10,2) | YES | | NULL || | tsid | int(11) | | PRI | NULL | auto_increment | | dispatch_time| time | | | 00:00:00 || | ctype| text | YES | | NULL || +--+---+--+-+ ++ 14 rows in set (0.00 sec) I am trying to use the following select statement: select ctype, count(tsid), sum(amount), sum(amount_collected), sum (camount), sum(commision), subtime(time_out,dispatch_time) as time from time_sheet where tdate='2005-06-22' group by ctype; It gives me the following results: +---+-+-+--- +--++--+ | ctype | count(tsid) | sum(amount) | sum(amount_collected) | sum (camount) | sum(commision) | time | +---+-+-+--- +--++--+ | CMP | 1 |0.00 | 0.00 | 0.00 | 0.00 | 01:15:00 | | INS | 2 |0.00 | 0.00 | 0.00 | 0.00 | 03:00:00 | | PMNR | 1 |0.00 | 0.00 | 0.00 | 0.00 | 01:30:00 | | SC| 1 |0.00 | 0.00 | 0.00 | 0.00 | 01:45:00 | +---+-+-+--- +--++--+ 4 rows in set (0.05 sec) The Time column at the end should not just be for 1 entry - it needs to show the time_out minus the dispatched time for the group. In other words if in the INS calltype I have 2 calls that: Dispatch TimeTime_out 12:00 14:00 14:00 15:00 My total Time Column should read 3:00 What I would like to use is: select ctype, count(tsid), sum(amount), sum(amount_collected), sum (camount), sum(commision), sum(subtime(time_out,dispatch_time)) as time from time_sheet where tdate='2005-06-22' group by ctype; NOTE: This adds a SUM() to the time column. Is this do-able - (doesn't work this way - maybe in another manner? Thanks. -Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Document Library
I am currently projecting a project where we can use MySQL to control documents similiar to ISBN for IntraNet. We plan to export into XML as well. It will require a good labelling, strategy and planning. Do anyone know where I can find good resource to get started? Thanks, Scott -- Power to people, Linux is here.
Re: Problem with a TIME calculation - Can I do this?
Christopher Molnar [EMAIL PROTECTED] wrote on 06/27/2005 12:55:08 PM: I have a table that has the following structure: mysql describe time_sheet; +--+---+--+-+ ++ | Field| Type | Null | Key | Default| Extra | +--+---+--+-+ ++ | tdate| date | | | 2000-01-01 || | callslip | text | | | || | customer | text | | | || | time_in | time | | | 00:00:00 || | time_out | time | | | 00:00:00 || | item_sold| text | YES | | NULL || | amount | decimal(10,2) | YES | | NULL || | citem_sold | text | YES | | NULL || | camount | decimal(10,2) | YES | | NULL || | amount_collected | decimal(10,2) | YES | | NULL || | commision| decimal(10,2) | YES | | NULL || | tsid | int(11) | | PRI | NULL | auto_increment | | dispatch_time| time | | | 00:00:00 || | ctype| text | YES | | NULL || +--+---+--+-+ ++ 14 rows in set (0.00 sec) I am trying to use the following select statement: select ctype, count(tsid), sum(amount), sum(amount_collected), sum (camount), sum(commision), subtime(time_out,dispatch_time) as time from time_sheet where tdate='2005-06-22' group by ctype; It gives me the following results: +---+-+-+--- +--++--+ | ctype | count(tsid) | sum(amount) | sum(amount_collected) | sum (camount) | sum(commision) | time | +---+-+-+--- +--++--+ | CMP | 1 |0.00 | 0.00 | 0.00 | 0.00 | 01:15:00 | | INS | 2 |0.00 | 0.00 | 0.00 | 0.00 | 03:00:00 | | PMNR | 1 |0.00 | 0.00 | 0.00 | 0.00 | 01:30:00 | | SC| 1 |0.00 | 0.00 | 0.00 | 0.00 | 01:45:00 | +---+-+-+--- +--++--+ 4 rows in set (0.05 sec) The Time column at the end should not just be for 1 entry - it needs to show the time_out minus the dispatched time for the group. In other words if in the INS calltype I have 2 calls that: Dispatch TimeTime_out 12:00 14:00 14:00 15:00 My total Time Column should read 3:00 Isn't that exactly what you posted? | INS | 2 |0.00 | 0.00 | 0.00 | 0.00 | 03:00:00 | That last column represents 3 hours, does it not? You have me confused. What I would like to use is: select ctype, count(tsid), sum(amount), sum(amount_collected), sum (camount), sum(commision), sum(subtime(time_out,dispatch_time)) as time from time_sheet where tdate='2005-06-22' group by ctype; NOTE: This adds a SUM() to the time column. Is this do-able - (doesn't work this way - maybe in another manner? That would have worked if TIME values weren't converted to numbers by packing their components together. (This is one of my very few peeves with MySQL. Packing works well for sorting but not at all for date math). To get good date math working, you need to use the FROM_UNIXTIME() and UNIX_TIMESTAMP() functions to convert your time differences into and out of actual numeric values (ones that are SUM()-able). http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html You could also use time_to_sec() and sec_to_time() to do the same down-conversion/restoration. (Any pair of functions that convert the a time into some kind of numeric value and back will work but these come to mind first.) Thanks. -Chris Try this: SELECT ctype , count(tsid) , sum(amount) , sum(amount_collected) , sum(camount) , sum(commision) , FROM_UNIXTIME(sum(UNIX_TIMESTAMP(subtime(time_out,dispatch_time as time FROM time_sheet WHERE tdate='2005-06-22' GROUP BY ctype; -- or -- SELECT ctype , count(tsid) , sum(amount) , sum(amount_collected) , sum(camount) , sum(commision) , SEC_TO_TIME(sum(TIME_TO_SEC(subtime(time_out,dispatch_time as time FROM time_sheet WHERE tdate='2005-06-22' GROUP BY ctype; Shawn Green Database
Re: Problem with a TIME calculation - Can I do this?
[EMAIL PROTECTED] wrote:
Christopher Molnar [EMAIL PROTECTED] wrote on 06/27/2005
12:55:08 PM:
snip
What I would like to use is:
select ctype, count(tsid), sum(amount), sum(amount_collected), sum
(camount), sum(commision), sum(subtime(time_out,dispatch_time)) as
time from time_sheet where tdate='2005-06-22' group by ctype;
NOTE: This adds a SUM() to the time column.
Is this do-able - (doesn't work this way - maybe in another manner?
That would have worked if TIME values weren't converted to numbers by
packing their components together. (This is one of my very few peeves with
MySQL. Packing works well for sorting but not at all for date math). To
What else would you have it do? The problem, I think, is the need to do date
math, not the storage method.
get good date math working, you need to use the FROM_UNIXTIME() and
UNIX_TIMESTAMP() functions to convert your time differences into and out
of actual numeric values (ones that are SUM()-able).
That won't work very well. FROM_UNIXTIME AND UNIX_TIMESTAMP work with DATEs.
mysql SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(SUBTIME('14:00','12:00')));
+-+
| FROM_UNIXTIME(UNIX_TIMESTAMP(SUBTIME('14:00','12:00'))) |
+-+
| 1970-01-01 00:00:00 |
+-+
1 row in set, 1 warning (0.00 sec)
http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html
You could also use time_to_sec() and sec_to_time() to do the same
down-conversion/restoration. (Any pair of functions that convert the a
time into some kind of numeric value and back will work but these come to
mind first.)
Yes, this is the way to go.
mysql SELECT SEC_TO_TIME(TIME_TO_SEC(SUBTIME('14:00','12:00')));
++
| SEC_TO_TIME(TIME_TO_SEC(SUBTIME('14:00','12:00'))) |
++
| 02:00:00 |
++
1 row in set (0.00 sec)
Thanks.
-Chris
Try this:
SELECT ctype
, count(tsid)
, sum(amount)
, sum(amount_collected)
, sum(camount)
, sum(commision)
,
FROM_UNIXTIME(sum(UNIX_TIMESTAMP(subtime(time_out,dispatch_time as
time
FROM time_sheet
WHERE tdate='2005-06-22'
GROUP BY ctype;
Not this.
-- or --
SELECT ctype
, count(tsid)
, sum(amount)
, sum(amount_collected)
, sum(camount)
, sum(commision)
, SEC_TO_TIME(sum(TIME_TO_SEC(subtime(time_out,dispatch_time
as time
FROM time_sheet
WHERE tdate='2005-06-22'
GROUP BY ctype;
Yes, this.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: could a Trigger execute a outside command?
Thanks. This info. is very useful to me. I don't know if it is possible under Mac OS. But I assume it is, consider it is basically a UNIX. I just want to trigger a shell script, say, a perl script. Could a shell script be called from a C program? It looks like I will need to go through a lot of stuff to do this. ted On Jun 24, 2005, at 2:42 PM, [EMAIL PROTECTED] wrote: Isn't this what a User Defined Function would be used for? Your trigger would call the UDF which would do a system(). Or is this not possible under Mac OS? -Lee Gleb Paharenko [EMAIL PROTECTED] I don't know any SQL statements which could launch extern commands, at least in MySQL. So in triggers it is impossible as well. Ted Zeng [EMAIL PROTECTED] wrote: Hi, I am using MySql on Mac OS X. I am wondering if I could execute a command (shell, perl) by a Trigger. I read the manual and it seems this is impossible. ted -- 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: question about field length for integer
Eko Budiharto [EMAIL PROTECTED] wrote on 06/27/2005 12:28:27 PM: Are you actually saying that you have a database with more than 1.8e+19 records in it? I don't think you do. I think you are combining several pieces of information into something that looks like a number and it's exceeding the storage limits of even BIGINT. What you have is actually a good idea but you are physically limited by the capacity of the column types available. In this case if you cannot create all of your key values so that they look like numbers smaller than 18446744073709551615, it can't fit into a BIGINT UNSIGNED column. What I have in here, I have a table that use integer(BIGINT) for index/ID and auto increment. CREATE TABLE server ( ID bigint(20) auto_increment , name varchar(25) not null , ip int unsigned , ... (any other fields you could define to describe this server) , PRIMARY KEY (ID) , UNIQUE(name) ) I use BIGINT because it has the capability auto_increment for index of records instead of using character. What I have in here, I am accessing my DB with ID, name. Any integer column can auto_increment. Just be careful you don't add enough records to exceed the storage limit of your auto_increment column. For example if you use a TINYINT column to store your ID value, you could add at most 127 new records before your auto_increment would start failing. I recommend INT as a storage type as it is not very often that you wind up with 2147483647 records in a table and it only uses half as many bytes (4) to store the value as BIGINT(8). If you had 200 rows and you used a BIGINT as your ID value, the space needed to store the data from just that one column would be 8*200 = 1600 bytes but if you had used a regular INT as your ID column it would have only used 4*200 = 800 bytes (about 8MB less disk space) That is a lot less data you need to move into memory whenever you need the PK index for that table. That's also a lot less data to read through when you search an index (Data bus speeds are fast but not infinitely so. It still takes some time to read a page of memory.). By the way, it is appropriate to respond to the list. Just in case I wasn't available to respond to you, someone else could answer your questions. It also gives the list a chance to learn from any answers you get. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Problem with a TIME calculation - Can I do this?
Thanks! Worked perfectly. And also thanks to the other people who responded. Being unfamiliar with how MySQL seems to handle time your responses really helped! -Chris On Jun 27, 2005, at 1:16 PM, [EMAIL PROTECTED] wrote: That would have worked if TIME values weren't converted to numbers by packing their components together. (This is one of my very few peeves with MySQL. Packing works well for sorting but not at all for date math). To get good date math working, you need to use the FROM_UNIXTIME() and UNIX_TIMESTAMP() functions to convert your time differences into and out of actual numeric values (ones that are SUM ()-able). http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html You could also use time_to_sec() and sec_to_time() to do the same down-conversion/restoration. (Any pair of functions that convert the a time into some kind of numeric value and back will work but these come to mind first.) Try this: SELECT ctype , count(tsid) , sum(amount) , sum(amount_collected) , sum(camount) , sum(commision) , FROM_UNIXTIME(sum(UNIX_TIMESTAMP(subtime (time_out,dispatch_time as time FROM time_sheet WHERE tdate='2005-06-22' GROUP BY ctype; -- or -- SELECT ctype , count(tsid) , sum(amount) , sum(amount_collected) , sum(camount) , sum(commision) , SEC_TO_TIME(sum(TIME_TO_SEC(subtime (time_out,dispatch_time as time FROM time_sheet WHERE tdate='2005-06-22' GROUP BY ctype; Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: could a Trigger execute a outside command?
On Jun 27, 2005, at 1:32 PM, Ted Zeng wrote: I don't know if it is possible under Mac OS. But I assume it is, consider it is basically a UNIX. I just want to trigger a shell script, say, a perl script. Could a shell script be called from a C program? It looks like I will need to go through a lot of stuff to do this. Just remember that this shell script will be called for every record involved in the database action. If up do an update on the table that affects 1000 rows, the database will go through the process of calling the UDF and associated system calls for each of the 1000 rows. This will degrade database performance. What it is you are trying to accomplish with the shell script? Perhaps it could be pulled into the UDF, or into some other mechanism. On Jun 24, 2005, at 2:42 PM, [EMAIL PROTECTED] wrote: Isn't this what a User Defined Function would be used for? Your trigger would call the UDF which would do a system(). Or is this not possible under Mac OS? -Lee Gleb Paharenko [EMAIL PROTECTED] I don't know any SQL statements which could launch extern commands, at least in MySQL. So in triggers it is impossible as well. Ted Zeng [EMAIL PROTECTED] wrote: Hi, I am using MySql on Mac OS X. I am wondering if I could execute a command (shell, perl) by a Trigger. I read the manual and it seems this is impossible. ted Mike Kruckenberg [EMAIL PROTECTED] ProMySQL Author http://promysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: could a Trigger execute a outside command?
When a new record is added to a specific table, I want to react to it right away, running a perl script to do something. Right now, I use a cronjob that checks the table every minuet. It is slow(it could waits up to a whole min.) and not efficient. To my understanding, I would need to put a trigger on this table. When the trigger is triggered, it will call the same perl script through UDF. Not sure whether I get it right. ted On Jun 27, 2005, at 12:19 PM, Michael Kruckenberg wrote: On Jun 27, 2005, at 1:32 PM, Ted Zeng wrote: I don't know if it is possible under Mac OS. But I assume it is, consider it is basically a UNIX. I just want to trigger a shell script, say, a perl script. Could a shell script be called from a C program? It looks like I will need to go through a lot of stuff to do this. Just remember that this shell script will be called for every record involved in the database action. If up do an update on the table that affects 1000 rows, the database will go through the process of calling the UDF and associated system calls for each of the 1000 rows. This will degrade database performance. What it is you are trying to accomplish with the shell script? Perhaps it could be pulled into the UDF, or into some other mechanism. On Jun 24, 2005, at 2:42 PM, [EMAIL PROTECTED] wrote: Isn't this what a User Defined Function would be used for? Your trigger would call the UDF which would do a system(). Or is this not possible under Mac OS? -Lee Gleb Paharenko [EMAIL PROTECTED] I don't know any SQL statements which could launch extern commands, at least in MySQL. So in triggers it is impossible as well. Ted Zeng [EMAIL PROTECTED] wrote: Hi, I am using MySql on Mac OS X. I am wondering if I could execute a command (shell, perl) by a Trigger. I read the manual and it seems this is impossible. ted Mike Kruckenberg [EMAIL PROTECTED] ProMySQL Author http://promysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: could a Trigger execute a outside command?
Hi Ted,
When a new record is added to a specific table, I want to
react to it right away, running a perl script to do something.
Right now, I use a cronjob that checks the table every minuet.
It is slow(it could waits up to a whole min.) and not efficient.
To my understanding, I would need to put a trigger on this table. When
the trigger
is triggered, it will call the same perl script through UDF. Not sure
whether I get it right.
Seems like a better approach might be:
* Build a UDF that sends a SIGALRM to your Perl script.
- You can test this independently by calling it with SELECT.
- This will mean getting the PID somehow.
- Likely, the Perl script will need to write its PID somewhere.
- You'll read the PID in your UDF and call kill(pid, SIGALRM)
* Write your Perl script so that it looks like so:
while(1) {
do_work();
check if the while should be broken
sleep 60;
}
* Upon receiving the SIGALRM, the Perl script will be woken up early and
get to its work immediately. If something fails for any reason, you go
back to the old behaviour of checking every 60 seconds.
Regards,
Jeremy
--
Jeremy Cole
Technical Yahoo - MySQL (Database) Geek
Desk: 408 349 5104
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Why would a UNION be 100x slower than the same SELECT...
Here's a big problem I'm having. If I have a query like: SELECT * FROM FOO WHERE FOO.LAST_UPDATED 1119898418779 AND FOO.FEED_ID = 1 ORDER BY FOO.LAST_UPDATED DESC LIMIT 10 it only takes about 10ms or so to execute. but... if I rewrite it to wrap it in a union like so: ( SELECT * FROM FOO WHERE FOO.LAST_UPDATED 1119898418779 AND FOO.FEED_ID = 1 ORDER BY FOO.LAST_UPDATED DESC LIMIT 10 ) ORDER BY LAST_UPDATED DESC LIMIT 10 then its 100x slower and takes about 1000ms No tmp disk tables were created (or at least thats what show status is telling me). Any idea whats going on and how I could fix this? Kevin -- Use Rojo (RSS/Atom aggregator)! - visit http://rojo.com. See irc.freenode.net #rojo if you want to chat. Rojo is Hiring! - http://www.rojonetworks.com/JobsAtRojo.html Kevin A. Burton, Location - San Francisco, CA AIM/YIM - sfburtonator, Web - http://peerfear.org/ GPG fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04 99F1 4412 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why would a UNION be 100x slower than the same SELECT...
Kevin Burton wrote: ( SELECT * FROM FOO WHERE FOO.LAST_UPDATED 1119898418779 AND FOO.FEED_ID = 1 ORDER BY FOO.LAST_UPDATED DESC LIMIT 10 ) ORDER BY LAST_UPDATED DESC LIMIT 10 OK. I *totally* just figured it out! WOW. so.. the LIMIT in the first SELECT is *totally* ignored and the entire SQL expression is evaluated which is then given to the union. Evil I say! Pure evil! I was able to figure this out because Handler_read_next was being incremented to the same value as the total number of rows in this expression. Cool now at least I know why its screwing up. Kevin -- Use Rojo (RSS/Atom aggregator)! - visit http://rojo.com. See irc.freenode.net #rojo if you want to chat. Rojo is Hiring! - http://www.rojonetworks.com/JobsAtRojo.html Kevin A. Burton, Location - San Francisco, CA AIM/YIM - sfburtonator, Web - http://peerfear.org/ GPG fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04 99F1 4412 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why would a UNION be 100x slower than the same SELECT...
Why do you think you're using a UNION in your query? The keyword UNION doesn't appear anywhere in your query. You don't even have a second query being UNIONed to the first. All you've got is a pair of parentheses surrounding your original query, which seems to perform okay. For what it's worth, I don't see why a pair of parentheses would change the performance but calling it a UNION just confuses the issue, in my view. Rhino - Original Message - From: Kevin Burton [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, June 27, 2005 4:17 PM Subject: Why would a UNION be 100x slower than the same SELECT... Here's a big problem I'm having. If I have a query like: SELECT * FROM FOO WHERE FOO.LAST_UPDATED 1119898418779 AND FOO.FEED_ID = 1 ORDER BY FOO.LAST_UPDATED DESC LIMIT 10 it only takes about 10ms or so to execute. but... if I rewrite it to wrap it in a union like so: ( SELECT * FROM FOO WHERE FOO.LAST_UPDATED 1119898418779 AND FOO.FEED_ID = 1 ORDER BY FOO.LAST_UPDATED DESC LIMIT 10 ) ORDER BY LAST_UPDATED DESC LIMIT 10 then its 100x slower and takes about 1000ms No tmp disk tables were created (or at least thats what show status is telling me). Any idea whats going on and how I could fix this? Kevin -- Use Rojo (RSS/Atom aggregator)! - visit http://rojo.com. See irc.freenode.net #rojo if you want to chat. Rojo is Hiring! - http://www.rojonetworks.com/JobsAtRojo.html Kevin A. Burton, Location - San Francisco, CA AIM/YIM - sfburtonator, Web - http://peerfear.org/ GPG fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04 99F1 4412 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.323 / Virus Database: 267.8.2/29 - Release Date: 27/06/2005 -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.323 / Virus Database: 267.8.2/29 - Release Date: 27/06/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: could a Trigger execute a outside command?
Jeremy,
Thanks. I used Perl's cron module to execute my script. So I assume
only the cron mdoule is active all the time, but not my perl script.
Your approach looks interesting to me. I will think about it.
ted zeng
On Jun 27, 2005, at 1:14 PM, Jeremy Cole wrote:
Hi Ted,
When a new record is added to a specific table, I want to
react to it right away, running a perl script to do something.
Right now, I use a cronjob that checks the table every minuet.
It is slow(it could waits up to a whole min.) and not efficient.
To my understanding, I would need to put a trigger on this table.
When the trigger
is triggered, it will call the same perl script through UDF. Not sure
whether I get it right.
Seems like a better approach might be:
* Build a UDF that sends a SIGALRM to your Perl script.
- You can test this independently by calling it with SELECT.
- This will mean getting the PID somehow.
- Likely, the Perl script will need to write its PID somewhere.
- You'll read the PID in your UDF and call kill(pid, SIGALRM)
* Write your Perl script so that it looks like so:
while(1) {
do_work();
check if the while should be broken
sleep 60;
}
* Upon receiving the SIGALRM, the Perl script will be woken up early
and get to its work immediately. If something fails for any reason,
you go back to the old behaviour of checking every 60 seconds.
Regards,
Jeremy
--
Jeremy Cole
Technical Yahoo - MySQL (Database) Geek
Desk: 408 349 5104
--
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: Why would a UNION be 100x slower than the same SELECT...
Kevin Burton wrote: Any idea whats going on and how I could fix this? This seems like a bug in the SQL parser. The LIMIT is only ignored in this one situation. If I just add a: UNION (SELECT * FROM FOO LIMIT 0) To the query will work correctly. This might be an acceptable workaround Kevin -- Use Rojo (RSS/Atom aggregator)! - visit http://rojo.com. See irc.freenode.net #rojo if you want to chat. Rojo is Hiring! - http://www.rojonetworks.com/JobsAtRojo.html Kevin A. Burton, Location - San Francisco, CA AIM/YIM - sfburtonator, Web - http://peerfear.org/ GPG fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04 99F1 4412 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
what WHERE how... i am confused: extra instances of foreign keys are being problematic
I am having trouble with this small bit of sql I am using for a homepage. I need to select information on the next two events from two separate tables; tblevents (which holds event related info), and tbleventdate (which holds info related to each date, including times and information) herein lies the problem: By using two separate tables (something that is required) I need to use a foreign key in tbleventdate. This foreign key is the key of tblevents, so when multiple dates occur for the same event (the reason for two tables), this foreign key is duplicated. When an event occurs on consecutive days, it will appear twice, being the only event seen (LIMIT 2). What I want is the next event which does not have the same eventid. And, just to clarify, it will not work if I say WHERE… =varDate, tblevents.eventid != tbleventdate.eventid (as this would return nothing) SELECT tblevents.eventid, tblevents.eventdescr, tblevents.eventname, tbleventdate.eventdate, tbleventdate.timeinfo, tbleventdate.eventid, tbleventdate.dateid, left(tblevents.eventdescr, 150) FROM tbleventdate, tblevents WHERE tblevents.eventid = tbleventdate.eventid AND tbleventdate.eventdate = varDate ORDER BY tbleventdate.eventdate DESC LIMIT 2 ***Note: varDate is defined as ?php date(Y-m-d) ? *** which returns -MM-DD Thank you in advance -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.323 / Virus Database: 267.8.2/29 - Release Date: 6/27/2005
FW: what WHERE how... i am confused: extra instances of foreign keys are being problematic
I am having trouble with this small bit of sql I am using for a homepage. I need to select information on the next two events from two separate tables; tblevents (which holds event related info), and tbleventdate (which holds info related to each date, including times and information) herein lies the problem: By using two separate tables (something that is required) I need to use a foreign key in tbleventdate. This foreign key is the key of tblevents, so when multiple dates occur for the same event (the reason for two tables), this foreign key is duplicated. When an event occurs on consecutive days, it will appear twice, being the only event seen (LIMIT 2). What I want is the next event which does not have the same eventid. And, just to clarify, it will not work if I say WHERE… =varDate, tblevents.eventid != tbleventdate.eventid (as this would return nothing) SELECT tblevents.eventid, tblevents.eventdescr, tblevents.eventname, tbleventdate.eventdate, tbleventdate.timeinfo, tbleventdate.eventid, tbleventdate.dateid, left(tblevents.eventdescr, 150) FROM tbleventdate, tblevents WHERE tblevents.eventid = tbleventdate.eventid AND tbleventdate.eventdate = varDate ORDER BY tbleventdate.eventdate DESC LIMIT 2 ***Note: varDate is defined as ?php date(Y-m-d) ? *** which returns -MM-DD Thank you in advance -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.323 / Virus Database: 267.8.2/29 - Release Date: 6/27/2005 -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.323 / Virus Database: 267.8.2/29 - Release Date: 6/27/2005
looking for a pure startup opportunity..
hi... i'm out here in california, the bay area, and was wondering if anyone on the list is interested in either being partners, forming a sweat equity startup? or if anyone has an idea/concept, and you're looking for people to be part of your team... i've read way too many articles about the 3 kids/guys/etc... who managed to get $10 million in funding for esentially a basic idea, but they had/have traffic/eyeballs!!! yeah, i recognize that the 'best' dream would be to have a wealthy benefactor, but that's not going to happen for the vast majority of people/ideas! i'm of the firm opinion that the right group, willing to sweat/work together, can build a pretty good business. this approach requires the parties to work on it part-time, until it generates revenues, and sustains itself. so, if you're looking at your shrinking retirement going to iraq, and you want something more out of life, let's talk!! but with the right combination of web development skills (perl/php/c/apache/linux/mysql/html/css) we can do some damage. there are numerous opportunities for the right 3-5 person team! regards, bruce [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Dynamic insertion of date for LOAD INFILE
Im trying to get dynamically insert the current date into a LOAD FILE statement for some ETL automation, but Im having difficulty passing the string into the LOAD statement: This will not work. SELECT @Today:=CURDATE(); LOAD DATA LOCAL INFILE '/s3/data/[EMAIL PROTECTED]' INTO TABLE Data; Anyone ever try something similar? How can this be done? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: looking for a pure startup opportunity..
if that was the case it would easy to be a millionaire.. spam or scam... it's all the same.. bruce wrote: hi... i'm out here in california, the bay area, and was wondering if anyone on the list is interested in either being partners, forming a sweat equity startup? or if anyone has an idea/concept, and you're looking for people to be part of your team... i've read way too many articles about the 3 kids/guys/etc... who managed to get $10 million in funding for esentially a basic idea, but they had/have traffic/eyeballs!!! yeah, i recognize that the 'best' dream would be to have a wealthy benefactor, but that's not going to happen for the vast majority of people/ideas! i'm of the firm opinion that the right group, willing to sweat/work together, can build a pretty good business. this approach requires the parties to work on it part-time, until it generates revenues, and sustains itself. so, if you're looking at your shrinking retirement going to iraq, and you want something more out of life, let's talk!! but with the right combination of web development skills (perl/php/c/apache/linux/mysql/html/css) we can do some damage. there are numerous opportunities for the right 3-5 person team! regards, bruce [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
