Re: Specifics on using join & multiple tables

[Bruce Feist]

Roman Neuhauser wrote:

# [EMAIL PROTECTED] / 2003-06-04 09:17:01 -0400:
 

I need all data in sales1 and sales2, but only for records from acc that 
are in either/both sales1, sales2.
   

   SELECT acc.name, acc.phone, acc.acctno,
  sales1.amt AS mo1sales, sales2.amt AS mo2sales,
  (sales1.amt - sales2.amt) AS diff
   FROM acc
   LEFT JOIN sales1 USING acctno
   LEFT JOIN sales2 USING acctno
   ORDER BY diff
 

To make sure that the rows exist in either sales1 or sales2, include a
WHERE (sales1.acctno IS NOT NULL OR sales2.acctno IS NOT NULL).
Also, I suspect that sales1 and sales2 should really be a single 'sales'
table with an extra column indicating which month the sales are for (and
maybe one for year as well), but I don't have enough information to be sure.
Bruce Feist





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

Re: MySQL Secure connections

[Paul Cunningham (At Home)]

Gareth,

Thanks

It looks as though my build is not picking up the OpenSSL stuff so
HAVE_OPENSSL is not getting defined :-(

can't see why at the moment though as OpenSSL is installed in
"--with-openssl=/usr/sfw"

Paul

Gareth Davis wrote:
> 
> I've just done a similar thing. Are you sure that you are running the
> mysql client that you have just compiled.
> 
> I used the following build options
> CFLAGS="-O3" CXX=gcc CXXFLAGS="-O3 -felide-constructors -fno-exceptions
> \
>-fno-rtti" ./configure \
>   --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 \
> --includedir=/usr/include \
> --mandir=/usr \
>   --with-embedded-server \
>   --enable-thread-safe-client \
>   --with-vio \
>   --with-openssl ;
> I nicked this out of the RPM build spec and added the last two options.
> 
> Gareth.
> On Tue, 2003-06-03 at 16:04, Paul Cunningham wrote:
> > Hi,
> >
> > Please can someone help with advise on setting up a Secure MySQL Connection. The
> > details are as follows.
> >
> > I have built and configured OpenSSL 0.9.7 and MySQL 4.0.12.
> > MySQL was configured with the options 
> >   ./configure \
> >   --enable-local-infile \
> >   --with-vio --with-openssl=/usr/sfw \
> >   --enable-thread-safe-client --with-pthread \
> >   --sysconfdir=/etc/sfw/mysql \
> >   --prefix=/usr/sfw/mysql
> >
> > I have set up the certificates as described in
> > http://www.mysql.com/doc/en/Secure_Create_Certs.html
> >
> > and ran the mysql daemon as follows .
> >
> >   CONFF=/export/openssl/my.cnf
> >   /usr/sfw/mysql/bin/mysqld_safe --defaults-file=${CONFF} --user=mysql &
> >
> > the contents of /export/openssl/my.cnf being ...
> >
> >   [client]
> >   ssl-ca=/export/openssl/cacert.pem
> >   ssl-cert=/export/openssl/client-cert.pem
> >   ssl-key=/export/openssl/client-key.pem
> >   [mysqld]
> >   master-ssl-ca=/export/openssl/cacert.pem
> >   master-ssl-cert=/export/openssl/server-cert.pem
> >   master-ssl-key=/export/openssl/server-key.pem
> >
> > mysqld started okay after changing the [mysqld] entries and added the 'master-'
> > bits.
> >
> > I then tried to run the 'mysql' client as follows ...
> >
> >/usr/sfw/mysql/bin/mysql --defaults-file=/export/openssl/my.cnf
> >
> > but this fails with the following 
> >
> >/usr/sfw/mysql/bin/mysql: ERROR: unknown variable
> >  'ssl-ca=/export/openssl/cacert.pem'
> >
> > The clients does not seem to like these options either in the my.cnf file or if
> > put on the mysql command-line.
> >
> > Has anyone got any ideas where I am going wrong?
> >
> > Thanks
> > Paul
> --
> Gareth Davis <[EMAIL PROTECTED]>
> Logical Practice Systems Limited

-- 
__
  Paul Cunningham   Email:  [EMAIL PROTECTED]
  Software Engineer Work:   (+44) (0)1923 696888
  Sun Microsystems  Fax:(+44) (0)1923 696801
  54 Clarendon Road, Watford,  Herts,  WD17 1DU,  UK
__

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

nested ORDER BY statements

[Dave Terrio]

Hello - I'm a relative newcomer to development with MySQL and am having a 
problem with ordering my query results...

I first want to select the 50 most recently created records (with respect to 
my date field) and then order these with respect to another field (eg name). 
That way I'm always "ordering" the 50 most recent records.  The problem is, I 
can't figure out how to do this - as MYSQl complains when I put 2 "ORDER BY" 
statements in a query such as in:

order by table1.time_created desc limit 50 order by table1.name;

Any suggestions?

Thanks,
-David


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

Re: Row Number

[Paul Najman]

Anthony,
Here's the full definition of select statement:
SELECT [STRAIGHT_JOIN]
   [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
   [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS] [HIGH_PRIORITY]
   [DISTINCT | DISTINCTROW | ALL]
select_expression,...
[INTO {OUTFILE | DUMPFILE} 'file_name' export_options]
[FROM table_references
  [WHERE where_definition]
  [GROUP BY {unsigned_integer | col_name | formula} [ASC | DESC], ...
  [HAVING where_definition]
  [ORDER BY {unsigned_integer | col_name | formula} [ASC | DESC] ,...]
  [LIMIT [offset,] rows]
  [PROCEDURE procedure_name]
  [FOR UPDATE | LOCK IN SHARE MODE]]

So,
LIMIT 10 ... means first 10 rows (or LIMIT 0, 10)
LIMIT 10, 10 ... means next 10 rows
LIMIT 20, 10 ... means ... etc

Best wishes,
Paul Najman [EMAIL PROTECTED]

- Original Message - 
From: "Anthony Ward" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Monday, June 02, 2003 6:21 PM
Subject: Row Number


> Hi,
> 
> If i have a table with just "Userid,Name,Language" 
> where userid is primary key. 
> now if i search the first 10 people that speaks english i.e
> SELECT * FROM tablex WHERE language='English' LIMIT 10; 
> 
> Can't i get the row number of the last matched record?
> So i can do 
> SELECT...LIMIT nex-ten-starting,10
> 
> Regards,
> Anthony W.
> 
> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
> 
> 

out f memory error

[Dana Sharvit - M]

I have  a program that runs a loop,in every loop I execute a query.
the query itself is very efficient, the result of the query is 4 rows at
most but...
after a while I get the following error:
Out of memory!
Bus error (core dumped)

how can I increase the memory that is used for the query?
Thanks
Dana

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

Re: Specifics on using join & multiple tables

[Roman Neuhauser]

# [EMAIL PROTECTED] / 2003-06-04 09:17:01 -0400:
> acc.name, acc.phone, acc.acctno, sales1.amt as mo1sales, sales2.amt as 
> mo2sales, (sales1.amt - sales2.amt) as diff SORT by diff
> 
> I need all data in sales1 and sales2, but only for records from acc that 
> are in either/both sales1, sales2.
> 
> I tried:
> select  from acc,sales1,sales2 where acc.acctno=sales1.acctno and 
> acc.acctno=sales2.acctno
> I got only records which were in all three tables.
> 
> I looked at using join, but can not determine which style of JOIN I need, 
> nor how to write the JOIN statement.  Can someone please help?

this?

SELECT acc.name, acc.phone, acc.acctno,
   sales1.amt AS mo1sales, sales2.amt AS mo2sales,
   (sales1.amt - sales2.amt) AS diff
FROM acc
LEFT JOIN sales1 USING acctno
LEFT JOIN sales2 USING acctno
ORDER BY diff

you might need to employ a function or two on the selected values
to coerce the NULLs into something that will make sense in the
computations.

-- 
If you cc me or remove the list(s) completely I'll most likely ignore
your message.see http://www.eyrie.org./~eagle/faqs/questions.html

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

URGENT: Problems compiling mysql 3 and 4 on IRIX indy 6.5 IP22 mips

[Jason Buchanan]

hi,

I'm having a hard time getting mysql 4 and mysql 3 to compile on IRIX 6.5...

Does anyone have a solution to this?

Below is an example of compiling mysql 4.0.13 (looks practically 
identical for mysql 3.23.56):

g++ -DUNDEF_THREADS_HACK -I. -I. -I.. -I./../include -I../include -I./.. 
-I.. -I.. -O -DDBUG_OFF -D_BOOL -c -o mysql.o `test -f mysql.cc || 
echo './'`mysql.cc
In file included from 
/usr/local/lib/gcc-lib/mips-sgi-irix6.5/2.95.3/include/curses.h:5,
from mysql.cc:54:
/usr/include/curses.h:112: syntax error before `,'
/usr/include/curses.h:143: syntax error before `;'
/usr/include/curses.h:285: `__curses_bool_t' was not declared in this scope
/usr/include/curses.h:285: warning: `_meta' initialized and declared 
`extern'
/usr/include/curses.h:301: type specifier omitted for parameter
/usr/include/curses.h:340: syntax error before `('
/usr/include/curses.h:388: type specifier omitted for parameter
/usr/include/curses.h:390: type specifier omitted for parameter
In file included from 
/usr/local/lib/gcc-lib/mips-sgi-irix6.5/2.95.3/include/curses.h:5,
from mysql.cc:54:
/usr/include/curses.h:856: type specifier omitted for parameter
/usr/include/curses.h:859: type specifier omitted for parameter
/usr/include/curses.h:860: type specifier omitted for parameter
/usr/include/curses.h:861: type specifier omitted for parameter
/usr/include/curses.h:862: type specifier omitted for parameter
/usr/include/curses.h:863: type specifier omitted for parameter
/usr/include/curses.h:864: type specifier omitted for parameter
/usr/include/curses.h:1015: type specifier omitted for parameter
make[2]: *** [mysql.o] Error 1
make[2]: Leaving directory `/usr/people/jsb/mysql-4.0.13/client'
make[1]: *** [all-recursive] Error 1
make[1]: Leaving directory `/usr/people/jsb/mysql-4.0.13'
make: *** [all] Error 2



Thanks,
Jason


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

Specifics on using join & multiple tables

[Patrick Shoaf]

I am fairly new to MySQL and SQL in general.  I have three tables, 
acc,sales1,sales2.  All have acctno in common.  acc is general customer 
table with lots of information. sales1 is a generated table of sales for 
specific customer from a given month/year.  sales2 is the sale as sales1 
except for a different month/year.  I need:

acc.name, acc.phone, acc.acctno, sales1.amt as mo1sales, sales2.amt as 
mo2sales, (sales1.amt - sales2.amt) as diff SORT by diff

I need all data in sales1 and sales2, but only for records from acc that 
are in either/both sales1, sales2.

I tried:
select  from acc,sales1,sales2 where acc.acctno=sales1.acctno and 
acc.acctno=sales2.acctno
I got only records which were in all three tables.

I looked at using join, but can not determine which style of JOIN I need, 
nor how to write the JOIN statement.  Can someone please help?



Patrick J. Shoaf, Systems Engineer
[EMAIL PROTECTED]
Midmon Internet Services, LLC
100 Third Street
Charleroi, PA 15022
http://www.midmon.com
Phone: 724-483-2400 ext. 105
 or888-638-6963
Fax:   724-489-4386


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

Re: mysql query output get wrapped

[Don Read]

On 03-Jun-2003 Asif Iqbal wrote:
> 
> something like this
> 
> ++-+---++++---
> +---+-+
>| id | EffectiveId | Queue | Type   | IssueStatement | Resolution | Owner
>| Subject   | InitialPriority |
> ++-+---++++---
> +---+-+
>|  5 |   5 | 4 | ticket |   NULL |   NULL |16
>| RE: phonebook |  10 |
> ++-+---++++---
> +---+-+
> 
> *unwrapped*
> 

xterm +aw

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]

Re: debuggine 1205 / LOCK wait timeout exceeded errors

[Roman Neuhauser]

# [EMAIL PROTECTED] / 2003-06-03 22:59:47 +0300:
> you can use SHOW INNODB STATUS to look what active transactions you have and
> how many lock structs they have.
> 
> Also innodb_lock_monitor helps.

Hi Heikki,

thanks for the reply. I have dropped the database, recreated it,
stopped the server and started it again, turned on the
innodb_lock_monitor, and had a hard time getting it to deadlock.

After some time, it finaly issued the LOCK wait timeout exceeded
error.

I have the error log with innodb_lock_monitor messages, and the
query log. Both were empty when I started the server, and I shut
down the server right after I got the error (looks like the monitor
writes into the error log even when the server doesn't process any
queries). Plus, I was the only user accessing the server.

Unfortunately, I've forgotten to run SHOW INNODB STATUS, is that
info crucial, or can you get it from the monitor? I hope the latter
is the case since the error is intermittent, and sometimes it's
quite hard to make it show up.

I've put the log files online at http://roman.bellavista.biz/mysql/,
since they're quite big.

An interesting fact: I was trying to get it to spit out the error,
and couldn't, then left the server alone for ~30 minutes, and then
got the error immediately: grep the logs for "13:53".

What I don't understand is the TABLE LOCK lines: all the tables are
InnoDB tables, and I thought InnoDB doesn't use table locks. Or does
it. The only SQL statements in the query log that have to do with
locking are two pairs of GET_LOCK()/RELEASE_LOCK() wrapping the
creation of sequence tables.

I will be very grateful for any info.

-- 
If you cc me or remove the list(s) completely I'll most likely ignore
your message.see http://www.eyrie.org./~eagle/faqs/questions.html

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

Re: malloc'ing 2GB+ of memory in mysql

[Heikki Tuuri]

Owen,

- Original Message - 
From: "Owen Scott Medd" <[EMAIL PROTECTED]>
To: "Heikki Tuuri" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Wednesday, June 04, 2003 3:13 PM
Subject: Re: malloc'ing 2GB+ of memory in mysql


> I know we are facing this same question right now (I have 8 way servers
> with 16GB of memory running MySQL, with 5 GB sitting unused while the poor
> innodb buffer pool sits starved for memory).  Do we replace these servers
> with 4 way Opterons (are there 8 ways promised yet?) or is there another
> answer?

best to test first with a borrowed Opteron. I want to see the stability and
that fsync() and other kernel calls work fast. A customer tested recently a
big Itanium II box and its performance looked ok.

> In particular, what is involved with being a "feature sponsor"?
> (lol... show me the money, eh?).  And, more importantly, have you done any
> estimates about how long it would take to implement AWE in the RedHat AS
> environment (which happens to be our environment too)?

The sponsorship of multiple tablespaces will take all available time till
September 15th, 2003. On that day you will be able to put every InnoDB table
to its own file :). After that it might be a 2 week job to get AWE to Linux.
The idea is to attach shared memory areas to the mysqld process on demand.
Since you already have the appropriate hardware, testing is no problem.

> Curious,
> Owen

Best regards,

Heikki


> On Wed, 4 Jun 2003, Heikki Tuuri wrote:
> > Per,
> >
> > I remember someone also reporting a problem that glibc or Linux does not
> > allow creation of new threads if one has allocated >= 2 GB user memory.
I
> > think there are problems in where the OS places the excutable, thread
> > stacks, etc.
> >
> > So it is uncharted territory. Oracle seems to have an option to use AWE
> > memory on the Red Hat Advanced Server. Then the limit is 64 GB on a
32-bit
> > Intel processor. InnoDB-4.1 has the same AWE option, but only on certain
> > Windows versions.
> >
> > If Itanium and Opteron fail to take off, or a feature sponsor appears, I
may
> > consider implementing AWE also on Linux. The memory crunch is getting so
> > severe that I believe some 64-bit processor must become common by 2005.
> >
> > Best regards,
> >
> > Heikki Tuuri
> > Innobase Oy
> > http://www.innodb.com
> > Transactions, foreign keys, and a hot backup tool for MySQL
> > Order MySQL technical support from https://order.mysql.com/
> >
> >
> > - Original Message - 
> > From: "Per Andreas Buer" <[EMAIL PROTECTED]>
> > Newsgroups: mailing.database.mysql
> > Sent: Wednesday, June 04, 2003 1:21 PM
> > Subject: malloc'ing 2GB+ of memory in mysql
> >
> >
> > > Hi
> > >
> > > The Mysql binary distribution for IA32-linux is statically linked with
> > > glibc. glibc malloc limits memory allocations to 2GB, which means that
a
> > > buffer in mysql can't grow beyond 2GB. This is due to some paranoia in
> > > glibc malloc - they don't rely on the size to be an unsigned int -
which
> > > limits the size to 2^31 on any 32-bit platform.
> > >
> > > Has anyone tried to remove this limit in glibc malloc or linking Mysql
> > > with another malloc implementation?
> > >
> > > -- 
> > > Per Andreas Buer
> > >
> > > -- 
> > > MySQL General Mailing List
> > > For list archives: http://lists.mysql.com/mysql
> > > To unsubscribe:
> > http://lists.mysql.com/[EMAIL PROTECTED]
> > >
> >
> >
> >
> >
>
> -- 
> USMail:   InterGuide Communications, 230 Lyn Anne Court, Ann Arbor, MI
48103
> phone:   +1 734 997-0922 fax: +1 734 661-0324
> mailto:[EMAIL PROTECTED] http://www.interguide.com/~osm/
>
> [ Sometimes wrong.  Never in doubt. ]
>



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

Re: malloc'ing 2GB+ of memory in mysql

[Owen Scott Medd]

I know we are facing this same question right now (I have 8 way servers 
with 16GB of memory running MySQL, with 5 GB sitting unused while the poor 
innodb buffer pool sits starved for memory).  Do we replace these servers 
with 4 way Opterons (are there 8 ways promised yet?) or is there another 
answer?  In particular, what is involved with being a "feature sponsor"?  
(lol... show me the money, eh?).  And, more importantly, have you done any 
estimates about how long it would take to implement AWE in the RedHat AS 
environment (which happens to be our environment too)?

Curious,
Owen

On Wed, 4 Jun 2003, Heikki Tuuri wrote:
> Per,
> 
> I remember someone also reporting a problem that glibc or Linux does not
> allow creation of new threads if one has allocated >= 2 GB user memory. I
> think there are problems in where the OS places the excutable, thread
> stacks, etc.
> 
> So it is uncharted territory. Oracle seems to have an option to use AWE
> memory on the Red Hat Advanced Server. Then the limit is 64 GB on a 32-bit
> Intel processor. InnoDB-4.1 has the same AWE option, but only on certain
> Windows versions.
> 
> If Itanium and Opteron fail to take off, or a feature sponsor appears, I may
> consider implementing AWE also on Linux. The memory crunch is getting so
> severe that I believe some 64-bit processor must become common by 2005.
> 
> Best regards,
> 
> Heikki Tuuri
> Innobase Oy
> http://www.innodb.com
> Transactions, foreign keys, and a hot backup tool for MySQL
> Order MySQL technical support from https://order.mysql.com/
> 
> 
> - Original Message - 
> From: "Per Andreas Buer" <[EMAIL PROTECTED]>
> Newsgroups: mailing.database.mysql
> Sent: Wednesday, June 04, 2003 1:21 PM
> Subject: malloc'ing 2GB+ of memory in mysql
> 
> 
> > Hi
> >
> > The Mysql binary distribution for IA32-linux is statically linked with
> > glibc. glibc malloc limits memory allocations to 2GB, which means that a
> > buffer in mysql can't grow beyond 2GB. This is due to some paranoia in
> > glibc malloc - they don't rely on the size to be an unsigned int - which
> > limits the size to 2^31 on any 32-bit platform.
> >
> > Has anyone tried to remove this limit in glibc malloc or linking Mysql
> > with another malloc implementation?
> >
> > -- 
> > Per Andreas Buer
> >
> > -- 
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]
> >
> 
> 
> 
> 

-- 
USMail:   InterGuide Communications, 230 Lyn Anne Court, Ann Arbor, MI 48103
phone:+1 734 997-0922   fax:+1 734 661-0324
mailto:[EMAIL PROTECTED]http://www.interguide.com/~osm/

[ Sometimes wrong.  Never in doubt. ]


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

Re: malloc'ing 2GB+ of memory in mysql

[Per Andreas Buer]

Hello Heikki, 

"Heikki Tuuri" <[EMAIL PROTECTED]> writes:

> I remember someone also reporting a problem that glibc or Linux does not
> allow creation of new threads if one has allocated >= 2 GB user memory. I
> think there are problems in where the OS places the excutable, thread
> stacks, etc.

We are running a mysql-server which has a innodb_buffer_pool_size of
2040 MB, 128MB Query Cache, 16MB key_buffer and is running ~ 250
threads. We are way past 2GB and running very stable.

As our dataset has grown we need to increase the size of the
innodb_buffer_pool_size. But malloc fails when innodb_buffer_pool_size >
2GB. The comments in glibc (malloc/malloc.c) explains this as predicted
behavior.

We are considering to remove the paranoia checks from glibc and to see
whether we are able to malloc more than 2GB in one go. But I believe
someone must have done this before... (?)


> So it is uncharted territory.

:(

> ..

We are saving up cash for an Opteron now. :)

-- 
Per Andreas Buer

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

Re: Can't open privilege tables: Table 'mysql.host' doesn't exist

[Heikki Tuuri]

Nils,

- Original Message - 
From: ""Nils Valentin"" <[EMAIL PROTECTED]>
Newsgroups: mailing.database.mysql
Sent: Wednesday, June 04, 2003 7:05 AM
Subject: Re: Can't open privilege tables: Table 'mysql.host' doesn't exist


> Sorry, I should have given more details.=20
> Its Mysql 4.1 alpha on a Suse Linux system Version 8.1.
>
> =2E..and yes, the mysql.host table and the other 5 exist and are well
alive=
>  ;-).
>
> If I replace my.cnf with a version skipping innodb then it perfectly
starts=
> =20
> up, so it must be related to innodb, perhaps my.cnf and/or mysql itself.

