Re: InnoDB Questions

[Nitin]

yea, he's right, it may be error log file

Nitin

- Original Message - 
From: "Chris Nolan" <[EMAIL PROTECTED]>
To: "Leo Huang" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Wednesday, November 05, 2003 5:53 AM
Subject: Re: InnoDB Questions


> The "last one" you're referring to - could it be the error log?
>
> The log files will only grow to a pre-determined limit. These log files
> are used to ensure that transactions maintain their durability.
>
> With Oracle, you'd want to be careful. Oracle gets very, very picky
> about the stuff underneath it when it's running. If it gets to a
> configuration limit, the results can be very, very depressing. Having to
> extend tablespaces by hand is a very common Oracle DBA task.
>
> Regards,
>
> Chris
>
> Leo Huang wrote:
>
> >Hello Nitin,
> >
> >From the timestamp of the log files, it seems that the first two files
> >works together while the last one seems just sitting there, doesn't do
> >anything.
> >
> >Also, will the log files getting bigger and bigger in the future?? If so
> >how should I deal with them?
> >
> >For your last suggestion, what will Oracle do if I specify a datafile
> >size less than the database size, e.g. I specify 20M in the my.cnf while
> >there are actually 400M of data in my database?
> >
> >Leo
> >
> >Nitin wrote:
> >
> >
> >
> >>Hello,
> >>
> >>first things first, you cann't resize your datafiles without shutting
down
> >>your database. if it's ok with you, have a look at
> >>http://www.mysql.com/doc/en/Adding_and_removing.html
> >>
> >>you may want to have a look at you my.cnf file, stored in mysql data dir
or
> >>in /etc dir, for the default options specified there fo the datafile
with:
> >>
> >>innodb_data_file_path
> >>
> >>Yes, you can add data file, just add another entry to above option.
option
> >>entry is self-explainatory.
> >>
> >>At last, ibdata1, ibdata2 are actual data files used to store actual
> >>data. one or more of these files are attached to one tablespace and one
file
> >>cant span across tablespaces.
> >>
> >>ib_logfile0, ib_logfile1 are log files, which are used to log sql
statements
> >>applied to database. these files are used to restore data in case of any
> >>crash or mishap.
> >>
> >>for further info, have a look at:
> >>http://www.mysql.com/doc/en/InnoDB_start.html
> >>
> >>Enjoy
> >>Nitin
> >>
> >>
> >>- Original Message - 
> >>From: "Leo Huang" <[EMAIL PROTECTED]>
> >>To: <[EMAIL PROTECTED]>
> >>Sent: Tuesday, November 04, 2003 6:28 PM
> >>Subject: InnoDB Questions
> >>
> >>
> >>
> >>
> >>
> >>
> >>>Hello,
> >>>
> >>>I have a few questions about InnoDB.
> >>>I am new to InnoDB, and just converted my MyISAM tables into InnoDB a
> >>>few days ago.
> >>>I notice that it generates these files
> >>>
> >>>-rw-rw1 mysqlmysql2560 Nov  2 13:07
> >>>ib_arch_log_00
> >>>-rw-rw1 mysqlmysql790626304 Nov  4 20:44 ibdata1
> >>>-rw-rw1 mysqlmysql10485760 Nov  4 20:44 ib_logfile0
> >>>-rw-rw1 mysqlmysql10485760 Nov  4 20:44 ib_logfile1
> >>>-rw-rw1 mysqlmysql10485760 Nov  3 00:02 ib_logfile2
> >>>
> >>>But how can those files work together?
> >>>
> >>>In InnoDB documentation, it suggests to add another file ibdata2 to
> >>>get higher performance. Can I do that now, after I have created the
> >>>ibdata1 and used it for a while?
> >>>
> >>>The most important thing is I deleted a 300M database, but the ibdata1
> >>>remains the same size. MyPHPAdmin says 330,000KB free. How can I make
> >>>the data file smaller?
> >>>
> >>>I will be really appreciated if someone can briefly describe what's
> >>>happening to those files or point me to some articles.
> >>>
> >>>Thanks a lot,
> >>>Leo
> >>>
> >>>
> >>>-- 
> >>>MySQL General Mailing List
> >>>For list archives: http://lists.mysql.com/mysql
> >>>To unsubscribe:
> >>>
> >>>
> >>>
> >>>
> >>http://lists.mysql.com/[EMAIL PROTECTED]
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >
> >
> >
> >
> >
>
>
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
>



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

Re: InnoDB Questions

[Nitin]

not all the log files will work together, i mean, these files work in cyclic
fashion, one fills up, second one gets used, it fills up, second one, and
then back to first one. so it wont keep getting bigger, it will overwrite
the previous data when it comes back to that file.

in oracle, if you have data more than the size you're specifying, it'll
through error and wont do nothing.

Nitin


- Original Message - 
From: "Leo Huang" <[EMAIL PROTECTED]>
To: "Nitin" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Wednesday, November 05, 2003 5:12 AM
Subject: Re: InnoDB Questions


> Hello Nitin,
>
> >From the timestamp of the log files, it seems that the first two files
> works together while the last one seems just sitting there, doesn't do
> anything.
>
> Also, will the log files getting bigger and bigger in the future?? If so
> how should I deal with them?
>
> For your last suggestion, what will Oracle do if I specify a datafile
> size less than the database size, e.g. I specify 20M in the my.cnf while
> there are actually 400M of data in my database?
>
> Leo
>
> Nitin wrote:
>
> >Hello,
> >
> >first things first, you cann't resize your datafiles without shutting
down
> >your database. if it's ok with you, have a look at
> >http://www.mysql.com/doc/en/Adding_and_removing.html
> >
> >you may want to have a look at you my.cnf file, stored in mysql data dir
or
> >in /etc dir, for the default options specified there fo the datafile
with:
> >
> >innodb_data_file_path
> >
> >Yes, you can add data file, just add another entry to above option.
option
> >entry is self-explainatory.
> >
> >At last, ibdata1, ibdata2 are actual data files used to store actual
> >data. one or more of these files are attached to one tablespace and one
file
> >cant span across tablespaces.
> >
> >ib_logfile0, ib_logfile1 are log files, which are used to log sql
statements
> >applied to database. these files are used to restore data in case of any
> >crash or mishap.
> >
> >for further info, have a look at:
> >http://www.mysql.com/doc/en/InnoDB_start.html
> >
> >Enjoy
> >Nitin
> >
> >
> >- Original Message - 
> >From: "Leo Huang" <[EMAIL PROTECTED]>
> >To: <[EMAIL PROTECTED]>
> >Sent: Tuesday, November 04, 2003 6:28 PM
> >Subject: InnoDB Questions
> >
> >
> >
> >
> >>Hello,
> >>
> >>I have a few questions about InnoDB.
> >>I am new to InnoDB, and just converted my MyISAM tables into InnoDB a
> >>few days ago.
> >>I notice that it generates these files
> >>
> >>-rw-rw1 mysqlmysql2560 Nov  2 13:07
> >>ib_arch_log_00
> >>-rw-rw1 mysqlmysql790626304 Nov  4 20:44 ibdata1
> >>-rw-rw1 mysqlmysql10485760 Nov  4 20:44 ib_logfile0
> >>-rw-rw1 mysqlmysql10485760 Nov  4 20:44 ib_logfile1
> >>-rw-rw1 mysqlmysql10485760 Nov  3 00:02 ib_logfile2
> >>
> >>But how can those files work together?
> >>
> >>In InnoDB documentation, it suggests to add another file ibdata2 to
> >>get higher performance. Can I do that now, after I have created the
> >>ibdata1 and used it for a while?
> >>
> >>The most important thing is I deleted a 300M database, but the ibdata1
> >>remains the same size. MyPHPAdmin says 330,000KB free. How can I make
> >>the data file smaller?
> >>
> >>I will be really appreciated if someone can briefly describe what's
> >>happening to those files or point me to some articles.
> >>
> >>Thanks a lot,
> >>Leo
> >>
> >>
> >>-- 
> >>MySQL General Mailing List
> >>For list archives: http://lists.mysql.com/mysql
> >>To unsubscribe:
> >>
> >>
> >http://lists.mysql.com/[EMAIL PROTECTED]
> >
> >
> >
> >
> >
> >
>
>
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
>



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

Can dbx update Tables "asynchronous", like ADO.

[sowee]

Can dbx update Tables "asynchronous", like ADO.


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

Mysql Connection Problems

[John]

HI All

I was wondering if anyone could tell me how to get around this problems

Error:  could not connect to the database.
It's possible the database itself is just not working at the moment.
The admin should also check that the database details have been correctly
specified in config.php
Database host: localhost
Database name:
Database user:

It seems that the database is loosing connections

Cheers
Trevor



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

Complex conditional insert

[Matt Young]

I have two table, accounts describing various accounts and gives the account 
balance and ledger, which recodrs all account transactions.

Playing around with the accounting tables, I have an user transaction that 
goes into the ledger but the values placed in the ledger balance columns 
depend on whether the entry is an asset, liability, or equity. 

The accounts table inform me of the type of entry, and has  columns 
(account int,   type enum (liability,asset,equity)

I have the account type and account number and cash value from the user form 
(user is making some transaction to an account, now I want to enter the 
transaction properly into the ledger)

 To make a journal entry:
// First get the account type
SELECT @type:= type from accounts where accounts.number="user_selected";

// then the hard part
INSERT into journal values 
case @type   
WHEN "equity" THEN
 (place account.number, value in second and fifth column)
WHEN "asset" THEN
 (place account.number, value in fsecond and fiftth column)
WHEN "liability" THEN
 (place account.number, value in third and fourth column);


or is it:

INSERT into journal values(account.number,
// and four separate case statements for each succeeding column)

or 

CASE @type
WHEN "equity" THEN
[do this complete INSERT statement
WHEN "equity" THEN
[do this different INSERT statement
WHEN "equity" THEN
[do this INSERT statement

or

none of the above


Matt Young
EverydaySoftware.biz


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

Re: InnoDB and raw tablespace

[Chris Nolan]

To my knowledge, ext2 does have the limitation but ext3 does not.

Additionally, ReiserFS, JFS and XFS all have disgustingly large file 
size limits.

As a side note, apparently NetWare has major file size limitations 
(going on Gupta's SQLBase documentation)

Regards,

Chris

Mark Lubratt wrote:

No, I'm thinking about ext2 on Linux.  Which I'm pretty sure has a 2GB 
limit.

Ext3 has the same limitation.  Both filesystems will support larger 
file sizes
if the kernel is configured with Large Filesystem Support (LFS).  The 
last time I
heard, this is still not fully implemented (at least enough to trust 
to something
like this...)

I could certainly be wrong on the LFS status.  If so, please let me 
know, I'm
running RH9.

Mark

On Tuesday, November 4, 2003, at 03:42  PM, Chris Nolan wrote:

2GB limit? On MacOS X?

On almost every OS I've played with lately, the file size limit is 
massive -
as in far beyond what disc capacity today will allow. Does MacOS X 
have a 2GB
limit?

Regards,

Chris

On Wed, 5 Nov 2003 04:03 am, Mark Lubratt wrote:

On Tuesday, November 4, 2003, at 10:25  AM, Harald Fuchs wrote:

In article <[EMAIL PROTECTED]>,

Mark Lubratt  writes:

I'm considering this option to keep database maintenance to a minimum
(running out of tablespace issues).  That way, InnoDB already owns 
all
the disk space and I don't have to continually be adding tablespace
files.


Huh?  What's wrong with ":autoextend"?

:autoextend works great until the 2GB file limit is reached.  Then you

have to add another
autoextending tablespace file.  If I can just make a large raw
tablespace, then I don't have to
bother with adding additional tablespace files every so often.







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

Re: mySQL with a quad processor system

[Matt W]

Hi Richard,

Correct. A single thread can only run on 1 CPU at any given time, even
though the OS may switch the thread between CPUs over time.


Matt


- Original Message -
From: "Richard Bewley"
Sent: Saturday, November 01, 2003 9:48 PM
Subject: RE: mySQL with a quad processor system


> Thanks for the response.  The CPU's are 450MHz each, and the system
has 3 GB
> RAM.  It works pretty nicely.  But, they would have to be separate
queries
> to span the 4 CPU's?  I guess one thread can't span across them...
>
> Thanks,
> Richard
>
> -Original Message-
> From: Matt W [mailto:[EMAIL PROTECTED]
> Sent: Saturday, November 01, 2003 10:34 PM
> To: Richard Bewley; [EMAIL PROTECTED]
> Subject: Re: mySQL with a quad processor system
>
> Hi Richard,
>
> Nope, since your OS can run the threads on different CPUs (unlike
> FreeBSD w/o LinuxThreads for example).  You just need 4+ threads
> (clients) running queries at once. :-)
>
> BTW, what kind of system? How fast are those 4 CPUs? ;-)
>
>
> Matt
>
>
> - Original Message -
> From: "Richard Bewley"
> Sent: Saturday, November 01, 2003 7:58 PM
> Subject: mySQL with a quad processor system
>
>
> > Hi,
> >
> > I'm setting up a machine that will have a rather heavy load on it,
> with
> > mysql that has 4 processors.  It's running RedHat Linux 9.0, with a
> kernel
> > with SMP to support the multiple processors.  Is there anything
> special I
> > need to do with mySQL to get it to take advantage of the multiple
> CPU's?
> >
> > Thanks,
> > Richard


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

Re: query time in ~3M row table

[Matt W]

Hi Sean,

LIKE is a range. ;-) I just tried it (on 4.0.16) with an index on (text,
num) and it uses the full key_len, so LIKE is not a problem:

EXPLAIN SELECT * FROM table WHERE text LIKE 'a%' AND num=123;

For the original poster, index on (url, ApacheDate) should be better
than (ApacheDate, url) because with his example queries, EXPLAIN
indicated that less rows would be examined when using the url index.

Oh wait, nevermind! He didn't actually post a query when the index on
ApacheDate was used (just the index scan), so I don't know for sure.
However, it's true that when he included url and ApacheDate in the
WHERE, it chose the url index, which tells me that it's more restrictive
than ApacheDate. In which case, it should still be better to composite
index (url, ApacheDate). :-)

Try it both ways to be sure and see what's faster.


Matt


- Original Message -
From: "sean peters"
Sent: Tuesday, November 04, 2003 5:44 PM
Subject: Re: query time in ~3M row table


> If you make the composite index (urlIndex, ApacheDate) - then the
WHERE
> condition for urlIndex cannot be a LIKE condition, it must be an exact
> condition (range should be ok too) otherwise the composite index
cannot be
> used. This is because the composite index is effectively an index on a
> concatenation of the columns in question.
>
> If you want to be able to search on either column, as well as on both,
i'd
> recommend an index on the urlIndex column, and a composite index
(ApacheDate,
> urlIndex) - because a date column is generally search for an exact or
range
> of values, and the urlIndex appears to get searched on as a begins.
>
> regards,
> sean peters
> [EMAIL PROTECTED]
>
> On Tuesday 04 November 2003 17:29, Matt W wrote:
> > Hi,
> >
> > Yes, make a composite index by adding ApacheDate as the second
column in
> > the urlIndex index.
> >
> > As a side note, Brent said that BETWEEN is not inclusive of the
second
> > parameter. But it IS inclusive. However, since you have a DATETIME
> > column, there is no row with an ApacheDate of *exactly* '2003-10-01'
> > (e.g. the time part is always there), so because of that, it may not
> > include any rows with a month of 10. Maybe that is what Brent meant.
:-)
> >
> > I was thinking of LIKE instead of BETWEEN: ... WHERE ApacheDate LIKE
> > '2003-09%'; I think that's correct.
> >
> >
> > Matt
> >
> >
> > - Original Message -
> > From: <[EMAIL PROTECTED]>
> > Sent: Tuesday, November 04, 2003 5:11 PM
> > Subject: Re: query time in ~3M row table
> >
> > > Yes, it is an indexed field:
> > > mysql> explain select count(*) from hitstats where
year(apacheDate) =
> >
> > 2003 and
> >
> > > -> month(apacheDate) = 9;
> >
> >
++-+--+---+---+---+-
> > +--+-+--+
> >
> > > | id | select_type | table| type  | possible_keys | key
|
> >
> > key_len | ref  | rows| Extra|
> >
> >
++-+--+---+---+---+-
> > +--+-+--+
> >
> > > |  1 | SIMPLE  | hitstats | index | NULL  | dateIndex
|
> >
> > 8 | NULL | 2749862 | Using where; Using index |
> >
> >
++-+--+---+---+---+-
> > +--+-+--+
> >
> > > 1 row in set (0.00 sec)
> > >
> > > Using between is much faster(?!)  Still though, the query is slow
when
> >
> > I add antoher part in, such as:
> > > mysql> select count(*) from hitstats where url like
> >
> > '/water/index.html' AND ApacheDate between '2003-09-01' and
> > '2003-10-01';
> >
> > > +--+
> > >
> > > | count(*) |
> > >
> > > +--+
> > >
> > > | 2396 |
> > >
> > > +--+
> > > 1 row in set (14.68 sec)
> > >
> > > mysql> explain select count(*) from hitstats where url like
> >
> > '/water/index.html' AND ApacheDate between '2003-09-01' and
> > '2003-10-01';
> >
> >
++-+--+---++--+-
> > +--+--+-+
> >
> > > | id | select_type | table| type  | possible_keys  | key
> > |
> > | key_len | ref  | rows | Extra   |
> >
> >
++-+--+---++--+-
> > +--+--+-+
> >
> > > |  1 | SIMPLE  | hitstats | range | urlIndex,dateIndex |
urlIndex
> > |
> > | 255 | NULL | 5368 | Using where |
> >
> >
++-+--+---++--+-
> > +--+--+-+
> >
> > > 1 row in set (0.00 sec)
> > >
> > >
> > > I have two indexes, would it be better if I did the two fields in
one
> >
> > index?
> >
> > > --ja
> > >
> > > On Tue, 4 Nov 2003, Brent Baisley wrote:
> > > > You're searching on a calculation so I'm pretty sure that MySQL
is
> >
> > not
> >
> > > > using an index for the search. You should use explain in front
of
> >
> > your
> >
> > > > query to

Re: MySQL vs .NET

[Curtis Maurand]

.NET v. MySQL is not really a question.  The question your asking is .NET
v. J2EE.

MySQL doesn't care about the client as long as it communicates properly. 
There are ODBC drivers as well as JDBC drivers.  I think I've seen a C#
driver, but then its windows and ODBC/ADO or whatever they want to call
the technology this week should do fine.


Curtis

Haydies said:
> You can get Native database drivers for Delphi to talk to MySQL your
> database back end makes no differance to any thing with Delphi
>
> God know what perl has to do with any thing totaly not the same
> thing as Delphi
>
> Soap supports every thing, soap is language independant... and is fairly
> cool. A bit like using CORBA and Delphi. Its only a XML based tansport
> layer, and its used by .NET as I am sure William knows. Delphi 8 has the
> best .Net support of any language, even better then VB and C++ but
> seeing as MS payed borland to implment it, thats not suprising
>
> I can how ever highly recomend soap, its exstreamly easy to use, though
> I havn't tried it from Delphi. Its all the same. Havn't done .Net at
> all other then the soap part, but if its like Corba (they do the same
> thing so it should be) then its bloody easy as well. Its all about
> remote objects. I would say though that Delphi isn't ideal for web
> services. Your better of thinking about a move to a more web based
> technology.
>
> I loved Delphi for 8 years, but alas, these days PHP is much more fun,
> works well and is fast. If you do want to consider some thing other then
> Delphi to do your thing then PHP kicks the hell out of Perl Poor old
> Delphi is a dieing market here :-(
>
> Other then that, really, Delphi to MySQL is just like Delphi to Paradox,
> or Oracle or any thing else just get the drivers and off you go :-)
>
>
> - Original Message -
> From: "Bill Hess" <[EMAIL PROTECTED]>
> To: "William IT" <[EMAIL PROTECTED]>
> Cc: <[EMAIL PROTECTED]>
> Sent: Tuesday, November 04, 2003 12:56 PM
> Subject: Re: MySQL vs .NET
>
>
> : If you are using Perl, take a look at SOAP::Lite - do not let the name
> : fool you...   www.soaplite.com   Combining this with Apache and
> mod_perl : and given Perl can directly interface with MySQL using Perl's
> DBI (and : also the countless other modules available on CPAN) we have
> found this : to be a very robust solution for a distributed and/or
> multi-tier system. : SOAP::Lite supports MS .Net clients using
> VB/VC++  since .Net is : pretty much MS's spin on the SOAP protocol, but
> there are some quirks... :
> : There are other SOAP variants for other languages like Java, Python, :
> etc...  but not sure how these stack up...
> :
> :
> : William IT wrote:
> :
> : > I am using Delphi 7 and MySql 4.0.16, since MS release .Net
> technology and
> : > also Delphi 8 .Net to provide web services application, I want to
> know how
> : > to make a web services application using Mysql? Or is there similiar
> : > technology like .Net but using Mysql?
> : >
> : >
> : >
> :
> : --
> :
> : Bill Hess
> : Technology Resource Group
> :
> :
> :
> : --
> : MySQL General Mailing List
> : For list archives: http://lists.mysql.com/mysql
> : To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]
> :
> :
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]




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

