Re: optimization needed

[Chris Elsworth]

On Wed, May 05, 2004 at 01:06:45PM -0400, Brent Baisley wrote:
> Basically, you can't, it's a limitation of the InnoDB format. If you 
> change the table type to MyISAM, that query would be almost 
> instantaneous. But you are probably using InnoDB for a reason, so you 
> may be stuck if you want a record count.

I might be way off base here, but couldn't he use the following index:

> >  INDEX protocol_TimeStamp (time_stamp)

Then do something like COUNT(*) WHERE time_stamp > '1979-';

Would this use the index to resolve it quickly? Or, having written
that and looked at it, will the 30% rule kick in? It probably will,
won't it.. Maybe a FORCE INDEX?

-- 
Chris

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

Re: check for certain characters

[Robert A. Rosenberg]

At 23:51 -0400 on 05/11/2004, Michael Stassen wrote about Re: check 
for certain characters:

Then you could add NOT to Paul's query:

  SELECT * FROM your table WHERE sequence NOT REGEXP '^[atcg]+$';

or, equivalently,

  SELECT * FROM your table WHERE sequence REGEXP '[^atcg]';

I suspect the latter may be faster, but you'd have to try them to be sure.

Note that pattern matching in mysql is case-insensitive by default. 
If that matters to you, then you would need to add the BINARY 
keyword to the WHERE clause:

  WHERE sequence NOT REGEXP BINARY '^[atcg]+$';

or

  WHERE sequence REGEXP BINARY '[^atcg]';

Michael
The need to go BINARY to detect case also requires that sequence be a 
BLOB not a TEXT field (I might have the 
case-sensitive/case-insensitive types reversed),

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

Re: check for certain characters

[Michael Stassen]

Then you could add NOT to Paul's query:

  SELECT * FROM your table WHERE sequence NOT REGEXP '^[atcg]+$';

or, equivalently,

  SELECT * FROM your table WHERE sequence REGEXP '[^atcg]';

I suspect the latter may be faster, but you'd have to try them to be sure.

Note that pattern matching in mysql is case-insensitive by default.   If 
that matters to you, then you would need to add the BINARY keyword to the 
WHERE clause:

  WHERE sequence NOT REGEXP BINARY '^[atcg]+$';

or

  WHERE sequence REGEXP BINARY '[^atcg]';

Michael

[EMAIL PROTECTED] wrote:

the output of the query should be: all the records that contain even one 
letter other than a,t,c or g.

Liz

Quoting Paul DuBois <[EMAIL PROTECTED]>:


At 21:41 -0400 5/11/04, [EMAIL PROTECTED] wrote:

hi,
   I have a field which is a genome sequence and I need to check if
each of

the entries made for the sequence field contains only a,t,c or g in the
string

and no other characters.
how will i give the query???
It depends.  You can find matching values with

WHERE col_name REGEXP '^[atcg]+$'

but you don't say what you want for output, so beyond that it's
difficult
to say what the rest of the query should look like.
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]






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

Re: check for certain characters

[lga2]

the output of the query should be: all the records that contain even one 
letter other than a,t,c or g.

Liz


Quoting Paul DuBois <[EMAIL PROTECTED]>:

> At 21:41 -0400 5/11/04, [EMAIL PROTECTED] wrote:
> >hi,
> > I have a field which is a genome sequence and I need to check if
> each of
> >the entries made for the sequence field contains only a,t,c or g in the
> string
> >and no other characters.
> >how will i give the query???
> 
> It depends.  You can find matching values with
> 
> WHERE col_name REGEXP '^[atcg]+$'
> 
> but you don't say what you want for output, so beyond that it's
> difficult
> to say what the rest of the query should look like.
> 
> -- 
> Paul DuBois, MySQL Documentation Team
> Madison, Wisconsin, USA
> MySQL AB, www.mysql.com
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
> 
> 


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

Re: check for certain characters

[Paul DuBois]

At 21:41 -0400 5/11/04, [EMAIL PROTECTED] wrote:
hi,
I have a field which is a genome sequence and I need to check if each of
the entries made for the sequence field contains only a,t,c or g in the string
and no other characters.
how will i give the query???
It depends.  You can find matching values with

WHERE col_name REGEXP '^[atcg]+$'

but you don't say what you want for output, so beyond that it's difficult
to say what the rest of the query should look like.
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: check for certain characters

[Dathan Vance Pattishall]

Off of the top of my head you can basically do a combination of all letters
in big or (use IN) list. It should be pretty fast. I'm personally leaning to
using REGEXP in mySQL yet, that would match the letters in a string and not
exclude others, unless explicitly told to. Using a REGEXP is slow.



--
DVP
> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, May 11, 2004 5:42 PM
> To: [EMAIL PROTECTED]
> Subject: check for certain characters
> 
> hi,
> I have a field which is a genome sequence and I need to check if each
> of
> the entries made for the sequence field contains only a,t,c or g in the
> string
> and no other characters.
> how will i give the query???
> 
> thanks,
> liz
> 
> --
> 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]

check for certain characters

[lga2]

hi,
I have a field which is a genome sequence and I need to check if each of 
the entries made for the sequence field contains only a,t,c or g in the string 
and no other characters.
how will i give the query???

thanks,
liz

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

Re: Slow login

[Roy Butler]

Jiri,

Gerald said it could be one of two things.  You've just said it's not 
the first.  Think some more about the second (reverse DNS)...

If you don't have the authority/ability to configure DNS in your 
environment, add the systems to each other's hosts files 
(C:\WINNT\system32\drivers\etc\hosts on W2K) and try again - a reboot 
may be required; hey, this is M$.  Connecting by IP addresses does not 
mean that the underlying mechanisms won't go through a host resolution 
process.

If that doesn't eleviate things, consider checking the network 
statistics for errors (netstat -e on W2K) and approach it from that 
angle; verifying proper speed/duplex configuration between your hosts 
and networking equipment.

As a last resort, you can look through your OS (eventvwr on W2K) and/or 
MySQL's log files for indications of the problem.

Good luck,
Roy
--

Date: Tue, 11 May 2004 08:47:13 +0200
To: <[EMAIL PROTECTED]>
From: "Jiri Matejka" <[EMAIL PROTECTED]>
Subject: Re: Slow login
Message-ID: <[EMAIL PROTECTED]>
>> Unfortunatelly it isn't true in my case. I connect to database server in
>> local network and I use IP address, so there is no DNS usage...
>>
>> Jiri Matejka
- Original Message -
From: "gerald_clark" <[EMAIL PROTECTED]>
To: "Jiri Matejka" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Monday, May 10, 2004 3:36 PM
Subject: Re: Slow login
> Slow connections are ususally a DNS or reverse DNS problem.

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

Re: Sorting Varchar

[Sasha Pachev]

[EMAIL PROTECTED] wrote:
How do I set up a column (cost) that contains numbers and text so that 
the numbers will sort the numbers accurately?  Using varchar results in 
a sort based on the first digit, so that I get e.g. 1, 10, 100, 3. . . 
when the command is "order by cost." Almost all of the text is "By County."
Ken:

Consider having two columns - num_val, and text_val, and then order by num_val, 
text_val

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

Sorting Varchar

[kc68]

How do I set up a column (cost) that contains numbers and text so that the 
numbers will sort the numbers accurately?  Using varchar results in a sort 
based on the first digit, so that I get e.g. 1, 10, 100, 3. . . when the 
command is "order by cost." Almost all of the text is "By County."

Ken

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

Re: fastest filesystem for MySQL

[Sasha Pachev]

Robert J Taylor wrote:
Completely depends on your situation -- big files, small files? Lots of 
writes or reads or both? Growing tables/files or lots of large dropped 
tables?

If you're into small files, go Reiser. Big data, JFS or XFS. 
EXT3 is slow, but, IIRC, it also is a true data journaling filesystem 
while some of the others aren't (and that's as deep as I go without a 
life-vest, if you know what I mean!).

Based on what I've seen so far, JFS and XFS do not yet have a solid track record 
of stability with MySQL. This does not mean they could not be good - I just do 
not trust them yet. I do vaguely remember a support case when a very strange 
corruption happened on either one of them - now cannot recall which one. In 3 
years of handling MySQL supoprt (2000-2003), I do not recall reports of table 
corruption on a ReiserFS file system. I do remember corruption cases in the 
early ext3.

If you are using InnoDB, the choice of the filesystem should not be that 
critical because of full caching. For MyISAM, having a good file system is very 
important - unlike InnoDB, MyISAM does not cache the data itself, and relies on 
the OS cache. So you do have a lot of read/write syscalls. To illustrate the 
difference - I recall a case when performance on MyISAM was terrible over NFS 
(well, that is to be expected), but once the table was changed to InnoDB, it 
improved drastically.

--
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: fastest filesystem for MySQL

[JFL]

>> I've heard and read that the Reiser filesystem should be better for
>> MySQL than Ext3. Is this still true?
>>
>> We will be running MySQL on either Red Hat ES 3, Suse or Debian.
>>
Completely depends on your situation -- big files, small files? Lots of 
writes or reads or both? Growing tables/files or lots of large dropped 
tables?
It's for a community site.
Lots of small data reads, less writes, many updates, and few deletes.
If you're into small files, go Reiser. Big data, JFS or XFS. 
EXT3 is slow, but, IIRC, it also is a true data journaling filesystem 
while some of the others aren't (and that's as deep as I go without a 
life-vest, if you know what I mean!).


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

Re: fastest filesystem for MySQL

[Robert J Taylor]

Completely depends on your situation -- big files, small files? Lots of 
writes or reads or both? Growing tables/files or lots of large dropped 
tables?

If you're into small files, go Reiser. Big data, JFS or XFS.  

EXT3 is slow, but, IIRC, it also is a true data journaling filesystem 
while some of the others aren't (and that's as deep as I go without a 
life-vest, if you know what I mean!).