the problem is in your my.cnf or the access rights of the person running
mysqld.

The error means mysqld cannot access the file host.frm in the mysql subdir
under the datadir. I suggest specifying the datadir explicitly in your
my.cnf.

> Best regards
>
> Nils Valentin

Best regards,

Heikki Tuuri
Innobase Oy
http://www.innodb.com
Transactions, foreign keys, and a hot backup tool for MySQL
Order MySQL technical support from https://order.mysql.com/



> > Hi Mysql list members,
> >
> > I added a Innodb database to the existing MyISAM databases and now MySQL
> > doesnt start up. From the InnoDB logfile I saw this error message
> >
> > Can't open privilege tables: Table 'mysql.host' doesn't exist
> >
> >
> > Does any body know what this error comes from ?
> >
> >  innodb logfile extract.
> >
> > 030603 16:46:20  InnoDB: Log file ./ib_logfile0 did not exist: new to be
> > created
> > InnoDB: Setting log file ./ib_logfile0 size to 80 MB
> > InnoDB: Database physically writes the file full: wait...
> > 030603 16:46:31  InnoDB: Log file ./ib_logfile1 did not exist: new to be
> > created
> > InnoDB: Setting log file ./ib_logfile1 size to 80 MB
> > InnoDB: Database physically writes the file full: wait...
> > InnoDB: Doublewrite buffer not found: creating new
> > InnoDB: Doublewrite buffer created
> > InnoDB: Creating foreign key constraint system tables
> > InnoDB: Foreign key constraint system tables created
> > 030603 16:46:48  InnoDB: Started
> > 030603 16:46:49  Fatal error: Can't open privilege tables: Table
> > 'mysql.host' doesn't exist
> > 030603 16:46:49  mysqld ended
> >
> > .
> >
> >
> > 030604 09:30:28  mysqld started
> > 030604  9:30:47  InnoDB: Database was not shut down normally.
> > InnoDB: Starting recovery from log files...
> > InnoDB: Starting log scan based on checkpoint at
> > InnoDB: log sequence number 0 43892
> > InnoDB: Doing recovery: scanned up to log sequence number 0 43892
> > 030604  9:30:48  InnoDB: Flushing modified pages from the buffer pool...
> > 030604  9:30:49  InnoDB: Started
> > 030604  9:30:49  Fatal error: Can't open privilege tables: Table
> > 'mysql.host' doesn't exist
> > 030604 09:30:49  mysqld ended
> >
> > Any reply much appreciated.
> >
> > --
> >
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
> > Valentin Nils
> > Internet Technology
> >
> >  E-Mail: [EMAIL PROTECTED]
> >  URL: http://www.knowd.co.jp
> >
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
>
> =2D-=20
>
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
> Valentin Nils
> Internet Technology
>
>  E-Mail: [EMAIL PROTECTED]
>  URL: http://www.knowd.co.jp
>
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
>
>
> -- 
> 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: standard SQL compliancy: primary keys: default: should be NOT NULL

[Bruce Feist]

Daniel Kasak wrote:

Neil Zanella wrote:

SQL92, AFAIK, specifies that if the primary
key is not explicitly set to NOT NULL then the RDBMS should 
automatically
and silently assume the user means NOT NULL.
Sounds like the end of the world to me ;)
I suppose you have to get excited about something... 
No need to belittle Neil's question; he's quite right about this. One of
the main strengths of SQL is its standardization; that makes up for a
multitude of other sins within it.  Any time an implementation is
non-standard, that means extra work in switching from one RDBMS to
another, and extra time which must be spent learning the product.  There
are better challenges to spend one's time and money on than compensating
for such deficiencies.
(This is not to say that MySQL is a poor product, of course -- it has
many wonderful features.  *Most* (or perhaps all) SQL implementations
don't meet the standard in all ways, and in each such case, it's a
problem with the product.
Extensions, by the way, are another story... although they may not be in
the standard, they allow you to take advantage of something not yet
supported by standard SQL.  That has the same disadvantages as I pointed
out above, but it also has corresponding advantages.  Leaving out a
feature that was specified in a standard 11 years ago doesn't fall into
this category, though!
Bruce Feist



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

Re: complex Subquery

[Bruce Feist]

surabhi sinha wrote:

Ihave the following subquery which i intend to use for mysql4.0.12
can u tell me how it could be formed for use since no common column
is there for it to be converted to Inner join 
What about Companyid?

Select CompanyName,City from Company where
CompanyName='Arihant Jain' and City='Delhi'
and companyid in (Select Companyid from Opport where Userid=1
and Opportunityfor=0 Union Select Companyid from Lead where Userid=1
and Qualifyflag=1 union Select companyid from lead where userid=1
and qualifyflag=2 union Select companyid from accountsorder where 
userid=1); 
How about:

Select CompanyName,City from Company c INNER JOIN Opport o ON
c.Companyid = o.Companyid
 where CompanyName='Arihant Jain' and City='Delhi'  AND Userid = 1 AND
Opportunityfor = 0
UNION
Select CompanyName,City from Company c INNER JOIN Lead l ON c.Companyid
= l.Companyid
 where CompanyName='Arihant Jain' and City='Delhi'  AND Userid = 1 AND
Qualifyflag IN (1, 2)
UNION
Select CompanyName,City from Company c INNER JOIN accountsorder a ON
c.Companyid = a.Companyid
 where CompanyName='Arihant Jain' and City='Delhi'  AND Userid = 1
Bruce Feist



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

Re: malloc'ing 2GB+ of memory in mysql

[Heikki Tuuri]

Per,

I remember someone also reporting a problem that glibc or Linux does not
allow creation of new threads if one has allocated >= 2 GB user memory. I
think there are problems in where the OS places the excutable, thread
stacks, etc.

So it is uncharted territory. Oracle seems to have an option to use AWE
memory on the Red Hat Advanced Server. Then the limit is 64 GB on a 32-bit
Intel processor. InnoDB-4.1 has the same AWE option, but only on certain
Windows versions.

If Itanium and Opteron fail to take off, or a feature sponsor appears, I may
consider implementing AWE also on Linux. The memory crunch is getting so
severe that I believe some 64-bit processor must become common by 2005.

Best regards,

Heikki Tuuri
Innobase Oy
http://www.innodb.com
Transactions, foreign keys, and a hot backup tool for MySQL
Order MySQL technical support from https://order.mysql.com/


- Original Message - 
From: "Per Andreas Buer" <[EMAIL PROTECTED]>
Newsgroups: mailing.database.mysql
Sent: Wednesday, June 04, 2003 1:21 PM
Subject: malloc'ing 2GB+ of memory in mysql


> Hi
>
> The Mysql binary distribution for IA32-linux is statically linked with
> glibc. glibc malloc limits memory allocations to 2GB, which means that a
> buffer in mysql can't grow beyond 2GB. This is due to some paranoia in
> glibc malloc - they don't rely on the size to be an unsigned int - which
> limits the size to 2^31 on any 32-bit platform.
>
> Has anyone tried to remove this limit in glibc malloc or linking Mysql
> with another malloc implementation?
>
> -- 
> Per Andreas Buer
>
> -- 
> 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

[Egor Egorov]

"Camillo Rospigliosi" <[EMAIL PROTECTED]> wrote:
> I've intalled the mysql 3.23.56-nt
> when I try to do the commands of the tutorial
> "C:\mysql\Docs\manual.html#Tutorial":
> mysql> load data local infile "pet.txt" into table pet;
> ERROR 1148: The used command is not allowed with this MySQL version
> 
> What can I do? what command is allowed with this MySQL version?

Check the following section of manual:
http://www.mysql.com/doc/en/LOAD_DATA_LOCAL.html



-- 
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: Replication of table conversions?

[Egor Egorov]

"Bialac, Dave" <[EMAIL PROTECTED]> wrote:
> I currently have two databases set up with a master/slave replication
> environment.  Right now, the tables are MyISAM, however I want to
> migrate to InnoDB so I can gain transactioins.  I've worked through the
> processes and nearly everything seems straightforward, except:  Does the
> conversion process replicate?  That is, when I alter the table, will the
> slave also alter the table, or do I need to instead stop the
> replication, alter the master, then recreate the slave?

Yes, table conversation is replicated.



-- 
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: Mysqld problem

[Victoria Reznichenko]

"Grzegorz Golofit" <[EMAIL PROTECTED]> wrote:
> I have problem with running mysqld
> I installed the following rpm packages incladed with RedHat 7.1 (Seawolf) edition 
> cd`s which I am running on my machine:
> -mysql-3.23.36-1
> - mysql-server-3.23.36.1
> - mysql-devel-3.23.36-1
> First I tried to initialize as a root tables with following command: 
> mysql_install_db and then typed in safe_mysqld &.
> I got the following inforamtion on the screen: Starting mysql deamon with database 
> from /var/lib/mysql and then 
> mysqld ended.
> In the /var/log/mysqld.log file I had the following information: 
> mysql started
> /usr/libexec/mysqld: Can`t find file ' ./mysql/host.frm' (errno: 13)
> mysqld ended
> I couldn`t see any mysql tables or databases in mentioned above file.
> Could you tell me what I did wrong 
> My Linux kernel is 2.4.2-2 on i686 and the glibc is glibc-2.2.2-10. This is 
> partitionless instalation.
> My hardwear is: Athlon 1.8 XP 

$ perror 13
Error code  13:  Permission denied

MySQL server should be owner of the datadir (/var/lib/mysql)


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





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

Re: [Fwd: More than 255 chars in column in heap table]

[Egor Egorov]

Frank de Bot <[EMAIL PROTECTED]> wrote:
> I want to make a HEAP table which must contain somewhat large text (at 
> least larger than 255 chars), but I need to store a little more data. Is 
> it somehow possible to have a column in a heap table which can contain 
> more than 255 bytes? (I'm thinking of not pretty much more like 1024 
> chars).

Nope. HEAP table doesn't support TEXT/BLOB types.



-- 
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: max()

[Victoria Reznichenko]

Jozef Kutej <[EMAIL PROTECTED]> wrote:
> mysql  Ver 12.20 Distrib 4.0.13, for pc-linux (i686)
> 
> Is this behavior OK?
> 
> mysql> select Sort FROM Bookmark where (UID='2' AND Up='0');
> +--+
> | Sort |
> +--+
> |1 |
> |1 |
> |2 |
> |3 |
> |4 |
> |5 |
> |6 |
> |8 |
> |9 |
> |   10 |
> |   11 |
> |   12 |
> |   13 |
> +--+
> 13 rows in set (0.02 sec)
> 
> mysql> select max(Sort) FROM Bookmark where UID='2' AND Up='0';
> +---+
> | max(Sort) |
> +---+
> |  NULL |
> +---+
> 1 row in set (0.00 sec)
> 

Hmm .. Worked fine on my test table.
Could you provide a test case?


-- 
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: mysqld CPU usage is almost 100% !!!

[Ahmad Rezal Ibrahim @ PNSB]

check either your using ODBC - trace is on ?
-Original Message-
From: nino [mailto:[EMAIL PROTECTED]
Sent: Thursday, June 05, 2003 11:05 AM
To: milis mysql
Subject: mysqld CPU usage is almost 100% !!!


Hi folks,

I'm experiencing some phenomenon that mysqld is consuming CPU at almost
100%. At this time, I only assemble a few database with record size is about
37000 rows. Is mysqld likely gonna crash or something ??

I also noticed that, if some other mysqld thread is active, the mysqld which
consume almost 100% CPU is decreasing its CPU usage a little bit, and
sometimes the CPU usage is normal.

Is it because my record size grows ? Anyway, it's only 37000 rows

please help me, thanks in advance

--nino 

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

Mysql

[Camillo Rospigliosi]

I've intalled the mysql 3.23.56-nt
when I try to do the commands of the tutorial
"C:\mysql\Docs\manual.html#Tutorial":
mysql> load data local infile "pet.txt" into table pet;
ERROR 1148: The used command is not allowed with this MySQL version

What can I do? what command is allowed with this MySQL version?

Ciao

Camillo Rospigliosi
Essellesse S.p.A.
Tel.: 0671039409
  0671039546


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

malloc'ing 2GB+ of memory in mysql

[Per Andreas Buer]

Hi

The Mysql binary distribution for IA32-linux is statically linked with
glibc. glibc malloc limits memory allocations to 2GB, which means that a
buffer in mysql can't grow beyond 2GB. This is due to some paranoia in
glibc malloc - they don't rely on the size to be an unsigned int - which
limits the size to 2^31 on any 32-bit platform. 

Has anyone tried to remove this limit in glibc malloc or linking Mysql
with another malloc implementation? 

-- 
Per Andreas Buer

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

Re: Mysqld problem

[Stefan Hinz]

Gregory,

> mysql started
> /usr/libexec/mysqld: Can`t find file ' ./mysql/host.frm' (errno: 13)
> mysqld ended

You forgot to run the mysql_install_db script that sets up the initial
grant tables:

http://www.mysql.com/doc/en/Post-installation.html

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

[filter fodder: sql, mysql, query]


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

Re: NEWBIE QUESTION: Copying Databases from local to server

[Stefan Hinz]

Tim,

> I have several databases currently located on my local machine that I need
> to copy to a remote server.

You can dump the contents of your databases with mysqldump, and load
those contents on your other server, using the mysql client program or
another tool like phpMyAdmin. You can even combine that, e.g.:

shell> mysqldump --host=localhost --all-databases | mysql --host=remotehost

Note that you may have to specify a number of other options, like
username and password (on both sides), and options like
--add-drop-table if you want to overwrite existing tables on your
remote server.

Please check out http://www.mysql.com/doc/en/mysqldump.html which
lists all the available options to mysqldump (don't panic, you'll need
only a few of those!). Moreover, that page has some good examples how
to use mysqldump (also in combination with mysql).

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(2): database copy

[Paul Najman]

Fabio,

I've just looked at MySQL CC properly for the first time and it doesn't seem
to have any import/export options. I use MySQL-Front, which does. You can
get the latest release from http://mysqlfront.venturemedia.de/ .

Best wishes,
Paul Najman [EMAIL PROTECTED]


- Original Message - 
From: "Fabio Bernardo" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Tuesday, June 03, 2003 4:12 PM
Subject: database copy


Hi there, I ´m using the Mysql control center with  a database called dbONE
in a PC, and I would like to copy it to another PC, Do you have any idea to
how can I do it? I dont wanna create all the tables again...
Thanks a lot

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

Re: efficient query or not?

[Anthony Ward]

Hi,

You mention this

  SELECT loc1.name, loc1.lat, loc1.lon, loc2.name, loc2.lat, loc2.lon,
  3963 * acos(cos(radians(90-loc1.lat)) * cos(radians(90-loc2.lat)) +
  sin(radians(90-loc1.lat)) * sin(radians(90-loc2.lat))
  * cos(radians(loc1.lon-loc2.lon))) AS Miles
  FROM locations AS loc1
  INNER JOIN locations AS loc2 ON loc1.id = 1 AND loc2.id = 2

the only problems is that I have tremendous problem understanding AS,
I think it is creating a "virtual" column name called Miles.
Also, if i search people that are about 100miles or XXmiles away from me,
how do i check that because
I don't understand the use of loc1.id=1 and loc2.id=2

Thanx for the help.

Anthony





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

Re: MySQL Mirroring.

[Gareth Davis]

On Wed, 2003-06-04 at 00:23, [EMAIL PROTECTED] wrote:
> Hi,
> 
> Please excuse me if this question has been discussed before.
> 
> Is there a way to securely setup 2 MySQL Servers to be in sync with each
> other?
> 
> Let's say that I have server A at home and Server B at my work and I
> want server be to mirror exactly what I do on Server A.
> 
> Can this be done?
> Is there a commercial package or provider that does something like this?
> 
> Your help will be appreciated.
> 
> Thank you.

read up on the replication setup in the admin section of the manual.

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

Gareth
-- 
Gareth Davis <[EMAIL PROTECTED]>
Logical Practice Systems Limited


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

mysqld CPU usage is almost 100% !!!

[nino]

Hi folks,

I'm experiencing some phenomenon that mysqld is consuming CPU at almost 100%. At this 
time, I only assemble a few database with record size is about 37000 rows. Is mysqld 
likely gonna crash or something ??

I also noticed that, if some other mysqld thread is active, the mysqld which consume 
almost 100% CPU is decreasing its CPU usage a little bit, and sometimes the CPU usage 
is normal.

Is it because my record size grows ? Anyway, it's only 37000 rows

please help me, thanks in advance

--nino 

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

RE: Mysqld problem

[Simon Green]

Try this in mysql/bin/perror 
Tells you what the error code means.

<---snip--->
> ./perror 13
Error code  13:  Permission denied
<---snip--->

Simon

-Original Message-
From: Grzegorz Golofit [mailto:[EMAIL PROTECTED]
Sent: 04 June 2003 08:36
To: [EMAIL PROTECTED]
Subject: Mysqld problem


I have problem with running mysqld
I installed the following rpm packages incladed with RedHat 7.1 (Seawolf)
edition cd`s which I am running on my machine:
-mysql-3.23.36-1
- mysql-server-3.23.36.1
- mysql-devel-3.23.36-1
First I tried to initialize as a root tables with following command:
mysql_install_db and then typed in safe_mysqld &.
I got the following inforamtion on the screen: Starting mysql deamon with
database from /var/lib/mysql and then 
mysqld ended.
In the /var/log/mysqld.log file I had the following information: 
mysql started
/usr/libexec/mysqld: Can`t find file ' ./mysql/host.frm' (errno: 13)
mysqld ended
I couldn`t see any mysql tables or databases in mentioned above file.
Could you tell me what I did wrong 
My Linux kernel is 2.4.2-2 on i686 and the glibc is glibc-2.2.2-10. This is
partitionless instalation.
My hardwear is: Athlon 1.8 XP 

Thanks in advance
Gregory

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

replication problem

[Peter Günthner]

Hi, i'm trying to replicate two mysql databases (master,slave) but my 
problem is that the slave don't recognized well my settings because in the 
err file he shows me:

Could not find target log during relay log initialization
Note: Failed to initialized master info
The master.info exists in the slave and it appoints to the server which has 
a file bin who are all the upgrade operations.

master.info:

merbasiu.bin
1
192.168.0.71
replicant
passwort
3306
60
The my.cnf of the server is:

[mysqld]
server-id=1
log-bin=merbasium.bin
And the my.cnf of the slave is:

[mysqld]
master-host=192.168.0.71
master-user=replicant
master-password=passwort
master-port= 3306
server-id=2
What should i do so that it functions correctly? Thanks.

_
Descubre el mayor catálogo de coches de la Red en MSN Motor. 
http://motor.msn.es/researchcentre/

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

Mysqld problem

[Grzegorz Golofit]

I have problem with running mysqld
I installed the following rpm packages incladed with RedHat 7.1 (Seawolf) edition cd`s 
which I am running on my machine:
-mysql-3.23.36-1
- mysql-server-3.23.36.1
- mysql-devel-3.23.36-1
First I tried to initialize as a root tables with following command: mysql_install_db 
and then typed in safe_mysqld &.
I got the following inforamtion on the screen: Starting mysql deamon with database 
from /var/lib/mysql and then 
mysqld ended.
In the /var/log/mysqld.log file I had the following information: 
mysql started
/usr/libexec/mysqld: Can`t find file ' ./mysql/host.frm' (errno: 13)
mysqld ended
I couldn`t see any mysql tables or databases in mentioned above file.
Could you tell me what I did wrong 
My Linux kernel is 2.4.2-2 on i686 and the glibc is glibc-2.2.2-10. This is 
partitionless instalation.
My hardwear is: Athlon 1.8 XP 

Thanks in advance
Gregory

RE: table copying/replication

[Martin Waite]

On Tue, 2003-06-03 at 22:44, Ross Simpson wrote:
> Thanks for the reply.
> 
> I didn't explain properly :)
> 
> A diagram should help:
> 
>||
> |-|  1  ||  2  | ||
> |stage| --> |prod| --> |-| ||
> |-| ||   |-|replicas|
>||
> 
> 'stage' is a staging mysql instance, where changes are made all the
> time.  When the data is ready for production, it needs to be pushed to
> 'prod', at which time it will be replicated out to all the slaves.  
> 
> Step 2 is covered by the answer to my previous question.
> 
> Step 1 is really my question.  My need is that somehow a table already
> existing on stage can be copied/replicated/etc over to prod, but _only_
> when requested, and then immediately.
> 

Run a MySQL replication chain from stage to prod to replicas.

On stage, prepare the data on shadow tables 
with different names to those used in prod.

Say, if your real tables are one, two, three, four, five
then create shadow tables one_shadow, two_shadow, three_shadow...

Once the data is ready in the shadow tables, do a rename:

rename table one to one_old, one_shadow to one, 
 two to two_old, two_shadow to two, ...

Replication will apply the rename to all your replicas 
and you should get a fairly snappy switchover.

The only drawback is that you have two copies of your tables,
which might be impractical depending on the amount of data involved.

regards,
Martin


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

authentification intranet

[antoine druon]

excuse me
i send my last e mail in french
so i reexplain
i have a mandrake 9.1 apache2 mysql 4.1 and phpmyadmin 4.0.1
my problem is :
when i give my login and my password in intranet interface, i receive 
"password not exact"
but password is good in database in mysql
i use cheklogin.php and secure.php
a another information when i give a bad login server says "user doesn't 
exist"
so please help me
thanks

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

max()

[Jozef Kutej]

mysql  Ver 12.20 Distrib 4.0.13, for pc-linux (i686)

Is this behavior OK?

mysql> select Sort FROM Bookmark where (UID='2' AND Up='0');
+--+
| Sort |
+--+
|1 |
|1 |
|2 |
|3 |
|4 |
|5 |
|6 |
|8 |
|9 |
|   10 |
|   11 |
|   12 |
|   13 |
+--+
13 rows in set (0.02 sec)

mysql> select max(Sort) FROM Bookmark where UID='2' AND Up='0';
+---+
| max(Sort) |
+---+
|  NULL |
+---+
1 row in set (0.00 sec)

mysql>


Thank you for reply.

Jozef Kutej.


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

authentification intranet

[antoine druon]

bonjour a tous,

je viens juste de m'inscrire a votre liste.
meme si je ne suis que stagiaire et mon experience pas tres longue
j'espere tout de meme pouvoir vous aider
mon projet de stage est de mettre en place un intranet
mon systeme est compose d'un serveru apache2 dun serveur mysql4.1 de 
phpmyadmin4.0.1 sur mandrake 9.1
pour me connecter a mon intranet j'utilise les scripts checklogin et 
secure.php qui s'authentifie aupres de ma base de donnees intranet
l'interface de connection se compose d'un login et d'un mot de passe
le probleme arrive
le pb ne se pose pas pour le login mais pour le password car il 
m'indique que "le mot de passe n'est pas valide"
j'ai fait le test avec un utilistauer inexistant et il me dit bien 
"utilisateur inexistant"
j'ai reverifie le mot de passe il est correct
quelqu'un aurait -il eu deja ce probleme ?
merci pour votre aide

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

RE: Displaying numbers with results - any ideas?

[Don Read]

On 02-Jun-2003 Jay Blanchard wrote:
> [snip]
> Its just a query.   And theyre being displayed on a webpage.
> 
> [/snip]
> 
> This just caught my eye ... "And theyre being displayed on a webpage."
> 
> SO, there is another language afoot here, right? You have to have
> something to retrieve to the web page, like PHP, PERL, VBScript, etc.
> You can also do the counting there when outputting each row from the
> query results.
> 

Or HTML :

Ordered (i.e. numbered) lists take the form:


   ... first list item
   ... second list item
  ...


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]

Can't create a new thread (errno 11)

[HERAS,DELFIN (HP-Belgium,ex1)]

Hi,

When we have too many connections opened at the same time (either because
they were left opened or because there are to many active connections
opened), I am getting this error message when I am trying to connect to
mysql:

"Can't create a new thread (errno 11). If you are not out of available
memory, you can consult the manual for a possible OS-dependent bug"

Assuming my problem is too many connections opened, is there any parameter
in what file I could change to increase the number of maximum allowed
connections?

If my problem is not related to the number of connections opened, what is it
then and how can I fix it?

 

Thanks already for your help,

D.

Re: C API

[Vincent . Badier]

>>#include 
>>#include 
>>#include "winsock.h"
>>#include 
>>#include 
>>#include 
>>#include 
>>
>>int main(void) {
>>MYSQL* toto;
>>
>>toto = mysql_init(toto);
>>}
>>
>>
>>/* this is the compler log */
>>
>>Compiler: Default compiler
>>Executing  gcc.exe...
>>gcc.exe "U:\mep\Dev\abr.c" -o "U:\mep\Dev\abr.exe"
>>-I"C:\Dev-Cpp\include"  -I"C:\mysql\include"   -L"C:\Dev-Cpp\lib"
>>C:\DOCUME~1\badier1\LOCALS~1\Temp/ccW8.o(.text+0x1d2):abr.c:
undefined
>>reference to [EMAIL PROTECTED]'
>>
>>Execution terminated
>>
>>
>
>Does that command list the directory where the MySQL client library
>is located, so that the compiler can find it?  -L"C:\mysql\lib\opt"
>or -L"C:\mysql\lib\debug" perhaps?

I also include thoses directories, where dll's are located, and error
remain the same :

Compiler: Default compiler
Executing  gcc.exe...
gcc.exe "U:\mep\Dev\abr.c" -o "U:\mep\Dev\abr.exe"
-I"C:\Dev-Cpp\include"  -I"C:\mysql\include"   -L"C:\Dev-Cpp\lib"
-L"c:\mysql\lib" -L"C:\mysql\lib\opt" -L"c:\mysql\lib\debug"
C:\DOCUME~1\badier1\LOCALS~1\Temp/ccKI.o(.text+0x25):abr.c: undefined
reference to [EMAIL PROTECTED]'

Execution terminated



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

Re: MySQL: standard SQL compliancy: primary keys: default: should be NOT NULL

[Peter Brawley]

There are versions of the SQL3 document available at
http://www.inf.fu-berlin.de/lehre/SS94/einfdb/SQL3/sqlindex.html and
http://dbs.uni-leipzig.de/en/lokal/standards.pdf, free of charge.

PB

-
  - Original Message -
  From: Neil Zanella
  To: MySQL Mailing List
  Sent: Wednesday, June 04, 2003 12:03 AM
  Subject: MySQL: standard SQL compliancy: primary keys: default: should be
NOT NULL



  Hello,

  I believe that MySQL is in error in reporting the following message just
  because I did not specify that the PRIMARY KEY should not be NULL. These
  days there are standards and SQL92, AFAIK, specifies that if the primary
  key is not explicitly set to NOT NULL then the RDBMS should automatically
  and silently assume the user means NOT NULL. After all, any half decent
  book on relational databases out there will tell you that primary keys
  cannot be null. So why does MySQL do it this way. By doing this MySQL is
  breaking the portability of my standard SQL code which works so well with
  postgreSQL and Oracle 9i. So why donesn't MySQL play nice and abide to
  the standard? Is this fixed in MySQL 4? I am running MySQL 3.23.54a as
  distributed with Red Hat 9, which, for some reason has not decided to
  update their mysql RPMS to MySQL 4 for that release but I am interesting
  in knowing if this has been fixed in MySQL. I think one of MySQL's goals
  should be to support standards such as SQL92 (if not SQL99). Even if
  internally some things don't work as expected, at a minimum, the
  parsers should be compatible as much as possible, including
  standard data types and assuming primary keys are not null
  by default.

  ERROR 1171: All parts of a PRIMARY KEY must be NOT NULL;  If you need NULL
  in a key, use UNIQUE instead

  BTW, for those willing to check it out, Part 1 (as well as other parts) of
  the SQL standard are available from http://webstore.ansi.org/ for 18 bucks
  as standard INCITS/ISO/IEC 9075-1-1999. The older standard is also
  available as ANSI INCITS 135-1992 (R1998) at the same price.

  Regards,

  Neil


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

Re: NEWBIE QUESTION: Copying Databases from local to server

[Kirby Lynx]

Try to use FTP to upload them.

--
>Hello,
>
>I have several databases currently located on my local machine that I need
>to copy to a remote server.
>
>Con someone explain the process and details of how this is done?
>
>Thanks,
>
>Tim Winters
>Manager, Creative Development
>Sampling Technologies Incorporated (STI)
>[EMAIL PROTECTED]
>[EMAIL PROTECTED]
>W: 902 450 5500
>C:  902 430 8498
>
>
>
>
>-- 
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
Kirby Lynx
Amid CO.LTD
[EMAIL PROTECTED]
--
Kirby wrote this at 13:37:59 2003-06-04



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

Re: complex Subquery

[Daniel Kasak]

surabhi sinha wrote:

Dear sir,
Ihave the following subquery which i intend to use for mysql4.0.12
can u tell me how it could be formed for use since no common column
is there for it to be converted to Inner join


Select CompanyName,City from Company where
CompanyName='Arihant Jain' and City='Delhi'
and companyid in (Select Companyid from Opport where Userid=1
and Opportunityfor=0 Union Select Companyid from Lead where Userid=1
and Qualifyflag=1 union Select companyid from lead where userid=1
and qualifyflag=2 union Select companyid from accountsorder where 
userid=1);
I'd take the subquery in the 'in' section and turn it into a 'create 
temporary table' statement, run it first, then run the main query, 
linking on the Companyid, then when you are finished, drop the temporary 
table (or let it drop itself when your connection is closed).

--
Daniel Kasak
IT Developer
* NUS Consulting Group*
Level 18, 168 Walker Street
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: [EMAIL PROTECTED]
website: www.nusconsulting.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: MySQL: standard SQL compliancy: primary keys: default: should be NOT NULL

[Daniel Kasak]

Neil Zanella wrote:

Hello,

I believe that MySQL is in error in reporting the following message just
because I did not specify that the PRIMARY KEY should not be NULL. These
days there are standards and SQL92, AFAIK, specifies that if the primary
key is not explicitly set to NOT NULL then the RDBMS should automatically
and silently assume the user means NOT NULL. After all, any half decent
book on relational databases out there will tell you that primary keys
cannot be null. So why does MySQL do it this way. By doing this MySQL is
breaking the portability of my standard SQL code which works so well with
postgreSQL and Oracle 9i. So why donesn't MySQL play nice and abide to
the standard? Is this fixed in MySQL 4? I am running MySQL 3.23.54a as
distributed with Red Hat 9, which, for some reason has not decided to
update their mysql RPMS to MySQL 4 for that release but I am interesting
in knowing if this has been fixed in MySQL. I think one of MySQL's goals
should be to support standards such as SQL92 (if not SQL99). Even if
internally some things don't work as expected, at a minimum, the
parsers should be compatible as much as possible, including
standard data types and assuming primary keys are not null
by default.
ERROR 1171: All parts of a PRIMARY KEY must be NOT NULL;  If you need NULL 
in a key, use UNIQUE instead

BTW, for those willing to check it out, Part 1 (as well as other parts) of
the SQL standard are available from http://webstore.ansi.org/ for 18 bucks
as standard INCITS/ISO/IEC 9075-1-1999. The older standard is also 
available as ANSI INCITS 135-1992 (R1998) at the same price.

Regards,

Neil

 

Sounds like the end of the world to me ;)
I suppose you have to get excited about something...
--
Daniel Kasak
IT Developer
* NUS Consulting Group*
Level 18, 168 Walker Street
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: [EMAIL PROTECTED]
website: www.nusconsulting.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