Re: InnoDB Questions

[Chris Nolan]

The "last one" you're referring to - could it be the error log?

The log files will only grow to a pre-determined limit. These log files 
are used to ensure that transactions maintain their durability.

With Oracle, you'd want to be careful. Oracle gets very, very picky 
about the stuff underneath it when it's running. If it gets to a 
configuration limit, the results can be very, very depressing. Having to 
extend tablespaces by hand is a very common Oracle DBA task.

Regards,

Chris

Leo Huang wrote:

Hello Nitin,

From the timestamp of the log files, it seems that the first two files
works together while the last one seems just sitting there, doesn't do
anything.
Also, will the log files getting bigger and bigger in the future?? If so
how should I deal with them?
For your last suggestion, what will Oracle do if I specify a datafile
size less than the database size, e.g. I specify 20M in the my.cnf while
there are actually 400M of data in my database?
Leo

Nitin wrote:

 

Hello,

first things first, you cann't resize your datafiles without shutting down
your database. if it's ok with you, have a look at
http://www.mysql.com/doc/en/Adding_and_removing.html
you may want to have a look at you my.cnf file, stored in mysql data dir or
in /etc dir, for the default options specified there fo the datafile with:
innodb_data_file_path

Yes, you can add data file, just add another entry to above option. option
entry is self-explainatory.
At last, ibdata1, ibdata2 are actual data files used to store actual
data. one or more of these files are attached to one tablespace and one file
cant span across tablespaces.
ib_logfile0, ib_logfile1 are log files, which are used to log sql statements
applied to database. these files are used to restore data in case of any
crash or mishap.
for further info, have a look at:
http://www.mysql.com/doc/en/InnoDB_start.html
Enjoy
Nitin
- Original Message - 
From: "Leo Huang" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, November 04, 2003 6:28 PM
Subject: InnoDB Questions



   

Hello,

I have a few questions about InnoDB.
I am new to InnoDB, and just converted my MyISAM tables into InnoDB a
few days ago.
I notice that it generates these files
-rw-rw1 mysqlmysql2560 Nov  2 13:07
ib_arch_log_00
-rw-rw1 mysqlmysql790626304 Nov  4 20:44 ibdata1
-rw-rw1 mysqlmysql10485760 Nov  4 20:44 ib_logfile0
-rw-rw1 mysqlmysql10485760 Nov  4 20:44 ib_logfile1
-rw-rw1 mysqlmysql10485760 Nov  3 00:02 ib_logfile2
But how can those files work together?

In InnoDB documentation, it suggests to add another file ibdata2 to
get higher performance. Can I do that now, after I have created the
ibdata1 and used it for a while?
The most important thing is I deleted a 300M database, but the ibdata1
remains the same size. MyPHPAdmin says 330,000KB free. How can I make
the data file smaller?
I will be really appreciated if someone can briefly describe what's
happening to those files or point me to some articles.
Thanks a lot,
Leo
--
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: query time in ~3M row table

[Brent Baisley]

It looks like MySQL is searching on the url first. I'd be curious if  
you reversed your WHERE clause order, put your ApacheDate first and see  
if MySQL optimizes your query differently. I'm assuming your URL field  
is rather large, thus a long string comparison is taking place even  
though it's indexed. If you can force MySQL to match on the dates  
first, I think it would be much faster.

I wouldn't replace the two indexes you have with one. Depending on  
which field you put first, the index couldn't be used on the second  
field. Your also combining two different data types in an index.

On Tuesday, November 4, 2003, at 06:11 PM, <[EMAIL PROTECTED]> wrote:

Yes, it is an indexed field:
mysql> explain select count(*) from hitstats where year(apacheDate) =  
2003 and
-> month(apacheDate) = 9;
++-+--+---+---+--- 
+-+--+-+--+
| id | select_type | table| type  | possible_keys | key   |  
key_len | ref  | rows| Extra|
++-+--+---+---+--- 
+-+--+-+--+
|  1 | SIMPLE  | hitstats | index | NULL  | dateIndex | 
   8 | NULL | 2749862 | Using where; Using index |
++-+--+---+---+--- 
+-+--+-+--+
1 row in set (0.00 sec)

Using between is much faster(?!)  Still though, the query is slow when  
I add antoher part in, such as:
mysql> select count(*) from hitstats where url like  
'/water/index.html' AND ApacheDate between '2003-09-01' and  
'2003-10-01';
+--+
| count(*) |
+--+
| 2396 |
+--+
1 row in set (14.68 sec)

mysql> explain select count(*) from hitstats where url like  
'/water/index.html' AND ApacheDate between '2003-09-01' and  
'2003-10-01';
++-+--+---++-- 
+-+--+--+-+
| id | select_type | table| type  | possible_keys  | key   
| key_len | ref  | rows | Extra   |
++-+--+---++-- 
+-+--+--+-+
|  1 | SIMPLE  | hitstats | range | urlIndex,dateIndex | urlIndex  
| 255 | NULL | 5368 | Using where |
++-+--+---++-- 
+-+--+--+-+
1 row in set (0.00 sec)

I have two indexes, would it be better if I did the two fields in one  
index?

--
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: query time in ~3M row table

[Brent Baisley]

Wow, ouch. That is an awfully bright light bulb that just lit up in my 
head. Duh, too obvious for me to see.

1 comes before 2 except after c.

On Tuesday, November 4, 2003, at 06:50 PM, Dan Nelson wrote:

In the last episode (Nov 04), Brent Baisley said:
I had thought it was inclusive, but in quick tests I discovered it
was not. I haven't tried to look it up in the documentation yet. In
my test I did a query on a contact database like this: select
lastname from contacts where lastname between 'A' and 'B';
Only 'A' names are returned. Yes, there are hundreds of 'B' names.
But are there any records where lastname = "B"?  "Baker" sorts below
"B", for example.
--
Dan Nelson
[EMAIL PROTECTED]

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

Re: mysql memory usage

[Jeremy Zawodny]

On Tue, Nov 04, 2003 at 10:09:01AM -, Alexis Guia wrote:
> 
> Hi,
> 
> I think that MyISAM uses the key buffer only if needed. The same happens
> with almost all the other buffers (read buffer, sort buffer, etc.). 

True, but there's a subtle difference between "uses" and "allocates."
If you tell MySQL that it has 16GB for a key_buffer, it'll allocate
16GB even if it only ever uses 28KB.  The same is true of several
(probably all?) other buffers.

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

MySQL 4.0.15-Yahoo-SMP: up 51 days, processed 1,925,645,484 queries (428/sec. avg)

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

Re: query time in ~3M row table

[Dan Nelson]

In the last episode (Nov 04), Brent Baisley said:
> I had thought it was inclusive, but in quick tests I discovered it
> was not. I haven't tried to look it up in the documentation yet. In
> my test I did a query on a contact database like this: select
> lastname from contacts where lastname between 'A' and 'B';
> 
> Only 'A' names are returned. Yes, there are hundreds of 'B' names. 

But are there any records where lastname = "B"?  "Baker" sorts below
"B", for example.

-- 
Dan Nelson
[EMAIL PROTECTED]

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

Re: InnoDB and raw tablespace

[Jeremy Zawodny]

