date:20040223

pls help ...............

2004-02-23 Thread CurlyBraces Technologies \( Pvt \) Ltd




hi , 
 
when i try to get mysql data to the web browser via 
php , always it is showing 
Parse error: parse error, unexpected T_LNUMBER, expecting 
',' or ';' in /var/www/html/smsc/test1.php on line 14
error message. but i tried to rectify the essage , 
i couldn't.
can some body help me to solve the problem 
?
 
thanx in advance
 
curlys
-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

WG: [mysql.com #3839] UTF8 and MySQL 4.1

2004-02-23 Thread Bernhard Geyer

Hi,

I want to add better support for Unicode in our application.

Currently we use the default charset of the application, but code our data
to UTF8 (It's a Delphi-Application with a native Component to connect to
MySQL). This works fine also for MySQL 3.23 with some limitations (No
sorting-support, no chance to use the wildcard '_', ...)

But I want to enhance our application so we can use the UTF8-Capabilities of
MySQL 4.1

1, I've creat the tables with UTF8-Saving (DEFAULT CHARACTER SET utf8)

2, I've added to commands when getting the connection to MySQL.

   SET NAMES 'utf8'
   SET CHARACTER SET utf8

as descriped unter http://www.mysql.com/doc/en/Charset-connection.html

But this will stop our UTF8-Support. I can't write utf8-coded Data to the
database.
Nearly all Texts will be saved corrupted to the Database (Some characters
are replaced with ?)

I'm using the MySQL 4.1.1 unter Windows XP.

Whats wrong?


Best regards

Bernhard Geyer


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

Re: privileges question

2004-02-23 Thread Jim Richardson

On Mon, Feb 23, 2004 at 05:30:38PM -0600, Paul DuBois wrote:
At 15:18 -0800 2/23/04, Jim Richardson wrote:
Installing a new RHEL box, with 3.23.58. (it's what RH provides, and I
don't feel like using stuff from out of the RH tree.) I am a little
confused about the privileges. I have done a GRANT ALL on *.* to root
IDENTIFIED BY 'password'; which is great, but if I run mysql so
mysql -u [EMAIL PROTECTED]

I get in, no password. How can I tell mysql to refuse anything that
isn't from localhost, and to require a password for root no matter
what?  Thanks. New to MySQL and databases in general, trying to make
sure I don't screw up too badly.
When you specify an account name for the GRANT statement, if you
specify only the username part, the hostname part defaults to '%'.  So
your GRANT statement is actually equivalent to GRANT ... TO 'root'@'%'.
Clobber that account and specify GRANT ... TO 'root'@'localhost'
instead.  Then root will be able to connect only from the localhost.


Thanks for your help. I realize now I was misunderstanding something,
that anyone can log into mysql from localhost, but they can't *do* much
of anything except with test_ databases. That was part of my problem,
the rest was answered with your post. Thanks. Now back to reading the
MySQL book, the author's name seems familiar... :) 

--
Jim Richardson http://www.eskimo.com/~warlock
"$HOME is where your dotfiles are"
- Gym Quirk
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

about PHP user interface

2004-02-23 Thread Togochog Enhebatu

hi,

I am new in PHP, and I would like to create some user interface with PHP. 
Could anyone tell me what is the easiest way to do this?

thanks a lot,

Enhebatu

_
Store more e-mails with MSN Hotmail Extra Storage  4 plans to choose from! 
http://click.atdmt.com/AVE/go/onm00200362ave/direct/01/

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

migration database

2004-02-23 Thread Elly Wisata

Hello 

I would like to ask how to migrate my table in database SQL Server to MYSQL.
The field name and data type mostly are different from the one I've prepared
in MYSQL. Could somebody show me the way or any recommended website which
discussing about this? For additional information, I am using MS SQL Server
Enterprise Manager and MySQL CC. Thanks in advance.

 

Elly

question about number of processes.

2004-02-23 Thread Randy Paries

Hello
 
I have a number of servers.
 
I have one running RH7.3 with mysql-3.23.56-1.73
one running RH9 with mysql-3.23.58-1.9
 
My question is when i do a ps -ef
 
on RH7.3 a get a number of /usr/libexec/mysqld
on RH9 i only get one.
 
can someone please explain why. does RH9 display thread differently?
 
 
BTW, they appear to both work ok, i am just trying to understand
 
Thanks for any insight.


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

RE: Can't create a new thread (errno 11)

2004-02-23 Thread Donny Simonton

Kevin,
As I mentioned earlier, set this in you're my.cnf and restart mysql.  It has
helped me on many occasions.

open-files-limit=36864

Donny

> -Original Message-
> From: Kevin Marks [mailto:[EMAIL PROTECTED]
> Sent: Monday, February 23, 2004 6:27 PM
> To: [EMAIL PROTECTED]
> Subject: Re: Can't create a new thread (errno 11)
> 
> 
> On Feb 23, 2004, at 10:36 AM, Sasha Pachev wrote:
> 
> > Christof Egner wrote:
> >> Hello James,
> >> thanks for your answer, but max_connections is set high enough
> >> (double the number of the currently used threads). I think the error
> >> would be "Too many connections", wouldn't it?
> >
> > This error comes from LinuxThreads when pthread_create() fails for one
> > reason or another. There could be several reasons - out of RAM, out of
> > address space, low compiled max thread limit, library compatibility,
> > or just a bug in LinuxThreads. If you are not using the official MySQL
> > binary, try it. If you are, try compiling your own - sometimes the
> > official binary does not quite get along with the local system
> > libraries or configuration.
> >
> > max_connections is just a sanity limit MySQL imposes on itself, and if
> > the thread library does not cooperate, it may stop allowing new
> > connections before reaching it.
> >
> 
> I'm seeing this too - the db gets into a state where it can no longer
> make new threads, and then mysql restart will fail, and only a reboot
> will fix it.
> 
> This is with mysql 4.0.16 on debian 2.4.20
> 
> 
> 
> --
> 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 versus MS SQL

2004-02-23 Thread Udikarni

Chris,

We run a large data warehouse with tables similar to yours. We basically gave up on 
indexing and the overhead involved and just tablescan. The key is to partition the 
data using a concept called Merge Tables. However, since we currently use Oracle, 
eager to migrate to MySQL - I don't have concrete MySQL stats on this concept. 

Theoretically speaking - here's the anticipated math if you were to try and set this 
up on MySQL Merge Tables (I made some assumptions about your table).

120,000,000 rows * 400 fields * 5 bytes/field average = 250 GB. Using our server as an 
example - we read at 800 MB/s so worse case you can read the entire table in 5 
minutes. However, if you partition your data by ZIP (which appears in all but 1 of 
your queries) you would only read those relevant slices, vastly reducing your run 
time. The one query which doesn't have zip has STATE so you can have a ZIP/STATE 
conversion table that generates a list of zips in leu of state and modify the query to 
use it - and again enjoy the benefits of partitioning.

If most of your queries use zip and most of them go after a small slice of the table 
you will probably be under 1 minute response time most of the time. Worse case would 
be 5 minutes. No indexes to maintain on insert, or rebuild from scratch.

Udi


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

Re: [4.1.1] 1062 errors on non-unique index during data load

2004-02-23 Thread Steven Roussey

I saw something like this as well. Using 4.1.2 made it go away. Try doing a
bk pull of the dev version of 4.1.2 and give it a go.

-steve--



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

Re: Can't create a new thread (errno 11)

2004-02-23 Thread Kevin Marks

On Feb 23, 2004, at 10:36 AM, Sasha Pachev wrote:

Christof Egner wrote:
Hello James,
thanks for your answer, but max_connections is set high enough 
(double the number of the currently used threads). I think the error 
would be "Too many connections", wouldn't it?
This error comes from LinuxThreads when pthread_create() fails for one 
reason or another. There could be several reasons - out of RAM, out of 
address space, low compiled max thread limit, library compatibility, 
or just a bug in LinuxThreads. If you are not using the official MySQL 
binary, try it. If you are, try compiling your own - sometimes the 
official binary does not quite get along with the local system 
libraries or configuration.

max_connections is just a sanity limit MySQL imposes on itself, and if 
the thread library does not cooperate, it may stop allowing new 
connections before reaching it.

I'm seeing this too - the db gets into a state where it can no longer 
make new threads, and then mysql restart will fail, and only a reboot 
will fix it.

This is with mysql 4.0.16 on debian 2.4.20



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

Re: HEAP tables vs MYISAM on ramdisk

2004-02-23 Thread Matt W

Hi Mark,

- Original Message -
From: "Mark Maunder"
Sent: Monday, February 23, 2004 4:17 PM
Subject: Re: HEAP tables vs MYISAM on ramdisk


> 411 is packed with features I'm dying to have on my production server,
> but I had it on my dev box, and I got some table corruption which,
> admittedly, I was too lazy to try to reproduce. So I've downgraded to
> production 4 again. I have a heavily updated fulltext index which may
be
> the root of the evil.

Yeah, there are a few bugs with full-text indexes in 4.1.1!  Hopefully
4.1.2 (which should be out soon I think) will be ready to rock in
production. :-)

Here's the 4.1.1 full-text bugs I know of:

http://bugs.mysql.com/1977
http://bugs.mysql.com/2190
http://bugs.mysql.com/2417

And with multi-byte character sets:

http://bugs.mysql.com/2033
http://bugs.mysql.com/2065


Matt


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

Re: run query second time

2004-02-23 Thread Matt W

Hi Mike,


- Original Message -
From: "Mike Mapsnac"
Sent: Monday, February 23, 2004 5:49 PM
Subject: run query second time


> Hello
>
> Today I run large query. It took more than 1 minute to start printing
the
> results. The output was about 5 rows. However, when I run the
query
> second time it took a couple of seconds before printing the results.
Why is
> that? Because data still in the RAM?
>
> Thanks

Yep, that's usually why. :-)


Matt


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

run query second time

2004-02-23 Thread Mike Mapsnac

Hello

Today I run large query. It took more than 1 minute to start printing  the 
results. The output was about 5 rows. However, when I run the query 
second time it took a couple of seconds before printing the results. Why is 
that? Because data still in the RAM?

Thanks

_
Take off on a romantic weekend or a family adventure to these great U.S. 
locations. http://special.msn.com/local/hotdestinations.armx

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

Re: Binary Log replay utility?

2004-02-23 Thread Jeremy Zawodny

On Mon, Jan 05, 2004 at 09:12:24AM +1300, Sam Vilain wrote:
> No-one out there doing step-by-step replay of binary logs ?

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

MySQL 4.0.15-Yahoo-SMP: up 162 days, processed 2,059,153,930 queries (146/sec. avg)

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

RE: Changing default delimiter

2004-02-23 Thread Paul DuBois

At 17:17 -0500 2/23/04, David Perron wrote:
Thanks Garth.  Actually Im trying to change the delimiter in the context of
running a sql file from the command line - so in this case, I would want to
change from tab to csv
 mysql -h db#-reports.adz -u readonly -p < script.sql > output.csv ads12
You can't do it.  You'll need to produce your datafile some other way,
or else postprocess the output from mysql to transform it from tab-delimited
to CSV format.  Here's a short fragment from MySQL Cookbook on the subject:
% mysql db_name < inputfile  \
| sed -e 's/"/""/g' -e 's/TAB/","/g' -e 's/^/"/' -e 's/$/"/' > outputfile
That's  fairly  cryptic,  to say the least.  You can achieve
the same result with other languages that may be  easier  to
read.   Here's  a short Perl script that does the same thing
as the sed command (it converts tab-delimited input  to  CSV
output), and includes comments to document how it works:
 #! /usr/bin/perl -w
 while (<>)  # read next input line
 {
 s/"/""/g;   # double any quotes within column values
 s/\t/","/g; # put `","' between column values
 s/^/"/; # add `"' before the first value
 s/$/"/; # add `"' after the last value
 print;  # print the result
 }
 exit (0);
If you name the script csv.pl, you can use it like this:

% mysql db_name < inputfile  | csv.pl > outputfile


-Original Message-
From: Garth Webb [mailto:[EMAIL PROTECTED]
Sent: Monday, February 23, 2004 5:01 PM
To: [EMAIL PROTECTED]
Subject: Re: Changing default delimiter
I'm not sure in what context you want to change the delimiter, but here's
the doc page on the LOAD DATA command which shows how to terminate (a.k.a.
delimit) fields with different characters:
http://www.mysql.com/doc/en/LOAD_DATA.html

On Mon, 2004-02-23 at 13:53, David Perron wrote:
 Greetings -
 Been scouring the docs for this all day and I come up with nothing for
 'delimiter' - Id like to change the default delimiter in mysql to
something
 other than tab.
 Is there a command to do this, and what are the options?
 Thank you!

 > David


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

Re: privileges question

2004-02-23 Thread Paul DuBois

At 15:18 -0800 2/23/04, Jim Richardson wrote:
Installing a new RHEL box, with 3.23.58. (it's what RH provides, and I
don't feel like using stuff from out of the RH tree.) I am a little
confused about the privileges. I have done a GRANT ALL on *.* to 
root IDENTIFIED BY 'password';
which is great, but if I run mysql so

mysql -u [EMAIL PROTECTED]

I get in, no password. How can I tell mysql to refuse anything that
isn't from localhost, and to require a password for root no matter what?
Thanks. New to MySQL and databases in general, trying to make sure I
don't screw up too badly.
When you specify an account name for the GRANT statement, if you specify
only the username part, the hostname part defaults to '%'.  So your
GRANT statement is actually equivalent to GRANT ... TO 'root'@'%'.
Clobber that account and specify GRANT ... TO 'root'@'localhost' instead.
Then root will be able to connect only from the localhost.
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
MySQL Users Conference: April 14-16, 2004
http://www.mysql.com/uc2004/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Concat. operator ||

2004-02-23 Thread Paul DuBois

At 17:20 -0600 2/23/04, Paul DuBois wrote:
At 12:50 -0600 2/23/04, Paul DuBois wrote:
At 15:59 +0200 2/23/04, Victoria Reznichenko wrote:
Hassan Shaikh <[EMAIL PROTECTED]> wrote:
 I am running MySQL in ANSI standard mode. However the following
 statement is giving unexpected result. Am I missing something?
mysql> select 'This is a' || ' test string';
+---+
| 'This is a' || ' test string' |
+---+
| 0 |
+---+
1 row in set (0.00 sec)
 Here's a part of my my.ini, indicating that I am in ANSI mode.

 # The MySQL server
 [mysqld]
 port=3306
 #socket=MySQL
 skip-locking
 set-variable= sql-mode=ansi
This should work, but when I try it, it doesn't work.  (The sql_mode
variable is set to 4, which is the numeric equivalent of the ANSI_QUOTES
option.  I believe what's happening is that there is an attempt to match
prefixes of option names that is done incorrectly.)
I'll file a bug report.  In the meantime, you can use this instead:

[mysqld]
ansi
Oops, sql-mod=ansi not working isn't a bug after all, at least not if you're
using MySQL 4.x.  The ansi shortcut was added in 4.1.1:
http://www.mysql.com/doc/en/Server_SQL_mode.html


Er, that should be: "at least not if you're using MYSQL 4.0.x".

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

Re: Concat. operator ||

2004-02-23 Thread Paul DuBois

At 12:50 -0600 2/23/04, Paul DuBois wrote:
At 15:59 +0200 2/23/04, Victoria Reznichenko wrote:
Hassan Shaikh <[EMAIL PROTECTED]> wrote:
 I am running MySQL in ANSI standard mode. However the following
 statement is giving unexpected result. Am I missing something?
mysql> select 'This is a' || ' test string';
+---+
| 'This is a' || ' test string' |
+---+
| 0 |
+---+
1 row in set (0.00 sec)
 Here's a part of my my.ini, indicating that I am in ANSI mode.

 # The MySQL server
 [mysqld]
 port=3306
 #socket=MySQL
 skip-locking
 set-variable= sql-mode=ansi
This should work, but when I try it, it doesn't work.  (The sql_mode
variable is set to 4, which is the numeric equivalent of the ANSI_QUOTES
option.  I believe what's happening is that there is an attempt to match
prefixes of option names that is done incorrectly.)
I'll file a bug report.  In the meantime, you can use this instead:

[mysqld]
ansi
Oops, sql-mod=ansi not working isn't a bug after all, at least not if you're
using MySQL 4.x.  The ansi shortcut was added in 4.1.1:
http://www.mysql.com/doc/en/Server_SQL_mode.html



That should produce the desired effect.

 > set-variable= key_buffer=32M
 set-variable= max_allowed_packet=1M
 set-variable= table_cache=32
 set-variable= net_buffer_length=8K
 set-variable= sort_buffer=1M
 set-variable= record_buffer=512K
 set-variable= myisam_sort_buffer_size=4M
 set-variable= thread_cache=8
 # Try number of CPU's*2 for thread_concurrency
 set-variable= thread_concurrency=8
 log-bin
 server-id= 1
What version of MySQL do you use?
If you use version < 4.1 , you should put in the my.cnf
[mysqld]
ansi
...
or

[musqld]
sql-mode=REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY
transaction-isolation=SERIALIZABLE
..
--
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]


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


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

privileges question

2004-02-23 Thread Jim Richardson



Installing a new RHEL box, with 3.23.58. (it's what RH provides, and I
don't feel like using stuff from out of the RH tree.) I am a little
confused about the privileges. I have done a 
GRANT ALL on *.* to root IDENTIFIED BY 'password';
which is great, but if I run mysql so

mysql -u [EMAIL PROTECTED]

I get in, no password. How can I tell mysql to refuse anything that
isn't from localhost, and to require a password for root no matter what? 

Thanks. New to MySQL and databases in general, trying to make sure I
don't screw up too badly. 

--
Jim Richardson http://www.eskimo.com/~warlock
"It says he made us all to be just like him.  So if we're dumb, then
god is dumb, and maybe even a little ugly on the side."
   -- Frank Zappa
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Which MySQL???

2004-02-23 Thread mos

At 04:40 PM 2/23/2004, you wrote:
Hello List:

This is the basic of the basic question. I have recently installed and
tested MySQL and am happy with it. Now I want to setup a production server.
I notice that there are two MySQL products listed on the MySQL org site (1)
MySQL & (2) MaxDB MySQL.
My question is; which of these two products to use? I personally like to use
MaxDB but I am not sure why is it better than regular MySQL?
Thanks.

Kirti

Kirti,
If you don't know why you need MaxDB, then you don't really need 
it. MySQL is better supported and is likely faster (and cheaper). There 
has been very little talk of MaxDB on this mailing list because it is for 
big iron machines (SAP).

Unless you're in a corporate environment and you think you need an 
enterprise solution (like Oracle) then you may want to try MaxDB.

http://searchenterpriselinux.techtarget.com/originalContent/0,289142,sid39_gci937988,00.html
http://sniptools.com/vault/maxdb-the-enhanced-sap-database-from-mysql.htm
http://mysql.bigbiz.com/products/maxdb/
Mike 

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

Re: MySql and php

2004-02-23 Thread Ligaya Turmelle

There are some excellent tutorials for PHP at www.codewalkers.com under the
tutorials/basics section.  You can sort the fields with SQL or with PHP (see
"Sorting Database Results with PHP") and in the Display section of the
tutorials are "Alternating row colors with PHP and MySQL" and "Multicolumn
output from a database with PHP".  If you have questions on the PHP their
are multiple forums to go to for help.  I personally use www.phphelp.com.

Good luck

Respectfully,
Ligaya Turmelle

""CurlyBraces Technologies ( Pvt ) Ltd"" <[EMAIL PROTECTED]> wrote in
message news:[EMAIL PROTECTED]
hi ,
i have a problem with Mysql Vs php . Can somebody help me to sort out this
problem ?

I wrote a php script to connet mysql data base and get some data from some
table of the database. It works well ..

1data1data2
5cccxcx
3aaa123
2klklopop
4opoppop

I want do the sort for the fields while its showing from the web. and in the
third data field i want to add some colors while its running in the html .

can some body help me to do the scripting plz ?

thanx in advace

curlys



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

rpm upgrade file conflict

2004-02-23 Thread Jim Austin

Hello Folks,
Am trying to upgrade MySQL with rpms.  rpm -Uvh 
MySQL-shared-compat-4.0.17-0i386.rpm worked fine.  When I try to upgrade 
the client or server I get warnings that the files conflict with the 
older versions that are currently installed.  I've tried to erase the 
older versions with rpm -e to no avail. What should I be doing to get a 
clean installation?
Thanks for the help!
Jim

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

Re: UTF-8

2004-02-23 Thread Ligaya Turmelle

version 4.1 and up are the versions to be unicode enabled.

Ligaya Turmelle

""Beau Hartshorne"" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
Hi,

I am about to start using UTF-8 for my internal data storage and display
for a CMS that I'm building. Are there any issues that I should be aware
of related to mysql and storing UTF-8 encoded characters? What versions
of mysql offer full UTF-8 support, and are there any weird caveats with
the implementation?

Thank you,

Beau




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

Re: MySQL Administrator - Startup Variables

2004-02-23 Thread Matt Silva

Oh we are using Red Hat Enterprise Linux WS ver 3.0

Thanks
Matt
Thomas Taylor wrote:

what platform are you on?
On Feb 23, 2004, at 4:27 PM, Matt Silva wrote:
I am using the ~new~ MySQL Administrator and i'm trying to load the 
Startup Variables and i'm receiving a "Could
not find my.cnf" error.  my.cnf is located in the /etc directory and 
I have temporarily relaxed the permissions to
777.  Is there something that i'm missing here?  Or is there a bug in 
the Administrator program?

Thanks
Matt
--
Matt Silva
Empower Software Technologies, LLC
27851 Bradley Rd. Suite 120
Sun City, CA 92586
PH: (909) 672-6257
WB: www.storagecommander.com
EM: [EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]


Thanks,
Thomas Taylor
17106 76th Ave
Maple Grove, MN 55311
[EMAIL PROTECTED]


--
Matt Silva
Empower Software Technologies, LLC
27851 Bradley Rd. Suite 120
Sun City, CA 92586
PH: (909) 672-6257
WB: www.storagecommander.com
EM: [EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

UTF-8

2004-02-23 Thread Beau Hartshorne

Hi,

I am about to start using UTF-8 for my internal data storage and display
for a CMS that I'm building. Are there any issues that I should be aware
of related to mysql and storing UTF-8 encoded characters? What versions
of mysql offer full UTF-8 support, and are there any weird caveats with
the implementation?

Thank you,

Beau 



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

Which MySQL???

2004-02-23 Thread Kirti S. Bajwa

Hello List:

This is the basic of the basic question. I have recently installed and
tested MySQL and am happy with it. Now I want to setup a production server.
I notice that there are two MySQL products listed on the MySQL org site (1)
MySQL & (2) MaxDB MySQL.

My question is; which of these two products to use? I personally like to use
MaxDB but I am not sure why is it better than regular MySQL? 

Thanks.

Kirti
 

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

MySQL Administrator - Startup Variables

2004-02-23 Thread Matt Silva

I am using the ~new~ MySQL Administrator and i'm trying to load the 
Startup Variables and i'm receiving a "Could
not find my.cnf" error.  my.cnf is located in the /etc directory and I 
have temporarily relaxed the permissions to
777.  Is there something that i'm missing here?  Or is there a bug in 
the Administrator program?

Thanks
Matt
--
Matt Silva
Empower Software Technologies, LLC
27851 Bradley Rd. Suite 120
Sun City, CA 92586
PH: (909) 672-6257
WB: www.storagecommander.com
EM: [EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Changing default delimiter

2004-02-23 Thread David Perron

Thanks Garth.  Actually Im trying to change the delimiter in the context of
running a sql file from the command line - so in this case, I would want to
change from tab to csv

 mysql -h db#-reports.adz -u readonly -p < script.sql > output.csv ads12

-Original Message-
From: Garth Webb [mailto:[EMAIL PROTECTED] 
Sent: Monday, February 23, 2004 5:01 PM
To: [EMAIL PROTECTED]
Subject: Re: Changing default delimiter

I'm not sure in what context you want to change the delimiter, but here's
the doc page on the LOAD DATA command which shows how to terminate (a.k.a.
delimit) fields with different characters:

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

On Mon, 2004-02-23 at 13:53, David Perron wrote:
> Greetings -
> Been scouring the docs for this all day and I come up with nothing for
> 'delimiter' - Id like to change the default delimiter in mysql to
something
> other than tab.
> Is there a command to do this, and what are the options?
> 
> Thank you!
> 
> David
> 
> 
-- 

 |- Garth Webb   -|
 |- [EMAIL PROTECTED] -|

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

Re: HEAP tables vs MYISAM on ramdisk

2004-02-23 Thread Mark Maunder

411 is packed with features I'm dying to have on my production server,
but I had it on my dev box, and I got some table corruption which,
admittedly, I was too lazy to try to reproduce. So I've downgraded to
production 4 again. I have a heavily updated fulltext index which may be
the root of the evil. 

The last 3 paragraphs of this doc give me the impression that key
caching helps with updates too:
http://www.mysql.com/doc/en/MyISAM_key_cache.html

The strange thing is that I have a 128 meg key_buffer on my server, but
I still get a huge speed increase by moving the MYI file of my heavily
utilized fulltext index table to a ramdisk. I suppose a cache by
definition can't be as efficient as if the entire index were being
accessed directly from memory. But the MYI file is only 14 megs, so the
entire thing should be cacheable.

On Mon, 2004-02-23 at 19:47, Eric B. wrote:
> Index caches are new to 4.1.x, but key caches have been around for a while.
> Definitely in 4.0, can't remember about 3.x.  Either way though, I don't see
> either helping with inserts or updates.  Only with queries.
> 
> MySQL dsadoes suggest using a seperate key cache for temporary tables though:
> http://www.mysql.com/doc/en/Multiple_key_caches.html
> 
> I haven't tried 4.11 yet (I'm just about to d/l it), but would expect it to
> be pretty stable.
> 
> Eric
> 
> 
> "Mark Maunder" <[EMAIL PROTECTED]> wrote in message
> news:[EMAIL PROTECTED]
> > The table I'm using is non-critical data, so it's not really an issue
> > for me. But I was browsing through the mysql manual looking for a way to
> > rebuild an MYI file from the .frm and MYD file (is there a way?) when I
> > came across this:
> >
> > http://www.mysql.com/doc/en/CACHE_INDEX.html
> >
> > Index caches are only available in MySQL 4.11 unfortunately, so perhaps
> > my ramdisk idea is a workaround for index caching until 411 is stable?
> >
> > On Mon, 2004-02-23 at 15:34, Eric B. wrote:
> > > How are you ensuring syncronization between the ram disk and the HD?  Is
> > > there a writeback / writethrough mechanism for ram disks?  Are you not
> > > risking major data loss if ever you have a power failure or PC failure?
> > >
> > > Thanks for the info!
> > >
> > > Eric
> > >
> > > "Mark Maunder" <[EMAIL PROTECTED]> wrote in message
> > > news:[EMAIL PROTECTED]
> > > > Since HEAP tables don't support fulltext indexes, is moving MYISAM
> > > > tables to ramdisk an acceptable workaround?
> > > >
> > > > On Sat, 2004-02-21 at 18:35, Mark Maunder wrote:
> > > > > I've noticed a 4 times insert speed improvement by moving the MYI
> index
> > > > > file of a myisam table to a ramdisk. The MYD file is still on a
> physical
> > > > > disk, and I benchmarked the difference between moving just the index
> > > > > file, or moving both, and it was only a 10% difference in speed. The
> > > > > table has a large fulltext index.
> > > > >
> > > > > Has anyone else played with moving MYI files to ramdisk for
> performance?
> > > > > Any caveats that you know of, besides running out of ramdisk space?
> > > > >
> > > > >
> >
> >
> >
> > -- 
> > 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]

Create database error

2004-02-23 Thread Colin O'Connor

Hi,

I'm bundling MySQL with a Java app, and it is currently working on Windows
and Mac OS X. Right now I'm porting it to Solaris.

The problem I'm having is when I try to create a database ("CREATE DATABASE
mydb") I get the error: "ERROR 1006: Can't create database 'mydb'. (errno:
2)"

Using perror, "errno: 2" means "No such file or directory".

Does anyone know why it might be giving me that error?

Thanks,
Colin


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

Re: Changing default delimiter

2004-02-23 Thread Garth Webb

I'm not sure in what context you want to change the delimiter, but
here's the doc page on the LOAD DATA command which shows how to
terminate (a.k.a. delimit) fields with different characters:

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

On Mon, 2004-02-23 at 13:53, David Perron wrote:
> Greetings - 
> Been scouring the docs for this all day and I come up with nothing for
> 'delimiter' - Id like to change the default delimiter in mysql to something
> other than tab.
> Is there a command to do this, and what are the options?
> 
> Thank you!
> 
> David
> 
> 
-- 

 |- Garth Webb   -|
 |- [EMAIL PROTECTED] -|


signature.asc
Description: This is a digitally signed message part

Changing default delimiter

2004-02-23 Thread David Perron

Greetings - 
Been scouring the docs for this all day and I come up with nothing for
'delimiter' - Id like to change the default delimiter in mysql to something
other than tab.
Is there a command to do this, and what are the options?

Thank you!

David

encoding question

2004-02-23 Thread mmckenzie





I wrote a cold fusion script to migrate data from an access 2000 db to a
mysql 4.0.18 db  and when I display the data with cold fusion I see these
little box characters in different spots.

I'm assuming that it's an issue with the encoding.  How do I correct this?

Thanks.


Mitchell S. McKenzie
Programmer
Learning Environment & Internet Services
University of Nebraska Medical Center
Phone: 402-559-9306
Cell Phone: 402-708-1493

University of Nebraska Medical Center E-mail Confidentiality Disclaimer :
The information in this e-mail is privileged and confidential, intended
only for the use of the addressee(s) above.
Any unauthorized use or disclosure of this information is prohibited.
If you have received this e-mail by mistake please delete it and
immediately contact the sender.


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

Re: Any way to pre-load an Index file into the key cache?

2004-02-23 Thread Eric B.

Thanks!

One question though - in the Index Preloading section, it says:
"If there are enough blocks in a key cache to hold blocks of an entire
index, or at least the blocks corresponding to its non-leaf nodes...".

How can I determine the number of blocks in an index, or the number of
blocks for the non-leaf nodes?  Is there anything that will help me
determine these values?

Thanks!

Eric

"Paul DuBois" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> At 13:35 -0500 2/23/04, Eric B. wrote:
> >Hi,
> >
> >I know this question has been posed in the past, but I haven't been able
to
> >find an answer for it yet.
> >
> >Is there any way in MySQL to pre-load a table's index file into the key
> >cache at startup?
>
> Yes, this is something that was implemented recently:
>
> http://www.mysql.com/doc/en/MyISAM_key_cache.html
>
>
> >   I know the key cache will build itself while exectuing
> >queries, but this will obviously slow down the first query that would
> >otherwise be using the index.  Short of manually executing all the
possible
> >queries (not a realistic option) before the system is enabled, does MySQL
> >offer a start-up option that would pre-load the index table into its key
> >cache such that the first queries do not suffer from the index loading?
> >
> >Of course, I realize that the key cache memory would need to be large
enough
> >to handle such a table.
>
>
> -- 
> Paul DuBois, MySQL Documentation Team
> Madison, Wisconsin, USA
> MySQL AB, www.mysql.com
>
> MySQL Users Conference: April 14-16, 2004
> http://www.mysql.com/uc2004/
>
> -- 
> 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: HEAP tables vs MYISAM on ramdisk

2004-02-23 Thread Eric B.

Index caches are new to 4.1.x, but key caches have been around for a while.
Definitely in 4.0, can't remember about 3.x.  Either way though, I don't see
either helping with inserts or updates.  Only with queries.

MySQL does suggest using a seperate key cache for temporary tables though:
http://www.mysql.com/doc/en/Multiple_key_caches.html

I haven't tried 4.11 yet (I'm just about to d/l it), but would expect it to
be pretty stable.

Eric


"Mark Maunder" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> The table I'm using is non-critical data, so it's not really an issue
> for me. But I was browsing through the mysql manual looking for a way to
> rebuild an MYI file from the .frm and MYD file (is there a way?) when I
> came across this:
>
> http://www.mysql.com/doc/en/CACHE_INDEX.html
>
> Index caches are only available in MySQL 4.11 unfortunately, so perhaps
> my ramdisk idea is a workaround for index caching until 411 is stable?
>
> On Mon, 2004-02-23 at 15:34, Eric B. wrote:
> > How are you ensuring syncronization between the ram disk and the HD?  Is
> > there a writeback / writethrough mechanism for ram disks?  Are you not
> > risking major data loss if ever you have a power failure or PC failure?
> >
> > Thanks for the info!
> >
> > Eric
> >
> > "Mark Maunder" <[EMAIL PROTECTED]> wrote in message
> > news:[EMAIL PROTECTED]
> > > Since HEAP tables don't support fulltext indexes, is moving MYISAM
> > > tables to ramdisk an acceptable workaround?
> > >
> > > On Sat, 2004-02-21 at 18:35, Mark Maunder wrote:
> > > > I've noticed a 4 times insert speed improvement by moving the MYI
index
> > > > file of a myisam table to a ramdisk. The MYD file is still on a
physical
> > > > disk, and I benchmarked the difference between moving just the index
> > > > file, or moving both, and it was only a 10% difference in speed. The
> > > > table has a large fulltext index.
> > > >
> > > > Has anyone else played with moving MYI files to ramdisk for
performance?
> > > > Any caveats that you know of, besides running out of ramdisk space?
> > > >
> > > >
>
>
>
> -- 
> 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: [4.1.1] 1062 errors on non-unique index during data load

2004-02-23 Thread mark warren bracher

Victoria Reznichenko wrote:
[snip]
Could you create repeatable test case (table structure (output of SHOW CREATE TABLE) and text file with data that will be enough to reproduce the above behavior)?
I'll see if I can get something generic to error out.  there's pretty 
much no way I'll be able to send my actual data file though...

- mark

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

Re: Master refusing Replication connections

2004-02-23 Thread Sasha Pachev

Bruce Dembecki wrote:
Help, I seem to be running into a problem with replication which up until
now has served us well.
We run mysql servers in pairs, with each server in a pair mastering off the
other. So for example mysql1 masters off mysql2, which masters off mysql1.
Friday morning one server stopped accepting connections for replication,
let's call it mysql2. It's partner, called mysql1, has some log entries
which seem on the face of it self explanatory...
040221 15:35:40  Slave I/O thread: error connecting to master
'[EMAIL PROTECTED]:3306': Error: 'Access denied for user:
'[EMAIL PROTECTED]' (Using password: YES)'  errno: 1045  retry-time:
60  retries: 86400
Try on the slave:

CHANGE MASTER TO MASTER_PASSWORD='correct_password'

I suspect the master.info file somehow got corrupted and it is reading the 
password incorrectly. Take a look at your master.info file, first the good one 
to know what it is supposed to look like, and then the bad one.

If that is the case, you could have a more serious problem - the offset and the 
name of the master log could have also gotten corrupted and you would have to 
reset the replication altogether.

--
Sasha Pachev
Create online surveys at http://www.surveyz.com/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

MySql and php

2004-02-23 Thread CurlyBraces Technologies \( Pvt \) Ltd




hi , 
i have a problem with Mysql Vs php . Can somebody 
help me to sort out this problem ?
 
I wrote a php script to connet mysql data base and 
get some data from some table of the database. It works well ..
 
1    data1    
data2
5    ccc    
    xcx
3    aaa    
123
2    klkl    
opop
4    opop    
pop
 
I want do the sort for the fields while its showing 
from the web. and in the third data field i want to add some colors while its 
running in the html .
 
can some body help me to do the scripting plz 
?
 
thanx in advace
 
curlys
-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: MySQL versus MS SQL

2004-02-23 Thread beacker

Chris Fossenier writes:
... Query 1
>  a1.phone_pander_flag <> 'Y'
>  AND state.state = 'PA'
>  AND ( h1.homeowner = 'Y' 
>   OR h2.probable_homeowner IN ('8','9') 
>   OR h2.homeowner_probability_model BETWEEN '080' AND '102' )
>  AND ( p1c.exact_age BETWEEN '40' AND '60'
>   OR estimated_age BETWEEN '40' AND '60' )
>  AND a1.phone is not null
>  AND p1a.first is not null
>  AND p1a.last is not null
>  AND a3.address is not null;

Chris,
 Have you been able to get an 'explain plan' on the query?  I'd
estimate that the combination of state (1 of 50), exact_age (20 of 80),
and estimated_age (20 of 80) should reduce you possible record search
from the original 160M to 200,000 (1/50 * 20/80 * 20/80 * 160M) if all
these indexes can be utilized.  The real question is what MySQL chooses
to use as far as indexes are concerned.
   Brad Eacker ([EMAIL PROTECTED])

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

RE: Setting variables on update

2004-02-23 Thread emierzwa

Try this...works on 4.1.2

UPDATE  ControlTable SET NextID= 1 + (select @prevval := NextID)

Ed

-Original Message-
From: Sasha Pachev [mailto:[EMAIL PROTECTED] 
Sent: Monday, February 23, 2004 11:19 AM
To: Matt Fagan
Cc: [EMAIL PROTECTED]
Subject: Re: Setting variables on update


Matt Fagan wrote:
> Hello,
> 
> I'm trying to run an SQL update statement like:
> 
> UPDATE ControlTable SET @prevval := NextID, NextID = NextID
> + 1
> 
> I've tried using a range of different syntax, but nothing
> seems to work. This is something that does work in another
> SQL server I used to use.
> 
> Does anybody know if this is something MySQL can do, and if
> so, what is the correct syntax? Note that I can't use an
> autoincrement column because the IDs aren't being put into
> a database.
> 
> I've searched through MySQL docs and mailing lists but
> couldn't find any mention of this. Any help would be
> appreciated.

A UDF or your own static function might work. Worst case, MySQL source
can be 
modified to make this work.


-- 
Sasha Pachev
Create online surveys at http://www.surveyz.com/

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


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

Re: HEAP tables vs MYISAM on ramdisk

2004-02-23 Thread Mark Maunder

The table I'm using is non-critical data, so it's not really an issue
for me. But I was browsing through the mysql manual looking for a way to
rebuild an MYI file from the .frm and MYD file (is there a way?) when I
came across this:

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

Index caches are only available in MySQL 4.11 unfortunately, so perhaps
my ramdisk idea is a workaround for index caching until 411 is stable?

On Mon, 2004-02-23 at 15:34, Eric B. wrote:
> How are you ensuring syncronization between the ram disk and the HD?  Is
> there a writeback / writethrough mechanism for ram disks?  Are you not
> risking major data loss if ever you have a power failure or PC failure?
> 
> Thanks for the info!
> 
> Eric
> 
> "Mark Maunder" <[EMAIL PROTECTED]> wrote in message
> news:[EMAIL PROTECTED]
> > Since HEAP tables don't support fulltext indexes, is moving MYISAM
> > tables to ramdisk an acceptable workaround?
> >
> > On Sat, 2004-02-21 at 18:35, Mark Maunder wrote:
> > > I've noticed a 4 times insert speed improvement by moving the MYI index
> > > file of a myisam table to a ramdisk. The MYD file is still on a physical
> > > disk, and I benchmarked the difference between moving just the index
> > > file, or moving both, and it was only a 10% difference in speed. The
> > > table has a large fulltext index.
> > >
> > > Has anyone else played with moving MYI files to ramdisk for performance?
> > > Any caveats that you know of, besides running out of ramdisk space?
> > >
> > >



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

Re: large insert with load

2004-02-23 Thread Sasha Pachev

Bernd Jagla wrote:
Hi,

we have a big problem with inserting large amounts of data (2GB) into our MyISAM databank (version 4.0.12).
The dataset  is loaded in using 
LOAD DATA infile 'bla' ignore into table tab fields terminated by ' ' optionally enclosed by '' (int1, int2, int3, date);

The format of the text file is...
int1 int2 int3 'date' 

...with white spaces between each field.  After loading the data, there seems to be inconsistencies between the text-input data and the data in the mySQL table.

The problem seems to be localized to int3 and date, which shows integers that have 20 appended to the original data.  (Ie.  if int3 is 15, it would be in the database as 2015)  and date is '-00-00'.

Any suggestions?
Check the syntax after optionally enclosed, make sure it is

optionally enclosed by '\''

not

optionally enclosed by ''

--
Sasha Pachev
Create online surveys at http://www.surveyz.com/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Newbie question

2004-02-23 Thread beacker

Bernard Clement <[EMAIL PROTECTED]> writes:
>Your problem is too much experiences not the lack of skills.
>
>I am in the same situation...it is very frustating.

Bernard,
 It appears I'm not the only one :(  Though such an observation
makes me wonder if it may be a case of age discrimination being a
possibility?  One would hope this would not be the case since I am
only 44.  But I have to wonder?

   Thank you for the response,
  Brad Eacker ([EMAIL PROTECTED])

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

Re: Any way to pre-load an Index file into the key cache?

2004-02-23 Thread Paul DuBois

At 13:35 -0500 2/23/04, Eric B. wrote:
Hi,

I know this question has been posed in the past, but I haven't been able to
find an answer for it yet.
Is there any way in MySQL to pre-load a table's index file into the key
cache at startup?
Yes, this is something that was implemented recently:

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


  I know the key cache will build itself while exectuing
queries, but this will obviously slow down the first query that would
otherwise be using the index.  Short of manually executing all the possible
queries (not a realistic option) before the system is enabled, does MySQL
offer a start-up option that would pre-load the index table into its key
cache such that the first queries do not suffer from the index loading?
Of course, I realize that the key cache memory would need to be large enough
to handle such a table.


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

Re: Concat. operator ||

2004-02-23 Thread Paul DuBois

At 15:59 +0200 2/23/04, Victoria Reznichenko wrote:
Hassan Shaikh <[EMAIL PROTECTED]> wrote:
 I am running MySQL in ANSI standard mode. However the following
 statement is giving unexpected result. Am I missing something?
mysql> select 'This is a' || ' test string';
+---+
| 'This is a' || ' test string' |
+---+
| 0 |
+---+
1 row in set (0.00 sec)
 Here's a part of my my.ini, indicating that I am in ANSI mode.

 # The MySQL server
 [mysqld]
 port=3306
 #socket=MySQL
 skip-locking
 set-variable= sql-mode=ansi
This should work, but when I try it, it doesn't work.  (The sql_mode
variable is set to 4, which is the numeric equivalent of the ANSI_QUOTES
option.  I believe what's happening is that there is an attempt to match
prefixes of option names that is done incorrectly.)
I'll file a bug report.  In the meantime, you can use this instead:

[mysqld]
ansi
That should produce the desired effect.

 > set-variable= key_buffer=32M
 set-variable= max_allowed_packet=1M
 set-variable= table_cache=32
 set-variable= net_buffer_length=8K
 set-variable= sort_buffer=1M
 set-variable= record_buffer=512K
 set-variable= myisam_sort_buffer_size=4M
 set-variable= thread_cache=8
 # Try number of CPU's*2 for thread_concurrency
 set-variable= thread_concurrency=8
 log-bin
 server-id= 1
What version of MySQL do you use?
If you use version < 4.1 , you should put in the my.cnf
[mysqld]
ansi
...
or

[musqld]
sql-mode=REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY
transaction-isolation=SERIALIZABLE
..
--
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]


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