NEWBIE QUESTION: Copying Databases from local to server

[Creative Solutions New Media]

Hello,

I have several databases currently located on my local machine that I need
to copy to a remote server.

Con someone explain the process and details of how this is done?

Thanks,

Tim Winters
Manager, Creative Development
Sampling Technologies Incorporated (STI)
[EMAIL PROTECTED]
[EMAIL PROTECTED]
W: 902 450 5500
C:  902 430 8498




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

complex Subquery

[surabhi sinha]

Dear sir,
Ihave the following subquery which i intend to use for 
mysql4.0.12
can u tell me how it could be formed for use since no common 
column
is there for it to be converted to Inner join



Select CompanyName,City from Company where
CompanyName='Arihant Jain' and City='Delhi'
and companyid in (Select Companyid from Opport where Userid=1
and Opportunityfor=0 Union Select Companyid from Lead where 
Userid=1
and Qualifyflag=1 union Select companyid from lead where 
userid=1
and qualifyflag=2 union Select companyid from accountsorder where 
userid=1);
___
Impress your clients! Send mail from me @ mycompany.com .
Just Rs.1499/year.
Click http://www.rediffmailpro.com to know more.

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

MySQL: standard SQL compliancy: primary keys: default: should be NOT NULL

[Neil Zanella]

Hello,

I believe that MySQL is in error in reporting the following message just
because I did not specify that the PRIMARY KEY should not be NULL. These
days there are standards and SQL92, AFAIK, specifies that if the primary
key is not explicitly set to NOT NULL then the RDBMS should automatically
and silently assume the user means NOT NULL. After all, any half decent
book on relational databases out there will tell you that primary keys
cannot be null. So why does MySQL do it this way. By doing this MySQL is
breaking the portability of my standard SQL code which works so well with
postgreSQL and Oracle 9i. So why donesn't MySQL play nice and abide to
the standard? Is this fixed in MySQL 4? I am running MySQL 3.23.54a as
distributed with Red Hat 9, which, for some reason has not decided to
update their mysql RPMS to MySQL 4 for that release but I am interesting
in knowing if this has been fixed in MySQL. I think one of MySQL's goals
should be to support standards such as SQL92 (if not SQL99). Even if
internally some things don't work as expected, at a minimum, the
parsers should be compatible as much as possible, including
standard data types and assuming primary keys are not null
by default.

ERROR 1171: All parts of a PRIMARY KEY must be NOT NULL;  If you need NULL 
in a key, use UNIQUE instead

BTW, for those willing to check it out, Part 1 (as well as other parts) of
the SQL standard are available from http://webstore.ansi.org/ for 18 bucks
as standard INCITS/ISO/IEC 9075-1-1999. The older standard is also 
available as ANSI INCITS 135-1992 (R1998) at the same price.

Regards,

Neil


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

Re: mysql query output get wrapped

[Joel Rees]

> Did not work

I wonder ...

> prompt > echo "select * from Tickets limit 1;" | mysql mydb > res
> prompt > cat res
> 
> id  EffectiveId Queue   TypeIssueStatement  Resolution
> Owner   Subject InitialPriority FinalPriority   PriorityStatus
> TimeWorked  TimeLeftToldStarts  Started Due Resolved
> LastUpdatedBy   LastUpdated Creator Created Disabled
> 5   5   4   ticket  NULLNULL16  RE: phonebook   10
> 80  10  resolved0   NULL1970-01-01 00:00:00
> NULLNULL1970-01-01 00:00:00 NULL1   2001-04-17
> 18:38:02 1   2001-04-17 18:26:46 0
> 
> Still wrapped :-)
> ...

How wide is your terminal window?

I've never had CRs actually put into the output, so all I've ever dealt
with was terminal wrap. Judging from the header lines

> > > ++-+---++++---+---+-+---+--+--++--+-++-+-+--+---+-+-+-+--+
> > > |  5 |   5 | 4 | ticket |   NULL |   NULL |16
> > > ...

I'm guessing your problem is terminal wrap, too. Viewing in the GUI (X
Windowing System) is probably your answer. Stretch your terminal window
wide. If that isn't enough, pipe to a file and break out a text editor
that scrolls horizontally as well as vertically.

-- 
Joel Rees <[EMAIL PROTECTED]>


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

Re: Can't open privilege tables: Table 'mysql.host' doesn't exist

[Nils Valentin]

Sorry, I should have given more details. 
Its Mysql 4.1 alpha on a Suse Linux system Version 8.1.

...and yes, the mysql.host table and the other 5 exist and are well alive ;-).

If I replace my.cnf with a version skipping innodb then it perfectly starts 
up, so it must be related to innodb, perhaps my.cnf and/or mysql itself.

Best regards

Nils Valentin


2003年 6月 4日 水曜日 12:36、Nils Valentin さんは書きました:
> Hi Mysql list members,
>
> I added a Innodb database to the existing MyISAM databases and now MySQL
> doesnt start up. From the InnoDB logfile I saw this error message
>
> Can't open privilege tables: Table 'mysql.host' doesn't exist
>
>
> Does any body know what this error comes from ?
>
>  innodb logfile extract.
>
> 030603 16:46:20  InnoDB: Log file ./ib_logfile0 did not exist: new to be
> created
> InnoDB: Setting log file ./ib_logfile0 size to 80 MB
> InnoDB: Database physically writes the file full: wait...
> 030603 16:46:31  InnoDB: Log file ./ib_logfile1 did not exist: new to be
> created
> InnoDB: Setting log file ./ib_logfile1 size to 80 MB
> InnoDB: Database physically writes the file full: wait...
> InnoDB: Doublewrite buffer not found: creating new
> InnoDB: Doublewrite buffer created
> InnoDB: Creating foreign key constraint system tables
> InnoDB: Foreign key constraint system tables created
> 030603 16:46:48  InnoDB: Started
> 030603 16:46:49  Fatal error: Can't open privilege tables: Table
> 'mysql.host' doesn't exist
> 030603 16:46:49  mysqld ended
>
> .
>
>
> 030604 09:30:28  mysqld started
> 030604  9:30:47  InnoDB: Database was not shut down normally.
> InnoDB: Starting recovery from log files...
> InnoDB: Starting log scan based on checkpoint at
> InnoDB: log sequence number 0 43892
> InnoDB: Doing recovery: scanned up to log sequence number 0 43892
> 030604  9:30:48  InnoDB: Flushing modified pages from the buffer pool...
> 030604  9:30:49  InnoDB: Started
> 030604  9:30:49  Fatal error: Can't open privilege tables: Table
> 'mysql.host' doesn't exist
> 030604 09:30:49  mysqld ended
>
> Any reply much appreciated.
>
> --
> 
> Valentin Nils
> Internet Technology
>
>  E-Mail: [EMAIL PROTECTED]
>  URL: http://www.knowd.co.jp
> 

-- 

Valentin Nils
Internet Technology

 E-Mail: [EMAIL PROTECTED]
 URL: http://www.knowd.co.jp



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

Can't open privilege tables: Table 'mysql.host' doesn't exist

[Nils Valentin]

Hi Mysql list members,

I added a Innodb database to the existing MyISAM databases and now MySQL 
doesnt start up. From the InnoDB logfile I saw this error message

Can't open privilege tables: Table 'mysql.host' doesn't exist


Does any body know what this error comes from ?

 innodb logfile extract.

030603 16:46:20  InnoDB: Log file ./ib_logfile0 did not exist: new to be 
created
InnoDB: Setting log file ./ib_logfile0 size to 80 MB
InnoDB: Database physically writes the file full: wait...
030603 16:46:31  InnoDB: Log file ./ib_logfile1 did not exist: new to be 
created
InnoDB: Setting log file ./ib_logfile1 size to 80 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Doublewrite buffer not found: creating new
InnoDB: Doublewrite buffer created
InnoDB: Creating foreign key constraint system tables
InnoDB: Foreign key constraint system tables created
030603 16:46:48  InnoDB: Started
030603 16:46:49  Fatal error: Can't open privilege tables: Table 'mysql.host' 
doesn't exist
030603 16:46:49  mysqld ended

.


030604 09:30:28  mysqld started
030604  9:30:47  InnoDB: Database was not shut down normally.
InnoDB: Starting recovery from log files...
InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 0 43892
InnoDB: Doing recovery: scanned up to log sequence number 0 43892
030604  9:30:48  InnoDB: Flushing modified pages from the buffer pool...
030604  9:30:49  InnoDB: Started
030604  9:30:49  Fatal error: Can't open privilege tables: Table 'mysql.host' 
doesn't exist
030604 09:30:49  mysqld ended

Any reply much appreciated.

-- 

Valentin Nils
Internet Technology

 E-Mail: [EMAIL PROTECTED]
 URL: http://www.knowd.co.jp



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

Redhat 8 thread problem...

[Tim Meader]

From what I can tell the server seems to be working acceptably. But when 
comparing it to another installation I have on Redhat 7.3, I notice that 
when MySQL starts on the Redhat 8 system it is only launching one 
/usr/sbin/mysqld process... and not the additional 2 (which I already 
understand are merely threads) that the Redhat 7.3 system launches in 
addition (what I understand is the normal behavior in Linux). I've done a 
LOT of newsgroup searching on the subject, and found only messages of 
people asking why it was starting 3 "processes" in the first place. Nothing 
like my situation through, where it doesn't seem to be starting enough. 
It's the same rpm's from MySQL's site being used for both installations 
(MySQL 3.23.56) and the Redhat 8 system has glibc installed. The only other 
difference I noticed was that the libpthread version in 7.3 was 0.09 and in 
8 was 0.10.

One final note, upon logging in to mysql on the command line, and after 
opening several connections on different systems, a "status" returns only 
one thread EVER. This cannot be correct behavior.

Any help would be greatly appreciated in this matter.

Thanks in advance.

---
Tim Meader
ODIN Unix Group
ACS Government Services, Inc. - (301) 286-8013
[EMAIL PROTECTED] 



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

Re: Find out what version I am using?

[Paul DuBois]

At 10:10 +0900 6/4/03, Nils Valentin wrote:


Hi Paul,

did I get something wrong or did you actually mean

"mysql SELECT VERSION(); " instead of " mysql> SHOW VERSION();" ???
You're right, I'm wrong.  SELECT VERSION();

(In case I am right, I didnt post as I thought is not so polite to "overwrite"
your statement)
I was incorrect, so I'll cc: this back to the list as well.  Thanks
for pointing it out.
Best regards

Nils Valentin
Tokyo/Japan


2003îN 6åé 2ì™ åéójì™ 09:54ÅAPaul DuBois DŽÇÒÇÕèëÇ´ÇÐǵLJ:
 At 22:52 -0400 5/31/03, Mehrdad Ziaei wrote:
 >That will give you version of mysql client you're running,
 That seems to have been what the original question was.

 >For version of mysql server, after successful connection to mysql, use
 >status command.
 >mysql> status
 >
 >Or
 >
 >telnet to port 3306 of server running mysql
 >C:\>telnet  192.168.1.2  3306
 >you'll see few garbage and mysql version number in there too.
 Re: that latter suggestion, it's easier to simply issue this query from
 the mysql client:
 mysql> SHOW VERSION();

 Then you don't have to ignore all the garbage characters. :-)

 >Me
 >
 >- Original Message -
 From: "Steven Kreuzer" <[EMAIL PROTECTED]>

 >To: <[EMAIL PROTECTED]>
 >Cc: "Mysql" <[EMAIL PROTECTED]>
 >Sent: Saturday, May 31, 2003 10:32 PM
 >Subject: Re: Find out what version I am using?
 >
 >>  $ mysql --version
 >>
 >>  On Sat, 2003-05-31 at 22:21, Robert Mark Bram wrote:
 >>  > Howdy all!
 >>  >
 >>  > How do I find out what version of mysql I am running from the command
 >
 >line?
 >
 >>  > Is there a type of system select statement I can execute?
 >>  >
 >>  > Rob
 >>  >
 >>  > :)
 >>  > :->
 >>  > :
 >  > > :-}
 --
 Paul DuBois
 http://www.kitebird.com/
 sql, query
--

Valentin Nils
Internet Technology
 E-Mail: [EMAIL PROTECTED]
 URL: http://www.knowd.co.jp



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

Re: Regarding inoperable symbolic links in Win2K Pro

[Nils Valentin]

Hi Charles,

Having spoken to "somebody" from MySQL AB in person last month here in Tokyo I 
know that the problem seems to be adressed currently. What I dont know is 
when, how and which new mechanism will be applied to the mailing list.

Best regards

Nils Valentin
Tokyo/Japan


2003年 6月 4日 水曜日 10:06、Charles L. Nelson さんは書きました:
> Hi,
>
> I recently posted an email about 'Inoperable Symbolic Links in Win2K Pro '
> and I subsequently subscribed to the belief that MySQL would fail when
> installed in a location that was different that the default location.  I
> tried a myriad of things to get the database system to function correctly.
> I could not get it to work when it was installed as a service or when the
> data was located somewhere different that drive c.
>
>
>
> After a couple of days I gave up and removed MySQL.  But since I am
> evaluating databases for use on my clients as well as my own platforms, I
> decided to try using MySQL one more time.  But this time I went back
> through six months of mail list archives and found the answer to my
> problems with MySQL.  I had access to the drive where I was installing the
> data restricted and when the MySQL attempted to access the drive an error
> was generated.
>
>
>
> The whole point of this story is two things,
>
>
>
> 1.) My setup was the problem and not the software
>
>
>
> and
>
>
>
> 2.) The mail list archive would benefit from some organizing.  Maybe the
> mail threads could be arranged into sections of similar information.
>
>
>
> I would be willing to help.
>
>
>
> CLN

-- 

Valentin Nils
Internet Technology

 E-Mail: [EMAIL PROTECTED]
 URL: http://www.knowd.co.jp



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

Regarding inoperable symbolic links in Win2K Pro

[Charles L. Nelson]

Hi,

I recently posted an email about 'Inoperable Symbolic Links in Win2K Pro '
and I subsequently subscribed to the belief that MySQL would fail when
installed in a location that was different that the default location.  I
tried a myriad of things to get the database system to function correctly.
I could not get it to work when it was installed as a service or when the
data was located somewhere different that drive c.



After a couple of days I gave up and removed MySQL.  But since I am
evaluating databases for use on my clients as well as my own platforms, I
decided to try using MySQL one more time.  But this time I went back through
six months of mail list archives and found the answer to my problems with
MySQL.  I had access to the drive where I was installing the data restricted
and when the MySQL attempted to access the drive an error was generated.



The whole point of this story is two things,



1.) My setup was the problem and not the software



and



2.) The mail list archive would benefit from some organizing.  Maybe the
mail threads could be arranged into sections of similar information.



I would be willing to help.



CLN

[Fwd: More than 255 chars in column in heap table]

[Frank de Bot]

Hi,

I want to make a HEAP table which must contain somewhat large text (at 
least larger than 255 chars), but I need to store a little more data. Is 
it somehow possible to have a column in a heap table which can contain 
more than 255 bytes? (I'm thinking of not pretty much more like 1024 
chars).

thanks in advanced,

Frank de Bot



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

Re: MySQL Mirroring.

[multimedia-fan]

My Message was in English.

Thank you for your help.


On Wed, 4 Jun 2003 00:33:10 +0100 , Andrew Braithwaite
<[EMAIL PROTECTED]> wrote:

>Assuming that you speak english - you can do the following...
>
>http://www.mysql.com/doc/en/Replication.html
>
>Cheers,
>
>Andrew
>
>-Original Message-
>From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
>Sent: Wednesday 04 June 2003 00:24
>To: [EMAIL PROTECTED]
>Subject: MySQL Mirroring.
>
>
>Hi,
>
>Please excuse me if this question has been discussed before.
>
>Is there a way to securely setup 2 MySQL Servers to be in sync with each
>other?
>
>Let's say that I have server A at home and Server B at my work and I want
>server be to mirror exactly what I do on Server A.
>
>Can this be done?
>Is there a commercial package or provider that does something like this?
>
>Your help will be appreciated.
>
>Thank you.


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

RE: MySQL Mirroring.

[Andrew Braithwaite]

Assuming that you speak english - you can do the following...

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

Cheers,

Andrew

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Wednesday 04 June 2003 00:24
To: [EMAIL PROTECTED]
Subject: MySQL Mirroring.


Hi,

Please excuse me if this question has been discussed before.

Is there a way to securely setup 2 MySQL Servers to be in sync with each
other?

Let's say that I have server A at home and Server B at my work and I want
server be to mirror exactly what I do on Server A.

Can this be done?
Is there a commercial package or provider that does something like this?

Your help will be appreciated.

Thank you.


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

Limiting Characters

[Steve Marquez]

I would like to limit the amount of characters that appear on a PHP page,
using PHP.

Here is the code that I am using:

$title\n";
echo "$author\n";
echo $article_contents['LimitChars'];
  
?>

This code works, however it limits the characters to one (1) character.

I would like to have the freedom to limit the characters to any length I
wish. Is there any code that does this?

Thanks,

Steve Marquez
[EMAIL PROTECTED]


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

MySQL Mirroring.

[multimedia-fan]

Hi,

Please excuse me if this question has been discussed before.

Is there a way to securely setup 2 MySQL Servers to be in sync with each
other?