On Tue, Nov 04, 2003 at 01:42:23AM -0600, Mark Lubratt wrote:
> I'm considering using the raw tablespace from InnoDB for a project I'm 
> working on.  I noticed a couple of years ago that there were reports of 
> tablespace corruption on Linux and these raw tablespaces.  Have these 
> problems been fixed? I'm considering running it on a hardware RAID 
> (stripes of mirrors, I forget if that's RAID 10, or RAID 01).  Should I 
> use FreeBSD instead of Linux?
> 
> I'm considering this option to keep database maintenance to a minimum 
> (running out of tablespace issues).  That way, InnoDB already owns all 
> the disk space and I don't have to continually be adding tablespace 
> files.
> 
> Any thoughts?

I usually tell people to think twice about using raw disks for two
main reasons:

  1. Performance.  I've not seen anybody report a significant
 performance boost doing this.

  2. Transparency.  It's nice to be able to use a wider variety of
 tools to examine, copy, back up, and otherwise tinker with data.
 By using a raw disk, you lose most of this.

However, if the performance gain is really there, maybe it's more
important than #2.

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

MySQL 4.0.15-Yahoo-SMP: up 51 days, processed 1,925,631,314 queries (428/sec. avg)

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

Re: query time in ~3M row table

[Brent Baisley]

I had thought it was inclusive, but in quick tests I discovered it was 
not. I haven't tried to look it up in the documentation yet.
In my test I did a query on a contact database like this:
select lastname from contacts where lastname between 'A' and 'B';

Only 'A' names are returned. Yes, there are hundreds of 'B' names. I 
haven't tried this with numbers and dates to see if it's the same 
result. I would think it should be the same. This is using 4.0.12 (I'll 
have to upgrade that soon). I'd be curious what your tests show.

On Tuesday, November 4, 2003, at 06:29 PM, Matt W wrote:

As a side note, Brent said that BETWEEN is not inclusive of the second
parameter. But it IS inclusive.
--
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: InnoDB Questions

[Leo Huang]

Hello Nitin,

>From the timestamp of the log files, it seems that the first two files
works together while the last one seems just sitting there, doesn't do
anything.

Also, will the log files getting bigger and bigger in the future?? If so
how should I deal with them?

For your last suggestion, what will Oracle do if I specify a datafile
size less than the database size, e.g. I specify 20M in the my.cnf while
there are actually 400M of data in my database?

Leo

Nitin wrote:

>Hello,
>
>first things first, you cann't resize your datafiles without shutting down
>your database. if it's ok with you, have a look at
>http://www.mysql.com/doc/en/Adding_and_removing.html
>
>you may want to have a look at you my.cnf file, stored in mysql data dir or
>in /etc dir, for the default options specified there fo the datafile with:
>
>innodb_data_file_path
>
>Yes, you can add data file, just add another entry to above option. option
>entry is self-explainatory.
>
>At last, ibdata1, ibdata2 are actual data files used to store actual
>data. one or more of these files are attached to one tablespace and one file
>cant span across tablespaces.
>
>ib_logfile0, ib_logfile1 are log files, which are used to log sql statements
>applied to database. these files are used to restore data in case of any
>crash or mishap.
>
>for further info, have a look at:
>http://www.mysql.com/doc/en/InnoDB_start.html
>
>Enjoy
>Nitin
>
>
>- Original Message - 
>From: "Leo Huang" <[EMAIL PROTECTED]>
>To: <[EMAIL PROTECTED]>
>Sent: Tuesday, November 04, 2003 6:28 PM
>Subject: InnoDB Questions
>
>
>  
>
>>Hello,
>>
>>I have a few questions about InnoDB.
>>I am new to InnoDB, and just converted my MyISAM tables into InnoDB a
>>few days ago.
>>I notice that it generates these files
>>
>>-rw-rw1 mysqlmysql2560 Nov  2 13:07
>>ib_arch_log_00
>>-rw-rw1 mysqlmysql790626304 Nov  4 20:44 ibdata1
>>-rw-rw1 mysqlmysql10485760 Nov  4 20:44 ib_logfile0
>>-rw-rw1 mysqlmysql10485760 Nov  4 20:44 ib_logfile1
>>-rw-rw1 mysqlmysql10485760 Nov  3 00:02 ib_logfile2
>>
>>But how can those files work together?
>>
>>In InnoDB documentation, it suggests to add another file ibdata2 to
>>get higher performance. Can I do that now, after I have created the
>>ibdata1 and used it for a while?
>>
>>The most important thing is I deleted a 300M database, but the ibdata1
>>remains the same size. MyPHPAdmin says 330,000KB free. How can I make
>>the data file smaller?
>>
>>I will be really appreciated if someone can briefly describe what's
>>happening to those files or point me to some articles.
>>
>>Thanks a lot,
>>Leo
>>
>>
>>-- 
>>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: RAND ()

[Matt W]

Hi Payne,

This is probably because of a bug in MySQL versions before 3.23.56 and
4.0.10 -- RAND() didn't work right the first time it was used in a *new*
connection. After the first run, though, it was OK.

To work around the problem, I've told people to just call RAND() a few
times before doing your query. Just like this in PHP:

mysql_query('SELECT RAND(), RAND(), RAND()');

Then the ORDER BY RAND() query should be random. Now that I think about
it, putting RAND() twice in the ORDER BY would probably have the same
effect. So you could try

$result = mysql_query('... ORDER BY RAND(), RAND() LIMIT 1');


Hope that helps.


Matt


- Original Message -
From: "Payne"
Sent: Tuesday, November 04, 2003 10:33 AM
Subject: RAND ()


> Hi,
>
> I have been playing around with RAND(). It works very well if I do a
sql
> statement with mysql, but I having problem using with mysql statement
> with php. When a person calls on the page,  the same output is always
> view. How can I get RAND() work with php.
>
> sorry for the php, stuff. Thanks for any help you can give.
>
> Payne


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

Re: query time in ~3M row table

[Dan Nelson]

In the last episode (Nov 04), [EMAIL PROTECTED] said:
> 
> I have a question about how long queries should be taking and if my server is too 
> small for what I want to be doing.  I have a table setup to record stats from an 
> apache web server.  I import the file currently once per month.  Here is my table:
> mysql> describe hitStats;
> +-+--+---+--+-+-++
> | Field   | Type | Collation | Null | Key | Default 
> | Extra  |
> +-+--+---+--+-+-++
> | hostIP  | varchar(24)  | latin1_swedish_ci |  | | 
> ||
> | apacheDate  | datetime | latin1_swedish_ci |  | MUL | -00-00 00:00:00 
> ||
> | status  | int(11)  | binary|  | | 0   
> ||
> | bytes   | varchar(20)  | latin1_swedish_ci |  | | 
> ||
> | contentType | varchar(40)  | latin1_swedish_ci |  | | 
> ||
> | url | varchar(255) | latin1_swedish_ci |  | MUL | 
> ||
> | referer | text | latin1_swedish_ci |  | | 
> ||
> | agent   | text | latin1_swedish_ci |  | | 
> ||
> | statID  | int(11)  | binary|  | PRI | NULL
> | auto_increment |
> +-+--+---+--+-+-++
> 
> So, about 3 million rows.
> 
> I have read the http://www.mysql.com/doc/en/Server_parameters.html
> page and think that I did what it told me to.
> 
> When I try to do any sort of query the times are really long.  Such as:
> 
> mysql> select count(*) from hitstats where year(apacheDate) = 2003 and 
> month(apacheDate) = 9;   
> +--+
> | count(*) |
> +--+
> |   988759 |
> +--+
> 1 row in set (25.17 sec)

Neither of those constraints can use indexes, so mysql is basically
doing a full index scan of apacheDate (you can verify this by doing an
EXPLAIN SELECT).  Try

select count(*) from hitstats where apacheDate between 2003090100 and 
200309

Mysql stores dates in integer format internally, which is why you can
cheat and use all 9's for an end day and time.

-- 
Dan Nelson
[EMAIL PROTECTED]

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

Re: InnoDB Questions

[Leo Huang]

I don't know.

I will get some time this week, shutdown MySQL, backup my binary files, 
have a go as what Nitin said and see what's going on there.

Leo

Gabriel Ricard wrote:

On Tuesday, November 4, 2003, at 07:58  AM, Leo Huang wrote:

In InnoDB documentation, it suggests to add another file ibdata2 to
get higher performance. Can I do that now, after I have created the
ibdata1 and used it for a while?


How exactly does this increase performance? Will InnoDB store some 
data in one data file and some in another (somewhat like RAID 1)?

- Gabriel




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

Re: query time in ~3M row table

[Matt W]

Hi,

Yes, make a composite index by adding ApacheDate as the second column in
the urlIndex index.

As a side note, Brent said that BETWEEN is not inclusive of the second
parameter. But it IS inclusive. However, since you have a DATETIME
column, there is no row with an ApacheDate of *exactly* '2003-10-01'
(e.g. the time part is always there), so because of that, it may not
include any rows with a month of 10. Maybe that is what Brent meant. :-)

I was thinking of LIKE instead of BETWEEN: ... WHERE ApacheDate LIKE
'2003-09%'; I think that's correct.


Matt


- Original Message -
From: <[EMAIL PROTECTED]>
Sent: Tuesday, November 04, 2003 5:11 PM
Subject: Re: query time in ~3M row table


>
> Yes, it is an indexed field:
> mysql> explain select count(*) from hitstats where year(apacheDate) =
2003 and
> -> month(apacheDate) = 9;
>
++-+--+---+---+---+-
+--+-+--+
> | id | select_type | table| type  | possible_keys | key   |
key_len | ref  | rows| Extra|
>
++-+--+---+---+---+-
+--+-+--+
> |  1 | SIMPLE  | hitstats | index | NULL  | dateIndex |
8 | NULL | 2749862 | Using where; Using index |
>
++-+--+---+---+---+-
+--+-+--+
> 1 row in set (0.00 sec)
>
> Using between is much faster(?!)  Still though, the query is slow when
I add antoher part in, such as:
> mysql> select count(*) from hitstats where url like
'/water/index.html' AND ApacheDate between '2003-09-01' and
'2003-10-01';
> +--+
> | count(*) |
> +--+
> | 2396 |
> +--+
> 1 row in set (14.68 sec)
>
> mysql> explain select count(*) from hitstats where url like
'/water/index.html' AND ApacheDate between '2003-09-01' and
'2003-10-01';
>
++-+--+---++--+-
+--+--+-+
> | id | select_type | table| type  | possible_keys  | key
| key_len | ref  | rows | Extra   |
>
++-+--+---++--+-
+--+--+-+
> |  1 | SIMPLE  | hitstats | range | urlIndex,dateIndex | urlIndex
| 255 | NULL | 5368 | Using where |
>
++-+--+---++--+-
+--+--+-+
> 1 row in set (0.00 sec)
>
>
> I have two indexes, would it be better if I did the two fields in one
index?
>
> --ja
> On Tue, 4 Nov 2003, Brent Baisley wrote:
>
> > You're searching on a calculation so I'm pretty sure that MySQL is
not
> > using an index for the search. You should use explain in front of
your
> > query to see if MySQL is using indexes. You do have that date field
> > indexed, don't you?
> > You should search on ApacheDate between 9/1/2003 and 10/1/2003.
> >
> > Something like this:
> > select count(*) from hitstats where apacheData between '2003-09-01'
and
> > '2003-10-01';
> >
> > I think that's right. The 'between' is not inclusive of the second
> > parameter.
> >
> > On Tuesday, November 4, 2003, at 05:09 PM, <[EMAIL PROTECTED]>
wrote:
> >
> > > When I try to do any sort of query the times are really long.
Such as:
> > >
> > > mysql> select count(*) from hitstats where year(apacheDate) = 2003
and
> > > month(apacheDate) = 9;   +--+
> > > | count(*) |
> > > +--+
> > > |   988759 |
> > > +--+
> > > 1 row in set (25.17 sec)


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

Re: InnoDB and raw tablespace

[Ware Adams]

Chris Nolan wrote:

>2GB limit? On MacOS X?
>
>On almost every OS I've played with lately, the file size limit is
>massive - as in far beyond what disc capacity today will allow. Does
>MacOS X have a 2GB limit?

No, OS X has a file size limit of 2 TB (prior to 10.2), 8 TB (10.2.x) or 16
TB (10.3).

http://docs.info.apple.com/article.html?artnum=25557

--Ware
>
>Regards,
>
>Chris
>
>
>On Wed, 5 Nov 2003 04:03 am, Mark Lubratt wrote:
>>On Tuesday, November 4, 2003, at 10:25  AM, Harald Fuchs wrote:
>>>In article <[EMAIL PROTECTED]>,
>>>
>>>Mark Lubratt  writes:
I'm considering this option to keep database maintenance to a
minimum (running out of tablespace issues).  That way, InnoDB
already owns all the disk space and I don't have to continually be
adding tablespace files.
>>>
>>>Huh?  What's wrong with ":autoextend"?
>>>
>>:autoextend works great until the 2GB file limit is reached.  Then you
>>
>>have to add another autoextending tablespace file.  If I can just
>>make a large raw tablespace, then I don't have to bother with adding
>>additional tablespace files every so often.
>
>

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

Re: query time in ~3M row table

[jabbott]

Yes, it is an indexed field:
mysql> explain select count(*) from hitstats where year(apacheDate) = 2003 and
-> month(apacheDate) = 9;
++-+--+---+---+---+-+--+-+--+
| id | select_type | table| type  | possible_keys | key   | key_len | ref  | 
rows| Extra|
++-+--+---+---+---+-+--+-+--+
|  1 | SIMPLE  | hitstats | index | NULL  | dateIndex |   8 | NULL | 
2749862 | Using where; Using index |
++-+--+---+---+---+-+--+-+--+
1 row in set (0.00 sec)

Using between is much faster(?!)  Still though, the query is slow when I add antoher 
part in, such as:
mysql> select count(*) from hitstats where url like '/water/index.html' AND ApacheDate 
between '2003-09-01' and '2003-10-01';
+--+
| count(*) |
+--+
| 2396 |
+--+
1 row in set (14.68 sec)

mysql> explain select count(*) from hitstats where url like '/water/index.html' AND 
ApacheDate between '2003-09-01' and '2003-10-01';
++-+--+---++--+-+--+--+-+
| id | select_type | table| type  | possible_keys  | key  | key_len | ref  
| rows | Extra   |
++-+--+---++--+-+--+--+-+
|  1 | SIMPLE  | hitstats | range | urlIndex,dateIndex | urlIndex | 255 | NULL 
| 5368 | Using where |
++-+--+---++--+-+--+--+-+
1 row in set (0.00 sec)


I have two indexes, would it be better if I did the two fields in one index?

--ja
On Tue, 4 Nov 2003, Brent Baisley wrote:

> You're searching on a calculation so I'm pretty sure that MySQL is not 
> using an index for the search. You should use explain in front of your 
> query to see if MySQL is using indexes. You do have that date field 
> indexed, don't you?
> You should search on ApacheDate between 9/1/2003 and 10/1/2003.
> 
> Something like this:
> select count(*) from hitstats where apacheData between '2003-09-01' and 
> '2003-10-01';
> 
> I think that's right. The 'between' is not inclusive of the second 
> parameter.
> 
> On Tuesday, November 4, 2003, at 05:09 PM, <[EMAIL PROTECTED]> wrote:
> 
> > When I try to do any sort of query the times are really long.  Such as:
> >
> > mysql> select count(*) from hitstats where year(apacheDate) = 2003 and
> > month(apacheDate) = 9;   +--+
> > | count(*) |
> > +--+
> > |   988759 |
> > +--+
> > 1 row in set (25.17 sec)
> >
> 

-- 


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

log information in MySQL

[Jon Miller]

I need a way to log every message that MySQL generates.  I have the following in the 
/etc/my.cnf file:
 [mysqld]
port=3309
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-bin=/var/log/mysql.log
bind-address=192.168.0.15
log=/var/log/mysqlquery.log

[mysql.server]
user=mysql
basedir=/var/lib

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Also in /etc/syslog.conf I have an entry:
# Log Mysql messages
mysqld.*/var/log/mysqld.log

Not sure if this will work.

Any suggesstions?




Yet when I go to look for any info I do not see anything.

Jon L. Miller, MCNE, CNS, ASE
Director/Sr Systems Consultant
MMT Networks Pty Ltd
http://www.mmtnetworks.com.au

"I don't know the key to success, but the key to failure
 is trying to please everybody." -Bill Cosby

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

Re: query time in ~3M row table

[Brent Baisley]

You're searching on a calculation so I'm pretty sure that MySQL is not 
using an index for the search. You should use explain in front of your 
query to see if MySQL is using indexes. You do have that date field 
indexed, don't you?
You should search on ApacheDate between 9/1/2003 and 10/1/2003.

Something like this:
select count(*) from hitstats where apacheData between '2003-09-01' and 
'2003-10-01';

I think that's right. The 'between' is not inclusive of the second 
parameter.

On Tuesday, November 4, 2003, at 05:09 PM, <[EMAIL PROTECTED]> wrote:

When I try to do any sort of query the times are really long.  Such as:

mysql> select count(*) from hitstats where year(apacheDate) = 2003 and
month(apacheDate) = 9;   +--+
| count(*) |
+--+
|   988759 |
+--+
1 row in set (25.17 sec)
--
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: Windows to Linux

[Matt Babineau]

Well, he could do that but being inexperienced with Linux, I figured it
would be more beneficial to use a familiar WYSIWYG so he doesn't blow
and hour playing the with CLI like I did :)