Re: Table Name Case Sensitivity

2004-02-23 Thread Peter Zaitsev

On Mon, 2004-02-23 at 08:56, Tim Hayes wrote:
> OK
> 
> There is still the possibility of an in-compatability between the 2
> platforms.
> 
> However - in both Linux and Windows (MySQL 4.0.17) the variable is not
> recognized / updateable using the set command!
> 
> 
> I get - Unknown system variable 'lower_case_table_names'
> 

This is startup option. Did you use it as such (in my.cnf) ?



-- 
Peter Zaitsev, Senior Support Engineer
MySQL AB, www.mysql.com

Meet the MySQL Team at User Conference 2004! (April 14-16, Orlando,FL)
  http://www.mysql.com/uc2004/


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

Any way to pre-load an Index file into the key cache?

2004-02-23 Thread Eric B.

Hi,

I know this question has been posed in the past, but I haven't been able to
find an answer for it yet.

Is there any way in MySQL to pre-load a table's index file into the key
cache at startup?  I know the key cache will build itself while exectuing
queries, but this will obviously slow down the first query that would
otherwise be using the index.  Short of manually executing all the possible
queries (not a realistic option) before the system is enabled, does MySQL
offer a start-up option that would pre-load the index table into its key
cache such that the first queries do not suffer from the index loading?