Let's say that I have server A at home and Server B at my work and I
want server be to mirror exactly what I do on Server A.

Can this be done?
Is there a commercial package or provider that does something like this?

Your help will be appreciated.

Thank you.


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

RE: table copying/replication

[Andrew Braithwaite]

OK - in that case there's not much you can do with replication...  But with
mysql you could write some bash to copy the raw files over like:

Run this from /datadir (on the server you want to copy from):

tar cf - databasedirname | ssh server_you_want_it_to_go_to 'cd /datadir; tar
xf -'

And supply the password (I think you can do that with a ssh option (or set
up some ssh keys)) then (if you have the right permissions set up) - issue a
"flush tables" on all the slaves from the master that will do the job with a
simple file transfer.

If you don't have ssh then you could set up a nfs mount or something to let
your scripts do the copying (I wouldn't recommend nfs for large amounts of
fast data transfer personally...)

Hope this helps...

Andrew



-Original Message-
From: Ross Simpson [mailto:[EMAIL PROTECTED] 
Sent: Tuesday 03 June 2003 23:57
To: Andrew Braithwaite
Cc: Dathan Vance Pattishall; [EMAIL PROTECTED]
Subject: RE: table copying/replication


I looked at that type of idea, and the reason I didn't mention it was the
indexes -- each table involved has ~5 indexes, and on a large table, that
equals a lot of work that's already done on 'stage' that must be redone on
'prod'.  I'd really like to preserve the indexes from stage if at all
possible.  Maybe  there's a way to dump and reload the indexes as well as
the data..

Thanks for the help, and I hope you don't get fired.. I won't tell anyone ;)

Ross