On Tue, 2003-11-04 at 16:58, Big Brother wrote:
> err why not do a mysqldump then just import that?
> 
> ---
> 
> 
> Quoting Matt Babineau <[EMAIL PROTECTED]>:
> 
> > Check out SQLYog, could can connect and copy databases...pretty much
> > like MSSQL Enterprise manager. They have a trial version on their site:
> > http://www.webyog.com/sqlyog
> > 
> > 
> > 
> > On Mon, 2003-11-03 at 19:51, Matt Fletcher wrote:
> > > Hi there,
> > > I have taken the plunge and dropped windows in favour of linux. My 
> > > question is what is the best way to get the data from my windows mysql 
> > > databases into linux? Can I just copy some files from one partition to 
> > > another or what?
> > > 
> > > Thanks,
> > > 
> > > Matt
> > 
> > 
> > -- 
> > 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]

query time in ~3M row table

[jabbott]

I have a question about how long queries should be taking and if my server is too 
small for what I want to be doing.  I have a table setup to record stats from an 
apache web server.  I import the file currently once per month.  Here is my table:
mysql> describe hitStats;
+-+--+---+--+-+-++
| Field   | Type | Collation | Null | Key | Default | 
Extra  |
+-+--+---+--+-+-++
| hostIP  | varchar(24)  | latin1_swedish_ci |  | | |  
  |
| apacheDate  | datetime | latin1_swedish_ci |  | MUL | -00-00 00:00:00 |  
  |
| status  | int(11)  | binary|  | | 0   |  
  |
| bytes   | varchar(20)  | latin1_swedish_ci |  | | |  
  |
| contentType | varchar(40)  | latin1_swedish_ci |  | | |  
  |
| url | varchar(255) | latin1_swedish_ci |  | MUL | |  
  |
| referer | text | latin1_swedish_ci |  | | |  
  |
| agent   | text | latin1_swedish_ci |  | | |  
  |
| statID  | int(11)  | binary|  | PRI | NULL| 
auto_increment |
+-+--+---+--+-+-++

As for what it has in it:
mysql> select count(*) from hitStats;   
+--+
| count(*) |
+--+
|  2749862 |
+--+
1 row in set (0.00 sec)

So, about 3 million rows.

I have this running on a Sun Blade 150 workstation w/ Solaris 9.  I think it has 1G 
memory.  MySql 4.1 is the only thing running on this server.  I have another server 
setup just like this one only with mysql 3.x and the times were similar last month 
when I was testing it.

I have read the http://www.mysql.com/doc/en/Server_parameters.html page and think that 
I did what it told me to.  

When I try to do any sort of query the times are really long.  Such as:

mysql> select count(*) from hitstats where year(apacheDate) = 2003 and
month(apacheDate) = 9;   +--+
| count(*) |
+--+
|   988759 |
+--+
1 row in set (25.17 sec)

Running top shows:
last pid: 22934;  load averages:  0.42,  0.13,  0.08   
35 processes:  34 sleeping, 1 on cpu
CPU states: 12.4% idle, 87.6% user,  0.0% kernel,  0.0% iowait,  0.0% swap
Memory: 768M real, 405M free, 433M swap in use, 642M swap free

   PID USERNAME THR PRI NICE  SIZE   RES STATETIMECPU COMMAND
 22921 mysql 13  590  414M  201M sleep3:39 67.73% mysqld 

So I guess my question is, do I not have a fast enough server?  Is this database too 
much for the machine I am running it on?  Or do I not have it optimized or is 
something else going wrong?

--ja
-- 


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

Re: phpmyadmin screw up

[Saqib Ali]

seems like you set the mysql root passwd
which is the right thing to do.

now should connect as follows:
# /usr/local/mysql/bin/mysql -u root -p

Saqib Ali
-
http://validate.sf.net <--- XHTML/HTML/DocBook Validator

On Tue, 4 Nov 2003, joffrey leevy wrote:

> Hi all:
>
> I was trying to use phpmyadmin for the first time and
> messed up when trying to give the [EMAIL PROTECTED] a
> password.  I did that because phpmyadmin told me
> something about lax security using "root" with no
> password.  Anyhow something went wrong.
>
>
> Now I can't do anything with phpadmin or mysql client.
>The message is -   "Error 1045:  Access Denied for
> User:  (Using Password:No).  Grateful for anyone's
> help.
>
>
> Thanks
>
>
>
>
> __
> Do you Yahoo!?
> Protect your identity with Yahoo! Mail AddressGuard
> http://antispam.yahoo.com/whatsnewfree
>
> --
> 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]

phpmyadmin screw up

[joffrey leevy]

Hi all: 
  
I was trying to use phpmyadmin for the first time and
messed up when trying to give the [EMAIL PROTECTED] a
password.  I did that because phpmyadmin told me
something about lax security using "root" with no
password.  Anyhow something went wrong.

  
Now I can't do anything with phpadmin or mysql client.
   The message is -   "Error 1045:  Access Denied for
User:  (Using Password:No).  Grateful for anyone's
help.

  
Thanks 

 


__
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard
http://antispam.yahoo.com/whatsnewfree

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

Re: PHP program for sql queries?

[Big Brother]

http://www.phpmyadmin.net/home_page/

--


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

Re: InnoDB and raw tablespace

[Chris Nolan]

2GB limit? On MacOS X?

On almost every OS I've played with lately, the file size limit is massive - 
as in far beyond what disc capacity today will allow. Does MacOS X have a 2GB 
limit?

Regards,

Chris


On Wed, 5 Nov 2003 04:03 am, Mark Lubratt wrote:
> On Tuesday, November 4, 2003, at 10:25  AM, Harald Fuchs wrote:
> > In article <[EMAIL PROTECTED]>,
> >
> > Mark Lubratt  writes:
> >> I'm considering this option to keep database maintenance to a minimum
> >> (running out of tablespace issues).  That way, InnoDB already owns all
> >> the disk space and I don't have to continually be adding tablespace
> >> files.
> >
> > Huh?  What's wrong with ":autoextend"?
> >
> :autoextend works great until the 2GB file limit is reached.  Then you
>
> have to add another
> autoextending tablespace file.  If I can just make a large raw
> tablespace, then I don't have to
> bother with adding additional tablespace files every so often.


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

Re: Selecting data from one table that is not in another

[Brent Baisley]

MySQL would eventually finish that query, but it's a big. You are 
joining every record in one database with every record in another, 
except where the id's match. You would probably end up with a result 
set of 499,000 records, assuming there is a one to one match with ids.

What you want to do is a left join and check for no match, like this:
SELECT t1.*,t2.model_id FROM data1 as t1
LEFT JOIN data2 as t2 ON t1.model_id=t2.model_id
WHERE t2.model_id IS NULL
You should be able to find lots of examples of this in the archives. 
This comes up a lot.

On Tuesday, November 4, 2003, at 02:31 PM, Agrin, Nathan wrote:

I'm looking for a way to select data from one table that is not found 
in
another.  I am sorting on a column called 'model_id'.  Basically I want
something like this to work:

"SELECT t1.*, t2.model_id FROM data1 as t1, data2 as t2 WHERE
t1.model_id != t2.model_id"
t1 has about 1000 entries and t2 has around 500.  Unfortunately when I
run this SQL query mySQL seems to hang up and never produces any data.
I'm running this through PHP and trying to display the information
returned on a webpage.  If you need any other info in order to help me,
let me know.
Thanks in advance,
-Nate
--
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: PHP program for sql queries?

[Daniel Kasak]

joffrey leevy wrote:

Hi:

Does anyone have a simple PHP program that will enable
SQL queries to be executed and displayed with MySQL?
thanks
 

Isn't there a thing called PHPMyAdmin or MyPHPAdmin or something like that?
I just use PHP. It's simple enough to not need a helper app.
--
Daniel Kasak
IT Developer
* NUS Consulting Group*
Level 5, 77 Pacific Highway
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: [EMAIL PROTECTED]
website: http://www.nusconsulting.com.au
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

another insert select question

[Jason Joines]

Table employees:

 
| idnumber | email | phone | address |
 
Table webusers:
 -
| idnumber | userid | website |
 -
  Table employees is completely populated.  Table webusers has the 
idnumber and website fields completely populated.  What query can I use 
to insert the email address from each persones record in the employees 
table into the userid field of their corresponding record in the 
webusers table?

Thanks,

Jason Joines
Open Source = Open Mind

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

Re: mysql noob can't get msyslog to connect to mysql

[Scott H]

Re-posted (corrected a typo in config file name):

I want to set up a central log server, doing this
on RH9. Using msyslog, a replacement for syslog
which can log to a mysql db.  I originally had
msyslogd logging successfully to mysql. Great,
but I want to be able to view my logs via http,
and after having trouble setting up apache with
PHP support and the tie-in to mysql, I decided to
use XAMPP (formerly LAMPP) ver 1.3, which
installs all the components I need apache, PHP,
mysql, PHPAdmin (and other items) as a package.
(I uninstalled mysql and apache first.)

Very nice but now I can't get msyslog to connect
to mysql. In the logs, I have:

