Re: optimization needed

2004-05-11 Thread 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

Re: check for certain characters

2004-05-11 Thread 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 lat

Re: check for certain characters

2004-05-11 Thread 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

Re: check for certain characters

2004-05-11 Thread 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 > >th

Re: check for certain characters

2004-05-11 Thread 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 v

RE: check for certain characters

2004-05-11 Thread 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.

check for certain characters

2004-05-11 Thread 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.

Re: Slow login

2004-05-11 Thread 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\hos

Re: Sorting Varchar

2004-05-11 Thread 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 te

Sorting Varchar

2004-05-11 Thread 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

Re: fastest filesystem for MySQL

2004-05-11 Thread 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 f

Re: fastest filesystem for MySQL

2004-05-11 Thread 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/f

Re: fastest filesystem for MySQL

2004-05-11 Thread 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

Re: questions about bind_param and mysql trace data included

2004-05-11 Thread 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

fastest filesystem for MySQL

2004-05-11 Thread 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.my

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

2004-05-11 Thread 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

Re: Saving PDF's as Blobs

2004-05-11 Thread 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,

Saving PDF's as Blobs

2004-05-11 Thread 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

2004-05-11 Thread 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

Huge query on MERGE tables

2004-05-11 Thread 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 w

questions about bind_param and mysql trace data included

2004-05-11 Thread 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

Re: Slow login

2004-05-11 Thread 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

Re: Problem running MySQL on MacOS X.3

2004-05-11 Thread 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.

RE: Set path variable on Windows 2000

2004-05-11 Thread 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 path variable on Windows 2000

2004-05-11 Thread 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,

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

2004-05-11 Thread 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. T

Re: Querying rows between two strings

2004-05-11 Thread 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

Re: Problem running MySQL on MacOS X.3

2004-05-11 Thread 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 an

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

2004-05-11 Thread 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]> wro

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

2004-05-11 Thread 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 PROTE

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

2004-05-11 Thread 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

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

2004-05-11 Thread 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 n

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

2004-05-11 Thread 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

Querying rows between two strings

2004-05-11 Thread 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 a

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

2004-05-11 Thread 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 > s

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

2004-05-11 Thread 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'

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

2004-05-11 Thread 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

RE: my.cnf setup

2004-05-11 Thread 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 l

how do i encrypt the .frm file with a password

2004-05-11 Thread 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 d

Re: Row level security in MySQL

2004-05-11 Thread 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 h

Re: mysql on redhat9

2004-05-11 Thread 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 optio

FW: Row level security in MySQL

2004-05-11 Thread 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 i

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

2004-05-11 Thread 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

Re: Max

2004-05-11 Thread 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. > H

Re: Row level security in MySQL

2004-05-11 Thread 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 MySQ

RE: Database structure

2004-05-11 Thread 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 WordMagic12

Row level security in MySQL

2004-05-11 Thread 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

RE: mysql on redhat9

2004-05-11 Thread 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 t

RE: mysql on redhat9

2004-05-11 Thread 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

Re: Database structure

2004-05-11 Thread 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 b

Re: mysql on redhat9

2004-05-11 Thread 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 testin

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

2004-05-11 Thread 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

mysql on redhat9

2004-05-11 Thread 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

2004-05-11 Thread 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

Re: Validation/Linking Table Question

2004-05-11 Thread 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 allo

Re: Multiple Mysqld Stability and maintanability

2004-05-11 Thread 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

Re: Problem running MySQL on MacOS X.3

2004-05-11 Thread 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

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

2004-05-11 Thread 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

Re: Database design

2004-05-11 Thread 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

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

2004-05-11 Thread 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

Max

2004-05-11 Thread 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"

Re: Database design.. Asking again

2004-05-11 Thread 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 u

Connecting to db via win32 (Word or Excel)

2004-05-11 Thread 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 er

timestamps and timezones

2004-05-11 Thread 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 c

RE: Database design.. Asking again

2004-05-11 Thread 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 colum

How to dump data in db.table syntax?

2004-05-11 Thread 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 res

Strange DBI error

2004-05-11 Thread 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->

Re: NDB Cluster configuration

2004-05-11 Thread 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