On Tue, 2003-06-03 at 16:53, Andrew Braithwaite wrote:
> Hi,
> 
> Assuming that this system runs on *nix and that "prod" is set up to 
> replicate to all the "replicas" you could write a small bash script to 
> push the data from the "stage" to the "prod" which would then 
> replicate as normal.
> 
> I would author the script something like this...
> 
> [bash]# mysqldump -e -hstage -usomeuser somedatabasename | mysql 
> -hprod -usomeuser
> 
> This would dump the entire "somedatabasename" to the "prod" server 
> which would then replicate it to all the slaves using the -e option 
> for faster
> inserts:
> 
> If you wanted to overwrite the existing data then use the 
> --add-drop-table option to mysqldump like this:
> 
> mysqldump --add-drop-table -e -hstage -usomeuser somedatabasename | 
> mysql -hprod -usomeuser
> 
> This will then replicate as fast as your network/hardware will allow.
> 
> Hope this helps.. (I'm all for open source but it's a bit weird that 
> I'm helping out our state-side mapping competitors here - at least 
> it's not microsoft - I'll check tomorrow to make sure I don't get 
> sacked :)
> 
> Cheers,
> 
> Andrew
> multimap.com
> 
> 
> -Original Message-
> From: Ross Simpson [mailto:[EMAIL PROTECTED]
> Sent: Tuesday 03 June 2003 22:44
> To: Dathan Vance Pattishall
> Cc: [EMAIL PROTECTED]
> Subject: RE: table copying/replication
> 
> 
> Thanks for the reply.
> 
> I didn't explain properly :)
> 
> A diagram should help:
> 
>||
> |-|  1  ||  2  | ||
> |stage| --> |prod| --> |-| ||
> |-| ||   |-|replicas|
>||
> 
> 'stage' is a staging mysql instance, where changes are made all the 
> time. When the data is ready for production, it needs to be pushed to 
> 'prod', at which time it will be replicated out to all the slaves.
> 
> Step 2 is covered by the answer to my previous question.
> 
> Step 1 is really my question.  My need is that somehow a table already 
> existing on stage can be copied/replicated/etc over to prod, but 
> _only_ when requested, and then immediately.
> 
> Any thoughts?
> 
> Thanks,
> Ross
> 
> 
> On Tue, 2003-06-03 at 12:07, Dathan Vance Pattishall wrote:
> > -->-Original Message-
> > -->From: Ross Simpson [mailto:[EMAIL PROTECTED]
> > -->Sent: Tuesday, June 03, 2003 10:31 AM
> > -->To: [EMAIL PROTECTED]
> > -->Subject: table copying/replication
> > -->
> > -->Hello,
> > -->
> > -->I have a need for fast copying of a specific table from a master
> > mysql
> > -->server to a number of slave servers (say 5).
> > Create the table on the master and if the master and slave config is
> > working then the same table will be on the slave.
> > 
> > 
> > -->
> > -->The database in question could potentially have up to 2000 
> > -->tables,
> > and
> > -->at any time, one of those tables would need to be copied to all 5
> > -->of
> > the
> > -->slaves, upon command of the master.
> > Make sure you have enough inodes that's 6000 files that will be
> > opened. Also set your ulimit high enough to open all the files.
> > 
> > Replication will perform the same action on the slaves as initiated 
> > by
> > the master. There is no need for a copy.
> > 
> > -->I also looked at doing table copies (insert into .. select * from
> > ..),
> > -->but these seem pretty slow.
> > It's building the index on the fly as well, if there are indexes on
> > the dst table. It does have to scan the src table and for every row 
> > insert it into the dst table. You can tweak yo

Re: cant grant privileges?!

[Nils Valentin]

Hi Allann, Ajos, Mysql List members

As far as I know the flush privileges is not necessary when using GRANT.

flush privileges is only used after you updated the privilege database "mysql" 
manually using INSERT or UPDATE.

Somebody please correct me if I am wrong.

Best regards

Nils Valentin
Tokyo/Japan


2003年 6月 4日 水曜日 01:31、ajos さんは書きました:
> Use it after the grant command:
>
> flush privileges;
>
> >hi everybody...
> >
> >i just installed mysql 4.0.13 on a redhat linux 8.0
> >when i create a database as root and then grant privileges to another user
> >like this:
> >GRANT ALL ON somedb.* TO [EMAIL PROTECTED] IDENTIFIED BY "passwd";
>
> Allann J.
> --

-- 

Valentin Nils
Internet Technology

 E-Mail: [EMAIL PROTECTED]
 URL: http://www.knowd.co.jp



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

RE: table copying/replication

[Ross Simpson]

I looked at that type of idea, and the reason I didn't mention it was
the indexes -- each table involved has ~5 indexes, and on a large table,
that equals a lot of work that's already done on 'stage' that must be
redone on 'prod'.  I'd really like to preserve the indexes from stage if
at all possible.  Maybe  there's a way to dump and reload the indexes as
well as the data..

Thanks for the help, and I hope you don't get fired.. I won't tell
anyone ;)

Ross


On Tue, 2003-06-03 at 16:53, Andrew Braithwaite wrote:
> Hi,
> 
> Assuming that this system runs on *nix and that "prod" is set up to
> replicate to all the "replicas" you could write a small bash script to push
> the data from the "stage" to the "prod" which would then replicate as
> normal.
> 
> I would author the script something like this...
> 
> [bash]# mysqldump -e -hstage -usomeuser somedatabasename | mysql -hprod
> -usomeuser
> 
> This would dump the entire "somedatabasename" to the "prod" server which
> would then replicate it to all the slaves using the -e option for faster
> inserts:
> 
> If you wanted to overwrite the existing data then use the --add-drop-table
> option to mysqldump like this:
> 
> mysqldump --add-drop-table -e -hstage -usomeuser somedatabasename | mysql
> -hprod -usomeuser
> 
> This will then replicate as fast as your network/hardware will allow.
> 
> Hope this helps.. (I'm all for open source but it's a bit weird that I'm
> helping out our state-side mapping competitors here - at least it's not
> microsoft - I'll check tomorrow to make sure I don't get sacked :)
> 
> Cheers,
> 
> Andrew
> multimap.com
> 
> 
> -Original Message-
> From: Ross Simpson [mailto:[EMAIL PROTECTED] 
> Sent: Tuesday 03 June 2003 22:44
> To: Dathan Vance Pattishall
> Cc: [EMAIL PROTECTED]
> Subject: RE: table copying/replication
> 
> 
> Thanks for the reply.
> 
> I didn't explain properly :)
> 
> A diagram should help:
> 
>||
> |-|  1  ||  2  | ||
> |stage| --> |prod| --> |-| ||
> |-| ||   |-|replicas|
>||
> 
> 'stage' is a staging mysql instance, where changes are made all the time.
> When the data is ready for production, it needs to be pushed to 'prod', at
> which time it will be replicated out to all the slaves.  
> 
> Step 2 is covered by the answer to my previous question.
> 
> Step 1 is really my question.  My need is that somehow a table already
> existing on stage can be copied/replicated/etc over to prod, but _only_ when
> requested, and then immediately.
> 
> Any thoughts?
> 
> Thanks,
> Ross
> 
> 
> On Tue, 2003-06-03 at 12:07, Dathan Vance Pattishall wrote:
> > -->-Original Message-
> > -->From: Ross Simpson [mailto:[EMAIL PROTECTED]
> > -->Sent: Tuesday, June 03, 2003 10:31 AM
> > -->To: [EMAIL PROTECTED]
> > -->Subject: table copying/replication
> > -->
> > -->Hello,
> > -->
> > -->I have a need for fast copying of a specific table from a master
> > mysql
> > -->server to a number of slave servers (say 5).
> > Create the table on the master and if the master and slave config is 
> > working then the same table will be on the slave.
> > 
> > 
> > -->
> > -->The database in question could potentially have up to 2000 tables,
> > and
> > -->at any time, one of those tables would need to be copied to all 5 
> > -->of
> > the
> > -->slaves, upon command of the master.
> > Make sure you have enough inodes that's 6000 files that will be 
> > opened. Also set your ulimit high enough to open all the files.
> > 
> > Replication will perform the same action on the slaves as initiated by 
> > the master. There is no need for a copy.
> > 
> > -->I also looked at doing table copies (insert into .. select * from
> > ..),
> > -->but these seem pretty slow.
> > It's building the index on the fly as well, if there are indexes on 
> > the dst table. It does have to scan the src table and for every row 
> > insert it into the dst table. You can tweak you're my.cnf values to 
> > make that operation happen faster.
> > 
> > -->
> > -->Has anyone solved this problem before?  Any ideas that would help
> > out?
> > -->
> > 
> > Yes, the mysql team with replication.
> > 
> > -->
> > -->--
> > -->Ross Simpson <[EMAIL PROTECTED]>
> > -->MapQuest.com
> > -->
> > -->
> > -->--
> > -->MySQL General Mailing List
> > -->For list archives: http://lists.mysql.com/mysql
> > -->To unsubscribe: 
> > -->http://lists.mysql.com/[EMAIL PROTECTED]
> > 
> -- 
> Ross Simpson <[EMAIL PROTECTED]>
> MapQuest.com
> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
-- 
Ross Simpson <[EMAIL PROTECTED]>
MapQuest.com


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

Re: MySQL Secure connections

[Gareth Davis]

I've just done a similar thing. Are you sure that you are running the
mysql client that you have just compiled. 

I used the following build options
CFLAGS="-O3" CXX=gcc CXXFLAGS="-O3 -felide-constructors -fno-exceptions
\
   -fno-rtti" ./configure \
  --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 \
--includedir=/usr/include \
--mandir=/usr \
  --with-embedded-server \
  --enable-thread-safe-client \
  --with-vio \
  --with-openssl ;
I nicked this out of the RPM build spec and added the last two options.

Gareth.
On Tue, 2003-06-03 at 16:04, Paul Cunningham wrote:
> Hi,
> 
> Please can someone help with advise on setting up a Secure MySQL Connection. The 
> details are as follows.
> 
> I have built and configured OpenSSL 0.9.7 and MySQL 4.0.12.
> MySQL was configured with the options 
>   ./configure \
>   --enable-local-infile \
>   --with-vio --with-openssl=/usr/sfw \
>   --enable-thread-safe-client --with-pthread \
>   --sysconfdir=/etc/sfw/mysql \
>   --prefix=/usr/sfw/mysql
> 
> I have set up the certificates as described in 
> http://www.mysql.com/doc/en/Secure_Create_Certs.html
> 
> and ran the mysql daemon as follows .
> 
>   CONFF=/export/openssl/my.cnf
>   /usr/sfw/mysql/bin/mysqld_safe --defaults-file=${CONFF} --user=mysql &
> 
> the contents of /export/openssl/my.cnf being ...
> 
>   [client]
>   ssl-ca=/export/openssl/cacert.pem
>   ssl-cert=/export/openssl/client-cert.pem
>   ssl-key=/export/openssl/client-key.pem
>   [mysqld]
>   master-ssl-ca=/export/openssl/cacert.pem
>   master-ssl-cert=/export/openssl/server-cert.pem
>   master-ssl-key=/export/openssl/server-key.pem
> 
> mysqld started okay after changing the [mysqld] entries and added the 'master-' 
> bits.
> 
> I then tried to run the 'mysql' client as follows ...
> 
>/usr/sfw/mysql/bin/mysql --defaults-file=/export/openssl/my.cnf
> 
> but this fails with the following 
> 
>/usr/sfw/mysql/bin/mysql: ERROR: unknown variable
>  'ssl-ca=/export/openssl/cacert.pem'
> 
> The clients does not seem to like these options either in the my.cnf file or if 
> put on the mysql command-line.
> 
> Has anyone got any ideas where I am going wrong?
> 
> Thanks
> Paul
-- 
Gareth Davis <[EMAIL PROTECTED]>
Logical Practice Systems Limited


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

RE: table copying/replication

[Andrew Braithwaite]

Hi,

Assuming that this system runs on *nix and that "prod" is set up to
replicate to all the "replicas" you could write a small bash script to push
the data from the "stage" to the "prod" which would then replicate as
normal.

I would author the script something like this...

[bash]# mysqldump -e -hstage -usomeuser somedatabasename | mysql -hprod
-usomeuser

This would dump the entire "somedatabasename" to the "prod" server which
would then replicate it to all the slaves using the -e option for faster
inserts:

If you wanted to overwrite the existing data then use the --add-drop-table
option to mysqldump like this:

mysqldump --add-drop-table -e -hstage -usomeuser somedatabasename | mysql
-hprod -usomeuser

This will then replicate as fast as your network/hardware will allow.

Hope this helps.. (I'm all for open source but it's a bit weird that I'm
helping out our state-side mapping competitors here - at least it's not
microsoft - I'll check tomorrow to make sure I don't get sacked :)

Cheers,

Andrew
multimap.com


-Original Message-
From: Ross Simpson [mailto:[EMAIL PROTECTED] 
Sent: Tuesday 03 June 2003 22:44
To: Dathan Vance Pattishall
Cc: [EMAIL PROTECTED]
Subject: RE: table copying/replication


Thanks for the reply.

I didn't explain properly :)

A diagram should help:

   ||
|-|  1  ||  2  | ||
|stage| --> |prod| --> |-| ||
|-| ||   |-|replicas|
   ||

'stage' is a staging mysql instance, where changes are made all the time.
When the data is ready for production, it needs to be pushed to 'prod', at
which time it will be replicated out to all the slaves.  

Step 2 is covered by the answer to my previous question.

Step 1 is really my question.  My need is that somehow a table already
existing on stage can be copied/replicated/etc over to prod, but _only_ when
requested, and then immediately.

Any thoughts?

Thanks,
Ross


On Tue, 2003-06-03 at 12:07, Dathan Vance Pattishall wrote:
> -->-Original Message-
> -->From: Ross Simpson [mailto:[EMAIL PROTECTED]
> -->Sent: Tuesday, June 03, 2003 10:31 AM
> -->To: [EMAIL PROTECTED]
> -->Subject: table copying/replication
> -->
> -->Hello,
> -->
> -->I have a need for fast copying of a specific table from a master
> mysql
> -->server to a number of slave servers (say 5).
> Create the table on the master and if the master and slave config is 
> working then the same table will be on the slave.
> 
> 
> -->
> -->The database in question could potentially have up to 2000 tables,
> and
> -->at any time, one of those tables would need to be copied to all 5 
> -->of
> the
> -->slaves, upon command of the master.
> Make sure you have enough inodes that's 6000 files that will be 
> opened. Also set your ulimit high enough to open all the files.
> 
> Replication will perform the same action on the slaves as initiated by 
> the master. There is no need for a copy.
> 
> -->I also looked at doing table copies (insert into .. select * from
> ..),
> -->but these seem pretty slow.
> It's building the index on the fly as well, if there are indexes on 
> the dst table. It does have to scan the src table and for every row 
> insert it into the dst table. You can tweak you're my.cnf values to 
> make that operation happen faster.
> 
> -->
> -->Has anyone solved this problem before?  Any ideas that would help
> out?
> -->
> 
> Yes, the mysql team with replication.
> 
> -->
> -->--
> -->Ross Simpson <[EMAIL PROTECTED]>
> -->MapQuest.com
> -->
> -->
> -->--
> -->MySQL General Mailing List
> -->For list archives: http://lists.mysql.com/mysql
> -->To unsubscribe: 
> -->http://lists.mysql.com/[EMAIL PROTECTED]
> 
-- 
Ross Simpson <[EMAIL PROTECTED]>
MapQuest.com


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

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

Re: help creating foreign keys

[Jeff Mathis]

yes, quite right. thanks for pointing that out.

jeff

Kevin Fries wrote:
> 
> In InnoDB you *must* put an index on foreign keys.  It doesn't do this
> for you automatically.
> 
> You can do it in one statement:
> 
> create table bar (
>bar_idint unsigned auto_increment,
>foo_idint unsigned,
>bar_value int,
>constraint bar_pk primary key (bar_id),
>index (foo_id),
>foreign key (foo_id) references foo (foo_id)
>  ) type=innodb;
> 
> -Original Message-
> From: Jeff Mathis [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, June 03, 2003 10:09 AM
> To: Justin Scheiber
> Cc: [EMAIL PROTECTED]
> Subject: Re: help creating foreign keys
> 
> the syntax for foreign key creation is different than what you have. You
> need an alter table statement.
> 
> create table bar (
>bar_idint unsigned auto_increment primary key,
>foo_idint unsigned,
>bar_value int,
>  ) type=innodb;
> alter table bar add constraint foreign key (foo_id) references
> foo(foo_id) on delete cascade;
> 
> Justin Scheiber wrote:
> >
> > Hello, I want to create the following tables - where a foriegn key
> > references an auto_incremented primary key of another table.  In my
> > simple logic, it seem like such a thing should be possible -- after
> > all, i just need the value of the referenced primary key.  I know you
> > can't have 2 auto_increment columns in a table,  and I have read up on
> the
> > errno: 150 but it still seems like this should be possible.Do I
> need
> > to rethink the table structure?  Or do I just not understand something
> 
> > here?
> >
> > create table foo (
> >   foo_idint unsigned auto_increment,
> >   foo_value int,
> >   primary key(foo_id)
> > ) type=innodb;
> >
> > create table bar (
> >   bar_idint unsigned auto_increment,
> >   foo_idint unsigned,
> >   bar_value int,
> >   primary key (bar_id),
> >   foreign key(foo_id) references foo(foo_id),
> > ) type=innodb;
> >
> > ERROR 1005: Can't create table './test/bar.frm' (errno: 150)
> >
> > -justin
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]
> 
> --
> 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]

-- 
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: table copying/replication

[Ross Simpson]

Thanks for the reply.

I didn't explain properly :)

A diagram should help:

   ||
|-|  1  ||  2  | ||
|stage| --> |prod| --> |-| ||
|-| ||   |-|replicas|
   ||

'stage' is a staging mysql instance, where changes are made all the
time.  When the data is ready for production, it needs to be pushed to
'prod', at which time it will be replicated out to all the slaves.  

Step 2 is covered by the answer to my previous question.

Step 1 is really my question.  My need is that somehow a table already
existing on stage can be copied/replicated/etc over to prod, but _only_
when requested, and then immediately.

Any thoughts?

Thanks,
Ross


On Tue, 2003-06-03 at 12:07, Dathan Vance Pattishall wrote:
> -->-Original Message-
> -->From: Ross Simpson [mailto:[EMAIL PROTECTED]
> -->Sent: Tuesday, June 03, 2003 10:31 AM
> -->To: [EMAIL PROTECTED]
> -->Subject: table copying/replication
> -->
> -->Hello,
> -->
> -->I have a need for fast copying of a specific table from a master
> mysql
> -->server to a number of slave servers (say 5).
> Create the table on the master and if the master and slave config is
> working then the same table will be on the slave.
> 
> 
> -->
> -->The database in question could potentially have up to 2000 tables,
> and
> -->at any time, one of those tables would need to be copied to all 5 of
> the
> -->slaves, upon command of the master.
> Make sure you have enough inodes that's 6000 files that will be opened.
> Also set your ulimit high enough to open all the files.
> 
> Replication will perform the same action on the slaves as initiated by
> the master. There is no need for a copy.
> 
> -->I also looked at doing table copies (insert into .. select * from
> ..),
> -->but these seem pretty slow.
> It's building the index on the fly as well, if there are indexes on the
> dst table. It does have to scan the src table and for every row insert
> it into the dst table. You can tweak you're my.cnf values to make that
> operation happen faster.
> 
> -->
> -->Has anyone solved this problem before?  Any ideas that would help
> out?
> -->
> 
> Yes, the mysql team with replication.
> 
> -->
> -->--
> -->Ross Simpson <[EMAIL PROTECTED]>
> -->MapQuest.com
> -->
> -->
> -->--
> -->MySQL General Mailing List
> -->For list archives: http://lists.mysql.com/mysql
> -->To unsubscribe:
> -->http://lists.mysql.com/[EMAIL PROTECTED]
> 
-- 
Ross Simpson <[EMAIL PROTECTED]>
MapQuest.com


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

RE: Mysql / PHP image link problem.

[Peter Lovatt]

';

?>

Peter

-Original Message-
From: Becoming Digital [mailto:[EMAIL PROTECTED]
Sent: 03 June 2003 20:57
To: [EMAIL PROTECTED]
Subject: Re: Mysql / PHP image link problem.


FWIW, there is a PHP-DB mailing list that might prove more helpful to you.
I
know certain folk on both lists can get mighty annoyed when an off-topic
question is posted, so I thought I'd give you a heads-up.
[EMAIL PROTECTED]

Edward Dudlik
Becoming Digital
www.becomingdigital.com


- Original Message -
From: "Daniel Crompton" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, 03 June, 2003 14:20
Subject: Mysql / PHP image link problem.


I have a mysql table column called 'imagecolumn'  The rows contain links
i.e.

image.gif
image.gif
null
image.gif
image.gif

To display in my web page im using:



This displays the above images in my html page

The problem i have is where the field in my table is left blank (null), a
web
browser shows this as a broken image link icon.  i.e. it doesn't ignore it.

Does anyone know how I can get my page to ignore it if there is no link.







--
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: optimize entire db

[Stefan Hinz]

Mark,

> Can anyone give the command line syntax for optimizing the entire db?
> thanks in advance.

mysqlcheck [OPTIONS] --databases DB1 [DB2 DB3...]

This program can be used to CHECK (-c,-m,-C), REPAIR (-r), ANALYZE (-a)
or OPTIMIZE (-o) tables. Some of the options (like -e or -q) can be
used at the same time. It works on MyISAM and in some cases on BDB tables.

(The above is a copy of the output of mysqlcheck --help.)

Note that you cannot use that program for InnoDB tables.

To check all tables in a database mydb, you would issue:

mysqlcheck --optimize --databases mydb

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 entire db

[colbey]

I've used a simply shell script in the past.. run from cron to do it...
I just see someone posted a perl solution.. I've used a php one aswell..


#!/bin/sh
# DB OPTIMIZE SCRIPT - !! WARNING, DOES TABLE LOCKING DURING OPTIMIZES
user=root
pass=secret
host=10.1.1.1
db=mydb
[EMAIL PROTECTED]
mysql="/usr/local/bin/mysql "

## BUILD TABLE LIST
flag=0
for table in \
`echo show tables|$mysql --host=$host --user=$user --password=$pass $db |
tail +2` ; \
do \
  if test $flag == 1
  then
dblist="$dblist,"
  fi
  dblist="$dblist $table"
  flag=1
done

## RUN OPTIMIZE
echo optimize table $dblist|$mysql --host=$host --user=$user
--password=$pass $db >> /tmp/db_optreport ; \

mail -s "Nightly DB Optimize" $report < /tmp/db_optreport
rm /tmp/db_optreport




On Tue, 3 Jun 2003, Mark Stringham wrote:

> Hi -
>
> Can anyone give the command line syntax for optimizing the entire db?
> thanks in advance.
>
> MS
>

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

Re: Red Hat linux database to Mac OS X

[Brent Baisley]

You shouldn't have any problems if you are using mysqldump. I've used 
it plenty of times on OSX.

On Tuesday, June 3, 2003, at 02:19 PM, Greg Grasmehr wrote:

Hello,

I was wondering if anyone has experienced moving a MySQL database from
Linux/Intel X86 to Mac OS X?
I plan to use mysqldump of course.  I was only wondering if anyone has
run into problems they might share.
Thanks.

Greg

__
Do you Yahoo!?
Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
http://calendar.yahoo.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]


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

Re: Re[4]: MySql data between Linux and Windows

[Carlos Diaz]

And you are completely wrigth. There is no MySql
problem.
I solved the problem but I did not gave you all the
necessary information.
In the middle of all the problem there is also a php
application, apache and the OS so I was confused.
The problem was with php.
In php.ini there is the following configuration:
"
; As of 4.0b4, PHP always outputs a character encoding
by default in
; the Content-type: header.  To disable sending of the
charset, simply
; set it to be empty.
;
; PHP's built-in default is text/html
default_mimetype = "text/html"
default_charset = "UTF-8"
"
I am testing in Linux and for testing purposes I have
two scripts one for the database in Linux and another
that have the windows data.
I have only tested in Linux because wrigth now I
cannot access the windows machine. With the above
settings the "Linux" database will work fine.
If I change the default_charset to "iso-8859-1" the
"linux" database will show "strange" chars but if I
load the "windows" database with iso-8859-1 as default
charset then it will show all characters OK.

So what I will do is to put default_charset = "UTF-8"
in the windows machine (I have no access to the
hosting of course!) and it will work fine when
exporting to Linux.
I will test tomorrow. If there is some problem I will
mail, if all goes well as I suspect no mail.
Since these migth look confusing these is the solution
I found.
Put in php.ini the same default charset as your
hosting if you want to be able to emulate the
application and export your data without any trouble.
I apologize for taking some of your time and hope
these will be helpfull to someone in the future.
Best regards.
Carlos Díaz.

--- Stefan Hinz <[EMAIL PROTECTED]> escribió: >
Carlos,
> 
> > Character set
> > In windows 2000 latin1
> > In Linux   hosting  latin1
>  
> > Character sets
> > In windows 2000 
> > latin1 big5 czech euc_kr gb2312 gbk sjis tis620
> ujis dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7
> cp1251 danish hebrew win1251 estonia hungarian
> koi8_ukr win1251ukr greek win1250 croat cp1257
> > latin5 
>  
> > In Linux
> > latin1 dec8 dos german1 hp8 koi8_ru latin2 swe7
> usa7 cp1251 danish hebrew win1251 estonia hungarian
> koi8_ukr win1251ukr greek win1250 croat cp1257
> latin5
> 
> > phpMyAdmin version
> > Windows 2.5.0
> > Linux  2.4.0
>  
> > MySql versions
> > Windows MySQL 3.23.52-nt 
> > Linux  MySQL 3.23.56-log 
> 
> I'm giving up, sorry. I cannot see anything in the
> above that would
> cause problems with Spanish characters when
> exchanging the data
> between those two servers.
> 
> Does anyone else on this list see more than I do?
> 
> > Stefan Hinz <[EMAIL PROTECTED]> wrote:
> > Carlos,
> 
> >> The character sets in both mysql severs in linux
> and windows are the same.
> >> When I query a table which has the same data in
> spanish with accents I can see the characters
> written are different.
> 
> > 1) So what are the values of the character_set and
> character_sets
> > variables, anyway?
> 
> > 2) Which MySQL server versions do you use, for the
> local and the ISP
> > server as well?
> 
> > 3) Which version of phpMyAdmin do you use? Do they
> differ on those two
> > machines?
> 
> >> My goal is to extract data from one of the data
> bases and be able to get then to the other database
> between linux ands windows without any problem.
> >> Apache is used as web server in both Linux and
> windows.
> >> Regards.
> >> Carlos Díaz
> 
> 
> >> Stefan Hinz 
> > wrote:
> >> Carlos,
> 
> >>> We have a hosting provider with a Linux box and
> an MySql database.
> >>> We have a local Mysql database.
> >>> In both machines I use phpMyAdmin.
> 
> >> On the startpage (right frame) of phpMyAdmin you
> should see a link
> >> called "Show MySQL system variables". Look what
> the values of the
> >> variables character_set and character_sets are.
> 
> >>> When I export from Windows to Linux then all the
> data in Linux is messed up with respect to special
> characters, we work in spanish and all accents are
> lost and converted to some extrange
> >>> character.
> >>> When I export from Linux to Windows the same
> thing happens.
> >>> Of course these has to do with character sets.
> 
> >> Most probably the two MySQL servers you are using
> have different
> >> character sets.
> 
> >>> ¿Any suggestion?
> 
> >> You can find more detailed information here:
> 
> >> http://www.mysql.com/doc/en/Character_sets.html
> 
> >> And here:
> 
> >> http://www.mysql.com/doc/en/SHOW_VARIABLES.html
> 
> >> Regards,
> >> --
> >> Stefan Hinz 
> >> iConnect GmbH 
> >> Heesestr. 6, 12169 Berlin (Germany)
> >> Telefon: +49 30 7970948-0 Fax: +49 30 7970948-3
> 
> >> [filter fodder: sql, mysql, query]
> 
> 
> 
> >> -
> >> Do You Yahoo!?
> >> Todo lo que quieres saber de Estados Unidos,
> América Latina y el resto del Mundo.
> >> Visíta Yahoo! Noticias.
> 
> 
> > Regards,
> > --
> > Stefan Hinz 
> > iConnect GmbH 
> > Heesestr. 6, 12169 Berlin (Germany)
> > Telefon: +49 30 7970948-

RE: optimize entire db

[Dathan Vance Pattishall]

You can use this script that I wrote. Also you can change OPTIMIZE TABLE
with ANAYLIZE TABLE

#!/usr/bin/perl -w
# If you use this give me CREDIT!! :) Dathan Vance Pattishall
#
use strict;
use DBI;
use lib '/site/lib';

my $USER = "";
my $PASSWD = "";

my ($host,$DB,$TABLE) = @ARGV;
die "must specify host" if (!defined $host);

my $dbh = DBI->connect("DBI:mysql:mysql:$host", $USER, $PASSWD); die
"can't connect to $host" if (!defined $dbh); my $dblist; if (!$DB) {
$dblist = $dbh->selectall_arrayref("SHOW DATABASES");
} else {
$dblist = [[$DB]];
}
for my $dr (@$dblist) {
  my ($db) = @$dr;
  next if ($db eq 'mysql' or $db eq 'test' or ($db =~ /\./) or ($db =~
/\-/));
  print "checking $db\n";
  $dbh->do("USE $db");
  my $tablist;
  if ($TABLE) {
$tablist = $dbh->selectall_arrayref("SHOW TABLES LIKE '$TABLE\%'");
  } else {
$tablist = $dbh->selectall_arrayref("SHOW TABLES");
  }
  for my $tr (@$tablist) {
my ($table) = @$tr;
print "...$table...\n";
my $rr = $dbh->selectall_arrayref("OPTIMIZE TABLE $table");
print "OK\n";
}
}

$dbh->disconnect();



-->-Original Message-
-->From: Mark Stringham [mailto:[EMAIL PROTECTED]
-->Sent: Tuesday, June 03, 2003 12:40 PM
-->To: [EMAIL PROTECTED]
-->Subject: optimize entire db
-->
-->Hi -
-->
-->Can anyone give the command line syntax for optimizing the entire db?
-->thanks in advance.
-->
-->MS



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

strange SSH/libmysql.dll problem - this time with dbug infos

[Tobias Schittkowski]

I think I need a MySQL code guru...

I am using the 4.0.12 client library on a Win2k host to
connect to a 4.0.13 linux server.

If I connect directly to the server using mysql_real_connect, everything
runs fine.

However, I want to use SSH tunneling.

So I set up a SSHv2 tunneling connection from localhost:3306 to server:3306.

If I use the command line, i.e. mysql -u foo -pbar, I can connect to the
remote server via SSH. The same is, if I use tools like SQLyog.

I enable the dbug, here is the output. The program hangs at the last line...

>my_init
| >my_win_init
| libmysql_init
my_malloc
| my: Size: 544  MyFlags: 48
| exit: ptr: 4d144c8
mysql_real_connect
| enter: host: 127.0.0.1  db: rssvertrieb  user: rss
| info: Server name: '127.0.0.1'.  Named Pipe: MySQL
| error: host: '127.0.0.1'  socket: ''  named_pipe: 0  have_tcpip: 1
| info: Server name: '127.0.0.1'.  TCP sock: 3306
| >vio_new
| | enter: sd=1140
| | >my_malloc
| | | my: Size: 84  MyFlags: 16
| | | exit: ptr: 4d124b0
| | vio_reset
| | | enter: type=1  sd=1140  localhost=0
| | my_net_init
| | >my_malloc
| | | my: Size: 8199  MyFlags: 16
| | | exit: ptr: 4d14720
| | vio_fastsend
| | | exit: 0
| | vio_keepalive
| | enter: sd=1140, set_keep_alive=1
| vio_is_blocking
| | exit: 1


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

Re: Mysql / PHP image link problem.

[Becoming Digital]

FWIW, there is a PHP-DB mailing list that might prove more helpful to you.  I
know certain folk on both lists can get mighty annoyed when an off-topic
question is posted, so I thought I'd give you a heads-up.
[EMAIL PROTECTED]

Edward Dudlik
Becoming Digital
www.becomingdigital.com


- Original Message -
From: "Daniel Crompton" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, 03 June, 2003 14:20
Subject: Mysql / PHP image link problem.


I have a mysql table column called 'imagecolumn'  The rows contain links i.e.

image.gif
image.gif
null
image.gif
image.gif

To display in my web page im using:



This displays the above images in my html page

The problem i have is where the field in my table is left blank (null), a web
browser shows this as a broken image link icon.  i.e. it doesn't ignore it.

Does anyone know how I can get my page to ignore it if there is no link.







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

Re: debuggine 1205 / LOCK wait timeout exceeded errors

[Heikki Tuuri]

Roman,

you can use SHOW INNODB STATUS to look what active transactions you have and
how many lock structs they have.

Also innodb_lock_monitor helps.

Best regards,

Heikki Tuuri
Innobase Oy
http://www.innodb.com
Transactions, foreign keys, and a hot backup tool for MySQL
Order MySQL technical support from https://order.mysql.com/


- Original Message - 
From: "Roman Neuhauser" <[EMAIL PROTECTED]>
Newsgroups: mailing.database.mysql
Sent: Tuesday, June 03, 2003 7:51 PM
Subject: debuggine 1205 / LOCK wait timeout exceeded errors


> Hi there,
>
> I have a problem with LOCKs. I use InnoDB tables and transactions.
>
> [EMAIL PROTECTED] ~ 1001:0 > /usr/local/libexec/mysqld --version
> /usr/local/libexec/mysqld  Ver 4.0.13 for portbld-freebsd4.8 on i386
> [EMAIL PROTECTED] ~ 1002:0 > uname -sr
> FreeBSD 4.8-RC
> [EMAIL PROTECTED] ~ 1003:0 > httpd -v
> Server version: Apache/1.3.26 (Unix)
> Server built:   Jul 23 2002 14:12:25
>
> mod_php-4.3.1 linked with libmysqlclient 4.0.11a-gamma
>
> This is one such case (one PHP script run, taken from the query log):
>
> 030603 18:18:47  19 Init DB webed
>  19 Init DB webed
>  19 Query   SELECT COUNT(*)
>   FROM editor
>   WHERE id = 1
>  19 Init DB webed
>  19 Query   SELECT e.login, e.authorized,
>  e.firstname, e.lastname,
>  e.validfrom, e.validtill,
>  e.createdby, e.createdon,
>  e.changedby, e.changedon,
>  e.caps, e.wysiwyg
>   FROM editor e
>   WHERE id = 1
> 030603 18:18:48  19 Init DB webed
>  19 Query   SELECT COUNT(*)
>   FROM server
>   WHERE id = 4
>  19 Init DB webed
>  19 Query   SELECT COUNT(*)
>   FROM editor
>   WHERE id = 1
>  19 Init DB webed
>  19 Query   SELECT g.id AS gid, g.name
>   FROM acl_group g, acl_member m
>   WHERE g.id = m.gid
>   AND uid = 1
>  19 Init DB webed
>  19 Query   SELECT
>   MAX(access) AS max,
>   MIN(access) AS min
>   FROM acl_access a
>   WHERE a.objtype = 128
>   AND a.objid = 4
>   AND a.gid IN (1)
>   GROUP BY objid
>  19 Init DB webed
>  19 Query   DELETE FROM acl_access
>   WHERE objtype = 128
>   AND objid = 4
>
> Here the server emitted 1205.
>
> DELETE FROM acl_access WHERE objtype = 128 AND objid = 4
> [nativecode = 1205 * * LOCK wait timeout exceeded; Try restarting
TRANSACTION ]
>
> I don't see what should cause it to behave so. I'm the only user ATM,
> IOW the listing above is unaltered in any way, and represents exactly
> one run of the PHP script. Notice the lack of any transaction-related or
> LOCK commands (in most cases, however, the DML command is preceeded by a
> BEGIN, and immediatelly [that is, after the timeout] followed by a
> ROLLBACK).
>
> I'd love to see this resolved, and will happily provide any info
> requested.
>
> TIA && HAND
>
> -- 
> FreeBSD 4.8-RC
> 6:23PM up 14 days, 2:54, 16 users, load averages: 0.04, 0.04, 0.00
>
> -- 
> 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 Secure connections

[Paul Cunningham]

Hi,

Please can someone help with advise on setting up a Secure MySQL Connection. The 
details are as follows.

I have built and configured OpenSSL 0.9.7 and MySQL 4.0.12.
MySQL was configured with the options 
./configure \
--enable-local-infile \
--with-vio --with-openssl=/usr/sfw \
--enable-thread-safe-client --with-pthread \
--sysconfdir=/etc/sfw/mysql \
--prefix=/usr/sfw/mysql
I have set up the certificates as described in 
http://www.mysql.com/doc/en/Secure_Create_Certs.html

and ran the mysql daemon as follows .

CONFF=/export/openssl/my.cnf
/usr/sfw/mysql/bin/mysqld_safe --defaults-file=${CONFF} --user=mysql &
the contents of /export/openssl/my.cnf being ...

[client]
ssl-ca=/export/openssl/cacert.pem
ssl-cert=/export/openssl/client-cert.pem
ssl-key=/export/openssl/client-key.pem
[mysqld]
master-ssl-ca=/export/openssl/cacert.pem
master-ssl-cert=/export/openssl/server-cert.pem
master-ssl-key=/export/openssl/server-key.pem
mysqld started okay after changing the [mysqld] entries and added the 'master-' 
bits.

I then tried to run the 'mysql' client as follows ...

  /usr/sfw/mysql/bin/mysql --defaults-file=/export/openssl/my.cnf

but this fails with the following 

  /usr/sfw/mysql/bin/mysql: ERROR: unknown variable
'ssl-ca=/export/openssl/cacert.pem'
The clients does not seem to like these options either in the my.cnf file or if 
put on the mysql command-line.

Has anyone got any ideas where I am going wrong?

Thanks
Paul


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

MySQL stops working, process cannot be killed

[Steven]

I have been having a problem with MySQL lately. Specs are:

RedHat Linux 7.3
MySQL  3.23.56, for pc-linux (i686) (same behavior with 4.0.12 as well)
Apache 1.3.27
PHP 4.3.2

(most (99%) of the MySQL connections are made through PHP scripts
running as an apache module)

The problem happens as such: 

1) MySQL will be running fine for a day or so, then any connections
attempted to MySQL will start to 'hang' - no error messages or
connection refused just sitting there trying to connect.

2) After around 10 minutes MySQL will start to return an error message
thatthere are too many connections and the connection has been refused.

3) A look at 'mysqladmin processlist' shows what appears to be normal
processes doing normal queries or trying to open tables. The last time
this happened there were 60 processes all attempting to open different
tables. There were no queries that were using any table that would have
kept the other processes sitting. Nothing is logged to the slow query
log which is set to log any queries running longer than a second.

At this point, a look in 'top' will show a single MySQL process running
at 99.9% of CPU. Any attempts to restart the MySQL server will result in
an error saying a mysqld server is already running on 3306.

There are no other errors listed in the error file for before or during
the crash/incident. At this point, a 'kill -9 pid' on the pid of the
MySQL process in top has no effect and does not kill the process. Other
signals also have no effect.

After killing the parents of the process, MySQL will restart; however,
the process in top stays there at 99.9% CPU utilization until the server
is rebooted.

This happens about once or twice per day, with seemingly no
predictability. If anyone has any steps I can take to trouble shoot this
or I would be grateful.

-Steven



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

optimize entire db

[Mark Stringham]

Hi -

Can anyone give the command line syntax for optimizing the entire db? 
thanks in advance.

MS

Re: Problem with priv_db

[Elian Kool]

Hello,

Egor Egorov wrote:

I even flushed the privileges and restarted the MySQL server.

Any idea?
   

Please, show me the structure of table 'user'.

I found it.
Create_tmp_table_priv was Y.
thanks anyway,

elian

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

RE: Mysql / PHP image link problem.

[Jonathan Villa]

You'll have to check if the result is NULL or not.  Try this

" : ""; ?>

This is print out nothing if your result is equal to null.  

On another notes, how do you specify the width and height of this image?
Or the alt tag, or set the  border so that on older versions on Netscape
it does not default to 1.

Why don't you try saving the entire image tag in the db, for example



and then you can use the same code from above

 
---> Jonathan
 
 
 

-Original Message-
From: Daniel Crompton [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, June 03, 2003 1:20 PM
To: [EMAIL PROTECTED]
Subject: Mysql / PHP image link problem.

I have a mysql table column called 'imagecolumn'  The rows contain links
i.e.

image.gif
image.gif
null
image.gif
image.gif

To display in my web page im using:

 

This displays the above images in my html page

The problem i have is where the field in my table is left blank (null),
a web browser shows this as a broken image link icon.  i.e. it doesn't
ignore it.

Does anyone know how I can get my page to ignore it if there is no link.






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

Re[4]: MySql data between Linux and Windows

[Stefan Hinz]

Carlos,

> Character set
> In windows 2000 latin1
> In Linux   hosting  latin1
 
> Character sets
> In windows 2000 
> latin1 big5 czech euc_kr gb2312 gbk sjis tis620 ujis dec8 dos german1 hp8 koi8_ru 
> latin2 swe7 usa7 cp1251 danish hebrew win1251 estonia hungarian koi8_ukr win1251ukr 
> greek win1250 croat cp1257
> latin5 
 
> In Linux
> latin1 dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7 cp1251 danish hebrew win1251 
> estonia hungarian koi8_ukr win1251ukr greek win1250 croat cp1257 latin5

> phpMyAdmin version
> Windows 2.5.0
> Linux  2.4.0
 
> MySql versions
> Windows MySQL 3.23.52-nt 
> Linux  MySQL 3.23.56-log 

I'm giving up, sorry. I cannot see anything in the above that would
cause problems with Spanish characters when exchanging the data
between those two servers.

Does anyone else on this list see more than I do?

> Stefan Hinz <[EMAIL PROTECTED]> wrote:
> Carlos,

>> The character sets in both mysql severs in linux and windows are the same.
>> When I query a table which has the same data in spanish with accents I can see the 
>> characters written are different.

> 1) So what are the values of the character_set and character_sets
> variables, anyway?

> 2) Which MySQL server versions do you use, for the local and the ISP
> server as well?