om_mysql: sending messages to localhost, database
msyslog, table syslogTB.
om_mysql_write: Lost connection! [Can't connect
to local MySQL server through socket
'/var/lib/mysq

That last line is truncated in the log. I presume
it would complete with the socket file, which
would be defined in my /etc/my.cnf:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

[mysql.server]
user=mysql
basedir=/var/lib

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Can anyone tell me where to start on this?
Thanks!!
Scott 

__
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard
http://antispam.yahoo.com/whatsnewfree

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

Deadlock

[Christophe Lombart]

Hello,

I'm using MySQL 4.0.15 standard - InnoDB and I got a deadlock. I don't 
see why !
How Can I find the reason ? It seems to occurs on delete statments.

Regards,
Christophe
Here is my InnoDB status :

=
Per second averages calculated from the last 51 seconds
--
SEMAPHORES
--
OS WAIT ARRAY INFO: reservation count 539, signal count 189
Mutex spin waits 800, rounds 10240, OS waits 328
RW-shared spins 370, OS waits 188; RW-excl spins 35, OS waits 23

LATEST DETECTED DEADLOCK

031104 21:39:32
*** (1) TRANSACTION:
TRANSACTION 0 119492, ACTIVE 7 sec, process no 3109, OS thread id 188446 
starting index read
mysql tables in use 1, locked 1
LOCK WAIT 27 lock struct(s), heap size 2496, undo log entries 42
MySQL thread id 38, query id 12022 localhost 127.0.0.1 root updating
DELETE FROM SLIDE_REVISION_PREDECESSOR WHERE REVISION_ID = 101
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 192 n bits 72 table 
test/SLIDE_REVISION_PREDECESSOR index PRIMARY trx id 0 119492 lock_mode 
X waiting
Record lock, heap no 1 RECORD: info bits 0 0: len 9; hex 
73757072656d756d00; asc supremum.;;
*** (2) TRANSACTION:
TRANSACTION 0 120070, ACTIVE 4 sec, process no 3095, OS thread id 131089 
starting index read, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
43 lock struct(s), heap size 5504, undo log entries 21
MySQL thread id 24, query id 12050 localhost 127.0.0.1 root Updating
update SLIDE_URI  set PARENT_URI_ID = 1 where URI_ID =  101
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 192 n bits 72 table 
test/SLIDE_REVISION_PREDECESSOR index PRIMARY trx id 0 120070 lock mode S
Record lock, heap no 1 RECORD: info bits 0 0: len 9; hex 
73757072656d756d00; asc supremum.;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 45 n bits 312 table test/SLIDE_URI index 
PRIMARY trx id 0 120070 lock_mode X locks rec but not gap waiting
Record lock, heap no 245 RECORD: info bits 0 0: len 4; hex 8065; asc 
...e;; 1: len 6; hex 0001d2c4; asc ..;;
*** WE ROLL BACK TRANSACTION (2)

TRANSACTIONS

Trx id counter 0 120984
Purge done for trx's n:o < 0 120974 undo n:o < 0 0
Total number of lock structs in row lock hash table 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 120878, not started, process no 3112, OS thread id 200720
MySQL thread id 41, query id 16537 localhost 127.0.0.1 root
---TRANSACTION 0 120764, not started, process no 3111, OS thread id 196640
MySQL thread id 40, query id 16625 localhost 127.0.0.1 root
---TRANSACTION 0 120916, not started, process no 3110, OS thread id 192543
MySQL thread id 39, query id 17002 localhost 127.0.0.1 root
---TRANSACTION 0 120809, not started, process no 3108, OS thread id 184349
MySQL thread id 37, query id 16538 localhost 127.0.0.1 root
---TRANSACTION 0 120898, not started, process no 3107, OS thread id 180252
MySQL thread id 36, query id 17001 localhost 127.0.0.1 root
---TRANSACTION 0 120920, not started, process no 3106, OS thread id 176155
MySQL thread id 35, query id 17004 localhost 127.0.0.1 root
---TRANSACTION 0 120052, not started, process no 3105, OS thread id 172058
MySQL thread id 34, query id 11808 localhost 127.0.0.1 root
---TRANSACTION 0 120070, not started, process no 3095, OS thread id 131089
MySQL thread id 24, query id 12050 localhost 127.0.0.1 root
---TRANSACTION 0 119640, not started, process no 3087, OS thread id 98329
MySQL thread id 16, query id 10305 localhost 127.0.0.1 root
---TRANSACTION 0 119645, not started, process no 3086, OS thread id 94232
MySQL thread id 15, query id 10616 localhost 127.0.0.1 root
---TRANSACTION 0 120810, not started, process no 3085, OS thread id 90135
MySQL thread id 14, query id 16539 localhost 127.0.0.1 root
---TRANSACTION 0 120918, not started, process no 3084, OS thread id 86038
MySQL thread id 13, query id 17003 localhost 127.0.0.1 root
---TRANSACTION 0 119987, not started, process no 3082, OS thread id 77844
MySQL thread id 11, query id 11686 localhost 127.0.0.1 root
---TRANSACTION 0 119810, not started, process no 3081, OS thread id 73747
MySQL thread id 10, query id 11306 localhost 127.0.0.1 root
---TRANSACTION 0 119808, not started, process no 3080, OS thread id 69650
MySQL thread id 9, query id 11625 localhost 127.0.0.1 root
---TRANSACTION 0 120065, not started, process no 3077, OS thread id 57359
MySQL thread id 6, query id 11946 localhost 127.0.0.1 root
---TRANSACTION 0 120965, not started, process no 3075, OS thread id 49165
MySQL thread id 4, query id 16559 localhost 127.0.0.1 root
---TRANSACTION 0 120983, not started, process no 2967, OS thread id 45068
MySQL thread id 3, query id 17043 localhost root
---TRANSACTION 0 0, not started, process no 2960, OS thread id 40971
MySQL thread id 2, query id 17046 localhost root
SHOW INNODB STATUS
---TRANSACTION 0 118021, not started, process no 2955, OS thread id 36874
MyS

Re: mysql noob can't get msyslog to connect to mysql

[gerald_clark]

Scott H wrote:

I want to set up a central log server, doing this
on RH9. Using msyslog, a replacement for syslog
which can log to a mysql db.  I originally had
msyslogd logging successfully to mysql. Great,
but I want to be able to view my logs via http,
and after having trouble setting up apache with
PHP support and the tie-in to mysql, I decided to
use XAMPP (formerly LAMPP) ver 1.3, which
installs all the components I need apache, PHP,
mysql, PHPAdmin (and other items) as a package.
(I uninstalled mysql and apache first.)
Very nice but now I can't get msyslog to connect
to mysql. In the logs, I have:
om_mysql: sending messages to localhost, database
msyslog, table syslogTB.
om_mysql_write: Lost connection! [Can't connect
to local MySQL server through socket
'/var/lib/mysq
That last line is truncated in the log. I presume
it would complete with the socket file, which
would be defined in my /etc/my.conf:
That should be /etc/my.cnf

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
[mysql.server]
user=mysql
basedir=/var/lib
[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
Can anyone tell me where to start on this?
Thanks!!
Scott 

=
--
 



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

mysql noob can't get msyslog to connect to mysql

[Scott H]

I want to set up a central log server, doing this
on RH9. Using msyslog, a replacement for syslog
which can log to a mysql db.  I originally had
msyslogd logging successfully to mysql. Great,
but I want to be able to view my logs via http,
and after having trouble setting up apache with
PHP support and the tie-in to mysql, I decided to
use XAMPP (formerly LAMPP) ver 1.3, which
installs all the components I need apache, PHP,
mysql, PHPAdmin (and other items) as a package.
(I uninstalled mysql and apache first.)

Very nice but now I can't get msyslog to connect
to mysql. In the logs, I have:

om_mysql: sending messages to localhost, database
msyslog, table syslogTB.
om_mysql_write: Lost connection! [Can't connect
to local MySQL server through socket
'/var/lib/mysq

That last line is truncated in the log. I presume
it would complete with the socket file, which
would be defined in my /etc/my.conf:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

[mysql.server]
user=mysql
basedir=/var/lib

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Can anyone tell me where to start on this?
Thanks!!
Scott 

=
--

To announce that there must be no criticism of the President, or that we are to stand 
by the President, right or wrong, is not only unpatriotic and servile, but is morally 
treasonable to the American public.
  -- Theodore Roosevelt, 1918






.

__
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard
http://antispam.yahoo.com/whatsnewfree

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

Re: problem with query

[gerald_clark]

Leonardo Javier Bel? wrote:

>Hi ALL!
>I have a problem with this query, because it keeps failing and I dont
>know why (it says that the concat statement is wrong but there is nothing on
>the online docs...)
>
>select st.id, concat(st.required), st.name from states st, agenda ag left
>outer join ag.id=concat("AGE",st.required)  where st.type='AGE' and st.id>0;
>  
>
Did you mean:

select st.id, concat(st.required), st.name from states st left join  agenda ag
on ag.id=concat("AGE",st.required)  where st.type='AGE' and st.id>0;



>in this st.required is an int(7) and ag.id is a varchar(40).
>Thanks
>Leo.
>
>Mysql sql query select,
>
>
>  
>



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

Selecting data from one table that is not in another

[Agrin, Nathan]

I'm looking for a way to select data from one table that is not found in
another.  I am sorting on a column called 'model_id'.  Basically I want
something like this to work:
 
"SELECT t1.*, t2.model_id FROM data1 as t1, data2 as t2 WHERE
t1.model_id != t2.model_id"
 
t1 has about 1000 entries and t2 has around 500.  Unfortunately when I
run this SQL query mySQL seems to hang up and never produces any data.
I'm running this through PHP and trying to display the information
returned on a webpage.  If you need any other info in order to help me,
let me know.
 
Thanks in advance,
-Nate

Re: Installing 3.23.57 version without root privileges

[Michael Stassen]

Use the --prefix option to tell configure where mysql should go.  Add

  --prefix=$HOME/mysql

to your configure options.  You could change $HOME/mysql to any path 
where you have write permission.

Michael

Fernando wrote:
Hello,

I need to install 3.23.57 version (i've downloaded the source code
from the mysql page) in a system where i'm not the root.
How can i install it in my account without getting errors while doing
the make install? I get this message saying i can't write in the default
folder because i do not have the privileges.
Thanks in advance!


http://webmail.wanadoo.es. Tu correo gratuito, rápido y en español



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

Re: Installing 3.23.57 version without root privileges

[Gabriel Guzman]

On Tuesday 04 November 2003 10:49 am, Fernando wrote:
> Hello,
>
> I need to install 3.23.57 version (i've downloaded the source code from the
> mysql page) in a system where i'm not the root. How can i install it in my
> account without getting errors while doing the make install? I get this
> message saying i can't write in the default folder because i do not have
> the privileges.
>
> Thanks in advance!

you could try setting --prefix=/home/yourusername/test  (or some other dir you 
have write access to)
when you ./configure  your mysql source 

do a ./configure --help 
for other possible changes you might need to make. 

no guarantees, but it's a start,
gabe. 

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

Re: Lost Password

[Saqib Ali]

you can always reset the mysql root passwd
http://www.mysql.com/doc/en/Resetting_permissions.html


Saqib Ali
-
http://validate.sf.net <--- XHTML/HTML/DocBook Validator

On Tue, 4 Nov 2003, Carol Andrejak wrote:

> Hello Listers,
>
> I have a problem in that I cannot find my user id and password to log into mysql as 
> administrator. I can never remember passwords so I write them all down in a small 
> book however when I reinstalled mysql early last month, evidently I neglected to 
> write down the new user id and password in my book. I haven't touched telnet since 
> then.
>
> So now I can't log in to create a new user or do anything else. 
>
> Is it possible to sit down in front of the server logged in as root and find the 
> file that has this information and have a look at it? Maybe if I can see the user 
> names it'll jog my memory as to the password. I can try a couple to see if any work.
>
> If this is not possible, what recourse do I have? I don't want to mess with the 
> database and start over and I seriously don't want to have to do anything to mysql 
> as it took me two weeks to get it to work with the DBI perl module. Now that it's 
> working, I'm terrified to touch it in case it breaks. Thanks for any guidance. I 
> promise this time to not forget to write it down.
>
>
>
>  Carol Andrejak
>  Webmaster
>  Delaware State University
>  Grossley Hall Rm. 1
>  302-857-7045
>
>
>

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

Re: Unicode with MySQL4.1

[Jeremy March]

Oops--the Unicode character must have gotten lost in the email, but it does 
show up in MySQL.  Its an "O" with a line above it.

_
MSN Messenger with backgrounds, emoticons and more. 
http://www.msnmessenger-download.com/tracking/cdp_customize

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

SUMmarizing/GROUPing over UNION SELECT?

[Ivo]

Is it possible to summarize and group over a result set derived from 
several different tables using UNION SELECT?

For example, this is what I have:

Table1.field1

1
2
3
4
5
5
Table2.field1

1
2
3
3
And what I want to get is something like this:

1) SUM=29

2) A result set like this by grouping:

1
2
3
4
5
TIA,

Ivo Karindi



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

Re: Unicode with MySQL4.1

[Jeremy March]

You need to be sure you are using a Unicode font.  Are you using xterm?  If 
so you can go to the edit tab and click Current Profile to change the font.  
Also note that most Unicode fonts only cover subsets of Unicode not the 
whole thing so use a font that supports the language you are using.  I use 
the MiscFixed that comes with Red Hat and maybe other distributions too.

It works for me:

mysql> select convert(_ucs2 0x00d4 using utf8);
+--+
| convert(_ucs2 0x00d4 using utf8) |
+--+
| Ô   |
+--+
1 row in set (0.00 sec)
Jeremy

_
Is your computer infected with a virus?  Find out with a FREE computer virus 
scan from McAfee.  Take the FreeScan now! 
http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963

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

Installing 3.23.57 version without root privileges

[Fernando]

Hello,

I need to install 3.23.57 version (i've downloaded the source code from the mysql 
page) in a system where i'm not the root. 
How can i install it in my account without getting errors while doing the make 
install? I get this message saying i can't write in the default folder because i do 
not have the privileges.

Thanks in advance!



http://webmail.wanadoo.es. Tu correo gratuito, rápido y en español


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

Distributed- and/or mutli-server databases

[eM_gotcha]

Hi there :)

Can anyone recommend a MySQL book about distributed- and/or multi-server
databases?
Or even a general book about it, not written especially for MySQL?


thanks, chris.


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

Lost Password

[Carol Andrejak]

Hello Listers,
 
I have a problem in that I cannot find my user id and password to log into mysql as 
administrator. I can never remember passwords so I write them all down in a small book 
however when I reinstalled mysql early last month, evidently I neglected to write down 
the new user id and password in my book. I haven't touched telnet since then.
 
So now I can't log in to create a new user or do anything else. 
 
Is it possible to sit down in front of the server logged in as root and find the file 
that has this information and have a look at it? Maybe if I can see the user names 
it'll jog my memory as to the password. I can try a couple to see if any work.
 
If this is not possible, what recourse do I have? I don't want to mess with the 
database and start over and I seriously don't want to have to do anything to mysql as 
it took me two weeks to get it to work with the DBI perl module. Now that it's 
working, I'm terrified to touch it in case it breaks. Thanks for any guidance. I 
promise this time to not forget to write it down.



 Carol Andrejak   
 Webmaster
 Delaware State University  
 Grossley Hall Rm. 1
 302-857-7045   

[Stats] MySQL List: October 2003

[Bill Doerrfeld]

--
Searchable archives for this list are available at

--
==
MySQL List Stats
October, 2003
==
Note: Up/Down % as compared with September, 2003

Posts:   2042 (Up   0%)
Authors:  623 (Up   4%)
Threads:  720 (Down 3%)
Top 20 Contributors by Number of Posts
--
Paul DuBois 65
Director General: NEFACOMP  56
Dathan Vance Pattishall 47
Victoria Reznichenko46
Roger Baklund   38
Jeremy Zawodny  37
Matt W  35
Nitin   33
Heikki Tuuri30
gerald_clark30
Egor Egorov 29
Fortuno, Adam   24
Rory McKinley   23
Gabriel Ricard  22
Wang Feng   21
Dan Greene  21
Randy Chrismon  20
bluejack19
Chris Nolan 18
Jeff McKeon 16
Top 20 Threads by Number of Posts
--
printing reports23
MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1   17
MySQL not null vs MSAccess required 14
Challenging query   14
web interface...13
InnoDB or OS restriction?   13
Anyone using MySQL 4.x on Apple's G5?   13
Error 1045  12
Check for data before inserting 12
Mysql Performance Question  11
Really slow query (compared with Visual FoxPro) 11
DB not restoring from dump file 11
Is MySQL Relational? (was: Foreigner keys in MySQL?)11
can NOT drop the database   10
Views in MYSQL  10
Data from two tables in one query   10
How do I restrict a mysql user only can work on a database  10
how to export data from multiple tables  9
Easy (?) conditional SELECT  9
Installation problem 9
Top 20 Search Terms by Number of Requests
--
MySQL   30
database17
lasso   14
to  13
table   12
password11
log 10
index   10
ssl 10
data 9
4.0  9
Informix 8
server   8
in   8
copy 8
sql  8
count8
from 8
week 

Re: mysql 4.0.16/4.0.15a build fails on Mac OS X 10.3

[Gabriel Ricard]

Interestingly enough, when I configure 4.0.16 like this it builds ok:

./configure --prefix=/usr/local --sysconfdir=/ --enable-shared 
--with-server-suffix=-imax-G5  --with-mysqld-user=mysql --with-comment 
--with-query-cache  --without-docs --with-innodb 
--enable-thread-safe-client --with-bench --with-openssl 
--with-openssl-includes=/usr/include --with-openssl-libs=/usr/lib

It was the --with-raid option that caused my build problems.

Also, there are problems with configuring it with --with-openssl in 
Panther. Using just --with-openssl results in an error even though the 
includes are in /usr/includes/openssl and the libraries are in /lib. 
One problem seems to be that it is looking for libssl.a, which does not 
come with Panther, only libssl.dylib, so I changed the configure script 
to look for that and I was able to get it to build using 
--with-openssl-libs and --with-openssl-includes then.

- Gabriel

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

Re: InnoDB Questions

[Gabriel Ricard]

On Tuesday, November 4, 2003, at 07:58  AM, Leo Huang wrote:

In InnoDB documentation, it suggests to add another file ibdata2 to
get higher performance. Can I do that now, after I have created the
ibdata1 and used it for a while?
How exactly does this increase performance? Will InnoDB store some data 
in one data file and some in another (somewhat like RAID 1)?

- Gabriel

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

Re: InnoDB and raw tablespace

[Gabriel Ricard]

On Tuesday, November 4, 2003, at 11:25  AM, Harald Fuchs wrote:

In article <[EMAIL PROTECTED]>,
Mark Lubratt <[EMAIL PROTECTED]> writes:
I'm considering using the raw tablespace from InnoDB for a project I'm
working on.  I noticed a couple of years ago that there were reports
of tablespace corruption on Linux and these raw tablespaces.  Have
these problems been fixed?
Yes.  I'm using a raw disk for some months now, without any problems.

However, I've heard that it doesn't give the performance improvement
I'd expected.  Try it yourself.
I don't have specific numbers in front of me right now, but I tested 
the raw performance of InnoDB on a G5 running OSX 10.3 and it was 
actually worse than using regular files. I'll see if I can dig up the 
specific numbers.

- Gabriel

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

Re: mysqldump sorted

[Jeremy Zawodny]

On Tue, Nov 04, 2003 at 05:32:47PM +0100, Al Bogner wrote:
>
> Is it possible to sort the records, which are created with
> mysqldump? I didn't find an optioin in man mysqldump. If you cannot
> do it with mysqldump, what would be the best workaroud for it? into
> outfile?

Have you tried sneaking an ORDER BY clause into it with the "-w" flag?
I don't know if it works, but it's the first thing I'd try.

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

MySQL 4.0.15-Yahoo-SMP: up 51 days, processed 1,920,649,939 queries (429/sec. avg)

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

Re: Unicode with MySQL4.1

[srinivas reddy]

It seems to be working, but I am facing a glitch with
display.

I tried
select convert(_ucs2 0x0400 using utf8);
+--+
| convert(_ucs2 0x0400 using utf8) |
+--+
| ?|
+--+
1 row in set (0.00 sec)

Why is '?' displayed instead of the actual character?

Thanks,
Srinivas
--- Jeremy March <[EMAIL PROTECTED]> wrote:
> Remember that utf8 is not the same as the Unicode
> codepoints.  If you want 
> to enter utf8 like that you need to convert it from
> ucs2 first.  Try this:
> 
> SELECT convert(_ucs2 0x00d4 using utf8);
> 
> and to insert:
> 
> INSERT INTO your_table values (convert(_ucs2 0x00d4
> using utf8));
> 
> You can compare the character with its utf8
> hexidecimal:
> 
> SELECT hex(my_column), my_column FROM your_table;
> 
> Hope this helps,
> Jeremy March
> 
>
_
> MSN Shopping upgraded for the holidays!  Snappier
> product search... 
> http://shopping.msn.com
> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:   
>
http://lists.mysql.com/[EMAIL PROTECTED]
> 


=
I am not afraid of losing. But I don't like it.

__
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard
http://antispam.yahoo.com/whatsnewfree

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

Re: InnoDB and raw tablespace

[Mark Lubratt]

On Tuesday, November 4, 2003, at 10:25  AM, Harald Fuchs wrote:

In article <[EMAIL PROTECTED]>,
Mark Lubratt  writes:

I'm considering this option to keep database maintenance to a minimum
(running out of tablespace issues).  That way, InnoDB already owns all
the disk space and I don't have to continually be adding tablespace
files.
Huh?  What's wrong with ":autoextend"?

:autoextend works great until the 2GB file limit is reached.  Then you 
have to add another
autoextending tablespace file.  If I can just make a large raw 
tablespace, then I don't have to
bother with adding additional tablespace files every so often.

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

Re: Adapting a Select statement to MySQL

[Jim Bartram]

Looks like there's a bug in SQLyog -- when I restarted my machine this 
morning, the query worked.

Thanks,

-Jim

Nitin wrote:
query seems perfect, what error is this giving?

Nitin

- Original Message - 
From: "Jim Bartram" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, November 04, 2003 10:07 AM
Subject: Adapting a Select statement to MySQL



I've got the following select statement that is defeating me!

select title_id, ytd_sales from titles
where ytd_sales between 4095 and 12000;
The WHERE clause is not working... How should this be done in MySQL?

Thanks for any help,

-Jim



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

mysqldump sorted

[Al Bogner]

Is it possible to sort the records, which are created with mysqldump? I didn't 
find an optioin in man mysqldump. If you cannot do it with mysqldump, what 
would be the best workaroud for it? into outfile?

Al

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

RAND ()

[Payne]

Hi,

I have been playing around with RAND(). It works very well if I do a sql 
statement with mysql, but I having problem using with mysql statement 
with php. When a person calls on the page,  the same output is always 
view. How can I get RAND() work with php.

sorry for the php, stuff. Thanks for any help you can give.

Payne



  $db = mysql_connect("127.0.0.0","fred","mrbill");

  mysql_select_db("links",$db);

  $result = mysql_query("SELECT url FROM sponsors order by 
rand() LIMIT 1", $db);

 if ($myrow = mysql_fetch_array($result)) {

  echo"";
   do {
  printf("%s\n",$myrow[url]);
   } while ($myrow = mysql_fetch_array($result));
   } else {
   echo "Sorry, no message of day today";
 }

echo ""

?>



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

Re: Column data type maximums?

[KKolle]

Yes, probably due to limitations of the Control Center. I also tried BLOB and I
get the same results.

Thanks



   

  Brent Baisley

  <[EMAIL PROTECTED]To:   [EMAIL PROTECTED]
 
  om>  cc:   [EMAIL PROTECTED] 

   Subject:  Re: Column data type 
maximums?
  11/04/2003 10:21 

  AM   

   

   





text would be more than enough to hold your data. a text column is
"limited" to about 65K characters.

Your are probably running into some limitation of MySQL Control Center.
Maybe it's using GETs instead of PUTs. I haven't used Control Center,
but I have input text much longer than 1000 characters through
importing and the web via PHP. So the limitation is most likely with
Control Center, it is still beta.


On Tuesday, November 4, 2003, at 11:01 AM, [EMAIL PROTECTED] wrote:

> I'm interested in storing a lot of plain text. I know varchar2 has a
> limit of
> 255 characters. I want to store characters in excess of 800 and even
> closer to
> 1000.'
> I've tried varchar2, text and longtext. My size that I'm trying is 713
> characters, including spaces. But, it is only storing 673 characters.
> I'm
> initially just inserting the data manually using MySQL Control Center.
> I have to
> insert using a text file.
>
> How can I use a single data type to store large amounts of text? Or,
> do I have
> to break up the text?
> (I read that by using varchar, it can dynamically expand the space
> needed for
> storage. Is this correct? I tried varchar, but didn't work for me.)
>
> Thanks,
> Kevin
>
--
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: Column data type maximums?

[Brent Baisley]

text would be more than enough to hold your data. a text column is 
"limited" to about 65K characters.

Your are probably running into some limitation of MySQL Control Center. 
Maybe it's using GETs instead of PUTs. I haven't used Control Center, 
but I have input text much longer than 1000 characters through 
importing and the web via PHP. So the limitation is most likely with 
Control Center, it is still beta.

On Tuesday, November 4, 2003, at 11:01 AM, [EMAIL PROTECTED] wrote:

I'm interested in storing a lot of plain text. I know varchar2 has a 
limit of
255 characters. I want to store characters in excess of 800 and even 
closer to
1000.'
I've tried varchar2, text and longtext. My size that I'm trying is 713
characters, including spaces. But, it is only storing 673 characters. 
I'm
initially just inserting the data manually using MySQL Control Center. 
I have to
insert using a text file.

How can I use a single data type to store large amounts of text? Or, 
do I have
to break up the text?
(I read that by using varchar, it can dynamically expand the space 
needed for
storage. Is this correct? I tried varchar, but didn't work for me.)

Thanks,
Kevin
--
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: group by _date_

[Roger Baklund]

* [EMAIL PROTECTED]
[...]
> Aha, now I have SUM(ROUND(how_much,2)), this works so far.

You should swap these two function calls, the way you are doing it you are
summing up the rounded values, instead you should round the sum of the
values: ROUND(SUM(how_much),2)

--
Roger


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

Re: group by _date_

[Roger Baklund]

* [EMAIL PROTECTED]
[...]
> Does this mean I can not trust float, decimal etc. for financial purposes?

Yes you can, but you need to consider the precission of your calculations
and comparisons. How much is 1 dollar divided by 3, if you can only use two
digits for decimals? What do you do with the extra cent? (...put it in your
own account, if you work in a bank...;))

--
Roger


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

Column data type maximums?

[KKolle]

MySQL version - 4.0.13
MySQL Control Center - 0.9.2 beta

I'm interested in storing a lot of plain text. I know varchar2 has a limit of
255 characters. I want to store characters in excess of 800 and even closer to
1000.'
I've tried varchar2, text and longtext. My size that I'm trying is 713
characters, including spaces. But, it is only storing 673 characters. I'm
initially just inserting the data manually using MySQL Control Center. I have to
insert using a text file.

How can I use a single data type to store large amounts of text? Or, do I have
to break up the text?
(I read that by using varchar, it can dynamically expand the space needed for
storage. Is this correct? I tried varchar, but didn't work for me.)

Thanks,
Kevin



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

Re: group by _date_

[taraben . a]

Thanks,
Roger Baklund wrote:
* [EMAIL PROTECTED]

Hello,
I have a table bill like
when (date), how_much (float), what (varchar).
Now I store some bills on it - how_much will get numbers with 2 digits
after point.


Why do you think that? Note that FLOAT is an approximate numeric type. The
value you insert is stored in a binary format, and when you retrieve it, it
may have a slightly different value from what you inserted, like you have
observed.
Read more here:

http://www.mysql.com/doc/en/Problems_with_float.html >
http://www.mysql.com/doc/en/Numeric_types.html >
Aha, now I have SUM(ROUND(how_much,2)), this works so far.
Does this mean I can not trust float, decimal etc. for financial purposes?

Then I want to know how much money I spent each month.
SELECT SUM(how_much), YEAR(when), MONTH(when) FROM bill GROUP BY
(YEAR(when)*12+MONTH(when))


I don't understand this GROUP BY clause... the subject of this message
indicates that this too was an issue? I would have expected "GROUP BY
YEAR(when), MONTH(when)".
The result is the same in my query.

I expect to get numbers with not more than 2 digits after the point.
Surprisingly I get ??.25978 when I expect to get ??.26 or some else
strange numbers.


See the ROUND() function:

http://www.mysql.com/doc/en/Mathematical_functions.html#IDX1291 >

Note the C library dependency of this function, it may behave differently on
different computers.
I would store the how_much as pfennnigs or cents or whatever and use an
INTEGER type.
--
Roger
Thanks again,
Adib.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

problem with query

[Leonardo Javier Belén]

Hi ALL!
I have a problem with this query, because it keeps failing and I dont
know why (it says that the concat statement is wrong but there is nothing on
the online docs...)

select st.id, concat(st.required), st.name from states st, agenda ag left
outer join ag.id=concat("AGE",st.required)  where st.type='AGE' and st.id>0;

in this st.required is an int(7) and ag.id is a varchar(40).
Thanks
Leo.

Mysql sql query select,


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

temporary tables

[Mhpetzold]

I am having trouble creating temp tables.  An error message keeps coming up 
saying that @localhost does not exist.  (or something like that) can anyone 
help.

Thanks mhp

RE: Windows to Linux

[Christensen, Dave]

If you can mount the Windows partitions under LINUX, you can copy file files
from partition to partition.  If that's not possible, you'll have to export
the data under Windows to another location/media, then boot into LINUX and
load the data into your LINUX database.

You can dump the databases from the Windows edition by using:

Mysqldump --all-databases --add-drop-table -uuserid -ppassword >
mydumpfilename.sql

To reload you can then:

Mysql -uusername -ppassword < mydumpfilename.sql



-Original Message-
From: Matt Fletcher [mailto:[EMAIL PROTECTED] 
Sent: Monday, November 03, 2003 6:51 PM
To: [EMAIL PROTECTED]
Subject: Windows to Linux


Hi there,
I have taken the plunge and dropped windows in favour of linux. My 
question is what is the best way to get the data from my windows mysql 
databases into linux? Can I just copy some files from one partition to 
another or what?

Thanks,

Matt

-- 
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: InnoDB Questions

[Nitin]

You're right, it wont decrease the physical size, but only free up the space
within file to optimize the tablespace, in case, you want to check the size
of this data file, you can remove autoextend from:

innodb_data_file_path = ibdata1:10M:autoextend

and specify the size limit in the place of 10M, but i guess, if you specify
the size to less than 790M (which is the current size of your datafile), to
say 500M and the space is free in that file, it will resize it. That's the
behaviour of Oracle datafiles (believe me, i'm oracle certified!). try it,
and let me know as i dont have my database on innodb yet.

'Tablespace is part of your database. database consists of at least one
tablespace. it's basically used to restrict users from seeing other user's
data. like, you can assign a tablespace to a user and none else (ofcourse
other than root) can see the data.

For more info, have a look at:
http://www.mysql.com/doc/en/InnoDB_File_space.html

Enjoy
Nitin


- Original Message - 
From: "Leo Huang" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, November 04, 2003 8:00 PM
Subject: Re: InnoDB Questions


>
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> Thank you very much for your reply, Nitin.
>
> I did read the Adding_and_removing in the manual, but it says
> "Currently you cannot remove a datafile from InnoDB. To decrease the
> size of your database you have to use `mysqldump' to dump all your
> tables, create a new database, and import your tables to the new
> database."
> It does reduce the size of the database(similar to optimize for
> MyISAM), but it doesn't reduce the size of the file.
>
> Could you explain a bit what is a "tablespace"?
>
> Thanks,
> Leo
>
> /etc/my.cnf:
> [mysqld]
> datadir=/var/lib/mysql
> set-variable=max_connections=300
> innodb_data_file_path = ibdata1:10M:autoextend
> default-table-type=InnoDB
> set-variable = innodb_buffer_pool_size=512M
> set-variable = innodb_additional_mem_pool_size=10M
> set-variable = innodb_log_file_size=10M
> set-variable = innodb_log_files_in_group=3
> set-variable = innodb_log_buffer_size=8M
> innodb_flush_log_at_trx_commit=1
>
>
> [mysql.server]
> user=mysql
> basedir=/usr
>
> [safe_mysqld]
> err-log=/var/log/mysqld.log
> pid-file=/var/run/mysqld/mysqld.pid
>
>
>
> - - Original Message - 
> From: "Nitin" <[EMAIL PROTECTED]>
> To: "Leo Huang" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
> Sent: Wednesday, November 05, 2003 1:01 AM
> Subject: Re: InnoDB Questions
>
>
> > Hello,
> >
> > first things first, you cann't resize your datafiles without
> shutting down
> > your database. if it's ok with you, have a look at
> > http://www.mysql.com/doc/en/Adding_and_removing.html
> >
> > you may want to have a look at you my.cnf file, stored in mysql data
> dir or
> > in /etc dir, for the default options specified there fo the datafile
> with:
> >
> > innodb_data_file_path
> >
> > Yes, you can add data file, just add another entry to above option.
> option
> > entry is self-explainatory.
> >
> > At last, ibdata1, ibdata2 are actual data files used to store
> actual
> > data. one or more of these files are attached to one tablespace and
> one file
> > cant span across tablespaces.
> >
> > ib_logfile0, ib_logfile1 are log files, which are used to log sql
> statements
> > applied to database. these files are used to restore data in case of
> any
> > crash or mishap.
> >
> > for further info, have a look at:
> > http://www.mysql.com/doc/en/InnoDB_start.html
> >
> > Enjoy
> > Nitin
> >
> >
> > - Original Message - 
> > From: "Leo Huang" <[EMAIL PROTECTED]>
> > To: <[EMAIL PROTECTED]>
> > Sent: Tuesday, November 04, 2003 6:28 PM
> > Subject: InnoDB Questions
> >
> >
> > > Hello,
> > >
> > > I have a few questions about InnoDB.
> > > I am new to InnoDB, and just converted my MyISAM tables into
> InnoDB a
> > > few days ago.
> > > I notice that it generates these files
> > >
> > > -rw-rw1 mysqlmysql2560 Nov  2 13:07
> > > ib_arch_log_00
> > > -rw-rw1 mysqlmysql790626304 Nov  4 20:44 ibdata1
> > > -rw-rw1 mysqlmysql10485760 Nov  4 20:44
> ib_logfile0
> > > -rw-rw1 mysqlmysql10485760 Nov  4 20:44
> ib_logfile1
> > > -rw-rw1 mysqlmysql10485760 Nov  3 00:02
> ib_logfile2
> > >
> > > But how can those files work together?
> > >
> > > In InnoDB documentation, it suggests to add another file ibdata2
> to
> > > get higher performance. Can I do that now, after I have created
> the
> > > ibdata1 and used it for a while?
> > >
> > > The most important thing is I deleted a 300M database, but the
> ibdata1
> > > remains the same size. MyPHPAdmin says 330,000KB free. How can I
> make
> > > the data file smaller?
> > >
> > > I will be really appreciated if someone can briefly describe
> what's
> > > happening to those files or point me to some articles.
> > >
> > > Thanks a lot,
> > > Leo
> > >
> > >
> > > -- 
> > > MySQL General Mailing List
> > > For list 

Re: mysqlimport question

[Victoria Reznichenko]

"Cersosimo, Steve" <[EMAIL PROTECTED]> wrote:
> 
> Am I wrong to assume mysqlimport is supposed to emulate the LOAD DATA INFILE
> syntax?  I cannot find the command line option to turn on the CONCURRENT
> flag.
> 

CONCURRENT currently is not supported by mysqlimport.


-- 
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: InnoDB Questions

[Leo Huang]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Thank you very much for your reply, Nitin.

I did read the Adding_and_removing in the manual, but it says
"Currently you cannot remove a datafile from InnoDB. To decrease the
size of your database you have to use `mysqldump' to dump all your
tables, create a new database, and import your tables to the new
database."
It does reduce the size of the database(similar to optimize for
MyISAM), but it doesn't reduce the size of the file.

Could you explain a bit what is a "tablespace"?

Thanks,
Leo

/etc/my.cnf:
[mysqld]
datadir=/var/lib/mysql
set-variable=max_connections=300
innodb_data_file_path = ibdata1:10M:autoextend
default-table-type=InnoDB
set-variable = innodb_buffer_pool_size=512M
set-variable = innodb_additional_mem_pool_size=10M
set-variable = innodb_log_file_size=10M
set-variable = innodb_log_files_in_group=3
set-variable = innodb_log_buffer_size=8M
innodb_flush_log_at_trx_commit=1


[mysql.server]
user=mysql
basedir=/usr

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid



- - Original Message - 
From: "Nitin" <[EMAIL PROTECTED]>
To: "Leo Huang" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Wednesday, November 05, 2003 1:01 AM
Subject: Re: InnoDB Questions


> Hello,
>
> first things first, you cann't resize your datafiles without
shutting down
> your database. if it's ok with you, have a look at
> http://www.mysql.com/doc/en/Adding_and_removing.html
>
> you may want to have a look at you my.cnf file, stored in mysql data
dir or
> in /etc dir, for the default options specified there fo the datafile
with:
>
> innodb_data_file_path
>
> Yes, you can add data file, just add another entry to above option.
option
> entry is self-explainatory.
>
> At last, ibdata1, ibdata2 are actual data files used to store
actual
> data. one or more of these files are attached to one tablespace and
one file
> cant span across tablespaces.
>
> ib_logfile0, ib_logfile1 are log files, which are used to log sql
statements
> applied to database. these files are used to restore data in case of
any
> crash or mishap.
>
> for further info, have a look at:
> http://www.mysql.com/doc/en/InnoDB_start.html
>
> Enjoy
> Nitin
>
>
> - Original Message - 
> From: "Leo Huang" <[EMAIL PROTECTED]>
> To: <[EMAIL PROTECTED]>
> Sent: Tuesday, November 04, 2003 6:28 PM
> Subject: InnoDB Questions
>
>
> > Hello,
> >
> > I have a few questions about InnoDB.
> > I am new to InnoDB, and just converted my MyISAM tables into
InnoDB a
> > few days ago.
> > I notice that it generates these files
> >
> > -rw-rw1 mysqlmysql2560 Nov  2 13:07
> > ib_arch_log_00
> > -rw-rw1 mysqlmysql790626304 Nov  4 20:44 ibdata1
> > -rw-rw1 mysqlmysql10485760 Nov  4 20:44
ib_logfile0
> > -rw-rw1 mysqlmysql10485760 Nov  4 20:44
ib_logfile1
> > -rw-rw1 mysqlmysql10485760 Nov  3 00:02
ib_logfile2
> >
> > But how can those files work together?
> >
> > In InnoDB documentation, it suggests to add another file ibdata2
to
> > get higher performance. Can I do that now, after I have created
the
> > ibdata1 and used it for a while?
> >
> > The most important thing is I deleted a 300M database, but the
ibdata1
> > remains the same size. MyPHPAdmin says 330,000KB free. How can I
make
> > the data file smaller?
> >
> > I will be really appreciated if someone can briefly describe
what's
> > happening to those files or point me to some articles.
> >
> > Thanks a lot,
> > Leo
> >
> >
> > -- 
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]
> >
>
>
>
-BEGIN PGP SIGNATURE-
Version: PGP 8.0.2

iQA/AwUBP6e4GMJH0J7PNsMYEQIXIQCdGgQEyxFfJ3Vk8wZBNIz7FT7ilF8AoIDN
h21IQZ8ozOUeELhvWSpznyTI
=H/2E
-END PGP SIGNATURE-


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

Re: InnoDB Questions

[Nitin]

Hello,

first things first, you cann't resize your datafiles without shutting down
your database. if it's ok with you, have a look at
http://www.mysql.com/doc/en/Adding_and_removing.html

you may want to have a look at you my.cnf file, stored in mysql data dir or
in /etc dir, for the default options specified there fo the datafile with:

innodb_data_file_path

Yes, you can add data file, just add another entry to above option. option
entry is self-explainatory.

At last, ibdata1, ibdata2 are actual data files used to store actual
data. one or more of these files are attached to one tablespace and one file
cant span across tablespaces.

ib_logfile0, ib_logfile1 are log files, which are used to log sql statements
applied to database. these files are used to restore data in case of any
crash or mishap.

for further info, have a look at:
http://www.mysql.com/doc/en/InnoDB_start.html

Enjoy
Nitin


- Original Message - 
From: "Leo Huang" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, November 04, 2003 6:28 PM
Subject: InnoDB Questions


> Hello,
>
> I have a few questions about InnoDB.
> I am new to InnoDB, and just converted my MyISAM tables into InnoDB a
> few days ago.
> I notice that it generates these files
>
> -rw-rw1 mysqlmysql2560 Nov  2 13:07
> ib_arch_log_00
> -rw-rw1 mysqlmysql790626304 Nov  4 20:44 ibdata1
> -rw-rw1 mysqlmysql10485760 Nov  4 20:44 ib_logfile0
> -rw-rw1 mysqlmysql10485760 Nov  4 20:44 ib_logfile1
> -rw-rw1 mysqlmysql10485760 Nov  3 00:02 ib_logfile2
>
> But how can those files work together?
>
> In InnoDB documentation, it suggests to add another file ibdata2 to
> get higher performance. Can I do that now, after I have created the
> ibdata1 and used it for a while?
>
> The most important thing is I deleted a 300M database, but the ibdata1
> remains the same size. MyPHPAdmin says 330,000KB free. How can I make
> the data file smaller?
>
> I will be really appreciated if someone can briefly describe what's
> happening to those files or point me to some articles.
>
> Thanks a lot,
> Leo
>
>
> -- 
> 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: Adapting a Select statement to MySQL

[Director General: NEFACOMP]

Have you tried 
WHERE ytd_sales >= 4095 AND ytd_sales <=12000;

Also, you may try adding ( ).
They sometimes help.

Thanks
Emery
- Original Message - 
From: "Nitin" <[EMAIL PROTECTED]>
To: "Jim Bartram" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Tuesday, November 04, 2003 08:30
Subject: Re: Adapting a Select statement to MySQL


> query seems perfect, what error is this giving?
> 
> Nitin
> 
> - Original Message - 
> From: "Jim Bartram" <[EMAIL PROTECTED]>
> To: <[EMAIL PROTECTED]>
> Sent: Tuesday, November 04, 2003 10:07 AM
> Subject: Adapting a Select statement to MySQL
> 
> 
> > I've got the following select statement that is defeating me!
> >
> > select title_id, ytd_sales from titles
> > where ytd_sales between 4095 and 12000;
> >
> > The WHERE clause is not working... How should this be done in MySQL?
> >
> > Thanks for any help,
> >
> > -Jim
> >
> >
> >
> > -- 
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]
> >
> 
> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
> 
> 
> 



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

Re: MySQL vs .NET

[Haydies]

You can get Native database drivers for Delphi to talk to MySQL your
database back end makes no differance to any thing with Delphi

God know what perl has to do with any thing totaly not the same thing as
Delphi

Soap supports every thing, soap is language independant... and is fairly
cool. A bit like using CORBA and Delphi. Its only a XML based tansport
layer, and its used by .NET as I am sure William knows. Delphi 8 has the
best .Net support of any language, even better then VB and C++ but seeing as
MS payed borland to implment it, thats not suprising

I can how ever highly recomend soap, its exstreamly easy to use, though I
havn't tried it from Delphi. Its all the same. Havn't done .Net at all
other then the soap part, but if its like Corba (they do the same thing so
it should be) then its bloody easy as well. Its all about remote objects. I
would say though that Delphi isn't ideal for web services. Your better of
thinking about a move to a more web based technology.

I loved Delphi for 8 years, but alas, these days PHP is much more fun, works
well and is fast. If you do want to consider some thing other then Delphi to
do your thing then PHP kicks the hell out of Perl Poor old Delphi is a
dieing market here :-(

Other then that, really, Delphi to MySQL is just like Delphi to Paradox, or
Oracle or any thing else just get the drivers and off you go :-)


- Original Message - 
From: "Bill Hess" <[EMAIL PROTECTED]>
To: "William IT" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Tuesday, November 04, 2003 12:56 PM
Subject: Re: MySQL vs .NET


: If you are using Perl, take a look at SOAP::Lite - do not let the name
: fool you...   www.soaplite.com   Combining this with Apache and mod_perl
: and given Perl can directly interface with MySQL using Perl's DBI (and
: also the countless other modules available on CPAN) we have found this
: to be a very robust solution for a distributed and/or multi-tier system.
: SOAP::Lite supports MS .Net clients using VB/VC++  since .Net is
: pretty much MS's spin on the SOAP protocol, but there are some quirks...
:
: There are other SOAP variants for other languages like Java, Python,
: etc...  but not sure how these stack up...
:
:
: William IT wrote:
:
: > I am using Delphi 7 and MySql 4.0.16, since MS release .Net technology
and
: > also Delphi 8 .Net to provide web services application, I want to know
how
: > to make a web services application using Mysql? Or is there similiar
: > technology like .Net but using Mysql?
: >
: >
: >
:
: -- 
:
: Bill Hess
: Technology Resource Group
:
:
:
: -- 
: 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]

InnoDB Questions

[Leo Huang]

Hello,

I have a few questions about InnoDB.
I am new to InnoDB, and just converted my MyISAM tables into InnoDB a
few days ago.
I notice that it generates these files

-rw-rw1 mysqlmysql2560 Nov  2 13:07
ib_arch_log_00
-rw-rw1 mysqlmysql790626304 Nov  4 20:44 ibdata1
-rw-rw1 mysqlmysql10485760 Nov  4 20:44 ib_logfile0
-rw-rw1 mysqlmysql10485760 Nov  4 20:44 ib_logfile1
-rw-rw1 mysqlmysql10485760 Nov  3 00:02 ib_logfile2

But how can those files work together?

In InnoDB documentation, it suggests to add another file ibdata2 to
get higher performance. Can I do that now, after I have created the
ibdata1 and used it for a while?

The most important thing is I deleted a 300M database, but the ibdata1
remains the same size. MyPHPAdmin says 330,000KB free. How can I make
the data file smaller?

I will be really appreciated if someone can briefly describe what's
happening to those files or point me to some articles.

Thanks a lot,
Leo


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

Re: MySQL vs .NET

[Bill Hess]

If you are using Perl, take a look at SOAP::Lite - do not let the name 
fool you...   www.soaplite.com   Combining this with Apache and mod_perl 
and given Perl can directly interface with MySQL using Perl's DBI (and 
also the countless other modules available on CPAN) we have found this 
to be a very robust solution for a distributed and/or multi-tier system. 
   SOAP::Lite supports MS .Net clients using VB/VC++  since .Net is 
pretty much MS's spin on the SOAP protocol, but there are some quirks...

There are other SOAP variants for other languages like Java, Python, 
etc...  but not sure how these stack up...

William IT wrote:

I am using Delphi 7 and MySql 4.0.16, since MS release .Net technology and
also Delphi 8 .Net to provide web services application, I want to know how
to make a web services application using Mysql? Or is there similiar
technology like .Net but using Mysql?


--

Bill Hess
Technology Resource Group


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

Re: What information does "Extra" and "Comments" give in "show full fields"

[Egor Egorov]

Karam Chand <[EMAIL PROTECTED]> wrote:
> How can I add comment to a column?

Use COMMENT keyword:

CREATE TABLE test(
id int NOT NULL auto_increment COMMENT 'this is a comment',
PRIMARY KEY  (id)
) TYPE=MyISAM;

> 
> karam
> --- Egor Egorov <[EMAIL PROTECTED]> wrote:
>> Karam Chand <[EMAIL PROTECTED]> wrote:
>> > 
>> > I have 4.1.0-alpha-max-nt. When i issue a command
>> -
>> > 
>> > show full fields from tblname;
>> > 
>> > I get information about the fields in the table.
>> There
>> > are two columns i.e. EXTRA and COMMENTS which is
>> > always blank. I cant seem to figure out what
>> > information they provide. The SHOW FULL FIELDS...
>> in
>> > my MySQL Doc does not talk on this columns.
>> 
>> Extra shows if column has attribute AUTO_INCREMENT.
>> Comments shows comment on the column level. For
>> example:
>> 
>> mysql> show full fields from test;
>>
> +---+-+---+--+-+-++-+---+
>> | Field | Type| Collation | Null | Key | Default
>> | Extra  | Privileges  |
>> Comment   |
>>
> +---+-+---+--+-+-++-+---+
>> | id| int(11) | NULL  |  | PRI | NULL   
>> | auto_increment | select,insert,update,references |
>> this is a comment |
>>
> +---+-+---+--+-+-++-+---+
>> 1 row in set (0.01 sec)



-- 
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: What information does "Extra" and "Comments" give in "show full fields"

[Karam Chand]

How can I add comment to a column?

karam
--- Egor Egorov <[EMAIL PROTECTED]> wrote:
> Karam Chand <[EMAIL PROTECTED]> wrote:
> > 
> > I have 4.1.0-alpha-max-nt. When i issue a command
> -
> > 
> > show full fields from tblname;
> > 
> > I get information about the fields in the table.
> There
> > are two columns i.e. EXTRA and COMMENTS which is
> > always blank. I cant seem to figure out what
> > information they provide. The SHOW FULL FIELDS...
> in
> > my MySQL Doc does not talk on this columns.
> 
> Extra shows if column has attribute AUTO_INCREMENT.
> Comments shows comment on the column level. For
> example:
> 
> mysql> show full fields from test;
>
+---+-+---+--+-+-++-+---+
> | Field | Type| Collation | Null | Key | Default
> | Extra  | Privileges  |
> Comment   |
>
+---+-+---+--+-+-++-+---+
> | id| int(11) | NULL  |  | PRI | NULL   
> | auto_increment | select,insert,update,references |
> this is a comment |
>
+---+-+---+--+-+-++-+---+
> 1 row in set (0.01 sec)
> 
> 
> 
> 
> -- 
> 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]
> 


