Re: Fulltext search -- no wildcards in phrases?

[Shamit Verma]

The replay on the webpage says that:

"Nope. That would be a really slow search since mysql cant use any indexes
and a table
scan would be the only way to find it."

Then even "LIKE" operator should suffer from the same performance drawback,
how does LIKE operator work with indexes?

Shamit Verma,
http://www.vshamit.com
- Original Message -
From: "Bernhard Döbler" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, April 01, 2003 3:01 PM
Subject: Fw: Fulltext search -- no wildcards in phrases?


I once got this answer to a similar question on another group.

http://www.mail-archive.com/[EMAIL PROTECTED]/msg00280.html

Bernhard

- Original Message -
From: "Nick Arnett" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Thursday, March 27, 2003 8:59 PM
Subject: Fulltext search -- no wildcards in phrases?


> It appears to me that fulltext phrase searches cannot include wildcards.
> For example, I would expect "app* serv*" to match "application server,"
> "application services," etc.  But it returns no results, so I'm having to
> run each variation separately.  Can anyone confirm that wildcards, indeed,
> can't be used in phrase searches.  I'm doing these in Boolean mode because
I
> need exact counts of occurrences.  This is on MySQL-4.0.12-nt.
>

--
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: primary key/foreign key constraints with InnoDB

[Stefan Hinz]

Jeff,

> I'm wondering if its somehow possible to create a pk/fk constraint for
> the table below

> create table Example (
> id int not null auto_increment primary key,
> table_name enum('TabA','TabB') not null,
> table_id int not null
> ) type = InnoDB;

> if table_name is 'TabA', then I want to make sure the row exists in
> TabA. Likewise if table_name is 'TabB'

You can find the syntax for MySQL / InnoDB and a good example here:

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

To avoid trouble, consider this sentence from that page:

Both tables have to be InnoDB type and there must be an index where
the foreign key and the referenced key are listed as the FIRST
columns. InnoDB does not auto-create indexes on foreign keys or
referenced keys: you have to create them explicitly.

The example on that page, however, shows exactly how you'd do that.

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

[filter fodder: sql, mysql, query]


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

Re: Enum Columns

[Stefan Hinz]

Ruben,

> I has a column defined as 
> enum not null (percent,absolute) default value percent (in psuedocode)
> and I was able to update a record which filled it either with a NULL or
> an empty string.

> How does this happen?  the value should be either percent or absolute,
> or the update should just fail.

>From the manual:

If you insert an invalid value into an ENUM (that is, a string not
present in the list of allowed values), the empty string is inserted
instead as a special error value. This string can be distinguished
from a 'normal' empty string by the fact that this string has the
numerical value 0.

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

Furthermore, MySQL does only very basic checking of values. Here's an
example:

mysql> CREATE TABLE t_enum (
->  a ENUM('percent','absolute') NOT NULL DEFAULT 'percent'
-> ) TYPE=MyISAM;
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO t_enum VALUES
->  ('percent'),('absolute'),('wrong_value'),(NULL)
-> ;
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 2

mysql> SELECT * FROM t_enum;
+--+
| a|
+--+
| percent  |
| absolute |
|  |
|  |
+--+
4 rows in set (0.00 sec)

As you can see, the NULL value is treated as a wrong value (because
the column is declared NOT NULL), so it is inserted as an empty string
(the special error value).

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

[filter fodder: sql, mysql, query]


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

Re: Mac OS X package problems

[Stefan Hinz]

Todd,

> At startup, the computer says something about starting a MySQL server.
> Could it be that the startup item is still trying to start mysqld even 
> though it no longer exists and in the process is tying up port 3306? 

Comparing it to Windows (I've not enough money to run an Apple ;-) you
should be able to have a symbolic link that tries to start something
that doesn't exist anymore.

> (I'm grasping at straws here. I tried manually installing the latest 
> 3.0 version and when I tried to run safe_mysqld it said that 
> libexec/mysqld did not exist, and, sure enough, there wasn't even a 
> libexec directory.)

You could try and start the server directly. like that:

usr/bin/mysql> mysqld &

This should start the MySQL server. If an error occurs during startup,
you can check the error log to see what's the problem. The error log
is called hostname.err (where hostname is the name of your machine),
and it's located in your MySQL data directory (e.g.
/var/lib/mysql/data/).

I hope this will help you track down your problem, and hopefully solve
it.

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

[filter fodder: sql, mysql, query]


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

Re: Optimize query, avoid 'using temporary'

[Stefan Hinz]

Eric,

> I have a query which I did several months ago, it recently caught my
> attention since it seems to be very slow when done a lot of times, which 
> causes a very loaded server.

> The query I used looks like this:
> SELECT files.file_id, filename FROM  access, files WHERE 
> directory_id="0" AND lang_id="1" AND ( files.file_id=access.conn_id AND 
> access.group_id IN (1) AND access.class_id="4" AND class_tbl="file" ) 
> group by file_id order by filename;

Just a short note on your query: If you compare integers in a text
context (like lang_id="1"), MySQL cannot use indexes that it could
possibly otherwise use. So rewrite those parts (e.g. lang_id=1).

Another advice would be to try a LEFT JOIN of access and files.

And, of course, you could add some indexes, on the filename and the
file columns.

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

[filter fodder: sql, mysql, query]


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

Re: PL/SQL, Views, Functions, Proceedures

[Shamit Verma]

Hi,

If you desperately need Views/Triggers/Stored procedures. Then there is way
to use some of them on MySQL if:
1. You can use Windows/MAC in your project (at least for the clients).
2. Amount of Data is not extremely large.

I have not tested this solution thoroughly, but it worked for me in one of
my projects.

Solution:
1. Create a new Database in MS Access.  Import you MySQL table in MS Access
using MyODBC.
2. Apply latest JET ( MS Access ) and MDAC service pack from Microsoft site.
3. Now you can create views on tables that were imported into access.

Now, at the client end, instead of accessing MySQL through MyODBC, user
applications can connect to MySQL through Ms Access (Using ODBC, ADO,  JDBC
and so on.).

In my project, views were required by a reporting tool. I created a view
that used 5 tables and had proper where clauses in place. All the report
development team had to worry about was a "Select * from ... " query ( and
filling some parameters ).

The MySQL database was not a large database. It had nearly half a million
records. Performance of MySQL + MS Access was good. I did not test it with a
really large database.

This is more of a Hack, but it worked fine in the given situation. And
fortunately, MS Access Driver is free of cost and included in most Windows
installation.

Regards,
Shamit Verma
http://www.vshamit.com

- Original Message -
From: "John Griffin" <[EMAIL PROTECTED]>
To: "hemanth" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Tuesday, April 01, 2003 9:09 PM
Subject: RE: PL/SQL, Views, Functions, Proceedures


Hi,

PL/SQL is an Oracle proprietary language and can not be used. I know that
the good people at MySQL are planning to add a language to MySQL but I do
not think that there is a target release for this. Views, Functions and
Procedures are also on the list of things to add to MySQL but I do not think
that there is a timeline for these things.

Adding anything to the execution path of an application, a database
application for example, will naturally make it slower. That said, the
people at MySQL have been very good at maintaining and in some cases
improving the performance of the database so I would not worry about new
features slowing the database.

Hopefully someone from MySQL can also respond and give more information than
I have.

