Re: duplicating records

[Jeremy March]

> I tried this and doesn't work unforunately. I get "ERROR: Duplicate 
> entry..." that occurs when copying from the temporary table back into 
> the original table.

> Is there a way to do this, to copy a single record, without moving the 
> primary key into the temporary table so I can avoid this error?
> Ari

>> create temporary table t1 select ... from orig_table where ...
>> insert into orig_table select ... from t1

Just insert null instead of the primary key:

create temporary table t1 select ... from orig_table where ...
insert into orig_table select null, your_column1, your_column2... from t1;


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: duplicating records

[Ari Denison]

I tried this and doesn't work unforunately. I get "ERROR: Duplicate 
entry..." that occurs when copying from the temporary table back into 
the original table.

Is there a way to do this, to copy a single record, without moving the 
primary key into the temporary table so I can avoid this error?
Ari

On Feb 17, 2004, at 7:13 PM, Sasha Pachev wrote:

Ari Denison wrote:
I have a need to duplicate individual records in a table with a large 
number of fields.
This is not part of a backup or restore process. I simply need to be 
able to duplicate a record or two on occasion.
 Is there an efficient way to do this?
I obviously don't want to duplicate keyfields, just the data.
create temporary table t1 select ... from orig_table where ...
insert into orig_table select ... from t1
--
Sasha Pachev
Create online surveys at http://www.surveyz.com/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]



 Ari Denison
 Owner, Pixenter INC
 Lead Developer, Nu-World Communications
 [EMAIL PROTECTED]
 (541) 513-1889
  stonecrusade (aim)
  Eugene, Oregon, USA

Re: Unix Timestamps

[Sasha Pachev]

Jeremy wrote:
I've been looking though "MySQL" by Paul DuBois (second edition) and the 
MySQL docs at http://www.mysql.com/doc/en/Date_and_time_functions.html 
(and others).

I'm looking for a MySQL function that returns the unix time (second 
since epoch), and there doesn't seem to be one.
UNIX_TIMESTAMP()

Using a PHP script, what's the best way to store the current time (for 
example, when a message board post is made) so it can be easily read 
back and formatted later? I need to be able to use either MySQL's 
fucntions or the PHP date functions to format the date like "Tuesday, 
Feb 17, 2004 9:18:30pm".
Store as datetime, use current_datetime() to get the current time to store, 
date_add() and date_sub() for arithmetic, and date_format() to retrieve.

--
Sasha Pachev
Create online surveys at http://www.surveyz.com/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Unix Timestamps

[Jeremy Zawodny]

On Tue, Feb 17, 2004 at 09:19:26PM -0500, Jeremy wrote:
> I've been looking though "MySQL" by Paul DuBois (second edition) and the 
> MySQL docs at http://www.mysql.com/doc/en/Date_and_time_functions.html 
> (and others).

UNIX_TIMESTAMP(NOW())
-- 
Jeremy D. Zawodny |  Perl, Web, MySQL, Linux Magazine, Yahoo!
<[EMAIL PROTECTED]>  |  http://jeremy.zawodny.com/

MySQL 4.0.15-Yahoo-SMP: up 157 days, processed 1,868,164,836 queries (137/sec. avg)

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Data lost

[Kelvin Leung]

My client reported the a case to me.  He said one mysql table lost some of
the data.  The table structure is shown as follows:
+---+---+--+-+-+---+
| Field | Type  | Null | Key | Default | Extra |
+---+---+--+-+-+---+
| uid   | varchar(32)   |  | PRI | |   |
| createdate| datetime  | YES  | | NULL|   |
| lastmoddate   | datetime  | YES  | | NULL|   |
| num_of_files  | int(13)   | YES  | | NULL|   |
| createby  | varchar(32)   | YES  | | NULL|   |
| field_107666797596432 | varchar(255)  | YES  | MUL | NULL|   |
| field_107666798773233 | varchar(255)  | YES  | MUL | NULL|   |
| field_107666808467434 | varchar(255)  | YES  | MUL | NULL|   |
| field_107666809745535 | decimal(13,5) | YES  | MUL | NULL|   |
| field_107666813934636 | varchar(255)  | YES  | MUL | NULL|   |
| field_107666815016037 | varchar(255)  | YES  | MUL | NULL|   |
| field_107666815625938 | text  | YES  | | NULL|   |
+---+---+--+-+-+---+
The fields "field_x" lost data in most of the records, but no data was
lost on other fields.  Do you think it is possible?  I'm guessing that it is
caused by the corrupted index file.  Does it help if I repair the index
file?  Your suggestion is greatly appreciated as I need to suggest my client
how to recover the data.
---
Kelvin Leung
IPOS Computer Systems Limited
http://www.iposcsl.com/
Mobile: +852 9199-5852
Tel: +852 2329-0345
Fax: +852 2329-0343



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Load data infile

[Scott Haneda]

What are the options to load data infile when not all lines in the file are
perfect.

For example, I have a 12 field file, but some of the data in the lines is
bad, could contain 10 or less, I just want to let those go in in the order
they are in.
-- 
-
Scott HanedaTel: 415.898.2602
http://www.newgeo.com   Fax: 313.557.5052
[EMAIL PROTECTED]Novato, CA U.S.A.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: duplicating records

[Sasha Pachev]

Ari Denison wrote:
I have a need to duplicate individual records in a table with a large 
number of fields.

This is not part of a backup or restore process. I simply need to be 
able to duplicate a record or two on occasion.

 Is there an efficient way to do this?

I obviously don't want to duplicate keyfields, just the data.
create temporary table t1 select ... from orig_table where ...
insert into orig_table select ... from t1
--
Sasha Pachev
Create online surveys at http://www.surveyz.com/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Can't connect using C API

[Sasha Pachev]

Cira Account wrote:
I apologize if the listings below are badly
formatted.  I am posting this from a webmail client
and it has very short line lengths.
I am using MySQL 4.0.17 on QNX 6.2.0.  I can connect
by typing "mysql -u root" from the command line. 
However, I get:

Database connect failed
Can't connect to local MySQL server through
socket '' (261) (2002)
when I run this program:

#include 
#include 
int main(int argc, char *argv[])
{
MYSQL   mysql;
if (!mysql_init(&mysql))
{
fprintf(stderr, "mysql_init failed\n");
}
else if (!mysql_real_connect(&mysql,
"localhost", "root", "", "qsbr", 0, "",
0) )
{
fprintf(stderr, "Database connect failed\n");
fprintf(stderr, "%s (%d)\n", mysql_error(&mysql),
mysql_errno(&mysql));
}
#if 0
else
{
int error = mysql_query(&mysql,
"select * from packet where date_add(timestamp, "
"interval 15 second) < now() order by priority
desc, timestamp;");
fprintf(stdout, "error = %d\n", error);
}
#endif
return(0);
}
Error 261 on QNX means "connection refused". Try specifying the socket path 
explicitly when calling mysql_real_connect() after you have verified that the 
server is actually running.

--
Sasha Pachev
Create online surveys at http://www.surveyz.com/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Unix Timestamps

[Jeremy]

I've been looking though "MySQL" by Paul DuBois (second edition) and the 
MySQL docs at http://www.mysql.com/doc/en/Date_and_time_functions.html 
(and others).

I'm looking for a MySQL function that returns the unix time (second 
since epoch), and there doesn't seem to be one.

Using a PHP script, what's the best way to store the current time (for 
example, when a message board post is made) so it can be easily read 
back and formatted later? I need to be able to use either MySQL's 
fucntions or the PHP date functions to format the date like "Tuesday, 
Feb 17, 2004 9:18:30pm".

Thanks..

Regards,
 Jeremy
--

~
Jeremy Booker - Owner / Webmaster
JTech Web Systems
www.JTechWebSystems.com
~
"Therefore do not worry about tomorrow, for tomorrow will worry about
itself. Each day has enough trouble of its own." -Mathew 5:34


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

duplicating records

[Ari Denison]

I have a need to duplicate individual records in a table with a large 
number of fields.

This is not part of a backup or restore process. I simply need to be 
able to duplicate a record or two on occasion.

 Is there an efficient way to do this?

I obviously don't want to duplicate keyfields, just the data.

Many thanks,
Ari
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: accumulating query

[Timothy Smith]

> I have reproduced this on two Windows 2000 boxes. What's going on? Why do 
> I keep adding on to the result set? When I drop back down to 4.0.13 this 
> problem disappears! Is this a bug in 4.0.18?

Bruce, this is curious.  To help us reproduce this, can you either
upload the table in question to ftp.mysql.com:/pub/mysql/Incoming,
along with a text file that describes the problem; or create a
self-contained test case (including CREATE TABLE, INSERT and SELECT
statements) that reliably causes this behavior for you?

If you can do that, submit a bug report via bugs.mysql.com.  If this
won't work for you, let us know so we can pursue this further.

Tim
-- 
-- MySQL Users Conference   April 14-16, 2004   http://www.mysql.com/uc2004/
--
-- Tim Smith, Support Engineer  Cortez, Colorado, USA
-- MySQL AB, www.mysql.com  Office: 1-970-739-3489

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

newbie q. db getting slow

[j. roughan]

I am using snort with mysql and am noticing
significant increases in response time, I think this
is due to data growth but I am still reading the docs.

Are there a few commands to check the db, and possibly
improve performance response time? Any pointer to
admin commands section welcome.

__
Do you Yahoo!?
Yahoo! Finance: Get your refund fast by filing online.
http://taxes.yahoo.com/filing.html

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: tmpdir "round-robin" algorithm?

[Jeremy Zawodny]

On Tue, Feb 17, 2004 at 01:18:45PM -0800, lee wrote:
> We are trying to do some capacity planning, and we need to know how MySQL
> allocates the the filespace in the directories set by the tmpdir
> configuration parameter.
> 
> The manual simply states: 
> "...It may be useful if your default /tmp directory resides on a partition
> that is too small to hold temporary tables. Starting from MySQL 4.1, this
> option accepts several paths that are used in round-robin fashion."
> 
> How is the work spread around amongst the different tmpdir 'segments' ( the
> directories enumerated by ':' in the tmpdir config. variable)? The manual
> says the directories are used in a "round-robin" fashion. 

I believe it's the first.  Each new need for a temp file picks from
the round-robin list.

Jeremy
-- 
Jeremy D. Zawodny |  Perl, Web, MySQL, Linux Magazine, Yahoo!
<[EMAIL PROTECTED]>  |  http://jeremy.zawodny.com/

MySQL 4.0.15-Yahoo-SMP: up 156 days, processed 1,863,165,389 queries (137/sec. avg)

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

accumulating query

[Bruce Altner]

Hello.

I just installed 4.0.18-nt and I am getting very weird results. I asked 
this before but the example was too complicated to follow. Sorry about 
that. Here it is in a much simpler form: For example after restarting 
mysqld I run this query:

select location from vevents where event_id=15 UNION ALL  select location 
from Vevents where event_id=20;

which gives me this:

+--+
| location |
+--+
| NBC-TV   |
| House|
+--+
2 rows in set (0.01 sec)
running it again gives me this:

+--+
| location |
+--+
| NBC-TV   |
| House|
| NBC-TV   |
| House|
+--+
4 rows in set (0.00 sec)
and again gives me this:

+--+
| location |
+--+
| NBC-TV   |
| House|
| NBC-TV   |
| House|
| NBC-TV   |
| House|
+--+
6 rows in set (0.02 sec)
and so on for as long as I care to entertain myself in this way.  If I 
restart mysql I get back to 2 rows returned.

I have reproduced this on two Windows 2000 boxes. What's going on? Why do I 
keep adding on to the result set? When I drop back down to 4.0.13 this 
problem disappears! Is this a bug in 4.0.18?

Thanks,
Bruce

Re: Not using index?

[Jigal van Hemert]

From: "Keith Thompson" <[EMAIL PROTECTED]>

> select somefields from t1, t2 where t1.id = t2.id and t2.x2 =
somenumber;

Try:
select somefields from t1 join t2 on t1.id = t2.id and t2.x2 = somenumber;

Also take a look at the estimate number of records for each step in the
explain output.
It's often faster to have a low number of records for each step than to have
an optimal join type.

The optimizer in MySQL needs help a lot of times.

I've found that I sometimes need to use IGNORE_INDEX or even FORCE_INDEX to
get MySQL to use the right index.

Regards, Jigal.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: AW: AW: Pending results blocking mysql-server

[Stefan Hinz]

Klaus,

>>in this case, I suspect, is that the disconnect happens in the
>> pre-data-sending stage, which is possibly quite lengthy.

> Exactly ! It takes hours till these results are erased and i even don't know
> where the period when they are erased come from.
> I still hope this is set in a server-variable.

In addition to what Sasha suggested (max_join_size), you might want to
have a look at query_prealloc_size and range_alloc_block_size.
Prerequisites are that you're using MySQL 4.0.16 or later, and that
you have enough memory for tuning those system variables.

http://www.mysql.com/doc/en/SHOW_VARIABLES.html

>>I would also check to see if there might be some simple fixes 
>>, such as adding appropriate keys to the tables, that would optimize the
>> slow queries.

> The problem is, that i can't prevent queries like
> SELECT * FROM  WHERE  LIKE 'a%'
> where an index will not help on a Table > 100.000.000 records.

I wouldn't say "it will not help", but I admit that "it might not
help" ;-). I've even seen significant performance improvements by
adding indexes for queries like "LIKE '%...'", so it might be worth a
try. Be aware, though, that creating an index on some 100 million rows
will take some time by itself.

> It's not that we have to answer hese queries (therefore the timeout), but
> it's impossible to prevent dumm users.

I don't know if that might help in your situation, but you should
consider limiting user resources:

http://www.mysql.com/doc/en/User_resources.html

>>The solution I would propose is to maintain a separate connection on which
> the KILL 
>>commands  will be sent for the long queries. 

> I did this (by reading processlist and kiliing any process that 
> lasts longer then 20 seconds) and it works fine, but it is not such a
> 'proper' solution.
> I still hope there is a way to get that by setting up the server.

If I'm not missing something obvious, there's no single system
variable that you could set for a "proper" solution. More probably,
you will have to set a number of variables affecting performance,
depending on your (hardware and software) environment.