__
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard
http://antispam.yahoo.com/whatsnewfree

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

filemaker pro TO MySQL

[K C]

Hi,

  Want to import filemaker pro table data on Windows to  MySQL database on 
Linux.

Export from  filemaker pro could be made in the following formats.

SYLK files (*.slk), DBF files (*.dbf), DIF files (*.dif), Lotus 1-2-3 files 
(*.wk1), Basic files (*.bas), Merge files (*.mer), HTML table files (*.htm), 
FileMaker Pro files (.fp5), XML files (*.xml)

Which is the BEST format to import data into MySQL.

And could you please describe in few steps the process to import

the above format file into MySQL on Linux.

Thanks in Advance.

Regards,

Kumar.

_
Are you an Elvis fan? Want to visit Heartbreak Hotel? 
http://server1.msn.co.in/sp03/elvis/ Here's how you can win a trip!

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

Re: group by _date_

[Roger Baklund]

* [EMAIL PROTECTED]
> Hello,
> I have a table bill like
> when (date), how_much (float), what (varchar).
>
> Now I store some bills on it - how_much will get numbers with 2 digits
> after point.

Why do you think that? Note that FLOAT is an approximate numeric type. The
value you insert is stored in a binary format, and when you retrieve it, it
may have a slightly different value from what you inserted, like you have
observed.