> 3) Which version of phpMyAdmin do you use? Do they differ on those two
> machines?

>> My goal is to extract data from one of the data bases and be able to get then to 
>> the other database between linux ands windows without any problem.
>> Apache is used as web server in both Linux and windows.
>> Regards.
>> Carlos Díaz


>> Stefan Hinz 
> wrote:
>> Carlos,

>>> We have a hosting provider with a Linux box and an MySql database.
>>> We have a local Mysql database.
>>> In both machines I use phpMyAdmin.

>> On the startpage (right frame) of phpMyAdmin you should see a link
>> called "Show MySQL system variables". Look what the values of the
>> variables character_set and character_sets are.

>>> When I export from Windows to Linux then all the data in Linux is messed up with 
>>> respect to special characters, we work in spanish and all accents are lost and 
>>> converted to some extrange
>>> character.
>>> When I export from Linux to Windows the same thing happens.
>>> Of course these has to do with character sets.

>> Most probably the two MySQL servers you are using have different
>> character sets.

>>> ¿Any suggestion?

>> You can find more detailed information here:

>> http://www.mysql.com/doc/en/Character_sets.html

>> And here:

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

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

>> [filter fodder: sql, mysql, query]



>> -
>> Do You Yahoo!?
>> Todo lo que quieres saber de Estados Unidos, América Latina y el resto del Mundo.
>> Visíta Yahoo! Noticias.


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

> [filter fodder: sql, mysql, query]



> -
> Do You Yahoo!?
> Todo lo que quieres saber de Estados Unidos, América Latina y el resto del Mundo.
> Visíta Yahoo! Noticias.


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: Mysql / PHP image link problem.

[Roman Neuhauser]

keep the lines at or under 72 characters, please.

# [EMAIL PROTECTED] / 2003-06-03 19:20:17 +0100:
> To display in my web page im using:
> 
>  
> 
> This displays the above images in my html page
> 
> The problem i have is where the field in my table is left blank
> (null), a web browser shows this as a broken image link icon.  i.e. it
> doesn't ignore it.
> 
> Does anyone know how I can get my page to ignore it if there is no link.

like: don't output the tag if the image is null?

-- 
If you cc me or remove the list(s) completely I'll most likely ignore
your message.see http://www.eyrie.org./~eagle/faqs/questions.html

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

RE: help creating foreign keys

[Kevin Fries]

In InnoDB you *must* put an index on foreign keys.  It doesn't do this
for you automatically.

You can do it in one statement:

create table bar (
   bar_idint unsigned auto_increment,
   foo_idint unsigned,
   bar_value int,
   constraint bar_pk primary key (bar_id),
   index (foo_id),
   foreign key (foo_id) references foo (foo_id)
 ) type=innodb;

-Original Message-
From: Jeff Mathis [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, June 03, 2003 10:09 AM
To: Justin Scheiber
Cc: [EMAIL PROTECTED]
Subject: Re: help creating foreign keys


the syntax for foreign key creation is different than what you have. You
need an alter table statement.

create table bar (
   bar_idint unsigned auto_increment primary key,
   foo_idint unsigned,
   bar_value int,
 ) type=innodb; 
alter table bar add constraint foreign key (foo_id) references
foo(foo_id) on delete cascade;

Justin Scheiber wrote:
> 
> Hello, I want to create the following tables - where a foriegn key 
> references an auto_incremented primary key of another table.  In my 
> simple logic, it seem like such a thing should be possible -- after 
> all, i just need the value of the referenced primary key.  I know you 
> can't have 2 auto_increment columns in a table,  and I have read up on
the
> errno: 150 but it still seems like this should be possible.Do I
need
> to rethink the table structure?  Or do I just not understand something

> here?
> 
> create table foo (
>   foo_idint unsigned auto_increment,
>   foo_value int,
>   primary key(foo_id)
> ) type=innodb;
> 
> create table bar (
>   bar_idint unsigned auto_increment,
>   foo_idint unsigned,
>   bar_value int,
>   primary key (bar_id),
>   foreign key(foo_id) references foo(foo_id),
> ) type=innodb;
> 
> ERROR 1005: Can't create table './test/bar.frm' (errno: 150)
> 
> -justin
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]

-- 
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: Mysql / PHP image link problem.

[Paul DuBois]

At 19:20 +0100 6/3/03, Daniel Crompton wrote:
I have a mysql table column called 'imagecolumn'  The rows contain links i.e.

image.gif
image.gif
null
image.gif
image.gif
To display in my web page im using:



This displays the above images in my html page

The problem i have is where the field in my table is left blank 
(null), a web browser shows this as a broken image link icon.  i.e. 
it doesn't ignore it.

Does anyone know how I can get my page to ignore it if there is no link.
Add WHERE imagecolumn IS NOT NULL to your SELECT statement.
Then you won't select the row at all.
Or did you mean something else?

--
Paul DuBois
http://www.kitebird.com/
sql, query
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Red Hat linux database to Mac OS X

[Paul DuBois]

Hello,

I was wondering if anyone has experienced moving a MySQL database from
Linux/Intel X86 to Mac OS X?
I plan to use mysqldump of course.  I was only wondering if anyone has
run into problems they might share.
You can't use BDB tables.

Thanks.

Greg


--
Paul DuBois
http://www.kitebird.com/
sql, query
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Red Hat linux database to Mac OS X

[Christensen, Dave]

Not that particular move, but have used mysqldump/mysql combo to move
between linux-->windows and back.  Works well.