If you can connect to your MySQL server from a Windows or a Linux box
(and if you're using MySQL 4.0 or later), you should try out MySQL
Administrator. That program allows you to view system variables that
affect performance in a pretty intuitive way (just click Health,
System Variables, General, Performance). Most of those variables can
be set at server runtime by double-clicking them in MySQL
Administrator.

http://www.mysql.com/products/administrator/index.html

Regards,
--
  Stefan Hinz <[EMAIL PROTECTED]>
  iConnect GmbH 
  Heesestr. 6, 12169 Berlin (Germany)
  Telefon: +49 30 7970948-0  Fax: +49 30 7970948-3

[filter fodder: sql, mysql, query]


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Can't connect using C API

[Cira Account]

I apologize if the listings below are badly
formatted.  I am posting this from a webmail client
and it has very short line lengths.

I am using MySQL 4.0.17 on QNX 6.2.0.  I can connect
by typing "mysql -u root" from the command line. 
However, I get:

Database connect failed
Can't connect to local MySQL server through
socket '' (261) (2002)

when I run this program:

#include 
#include 

int main(int argc, char *argv[])
{
MYSQL   mysql;

if (!mysql_init(&mysql))
{
fprintf(stderr, "mysql_init failed\n");
}
else if (!mysql_real_connect(&mysql,
"localhost", "root", "", "qsbr", 0, "",
0) )
{
fprintf(stderr, "Database connect failed\n");
fprintf(stderr, "%s (%d)\n", mysql_error(&mysql),
mysql_errno(&mysql));
}
#if 0
else
{
int error = mysql_query(&mysql,
"select * from packet where date_add(timestamp, "
"interval 15 second) < now() order by priority
desc, timestamp;");
fprintf(stdout, "error = %d\n", error);
}
#endif
return(0);
}



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

The command line, mysql and HTML Tidy

[Gilbert Wilson]

Hi all,

I have some questions I thought people might be able to enlighten me on. 
 I have a database of website content that I have inherited that is 
full of standards-killing html (about 500 website pages; long story).  I 
would like to change that, but its a job that is a little too big for 
one person.  That's why I thought I might be able to enlist the help of 
html tidy, the nifty command line tool.

Under normal circumstances html tidy would be utilized in a loop to go 
though a directory.  Like so:

#! /bin/sh
for a in 'ls -l *.html'
do
cp $a $a.bak
tidy $a > $a.new
mv $a.new $a
echo "$a is done!"
done
But since all the content is in MySQL, and I'm a newbie to such things 
as the command line, MySQL, and PHP I'm pushing my limits here (which 
is, of course a good thing!).  I was curious if anyone could help me out 
by pointing me in the right direction on how I get the data out of the 
database (and back in) so that Tidy can work on it -- or get HTML Tidy 
into the database.

Thanks!

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: OR in query doesn't use keys?

[Bill Marrs]

At 01:54 PM 2/17/2004, walt wrote:
>> (SELECT FROM table WHERE condition1) UNION (SELECT FROM table WHERE
>> condition2);
Ah, interesting... I'll play around with UNION to see if that will do the 
trick for me.

Right off the bat, I am able to get a fast query with it, but the output 
isn't quite, right (though, I can probably massage it to be workable).

mysql> (SELECT count(*) FROM Trades WHERE User1 = 79909) UNION (SELECT 
count(*) FROM Trades WHERE User2 = 79909);
+--+
| count(*) |
+--+
|   22 |
|   33 |
+--+
2 rows in set (0.01 sec)

Thanks.

-bill

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

tmpdir "round-robin" algorithm?

[lee]

We are trying to do some capacity planning, and we need to know how MySQL
allocates the the filespace in the directories set by the tmpdir
configuration parameter.

The manual simply states: 
"...It may be useful if your default /tmp directory resides on a partition
that is too small to hold temporary tables. Starting from MySQL 4.1, this
option accepts several paths that are used in round-robin fashion."

How is the work spread around amongst the different tmpdir 'segments' ( the
directories enumerated by ':' in the tmpdir config. variable)? The manual
says the directories are used in a "round-robin" fashion. 

Do this mean each new job requiring the use of temp space (spilling out of a
sort heap, I assume?) goes to a different tmpdir 'segment' directory in a
round-robin fashion? Or does it mean that the tmpdir 'segments' are assigned
round-robin as they fill up? 

Also, we'd like to make a RAMDISK the default directory for most of the
smaller tmpdir work, but the big jobs will spill out to other disks. Is this
determined by which dir is listed 1st? Or is the default always /tmp? And
will it allocate work files to the directories in the order listed in tmpdir?
Or are all the directories functionally equal?

Thanks in advance!

/lee


=
==
/lee
+---+
| This concludes our broadcast day|
+---+

__
Do you Yahoo!?
Yahoo! Finance: Get your refund fast by filing online.
http://taxes.yahoo.com/filing.html

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

mysqld_multi

[Ansari, Raza \(GEI, GEFA\)]

I am trying to execute mysqld_multi script in unix. But it's giving me following error

"WARNING! my_print_defaults command not found. Please make sure you have this command 
available and in your path. The command is available from the latest MySQL 
distribution."

I have the laterst MySQL distribution installed on my machine. If anyone seen this 
error before, please let me know the fix.

Thanks
Raza

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Installation Confusion in Linux

[Ross O]

[EMAIL PROTECTED] root]# rpm -qvl MySQL-server-4.0.18-0
-rwxr-xr-x1 rootroot 5575 Feb 12
09:02 /etc/init.d/mysql
-rw-r--r--1 rootroot  657 Feb 12
09:02 /etc/logrotate.d/mysql
-rwxr-xr-x1 rootroot   900144 Feb 12
09:02 /usr/bin/isamchk

-rwxr-xr-x1 rootroot   850824 Feb 12
09:02 /usr/bin/isamlog

-rwxr-xr-x1 rootroot   777428 Feb 12
09:02 /usr/bin/my_print_defaults

-rwxr-xr-x1 rootroot   874860 Feb 12
09:02 /usr/bin/myisam_ftdump

-rwxr-xr-x1 rootroot  1011640 Feb 12
09:02 /usr/bin/myisamchk

-rwxr-xr-x1 rootroot   919496 Feb 12
09:02 /usr/bin/myisamlog

-rwxr-xr-x1 rootroot   942548 Feb 12
09:02 /usr/bin/myisampack

-rwxr-xr-x1 rootroot 2977 Feb 12
09:02 /usr/bin/mysql_convert_table_format

-rwxr-xr-x1 rootroot10419 Feb 12
09:02 /usr/bin/mysql_explain_log

-rwxr-xr-x1 rootroot  483 Feb 12
09:02 /usr/bin/mysql_fix_extensions

-rwxr-xr-x0 rootroot 6927 Feb 12
09:02 /usr/bin/mysql_fix_privilege_tables

-rwxr-xr-x1 rootroot14289 Feb 12
09:02 /usr/bin/mysql_install_db

-rwxr-xr-x1 rootroot 6308 Feb 12
09:02 /usr/bin/mysql_secure_installation

-rwxr-xr-x1 rootroot17275 Feb 12
09:02 /usr/bin/mysql_setpermission

-rwxr-xr-x1 rootroot 3123 Feb 12
09:02 /usr/bin/mysql_zap

-rwxr-xr-x0 rootroot10317 Feb 12
09:02 /usr/bin/mysqlbug

-rwxr-xr-x1 rootroot18919 Feb 12
09:02 /usr/bin/mysqld_multi

-rwxr-xr-x1 rootroot10714 Feb 12
09:02 /usr/bin/mysqld_safe

-rwxr-xr-x1 rootroot31748 Feb 12
09:02 /usr/bin/mysqlhotcopy

-rwxr-xr-x1 rootroot   744760 Feb 12
09:02 /usr/bin/mysqltest

-rwxr-xr-x1 rootroot   863104 Feb 12
09:02 /usr/bin/pack_isam

-rwxr-xr-x1 rootroot   771724 Feb 12
09:02 /usr/bin/perror

-rwxr-xr-x1 rootroot   779312 Feb 12
09:02 /usr/bin/replace

-rwxr-xr-x1 rootroot   770452 Feb 12
09:02 /usr/bin/resolve_stack_dump

-rwxr-xr-x1 rootroot   771336 Feb 12
09:02 /usr/bin/resolveip

lrwxr-xr-x1 rootroot   13 Feb 12
09:02 /usr/bin/safe_mysqld -> ./mysqld_safe

-rw-r--r--1 rootroot   413255 Feb 12
09:02 /usr/lib/mysql/mysqld.sym

-rwxr-xr-x1 rootroot  4062636 Feb 12
09:02 /usr/sbin/mysqld

lrwxr-xr-x1 rootroot   17 Feb 12
09:02 /usr/sbin/rcmysql -> /etc/init.d/mysql

drwxr-xr-x2 rootroot0 Feb 12
09:02 /usr/share/doc/packages/MySQL-server

-rw-r--r--1 rootroot19106 Feb 10
10:18 /usr/share/doc/packages/MySQL-server/COPYING

-rw-r--r--1 rootroot 1937 Feb 10
10:15 /usr/share/doc/packages/MySQL-server/README

-rw-r--r--1 rootroot  3475464 Feb 10
10:18 /usr/share/doc/packages/MySQL-server/manual.html

-rw-r--r--1 rootroot  117 Feb 10
10:39 /usr/share/doc/packages/MySQL-server/manual.ps

-rw-r--r--1 rootroot  3175929 Feb 10
10:16 /usr/share/doc/packages/MySQL-server/manual.texi

-rw-r--r--1 rootroot  3019963 Feb 10
10:18 /usr/share/doc/packages/MySQL-server/manual.txt

-rw-r--r--1 rootroot   134754 Feb 10
10:18
/usr/share/doc/packages/MySQL-server/manual_toc.html

-rw-r--r--1 rootroot 4866 Feb 12
09:02 /usr/share/doc/packages/MySQL-server/my-huge.cnf

-rw-r--r--1 rootroot 4842 Feb 12
09:02
/usr/share/doc/packages/MySQL-server/my-large.cnf

-rw-r--r--1 rootroot 4825 Feb 12
09:02
/usr/share/doc/packages/MySQL-server/my-medium.cnf

-rw-r--r--1 rootroot 2410 Feb 12
09:02
/usr/share/doc/packages/MySQL-server/my-small.cnf

-rw-r--r--1 rootroot   905192 Feb 12
09:02 /usr/share/info/mysql.info.gz

-rw-r--r--1 rootman  1780 Feb 12
09:02 /usr/share/man/man1/isamchk.1.gz

-rw-r--r--1 rootman   975 Feb 12
09:02 /usr/share/man/man1/isamlog.1.gz

-rw-r--r--1 rootman   664 Feb 12
09:02
/usr/share/man/man1/mysql_fix_privilege_tables.1.gz

-rw-r--r--1 rootman   765 Feb 12
09:02 /usr/share/man/man1/mysql_zap.1.gz

-rw-r--r--1 rootman  4154 Feb 12
09:02 /usr/share/man/man1/mysqld.1.gz

-rw-r--r--1 rootman  1277 Feb 12
09:02 /usr/share/man/man1/mysqld_multi.1.gz

-rw-r--r--1 rootman  1047 Feb 12
09:02 /usr/share/man/man1/mysqld_safe.1.gz

-rw-r--r--1 rootman   855 Feb 12
09:02 /usr/share/man/man1/perror.1.gz

-rw-r--r--0 rootman   961 Feb 12
09:02 /usr/share/man/man1/replace.1.gz

drwxr-xr-x2 rootroot0 Feb 12
09:02 /usr/share/mysql

-rwxr-xr-x1 roo

RE: MySQL 4.0.17 on SCO OpenServer 5.0.6

[Boyd Gerber]

On Wed, 11 Feb 2004, Sean Flynn wrote:
> Humble apologies for the broken link. Google on the filename; it seems
> popular enough that it should be out there somewhere. Particularly look
> at the MySQL site; there might've been a few links there.

The latest FSU-threads site is at

http://moss.csc.ncsu.edu/~mueller/pthreads/

ftp://ftp.zenez.com/pub/zenez/prgms/FSU-threads.tar.gz

and test version is at
ftp://ftp.zenez.com/pub/zenez/prgms/FSU-threadsa.tar.gz

