UTF8 conversion
Having done my upgrade from version 3 to 5, I'm looking forward to the benefits of language support beyond the confines of Western Europe. However, it seems I need to convert the database I have now. We have material in the database at the moment in English, Dutch, Italian, Portuguese and Spanish. All the pages and PHP scripts are in UTF8, and I've been using the PHP function utf8_decode() before recording strings in the db because MySQL 3 doesn't support UFT8. Similarly I've been using utf8_encode() after reading them and outputting them to the web. It's become clear (from removing utf8_encode() from a script that reads the data) that simply setting the table's collation to utf8_general_ci isn't enough, and that the data itself is not utf8 encoded. I've looked in the docs but can't find a procedure to follow to convert my existing data to utf8. Can anyone point me to the right page? Or is the only way simply to knock up a utility script in php to read it all out, encode it, then write it back? Easy enough to do, but a bit of a pain! -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ I used to think I was indecisive, but now I'm not so sure. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Doh! Ignore last post...
I've just found convert()... -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ I used to think I was indecisive, but now I'm not so sure. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump and foreign keys
Nico Rittner wrote: hello, does anybody now how mysqldump handles the actions for 'on delete' and 'on update' ? In my Version 4.1.14 the action clauses are missing. When reimporting the dump, how does mysql know about the 'action' when altering the table with forein keys. Thanks, Nico Hi Nico, are you using the InnoDB storage engine for your tables? If not, the clauses will not be included in the dump, as only InnoDB supports foreign keys. When you create a non-InnoDB table which has a foreign key specification, MySQL just ignores the specification. Cheers Frank -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
problem with coalesce function
When I use coalesce function I receive this error: FUNCTION sirius.coalesce does not exist. sirius is the name of db. Can someone help me with this error Thanks, Pedro ___ O SAPO já está livre de vírus com a Panda Software, fique você também! Clique em: http://antivirus.sapo.pt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: problem with coalesce function
When I use coalesce function I receive this error: FUNCTION sirius.coalesce does not exist. sirius is the name of db. Can someone help me with this error Sure: use functions that exist. Now, if you want a better answer, try asking a better question. Showing us the code, for example, would help. What about your MySQL version, platform? Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: problem with coalesce function
Hi Pedro, What version of MySQL are you running? From the manual COALESCE(value,...) Returns the first non-NULL value in the list, or NULL if there are no non-NULL values. mysql SELECT COALESCE(NULL,1); - 1 mysql SELECT COALESCE(NULL,NULL,NULL); - NULL COALESCE() was added in MySQL 3.23.3. -- Are you running at least that? Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, 24 April 2006 7:47 PM To: mysql@lists.mysql.com Subject: problem with coalesce function When I use coalesce function I receive this error: FUNCTION sirius.coalesce does not exist. sirius is the name of db. Can someone help me with this error Thanks, Pedro ___ O SAPO já está livre de vírus com a Panda Software, fique você também! Clique em: http://antivirus.sapo.pt -- 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: problem with coalesce function
MySQL version is 5.0.19. Runs in Win. XP pack 2. I put sql in command line and i
try using jdbc.
The code:
SELECT mfn,COALESCE (user_id,0)
FROM hits WHERE mfn =1;
Citando Logan, David (SST - Adelaide) [EMAIL PROTECTED]:
Hi Pedro,
What version of MySQL are you running? From the manual
COALESCE(value,...)
Returns the first non-NULL value in the list, or NULL if there are no
non-NULL values.
mysql SELECT COALESCE(NULL,1);
- 1
mysql SELECT COALESCE(NULL,NULL,NULL);
- NULL
COALESCE() was added in MySQL 3.23.3. --
Are you running at least that?
Regards
---
** _/ ** David Logan
*** _/ *** ITO Delivery Specialist - Database
*_/* Hewlett-Packard Australia Ltd
_/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED]
_/ _/ _/ _/ Desk: +618 8408 4273
_/ _/ _/_/_/ Mobile: 0417 268 665
*_/ **
** _/ Postal: 148 Frome Street,
_/ ** Adelaide SA 5001
Australia
invent
---
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Monday, 24 April 2006 7:47 PM
To: mysql@lists.mysql.com
Subject: problem with coalesce function
When I use coalesce function I receive this error:
FUNCTION sirius.coalesce does not exist. sirius is the name of db. Can
someone
help me with this error
Thanks,
Pedro
___
O SAPO já está livre de vírus com a Panda Software, fique você também!
Clique em: http://antivirus.sapo.pt
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
___
O SAPO já está livre de vírus com a Panda Software, fique você também!
Clique em: http://antivirus.sapo.pt
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: problem with coalesce function
Hello Pedro, MySQL version is 5.0.19. Runs in Win. XP pack 2. I put sql in command line and i try using jdbc. The code: SELECT mfn,COALESCE (user_id,0) FROM hits WHERE mfn =1; Although it sounds silly, remove the space between COALESCE and the ( That should do the trick. Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle MS SQL Server Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fulltext 3 letter words
I think you are using .ini files present in the source directory . Can you copy the .ini file to your datadir ( eg : my-innodb-heavy-4G.ini conf file for 4GB RAM ) and pt out the same at startup ? Not sure whether iam in the right path .. Correct me if iam wrong --Praj On Mon, 24 Apr 2006 13:59:27 +1000 Taco Fleur [EMAIL PROTECTED] wrote: Hi Prasad, I have no my.cfn, I have a my.ini which is located at in the same directory as the my-innodb-heavy-4G.ini C:\Program Files\MySQL\MySQL Server 5.0 Kind regards, Taco Fleur (Mobile 0421 851 786) Commerce Engine Pty Ltd - Australia's leading online Payment Gateway . Local Call 1300 859 179 Postal Address: PO Box 15118, City East Brisbane, Queensland, 4002, Australia Head office: 31 Valencia Court, Eatons Hill, Queensland, 4037, Australia Telephone: +61 (0) 7 3857 3881 Fax: +61 (0) 7 3414 6464 Internet: http://www.commerceengine.com.au * Accepting payments online is easy * Developer API, XML, POST * Low fees * Full integration by a one-stop-shop The information contained in this email may be confidential. You should only disclose, re-transmit, copy, distribute, act in reliance on or commercialise the information if you are authorised to do so. Any views expressed in this email communication are those of the individual sender, except where the sender specifically states them to be the views of Commerce Engine Pty Ltd. Any advice contained in this e-mail has been prepared without taking into account your objectives, financial situation or needs. Before acting on any advice in this e-mail, Commerce Engine recommends that you consider whether it is appropriate for your circumstances. Commerce Engine does not represent, warrant or guarantee that the integrity of this communication has been maintained nor that the communication is free of errors, virus or interference. Commerce Engine Pty Ltd ACN 118 850 552 -Original Message- From: Prasad [mailto:[EMAIL PROTECTED] Sent: Monday, 24 April 2006 1:51 PM To: [EMAIL PROTECTED] Subject: Re: Fulltext 3 letter words Hi Taco Fleur., Where is your current my.cnf file present? have it in c:\my.cnf or you have to mention where your file present in the startup with --defaults-file=path/my.cnf.Note even without cnf file also mysql can be started. -Prasad. Sify. - Original Message - From: Taco Fleur [EMAIL PROTECTED] To: 'Prasad' [EMAIL PROTECTED] Sent: Monday, April 24, 2006 9:10 AM Subject: RE: Fulltext 3 letter words Sorry I am on OS Windows 2003 Kind regards, Taco Fleur (Mobile 0421 851 786) Commerce Engine Pty Ltd - Australia's leading online Payment Gateway . Local Call 1300 859 179 Postal Address: PO Box 15118, City East Brisbane, Queensland, 4002, Australia Head office: 31 Valencia Court, Eatons Hill, Queensland, 4037, Australia Telephone: +61 (0) 7 3857 3881 Fax: +61 (0) 7 3414 6464 Internet: http://www.commerceengine.com.au * Accepting payments online is easy * Developer API, XML, POST * Low fees * Full integration by a one-stop-shop The information contained in this email may be confidential. You should only disclose, re-transmit, copy, distribute, act in reliance on or commercialise the information if you are authorised to do so. Any views expressed in this email communication are those of the individual sender, except where the sender specifically states them to be the views of Commerce Engine Pty Ltd. Any advice contained in this e-mail has been prepared without taking into account your objectives, financial situation or needs. Before acting on any advice in this e-mail, Commerce Engine recommends that you consider whether it is appropriate for your circumstances. Commerce Engine does not represent, warrant or guarantee that the integrity of this communication has been maintained nor that the communication is free of errors, virus or interference. Commerce Engine Pty Ltd ACN 118 850 552 -Original Message- From: Prasad [mailto:[EMAIL PROTECTED] Sent: Monday, 24 April 2006 1:32 PM To: [EMAIL PROTECTED] Subject: Re: Fulltext 3 letter words Hi., Are you using unix flavor? If so do you have a file my.cnf under /etc. If not so copy the file from /mysqlhomepath/support-file/my-huge.cnf to /etc/my.cnf Modify the changes in /etc/my.cnf under [mysqld] Innodb tables dosent support fulltext index type. You can gothrough the below link where you can find full-text search functions. http://dev.mysql.com/doc/refman/4.1/en/fulltext-search.html -Prasad. Sify. - Original Message - From: Taco Fleur [EMAIL PROTECTED] To: 'John Hicks' [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Monday, April 24, 2006 8:22 AM Subject: RE: Fulltext 3 letter words In my-innodb-heavy-4G.ini I
Re: Fulltext 3 letter words
You can find out whether the server has picked up the changes from your configuration file by running the following SQL: show variables like 'ft_min_word_len'; As per the instructions in the manual http://dev.mysql.com/doc/refman/5.0/en/fulltext-fine-tuning.html you must rebuild the indexes on any tables with a full-text index. Also, note the warning about using myisamchk further down the manual page. HTH James Harvard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problems with Mysql 5 and Visual Basic 5
I migrated to mysql 5 but the applications with Visual Basic 5 return error in data. We have installed ODBC 3.51 -- Saludos cordiales. Ing. Gabriel Mahiques Dto. Control de Gestión ELECTROINGENIERA S.A. Telefono: 474 1414 Uspallata 1461. Barrio San Martín. CP: X5008HSH Córdoba. República Argentina. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: If exists query.
On 4/23/06, John Hicks [EMAIL PROTECTED] wrote:
Paul Halliday wrote:
I am doing queries that produce a table that looks something like this:
Count | IP Address| First Seen| Last Seen | Days
5000 10.0.0.1 2005-12-102006-04-15 50*
6500 10.0.0.2 2006-04-012006-04-06 3**
*So of the 5000 events count(*) between 2005-12-10 and 2006-04-14
the count was distributed within 50 distinct days.
**This time the count is still high and it occured within 3 days
between 2006-04-01 and 2006-04-06.
I just cant figure out how to come up with the days part :).
Well, I guess that is a *bit* more specific, but not much better :)
So I'm guessing that the source of this data is perhaps a web access log
and that you are tracking IP addresses of visitors.
Can we tickle a little more information out of you?
Sorry,
Ok. The data is IDS events. I am not trying to create any new
information I just want to extract information. This information will
be used to relay whether a particular machine has ongoing issues.
For example,
SELECT COUNT(src_ip) AS CNT, INET_NTOA(src_ip), MAX(timestamp) FROM
event WHERE timestamp '2006-04-24 03:00:00' AND sid=1 AND
signature_id1 GROUP BY src_ip ORDER BY cnt DESC LIMIT 10
This will give me the top 10 source addresses for today based on how
many events they have triggered.
If they make the top ten, I want to see when we first saw that address:
SELECT MIN(timestamp) FROM event WHERE src_ip=INET_ATON('loop through
top ten')
I now want to know; out of all of the days between first seen and last
seen which days had events on them. I dont want the count(events) for
eah day, just whether there was an event or not so that I know between
first seen and last seen what the rate of appearance was.
I could do something crufty like this (the row count would be the
answer I am looking for):
SELECT DATE_FORMAT(timestamp,'%Y-%m-%d %a') AS DAY, COUNT(*) FROM
event WHERE timestamp BETWEEN '2006-01-01' AND '2006-04-24' and
src_ip=INET_NTOA('10.0.0.1') OR dst_ip=INET_ATON('10.0.0.1') GROUP BY
DAY;
But that seems like a lot of extra processing.
Thanks and sorry for the confusion.
Ideally, it would be nice to know what task you are trying to accomplish.
What is the source of your data?
What is the condition you are testing for?
And what, very specifically, is it that you would like us to help you with.
--John
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problems with Mysql 5 and Visual Basic 5
I think you will need to give the list some more information! What error message do you get? Is it an error when running SQL queries, or a problem connecting to the database? James Harvard At 9:22 am -0300 24/4/06, Gabriel Mahiques wrote: I migrated to mysql 5 but the applications with Visual Basic 5 return error in data. We have installed ODBC 3.51 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Compiling DBD::mysql with MySQL 5.0.20a
Description: I have Problem Compiling DBD::mysql with MySQL 5.0.20a. I get the following Message: meyer-lx:~/.cpan/build/DBD-mysql-3.0002 # make test PERL_DL_NONLAZY=1 /usr/bin/perl -MExtUtils::Command::MM -e test_harness(0, 'blib/lib', 'blib/arch') t/*.t t/00base.install_driver(mysql) failed: Can't load '/root/.cpan/build/DBD-mysql-3.0002/blib/arch/auto/DBD/mysql/mysql.so' for module DBD::mysql: /root/.cpan/build/DBD-mysql-3.0002/blib/arch/auto/DBD/mysql/mysql.so: undefined symbol: __builtin_delete at /usr/lib/perl5/5.8.7/i586-linux-thread-multi/DynaLoader.pm line 230. I get this kind of error since MySQL 5.0.20. How-To-Repeat: Install DBI 1.50 Install MySQL Development Files 5.0.20a Install DBD::mysql 3.0002 run perl Makefile.PL make make test Now i get errors. Here the Output: meyer-lx:~/.cpan/build/DBD-mysql-3.0002 # perl Makefile.PL I will use the following settings for compiling and testing: cflags(mysql_config) = -I/usr/include/mysql -I/usr/local/include -L/usr/local/lib -mcpu=i486 -fno-strength-reduce embedded (mysql_config) = libs (mysql_config) = -L/usr/lib/mysql -lmysqlclient -lz -lcrypt -lnsl -lm -lc -lnss_files -lnss_dns -lresolv -lc -lnss_files -lnss_dns -lresolv mysql_config (guessed ) = mysql_config nocatchstderr (default ) = 0 nofoundrows (default ) = 0 ssl (guessed ) = 0 testdb(default ) = test testhost (default ) = testpassword (default ) = testsocket(default ) = testuser (default ) = To change these settings, see 'perl Makefile.PL --help' and 'perldoc INSTALL'. Checking if your kit is complete... Looks good Using DBI 1.50 (for perl 5.008007 on i586-linux-thread-multi) installed in /usr/lib/perl5/site_perl/5.8.7/i586-linux-thread-multi/auto/DBI/ Writing Makefile for DBD::mysql meyer-lx:~/.cpan/build/DBD-mysql-3.0002 # make cp lib/DBD/mysql.pm blib/lib/DBD/mysql.pm cp lib/DBD/mysql/GetInfo.pm blib/lib/DBD/mysql/GetInfo.pm cp lib/Mysql.pm blib/lib/Mysql.pm cp lib/DBD/mysql/INSTALL.pod blib/lib/DBD/mysql/INSTALL.pod cp lib/Mysql/Statement.pm blib/lib/Mysql/Statement.pm cp lib/Bundle/DBD/mysql.pm blib/lib/Bundle/DBD/mysql.pm cc -c -I/usr/lib/perl5/site_perl/5.8.7/i586-linux-thread-multi/auto/DBI/ -I/usr/include/mysql -I/usr/local/include -L/usr/local/lib -mcpu=i486 -fno-strength-reduce -DDBD_MYSQL_INSERT_ID_IS_GOOD -g -D_REENTRANT -D_GNU_SOURCE -DTHREADS_HAVE_PIDS -DDEBUGGING -fno-strict-aliasing -pipe -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 -O2 -march=i586 -mtune=i686 -fmessage-length=0 -Wall -D_FORTIFY_SOURCE=2 -g -Wall -pipe -DVERSION=\3.0002\ -DXS_VERSION=\3.0002\ -fPIC -I/usr/lib/perl5/5.8.7/i586-linux-thread-multi/CORE dbdimp.c dbdimp.c: In function 'mysql_st_internal_execute41': dbdimp.c:2461: warning: 'rows' may be used uninitialized in this function /usr/bin/perl -p -e s/~DRIVER~/mysql/g /usr/lib/perl5/site_perl/5.8.7/i586-linux-thread-multi/auto/DBI//Driver.xst mysql.xsi /usr/bin/perl /usr/lib/perl5/5.8.7/ExtUtils/xsubpp -typemap /usr/lib/perl5/5.8.7/ExtUtils/typemap mysql.xs mysql.xsc mv mysql.xsc mysql.c Warning: duplicate function definition 'do' detected in mysql.xs, line 224 Warning: duplicate function definition 'rows' detected in mysql.xs, line 559 cc -c -I/usr/lib/perl5/site_perl/5.8.7/i586-linux-thread-multi/auto/DBI/ -I/usr/include/mysql -I/usr/local/include -L/usr/local/lib -mcpu=i486 -fno-strength-reduce -DDBD_MYSQL_INSERT_ID_IS_GOOD -g -D_REENTRANT -D_GNU_SOURCE -DTHREADS_HAVE_PIDS -DDEBUGGING -fno-strict-aliasing -pipe -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 -O2 -march=i586 -mtune=i686 -fmessage-length=0 -Wall -D_FORTIFY_SOURCE=2 -g -Wall -pipe -DVERSION=\3.0002\ -DXS_VERSION=\3.0002\ -fPIC -I/usr/lib/perl5/5.8.7/i586-linux-thread-multi/CORE mysql.c mysql.xs: In function 'XS_DBD__mysql__GetInfo_dbd_mysql_get_info': mysql.xs:631: warning: implicit declaration of function 'is_prefix' Running Mkbootstrap for DBD::mysql () chmod 644 mysql.bs rm -f blib/arch/auto/DBD/mysql/mysql.so LD_RUN_PATH=/lib /usr/bin/perl myld cc -shared dbdimp.o mysql.o -o blib/arch/auto/DBD/mysql/mysql.so\ -L/usr/lib/mysql -lmysqlclient -lz -lcrypt -lnsl -lm -lc -lnss_files -lnss_dns -lresolv -lc -lnss_files -lnss_dns -lresolv \ chmod 755 blib/arch/auto/DBD/mysql/mysql.so cp mysql.bs blib/arch/auto/DBD/mysql/mysql.bs chmod 644 blib/arch/auto/DBD/mysql/mysql.bs Manifying blib/man3/DBD::mysql.3pm Manifying blib/man3/DBD::mysql::INSTALL.3pm Manifying blib/man3/Mysql.3pm Manifying blib/man3/Bundle::DBD::mysql.3pm meyer-lx:~/.cpan/build/DBD-mysql-3.0002 # make test PERL_DL_NONLAZY=1 /usr/bin/perl -MExtUtils::Command::MM -e test_harness(0, 'blib/lib', 'blib/arch') t/*.t t/00base.install_driver(mysql) failed: Can't load
Re: If exists query.
--- Paul Halliday [EMAIL PROTECTED] wrote:
On 4/23/06, John Hicks [EMAIL PROTECTED] wrote:
Paul Halliday wrote:
I am doing queries that produce a table that looks something like
this:
Count | IP Address| First Seen| Last Seen | Days
5000 10.0.0.1 2005-12-102006-04-15 50*
6500 10.0.0.2 2006-04-012006-04-06 3**
*So of the 5000 events count(*) between 2005-12-10 and
2006-04-14
the count was distributed within 50 distinct days.
**This time the count is still high and it occured within 3 days
between 2006-04-01 and 2006-04-06.
I just cant figure out how to come up with the days part :).
Well, I guess that is a *bit* more specific, but not much better :)
So I'm guessing that the source of this data is perhaps a web
access log
and that you are tracking IP addresses of visitors.
Can we tickle a little more information out of you?
Sorry,
Ok. The data is IDS events. I am not trying to create any new
information I just want to extract information. This information will
be used to relay whether a particular machine has ongoing issues.
For example,
SELECT COUNT(src_ip) AS CNT, INET_NTOA(src_ip), MAX(timestamp) FROM
event WHERE timestamp '2006-04-24 03:00:00' AND sid=1 AND
signature_id1 GROUP BY src_ip ORDER BY cnt DESC LIMIT 10
This will give me the top 10 source addresses for today based on how
many events they have triggered.
If they make the top ten, I want to see when we first saw that
address:
SELECT MIN(timestamp) FROM event WHERE src_ip=INET_ATON('loop
through
top ten')
I now want to know; out of all of the days between first seen and
last
seen which days had events on them. I dont want the count(events) for
eah day, just whether there was an event or not so that I know
between
first seen and last seen what the rate of appearance was.
I could do something crufty like this (the row count would be the
answer I am looking for):
SELECT DATE_FORMAT(timestamp,'%Y-%m-%d %a') AS DAY, COUNT(*) FROM
event WHERE timestamp BETWEEN '2006-01-01' AND '2006-04-24' and
src_ip=INET_NTOA('10.0.0.1') OR dst_ip=INET_ATON('10.0.0.1') GROUP BY
DAY;
But that seems like a lot of extra processing.
Thanks and sorry for the confusion.
Ideally, it would be nice to know what task you are trying to
accomplish.
What is the source of your data?
What is the condition you are testing for?
And what, very specifically, is it that you would like us to help
you with.
--John
I think what you are looking for is the DISTINCT modifier to the
COUNT() aggregate function. That way you count only how many different
values exist in the list, not how many items are in the list.
Count | IP Address| First Seen| Last Seen | Days
SELECT
COUNT(src_ip) AS CNT
, INET_NTOA(src_ip)
, MIN(timestamp) first
, MAX(timestamp) last
, COUNT(DISTINCT DATE(timestamp)) days
FROM event
WHERE timestamp '2006-04-24 03:00:00'
AND sid=1
AND signature_id1
GROUP BY src_ip
ORDER BY cnt DESC
LIMIT 10
That is close to what you want. There are several ways to convert
timetsamps values into something that can be counted as a unique day
but I think the DATE function will be fast enough.
Shawn Green
__
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump and foreign keys
hi, are you using the InnoDB storage engine for your tables? yes, i do. example: $ mysqldump -d core groups : CREATE TABLE `groups` ( `id` smallint(5) unsigned NOT NULL default '0', `name` varchar(32) NOT NULL default '', `parent_id` smallint(5) unsigned default NULL, `setting_` text NOT NULL, `r__groups_users_status__id` tinyint(3) unsigned NOT NULL default '0', `_ctime` int(10) unsigned NOT NULL default '0', `_mtime` int(10) unsigned NOT NULL default '0', `_uid` smallint(5) unsigned NOT NULL default '0', `_gid` smallint(5) unsigned NOT NULL default '0', `_mod` smallint(5) unsigned NOT NULL default '0', PRIMARY KEY (`id`), KEY `_uid` (`_uid`), KEY `_gid` (`_gid`), KEY `_mod` (`_mod`), KEY `parent_id` (`parent_id`), KEY `r__groups_users_status__id` (`r__groups_users_status__id`), CONSTRAINT `groups_ibfk_10` FOREIGN KEY (`_gid`) REFERENCES `groups` (`id`), CONSTRAINT `groups_ibfk_11` FOREIGN KEY (`_uid`) REFERENCES `users` (`id`), CONSTRAINT `groups_ibfk_7` FOREIGN KEY (`parent_id`) REFERENCES `groups` (`id`), CONSTRAINT `groups_ibfk_8` FOREIGN KEY (`r__groups_users_status__id`) REFERENCES `groups_users_status` (`id`), ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='groups'; As you can see, the foreign keys - statements are included, but without the 'action parts' ( on update,on delete ); thanks, Nico -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Optimising for many rows and returned records (de-coupling query time to record set size for range queries)
Hello Adam Adam Wolff wrote: Actually runs through the table four times instead of twice, and maybe can't even use the index for the whole query. Assuming my results are not typical of MySQL query times, this would explain the sqrt() relationship of returned rows to query time. I have tried your suggestions of using a sub-query and have had trouble getting the syntax valid. But on using explain, it seems that 4 bytes of the index (either lat or lon) are being used and a brute force search on the index for the other constraint. If the query is returning 25600 points from a 100m dataset, it is brute seaching through 1.6m records in the second part of the index. If it were an option of creating 2 1.6M lists then looking for commonalities, it may be faster to instead use 1 1.6m item list then brute force constraint search. I have received suggestions to use spatial indexes, which I am looking into. Alternatively, I could optimise queries by creating multiple slices of the data set accross one axis then use a key on the other axis. MySQL 5.1 partitioning scheme may help. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql query browser- editing resultsets
Can someone explain to me how to edit this...I click on the cells and nothing From the manual If the query resultset is editable, you can click the Edit button below the resultset view. Double clicking cells in the resultset will allow you to edit their contents. Changes are commited to the database only after the Apply Changes button is pressed. For a resultset to be editable, the following conditions must be met: a.. the resultset must contain columns from no more than one single table; b.. the queried columns must be proper column names (e.g: no functions, such as in SELECT max(price) FROM products); c.. this table must contain a Primary Key column, although not necessarily in the query. If the query does not contain the primary key, it will be automatically added before the MySQL Query Browser sends the query to the MySQL server, but will display only the columns you requested;
RE: mysql query browser- editing resultsets
There is an EDIT button on the bottom of the Query Browser. You have to activate the edit feature by clicking on this. You will see that it remains highlighted. To edit your cells, double click on them. When finished, hit APPLY CHANGES at the bottom. If you do not apply them, they will not commit. J.R. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, April 24, 2006 11:14 AM To: mysql@lists.mysql.com Subject: mysql query browser- editing resultsets Can someone explain to me how to edit this...I click on the cells and nothing From the manual If the query resultset is editable, you can click the Edit button below the resultset view. Double clicking cells in the resultset will allow you to edit their contents. Changes are commited to the database only after the Apply Changes button is pressed. For a resultset to be editable, the following conditions must be met: a.. the resultset must contain columns from no more than one single table; b.. the queried columns must be proper column names (e.g: no functions, such as in SELECT max(price) FROM products); c.. this table must contain a Primary Key column, although not necessarily in the query. If the query does not contain the primary key, it will be automatically added before the MySQL Query Browser sends the query to the MySQL server, but will display only the columns you requested; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Optimising for many rows and returned records (de-coupling query time to record set size for range queries)
Well, I hadn't known about the spatial features of MySQL. If you're ok using vendor extensions then that definitely looks like the way to go: http://dev.mysql.com/doc/refman/5.0/en/gis-introduction.html A On Apr 24, Nick Hill wrote: Hello Adam Adam Wolff wrote: Actually runs through the table four times instead of twice, and maybe can't even use the index for the whole query. Assuming my results are not typical of MySQL query times, this would explain the sqrt() relationship of returned rows to query time. I have tried your suggestions of using a sub-query and have had trouble getting the syntax valid. But on using explain, it seems that 4 bytes of the index (either lat or lon) are being used and a brute force search on the index for the other constraint. If the query is returning 25600 points from a 100m dataset, it is brute seaching through 1.6m records in the second part of the index. If it were an option of creating 2 1.6M lists then looking for commonalities, it may be faster to instead use 1 1.6m item list then brute force constraint search. I have received suggestions to use spatial indexes, which I am looking into. Alternatively, I could optimise queries by creating multiple slices of the data set accross one axis then use a key on the other axis. MySQL 5.1 partitioning scheme may help. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql query browser- editing resultsets
The edit button is 'greyed out' Ross - Original Message - From: J.R. Bullington [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, April 24, 2006 4:20 PM Subject: RE: mysql query browser- editing resultsets There is an EDIT button on the bottom of the Query Browser. You have to activate the edit feature by clicking on this. You will see that it remains highlighted. To edit your cells, double click on them. When finished, hit APPLY CHANGES at the bottom. If you do not apply them, they will not commit. J.R. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, April 24, 2006 11:14 AM To: mysql@lists.mysql.com Subject: mysql query browser- editing resultsets Can someone explain to me how to edit this...I click on the cells and nothing From the manual If the query resultset is editable, you can click the Edit button below the resultset view. Double clicking cells in the resultset will allow you to edit their contents. Changes are commited to the database only after the Apply Changes button is pressed. For a resultset to be editable, the following conditions must be met: a.. the resultset must contain columns from no more than one single table; b.. the queried columns must be proper column names (e.g: no functions, such as in SELECT max(price) FROM products); c.. this table must contain a Primary Key column, although not necessarily in the query. If the query does not contain the primary key, it will be automatically added before the MySQL Query Browser sends the query to the MySQL server, but will display only the columns you requested; -- 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: mysql query browser- editing resultsets
Then, as the documentation states, you cannot edit the fields in that particular query. Either the table is read-only, you have multiple tables (like a join) in your SQL string, or you have functions (max(),min(),count()) in your string. Check out those items again, and if you still can't edit it, post your SQL query and your CREATE TABLE statement so that we (the list) can test this out for you. J.R. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, April 24, 2006 11:42 AM To: J.R. Bullington; mysql@lists.mysql.com Subject: Re: mysql query browser- editing resultsets The edit button is 'greyed out' Ross - Original Message - From: J.R. Bullington [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, April 24, 2006 4:20 PM Subject: RE: mysql query browser- editing resultsets There is an EDIT button on the bottom of the Query Browser. You have to activate the edit feature by clicking on this. You will see that it remains highlighted. To edit your cells, double click on them. When finished, hit APPLY CHANGES at the bottom. If you do not apply them, they will not commit. J.R. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, April 24, 2006 11:14 AM To: mysql@lists.mysql.com Subject: mysql query browser- editing resultsets Can someone explain to me how to edit this...I click on the cells and nothing From the manual If the query resultset is editable, you can click the Edit button below the resultset view. Double clicking cells in the resultset will allow you to edit their contents. Changes are commited to the database only after the Apply Changes button is pressed. For a resultset to be editable, the following conditions must be met: a.. the resultset must contain columns from no more than one single table; b.. the queried columns must be proper column names (e.g: no functions, such as in SELECT max(price) FROM products); c.. this table must contain a Primary Key column, although not necessarily in the query. If the query does not contain the primary key, it will be automatically added before the MySQL Query Browser sends the query to the MySQL server, but will display only the columns you requested; -- 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: mysql query browser- editing resultsets
In this case, the result set is not editable. It needs to have some way to uniquely identify the row(s) under edit so it can perform an update. Tim -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, April 24, 2006 11:42 AM To: J.R. Bullington; mysql@lists.mysql.com Subject: Re: mysql query browser- editing resultsets The edit button is 'greyed out' Ross - Original Message - From: J.R. Bullington [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, April 24, 2006 4:20 PM Subject: RE: mysql query browser- editing resultsets There is an EDIT button on the bottom of the Query Browser. You have to activate the edit feature by clicking on this. You will see that it remains highlighted. To edit your cells, double click on them. When finished, hit APPLY CHANGES at the bottom. If you do not apply them, they will not commit. J.R. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, April 24, 2006 11:14 AM To: mysql@lists.mysql.com Subject: mysql query browser- editing resultsets Can someone explain to me how to edit this...I click on the cells and nothing From the manual If the query resultset is editable, you can click the Edit button below the resultset view. Double clicking cells in the resultset will allow you to edit their contents. Changes are commited to the database only after the Apply Changes button is pressed. For a resultset to be editable, the following conditions must be met: a.. the resultset must contain columns from no more than one single table; b.. the queried columns must be proper column names (e.g: no functions, such as in SELECT max(price) FROM products); c.. this table must contain a Primary Key column, although not necessarily in the query. If the query does not contain the primary key, it will be automatically added before the MySQL Query Browser sends the query to the MySQL server, but will display only the columns you requested; -- 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]
Re: mysql query browser- editing resultsets
I think the most likely case is the table is read-only. How do I change this? Ross - Original Message - From: J.R. Bullington [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, April 24, 2006 4:46 PM Subject: RE: mysql query browser- editing resultsets Then, as the documentation states, you cannot edit the fields in that particular query. Either the table is read-only, you have multiple tables (like a join) in your SQL string, or you have functions (max(),min(),count()) in your string. Check out those items again, and if you still can't edit it, post your SQL query and your CREATE TABLE statement so that we (the list) can test this out for you. J.R. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, April 24, 2006 11:42 AM To: J.R. Bullington; mysql@lists.mysql.com Subject: Re: mysql query browser- editing resultsets The edit button is 'greyed out' Ross - Original Message - From: J.R. Bullington [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, April 24, 2006 4:20 PM Subject: RE: mysql query browser- editing resultsets There is an EDIT button on the bottom of the Query Browser. You have to activate the edit feature by clicking on this. You will see that it remains highlighted. To edit your cells, double click on them. When finished, hit APPLY CHANGES at the bottom. If you do not apply them, they will not commit. J.R. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, April 24, 2006 11:14 AM To: mysql@lists.mysql.com Subject: mysql query browser- editing resultsets Can someone explain to me how to edit this...I click on the cells and nothing From the manual If the query resultset is editable, you can click the Edit button below the resultset view. Double clicking cells in the resultset will allow you to edit their contents. Changes are commited to the database only after the Apply Changes button is pressed. For a resultset to be editable, the following conditions must be met: a.. the resultset must contain columns from no more than one single table; b.. the queried columns must be proper column names (e.g: no functions, such as in SELECT max(price) FROM products); c.. this table must contain a Primary Key column, although not necessarily in the query. If the query does not contain the primary key, it will be automatically added before the MySQL Query Browser sends the query to the MySQL server, but will display only the columns you requested; -- 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]
exporting a mysql database via mysql query browser
I am a phpmyadmin user and have never really used mysql query browser before. I have a database sitting on my localhost and I want to export the whole thing via mysql query browser to the host. What is the easiest way to do it? Ross
totalizing of Rows please help!!
i have three Values in each row of my MySQL database, im needing to sum them in the table as they are displayed per Row ?? how do i do this ? Projectname Elecremain Controlremainotherremain ?php ? Project1 2300 1600 250 ?php (Sum) ? Project2 4300 600 150 ?php (Sum) ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: exporting a mysql database via mysql query browser
[EMAIL PROTECTED] wrote: I am a phpmyadmin user and have never really used mysql query browser before. I have a database sitting on my localhost and I want to export the whole thing via mysql query browser to the host. What is the easiest way to do it? Ross Use the MySQL admin tool, not the query browser. Look at the back options in the admin tool -- Chris W KE5GIX Gift Giving Made Easy Get the gifts you want give the gifts they want One stop wish list for any gift, from anywhere, for any occasion! http://thewishzone.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: totalizing of Rows please help!!
Hi, Brian! First of all I think next time you should better attach your database structure. It'll be much easier to understand your problem if you will... Maybe you're asking for something like this: SELECT project_name, elecremain, controlremain, otherremain, elecremain + controlremain + otherremain AS total FROM table_name; Brian E Boothe wrote: i have three Values in each row of my MySQL database, im needing to sum them in the table as they are displayed per Row ?? how do i do this ? Projectname Elecremain Controlremain otherremain ?php ? Project1 2300 1600 250 ?php (Sum) ? Project2 4300 600 150 ?php (Sum) ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Error wiht VB 5 and MySQL
Hi. A Brief description about my problem. We have many applications development in Visual Basic 5. These applications read data from MySQL 4.0 installed on Linux (and odbc 3.51.06). Many fields in our tables are in decimal format. Last weekend we installed MySQL 5 on Linux When we ran the applications they had an error: all data in decimal format return ??? (for example: if the data must be 1345,68 the form show ). Can anybory help me. -- Saludos cordiales. Ing. Gabriel Mahiques Dto. Control de Gestión ELECTROINGENIERA S.A. Telefono: 474 1414 Uspallata 1461. Barrio San Martín. CP: X5008HSH Córdoba. República Argentina. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Table design; 2-column index
Hello List, If I have a table: CREATE TABLE t ( id int(11) NOT NULL auto_increment, fk1 mediumint(9) NOT NULL default '0', fk2 smallint(6) NOT NULL default '0', PRIMARY KEY (id), UNIQUE KEY idxfk1 (fk1,fk2), UNIQUE KEY idxfk2 (fk2,fk1) ) TYPE=MyISAM; I will about half the time have a query WHERE fk1 IN () and about the other half the time have WHERE fk2 IN () Does it make sense to define the UNIQUE KEYS the way I have? Thanks! -- Wellington -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table design; 2-column index
In the last episode (Apr 24), Fan, Wellington said: If I have a table: CREATE TABLE t ( id int(11) NOT NULL auto_increment, fk1 mediumint(9) NOT NULL default '0', fk2 smallint(6) NOT NULL default '0', PRIMARY KEY (id), UNIQUE KEY idxfk1 (fk1,fk2), UNIQUE KEY idxfk2 (fk2,fk1) ) TYPE=MyISAM; I will about half the time have a query WHERE fk1 IN () and about the other half the time have WHERE fk2 IN () Does it make sense to define the UNIQUE KEYS the way I have? You only need one unique index to enforce uniqueness, so you can safely convert your idxfk2 to a single-column regular index and save a little bit of space. ALTER TABLE t drop key idxfk2, add key idxfk2 (fk2); -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Table design; 2-column index
Hey Dan, Thanks; I was really trying to ask about the potential performance gain, however. I don't care so much about the UNIQUEness, but the INDEXness. See, I am wondering if I create an 2-column index wiht fk1 as the first component, will that index help me if I am refering fk2 in my query? -Original Message- From: Dan Nelson [mailto:[EMAIL PROTECTED] Sent: Monday, April 24, 2006 1:40 PM To: Fan, Wellington Cc: mysql@lists.mysql.com Subject: Re: Table design; 2-column index In the last episode (Apr 24), Fan, Wellington said: If I have a table: CREATE TABLE t ( id int(11) NOT NULL auto_increment, fk1 mediumint(9) NOT NULL default '0', fk2 smallint(6) NOT NULL default '0', PRIMARY KEY (id), UNIQUE KEY idxfk1 (fk1,fk2), UNIQUE KEY idxfk2 (fk2,fk1) ) TYPE=MyISAM; I will about half the time have a query WHERE fk1 IN () and about the other half the time have WHERE fk2 IN () Does it make sense to define the UNIQUE KEYS the way I have? You only need one unique index to enforce uniqueness, so you can safely convert your idxfk2 to a single-column regular index and save a little bit of space. ALTER TABLE t drop key idxfk2, add key idxfk2 (fk2); -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Error wiht VB 5 and MySQL
Check the way your forms deal with the data from the resultset, any implicit conversion? Maybe a declaration of type that is casting another type for the value returned from the resultset. Also check this: http://dev.mysql.com/doc/refman/5.0/en/precision-math-decimal-changes.html On 4/24/06, Gabriel Mahiques [EMAIL PROTECTED] wrote: Hi. A Brief description about my problem. We have many applications development in Visual Basic 5. These applications read data from MySQL 4.0 installed on Linux (and odbc 3.51.06). Many fields in our tables are in decimal format. Last weekend we installed MySQL 5 on Linux When we ran the applications they had an error: all data in decimal format return ??? (for example: if the data must be 1345,68 the form show ). Can anybory help me. -- Saludos cordiales. Ing. Gabriel Mahiques Dto. Control de Gestión ELECTROINGENIERA S.A. Telefono: 474 1414 Uspallata 1461. Barrio San Martín. CP: X5008HSH Córdoba. República Argentina. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Daniel da Veiga Computer Operator - RS - Brazil -BEGIN GEEK CODE BLOCK- Version: 3.1 GCM/IT/P/O d-? s:- a? C++$ UBLA++ 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-- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: totalizing of Rows please help!!
Brian, i have three Values in each row of my MySQL database, im needing to sum them in the table as they are displayed per Row ?? how do i do this ? Projectname Elecremain Controlremain otherremain ?php ? Project1 2300 1600 250 ?php (Sum) ? Project2 4300 600 150 ?php (Sum) ? In the query, with something like SELECT elecremain, controlremain, otherremain, elecremain+controlremain+otherremain AS SumRemain FROM ... etc PB -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.4.5/322 - Release Date: 4/22/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table design; 2-column index
In the last episode (Apr 24), Fan, Wellington said: Thanks; I was really trying to ask about the potential performance gain, however. I don't care so much about the UNIQUEness, but the INDEXness. See, I am wondering if I create an 2-column index wiht fk1 as the first component, will that index help me if I am refering fk2 in my query? You mean like SELECT fk2 FROM t WHERE fk1 IN (1,2,3,4)? Yes. You can verify this by looking at the EXPLAIN plan for the query. If it says Using index in the Extra column, it means all the fields mysql needs is in the index and it won't have to fetch row data. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: If exists query.
Paul,
I am doing queries that produce a table that looks
something like this:
Count | IP Address | First Seen | Last
Seen | Days
5000 10.0.0.1 2005-12-10
2006-04-15 50*
6500 10.0.0.2 2006-04-01
2006-04-06 3**
*So of the 5000 events "count(*) between 2005-12-10
and 2006-04-14"
the count was distributed within 50 distinct days.
**This time the count is still high and it occured
within 3 days
between 2006-04-01 and 2006-04-06.
I just cant figure out how to come up with the days
part :) .
snip
You could grab the first day for each src_ip with something like ...
SELECT
COUNT(src_ip) AS CNT,
INET_NTOA(src_ip),
MAX(timestamp),
(SELECT MIN(timestamp) FROM event e2 WHERE e2.src_ip=e1.src_ip) AS
First
FROM event e1
WHERE timestamp '2006-04-24 03:00:00'
AND sid=1
AND signature_id1
GROUP BY src_ip
ORDER BY cnt DESC LIMIT 10
I now want to know; out of all of the days between first seen and
last
seen which days had events on them. I dont want the count(events)
for
eah day, just whether there was an event or not so that I know
between
first seen and last seen what the rate of appearance was.
That's easiest if you maintain a calendar table with one row per day in
the appropriate range. Then you can simply count the rows you get on a
ranged events-calendar join.
PB
-
Paul Halliday wrote:
On 4/23/06, John Hicks [EMAIL PROTECTED] wrote:
Paul Halliday wrote:
I am doing queries that produce a table that looks something like this:
Count | IP Address| First Seen| Last Seen | Days
5000 10.0.0.1 2005-12-102006-04-15 50*
6500 10.0.0.2 2006-04-012006-04-06 3**
*So of the 5000 events "count(*) between 2005-12-10 and 2006-04-14"
the count was distributed within 50 distinct days.
**This time the count is still high and it occured within 3 days
between 2006-04-01 and 2006-04-06.
I just cant figure out how to come up with the days part :).
Well, I guess that is a *bit* more specific, but not much better :)
So I'm guessing that the source of this data is perhaps a web access log
and that you are tracking IP addresses of visitors.
Can we tickle a little more information out of you?
Sorry,
Ok. The data is IDS events. I am not trying to create any new
information I just want to extract information. This information will
be used to relay whether a particular machine has ongoing issues.
For example,
SELECT COUNT(src_ip) AS CNT, INET_NTOA(src_ip), MAX(timestamp) FROM
event WHERE timestamp '2006-04-24 03:00:00' AND sid="1" AND
signature_id"1" GROUP BY src_ip ORDER BY cnt DESC LIMIT 10
This will give me the top 10 source addresses for today based on how
many events they have triggered.
If they make the top ten, I want to see when we first saw that address:
SELECT MIN(timestamp) FROM event WHERE src_ip=INET_ATON('loop through
top ten')
I now want to know; out of all of the days between first seen and last
seen which days had events on them. I dont want the count(events) for
eah day, just whether there was an event or not so that I know between
first seen and last seen what the rate of appearance was.
I could do something crufty like this (the row count would be the
answer I am looking for):
SELECT DATE_FORMAT(timestamp,'%Y-%m-%d %a') AS DAY, COUNT(*) FROM
event WHERE timestamp BETWEEN '2006-01-01' AND '2006-04-24' and
src_ip=INET_NTOA('10.0.0.1') OR dst_ip=INET_ATON('10.0.0.1') GROUP BY
DAY;
But that seems like a lot of extra processing.
Thanks and sorry for the confusion.
Ideally, it would be nice to know what task you are trying to accomplish.
What is the source of your data?
What is the "condition" you are testing for?
And what, very specifically, is it that you would like us to help you with.
--John
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.4.5/322 - Release Date: 4/22/2006
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Older version MySQL
Mike, all, the packages you need should exist: Mike Blezien wrote: Hello, I am attempting to upgrade one of our older servers running RH 7.3 w/glibc-2.2.5-44 Ok, so you need a package for glibc 2.2. There are two choices, both on the 4.0 download page: 1) From the section Linux (non RPM package) downloads, first row, a tar.gz: Linux (x86, glibc-2.2, ... 2) A bit further down, the RPMs: Linux x86 generic RPM (statically linked against glibc 2.2.5) downloads Here, the server and client RPMs do not require the glibc 2.3. it's currently using 3.23.58 and tried installing 4.0.26 rpm's for linux, but won't install due to the following error: libc.so.6(GLIBC_2.3) is needed by MySQL-shared-4.0.26-0 libpthread.so.0(GLIBC_2.3.2) is needed by MySQL-shared-4.0.26-0 libc.so.6(GLIBC_2.3) is needed by MySQL-shared-compat-4.0.26-0 libpthread.so.0(GLIBC_2.3.2) is needed by MySQL-shared-compat-4.0.26-0 I am surprised the shared packages need glibc 2.3, typically this should be indicated by a -glibc23 part in the package name. For which purpose do you need shared and shared-compat? Now of the RPM's will install. Is there a 4.0.+ version that will install on this type of server without have to upgrade alot of other stuff ?? Or can we build it from source, and if so, what is the recommend configure options used to build from source ? Yes, you can build it from source. IMHO, running the RPM build process (using the spec file) on your machine should even provide these packages. HTH, Joerg -- 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]
Re: Error wiht VB 5 and MySQL
but, when the fields are float type, this error don't happen. When the fileds are float the result is the correct (if I have 1.2569 in the table, in the application I see 1.2569.) Daniel da Veiga escribió: Check the way your forms deal with the data from the resultset, any implicit conversion? Maybe a declaration of type that is casting another type for the value returned from the resultset. Also check this: http://dev.mysql.com/doc/refman/5.0/en/precision-math-decimal-changes.html On 4/24/06, Gabriel Mahiques [EMAIL PROTECTED] wrote: Hi. A Brief description about my problem. We have many applications development in Visual Basic 5. These applications read data from MySQL 4.0 installed on Linux (and odbc 3.51.06). Many fields in our tables are in decimal format. Last weekend we installed MySQL 5 on Linux When we ran the applications they had an error: all data in decimal format return ??? (for example: if the data must be 1345,68 the form show ). Can anybory help me. -- Saludos cordiales. Ing. Gabriel Mahiques Dto. Control de Gestión ELECTROINGENIERA S.A. Telefono: 474 1414 Uspallata 1461. Barrio San Martín. CP: X5008HSH Córdoba. República Argentina. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Daniel da Veiga Computer Operator - RS - Brazil -BEGIN GEEK CODE BLOCK- Version: 3.1 GCM/IT/P/O d-? s:- a? C++$ UBLA++ 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-- -- Saludos cordiales. Ing. Gabriel Mahiques Dto. Control de Gestión ELECTROINGENIERA S.A. Telefono: 474 1414 Uspallata 1461. Barrio San Martín. CP: X5008HSH Córdoba. República Argentina. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: what is this? -- errno=2006 errmsg=Server gone
Hi Martin, all, again, there is quite a delay to my reply. Martin Olsson wrote: This is software I use: D:\MDmysql --version mysql Ver 14.7 Distrib 4.1.12, for Win32 (ia32) Until now, I lived under the impression that error 2006 would not occur on Windows, as it happens on a Unix condition which Windows does not indicate. So this understanding seems wrong - sorry. C:\Apache\Apache2\binApache.exe -v Server version: Apache/2.0.54 Server built: Apr 16 2005 14:25:31 C:\Apache\Apache2\binver Microsoft Windows 2000 [Version 5.00.2195] Exactly which parameters can I tweak to fix this error (i.e. errno=2006 errmsg=Server gone)?? At this stage _any_ help/hint/guess would be really appreciated. Are you sure the server is up and running right before you try to send your data? My understanding till now was that error 2006 is reported when the send fails due to a closed socket or similar condition, and that _during the send_ this is only recognizable on Unix and not on Windows. But that is about all I (believe to) know about this. [[...]] --- Joerg Bruehe; you pointed out that: this message is issued if the client gets an error reported while sending a command to the server. How can I determine if this is in fact the case? And in particular, how can I get my hands on the exact error sent? In general, is there any types of logging I can check and/or enable to further dig into the cause of this problem? For both questions I lack the info - sorry. Joer -- 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]
Re: totalizing of Rows please help!!SQL Dump
Please answer to list next time.
Good. Now, what is your Elecremain, Controlremain, otherremain?
How should they be calculated...
P.S.: Why don't you give a try to some numerical data types? ;) To use
varchar everythere isn't a right way I think.
Brian E Boothe wrote:
my database structure SQL dump
# phpMyAdmin SQL Dump
# version 2.5.7-pl1
# http://www.phpmyadmin.net
#
# Host: localhost
# Generation Time: Apr 14, 2006 at 12:36 AM
# Server version: 4.0.20
# PHP Version: 4.4.2
#
# Database : `workorder`
#
#
#
# Table structure for table `orders`
#
CREATE TABLE `orders` (
`ordernumber` varchar(12) NOT NULL default '',
`companyname` varchar(12) NOT NULL default '',
`billingaddress` varchar(12) NOT NULL default '',
`City` varchar(12) NOT NULL default '',
`State` varchar(12) default NULL,
`Zip` varchar(12) NOT NULL default '',
`PhoneNumber` varchar(12) NOT NULL default '',
`FaxNumber` varchar(12) NOT NULL default '',
`WebPage` varchar(12) NOT NULL default '',
`EmailAddress` varchar(12) NOT NULL default '',
`Notes` varchar(23) NOT NULL default '',
`Customer` varchar(12) NOT NULL default '',
`Startdate` varchar(12) NOT NULL default '',
`Completedate` varchar(12) NOT NULL default '',
`Biddate` varchar(12) NOT NULL default '',
`Bidamount` varchar(12) NOT NULL default '',
`ElecProjCost` varchar(12) NOT NULL default '',
`ElecProjBill` varchar(12) NOT NULL default '',
`ElecRem` varchar(12) NOT NULL default '',
`CtrlProjCost` varchar(12) NOT NULL default '',
`CtrlProjBill` varchar(12) NOT NULL default '',
`CtrlProjrem` varchar(12) NOT NULL default '',
`OthrProjCost` varchar(12) NOT NULL default '',
`OthrProjBill` varchar(12) NOT NULL default '',
`OthrProjrem` varchar(12) NOT NULL default '',
`BondAm` varchar(7) NOT NULL default '',
`BondBill` varchar(7) NOT NULL default '',
`BondRem` varchar(7) NOT NULL default ''
) TYPE=MyISAM;
#
# Dumping data for table `orders`
#
INSERT INTO `orders` VALUES ('4132006', 'Siouxcity SS', '231 Way way',
'SiouxCity', 'IA', '51101', '712-278-2331', '', 'ssrw.com',
'[EMAIL PROTECTED]', 'efbebebeftrbbe', 'Siouxcity', '12/2/2006',
'2/5/2007', '2/4/2007', '123000', '234', '2', '232', '435', '23', '',
'345', '23', '', '345', '23', '');
INSERT INTO `orders` VALUES ('1232006', 'Sciouscenter', '124323wgwr',
'Siouxcenter', 'IA', '51101', '712-278-2331', '', '', '', 'this is a
test project', 'Sioux', '12/2/2006', '2/5/2007', '2/4/2007', '123000',
'543', '456', '87', '654', '345', '309', '765', '123', '642', '8760',
'234', '8526');
(---End
Dump)
Eugene Kosov wrote:
Hi, Brian!
First of all I think next time you should better attach your database
structure. It'll be much easier to understand your problem if you
will...
Maybe you're asking for something like this:
SELECT project_name, elecremain, controlremain, otherremain,
elecremain + controlremain + otherremain AS total FROM table_name;
Brian E Boothe wrote:
i have three Values in each row of my MySQL database, im needing to
sum them in the table as they are displayed per Row ?? how do i do
this ?
Projectname Elecremain Controlremain
otherremain ?php ?
Project1 2300 1600
250 ?php (Sum) ?
Project2 4300 600
150 ?php (Sum) ?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MYSQL 4.1 Collation
Is there any way to turn off collation in mysql 4.1? PHPadmin is showing a new colation dropdown menu which I need to get rid of. Thanks, Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Individual Row Addition ((!!!help!!)
well i have this $sql2=SELECT ordernumber,Elecrem, CtrlProjrem, OthrProjrem SUM(Elecrem+CtrlProjrem+OthrProjrem) AS btstotal9 FROM orders GROUP BY ordernumber ORDER BY ordernumber; only add's up the first row it hits and adds it to the remaining rows ,, I need individual Row Addition Please helppp OrdernumberElecrem, CtrlProjrem,OthrProjrem 12311314234 123 123 = ? row ? = 480 12354314100 123 123 = ? row ? = 346 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: totalizing of Rows please help!!
Brian E Boothe wrote: i have three Values in each row of my MySQL database, im needing to sum them in the table as they are displayed per Row ?? how do i do this ? As Eugene said, VARCHAR values don't sum, since they are not numeric. Either cast the column values you wish sums of to a numeric type, or--a much better idea--correct your table structure. PB -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.4.5/322 - Release Date: 4/22/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Fulltext 3 letter words
Hi James, Yes I have verified whether the variable has changed with show variables, and it has not changed as per my first email. I have not gotten to rebuilding as the variable just will not change ;-) Kind regards, Taco Fleur Free Call 1800 032 982 or Mobile 0421 851 786 Pacific Fox http://www.pacificfox.com.au an industry leader with commercial IT experience since 1994 . * Web Design and Development * SMS Solutions, including developer API * Domain Registration, .COM for as low as fifteen dollars a year, .COM.AU for fifty dollars two years! -Original Message- From: James Harvard [mailto:[EMAIL PROTECTED] Sent: Monday, 24 April 2006 10:11 PM To: mysql@lists.mysql.com Subject: Re: Fulltext 3 letter words You can find out whether the server has picked up the changes from your configuration file by running the following SQL: show variables like 'ft_min_word_len'; As per the instructions in the manual http://dev.mysql.com/doc/refman/5.0/en/fulltext-fine-tuning.html you must rebuild the indexes on any tables with a full-text index. Also, note the warning about using myisamchk further down the manual page. HTH James Harvard -- 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]