-Original Message-
From: hemanth [mailto:[EMAIL PROTECTED]
Sent: Tuesday, April 01, 2003 12:40 AM
To: [EMAIL PROTECTED]
Subject: PL/SQL, Views, Functions, Proceedures


Hi,

Why MySQL does not support PL/SQL, Views, Functions, Procedures,
Sub queries.  Will that be added in forthcoming MySQL version.

If it above mentioned features are added will the database becomes
slow.

Pls. give me some information on this issue.

Thanks in Advance.

HEMANTH





--
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: load data infile not being replicated properly....

[Jeff Kilbride]

Another problem with load data and replication? I've identified 2 confirmed
bugs in the last 2 releases. Honestly, I've pretty much given up on using
load data on my master server for the time being. I'm now using scripts that
generate extended insert statements instead.

--jeff

- Original Message -
From: "Andrew Braithwaite" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, April 01, 2003 10:09 AM
Subject: load data infile not being replicated properly


> Hi all,
>
> I've seen a strange replication problem with MySQL v4.0.12.
>
> Steps to duplicate...
>
> I have a table 'andrew'..
>
> mysql> desc andrew;
> +---+-+--+-+-+---+
> | Field | Type| Null | Key | Default | Extra |
> +---+-+--+-+-+---+
> | f1| varchar(10) | YES  | | NULL|   |
> | f2| varchar(10) | YES  | | NULL|   |
> +---+-+--+-+-+---+
> 2 rows in set (0.00 sec)
>
> And a text file '/home/andrew/upload' that looks like this...
>
> hell0,hello
> hi,hi
>
> When I issue the following command on the master:
>
> mysql> LOAD DATA LOCAL INFILE '/home/andrew/upload' into table andrew
FIELDS
> TERMINATED BY ',' lines terminated by '\n';
> Query OK, 2 rows affected (0.01 sec)
> cords: 2  Deleted: 0  Skipped: 0  Warnings: 0
>
> The resulting table on the master looks like this..
>
> mysql> select * from andrew;
> +---+---+
> | f1| f2|
> +---+---+
> | hell0 | hello |
> | hi| hi|
> +---+---+
> 2 rows in set (0.00 sec)
>
> But on the slave looks it like this...
>
> mysql> select * from andrew;
> ++--+
> | f1 | f2   |
> ++--+
> | hell0,hell | NULL |
> | hi,hi  | NULL |
> ++--+
> 2 rows in set (0.00 sec)
>
> Weird hey?
>
> Works fine with tab delimited files.
>
> Any ideas anyone?
>
> Cheers,
>
> Andrew
>
> Sql,query
>
>
> --
> 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: composite keys indexing

[Michael Brunson]

On Tue, 1 Apr 2003 20:30:32 -0800 (PST), Jinesh Varia used a few
recycled electrons to form:
| Hello group,
| 
| I have a simple composite key
| create table a_b (aID int NOT NULL, bID int NOT NULL, primary key
| (aID,bID));
| 
| Where aID is primary key in the table a and bID is the primary key in table b,
| 
| While my primary keys are automatically indexed, I am having problems when I use the 
bID.
| 
| I read the documentation about only the first column will be actually indexed, but 
is there any
| workaround (I am using 4.0 mysql version)
| 
| for ex,
| when I use
| select a.* from a where a_b.aID=34;
| 
| the query is damn fast!! it is only indexing the aID.
| 
| but when I use
| 
| select a.* from a,a_b where a_b.bID=55 and a_b.aID=a.aID;

Try:

select a.* from a_b left join a using aID where a_b.bID=55;

This will be a lot faster, since you are restricting your result
set, before doing the join.



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

Working examples

[Martin Gainty]

Ive seen plenty examples in books but are there any
working MySQL examples someone can use immediately..Anything anywhere?
Thank You,
Martin
- Original Message -
From: "Marko Hrastovec" <[EMAIL PROTECTED]>
To: "'gerald_clark'" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Tuesday, April 01, 2003 10:11 PM
Subject: RE: Upgrading from 3.23.5x to 4.0.12 version 4 show no tables in
any database


> That was my first thought too. Shouldn't then MySQL also report
nonexisting
> databases? When I run client "mysql database_name" it runs only on
existing
> databases. When I write wrong database name it won't run, so it look in
the
> right directory. If the database directory was wrong it shouldn't work on
> other
> four computers which have all RedHat Linux installed.
>
> Regards
> Marko
>
> > -Original Message-
> > From: gerald_clark [mailto:[EMAIL PROTECTED]
> > Sent: Tuesday, April 01, 2003 4:26 PM
> > To: [EMAIL PROTECTED]
> > Cc: [EMAIL PROTECTED]
> > Subject: Re: Upgrading from 3.23.5x to 4.0.12 version 4 show
> > no tables in any database
> >
> >
> > Add your database directory to your my.cnf file.
> > They were possibly compiled with diferent default
> > database locations.
> >
> > [EMAIL PROTECTED] wrote:
> >
> > >>Description:
> > >>
> > >>
> > > I have upgraded from 3.23.5x versions to 4.0.12 on Linux Redhat
> > >operating system. On 3 machines I only upgraded the RPMs and
> > >everything worked. On only Linux Redhat 7.3 server
> > RPM upgrade
> > >also worked fine. The problem is that mysql server report no
> > >tables in any database. When I run MySQL client
> > "show tables;"
> > >return empty dataset in databases. 3.23.5x work fine.
> > >
> > > I am reporting this from the server where upgrade was
> > successful.
> > > The server where error occured is almost identical it only is
> > >Redhat 7.3 instead of 7.2.
> > >
> > >
> > >>How-To-Repeat:
> > >>
> > >>
> > > I have downgraded to 3.23.56 where tables are seen. Every time I
> > >upgrade I get the same error.
> > >
> > >
> > >>Fix:
> > >>
> > >>
> > > No idea.
> > >
> > >
> > >
> > >>Submitter-Id: 
> > >>Originator:
> > >>Organization:
> > >>
> > >>
> > >
> > >
> > >
> > >>MySQL support: none
> > >>Synopsis: Upgrade problem
> > >>Severity: serious
> > >>Priority: medium
> > >>Category: mysql
> > >>Class: sw-bug
> > >>Release: mysql-4.0.12 (Official MySQL RPM)
> > >>Server: /usr/bin/mysqladmin  Ver 8.40 Distrib 4.0.12, for
> > pc-linux on i686
> > >>
> > >>
> > >Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
> > >This software comes with ABSOLUTELY NO WARRANTY. This is
> > free software,
> > >and you are welcome to modify and redistribute it under the
> > GPL license
> > >
> > >Server version 4.0.12
> > >Protocol version 10
> > >Connection Localhost via UNIX socket
> > >UNIX socket /var/lib/mysql/mysql.sock
> > >Uptime: 3 days 15 hours 11 min 33 sec
> > >
> > >Threads: 1  Questions: 3114  Slow queries: 0  Opens: 151
> > Flush tables: 1  Open tables: 46  Queries per second avg: 0.010
> > >
> > >
> > >>C compiler:2.95.3
> > >>C++ compiler:  2.95.3
> > >>Environment:
> > >>
> > >>
> > >
> > >System: Linux falcon 2.4.7-10 #1 Thu Sep 6 17:27:27 EDT 2001
> > i686 unknown
> > >Architecture: i686
> > >
> > >Some paths:  /usr/bin/perl /usr/bin/make /usr/bin/gmake
> > /usr/bin/gcc /usr/bin/cc
> > >GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/2.96/specs
> > >gcc version 2.96 2731 (Red Hat Linux 7.2 2.96-112.7.2)
> > >Compilation info: CC='gcc'  CFLAGS='-O6
> > -fno-omit-frame-pointer -mpentium'  CXX='g++'  CXXFLAGS='-O6
> > -fno-omit-frame-pointer   -felide-constructors
> > -fno-exceptions -fno-rtti -mpentium'  LDFLAGS=''  ASFLAGS=''
> > >LIBC:
> > >lrwxrwxrwx1 root root   13 Mar 20 09:15
> > /lib/libc.so.6 -> libc-2.2.4.so
> > >-rwxr-xr-x1 root root  1285884 Mar  6 16:03
> > /lib/libc-2.2.4.so
> > >-rw-r--r--1 root root 27338566 Mar  6 15:05
> > /usr/lib/libc.a
> > >-rw-r--r--1 root root  178 Mar  6 15:05
> > /usr/lib/libc.so
> > >Configure command: ./configure '--disable-shared'
> > '--with-mysqld-ldflags=-all-static'
> > '--with-client-ldflags=-all-static' '--without-berkeley-db'
> > '--with-innodb' '--without-vio' '--without-openssl'
> > '--enable-assembler' '--enable-local-infile'
> > '--with-mysqld-user=mysql'
> > '--with-unix-socket-path=/var/lib/mysql/mysql.sock'
> > '--prefix=/' '--with-extra-charsets=complex'
> > '--exec-prefix=/usr' '--libexecdir=/usr/sbin'
> > '--sysconfdir=/etc' '--datadir=/usr/share'
> > '--localstatedir=/var/lib/mysql' '--infodir=/usr/share/info'
> > '--includedir=/usr/include' '--mandir=/usr/share/man'
> > '--with-embedded-server' '--enable-thread-safe-client'
> > '--with-comment=Official MySQL RPM' 'CFLAGS=-O6
> > -fno-omit-frame-pointer -mpentium' 'CXXFLAGS=-O6
> > -fno-omit-frame-pointer   -felide-constructors
> > -fno-exceptions -fno-rtt

RE: Upgrading from 3.23.5x to 4.0.12 version 4 show no tables in any database

[Marko Hrastovec]

That was my first thought too. Shouldn't then MySQL also report nonexisting
databases? When I run client "mysql database_name" it runs only on existing
databases. When I write wrong database name it won't run, so it look in the
right directory. If the database directory was wrong it shouldn't work on
other
four computers which have all RedHat Linux installed.

Regards
Marko

> -Original Message-
> From: gerald_clark [mailto:[EMAIL PROTECTED] 
> Sent: Tuesday, April 01, 2003 4:26 PM
> To: [EMAIL PROTECTED]
> Cc: [EMAIL PROTECTED]
> Subject: Re: Upgrading from 3.23.5x to 4.0.12 version 4 show 
> no tables in any database
> 
> 
> Add your database directory to your my.cnf file.
> They were possibly compiled with diferent default
> database locations.
> 
> [EMAIL PROTECTED] wrote:
> 
> >>Description:
> >>
> >>
> > I have upgraded from 3.23.5x versions to 4.0.12 on Linux Redhat
> >operating system. On 3 machines I only upgraded the RPMs and
> >everything worked. On only Linux Redhat 7.3 server 
> RPM upgrade
> >also worked fine. The problem is that mysql server report no
> >tables in any database. When I run MySQL client 
> "show tables;"
> >return empty dataset in databases. 3.23.5x work fine.
> >
> > I am reporting this from the server where upgrade was 
> successful.
> > The server where error occured is almost identical it only is
> >Redhat 7.3 instead of 7.2.
> >  
> >
> >>How-To-Repeat:
> >>
> >>
> > I have downgraded to 3.23.56 where tables are seen. Every time I
> >upgrade I get the same error.
> >  
> >
> >>Fix:
> >>
> >>
> > No idea.
> >
> >  
> >
> >>Submitter-Id:   
> >>Originator: 
> >>Organization:
> >>
> >>
> > 
> >  
> >
> >>MySQL support: none
> >>Synopsis:   Upgrade problem
> >>Severity:   serious
> >>Priority:   medium
> >>Category:   mysql
> >>Class:  sw-bug
> >>Release:mysql-4.0.12 (Official MySQL RPM)
> >>Server: /usr/bin/mysqladmin  Ver 8.40 Distrib 4.0.12, for 
> pc-linux on i686
> >>
> >>
> >Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
> >This software comes with ABSOLUTELY NO WARRANTY. This is 
> free software,
> >and you are welcome to modify and redistribute it under the 
> GPL license
> >
> >Server version   4.0.12
> >Protocol version 10
> >Connection   Localhost via UNIX socket
> >UNIX socket  /var/lib/mysql/mysql.sock
> >Uptime:  3 days 15 hours 11 min 33 sec
> >
> >Threads: 1  Questions: 3114  Slow queries: 0  Opens: 151  
> Flush tables: 1  Open tables: 46  Queries per second avg: 0.010
> >  
> >
> >>C compiler:2.95.3
> >>C++ compiler:  2.95.3
> >>Environment:
> >>
> >>
> > 
> >System: Linux falcon 2.4.7-10 #1 Thu Sep 6 17:27:27 EDT 2001 
> i686 unknown
> >Architecture: i686
> >
> >Some paths:  /usr/bin/perl /usr/bin/make /usr/bin/gmake 
> /usr/bin/gcc /usr/bin/cc
> >GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/2.96/specs
> >gcc version 2.96 2731 (Red Hat Linux 7.2 2.96-112.7.2)
> >Compilation info: CC='gcc'  CFLAGS='-O6 
> -fno-omit-frame-pointer -mpentium'  CXX='g++'  CXXFLAGS='-O6 
> -fno-omit-frame-pointer -felide-constructors 
> -fno-exceptions -fno-rtti -mpentium'  LDFLAGS=''  ASFLAGS=''
> >LIBC: 
> >lrwxrwxrwx1 root root   13 Mar 20 09:15 
> /lib/libc.so.6 -> libc-2.2.4.so
> >-rwxr-xr-x1 root root  1285884 Mar  6 16:03 
> /lib/libc-2.2.4.so
> >-rw-r--r--1 root root 27338566 Mar  6 15:05 
> /usr/lib/libc.a
> >-rw-r--r--1 root root  178 Mar  6 15:05 
> /usr/lib/libc.so
> >Configure command: ./configure '--disable-shared' 
> '--with-mysqld-ldflags=-all-static' 
> '--with-client-ldflags=-all-static' '--without-berkeley-db' 
> '--with-innodb' '--without-vio' '--without-openssl' 
> '--enable-assembler' '--enable-local-infile' 
> '--with-mysqld-user=mysql' 
> '--with-unix-socket-path=/var/lib/mysql/mysql.sock' 
> '--prefix=/' '--with-extra-charsets=complex' 
> '--exec-prefix=/usr' '--libexecdir=/usr/sbin' 
> '--sysconfdir=/etc' '--datadir=/usr/share' 
> '--localstatedir=/var/lib/mysql' '--infodir=/usr/share/info' 
> '--includedir=/usr/include' '--mandir=/usr/share/man' 
> '--with-embedded-server' '--enable-thread-safe-client' 
> '--with-comment=Official MySQL RPM' 'CFLAGS=-O6 
> -fno-omit-frame-pointer -mpentium' 'CXXFLAGS=-O6 
> -fno-omit-frame-pointer -felide-constructors 
> -fno-exceptions -fno-rtti -mpentium'
> >
> >
> >  
> >
> 


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

RE: Error 28

[Don Read]

On 01-Apr-2003 Hutterer wrote:
> DO not know what I have changed, but afte mmonths of using mysql without
> problems - 
> NOW I get ERROR 28 ( (errno: 28) and  cannot create new databases.
>  
> Does anybody know how to fix this
> Thanks Robert
>  
>  

localhost.dread$ perror 28
Error code  28:  No space left on device
localhost.dread$ 

Regards,
-- 
Don Read   [EMAIL PROTECTED]
-- It's always darkest before the dawn. So if you are going to 
   steal the neighbor's newspaper, that's the time to do it.
(53kr33t w0rdz: sql table query)


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

composite keys indexing

[Jinesh Varia]

Hello group,

I have a simple composite key
create table a_b (aID int NOT NULL, bID int NOT NULL, primary key
(aID,bID));

Where aID is primary key in the table a and bID is the primary key in table b,

While my primary keys are automatically indexed, I am having problems when I use the 
bID.

I read the documentation about only the first column will be actually indexed, but is 
there any
workaround (I am using 4.0 mysql version)

for ex,
when I use
select a.* from a where a_b.aID=34;

the query is damn fast!! it is only indexing the aID.

but when I use

select a.* from a,a_b where a_b.bID=55 and a_b.aID=a.aID;

this query is taking hours... I have a huge database.

Is there any work around?

Helps appreciated!

Jin


=
-
Jinesh Varia
Graduate Student, Information Systems
Pennsylvania State University
Email: [EMAIL PROTECTED]
-
'Self is the author of its actions.'

__
Do you Yahoo!?
Yahoo! Tax Center - File online, calculators, forms, and more
http://tax.yahoo.com

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

Re: Cannot add new user to newly created database, help needed

[ag315]

mysql> use mysql;
mysql> update user set grant_priv = 'Y' where user = 'root';
mysql> flush privileges

and reconnect to mysql server as root user and
retry grant clause. You'll be happy!!! ^^

> Greetings,
>
> I'm trying to create a database and then adding a
> specialized user so it can work with it, but so far i
> got no luck:
>
> [EMAIL PROTECTED] josevnz]$ mysql -u root -p -h
> localhost.localdomain
> Enter password:
> Welcome to the MySQL monitor.  Commands end with ; or
> \g.
> Your MySQL connection id is 11 to server version:
> 4.0.12
>
> Type 'help;' or '\h' for help. Type '\c' to clear the
> buffer.
>
> mysql> use mysql;
> Database changed
> mysql> CREATE DATABASE test_cvebrowser;
> Query OK, 1 row affected (0.00 sec)
>
> mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
> ON test_cvebrowser.* TO
> 'test_cvebrowser_user'@'localhost.localdomain'
> IDENTIFIED BY 'cv3br0ws3r';
> ERROR 1044: Access denied for user: '[EMAIL PROTECTED]'
> to database 'test_cvebrowser'
> mysql>
>
> Any ideas what's wrong? (must be something pretty
> simple, but no cigar so far). I don't understand why
> the error if i managed to connect the first time.
>
> I'm using:
> - Redhat Linux 8.0
> - MySQL 4.0.12 (fresh install)
>
> PD: I already checked the documentation at
> 'http://www.mysql.com/doc/en/Database_use.html', but
> no luck.
>
> Thanks in advance,
>
> JV.
>
>
> --
> 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 & mail servers

[Michael Brunson]

On Tue, 1 Apr 2003 13:15:00 -0800, Jeremy Zawodny used a few
recycled electrons to form:
| On Tue, Apr 01, 2003 at 01:04:59PM -0800, Steven Nakhla wrote:
| >
| > Are there currently any open-source mail servers that utilize MySQL?
| 
| Yes, many.
| 
| > I know that there are some that will authenticate against accounts
| > stored in a MySQL database, but I am looking for a mail server that
| > actually stores the messages in the database.
| 
| Why, exactly?

I know of one, but it isn't open source. It's called "Iris" and
was written in-house by one of our developers. Iris currently
processes all the email forwarding, pop, IMAP, etc for
directNIC.com customers. It supports filters, mailing lists,
archiving, RBLs, auto-responders, and probably a couple things I
forget... I'm trying to get him to add a NNTP interface for the
mailing lists now. ;-)

So I guess the short answer is yes, there is at least one. We
only use it in-house currently.

There might be others...

--Michael


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

Re: Rename table?

[ag315]

If you use PHP
program like below...



It's simple
OK ?

if you use other programming language, It's similar to above example.

Sorry, My english is so poor -_-;;

> Is it possible to rename a MySQL table? If so, how?
> If not, is there a simple query syntax to copy a table?
> I don't have access to the command-line version,
> just a web interface, and no, not anything nice like PhpMyAdmin! :-(  --
>
> Mark Wilson, Computer Programming Unlimited (cpuworks.com)
> Web  : http://cpuworks.com Tel: 410-549-6006
> Email: [EMAIL PROTECTED] Fax: 410-549-4408
>
>
>
>
>
> --
> 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]

Mac OS X package problems

[Todd O'Bryan]

In desperation I have restarted my machine a couple of times, and once 
I actually watched the start-up routine...this gave me a clue:

In accord with the installation instructions, I used Marc Liyanages' 
startup item installer. Since then, I have removed the every vestige of
my MySQL installation following the instructions given by Lenz Grimmer.

BUT,

At startup, the computer says something about starting a MySQL server. 
Could it be that the startup item is still trying to start mysqld even 
though it no longer exists and in the process is tying up port 3306? 
(I'm grasping at straws here. I tried manually installing the latest 
3.0 version and when I tried to run safe_mysqld it said that 
libexec/mysqld did not exist, and, sure enough, there wasn't even a 
libexec directory.)

Please, please, please, if anyone thinks they could get me to the point 
of having a running mysql system on OS X, I'd really appreciate the 
help.

I teach high school, and I desperately need to set up a database of 
practice questions for my students who are taking the AP Computer 
Science exam. I'm working on Java programs to read in the questions and 
quiz students on questions they haven't successfully answered yet, but 
it's hard to be successful at that if I don't have a working mysql 
installation. Each student has entered 6 or 7 questions as an XML 
document, and I just have to get them into the database and set up a 
quiz program.

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

Rename table?

[Mark Wilson]

Is it possible to rename a MySQL table? If so, how?
If not, is there a simple query syntax to copy a table? 
I don't have access to the command-line version,
just a web interface, and no, not anything nice like PhpMyAdmin! :-( 
-- 
Mark Wilson, Computer Programming Unlimited (cpuworks.com)
Web  : http://cpuworks.com Tel: 410-549-6006
Email: [EMAIL PROTECTED] Fax: 410-549-4408





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

Cannot add new user to newly created database, help needed

[José]

Greetings,

I'm trying to create a database and then adding a
specialized user so it can work with it, but so far i
got no luck:

[EMAIL PROTECTED] josevnz]$ mysql -u root -p -h
localhost.localdomain
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or
\g.
Your MySQL connection id is 11 to server version:
4.0.12

Type 'help;' or '\h' for help. Type '\c' to clear the
buffer.

mysql> use mysql;
Database changed
mysql> CREATE DATABASE test_cvebrowser;
Query OK, 1 row affected (0.00 sec)

mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
ON test_cvebrowser.* TO
'test_cvebrowser_user'@'localhost.localdomain'
IDENTIFIED BY 'cv3br0ws3r';
ERROR 1044: Access denied for user: '[EMAIL PROTECTED]'
to database 'test_cvebrowser'
mysql> 

Any ideas what's wrong? (must be something pretty
simple, but no cigar so far). I don't understand why
the error if i managed to connect the first time.

I'm using:
- Redhat Linux 8.0
- MySQL 4.0.12 (fresh install)

PD: I already checked the documentation at
'http://www.mysql.com/doc/en/Database_use.html', but
no luck.

Thanks in advance,

JV.


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

Re: How Many

[Cal Evans]

Ed,

1: It depends on your hardware. I don't think MySQL imposes a restriction.

2: It doesn't work that way.  It's not like VFP or Access where you 'edit' a
record. You can SELECT the contents of a record, you can manipulate them and
then you can UPDATE that record (assuming you have a primary key) but the
last person to issue an update is the one who wins.

One thing I've done in the past is use 'soft locks'.  Basically if you HAVE
to insure that while someone has requested the right to edit a record, no
one else can do it you have to enforce it in your own software.

A soft lock is simply a couple of fields in a record. usually a userID and a
timedate field.  When someone requests permission to edit a record I do:

1: Make sure the userid field is empty
2: UPDATE the record with this users userid and the current timedate.
3: SELECT the record again and make sure that this user's userid is the
current one. (Keeps someone from slipping in on ya.)
4: Allow them to edit the record
5: Commit the record and clear the userid.

If there IS a userid already on the record, I check the timestamp.  If it's
more than x time units (10 minutes, 2 hours, 3 days, etc) old, I invalidate
the lock by going to step 2 anyhow.

Clear as mud?

=C=
- Original Message -
From: "Ed Kiefer" <[EMAIL PROTECTED]>
To: "MySQL" <[EMAIL PROTECTED]>
Sent: Tuesday, April 01, 2003 7:40 AM
Subject: How Many


> How many people can be accessing the same mysql database at the same time?
> Can several people work on the same record at the same time?
>
>
> --
> 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: Passwords don't work in a WinME installation?

[Martin Gainty]

The key to Pandoras Box is available to those who wrote the encryption
algorithm
Triple DES anyone?
-Martin
- Original Message -
From: "Doug Thompson" <[EMAIL PROTECTED]>
To: "Martin Gainty" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Monday, March 31, 2003 8:06 PM
Subject: Re: Passwords don't work in a WinME installation?


> It is all disinformation to confuse Micro$oft.   8-)
>
>
> On Mon, 31 Mar 2003 09:45:09 -0700, Martin Gainty wrote:
>
> >Michael et al-
> >I am trying to locate ONE working MySQL example for windows. Is this a
myth
> >or is there WORKING source example out there somewhere?
> >Thanks,
> >Martin
> >- Original Message -
> >From: "Michael Shulman" <[EMAIL PROTECTED]>
> >To: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
> >Sent: Monday, March 31, 2003 9:24 AM
> >Subject: RE: Passwords don't work in a WinME installation?
> >
> >
> >Start reading here:
> >http://www.mysql.com/doc/en/Privileges.html
> >
> >and read to 4.2.10.
> >
> >Two tips I found useful:
> >1. The current_user() function tells you what MySQL user name it thinks
that
> >you are. This can be used to verify if you are an authenticated user.
> >mysql> SELECT current_user();
> >
> >2. Remove unneeded rows from the user table. For my installation, I
removed
> >all rows except 'root' logging in on the local machine:
> >
> >mysql> use mysql;
> >mysql> delete from user where user != 'root';
> >mysql> delete from user where host != 'localhost';
> >mysql> flush privileges;
> >mysql> select user, password, host from user;
> >+---+---+--+
> >| user  | host  | password |
> >+---+---+--+
> >| root  | localhost |  |
> >+---+---+--+
> >1 row in set (0.00 sec)
> >
> >If you have a line that has an empty username or % for hostname, this
will
> >make diagnosing your security issues difficult.
> >
> >-ms
> >
> >
> >
> >
> >
> >-Original Message-
> >From: Helge Moulding [mailto:[EMAIL PROTECTED]
> >Sent: Sunday, March 30, 2003 11:02 PM
> >To: [EMAIL PROTECTED]
> >Subject: Passwords don't work in a WinME installation?
> >
> >
> >I'm learning the ropes with MySQL. I've got it installed on a WinME
> >
> >system, and one of the first things I've noticed is that it doesn't
> >
> >seem to care about users. Is that correct? In particular, I've tried
> >
> >out the GRANT command to create a new user, but MySQL doesn't seem
> >
> >to recognize the new user. Here's what happens:
> >
> >
> >
> >C:\Program Files\mysql40\bin>mysqld
> >
> >
> >
> >C:\Program Files\mysql40\bin>mysql
> >
> >Welcome to the MySQL monitor.  Commands end with ; or \g.
> >
> >Your MySQL connection id is 1 to server version: 4.0.12-max-debug
> >
> >
> >
> >Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
> >
> >
> >
> >mysql> create database dummy;
> >
> >Query OK, 1 row affected (0.44 sec)
> >
> >
> >
> >mysql> grant select on dummy.* to [EMAIL PROTECTED] identified by 'blah';
> >
> >Query OK, 0 rows affected (0.28 sec)
> >
> >
> >
> >mysql> quit
> >
> >Bye
> >
> >
> >
> >C:\Program Files\mysql40\bin>mysqladmin flush-privileges
> >
> >
> >
> >C:\Program Files\mysql40\bin>mysql -u dumdum -p dummy
> >
> >Enter password: 
> >
> >ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password:
YES)
> >
> >
> >
> >C:\Program Files\mysql40\bin>
> >
> >
> >
> >I've tried to be very careful to make sure that the password is correct
:-)
> >
> >I don't know if the fault lies in using "localhost" instead of
"127.0.0.1"
> >
> >for the host part of the user. I'd have thought that "localhost" and
> >
> >"127.0.0.1" would be synonymous...
> >
> >
> >
> >However, if I try to mess with any kind of passwords, I get difficulties.
> >
> >For example, I don't get to set a password for root:
> >
> >
> >
> >C:\Program Files\mysql40\bin>mysqladmin -u root password temp
> >
> >
> >
> >C:\Program Files\mysql40\bin>mysql -u root
> >
> >Welcome to the MySQL monitor.  Commands end with ; or \g.
> >
> >Your MySQL connection id is 6 to server version: 4.0.12-max-debug
> >
> >
> >
> >Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
> >
> >
> >
> >mysql>
> >
> >
> >
> >Notice that mysql let user root in without asking for a password, even
> >
> >though I used mysqladmin to set a password. (In the book I'm using,
> >
> >"MySQL Visual Quickstart Guide" by Ullman, the password is supposed to
> >
> >be in single quotes, but in the mysqladmin help output, there are no
> >
> >quotes. I've tried it both ways, and neither has the expected effect.)
> >
> >
> >
> >I used winmysqladmin, which prompted me for a user name and password.
> >
> >But mysql lets me in without giving a password for that user, either.
> >
> >
> >
> >For that matter, mysql will let in any user, even if they haven't been
> >
> >created by any method I know of:
> >
> >
> >
> >C:\Program Files\mysql40\bin>mysql -u nooneknowsme
> >
> >Welcome to the MySQL monitor.  

Re: Rename/Copy a Table????

[ag315]

RENAME TABLE tbl_name TO new_tbl_name[, tbl_name2 TO new_tbl_name2,...]

you can find it from MYSQL MANUAL section RENAME TABLE SYNTAX

> (I tried to post this once, but it didn't show up - sorry if
> this is a duplicate!)
> Can you rename a table? If so, how?
> If not, is there an easy query to run to copy a table?
> (Preserving the data as well as the structure, of course.)
> Couldn't find it in my MySQL on-line manual...
> --
> Mark Wilson, Computer Programming Unlimited (cpuworks.com)
> Web  : http://cpuworks.com Tel: 410-549-6006
> Email: [EMAIL PROTECTED] Fax: 410-549-4408
>
>
>
>
>
> --
> 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: Passwords don't work in a WinME installation?

[Doug Thompson]

It is all disinformation to confuse Micro$oft.   8-)