Of course, I realize that the key cache memory would need to be large enough
to handle such a table.

Thanks,

Eric




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

Re: Can't create a new thread (errno 11)

2004-02-23 Thread Sasha Pachev

Christof Egner wrote:
Hello James,

thanks for your answer, but max_connections is set high enough (double 
the number of the currently used threads). I think the error would be 
"Too many connections", wouldn't it?

This error comes from LinuxThreads when pthread_create() fails for one reason or 
another. There could be several reasons - out of RAM, out of address space, low 
compiled max thread limit, library compatibility, or just a bug in LinuxThreads. 
If you are not using the official MySQL binary, try it. If you are, try 
compiling your own - sometimes the official binary does not quite get along with 
the local system libraries or configuration.

max_connections is just a sanity limit MySQL imposes on itself, and if the 
thread library does not cooperate, it may stop allowing new connections before 
reaching it.

--
Sasha Pachev
Create online surveys at http://www.surveyz.com/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: GRANT question

2004-02-23 Thread Paul DuBois

At 9:31 + 2/23/04, [EMAIL PROTECTED] wrote:
"Rhino" <[EMAIL PROTECTED]> wrote on 21/02/2004 16:59:27:

 I am new to most aspects of MySQL administration so I was wondering
 if someone can help me figure out what GRANTs I need for a
 particular situation.
 I have a user who needs to be able to run some MySQL scripts that
 create and load tables in a database named NFL. He is accessing
 MySQL remotely from a client on a Windows machine. Our server is
 running in Linux Mandrake 9.1; the client is Windows 98SE.
 I've run the following grants for him:
 grant all on NFL.* to [EMAIL PROTECTED] indentified by 'x';
 grant all on NFL.* to [EMAIL PROTECTED] identified by 'x';
 grant file on NFL.* to [EMAIL PROTECTED] identified by 'x';
 grant file on NFL.* to [EMAIL PROTECTED] identified by 'x';
 When I try signing on as him from my remote Windows client, he can
 connect to MySQL and get to the mysql prompt. He can run the script
 which creates and loads the tables. However, he gets "Access Denied"
 on the Load Data Infile statements when the script executes.
 1. Why is this happening? The Load Data article says he needs the
 File privilege and I've given it to him. He also has all privileges
 on the NFL database. What more do I need to do for him?
 2. Are the privileges given to [EMAIL PROTECTED] redundant with the
 privileges given to [EMAIL PROTECTED] They *look* redundant to me; I
 suspect I've misinterpreted something I read in the manual. Can I
 get by with giving him just the [EMAIL PROTECTED] privileges? Do I
 need to give him anything else to account for the fact that he is
 coming in from a remote client?