Read more here:

http://www.mysql.com/doc/en/Problems_with_float.html >
http://www.mysql.com/doc/en/Numeric_types.html >

> Then I want to know how much money I spent each month.
> SELECT SUM(how_much), YEAR(when), MONTH(when) FROM bill GROUP BY
> (YEAR(when)*12+MONTH(when))

I don't understand this GROUP BY clause... the subject of this message
indicates that this too was an issue? I would have expected "GROUP BY
YEAR(when), MONTH(when)".

> I expect to get numbers with not more than 2 digits after the point.
> Surprisingly I get ??.25978 when I expect to get ??.26 or some else
> strange numbers.

See the ROUND() function:

http://www.mysql.com/doc/en/Mathematical_functions.html#IDX1291 >

Note the C library dependency of this function, it may behave differently on
different computers.

I would store the how_much as pfennnigs or cents or whatever and use an
INTEGER type.

--
Roger


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

Re: LOAD DATA hangs

[Eric Jain]

> Any idea what has gone wrong here? Yet another limitation I have run
> into?

Tried this:

1) CREATE TABLE
2) ALTER TABLE DISABLE KEYS
3) LOAD DATA -> Complete after 30 min, table size 5.78 GB, index size
1.21 GB.
4) ALTER TABLE ENABLE KEYS -> Puts a full load on the CPU, but neither
index nor table size seem to change, even after waiting for an hour.
Based on smaller data sets, I'd expect the index to reach something
close to 4 GB.