Just saw this on LWN.net:

Benchmarks of EXT2, EXT3, JFS, XFS in various unreal stressful 
scenarios. Interesting, anyway:

http://209.81.41.149/~jpiszcz/index.html

Robert J Taylor
[EMAIL PROTECTED]
JFL wrote:

I've heard and read that the Reiser filesystem should be better for 
MySQL than Ext3. Is this still true?

We will be running MySQL on either Red Hat ES 3, Suse or Debian.

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

Re: questions about bind_param and mysql trace data included

[Brad Lhotsky]

Try this:

On Tue, May 11, 2004 at 12:51:28PM -0700, [EMAIL PROTECTED] wrote:
> 
> 
> below I have a snippet from a trace file read out for a section of code below. The 
> problem seems to be with '$sth->bind_param (2,$rh_row->{prop_str_addr});' The first 
> Bind works fine however the next one in the line listed produces a null entry per 
> the trace file. I have verified that the data is correct in 
> '$rh_row->{prop_str_addr}' and that it is quoted but for some reason myql isn't 
> recieving the data any help would be appreciated.
> 
> <- dbd_st_execute 0 rows
> <- execute= '0E0' at db_load_1-2.pl line 128
> -> bind_param for DBD::mysql::st (DBI::st=HASH(0x8104f14)~0x8104e3c 1 
> ''310-673-5515'')
> <- bind_param= 1 at db_load_1-2.pl line 122
> -> execute for DBD::mysql::st (DBI::st=HASH(0x8104f14)~0x8104e3c)
> -> dbd_st_execute for 08104e48
>   Binding parameters: UPDATE own_info
>SET own_phone_home = '\'310-673-5515\''
>WHERE own_str_addr = NULL
> 
> 
> 
> ###CODE SNIPPETTE
> $dbh = connect_try("**","**");
> foreach $k (keys (%{$ar_info->[1]})){ # retrieves a generic set of fields and 
> uses them to assign values for each row.
> if ($table eq "prop_info"){ # checks which table is being used and assigns 
> the correct SQL statement
> $sth = $dbh->prepare ("UPDATE prop_info
>SET $k = ?
>WHERE prop_str_addr = ?") or
>err_trap("failed to prepare statement\n");
> }elsif ($table eq "own_info"){
> $sth = $dbh->prepare ("UPDATE own_info
>SET $k = ?
>WHERE own_str_addr = ?") or
>err_trap("failed to prepare statement\n");
> 
> 
> }
> $sth->trace(2, "./trace_data.txt");
> foreach $rh_row (@$ar_info) { # iterates through the list of rows and 
> assigns the correct value to the field
> print "::$k=>$rh_row->{$k}"; # this is an internal check to verify what 
> values are being inserted
> $sth->bind_param (1,$rh_row->{$k});

  if($table eq 'prop_info') {

> if ($table eq "prop_str_addr") {
> $sth->bind_param (2,$rh_row->{prop_str_addr});
> }elsif  ($table eq "own_str_addr") {
> $sth->bind_param (2,$rh_row->{own_str_addr});
> }
> $sth->execute() or
> err_trap("failed to execute statement\n");
> 
> }
> print "\n===\n";
> 
> }
> $sth->finish();
> $dbh->disconnect or
> err_trap("failed to disconnect statement\n");
> 
> 
> -- 
> To unsubscribe, e-mail: [EMAIL PROTECTED]
> For additional commands, e-mail: [EMAIL PROTECTED]
>  
> 
> 

-- 
Brad Lhotsky <[EMAIL PROTECTED]>

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

fastest filesystem for MySQL

[JFL]

I've heard and read that the Reiser filesystem should be better for 
MySQL than Ext3. Is this still true?

We will be running MySQL on either Red Hat ES 3, Suse or Debian.

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

Re: Connecting to db via win32 (Word or Excel)

[Daniel Kasak]

Thomas Nyman wrote:

Hi all

I'm a mysql beginner. I have my database up and running and can query 
it via php and apache.

I would like to set things up so that MSExcel and or MSWord can query 
the database and extract info from it. For this reason I downloaded 
the and installed MyODBC-standard-3.51.04-win.exe. No errors where 
generated upon install on my W2K machine. But when I look in 
Datasources ODBC under administrative tools there is no mysql-driver 
installed.

What am I missing? Is there something else that needs to be installed?

Thanks
Thomas

You need to configure a datasource. All you've done so far is install 
the drivers ( and old ones at that ).
In the administrative tools, create a new System DSN. If you need help 
with this, have a look at my page on it:
http://enthalpy.homelinux.org/MySQL/setup.html

Also note that using MS Query from Word or Excel ( or anything else ) 
will *not* work well with MySQL. MS Query seems to be quite buggy. The 
only reliable way of talking to MySQL from Word or Excel is to use ADO.

Dan

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

Re: Saving PDF's as Blobs

[Sasha Pachev]

Lou Olsten wrote:
What SQL statement can I use to insert a PDF into a BLOB column?  I don't want a pointer to the file, I want the actual file stored as a BLOB.  
If the file is on the server already -

insert into pdfs (content) values(load_file('/path/to/file.pdf'))

If the file is on the client, use some client language (C,Perl,PHP), call 
mysql_real_escape_string() on the contents of the file and insert the escaped 
string.



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

Saving PDF's as Blobs

[Lou Olsten]

What SQL statement can I use to insert a PDF into a BLOB column?  I don't want a 
pointer to the file, I want the actual file stored as a BLOB.  

Thanks!

Lou

Re: Importing Text Files in phpMyAdmin vs the Infamous

[Stephen E. Bacher]

>> I have a php website connecting to mysql database.
>> How can i have a user export his database through
>> the webpage to csv format?
>> I would like the user to have a button he can press
>> that would pop up a
>> "save as" screen so he can save his database.

The PHP code to do this would look something like this:

 // -- Do not send any HTML before this point -- //

 header("Content-type: application/vnd.ms-excel");
 header('Content-Disposition: attachment; filename="myfile.xls"');
 header("Content-Transfer-Encoding: binary");

 $r1 = // -- do query here -- //;
 $nf = mysql_num_fields($r1);
 for ($i=0; $i<$nf; $i++) {
  $x = mysql_field_name($r1,$i);
  echo '"' . str_replace('"','""',htmlspecialchars($x)) . '"\t';
 }
 echo "\n";
 while ($a1 = mysql_fetch_array($r1)) {
  for ($i=0; $i<$nf; $i++) {
   $x = $a1[$i];
   echo '"' . str_replace('"','""',htmlspecialchars($x)) . '"\t';
  }
  echo "\n";
 }

Hope this helps.

 - seb


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

Huge query on MERGE tables

[Rodrigo Abt]

Hi everyone,

I have monthly data from 4 years, this means 48 MyISAM tables with identical
structure, all defined and indexed properly (I guess) with 650,000 rows
(approx.) and 99 columns. The tables are stored in a dedicated partition
with 80 Gb of free space.  The server is running Windows 2000 with 696 megs
of RAM, pentium 4 processor and a 7200 rpm hard disk. Mysql version used is
4.0.17

Now from my database, I have the following:

mysql> show fields from fun199801;< One of the 48 tables.

+---+---+--+
-+-+---+
| Field | Type  | Null | Key
| Default | Extra |
+---+---+--+
-+-+---+
| id| int(10) unsigned  |  | MUL
| 0   |   |
| comn  | int(11)   |  | MUL
| 0   |   |
| actc  | int(11)   |  | MUL
| 0   |   |
| ind   | int(10) unsigned  |  | MUL
| 0   |   |
| origin| char(1)   | YES  |
| NULL|   |
| period| varchar(6)| YES  |
| NULL|   |
| pres  | varchar(8)| YES  |
| NULL|   |
| c20   | bigint(20) unsigned   | YES  |
| NULL|   |
| c27   | bigint(20) unsigned   | YES  |
| NULL|   |
| c28   | bigint(20) unsigned   | YES  |
| NULL|   |
| c30   | bigint(20) unsigned   | YES  |
| NULL|   |
| c31   | bigint(20) unsigned   | YES  |
| NULL|   |
| c32   | bigint(20) unsigned   | YES  |
| NULL|   |
| c33   | bigint(20) unsigned   | YES  |
| NULL|   |
| c39   | bigint(20) unsigned   | YES  |
| NULL|   |
| c41   | bigint(20) unsigned   | YES  |
| NULL|   |
| c42   | bigint(20) unsigned   | YES  |
| NULL|   |
...
99 rows in set (0.02 sec)

I made a MERGE table for each year, so I have 4 MERGE tables named
fx1998,fx1999,fx2000 and fx2001

Then I wrote the following query:

SELECT
IFNULL(fx1998.comn,0) as idcomn,
IFNULL(fx1998pt.actc,0) as idactc,
IFNULL(
(CASE
WHEN fx1998.id=stat.id THEN 5
WHEN fx1998.id=soc1998.id THEN 10
ELSE (
CASE
WHEN fx1998.id BETWEEN 1 AND 4999 THEN 1
WHEN fx1998.id BETWEEN 5000 AND 5299 THEN 2
WHEN fx1998.id BETWEEN 5300 AND 5899 THEN 4
WHEN fx1998.id BETWEEN 5900 AND 5999 THEN 3
WHEN (fx1998.id BETWEEN 7000 AND 7699) OR
 (fx1998.id BETWEEN 7900 AND 7949) THEN 7
WHEN (fx1998.id BETWEEN 7700 AND 7899) OR
 (fx1998.id BETWEEN 7950 AND 8699) OR
 (fx1998.id BETWEEN 8750 AND 8999) THEN 8
WHEN fx1998.id BETWEEN 8700 AND 8749 THEN 9
WHEN fx1998.id>=9000 THEN 11
ELSE 6
END)
END),0) as idsoc,
COUNT(DISTINCT fx1998.id) as num,
SUM(c108+c111+c112+c154) as sales,
SUM(c109) as cost,
SUM(c39+c42) as retenc,
1998 as year
FROM
ipt.fx1998 LEFT JOIN utils.soc1998 ON fx1998.id=soc1998.id
 LEFT JOIN utils.stat ON fx1998.id=stat.id
GROUP BY
idcomn,idactc,idsoc
UNION ALL
(the same syntax as the above, but for the remaining 3 years)

Here is the output of the EXPLAIN command for the query:

++--+---+--+-+--
--+-+-+
| table  | type | possible_keys | key  | key_len | ref
| rows| Extra   |
++--+---+--+-+--
--+-+-+
| fx1998 | ALL  | NULL  | NULL |NULL | NULL
| 8079209 | Using temporary; Using filesort |
| soc1998| ref  | ind   | ind  |   4 |
f29a1998pt.rut |   1 | Using index |
| stat   | ref  | ind   | ind  |   4 |
f29a1998pt.rut |   1 | Using index |
| fx1999 | ALL  | NULL  | NULL |NULL | NULL
| 82220

questions about bind_param and mysql trace data included

[smrtalec]

below I have a snippet from a trace file read out for a section of code below. The 
problem seems to be with '$sth->bind_param (2,$rh_row->{prop_str_addr});' The first 
Bind works fine however the next one in the line listed produces a null entry per the 
trace file. I have verified that the data is correct in '$rh_row->{prop_str_addr}' and 
that it is quoted but for some reason myql isn't recieving the data any help would be 
appreciated.

<- dbd_st_execute 0 rows
<- execute= '0E0' at db_load_1-2.pl line 128
-> bind_param for DBD::mysql::st (DBI::st=HASH(0x8104f14)~0x8104e3c 1 
''310-673-5515'')
<- bind_param= 1 at db_load_1-2.pl line 122
-> execute for DBD::mysql::st (DBI::st=HASH(0x8104f14)~0x8104e3c)
-> dbd_st_execute for 08104e48
  Binding parameters: UPDATE own_info
   SET own_phone_home = '\'310-673-5515\''
   WHERE own_str_addr = NULL



###CODE SNIPPETTE
$dbh = connect_try("**","**");
foreach $k (keys (%{$ar_info->[1]})){ # retrieves a generic set of fields and uses 
them to assign values for each row.
if ($table eq "prop_info"){ # checks which table is being used and assigns the 
correct SQL statement
$sth = $dbh->prepare ("UPDATE prop_info
   SET $k = ?
   WHERE prop_str_addr = ?") or
   err_trap("failed to prepare statement\n");
}elsif ($table eq "own_info"){
$sth = $dbh->prepare ("UPDATE own_info
   SET $k = ?
   WHERE own_str_addr = ?") or
   err_trap("failed to prepare statement\n");


}
$sth->trace(2, "./trace_data.txt");
foreach $rh_row (@$ar_info) { # iterates through the list of rows and assigns 
the correct value to the field
print "::$k=>$rh_row->{$k}"; # this is an internal check to verify what 
values are being inserted
$sth->bind_param (1,$rh_row->{$k});
if ($table eq "prop_str_addr") {
$sth->bind_param (2,$rh_row->{prop_str_addr});
}elsif  ($table eq "own_str_addr") {
$sth->bind_param (2,$rh_row->{own_str_addr});
}
$sth->execute() or
err_trap("failed to execute statement\n");

}
print "\n===\n";

}
$sth->finish();
$dbh->disconnect or
err_trap("failed to disconnect statement\n");


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

Re: Slow login

[Sasha Pachev]

Jiri Matejka wrote:
Unfortunatelly it isn't true in my case. I connect to database server in
local network and I use IP address, so there is no DNS usage...
Jiri Matejka
If I remember correctly, this is an old bug Windows-specific that has been fixed 
in the latest release. Try upgrading to 3.23.58 or 4.0.18.

--
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: Problem running MySQL on MacOS X.3

[Rich Allen]

ps should show you something like the following:

Jupiter:~/desktop hcir$ ps uax | grep mysql
root  291   0.0  0.118644   1072  ??  S 3May04   0:00.06 sh  
./bin/safe_mysqld --user=mysql
mysql 338   0.0  2.551720  19872  ??  S 3May04  10:17.86  
/usr/local/mysql-standard-4.1.0-alpha-apple-darwin6.4-powerpc/bin/ 
mysqld
hcir20399   0.0  0.018172344 std  S+   10:52AM   0:00.01  
grep mysql

if you dont see this i would check the error log after you try to start  
the server with something like:

#/usr/local/mysql/bin/mysqld_safe -uuser &

- hcir

On May 11, 2004, at 10:29 AM, Gabriel Ricard wrote:

Did you configure MySQL and run the  
/usr/local/mysql/scripts/mysql_install_db to initialize your data  
directory?

- Gabriel

On May 10, 2004, at 5:21 PM, Tim Jarman wrote:

I have an iBook G4 running MacOS X.3 on Darwin 7.0. I downloaded and  
ran the
binary installer (mysql-standard-4.0.18.pkg and it appeared to work  
fine; I
have /usr/local/mysql and so on as per the docs. I also installed
MySQLStartupItem.

However, I don't actually appear to have a functional installation.  
If I do:

/usr/local/mysql/bin/mysql
or even

sudo /usr/local/mysql/bin/mysql
I get:

ERROR 2002: Can't connect to local MySQL server through socket
'/tmp/mysql.sock' (61)
although /tmp/mysql.sock does exist, which seems to indicate the  
server is
running. But then again ps -a -x doesn't seem to show it, so maybe  
it isn't;
if I do ps -A |fgrep "mysql" on my Linux box, where MySQL is running  
happily,
I get a hit for mysqld_safe plus ten others for mysqld.

I tried starting the server manually as suggested in the docs:

sudo /Library/StartupItems/MySQL/MySQL start
Starting MySQL database server
but when I try running mysql I get error 2002 as before.

I found mention of this problem on FAQTS and in the MySQL mailing  
lists
archive, the latter of which suggested this email address. Any clues  
would be
most welcome! Please cc any replies to me as I am not currently  
subscribed to
any of the MySQL lists.

Thanks in advance,

Tim Jarman



- hcir

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

RE: Set path variable on Windows 2000

[Victor Pendleton]

Globally:
Add c:\mysql\bin to path in the environment variables under the My computer
--> properties window

Session:
set path=%path%;c:\mysql\bin


-Original Message-
From: MightyData
To: MySQL
Sent: 5/11/04 1:50 PM
Subject: Set path variable on Windows 2000

What is the correct syntax to set the path variable on Windows 2000 so I
can
type "mysql" instead of "c:\mysql\bin" at the command prompt?


-
Kirk Bowman   Phone:
972-390-8600
MightyData, LLC
http://www.mightydata.com

-


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

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

Set path variable on Windows 2000

[MightyData]

What is the correct syntax to set the path variable on Windows 2000 so I can
type "mysql" instead of "c:\mysql\bin" at the command prompt?

-
Kirk Bowman   Phone: 972-390-8600
MightyData, LLC http://www.mightydata.com
-


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

Re: how do i encrypt the .frm file with a password

[Robert J Taylor]

How would password protecting without encrypting it be meaningful? 
(Answer: It wouldn't)

Look at PGP/GPG encryption, as an example of private/public key 
encryption. Feed the encryption program uncompressed data and get either 
keyed or password protected data that is encrypted and compressed.

This isn't a MySQL issue, strictly speaking, but I hope this helps you 
find an answer.

references:

http://www.pgp.com
http://gnupg.org  

Robert J Taylor
[EMAIL PROTECTED]
David Crane wrote:

I was considering encrypting the data itself. However, that would impact
performance and our ability to compress it.
We are using access now and it is a 5 cd install. I was hoping I could get
away with password protecting the files to provide
some security.
""David Crane"" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
 

I need to provide some security to a database that I am working on. This
database will be distributed and I need to prevent users from being able
   

to
 

simply copy the files and being able to have complete access to it. I want
to do this: "Encrypt the `.frm' file with a password. This option doesn't
   

do
 

anything in the standard MySQL version. "

(http://dev.mysql.com/doc/mysql/en/CREATE_TABLE.html) I have recompiled
mysql to enable 64 indexes on a table. So, recompiling it is not a
   

problem.
 

Do I need a custom version or MaxDB?

   



 

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

Re: Querying rows between two strings

[Garth Webb]

On Tue, 2004-05-11 at 10:45, Lionel Pitaru wrote:
> Hello
> I would like to make a question: I'm creating a list with a scripting lenguage with 
> MySql database. The list presents username, first name and last name of persons. It 
> has three pairs of filters for username, first name and last name. For each of them 
> the user of the list can type a text as limit for his field with a from and until 
> manner. I would like to create this query with regexp, but i can't make it work.
> An example of working in this:
> 
> UsernameFirst nameLast name
> -
> alberto alberto gomez
> ariel ariel ramirez
> leandro lean123   pass123
> 
> with this filters:
> username from 'a' until 'le'
> first name from 'al' until 'lean'
> there should be the hole list
> 
> but with this filters:
> username from 'a' until 'la'
> first name from 'al' until 'lean'
> there should be this list:
> 
> UsernameFirst nameLast name
> -
> alberto alberto gomez
> ariel ariel ramirez
> 
> I hope i was clear and you could help me with this. 

You can use 'between' to do this.  The only catch is that your ranges
don't completely cover the dataset you want returned in the traditional
manner.  As an example, if we were using numbers, what you are asking
for is something like asking for numbers between 2 and 7 from the list:

1
2.3
4
6
7.8
9

and desiring the result to be

2.3
4
6
7.8

In this case asking for all numbers 2 <= X <= 7 doesn't work because you
want ANY number starting with a 7.  So, to get the affect that you want,
you'd have to ask for 2 <= X <= (7+1).

Likewise, for your first example you'd use:

SELECT * FROM names WHERE username BETWEEN 'a' and 'ld' AND
  first_name BETWEEN 'al' and 'leao'

Note that for the end condition of each comparison the last letter is
increased by one letter so that it completely covers all words that
begin with the target letters.

The second example would be:

SELECT * FROM names WHERE username BETWEEN 'a' and 'lb' AND
  first_name BETWEEN 'al' and 'leao'


HTH


-- 
. Garth Webb
. [EMAIL PROTECTED]
.
. shoes * éå * schoenen * ëí * chaussures * zapatos
. Schuhe * ÏÎÏÎÏÏÏÎÎ * pattini * é * sapatas * ÐÐÑ

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

Re: Problem running MySQL on MacOS X.3

[Gabriel Ricard]

Did you configure MySQL and run the 
/usr/local/mysql/scripts/mysql_install_db to initialize your data 
directory?

- Gabriel

On May 10, 2004, at 5:21 PM, Tim Jarman wrote:

I have an iBook G4 running MacOS X.3 on Darwin 7.0. I downloaded and 
ran the
binary installer (mysql-standard-4.0.18.pkg and it appeared to work 
fine; I
have /usr/local/mysql and so on as per the docs. I also installed
MySQLStartupItem.

However, I don't actually appear to have a functional installation. If 
I do:

/usr/local/mysql/bin/mysql
or even

sudo /usr/local/mysql/bin/mysql
I get:

ERROR 2002: Can't connect to local MySQL server through socket
'/tmp/mysql.sock' (61)
although /tmp/mysql.sock does exist, which seems to indicate the 
server is
running. But then again ps -a -x doesn't seem to show it, so maybe it 
isn't;
if I do ps -A |fgrep "mysql" on my Linux box, where MySQL is running 
happily,
I get a hit for mysqld_safe plus ten others for mysqld.

I tried starting the server manually as suggested in the docs:

sudo /Library/StartupItems/MySQL/MySQL start
Starting MySQL database server
but when I try running mysql I get error 2002 as before.

I found mention of this problem on FAQTS and in the MySQL mailing lists
archive, the latter of which suggested this email address. Any clues 
would be
most welcome! Please cc any replies to me as I am not currently 
subscribed to
any of the MySQL lists.

Thanks in advance,

Tim Jarman

--
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 encrypt the .frm file with a password

[David Crane]

I was considering encrypting the data itself. However, that would impact
performance and our ability to compress it.
We are using access now and it is a 5 cd install. I was hoping I could get
away with password protecting the files to provide
some security.


""David Crane"" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> I need to provide some security to a database that I am working on. This
> database will be distributed and I need to prevent users from being able
to
> simply copy the files and being able to have complete access to it. I want
> to do this: "Encrypt the `.frm' file with a password. This option doesn't
do
> anything in the standard MySQL version. "
>
> (http://dev.mysql.com/doc/mysql/en/CREATE_TABLE.html) I have recompiled
> mysql to enable 64 indexes on a table. So, recompiling it is not a
problem.
> Do I need a custom version or MaxDB?
>
>



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

Re: how do i encrypt the .frm file with a password

[Robert J Taylor]

Exactly. Think "HIPAA" and the methods used to secure data from the 
priying eyes of DBAs (supposedly) under HIPAA/Security.

Dan Greene wrote:

if the data is the concern, not the data structure, why not encrypt the data itself?

 

-Original Message-
From: David Crane [mailto:[EMAIL PROTECTED]
Sent: Tuesday, May 11, 2004 2:15 PM
To: [EMAIL PROTECTED]
Subject: Re: how do i encrypt the .frm file with a password
Since this database will be distributed, our users will be 
administrators.
If they are able to copy the files to another computer and 
set up mysql
themselves, they could get access to the entire database 
directly and export
any or all data. Configuring the service to run as a 
different user would
not prevent this. And, they would have access to the files as 
administrator.
As I see it, the passwords for users are only used by the 
service itself and
passwords are not applied to the files themselves. As for 
operating systems,
we are going to support Windows NT, 2000, & XP.

""David Crane"" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
   

I need to provide some security to a database that I am 
 

working on. This
   

database will be distributed and I need to prevent users 
 

from being able
to
   

simply copy the files and being able to have complete 
 

access to it. I want
   

to do this: "Encrypt the `.frm' file with a password. This 
 

option doesn't
do
   

anything in the standard MySQL version. "

(http://dev.mysql.com/doc/mysql/en/CREATE_TABLE.html) I 
 

have recompiled
   

mysql to enable 64 indexes on a table. So, recompiling it is not a
 

problem.
   

Do I need a custom version or MaxDB?

 

--
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 encrypt the .frm file with a password

[Dan Greene]

if the data is the concern, not the data structure, why not encrypt the data itself?

> -Original Message-
> From: David Crane [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, May 11, 2004 2:15 PM
> To: [EMAIL PROTECTED]
> Subject: Re: how do i encrypt the .frm file with a password
> 
> 
> Since this database will be distributed, our users will be 
> administrators.
> If they are able to copy the files to another computer and 
> set up mysql
> themselves, they could get access to the entire database 
> directly and export
> any or all data. Configuring the service to run as a 
> different user would
> not prevent this. And, they would have access to the files as 
> administrator.
> As I see it, the passwords for users are only used by the 
> service itself and
> passwords are not applied to the files themselves. As for 
> operating systems,
> we are going to support Windows NT, 2000, & XP.
> 
> ""David Crane"" <[EMAIL PROTECTED]> wrote in message
> news:[EMAIL PROTECTED]
> > I need to provide some security to a database that I am 
> working on. This
> > database will be distributed and I need to prevent users 
> from being able
> to
> > simply copy the files and being able to have complete 
> access to it. I want
> > to do this: "Encrypt the `.frm' file with a password. This 
> option doesn't
> do
> > anything in the standard MySQL version. "
> >
> > (http://dev.mysql.com/doc/mysql/en/CREATE_TABLE.html) I 
> have recompiled
> > mysql to enable 64 indexes on a table. So, recompiling it is not a
> problem.
> > Do I need a custom version or MaxDB?
> >
> >
> 
> 
> 
> -- 
> 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 encrypt the .frm file with a password

[David Crane]

Since this database will be distributed, our users will be administrators.
If they are able to copy the files to another computer and set up mysql
themselves, they could get access to the entire database directly and export
any or all data. Configuring the service to run as a different user would
not prevent this. And, they would have access to the files as administrator.
As I see it, the passwords for users are only used by the service itself and
passwords are not applied to the files themselves. As for operating systems,
we are going to support Windows NT, 2000, & XP.

""David Crane"" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> I need to provide some security to a database that I am working on. This
> database will be distributed and I need to prevent users from being able
to
> simply copy the files and being able to have complete access to it. I want
> to do this: "Encrypt the `.frm' file with a password. This option doesn't
do
> anything in the standard MySQL version. "
>
> (http://dev.mysql.com/doc/mysql/en/CREATE_TABLE.html) I have recompiled
> mysql to enable 64 indexes on a table. So, recompiling it is not a
problem.
> Do I need a custom version or MaxDB?
>
>



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

RE: how do i encrypt the .frm file with a password

[Kevin Cowley]

Which OS? 
Doesn't 2000/2000 pro allow you to run a service as one user with similar
access restrictions but allow other users to connect to the service?

Kevin Cowley
R&D
 
Tel: 0118 902 9099 (direct line)
Email: [EMAIL PROTECTED]
Web: http://www.alchemetrics.co.uk

-Original Message-
From: David Crane [mailto:[EMAIL PROTECTED] 
Sent: 11 May 2004 18:21
To: [EMAIL PROTECTED]
Subject: Re: how do i encrypt the .frm file with a password

This will be distributed on Windows computers and not unix/linux.

""David Crane"" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> I need to provide some security to a database that I am working on. This
> database will be distributed and I need to prevent users from being able
to
> simply copy the files and being able to have complete access to it. I want
> to do this: "Encrypt the `.frm' file with a password. This option doesn't
do
> anything in the standard MySQL version. "
>
> (http://dev.mysql.com/doc/mysql/en/CREATE_TABLE.html) I have recompiled
> mysql to enable 64 indexes on a table. So, recompiling it is not a
problem.
> Do I need a custom version or MaxDB?
>
>



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


**
ALCHEMETRICS LIMITED (ALCHEMETRICS)
Mulberry Park, Fishponds Road, Wokingham, Berkshire, RG41 2GX
Tel:  +44 (0) 118 902 9000Fax:  +44 (0) 118 902 9001
This e-mail is confidential and is intended for the use of the addressee only.
If you are not the intended recipient, you are hereby notified that you must 
not use, copy, disclose, otherwise disseminate or take any action based on 
this e-mail or any information herein.
If you receive this transmission in error, please notify the sender
immediately by reply e-mail or by using the contact details above and then
delete this e-mail.
Please note that e-mail may be susceptible to data corruption, interception 
and unauthorised amendment.  Alchemetrics does not accept any liability for 
any such corruption, interception, amendment or the consequences thereof.
**


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

Querying rows between two strings

[Lionel Pitaru]

Hello
I would like to make a question: I'm creating a list with a scripting lenguage with 
MySql database. The list presents username, first name and last name of persons. It 
has three pairs of filters for username, first name and last name. For each of them 
the user of the list can type a text as limit for his field with a from and until 
manner. I would like to create this query with regexp, but i can't make it work.
An example of working in this:

UsernameFirst nameLast name
-
alberto alberto gomez
ariel ariel ramirez
leandro lean123   pass123

with this filters:
username from 'a' until 'le'
first name from 'al' until 'lean'
there should be the hole list

but with this filters:
username from 'a' until 'la'
first name from 'al' until 'lean'
there should be this list:

UsernameFirst nameLast name
-
alberto alberto gomez
ariel ariel ramirez

I hope i was clear and you could help me with this. 

Thanks
Lionel Pitaru

Re: how do i encrypt the .frm file with a password

[David Crane]

This will be distributed on Windows computers and not unix/linux.

""David Crane"" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> I need to provide some security to a database that I am working on. This
> database will be distributed and I need to prevent users from being able
to
> simply copy the files and being able to have complete access to it. I want
> to do this: "Encrypt the `.frm' file with a password. This option doesn't
do
> anything in the standard MySQL version. "
>
> (http://dev.mysql.com/doc/mysql/en/CREATE_TABLE.html) I have recompiled
> mysql to enable 64 indexes on a table. So, recompiling it is not a
problem.
> Do I need a custom version or MaxDB?
>
>



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

Re: how do i encrypt the .frm file with a password

[Paul DuBois]

At 12:45 -0400 5/11/04, David Crane wrote:
I need to provide some security to a database that I am working on. This
database will be distributed and I need to prevent users from being able to
simply copy the files and being able to have complete access to it. I want
to do this: "Encrypt the `.frm' file with a password. This option doesn't do
anything in the standard MySQL version. "
As far as I know, this was done long ago for one specific customer and
is not generally implemented.
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: how do i encrypt the .frm file with a password

[Kevin Cowley]

David

I suggest you need to go back to first principles on Unix security,
applications, and Mysql users.

Firstly and Mysql user is not necessarily a Unix user and vica-versa.

The simplest way to prevent anyone being able to access a file in Unix is
simply to remove the relevant permissions from the file.

If you have Mysql running under the user mysql and you set the permissions
as 700 (rwx --- ---) on the database directories and files then only the
mysql user, which the mysqld deamon runs as has permission to access them.

If it's simply a case of protecting tables then you just need to grant the
appropriate permissions to that Mysql user. Removing update privilege means
they can't change anything, removing delete privilege means they can't
remove anything, removing insert privilege means they can't add anything,
and removing select privilege means they can't access the table.

Kevin Cowley
R&D
 
Tel: 0118 902 9099 (direct line)
Email: [EMAIL PROTECTED]
Web: http://www.alchemetrics.co.uk

-Original Message-
From: David Crane [mailto:[EMAIL PROTECTED] 
Sent: 11 May 2004 17:45
To: [EMAIL PROTECTED]
Subject: how do i encrypt the .frm file with a password

I need to provide some security to a database that I am working on. This
database will be distributed and I need to prevent users from being able to
simply copy the files and being able to have complete access to it. I want
to do this: "Encrypt the `.frm' file with a password. This option doesn't do
anything in the standard MySQL version. "

(http://dev.mysql.com/doc/mysql/en/CREATE_TABLE.html) I have recompiled
mysql to enable 64 indexes on a table. So, recompiling it is not a problem.
Do I need a custom version or MaxDB?



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


**
ALCHEMETRICS LIMITED (ALCHEMETRICS)
Mulberry Park, Fishponds Road, Wokingham, Berkshire, RG41 2GX
Tel:  +44 (0) 118 902 9000Fax:  +44 (0) 118 902 9001
This e-mail is confidential and is intended for the use of the addressee only.
If you are not the intended recipient, you are hereby notified that you must 
not use, copy, disclose, otherwise disseminate or take any action based on 
this e-mail or any information herein.
If you receive this transmission in error, please notify the sender
immediately by reply e-mail or by using the contact details above and then
delete this e-mail.
Please note that e-mail may be susceptible to data corruption, interception 
and unauthorised amendment.  Alchemetrics does not accept any liability for 
any such corruption, interception, amendment or the consequences thereof.
**


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

RE: my.cnf setup

[Dathan Vance Pattishall]

> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, May 11, 2004 7:42 AM
> To: Dathan Vance Pattishall
> Subject: RE: my.cnf setup
> 
> Thanks for the feedback!
> 
> I have made the changes you suggested.  I do have a question about the
> slow query log though.  I added it to my my.cnf file as
> 
> =
> [mysqld]
> 
> port= 3306
> log-slow-queries = /usr/local/mysql/slowlog
> socket  = /tmp/mysql.sock
> skip-locking
> key_buffer = 384M
> max_allowed_packet = 1M
> table_cache = 512
> sort_buffer_size = 2M
> read_buffer_size = 2M
> read_rnd_buffer_size = 2M
> myisam_sort_buffer_size = 64M
> thread_cache = 8
> query_cache_size = 32M
> # Try number of CPU's*2 for thread_concurrency
> thread_concurrency = 4

Increase your tmp_table_size to 32M

Additionally ensure that your indexes on your tables are correct. If you
need some help with that send the query + table structure to the list.




> 
> =
> 
> Is this correct?  Will it just make the file called slowlog?  So far it
> hasnt done anything.  And i did restart the server fyi.
> 
> Anything you can clear up?
> 
> Conner
> >
> >
> >> -Original Message-
> >> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
> >> Sent: Monday, May 10, 2004 2:23 PM
> >> To: [EMAIL PROTECTED]
> >> Subject: my.cnf setup
> >>
> >> I am running a mysql server off a Dell 2650.
> >> Dual 2.8Ghz Intel Xeon processors
> >> 1 Gig of RAM
> >>
> >> The MySQL data comes up rather slowly.  Would like some suggestions on
> >> my
> >> my.cnf file if you had a minute.
> >>
> >> here is what I have
> >> | Handler_read_first | 2|
> >> | Handler_read_key   | 9080 |
> >> | Handler_read_next  | 35   |
> >> | Handler_read_prev  | 1764 |
> >> | Handler_read_rnd   | 723  |
> >> | Handler_read_rnd_next  | 63575|
> >
> > You are not indexing your table right. The read random next is to high.
> > Turn
> > on the slow query log to find out what query is the problem.
> >
> >
> >
> >> | Handler_rollback   | |
> >> | Handler_update | |
> >> | Handler_write  | 154  |
> >> | Key_blocks_used| 51   |
> >> | Key_read_requests  | 13370|
> >> | Key_reads  | 41   |
> >> | Key_write_requests | 67   |
> >> | Key_writes | |
> >> | Max_used_connections   | 4|
> >> | Not_flushed_key_blocks | |
> >> | Not_flushed_delayed_rows   | |
> >> | Open_tables| 18   |
> >> | Open_files | 38   |
> >> | Open_streams   | |
> >> | Opened_tables  | 24   |
> >> | Questions  | 9971 |
> >> | Qcache_queries_in_cache| 173  |
> >> | Qcache_inserts | 173  |
> >> | Qcache_hits| 9665 |
> >> | Qcache_lowmem_prunes   | |
> >> | Qcache_not_cached  | 8|
> >> | Qcache_free_memory | 32908680 |
> >> | Qcache_free_blocks | 1|
> >> | Qcache_total_blocks| 365  |
> >> | Rpl_status | NULL |
> >> | Select_full_join   | 8|
> >> | Select_full_range_join | |
> >> | Select_range   | 66   |
> >> | Select_range_check | |
> >> | Select_scan| 84   |
> >> | Slave_open_temp_tables | |
> >> | Slave_running  | OFF  |
> >> | Slow_launch_threads| |
> >> | Slow_queries   | |
> >> | Sort_merge_passes  | |
> >> | Sort_range | |
> >> | Sort_rows  | 723  |
> >> | Sort_scan  | 16   |
> >> | Ssl_accepts| |
> >> | Ssl_finished_accepts   | |
> >> | Ssl_finished_connects  | |
> >> | Ssl_accept_renegotiates| |
> >> | Ssl_connect_renegotiates   | |
> >> | Ssl_callback_cache_hits| |
> >> | Ssl_session_cache_hits | |
> >> | Ssl_session_cache_misses   | |
> >> | Ssl_session_cache_timeouts | |
> >> | Ssl_used_session_cache_entries | |
> >> | Ssl_client_connects| |
> >> | Ssl_session_cache_overflows| |
> >> | Ssl_session_cache_size | |
> >> | Ssl_session_cache_mode | NONE |
> >> | Ssl_sessions_reused| |
> >> | Ssl_ctx_verify_mode| |
> >> | Ssl_ctx_verify_depth   | |
> >> | Ssl_verify_mode| |
> >> | Ssl_verify_depth   |   

how do i encrypt the .frm file with a password

[David Crane]

I need to provide some security to a database that I am working on. This
database will be distributed and I need to prevent users from being able to
simply copy the files and being able to have complete access to it. I want
to do this: "Encrypt the `.frm' file with a password. This option doesn't do
anything in the standard MySQL version. "

(http://dev.mysql.com/doc/mysql/en/CREATE_TABLE.html) I have recompiled
mysql to enable 64 indexes on a table. So, recompiling it is not a problem.
Do I need a custom version or MaxDB?



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

Re: Row level security in MySQL

[Alec . Cawley]

"Sagar, Sanjeev" <[EMAIL PROTECTED]> wrote on 11/05/2004 16:16:19:

> Hello All,
>
> I have a requirement where I have to implement the Oracle fine grain
access
> control functionality in MySQL 4.0.19
>
> Any ideas will be highly appreciable that how can I implement this in
MySQL.
> I know how to do it in Oracle but not sure if MySQL can do this.

Use the InnoDB table type, which is closely modelled on Oracle structurea
and has row-level locking.

  Alec


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

Re: mysql on redhat9

[Egor Egorov]

"Stephen Camilleri" <[EMAIL PROTECTED]> wrote:
> Thanks Egor..
> 
> Yes I did. I've been trying to create grant tables using
> /usr/bin/mysqladmin but I keep getting access denied for user root.

mysqladmin doesn't create grant tables.
Do you mean setting password for root user?
If so, use -p option for command-line clients to specify password. For example:
mysql -uroot -p

> 
> -Original Message-
> From: Egor Egorov [mailto:[EMAIL PROTECTED] 
> Sent: 11 May 2004 16:52
> To: [EMAIL PROTECTED]
> Subject: Re: mysql on redhat9
> 
> 
> "Stephen Camilleri" <[EMAIL PROTECTED]> wrote:
>> I was running mysql 0.8.12 on a Redhat9 installation. After I rebooted
> 
>> machine I lost access to mysql as root user.
>> 
>> Maybe its a stupid question but I'm new at this... Did I have to do 
>> anything 'persistent' to db before rebooting. I am testing mysqld with
> 
>> ser (SIP Proxy)
> 
> Did you get "access denied" error or what?



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

FW: Row level security in MySQL

[Sagar, Sanjeev]

Thanks !

-Original Message-
From: Paul DuBois [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, May 11, 2004 10:28 AM
To: Sagar, Sanjeev; [EMAIL PROTECTED]
Subject: Re: Row level security in MySQL


At 10:16 -0500 5/11/04, Sagar, Sanjeev wrote:
>Hello All,
>
>I have a requirement where I have to implement the Oracle fine grain 
>access control functionality in MySQL 4.0.19
>
>Any ideas will be highly appreciable that how can I implement this in 
>MySQL. I know how to do it in Oracle but not sure if MySQL can do this.

It cannot. You must implement this in your application.



-- 
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com

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

[PATCH] for mysqldump: full insert statements (db.table) with -c

[Nico Sabbi]

Hi,
with this patch if you use mysqldump with -c the db name will precede the 
table name.
Hopefully this will solve the replication problem I described earlier.

ico
--- mysqldump.c.orig	2004-02-10 19:15:59.0 +0100
+++ mysqldump.c	2004-05-11 17:33:16.407884792 +0200
@@ -670,7 +670,7 @@
 }
 
 if (cFlag)
-  sprintf(insert_pat, "INSERT %sINTO %s (", delayed, opt_quoted_table);
+  sprintf(insert_pat, "INSERT %sINTO %s.%s (", delayed, db, opt_quoted_table);
 else
 {
   sprintf(insert_pat, "INSERT %sINTO %s VALUES ", delayed,
@@ -732,7 +732,7 @@
   fprintf(sql_file, "CREATE TABLE %s (\n", result_table);
 }
 if (cFlag)
-  sprintf(insert_pat, "INSERT %sINTO %s (", delayed, result_table);
+  sprintf(insert_pat, "INSERT %sINTO %s.%s (", delayed, db, result_table);
 else
 {
   sprintf(insert_pat, "INSERT %sINTO %s VALUES ", delayed, result_table);

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

Re: Max

[Mikhail Entaltsev]

Hi,

select YourField from YourTable order by YourField limit 1;

Best regards,
Mikhail.

- Original Message - 
From: "A Z" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, May 11, 2004 3:01 PM
Subject: Max


>
> Hi,
>
> A field of type VarChar() with following syntax:
> ABA1.
> How to get the Max value.  Bearing in mind that value
> ABA10 comes before ABA2.
>
> regards
>
>
>
>
>
>
> 
> Yahoo! Messenger - Communicate instantly..."Ping"
> your friends today! Download Messenger Now
> http://uk.messenger.yahoo.com/download/index.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: Row level security in MySQL

[Paul DuBois]

At 10:16 -0500 5/11/04, Sagar, Sanjeev wrote:
Hello All,

I have a requirement where I have to implement the Oracle fine grain access
control functionality in MySQL 4.0.19
Any ideas will be highly appreciable that how can I implement this in MySQL.
I know how to do it in Oracle but not sure if MySQL can do this.
It cannot. You must implement this in your application.



--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Database structure

[Donny Simonton]

Here are a few examples of my tables.  Table name, # of records, type, and
size.  The database currently has 898 million records in it and it's right
over 100 gigs.

Phrase49,769,178   MyISAM5.3 GB
Volume9,671,996   MyISAM1.1 GB
Word7,790,076   MyISAM942.2 MB
WordMagic128,881,167   MyISAM6.0 GB
WordMagicScores111,060,572   MyISAM7.4 GB
WordWatcher44,270,528   MyISAM4.3 GB
WordPhrases11,154,414   MyISAM450.9 MB
WordRelated13,685,867   MyISAM2.7 GB
WordRelated213,194,313   MyISAM2.6 GB
WordScore68,437,613   MyISAM12.7 GB
WordScoreTemp118,723,375   MyISAM25.3 GB
WordSearch188,769,835   MyISAM11.5 GB
WordStem15,623,221   MyISAM417.3 MB


Donny

> -Original Message-
> From: Brent Baisley [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, May 11, 2004 10:09 AM
> To: Ronan Lucio
> Cc: [EMAIL PROTECTED]
> Subject: Re: Database structure
> 
> Where did you read that 25 million records would be a problem? I've
> heard of people with billions of records in one table. The only
> question would be performance, but indexes would largely take care of
> that. You may run into issues with the physical size of the table and
> the underlying OS not being able to create a large enough file (i.e.
> 4GB). This is a problem with the OS, not MySQL. InnoDB would allow you
> to work around file size limitation in the OS by splitting the database
> into separate files.
> 
> 
> On May 11, 2004, at 10:10 AM, Ronan Lucio wrote:
> 
> > Hi,
> >
> > I´m working in a project of a database that should be grow
> > to more than 25,000,000 of clients.
> >
> > For all I´ve read in MySQL manual pages it´s too much records
> > to place in only one table.
> > So, my main doubt is how to divide it.
> >
> > I divide the client table in few tables according with the different
> > kinds of clients.
> > Even getting some duplicated records and getting some difficulties
> > importing and exporting clients from one table to another it should
> > take the database load cooler.
> >
> > But, I think I´ll need to place all logins and access levels in the
> > same
> > table.
> >
> > Would it be a problem?
> > Any idea how can I deal with it?
> >
> > I´m thinking to use InnoDB tables.
> >
> > Thanks,
> > Ronan
> >
> >
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:
> > http://lists.mysql.com/[EMAIL PROTECTED]
> >
> >
> --
> Brent Baisley
> Systems Architect
> Landover Associates, Inc.
> Search & Advisory Services for Advanced Technology Environments
> p: 212.759.6400/800.759.0577
> 
> 
> --
> 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]

Row level security in MySQL

[Sagar, Sanjeev]

Hello All,

I have a requirement where I have to implement the Oracle fine grain access
control functionality in MySQL 4.0.19

Any ideas will be highly appreciable that how can I implement this in MySQL.
I know how to do it in Oracle but not sure if MySQL can do this.

For details about Oracle fine grain access or row level security, see below
mention link

http://www.unix.org.ua/orelly/oracle/guide8i/ch08_01.htm
 


Best Regards,



 <> 

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

RE: mysql on redhat9

[Peter J Milanese]

Silly question. 

Are you sure it is running? as expected?

P






"Stephen Camilleri" <[EMAIL PROTECTED]>
05/11/2004 11:10 AM
 
To: "Mysql List" <[EMAIL PROTECTED]>
cc: 
Subject:RE: mysql on redhat9


Thanks Egor..

Yes I did. I've been trying to create grant tables using
/usr/bin/mysqladmin but I keep getting access denied for user root.


Stephen

-Original Message-
From: Egor Egorov [mailto:[EMAIL PROTECTED] 
Sent: 11 May 2004 16:52
To: [EMAIL PROTECTED]
Subject: Re: mysql on redhat9


"Stephen Camilleri" <[EMAIL PROTECTED]> wrote:
> I was running mysql 0.8.12 on a Redhat9 installation. After I rebooted

> machine I lost access to mysql as root user.
> 
> Maybe its a stupid question but I'm new at this... Did I have to do 
> anything 'persistent' to db before rebooting. I am testing mysqld with

> ser (SIP Proxy)

Did you get "access denied" error or what?



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




-- 
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 on redhat9

[Stephen Camilleri]

Thanks Egor..

Yes I did. I've been trying to create grant tables using
/usr/bin/mysqladmin but I keep getting access denied for user root.


Stephen

-Original Message-
From: Egor Egorov [mailto:[EMAIL PROTECTED] 
Sent: 11 May 2004 16:52
To: [EMAIL PROTECTED]
Subject: Re: mysql on redhat9


"Stephen Camilleri" <[EMAIL PROTECTED]> wrote:
> I was running mysql 0.8.12 on a Redhat9 installation. After I rebooted

> machine I lost access to mysql as root user.
> 
> Maybe its a stupid question but I'm new at this... Did I have to do 
> anything 'persistent' to db before rebooting. I am testing mysqld with

> ser (SIP Proxy)

Did you get "access denied" error or what?



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




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

Re: Database structure

[Brent Baisley]

Where did you read that 25 million records would be a problem? I've 
heard of people with billions of records in one table. The only 
question would be performance, but indexes would largely take care of 
that. You may run into issues with the physical size of the table and 
the underlying OS not being able to create a large enough file (i.e. 
4GB). This is a problem with the OS, not MySQL. InnoDB would allow you 
to work around file size limitation in the OS by splitting the database 
into separate files.

On May 11, 2004, at 10:10 AM, Ronan Lucio wrote:

Hi,

I´m working in a project of a database that should be grow
to more than 25,000,000 of clients.
For all I´ve read in MySQL manual pages it´s too much records
to place in only one table.
So, my main doubt is how to divide it.
I divide the client table in few tables according with the different
kinds of clients.
Even getting some duplicated records and getting some difficulties
importing and exporting clients from one table to another it should
take the database load cooler.
But, I think I´ll need to place all logins and access levels in the 
same
table.

Would it be a problem?
Any idea how can I deal with it?
I´m thinking to use InnoDB tables.

Thanks,
Ronan


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


--
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search & Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: mysql on redhat9

[Egor Egorov]

"Stephen Camilleri" <[EMAIL PROTECTED]> wrote:
> I was running mysql 0.8.12 on a Redhat9 installation. After I rebooted
> machine I lost access to mysql as root user.
> 
> Maybe its a stupid question but I'm new at this... Did I have to do
> anything 'persistent' to db before rebooting. I am testing mysqld with
> ser (SIP Proxy)

Did you get "access denied" error or what?



-- 
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: How to dump data in db.table syntax?

[Nico Sabbi]

Alle Tuesday 11 May 2004 15:33, Nico Sabbi ha scritto:
> Alle Tuesday 11 May 2004 15:00, Egor Egorov ha scritto:
> > Nico Sabbi <[EMAIL PROTECTED]> wrote:
> > > Hi,
> > > as the title says mysqldump 4.0.18 (and previous versions) doesn't want
> > > to dump data in the format
> > >
> > > insert into db.table values()
> > >
> > > not even using -e or -a.
> > >
> > > Is there any other cli switch that can do this?
> >
> > No, but if you use -B option of mysqldump USE db_name statements will be
> > included in the output.
>
> I see, but this creates me a serious problem:
> I usually replicate my databases  between a number of mysqld servers;
> when I want to copy a database "db" plus some additional metadata from the
> local server to the master I usually run
>
> mysqldump -B db -h local | mysql -h master
>
> that works correctly on the new_server, but totally messes up the current
> slaves that are configured to
>
> replicate-wild-do-table=db.%
>
> because there's no db.table syntax, so the slaves discard the insert.
> Maybe adding
>
> replicate-do-db=db (for all of my dbs) will do the trick?

sorry for replying to myself, but I verified that adding replicate-do-db=db to 
my.cnf doesn't work as I expected

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

mysql on redhat9

[Stephen Camilleri]

I was running mysql 0.8.12 on a Redhat9 installation. After I rebooted
machine I lost access to mysql as root user.
 
Maybe its a stupid question but I'm new at this... Did I have to do
anything 'persistent' to db before rebooting. I am testing mysqld with
ser (SIP Proxy)
 
Steve

Database structure

[Ronan Lucio]

Hi,

I´m working in a project of a database that should be grow
to more than 25,000,000 of clients.

For all I´ve read in MySQL manual pages it´s too much records
to place in only one table.
So, my main doubt is how to divide it.

I divide the client table in few tables according with the different
kinds of clients.
Even getting some duplicated records and getting some difficulties
importing and exporting clients from one table to another it should
take the database load cooler.

But, I think I´ll need to place all logins and access levels in the same
table.

Would it be a problem?
Any idea how can I deal with it?

I´m thinking to use InnoDB tables.

Thanks,
Ronan




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

Re: Validation/Linking Table Question

[Lou Olsten]

Bart,

One other thing... based on your description of your needs, you seem to have
a one-to-many relationship between computers and users.  In that case, there
wouldn't be a need for the intersection table comp_user_link.  You could
simply add a user_id column to your computers table, make it allow nulls
(for computers that don't have users), then your query would look like this
and you would have eliminated the overhead of an extra table.

select * from users left join computers on (users.user_id =
computers.user_id)
 where computers.user_id is NULL;

Lou

- Original Message - 
From: "Bart Nessux" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Monday, May 10, 2004 8:07 PM
Subject: Re: Validation/Linking Table Question


> Thanks Lou, I'll give this a go!
>
>
> >From: "Lou Olsten" <[EMAIL PROTECTED]>
> >Reply-To: "Lou Olsten" <[EMAIL PROTECTED]>
> >To: "Bart Nessux" <[EMAIL PROTECTED]>,<[EMAIL PROTECTED]>
> >Subject: Re: Validation/Linking Table Question
> >Date: Mon, 10 May 2004 17:52:38 -0400
> >
> >Assuming that your pre 4.1, meaning that you can't use a subquery
this
> >will do it, BUT comp_id must be set to allow NULLS for this to work
> >otherwise the optimizer will handle it differently
> >(http://dev.mysql.com/doc/mysql/en/LEFT_JOIN_optimisation.html).  It is
> >legal to create a primary key on a column that allows nulls, so it's just
a
> >question of whether or not you want to do that.
> >
> >select * from users left join comp_user_link on (users.user_id =
> >comp_user_link.user_id)
> >where comp_user_link.comp_id is null;
> >
> >
> >- Original Message -
> >From: "Bart Nessux" <[EMAIL PROTECTED]>
> >To: <[EMAIL PROTECTED]>
> >Sent: Monday, May 10, 2004 1:38 PM
> >Subject: Validation/Linking Table Question
> >
> >
> > > I have three tables in a MySQL DB... among others:
> > >
> > > 1. computers
> > > 2. users
> > > 3. comp_user_link
> > >
> > > This particular DB is used for inventory and tracking purposes. I
think
> > > the table names are self explanatory. Computers has 'comp_id'... users
> > > has 'user_id' as their primary indexed keys. These keys are linked
> > > together in the comp_user_link table. A user may have more than one
> > > computer (For example, Bob may have a desktop and a laptop at the same
> > > time), but no computer may be linked to more than one user
> > > simultaneously (Bob and Tom should never have the same laptop at the
> > > same time).
> > >
> > > Anyway, what I'd like to do is to find all 'user_ids' that aren't
linked
> > > to a computer. Any tips on how to arrange a SELECT statement to do
that?
> > >
> > > Thanks,
> > >
> > > Bart
> > >
> > > --
> > > MySQL General Mailing List
> > > For list archives: http://lists.mysql.com/mysql
> > > To unsubscribe:
> >http://lists.mysql.com/[EMAIL PROTECTED]
> > >
> >
>
> _
> Best Restaurant Giveaway Ever! Vote for your favorites for a chance to win
> $1 million! http://local.msn.com/special/giveaway.asp
>


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

Re: Multiple Mysqld Stability and maintanability

[John Thorpe]

We have been running two 3.23.47 and one 4.0.4 (innodb) on
a single machine the past 2 years.  We haven't had stability
or any ongoing problems.  If something happens to one instance,
it has not affected any other. The main issue is resource
allocation.  Also be sure that the different instances do not
share the same directories, tmp space as well.
John

Winner H Manurung wrote:

Dear All,

I was an Oracle dba, now my new company want to use Mysql 4.0.18. Does
anybody here has experience of running multiple mysqld (i.e. multiple
instance on one machine). Is it stable and totally independent to each
other?
Thank you for your answer
Winner

Mau dapat hadiah jutaan rupiah, ikuti game dan quiz-nya di http://www.m-stars.net




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

Re: Problem running MySQL on MacOS X.3

[Brent Baisley]

If you don't have a process mysqld running, then mysql isn't running. 
You may try starting up mysql directly (just type mysqld), rather than 
through the startup script or through mysqld_safe, since they suppress 
the error messages. You should then see some error message that may 
help determine why mysql isn't running.
Also, to access mysql, you should provide user name and password. You 
won't be able to access it if you don't, unless it's configured that 
way.
mysql -u username -p

Keep in mind that the username and password are not related to the 
username and password in the OS. Many people trying to run it on a Mac 
miss this point.

You may also try running the repair disk permissions from Apple's Disk 
Utility and then installing MySQL again. In the past there had been 
privilege issues on certain folders that prevented MySQL from running 
or installing correctly.

That said, I've been running MySQL on a Mac for quite some time without 
a problem. So if will run. There are some known issues with running 4.1 
on a Mac.

On May 10, 2004, at 5:21 PM, Tim Jarman wrote:

I have an iBook G4 running MacOS X.3 on Darwin 7.0. I downloaded and 
ran the
binary installer (mysql-standard-4.0.18.pkg and it appeared to work 
fine; I
have /usr/local/mysql and so on as per the docs. I also installed
MySQLStartupItem.

However, I don't actually appear to have a functional installation. If 
I do:

/usr/local/mysql/bin/mysql
or even

sudo /usr/local/mysql/bin/mysql
I get:

ERROR 2002: Can't connect to local MySQL server through socket
'/tmp/mysql.sock' (61)
although /tmp/mysql.sock does exist, which seems to indicate the 
server is
running. But then again ps -a -x doesn't seem to show it, so maybe it 
isn't;
if I do ps -A |fgrep "mysql" on my Linux box, where MySQL is running 
happily,
I get a hit for mysqld_safe plus ten others for mysqld.

I tried starting the server manually as suggested in the docs:

sudo /Library/StartupItems/MySQL/MySQL start
Starting MySQL database server
but when I try running mysql I get error 2002 as before.

I found mention of this problem on FAQTS and in the MySQL mailing lists
archive, the latter of which suggested this email address. Any clues 
would be
most welcome! Please cc any replies to me as I am not currently 
subscribed to
any of the MySQL lists.

Thanks in advance,

Tim Jarman

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


--
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search & Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: How to dump data in db.table syntax?

[Nico Sabbi]

Alle Tuesday 11 May 2004 15:00, Egor Egorov ha scritto:
> Nico Sabbi <[EMAIL PROTECTED]> wrote:
> > Hi,
> > as the title says mysqldump 4.0.18 (and previous versions) doesn't want
> > to dump data in the format
> >
> > insert into db.table values()
> >
> > not even using -e or -a.
> >
> > Is there any other cli switch that can do this?
>
> No, but if you use -B option of mysqldump USE db_name statements will be
> included in the output.

I see, but this creates me a serious problem:
I usually replicate my databases  between a number of mysqld servers;
when I want to copy a database "db" plus some additional metadata from the 
local server to the master I usually run

mysqldump -B db -h local | mysql -h master

that works correctly on the new_server, but totally messes up the current 
slaves that are configured to 

replicate-wild-do-table=db.%

because there's no db.table syntax, so the slaves discard the insert.
Maybe adding 

replicate-do-db=db (for all of my dbs) will do the trick?

>
> > Another question: is there a way to dump all dbs that DON'T match a
> > pattern without resorting to pipes / grep -v / xargs?
>
> No, mysqldump doesn't have such option.
>

it's a pity :(

Thanks,
Nico

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

Re: Database design

[Brent Baisley]

I'm not sure what the maximum number of tables are per database (512? 
1024?), but I wouldn't split your data unless you really have to. 70 
million records may seem like a lot for a table, but it's really not. 
Especially if the physical size of your data is small. Tables with 
billions of records is not unusual for MySQL. The physical size of the 
table is limited by the operating system you are running it on, which 
may mean 4GB. MySQL itself can handle terabyte sized tables.

You can also look into using InnoDB as your table type, which would 
allow you to split your database into separate files without the need 
to change any of your programming to reference the data.

On May 10, 2004, at 7:30 PM, Scott Haneda wrote:

I can not seem to find the section in the manual that talks about the 
max
number of tables MySql can use, can someone point me please?

I have been asked to build a database which could have some potentially
interesting storage needs.
There will be a users table, there can be x users, if all goes well, x 
will
be 1000's.

Each user will be able to upload any number of records, with 100,000 
being
the most.  Average would be about 10,000 records, but I want to plan 
this as
if average was 70,000.

The 70,000 records will have the following structure:
Id, first name, last name
So the table will be relatively meager in its storage needs.  Lets 
call this
table user_contacts.

If users become day 1000 and each of those users has 70,000 
user_contacts,
that would be 70,000 * 1000 total records in one table, as users grow, 
this
becomes perhaps too many records in one table.  Or at least the 
potential
for it.

My next option would be to make a new table, user_contact-userid and 
make
one for each user, would would then mean, rather than one table with a 
lot
of records in it, there would be many tables with a max of 100,000 
records
in it.

Can someone share with me their thoughts and suggestions on this?
--
-
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]


--
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search & Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: How to dump data in db.table syntax?

[Egor Egorov]

Nico Sabbi <[EMAIL PROTECTED]> wrote:
> Hi,
> as the title says mysqldump 4.0.18 (and previous versions) doesn't want
> to dump data in the format 
> 
> insert into db.table values()
> 
> not even using -e or -a.
> 
> Is there any other cli switch that can do this?

No, but if you use -B option of mysqldump USE db_name statements will be included in 
the output.

> 
> Another question: is there a way to dump all dbs that DON'T match a 
> pattern without resorting to pipes / grep -v / xargs?
> 

No, mysqldump doesn't have such option.



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

Max

[A Z]

Hi,

A field of type VarChar() with following syntax:
ABA1.
How to get the Max value.  Bearing in mind that value
ABA10 comes before ABA2.

regards







Yahoo! Messenger - Communicate instantly..."Ping" 
your friends today! Download Messenger Now 
http://uk.messenger.yahoo.com/download/index.html

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

Re: Database design.. Asking again

[Chris Torske]

Scott Haneda wrote:
I can not seem to find the section in the manual that talks about the max
number of tables MySql can use, can someone point me please?
I have been asked to build a database which could have some potentially
interesting storage needs.
There will be a users table, there can be x users, if all goes well, x will
be 1000's.
Each user will be able to upload any number of records, with 100,000 being
the most.  Average would be about 10,000 records, but I want to plan this as
if average was 70,000.
The 70,000 records will have the following structure:
Id, first name, last name
So the table will be relatively meager in its storage needs.  Lets call this
table user_contacts.
If users become day 1000 and each of those users has 70,000 user_contacts,
that would be 70,000 * 1000 total records in one table, as users grow, this
becomes perhaps too many records in one table.  Or at least the potential
for it.
My next option would be to make a new table, user_contact-userid and make
one for each user, would would then mean, rather than one table with a lot
of records in it, there would be many tables with a max of 100,000 records
in it.
Can someone share with me their thoughts and suggestions on this?

If anyone thinks I should just allow one table to store all this, with that
table having 70 million records in it, then I can of course go that road as
well.
Another thing you may want to also consider when using huge databases 
(10M+); is the amount of time for searching and the overhead that would 
also cause.  A with any search, regardless of the size of the database, 
it will take a small amount of time.  Sure that amount of time would be 
around a couple seconds if even that.  While a 10M record database could 
take significantly more.  Which most likely that amount, may be even 
closer to about a minute or more.  Now I don't know if a delay for 
searching the database would be a critical thing or not for you.  Now of 
course, these time amounts won't be the same for every machine; nor are 
these time amounts be exact.  Now if your are not in a time critical 
area, then  you most likely won't care about this.  Though, you may want 
to just do a  simple query through a table, on your worst case 
environment; which from what you say may be up to about 70M records,and 
get an idea how much time it would take.

Chris

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

Connecting to db via win32 (Word or Excel)

[Thomas Nyman]

Hi all

I'm a mysql beginner. I have my database up and running and can query it 
via php and apache.

I would like to set things up so that MSExcel and or MSWord can query 
the database and extract info from it. For this reason I downloaded the 
and installed MyODBC-standard-3.51.04-win.exe. No errors where generated 
upon install on my W2K machine. But when I look in Datasources ODBC 
under administrative tools there is no mysql-driver installed.

What am I missing? Is there something else that needs to be installed?

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

timestamps and timezones

[joe collins]

I see that
LOCALTIMESTAMP and LOCALTIMESTAMP() are synonyms for NOW()
when I use now() I get the database time but
I have a webhost based in US (Pacific Time), however most database events
will be generated from BST and this is the time zone I want to use to
populate audit trail tables etc. I could programmatically compensate the US
time zone by adding the hours to make up the BSTis there any neater way
of doing this, as this solution would involve some hard coding.

Kind regards

Joe


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

RE: Database design.. Asking again

[electroteque]

Man 3 times same thread !

What I could consider to you, I don't really understand what you are getting
at, what is wrong with 1000 users firstly ? And in the entry table store
their userID which is stored in a session when they login ? So when they
enter in data it stores their userID into a column , is that right ?

userID = autoinc userID in the user table

> -Original Message-
> From: Scott Haneda [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, May 11, 2004 2:50 PM
> To: MySql
> Subject: Database design.. Asking again
>
>
> Sorry for the post again, I hijacked a thread and wanted to get
> this on the
> correct track.
>
> I can not seem to find the section in the manual that talks about the max
> number of tables MySql can use, can someone point me please?
>
> I have been asked to build a database which could have some potentially
> interesting storage needs.
>
> There will be a users table, there can be x users, if all goes
> well, x will
> be 1000's.
>
> Each user will be able to upload any number of records, with 100,000 being
> the most.  Average would be about 10,000 records, but I want to
> plan this as
> if average was 70,000.
>
> The 70,000 records will have the following structure:
> Id, first name, last name
>
> So the table will be relatively meager in its storage needs.
> Lets call this
> table user_contacts.
>
> If users become day 1000 and each of those users has 70,000 user_contacts,
> that would be 70,000 * 1000 total records in one table, as users
> grow, this
> becomes perhaps too many records in one table.  Or at least the potential
> for it.
>
> My next option would be to make a new table, user_contact-userid and make
> one for each user, would would then mean, rather than one table with a lot
> of records in it, there would be many tables with a max of 100,000 records
> in it.
>
> Can someone share with me their thoughts and suggestions on this?
>
>
> If anyone thinks I should just allow one table to store all this,
> with that
> table having 70 million records in it, then I can of course go
> that road as
> well.
> --
> -
> 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 General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

How to dump data in db.table syntax?

[Nico Sabbi]

Hi,
as the title says mysqldump 4.0.18 (and previous versions) doesn't want
to dump data in the format 

insert into db.table values()

not even using -e or -a.

Is there any other cli switch that can do this?

Another question: is there a way to dump all dbs that DON'T match a 
pattern without resorting to pipes / grep -v / xargs?

Thanks,
Nico

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

Strange DBI error

[Fagyal, Csongor]

Hi,

I am using DBI from mod_perl, and sometimes get the following error:

" You have an error in your SQL syntax.  Check the manual that 
corresponds to your MySQL server version for the right syntax to use 
near '-63, 20' at line 1 "

This is given me right at:
eval {
   $dbh = 
DBI->connect("DBI:mysql:database=$db;host=$host:$port", $user, $pass,
   {'RaiseError' => 1} );
};
($db, $host, $user, $port and $pass are constants.)

I am using "mysql  Ver 12.22 Distrib 4.0.17, for pc-linux (i686)" on 
RedHat 9.

Any ideas?

Thank you,
- Csongor


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

Re: NDB Cluster configuration

[Mikael Ronström]

Hi,
It sounds as if you if you have some problem with the configuration 
file. Have you
introduced the second computer and its hostname into the configuration 
file?
Also the node on the second computer need to refer to this second 
computer.
If your problem continue please attach the configuration file.

Rgrds Mikael

2004-05-11 kl. 02.43 skrev J Brian Ismay:

I am trying to setup an NDB Cluster database for testing purposes. I 
have no
problems getting things to work when all of my DB and API nodes are 
located
on one machine. I run into trouble when I try to have one machine run 
the
management server, a db node, and an API node, while the second 
machine runs
another db node. The db node on the second machine appears to talk to 
the
management node and seems to start properly, but the mgmtclient never 
shows
it as actually being a part of the cluster. The same behavior is 
observed
when trying to start an API node on a second machine. Can anyone help?

J Brian Ismay
[EMAIL PROTECTED]


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mikael Ronström, Senior Software Architect
MySQL AB, www.mysql.com
Clustering:
http://www.infoworld.com/article/04/04/14/HNmysqlcluster_1.html
http://www.eweek.com/article2/0,1759,1567546,00.asp



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