-Original Message-
From: Greg Grasmehr [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, June 03, 2003 1:19 PM
To: [EMAIL PROTECTED]
Subject: Red Hat linux database to Mac OS X


Hello,

I was wondering if anyone has experienced moving a MySQL database from
Linux/Intel X86 to Mac OS X?

I plan to use mysqldump of course.  I was only wondering if anyone has run
into problems they might share.

Thanks.

Greg

__
Do you Yahoo!?
Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
http://calendar.yahoo.com

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

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

RE: full text searching question

[Andy Eastham]

Chris,

I should have added that the explanation is that the full text query does
not automatically sort on the score when boolean mode is selected.

Andy

> -Original Message-
> From: Chris Wilkinson [mailto:[EMAIL PROTECTED]
> Sent: 03 June 2003 13:12
> To: [EMAIL PROTECTED]
> Subject: full text searching question
>
>
> can anybody explain this to me please!  I search am searching
> through a database
> with first name and last names.  BTW I use mysql 4.0.13 on RedHat
> Linux 9.0 in
> case that matters.  I created a fulltext search on both fields
> together so I can
> search them like this:
>
> mysql> SELECT first_name,last_name FROM names_table WHERE
> MATCH(first_name,last_name) AGAINST ('+wilkinson* chris*' IN
> BOOLEAN MODE);
>
> The problem is that for some reason the record with
> first_name=fran is displayed
> first even though the score is lower than the record where
> first_name=christopher
>
> mysql> SELECT first_name,last_name,MATCH(first_name,last_name) AGAINST
> ('+wilkinson* chris*' IN BOOLEAN MODE) AS score FROM names_table WHERE
> MATCH(first_name,last_name) AGAINST ('+wilkinson* chris*' IN
> BOOLEAN MODE);
> +-+---+-+
> | first_name  | last_name | score   |
> +-+---+-+
> | Fran| Wilkinson |   1 |
> | Christopher | Wilkinson | 1.333730698 |
> +-+---+-+
> 2 rows in set (0.01 sec)
>
> Can somebody please explain why this is and how I can get
> Christopher to show up
> before Fran!  Thanks!
>
> --
> Chris Wilkinson
> [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: full text searching question

[Andy Eastham]

Chris,

You're nearly there - the way to do it is:

SELECT first_name,last_name,MATCH(first_name,last_name) AGAINST
('+wilkinson* chris*' IN BOOLEAN MODE) AS score FROM names_table WHERE
MATCH(first_name,last_name) AGAINST ('+wilkinson* chris*' IN
BOOLEAN MODE) ORDER BY score DESC;

The db engine won't execute the full text query more than once so this is
not inefficient.

Andy

> -Original Message-
> From: Chris Wilkinson [mailto:[EMAIL PROTECTED]
> Sent: 03 June 2003 13:12
> To: [EMAIL PROTECTED]
> Subject: full text searching question
>
>
> can anybody explain this to me please!  I search am searching
> through a database
> with first name and last names.  BTW I use mysql 4.0.13 on RedHat
> Linux 9.0 in
> case that matters.  I created a fulltext search on both fields
> together so I can
> search them like this:
>
> mysql> SELECT first_name,last_name FROM names_table WHERE
> MATCH(first_name,last_name) AGAINST ('+wilkinson* chris*' IN
> BOOLEAN MODE);
>
> The problem is that for some reason the record with
> first_name=fran is displayed
> first even though the score is lower than the record where
> first_name=christopher
>
> mysql> SELECT first_name,last_name,MATCH(first_name,last_name) AGAINST
> ('+wilkinson* chris*' IN BOOLEAN MODE) AS score FROM names_table WHERE
> MATCH(first_name,last_name) AGAINST ('+wilkinson* chris*' IN
> BOOLEAN MODE);
> +-+---+-+
> | first_name  | last_name | score   |
> +-+---+-+
> | Fran| Wilkinson |   1 |
> | Christopher | Wilkinson | 1.333730698 |
> +-+---+-+
> 2 rows in set (0.01 sec)
>
> Can somebody please explain why this is and how I can get
> Christopher to show up
> before Fran!  Thanks!
>
> --
> Chris Wilkinson
> [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: full text searching question

[Paul DuBois]

At 13:11 +0100 6/3/03, Chris Wilkinson wrote:
can anybody explain this to me please!  I search am searching 
through a database
with first name and last names.  BTW I use mysql 4.0.13 on RedHat Linux 9.0 in
case that matters.  I created a fulltext search on both fields 
together so I can
search them like this:

mysql> SELECT first_name,last_name FROM names_table WHERE
MATCH(first_name,last_name) AGAINST ('+wilkinson* chris*' IN BOOLEAN MODE);
The problem is that for some reason the record with first_name=fran 
is displayed
first even though the score is lower than the record where 
first_name=christopher

mysql> SELECT first_name,last_name,MATCH(first_name,last_name) AGAINST
('+wilkinson* chris*' IN BOOLEAN MODE) AS score FROM names_table WHERE
MATCH(first_name,last_name) AGAINST ('+wilkinson* chris*' IN BOOLEAN MODE);
+-+---+-+
| first_name  | last_name | score   |
+-+---+-+
| Fran| Wilkinson |   1 |
| Christopher | Wilkinson | 1.333730698 |
+-+---+-+
2 rows in set (0.01 sec)
Can somebody please explain why this is and how I can get 
Christopher to show up
before Fran!  Thanks!
Add ORDER BY score DESC to the query.

--
Chris Wilkinson
[EMAIL PROTECTED]


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

Mysql / PHP image link problem.

[Daniel Crompton]

I have a mysql table column called 'imagecolumn'  The rows contain links i.e.

image.gif
image.gif
null
image.gif
image.gif

To display in my web page im using:

 

This displays the above images in my html page

The problem i have is where the field in my table is left blank (null), a web browser 
shows this as a broken image link icon.  i.e. it doesn't ignore it.

Does anyone know how I can get my page to ignore it if there is no link.

Red Hat linux database to Mac OS X

[Greg Grasmehr]

Hello,

I was wondering if anyone has experienced moving a MySQL database from
Linux/Intel X86 to Mac OS X?

I plan to use mysqldump of course.  I was only wondering if anyone has
run into problems they might share.

Thanks.

Greg

__
Do you Yahoo!?
Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
http://calendar.yahoo.com

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

Re: full text searching question

[kjc]

User ORDER BY First Name

Chris Wilkinson wrote:

can anybody explain this to me please!  I search am searching through a database
with first name and last names.  BTW I use mysql 4.0.13 on RedHat Linux 9.0 in
case that matters.  I created a fulltext search on both fields together so I can
search them like this:
mysql> SELECT first_name,last_name FROM names_table WHERE
MATCH(first_name,last_name) AGAINST ('+wilkinson* chris*' IN BOOLEAN MODE);
The problem is that for some reason the record with first_name=fran is displayed
first even though the score is lower than the record where first_name=christopher
mysql> SELECT first_name,last_name,MATCH(first_name,last_name) AGAINST
('+wilkinson* chris*' IN BOOLEAN MODE) AS score FROM names_table WHERE
MATCH(first_name,last_name) AGAINST ('+wilkinson* chris*' IN BOOLEAN MODE);
+-+---+-+
| first_name  | last_name | score   |
+-+---+-+
| Fran| Wilkinson |   1 |
| Christopher | Wilkinson | 1.333730698 |
+-+---+-+
2 rows in set (0.01 sec)
Can somebody please explain why this is and how I can get Christopher to show up
before Fran!  Thanks!
 

--

Kevin J Citron
Sr. Object Imagineer
Optimized Objects, Inc.
EL Paso, Texas 79930
(915) 565-5777/566-2403


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

RE: database copy

[Christensen, Dave]

If you check the documentation about mysqldump, you'll find the command line
syntax for using a combination of mysqldump on the source machine and mysql
pumping  to the target machine.  It's simple, fast and everything is there
to work just fine.

-Original Message-
From: Paul Najman [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, June 03, 2003 11:33 AM
To: Fabio Bernardo
Cc: [EMAIL PROTECTED]
Subject: Re: database copy


Fabio,

I have had to do this quite a few times myself.
If you have use of a server with MySQL on it then it can be done using a GUI
(I use MySQL-Front, but most people seem to MySQL Control Center. All you do
is use the built-in tools to transfer table structures + data to a database
(any) on the external server and then transfer it from there to the other
PC.

If this doesn't make much sense to you, let me know!
Best wishes,
Paul Najman

- Original Message - 
From: "Fabio Bernardo" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Tuesday, June 03, 2003 4:12 PM
Subject: database copy


Hi there, I ´m using the Mysql control center with  a database called dbONE
in a PC, and I would like to copy it to another PC, Do you have any idea to
how can I do it? I dont wanna create all the tables again... Thanks a lot

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

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

Re: database copy

[Karam Chand]

Hello

I use SQLyog at http://www.webyog.com/sqlyog. It has a
great facility to do exactly what you want. Just
connect to two differt servers and choose DB->Copy
Database To Another Host/DB option...and wholla you
are done.

Karam

--- Paul Najman <[EMAIL PROTECTED]> wrote:
> Fabio,
> 
> I have had to do this quite a few times myself.
> If you have use of a server with MySQL on it then it
> can be done using a GUI
> (I use MySQL-Front, but most people seem to MySQL
> Control Center.
> All you do is use the built-in tools to transfer
> table structures + data to
> a database (any) on the external server and then
> transfer it from there to
> the other PC.
> 
> If this doesn't make much sense to you, let me know!
> Best wishes,
> Paul Najman
> 
> - Original Message - 
> From: "Fabio Bernardo"
> <[EMAIL PROTECTED]>
> To: <[EMAIL PROTECTED]>;
> <[EMAIL PROTECTED]>
> Sent: Tuesday, June 03, 2003 4:12 PM
> Subject: database copy
> 
> 
> Hi there, I ´m using the Mysql control center with 
> a database called dbONE
> in a PC, and I would like to copy it to another PC,
> Do you have any idea to
> how can I do it? I dont wanna create all the tables
> again...
> Thanks a lot
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:   
>
http://lists.mysql.com/[EMAIL PROTECTED]
> 


__
Do you Yahoo!?
Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
http://calendar.yahoo.com

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

full text searching question

[Chris Wilkinson]

can anybody explain this to me please!  I search am searching through a database
with first name and last names.  BTW I use mysql 4.0.13 on RedHat Linux 9.0 in
case that matters.  I created a fulltext search on both fields together so I can
search them like this:

mysql> SELECT first_name,last_name FROM names_table WHERE
MATCH(first_name,last_name) AGAINST ('+wilkinson* chris*' IN BOOLEAN MODE);

The problem is that for some reason the record with first_name=fran is displayed
first even though the score is lower than the record where first_name=christopher

mysql> SELECT first_name,last_name,MATCH(first_name,last_name) AGAINST
('+wilkinson* chris*' IN BOOLEAN MODE) AS score FROM names_table WHERE
MATCH(first_name,last_name) AGAINST ('+wilkinson* chris*' IN BOOLEAN MODE);
+-+---+-+
| first_name  | last_name | score   |
+-+---+-+
| Fran| Wilkinson |   1 |
| Christopher | Wilkinson | 1.333730698 |
+-+---+-+
2 rows in set (0.01 sec)

Can somebody please explain why this is and how I can get Christopher to show up
before Fran!  Thanks!

-- 
Chris Wilkinson
[EMAIL PROTECTED]

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

RE: mysql query output get wrapped

[Christensen, Dave]

Since this in running in Windows, I don't think you will have much luck  in
a simple console display.  While you can change size properties for Window
and Screen Buffer, these sizes are ONLY for length and you can't really do
much for horizontal size.

What I'd suggest tying is to run mysql as a command line batch job with the
results being redirected into a .txt file that you could open with an editor
that will allow wider lines.  Something like:


C:\>mysql -uuserid -pyourpassword < yourcommandtext.sql >
yourcommandoutput.txt 



Without using a tool like EMS MySQL Manager, etc, I think this might be your
best bet.

-Original Message-
From: Keith Stevenson [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, June 03, 2003 11:14 AM
To: 'Asif Iqbal'; Paul DuBois
Cc: [EMAIL PROTECTED]
Subject: RE: mysql query output get wrapped


Asif,
If I understand you correctly, you should just be able to increase
the size of the DOS window (Of course this would have to be on Windwos).

If so simply right click the Title Bar and Choose Properties, then on the
Layout Tab increase the Window Size and Screen Buffer size 

Hope this Helps

Kind Regards


-Original Message-
From: Asif Iqbal [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, June 03, 2003 4:57 PM
To: Paul DuBois
Cc: [EMAIL PROTECTED]
Subject: Re: mysql query output get wrapped



something like this

++-+---++++---+-
--+-+
| id | EffectiveId | Queue | Type   | IssueStatement | Resolution | Owner
| Subject   | InitialPriority |
++-+---++++---+-
--+-+
|  5 |   5 | 4 | ticket |   NULL |   NULL |16
| RE: phonebook |  10 |
++-+---++++---+-
--+-+

*unwrapped*


On Tue, 3 Jun 2003, Paul DuBois wrote:

> At 11:39 -0400 6/3/03, Asif Iqbal wrote:
> >I want the columns unwrapped to make it easy to read. Sorry if I
> >confused you earlier
>
> I still don't know what you mean.  What would this output *look like*?
> Please show an example.
>
> >
> >
> >On Tue, 3 Jun 2003, Paul DuBois wrote:
> >
> >>  At 11:27 -0400 6/3/03, Asif Iqbal wrote:
> >>  >mysql> select * from Tickets limit 1;
> >>
>++-+---++++---+
---+-+---+--+--+
+--+-++-+---
+--+-++-+
--+--+---+-+-+--
--+--+---+-+-+
---+--+
> >>  >| id | EffectiveId | Queue | Type   | IssueStatement | Resolution |
Owner
> >>  >| Subject   | InitialPriority | FinalPriority | Priority | Status
|
> >>  >TimeWorked | TimeLeft | Told| Starts | Started | Due
> >>  >| Resolved | LastUpdatedBy | LastUpdated | Creator | Created
> >>  >| Disabled |
> >>
>++-+---++++---+
---+-+---+--+--+
+--+-++-+---
+--+-++-+
--+--+---+-+-+--
--+--+---+-+-+
---+--+
> >>  >|  5 |   5 | 4 | ticket |   NULL |   NULL |
16
> >>  >| RE: phonebook |  10 |80 |   10 |
resolved |
> >>  >0 | NULL | 1970-01-01 00:00:00 | NULL   | NULL| 1970-01-01
> >>  >00:00:00 | NULL | 1 | 2001-04-17 18:38:02 |   1 |
> >>  >2001-04-17 18:26:46 |0 |
> >>  
> >>
>++-+---++++---+
---+-+---+--+--+
+--+-++-+---
+--+-++-+
--+--+---+-+-+--
--+--+---+-+-+
---+--+
> >>  >1 row in set (0.00 sec)
> >>  >
> >>  >Is there a way I can get the output unwrapped
> >>  >
> >>  >something like this
> >>  >
> >>  >mysql> select * from Tickets limit 1 \G
> >>  >*** 1. row ***
> >>  >  id: 5
> >>  > EffectiveId: 5
> >>  >   Queue: 4
> >>  >Type: ticket
> >>  >  IssueStatement: NULL
> >>  >  Resolution: NULL
> >>  >   Owner: 16
> >>  > Subject: RE: phonebook
> >>  >InitialPriority: 10
> >>  >   FinalPriority: 80

RE: table copying/replication

[Dathan Vance Pattishall]

-->-Original Message-
-->From: Ross Simpson [mailto:[EMAIL PROTECTED]
-->Sent: Tuesday, June 03, 2003 10:31 AM
-->To: [EMAIL PROTECTED]
-->Subject: table copying/replication
-->
-->Hello,
-->
-->I have a need for fast copying of a specific table from a master
mysql
-->server to a number of slave servers (say 5).
Create the table on the master and if the master and slave config is
working then the same table will be on the slave.


-->
-->The database in question could potentially have up to 2000 tables,
and
-->at any time, one of those tables would need to be copied to all 5 of
the
-->slaves, upon command of the master.
Make sure you have enough inodes that's 6000 files that will be opened.
Also set your ulimit high enough to open all the files.

Replication will perform the same action on the slaves as initiated by
the master. There is no need for a copy.

-->I also looked at doing table copies (insert into .. select * from
..),
-->but these seem pretty slow.
It's building the index on the fly as well, if there are indexes on the
dst table. It does have to scan the src table and for every row insert
it into the dst table. You can tweak you're my.cnf values to make that
operation happen faster.

-->
-->Has anyone solved this problem before?  Any ideas that would help
out?
-->

Yes, the mysql team with replication.

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




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

Making MySQL 5.0 Source Tree

[Primaria Falticeni]

Hello,


I gave the follow commands to make the binary form in cygwin:

autoconf configure.in
./configure
and the messages were:
loading cache ...
bla bla bla
checking build system type... i686-pc-cygwin
configure: 724: Syntax error: word unexpected (expecting ")")

Maybe the cause is the linefeeds conversion from UNIX to DOS.

I need help to be able to compile MySQL 5.0 source tree.

Sincerely Yours,
Iulian
Information System Officer/Economist
Falticeni, jud. Suceava
Romania, Europe

Re: mysql query output get wrapped

[Chris Tucker]

Are you using the -S switch to less?  Note that it's a capital S, not lower 
case.  Does the output get properly sent through the less pager (i.e do you have 
to page through the results that get selected)?  Check the manpage for less on 
your machine and find out what the truncate lines option is, if it is different 
(highly unlikely).  Or try a different pager.  Make sure you execute your SQL 
command using \g or ;.  Make sure you don't have anything funky set in your 
client section of your my.cnf that may mess with pagers/output.  See whether 
using tee (\T ) tees the output into the specified output file without 
wrapping.

Chris

Asif Iqbal wrote:
I am on solaris 2.8 sparc. You less works fine if I use \G switch. But I
want (as you explained) list of column names in one line in first row and
the results are thereafter in single rows instead of getting wrapped.
Like I said wish I remebered the swithc for that

On Tue, 3 Jun 2003, Chris Tucker wrote:


What platform are you on?  If you're on windows you probably won't have less
installed, in which case you'll need to either (a) install it or (b) use a
different pager (not sure what you'll have with windows, you'd have to
independently research it).  I'm assuming that what you want is to have the list
of column names as your first row, then each row thereafter on a single line in
your display window (i.e. no wrapping of those lines on display).  If you need
to get column names into the output for each cell, then you'll need to do some
post-processing of your own.
Chris

Asif Iqbal wrote:

That works with \G switch , but without the \G switch Istill get it
wrapped
wish I can remeber the switch

On Tue, 3 Jun 2003, Chris Tucker wrote:



The pager option can be used to accomplish this (if you don't mind having things
running through, e.g., less).  When I need to do this I just do:
mysql> \P less -S
mysql> 
You'll get unwrapped output (the -S option to less tells it to truncate rather
than wrap over-long lines).
You can use anything you like as your pager (as far as I can tell): all it has
to do is take some input on the input stream and write its output out to the
output stream.  It is up to the pager how it does this: in the example of less
it will paginate and not wrap.  Other pagers can be used to achieve different
results, processing the stream however you like, based on your needs.
Cheers,
Chris
Asif Iqbal wrote:


mysql> select * from Tickets limit 1;
++-+---++++---+---+-+---+--+--++--+-++-+-+--+---+-+-+-+--+
| id | EffectiveId | Queue | Type   | IssueStatement | Resolution | Owner
| Subject   | InitialPriority | FinalPriority | Priority | Status   |
TimeWorked | TimeLeft | Told| Starts | Started | Due
| Resolved | LastUpdatedBy | LastUpdated | Creator | Created
| Disabled |
++-+---++++---+---+-+---+--+--++--+-++-+-+--+---+-+-+-+--+
|  5 |   5 | 4 | ticket |   NULL |   NULL |16
| RE: phonebook |  10 |80 |   10 | resolved |
0 | NULL | 1970-01-01 00:00:00 | NULL   | NULL| 1970-01-01
00:00:00 | NULL | 1 | 2001-04-17 18:38:02 |   1 |
2001-04-17 18:26:46 |0 |
++-+---++++---+---+-+---+--+--++--+-++-+-+--+---+-+-+-+--+
1 row in set (0.00 sec)
Is there a way I can get the output unwrapped

something like this

mysql> select * from Tickets limit 1 \G
*** 1. row ***
   id: 5
  EffectiveId: 5
Queue: 4
 Type: ticket
IssueStatement: NULL
   Resolution: NULL
Owner: 16
  Subject: RE: phonebook
InitialPriority: 10
FinalPriority: 80
 Priority: 10
   Status: resolved
   TimeWorked: 0
 TimeLeft: NULL
 Told: 1970-01-01 00:00:00
   Starts: NULL
  Started: NULL
  Due: 1970-01-01 00:00:00
 Resolved: NULL
LastUpdatedBy: 1
  LastUpdated: 2001-04-17 18:38:02
  Creator: 1
  Created: 2001-04-17 18:26:46
 Disabled: 0
1 row in set (0.00 sec)
Except I want it Horizontally

Thanks

Asif









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