> --Sean Flynn
>
> Stefaan Van Dooren <[EMAIL PROTECTED]> wrote:
> Thanx for the answer,
>
> But the link you gave me is broken :-(
> I have FSU-threads version 3.14 installed on that system.
>
>
> Stefaan
>
> -Original Message-
> From: Sean Flynn [mailto:[EMAIL PROTECTED]
> Sent: dinsdag 10 februari 2004 23:17
> To: Stefaan Van Dooren; [EMAIL PROTECTED]
> Subject: Re: MySQL 4.0.17 on SCO OpenServer 5.0.6
>
>
> Howdy,
>
> I'm something of a newbie, so (1)bear with me, and (2) my advice is nowhere
> near authoritative...
>
> ...but I did some googling, and I managed to track down someone with a
> vaguely similar problem. It would seem that that version of FSU pthreads
> hasn't implemented that symbol. Solution: find a way to upgrade it. I'm not
> sure if that upgrades along with gcc or not; however, the development page
> links to a version of pthreads for SCO; the latest one seems to be here.
> http://www.cs.wustl.edu/~schmidt/ACE_wrappers/FSU-threads.tar.gz .
> Hopefully, there will also be a readme in there somewhere to help out. Sorry
> I couldn't throw more your way.
>
> peace,
> Sean Flynn
>
>
> Stefaan Van Dooren wrote:
> Hi,
>
> I'm trying to compile MySQL 4.0.17 on SCO OpenServer 5.0.6, gcc version
> 2.95.3 20030406 (SCO/p4).
>
> I always get the following error :
>
> Undefined first referenced
> symbol in file
> pthread_key_delete libmysys.a(my_thr_init.o)
> ERROR: Symbol referencing errors. No output written to test_thr_alarm
> collect2: ld returned 1 exit status
> make[2]: *** [test_thr_alarm] Error 1
> make[2]: Leaving directory `/a/mysql-4.0.17/mysys'
> make[1]: *** [all-recursive] Error 1
> make[1]: Leaving directory `/a/mysql-4.0.17'
> make: *** [all] Error 2
>
> MySQL 4.0.16 compiles just fine on this system.
>
> This is the configure I use :
>
> CC=gcc CFLAGS="-O3 -mpentium" LDFLAGS=-static CXX=gcc \ CXXFLAGS="-O3
> -mpentium -felide-constructors" \ ./configure --prefix=/a/mysql \
> --enable-thread-safe-client --enable-local-infile \ --disable-shared
>
> All help welcome.
>
>
> Stefaan
>
>
>
>
> -BEGIN GEEK CODE BLOCK-
> Version: 3.1
> GCS/CM/G d+(--) s--:-- a--- C$ UL++> P+ L++>+
> E W++(+++) N+ !o K? w+() !O M++ !V PS+ PE
> Y+>++ PGP- t++ 5++ X+ R tv- b+++ DI+ D++ G e* h! r* y-
> -END GEEK CODE BLOCK-
>
> geekcode: http://www.geekcode.com/geek.html
> geekcode decoder: http://www.ebb.org/ungeek/
>
>
> Do you Yahoo!?
> Yahoo! Finance: Get your refund fast by filing online
>
>
> -BEGIN GEEK CODE BLOCK-
> Version: 3.1
> GCS/CM/G d+(--) s--:-- a--- C$ UL++> P+ L++>+
> E W++(+++) N+ !o K? w+() !O M++ !V PS+ PE
> Y+>++ PGP- t++ 5++ X+ R tv- b+++ DI+ D++ G e* h! r* y-
> -END GEEK CODE BLOCK-
>
> geekcode: http://www.geekcode.com/geek.html
> geekcode decoder: http://www.ebb.org/ungeek/
>
> -
> Do you Yahoo!?
> Yahoo! Finance: Get your refund fast by filing online

--
Boyd Gerber <[EMAIL PROTECTED]>
ZENEZ   1042 East Fort Union #135, Midvale Utah  84047

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: How To Write This SQL Statement

[Bernard Clement]

Assuming that the address field is of type char or varchar,

Assuming that the address field has a NULL default value the following should 
do it

select ... from ... wehre address is null;

In case that the address field has no default value the following should do 
it:
select ... from ... where address='';

If one or more of my assumptions are wrong PLS provide us with the structure 
of your table with "describe tables ..."

Bernard

On Tuesday 17 February 2004 14:16, Caroline Jen wrote:
> Please help.  What is the syntax of idenfying those
> who "do not" have addresses (in the address field) in
> the database?
>
> __
> Do you Yahoo!?
> Yahoo! Finance: Get your refund fast by filing online.
> http://taxes.yahoo.com/filing.html


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: MySQL 4.0.17 on SCO OpenServer 5.0.6

[Boyd Gerber]

On Tue, 10 Feb 2004, Stefaan Van Dooren wrote:
> I'm trying to compile MySQL 4.0.17 on SCO OpenServer 5.0.6, gcc version 2.95.3 
> 20030406 (SCO/p4).
>
> I always get the following error :
>
> Undefined   first referenced
> symbol  in file
> pthread_key_delete  libmysys.a(my_thr_init.o)
> ERROR: Symbol referencing errors. No output written to test_thr_alarm
> collect2: ld returned 1 exit status
> make[2]: *** [test_thr_alarm] Error 1
> make[2]: Leaving directory `/a/mysql-4.0.17/mysys'
> make[1]: *** [all-recursive] Error 1
> make[1]: Leaving directory `/a/mysql-4.0.17'
> make: *** [all] Error 2
>
> MySQL 4.0.16 compiles just fine on this system.
>
> This is the configure I use :
>
> CC=gcc CFLAGS="-O3 -mpentium" LDFLAGS=-static CXX=gcc \
> CXXFLAGS="-O3 -mpentium -felide-constructors" \
> ./configure --prefix=/a/mysql \
> --enable-thread-safe-client --enable-local-infile \
> --disable-shared
>
> All help welcome.

There is a sample FSU-threads program on ftp.zenez.com in the
pub/zenez/prgms directory that allows this to compile it is
FSU-threads-3.14a.tar.gz


--
Boyd Gerber <[EMAIL PROTECTED]>
ZENEZ   1042 East Fort Union #135, Midvale Utah  84047

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Urgent help needed - SCO Unix binary

[Boyd Gerber]

On Tue, 10 Feb 2004 [EMAIL PROTECTED] wrote:
> Hi,
>
> I could not find the binary for SCO Unixware (Intel based) in download
> section. I suppose mysql supports this environment too. May I know as to
> where can I find this binary. Do I need to build this myself?

There are some version on ftp.zenez.com in the pub/zenez/prgms directory.
You may want to try one of them.

--
Boyd Gerber <[EMAIL PROTECTED]>
ZENEZ   1042 East Fort Union #135, Midvale Utah  84047

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

The ID... messages are the Bagle.B worm

[Heikki Tuuri]

Hi!

http://www.f-secure.com/v-descs/bagle_b.shtml

A new Bagle.B worm epidemy is apparently causing the messages ID  sent to the MySQL mailing list by my name.

The worm has probably infected the computer of someone following this
mailing list, and the worm has scanned my email address from the hard disk
of the computer. The worm then uses my email address as the return email
address.

If someone has plenty of spare time, he could try to decipher the return
path of those messages, and inform the ISP(s) of the owner(s) of the
infected computer(s).

Regards,

Heikki


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Not using index?

[Sasha Pachev]

Keith Thompson wrote:
I've got a query that's not using an index and I don't understand why.
Here's a stripped down version of what the tables look like:
create table t1 (
   id int(10) unsigned not null auto_increment,
   x1 date not null,
   ... 8 other small, fixed fields
   primary key (id),
   key search_x1 (x1)
) type=innodb;
create table t2 (
   id int(10) unsigned not null,
   x2 bigint(20) unsigned default null,
   ... 10 other small, fixed fields
   key join_t1 (id),
   key search_x2 (x2)
) type=innodb;
Table t1 has about 60 million rows and t2 about 70 million.  Cardinality
for x1 and x2 values is high, with no more than maybe 10,000 hits for
x2 values and 20,000 for x1 values.
These queries run very quickly (usually subsecond):

select somefields from t1 where x1 = somedate
select somefields from t2 where x2 = somenumber
select somefields from t1, t2 where t1.id = t2.id and t1.x1 = somedate
This query runs painfully slow (usually several minutes):

select somefields from t1, t2 where t1.id = t2.id and t2.x2 = somenumber;

Using EXPLAIN, the first three queries above all report "using where; using
index" for search_x1 or search_x2, whereas this last query only reports
"using where".  Why would this query not use the search_x2 index?
Suggestions?
Looks like an optimizer bug, very possibly InnoDB specific. Try ALTER TABLE 
TYPE=MyISAM for the affected tables to see if it makes a difference - if it 
does, this enough evidence that it's a bug, and Heikki (the InnoDB developer) 
will be very interested in fixing it.

--
Sasha Pachev
Create online surveys at http://www.surveyz.com/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Installation Confusion in Linux

[walt]

Ross,
rpm -qlp package_name.rpm will list all the files in an rpm for you. You
may
need to install the client rpm as well.

walt


Ross O wrote:
> 
> I was able to successfully install MySQL server 4
> under windows, but am a little confused about doing it
> through linux.
> 
> My main confusion is where the install goes? Im using
> the rpm, and from my personal directory where i
> downloaded the rpm to, i issue a rpm -i
> MySQL-server-...rpm command and it goes through its
> deal. The very first thing I notice is it says is to
> execute /usr/bin/mysqladmin -u root... to set the
> passwords. Well there are about a dozen mysql scripts
> in that directory but no mysqladmin, so immediately Im
> thinking i have a problem or did something wrong. Next
> it says the default directory for the install is
> /usr/local/mysql unless i specified otherwise. I did
> not and that directory does not exist.
> 
> so what happened? i downloaded the rpm off the mysql
> site and all i did was run it. didnt touch anything
> else. it installs the mysql service fine, and it puts
> all its db files in the /var/lib directory like it
> says it will, but it sure seems like im missing some
> other critical files?
> 
> __
> Do you Yahoo!?
> Yahoo! Finance: Get your refund fast by filing online.
> http://taxes.yahoo.com/filing.html
> 
> --
> 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: Installation Confusion in Linux

[Matt Silva]

This will list all the files in the package and where they are located.

rpm -qpl .rpm

or from the rpm database

rpm -ql 

Matt

Ross O wrote:

I was able to successfully install MySQL server 4
under windows, but am a little confused about doing it
through linux.  

My main confusion is where the install goes? Im using
the rpm, and from my personal directory where i
downloaded the rpm to, i issue a rpm -i
MySQL-server-...rpm command and it goes through its
deal. The very first thing I notice is it says is to
execute /usr/bin/mysqladmin -u root... to set the
passwords. Well there are about a dozen mysql scripts
in that directory but no mysqladmin, so immediately Im
thinking i have a problem or did something wrong. Next
it says the default directory for the install is
/usr/local/mysql unless i specified otherwise. I did
not and that directory does not exist. 

so what happened? i downloaded the rpm off the mysql
site and all i did was run it. didnt touch anything
else. it installs the mysql service fine, and it puts
all its db files in the /var/lib directory like it
says it will, but it sure seems like im missing some
other critical files?
__
Do you Yahoo!?
Yahoo! Finance: Get your refund fast by filing online.
http://taxes.yahoo.com/filing.html
 

--
Matt Silva
Empower Software Technologies, LLC
27851 Bradley Rd. Suite 120
Sun City, CA 92586
PH: (909) 672-6257
WB: www.storagecommander.com
EM: [EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: How do I unsubscribe

[Neil Shafranski]

Yeah but it doesn't work. I unsubscribed 3 months ago

-Original Message-
From: Mike Johnson [mailto:[EMAIL PROTECTED]
Sent: Tuesday, February 17, 2004 2:03 PM
To: Tim V; [EMAIL PROTECTED]
Subject: RE: How do I unsubscribe


From: Tim V [mailto:[EMAIL PROTECTED]

> How do I unsubscribe?


This message should have a "To unsubscribe" link at the bottom (if you
haven't already noticed it on every other message you receive).


--
Mike Johnson
Web Developer
Smarter Living, Inc.
phone (617) 886-5539

--
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: what can i change in my.cnf/mysqld to get better perfomance

[Donny Simonton]

The biggest problem I see is that you are using a text field and you don't
have an index on it anyway.  Maybe you should use a different field type and
index it.  That should make it a little bit faster.

Donny

> -Original Message-
> From: Lancashire, Pete [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, February 17, 2004 1:12 PM
> To: [EMAIL PROTECTED]
> Cc: [EMAIL PROTECTED]
> Subject: RE: what can i change in my.cnf/mysqld to get better perfomance
>
> no swapping so far
>
> I can see where I'm 'asking for' more then 8 GB, but for testing,
> I gambling that the total amount of test data is less
> then 8GB. also, i'm currently only doing tests with only one query at
> a time.
>
> one thing i've notices is the memory being consumed by mysqld has
> so far not gone above 1 GB (both resident, and requested).
>
> -pete
>
>
> -Original Message-
> From: walt [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, February 17, 2004 10:47 AM
> To: Lancashire, Pete
> Cc: [EMAIL PROTECTED]
> Subject: Re: what can i change in my.cnf/mysqld to get better perfomance
>
>
> Pete,
> I was just glancing at this and it looks like you have about 13.5GB of
> memory allocated on a 8GB machine (4096+4096+512+4096+1024). Are you
> swapping badly??
>
> walt
>
> "Lancashire, Pete" wrote:
> >
> > I'm doing some tests on a small Sun server, a 280R to demo MySQL vs.
> > Oracle. For the demo I using a subset of a test table.
> >
> > The destination server will be a Sun 880 with 8 1.2 GHz CPU's, 16 GB
> > RAM, Sun T3+ RAID Array.
> >
> > Any suggestions on settings or other changes would be helpful.
> >
> > Also what would be the best variables/settings to compile a binary for
>
> > a server of this type ? Gcc 3.3.2, Solaris 9.
> >
> > number of rows is only 10,735,291
> >
> > MYD is 6.7 GBytes, and MYI is 1.4 GBytes.
> >
> > This table is a subset of the real table of about 120 Gb.
> >
> > the test/lab server is a small sun box with 2 each 900 MHz CPUs, and 8
>
> > GB of RAM.
> >
> > I'm trying to avoid using disk until I get a better performance disk
> > subsystem.
> >
> > The version is the website binary, solaris 64 bit.
> >
> > the setting i currently have
> >
> > [mysqld]
> > set-variable = myisam_sort_buffer_size=4096M
> > set-variable = sort_buffer_size=4096M
> > set-variable = read_buffer_size=512M
> > set-variable = key_buffer_size=4096M
> > set-variable = table_cache=1024
> >
> > tmp_table_size  = 1024M
> > max_heap_table_size = 1024M
> >
> > a search on text fields such as
> >
> > select substring_index(request_uri,'/',-1) jsp, count(*) from
> > apache_jan where request_is_jsp = 1 group by jsp;
> >
> > takes 8 1/2 minutes.
> >
> > A rebuild of the indexes takes over 8 hours.
> >
> > the table format
> >
> > CREATE TABLE `apache_jan` (
> >   `dt` datetime NOT NULL default '-00-00 00:00:00',
> >   `hostname` varchar(125) default NULL,
> >   `ip` varchar(15) default NULL,
> >   `ipn` int(10) unsigned default NULL,
> >   `user` varchar(125) default NULL,
> >   `timetaken` float default NULL,
> >   `auth` varchar(255) default NULL,
> >   `request` text,
> >   `request_type` varchar(255) default NULL,
> >   `request_uri` text,
> >   `request_file` varchar(255) default NULL,
> >   `request_is_jsp` tinyint(1) default NULL,
> >   `request_is_servlet` tinyint(1) default NULL,
> >   `request_query` text,
> >   `request_version` varchar(255) default NULL,
> >   `status` smallint(3) unsigned default NULL,
> >   `status_orig` smallint(3) unsigned default NULL,
> >   `bytes` int(11) default NULL,
> >   `bytes_i` int(11) default NULL,
> >   `bytes_o` int(11) default NULL,
> >   `header` text,
> >   `header_uri` text,
> >   `header_file` varchar(255) default NULL,
> >   `header_is_jsp` tinyint(1) default NULL,
> >   `useragent` varchar(255) default NULL,
> >   `biz_obj` varchar(255) default NULL,
> >   KEY `dt` (`dt`),
> >   KEY `hostname` (`hostname`),
> >   KEY `ip` (`ip`),
> >   KEY `status` (`status`),
> >   KEY `request_is_jsp` (`request_is_jsp`),
> >   KEY `request_is_servlet` (`request_is_servlet`),
> >   KEY `request_file` (`request_file`),
> >   KEY `header_file` (`header_file`),
> >   KEY `header_is_jsp` (`header_is_jsp`),
> >   KEY `ipn` (`ipn`)
> > ) TYPE=MyISAM DEFAULT CHARSET=latin1 MAX_ROWS=2
> > AVG_ROW_LENGTH=600 INDEX DIRECTORY='/DISK1/MYSQL/pdm/'
> >
> > --
> > 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: AW: AW: Pending results blocking mysql-server

[Sasha Pachev]

The problem is, that i can't prevent queries like
SELECT * FROM  WHERE  LIKE 'a%'
where an index will not help on a Table > 100.000.000 records.
It's not that we have to answer hese queries (therefore the timeout), but
it's impossible to prevent dumm users.
Run EXPLAIN on the untrusted queries first before executing them or set 
max_join_size low enough.

--
Sasha Pachev
Create online surveys at http://www.surveyz.com/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

How To Write This SQL Statement

[Caroline Jen]

Please help.  What is the syntax of idenfying those
who "do not" have addresses (in the address field) in
the database?

__
Do you Yahoo!?
Yahoo! Finance: Get your refund fast by filing online.
http://taxes.yahoo.com/filing.html

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: [Slightly OT] Tricky SQL Query

[Garth Webb]

On Tue, 2004-02-17 at 11:00, A. Clausen wrote:
> I am trying to construct what is proving to be a somewhat tricky SQL query.
> I have a database in which wireless activity data is dumped.  The following
> is a boiled down version of what the table and the contents may look like:
> 
> Date,Unit,InputOctets,OutputOctets,AccountingID
> 2004-01-31 23:59:05,0E:3A:0F,10101,3949,SLAKRMMM
> 2004-02-01 00:04:07,0E:3A:0F,13843,4192,SLAKRMMM
> 2004-02-01 00:09:10,0E:3A:0F,53233,9021,SLAKRMMM
> 2004-02-01 00:14:08,0E:3A:0F,2383,109,NX0ADFF
> ...
> 2004-02-29 23:59:15,0E:3A:0F,481234,18233,LAQ5YN3
[snip]
> What I need to do is to find out just how much traffic that unit has done in
> the month of February.  I realize I will have to do one query to discover
> the byte counts for the last record in January, but beyond that I'm stumped.

What about this:

select Unit, sum(InputOctets), sum(OutputOctets)
from  your_table
where date between '2004-02-01 00:00:00' and
   '2004-02-29 23:59:55'
group by Unit;

-- 

 |- Garth Webb   -|
 |- [EMAIL PROTECTED] -|


signature.asc
Description: This is a digitally signed message part

Re: what can i change in my.cnf/mysqld to get better perfomance

[Sasha Pachev]

Lancashire, Pete wrote:
I'm doing some tests on a small Sun server, a 280R to demo
MySQL vs. Oracle. For the demo I using a subset of a test table.
The destination server will be a Sun 880 with 8 1.2 GHz CPU's,
16 GB RAM, Sun T3+ RAID Array.
Any suggestions on settings or other changes would be helpful.

Also what would be the best variables/settings to compile a binary
for a server of this type ? Gcc 3.3.2, Solaris 9.
number of rows is only 10,735,291

MYD is 6.7 GBytes, and MYI is 1.4 GBytes.

This table is a subset of the real table of about 120 Gb.

the test/lab server is a small sun box with 2 each 900 MHz CPUs, and 8
GB of RAM.
I'm trying to avoid using disk until I get a better performance disk
subsystem.
The version is the website binary, solaris 64 bit.

the setting i currently have

[mysqld]
set-variable = myisam_sort_buffer_size=4096M
set-variable = sort_buffer_size=4096M
set-variable = read_buffer_size=512M
set-variable = key_buffer_size=4096M
set-variable = table_cache=1024
tmp_table_size  = 1024M
max_heap_table_size = 1024M
a search on text fields such as

select substring_index(request_uri,'/',-1) jsp, count(*) from apache_jan
where request_is_jsp = 1 group by jsp;
takes 8 1/2 minutes.
Pete:

I am afraid your question is somewhat like : "It takes really long to drive from 
Los Angeles to San Francisco going through Denver in my super speedy car. How 
can I make the car faster to cut the trip time?" Perhaps a better question to 
ask first is "Do I really have any business in Denver that I have to attend on 
my way to San Francisco?", and hope that the answer will be "no".

To apply the above to your case, if possible, rebuild the table to store the 
file extension ( I assume you can identify the type of request by extension), 
and the file stem (file name without extension) for each request. Then you can 
drop request_is_jsp and request_is_servet fields. Also, think about your other 
frequent queries to see if they can benefit from a different data layout, and 
consider using summary tables to avoid lengthy repeated group by queries.



--
Sasha Pachev
Create online surveys at http://www.surveyz.com/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Pending results blocking mysql-server

[Aftab Jahan Subedar]

I am interested to help, you can email me the call graph or source.
I nearly have nothing much to do ( no client, teching guiter now!!! ha 
ha). I am using the C API 100% flawlessly in my 
http://www.DhakaStockExchangeGame.com/
Still using it 



--

Aftab Jahan Subedar
CEO/Software Engineer
Subedar Technologies
Subedar Baag
Bibir Bagicha #1
North Jatrabari
Dhaka 1204
Bangladesh
http://www.SubedarTechnologies.com
http://www.DhakaStockExchangeGame.com/
http://www.CEOBangladesh.com/
http://www.NYSEGame.com
tel://+88027519050
EMail://[EMAIL PROTECTED] - Directly to my notebook
Stefan Hinz wrote:
Klaus,


we are porting an application from ORACLE to MySQL.
The application does lot of queries and has and times out a query, if no result is back
after 10 seconds.
It handles queries parrallel and rasies or lowers the amount of connection variable
(the programm is a server itself).
After a while, there is very big load on the MySQL-server (up to 90) and 'show 
processlist' shows very
many connections with queries that are in 'sending data'-state. But after they are 
timed out,
this results will never be fetched, but the threads for them are kept a very long time.
With lot of queries, it even happens, that a connection to the mysql-server is blocked.
i think it would be the best to get rid of hte pending results on the server, that are 
not
fetched after let's say 20 seconds.
Which variable do i need to set to achieve this (if possible).
The program uses the C-API. The serve is 4.017 with MyISAM-Tables on SUSE-LINUX SLES 
8.0,
Ext3-filesystem, a 3Ghz xeleron, 1.5G MEM.
Please help me, after i convienced my boss to use MySQL for this duty (which took 
about 18 month),
this might be very dangerous for my carreer.


Would it be possible to call mysql_free_result() from your
application? You might be able to set a "timer" in your app, calling
that function after, for example, 20 seconds.
http://www.mysql.com/doc/en/mysql_free_result.html

Regards,
--
  Stefan Hinz <[EMAIL PROTECTED]>
  iConnect GmbH 
  Heesestr. 6, 12169 Berlin (Germany)
  Telefon: +49 30 7970948-0  Fax: +49 30 7970948-3
[filter fodder: sql, mysql, query]






--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: what can i change in my.cnf/mysqld to get better perfomance

[Lancashire, Pete]

no swapping so far

I can see where I'm 'asking for' more then 8 GB, but for testing,
I gambling that the total amount of test data is less
then 8GB. also, i'm currently only doing tests with only one query at
a time.

one thing i've notices is the memory being consumed by mysqld has
so far not gone above 1 GB (both resident, and requested).

-pete


-Original Message-
From: walt [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, February 17, 2004 10:47 AM
To: Lancashire, Pete
Cc: [EMAIL PROTECTED]
Subject: Re: what can i change in my.cnf/mysqld to get better perfomance


Pete,
I was just glancing at this and it looks like you have about 13.5GB of
memory allocated on a 8GB machine (4096+4096+512+4096+1024). Are you
swapping badly??

walt

"Lancashire, Pete" wrote:
> 
> I'm doing some tests on a small Sun server, a 280R to demo MySQL vs. 
> Oracle. For the demo I using a subset of a test table.
> 
> The destination server will be a Sun 880 with 8 1.2 GHz CPU's, 16 GB 
> RAM, Sun T3+ RAID Array.
> 
> Any suggestions on settings or other changes would be helpful.
> 
> Also what would be the best variables/settings to compile a binary for

> a server of this type ? Gcc 3.3.2, Solaris 9.
> 
> number of rows is only 10,735,291
> 
> MYD is 6.7 GBytes, and MYI is 1.4 GBytes.
> 
> This table is a subset of the real table of about 120 Gb.
> 
> the test/lab server is a small sun box with 2 each 900 MHz CPUs, and 8

> GB of RAM.
> 
> I'm trying to avoid using disk until I get a better performance disk 
> subsystem.
> 
> The version is the website binary, solaris 64 bit.
> 
> the setting i currently have
> 
> [mysqld]
> set-variable = myisam_sort_buffer_size=4096M
> set-variable = sort_buffer_size=4096M
> set-variable = read_buffer_size=512M
> set-variable = key_buffer_size=4096M
> set-variable = table_cache=1024
> 
> tmp_table_size  = 1024M
> max_heap_table_size = 1024M
> 
> a search on text fields such as
> 
> select substring_index(request_uri,'/',-1) jsp, count(*) from 
> apache_jan where request_is_jsp = 1 group by jsp;
> 
> takes 8 1/2 minutes.
> 
> A rebuild of the indexes takes over 8 hours.
> 
> the table format
> 
> CREATE TABLE `apache_jan` (
>   `dt` datetime NOT NULL default '-00-00 00:00:00',
>   `hostname` varchar(125) default NULL,
>   `ip` varchar(15) default NULL,
>   `ipn` int(10) unsigned default NULL,
>   `user` varchar(125) default NULL,
>   `timetaken` float default NULL,
>   `auth` varchar(255) default NULL,
>   `request` text,
>   `request_type` varchar(255) default NULL,
>   `request_uri` text,
>   `request_file` varchar(255) default NULL,
>   `request_is_jsp` tinyint(1) default NULL,
>   `request_is_servlet` tinyint(1) default NULL,
>   `request_query` text,
>   `request_version` varchar(255) default NULL,
>   `status` smallint(3) unsigned default NULL,
>   `status_orig` smallint(3) unsigned default NULL,
>   `bytes` int(11) default NULL,
>   `bytes_i` int(11) default NULL,
>   `bytes_o` int(11) default NULL,
>   `header` text,
>   `header_uri` text,
>   `header_file` varchar(255) default NULL,
>   `header_is_jsp` tinyint(1) default NULL,
>   `useragent` varchar(255) default NULL,
>   `biz_obj` varchar(255) default NULL,
>   KEY `dt` (`dt`),
>   KEY `hostname` (`hostname`),
>   KEY `ip` (`ip`),
>   KEY `status` (`status`),
>   KEY `request_is_jsp` (`request_is_jsp`),
>   KEY `request_is_servlet` (`request_is_servlet`),
>   KEY `request_file` (`request_file`),
>   KEY `header_file` (`header_file`),
>   KEY `header_is_jsp` (`header_is_jsp`),
>   KEY `ipn` (`ipn`)
> ) TYPE=MyISAM DEFAULT CHARSET=latin1 MAX_ROWS=2 
> AVG_ROW_LENGTH=600 INDEX DIRECTORY='/DISK1/MYSQL/pdm/'
> 
> --
> 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: How do I unsubscribe

[Mike Johnson]

From: Tim V [mailto:[EMAIL PROTECTED]

> How do I unsubscribe?


This message should have a "To unsubscribe" link at the bottom (if you haven't already 
noticed it on every other message you receive).


-- 
Mike Johnson
Web Developer
Smarter Living, Inc.
phone (617) 886-5539

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Installation Confusion in Linux

[Ross O]

I was able to successfully install MySQL server 4
under windows, but am a little confused about doing it
through linux.  

My main confusion is where the install goes? Im using
the rpm, and from my personal directory where i
downloaded the rpm to, i issue a rpm -i
MySQL-server-...rpm command and it goes through its
deal. The very first thing I notice is it says is to
execute /usr/bin/mysqladmin -u root... to set the
passwords. Well there are about a dozen mysql scripts
in that directory but no mysqladmin, so immediately Im
thinking i have a problem or did something wrong. Next
it says the default directory for the install is
/usr/local/mysql unless i specified otherwise. I did
not and that directory does not exist. 

so what happened? i downloaded the rpm off the mysql
site and all i did was run it. didnt touch anything
else. it installs the mysql service fine, and it puts
all its db files in the /var/lib directory like it
says it will, but it sure seems like im missing some
other critical files?

__
Do you Yahoo!?
Yahoo! Finance: Get your refund fast by filing online.
http://taxes.yahoo.com/filing.html

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

[Slightly OT] Tricky SQL Query

[A. Clausen]

I am trying to construct what is proving to be a somewhat tricky SQL query.
I have a database in which wireless activity data is dumped.  The following
is a boiled down version of what the table and the contents may look like:

Date,Unit,InputOctets,OutputOctets,AccountingID
2004-01-31 23:59:05,0E:3A:0F,10101,3949,SLAKRMMM
2004-02-01 00:04:07,0E:3A:0F,13843,4192,SLAKRMMM
2004-02-01 00:09:10,0E:3A:0F,53233,9021,SLAKRMMM
2004-02-01 00:14:08,0E:3A:0F,2383,109,NX0ADFF
...
2004-02-29 23:59:15,0E:3A:0F,481234,18233,LAQ5YN3

This requires a bit of explanation.  The Unit is the actually wireless
modem, and the octet counts are the totals for the unit since it was last
rebooted (by adding the new byte activity since the last accounting data
five minutes before).  The problem comes when the unit is rebooted.  At that
point the counters start at 0 and a new accounting ID is written.

What I need to do is to find out just how much traffic that unit has done in
the month of February.  I realize I will have to do one query to discover
the byte counts for the last record in January, but beyond that I'm stumped.

-- 
Aaron Clausen


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

How do I unsubscribe

[Tim V]

How do I unsubscribe?



__
Do you Yahoo!?
Yahoo! Finance: Get your refund fast by filing online.
http://taxes.yahoo.com/filing.html

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Replication: Setting up a slave

[Pierre Luguern]

I run MySQL version 4.0.16 on my linux box.

My replication is working fine when I drop my database and import it on the
master server. It seems to me that configuration files for both master and
slave are OK. Privillege too.

I define a user with the following right's:
  * Reload
  *  File
  * Super
  * Execute
  * Replication client
  * Replication slave

Now, if want to set up a new slave following these steps:
* Start mysql on the slave
* Run the following command to set up my slave  LOAD DATA FROM MASTER;

I get  error code 1219 (check privillege).

I specify, I use InnodB scheme for my databases in my master configuration
file. I read that the LOAD MASTER FILE FROM MASTER command support will be
available for Innodb. Is this command available in my version of mysql (
4.0.16)

If yes ,what is going wrong ?
Thanks in advance.



Pierre LUGUERN
 IBM Business Consulting Services
 Tour Descartes
 2 avenue Gambetta - La Défense 5
 92066 Paris La Défense
 Ext: 01 49 05 55 63   Int: (33) 5563
 Fax: 01 49 05 81 18









--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: OR in query doesn't use keys?

[walt]

Bill,
Someone sent this too the list the other day.

>> MySQL's optimizer has a slight problem. OR queries cause it to get very
>> confused.
>>
>> Try the following to get the best performance:
>>
>> Rewrite SELECT FROM table WHERE (condition1) OR (condition2);
>>
>> As:
>>
>> (SELECT FROM table WHERE condition1) UNION (SELECT FROM table WHERE
>> condition2);

walt

Bill Marrs wrote:
> 
> I've noticed that If I use an OR in my query, mysql seems to choose not to
> use my indexes.  Though, it would seem to help (as, if I do the query in
> two steps, I can get faster results than as one query).
> 
> Is there some way I can convince mysql to use my keys with an OR, or
> perhaps another way to do queries to avoid OR, but still get an OR-like result?
> 
> Here's a simplified example (my actual case is more complicated and slower):
> 
> mysql> SELECT count(*) FROM Trades WHERE User1 = 79909;
> +--+
> | count(*) |
> +--+
> |   22 |
> +--+
> 1 row in set (0.00 sec)
> 
> mysql> SELECT count(*) FROM Trades WHERE User2 = 79909;
> +--+
> | count(*) |
> +--+
> |   33 |
> +--+
> 1 row in set (0.01 sec)
> 
> mysql> SELECT count(*) FROM Trades WHERE (User1 = 79909 OR User2 = 79909);
> +--+
> | count(*) |
> +--+
> |   55 |
> +--+
> 1 row in set (0.35 sec)
> 
> Note - the OR is slower, describe (below) even says that it doesn't use
> either key in this case.
> 
> mysql> describe SELECT count(*) FROM Trades WHERE (User1 = 79909 OR User2 =
> 79909);
> ++--+---+--+-+--++-+
> | table  | type | possible_keys | key  | key_len | ref  | rows   |
> Extra   |
> ++--+---+--+-+--++-+
> | Trades | ALL  | User1Key,User2Key | NULL |NULL | NULL | 100775 |
> Using where |
> ++--+---+--+-+--++-+
> 1 row in set (0.00 sec)
> 
> Here's the table:
> 
> CREATE TABLE Trades (
>UID int(10) unsigned NOT NULL auto_increment,
>User1 int(10) unsigned NOT NULL default '0',
>User2 int(10) unsigned NOT NULL default '0',
>PRIMARY KEY  (UID),
>KEY User1Key (User1),
>KEY User2Key (User2)
> ) TYPE=MyISAM PACK_KEYS=1;
> 
> Anyone know a trick to do OR queries faster?
> 
> Thanks in advance,
> 
> -bill
> 
> --
> 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 Memory

[Sasha Pachev]

Sp.Raja wrote:
Thanks for your lightening response.

Even I thought of this idea
 - Isolate pairs like 
   - my_malloc and my_free
   - Innodb pair: ut_malloc and ut_free

But if pointers are passed between InnoDB and MySQL or MySQL and clients, then the memory consumer should free it using my wrapper and not system defined free. 

Is there such communication or all pointers allocated by one modules stays with in it and gets freed by the module.
Innodb code should be independent of MySQL internal libraries and visa versa.

--
Sasha Pachev
Create online surveys at http://www.surveyz.com/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: what can i change in my.cnf/mysqld to get better perfomance

[walt]

Pete,
I was just glancing at this and it looks like you have about 13.5GB of
memory allocated on a 8GB machine (4096+4096+512+4096+1024).
Are you swapping badly??

walt

"Lancashire, Pete" wrote:
> 
> I'm doing some tests on a small Sun server, a 280R to demo
> MySQL vs. Oracle. For the demo I using a subset of a test table.
> 
> The destination server will be a Sun 880 with 8 1.2 GHz CPU's,
> 16 GB RAM, Sun T3+ RAID Array.
> 
> Any suggestions on settings or other changes would be helpful.
> 
> Also what would be the best variables/settings to compile a binary
> for a server of this type ? Gcc 3.3.2, Solaris 9.
> 
> number of rows is only 10,735,291
> 
> MYD is 6.7 GBytes, and MYI is 1.4 GBytes.
> 
> This table is a subset of the real table of about 120 Gb.
> 
> the test/lab server is a small sun box with 2 each 900 MHz CPUs, and 8
> GB of RAM.
> 
> I'm trying to avoid using disk until I get a better performance disk
> subsystem.
> 
> The version is the website binary, solaris 64 bit.
> 
> the setting i currently have
> 
> [mysqld]
> set-variable = myisam_sort_buffer_size=4096M
> set-variable = sort_buffer_size=4096M
> set-variable = read_buffer_size=512M
> set-variable = key_buffer_size=4096M
> set-variable = table_cache=1024
> 
> tmp_table_size  = 1024M
> max_heap_table_size = 1024M
> 
> a search on text fields such as
> 
> select substring_index(request_uri,'/',-1) jsp, count(*) from apache_jan
> where request_is_jsp = 1 group by jsp;
> 
> takes 8 1/2 minutes.
> 
> A rebuild of the indexes takes over 8 hours.
> 
> the table format
> 
> CREATE TABLE `apache_jan` (
>   `dt` datetime NOT NULL default '-00-00 00:00:00',
>   `hostname` varchar(125) default NULL,
>   `ip` varchar(15) default NULL,
>   `ipn` int(10) unsigned default NULL,
>   `user` varchar(125) default NULL,
>   `timetaken` float default NULL,
>   `auth` varchar(255) default NULL,
>   `request` text,
>   `request_type` varchar(255) default NULL,
>   `request_uri` text,
>   `request_file` varchar(255) default NULL,
>   `request_is_jsp` tinyint(1) default NULL,
>   `request_is_servlet` tinyint(1) default NULL,
>   `request_query` text,
>   `request_version` varchar(255) default NULL,
>   `status` smallint(3) unsigned default NULL,
>   `status_orig` smallint(3) unsigned default NULL,
>   `bytes` int(11) default NULL,
>   `bytes_i` int(11) default NULL,
>   `bytes_o` int(11) default NULL,
>   `header` text,
>   `header_uri` text,
>   `header_file` varchar(255) default NULL,
>   `header_is_jsp` tinyint(1) default NULL,
>   `useragent` varchar(255) default NULL,
>   `biz_obj` varchar(255) default NULL,
>   KEY `dt` (`dt`),
>   KEY `hostname` (`hostname`),
>   KEY `ip` (`ip`),
>   KEY `status` (`status`),
>   KEY `request_is_jsp` (`request_is_jsp`),
>   KEY `request_is_servlet` (`request_is_servlet`),
>   KEY `request_file` (`request_file`),
>   KEY `header_file` (`header_file`),
>   KEY `header_is_jsp` (`header_is_jsp`),
>   KEY `ipn` (`ipn`)
> ) TYPE=MyISAM DEFAULT CHARSET=latin1 MAX_ROWS=2
> AVG_ROW_LENGTH=600 INDEX DIRECTORY='/DISK1/MYSQL/pdm/'
> 
> --
> 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]

Not using index?

[Keith Thompson]

I've got a query that's not using an index and I don't understand why.
Here's a stripped down version of what the tables look like:

create table t1 (
   id int(10) unsigned not null auto_increment,
   x1 date not null,
   ... 8 other small, fixed fields
   primary key (id),
   key search_x1 (x1)
) type=innodb;

create table t2 (
   id int(10) unsigned not null,
   x2 bigint(20) unsigned default null,
   ... 10 other small, fixed fields
   key join_t1 (id),
   key search_x2 (x2)
) type=innodb;

Table t1 has about 60 million rows and t2 about 70 million.  Cardinality
for x1 and x2 values is high, with no more than maybe 10,000 hits for
x2 values and 20,000 for x1 values.

These queries run very quickly (usually subsecond):

select somefields from t1 where x1 = somedate
select somefields from t2 where x2 = somenumber
select somefields from t1, t2 where t1.id = t2.id and t1.x1 = somedate

This query runs painfully slow (usually several minutes):

select somefields from t1, t2 where t1.id = t2.id and t2.x2 = somenumber;

Using EXPLAIN, the first three queries above all report "using where; using
index" for search_x1 or search_x2, whereas this last query only reports
"using where".  Why would this query not use the search_x2 index?

Suggestions?

Thanks -keith



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

OR in query doesn't use keys?

[Bill Marrs]

I've noticed that If I use an OR in my query, mysql seems to choose not to 
use my indexes.  Though, it would seem to help (as, if I do the query in 
two steps, I can get faster results than as one query).

Is there some way I can convince mysql to use my keys with an OR, or 
perhaps another way to do queries to avoid OR, but still get an OR-like result?

Here's a simplified example (my actual case is more complicated and slower):

mysql> SELECT count(*) FROM Trades WHERE User1 = 79909;
+--+
| count(*) |
+--+
|   22 |
+--+
1 row in set (0.00 sec)
mysql> SELECT count(*) FROM Trades WHERE User2 = 79909;
+--+
| count(*) |
+--+
|   33 |
+--+
1 row in set (0.01 sec)
mysql> SELECT count(*) FROM Trades WHERE (User1 = 79909 OR User2 = 79909);
+--+
| count(*) |
+--+
|   55 |
+--+
1 row in set (0.35 sec)
Note - the OR is slower, describe (below) even says that it doesn't use 
either key in this case.

mysql> describe SELECT count(*) FROM Trades WHERE (User1 = 79909 OR User2 = 
79909);
++--+---+--+-+--++-+
| table  | type | possible_keys | key  | key_len | ref  | rows   | 
Extra   |
++--+---+--+-+--++-+
| Trades | ALL  | User1Key,User2Key | NULL |NULL | NULL | 100775 | 
Using where |
++--+---+--+-+--++-+
1 row in set (0.00 sec)

Here's the table:

CREATE TABLE Trades (
  UID int(10) unsigned NOT NULL auto_increment,
  User1 int(10) unsigned NOT NULL default '0',
  User2 int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (UID),
  KEY User1Key (User1),
  KEY User2Key (User2)
) TYPE=MyISAM PACK_KEYS=1;
Anyone know a trick to do OR queries faster?

Thanks in advance,

-bill

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Sequrity question or am I paranoid?

[Eve Atley]

Speaking of MySQL database security, what are some key things to keep in
mind, other than changing the root password?

Thanks,
Eve



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Sequrity question or am I paranoid?

[Steenveld, A.]

Well, I agree with your reply. But to my opinion there is a big difference between 
letting someone walk into an open manhole or put a small fence around it for 
protection.

The way it is now, not much is done to prevent the stupid from being stupid. I like to 
see some minimum safeguards just to prevent the obvious. It will not bring a secure 
database but some sensible settings can make life just that easier for the starters.

Anyway, it was not difficult to find my way out of it. It only took a lot of time to 
brouse the 1100+ manual pages.

Kind regards,

André Steenveld.

PS: I agree wit your first statement too... they are comming to take me away HA HA. :]



> -Original Message-
> From: Peter Lovatt [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, February 17, 2004 3:04 PM
> To: Steenveld, A.; [EMAIL PROTECTED]
> Subject: RE: Sequrity question or am I paranoid?
> 
> 
> no .they really are out to get you :)
> 
> Security is always a challenge. You can build the most secure 
> system in the
> world but if the users are not educated in security you have 
> wasted your
> time.
> 
> The "no password" relies on a user knowing what to do. The 
> question is this
> OK default behaviour - the number of MS SQL installations 
> with no master
> password (I remember reading an article about it) says that 
> there are plenty
> of newbie/uneducated/amater/stupid DBAs out there for it to 
> be problem.
> 
> Perhaps forcing the user into setting a password during setup 
> would be a
> good idea, particularly as MySql expands its userbase beyond 
> the net, where
> security tend to be a priority and DBAs tend to be reasonably skilled.
> 
> The password is less of a problem - if you set 'letmein' or 
> something well
> known then the argument above applies. If your password is 
> secure then a)
> only a user with access to the MySql database will see the encrypted
> password, so they probably already know the root password 
> anyway. b)you
> would still have to try thousands or millions of combinations 
> before you
> found the right one. Not impossible, but a reasonable barrier.
> 
> If you try a brute force attack as an external user trying to 
> login, MySql
> will lock you out after 10 attempts.
> 
> just my 2p worth :)
> 
> Peter
> 
> 
> ---
> Excellence in internet and open source software
> ---
> Sunmaia
> Birmingham
> UK
> www.sunmaia.net
> tel. 0121-242-1473
> International +44-121-242-1473
> ---
> 
> 
> 
> 
> 
> 
> 
> -Original Message-
> From: Steenveld, A. [mailto:[EMAIL PROTECTED]
> Sent: 17 February 2004 13:23
> To: [EMAIL PROTECTED]
> Subject: Sequrity question or am I paranoid?
> 
> 
> I'm new to MySQL and in starting to use it some questions 
> came up to me
> about sequrity. Please allow me to post them here.
> 
> Why is is that MySQL on a new installation has *no* password at all?
> OK, the documentation gives you a waring for it and strongly suggest
> to install one. But why not installing it with a default password?
> (Of cause, just as unsafe, but at least one must read the manual
> before they stumble into dangerous territory)
> 
> Why is it that the documentations pays so less attention to the fact
> that a password is assingned on a link/user basis? (To less is what
> it looks to me, I just overlooked the whole concept and had the
> database wide open for everyone without me knowing about it.)
> 
> To my opinion these two point should be handled as bugs.
> 
> 
> And last but not least I noticed that it is possible to guess any
> password when you have access to the user table in mysql. Let me
> tell you how.
> Passwords are stored in an encripted way but when two users have
> the same password they will end up with the same encripted item
> in the user table. E.g. in the table below the users 'root' and
> 'me' use the same password.
> +---+-+--+
> | host  | user| password |
> +---+-+--+
> | localhost | root| 58982d15048734ee |
> | localhost | me  | 58982d15048734ee |
> +---+-+--+
> 
> An easy way to do something about this is not to encript
> password("")
> but something like password("@=") which 
> will guarantee
> a different encription for each user/host combination.
> 
> Kind regards,
> 
> André Steenveld.
> 
> --
> 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]

SV: SQL-HELP

["Carl Schéle, IT, Posten"]

It works fine (with a little tweak).

SELECT DISTINCT CASE WHEN c1.winner_1 = c1.winner_2 THEN c1.winner_1 ELSE c1.winner_2 
END AS winner FROM champions c1,champions c2 ORDER BY winner ASC

is what I wanted. Thank you very much! Btw, I can't help my webhotel is rotten and 
only uses old versions. But it's cheap :)

/Carl

-Ursprungligt meddelande-
Från: Jochem van Dieten [mailto:[EMAIL PROTECTED] 
Skickat: den 17 februari 2004 16:44
Till: Carl Schéle, IT, Posten
Kopia: [EMAIL PROTECTED]
Ämne: Re: SQL-HELP

Carl Schéle, IT, Posten wrote:
> 
> I got a table, champions, looking like this:
> 
> id   winner_1 winner_2
> 
> 0carl mattias
> 1daniel carl
> 2erik daniel
> 3erik johan
> 
> What I want is to retrieve the unique names ie:
> 
> carl
> mattias
> daniel
> erik
> johan
> 
> I use MySQL 3.23.58 (which means I can't use sub-selects).

The smart way: get a database that understands UNION.

The other way:
SELECT DISTINCT
CASE
WHEN c1.id = c1.id THEN c1.winner_1
ELSE c1.winner_2
END AS winner
FROM
champions c1,
champions c2

Jochem

-- 
I don't get it
immigrants don't work
and steal our jobs
 - Loesje




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Sequrity question or am I paranoid?

[Martin Gainty]

Store the password (encrypted of course) in your DB de jour
Read the 'user record' for that username and pull the associated password
Of course its nice to allow the capability to assign a new password
Yes I Agree wholeheartedly with your first statement..
-Martin
- Original Message -
From: "Peter Lovatt" <[EMAIL PROTECTED]>
To: "Steenveld, A." <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Tuesday, February 17, 2004 9:03 AM
Subject: RE: Sequrity question or am I paranoid?


> no .they really are out to get you :)
>
> Security is always a challenge. You can build the most secure system in
the
> world but if the users are not educated in security you have wasted your
> time.
>
> The "no password" relies on a user knowing what to do. The question is
this
> OK default behaviour - the number of MS SQL installations with no master
> password (I remember reading an article about it) says that there are
plenty
> of newbie/uneducated/amater/stupid DBAs out there for it to be problem.
>
> Perhaps forcing the user into setting a password during setup would be a
> good idea, particularly as MySql expands its userbase beyond the net,
where
> security tend to be a priority and DBAs tend to be reasonably skilled.
>
> The password is less of a problem - if you set 'letmein' or something well
> known then the argument above applies. If your password is secure then a)
> only a user with access to the MySql database will see the encrypted
> password, so they probably already know the root password anyway. b)you
> would still have to try thousands or millions of combinations before you
> found the right one. Not impossible, but a reasonable barrier.
>
> If you try a brute force attack as an external user trying to login, MySql
> will lock you out after 10 attempts.
>
> just my 2p worth :)
>
> Peter
>
>
> ---
> Excellence in internet and open source software
> ---
> Sunmaia
> Birmingham
> UK
> www.sunmaia.net
> tel. 0121-242-1473
> International +44-121-242-1473
> ---
>
>
>
>
>
>
>
> -Original Message-
> From: Steenveld, A. [mailto:[EMAIL PROTECTED]
> Sent: 17 February 2004 13:23
> To: [EMAIL PROTECTED]
> Subject: Sequrity question or am I paranoid?
>
>
> I'm new to MySQL and in starting to use it some questions came up to me
> about sequrity. Please allow me to post them here.
>
> Why is is that MySQL on a new installation has *no* password at all?
> OK, the documentation gives you a waring for it and strongly suggest
> to install one. But why not installing it with a default password?
> (Of cause, just as unsafe, but at least one must read the manual
> before they stumble into dangerous territory)
>
> Why is it that the documentations pays so less attention to the fact
> that a password is assingned on a link/user basis? (To less is what
> it looks to me, I just overlooked the whole concept and had the
> database wide open for everyone without me knowing about it.)
>
> To my opinion these two point should be handled as bugs.
>
>
> And last but not least I noticed that it is possible to guess any
> password when you have access to the user table in mysql. Let me
> tell you how.
> Passwords are stored in an encripted way but when two users have
> the same password they will end up with the same encripted item
> in the user table. E.g. in the table below the users 'root' and
> 'me' use the same password.
> +---+-+--+
> | host  | user| password |
> +---+-+--+
> | localhost | root| 58982d15048734ee |
> | localhost | me  | 58982d15048734ee |
> +---+-+--+
>
> An easy way to do something about this is not to encript
> password("")
> but something like password("@=") which will
guarantee
> a different encription for each user/host combination.
>
> Kind regards,
>
> André Steenveld.
>
> --
> 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]

what can i change in my.cnf/mysqld to get better perfomance

[Lancashire, Pete]

I'm doing some tests on a small Sun server, a 280R to demo
MySQL vs. Oracle. For the demo I using a subset of a test table.

The destination server will be a Sun 880 with 8 1.2 GHz CPU's,
16 GB RAM, Sun T3+ RAID Array.

Any suggestions on settings or other changes would be helpful.

Also what would be the best variables/settings to compile a binary
for a server of this type ? Gcc 3.3.2, Solaris 9.

number of rows is only 10,735,291

MYD is 6.7 GBytes, and MYI is 1.4 GBytes.

This table is a subset of the real table of about 120 Gb.

the test/lab server is a small sun box with 2 each 900 MHz CPUs, and 8
GB of RAM.

I'm trying to avoid using disk until I get a better performance disk
subsystem.

The version is the website binary, solaris 64 bit.

the setting i currently have

[mysqld]
set-variable = myisam_sort_buffer_size=4096M
set-variable = sort_buffer_size=4096M
set-variable = read_buffer_size=512M
set-variable = key_buffer_size=4096M
set-variable = table_cache=1024

tmp_table_size  = 1024M
max_heap_table_size = 1024M

a search on text fields such as

select substring_index(request_uri,'/',-1) jsp, count(*) from apache_jan
where request_is_jsp = 1 group by jsp;

takes 8 1/2 minutes.

A rebuild of the indexes takes over 8 hours.

the table format

CREATE TABLE `apache_jan` (
  `dt` datetime NOT NULL default '-00-00 00:00:00',
  `hostname` varchar(125) default NULL,
  `ip` varchar(15) default NULL,
  `ipn` int(10) unsigned default NULL,
  `user` varchar(125) default NULL,
  `timetaken` float default NULL,
  `auth` varchar(255) default NULL,
  `request` text,
  `request_type` varchar(255) default NULL,
  `request_uri` text,
  `request_file` varchar(255) default NULL,
  `request_is_jsp` tinyint(1) default NULL,
  `request_is_servlet` tinyint(1) default NULL,
  `request_query` text,
  `request_version` varchar(255) default NULL,
  `status` smallint(3) unsigned default NULL,
  `status_orig` smallint(3) unsigned default NULL,
  `bytes` int(11) default NULL,
  `bytes_i` int(11) default NULL,
  `bytes_o` int(11) default NULL,
  `header` text,
  `header_uri` text,
  `header_file` varchar(255) default NULL,
  `header_is_jsp` tinyint(1) default NULL,
  `useragent` varchar(255) default NULL,
  `biz_obj` varchar(255) default NULL,
  KEY `dt` (`dt`),
  KEY `hostname` (`hostname`),
  KEY `ip` (`ip`),
  KEY `status` (`status`),
  KEY `request_is_jsp` (`request_is_jsp`),
  KEY `request_is_servlet` (`request_is_servlet`),
  KEY `request_file` (`request_file`),
  KEY `header_file` (`header_file`),
  KEY `header_is_jsp` (`header_is_jsp`),
  KEY `ipn` (`ipn`)
) TYPE=MyISAM DEFAULT CHARSET=latin1 MAX_ROWS=2
AVG_ROW_LENGTH=600 INDEX DIRECTORY='/DISK1/MYSQL/pdm/'



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: MySQL Memory

[Sp.Raja]

Thanks for your lightening response.

Even I thought of this idea
 - Isolate pairs like 
   - my_malloc and my_free
   - Innodb pair: ut_malloc and ut_free

But if pointers are passed between InnoDB and MySQL or MySQL and clients, then the 
memory consumer should free it using my wrapper and not system defined free. 

Is there such communication or all pointers allocated by one modules stays with in it 
and gets freed by the module.

Thanks,
Sp.Raja

> Original Message
> From: Sasha Pachev <[EMAIL PROTECTED]>
> To: Chris Nolan <[EMAIL PROTECTED]>
> Cc: "Sp.Raja" <[EMAIL PROTECTED]>, [EMAIL PROTECTED]
> Date: Tue, Feb-17-2004 6:55 PM
> Subject: Re: MySQL Memory
> 
> Chris Nolan wrote:
> > Hi!
> > 
> > You'll want to look through the MySQL C API docs. There are specific 
> > calls for allocating certain structures that MySQL client software 
> > should use (and routines for deallocating it as well).
> > 
> > Regards,
> > 
> > Chris
> > 
> > Sp.Raja wrote:
> > 
> >> Hi List,
> >>
> >> I want to use some other memory allocator routine other than malloc in 
> >> MySQL/InnoDB 4.0.15a.
> >> Does MySQL call malloc directly? or thru wrappers? If it is through 
> >> wrappers then my job will be much simpler, If so please give me the 
> >> list of wrappers.
> 
> You can force MySQL client to allocate using your own routines if you get the 
> client source,replace my_malloc() and my_free() (see mysys/my_alloc.c), and link 
> your code against your modified version. In fact, you could even do it for the 
> server.
> 
> 
> -- 
> Sasha Pachev
> Create online surveys at http://www.surveyz.com/
> 



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: SQL-HELP

[Jochem van Dieten]

Carl Schéle, IT, Posten wrote:
I got a table, champions, looking like this:

id   winner_1 winner_2

0carl mattias
1daniel carl
2erik daniel
3erik johan
What I want is to retrieve the unique names ie:

carl
mattias
daniel
erik
johan
I use MySQL 3.23.58 (which means I can't use sub-selects).
The smart way: get a database that understands UNION.

The other way:
SELECT DISTINCT
CASE
WHEN c1.id = c1.id THEN c1.winner_1
ELSE c1.winner_2
END AS winner
FROM
champions c1,
champions c2
Jochem

--
I don't get it
immigrants don't work
and steal our jobs
- Loesje
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

help needed reg MySQL 5.0.0a-aplha Windows

[Arunachalam]

hi all,

I have installed MySQL 5.0.0a-alpha for Windows...

Everday 1st time it's started using WinMySQLAdmin 1.4 tool
and working on the mysql prompt, it works fine...

whenever i shut down the system OR  Stop the service once
and restart again it started asusual, but no values displayed 
in Environment tab --> Server details such as Server info, Host Info, 
Client Info, Protocol info all the remainig details too

After some 15 mnts of time the server gone away... WinMySQLAdmin won't 
provide Show me option during mouse click

but I can see the Process WinMySQLAdmin.exe and mysqld.exe are active
in TASK MANAGER..

Pls suggest me what could makes this trouble here...


I have 
OS : Windows 2000 SP 4

Thanks in advance...

Arun.



Yahoo! India Insurance Special: Be informed on the best policies, services, tools and 
more. 
Go to: http://in.insurance.yahoo.com/licspecial/index.html

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

SV: SQL-HELP

["Carl Schéle, IT, Posten"]

Now UNION is implemented in MySQL 4.0.0. and as I stated earlier I run 3.23.58.

-Ursprungligt meddelande-
Från: Rodolphe Toots [mailto:[EMAIL PROTECTED] 
Skickat: den 17 februari 2004 16:19
Till: Jonas Lindén; Carl Schéle; [EMAIL PROTECTED]
Ämne: SV: SQL-HELP

yeah
but that wont really do it since the names are in two columns
so, there must also be a UNION included
do a union and then select distinct on the result from the union
that should do it (eller hur?)

-Ursprungligt meddelande-
Från: Jonas Lindén [mailto:[EMAIL PROTECTED]
Skickat: den 17 februari 2004 16:01
Till: Carl Schéle; IT; Posten; [EMAIL PROTECTED]
Ämne: Re: SQL-HELP


Hello, you might want to try select DISTINCT ?

http://www.mysqlfreaks.com/statements/18.php

/Jonas

- Original Message - 
From: "Carl Schéle, IT, Posten" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, February 17, 2004 3:49 PM
Subject: SQL-HELP


Hello!

I got a table, champions, looking like this:



id   winner_1 winner_2



0carl mattias

1daniel carl

2erik daniel

3erik johan



What I want is to retrieve the unique names ie:



carl

mattias

daniel

erik

johan



I use MySQL 3.23.58 (which means I can't use sub-selects).



/Carl





-- 
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]

SV: SQL-HELP

[Rodolphe Toots]

yeah
but that wont really do it since the names are in two columns
so, there must also be a UNION included
do a union and then select distinct on the result from the union
that should do it (eller hur?)

-Ursprungligt meddelande-
Från: Jonas Lindén [mailto:[EMAIL PROTECTED]
Skickat: den 17 februari 2004 16:01
Till: Carl Schéle; IT; Posten; [EMAIL PROTECTED]
Ämne: Re: SQL-HELP


Hello, you might want to try select DISTINCT ?

http://www.mysqlfreaks.com/statements/18.php

/Jonas

- Original Message - 
From: "Carl Schéle, IT, Posten" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, February 17, 2004 3:49 PM
Subject: SQL-HELP


Hello!

I got a table, champions, looking like this:



id   winner_1 winner_2



0carl mattias

1daniel carl

2erik daniel

3erik johan



What I want is to retrieve the unique names ie:



carl

mattias

daniel

erik

johan



I use MySQL 3.23.58 (which means I can't use sub-selects).



/Carl





-- 
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]

AW: AW: Pending results blocking mysql-server

[Franz, Fa. PostDirekt MA]

Hi Sacha,

Thx a lot for your reply
you wrote:

>in this case, I suspect, is that the disconnect happens in the
pre-data-sending 
>stage, which is possibly quite lengthy. 

Exactly ! It takes hours till these results are erased and i even don't know
where the period when they are erased come from.
I still hope this is set in a server-variable.

>I would also check to see if there might be some simple fixes 
>, such as adding appropriate keys to the tables, that would optimize the
slow 
>queries.

The problem is, that i can't prevent queries like
SELECT * FROM  WHERE  LIKE 'a%'
where an index will not help on a Table > 100.000.000 records.
It's not that we have to answer hese queries (therefore the timeout), but
it's impossible to prevent dumm users.

>The solution I would propose is to maintain a separate connection on which
the KILL 
>commands  will be sent for the long queries. 

I did this (by reading processlist and kiliing any process that 
lasts longer then 20 seconds) and it works fine, but it is not such a
'proper' solution.
I still hope there is a way to get that by setting up the server.

Thx a lot again
Klaus

Ursprüngliche Nachricht-
Von: Sasha Pachev [mailto:[EMAIL PROTECTED]
Gesendet: Dienstag, 17. Februar 2004 14:19
An: Franz, Fa. PostDirekt MA
Cc: Stefan Hinz; [EMAIL PROTECTED]
Betreff: Re: AW: Pending results blocking mysql-server


Franz, Fa. PostDirekt MA wrote:
> Hi Stefan,
> 
> thanks for your fast reply !
> 
> 
>>>Would it be possible to call mysql_free_result() from your
>>>application? You might be able to set a "timer" in your app, calling
>>>that function after, for example, 20 seconds.
> 
> 
> I am not the of developer this programm ...
> But i'm sure I can convince him if it helps :0)
> I read about mysql_free_result() already, but i thought it would
> clear the local buffer for an already fetched result.
> Are you sure it erases a pending result on the server?

mysql_free_result() frees only the client memory. However, a disconnect on
the 
socket will tell the server to stop sending the data and clean up. The
problem 
in this case, I suspect, is that the disconnect happens in the
pre-data-sending 
stage, which is possibly quite lengthy. The solution I would propose is to 
maintain a separate connection on which the KILL commands  will be sent for
the 
long queries. And, I would also check to see if there might be some simple
fixes 
, such as adding appropriate keys to the tables, that would optimize the
slow 
queries.


-- 
Sasha Pachev
Create online surveys at http://www.surveyz.com/

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

ID kewuq... thanks

[miguel]

Yours ID afccvi
--
Thank 


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: SQL-HELP

[Jonas Lindén]

Hello, you might want to try select DISTINCT ?

http://www.mysqlfreaks.com/statements/18.php

/Jonas

- Original Message - 
From: "Carl Schéle, IT, Posten" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, February 17, 2004 3:49 PM
Subject: SQL-HELP


Hello!

I got a table, champions, looking like this:



id   winner_1 winner_2



0carl mattias

1daniel carl

2erik daniel

3erik johan



What I want is to retrieve the unique names ie:



carl

mattias

daniel

erik

johan



I use MySQL 3.23.58 (which means I can't use sub-selects).



/Carl





-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Bug in subqueries?

[Victoria Reznichenko]

Andreas Pardeike <[EMAIL PROTECTED]> wrote:
> Hi List,
> should I report the following as a bug?
> 
> mysql> select count(*) from words as w1 where w1.val in (select w2.val 
> from words as w2 where w2.val like 'm%');   
>+--+
> | count(*) |
> +--+
> | 3552 |
> +--+
> 1 row in set (0.78 sec)
> 
> mysql> select count(*) from words as w1 where w1.val in (select w3.val 
> from words as w3 where w3.val like 'e%');
> +--+
> | count(*) |
> +--+
> | 1626 |
> +--+
> 1 row in set (0.78 sec)
> 
> mysql> select count(*) from words as w1 where w1.val in (select w2.val 
> from words as w2 where w2.val like 'm%') and w1.val in (select w3.val 
> from words as w3 where w3.val like 'e%');
> +--+
> | count(*) |
> +--+
> |53556 |
> +--+
> 1 row in set (0.08 sec)
> 
> mysql> select count(*) from words;
> +--+
> | count(*) |
> +--+
> |53556 |
> +--+
> 1 row in set (0.00 sec)
> 
> 

Entered to the bug database as bug #2838:
http://bugs.mysql.com/bug.php?id=2838

Thanks for bug report!


-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Victoria Reznichenko
 / /|_/ / // /\ \/ /_/ / /__   [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]

SQL-HELP

["Carl Schéle, IT, Posten"]

Hello!

I got a table, champions, looking like this:

 

id   winner_1 winner_2



0carl mattias

1daniel carl

2erik daniel

3erik johan

 

What I want is to retrieve the unique names ie:

 

carl

mattias

daniel

erik

johan

 

I use MySQL 3.23.58 (which means I can't use sub-selects).

 

/Carl

 

RE: Sequrity question or am I paranoid?

[Peter Lovatt]

no .they really are out to get you :)

Security is always a challenge. You can build the most secure system in the
world but if the users are not educated in security you have wasted your
time.

The "no password" relies on a user knowing what to do. The question is this
OK default behaviour - the number of MS SQL installations with no master
password (I remember reading an article about it) says that there are plenty
of newbie/uneducated/amater/stupid DBAs out there for it to be problem.

Perhaps forcing the user into setting a password during setup would be a
good idea, particularly as MySql expands its userbase beyond the net, where
security tend to be a priority and DBAs tend to be reasonably skilled.

The password is less of a problem - if you set 'letmein' or something well
known then the argument above applies. If your password is secure then a)
only a user with access to the MySql database will see the encrypted
password, so they probably already know the root password anyway. b)you
would still have to try thousands or millions of combinations before you
found the right one. Not impossible, but a reasonable barrier.

If you try a brute force attack as an external user trying to login, MySql
will lock you out after 10 attempts.

just my 2p worth :)

Peter


---
Excellence in internet and open source software
---
Sunmaia
Birmingham
UK
www.sunmaia.net
tel. 0121-242-1473
International +44-121-242-1473
---







-Original Message-
From: Steenveld, A. [mailto:[EMAIL PROTECTED]
Sent: 17 February 2004 13:23
To: [EMAIL PROTECTED]
Subject: Sequrity question or am I paranoid?


I'm new to MySQL and in starting to use it some questions came up to me
about sequrity. Please allow me to post them here.

Why is is that MySQL on a new installation has *no* password at all?
OK, the documentation gives you a waring for it and strongly suggest
to install one. But why not installing it with a default password?
(Of cause, just as unsafe, but at least one must read the manual
before they stumble into dangerous territory)

Why is it that the documentations pays so less attention to the fact
that a password is assingned on a link/user basis? (To less is what
it looks to me, I just overlooked the whole concept and had the
database wide open for everyone without me knowing about it.)

To my opinion these two point should be handled as bugs.


And last but not least I noticed that it is possible to guess any
password when you have access to the user table in mysql. Let me
tell you how.
Passwords are stored in an encripted way but when two users have
the same password they will end up with the same encripted item
in the user table. E.g. in the table below the users 'root' and
'me' use the same password.
+---+-+--+
| host  | user| password |
+---+-+--+
| localhost | root| 58982d15048734ee |
| localhost | me  | 58982d15048734ee |
+---+-+--+

An easy way to do something about this is not to encript
password("")
but something like password("@=") which will guarantee
a different encription for each user/host combination.

Kind regards,

André Steenveld.

--
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]

Fw: Re: MySQL Memory

[Sp.Raja]

Missed ccing it to list!

Yes In mysqld. system provided malloc has access to a samller space of entire main 
memory, and we APIs to access the rest, I'm trying to change mysqlds malloc calls to 
our extended malloc which make use of the entire main memory.
 
Regards,
Sp.Raja
 
> > Original Message
> > From: Chris Nolan <[EMAIL PROTECTED]>
> > To: "Sp.Raja" <[EMAIL PROTECTED]>
> > Cc: [EMAIL PROTECTED]
> > Date: Tue, Feb-17-2004 6:53 PM
> > Subject: Re: MySQL Memory
> > 
> > Server end? As in, writing code that will integrate with the MySQL 
> > Server process? Or are you building UDFs?
> > 
> > Regards,
> > 
> > Chris
> > 
> > Sp.Raja wrote:
> > 
> > >Thanks for your input. I'm trying to do is at the server end(mysqld), could you 
> > >get some pointers?
> > >
> > >Regards,
> > >Sp.Raja
> > >
> > >  
> > >
> > >>Original Message
> > >>From: Chris Nolan <[EMAIL PROTECTED]>
> > >>To: "Sp.Raja" <[EMAIL PROTECTED]>
> > >>Cc: [EMAIL PROTECTED]
> > >>Date: Tue, Feb-17-2004 6:29 PM
> > >>Subject: Re: MySQL Memory
> > >>
> > >>Hi!
> > >>
> > >>You'll want to look through the MySQL C API docs. There are specific 
> > >>calls for allocating certain structures that MySQL client software 
> > >>should use (and routines for deallocating it as well).
> > >>
> > >>Regards,
> > >>
> > >>Chris
> > >>
> > >>Sp.Raja wrote:
> > >>
> > >>
> > >>
> > >>>Hi List,
> > >>>
> > >>>I want to use some other memory allocator routine other than malloc in 
> > >>>MySQL/InnoDB 4.0.15a.
> > >>>Does MySQL call malloc directly? or thru wrappers? If it is through wrappers 
> > >>>then my job will be much simpler, If so please give me the list of wrappers.
> > >>>
> > >>>Any Advice welcome
> > >>>
> > >>>Regards,
> > >>>Sp.Raja
> > >>>
> > >>>
> > >>>
> > >>>--
> > >>>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]
> > 
> 



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

UNION caches query?

[Bruce Altner]

Greetings:

I just installed 4.0.18-nt  on Windows 2000 Pro to utilize the new UNION 
feature and am experiencing weirdness which makes me think that MySQL is 
caching result sets when it shouldn't.

Here's a simple query from an events table and a UNION with a recurring 
events table, and its (correct) results (16 rows):

(SELECT SQL_NO_CACHE A.EVENT_ID AS REVENT_ID,
   A.EVENT_ID as VEVENT_ID,
   A.DTSTART,
   A.DTEND,
   A.SUMMARY
FROM VEVENTS A
WHERE 0=0
AND ((A.DTSTART >= '2004-02-01 00:00:00' AND A.DTEND <= '2004-03-01 00:00:00'))
)
UNION DISTINCT

(SELECT SQL_NO_CACHE B.REVENT_ID,
   B.VEVENT_ID,
   B.DTSTART,
   B.DTEND,
   C.SUMMARY
FROM RVEVENTS B,VEVENTS C
WHERE B.VEVENT_ID = C.EVENT_ID
)
ORDER BY 3 DESC

+---+---+-+-+---+
| REVENT_ID | VEVENT_ID | DTSTART | DTEND   | 
SUMMARY   |
+---+---+-+-+---
|51 |51 | 2004-02-29 14:00:00 | 2004-02-29 15:00:00 | 
Test
|33 |33 | 2004-02-21 15:45:00 | 2004-02-22 00:45:00 | An 
Event Added from Michael's Machine
|25 |25 | 2004-02-19 14:25:00 | 2004-02-19 14:32:00 | MLK 
II
|54 |54 | 2004-02-16 13:00:00 | 2004-02-17 00:00:00 | 
Fixing Errors in the Code
| 4 |50 | 2004-02-14 05:00:00 | 2004-02-14 05:00:00 | An 
Event To End All Events
| 3 |50 | 2004-02-13 05:00:00 | 2004-02-13 05:00:00 | An 
Event To End All Events
| 2 |50 | 2004-02-12 05:00:00 | 2004-02-12 05:00:00 | An 
Event To End All Events
|45 |45 | 2004-02-11 16:30:00 | 2004-02-11 17:30:00 | A 
simultaneous event
|32 |32 | 2004-02-11 13:00:00 | 2004-02-11 14:00:00 | Just 
an ordinary event, typical in many ways.
|46 |46 | 2004-02-11 13:00:00 | 2004-02-11 22:00:00 | 
Requirements Definition
| 1 |50 | 2004-02-11 05:00:00 | 2004-02-11 05:00:00 | An 
Event To End All Events
|50 |50 | 2004-02-10 05:00:00 | 2004-02-10 05:00:00 | An 
Event To End All Events
|44 |44 | 2004-02-10 13:00:00 | 2004-02-11 22:00:00 | 
Multiday event with end date specified
|43 |43 | 2004-02-10 01:00:00 | 2004-02-10 02:00:00 | Late 
Night with Letterman
|41 |41 | 2004-02-09 05:00:00 | 2004-02-11 05:00:00 | 
"Meeting of the Minds"
|42 |42 | 2004-02-09 05:00:00 | 2004-02-11 05:00:00 | On 
this day there are only other events
+---+---+-+-+---+

However, when I replace the SUMMARY column with LOCATION instead of just 
getting LOCATION for these 16 rows I get a mixture of LOCATION and SUMMARY, 
which now gives me 32 rows...(SNIPPED FOR BREVITY]

New query:
(SELECT SQL_NO_CACHE A.EVENT_ID AS REVENT_ID,
   A.EVENT_ID as VEVENT_ID,
   A.DTSTART,
   A.DTEND,
   A.LOCATION
FROM VEVENTS A
WHERE 0=0
AND ((A.DTSTART >= '2004-02-01 00:00:00' AND A.DTEND <= '2004-03-01 00:00:00'))
)
UNION DISTINCT

(SELECT SQL_NO_CACHE B.REVENT_ID,
   B.VEVENT_ID,
   B.DTSTART,
   B.DTEND,
   B.LOCATION
FROM RVEVENTS B,VEVENTS C
WHERE B.VEVENT_ID = C.EVENT_ID
)
ORDER BY 3 DESC

New results:

+---+---+-+-+---+
| REVENT_ID | VEVENT_ID | DTSTART | DTEND   | 
LOCATION  |
+---+---+-+-+---
|51 |51 | 2004-02-29 14:00:00 | 2004-02-29 15:00:00 | 
Test
|51 |51 | 2004-02-29 14:00:00 | 2004-02-29 15:00:00 
|
|33 |33 | 2004-02-21 15:45:00 | 2004-02-22 00:45:00 | An 
Event Added from Michael's Machine
|33 |33 | 2004-02-21 15:45:00 | 2004-02-22 00:45:00 | 
Living Room Sofa
|25 |25 | 2004-02-19 14:25:00 | 2004-02-19 14:32:00 | MLK 
II
|25 |25 | 2004-02-19 14:25:00 | 2004-02-19 14:32:00 | 
tbd
|54 |54 | 2004-02-16 13:00:00 | 2004-02-17 00:00:00 | 
Fixing Errors in the Code
|54 |54 | 2004-02-16 13:00:00 | 2004-02-17 00:00:00 
|
| 4 |50 | 2004-02-14 05:00:00 | 2004-02-14 05:00:00 | An 
Event To End All Events
| 4 |50 | 2004-02-14 05:00:00 | 2004-02-14 05:00:00 | Loc 
D
| 3 |50 | 2004-02-13 05:00:00 | 2004-02-13 05:00:00 | An 
Event To End All Events
| 3 |50 | 2004-02-13 05:00:00 | 2004-02-13 05:00:00 | Loc 
C
| 2 |50 | 2004-02-12 05:00:00 | 2004-02-12 05:00:00 | An 
Event To End All Events
| 2 |50 | 2004-02-12 05:00:00 | 2004-02-12 05:00:00 | Lo

Sequrity question or am I paranoid?

[Steenveld, A.]

I'm new to MySQL and in starting to use it some questions came up to me about 
sequrity. Please allow me to post them here.

Why is is that MySQL on a new installation has *no* password at all?
OK, the documentation gives you a waring for it and strongly suggest
to install one. But why not installing it with a default password?
(Of cause, just as unsafe, but at least one must read the manual 
before they stumble into dangerous territory)

Why is it that the documentations pays so less attention to the fact 
that a password is assingned on a link/user basis? (To less is what
it looks to me, I just overlooked the whole concept and had the
database wide open for everyone without me knowing about it.)

To my opinion these two point should be handled as bugs.


And last but not least I noticed that it is possible to guess any
password when you have access to the user table in mysql. Let me
tell you how.
Passwords are stored in an encripted way but when two users have
the same password they will end up with the same encripted item
in the user table. E.g. in the table below the users 'root' and
'me' use the same password.
+---+-+--+
| host  | user| password |
+---+-+--+
| localhost | root| 58982d15048734ee |
| localhost | me  | 58982d15048734ee |
+---+-+--+

An easy way to do something about this is not to encript password("") 
but something like password("@=") which will guarantee
a different encription for each user/host combination.

Kind regards,

André Steenveld.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: MySQL Memory

[Sasha Pachev]

Chris Nolan wrote:
Hi!

You'll want to look through the MySQL C API docs. There are specific 
calls for allocating certain structures that MySQL client software 
should use (and routines for deallocating it as well).

Regards,

Chris

Sp.Raja wrote:

Hi List,

I want to use some other memory allocator routine other than malloc in 
MySQL/InnoDB 4.0.15a.
Does MySQL call malloc directly? or thru wrappers? If it is through 
wrappers then my job will be much simpler, If so please give me the 
list of wrappers.
You can force MySQL client to allocate using your own routines if you get the 
client source,replace my_malloc() and my_free() (see mysys/my_alloc.c), and link 
your code against your modified version. In fact, you could even do it for the 
server.

--
Sasha Pachev
Create online surveys at http://www.surveyz.com/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: MySQL Memory

[Chris Nolan]

Server end? As in, writing code that will integrate with the MySQL 
Server process? Or are you building UDFs?

Regards,

Chris

Sp.Raja wrote:

Thanks for your input. I'm trying to do is at the server end(mysqld), could you get some pointers?

Regards,
Sp.Raja
 

Original Message
From: Chris Nolan <[EMAIL PROTECTED]>
To: "Sp.Raja" <[EMAIL PROTECTED]>
Cc: [EMAIL PROTECTED]
Date: Tue, Feb-17-2004 6:29 PM
Subject: Re: MySQL Memory
Hi!

You'll want to look through the MySQL C API docs. There are specific 
calls for allocating certain structures that MySQL client software 
should use (and routines for deallocating it as well).

Regards,

Chris

Sp.Raja wrote:

   

Hi List,

I want to use some other memory allocator routine other than malloc in MySQL/InnoDB 
4.0.15a.
Does MySQL call malloc directly? or thru wrappers? If it is through wrappers then my 
job will be much simpler, If so please give me the list of wrappers.
Any Advice welcome

Regards,
Sp.Raja


--
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: Newbie Question

[Egor Egorov]

"Rhino" <[EMAIL PROTECTED]> wrote:
> I'm confused. According to your note and to a passage I found in the manual,
> InnoDB support is installed in all 4.0.x versions of MySQL, which should
> obviously include my 4.0.11 install. Yet SHOW_VARIABLES LIKE 'have_innodb'
> returned NO. Why? It would appear that I need to do something besides
> installing a 4.0.x version of MySQL but I can't tell what that is from the
> manual. I found one section that said installing MySQL-Max-VERSION.i386.rpm
> will give me "additional capabilities" - without specifying what those
> capabilities were in any way. Do I need this RPM to get InnoDB support?

If you use 3.23.xx version you must install MySQL-Max.
Official binaries 4.0 binaries are configured with InnoDB support. Did you install 
official binaries (from MySQL site) or binaries that come with Red Hat distribution?

> 
> By the way, I'm willing to install a newer version of MySQL if that will
> help. However, that raises some additional questions.
> 
> I found the Upgrading/Downgrading section of the manual but it's pretty
> vague. For example it doesn't explicity say whether I need to uninstall
> 4.0.11 before installing the newer version. I suspect from the wording that
> I don't need to uninstall first but I'd feel a lot happier is someone could
> confirm that.

If you use RPM, you can just do

rpm -Uvh .rpm

> Also, the manual says that we are supposed to take backups of our databases
> before upgrading but the Database Backups section describes several
> different ways to do backups, each of which seems to be different. Which one
> should I use: a regular backup or an SQL level backup? Should I use SELECT
> INTO OUTFILE? BACKUP TABLE? mysqldump? mysqlhotcopy? What are the pros and
> cons of each? Do I need to do LOCK TABLES and FLUSH TABLES for each of these
> approaches? If yes, what is the right sequence: do I need to LOCK TABLES and
> FLUSH TABLES first, then do the backup? Or do I LOCK TABLES, backup, then
> FLUSH TABLES? The manual leaves a lot to the imagination and I've got a good
> imagination so I need some clarification ;-)
> 

If you use only ISAM/MyISAM tables you can execute FLUSH TABLES WITH READ LOCK and 
then copy data dir to the safe place.
Of course, you can also use mysqldump utility. In this case you get SQL dump.



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Egor Egorov
 / /|_/ / // /\ \/ /_/ / /__   [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: AW: Pending results blocking mysql-server

[Sasha Pachev]

Franz, Fa. PostDirekt MA wrote:
Hi Stefan,

thanks for your fast reply !


Would it be possible to call mysql_free_result() from your
application? You might be able to set a "timer" in your app, calling
that function after, for example, 20 seconds.


I am not the of developer this programm ...
But i'm sure I can convince him if it helps :0)
I read about mysql_free_result() already, but i thought it would
clear the local buffer for an already fetched result.
Are you sure it erases a pending result on the server?
mysql_free_result() frees only the client memory. However, a disconnect on the 
socket will tell the server to stop sending the data and clean up. The problem 
in this case, I suspect, is that the disconnect happens in the pre-data-sending 
stage, which is possibly quite lengthy. The solution I would propose is to 
maintain a separate connection on which the KILL commands  will be sent for the 
long queries. And, I would also check to see if there might be some simple fixes 
, such as adding appropriate keys to the tables, that would optimize the slow 
queries.

--
Sasha Pachev
Create online surveys at http://www.surveyz.com/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: MySQL Memory

[Sp.Raja]

Thanks for your input. I'm trying to do is at the server end(mysqld), could you get 
some pointers?

Regards,
Sp.Raja

> Original Message
> From: Chris Nolan <[EMAIL PROTECTED]>
> To: "Sp.Raja" <[EMAIL PROTECTED]>
> Cc: [EMAIL PROTECTED]
> Date: Tue, Feb-17-2004 6:29 PM
> Subject: Re: MySQL Memory
> 
> Hi!
> 
> You'll want to look through the MySQL C API docs. There are specific 
> calls for allocating certain structures that MySQL client software 
> should use (and routines for deallocating it as well).
> 
> Regards,
> 
> Chris
> 
> Sp.Raja wrote:
> 
> >Hi List,
> >
> >I want to use some other memory allocator routine other than malloc in MySQL/InnoDB 
> >4.0.15a.
> >Does MySQL call malloc directly? or thru wrappers? If it is through wrappers then 
> >my job will be much simpler, If so please give me the list of wrappers.
> >
> >Any Advice welcome
> >
> >Regards,
> >Sp.Raja
> >
> >
> >
> >--
> >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: Inconsistant TIMESTAMP behavoir

[Egor Egorov]

Garth Webb <[EMAIL PROTECTED]> wrote:
> 
> Hi, I am experiencing strange behavior from a TIMESTAMP column in an
> InnoDB table on my MySQL 4.0.16 server.  Specifically, it fails to
> select certain rows by this column.  The table is defined as:
> 
> CREATE TABLE log (
>s_id int(11) not null,
>p_id int(11) not null,
>action enum('A', 'B', 'C'),
>tstamp timestamp(14),
>index ( s_id, p_id ),
>index ( tstamp )
> );
> 
> My problem exists for the first few rows in this table.  For example,
> take the first row by date:
> 
> mysql> select min(tstamp) from log;
> ++
> | min(tstamp)|
> ++
> | 20031026010002 |
> ++
> 
> Now consider the following attempts to select this row:
> 
> mysql> select * from log where tstamp = 20031026010002;
> mysql> select * from log where tstamp = '20031026010002';
> mysql> select * from log where tstamp = "20031026010002";
> mysql> select * from log where tstamp = "2003-10-26 01:00:02";
> mysql> select * from log where tstamp > "20031026010001"
>-> and tstamp < "20031026010003";
> 
> None of these return any rows.  To add insult to injury, this database
> is replicated, and on the *replicated* database, ALL of these queries
> work.  The only query by date that work on the main database is:
> 
> mysql> select * from log where tstamp < "20031026010003"
> 
> Which doesn't make any sense considering that the date range query
> didn't work.
> 
> It seemed like this table must be corrupted somehow, so I ran a 'check
> table' on it which return a status of OK.
> 
> Unfortunately I cannot replicate this problem, so my hope is that
> someone else has experienced this as well, or that someone can suggest a
> method to repair this table.
> 
> Some final notes.  While this problem may have existed previously, I
> only noticed it when I began deleting old log data by date via a
> script.  Furthermore, only the first 2000 or so earliest dates cannot be
> selected.  All dates after this 'blackout' period can be selected
> without a problem.
> 

Recreate indexes and see if it helps.



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Egor Egorov
 / /|_/ / // /\ \/ /_/ / /__   [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: Multiple concurrent transactions per connection

[Chris Nolan]

Michael McTernan wrote:

Hi there,

 

Given this model, each thread is obviously going to want to have
multiple transactions outstanding. Is this something that might be added
to MySQL in future or am I totally overestimating the expense of using
one thread per connection?
   

I'm guessing you mean that each thread will want to have a number of
different transactions active at one time.  Depending on how your
application works, you could nest transactions on one connection through
careful use of savepoints and rollbacks to save points (under InnoDb from
4.0.14).  This won't help overlapping transactions that don't nest neatly
though.
 

Indeed! That was one thing I was thinking of, but the fact that InnoDB 
has non-saga style "transaction nesting" makes things a bit difficult.

Alternatively you can use connection pooling and have it such that you use a
connection per transaction.  You could make it such that once all the
connections were taken, your application blocked the start of further
transactions.
 

That was also something I looked at, but was worried about hitting the 
limit of the connection pool.

I think you should be careful though.  While a good design will help
performance, you might end up finding that the benefits of your design are
lost since disk IO or some other larger factor proves to be a much bigger
bottleneck!
 

Damned disks...err...I mean I was going to store all of my data in RAM 
anyway. :-)

Thanks,

Mike

 

Thanks for your insights!

Regards,

Chris

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: MySQL Memory

[Chris Nolan]

Hi!

You'll want to look through the MySQL C API docs. There are specific 
calls for allocating certain structures that MySQL client software 
should use (and routines for deallocating it as well).

Regards,

Chris

Sp.Raja wrote:

Hi List,

I want to use some other memory allocator routine other than malloc in MySQL/InnoDB 
4.0.15a.
Does MySQL call malloc directly? or thru wrappers? If it is through wrappers then my 
job will be much simpler, If so please give me the list of wrappers.
Any Advice welcome

Regards,
Sp.Raja


--
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 - Select with Text and Blob

[Jacob Joseph]

When a select query is run on a mysql table with text and blob fields, the
text fields return the whole text but the blob fields return only the first
4 bytes of the blob data. I use MySQL 4.0.17 with VB6.0 on Windows 2000.

What should I do to retrieve complete data from the blob field?

Any help is appreciated and thanks in advance.

Jacob Joseph.





---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.588 / Virus Database: 372 - Release Date: 2/13/04


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

AW: Pending results blocking mysql-server

[Franz, Fa. PostDirekt MA]

Hi Stefan,

thanks for your fast reply !

>>Would it be possible to call mysql_free_result() from your
>>application? You might be able to set a "timer" in your app, calling
>>that function after, for example, 20 seconds.

I am not the of developer this programm ...
But i'm sure I can convince him if it helps :0)
I read about mysql_free_result() already, but i thought it would
clear the local buffer for an already fetched result.
Are you sure it erases a pending result on the server?

mfg
Klaus



>>Would it be possible to call mysql_free_result() from your
>>application? You might be able to set a "timer" in your app, calling
>>that function after, for example, 20 seconds.

-Ursprüngliche Nachricht-
Von: Stefan Hinz [mailto:[EMAIL PROTECTED]
Gesendet: Dienstag, 17. Februar 2004 12:15
An: Franz, Fa. PostDirekt MA
Cc: [EMAIL PROTECTED]
Betreff: Re: Pending results blocking mysql-server


Klaus,

> we are porting an application from ORACLE to MySQL.
> The application does lot of queries and has and times out a query, if no result is 
> back
> after 10 seconds.
> It handles queries parrallel and rasies or lowers the amount of connection variable
> (the programm is a server itself).
> After a while, there is very big load on the MySQL-server (up to 90) and 'show 
> processlist' shows very
> many connections with queries that are in 'sending data'-state. But after they are 
> timed out,
> this results will never be fetched, but the threads for them are kept a very long 
> time.
> With lot of queries, it even happens, that a connection to the mysql-server is 
> blocked.
> i think it would be the best to get rid of hte pending results on the server, that 
> are not
> fetched after let's say 20 seconds.
> Which variable do i need to set to achieve this (if possible).
> The program uses the C-API. The serve is 4.017 with MyISAM-Tables on SUSE-LINUX SLES 
> 8.0,
> Ext3-filesystem, a 3Ghz xeleron, 1.5G MEM.
> Please help me, after i convienced my boss to use MySQL for this duty (which took 
> about 18 month),
> this might be very dangerous for my carreer.

Would it be possible to call mysql_free_result() from your
application? You might be able to set a "timer" in your app, calling
that function after, for example, 20 seconds.

http://www.mysql.com/doc/en/mysql_free_result.html

Regards,
--
  Stefan Hinz <[EMAIL PROTECTED]>
  iConnect GmbH 
  Heesestr. 6, 12169 Berlin (Germany)
  Telefon: +49 30 7970948-0  Fax: +49 30 7970948-3

[filter fodder: sql, mysql, query]

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Multiple concurrent transactions per connection

[Michael McTernan]

Hi there,

> Given this model, each thread is obviously going to want to have
> multiple transactions outstanding. Is this something that might be added
> to MySQL in future or am I totally overestimating the expense of using
> one thread per connection?

I'm guessing you mean that each thread will want to have a number of
different transactions active at one time.  Depending on how your
application works, you could nest transactions on one connection through
careful use of savepoints and rollbacks to save points (under InnoDb from
4.0.14).  This won't help overlapping transactions that don't nest neatly
though.

Alternatively you can use connection pooling and have it such that you use a
connection per transaction.  You could make it such that once all the
connections were taken, your application blocked the start of further
transactions.

I think you should be careful though.  While a good design will help
performance, you might end up finding that the benefits of your design are
lost since disk IO or some other larger factor proves to be a much bigger
bottleneck!

Thanks,

Mike



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Pending results blocking mysql-server

[Stefan Hinz]

Klaus,

> we are porting an application from ORACLE to MySQL.
> The application does lot of queries and has and times out a query, if no result is 
> back
> after 10 seconds.
> It handles queries parrallel and rasies or lowers the amount of connection variable
> (the programm is a server itself).
> After a while, there is very big load on the MySQL-server (up to 90) and 'show 
> processlist' shows very
> many connections with queries that are in 'sending data'-state. But after they are 
> timed out,
> this results will never be fetched, but the threads for them are kept a very long 
> time.
> With lot of queries, it even happens, that a connection to the mysql-server is 
> blocked.
> i think it would be the best to get rid of hte pending results on the server, that 
> are not
> fetched after let's say 20 seconds.
> Which variable do i need to set to achieve this (if possible).
> The program uses the C-API. The serve is 4.017 with MyISAM-Tables on SUSE-LINUX SLES 
> 8.0,
> Ext3-filesystem, a 3Ghz xeleron, 1.5G MEM.
> Please help me, after i convienced my boss to use MySQL for this duty (which took 
> about 18 month),
> this might be very dangerous for my carreer.

Would it be possible to call mysql_free_result() from your
application? You might be able to set a "timer" in your app, calling
that function after, for example, 20 seconds.

http://www.mysql.com/doc/en/mysql_free_result.html

Regards,
--
  Stefan Hinz <[EMAIL PROTECTED]>
  iConnect GmbH 
  Heesestr. 6, 12169 Berlin (Germany)
  Telefon: +49 30 7970948-0  Fax: +49 30 7970948-3

[filter fodder: sql, mysql, query]


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Random and string splitting

[Scott Haneda]

Hello list, I have a text field in a database, it contains a \r seperated
list of items.  I want a random item out of the list.

Please don't lead me down a path of using random in php or perl, that wont
be happening on this one, I am looking for something more like..

Select name, random(split(data, "\r")) from something;
-- 
-
Scott HanedaTel: 415.898.2602
http://www.newgeo.com   Fax: 313.557.5052
[EMAIL PROTECTED]Novato, CA U.S.A.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

MySQL Memory

[Sp.Raja]

Hi List,

I want to use some other memory allocator routine other than malloc in MySQL/InnoDB 
4.0.15a.
Does MySQL call malloc directly? or thru wrappers? If it is through wrappers then my 
job will be much simpler, If so please give me the list of wrappers.

Any Advice welcome

Regards,
Sp.Raja



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: mySQL SQL Conversion

[Victoria Reznichenko]

"Logan McKinley" <[EMAIL PROTECTED]> wrote:

> I am using MySQL (4.0.17) and am getting errors with the following SQL
> statement originally writen for MS products.
> 
> SELECT
> survey_questions.Question_ID,
> First(survey_questions.Question_Text) AS FirstOfQuestion_Text, <== this line
> returns errors i believe it is the AS
> Avg(survey_responses.Response) AS AvgOfResponse,
> Count(survey_responses.Response) AS CountOfResponse,
> survey_questions.survey_num
>
> FROM
> survey_questions INNER JOIN survey_responses ON survey_questions.Question_ID
> = survey_responses.QuestionID
>
> GROUP BY survey_questions.Question_ID, survey_questions.survey_num
> 
> HAVING (((Avg(survey_responses.Response))<>-1) AND
>((survey_questions.survey_num)=0));

There is no FIRST() function in MySQL.


-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Victoria Reznichenko
 / /|_/ / // /\ \/ /_/ / /__   [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]

Pending results blocking mysql-server

[Franz, Fa. PostDirekt MA]

Dear Sirs and Ladies,

we are porting an application from ORACLE to MySQL.
The application does lot of queries and has and times out a query, if no result is back
after 10 seconds.
It handles queries parrallel and rasies or lowers the amount of connection variable
(the programm is a server itself).
After a while, there is very big load on the MySQL-server (up to 90) and 'show 
processlist' shows very
many connections with queries that are in 'sending data'-state. But after they are 
timed out,
this results will never be fetched, but the threads for them are kept a very long time.
With lot of queries, it even happens, that a connection to the mysql-server is blocked.
i think it would be the best to get rid of hte pending results on the server, that are 
not
fetched after let's say 20 seconds.
Which variable do i need to set to achieve this (if possible).
The program uses the C-API. The serve is 4.017 with MyISAM-Tables on SUSE-LINUX SLES 
8.0,
Ext3-filesystem, a 3Ghz xeleron, 1.5G MEM.
Please help me, after i convienced my boss to use MySQL for this duty (which took 
about 18 month),
this might be very dangerous for my carreer.

PLEASE HELP ME (or give me a new job :-])

Klaus



Topaktuelle Consumer-Adressen anmieten www.consumeradressen.de 


Diese Mail ist von:
Deutsche Post Direkt GmbH
Beleglese Center Mannheim

Klaus Franz 
Manager Abgleichsysteme 

Willy-Brandt-Platz 13   Tel. 06 21.129 56 436
68161 Mannheim  


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Query to another server

[Chris Nolan]

Sure! Just remember that your slave will have to be able to keep up with 
all the UPDATE, INSERT and DELETE statements issued on the master servers.

Regards,

Chris

[EMAIL PROTECTED] wrote:

That looks like a good solution...
Do you know if i can replicate multiple databases from diferent servers to 
one server? many masters and one slave, the slave might have replicas of 
each master database
(cause i need to query on many diferent mysql servers)

thanks...
FBR


Chris Nolan <[EMAIL PROTECTED]> 
17/02/2004 12:52 a.m.

To
Terence <[EMAIL PROTECTED]>
cc
[EMAIL PROTECTED]
Subject
Re: Query to another server




You might be able to cheat and replicate the required database to the 
local machine.

Regards,

Chris

Terence wrote:

 

you'll need to create temporary tables in one of the servers based on the
results of the other and then join.
- Original Message - 
From: <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, February 17, 2004 2:54 PM
Subject: Query to another server

Is it possible to query one server from another even if those servers are 
   

 

in diferent machines at diferent locations?

for example:

select * from localtable left join 
   

remoteserver.remotedatabase.remotetable 
 

where localtable.key = remoteserver.remotedatabase.remotetable.key

FBR



   



 



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: FOEIGN KEY

[Victoria Reznichenko]

On Monday 16 February 2004 18:27, Mulugeta Maru wrote:
> Many thanks. What I would also like to know is that can a field in Table A
> be a foreign key of on of the fields in Table B with has two fields
> combined as a primary key?

Yes, it's possible. 
Note: column must be first column in the multiple-column primary key.


-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Victoria Reznichenko
 / /|_/ / // /\ \/ /_/ / /__   [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: Query to another server

[fbeltran]

That looks like a good solution...
Do you know if i can replicate multiple databases from diferent servers to 
one server? many masters and one slave, the slave might have replicas of 
each master database
(cause i need to query on many diferent mysql servers)

thanks...
FBR




Chris Nolan <[EMAIL PROTECTED]> 
17/02/2004 12:52 a.m.

To
Terence <[EMAIL PROTECTED]>
cc
[EMAIL PROTECTED]
Subject
Re: Query to another server






You might be able to cheat and replicate the required database to the 
local machine.

Regards,

Chris

Terence wrote:

>you'll need to create temporary tables in one of the servers based on the
>results of the other and then join.
>
>- Original Message - 
>From: <[EMAIL PROTECTED]>
>To: <[EMAIL PROTECTED]>
>Sent: Tuesday, February 17, 2004 2:54 PM
>Subject: Query to another server
>
>
>Is it possible to query one server from another even if those servers are 

>in diferent machines at diferent locations?
>
>for example:
>
>select * from localtable left join 
remoteserver.remotedatabase.remotetable 
>
>where localtable.key = remoteserver.remotedatabase.remotetable.key
>
>
>FBR
>
>
> 
>



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]

Re: ib* files in /var/lib/mysql

[Victoria Reznichenko]

Bill Marrs <[EMAIL PROTECTED]> wrote:
> I noticed these files in my mysql data directory:
> 
> -rw-rw1 mysqlmysql 5242880 Feb 15 20:54 ib_logfile0
> -rw-rw1 mysqlmysql10485760 Feb 15 20:54 ibdata1
> -rw-rw1 mysqlmysql   25088 Jun  3  2003 ib_arch_log_00
> -rw-rw1 mysqlmysql 5242880 Jun  3  2003 ib_logfile1
> 
> I believe they are InnoDB related, but as far as I know, I'm not using any 
> InnoDB tables.
> 
> Is it OK to delete them?

If you don't use InnoDB you can delete these files and run MySQL server with 
--skip-innodb option.


-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Victoria Reznichenko
 / /|_/ / // /\ \/ /_/ / /__   [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]