I note that you are running a mixed Windows/Linux system. My experience is
that using uppercase in such systems is a source of create problems. Since
database names and table names for MyISAM tables are file names, they are
case significant on Linux and non-significant on Windows. While I am sure
that there is an analytic way of handling the problems which arise, I found
that it was very difficult to understand and a source of avoidable
confusion. I would recommend that in any case where mixed *nix and Windows
systems are involved, you keep database and table names entirely to lower
case. The world will give you enough unavoidable problems - don't add an
avoidable one.
  Alec
I would say that it shouldn't matter whether you use uppercase or lowercase.
What matters is that you pick one lettercase and use it consistently.
Another option is to set lower_case_table_names to 1 on all platforms.
Before using it, however, you should change all database and table names
to lowercase manually.
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
MySQL Users Conference: April 14-16, 2004
http://www.mysql.com/uc2004/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Table Name Case Sensitivity

2004-02-23 Thread Paul DuBois

At 16:56 + 2/23/04, Tim Hayes wrote:
OK

There is still the possibility of an in-compatability between the 2
platforms.
However - in both Linux and Windows (MySQL 4.0.17) the variable is not
recognized / updateable using the set command!
Correct.  You must set it using an option at server startup time, either on
the command line or (more usually) in an option file.


I get - Unknown system variable 'lower_case_table_names'

Tim Hayes


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

Re: Setting variables on update

2004-02-23 Thread Sasha Pachev

Matt Fagan wrote:
Hello,

I'm trying to run an SQL update statement like:

UPDATE ControlTable SET @prevval := NextID, NextID = NextID
+ 1
I've tried using a range of different syntax, but nothing
seems to work. This is something that does work in another
SQL server I used to use.
Does anybody know if this is something MySQL can do, and if
so, what is the correct syntax? Note that I can't use an
autoincrement column because the IDs aren't being put into
a database.
I've searched through MySQL docs and mailing lists but
couldn't find any mention of this. Any help would be
appreciated.
A UDF or your own static function might work. Worst case, MySQL source can be 
modified to make this work.

--
Sasha Pachev
Create online surveys at http://www.surveyz.com/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Help! How to handle Massive index file???

2004-02-23 Thread Sasha Pachev

Eric B. wrote:
Sure!  Here it is:

CREATE TABLE `index2` (
`id` varchar(50) NOT NULL default '',
`recordid` varchar(20) NOT NULL default '',
`transid` varchar(20) NOT NULL default '',
`formid` varchar(20) NOT NULL default '',
`groupid` varchar(20) NOT NULL default '',
`clientid` varchar(20) NOT NULL default '',
`userid` varchar(20) NOT NULL default '',
`keyid` varchar(50) NOT NULL default '',
`active_recordid` varchar(20) default NULL,
`replacing` varchar(20) default NULL,
`created` datetime default NULL,
`lastmodified` datetime default NULL,
`issaved` decimal(18,0) NOT NULL default '0',
`isclosed` decimal(18,0) NOT NULL default '0',
`isdeleted` decimal(18,0) NOT NULL default '0',
`indexfield` varchar(50) default NULL,
`indexvalue` varchar(200) default NULL,
PRIMARY KEY (`id`),
KEY `recordid_idxfield_idxvalue` (`recordid`,`indexfield`,`indexvalue`),
KEY `indexfield_idxvalue` (`indexfield`,`indexvalue`),
KEY `Fixed_fields_and_generic_fields`
(`recordid`,`transid`,`formid`,`clientid`,`active_recordid`,`issaved`,`isclo
sed`,`isdeleted`,`indexfield`,`indexvalue`),
KEY `recordid_idxfield_formid_activeRid_isvars_idxval`
(`recordid`,`indexfield`,`formid`,`active_recordid`,`issaved`,`isclosed`,`is
deleted`,`indexvalue`),
KEY `formid_idxfield_idxvalue` (`formid`,`indexfield`,`indexvalue`)
) TYPE=MyISAM
Drop Fixed_fields_and_generic_fields and 
recordid_idxfield_formid_activeRid_isvars_idxval

Most queries would probably not benefit from those in the presence of other keys.

Depending on the kind of queries you are running, you may want to reorganize 
some of your keys. Good rule of thumb is if you have a key on (A,B,C) you 
usually do not need a key on (A,B), and if for each unique value of (A,B) there 
are no  more than 10 matches, you do not usually need a key on (A,B,C).

Disk trashing can happen on a keyed query if the records in the data file are 
not in the same order as the ones in the key file. A certain order in the data 
file can be forced with periodic runs of ALTER TABLE ORDER BY.

--
Sasha Pachev
Create online surveys at http://www.surveyz.com/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Improving seek/access times -- does RAID help?

2004-02-23 Thread Sasha Pachev

Matt W wrote:
Hi Ted,