On Mon, 31 Mar 2003 09:45:09 -0700, Martin Gainty wrote:

>Michael et al-
>I am trying to locate ONE working MySQL example for windows. Is this a myth
>or is there WORKING source example out there somewhere?
>Thanks,
>Martin
>- Original Message -
>From: "Michael Shulman" <[EMAIL PROTECTED]>
>To: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
>Sent: Monday, March 31, 2003 9:24 AM
>Subject: RE: Passwords don't work in a WinME installation?
>
>
>Start reading here:
>http://www.mysql.com/doc/en/Privileges.html
>
>and read to 4.2.10.
>
>Two tips I found useful:
>1. The current_user() function tells you what MySQL user name it thinks that
>you are. This can be used to verify if you are an authenticated user.
>mysql> SELECT current_user();
>
>2. Remove unneeded rows from the user table. For my installation, I removed
>all rows except 'root' logging in on the local machine:
>
>mysql> use mysql;
>mysql> delete from user where user != 'root';
>mysql> delete from user where host != 'localhost';
>mysql> flush privileges;
>mysql> select user, password, host from user;
>+---+---+--+
>| user  | host  | password |
>+---+---+--+
>| root  | localhost |  |
>+---+---+--+
>1 row in set (0.00 sec)
>
>If you have a line that has an empty username or % for hostname, this will
>make diagnosing your security issues difficult.
>
>-ms
>
>
>
>
>
>-Original Message-
>From: Helge Moulding [mailto:[EMAIL PROTECTED]
>Sent: Sunday, March 30, 2003 11:02 PM
>To: [EMAIL PROTECTED]
>Subject: Passwords don't work in a WinME installation?
>
>
>I'm learning the ropes with MySQL. I've got it installed on a WinME
>
>system, and one of the first things I've noticed is that it doesn't
>
>seem to care about users. Is that correct? In particular, I've tried
>
>out the GRANT command to create a new user, but MySQL doesn't seem
>
>to recognize the new user. Here's what happens:
>
>
>
>C:\Program Files\mysql40\bin>mysqld
>
>
>
>C:\Program Files\mysql40\bin>mysql
>
>Welcome to the MySQL monitor.  Commands end with ; or \g.
>
>Your MySQL connection id is 1 to server version: 4.0.12-max-debug
>
>
>
>Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
>
>
>
>mysql> create database dummy;
>
>Query OK, 1 row affected (0.44 sec)
>
>
>
>mysql> grant select on dummy.* to [EMAIL PROTECTED] identified by 'blah';
>
>Query OK, 0 rows affected (0.28 sec)
>
>
>
>mysql> quit
>
>Bye
>
>
>
>C:\Program Files\mysql40\bin>mysqladmin flush-privileges
>
>
>
>C:\Program Files\mysql40\bin>mysql -u dumdum -p dummy
>
>Enter password: 
>
>ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES)
>
>
>
>C:\Program Files\mysql40\bin>
>
>
>
>I've tried to be very careful to make sure that the password is correct :-)
>
>I don't know if the fault lies in using "localhost" instead of "127.0.0.1"
>
>for the host part of the user. I'd have thought that "localhost" and
>
>"127.0.0.1" would be synonymous...
>
>
>
>However, if I try to mess with any kind of passwords, I get difficulties.
>
>For example, I don't get to set a password for root:
>
>
>
>C:\Program Files\mysql40\bin>mysqladmin -u root password temp
>
>
>
>C:\Program Files\mysql40\bin>mysql -u root
>
>Welcome to the MySQL monitor.  Commands end with ; or \g.
>
>Your MySQL connection id is 6 to server version: 4.0.12-max-debug
>
>
>
>Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
>
>
>
>mysql>
>
>
>
>Notice that mysql let user root in without asking for a password, even
>
>though I used mysqladmin to set a password. (In the book I'm using,
>
>"MySQL Visual Quickstart Guide" by Ullman, the password is supposed to
>
>be in single quotes, but in the mysqladmin help output, there are no
>
>quotes. I've tried it both ways, and neither has the expected effect.)
>
>
>
>I used winmysqladmin, which prompted me for a user name and password.
>
>But mysql lets me in without giving a password for that user, either.
>
>
>
>For that matter, mysql will let in any user, even if they haven't been
>
>created by any method I know of:
>
>
>
>C:\Program Files\mysql40\bin>mysql -u nooneknowsme
>
>Welcome to the MySQL monitor.  Commands end with ; or \g.
>
>Your MySQL connection id is 9 to server version: 4.0.12-max-debug
>
>
>
>Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
>
>
>
>mysql>
>
>
>
>What's going on here? Is the password stuff broken, or is it the fault
>
>of me working on Windows ME?
>
>--
>
>Helge Moulding
>
>mailto:[EMAIL PROTECTED]Just another guy
>
>http://hmoulding.cjb.net/  with a weird name
>
>___
>Join Excite! - http://www.excite.com
>The most personalized portal on the Web!
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe:http://lists.mysql.com/[EMAIL 

RE: Rename/Copy a Table????

[Jennifer Goodie]

You can rename a table by using the alter syntax
http://www.mysql.com/doc/en/ALTER_TABLE.html
ALTER TABLE tbl_name RENAME [TO] new_tbl_name

> -Original Message-
> From: Mark Wilson [mailto:[EMAIL PROTECTED]
> Sent: Monday, March 31, 2003 4:41 PM
> To: Mysql Mailing List
> Subject: Rename/Copy a Table
>
>
> (I tried to post this once, but it didn't show up - sorry if
> this is a duplicate!)
> Can you rename a table? If so, how?
> If not, is there an easy query to run to copy a table?
> (Preserving the data as well as the structure, of course.)
> Couldn't find it in my MySQL on-line manual...
> --
> Mark Wilson, Computer Programming Unlimited (cpuworks.com)
> Web  : http://cpuworks.com Tel: 410-549-6006
> Email: [EMAIL PROTECTED] Fax: 410-549-4408
>
>
>
>
>
> --
> 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: Merge MySQL's data directly into Acrobat forms, without programming

[thomas getty]

Jeremy,

I appreciate your candid view of the situation. No,
you don't need Oracle, but you do need PF-Merge to
fully exploit MySQL into a cosmic-class database.


Tom

--- Jeremy Zawodny <[EMAIL PROTECTED]> wrote:
> On Wed, Mar 26, 2003 at 03:51:09PM -0800, thomas
> getty wrote:
> > 
> > Merge databases’ data (including images) directly
> into
> > Acrobat-based Forms, without programming;
> optionally
> > auto-email/fax generated pdf. Each pdf may be
> > password/encryption protected. The “database” can
> be
> > desktop class databases (MySQL, Filemaker, Excel,
> > Paradox, Access, Dbase, FoxPro, Goldmine,
> Act!2000,
> > Outlook, Text files, xml, Lotus-123) or enterprise
> > class databases (Oracle, Sybase, MS Sql Server,
> > Sybase, Informix, IBM DB2).
> 
> Wow.  It's good that someone told me we're using a
> "desktop class"
> database on all these servers.  I guess we need to
> upgrade to Oracle
> in a hurry! :-)
> 
> Jeremy
> -- 
> Jeremy D. Zawodny |  Perl, Web, MySQL, Linux
> Magazine, Yahoo!
> <[EMAIL PROTECTED]>  |  http://jeremy.zawodny.com/
> 
> MySQL 4.0.8: up 52 days, processed 1,805,379,170
> queries (396/sec. avg)


__
Do you Yahoo!?
Yahoo! Tax Center - File online, calculators, forms, and more
http://platinum.yahoo.com

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

Optimize query, avoid 'using temporary'

[Eric Persson]

Hi,

I have a query which I did several months ago, it recently caught my 
attention since it seems to be very slow when done a lot of times, which 
causes a very loaded server.

Its a webbased filesystem, which stores access/file information in 
mysql, the actual files are stored on disk. The problem is when I want 
to get all files in a directory which the current user have access to.

Below are the table structures used for this(descripten below them):
CREATE TABLE access (
  class_id int(10) unsigned NOT NULL default '0',
  group_id int(10) unsigned NOT NULL default '0',
  class_tbl char(10) NOT NULL default '',
  conn_id int(10) unsigned NOT NULL default '0'
) TYPE=MyISAM;
# The table above is used for more the the accessinfo for the files,
# its also used for directories etc. Thats why conn_id==file_id in
# this case. And class_id=4 and class_tbl=file
CREATE TABLE files (
  file_id int(10) unsigned NOT NULL auto_increment,
  lang_id int(10) unsigned NOT NULL default '0',
  directory_id int(10) unsigned NOT NULL default '0',
  filename varchar(255) NOT NULL default '',
  PRIMARY KEY  (file_id)
) TYPE=MyISAM;
#
# Actual file information, lang_id=1 and directory_id=0 in this case
#
The query I used looks like this:
SELECT files.file_id, filename FROM  access, files WHERE 
directory_id="0" AND lang_id="1" AND ( files.file_id=access.conn_id AND 
access.group_id IN (1) AND access.class_id="4" AND class_tbl="file" ) 
group by file_id order by filename;

Since access can have several rows per file_id(associated by 
file_id=conn_id ) I have to use group by to avoid getting multiple lines 
of the same file.

The part access.group_id IN (1) is the groups which the user have access 
to, could be more of them to. Used one for simplicity here.

An explain of the query gives me:
mysql> explain SELECT files.file_id, filename FROM  access, files WHERE 
directory_id="0" AND lang_id="1" AND ( files.file_id=access.conn_id AND 
access.group_id IN (1) AND access.class_id="4" AND class_tbl="file" ) 
group by file_id order by filename\G
*** 1. row ***
table: access
 type: ALL
possible_keys: NULL
  key: NULL
  key_len: NULL
  ref: NULL
 rows: 8958
Extra: where used; Using temporary; Using filesort
*** 2. row ***
table: files
 type: eq_ref
possible_keys: PRIMARY
  key: PRIMARY
  key_len: 4
  ref: access.conn_id
 rows: 1
Extra: where used
2 rows in set (0.00 sec)

If I exclude the group by and order by parts I get only where used, 
which is good, but gives me the wrong result.

Is it possible to rewrite the query to get better performance out of 
this? Or do I have to change the table structure?

Thanks in advance, best regards,
Eric


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

Rename/Copy a Table????

[Mark Wilson]

(I tried to post this once, but it didn't show up - sorry if 
this is a duplicate!)
Can you rename a table? If so, how?
If not, is there an easy query to run to copy a table?
(Preserving the data as well as the structure, of course.)
Couldn't find it in my MySQL on-line manual...
-- 
Mark Wilson, Computer Programming Unlimited (cpuworks.com)
Web  : http://cpuworks.com Tel: 410-549-6006
Email: [EMAIL PROTECTED] Fax: 410-549-4408





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

How come I can't use an "AS" field in other operations?

[Daevid Vincent]

Is this a bug or just not implemented in 3.23.56-Max?

mysql> SELECT alarm_notes, DATE_FORMAT(alarm_date,'%m/%d/%y %h:%i %p') AS
alarm_date_format, IFNULL(CONCAT(contact_fname, ' ', alarm_date),
alarm_date_format) AS contact_name, contact_email, contact_phone FROM
alarm_table LEFT JOIN contact_table ON alarm_contact_table_id = contact_id
WHERE alarm_rep_table_id = '1' ORDER BY alarm_date;
ERROR 1054: Unknown column 'alarm_date_format' in 'field list'


mysql> SELECT alarm_notes, DATE_FORMAT(alarm_date,'%m/%d/%y %h:%i %p') AS
alarm_date_format, IFNULL(CONCAT(contact_fname, ' ', alarm_date),
alarm_date) AS contact_name, contact_email, contact_phone FROM alarm_table
LEFT JOIN contact_table ON alarm_contact_table_id = contact_id WHERE
alarm_rep_table_id = '1' ORDER BY alarm_date;
+---
-+---+--+---
--+---+
| alarm_notes
| alarm_date_format | contact_name | contact_email   |
contact_phone |
+---
-+---+--+---
--+---+
| this is an alarm scheduled for a past date not assigned to a contact, so
it's overdue. | 03/31/03 09:00 PM | 2003-03-31 21:00:00  | NULL
| NULL  |
| this is an alarm scheduled for a future date for this contact
| 04/10/03 02:00 AM | Tony 2003-04-10 02:00:00 | [EMAIL PROTECTED] |
425.985.3453  |
+---
-+---+--+---
--+---+
2 rows in set (0.00 sec)


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

Re: Opposite of DISTINCT()

[Bruce Feist]

Jennifer Goodie wrote:

Why the join?  Why not just "select p1.email, count(*) as occurances from
table p1 group by p1.email having occurances > 1"?  Am I missing something?
Possibly.  It depends on whether the OP wanted to see which rows had 
duplicates, or to actually *see* the duplicates, in which case a join 
would be needed.  (It would not, however, be quite the join shown 
below... one query would find the rows with duplicates and yield a 
temporary table, and then the second would join that table back to the 
original to find the details.

Bruce Feist

 

-Original Message-
You have to do a self join - try this off the top of my head... -
Select p1.email
FROM tblperson p1, tblperson p2
WHERE p1.email = p2.email
GROUP BY p1.email
HAVING count(p1.email) > 1
   





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

Re: Adding index fails with error 1034: 126

[Chad Hanna]

In message <[EMAIL PROTECTED]>, 
Andrew Braithwaite <[EMAIL PROTECTED]> writes
Hi,

What are the sizes of your index files? (i.e. name_index2.MYI in the
datadir).
It's a complete guess but with a large table like that, it's possible that
you're reaching some kind of limit with your os/mysql setup due to file
size.  (greater than 4 GB would be my guess)
Cheers,

Andrew
Thanks,

Files aren't huge - row size is 86 bytes.

name_index2.MYD 1190595954
name_index2.MYI would go over 1 Gbyte as well.
It feels like some limit within MySQL - perhaps thrashing in the key 
cache (not that I know what I'm talking about) - but that should just 
make it much slower, shouldn't it? For better, or more probably worse, 
I'm operating with some swap (1 Gbyte RAM with 256 Mbyte swap).

Using the huge configuration (for 1 to 2 Gbytes RAM) makes no 
difference, errors after an hour.

Dropping the first two indexes, and then adding the troublesome third 
index on its own works. .MYI Index size is 212,271,104 bytes takes 5min 
2.83 secs.

Cheers, Chad
-Original Message-
From: Chad Hanna [mailto:[EMAIL PROTECTED]
Sent: Tuesday 01 April 2003 17:03
To: [EMAIL PROTECTED]
Subject: Adding index fails with error 1034: 126
Hi, I've been bashing my brains out for a day or two on this, so I'd
appreciate some pointers.
When altering a table to add indexes I get a message like :

error 1034: 126 at record pos 957847618

mysql -V tells me:
mysql  Ver 11.18 Distrib 3.23.51, for unknown-freebsdelf4.6.2 (i386)
myisamchk -dv name_index2 tells me:

myisamchk -dv name_index2

MyISAM file: name_index2
Record format:   Fixed length
Character set:   latin1 (8)
File-version:1
Creation time:   2003-04-01 13:32:18
Recover time:2003-04-01 14:26:25
Status:  checked
Data records: 13844139  Deleted blocks: 0
Datafile parts:   13844139  Deleted data:   0
Datafile pointer (bytes):4  Keyfile pointer (bytes):3
Datafile length:1190595954  Keyfile length: 522063872
Max datafile length:  369367187454  Max keyfile length:   17179868159
Recordlength:   86
table description:
Key Start Len Index   Type Rec/key Root
Blocksize
1   2 4   multip. unsigned long  0412131328
1024
   6 4   unsigned long  0
2   1120  multip. char packed stripped   0 97328128
1024
   5520  char stripped  0
   803   char   0
show create table name_index2 tells me the create statement is:

CREATE TABLE `name_index2` (
 `dataset_vers_id` int(10) unsigned NOT NULL default '0',
 `row_id` int(10) unsigned NOT NULL default '0',
 `quality_level` tinyint(4) NOT NULL default '100',
 `surname` char(20) NOT NULL default '',
 `emanrus` char(20) NOT NULL default '',
 `surname_id` int(10) unsigned NOT NULL default '0',
 `forename` char(20) NOT NULL default '',
 `forename_id` int(10) unsigned NOT NULL default '0',
 `forename_pos` tinyint(4) NOT NULL default '0',
 `place_code` char(3) NOT NULL default '',
 `start_year` smallint(6) NOT NULL default '0',
 `end_year` smallint(6) NOT NULL default '2100',
 KEY `name_index_idx1` (`dataset_vers_id`,`row_id`),
 KEY `name_index_idx2` (`surname`,`forename`,`place_code`)
) TYPE=MyISAM
I've been adding the indexes one at time to try and isolate the problem, the
failure occurs when I add:
alter table name_index2
add index name_index_idx3 (surname_id, forename_id, place_code);
Possibly important: 744845 data records (~ 5%) have a surname_id = 0 I'm
using the 'large' my.cnf from the distribution with a couple of tweaks.
A couple of weeks ago, a 10% smaller table took 45 minutes to add the
indexes. This one an hour to add two indexes and several hours to fail to
add all four.
I'm getting to the stage when I'm considering MERGE tables.

Any ideas welcome!

--
Chad Hanna
Chairman Berkshire Family History Society www.berksfhs.org.uk
--
Chad Hanna
Chairman Berkshire Family History Society www.berksfhs.org.uk
Quality Family History Data www.familyhistoryonline.net
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: database design : 3 month later

[Damien COLA]

Peter,

>1. If a column is a primary key, there is no need to declare it a
unique.
Thank you, I was doing that for every table since I started.

>2. Without foreign keys, your CREATE statements don't reveal table
relationships.
I am surprised, I thought my SQL is not a proper relationnal database
and has no embedded way of specifying the foreign key. 
For my part I am doing :
Have a field in attached table for 1-n relationships
Have another table (3) with table1.id and table2.id for n-n
relationships
What is the proper way then ?

>3. The best way I know to validate a schema is to (i) write or draw all
the application's use cases, (ii) from the use 
>cases derive all the required data items, (iii) organise these items
into a structure diagram with a modelling tool like 
>Microsoft Visio or Dezign, (iv) have the modelling tool generate the
database, (v) populate the database with a bit of 
>test data and (vi) see if you can derive (with or without pseudocode)
all the system's required outputs. The crucial tests 
>are step (iv) anf (vi).

I have no such program to help design a database, but I have to agree
this STEP seems like an optmized way of doing things.
I'll try get one such program in the public domain perhaps.

Thank you.

ps: I still have my SQL dump if you want to have another look.. each
time there's a 'simulated' foreign key, it's at the end of the fields
and has the nameOfTheTableID naming scheme.
For example for displaying a menu, I need to query the tables tgl_item,
tgl_course. Since items are classified by course, I've put the simulated
foreign key of tgl_course in tgl_item
For example when I want to know if a restaurant is open at this time or
not, I have all the possible times in tgl_time and the times registered
for that restaurant in tgl_rel_rest_time (restID, timeID, weekday) so
the three fields of tgl_rel_rest_time form a primary key, but there will
be about 300-400 rows for one restaurant.. I don't know if putting a
primary key on 3 fields is a good thing to do for speed and storage..
I have no experience in fact.

Cheers,
Damien



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

How Many

[Ed Kiefer]

How many people can be accessing the same mysql database at the same time?
Can several people work on the same record at the same time?


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

RE: password not working from command line

[Michael Shulman]

Privilege changes (at least on Windows) don't seem to take effect until
either (a) the server is restarted, or (b) the server is instructed to
re-read its permissions tables.

The best solution I've found is to include the line "flush privileges" into
Mysql sessions that manipulate users or permissions.

-ms


-Original Message-
From: Helge Moulding [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, April 01, 2003 3:07 PM
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: RE: password not working from command line


Andy Eastham wrote:

> Make sure you don't enter a space between -u and the username 

> and -p and the password ie mysql -uuser -ppassword



Actually, that works for me, i.e.

C:\Program Files\mysql40\bin>mysql -u root -p

Enter password: *

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 12 to server version: 4.0.12-max-debug



Type 'help;' or '\h' for help. Type '\c' to clear the buffer.



mysql>



But now it gets weird. I should have said: that *used* to work for 

me. Earlier today I was working with that password issue from the

other password thread, and I had finally got it all working the 

way I expected it to. Now I'm trying to answer Andy, and I find that

it *no longer works*! Not even if I leave out the space between -u

and root. Arrgh! The only thing that has changed since then is that

I did shut down the server for a while.



I can still make it work when I enter

C:\Program Files\mysql40\bin>mysql -u root -px

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 12 to server version: 4.0.12-max-debug



Type 'help;' or '\h' for help. Type '\c' to clear the buffer.



mysql>



Note the space between -u and root, and no space between -p and the

password. (If I put a space in there, it doesn't see the password.)

As was pointed out, it's not a good idea to put the password on the

command line, since that puts it in plain text.



OK, so what the heck is going on here Does MySQL have gremlins?

Is this the same problem that Weldon was complaining about?

-- 

Helge Moulding

mailto:[EMAIL PROTECTED]Just another guy

http://hmoulding.cjb.net/  with a weird name



___
Join Excite! - http://www.excite.com
The most personalized portal on the Web!

-- 
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: password not working from command line

[Helge Moulding]

Andy Eastham wrote:
> Make sure you don't enter a space between -u and the username 
> and -p and the password ie mysql -uuser -ppassword

Actually, that works for me, i.e.
C:\Program Files\mysql40\bin>mysql -u root -p
Enter password: *
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12 to server version: 4.0.12-max-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

But now it gets weird. I should have said: that *used* to work for 
me. Earlier today I was working with that password issue from the
other password thread, and I had finally got it all working the 
way I expected it to. Now I'm trying to answer Andy, and I find that
it *no longer works*! Not even if I leave out the space between -u
and root. Arrgh! The only thing that has changed since then is that
I did shut down the server for a while.

I can still make it work when I enter
C:\Program Files\mysql40\bin>mysql -u root -px
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12 to server version: 4.0.12-max-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

Note the space between -u and root, and no space between -p and the
password. (If I put a space in there, it doesn't see the password.)
As was pointed out, it's not a good idea to put the password on the
command line, since that puts it in plain text.

OK, so what the heck is going on here Does MySQL have gremlins?
Is this the same problem that Weldon was complaining about?
-- 
Helge Moulding
mailto:[EMAIL PROTECTED]Just another guy
http://hmoulding.cjb.net/  with a weird name


___
Join Excite! - http://www.excite.com
The most personalized portal on the Web!

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

Re: MySQL & mail servers

[mazur]

---
** ORIGINAL MESSAGE FROM:Jeff Kilpatrick <[EMAIL PROTECTED]>**
** ORIGINAL MESSAGE SENT:Tue Apr 01 14:38:21 **
** ORIGINAL MESSAGE BELOW **
---
>On Wed 1969-12-31 at 17:59 mazur wrote:
> ---
> ** ORIGINAL MESSAGE FROM:Jeremy Zawodny <[EMAIL PROTECTED]>**
> ** ORIGINAL MESSAGE SENT:Tue Apr 01 13:15:00 **
> ** ORIGINAL MESSAGE BELOW **
> ---
> >>re: On Tue Apr 01 2003 at 01:04:59PM -0800 Steven Nakhla wrote:
> >>
> >> Are there currently any open-source mail servers that utilize
> MySQL?
> >
> >Jeremey wrote:
> >Yes many.
> >
> > Steven wrote:
> >> I know that there are some that will authenticate against
accounts
> >> stored in a MySQL database but I am looking for a mail server
that
> >> actually stores the messages in the database.
> >
> >Jeremey wrote:
> >Why exactly?
> >
> >Jeremy
> 
> Well I can answer the why and say I built myself a web-based email
>  application (Java/Tomcat/MySQL/Linux) so that my email would be
> centralized.  I found myself working on and using one of about 6
> computers each week and my email was getting spread out over the
> machines.  I wasnt interested in using a Yahoo/Hotmail type account
> as dealing with all the banners and flashing lights didnt attract
me.
>   So I decided to roll my own.
> 
> Now my email is stored on and handled through one server in a MySQL
> database.  I can search the heck out of it and needless to say
> access it anywhere.  Im now considering a java GUI app to manage my
> mail (access via RMI perhaps) so having it in the database is very
> convenient.
> 
> I suppose other people have their reasons for storing email in a
> databaseanyone?
> 
> Rob Mazur
> -
>
>re: This is not flamebait.  Im not trying to be disagreeable or a
jerk. 
>Just a thought.
>
>What about IMAP?
>
>-jeff

Oh that's fine. :-)

I agree IMAP would do the trick.  I did it more as a pet project, and
as a way to explore Sun's JavaMail API.  It also gave me an excuse to
add another MySQL database to my collection!

In other words, it is my hobby project that I enjoy coding on the
side.
Rob Mazur
-


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

RE: Opposite of DISTINCT()

[Bob Sawyer]

Question answered.

Thanks to all who responded.


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

RE: Opposite of DISTINCT()

[Jennifer Goodie]

Why the join?  Why not just "select p1.email, count(*) as occurances from
table p1 group by p1.email having occurances > 1"?  Am I missing something?

> -Original Message-
> From: Andy Eastham [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, April 01, 2003 12:29 PM
> To: [EMAIL PROTECTED] Mysql. Com
> Subject: RE: Opposite of DISTINCT()
>
>
> Bob,
>
> You have to do a self join - try this off the top of my head... -
>
> Select p1.email
> FROM tblperson p1, tblperson p2
> WHERE p1.email = p2.email
> GROUP BY p1.email
> HAVING count(p1.email) > 1
>
> Andy
>
> > -Original Message-
> > From: Bob Sawyer [mailto:[EMAIL PROTECTED]
> > Sent: 01 April 2003 21:04
> > To: MySQL List
> > Subject: Opposite of DISTINCT()
> >
> >
> > I know that using SELECT DISTINCT(colname) will result in
> output that does
> > not contain any duplicates from that column. But how would I
> > output JUST the
> > duplicates? If I have as part of a table a column containing email
> > addresses, and I want to list just the duplicate addresses
> rather than the
> > distinct addresses, what's the syntax there?
> >
> > Thanks,
> > Bob
> >
> >
> >
> > --
> > 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 & mail servers

[Jeff Kilpatrick]

This is not flamebait.  I'm not trying to be disagreeable or a jerk. 
Just a thought.

What about IMAP?

-jeff
On Wed, 1969-12-31 at 17:59, mazur wrote:
> ---
> ** ORIGINAL MESSAGE FROM:Jeremy Zawodny <[EMAIL PROTECTED]>**
> ** ORIGINAL MESSAGE SENT:Tue Apr 01 13:15:00 **
> ** ORIGINAL MESSAGE BELOW **
> ---
> >>re: On Tue Apr 01 2003 at 01:04:59PM -0800 Steven Nakhla wrote:
> >>
> >> Are there currently any open-source mail servers that utilize
> MySQL?
> >
> >Jeremey wrote:
> >Yes many.
> >
> > Steven wrote:
> >> I know that there are some that will authenticate against accounts
> >> stored in a MySQL database but I am looking for a mail server that
> >> actually stores the messages in the database.
> >
> >Jeremey wrote:
> >Why exactly?
> >
> >Jeremy
> 
> Well, I can answer the "why", and say I built myself a web-based email
>  application (Java/Tomcat/MySQL/Linux) so that my email would be
> centralized.  I found myself working on and using one of about 6
> computers each week, and my email was getting spread out over the
> machines.  I wasn't interested in using a Yahoo/Hotmail type account,
> as dealing with all the banners and flashing lights didn't attract me.
>   So I decided to roll my own.
> 
> Now my email is stored on and handled through one server, in a MySQL
> database.  I can search the heck out of it, and needless to say,
> access it anywhere.  I'm now considering a java GUI app to manage my
> mail (access via RMI perhaps), so having it in the database is very
> convenient.
> 
> I suppose other people have their reasons for storing email in a
> databaseanyone?
> 
> Rob Mazur
> -


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

RE: Opposite of DISTINCT()

[Kevin Fries]

To find duplicates, use something like:

SELECT address, count(*)
>From Customer
GROUP BY address
HAVING count(*) > 1;

-Original Message-
From: Bob Sawyer [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, April 01, 2003 12:04 PM
To: MySQL List
Subject: Opposite of DISTINCT()


I know that using SELECT DISTINCT(colname) will result in output that
does not contain any duplicates from that column. But how would I output
JUST the duplicates? If I have as part of a table a column containing
email addresses, and I want to list just the duplicate addresses rather
than the distinct addresses, what's the syntax there?

Thanks,
Bob




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

Re: MySQL & mail servers

[mazur]

---
** ORIGINAL MESSAGE FROM:Jeremy Zawodny <[EMAIL PROTECTED]>**
** ORIGINAL MESSAGE SENT:Tue Apr 01 13:15:00 **
** ORIGINAL MESSAGE BELOW **
---
>>re: On Tue Apr 01 2003 at 01:04:59PM -0800 Steven Nakhla wrote:
>>
>> Are there currently any open-source mail servers that utilize
MySQL?
>
>Jeremey wrote:
>Yes many.
>
> Steven wrote:
>> I know that there are some that will authenticate against accounts
>> stored in a MySQL database but I am looking for a mail server that
>> actually stores the messages in the database.
>
>Jeremey wrote:
>Why exactly?
>
>Jeremy

Well, I can answer the "why", and say I built myself a web-based email
 application (Java/Tomcat/MySQL/Linux) so that my email would be
centralized.  I found myself working on and using one of about 6
computers each week, and my email was getting spread out over the
machines.  I wasn't interested in using a Yahoo/Hotmail type account,
as dealing with all the banners and flashing lights didn't attract me.
  So I decided to roll my own.

Now my email is stored on and handled through one server, in a MySQL
database.  I can search the heck out of it, and needless to say,
access it anywhere.  I'm now considering a java GUI app to manage my
mail (access via RMI perhaps), so having it in the database is very
convenient.

I suppose other people have their reasons for storing email in a
databaseanyone?

Rob Mazur
-


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

Re: MySQL & mail servers

[Jason Burfield]

Take a look at dbmail.

http://www.dbmail.org

  --  Jason


On 4/1/03 4:04 PM, "Steven Nakhla" <[EMAIL PROTECTED]> wrote:

> Are there currently any open-source mail servers that
> utilize MySQL?  I know that there are some that will
> authenticate against accounts stored in a MySQL
> database, but I am looking for a mail server that
> actually stores the messages in the database.
> 
> Ideally, I'd like to be able to setup an IMAP server
> that stores everything via a MySQL backend.  Any
> thoughts?  Recommendations?  Or is this not possible?
> 
> Steve Nakhla
> [EMAIL PROTECTED]
> 
> __
> Do you Yahoo!?
> Yahoo! Tax Center - File online, calculators, forms, and more
> http://platinum.yahoo.com

 ---
| Jason Burfield  | |
| [EMAIL PROTECTED]  |   http://www.burfield.com   |
 ---


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

Re: MySQL & mail servers

[Jeremy Zawodny]

On Tue, Apr 01, 2003 at 01:17:45PM -0800, Steven Nakhla wrote:
>
> Why?  Well, we have our reasons.

I'm sure you do.  But I'm curious to know them... unless they're
secret, of course. :-)

> Particularly because we want an efficient way to handle millions of
> messages, plus the ability to search/manipulate/data mine through
> them using specific applications.

Got it.

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

MySQL 4.0.8: up 57 days, processed 1,928,958,383 queries (388/sec. avg)

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

Re: password not working from command line

[David T-G]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Eldon, et al --

...and then Michael Shulman said...
% 
% Or, you can use the alternate, more legible syntax:
% 
% mysql --user=username --password=pass

Or, better yet, you can use 

  mysql -uusername -p [database]

or

  mysql -user=username -p [database]

to not expose the password on the command line.  That, in fact, seems to
be what Eldon was doing, as I read his original email, so there may be a
problem that we don't yet see.

So the reply simply becomes "give us more detail and actual examples".


HTH & HAND

mysql query,
:-D
- -- 
David T-G  * There is too much animal courage in 
(play) [EMAIL PROTECTED] * society and not sufficient moral courage.
(work) [EMAIL PROTECTED]  -- Mary Baker Eddy, "Science and Health"
http://justpickone.org/davidtg/  Shpx gur Pbzzhavpngvbaf Qrprapl Npg!

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.0.7 (FreeBSD)

iD8DBQE+igbtGb7uCXufRwARAspVAJ9jc10b0Yu0YBX9jp17MTeujfwJvACgxtOo
EruB7YQ0ifu5RenOoxKNt1c=
=5jyb
-END PGP SIGNATURE-

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

Re: [ mysql for red hat 7.2 ]

[[EMAIL PROTECTED]]

Elby Vaz schrieb:
hello,

i installed MySQL (server, max and client) for linux but i`m needing of 
`mysql.sock` file.
some body has?

obs.: what path stays the `my.cnf` file? how do i do to configure it?

eg. rhl8.0

$ rpm -q mysql-server
mysql-server-3.23.54a-4
$ ll /etc/my.cnf;cat /etc/my.cnf
-rw-r--r--1 root root  224 Feb 16 19:55 /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
set-variable=thread_stack=256k
[mysql.server]
user=mysql
basedir=/var/lib
[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
--
shrek-m
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Optimize query, avoid 'using temporary'

[Bruce Feist]

Eric Persson wrote:

CREATE TABLE access (
  class_id int(10) unsigned NOT NULL default '0',
  group_id int(10) unsigned NOT NULL default '0',
  class_tbl char(10) NOT NULL default '',
  conn_id int(10) unsigned NOT NULL default '0'
) TYPE=MyISAM;

CREATE TABLE files (
  file_id int(10) unsigned NOT NULL auto_increment,
  lang_id int(10) unsigned NOT NULL default '0',
  directory_id int(10) unsigned NOT NULL default '0',
  filename varchar(255) NOT NULL default '',
  PRIMARY KEY  (file_id)
) TYPE=MyISAM;

SELECT files.file_id, filename FROM  access, files WHERE
directory_id="0" AND lang_id="1" AND ( files.file_id=access.conn_id AND
access.group_id IN (1) AND access.class_id="4" AND class_tbl="file" )
group by file_id order by filename;

Is it possible to rewrite the query to get better performance out of
this? Or do I have to change the table structure?
Try adding a primary key to access().  I'm not sure what combination of
columns would be a unique identifier on it, but any index beginning with
class_id, class_tbl, and/or group_id (either a primary key or an index)
is likely to help significantly.  In fact, unless the combination of all
three of those fields is a unique identifier, I suggest that you index
on that combination.
Indexing "files" on the combination of lang_id and directory_id might
help as well.
Bruce Feist



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

RE: Opposite of DISTINCT()

[Andy Eastham]

Bob,

You have to do a self join - try this off the top of my head... -

Select p1.email
FROM tblperson p1, tblperson p2
WHERE p1.email = p2.email
GROUP BY p1.email
HAVING count(p1.email) > 1

Andy

> -Original Message-
> From: Bob Sawyer [mailto:[EMAIL PROTECTED]
> Sent: 01 April 2003 21:04
> To: MySQL List
> Subject: Opposite of DISTINCT()
>
>
> I know that using SELECT DISTINCT(colname) will result in output that does
> not contain any duplicates from that column. But how would I
> output JUST the
> duplicates? If I have as part of a table a column containing email
> addresses, and I want to list just the duplicate addresses rather than the
> distinct addresses, what's the syntax there?
>
> Thanks,
> Bob
>
>
>
> --
> 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 & mail servers

[Ruben Safir]

Well


To do selects on your mail archive?

BTW - I've heard that Linux Magazine has developed into a handsom
rag.  Congradulations.  I would never had thought it after seeing the
first issue.

Ruben

On Tue, Apr 01, 2003 at 01:15:00PM -0800, Jeremy Zawodny wrote:
> On Tue, Apr 01, 2003 at 01:04:59PM -0800, Steven Nakhla wrote:
> >
> > Are there currently any open-source mail servers that utilize MySQL?
> 
> Yes, many.
> 
> > I know that there are some that will authenticate against accounts
> > stored in a MySQL database, but I am looking for a mail server that
> > actually stores the messages in the database.
> 
> Why, exactly?
> 
> Jeremy
> -- 
> Jeremy D. Zawodny |  Perl, Web, MySQL, Linux Magazine, Yahoo!
> <[EMAIL PROTECTED]>  |  http://jeremy.zawodny.com/
> 
> MySQL 4.0.8: up 57 days, processed 1,928,444,836 queries (388/sec. avg)
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

-- 
__
Brooklyn Linux Solutions
__
DRM is THEFT - We are the STAKEHOLDERS http://fairuse.nylxs.com

http://www.mrbrklyn.com - Consulting
http://www.inns.net <-- Happy Clients
http://www.nylxs.com - Leadership Development in Free Software
http://www2.mrbrklyn.com/resources - Unpublished Archive or stories and articles from 
around the net
http://www2.mrbrklyn.com/downtown.html - See the New Downtown Brooklyn

1-718-382-0585

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

Enum Columns

[Ruben Safir]

I has a column defined as 
enum not null (percent,absolute) default value percent (in psuedocode)
and I was able to update a record which filled it either with a NULL or
an empty string.

How does this happen?  the value should be either percent or absolute,
or the update should just fail.


Ruben

-- 
__
Brooklyn Linux Solutions
__
DRM is THEFT - We are the STAKEHOLDERS http://fairuse.nylxs.com

http://www.mrbrklyn.com - Consulting
http://www.inns.net <-- Happy Clients
http://www.nylxs.com - Leadership Development in Free Software
http://www2.mrbrklyn.com/resources - Unpublished Archive or stories and articles from 
around the net
http://www2.mrbrklyn.com/downtown.html - See the New Downtown Brooklyn

1-718-382-0585

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

fail-safe queries

[Dave Dyer]

 I'm a little concerned by the possibilty of a malformed query
accidentally destroying a whole databse.   For example, a badly
constructed boolean, intended to select exactly one row, but which
actually modifies all rows.

 update mytables set data='who' where row='1234';

 (updates 1 row)

verses

 update mytables set data='who where row=1234';

 (damages all rows)

 The obvious answer is to use a LIMIT clause to limit the damage,
but (1) there is still damagage (2) the LIMIT clause is as
likely to be damaged as the WHERE.

 update mytables set data='who where row=1234' LIMIT 1;

 (damages 1 row, gives no indication of error.  Better I suppose.)

So my proposal is a "FAIL IF rows>1" type clause, which would be
syntactically FIRST in the string, and so unlikely to be damaged
by errors constructing a complex query.

 FAIL IF rows>1 update mytables set data='who where row=1234';

 (fails, damaging no rows.)




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

Re: Upgrading from 3.23.5x to 4.0.12 version 4 show no tables in any database

[gerald_clark]

Add your database directory to your my.cnf file.
They were possibly compiled with diferent default
database locations.
[EMAIL PROTECTED] wrote:

Description:
   

I have upgraded from 3.23.5x versions to 4.0.12 on Linux Redhat
   operating system. On 3 machines I only upgraded the RPMs and
   everything worked. On only Linux Redhat 7.3 server RPM upgrade
   also worked fine. The problem is that mysql server report no
   tables in any database. When I run MySQL client "show tables;"
   return empty dataset in databases. 3.23.5x work fine.
	I am reporting this from the server where upgrade was successful.
	The server where error occured is almost identical it only is
   Redhat 7.3 instead of 7.2.
 

How-To-Repeat:
   

	I have downgraded to 3.23.56 where tables are seen. Every time I
   upgrade I get the same error.
 

Fix:
   

	No idea.

 

Submitter-Id:	
Originator:	
Organization:
   

 

MySQL support: none
Synopsis:	Upgrade problem
Severity:	serious
Priority:	medium
Category:	mysql
Class:		sw-bug
Release:	mysql-4.0.12 (Official MySQL RPM)
Server: /usr/bin/mysqladmin  Ver 8.40 Distrib 4.0.12, for pc-linux on i686
   

Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license
Server version  4.0.12
Protocol version10
Connection  Localhost via UNIX socket
UNIX socket /var/lib/mysql/mysql.sock
Uptime: 3 days 15 hours 11 min 33 sec
Threads: 1  Questions: 3114  Slow queries: 0  Opens: 151  Flush tables: 1  Open tables: 46  Queries per second avg: 0.010
 

C compiler:2.95.3
C++ compiler:  2.95.3
Environment:
   


System: Linux falcon 2.4.7-10 #1 Thu Sep 6 17:27:27 EDT 2001 i686 unknown
Architecture: i686
Some paths:  /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc
GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/2.96/specs
gcc version 2.96 2731 (Red Hat Linux 7.2 2.96-112.7.2)
Compilation info: CC='gcc'  CFLAGS='-O6 -fno-omit-frame-pointer -mpentium'  CXX='g++'  CXXFLAGS='-O6 -fno-omit-frame-pointer 	  -felide-constructors -fno-exceptions -fno-rtti -mpentium'  LDFLAGS=''  ASFLAGS=''
LIBC: 
lrwxrwxrwx1 root root   13 Mar 20 09:15 /lib/libc.so.6 -> libc-2.2.4.so
-rwxr-xr-x1 root root  1285884 Mar  6 16:03 /lib/libc-2.2.4.so
-rw-r--r--1 root root 27338566 Mar  6 15:05 /usr/lib/libc.a
-rw-r--r--1 root root  178 Mar  6 15:05 /usr/lib/libc.so
Configure command: ./configure '--disable-shared' '--with-mysqld-ldflags=-all-static' '--with-client-ldflags=-all-static' '--without-berkeley-db' '--with-innodb' '--without-vio' '--without-openssl' '--enable-assembler' '--enable-local-infile' '--with-mysqld-user=mysql' '--with-unix-socket-path=/var/lib/mysql/mysql.sock' '--prefix=/' '--with-extra-charsets=complex' '--exec-prefix=/usr' '--libexecdir=/usr/sbin' '--sysconfdir=/etc' '--datadir=/usr/share' '--localstatedir=/var/lib/mysql' '--infodir=/usr/share/info' '--includedir=/usr/include' '--mandir=/usr/share/man' '--with-embedded-server' '--enable-thread-safe-client' '--with-comment=Official MySQL RPM' 'CFLAGS=-O6 -fno-omit-frame-pointer -mpentium' 'CXXFLAGS=-O6 -fno-omit-frame-pointer 	  -felide-constructors -fno-exceptions -fno-rtti -mpentium'

 



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

Re: Quick "unique" question...

[gerald_clark]

Why don't you try it yourself.
You could have tried it in less time than it took to mail the question.
The manual works too.
I'm not going to answer because you need to learn how to learn.

Noel Wade wrote:

I know its possible to use multiple fields for the primary key in a table 
i.e. "primary key(foo,bar)"

Is it possible to do this with "unique" identifiers as well?  Like "unique key(foo,bar)" ??

I already have a primary key on a table, but I'd also like to force two fields to form a unique combination (no two records should have the same foo & bar values).

Thanks!

--Noel

 



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

Re: MySQL & mail servers

[Steven Nakhla]

Why?  Well, we have our reasons.  Particularly because
we want an efficient way to handle millions of
messages, plus the ability to search/manipulate/data
mine through them using specific applications.

SN


--- Jeremy Zawodny <[EMAIL PROTECTED]> wrote:
> On Tue, Apr 01, 2003 at 01:04:59PM -0800, Steven
> Nakhla wrote:
> >
> > Are there currently any open-source mail servers
> that utilize MySQL?
> 
> Yes, many.
> 
> > I know that there are some that will authenticate
> against accounts
> > stored in a MySQL database, but I am looking for a
> mail server that
> > actually stores the messages in the database.
> 
> Why, exactly?
> 
> Jeremy
> -- 
> Jeremy D. Zawodny |  Perl, Web, MySQL, Linux
> Magazine, Yahoo!
> <[EMAIL PROTECTED]>  |  http://jeremy.zawodny.com/
> 
> MySQL 4.0.8: up 57 days, processed 1,928,444,836
> queries (388/sec. avg)


__
Do you Yahoo!?
Yahoo! Tax Center - File online, calculators, forms, and more
http://platinum.yahoo.com

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

Which process holds the locks?

[JRice]

Greetings.

I'm using mysql 3.23.54-Max with MyISAM tables on Linux 2.4.9-34 (RedHat
7.2).  We've got about 20 users and a myriad of automated scripts (Perl
DBI, Java JDBC) connecting to the database on a regular basis.

Twice now, we've had problems with too many connections (max connections
is currently set at 100, we may raise this, but that's another matter).

The last time this happened, most processes were showing a state
(through processlist) of "Locked", such as this:

  | Id | User | Host   | db  | Command | Time | State  | 
Info
  | 867791 | textcafe | jed.texterity.com  | textman | Query   | 165 | Locked | 
SELECT tickets.ticket_id FROM tickets LEFT JOIN projects ON tickets.project_id = 
projects.project_id |


My assumption (which may be flawed) is that there is a proccess which
has LOCKed TABLEs, but is not UNLOCKing them.

I would love to track this down.

So, my question is (I think) a simple one, yet I have found no answer in
my searches on-line:

How do I tell which process has a lock on what tables?


Thanks.


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

RE: Can't create thread error

[Mike William]

I am now pretty sure that I have got my previous problem licked.  I am 
posting the following additional information to the list in order to help 
others in the future who have the same problem and come across this thread 
in the list archives.

Here's a really interesting experiment I just did.  I set ulimit -u to 50 
and restarted mysql.  It worked fine until max_used_connectsion and the 
number of sleeping/persistent connections got up to 45, then it started 
throwing off the "Can't create thread" error.  Then I did ulimit -u 150 and 
restarted mysql again.  This time is worked fine until used_connectsion and 
the number of sleeping/persistent connections got up to 145, and then it 
started giving the error.  Then I set ulimit -u to unlimited and restarted 
mysql, and now it's working fine at all load levels.  So there seems to be 
an n-5 rule at work, i.e., whatever ulimit -u is set to minus 5 is the 
"ceiling" on max_used_connectsion and the number of sleeping/persistent 
connections.  When high load tries to exceed this ceiling, the "Can't 
create thread" error begins to rear its ugly head.

Also consistent with this theory:  the default on my system for ulimit -u 
is 256, which would explain why in my initial post the "ceiling" I was 
complaining about was 251.

Good luck to anyone in the future who has this same problem.  I hope you 
are able to find this thread in the archives!

Mike

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

Re: MySQL & mail servers

[Jeremy Zawodny]

On Tue, Apr 01, 2003 at 01:04:59PM -0800, Steven Nakhla wrote:
>
> Are there currently any open-source mail servers that utilize MySQL?

Yes, many.

> I know that there are some that will authenticate against accounts
> stored in a MySQL database, but I am looking for a mail server that
> actually stores the messages in the database.

Why, exactly?

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

MySQL 4.0.8: up 57 days, processed 1,928,444,836 queries (388/sec. avg)

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

RE: password not working from command line

[Michael Shulman]

Or, you can use the alternate, more legible syntax:

mysql --user=username --password=pass

-ms

-Original Message-
From: Andy Eastham [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, April 01, 2003 10:36 AM
To: [EMAIL PROTECTED] Mysql. Com
Subject: RE: password not working from command line

Eldon,

Make sure you don't enter a space between -u and the username and -p and the
password

ie mysql -uuser -ppassword

Andy

> -Original Message-
> From: Eldon Ziegler [mailto:[EMAIL PROTECTED]
> Sent: 01 April 2003 16:15
> To: [EMAIL PROTECTED]
> Subject: password not working from command line
>
>
> The password I entered in a GRANT statement isn't being accepted from the
> command line after entering mysql -u username -p and then entering the
> password from the GRANT statement. Is there something else I need to do?
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]
>
>



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



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

MySQL & mail servers

[Steven Nakhla]

Are there currently any open-source mail servers that
utilize MySQL?  I know that there are some that will
authenticate against accounts stored in a MySQL
database, but I am looking for a mail server that
actually stores the messages in the database.  

Ideally, I'd like to be able to setup an IMAP server
that stores everything via a MySQL backend.  Any
thoughts?  Recommendations?  Or is this not possible?

Steve Nakhla
[EMAIL PROTECTED]

__
Do you Yahoo!?
Yahoo! Tax Center - File online, calculators, forms, and more
http://platinum.yahoo.com

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

mysql update on myisam

[Jerg Schmidt]

Hi there,

I'm running MySQL-server-4.0.10-0.i386 on a Linux Box (SuSE 8.1) and all
tables are MyISM. Today a made strange observation. All update
statements on one table where completely "ignored" by the server. There
was no error message - just a "0 rows affected". After restart of mysqld
everyting worked fine and the privious statement was successful. Since I
don't know how to reproduce this a more generall question. Does anybody
know of bugs under 4.0.10-0 which prevent write access to MyISAM tables
without affecting read access. Might it be that a lock on this table was
stuck...

Always thankful for help in any way

Jerg

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

[ mysql for red hat 7.2 ]

[Elby Vaz]

hello,

i installed MySQL (server, max and client) for linux but i`m needing of 
`mysql.sock` file.
some body has?

obs.: what path stays the `my.cnf` file? how do i do to configure it?

thanks,
Elby.




_
MSN Hotmail, o maior webmail do Brasil.  http://www.hotmail.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Quick "unique" question...

[Noel Wade]

Answered my own question.  For posterity:

"alter table abc add unique(foo, bar);"

And the index will be created using the name of the first column you
specify.  Works like a charm!

--Noel

- Original Message -
From: "Noel Wade" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, April 01, 2003 12:04 AM
Subject: Quick "unique" question...


I know its possible to use multiple fields for the primary key in a table
i.e. "primary key(foo,bar)"

Is it possible to do this with "unique" identifiers as well?  Like "unique
key(foo,bar)" ??

I already have a primary key on a table, but I'd also like to force two
fields to form a unique combination (no two records should have the same foo
& bar values).

Thanks!

--Noel




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

How can i change the charset in MySQL after Install with RPM Package MDK

[Pratchaya Chatuphian]

I use MDK 9.0 and use MySQL  installed with RPM Package

How can i change or set new charset on mysql .

Caz , i want to use tis620 charset  ..   

I live in thailand and must use  charset that i can sort my data correctly on my MySQL 
... sir


Thank u for advance sir .

[EMAIL PROTECTED]


" Every day is a new beginning "

primary key/foreign key constraints with InnoDB

[Jeff Mathis]

hello all,

I'm wondering if its somehow possible to create a pk/fk constraint for
the table below

create table Example (
id int not null auto_increment primary key,
table_name enum('TabA','TabB') not null,
table_id int not null
) type = InnoDB;

if table_name is 'TabA', then I want to make sure the row exists in
TabA. Likewise if table_name is 'TabB'

the usual syntax to add a foreign key constraint doesn't seem to allow
this. I could certainly perform this check with an insert trigger, but,
alas, mysql does not support triggers.

any help?

-- 
Jeff Mathis, Ph.D.  505-955-1434
The Prediction Company  [EMAIL PROTECTED]
525 Camino de los Marquez, Ste 6http://www.predict.com
Santa Fe, NM 87505

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

RE: Opposite of DISTINCT()

[Michael Shulman]

SELECT col1
FROM table1
GROUP by col1
HAVING count(col1) >  1

-ms


-Original Message-
From: Bob Sawyer [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, April 01, 2003 12:04 PM
To: MySQL List
Subject: Opposite of DISTINCT()

I know that using SELECT DISTINCT(colname) will result in output that does
not contain any duplicates from that column. But how would I output JUST the
duplicates? If I have as part of a table a column containing email
addresses, and I want to list just the duplicate addresses rather than the
distinct addresses, what's the syntax there?

Thanks,
Bob



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



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

Re: Why don't ISPs use v4

[Benjamin Pflugmann]

Hi.

On Mon 2003-03-31 at 21:20:25 +0100, [EMAIL PROTECTED] wrote:
> Sorry everybody.  I assumed that as it had been out for a long time (2 
> years?) it was stable.

It's been 1 1/2 years (Oct 2001). But that was an alpha release. The
open development model of MySQL screws numbers a bit. The first beta
release was Aug 2002. So in real, it was about 7 month since feature
freeze. The release dates are part of

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

For more info on the release designation (alpha, beta, gamme,
production), have a look at

  http://www.mysql.com/doc/en/Which_version.html
  (especially the second half)

HTH,

Benjamin.


-- 
[EMAIL PROTECTED]

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

Opposite of DISTINCT()

[Bob Sawyer]

I know that using SELECT DISTINCT(colname) will result in output that does
not contain any duplicates from that column. But how would I output JUST the
duplicates? If I have as part of a table a column containing email
addresses, and I want to list just the duplicate addresses rather than the
distinct addresses, what's the syntax there?

Thanks,
Bob



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

Support for Dynix/ptx

[Klepetka, Tom]

Hi -

I would like to access a mysql database server from a NUMA-Q Sequent platform running 
Dynix/ptx v4.5.2.  Does any of mysql's connectivity products (Connector/ODBC, 
Connector/C++) run under this platform/OS?  Also, is there a C (not C++) API available?

Tom Klepetka
Senior Software Engineer

Intrado Inc.
6285 Lookout Road
Boulder, CO  80301-3343
direct: 630-300-2739
fax: 303-981-0900 (cover letter with my name on it)

Intrado. Informed Response.TM
www.intrado.com 

ATTENTION:

The information contained in this electronic message and any attachments to this 
message are intended for the exclusive use of the addressee(s) and may contain 
confidential or privileged information. If you are not the intended recipient, please 
notify Intrado Inc. immediately at (303) 581-5600 and destroy all copies of this 
message and any attachments.




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

RE: Can't create thread error

[Mike William]

Hi Don,

Thanks again so much for your help.  Here is where things stand right 
now.  I tried a lot of different things today, mostly to no avail, and then 
stumbled on an old MySQL mailing list message that suggested this problem 
might be related the OS limit on the number of processes a single user can 
run at one time.  So I ran "ulimit -u unlimited" (it had previously been 
set at 256) and then restarted MySQL.  Now it is working fine.  Both the 
number of sleeping/persistent connections and the max_used_connections have 
gone up well past my earlier 250-251 "ceiling" and I am not seeing any 
error messages anywhere.  I still need to give it a few days, but at the 
moment I am thinking that somehow, for reasons I don't really understand, 
raising ulimit -u fixed the problem.

If it didn't, I will probably be back on the mailing list in a couple days.  :)

Thanks,
Mike
At 02:42 PM 3/31/2003 -0600, Don Read wrote:

On 31-Mar-2003 Mike William wrote:
> Hi Don,
>
> Thanks for the suggestions.  Here's what happened.
>
>  > Your pconnects are probably sleeping too long.
>>  in /etc/my.cnf:
>>
>>[mysqld]
>>   set-variable = wait_timeout=10
>
> I tried this, and it was a mixed bag.  On the one hand, the "Can't create
> thread..." error did indeed go away, and max_used_connections and the
> number of sleeping/persistent connections dropped down well below
> 250.  However, making this change caused a new problem:  on about every
> 50%
> of page loads, the following PHP warning would occur:
>
> Warning: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES)
> in
> /www/functions.inc on line 66
>
> Line 66 of that file is a call to mysql_pconnect().  The MySQL user name
> and password hard coded in that file are definitely correct, since the
> error is only happening about 50% of the time and no one is modifying
> this
> file or the grant tables in between.
>
> Interestingly, nothing gets logged to the MySQL error log when this PHP
> warning is generated.  I'm not sure whether there are different logging
> levels in MySQL and if I need to make my logging more verbose or
> something,
> but at any rate I can't get any additional information about this error
> from the log.
>
>>If that doesn't take care of it, check your PHP version. Early (~ 2001)
>>PHP4 had some buggy pconnect problems.
>
> I'm using PHP 3.0.18.  We have been using the same pconnect() code with
> this same version of PHP for years and this problem started happening out
> of nowhere only yesterday.
>
>>Last resort:
>>Don't use pconnect unless you really --and I mean *really* need it.
>>mysql_connect() is fast enough for just about everything.
>
> I tried changing all PHP calls to mysql_pconnect() to mysql_connect()
> without making any other code changes.  Then I restarted MySQL.  These
> changes had basically no effect, i.e., both max_used_connections and the
> number of sleeping/persistent connections both shot right back up to
> their
> "ceiling" level of ~250.  Any thoughts on this?
>
Strange ...

Ok, put the pconnects back, and try a 20 second wait_timeout.

The 'ceiling' is 'max_connections' either in one of the my.cnf or as
an option in your rc.* scripts.
mysql> show variables like '%connect%';

CHECK TABLES been run ?

What is Apache's MaxRequestsPerChild set at ?

MySQL back_log ?

mysql> show variables like '%back%';

(stabbing wildly in the dark.)

--
Don Read   [EMAIL PROTECTED]
-- It's always darkest before the dawn. So if you are going to
   steal the neighbor's newspaper, that's the time to do it.
(53kr33t w0rdz: sql table query)
--
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: Error 28

[dpgirago]

Perhaps it's this...

C:\>perror 28
Error code  28:  No space left on device


David



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

Re: Could we make this a web discussion forum?

[David T-G]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Jeremy, et al --

...and then Jeremy Zawodny said...
% 
% On Mon, Mar 31, 2003 at 11:37:47AM -0800, Seth Brundle wrote:
% > First people, please dont get all angry about my suggestion...please hear me
% > out...

I admit that I was one of those to bristle when I read your first post in
the thread, and was very happy to see others smack down the idea :-)
Your later comment that mail and news would be nice is right up my alley;
I'm all in favor of that, just like the perl.com lists.

In case there's still any question, I would loudly bewail the
disappearance of this list ("make this a web discussion forum") and would
never visit a web site or even a newsgroup.


% 
...
% > Unless you have the entire history of the mailing list downloaded to
% > your mail spool and have a very powerful, feature-rich, and most
% > importatly very fast email search tool, you cant both search
% > archives and post messages with the same piece of software.
% 
% Agreed.  I've yet to see a good mail tool that does it all.  But that
% doesn't mean mailing lists bad.

I guess neither of you have seen mutt with an nntp patch applied ;-)

I think the real problem is that MS Outhouse has taken over much of the
email traffic running today in the hands of 'net newbies, unlike a sane
and simple newsreader (like, say, trn) in the hands of good netizens.
Frankly I long a bit for the days when TOFU was unknown, everyone knew
how to be McQ, and one could either be a dependent leaf or just pull down
your own mail and news to have it locally (web sites aren't typically
meant to be mirrored by the anonymous visitor).


mysql query,
:-D
- -- 
David T-G  * There is too much animal courage in 
(play) [EMAIL PROTECTED] * society and not sufficient moral courage.
(work) [EMAIL PROTECTED]  -- Mary Baker Eddy, "Science and Health"
http://justpickone.org/davidtg/  Shpx gur Pbzzhavpngvbaf Qrprapl Npg!

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.0.7 (FreeBSD)

iD8DBQE+iddBGb7uCXufRwARAqp4AJ9OJzKWYAXm0sXlS1tz5BNovTH+QACgh7Zf
6FIv6KNSv5rtmDPABAFKET4=
=Vah3
-END PGP SIGNATURE-

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

Error 28

[Hutterer]

DO not know what I have changed, but afte mmonths of using mysql without
problems - 
NOW I get ERROR 28 ( (errno: 28) and  cannot create new databases.
 
Does anybody know how to fix this
Thanks Robert
 
 

Fw: Mysql and postfix

[Juan Nin]

> From: "Arthur Lessard" <[EMAIL PROTECTED]>
> 
> > Thanks, Juan, and Patrik as well, this helped
> > immensely.  I can get postfix working, and it seems to
> > read the mysql tables on startup, which it wouldn't do
> > before.
 
 great  :)
 
> > However, I still have one bad problem - it
> > won't accept mail.  Specifically, when I telnet to
> > port 25, postfix looks OK, but if I send mail to any
> > aaddres (using manual commands at the telnet prompt,
> > which I usually use for troubleshooting mail), it
> > accepts the EHLO and MAIL commands, but as soon as I
> > specify a destination mail addres, it kills the telnet
> > session and postfix seems to go into a hibernating
> > state for a while (during which I see a new process,
> > "cleanup -t unix -u -c"), and it won't accept telnet
> > sessions to port 25 anymore.  After a while, it comes
> > back.
 
 are you using the master.cf that the package installed, or are you using
another previous version you had?
it may be that
 
 if not, check for error messages in /var/log/mailog
 
 Juan
 


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

Re: mySQL on Windows 2000 Server

[MySQL]

I am running MySQL on both NT4 and Win2K servers across a 64k WAN with over
600 users at 60 geographical sites - It rocks !!!

Jeff

- Original Message -
From: "Bernhard Döbler" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, April 01, 2003 7:34 PM
Subject: Re: mySQL on Windows 2000 Server


Not only is MySQL a native Win2k app, but it also runs as a NT-Service...

Bernhard

- Original Message -
From: "Egor Egorov" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Friday, March 28, 2003 2:34 PM
Subject: re: mySQL on Windows 2000 Server


> On Friday 28 March 2003 11:23, Neil Tompkins wrote:
>
> > Has anyone installed mySQL on Windows 2000 server, can you tell me your
> > experiences, or should I look for another option ?
>
> MySQL is a native win32 application and runs excellent on Win2k. It's a
good
> choice.
>

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

R: R: Replication don't work.

[trashMan]

Sorry for my mails, but i'm blocked with my work from several days.

Last info about my problem.

The webserver.bin is not updated after insert operation in db. I've
found a Delayed_insert...

If i do on the master

>SHOW PROCESSLIST;

++--+-+--++-
-+-+--+
| Id | User | Host| db   | Command|
Time | State  | Info |
++--+-+--++-
-+-+--+
|  2 | baldomax | localhost   | NULL | Sleep  |
2|| NULL |   |  6 | ODBC | localhost
| zope | Query  | 0| N   | show processlist
|
| 17 | ODBC | localhost   | zope | Sleep  |
720  || NULL |
| 18 | ODBC | localhost   | zope | Sleep  |
28   || NULL |
| 19 | ODBC | localhost   | zope | Sleep  |
24   || NULL |
| 24 | repl | ppp-X.inwind.it | NULL | Binlog Dump| 257  |
Slave connection: waiting for binlog update | NULL |
| 25 | DELAYED  | localhost   | zope | Delayed_insert |
129  | Waiting on cond| log  |
++--+-+--++-
-+-+--+
7 rows in set (0.00 sec)



Massi


-Messaggio originale-
Da: trashMan [mailto:[EMAIL PROTECTED] 
Inviato: martedì 1 aprile 2003 11.31
A: [EMAIL PROTECTED]
Oggetto: R: R: Replication don't work.



I've found the error, but now i must solve it!
The master, when i execute an insert operation in db, don't update the
'webserver-bin.010'. If i do 

>SHOW MASTER STATUS;

The pos is the same as before and webserver-bin.010 is not updated.

I've tried to do the operation

>SET SQL_LOG_BIN = 1

But the result is the same.

What can i do now??

Massi


-Original Message-
From: trashMan [mailto:[EMAIL PROTECTED] 
Sent: Monday, March 31, 2003 3:19 PM
To: 'Scott Helms'; [EMAIL PROTECTED]
Subject: R: R: Replication don't work.

Sigh.


Master.err
MySql: Pronto per le connessioni  (ready for connection)

Slave.err

030401  1:04:17  Slave: connected to master '[EMAIL PROTECTED]:3306',
replication started in log 'webserver-bin.010' at position 73

Master.info of slave

webserver-bin.010
73
xx.xx.xx.x
replica
pwdreplica
3306
60

My.ini  of master

[mysqld]
skip-innodb
basedir=C:/mysql
datadir=C:/mysql/data
log-bin
server-id=1
language=C:/mysql/share/italian
binlog-do-db=Zope



My.ini  of slave

[mysqld]
skip-innodb
port=3306
language=c:/programmi/mysql/share/italian
server-id=12
master-host=xx.xx.x.xx
master-user=replica
master-password=pwdreplica
master-port=3306
basedir=C:/Programmi/mysql/
datadir=C:/Programmi/mysql/data/
replicate-do-db=Zope




-Messaggio originale-
Da: Scott Helms [mailto:[EMAIL PROTECTED] 
Inviato: martedì 1 aprile 2003 0.46
A: trashMan
Cc: 'Jennifer Goodie'; mysql
Oggetto: Re: R: Replication don't work.


Massi,

You will get much better results if you provide the following:

Copies of:
my.cnf file from both master and slave
(look in $MYSQL/data or /etc)

$HOSTNAME.err logs from master and slave

master.info file from slave
(look in $MYSQL/data directory) 
and check that one does not exist on the master, this gave me fits for
several hours one day while moving a slave to being the master when the
then current master died of a bad hard drive.

No guarantees, but this will greatly improve your chances of an
intelligent answer. ;p

Scott Helms

On Mon, 2003-03-31 at 17:04, trashMan wrote:
> The error.log on the master dont' report anything...
> 
> MySql: ready for connections
> 
> Other info
> 
> >Show master status;
> 
> ***
> Headeranswer note
> ***
> File webserver-bin.008
> Position 73  
> 
> 
> 
> >SHOW PROCESSLIST
> 
> ***
> Header   answer
> ***
> User userreplica
> Host X
> Db   NULL
> Command  Binlog dump
> Time (the time passed from the mysql
> start)
> Queryconnect
> Time (the time passed from the mysql
> Stateslave connection: waiting for binlog update
> 
> 
> :-/
> 
> Massi
> 
> 
> 
> 
> 
> -Messaggio originale-
> Da: Jennifer Goodie [mailto:[EMAIL PROTECTED]
> Inviato: lunedì 31 marzo 2003 23.41
> A: trashMan; [EMAIL PROTECTED]
> Oggetto: RE: Replication don't work.
> 
> 
> It the master actually writting to the binlog?  What does the master 
> 

RE: password not working from command line

[Andy Eastham]

Eldon,

Make sure you don't enter a space between -u and the username and -p and the
password

ie mysql -uuser -ppassword

Andy

> -Original Message-
> From: Eldon Ziegler [mailto:[EMAIL PROTECTED]
> Sent: 01 April 2003 16:15
> To: [EMAIL PROTECTED]
> Subject: password not working from command line
>
>
> The password I entered in a GRANT statement isn't being accepted from the
> command line after entering mysql -u username -p and then entering the
> password from the GRANT statement. Is there something else I need to do?
>
>
> --
> 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 and postfix

[Arthur Lessard]

Thanks, Juan, and Patrik as well, this helped
immensely.  I can get postfix working, and it seems to
read the mysql tables on startup, which it wouldn't do
before.  However, I still have one bad problem - it
won't accept mail.  Specifically, when I telnet to
port 25, postfix looks OK, but if I send mail to any
aaddres (using manual commands at the telnet prompt,
which I usually use for troubleshooting mail), it
accepts the EHLO and MAIL commands, but as soon as I
specify a destination mail addres, it kills the telnet
session and postfix seems to go into a hibernating
state for a while (during which I see a new process,
"cleanup -t unix -u -c"), and it won't accept telnet
sessions to port 25 anymore.  After a while, it comes
back. 

So obviously I'm still doing something wrong in
getting postix to use mysql...I've researched this on
the net, to no avail.  I realize it's mostly a postfix
question, but I was wondering if anyone else had
experienced this?

Thanks again for the compiling directions...:)

 Aj

-Original Message-
From: Juan Nin [mailto:[EMAIL PROTECTED]
Sent: Tuesday, April 01, 2003 4:24 AM
To: Arthur Lessard; [EMAIL PROTECTED]
Subject: Re: Mysql and postfix


From: "Arthur Lessard" <[EMAIL PROTECTED]>

> This is probably a topic that has been beaten to
> death, but I need help in finding a resource for
> getting postfix to support mysql on RedHat 8.0.

You can use Simon J. Mudd's SRPMs or Tuomo Soini's
ones...


Simon J. Mudd's:
http://postfix.wl0.org/en/

First install the SRPM:

# rpm -ivh postfix-xxx.src.rpm
# cd /usr/src/redhat/SOURCES/

then set the required build options

 # export POSTFIX_MYSQL=1#
this one for the MySQL
RPM from mysql.com
 # export POSTFIX_REDHAT_MYSQL=1   # this one for
Red Hat's mysql RPM

then create postfix.spec

# sh ./make-postfix.spec
# cd /usr/src/redhat/SPECS
# rpmbuild -ba postfix.spec

the RPM will be left on /usr/src/redhat/RPMS/i386/

NOTE: Simon's RPMs are configured to be chrooted by
default!!!
In his site you can see the whole list of
options

#

Tuomo Soini's
http://tis.foobar.fi/software/?postfix

# rpm -ivh postfix-xxx.src.rpm
# cd /usr/src/redhat/SPECS
# rpmbuild -ba postfix.spec --with mysql

the RPM will be left on /usr/src/redhat/RPMS/i386/
In his site you can see the whole list of options

Regards,

Juan



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

RE: Adding index fails with error 1034: 126

[Andrew Braithwaite]

Hi,

What are the sizes of your index files? (i.e. name_index2.MYI in the
datadir).

It's a complete guess but with a large table like that, it's possible that
you're reaching some kind of limit with your os/mysql setup due to file
size.  (greater than 4 GB would be my guess)

Cheers,

Andrew

-Original Message-
From: Chad Hanna [mailto:[EMAIL PROTECTED] 
Sent: Tuesday 01 April 2003 17:03
To: [EMAIL PROTECTED]
Subject: Adding index fails with error 1034: 126


Hi, I've been bashing my brains out for a day or two on this, so I'd
appreciate some pointers.

When altering a table to add indexes I get a message like :

error 1034: 126 at record pos 957847618

mysql -V tells me:
mysql  Ver 11.18 Distrib 3.23.51, for unknown-freebsdelf4.6.2 (i386)

myisamchk -dv name_index2 tells me:

myisamchk -dv name_index2

MyISAM file: name_index2
Record format:   Fixed length
Character set:   latin1 (8)
File-version:1
Creation time:   2003-04-01 13:32:18
Recover time:2003-04-01 14:26:25
Status:  checked
Data records: 13844139  Deleted blocks: 0
Datafile parts:   13844139  Deleted data:   0
Datafile pointer (bytes):4  Keyfile pointer (bytes):3
Datafile length:1190595954  Keyfile length: 522063872
Max datafile length:  369367187454  Max keyfile length:   17179868159
Recordlength:   86

table description:
Key Start Len Index   Type Rec/key Root
Blocksize
1   2 4   multip. unsigned long  0412131328
1024
6 4   unsigned long  0
2   1120  multip. char packed stripped   0 97328128
1024
5520  char stripped  0
803   char   0

show create table name_index2 tells me the create statement is:

 CREATE TABLE `name_index2` (
  `dataset_vers_id` int(10) unsigned NOT NULL default '0',
  `row_id` int(10) unsigned NOT NULL default '0',
  `quality_level` tinyint(4) NOT NULL default '100',
  `surname` char(20) NOT NULL default '',
  `emanrus` char(20) NOT NULL default '',
  `surname_id` int(10) unsigned NOT NULL default '0',
  `forename` char(20) NOT NULL default '',
  `forename_id` int(10) unsigned NOT NULL default '0',
  `forename_pos` tinyint(4) NOT NULL default '0',
  `place_code` char(3) NOT NULL default '',
  `start_year` smallint(6) NOT NULL default '0',
  `end_year` smallint(6) NOT NULL default '2100',
  KEY `name_index_idx1` (`dataset_vers_id`,`row_id`),
  KEY `name_index_idx2` (`surname`,`forename`,`place_code`)
) TYPE=MyISAM

I've been adding the indexes one at time to try and isolate the problem, the
failure occurs when I add:

alter table name_index2
 add index name_index_idx3 (surname_id, forename_id, place_code);

Possibly important: 744845 data records (~ 5%) have a surname_id = 0 I'm
using the 'large' my.cnf from the distribution with a couple of tweaks.

A couple of weeks ago, a 10% smaller table took 45 minutes to add the
indexes. This one an hour to add two indexes and several hours to fail to
add all four.

I'm getting to the stage when I'm considering MERGE tables.

Any ideas welcome!

-- 
Chad Hanna
Chairman Berkshire Family History Society www.berksfhs.org.uk

-- 
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: Auto_increment question...

[Egor Egorov]

On Tuesday 01 April 2003 08:57, Noel Wade wrote:

> Say I have a table with an auto_increment field that is being used at the
> primary key.
>
> When I insert a new record, is there any way to easily grab the primary key
> value for that new record?  I had another unique field that I was
> originally going to use to do a select statement; but now that's been
> changed to a non-unique field.
>
> Is there some way to easily snag the record that has just been inserted (or
> the primary key from this new record)?

Take a look at LAST_INSERT_ID() function:
http://www.mysql.com/doc/en/Miscellaneous_functions.html

> Or is it easier and better to determine the auto_increment value BEFORE
> doing the insert (and manually specifying the value for the primary key in
> the insert statement)?





-- 
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: R: R: Replication don't work.

[Victoria Reznichenko]

On Tuesday 01 April 2003 12:31, trashMan wrote:

> I've found the error, but now i must solve it!
> The master, when i execute an insert operation in db, don't update the
> 'webserver-bin.010'.
> If i do
>
> >SHOW MASTER STATUS;
>
> The pos is the same as before and webserver-bin.010 is not updated.
>
> I've tried to do the operation
>
> >SET SQL_LOG_BIN = 1
>
> But the result is the same.
>
> What can i do now??

Do you use binlog-do-db/binlog-ignore-db options?



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





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

re: Quick "unique" question...

[Egor Egorov]

On Tuesday 01 April 2003 09:04, Noel Wade wrote:

> I know its possible to use multiple fields for the primary key in a table
> i.e. "primary key(foo,bar)"
>
> Is it possible to do this with "unique" identifiers as well?  Like "unique
> key(foo,bar)" ??
>
> I already have a primary key on a table, but I'd also like to force two
> fields to form a unique combination (no two records should have the same
> foo & bar values).

Yes, it's possible.



-- 
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: Why Replication Stops ?

[Victoria Reznichenko]

On Tuesday 01 April 2003 08:37, hemanth wrote:

>Can any body tell me the various reasons why the MySQL replication
>stops?

Because something is going wrong ;)

Check error logs, you can find causes there.


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





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

re: processlist

[Victoria Reznichenko]

On Tuesday 01 April 2003 10:55, Martin Hudec wrote:

> can anyone tell me, please, what kind of format is Time in PROCESSLIST?

In seconds.


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





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

RE: Optimize query, avoid 'using temporary'

[Andrew Braithwaite]

Hi,

I would add some indicies to the two tables.

Looking at the below I waould add an index on the following fields:

files.directory_id
files.lang_id
files.class_tbl
access.conn_id
access.group_id
access.class_id

You can do this easily with the "alter table  add index..." syntax
easily.

It should run way faster then..

Cheers,

Andrew


-Original Message-
From: Eric Persson [mailto:[EMAIL PROTECTED] 
Sent: Tuesday 01 April 2003 19:02
To: MySQL List
Subject: Optimize query, avoid 'using temporary'


Hi,

I have a query which I did several months ago, it recently caught my
attention since it seems to be very slow when done a lot of times, which
causes a very loaded server.

Its a webbased filesystem, which stores access/file information in mysql,
the actual files are stored on disk. The problem is when I want to get all
files in a directory which the current user have access to.

Below are the table structures used for this(descripten below them): CREATE
TABLE access (
   class_id int(10) unsigned NOT NULL default '0',
   group_id int(10) unsigned NOT NULL default '0',
   class_tbl char(10) NOT NULL default '',
   conn_id int(10) unsigned NOT NULL default '0'
) TYPE=MyISAM;
# The table above is used for more the the accessinfo for the files, # its
also used for directories etc. Thats why conn_id==file_id in # this case.
And class_id=4 and class_tbl=file

CREATE TABLE files (
   file_id int(10) unsigned NOT NULL auto_increment,
   lang_id int(10) unsigned NOT NULL default '0',
   directory_id int(10) unsigned NOT NULL default '0',
   filename varchar(255) NOT NULL default '',
   PRIMARY KEY  (file_id)
) TYPE=MyISAM;
#
# Actual file information, lang_id=1 and directory_id=0 in this case #

The query I used looks like this:
SELECT files.file_id, filename FROM  access, files WHERE directory_id="0"
AND lang_id="1" AND ( files.file_id=access.conn_id AND access.group_id IN
(1) AND access.class_id="4" AND class_tbl="file" ) group by file_id order by
filename;

Since access can have several rows per file_id(associated by file_id=conn_id
) I have to use group by to avoid getting multiple lines of the same file.

The part access.group_id IN (1) is the groups which the user have access to,
could be more of them to. Used one for simplicity here.

An explain of the query gives me:
mysql> explain SELECT files.file_id, filename FROM  access, files WHERE
directory_id="0" AND lang_id="1" AND ( files.file_id=access.conn_id AND
access.group_id IN (1) AND access.class_id="4" AND class_tbl="file" ) group
by file_id order by filename\G
*** 1. row ***
 table: access
  type: ALL
possible_keys: NULL
   key: NULL
   key_len: NULL
   ref: NULL
  rows: 8958
 Extra: where used; Using temporary; Using filesort
*** 2. row ***
 table: files
  type: eq_ref
possible_keys: PRIMARY
   key: PRIMARY
   key_len: 4
   ref: access.conn_id
  rows: 1
 Extra: where used
2 rows in set (0.00 sec)

If I exclude the group by and order by parts I get only where used, which is
good, but gives me the wrong result.

Is it possible to rewrite the query to get better performance out of this?
Or do I have to change the table structure?

Thanks in advance, best regards,
Eric






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

Adding index fails with error 1034: 126

[Chad Hanna]

Hi, I've been bashing my brains out for a day or two on this, so I'd
appreciate some pointers.

When altering a table to add indexes I get a message like :

error 1034: 126 at record pos 957847618

mysql -V tells me:
mysql  Ver 11.18 Distrib 3.23.51, for unknown-freebsdelf4.6.2 (i386)

myisamchk -dv name_index2 tells me:

myisamchk -dv name_index2

MyISAM file: name_index2
Record format:   Fixed length
Character set:   latin1 (8)
File-version:1
Creation time:   2003-04-01 13:32:18
Recover time:2003-04-01 14:26:25
Status:  checked
Data records: 13844139  Deleted blocks: 0
Datafile parts:   13844139  Deleted data:   0
Datafile pointer (bytes):4  Keyfile pointer (bytes):3
Datafile length:1190595954  Keyfile length: 522063872
Max datafile length:  369367187454  Max keyfile length:   17179868159
Recordlength:   86

table description:
Key Start Len Index   Type Rec/key Root
Blocksize
1   2 4   multip. unsigned long  0412131328
1024
6 4   unsigned long  0
2   1120  multip. char packed stripped   0 97328128
1024
5520  char stripped  0
803   char   0

show create table name_index2 tells me the create statement is:

 CREATE TABLE `name_index2` (
  `dataset_vers_id` int(10) unsigned NOT NULL default '0',
  `row_id` int(10) unsigned NOT NULL default '0',
  `quality_level` tinyint(4) NOT NULL default '100',
  `surname` char(20) NOT NULL default '',
  `emanrus` char(20) NOT NULL default '',
  `surname_id` int(10) unsigned NOT NULL default '0',
  `forename` char(20) NOT NULL default '',
  `forename_id` int(10) unsigned NOT NULL default '0',
  `forename_pos` tinyint(4) NOT NULL default '0',
  `place_code` char(3) NOT NULL default '',
  `start_year` smallint(6) NOT NULL default '0',
  `end_year` smallint(6) NOT NULL default '2100',
  KEY `name_index_idx1` (`dataset_vers_id`,`row_id`),
  KEY `name_index_idx2` (`surname`,`forename`,`place_code`)
) TYPE=MyISAM

I've been adding the indexes one at time to try and isolate the problem,
the failure occurs when I add:

alter table name_index2
 add index name_index_idx3 (surname_id, forename_id, place_code);

Possibly important: 744845 data records (~ 5%) have a surname_id = 0
I'm using the 'large' my.cnf from the distribution with a couple of
tweaks.

A couple of weeks ago, a 10% smaller table took 45 minutes to add the
indexes. This one an hour to add two indexes and several hours to fail
to add all four.

I'm getting to the stage when I'm considering MERGE tables.

Any ideas welcome!

-- 
Chad Hanna
Chairman Berkshire Family History Society www.berksfhs.org.uk

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

load data infile not being replicated properly....

[Andrew Braithwaite]

Hi all,

I've seen a strange replication problem with MySQL v4.0.12.

Steps to duplicate...

I have a table 'andrew'..

mysql> desc andrew;
+---+-+--+-+-+---+
| Field | Type| Null | Key | Default | Extra |
+---+-+--+-+-+---+
| f1| varchar(10) | YES  | | NULL|   |
| f2| varchar(10) | YES  | | NULL|   |
+---+-+--+-+-+---+
2 rows in set (0.00 sec)

And a text file '/home/andrew/upload' that looks like this...

hell0,hello
hi,hi

When I issue the following command on the master:

mysql> LOAD DATA LOCAL INFILE '/home/andrew/upload' into table andrew FIELDS
TERMINATED BY ',' lines terminated by '\n';
Query OK, 2 rows affected (0.01 sec)
cords: 2  Deleted: 0  Skipped: 0  Warnings: 0

The resulting table on the master looks like this..

mysql> select * from andrew;
+---+---+
| f1| f2|
+---+---+
| hell0 | hello |
| hi| hi|
+---+---+
2 rows in set (0.00 sec)

But on the slave looks it like this...

mysql> select * from andrew;
++--+
| f1 | f2   |
++--+
| hell0,hell | NULL |
| hi,hi  | NULL |
++--+
2 rows in set (0.00 sec)

Weird hey?

Works fine with tab delimited files.

Any ideas anyone?

Cheers,

Andrew

Sql,query


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

RE: PL/SQL, Views, Functions, Proceedures

[mazur]

As John said, these are supposed to be upcoming features.

Take a look at the bottom portion of this page where it says "Upcoming
 Features".
http://www.mysql.com/products/mysql/index.html

The MySQL User's Conference next week is supposed to have a spot on
the future feature of Stored Procedures too:
http://www.mysql.com/events/uc2003/session.html#Session:MySQLStoredPro
cedures

Personally, I am drooling with anticipation for these additions.  :-)

Rob Mazur

---
** ORIGINAL MESSAGE FROM:John Griffin <[EMAIL PROTECTED]>**
** ORIGINAL MESSAGE SENT:Tue Apr 01 07:39:38 **
** ORIGINAL MESSAGE BELOW **
---
re: Hi

PL/SQL is an Oracle proprietary language and can not be used. I know
that the good people at MySQL are planning to add a language to MySQL
but I do not think that there is a target release for this. Views
Functions and Procedures are also on the list of things to add to
MySQL but I do not think that there is a timeline for these things.

Adding anything to the execution path of an application a database
application for example will naturally make it slower. That said the
people at MySQL have been very good at maintaining and in some cases
improving the performance of the database so I would not worry about
new features slowing the database.

Hopefully someone from MySQL can also respond and give more
information than I have.

-Original Message-
From: hemanth [mailto:[EMAIL PROTECTED]
Sent: Tuesday April 01 2003 12:40 AM
To: [EMAIL PROTECTED]
Subject: PL/SQL Views Functions Proceedures

Hi

Why MySQL does not support PL/SQL Views Functions Procedures
Sub queries.  Will that be added in forthcoming MySQL version.

If it above mentioned features are added will the database becomes
slow.

Pls. give me some information on this issue.

Thanks in Advance.

HEMANTH
 


-- 
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: Why Replication Stops ?

[Jeremy Zawodny]

On Tue, Apr 01, 2003 at 11:07:30AM +0530, hemanth wrote:
> Hi there,
> 
>Can any body tell me the various reasons why the MySQL replication
>stops?

All of them?  No, not off the top of my head.

If you have a question about a specific instance, I'd be glad to
help.  Please post the output of SHOW SLAVE STATUS on the slave,
assuming that's where it stopped.

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

MySQL 4.0.8: up 57 days, processed 1,923,049,416 queries (389/sec. avg)

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

password not working from command line

[Eldon Ziegler]

The password I entered in a GRANT statement isn't being accepted from the 
command line after entering mysql -u username -p and then entering the 
password from the GRANT statement. Is there something else I need to do?

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

Optimize query, avoid 'using temporary'

[Eric Persson]

Hi,

I have a query which I did several months ago, it recently caught my
attention since it seems to be very slow when done a lot of times, which
causes a very loaded server.
Its a webbased filesystem, which stores access/file information in
mysql, the actual files are stored on disk. The problem is when I want
to get all files in a directory which the current user have access to.
Below are the table structures used for this(descripten below them):
CREATE TABLE access (
  class_id int(10) unsigned NOT NULL default '0',
  group_id int(10) unsigned NOT NULL default '0',
  class_tbl char(10) NOT NULL default '',
  conn_id int(10) unsigned NOT NULL default '0'
) TYPE=MyISAM;
# The table above is used for more the the accessinfo for the files,
# its also used for directories etc. Thats why conn_id==file_id in
# this case. And class_id=4 and class_tbl=file
CREATE TABLE files (
  file_id int(10) unsigned NOT NULL auto_increment,
  lang_id int(10) unsigned NOT NULL default '0',
  directory_id int(10) unsigned NOT NULL default '0',
  filename varchar(255) NOT NULL default '',
  PRIMARY KEY  (file_id)
) TYPE=MyISAM;
#
# Actual file information, lang_id=1 and directory_id=0 in this case
#
The query I used looks like this:
SELECT files.file_id, filename FROM  access, files WHERE
directory_id="0" AND lang_id="1" AND ( files.file_id=access.conn_id AND
access.group_id IN (1) AND access.class_id="4" AND class_tbl="file" )
group by file_id order by filename;
Since access can have several rows per file_id(associated by
file_id=conn_id ) I have to use group by to avoid getting multiple lines
of the same file.
The part access.group_id IN (1) is the groups which the user have access
to, could be more of them to. Used one for simplicity here.
An explain of the query gives me:
mysql> explain SELECT files.file_id, filename FROM  access, files WHERE
directory_id="0" AND lang_id="1" AND ( files.file_id=access.conn_id AND
access.group_id IN (1) AND access.class_id="4" AND class_tbl="file" )
group by file_id order by filename\G
*** 1. row ***
table: access
 type: ALL
possible_keys: NULL
  key: NULL
  key_len: NULL
  ref: NULL
 rows: 8958
Extra: where used; Using temporary; Using filesort
*** 2. row ***
table: files
 type: eq_ref
possible_keys: PRIMARY
  key: PRIMARY
  key_len: 4
  ref: access.conn_id
 rows: 1
Extra: where used
2 rows in set (0.00 sec)
If I exclude the group by and order by parts I get only where used,
which is good, but gives me the wrong result.
Is it possible to rewrite the query to get better performance out of
this? Or do I have to change the table structure?
Thanks in advance, best regards,
Eric




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

RE: PL/SQL, Views, Functions, Proceedures

[John Griffin]

Hi,

PL/SQL is an Oracle proprietary language and can not be used. I know that the good 
people at MySQL are planning to add a language to MySQL but I do not think that there 
is a target release for this. Views, Functions and Procedures are also on the list of 
things to add to MySQL but I do not think that there is a timeline for these things.

Adding anything to the execution path of an application, a database application for 
example, will naturally make it slower. That said, the people at MySQL have been very 
good at maintaining and in some cases improving the performance of the database so I 
would not worry about new features slowing the database.

Hopefully someone from MySQL can also respond and give more information than I have.

-Original Message-
From: hemanth [mailto:[EMAIL PROTECTED]
Sent: Tuesday, April 01, 2003 12:40 AM
To: [EMAIL PROTECTED]
Subject: PL/SQL, Views, Functions, Proceedures


Hi,

Why MySQL does not support PL/SQL, Views, Functions, Procedures,
Sub queries.  Will that be added in forthcoming MySQL version.

If it above mentioned features are added will the database becomes
slow.

Pls. give me some information on this issue.

Thanks in Advance.

HEMANTH
 




-- 
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: Auto_increment question...

[gerald_clark]

Check the manual for last_insert_id().

Noel Wade wrote:

Hi All,

Say I have a table with an auto_increment field that is being used at the primary key.  

When I insert a new record, is there any way to easily grab the primary key value for that new record?  I had another unique field that I was originally going to use to do a select statement; but now that's been changed to a non-unique field.

Is there some way to easily snag the record that has just been inserted (or the primary key from this new record)?

Or is it easier and better to determine the auto_increment value BEFORE doing the insert (and manually specifying the value for the primary key in the insert statement)?

Thanks, take care,

--Noel

 



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

Re: processlist

[Terence]

seconds

- Original Message - 
From: "Martin Hudec" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, April 01, 2003 3:55 PM
Subject: processlist


Hello all at mysql list,

can anyone tell me, please, what kind of format is Time in PROCESSLIST?

thanks
-- 
Martin Hudec
--
:@: [EMAIL PROTECTED]
:w: http://www.corwin.sk
:m: +421.907.303.393

"In google non est, ergo non est."
- unknown IRC operator
--

-- 
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: Could we make this a web discussion forum?

[David Brodbeck]

> -Original Message-
> From: Seth Brundle [mailto:[EMAIL PROTECTED]

> > I don't have to wait for some remote, overloaded server
> > to respond.
> 
> I dont understand this one at all.

Every web forum I've used has been slow.  I don't want to have to wait 10-20
seconds to pull up each message when I'm browsing, when in an email list my
client can pull them up instantly.  It's frustrating and a waste of my time.

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

RE: Could we make this a web discussion forum?

[David Brodbeck]

> -Original Message-
> From: Seth Brundle [mailto:[EMAIL PROTECTED]

> This makes no sense as message I post to a Yahoo! Group get emailed to
> opt-in members and appears on the group within seconds, while 
> I may not
> receive something I posted to the MySQL list for minutes or 
> sometimes hours.

It's not the turn-around time I'm referring to.  I can go do something else
during that.  It's the time to pull up each message.

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

RE: Could we make this a web discussion forum?

[David Brodbeck]

> -Original Message-
> From: Seth Brundle [mailto:[EMAIL PROTECTED]

> I've worked with MySQL for about 7 years 
> and have never
> found it convenient.

Fair enough.  But I've worked with probably half a dozen web boards and have
yet to see one I liked better than an email list.  All the ones I've tried
have been awkward and much more time consuming to use.  Anything delivered
through HTTP that plays at being realtime and two-way ends up being slow and
painful.  It's just not what the protocol was designed for.

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

Re: Mysql and postfix

[Juan Nin]

From: "Arthur Lessard" <[EMAIL PROTECTED]>

> This is probably a topic that has been beaten to
> death, but I need help in finding a resource for
> getting postfix to support mysql on RedHat 8.0.

You can use Simon J. Mudd's SRPMs or Tuomo Soini's ones...


Simon J. Mudd's:
http://postfix.wl0.org/en/

First install the SRPM:

# rpm -ivh postfix-xxx.src.rpm
# cd /usr/src/redhat/SOURCES/

then set the required build options

 # export POSTFIX_MYSQL=1# this one for the MySQL
RPM from mysql.com
 # export POSTFIX_REDHAT_MYSQL=1   # this one for Red Hat's mysql RPM

then create postfix.spec

# sh ./make-postfix.spec
# cd /usr/src/redhat/SPECS
# rpmbuild -ba postfix.spec

the RPM will be left on /usr/src/redhat/RPMS/i386/

NOTE: Simon's RPMs are configured to be chrooted by default!!!
In his site you can see the whole list of options

#

Tuomo Soini's
http://tis.foobar.fi/software/?postfix

# rpm -ivh postfix-xxx.src.rpm
# cd /usr/src/redhat/SPECS
# rpmbuild -ba postfix.spec --with mysql

the RPM will be left on /usr/src/redhat/RPMS/i386/
In his site you can see the whole list of options

Regards,

Juan



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

Re: Passwords don't work in a WinME installation?

[Mark Matthews]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Helge Moulding wrote:
[snip]
|
| 10 further investigation shows that these users have all privileges
| mysql> select * from user;
|
+---+--+--+-+-+-+-+-+---+-+---+--+---++-+++--++---+--+--+-+--+--++-+--+---+-+-+
| | Host  | User | password | Select_priv | Insert_priv |
Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv |
Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv
| Index_priv | Alter_priv | Show_db_priv | Super_priv |
Create_tmp_table_priv | Lock_tables_priv | Execute_priv |
Repl_slave_priv | Repl_client_priv | ssl_type | ssl_cipher | x509_issuer
| x509_subject | max_questions | max_updates | max_connections |
|
+---+--+--+-+-+-+-+-+---+-+---+--+---++-+++--++---+--+--+-+--+--++-+--+---+-+-+
| | localhost | root |  | Y   | Y   | Y
~  | Y   | Y   | Y | Y   | Y
| Y| Y | Y  | Y   | Y  |
Y  | Y| Y  | Y | Y
~  | Y| Y   | Y|
~ || |  | 0 |   0
|   0 |
| | % | root |  | Y   | Y   | Y
~  | Y   | Y   | Y | Y   | Y
| Y| Y | Y  | Y   | Y  |
Y  | Y| Y  | Y | Y
~  | Y| Y   | Y|
~ || |  | 0 |   0
|   0 |
| | localhost |  |  | Y   | Y   | Y
~  | Y   | Y   | Y | Y   | Y
| Y| Y | Y  | Y   | Y  |
Y  | N| N  | N | N
~  | N| N   | N|
~ || |  | 0 |   0
|   0 |
| | % |  |  | N   | N   | N
~  | N   | N   | N | N   | N
| N| N | N  | N   | N  |
N  | N| N  | N | N
~  | N| N   | N|
~ || |  | 0 |   0
|   0 |
|
+---+--+--+-+-+-+-+-+---+-+---+--+---++-+++--++---+--+--+-+--+--++-+--+---+-+-+
|
| 11 it appears that the blank user from localhost without a password is
|allowed to do anything he wants. Note that this blank user came into
|existance simply by starting mysql monitor.
Yes, except for anything that requires the 'super' privilege (shutdown,e
tc).
|
| 12 what we want is to be able to control what users have access, so lets
|add a password protected user by using the database itself
| mysql> insert into user (host, user, password)
| -> values ('localhost', 'chicken', 'soup');
|
| 13 find out what that did
| select * from user where user = 'chicken';
|
+---+-+--+-+-+-+-+-+---+-+---+--+---++-+++--++---+--+--+-+--+--++-+--+---+-+-+
| | Host  | User| password | Select_priv | Insert_priv |
Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv |
Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv
| Index_priv | Alter_priv | Show_db_priv | Super_priv |
Cr

Get "Text" back as Object in my recordset?

[Johan Ryberg]

Hi, I need desperatly some help.

I have two mashines and in ONE of them all Field Type that are "text" is
handled as Objects instead of String in my Windows 2000 server with IIS5
(ASP application)  and mySQL.

I use mySQL ODBC 3.51 and mySQL server 3.23.55-nt

If I use "Response.Write(Vartype(rsShowInfo("Info")))" in my ASP application
i got a 9 in return and that is the number of an Object

It shall be handled as a string.

Anyone?

Best Regards
Johan Ryberg

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

Linux kernel diferences between all kernel version -- differences between all soaps ...

[Luiz Rafael Culik Guimaraes]

 
 >   TheBS>>> Do you mean the "stock" kernel branches
 >   TheBS>>> (2.0, 2.2, 2.4, 2.6)?
> yes, as you point
 I need to enumerate the changes between all Version

Regards

Luiz

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

Re: mySQL on Windows 2000 Server

[Bernhard Döbler]

Not only is MySQL a native Win2k app, but it also runs as a NT-Service...

Bernhard

- Original Message - 
From: "Egor Egorov" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Friday, March 28, 2003 2:34 PM
Subject: re: mySQL on Windows 2000 Server


> On Friday 28 March 2003 11:23, Neil Tompkins wrote:
> 
> > Has anyone installed mySQL on Windows 2000 server, can you tell me your
> > experiences, or should I look for another option ?
> 
> MySQL is a native win32 application and runs excellent on Win2k. It's a good 
> choice. 
>

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

Re: Mysql and postfix

[Patrick Näf]

Hello

I would download the original source - rpm.
Then install the rpm and switch mysql on in the spec - file 
(/usr/src/redhat/SPECS/).
You can build your rpm then with rpm -bb SPEC-FILE

Patrick

Arthur Lessard wrote:

This is probably a topic that has been beaten to
death, but I need help in finding a resource for
getting postfix to support mysql on RedHat 8.0.  I'm
not a serious RedHat admin, so I'm looking more for an
rpm of a prebuilt mysql-ready postfix for RedHat, but
if I can find a set of (fairly explicit) instructions
for compiling, I can do that too.  I've found a bunch
of articles online, but most of them assume a fairly
heavy RedHat admin background.
Can anyone take pity on a relatively novice user and
point me in the right direction?
Thanks...

Aj

 



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

Fw: Fulltext search -- no wildcards in phrases?

[Bernhard Döbler]

I once got this answer to a similar question on another group.

http://www.mail-archive.com/[EMAIL PROTECTED]/msg00280.html

Bernhard

- Original Message - 
From: "Nick Arnett" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Thursday, March 27, 2003 8:59 PM
Subject: Fulltext search -- no wildcards in phrases?


> It appears to me that fulltext phrase searches cannot include wildcards.
> For example, I would expect "app* serv*" to match "application server,"
> "application services," etc.  But it returns no results, so I'm having to
> run each variation separately.  Can anyone confirm that wildcards, indeed,
> can't be used in phrase searches.  I'm doing these in Boolean mode because I
> need exact counts of occurrences.  This is on MySQL-4.0.12-nt.
>

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