I'd appreciate any suggestions. Do I need to allocate more memory? Which
parameters? Surely I'm not the first to store more than a gigabyte of
data into a table with a few indexes :-)

--
Eric Jain


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

Re: What information does "Extra" and "Comments" give in "show full fields"

[Egor Egorov]

Karam Chand <[EMAIL PROTECTED]> wrote:
> 
> I have 4.1.0-alpha-max-nt. When i issue a command -
> 
> show full fields from tblname;
> 
> I get information about the fields in the table. There
> are two columns i.e. EXTRA and COMMENTS which is
> always blank. I cant seem to figure out what
> information they provide. The SHOW FULL FIELDS... in
> my MySQL Doc does not talk on this columns.

Extra shows if column has attribute AUTO_INCREMENT. Comments shows comment on the 
column level. For example:

mysql> show full fields from test;
+---+-+---+--+-+-++-+---+
| Field | Type| Collation | Null | Key | Default | Extra  | Privileges 
 | Comment   |
+---+-+---+--+-+-++-+---+
| id| int(11) | NULL  |  | PRI | NULL| auto_increment | 
select,insert,update,references | this is a comment |
+---+-+---+--+-+-++-+---+
1 row in set (0.01 sec)




-- 
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: Heap table in replication,bug?

[Victoria Reznichenko]

"MaFai" <[EMAIL PROTECTED]> wrote:
> Hello, Victoria Reznichenko,
> 
> The p_showing table,the following is the create table sql statement.
> 
> 
> | p_showing | CREATE TABLE `p_showing` (
>  `showing_timestamp` timestamp(14) NOT NULL,
>  `showing_channel_name` varchar(50) NOT NULL default '',
>  `showing_asset_name` varchar(50) NOT NULL default '',
>  `showing_start_time` datetime default NULL,
>  `showing_keywords` varchar(100) default NULL,
>  `showing_ip` varchar(15) NOT NULL default '',
>  `showing_port` varchar(10) NOT NULL default '',
>  `showing_end_time` datetime default NULL,
>  `showing_resource` varchar(20) NOT NULL default '',
>  `showing_status` varchar(20) NOT NULL default '',
>  `showing_AutoDelte` varchar(5) NOT NULL default '',
>  `showing_Feed` varchar(20) NOT NULL default '',
>  `showing_Interactive_Control` varchar(5) NOT NULL default '',
>  `showing_start_time_ctime` datetime default NULL,
>  `showing_end_time_ctime` datetime default NULL,
>  `showing_URL` varchar(100) NOT NULL default '',
>  `source_ip` varchar(15) default NULL
> ) TYPE=HEAP |
> 
> Mysql Version
> 
> 
> mysqladmin  Ver 8.40 Distrib 4.0.12, for pc-linux on i686
> Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
> This software comes with ABSOLUTELY NO WARRANTY. This is free software,
> and you are welcome to modify and redistribute it under the GPL license
> 
> Server version  4.0.12-standard-log
> Protocol version10
> Connection  Localhost via UNIX socket
> UNIX socket /tmp/mysql.sock
> Uptime: 1 day 5 hours 9 min 2 sec
> 
> Threads: 4  Questions: 1005973  Slow queries: 22  Opens: 102  Flush tables: 1  Open 
> tables: 78  Queries per second avg: 9.586
> 

I tested using your table structure and replication worked well. Please, upgrade MySQL 
server to 4.0.16 and let me know if the problem still exists.

> 
> 
> 
> === At 2003-11-03, 14:51:00 you wrote: ===
> 
>>"MaFai" <[EMAIL PROTECTED]> wrote:
>>> Hello, mysql,
>>> 
>>> The replication running smoothly between the master and slave,except that the heap 
>>> table can not be synchronized.
>>> 
>>> While the master insert the record into the heap table,the slave would do the same 
>>> job.
>>> While the master delete the record in the heap table,the slave wouldn't do so.
>>> After serval days passed,the heap table would overloading in the slave,because it 
>>> never delete the record in heap table.
>>> 
>>> The mysql seems doesn't support heap table in replication,right?
>>> If yes,i should change the table type to innodb,although it would lose the 
>>> performance.
>>> 
>>> The manaul doesn't mention this.
>>> 
>>> Any idea apprecated.
>>> 
>>> Env:Mysql 4.0,Linux Red Hat7,256mb ram.
>>> 
>>
>>What exactly version of MySQL do you use? I tested replication with HEAP tables and 
>>all worked like a charm. 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: mysql memory usage

[Alexis Guia]

Hi,

I think that MyISAM uses the key buffer only if needed. The same happens
with almost all the other buffers (read buffer, sort buffer, etc.). 

;)
Alexis


-Original Message-
From: Benjamin KRIEF [mailto:[EMAIL PROTECTED] 
Sent: segunda-feira, 3 de Novembro de 2003 21:00
To: [EMAIL PROTECTED]
Subject: mysql memory usage

hi everyone.

i'd like to know if mysql always uses all the key_buffer size it has
been
given in my.cnf

especially, on my server with :

set-variable= thread_stack=128K
set-variable= key_buffer=200M
set-variable= max_allowed_packet=1M
set-variable= table_cache=128
set-variable= sort_buffer=4M
set-variable= net_buffer_length=8K

top shows this :

21:39:49 up 134 days,  3:00,  1 user,  load average: 21.82, 24.32, 21.84
Tasks: 375 total,  20 running, 355 sleeping,   0 stopped,   0 zombie
Cpu(s):  83.3% user,  16.7% system,   0.0% nice,   0.0% idle
Mem:901156k total,   823388k used,77768k free, 6360k buffers
Swap:  1951888k total, 3376k used,  1948512k free,   711876k cached

PID   USER   PR  NI  VIRT  RES  SHR S %CPU %MEMTIME+  COMMAND
18598 mysql  20   0 54524  53m 2300 R 10.0  6.1   1:35.66 mysqld
(329 lines like this one, except for the %CPU column)

obviously , my server (dual pIII 1Ghz) is a bit exhausted.

but my point is :

mysql is threaded, so i guess the whole mysql size is 54524
(swap+physical).
why doesn't mysql use the key_buffer size ?
mysqladmin variables show the good key_buffer_size value.
mytop says my key efficiency is 99.54%.

maybe it has something to do with the database files size?

bye.


*
Benjamin KRIEF * Directeur Technique *  IGUANE Studio
*



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


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

FW: performance while creating indexes

[Alexis Guia]

Hi,

When creating indices, MyIsam needs a big sort buffer (See MySQL Manual:
" myisam_sort_buffer_size: The buffer that is allocated when sorting the
index when doing a REPAIR or when creating indexes with CREATE INDEX or
ALTER TABLE. ").

I don't know how works ISAM tables, but you can try setting your sort
buffer to a high value.

;)
Alexis

-Original Message-
From: William Baker [mailto:[EMAIL PROTECTED] 
Sent: segunda-feira, 3 de Novembro de 2003 19:37
To: [EMAIL PROTECTED]
Subject: Re: performance while creating indexes

Now why didn't I think of a single alter tablethat should certainly 
improve things.  I'll give it a try.

bbaker

>
>
> William Baker wrote:
>
>> I am using a pentium4-2GHz machine with Linux-RH9 installed and 1GB 
>> RAM.  The database is on a dedicated SCSI drive with an Adaptec 
>> UltraScsi3 controller which shows 40MHz bus connecting the 10K-RPM 
>> disks.  (Fairly new, fairly capable, low-end server grade.)
>>
>> I have a 2GB datafile with 10 indexes.  Each of those indexes takes 
>> about 1.5 hrs to build (total of 15 hours).  Any suggestions for 
>> reducing build time ... preferrably to around 10 minutes or less?  I 
>> could even live with 20 minutes for each.  (Our current system uses 
>> ISAM style indexed data files.  It has a utility that can rebuild the

>> indexes for this file in about an hour, so the performance I am 
>> looking for should be possible on this hardware.)  I am using innodb 
>> and have raised buffer limits with the /etc/my.cnf method and 
>> verified that the new parameters were indeed loaded using "show 
>> variables".  Indexes have several segments, ie, a typical index 
>> definition:
>>
>> CREATE UNIQUE INDEX `TRANSACT_INDEX03` ON `TRANSACT` (`LOC`, 
>> `PRODUCT`, `ITEM_NO`, `TRAN_DATE`, `RECNUM`)
>>
>> This is a generic 4.0.16 server.  During the last 6 months we have 
>> been testing our applications and I have tried different 
>> configurations on different disks and OS's.  The 1.5 hrs/index is 
>> fairly typical.
>>
>> Copy of my.cfg follows.
>>
>> bbaker
>>
>>
>> [mysqld]
>> datadir=/raid/db
>> socket=/raid/mysql/mysql.sock
>>
>> set-variable = innodb_buffer_pool_size=128M
>> set-variable = innodb_additional_mem_pool_size=20M
>> innodb_log_group_home_dir=/raid/db
>> innodb_log_arch_dir=/raid/db
>> #set-variable = innodb_log_files_in_group=3
>> #set-variable = innodb_log_file_size=32M
>> #set-variable = innodb_log_buffer_size=8M
>>
>> innodb_flush_log_at_trx_commit=0
>> set-variable = innodb_lock_wait_timeout=50
>>
>> skip-locking
>> set-variable = max_connections=200
>> set-variable = read_buffer_size=8M
>> set-variable = sort_buffer=8M
>> set-variable = key_buffer_size=256M
>>
>> [mysql.server]
>> user=mysql
>> basedir=/raid/db
>>
>> [safe_mysqld]
>>
>> [mysql.server]
>> user=mysql
>> basedir=/raid
>>
>> [safe_mysqld]
>> err-log=/raid/mysqld.log
>> pid-file=/var/run/mysqld/mysqld.pid
>>
>>
>>
>>
> We add all our indicies at once with a single alter table command.
> We are using MYISAM tables, though.
>



-- 
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: minus operation

[Nitin]

In oracle, intersect will work for you better than minus. as it shows data
from both tables which doesn't exist in another one.

anyway in mysql, you can try this:

select A.* from A left join B on (f1, f2, f3, f4) where B.f1 is null and
B.f2 is null and B.f3 is null and B.f4 is null;

hope it helps
Nitin

- Original Message - 
From: "fab" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, November 04, 2003 1:58 PM
Subject: minus operation


> Hi to all,
>
> My question is rather about sql langage than mysql.
>
> Here it is: i have 2 tables (A and B) with the same structure (4 fields
> called f1,f2,f3,f4).
> Table A counts x records and table B counts y records (and x <> y).
> Then, table A has records that do not belong to table B and table B has
> records that do not belong to table A.
>
> My question is: how to get the records that belongs to A and not to B
> and the records that belongs to B and not to A.
>
> If i was using oracle, i guess i could use the minus sql command. More
> over, i don't think i can use a simple left join because i have to
> verify the 4 fields and not only the first one.
>
> If anyone could answer, it would be great. Actualy, i lost lots of
> records and i try to rebuild my table with the help of the savings of
> this table.
>
> f.
>
>
>
>
> -- 
> 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]

group by _date_

[taraben . a]

Hello,
I have a table bill like
when (date), how_much (float), what (varchar).
Now I store some bills on it - how_much will get numbers with 2 digits 
after point.

Then I want to know how much money I spent each month.
SELECT SUM(how_much), YEAR(when), MONTH(when) FROM bill GROUP BY 
(YEAR(when)*12+MONTH(when))

I expect to get numbers with not more than 2 digits after the point. 
Surprisingly I get ??.25978 when I expect to get ??.26 or some else 
strange numbers.

Where is my mistake ??
I already googel-t and browsed the archive, no success.
Thanks,

Adib.

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

minus operation

[fab]

Hi to all,

My question is rather about sql langage than mysql.

Here it is: i have 2 tables (A and B) with the same structure (4 fields 
called f1,f2,f3,f4).
Table A counts x records and table B counts y records (and x <> y). 
Then, table A has records that do not belong to table B and table B has 
records that do not belong to table A.

My question is: how to get the records that belongs to A and not to B 
and the records that belongs to B and not to A.

If i was using oracle, i guess i could use the minus sql command. More 
over, i don't think i can use a simple left join because i have to 
verify the 4 fields and not only the first one.

If anyone could answer, it would be great. Actualy, i lost lots of 
records and i try to rebuild my table with the help of the savings of 
this table.

f.



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

MySQL vs .NET

[William IT]

I am using Delphi 7 and MySql 4.0.16, since MS release .Net technology and
also Delphi 8 .Net to provide web services application, I want to know how
to make a web services application using Mysql? Or is there similiar
technology like .Net but using Mysql?



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

compiler errors including

[thomas . siebert]

I work with MSVC60 on Win98se with mysql-4.0.16-win.

I got a project that compile and link. The mysql-include folder is set
as a standard include folder and I link the project against
mysqlclient.lib.

If I include  in some of my source-files I got this errors from
the compiler:


Konfiguration: VerleihNix - Win32 Debug
Kompilierung läuft...
VNApplication.cpp
c:\mysql\include\mysql_com.h(116) : error C2146: Syntaxfehler : Fehlendes ';' vor 
Bezeichner 'fd'
c:\mysql\include\mysql_com.h(116) : error C2501: 'SOCKET' : Fehlende Speicherklasse 
oder Typbezeichner
c:\mysql\include\mysql_com.h(116) : error C2501: 'fd' : Fehlende Speicherklasse oder 
Typbezeichner
c:\mysql\include\mysql_com.h(180) : error C2065: 'SOCKET' : nichtdeklarierter 
Bezeichner
c:\mysql\include\mysql_com.h(180) : error C2146: Syntaxfehler : Fehlendes ')' vor 
Bezeichner 's'
c:\mysql\include\mysql_com.h(181) : error C2059: Syntaxfehler : ')'
Fehler beim Ausführen von cl.exe.

VerleihNix.exe - 6 Fehler, 0 Warnung(en)


I do not use any mysql-code! I only include the mysql.h.


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

InnoDB and raw tablespace

[Mark Lubratt]

I'm considering using the raw tablespace from InnoDB for a project I'm 
working on.  I noticed a couple of years ago that there were reports of 
tablespace corruption on Linux and these raw tablespaces.  Have these 
problems been fixed? I'm considering running it on a hardware RAID 
(stripes of mirrors, I forget if that's RAID 10, or RAID 01).  Should I 
use FreeBSD instead of Linux?

I'm considering this option to keep database maintenance to a minimum 
(running out of tablespace issues).  That way, InnoDB already owns all 
the disk space and I don't have to continually be adding tablespace 
files.

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