Heh. :-)  This could be many GBs.  There's no problem reading rows that
are in RAM (cached by the OS) -- can read over 10,000/second.  If
there's enough RAM, the OS will take care of it (you could cat table.MYD
to /dev/null). No ramdisk necessary. :-)
BTW, this is for MySQL's full-text search.  It works pretty well (fast)
as far as doing the lookups and searching in the index.  That's not a
concern at all.  The problem is that it *has to* read the data file for
each matching row (and possibly non-matching rows, depending on the
search). :-(  Searches need to be reasonably fast on millions of rows,
while possibly reading 10s of thousands of data rows.  It takes a lot
more time when those rows aren't cached.
The only thing I've thought of so far is symlinking the data file on a
separate drive, but I'm not sure how much that will actually help.
Matt
Matt:

Post your schema (use SHOW CREATE TABLE), and give an example of a couple of 
queries that are slow including the output of EXPLAIN. It is quite possible that 
we can find a fairly simple solution to avoid excessive random disk access.

--
Sasha Pachev
Create online surveys at http://www.surveyz.com/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: MySQL versus MS SQL

2004-02-23 Thread HuMPie

I saw the same that ms sql is faster but I think it's because of the ms
sql makes better use of index's 
I had a table with almos 300mil records on ms sql query with index'es it
was no problem (in about 5 min I got the result) and with a much smaller
db on mysql (with 80 mil records it took almost 30 min this with the
same index'es)

If this are query's only for report purpuse I would say go for MySQL
(much cheaper) and if it's production and this query must be diplay'd
every x min then keep with MS SQL.

And if the NULL value are on a varchar based field I said use NULL
otherwise it's make no different (on MS SQL)

 
Best Regards,
 
HuMPie @ Grunn.Org
 
mrtg.grunn.org Dutch mirror of MRTG
www.webdns.info Dutch private DNS hoster
===
Motto:
Writing software is more fun than working.
 
===
Disclaimer:
All you do with the suggestion in this mail is you responsibillity even
if your system will crash :)


-Original Message-
From: Chris Fossenier [mailto:[EMAIL PROTECTED] 
Sent: maandag 23 februari 2004 18:16
To: [EMAIL PROTECTED]
Subject: MySQL versus MS SQL


Hello,
 
This is a long post, my apologies.
 
I have been working on migrating a database from MS SQL to MySQL for
about 1 month now and am at a point where I can start running some tests
to see how they compare. I'll provide a little background to help you
out.
 
The MS SQL database is 120million records long and 400 fields wide. It's
all in one table with 19 indexes. There is a cluster index on the ZIP
code field. This database is running on a DELL dual Xeon (MHz 2MB cpu
cache, 4GB RAM), DELL Powervault RAID5 storage array.
 
My initial plan was to simply move the big db/table structure from MS
SQL to MySQL but we found that indexing was a roadblock. The indexes
(after 3 of
them) would start to take longer and longer. We are still performing
testing on this as it's possible that some of our hardware (DELL 8450s)
may have issues running with an EMC Symmetrix RAID0+1. In the end, we
normalized the data into 17 tables with no more than 3 indexes per
table.
 
The MySQL database is 106million records (we got rid of some duplicates)
long and 250 fields (we got rid of some unused fields) wide. As
mentioned above, it's in 17 tables. This database is running on a HP
quad Xeon (500MHz 512MB cpu cache, 8GB RAM), EMC Symmetrix storage
array.
 
I am including the queries in this post to see if anyone has any advice
on what we could do to make these queries run faster. I realize that the
hardware isn't identical but I think it's close enough to get somewhat
accurate comparisons.
 
Here are the queries. the first query in each case is the MS SQL query.
The second query is the MySQL query. At the beginning section for the
queries, I indicate which fields are indexed (it's the same for both
tables). MS SQL beats MySQL handily except for one query and I'm not
sure why. All queries were run as the only process on each server.
 
QUERY1
~~~
Indexed Fields (link, phone_pander,state, exact_age, estimated_age,
phone, first, last, address) MS SQL QUERY (TIME TO COMPLETE: 21m33s)
SELECT
phone,first,last,address,address2,city,state,zip,plus4,county,phone_pand
er_f
lag,homeowner,probable_homeowner,homeowner_probability_model,exact_age,e
stim
ated_age
FROM consumer1
WHERE phone_pander_flag <> 'Y'
AND STATE = 'PA'
AND (homeowner = 'Y' OR probable_homeowner IN ('8','9') OR
homeowner_probability_model BETWEEN '080' AND '102') AND (exact_age
BETWEEN '40' AND '60' OR estimated_age BETWEEN '40' AND '60') AND phone
> ' ' AND first > ' ' AND last > ' ' AND address > ' ';
 
MySQL QUERY(TIME TO COMPLETE: 42m27s)
SELECT 
 a1.phone, p1a.first, p1a.last, a3.address, a1.address2, a1.city, 
 state.state, a2.zip, a1.plus4, a3.county, 
 a1.phone_pander_flag, h1.homeowner, h2.probable_homeowner, 
 h2.homeowner_probability_model, p1c.exact_age, p1c.estimated_age FROM
a1
INNER JOIN a2on a2.link= a1.link
INNER JOIN a3on a3.link= a1.link
INNER JOIN h1on h1.link= a1.link
INNER JOIN h2on h2.link= a1.link
INNER JOIN p1a   on p1a.link   = a1.link
INNER JOIN p1c   on p1c.link   = a1.link
INNER JOIN state on state.state_code = a2.state_code
WHERE 
 a1.phone_pander_flag <> 'Y'
 AND state.state = 'PA'
 AND ( h1.homeowner = 'Y' 
  OR h2.probable_homeowner IN ('8','9') 
  OR h2.homeowner_probability_model BETWEEN '080' AND '102' )  AND (
p1c.exact_age BETWEEN '40' AND '60'
  OR estimated_age BETWEEN '40' AND '60' )
 AND a1.phone is not null
 AND p1a.first is not null
 AND p1a.last is not null
 AND a3.address is not null;

QUERY2

Indexed Fields (link, zip, phone_pander,phone, first, last, address) MS
SQL QUERY(12 seconds) SELECT
phone,first,last,address,address2,city,state,zip,plus4,county,phone_pand
er_f
lag,homeowner,probable_homeowner,homeowner_probability_model,number_of_u
nits
,primary_ethnic_market_code,household_hispanic
FROM consumer1
WHERE zip in ('60402','60650','60607

RE: MySQL versus MS SQL

2004-02-23 Thread Chris Fossenier

I specifically wrote my import script to parse out the fields that we need
and add NULLs. I was told that having NULL values was faster than using ' '.
If this isn't the case, I'd like to know.

Thanks.

-Original Message-
From: Kurt Hansen [mailto:[EMAIL PROTECTED] 
Sent: Monday, February 23, 2004 11:42 AM
To: Chris Fossenier; [EMAIL PROTECTED]
Subject: Re: MySQL versus MS SQL


Hi Chris,

Chris Fossenier wrote:

> This is a long post, my apologies.

Speaking for myself, I found the detail most helpful. Thanks!

See response at bottom.

...snip...
> QUERY1
> ~~~
> Indexed Fields (link, phone_pander,state, exact_age, estimated_age, 
> phone, first, last, address) MS SQL QUERY (TIME TO COMPLETE: 21m33s)

...snip...

> MySQL QUERY(TIME TO COMPLETE: 42m27s)
> SELECT
>  a1.phone, p1a.first, p1a.last, a3.address, a1.address2, a1.city, 
>  state.state, a2.zip, a1.plus4, a3.county, 
>  a1.phone_pander_flag, h1.homeowner, h2.probable_homeowner, 
>  h2.homeowner_probability_model, p1c.exact_age, p1c.estimated_age
> FROM a1
> INNER JOIN a2on a2.link= a1.link
> INNER JOIN a3on a3.link= a1.link
> INNER JOIN h1on h1.link= a1.link
> INNER JOIN h2on h2.link= a1.link
> INNER JOIN p1a   on p1a.link   = a1.link
> INNER JOIN p1c   on p1c.link   = a1.link
> INNER JOIN state on state.state_code = a2.state_code
> WHERE 
>  a1.phone_pander_flag <> 'Y'
>  AND state.state = 'PA'
>  AND ( h1.homeowner = 'Y' 
>   OR h2.probable_homeowner IN ('8','9') 
>   OR h2.homeowner_probability_model BETWEEN '080' AND '102' )
>  AND ( p1c.exact_age BETWEEN '40' AND '60'
>   OR estimated_age BETWEEN '40' AND '60' )
>  AND a1.phone is not null
>  AND p1a.first is not null
>  AND p1a.last is not null
>  AND a3.address is not null;
> 
> QUERY2
> 
> Indexed Fields (link, zip, phone_pander,phone, first, last, address) 
> MS SQL QUERY(12 seconds)

...snip...

> MySQL QUERY(10min51secs)
> SELECT
>  a1.phone, p1a.first, p1a.last, a3.address, a1.address2, a1.city, 
>  state.state, a2.zip, a1.plus4, a3.county, a1.phone_pander_flag,
> h1.homeowner, 
>  h2.probable_homeowner, h2.homeowner_probability_model, 
>  a1.number_of_units, p1a.primary_ethnic_market_code,
> c2k.2000_census_hispanic
> FROM a1
> INNER JOIN a2on a2.link= a1.link
> INNER JOIN a3on a3.link= a1.link
> INNER JOIN h1on h1.link= a1.link
> INNER JOIN h2on h2.link= a1.link
> INNER JOIN p1a   on p1a.link   = a1.link
> INNER JOIN c2k   on c2k.link   = a1.link
> INNER JOIN state on state.state_code = a2.state_code
> WHERE 
>  a2.zip in ('60402', '60650', '60607', '60608', '60612', '60622')
>  AND a1.phone_pander_flag <> 'Y'
>  AND ( h1.homeowner='Y' 
>   OR h2.probable_homeowner in ('8','9') 
>   OR h2.homeowner_probability_model BETWEEN '080' AND '102')
>  AND a1.phone is not null
>  AND p1a.first is not null
>  AND p1a.last is not null
>  AND a3.address is not null
>  AND a1.number_of_units IN ('0001','0002')
>  AND ( p1a.primary_ethnic_market_code='38' 
>   OR c2k.2000_census_hispanic >='0850' );
> 
>  
> QUERY3
> ~
> Indexed Fields (link, zip, phone_pander, phone, first, last, address) 
> MS SQL QUERY(10seconds)

...snip...

> MySQL QUERY(5min22secs)
> SELECT
>  a1.phone, p1a.first, p1a.last, a3.address, a1.address2, a1.city, 
>  state.state, a2.zip, a1.plus4, a3.county, h1.home_purchase_date, 
>  mtg.mortgage_amount_in_thousands, a1.phone_pander_flag
> FROM a1
> INNER JOIN a2on a2.link= a1.link
> INNER JOIN a3on a3.link= a1.link
> INNER JOIN h1on h1.link= a1.link
> INNER JOIN p1a   on p1a.link   = a1.link
> INNER JOIN mtg   on mtg.link   = a1.link
> INNER JOIN state on state.state_code = a2.state_code
> WHERE 
>  a2.zip IN ('14221', '14224', '14226', '14227', '14051', '14031', '14057')
>  AND a1.phone_pander_flag <> 'Y'
>  AND a1.phone is not null
>  AND p1a.first is not null
>  AND p1a.last is not null
>  AND a3.address is not null
>  AND mtg.mortgage_amount_in_thousands>='0075'
>  AND (h1.home_purchase_date > '2003' and h1.home_purchase_date < '2004');
> 
>  
> QUERY4
> ~~
> Indexed Fields (link, state, first, last, address)

...snip...

> MySQL QUERY(2hrs17mins)
> SELECT
>  a1.phone, p1a.first, p1a.last, a3.address, a1.address2, 
>  a1.city, state.state, a2.zip, a1.plus4, a3.county, 
>  mtg.mortgage_loan_type, mtg.mortgage_amount_in_thousands, 
>  h1.home_purchase_price, h1.home_purchase_date
> FROM a1
> INNER JOIN a2on a2.link= a1.link
> INNER JOIN a3on a3.link= a1.link
> INNER JOIN h1on h1.link= a1.link
> INNER JOIN p1a   on p1a.link   = a1.link
> INNER JOIN mtg   on mtg.link   = a1.link
> INNER JOIN state on state.state_code = a2.state_code
> WHERE 
>  state.state IN ('PA', 'OH')
>  AND p1a.first is not null
>  AND p1a.last is not null
>  AND a3.address is not null
>  AND mtg.mortgage_loan_type = 'V'
>  AND (mtg.mortgage_amount_in_thousands>='0100' OR
> h1.home_purchase_price>='010')
>  AND h1.home_purchase_date between '2002' and '2

MySQL 4.1 - Installation of grant tables failed

2004-02-23 Thread Sabine Seipel

hi,

I have the same problem with executing mysql_install_db in mysql 4.1 after
compiling it on debian 3.0, lust like Bug #2915 installation of grant tables
failed! 

after sucessfully configure (./configure --prefix=/usr/local/mysql
--with-innodb) & make & make install and executing mysql_install_db as root, I got the
error:

make[3]: Leaving directory `/files/mysql-4.1.1-alpha/support-files'
make[2]: Leaving directory `/files/mysql-4.1.1-alpha/support-files'
make[1]: Leaving directory `/files/mysql-4.1.1-alpha/support-files'
neptun:/files/mysql-4.1.1-alpha# scripts/mysql_install_db
Installing all prepared tables
ERROR: 1064  You have an error in your SQL syntax.  Check the manual that
corres
ND(expr)\nReturns the bitwise AND of all bits in expr. The calculation ' at
line
040221 14:46:17  Aborting

040221 14:46:17  /usr/local/mysql/libexec/mysqld: Shutdown Complete

Installation of grant tables failed!

Examine the logs in /usr/local/mysql/var for more information.
You can also try to start the mysqld daemon with:
/usr/local/mysql/libexec/mysqld --skip-grant &
You can use the command line tool
/usr/local/mysql/bin/mysql to connect to the mysql
database and look at the grant tables:
...
...

Is it a bug or have I made the same error as in Bug #2915?

I would be happy, if anybody could help me.

kind regards
 Sabine Seipel

-- 
GMX ProMail (250 MB Mailbox, 50 FreeSMS, Virenschutz, 2,99 EUR/Monat...)
jetzt 3 Monate GRATIS + 3x DER SPIEGEL +++ http://www.gmx.net/derspiegel +++


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

Re: MySQL versus MS SQL

2004-02-23 Thread Kurt Hansen

Hi Chris,

Chris Fossenier wrote:

This is a long post, my apologies.
Speaking for myself, I found the detail most helpful. Thanks!

See response at bottom.

...snip...
QUERY1
~~~
Indexed Fields (link, phone_pander,state, exact_age, estimated_age, phone,
first, last, address)
MS SQL QUERY (TIME TO COMPLETE: 21m33s)
...snip...

MySQL QUERY(TIME TO COMPLETE: 42m27s)
SELECT 
 a1.phone, p1a.first, p1a.last, a3.address, a1.address2, a1.city, 
 state.state, a2.zip, a1.plus4, a3.county, 
 a1.phone_pander_flag, h1.homeowner, h2.probable_homeowner, 
 h2.homeowner_probability_model, p1c.exact_age, p1c.estimated_age
FROM a1
INNER JOIN a2on a2.link= a1.link
INNER JOIN a3on a3.link= a1.link
INNER JOIN h1on h1.link= a1.link
INNER JOIN h2on h2.link= a1.link
INNER JOIN p1a   on p1a.link   = a1.link
INNER JOIN p1c   on p1c.link   = a1.link
INNER JOIN state on state.state_code = a2.state_code
WHERE 
 a1.phone_pander_flag <> 'Y'
 AND state.state = 'PA'
 AND ( h1.homeowner = 'Y' 
  OR h2.probable_homeowner IN ('8','9') 
  OR h2.homeowner_probability_model BETWEEN '080' AND '102' )
 AND ( p1c.exact_age BETWEEN '40' AND '60'
  OR estimated_age BETWEEN '40' AND '60' )
 AND a1.phone is not null
 AND p1a.first is not null
 AND p1a.last is not null
 AND a3.address is not null;

QUERY2

Indexed Fields (link, zip, phone_pander,phone, first, last, address)
MS SQL QUERY(12 seconds)
...snip...

MySQL QUERY(10min51secs)
SELECT 
 a1.phone, p1a.first, p1a.last, a3.address, a1.address2, a1.city, 
 state.state, a2.zip, a1.plus4, a3.county, a1.phone_pander_flag,
h1.homeowner, 
 h2.probable_homeowner, h2.homeowner_probability_model, 
 a1.number_of_units, p1a.primary_ethnic_market_code,
c2k.2000_census_hispanic
FROM a1
INNER JOIN a2on a2.link= a1.link
INNER JOIN a3on a3.link= a1.link
INNER JOIN h1on h1.link= a1.link
INNER JOIN h2on h2.link= a1.link
INNER JOIN p1a   on p1a.link   = a1.link
INNER JOIN c2k   on c2k.link   = a1.link
INNER JOIN state on state.state_code = a2.state_code
WHERE 
 a2.zip in ('60402', '60650', '60607', '60608', '60612', '60622')
 AND a1.phone_pander_flag <> 'Y'
 AND ( h1.homeowner='Y' 
  OR h2.probable_homeowner in ('8','9') 
  OR h2.homeowner_probability_model BETWEEN '080' AND '102')
 AND a1.phone is not null
 AND p1a.first is not null
 AND p1a.last is not null
 AND a3.address is not null
 AND a1.number_of_units IN ('0001','0002')
 AND ( p1a.primary_ethnic_market_code='38' 
  OR c2k.2000_census_hispanic >='0850' );

 
QUERY3
~
Indexed Fields (link, zip, phone_pander, phone, first, last, address)
MS SQL QUERY(10seconds)
...snip...

MySQL QUERY(5min22secs)
SELECT 
 a1.phone, p1a.first, p1a.last, a3.address, a1.address2, a1.city, 
 state.state, a2.zip, a1.plus4, a3.county, h1.home_purchase_date, 
 mtg.mortgage_amount_in_thousands, a1.phone_pander_flag
FROM a1
INNER JOIN a2on a2.link= a1.link
INNER JOIN a3on a3.link= a1.link
INNER JOIN h1on h1.link= a1.link
INNER JOIN p1a   on p1a.link   = a1.link
INNER JOIN mtg   on mtg.link   = a1.link
INNER JOIN state on state.state_code = a2.state_code
WHERE 
 a2.zip IN ('14221', '14224', '14226', '14227', '14051', '14031', '14057')
 AND a1.phone_pander_flag <> 'Y'
 AND a1.phone is not null
 AND p1a.first is not null
 AND p1a.last is not null
 AND a3.address is not null
 AND mtg.mortgage_amount_in_thousands>='0075'
 AND (h1.home_purchase_date > '2003' and h1.home_purchase_date < '2004');

 
QUERY4
~~
Indexed Fields (link, state, first, last, address)
...snip...

MySQL QUERY(2hrs17mins)
SELECT 
 a1.phone, p1a.first, p1a.last, a3.address, a1.address2, 
 a1.city, state.state, a2.zip, a1.plus4, a3.county, 
 mtg.mortgage_loan_type, mtg.mortgage_amount_in_thousands, 
 h1.home_purchase_price, h1.home_purchase_date
FROM a1
INNER JOIN a2on a2.link= a1.link
INNER JOIN a3on a3.link= a1.link
INNER JOIN h1on h1.link= a1.link
INNER JOIN p1a   on p1a.link   = a1.link
INNER JOIN mtg   on mtg.link   = a1.link
INNER JOIN state on state.state_code = a2.state_code
WHERE 
 state.state IN ('PA', 'OH')
 AND p1a.first is not null
 AND p1a.last is not null
 AND a3.address is not null
 AND mtg.mortgage_loan_type = 'V'
 AND (mtg.mortgage_amount_in_thousands>='0100' OR
h1.home_purchase_price>='010')
 AND h1.home_purchase_date between '2002' and '2003';

QUERY5
~~
Indexed Fields (link, state, first, last, address)
MS SQL QUERY(27min28secs)
...snip...

MySQL QUERY(3hrs53mins)
SELECT 
 a1.phone, p1a.first, p1a.last, a3.address, a1.address2, 
 a1.city, state.state, a2.zip, a1.plus4, a3.county, 
 a1.phone_pander_flag, a1.number_of_units, h1.dwelling_type, 
 h2.homeowner_probability_model, h1.home_property_indicator
FROM a1
INNER JOIN a2on a2.link= a1.link
INNER JOIN a3on a3.link= a1.link
INNER JOIN h1on h1.link= a1.link
INNER JOIN h2on h2.link= a1.link
INNER JOIN p1a   on p1a.link   = a1.link
INNER JOIN mtg   on mtg.link   = a1.link

MySQL versus MS SQL

2004-02-23 Thread Chris Fossenier

Hello,
 
This is a long post, my apologies.
 
I have been working on migrating a database from MS SQL to MySQL for about 1
month now and am at a point where I can start running some tests to see how
they compare. I'll provide a little background to help you out.
 
The MS SQL database is 120million records long and 400 fields wide. It's all
in one table with 19 indexes. There is a cluster index on the ZIP code
field. This database is running on a DELL dual Xeon (MHz 2MB cpu cache, 4GB
RAM), DELL Powervault RAID5 storage array.
 
My initial plan was to simply move the big db/table structure from MS SQL to
MySQL but we found that indexing was a roadblock. The indexes (after 3 of
them) would start to take longer and longer. We are still performing testing
on this as it's possible that some of our hardware (DELL 8450s) may have
issues running with an EMC Symmetrix RAID0+1. In the end, we normalized the
data into 17 tables with no more than 3 indexes per table.
 
The MySQL database is 106million records (we got rid of some duplicates)
long and 250 fields (we got rid of some unused fields) wide. As mentioned
above, it's in 17 tables. This database is running on a HP quad Xeon (500MHz
512MB cpu cache, 8GB RAM), EMC Symmetrix storage array.
 
I am including the queries in this post to see if anyone has any advice on
what we could do to make these queries run faster. I realize that the
hardware isn't identical but I think it's close enough to get somewhat
accurate comparisons.
 
Here are the queries. the first query in each case is the MS SQL query. The
second query is the MySQL query. At the beginning section for the queries, I
indicate which fields are indexed (it's the same for both tables). MS SQL
beats MySQL handily except for one query and I'm not sure why. All queries
were run as the only process on each server.
 
QUERY1
~~~
Indexed Fields (link, phone_pander,state, exact_age, estimated_age, phone,
first, last, address)
MS SQL QUERY (TIME TO COMPLETE: 21m33s)
SELECT
phone,first,last,address,address2,city,state,zip,plus4,county,phone_pander_f
lag,homeowner,probable_homeowner,homeowner_probability_model,exact_age,estim
ated_age
FROM consumer1
WHERE phone_pander_flag <> 'Y'
AND STATE = 'PA'
AND (homeowner = 'Y' OR probable_homeowner IN ('8','9') OR
homeowner_probability_model BETWEEN '080' AND '102')
AND (exact_age BETWEEN '40' AND '60' OR estimated_age BETWEEN '40' AND '60')
AND phone > ' '
AND first > ' '
AND last > ' '
AND address > ' ';
 
MySQL QUERY(TIME TO COMPLETE: 42m27s)
SELECT 
 a1.phone, p1a.first, p1a.last, a3.address, a1.address2, a1.city, 
 state.state, a2.zip, a1.plus4, a3.county, 
 a1.phone_pander_flag, h1.homeowner, h2.probable_homeowner, 
 h2.homeowner_probability_model, p1c.exact_age, p1c.estimated_age
FROM a1
INNER JOIN a2on a2.link= a1.link
INNER JOIN a3on a3.link= a1.link
INNER JOIN h1on h1.link= a1.link
INNER JOIN h2on h2.link= a1.link
INNER JOIN p1a   on p1a.link   = a1.link
INNER JOIN p1c   on p1c.link   = a1.link
INNER JOIN state on state.state_code = a2.state_code
WHERE 
 a1.phone_pander_flag <> 'Y'
 AND state.state = 'PA'
 AND ( h1.homeowner = 'Y' 
  OR h2.probable_homeowner IN ('8','9') 
  OR h2.homeowner_probability_model BETWEEN '080' AND '102' )
 AND ( p1c.exact_age BETWEEN '40' AND '60'
  OR estimated_age BETWEEN '40' AND '60' )
 AND a1.phone is not null
 AND p1a.first is not null
 AND p1a.last is not null
 AND a3.address is not null;

QUERY2

Indexed Fields (link, zip, phone_pander,phone, first, last, address)
MS SQL QUERY(12 seconds)
SELECT
phone,first,last,address,address2,city,state,zip,plus4,county,phone_pander_f
lag,homeowner,probable_homeowner,homeowner_probability_model,number_of_units
,primary_ethnic_market_code,household_hispanic
FROM consumer1
WHERE zip in ('60402','60650','60607','60608','60612','60622')
AND phone_pander_flag<>'y'
AND (homeowner='y' OR probable_homeowner in ('8','9') OR
homeowner_probability_model BETWEEN '080' AND '102')
AND phone > ' '
AND first > ' '
AND last > ' '
AND address > ' '
AND number_of_units IN ('0001','0002')
AND (primary_ethnic_market_code='38' OR household_hispanic>='0850')

MySQL QUERY(10min51secs)
SELECT 
 a1.phone, p1a.first, p1a.last, a3.address, a1.address2, a1.city, 
 state.state, a2.zip, a1.plus4, a3.county, a1.phone_pander_flag,
h1.homeowner, 
 h2.probable_homeowner, h2.homeowner_probability_model, 
 a1.number_of_units, p1a.primary_ethnic_market_code,
c2k.2000_census_hispanic
FROM a1
INNER JOIN a2on a2.link= a1.link
INNER JOIN a3on a3.link= a1.link
INNER JOIN h1on h1.link= a1.link
INNER JOIN h2on h2.link= a1.link
INNER JOIN p1a   on p1a.link   = a1.link
INNER JOIN c2k   on c2k.link   = a1.link
INNER JOIN state on state.state_code = a2.state_code
WHERE 
 a2.zip in ('60402', '60650', '60607', '60608', '60612', '60622')
 AND a1.phone_pander_flag <> 'Y'
 AND ( h1.homeowner='Y' 
  OR h2.probable_homeowner in ('8','9') 
  OR h2.homeowner_probability_mode

Re: Table Name Case Sensitivity

2004-02-23 Thread Heikki Tuuri

Hi!

Please observe the following about InnoDB table names on Windows:

http://www.innodb.com/ibman.php#Moving

"
On Windows InnoDB stores the database names and table names internally
always in lower case. To move databases in a binary format from Unix to
Windows or from Windows to Unix you should have all table and database names
in lower case. A convenient way to accomplish this is to add on Unix the
line set-variable=lower_case_table_names=1 to the [mysqld] section of your
my.cnf before you start creating your tables. On Windows
set-variable=lower_case_table_names=1 is the default setting.
"

I think it is bad programming style to have tables whose name only differs
in case. I recommend using in my.cnf

lower_case_table_names=1

on all platforms.

Regards,

Heikki

..
List: MySQL General Discussion
From: Tim Hayes Date:February 23 2004 5:56pm
Subject: Re: Table Name Case Sensitivity

OK

There is still the possibility of an in-compatability between the 2
platforms.

However - in both Linux and Windows (MySQL 4.0.17) the variable is not
recognized / updateable using the set command!

I get - Unknown system variable 'lower_case_table_names'

Tim Hayes

- Original Message -
From: "Peter Zaitsev" <[EMAIL PROTECTED]>
To: "Tim Hayes" <[EMAIL PROTECTED]>
Sent: Monday, February 23, 2004 4:19 PM
Subject: Re: Table Name Case Sensitivity

> On Mon, 2004-02-23 at 08:15, Tim Hayes wrote:
> > Can anyone offer advice?
>
> Run with lower_case_table_names=1
>
>
>
>
> >
> > I have come across a MySQL database on Linux with duplicate table
names - "Accounts" and "accounts". This seems fine on Linux, but does
not
transfer to the Windows environment - it is rejected because of the
duplicate name. However I do see that Column Names have to be unique in both
environments.
> >
> > Q. Is there some sort of practical advantage for having case sensitive
table names in Linux, or is this a design "shortcoming"? Postgress rejects
duplicate table names.
> >
> > Thanks
> > Tim Hayes
> --
> Peter Zaitsev, Senior Support Engineer
> MySQL AB, www.mysql.com
>
> Meet the MySQL Team at User Conference 2004! (April 14-16, Orlando,FL)
>   http://www.mysql.com/uc2004/
>
>

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

Re: Newbie question

2004-02-23 Thread Bernard Clement

Dear Brad,

Your problem is too much experiences not the lack of skills.

I am in the same situation...it is very frustating.

Regards,

Bernard

On Monday 23 February 2004 11:14, Brad Eacker wrote:
> Donny Simonton <[EMAIL PROTECTED]> writes:
> >But as a manager, the first thing I do is ask for code examples, as well
> > as table structures.  I don't even need to see a resume once I look at
> > somebody's code and table structures.
>
>  Unfortunately I will have to disagree with you on this one.  Having
> been searching for solid employment for the last year, I'm finding that the
> managers seek only to place someone who has done the exact job they are
> trying to fill.  And this is with 22 years of relevant experience on my
> part.  8 years of web-back integration, 13 years inside the unix kernel,
> coupled with 9 years of RDBMS experience don't seem to hold much weight
> anymore.
>
>  You mention examples, I have a pair I use having fully developed both
> sets
>
> http://www.misc.com/costumes/Dickens_0312/index.php
> http://66.134.203.2/~beacker/genbank/gbf_extract.cgi?LOCUS=AF158101
>
> The first being a photographic PHP based mechanism that I use in at least
> a dozen pages without change.  The second being a 30 million record data
> set I utilize for selecting GenBank info.  I'm finding it very frustrating
> that my knowledge and capabilities are not found to be compelling.  I have
> utilized C for over 21 years, and perl for 20 years.  Yet these don't
> appear to be enough to get a position in Silicon Valley.
>
>Brad Eacker ([EMAIL PROTECTED])


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

Re: Table Name Case Sensitivity

2004-02-23 Thread Tim Hayes

OK

There is still the possibility of an in-compatability between the 2
platforms.

However - in both Linux and Windows (MySQL 4.0.17) the variable is not
recognized / updateable using the set command!


I get - Unknown system variable 'lower_case_table_names'

Tim Hayes




- Original Message -
From: "Peter Zaitsev" <[EMAIL PROTECTED]>
To: "Tim Hayes" <[EMAIL PROTECTED]>
Sent: Monday, February 23, 2004 4:19 PM
Subject: Re: Table Name Case Sensitivity


> On Mon, 2004-02-23 at 08:15, Tim Hayes wrote:
> > Can anyone offer advice?
>
> Run with lower_case_table_names=1
>
>
>
>
> >
> > I have come across a MySQL database on Linux with duplicate table
names - "Accounts" and "accounts". This seems fine on Linux, but does not
transfer to the Windows environment - it is rejected because of the
duplicate name. However I do see that Column Names have to be unique in both
environments.
> >
> > Q. Is there some sort of practical advantage for having case sensitive
table names in Linux, or is this a design "shortcoming"? Postgress rejects
duplicate table names.
> >
> > Thanks
> > Tim Hayes
> --
> Peter Zaitsev, Senior Support Engineer
> MySQL AB, www.mysql.com
>
> Meet the MySQL Team at User Conference 2004! (April 14-16, Orlando,FL)
>   http://www.mysql.com/uc2004/
>
>


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

Re: Table Name Case Sensitivity

2004-02-23 Thread Alec . Cawley








"Tim Hayes" <[EMAIL PROTECTED]> wrote on 23/02/2004 16:15:36:

> Can anyone offer advice?
>
> I have come across a MySQL database on Linux with duplicate table
> names - "Accounts" and "accounts". This seems fine on Linux, but
> does not transfer to the Windows environment - it is rejected
> because of the duplicate name. However I do see that Column Names
> have to be unique in both environments.
>
> Q. Is there some sort of practical advantage for having case
> sensitive table names in Linux, or is this a design "shortcoming"?
> Postgress rejects duplicate table names.

It is a design feature - shortcoming if you will. For MyISAM tables, each
table is a file (well, three files). The table name therefore shares the
case (in)sensitivity of the underlying OS. The same will apply to database
names, because they are directories, but not to column names because they
are concealed inside the data files.

There is no right answer to the question of case sensitivity. Mostly it
will depend from which direction you approach the problem. As someone who
uses both Windows and Linux, I can put on a different mental hat and get a
different "correct" answer each time I approach the question.

Anyway, theoretical elegance is irrelevant: you have to live with the world
as you find it. In practice, I have found the only solution that works is
lower case table names only. Obviously, this is too late for you - you have
"duplicate" tables already.

I imagine the problem would not occur with InnoDB tables, which put all the
tables into a single data space (perhaps someone could confirm). In  which
case you could solve the problem by changing table types.

  Alec




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

large insert with load

2004-02-23 Thread Bernd Jagla

Hi,

we have a big problem with inserting large amounts of data (2GB) into our MyISAM 
databank (version 4.0.12).
The dataset  is loaded in using 
LOAD DATA infile 'bla' ignore into table tab fields terminated by ' ' optionally 
enclosed by '' (int1, int2, int3, date);

The format of the text file is...
int1 int2 int3 'date' 

...with white spaces between each field.  After loading the data, there seems to be 
inconsistencies between the text-input data and the data in the mySQL table.

The problem seems to be localized to int3 and date, which shows integers that have 20 
appended to the original data.  (Ie.  if int3 is 15, it would be in the database as 
2015)  and date is '-00-00'.

Any suggestions?

Thanks

Bernd

Re: Table Name Case Sensitivity

2004-02-23 Thread Jochem van Dieten

Tim Hayes said:
> Can anyone offer advice?
>
> I have come across a MySQL database on Linux with duplicate table
> names - "Accounts" and "accounts". This seems fine on Linux, but
> does not transfer to the Windows environment - it is rejected
> because of the duplicate name. However I do see that Column Names
> have to be unique in both environments.
>
> Q. Is there some sort of practical advantage for having case
> sensitive table names in Linux, or is this a design "shortcoming"?

The SQL standard says that table names inside identifier quotes should
be matched "as is" against schema objects. Table names outside
identifier quotes should first be converted to uppercase and then
matched to schema objects. According to the SQL standard, double
quotes are identifier quotes.
So I would qualify the current behaviour of MySQL as a design
shortcoming. And the fact that it behaves differently on Windows and
Unix does not improve the situation.


> Postgress rejects duplicate table names.

MySQL rejects duplicate table names just as PostgreSQL does. Table
names that differ in case are not duplicates on all platforms.

And for the record, PostgreSQL's behaviour is not exactly standard
compliant either. Instead of converting unquoted identifiers to
uppercase they convert them to lowercase for readability.
But at least it behaves consistent between platforms and it uses
identifier quotes correctly.

Jochem





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

RE: Can't create a new thread (errno 11)

2004-02-23 Thread Donny Simonton

Set this in you're my.cnf and restart and see if that solves your problem.

open-files-limit=36864

*** Don't ask me what that number is, it's just a random number.

Donny

> -Original Message-
> From: Ken Menzel [mailto:[EMAIL PROTECTED]
> Sent: Monday, February 23, 2004 10:20 AM
> To: Christof Egner
> Cc: [EMAIL PROTECTED]
> Subject: Re: Can't create a new thread (errno 11)
>
> Hi Christof,
>You don't mention the OS version you are using (So I will assume
> Linux) is there a sysctl for the max number of threads on your OS?
> Ken
> - Original Message -
> From: "Christof Egner" <[EMAIL PROTECTED]>
> To: "James Kelty" <[EMAIL PROTECTED]>
> Cc: <[EMAIL PROTECTED]>
> Sent: Saturday, February 21, 2004 5:18 AM
> Subject: Re: Can't create a new thread (errno 11)
>
>
> > Hello James,
> >
> > thanks for your answer, but max_connections is set high enough
> (double
> > the number of the currently used threads). I think the error would
> be
> > "Too many connections", wouldn't it?
> >
> > -Christof
> >
> > James Kelty wrote:
> > > Check the max_connections variable
> > >
> > >
> > > Mysql> show variables liks '%connection%';
> > >
> > > -James
> > >
> > > -Original Message-
> > > From: Christof Egner [mailto:[EMAIL PROTECTED]
> > > Sent: Friday, February 20, 2004 9:03 AM
> > > To: [EMAIL PROTECTED]
> > > Subject: Can't create a new thread (errno 11)
> > >
> > > Hello,
> > >
> > > I'm having problems with a 4.0.18 MySQL Server. Since this
> afternoon I
> > > get the error "Can't create a new thread (errno 11)" when MySQL
> has
> > > forked about 200-300 threads.
> > >
> > > What does it mean? I'm not running out of RAM or disk space.
> > >
> > > Best Regards,
> > > Christof
> > >
> > >
> > >
> >
> >
> > --
> > ___
> > Configo Systems GmbH - Jahnstr. 62 - 64285 Darmstadt - DE
> > Tel +49.6151.39656.0 Fax .49 http://www.configo.de
> >
> >
> > --
> > 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: Can't create a new thread (errno 11)

2004-02-23 Thread Ken Menzel

Hi Christof,
   You don't mention the OS version you are using (So I will assume
Linux) is there a sysctl for the max number of threads on your OS?
Ken
- Original Message - 
From: "Christof Egner" <[EMAIL PROTECTED]>
To: "James Kelty" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Saturday, February 21, 2004 5:18 AM
Subject: Re: Can't create a new thread (errno 11)


> Hello James,
>
> thanks for your answer, but max_connections is set high enough
(double
> the number of the currently used threads). I think the error would
be
> "Too many connections", wouldn't it?
>
> -Christof
>
> James Kelty wrote:
> > Check the max_connections variable
> >
> >
> > Mysql> show variables liks '%connection%';
> >
> > -James
> >
> > -Original Message-
> > From: Christof Egner [mailto:[EMAIL PROTECTED]
> > Sent: Friday, February 20, 2004 9:03 AM
> > To: [EMAIL PROTECTED]
> > Subject: Can't create a new thread (errno 11)
> >
> > Hello,
> >
> > I'm having problems with a 4.0.18 MySQL Server. Since this
afternoon I
> > get the error "Can't create a new thread (errno 11)" when MySQL
has
> > forked about 200-300 threads.
> >
> > What does it mean? I'm not running out of RAM or disk space.
> >
> > Best Regards,
> > Christof
> >
> >
> >
>
>
> -- 
> ___
> Configo Systems GmbH - Jahnstr. 62 - 64285 Darmstadt - DE
> Tel +49.6151.39656.0 Fax .49 http://www.configo.de
>
>
> -- 
> 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]

Table Name Case Sensitivity

2004-02-23 Thread Tim Hayes

Can anyone offer advice?

I have come across a MySQL database on Linux with duplicate table names - "Accounts" 
and "accounts". This seems fine on Linux, but does not transfer to the Windows 
environment - it is rejected because of the duplicate name. However I do see that 
Column Names have to be unique in both environments.

Q. Is there some sort of practical advantage for having case sensitive table names in 
Linux, or is this a design "shortcoming"? Postgress rejects duplicate table names.

Thanks
Tim Hayes

Re: Newbie question

2004-02-23 Thread Brad Eacker

Donny Simonton <[EMAIL PROTECTED]> writes:
>But as a manager, the first thing I do is ask for code examples, as well as
>table structures.  I don't even need to see a resume once I look at
>somebody's code and table structures.

 Unfortunately I will have to disagree with you on this one.  Having
been searching for solid employment for the last year, I'm finding that the
managers seek only to place someone who has done the exact job they are
trying to fill.  And this is with 22 years of relevant experience on my
part.  8 years of web-back integration, 13 years inside the unix kernel,
coupled with 9 years of RDBMS experience don't seem to hold much weight
anymore.

 You mention examples, I have a pair I use having fully developed both
sets

http://www.misc.com/costumes/Dickens_0312/index.php
http://66.134.203.2/~beacker/genbank/gbf_extract.cgi?LOCUS=AF158101

The first being a photographic PHP based mechanism that I use in at least
a dozen pages without change.  The second being a 30 million record data
set I utilize for selecting GenBank info.  I'm finding it very frustrating
that my knowledge and capabilities are not found to be compelling.  I have
utilized C for over 21 years, and perl for 20 years.  Yet these don't
appear to be enough to get a position in Silicon Valley.

   Brad Eacker ([EMAIL PROTECTED])

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

Re: HEAP tables vs MYISAM on ramdisk

2004-02-23 Thread Eric B.

How are you ensuring syncronization between the ram disk and the HD?  Is
there a writeback / writethrough mechanism for ram disks?  Are you not
risking major data loss if ever you have a power failure or PC failure?

Thanks for the info!

Eric

"Mark Maunder" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> Since HEAP tables don't support fulltext indexes, is moving MYISAM
> tables to ramdisk an acceptable workaround?
>
> On Sat, 2004-02-21 at 18:35, Mark Maunder wrote:
> > I've noticed a 4 times insert speed improvement by moving the MYI index
> > file of a myisam table to a ramdisk. The MYD file is still on a physical
> > disk, and I benchmarked the difference between moving just the index
> > file, or moving both, and it was only a 10% difference in speed. The
> > table has a large fulltext index.
> >
> > Has anyone else played with moving MYI files to ramdisk for performance?
> > Any caveats that you know of, besides running out of ramdisk space?
> >
> >
> -- 
> Mark Maunder <[EMAIL PROTECTED]>
> ZipTree.com
>
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
>
>




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

Re: Re: Trouble withg replication

2004-02-23 Thread Liying Huang

Hi, Sasha:

I got replication working now. Thanks veryyy much for your suggestion.

Liying



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

Re: Urgent: Prepared Statements C API hangup the Server - Help Urgent....

2004-02-23 Thread Arunachalam

Hi,

I have written in intermediate, on the appropriate position in this 
discussion, please go thru in full.

 --- Michael Widenius <[EMAIL PROTECTED]> wrote: > 
> hi!
> 
> > "A" == Arunachalam  <[EMAIL PROTECTED]> writes:
 
> A> As per my searching I have found the right C API in libmysql.lib from
> A> MySQL 5.0.0, (i have doubt: Is any eariler version 4.0.xx provides this???)
> A> C API routines are: mysql_prepare(), mysql_bind_param(), mysql_execute(), 
> A> mysql_stmt_affected_rows(), mysql_fetch(), mysql_stmt_close()...
> 
> You should probablty use MySQL 4.1 instead of MySQL 5.0 for testing
> this as we make more often releases for 4.1 than for 5.0 (until 4.1 is
> production ready).


I didn't find the API's what I have specified above in MySQL 4.1.1 in 
*libmysql.dll*,  but those API's are provided by *mysqlclient.lib* in 
this version. 

I have created COBOL specific *libmysql.lib* from existing *libmysql.dll* 
becaz libmysql is Dynamic Access Library - right.  

(what I have did to convert libmysql.dll file into COBOL compiler 
specific *libmysql.lib* is by using an utility *pexports.exe*, i have
convert given libmysql.dll into libmysql.def file then by using LIB.EXE
of COBOL compiler convert libmysql.def into libmysql.lib file.)



> A> I have installed MySQL 5.0.0-alpha for Windows and I have properly linked 
> A> thelibmysql.lib into my COBOL compiler... To verify the Parameter Passing 
> A> mechanism to the API routines i have taken the sample C code from MySQL 
> A> manual (URL: * http://www.mysql.com/doc/en/mysql_execute.html * )
> 
> A> When I compiled this sample coding using VC++, it ask for *typelib.h* header 
> A> file, it is not given with insatllation pack, under the Mysql folder 
> A> (C:/MySQL/Include), I have searched on the net and include it accordingly.
> 
> Thanks for notifying us about this. typelib.h should be included in
> the MySQL folder and we will do that for next MySQL windows release.
> (4.1.2)
> 
> A> After that i have made changes in some variable names becaz of it follows 
> A> other header files variable name as such.
> 
> What changes did you have to make ?
> Including typelib.h should be enough.

When I try to first compile this program in VC++ it notify the mismatch
variable as 

MYSQL_BINDbind[3];
which is already used by  header file, so I have change this variable
into *Pbind* like this. (I have doubt: after your discussion i once again compiled
it with previous name(i.e, bind) it never produce any error).

The variable mysql used in other API's to be changed to *&mysql*, I have changed 
this after meeting the following errors;

D:\vcprojects\mysql\src\source.c(41) : error C2115: 'function' :  incompatible types
D:\vcprojects\mysql\src\source.c(41) : warning C4024: 'mysql_query' : different types 
for formal
and actual parameter 1
D:\vcprojects\mysql\src\source.c(44) : error C2115: 'function' : incompatible types
D:\vcprojects\mysql\src\source.c(44) : warning C4024: 'mysql_error' : different types 
for formal
and actual parameter 1
D:\vcprojects\mysql\src\source.c(48) : error C2115: 'function' : incompatible types
D:\vcprojects\mysql\src\source.c(58) : warning C4024: 'mysql_prepare' : different 
types for formal
and actual parameter 1
D:\vcprojects\mysql\src\source.c(62) : error C2115: 'function' : incompatible types
 
> I will ask our windows team to verify that the example works ok with
> VC++
> 
> A> Upon all the changes I can able to execute succcessfully the coding and it 
> A> does the function properly on the MySQL server database.
> 
> A> The PROBLEM I met is, Once this sample code executed and inserted data to 
> A> the Database the server get's hanged - why???


After the discussion with you only i came to listen mysqld.trace, but it's content 
cann't be understand by me, herewith I have attached that file for your reference.
anyhow i understand that there is some Memory alloca/dealloc problem hangs up my
MySQL server.

 
> This shouldn't happen.
> Can you please describe exactly what you mean with a 'hang' ?
> 
> - Does the connection in your test program stop respoding ?
Yes, if I run the client C program again, the console ouput stay blank
with out any message.

> - Can you connect to mysqld with other clients ?
No, WinMySQLAdmin tool won't allow me to right click on it
mysql -u user -ppass also stay as such when i try it from console 

> - Does the MySQL server take all cpu
No, I can work with other application with out any trouble.

I hope I have given here enough information to identify my trouble, suggest me 
in detail ASAP please...

Thanks a lot...

Regards,
Arun.


Yahoo! India Insurance Special: Be informed on the best policies, services, tools and 
more. 
Go to: http://in.insurance.yahoo.com/licspecial/index.html
-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysq

Re: Concat. operator ||

2004-02-23 Thread Victoria Reznichenko

Hassan Shaikh <[EMAIL PROTECTED]> wrote:
> 
> I am running MySQL in ANSI standard mode. However the following 
> statement is giving unexpected result. Am I missing something?
> 
>mysql> select 'This is a' || ' test string';
>+---+
>| 'This is a' || ' test string' |
>+---+
>| 0 |
>+---+
>1 row in set (0.00 sec)
> 
> Here's a part of my my.ini, indicating that I am in ANSI mode.
> 
> # The MySQL server
> [mysqld]
> port=3306
> #socket=MySQL
> skip-locking
> set-variable= sql-mode=ansi
> set-variable= key_buffer=32M
> set-variable= max_allowed_packet=1M
> set-variable= table_cache=32
> set-variable= net_buffer_length=8K
> set-variable= sort_buffer=1M
> set-variable= record_buffer=512K
> set-variable= myisam_sort_buffer_size=4M
> set-variable= thread_cache=8
> # Try number of CPU's*2 for thread_concurrency
> set-variable= thread_concurrency=8
> log-bin
> server-id= 1
> 

What version of MySQL do you use?
If you use version < 4.1 , you should put in the my.cnf

[mysqld]
ansi
...

or

[musqld]
sql-mode=REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY
transaction-isolation=SERIALIZABLE
..


-- 
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: Concat. operator ||

2004-02-23 Thread Rhino

I get the same thing on our system (MySQL 4.0.15 running on Linux Mandrake
9.1).

According to the manual, section 1.8.4:

"MySQL Server understands the || and && operators to mean logical OR and
AND, as in the C programming language. In MySQL Server, || and OR are
synonyms, as are && and AND. Because of this nice syntax, MySQL Server
doesn't support the standard SQL-99 || operator for string concatenation;
use CONCAT() instead. Because CONCAT() takes any number of arguments, it's
easy to convert use of the || operator to MySQL Server."

If you rewrite your query as follows, you should get the result you wanted:

select concat('This is a ', 'test string');

Rhino

- Original Message - 
From: "Hassan Shaikh" <[EMAIL PROTECTED]>
To: "MySQL (General List)" <[EMAIL PROTECTED]>
Sent: Monday, February 23, 2004 7:58 AM
Subject: Concat. operator ||


> Hi All,
>
> I am running MySQL in ANSI standard mode. However the following
> statement is giving unexpected result. Am I missing something?
>
> mysql> select 'This is a' || ' test string';
> +---+
> | 'This is a' || ' test string' |
> +---+
> | 0 |
> +---+
> 1 row in set (0.00 sec)
>
> Here's a part of my my.ini, indicating that I am in ANSI mode.
>
> # The MySQL server
> [mysqld]
> port=3306
> #socket=MySQL
> skip-locking
> set-variable= sql-mode=ansi
> set-variable= key_buffer=32M
> set-variable= max_allowed_packet=1M
> set-variable= table_cache=32
> set-variable= net_buffer_length=8K
> set-variable= sort_buffer=1M
> set-variable= record_buffer=512K
> set-variable= myisam_sort_buffer_size=4M
> set-variable= thread_cache=8
> # Try number of CPU's*2 for thread_concurrency
> set-variable= thread_concurrency=8
> log-bin
> server-id= 1
>
> Thanks.
>
>
> Hassan
>
> -- 
> 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]

Bug on MYSQL-5.0 in statement "OPTIMIZE TABLE"

2004-02-23 Thread Gelu Gogancea

Title: Bug on MYSQL-5.0 in statement "OPTIMIZE TABLE"






Hi,

TABLE CREATE DESCRIPTION :

CREATE TABLE T1(IDAP INT(16),TVAL INT(2),CH_VAL_SEC INT(16),INDEX(IDAP),INDEX(TVAL),INDEX(CH_VAL_SEC));


RUNNING CONDITIONS:

-use persistent connection.


RUNNING SEQUENCE:

1-Open 1-st connection.

2-insert in table many values at once time with "delayed" option;

"insert delayed into T1 (IDAP,TVAL,CH_VAL_SEC) VALUES(1,-2,100),(2,-3,101),(3,-4,102);"

3-I let 1-st connection open.Open 2-nd connection to execute the  next 2 statement.

4-delete all data from table :

"delete from T1;"

5-optimize the table :

"optimize table T1;"

MySQL daemon crash on this last statement if i use PERSISTENT CONNECTION, otherwise is OK.

From the mysql daemon log:


040223 14:59:49  mysqld started

040223 14:59:50  InnoDB: Started; log sequence number 0 43634

040223 14:59:50  /mysql/mysql-5.0.bin/libexec/mysqld: Can't create/write to file '/var/run/mysqld/mysqld.pid' (Errcode: 2)

040223 14:59:50  Found 4.1 style password for user '[EMAIL PROTECTED]'. Ignoring user. You should change password for this user.

/mysql/mysql-5.0.bin/libexec/mysqld: ready for connections.

Version: '5.0.0-alpha'  socket: '/mysql/mydatabase/mysql.sock'  port: 3306

mysqld got signal 11;

This could be because you hit a bug. It is also possible that this binary

or one of the libraries it was linked against is corrupt, improperly built,

or misconfigured. This error can also be caused by malfunctioning hardware.

We will try our best to scrape up some info that will hopefully help diagnose

the problem, but since we have already crashed, something is definitely wrong

and this may fail.


key_buffer_size=8388600

read_buffer_size=131072

max_used_connections=2

max_connections=100

threads_connected=3

It is possible that mysqld could use up to 

key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 225791 K

bytes of memory

Hope that's ok; if not, decrease some variables in the equation.


thd=0x85b00a8

Attempting backtrace. You can use the following information to find out

where mysqld died. If you see no messages after this, something went

terribly wrong...

Cannot determine thread, fp=0x41da4ee4, backtrace may not be correct.

Stack range sanity check OK, backtrace follows:

0x813c7c4

0x4004e618

0x40049dea

0x4202726c

0x40049dea

0x8185301

0x400492b6

0x420de407

New value of fp=(nil) failed sanity check, terminating stack trace!

Please read http://www.mysql.com/doc/en/Using_stack_trace.html and follow instructions on how to resolve the stack trace. Resolved

stack trace is much more helpful in diagnosing the problem, so please do 

resolve it

Trying to get some variables.

Some pointers may be invalid and cause the dump to abort...

thd->query at 0x8584920 = O.H..B.

thd->thread_id=4

The manual page at http://www.mysql.com/doc/en/Crashing.html contains

information that should help you find out what is causing the crash.

040223 15:01:37  mysqld ended


MY.CNF FILE :

[mysqld]

datadir=/mysql/mydatabase

socket=/mysql/mydatabase/mysql.sock

 

[mysql.server]

user=mysql

basedir=/mysql/mydatabase


[safe_mysqld]

err-log=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid


OS ENVIRONMENT:  RED HAT 8.0

HARDWARE :  Pentium IV 1,7 Ghz,512 Mb RAM,2 x 40GB EIDE HDD


Regards,

Gelu


_
G.NET SOFTWARE COMPANY

SYSTEM INTEGRATOR - AUTOMATION & SOFTWARE DEVELOPER

Permanent e-mail address :

[EMAIL PROTECTED] 

[EMAIL PROTECTED]



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

Concat. operator ||

2004-02-23 Thread Hassan Shaikh

Hi All,

I am running MySQL in ANSI standard mode. However the following 
statement is giving unexpected result. Am I missing something?

   mysql> select 'This is a' || ' test string';
   +---+
   | 'This is a' || ' test string' |
   +---+
   | 0 |
   +---+
   1 row in set (0.00 sec)
Here's a part of my my.ini, indicating that I am in ANSI mode.

# The MySQL server
[mysqld]
port=3306
#socket=MySQL
skip-locking
set-variable= sql-mode=ansi
set-variable= key_buffer=32M
set-variable= max_allowed_packet=1M
set-variable= table_cache=32
set-variable= net_buffer_length=8K
set-variable= sort_buffer=1M
set-variable= record_buffer=512K
set-variable= myisam_sort_buffer_size=4M
set-variable= thread_cache=8
# Try number of CPU's*2 for thread_concurrency
set-variable= thread_concurrency=8
log-bin
server-id= 1
Thanks.

Hassan

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

Re: [4.1.1] 1062 errors on non-unique index during data load

2004-02-23 Thread Victoria Reznichenko

mark warren bracher <[EMAIL PROTECTED]> wrote:
> heh, sometimes the obvious is right in front of you...
> 
> I still can't get the full dataset to load with indexes on the table, 
> but I _can_ apply indexes after the load.  the indexes apply cleanly, so 
> it doesn't seem to have been a data issue.  in any case, I can continue 
> prototyping.
> 
> it remains to be seen whether this will crop back up during incremental 
> update of the already filled (and indexed) table, but I've got a good 
> bit of coding to do before I'll have an answer to that question...
> 
> - mark
> 
> mark warren bracher wrote:
>> In the last few days I've been doing some prototyping on mysql 4.1.1 
>> (mainly because I want fulltext indexes against utf-8 data).
>> 
>> I have a table, artists_search_A, in which I want to load ~100K records. 
>>  My load process will routinely load 14783 records successfully.  After 
>> 14783 inserts, any insert attempt results in a 1062 error, duplicate 
>> entry; specifically,
>> 
>> Duplicate entry 'en-us' for key 2 error 1062 recorded
> [snip]
> 

Could you create repeatable test case (table structure (output of SHOW CREATE TABLE) 
and text file with data that will be enough to reproduce the above behavior)?


-- 
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: Want to understand MySQL Code? Where to start...

2004-02-23 Thread Stefan Hinz

Karam,

> I am CS student. I want to understand the architecture
> and code of MySQL (guess the subjects says it
> all)...What do you think will be the best place to
> start off?

> Any docs on the architecture of MySQL?

You may want to have a look at internals.texi. As its name implies,
this is a document that deals with MySQL internals. It's not (yet)
available in HTML, but you can get it from the mysqldoc BitKeeper
tree. The procedure how to clone that tree is described on:

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

(near the bottom of that page)

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

[filter fodder: sql, mysql, query]


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

Re: Want to understand MySQL Code? Where to start...

2004-02-23 Thread Egor Egorov

Karam Chand <[EMAIL PROTECTED]> wrote:
> Hello
> 
> I am CS student. I want to understand the architecture
> and code of MySQL (guess the subjects says it
> all)...What do you think will be the best place to
> start off?
> 

Download and install BitKeeper. Then clone MySQL manual with command:
bk clone bk://mysql.bkbits.net/mysqldoc mysqldoc

After you execute the following commands, you will see internals.texi file in the Docs 
directory
cd mysqldoc/
bk -r edit
cd Docs/





-- 
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: GRANT question

2004-02-23 Thread Alec . Cawley








"Rhino" <[EMAIL PROTECTED]> wrote on 21/02/2004 16:59:27:

> I am new to most aspects of MySQL administration so I was wondering
> if someone can help me figure out what GRANTs I need for a
> particular situation.
>
> I have a user who needs to be able to run some MySQL scripts that
> create and load tables in a database named NFL. He is accessing
> MySQL remotely from a client on a Windows machine. Our server is
> running in Linux Mandrake 9.1; the client is Windows 98SE.
>
> I've run the following grants for him:
> grant all on NFL.* to [EMAIL PROTECTED] indentified by 'x';
> grant all on NFL.* to [EMAIL PROTECTED] identified by 'x';
> grant file on NFL.* to [EMAIL PROTECTED] identified by 'x';
> grant file on NFL.* to [EMAIL PROTECTED] identified by 'x';
>
> When I try signing on as him from my remote Windows client, he can
> connect to MySQL and get to the mysql prompt. He can run the script
> which creates and loads the tables. However, he gets "Access Denied"
> on the Load Data Infile statements when the script executes.
>
> 1. Why is this happening? The Load Data article says he needs the
> File privilege and I've given it to him. He also has all privileges
> on the NFL database. What more do I need to do for him?
>
> 2. Are the privileges given to [EMAIL PROTECTED] redundant with the
> privileges given to [EMAIL PROTECTED] They *look* redundant to me; I
> suspect I've misinterpreted something I read in the manual. Can I
> get by with giving him just the [EMAIL PROTECTED] privileges? Do I
> need to give him anything else to account for the fact that he is
> coming in from a remote client?

I note that you are running a mixed Windows/Linux system. My experience is
that using uppercase in such systems is a source of create problems. Since
database names and table names for MyISAM tables are file names, they are
case significant on Linux and non-significant on Windows. While I am sure
that there is an analytic way of handling the problems which arise, I found
that it was very difficult to understand and a source of avoidable
confusion. I would recommend that in any case where mixed *nix and Windows
systems are involved, you keep database and table names entirely to lower
case. The world will give you enough unavoidable problems - don't add an
avoidable one.

  Alec


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

79 matches

Mail list logo