Re: right side of UNION not reported faithfully

[Eric Prud'hommeaux]

On Mon, Nov 15, 2004 at 10:26:29PM -0600, Paul DuBois wrote:
> At 13:10 +0900 11/16/04, Eric Prud'hommeaux wrote:
> > >Description:
> > A UNION B reports the A results faithfully, but 0s and NULLs
> >in B's results aren't making it to the result set intact.
> >>How-To-Repeat:
> > SELECT 1 AS a, NULL AS b
> >UNION
> >SELECT NULL AS a, 1 AS b;
> >+---+--+
> >| a | b|
> >+---+--+
> >| 1 | NULL |
> >| 0 |  |
> >+---+--+
> >expected:
> >+--+--+
> >| a| b|
> >+--+--+
> >|1 | NULL |
> >| NULL |1 |
> >+--+--+
> >SELECT 1 AS a, NULL AS b, NULL AS c
> >UNION
> >SELECT NULL AS a, 1 AS b, NULL AS c
> >UNION
> >SELECT NULL AS a, NULL AS b, 1 AS c;
> >+---+--+--+
> >| a | b| c|
> >+---+--+--+
> >| 1 | NULL | NULL |
> >| 0 |  | NULL |
> >| 0 | NULL |  |
> >+---+--+--+
> >contrast with:
> >SELECT 1 AS a, 2 AS b, 2 AS c
> >UNION
> >SELECT 2 AS a, 1 AS b, 2 AS c
> >UNION
> >SELECT 2 AS a, 2 AS b, 1 AS c;
> >+---+---+---+
> >| a | b | c |
> >+---+---+---+
> >| 1 | 2 | 2 |
> >| 2 | 1 | 2 |
> >| 2 | 2 | 1 |
> >+---+---+---+
> >which is what I expected.
> >
> >>Fix:
> > look for result sets being set instead of set to a truth value.
> >
> >>Submitter-Id:   
> >>Originator: Eric Prud'hommeaux
> >>Organization:
> >  -eric
> >
> >  office: +1.617.258.5741 NE43-344, MIT, Cambridge, MA 02144 USA
> >  cell:   +1.857.222.5741
> >
> >  ([EMAIL PROTECTED])
> >  Feel free to forward this message to any list for any purpose other than
> >  email address distribution.
> >>
> >>MySQL support: none
> >>Synopsis:   right side of UNION not reported faithfully
> >>Severity:   serious
> >>Priority:   medium
> >>Category:   mysql
> >>Class:  sw-bug
> >>Release:mysql-4.0.22 (Source distribution)
> 
> This is a problem that's fixed in MySQL 4.1.  (4.1.2, I believe)

I can confirm this (sorry I didn't test before submitting):
after a make install:

unagi:/usr/local/src/mysql-4.1.8-nightly-20041115$ 
LD_LIBRARY_PATH=/usr/local/lib/mysql /usr/local/bin/mysqld_safe &
Starting mysqld daemon with databases from /usr/local/var
[1] 24404
unagi:/usr/local/src/mysql-4.1.8-nightly-20041115$ 
LD_LIBRARY_PATH=/usr/local/lib/mysql /usr/local/bin/mysql test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.8-nightly-20041115

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

mysql> SELECT 1 AS a, NULL AS b, NULL AS c UNION SELECT NULL AS a, 1 AS b, NULL 
AS c UNION SELECT NULL AS a, NULL AS b, 1 AS c;
+--+--+--+
| a| b| c|
+--+--+--+
| 1| NULL | NULL |
| NULL | 1| NULL |
| NULL | NULL | 1|
+--+--+--+
3 rows in set (0.01 sec)

-- 
-eric

office: +81.466.49.1170 W3C, Keio Research Institute at SFC,
Shonan Fujisawa Campus, Keio University,
5322 Endo, Fujisawa, Kanagawa 252-8520
JAPAN
+1.617.258.5741 NE43-344, MIT, Cambridge, MA 02144 USA
cell:   +1.857.222.5741 (does not work in Asia)

([EMAIL PROTECTED])
Feel free to forward this message to any list for any purpose other than
email address distribution.


signature.asc
Description: Digital signature

TCP No Longer Working

[Doug Peters]

I suddenly have lost the ability to connect to the MySQL server from my
remote host and can not figure out what is wrong.  I have searched for
the last couple of days and cannot figure out why.

 

I am running MySql 5.0.0 on Linux Suse 9.1

I normally connect to MySQL from Windows XP Pro using either the .Net
connector and/or the ODBC connector.

 

When I check the variables it shows that the port is set to 3306

 

But when I run the following it appears that TCP is not working
correctly. 

linux:~ # netstat -l | grep mysql

tcp0  0 *:mysql *:*
LISTEN

unix  2  [ ACC ] STREAM LISTENING 4671
/var/lib/mysql/mysql.sock

 

If I log in via SSH or from a shell on the Linux box itself I can
connect to the database in any of the following ways:
mysql -u user_name -p database_name

mysql -u user_name -p -h localhost database_name

mysql -u user_name -p -h 127.0.0.1 database_name

mysql -u user_name -p -h the_static_ip_address database_name

 

As soon as I try to connect from Windows XP using the .Net connector,
the ODBC connector, MySQL Query Browser or MySQL Administrator I get the
following error:

Could not connect to the specified instance.

 

MySQL Error Nr. 2003

Can't connect to MySQL server on 'xxx.xxx.xxx.xxx' (10060)

 

I am able to successfully ping the IP address of the Linux machine.

 

Here is what is listed in the /etc/my.cnf file

 

[mysqld]

datadir=/var/lib/mysql

port=3306

socket=/var/lib/mysql/mysql.sock

 

[mysql.server]

basedir=/usr/bin

 

 

Any assistance is greatly appreciated.

 

Doug

Re: What changed in 4.1.7 to break DBD::Mysql

[William R. Mussatto]

Patrick Galbraith said:
> William,
>
> What was the error that you had specifically? Did you use a precompiled
> version of DBD::mysql like a RPM, or did you build it yourself? I don't
> know if this is a DBD version issue that you are experiencing, because
> nothing was changed in authentication protocol. The main thing that
> I've coded into the newer version of DBD::mysql is support for prepared
> statements.
>
> regards,
>
> Patrick
The DBD::mysql is from Active State's repository via the ppm auto install
process.  The version they supply supports only the older short passwords.
Since I missed the change in 4.1.x to longer passwords I was unable to
connect to the newly installed mysql using the DBI interface.  I'm not
sure of clean way other than what I did (force the use of old_password's. 
I have other boxes which run older mysql which have to support the old
interfaces.  The only think I can think to do (assuming you can't ask
which version is supported) is to try the long and then the short version
of the passwords, or to allow the default behavior of the DBI-> connect
function to be altered by a parameter.
> On Nov 15, 2004, at 5:14 PM, William R. Mussatto wrote:
>
>> Mark Matthews said:
>>> -BEGIN PGP SIGNED MESSAGE-
>>> Hash: SHA1
>>>
>>> William R. Mussatto wrote:
 Keith Ivey said:

> William R. Mussatto wrote:
>
>
>> I've been googling for 1/2 hr w/o any answers.  sorry if I've
>> missed
>> the obvious.
>> Problem. Fresh install of mysql 4.7.1, AS perl 5.8
>> DBI and DBD-Mysql via ppm.
>> Client does not support authnticaiton protocol
>>
>
> What version of MySQL were you using previously?  If it was 4.1.0
> or earlier, then this
> might be useful reading:
>
> http://dev.mysql.com/doc/mysql/en/Password_hashing.html
>
> --
> Keith Ivey <[EMAIL PROTECTED]>
> Washington, DC

 Thanks, I'll check it out.  I did the development under linux using
 3.23.xx and perl 5.6 Basically debian stable.

 From the article it looks like I'll have to fall back to a 4.0
 version.

 I was hoping, but using a clean install on the windows box to avoid
 these issues.
 I'll try  SET PASSWORD FOR 'some_user'@'some_host' =
 OLD_PASSWORD('mypass');
>>>
>>> William,
>>>
>>> You'll need a copy of DBD::MySQL that's linked with libmysql from 4.1
>>>  to
>>> get around this issue (and use the new server-side prepared
>>> statements as well). Patrick Galbraith (a MySQL engineer) has pushed
>>> the required changes into the CVS repo of DBD::MySQL, however that
>>> hasn't been released yet as a binary by the DBD::MySQL maintainer
>>> (DBD::MySQL version  2.9015)
>>>
 Thanks that worked, now to see what happends when I try
 w/connector/J
>>>
>>> As long as you're using a recent version of Connector/J (3.0.15 or
>>> 3.1.4), it'll work fine.
>>>
>>> Regards,
>>>
>>> -Mark
>>>
>>>
>>> - --
>>> Mark Matthews
>>> MySQL AB, Software Development Manager - Client Connectivity
>>> Office: +1 708 332 0507
>>> www.mysql.com
>> Thanks, I figured I just hit it at the wrong time (4.1.7 out but DBD
>> not
>> yet updated).   the connector/j just came down from mysql.com so it
>> should
>> be current.  I do most of my production work on debian linux but
>> wanted to
>> give this client the current stuff.
>>
>> --
>> William R. Mussatto, Senior Systems Engineer
>> Ph. 909-920-9154 ext. 27
>> FAX. 909-608-7061
>>
>>
> Patrick Galbraith Senior Software Developer
> [EMAIL PROTECTED] http://www.mysql.com
>
> "Whatever action a great man performs, common men follow. Whatever
> standards he sets by exemplary acts, all the world pursues"  --
> Bhagavad Gita




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

Mysql taking 100% Mem utilisation

[Naveen C Joshi]

Hi,

I have installed MySQL-4.0.5-0 version on my RH Linux-7.1 box. I am using the 
innodb database with tables (.frm, .MYD & .MYI ).  My system's memory is around 
3 GB ( 3798748K ). But I am facing problem  of memory utilization. The "top" 
output is as below ;

Mem:  3798748K av, 2378268K used, 1420480K free,   0K shrd,7964K buff
Swap: 2096220K av,   0K used, 2096220K free  509892K cached
  PID USER PRI  NI  SIZE  RSS SHARE STAT %CPU %MEM   TIME COMMAND
  990 mysql 20   0 1756M 1.7G  2060 R21.0 47.3 135:53 mysqld
  883 mysql 20   0 1756M 1.7G  2060 R20.9 47.3 136:15 mysqld
  879 mysql 20   0 1756M 1.7G  2060 R20.6 47.3 135:49 mysqld
  902 mysql 14   0 1756M 1.7G  2060 R20.3 47.3 136:18 mysqld
22803 root  17   0  1088 1088   840 R13.3  0.0   0:11 top
  866 mysql  9   0 1756M 1.7G  2060 S 2.4 47.3   1:33 mysqld
  897 mysql  9   0 1756M 1.7G  2060 S 1.7 47.3   1:32 mysqld
  898 mysql  9   0 1756M 1.7G  2060 S 1.3 47.3   1:32 mysqld
  877 mysql  9   0 1756M 1.7G  2060 S 0.7 47.3   0:35 mysqld
  901 mysql  9   0 1756M 1.7G  2060 S 0.4 47.3   0:35 mysqld
  900 mysql  9   0 1756M 1.7G  2060 S 0.2 47.3   0:36 mysqld
  875 mysql  9   0 1756M 1.7G  2060 S 0.1 47.3   0:34 mysqld
1 root   8   0   544  544   472 S 0.0  0.0   0:04 init

My configuration (my.cnf) file of mysql server is as below :


[client]
port= 3306
socket  = /var/lib/mysql/mysql.sock
[mysqld]
datadir = /xxx/yyy
basedir = /
port= 3306
socket  = /var/lib/mysql/mysql.sock
skip-locking
set-variable= key_buffer=768M
set-variable= max_allowed_packet=1M
set-variable= table_cache=512
set-variable= sort_buffer=9M
set-variable= record_buffer=3M
set-variable= thread_cache=8
set-variable= max_connections=150
set-variable= thread_concurrency=8
set-variable= myisam_sort_buffer_size=64M
set-variable= thread_stack=256k
log-bin
server-id   = 1
binlog-do-db= sms110
slave-skip-errors   =   all
innodb_data_home_dir = /xxx/yyy/
innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
innodb_log_group_home_dir = /xxx/yyy/
innodb_log_arch_dir = /xxx/yyy/
set-variable = innodb_buffer_pool_size=1500M
set-variable = innodb_additional_mem_pool_size=20M
set-variable = innodb_log_file_size=400M
set-variable = innodb_log_buffer_size=10M
innodb_flush_log_at_trx_commit=1
set-variable = innodb_lock_wait_timeout=50
set-variable= innodb_file_io_threads=4
transaction-isolation   = READ-COMMITTED
innodb_thread_concurrency   = 4
[mysqldump]
quick
set-variable= max_allowed_packet=16M
[mysql]
no-auto-rehash
[isamchk]
set-variable= key_buffer=256M
set-variable= sort_buffer=256M
set-variable= read_buffer=2M
set-variable= write_buffer=2M
[myisamchk]
set-variable= key_buffer=256M
set-variable= sort_buffer=256M
set-variable= read_buffer=2M
set-variable= write_buffer=2M
[mysqlhotcopy]
interactive-timeout

Please advise me where I have to made changes in config file for the best 
performence of  mysql server.

Regards

Naveen
 

Re: right side of UNION not reported faithfully

[Paul DuBois]

At 13:10 +0900 11/16/04, Eric Prud'hommeaux wrote:
 >Description:
A UNION B reports the A results faithfully, but 0s and NULLs
in B's results aren't making it to the result set intact.
How-To-Repeat:
SELECT 1 AS a, NULL AS b
UNION
SELECT NULL AS a, 1 AS b;
+---+--+
| a | b|
+---+--+
| 1 | NULL |
| 0 |  |
+---+--+
expected:
+--+--+
| a| b|
+--+--+
|1 | NULL |
| NULL |1 |
+--+--+
SELECT 1 AS a, NULL AS b, NULL AS c
UNION
SELECT NULL AS a, 1 AS b, NULL AS c
UNION
SELECT NULL AS a, NULL AS b, 1 AS c;
+---+--+--+
| a | b| c|
+---+--+--+
| 1 | NULL | NULL |
| 0 |  | NULL |
| 0 | NULL |  |
+---+--+--+
contrast with:
SELECT 1 AS a, 2 AS b, 2 AS c
UNION
SELECT 2 AS a, 1 AS b, 2 AS c
UNION
SELECT 2 AS a, 2 AS b, 1 AS c;
+---+---+---+
| a | b | c |
+---+---+---+
| 1 | 2 | 2 |
| 2 | 1 | 2 |
| 2 | 2 | 1 |
+---+---+---+
which is what I expected.
Fix:
look for result sets being set instead of set to a truth value.
Submitter-Id:   
Originator: Eric Prud'hommeaux
Organization:
  -eric
  office: +1.617.258.5741 NE43-344, MIT, Cambridge, MA 02144 USA
  cell:   +1.857.222.5741
  ([EMAIL PROTECTED])
  Feel free to forward this message to any list for any purpose other than
  email address distribution.
MySQL support: none
Synopsis:   right side of UNION not reported faithfully
Severity:   serious
Priority:   medium
Category:   mysql
Class:  sw-bug
Release:mysql-4.0.22 (Source distribution)
This is a problem that's fixed in MySQL 4.1.  (4.1.2, I believe)
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Connecting to MySQL using C API

[Aftab Jahan Subedar]

Have you tried my MySQL C API Site ?
I have posted some C API examples here.
http://www.geocities.com/jahan.geo
Aftab Jahan Subear
Subedar Technologies
Subedar Baag
Bibir Bagicha #1
North Jatrabari
Dhaka 1204
Bangladesh
http://www.DhakaStockExchangeGame.com
Tel: +880-2-7519050
/*
Get customized T-Shirts with logo for your IT company for 12 units @ 
US$14 ~ US$16.
Order quantity must be atleast USD$5000++.
IT company to  IT company.
*/

premal mishra wrote:
I'm using MySQL version 4.0.20a on win98.
Compiler is Bloodshed Dev-C++ version 4.
My program compiles and links but gives a page fault error on running.
I'm linking the '\mysql\lib\opt\mysqlclient.lib' file.
Linker gives the warning >> ignoring duplicate section '.text'
CODE:
#include 
#include 
#include 
int main(int argc, char *argv[])
{
MYSQL mysql;
MYSQL *connection;
connection= mysql_real_connect(&mysql, "localhost", "root", "premal", "test", 
0, NULL, 0);
   return 0;
}
 
Regards
Premal.



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

right side of UNION not reported faithfully

[Eric Prud'hommeaux]

>Description:
A UNION B reports the A results faithfully, but 0s and NULLs
in B's results aren't making it to the result set intact.
>How-To-Repeat:
SELECT 1 AS a, NULL AS b 
UNION 
SELECT NULL AS a, 1 AS b;
+---+--+
| a | b|
+---+--+
| 1 | NULL |
| 0 |  |
+---+--+
expected:
+--+--+
| a| b|
+--+--+
|1 | NULL |
| NULL |1 |
+--+--+
SELECT 1 AS a, NULL AS b, NULL AS c 
UNION 
SELECT NULL AS a, 1 AS b, NULL AS c 
UNION 
SELECT NULL AS a, NULL AS b, 1 AS c;
+---+--+--+
| a | b| c|
+---+--+--+
| 1 | NULL | NULL |
| 0 |  | NULL |
| 0 | NULL |  |
+---+--+--+
contrast with:
SELECT 1 AS a, 2 AS b, 2 AS c 
UNION 
SELECT 2 AS a, 1 AS b, 2 AS c 
UNION 
SELECT 2 AS a, 2 AS b, 1 AS c;
+---+---+---+
| a | b | c |
+---+---+---+
| 1 | 2 | 2 |
| 2 | 1 | 2 |
| 2 | 2 | 1 |
+---+---+---+
which is what I expected.

>Fix:
look for result sets being set instead of set to a truth value.

>Submitter-Id:  
>Originator:Eric Prud'hommeaux
>Organization:
  -eric
  
  office: +1.617.258.5741 NE43-344, MIT, Cambridge, MA 02144 USA
  cell:   +1.857.222.5741
  
  ([EMAIL PROTECTED])
  Feel free to forward this message to any list for any purpose other than
  email address distribution.
>
>MySQL support: none
>Synopsis:  right side of UNION not reported faithfully
>Severity:  serious
>Priority:  medium
>Category:  mysql
>Class: sw-bug
>Release:   mysql-4.0.22 (Source distribution)
>Server: /usr/bin/mysqladmin  Ver 8.40 Distrib 4.0.22, for pc-linux on i386
Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license

Server version  4.0.22-log
Protocol version10
Connection  Localhost via UNIX socket
UNIX socket /var/run/mysqld/mysqld.sock
Uptime: 2 days 16 hours 48 min 4 sec

Threads: 2  Questions: 324  Slow queries: 0  Opens: 56  Flush tables: 1  Open 
tables: 43  Queries per second avg: 0.001
>C compiler:gcc (GCC) 3.3.5 (Debian 1:3.3.5-2)
>C++ compiler:  gcc (GCC) 3.3.5 (Debian 1:3.3.5-2)
>Environment:

System: Linux unagi.w3.org 2.6.7t42 #1 Tue Nov 2 02:11:32 JST 2004 i686 
GNU/Linux
Architecture: i686

Some paths:  /usr/bin/perl /usr/bin/make /usr/bin/gcc /usr/bin/cc
GCC: Reading specs from /usr/lib/gcc-lib/i486-linux/3.3.5/specs
Configured with: ../src/configure -v 
--enable-languages=c,c++,java,f77,pascal,objc,ada,treelang --prefix=/usr 
--mandir=/usr/share/man --infodir=/usr/share/info 
--with-gxx-include-dir=/usr/include/c++/3.3 --enable-shared --with-system-zlib 
--enable-nls --without-included-gettext --enable-__cxa_atexit 
--enable-clocale=gnu --enable-debug --enable-java-gc=boehm 
--enable-java-awt=xlib --enable-objc-gc i486-linux
Thread model: posix
gcc version 3.3.5 (Debian 1:3.3.5-2)
Compilation info: CC='gcc'  CFLAGS='-DBIG_JOINS=1'  CXX='gcc'  
CXXFLAGS='-DBIG_JOINS=1 -felide-constructors -fno-rtti'  LDFLAGS=''  ASFLAGS=''
LIBC: 
lrwxrwxrwx  1 root root 13 2004-11-02 01:19 /lib/libc.so.6 -> libc-2.3.2.so
-rw-r--r--  1 root root 1244080 2004-10-14 04:40 /lib/libc-2.3.2.so
-rw-r--r--  1 root root 2640954 2004-10-14 04:41 /usr/lib/libc.a
-rw-r--r--  1 root root 204 2004-10-14 04:16 /usr/lib/libc.so
Configure command: ./configure '--build=i386-linux' '--host=i386-linux' 
'--prefix=/usr' '--exec-prefix=/usr' '--libexecdir=/usr/sbin' 
'--datadir=/usr/share' '--sysconfdir=/etc/mysql' 
'--localstatedir=/var/lib/mysql' '--includedir=/usr/include' 
'--infodir=/usr/share/info' '--mandir=/usr/share/man' '--enable-shared' 
'--enable-static' '--enable-thread-safe-client' '--enable-assembler' 
'--enable-local-infile' '--with-raid' 
'--with-unix-socket-path=/var/run/mysqld/mysqld.sock' 
'--with-mysqld-user=mysql' '--with-libwrap' '--with-client-ldflags=-lstdc++' 
'--with-embedded-server' '--with-vio' '--with-openssl' '--without-docs' 
'--without-bench' '--without-readline' '--with-extra-charsets=all' 
'--with-berkeley-db' '--with-innodb' 'CC=gcc' 'CFLAGS=-DBIG_JOINS=1' 
'CXXFLAGS=-DBIG_JOINS=1 -felide-constructors -fno-rtti' 'CXX=gcc' 
'build_alias=i386-linux' 'host_alias=i386-linux'


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

MySQL Client unicode program

[Koon Yue Lam]

Hi, I am using MySQL 4.1.1 and all databases, columns are set to UTF-8 encoding

I have try both MySQL cc and Query browser and try to enter some
Chinese character, but when I print out the HEX value of the
character. It isn't in Unicode that I expected, but i can't see those
Chinese character using the above clients

I can only insert correct Unicode character by using JSP page and a
HTML form, encode all character in Unicode and send to database.
However it is useless because I can't run some query directly to
databse when the query contains Chinese character.

How can I run an insert query directly to database and the data would
be stored in Unicode?

I have try prefix the character with character set and collation, and
I got a warning :

insert into tables set name = _utf8'æ'
code 1265: Data truncated for column 'name' at row 1

If the query becomes this:
insert into building set name = convert('æ' using 'utf8')

I don't have warning and insert is OK, but the HEX is wrong

the Unicode of 'æ' should be E697A5, 
but the above query insert a HEX value of 'C2A4C3A9'
which I don't know what it is ...

The only method that I can insert an unicode character is by using
their Unicode directly in a query:
insert into building set name = 0xE697A5

then everything are fine except I can't read it from any clients, but
JSP can display it correctly.

Anyone know what I am doing wrong? 

Any help would be appreciated

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

RE: Database/Table Structure

[Ferguson, Michael]

Adam,
Thanks much. Best wishes. Cheers.

-Original Message-
From: Adam [mailto:[EMAIL PROTECTED] 
Sent: Monday, November 15, 2004 8:47 PM
To: Ferguson, Michael; MySQL General
Subject: Re: Database/Table Structure


Michael,

There are a bunch. I'll assume you have a Win32 client to use these
tools on. Take a look at:

- Erwin (http://www3.ca.com/Solutions/Product.asp?ID=260)
- MicroLap Database Designer
(http://www.microolap.com/dba/mysql/designer/)
- ER/Studio (http://www.embarcadero.com/products/erstudio/index.html)

There are many other some are probably free. Look around! I hope you
find something you like.

Cheers,
Adam

Ferguson, Michael>

> G'Day All,
> 
> Can anyone recommend an application that will read/probe a MySQL 
> database on a Linux server and generate a graphical output of 
> thedatabase/table structure? FlowChart type?
> 
> Thanks
>  



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

Re: Database/Table Structure

[Adam]

Michael,

There are a bunch. I'll assume you have a Win32 client to use these tools
on. Take a look at:

- Erwin (http://www3.ca.com/Solutions/Product.asp?ID=260)
- MicroLap Database Designer (http://www.microolap.com/dba/mysql/designer/)
- ER/Studio (http://www.embarcadero.com/products/erstudio/index.html)

There are many other some are probably free. Look around! I hope you find
something you like.

Cheers,
Adam

Ferguson, Michael>

> G'Day All,
> 
> Can anyone recommend an application that will read/probe a MySQL
> database on a Linux server and generate a graphical output of
> thedatabase/table structure? FlowChart type?
> 
> Thanks
>  



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

Re: Problem connecting to MySQL 4.1.7 running on another machine

[Adam]

Kevin,

Could be a few things causing problems. Lets look at the most obvious.

First, make sure you can login locally as root.

Second, MySQL authenticates based on user, password, and host. If you have a
know user with the right password coming from an unknown host MySQL will
bounce you.

http://dev.mysql.com/doc/mysql/en/Connection_access.html

In the MySQL database, there is a table named `user`. In that table is a
list of authorized users to the database server. Check the table and make
sure you're accounts are in their and you have the host set correctly.

-- Execute this with a user that has read access to MySQL database.
SELECT u.user, u.host FROM mysql.user u;

If you see something like this, then you know user `Bigkevin` can only
connect from the db-server's box.

++---+
| user   | host  |
++---+
| BigKevin   | localhost |
++---+

If you see something like this, then you know user `BigKevin` can connect
from _ANY_ machine.

++---+
| user   | host  |
++---+
| BigKevin   | % |
++---+

Hope this helps some.

Cheers,
Adam


Kevin Barry>

> I've installed ver 4.1.7 on a brand new Fedora Core 3 server but cannot
> connect remotely from a Mac OSX machine running Navicat or a Win XP machine
> running MySQL Admin. I can ping the machine with no problem and have opened
> the firewall for the 3306 port.
> 
> The error I receive is:
> 
> Could not connect to the specified instance.
> 
> MySQL Error Number 2003
> Can't connect to MySQL server on 192.168.0.11(10060)
> 
> Thanks.
> 
> Kevin
> 


Regards,
Adam



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

Re: What changed in 4.1.7 to break DBD::Mysql

[William R. Mussatto]

Mark Matthews said:
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> William R. Mussatto wrote:
>> Keith Ivey said:
>>
>>>William R. Mussatto wrote:
>>>
>>>
I've been googling for 1/2 hr w/o any answers.  sorry if I've missed
 the obvious.
Problem. Fresh install of mysql 4.7.1, AS perl 5.8
DBI and DBD-Mysql via ppm.
Client does not support authnticaiton protocol

>>>
>>>What version of MySQL were you using previously?  If it was 4.1.0 or
>>> earlier, then this
>>>might be useful reading:
>>>
>>>http://dev.mysql.com/doc/mysql/en/Password_hashing.html
>>>
>>>--
>>>Keith Ivey <[EMAIL PROTECTED]>
>>>Washington, DC
>>
>> Thanks, I'll check it out.  I did the development under linux using
>> 3.23.xx and perl 5.6 Basically debian stable.
>>
>> From the article it looks like I'll have to fall back to a 4.0
>> version.
>>
>> I was hoping, but using a clean install on the windows box to avoid
>> these issues.
>> I'll try  SET PASSWORD FOR 'some_user'@'some_host' =
>> OLD_PASSWORD('mypass');
>
> William,
>
> You'll need a copy of DBD::MySQL that's linked with libmysql from 4.1 to
> get around this issue (and use the new server-side prepared statements
> as well). Patrick Galbraith (a MySQL engineer) has pushed the required
> changes into the CVS repo of DBD::MySQL, however that hasn't been
> released yet as a binary by the DBD::MySQL maintainer  (DBD::MySQL
> version  2.9015)
>
>> Thanks that worked, now to see what happends when I try w/connector/J
>
> As long as you're using a recent version of Connector/J (3.0.15 or
> 3.1.4), it'll work fine.
>
> Regards,
>
>   -Mark
>
>
> - --
> Mark Matthews
> MySQL AB, Software Development Manager - Client Connectivity
> Office: +1 708 332 0507
> www.mysql.com
Thanks, I figured I just hit it at the wrong time (4.1.7 out but DBD not
yet updated).   the connector/j just came down from mysql.com so it should
be current.  I do most of my production work on debian linux but wanted to
give this client the current stuff.

-- 
William R. Mussatto, Senior Systems Engineer
Ph. 909-920-9154 ext. 27
FAX. 909-608-7061



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

Re: What changed in 4.1.7 to break DBD::Mysql

[Mark Matthews]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

William R. Mussatto wrote:
> Keith Ivey said:
> 
>>William R. Mussatto wrote:
>>
>>
>>>I've been googling for 1/2 hr w/o any answers.  sorry if I've missed
>>>the obvious.
>>>Problem. Fresh install of mysql 4.7.1, AS perl 5.8
>>>DBI and DBD-Mysql via ppm.
>>>Client does not support authnticaiton protocol
>>>
>>
>>What version of MySQL were you using previously?  If it was 4.1.0 or
>>earlier, then this
>>might be useful reading:
>>
>>http://dev.mysql.com/doc/mysql/en/Password_hashing.html
>>
>>--
>>Keith Ivey <[EMAIL PROTECTED]>
>>Washington, DC
> 
> Thanks, I'll check it out.  I did the development under linux using
> 3.23.xx and perl 5.6 Basically debian stable.
> 
> From the article it looks like I'll have to fall back to a 4.0 version.
> 
> I was hoping, but using a clean install on the windows box to avoid these
> issues.
> I'll try  SET PASSWORD FOR 'some_user'@'some_host' = OLD_PASSWORD('mypass');

William,

You'll need a copy of DBD::MySQL that's linked with libmysql from 4.1 to
get around this issue (and use the new server-side prepared statements
as well). Patrick Galbraith (a MySQL engineer) has pushed the required
changes into the CVS repo of DBD::MySQL, however that hasn't been
released yet as a binary by the DBD::MySQL maintainer  (DBD::MySQL
version  2.9015)

> Thanks that worked, now to see what happends when I try w/connector/J

As long as you're using a recent version of Connector/J (3.0.15 or
3.1.4), it'll work fine.

Regards,

-Mark


- --
Mark Matthews
MySQL AB, Software Development Manager - Client Connectivity
Office: +1 708 332 0507
www.mysql.com
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.6 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFBmUi5tvXNTca6JD8RAllHAJ4060+3153gCOUo5IKBthDEHsUN2wCfYeNc
tkoDjKqS0DBxz7mE7ZWIqG8=
=hqLF
-END PGP SIGNATURE-

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

Re: Problem connecting to MySQL 4.1.7 running on another machine

[Richard Whitney]

Quoting Kevin Barry <[EMAIL PROTECTED]>:

> I've installed ver 4.1.7 on a brand new Fedora Core 3 server but cannot
> connect remotely from a Mac OSX machine running Navicat or a Win XP machine
> running MySQL Admin. I can ping the machine with no problem and have opened
> the firewall for the 3306 port.
>
> The error I receive is:
>
> Could not connect to the specified instance.
>
> MySQL Error Number 2003
> Can't connect to MySQL server on 192.168.0.11(10060)
>
> Thanks.
>
> Kevin

Kevin!

Did you add the appropriate entry to your hosts table?
I'm not sure how to do it, but that's the idea

Richard


R. Whitney
Transcend Development
"Producing the next phase of your internet presence"
http://xend.net
Premium Quality Web Hosting
http://hosting.xend.net
rw AT xend.net
Net Binder  http://netbinder.net
310-943-6498
602-288-5340


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

Re: What changed in 4.1.7 to break DBD::Mysql

[William R. Mussatto]

Keith Ivey said:
> William R. Mussatto wrote:
>
>>I've been googling for 1/2 hr w/o any answers.  sorry if I've missed
>> the obvious.
>>Problem. Fresh install of mysql 4.7.1, AS perl 5.8
>>DBI and DBD-Mysql via ppm.
>>Client does not support authnticaiton protocol
>>
>
> What version of MySQL were you using previously?  If it was 4.1.0 or
> earlier, then this
> might be useful reading:
>
> http://dev.mysql.com/doc/mysql/en/Password_hashing.html
>
> --
> Keith Ivey <[EMAIL PROTECTED]>
> Washington, DC
Thanks, I'll check it out.  I did the development under linux using
3.23.xx and perl 5.6 Basically debian stable.

>From the article it looks like I'll have to fall back to a 4.0 version.

I was hoping, but using a clean install on the windows box to avoid these
issues.
I'll try  SET PASSWORD FOR 'some_user'@'some_host' = OLD_PASSWORD('mypass');

Thanks that worked, now to see what happends when I try w/connector/J



-- 
William R. Mussatto, Senior Systems Engineer
Ph. 909-920-9154 ext. 27
FAX. 909-608-7061



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

Problem connecting to MySQL 4.1.7 running on another machine

[Kevin Barry]

I've installed ver 4.1.7 on a brand new Fedora Core 3 server but cannot
connect remotely from a Mac OSX machine running Navicat or a Win XP machine
running MySQL Admin. I can ping the machine with no problem and have opened
the firewall for the 3306 port.

The error I receive is:

Could not connect to the specified instance.

MySQL Error Number 2003
Can't connect to MySQL server on 192.168.0.11(10060)

Thanks.

Kevin


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

Re: myisam table corruption

[Andrew_Hoying]

I see the expected behavior from INSERT DELAYED, the server returns right
away and a new delayed thread gets created. I have noticed that my
application's internal queue flushes significantly faster with INSERT
DELAYED than it did with just INSERT, as expected.

Andrew



   
 robby chr 
  To 
   Jeff Smelser <[EMAIL PROTECTED]> 
 11/15/2004 04:07   cc 
 PM[EMAIL PROTECTED]   
   Subject 
   Re: myisam table corruption 
 Please respond to 
 robby chr 
  
   
   




Is this accurate?  My understanding from the changelog in 4.0.18 was
that DELAYED is only ignored when using:

INSERT DELAYED ... SELECT ...

But not otherwise.  So unless he's doing that, that portion shouldn't
be an issue.  If you have alternative information, please let me know.
 Thanks!


On Mon, 15 Nov 2004 15:40:42 -0600, Jeff Smelser <[EMAIL PROTECTED]>
wrote:
> On Monday 15 November 2004 03:25 pm, Jeff Smelser wrote:
> > insert delayed is a myth.. Least in the 4.1 version. 4.1.18 it was
taken
> > out.
>
> Course thats 4.0.18.
>
>
>

--
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: What changed in 4.1.7 to break DBD::Mysql

[Keith Ivey]

William R. Mussatto wrote:
I've been googling for 1/2 hr w/o any answers.  sorry if I've missed the
obvious.
Problem. Fresh install of mysql 4.7.1, AS perl 5.8
DBI and DBD-Mysql via ppm.
Client does not support authnticaiton protocol
What version of MySQL were you using previously?  If it was 4.1.0 or 
earlier, then this
might be useful reading:

http://dev.mysql.com/doc/mysql/en/Password_hashing.html
--
Keith Ivey <[EMAIL PROTECTED]>
Washington, DC
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: myisam table corruption

[robby chr]

Is this accurate?  My understanding from the changelog in 4.0.18 was
that DELAYED is only ignored when using:

INSERT DELAYED ... SELECT ...

But not otherwise.  So unless he's doing that, that portion shouldn't
be an issue.  If you have alternative information, please let me know.
 Thanks!


On Mon, 15 Nov 2004 15:40:42 -0600, Jeff Smelser <[EMAIL PROTECTED]> wrote:
> On Monday 15 November 2004 03:25 pm, Jeff Smelser wrote:
> > insert delayed is a myth.. Least in the 4.1 version. 4.1.18 it was taken
> > out.
> 
> Course thats 4.0.18.
> 
> 
>

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

What changed in 4.1.7 to break DBD::Mysql

[William R. Mussatto]

I've been googling for 1/2 hr w/o any answers.  sorry if I've missed the
obvious.
Problem. Fresh install of mysql 4.7.1, AS perl 5.8
DBI and DBD-Mysql via ppm.
Client does not support authnticaiton protocol

DBI connect('cjuhsdinfo','{username}',...) failed: Client does not support
authentication protocol requested by server; consider upgrading MySQL
client at stdconfig.
ph line 2
DBI::__ANON__[C:/Perl/site/lib/DBI.pm:660]('undef', 'undef')
called at C
:/Perl/site/lib/DBI.pm line 662
DBI::connect('DBI', 'dbi:mysql:cjuhsdinfo', '{username}',
'{password}') called
at stdconfig.ph line 2

Any thoughts on how far I need to fall back to get around this?

-- 
William R. Mussatto, Senior Systems Engineer
Ph. 909-920-9154 ext. 27
FAX. 909-608-7061



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

RE: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) and Replication

[Donny Simonton]

Got me stumped on this one, I've been using 4.1 for over a year now and I
haven't run into this problem except somebody was using phpmyadmin or
something.  Maybe somebody else can help with this one, sorry.

 

Donny

 

  _  

From: Michael Grubb [mailto:[EMAIL PROTECTED] 
Sent: Monday, November 15, 2004 4:23 PM
To: Donny Simonton
Cc: [EMAIL PROTECTED]
Subject: Re: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and
(utf8_general_ci,COERCIBLE) and Replication

 

Donny,
I certainly appreciate your help.

"show create table contacts" shows the same output on both the master and
the slave ('latin1').

This is being run by the slave's SQL thread, and the Error is shown in the
"Last error" line of
"show slave status" and the error log.

Thanks again,

Michael

Donny Simonton wrote: 

Michael,
Normally I would let a fellow domain registrar fend for themselves, but I'm
feeling nice today.  :)  
 
Do a "show create table contacts" and see what the charset is set too.
 
I bet the character set on the slave is different.   Are you running this
from the command line?  
 
Or are you getting this error in the error_log?
 
Donny
 
  

-Original Message-
From: Michael Grubb [mailto:[EMAIL PROTECTED]
Sent: Monday, November 15, 2004 2:49 PM
To: [EMAIL PROTECTED]
Subject: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and
(utf8_general_ci,COERCIBLE) and Replication
 
Hello all,
I'm having a very peculiar and nerve racking experience with MySQL 4.1.7
running on Fedora Core 2.
On the master the query executes just fine, however on the slave I get:
Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and
(utf8_general_ci,COERCIBLE) for operation '='' on query. Default
database: 'BRITEERNO'. Query: 'Update contacts
 set firstname = '',lastname = '',email =
'' ,company = '',address1 = '',address2 = ''
 ,address3 = '',city = '',state =
'XX' ,zip = '', country = 'XX',phone = ''
 ,fax = '',user = '',pass =
'',changed_on = now() ,status = 'X'
 where RNK_id = '''
 
 
I'm completely and uterly stumped by this, I have no idea of where to go
from here.
Any help will be greatly appreciated.
 
Thank,
 
Michael Grubb
000domains.com
 
Below is output from my master and slave "show variables" command.
 
Show variables on the master:
 
character_set_client   latin1
 
character_set_connection   latin1
character_set_database latin1
character_set_results  latin1
 
character_set_server   latin1
  character_set_system utf8
character_sets_dir


/usr/share/mysql/charsets/
  

collation_connection
 latin1_swedish_ci
  collation_database
latin1_swedish_ci
collation_server   latin1_swedish_ci
 
Show variables on the slave:
character_set_client   latin1
  character_set_connection latin1
   character_set_database latin1
   character_set_results


latin1
  

   character_set_server
latin1
character_set_system   utf8
   character_sets_dir  /usr/share/mysql/charsets/
collation_connection  latin1_swedish_ci
   collation_database
 latin1_swedish_ci
collation_server
 latin1_swedish_ci


 
 
  

 

RE: Poor Select speed on simple 1 table query

[Donny Simonton]

You could always add an index on company_name + rcd_id.  That technically
shouldn't help, but I've seen crazier things before.

Donny

> -Original Message-
> From: mos [mailto:[EMAIL PROTECTED]
> Sent: Monday, November 15, 2004 4:23 PM
> To: [EMAIL PROTECTED]
> Subject: RE: Poor Select speed on simple 1 table query
> 
> At 03:32 PM 11/15/2004, Donny Simonton wrote:
> >Mos,
> >Personally, I never use like for anything.  I would add a fulltext index
> >myself and call it a day.  But that's me.
> >
> >Donny
> 
> Donny,
>  Unfortunately I can't. The query must return all rows that
> *start*
> with a certain phrase.  It's similar to doing a search on a range of
> values
> using Between "fra" and "frazzz" but this too is very very slow (324
> seconds to return 62k rows).  The Like operator will use the index if the
> wildcard is not used in the first character position. The Explain command
> shows the index is being used, and the thing that I think is slowing it
> down is not the Where clause but the reference to Rcd_Id which is not in
> the index. It's almost like MySQL is returning the results from the index
> file and then doing a non-indexed table join to the table data to get the
> Rcd_Id.
> 
> Mike
> 
> 
> > > -Original Message-
> > > From: mos [mailto:[EMAIL PROTECTED]
> > > Sent: Monday, November 15, 2004 2:40 PM
> > > To: [EMAIL PROTECTED]
> > > Subject: Poor Select speed on simple 1 table query
> > >
> > > It doesn't get any simpler than this. :)
> > >
> > > The Select statement takes way too long to complete.
> > >
> > > select rcd_id, company_name from company where company_name like
> "fra%"
> > > 12357 rows fetched (86.08 seconds)
> > >
> > > However if it returns just the column value from the index, it is
> quite
> > > fast:
> > > select company_name from company where company_name like 'fra%'
> > > 12357 rows fetched ( 0.14 sec)
> > >
> > > So by referencing a column (Rcd_Id or Cust_Name) from the data file,
> it
> > > becomes 600x slower compared to just referencing the value from the
> index
> > > by itself namely Company_Name.
> > >
> > > I've run Analyze on the table, I've even repaired it and rebuilt the
> index
> > > with no increase in speed.
> > >
> > > The table has 10 million rows in it.
> > >
> > > CREATE TABLE `company` (
> > >`Rcd_Id` int(4) NOT NULL auto_increment,
> > >`Company_Name` char(30) NOT NULL default '',
> > >`Cust_Name` char(15) default NULL,
> > >PRIMARY KEY  (`Rcd_Id`),
> > >KEY `CompanyName_Index` (`Company_Name`)
> > > ) ENGINE=MyISAM DEFAULT CHARSET=latin1
> > >
> > >
> > > mysql> explain select rcd_id, company_name from company where
> company_name
> > > like 'fra%';
> > > ++-+-+---+---+
> 
> > > ---+-+--+---+-+
> > > | id | select_type | table   | type  | possible_keys |
> > > key   | key_len | ref  | rows  | Extra   |
> > > ++-+-+---+---+
> 
> > > ---+-+--+---+-+
> > > |  1 | SIMPLE  | company | range | CompanyName_Index |
> > > CompanyName_Index |  30 | NULL | 10505 | Using where |
> > > ++-+-+---+---+
> 
> > > ---+-+--+---+-+
> > >
> > >
> > > mysql> explain select company_name from company where company_name
> like
> > > 'gre%';
> > > ++-+-+---+---+
> 
> > > ---+-+--+---+--+
> > > | id | select_type | table   | type  | possible_keys |
> > > key   | key_len | ref  | rows  | Extra
> |
> > > ++-+-+---+---+
> 
> > > ---+-+--+---+--+
> > > |  1 | SIMPLE  | company | range | CompanyName_Index |
> > > CompanyName_Index |  30 | NULL | 10505 | Using where; Using index
> |
> > > ++-+-+---+---+
> 
> > > ---+-+--+---+--+
> > >
> > >
> > > So is a 600x slower query typical of queries that reference the data
> > > portion of the table compared to queries that reference just the
> indexed
> > > columns?
> > > Is there any way to speed it up?
> > >
> > > TIA
> > >
> > > Mike
> > >
> > >
> > > --
> > > MySQL General Mailing List
> > > For list archives: http://lists.mysql.com/mysql
> > > To unsubscribe:
> > > http://lists.mysql.com/[EMAIL PROTECTED]
> > >
> >
> >
> >
> >--
> >MySQL General Mailing List
> >For list archives: http://lists.mysql.com/mysql
> >To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
> 
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]
> 



-- 
MySQL General Mailing List
For list archives: http://lists

RE: Poor Select speed on simple 1 table query

[mos]

At 03:32 PM 11/15/2004, Donny Simonton wrote:
Mos,
Personally, I never use like for anything.  I would add a fulltext index
myself and call it a day.  But that's me.
Donny
Donny,
Unfortunately I can't. The query must return all rows that *start* 
with a certain phrase.  It's similar to doing a search on a range of values 
using Between "fra" and "frazzz" but this too is very very slow (324 
seconds to return 62k rows).  The Like operator will use the index if the 
wildcard is not used in the first character position. The Explain command 
shows the index is being used, and the thing that I think is slowing it 
down is not the Where clause but the reference to Rcd_Id which is not in 
the index. It's almost like MySQL is returning the results from the index 
file and then doing a non-indexed table join to the table data to get the 
Rcd_Id.

Mike

> -Original Message-
> From: mos [mailto:[EMAIL PROTECTED]
> Sent: Monday, November 15, 2004 2:40 PM
> To: [EMAIL PROTECTED]
> Subject: Poor Select speed on simple 1 table query
>
> It doesn't get any simpler than this. :)
>
> The Select statement takes way too long to complete.
>
> select rcd_id, company_name from company where company_name like "fra%"
> 12357 rows fetched (86.08 seconds)
>
> However if it returns just the column value from the index, it is quite
> fast:
> select company_name from company where company_name like 'fra%'
> 12357 rows fetched ( 0.14 sec)
>
> So by referencing a column (Rcd_Id or Cust_Name) from the data file, it
> becomes 600x slower compared to just referencing the value from the index
> by itself namely Company_Name.
>
> I've run Analyze on the table, I've even repaired it and rebuilt the index
> with no increase in speed.
>
> The table has 10 million rows in it.
>
> CREATE TABLE `company` (
>`Rcd_Id` int(4) NOT NULL auto_increment,
>`Company_Name` char(30) NOT NULL default '',
>`Cust_Name` char(15) default NULL,
>PRIMARY KEY  (`Rcd_Id`),
>KEY `CompanyName_Index` (`Company_Name`)
> ) ENGINE=MyISAM DEFAULT CHARSET=latin1
>
>
> mysql> explain select rcd_id, company_name from company where company_name
> like 'fra%';
> ++-+-+---+---+
> ---+-+--+---+-+
> | id | select_type | table   | type  | possible_keys |
> key   | key_len | ref  | rows  | Extra   |
> ++-+-+---+---+
> ---+-+--+---+-+
> |  1 | SIMPLE  | company | range | CompanyName_Index |
> CompanyName_Index |  30 | NULL | 10505 | Using where |
> ++-+-+---+---+
> ---+-+--+---+-+
>
>
> mysql> explain select company_name from company where company_name like
> 'gre%';
> ++-+-+---+---+
> ---+-+--+---+--+
> | id | select_type | table   | type  | possible_keys |
> key   | key_len | ref  | rows  | Extra|
> ++-+-+---+---+
> ---+-+--+---+--+
> |  1 | SIMPLE  | company | range | CompanyName_Index |
> CompanyName_Index |  30 | NULL | 10505 | Using where; Using index |
> ++-+-+---+---+
> ---+-+--+---+--+
>
>
> So is a 600x slower query typical of queries that reference the data
> portion of the table compared to queries that reference just the indexed
> columns?
> Is there any way to speed it up?
>
> TIA
>
> Mike
>
>
> --
> 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: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) and Replication

[Michael Grubb]

Donny,
I certainly appreciate your help.

"show create table contacts" shows the same output on both the master
and the slave ('latin1').

This is being run by the slave's SQL thread, and the Error is shown in
the "Last error" line of
"show slave status" and the error log.

Thanks again,

Michael

Donny Simonton wrote:

  Michael,
Normally I would let a fellow domain registrar fend for themselves, but I'm
feeling nice today.  :)  

Do a "show create table contacts" and see what the charset is set too.

I bet the character set on the slave is different.   Are you running this
from the command line?  

Or are you getting this error in the error_log?

Donny

  
  
-Original Message-
From: Michael Grubb [mailto:[EMAIL PROTECTED]]
Sent: Monday, November 15, 2004 2:49 PM
To: [EMAIL PROTECTED]
Subject: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and
(utf8_general_ci,COERCIBLE) and Replication

Hello all,
I'm having a very peculiar and nerve racking experience with MySQL 4.1.7
running on Fedora Core 2.
On the master the query executes just fine, however on the slave I get:
Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and
(utf8_general_ci,COERCIBLE) for operation '='' on query. Default
database: 'BRITEERNO'. Query: 'Update contacts
 set firstname = '',lastname = '',email =
'' ,company = '',address1 = '',address2 = ''
 ,address3 = '',city = '',state =
'XX' ,zip = '', country = 'XX',phone = ''
 ,fax = '',user = '',pass =
'',changed_on = now() ,status = 'X'
 where RNK_id = '''


I'm completely and uterly stumped by this, I have no idea of where to go
from here.
Any help will be greatly appreciated.

Thank,

Michael Grubb
000domains.com

Below is output from my master and slave "show variables" command.

Show variables on the master:

character_set_client		latin1

character_set_connection	latin1
character_set_database		latin1
character_set_results		latin1

character_set_server		latin1
  character_set_system		utf8
character_sets_dir

  
  /usr/share/mysql/charsets/
  
  
collation_connection
	latin1_swedish_ci
  collation_database
latin1_swedish_ci
collation_server		latin1_swedish_ci

Show variables on the slave:
character_set_client		latin1
  character_set_connection	latin1
   character_set_database		latin1
   character_set_results

  
  latin1
  
  
   character_set_server
latin1
character_set_system		utf8
   character_sets_dir		/usr/share/mysql/charsets/
collation_connection		latin1_swedish_ci
   collation_database
	latin1_swedish_ci
collation_server
	latin1_swedish_ci

  
  

  






signature.asc
Description: OpenPGP digital signature

Naming columns dynamically

[Mauricio Pellegrini]

Hi ,

I need to do the following:
Given a certain variable for example  @var  I want to include the
contents of that variable as part of the name of a result column.

I'll try to explain myself a bit more with this example


Set @var = 'October_' ;

Select 

   Tot as 'Totals'  <-- Here I would like to include the
contents
of @var  
From sales  So the resulting column name would be
'October_Totals'

The reason for this is because I don't know the value of @var which is
typed by the operator at the time the query is run.

Is there a way to concatenate the contens of a variable  and a column
alias at run time ?

thanks for your ideas
Mauricio







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

Re: myisam table corruption

[Jeff Smelser]

On Monday 15 November 2004 03:25 pm, Jeff Smelser wrote:
> insert delayed is a myth.. Least in the 4.1 version. 4.1.18 it was taken
> out.

Course thats 4.0.18.


pgpBoy3BGuPlh.pgp
Description: PGP signature

Database/Table Structure

[Ferguson, Michael]

G'Day All,

Can anyone recommend an application that will read/probe a MySQL
database on a Linux server and generate a graphical output of
thedatabase/table structure? FlowChart type?

Thanks
 

RE: Poor Select speed on simple 1 table query

[Donny Simonton]

Mos,
Personally, I never use like for anything.  I would add a fulltext index
myself and call it a day.  But that's me.

Donny 

> -Original Message-
> From: mos [mailto:[EMAIL PROTECTED]
> Sent: Monday, November 15, 2004 2:40 PM
> To: [EMAIL PROTECTED]
> Subject: Poor Select speed on simple 1 table query
> 
> It doesn't get any simpler than this. :)
> 
> The Select statement takes way too long to complete.
> 
> select rcd_id, company_name from company where company_name like "fra%"
> 12357 rows fetched (86.08 seconds)
> 
> However if it returns just the column value from the index, it is quite
> fast:
> select company_name from company where company_name like 'fra%'
> 12357 rows fetched ( 0.14 sec)
> 
> So by referencing a column (Rcd_Id or Cust_Name) from the data file, it
> becomes 600x slower compared to just referencing the value from the index
> by itself namely Company_Name.
> 
> I've run Analyze on the table, I've even repaired it and rebuilt the index
> with no increase in speed.
> 
> The table has 10 million rows in it.
> 
> CREATE TABLE `company` (
>`Rcd_Id` int(4) NOT NULL auto_increment,
>`Company_Name` char(30) NOT NULL default '',
>`Cust_Name` char(15) default NULL,
>PRIMARY KEY  (`Rcd_Id`),
>KEY `CompanyName_Index` (`Company_Name`)
> ) ENGINE=MyISAM DEFAULT CHARSET=latin1
> 
> 
> mysql> explain select rcd_id, company_name from company where company_name
> like 'fra%';
> ++-+-+---+---+
> ---+-+--+---+-+
> | id | select_type | table   | type  | possible_keys |
> key   | key_len | ref  | rows  | Extra   |
> ++-+-+---+---+
> ---+-+--+---+-+
> |  1 | SIMPLE  | company | range | CompanyName_Index |
> CompanyName_Index |  30 | NULL | 10505 | Using where |
> ++-+-+---+---+
> ---+-+--+---+-+
> 
> 
> mysql> explain select company_name from company where company_name like
> 'gre%';
> ++-+-+---+---+
> ---+-+--+---+--+
> | id | select_type | table   | type  | possible_keys |
> key   | key_len | ref  | rows  | Extra|
> ++-+-+---+---+
> ---+-+--+---+--+
> |  1 | SIMPLE  | company | range | CompanyName_Index |
> CompanyName_Index |  30 | NULL | 10505 | Using where; Using index |
> ++-+-+---+---+
> ---+-+--+---+--+
> 
> 
> So is a 600x slower query typical of queries that reference the data
> portion of the table compared to queries that reference just the indexed
> columns?
> Is there any way to speed it up?
> 
> TIA
> 
> Mike
> 
> 
> --
> 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: myisam table corruption

[Jeff Smelser]

On Monday 15 November 2004 11:30 am, [EMAIL PROTECTED] wrote:

> I am running mysql version 4.1.7 on SuSE professional 9.1.

I run 4.0.22

> I have a MyISAM table which I use for syslog messages. This table gets
> about 40-70 inserts a second with peaks of over 400 inserts a second. I am
> using INSERT DELAYED for all inserts, and only one thread of my application
> has write access to the database.

insert delayed is a myth.. Least in the 4.1 version. 4.1.18 it was taken out.

> Nightly I take messages older than three days, copy them to a new table and
> then delete them from the primary table. After that I run OPTIMIZE TABLES
> on the primary table.
>
> My problem is that a few times a week the table gets corrupted and I can
> not select from it until I run REPAIR TABLES on it. What do I need to do to
> diagnose this problem so I can hopefully generate a repeatable bug report
> on it?

I get them every few weeks..  I have been trying to sort it myself.. 

> The table format is below. I know I need to better optimize my indexes, for
> instance I added the date_program index, but did not go back and remove the
> date index. I do plan on fixing that soon, but did not want to change
> anything before getting some direction on how to diagnose the problem. I
> may remove all indexes except the primary key and date_program as those are
> the only two that the majority of my selects hit.

you really need to get rid of those indexes.. other then maybe is and date.. 
there is no reason so have all those indexes on there. that just slows down 
inserts.

Jeff


pgpaA9bNx47Mt.pgp
Description: PGP signature

RE: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) and Replication

[Donny Simonton]

Michael,
Normally I would let a fellow domain registrar fend for themselves, but I'm
feeling nice today.  :)  

Do a "show create table contacts" and see what the charset is set too.

I bet the character set on the slave is different.   Are you running this
from the command line?  

Or are you getting this error in the error_log?

Donny

> -Original Message-
> From: Michael Grubb [mailto:[EMAIL PROTECTED]
> Sent: Monday, November 15, 2004 2:49 PM
> To: [EMAIL PROTECTED]
> Subject: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and
> (utf8_general_ci,COERCIBLE) and Replication
> 
> Hello all,
> I'm having a very peculiar and nerve racking experience with MySQL 4.1.7
> running on Fedora Core 2.
> On the master the query executes just fine, however on the slave I get:
> Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and
> (utf8_general_ci,COERCIBLE) for operation '='' on query. Default
> database: 'BRITEERNO'. Query: 'Update contacts
>  set firstname = '',lastname = '',email =
> '' ,company = '',address1 = '',address2 = ''
>  ,address3 = '',city = '',state =
> 'XX' ,zip = '', country = 'XX',phone = ''
>  ,fax = '',user = '',pass =
> '',changed_on = now() ,status = 'X'
>  where RNK_id = '''
> 
> 
> I'm completely and uterly stumped by this, I have no idea of where to go
> from here.
> Any help will be greatly appreciated.
> 
> Thank,
> 
> Michael Grubb
> 000domains.com
> 
> Below is output from my master and slave "show variables" command.
> 
> Show variables on the master:
> 
> character_set_client  latin1
> 
> character_set_connection  latin1
> character_set_databaselatin1
> character_set_results latin1
> 
> character_set_server  latin1
>   character_set_systemutf8
> character_sets_dir
/usr/share/mysql/charsets/
> collation_connection
>   latin1_swedish_ci
>   collation_database
> latin1_swedish_ci
> collation_server  latin1_swedish_ci
> 
> Show variables on the slave:
> character_set_client  latin1
>   character_set_connectionlatin1
>character_set_database latin1
>character_set_results
latin1
>character_set_server
> latin1
> character_set_system  utf8
>character_sets_dir /usr/share/mysql/charsets/
> collation_connection  latin1_swedish_ci
>collation_database
>   latin1_swedish_ci
> collation_server
>   latin1_swedish_ci


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

Re: 4.1.7 insert locks killing me

[Dan Nelson]

In the last episode (Nov 15), matt_lists said:
> I think I found the problem all our input files are prepped in a
> separate table then insert delayed select * from temptable into
> production
> 
> 4.0.18-19 removed this!
> 
> I dont know why they would allow insert delayed values, or load data
> infile concurrent but not on a select into table!?
> 
> Sucks, rewriting all my programs to dump temp tables to the drive,
> then reload them back with load data infile concurrent seems like
> mysql features went backwards instead of forwards with newer
> versions?

Please see http://dev.mysql.com/doc/mysql/en/News-4.0.18.html :

 INSERT DELAYED ... SELECT ... could cause table corruption because
 tables were not locked properly. This is now fixed by ignoring DELAYED
 in this context. (Bug #1983)

http://bugs.mysql.com/bug.php?id=1983



-- 
Dan Nelson
[EMAIL PROTECTED]

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

Re: scalability of MySQL - future plans?

[Jacek Becla]

The reality we have to live in is that decisions about software for 
large projects are made several years prior to turning the system on 
line, so inevitably many guesses have to be made based on what is known 
today. Although (obviously) no one knows how the database world will 
look like in 2012, your answers did give me useful hints to 
consider/think about. Thank you all who responded.

Jacek
Rhino wrote:
I hope that the original poster notes that none of the comments in this
thread actually answer the question about how scaleable MySQL will be in
2012. No one has talked about that for one simple reason: no one knows.
Frankly, I think the entire question was naive from the start. I can't think
of any software developers who announce their plans for 8 or 10 years down
the line. Software is a very competitive and ever-evolving thing. Changes in
software, like increased scalability, are driven primarily by a few factors:
a) Customer demand - if no customers are switching to other databases
because they have greater scalability, MySQL has relatively little incentive
to invest time and money on improving scalability unless perhaps they want
to do it for prestige ("bragging rights").
b) Hardware innovation - the advent of new and faster devices and networks
may make scalability easier to accomplish at a price that is attractive to
customers.
While we can guess at what hardware innovations are coming along by reading
announcements about new research findings, some research in hardware is
proprietary and is therefore not necessarily published. That means it is
difficult to know about some new innovations until someone has released a
device that uses the new innovation; then, there may be months or years of
delay before competitors can acquire that innovation (or make their own
version of it) and modify their software to use it. Even publicized research
is not necessarily readily available to software developers: even if the new
Fizzbin UltraHardDrive is very hot, it might be so expensive that very few
people will ever buy one and therefore it isn't economic to write software
for it because the installed base of these things will be tiny until the
price of the device is cheaper. Or maybe the Fizzbin drive will be used by
elite customers who will spare no expense to achieve scalability and MySQL
will write a special version that addresses that hardware.
As for customer demand, marketing gurus study this subject endlessly but
don't really know what customers will do or want in the future; they can
only offer guesses.
MySQL has a "to do" list in the MySQL manual but the items in that list are
mostly things that they plan to work on in the next year or two. I doubt
many of the developers are thinking much about the shape of MySQL in 2012
yet. There may be an architect or chief developer of some kind who is
fantasizing about major increases in scalability somewhere down the road but
I'd bet that those plans are vague at best and certainly nothing that would
be quantifiable. Even if they were, I doubt that MySQL would announce
specific plans this far ahead of time because it would give their
competitors too much information that the competitors might use against
them. Besides, if they did make such an announcement, they would have to
phrase it as either:
1) a solid committment, in which case they would probably *have* to live up
to the promise for fear of legal action if they failed to do so.
2) a "statement of intent" that they could wiggle out of if the marketplace
changed in such a way that it didn't make sense to achieve the stated
scalability of the goals.
The only type of announcement that a system planner should probably take
seriously is the solid commitment; anything else is just vapourware and
should be ignored. I doubt you will see many solid commitments for 8 years
in the future, unless you consider platitudes like "we will be the best" as
a solid commitment.
I hope this doesn't come across as cynical. I have seen the software
industry do tremendous things in the past 20 odd years. I'm expecting many
more stunningly impressive innovations in the coming years. I just don't
think anyone can predict with any precision what capabilities any given
program, like MySQL, will have in 2012.
Rhino
- Original Message - 
From: "Mark Papadakis" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Saturday, November 13, 2004 8:05 AM
Subject: Re: scalability of MySQL - future plans?

 

Smart Software is the key to many problems hardware with 'standard'
software cannot solve or handle.
I believe that mySQL will, by year 2012, be able to handle it
gracefully. It will be able to do so much more by then (easy and
robust clustering / HA, for example) and even incorporate technologies
and ideas that we (as in, people of our time) have not thought of as
yet. It might even become the dominant database on the market..  7
years is a century's worth of time in our world.
It is the evolution of software.

Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) and Replication

[Michael Grubb]

Hello all,
I'm having a very peculiar and nerve racking experience with MySQL 4.1.7
running on Fedora Core 2.
On the master the query executes just fine, however on the slave I get:
Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and
(utf8_general_ci,COERCIBLE) for operation '='' on query. Default
database: 'BRITEERNO'. Query: 'Update contacts
set firstname = '',lastname = '',email =
'' ,company = '',address1 = '',address2 = ''
,address3 = '',city = '',state =
'XX' ,zip = '', country = 'XX',phone = ''
,fax = '',user = '',pass =
'',changed_on = now() ,status = 'X'
where RNK_id = '''
I'm completely and uterly stumped by this, I have no idea of where to go
from here.
Any help will be greatly appreciated.
Thank,
Michael Grubb
000domains.com
Below is output from my master and slave "show variables" command.
Show variables on the master:
character_set_client		latin1 

character_set_connection	latin1
character_set_database		latin1 
character_set_results		latin1 

character_set_server		latin1 
 character_set_system		utf8 
   character_sets_dir		/usr/share/mysql/charsets/ 
   collation_connection		latin1_swedish_ci 
 collation_database	 
latin1_swedish_ci 
collation_server		latin1_swedish_ci

Show variables on the slave:
character_set_client		latin1 
 character_set_connection	latin1 
  character_set_database		latin1 
  character_set_results		latin1 
  character_set_server	 
latin1 
character_set_system		utf8 
  character_sets_dir		/usr/share/mysql/charsets/ 
   collation_connection		latin1_swedish_ci 
  collation_database		latin1_swedish_ci 
   collation_server		latin1_swedish_ci


signature.asc
Description: PGP signature


signature.asc
Description: OpenPGP digital signature

Transaction Deadlocks

[Emmett Bishop]

Howdy all,

We have a set of tables in a database that maintain
session information of users of a website. Each user's
session is identified by a GUID that we generate when
they login. As the users perform various operations
records are inserted/updated/deleted from these
session tables. Some of the tables change frequently
as users go about their business. One thing that I'm
seesing is that we're getting deadlocks when records
are being removed.

Here's the structure of the table:

CREATE TABLE `s_customer_identifier` (
  `session_guid` varchar(36) NOT NULL default '',
  `label` varchar(50) NOT NULL default '',
  `customer_id` int(11) NOT NULL default '0',
  `pref_id` int(11) NOT NULL default '0',
  `office_id` int(11) NOT NULL default '0',
  `sort_id` int(11) NOT NULL default '0',
  KEY `idx_session_guid` (`session_guid`)
) TYPE=InnoDB

The records are referenced for deletion by the
session_guid and label:

DELETE FROM s_customer_identifier WHERE session_guid =
'5E6ED651-3725-11D9-96C0-FE7645265844' AND label =
'selected_customers';

is a typical example.

A typical log statement we get when this happens is:

General error,  message from server: "Deadlock found
when trying to get lock; Try restarting transaction"

Is there some obvious cause based on what I've
provided?

Thanks,

Tripp



__ 
Do you Yahoo!? 
The all-new My Yahoo! - Get yours free! 
http://my.yahoo.com 
 


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

Re: 4.1.7 insert locks killing me

[matt_lists]

I think I found the problem
all our input files are prepped in a separate table
then insert delayed select * from temptable into production
4.0.18-19 removed this!
I dont know why they would allow insert delayed values, or load data 
infile concurrent

but not on a select into table!?
Sucks, rewriting all my programs to dump temp tables to the drive, then 
reload them back with load data infile concurrent

seems like mysql features went backwards instead of forwards with newer 
versions?


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

RE: 4.1.8

[Donny Simonton]

I think a lot of it has to do with the fact that 4.1 is now production and a
lot more people are now using it then before.  So you are bound to have new
bugs crop up, or in some cases people think they are bugs and they just
haven't read the manual.  You can see what's already been fixed for 4.1.8 at
http://dev.mysql.com/doc/mysql/en/News-4.1.8.html

Donny

> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
> Sent: Monday, November 15, 2004 2:35 PM
> To: [EMAIL PROTECTED]
> Subject: 4.1.8
> 
> Does 4.1.8 address any of the issues I am reading about in 4.1.7?  Are all
> of these issues valid or are you finding that its Lazy coding?
> 
> 
> 
> Thanks
> Donny Lairson
> President
> http://www.gunmuse.com
> 469 228 2183
> 
> 
> --
> 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.7 insert locks killing me

[mos]

At 02:12 PM 11/15/2004, you wrote:
How do I avoid insert locks?
We updated from mysql 4.0.18 to 4.1.7
now all my inserts lock tables, I had 500 asp timeouts within 30 minutes 
after installing 4.1.7

I also had over 400 connections to the database, as all the threads were 
locked waiting, asp timeout is set to 30 seconds, the inserts always take 
up to an hour, but never caused locks before?!?

all our tables are myisam, and all data is adding, there are no deletes to 
these tables, they are log tables.

I'm going to try going back to 4.0.18
If there are no holes in the table, then it is my understanding that locks 
aren't required if you are doing Inserts on a MyISAM table. Have you tried 
optimizing the table to get rid of the holes?

Mike 

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

Poor Select speed on simple 1 table query

[mos]

It doesn't get any simpler than this. :)
The Select statement takes way too long to complete.
select rcd_id, company_name from company where company_name like "fra%"
12357 rows fetched (86.08 seconds)
However if it returns just the column value from the index, it is quite fast:
select company_name from company where company_name like 'fra%'
12357 rows fetched ( 0.14 sec)
So by referencing a column (Rcd_Id or Cust_Name) from the data file, it 
becomes 600x slower compared to just referencing the value from the index 
by itself namely Company_Name.

I've run Analyze on the table, I've even repaired it and rebuilt the index 
with no increase in speed.

The table has 10 million rows in it.
CREATE TABLE `company` (
  `Rcd_Id` int(4) NOT NULL auto_increment,
  `Company_Name` char(30) NOT NULL default '',
  `Cust_Name` char(15) default NULL,
  PRIMARY KEY  (`Rcd_Id`),
  KEY `CompanyName_Index` (`Company_Name`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
mysql> explain select rcd_id, company_name from company where company_name 
like 'fra%';
++-+-+---+---+---+-+--+---+-+
| id | select_type | table   | type  | possible_keys | 
key   | key_len | ref  | rows  | Extra   |
++-+-+---+---+---+-+--+---+-+
|  1 | SIMPLE  | company | range | CompanyName_Index | 
CompanyName_Index |  30 | NULL | 10505 | Using where |
++-+-+---+---+---+-+--+---+-+

mysql> explain select company_name from company where company_name like 'gre%';
++-+-+---+---+---+-+--+---+--+
| id | select_type | table   | type  | possible_keys | 
key   | key_len | ref  | rows  | Extra|
++-+-+---+---+---+-+--+---+--+
|  1 | SIMPLE  | company | range | CompanyName_Index | 
CompanyName_Index |  30 | NULL | 10505 | Using where; Using index |
++-+-+---+---+---+-+--+---+--+

So is a 600x slower query typical of queries that reference the data 
portion of the table compared to queries that reference just the indexed 
columns?
Is there any way to speed it up?

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

4.1.8

[gunmuse]

Does 4.1.8 address any of the issues I am reading about in 4.1.7?  Are all
of these issues valid or are you finding that its Lazy coding?



Thanks
Donny Lairson
President
http://www.gunmuse.com
469 228 2183


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

relay-bin file missing

[Sebastien Hould]

I am missing my *-relay-bin file on my slave so I got an error on my
show slave status; . The slave is stopped, now I want to resync but
whenever I do a change master_log_pos and master_log_file it outputs a

ERROR 1105 (HY000): Failed purging old relay logs: Failed during log reset

what should I do?

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

4.1.7 insert locks killing me

[matt_lists]

How do I avoid insert locks?
We updated from mysql 4.0.18 to 4.1.7
now all my inserts lock tables, I had 500 asp timeouts within 30 minutes 
after installing 4.1.7

I also had over 400 connections to the database, as all the threads were 
locked waiting, asp timeout is set to 30 seconds, the inserts always 
take up to an hour, but never caused locks before?!?

all our tables are myisam, and all data is adding, there are no deletes 
to these tables, they are log tables.

I'm going to try going back to 4.0.18
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

myisam table corruption

[Andrew_Hoying]

Hello,

I am running mysql version 4.1.7 on SuSE professional 9.1.

I have a MyISAM table which I use for syslog messages. This table gets
about 40-70 inserts a second with peaks of over 400 inserts a second. I am
using INSERT DELAYED for all inserts, and only one thread of my application
has write access to the database.

Nightly I take messages older than three days, copy them to a new table and
then delete them from the primary table. After that I run OPTIMIZE TABLES
on the primary table.

My problem is that a few times a week the table gets corrupted and I can
not select from it until I run REPAIR TABLES on it. What do I need to do to
diagnose this problem so I can hopefully generate a repeatable bug report
on it?

The table format is below. I know I need to better optimize my indexes, for
instance I added the date_program index, but did not go back and remove the
date index. I do plan on fixing that soon, but did not want to change
anything before getting some direction on how to diagnose the problem. I
may remove all indexes except the primary key and date_program as those are
the only two that the majority of my selects hit.

describe todays_syslogs;
+--+--+--+-+--++
| Field| Type | Null | Key | Default  | Extra
|
+--+--+--+-+--++
| id   | bigint(20) unsigned  |  | PRI | NULL | auto_increment
|
| host | smallint(5) unsigned |  | MUL | 0|
|
| facility | smallint(5) unsigned |  | MUL | 0|
|
| priority | smallint(5) unsigned |  | MUL | 0|
|
| tag  | char(2)  |  | |  |
|
| program  | smallint(5) unsigned |  | MUL | 0|
|
| date | smallint(5) unsigned |  | MUL | 0|
|
| time | time |  | | 00:00:00 |
|
| msg  | text | YES  | | NULL |
|
+--+--+--+-+--++

show indexes from todays_syslogs;
+++--+--+-+---+-+--++--++-+
| Table  | Non_unique | Key_name | Seq_in_index | Column_name |
Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+++--+--+-+---+-+--++--++-+
| todays_syslogs |  0 | PRIMARY  |1 | id  |
A | 2933115 | NULL | NULL   |  | BTREE  | |
| todays_syslogs |  1 | host |1 | host|
A |  27 | NULL | NULL   |  | BTREE  | |
| todays_syslogs |  1 | facility |1 | facility|
A |  11 | NULL | NULL   |  | BTREE  | |
| todays_syslogs |  1 | priority |1 | priority|
A |   7 | NULL | NULL   |  | BTREE  | |
| todays_syslogs |  1 | program  |1 | program |
A |  55 | NULL | NULL   |  | BTREE  | |
| todays_syslogs |  1 | date |1 | date|
A |   2 | NULL | NULL   |  | BTREE  | |
| todays_syslogs |  1 | date_program |1 | date|
A |   2 | NULL | NULL   |  | BTREE  | |
| todays_syslogs |  1 | date_program |2 | program |
A | 101 | NULL | NULL   |  | BTREE  | |
+++--+--+-+---+-+--++--++-+


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

Re: Create table

[Michael Stassen]

rahul pathak wrote:
Hi,
  I am using the "Create table select * into ..." query to create a 
table using two exsisting tables
Do you mean "CREATE TABLE ... SELECT..." or "INSERT INTO table SELECT ..."?
But the table is created with only 1000 rows, which should not be the 
case.Is there a config setting which defaults the rowcount to 1000?
  I am using the MySQL control center.
MySQL Control Center has a default LIMIT 1000, which probably shouldn't 
apply to "CREATE  SELECT" or "INSERT ... SELECT".  I believe you can 
change that from within mysqlcc.  You can also change it when starting from 
the command line (select_limit option) 
, or set it in your option 
file (my.cnf|.ini) .

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

Re: Fulltext insert/update

[Sergei Golubchik]

Hi!

On Nov 15, John Smith wrote:
> I know that having a fulltext index on a table slows inserts and updates
> down (or I think I do...might have miss read something)
> 
> I have a large table that uses fulltext which I plan to update nearly
> 90% of the rows (400,00+) and add new ones.
> 
> It currently takes around 35 minutes to remove the index and then
> another 35 minutes to re index it.
> 
> I don't need to use the index until after I have finised with the
> updateing and run myisamchk -rq --sort-index --analyze --sort-records=7
> on the .MYI file.
> 
> Is it worth over an hour of adding and removing the fulltext?
> 
> Its just that my code takes 4 hours to finish and I am reluctant to add
> on another hour!

No need to remove the index.
Use

ALTER TABLE ... DISABLE KEYS

(and ... ENABLE when you're done with update)

Yes, it is worth to wait 35 minutes when the index will be rebuilt.
Maintaining live fulltext index is much slower.

(at least, inserting lots of rows into a table with fulltext index could
be up to 100 times slower than adding an index afterwards).
 
Regards,
Sergei

-- 
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /   Sergei Golubchik <[EMAIL PROTECTED]>
 / /|_/ / // /\ \/ /_/ / /__  MySQL AB, Senior Software Developer
/_/  /_/\_, /___/\___\_\___/  Osnabrueck, Germany
   <___/  www.mysql.com

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

Re: setting system variables

[Michael Stassen]

Do you mean this page ?
Michael
[EMAIL PROTECTED] wrote:
Could it be that you have put your variables into the wrong section of 
your configuration file?  I haven't been able to look it up to verify my 
information but I thought the section that starts with [mysql] contained 
settings for the command line client while the section that starts with 
[mysqld] contains settings for the server. 

I would love to have the link(s) into the manual for the page(s) that 
describes which section header to use in the INI/CFG file for which 
product (mysql, mysqld, mysqldump, etc).  I know I read it somewhere (a 
long time ago) but I can't find it today. Thanks!

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Hariett Jones <[EMAIL PROTECTED]> wrote on 11/15/2004 06:42:31 AM:

I want to set some variables once and for all. How should i do it ?
I need following vars to be set :
character_set_client=latin2
character_set_connection=latin2
character_set_database=latin2
character_set_results=latin2
character_set_server=latin2
character_set_system=latin2
collation_connection=latin2_bin
collation_database=latin2_bin
collation_server=latin2_bin
i have put them into /etc/my.cnf ,but it does not work.
[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
character_set_client=latin2
character_set_connection=latin2
character_set_database=latin2
character_set_results=latin2
character_set_server=latin2
character_set_system=latin2
collation_connection=latin2_bin
collation_database=latin2_bin
collation_server=latin2_bin
The variables appear to be :
variable  session value
   global value
character set clientlatin1latin2
character set connectionlatin1latin2
character set databaselatin2latin2
character set resultslatin1latin2
character set serverlatin2latin2
character set systemutf8utf8
character sets dir/usr/local/mysql/share/mysql/charsets/ 
/usr/local/mysql/share/mysql/charsets/
collation connectionlatin1_swedish_cilatin2_general_ci
collation databaselatin2_general_cilatin2_general_ci
collation serverlatin2_general_cilatin2_general_ci


--
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: setting system variables

[SGreen]

Could it be that you have put your variables into the wrong section of 
your configuration file?  I haven't been able to look it up to verify my 
information but I thought the section that starts with [mysql] contained 
settings for the command line client while the section that starts with 
[mysqld] contains settings for the server. 

I would love to have the link(s) into the manual for the page(s) that 
describes which section header to use in the INI/CFG file for which 
product (mysql, mysqld, mysqldump, etc).  I know I read it somewhere (a 
long time ago) but I can't find it today. Thanks!

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine


Hariett Jones <[EMAIL PROTECTED]> wrote on 11/15/2004 06:42:31 AM:

> I want to set some variables once and for all. How should i do it ?
> 
> I need following vars to be set :
> 
> character_set_client=latin2
> character_set_connection=latin2
> character_set_database=latin2
> character_set_results=latin2
> character_set_server=latin2
> character_set_system=latin2
> collation_connection=latin2_bin
> collation_database=latin2_bin
> collation_server=latin2_bin
> 
> 
> i have put them into /etc/my.cnf ,but it does not work.
> 
> [mysql]
> no-auto-rehash
> # Remove the next comment character if you are not familiar with SQL
> #safe-updates
> character_set_client=latin2
> character_set_connection=latin2
> character_set_database=latin2
> character_set_results=latin2
> character_set_server=latin2
> character_set_system=latin2
> collation_connection=latin2_bin
> collation_database=latin2_bin
> collation_server=latin2_bin
> 
> 
> The variables appear to be :
>  variable  session value
> global value
> 
> 
> character set clientlatin1latin2
> character set connectionlatin1latin2
> character set databaselatin2latin2
> character set resultslatin1latin2
> character set serverlatin2latin2
> character set systemutf8utf8
> character sets dir/usr/local/mysql/share/mysql/charsets/ 
> /usr/local/mysql/share/mysql/charsets/
> collation connectionlatin1_swedish_cilatin2_general_ci
> collation databaselatin2_general_cilatin2_general_ci
> collation serverlatin2_general_cilatin2_general_ci
> 
> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
> 

Re: Conditonal where

[Michael Stassen]

Stuart Felenstein wrote:
--- "Adams, Pat 006"
<[EMAIL PROTECTED]> wrote:
You could add a where condition that's always true
to the main part of
the SQL statement so that you can just tack on more
clauses
conditionally.
$sql .= "SELECT PostStart, JobTitle, Industry,
LocationState, VendorID "
. "FROM VendorJobs"
. "WHERE 1 = 1 ";
if ($s_Ind) {
$sql .= "AND VendorJobs.Industry IN ($s_Ind) ";
}
if ($s_State) {
$sql .= " AND VendorJobs.LocationState IN
($s_State)";
}
See I knew I wasn't crazy as Rhino may have suggested
:).  I had a vague idea that 1=1 would have worked but
sadly I did not test it.  Anyway, I went with just a
blank array and as values are set , the where clause
grows dynamically.
Thanks for your help.
Stuart
Well, you could do that (1=1), but why complicate the query to simplify the 
code?  Of course, the optimizer is smart enough to drop a condition which is 
always true, but it's usually better to do the reverse: complicate the code 
to send the correct query.  I think the array solution you already have is 
probably better, but you could also fix your original query with two more ifs:

  $sql .= "SELECT PostStart, JobTitle, Industry, LocationState, VendorID
   FROM VendorJobs";
  if ($s_ind OR $s_State) {$sql .= "WHERE ";}
  if ($s_Ind) {$sql .= "VendorJobs.Industry IN ($s_Ind)";}
  if ($s_ind AND $s_State) {$sql .= " AND ";}
  if ($s_State) {$sql .= "VendorJobs.LocationState IN ($s_State)";}

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

Create table

[rahul pathak]

Hi,
  I am using the "Create table select * into ..." query to create a table 
using two exsisting tables
But the table is created with only 1000 rows, which should not be the 
case.Is there a config setting which defaults the rowcount to 1000?
  I am using the MySQL control center.

thanks,
r

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

fsockopen causing "connection errors"

[Henry Hank]

Hello,

  First, I tried searching the mysql list archives, but the archive search
function on mysql.com does not seem to be working.

Second, I'm using the PHP fsockopen() function on port 3306 to test if a
database is up and responsive. If the socket opens, I'm assuming the database
is up, and I then call fclose() to close the socket.
 
  The problem is that if I call this several times in a row quickly, the
database senses a "connection error" problem and refuses to connect until I
issue a "flush hosts" command. 

  Is there are "more proper" was to use fsockopen()/fclose() to simply ping the
database?  I do NOT want to use mysql_connect in this case - I just want to
ping 3306 without causing MySQL to count them as broken connections and
disconnect the client host. 

Thanks,

-Hank



__ 
Do you Yahoo!? 
Check out the new Yahoo! Front Page. 
www.yahoo.com 
 


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

FW: Moving innodb from Linux to Windows

[Boyd E. Hemphill]

Best Regards,
Boyd E. Hemphill
MySQL Certified Professional
[EMAIL PROTECTED]
Triand, Inc.
www.triand.com
O:  (512) 248-2278
M:  (713) 252-4688

-Original Message-
From: Heikki Tuuri [mailto:[EMAIL PROTECTED] 
Sent: Sunday, November 14, 2004 11:32 AM
To: Boyd E. Hemphill
Cc: [EMAIL PROTECTED]
Subject: Re: Moving innodb from Linux to Windows

Boyd,

please send this correspondence to [EMAIL PROTECTED]

Right,

lower_case_table_names=2

does not work with InnoDB. I guess the way for you is then to use

lower_case_table_names=1

on both Unix and Windows. The drawback is that table dumps will have the 
name in lower case.

Note that, according to SQL standards, a database server should convert all 
table names to UPPER CASE by default.

My RENAME TABLE advice was for the case where you already have some tables, 
and want to make them to conform to lower_case_table_names=1.

Regards,

Heikki

- Alkuperäinen viesti - 
Lähettäjä: "Boyd E. Hemphill" <[EMAIL PROTECTED]>
Vastaanottaja: "'Heikki Tuuri'" <[EMAIL PROTECTED]>
Kopio: <[EMAIL PROTECTED]>
Lähetetty: Friday, November 12, 2004 3:51 PM
Aihe: RE: Moving innodb from Linux to Windows


Heikki & Gleb:

Thank you for the quick responses.  I had read the documentation that Dr.
Tuuri quotes and took it to mean that I had to account for this with
parameter settings, etc.

The DB I am working with is a 40G production tablespace.  It has over 100k
lines of Java code against it.  We capitalize the first letter in each word
of table and column names.  Changing this would require a very serious
undertaking.

I understood this document
http://dev.mysql.com/doc/mysql/en/Name_case_sensitivity.html to mean that
this problem could be dealt with by setting

lower_case_table_names=2

I think the issue is that the doc was written for the MyISAM engine and not
the InnoDB engine.  Could one of you clarify?

I appreciate your time in the matter.

Best Regards,
Boyd E. Hemphill
MySQL Certified Professional
[EMAIL PROTECTED]
Triand, Inc.
www.triand.com
O:  (512) 248-2278
M:  (713) 252-4688

-Original Message-
From: Heikki Tuuri [mailto:[EMAIL PROTECTED]
Sent: Friday, November 12, 2004 3:08 AM
To: [EMAIL PROTECTED]
Subject: Re: Moving innodb from Linux to Windows

Boyd,

> --=_NextPart_000_0038_01C4C7F5.22045240
> Content-Type: text/plain;
> charset="us-ascii"
> Content-Transfer-Encoding: 7bit
>
> All:
>
>
>
> I am attempting to move a set of db's from Linux (Mysql 4.0.20) to Windows
> (4.1.6).  I have everything running, and I can query tables that are all
> lower case.
>
>
>
> However I cannot issue a query on a table that contains uppercase letters.
> This is the my.ini file:

on Windows, InnoDB puts all database names and table names to lower case,
because on Windows MySQL is case-insensitive.

You should use, e.g.,

RENAME TABLE UppercaseDBName.UpperCaseTable TO
uppercasedbname.uppercasetable;

in your Unix database to make it suitable for porting to Windows. And set

lowercase_table_names=1

in the my.cnf of your Unix database, so that your applications can find the
tables after renaming them.

http://dev.mysql.com/doc/mysql/en/Moving.html

"
On Windows, InnoDB internally always stores database and table names in
lowercase. 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 lowercase.
A convenient way to accomplish this on Unix is to add the following line to
the [mysqld] section of your `my.cnf' before you start creating your
databases and tables:

[mysqld]
set-variable = lower_case_table_names=1

On Windows, lower_case_table_names is set to 1 by default.
"

We may fix this in the future, so that on Windows InnoDB will also find
tables with uppercase letters in their name. For that, we need a MySQL
function that looks from the .frm file name and directory what is the
'canonical' case of the letters in the database name and the table name.

> Best Regards,
>
> Boyd E. Hemphill

Best regards,

Heikki Tuuri
Innobase Oy
Foreign keys, transactions, and row level locking for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM
tables
http://www.innodb.com/order.php

Order MySQL technical support from https://order.mysql.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: [PHP-DB] Upgrading mySQL

[GH]

when I go to both 2.10 Upgrading MySQL
(http://dev.mysql.com/doc/mysql/en/Upgrade.html) and  2.10.2 Upgrading
from Version 4.0 to 4.1
(http://dev.mysql.com/doc/mysql/en/Upgrading-from-4.0.html) it does
not tell me how to upgrade it only tells me about important changes
and recomendation. I am looking for a guide of what commands I need to
use and so forth. As I said in the inital mailing I made, "I am a
newbie to Linux".

It also says that
"It is a good idea to rebuild and reinstall the Perl DBD::mysql module
whenever you install a new release of MySQL. The same applies to other
MySQL interfaces as well, such as the PHP mysql extension and the
Python MySQLdb module. " [But now how to]


Please Assist...
Thank You.

On Sun, 14 Nov 2004 19:55:52 -0800, Ardilla Roja <[EMAIL PROTECTED]> wrote:
> this one will be usefull ...
> 
> 2.10.2 Upgrading from Version 4.0 to 4.1
> - http://dev.mysql.com/doc/mysql/en/Upgrading-from-4.0.html
> 
> 
> 
> 
> On Sun, 14 Nov 2004 09:30:49 -0500, GH <[EMAIL PROTECTED]> wrote:
> > I would like to know how to upgrade mySql from 4.0 to 4.1?
> >
> > I am running mandrake 10
> >
> > I am using the preinstalled versions of both mySQL and PHP (php4).
> >
> > I am a newbie to Linux and am a bit nervous.
> >
> > Thanks.
> > 
> > --
> > PHP Database Mailing List (http://www.php.net/)
> > To unsubscribe, visit: http://www.php.net/unsub.php
> >
> >
>

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

RE: Conditonal where

[Stuart Felenstein]

--- "Adams, Pat 006"
<[EMAIL PROTECTED]> wrote:
> You could add a where condition that's always true
> to the main part of
> the SQL statement so that you can just tack on more
> clauses
> conditionally.
> 
> $sql .= "SELECT PostStart, JobTitle, Industry,
> LocationState, VendorID "
>   . "FROM VendorJobs"
>   . "WHERE 1 = 1 ";
> if ($s_Ind) {
>   $sql .= "AND VendorJobs.Industry IN ($s_Ind) ";
> }
> if ($s_State) {
>   $sql .= " AND VendorJobs.LocationState IN
> ($s_State)";
> }
> 
See I knew I wasn't crazy as Rhino may have suggested
:).  I had a vague idea that 1=1 would have worked but
sadly I did not test it.  Anyway, I went with just a
blank array and as values are set , the where clause
grows dynamically.

Thanks for your help.

Stuart

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

select distinct

[BARBIER Luc 099046]

Using the request : 
 
SELECT DISTINCT journal
FROM reference
WHERE TYPE = 'article' AND etat = 'published' AND journal = 'Physical Review
Letters'
 
I get two type of answers 
One with 'Physical Review Letters' 
(WHERE `journal` = 'Physical Review Letters' )
 

the other with 'Physical Review Letters ' 
with an additional blank at the end of the journal name. 
(WHERE `journal` = 'Physical Review Letters\r\n' ) 
 
How to select with the exact journal name only.
Thanks for your help,
 
L. Barbier 
 

 
 
_ 
Luc BARBIER 
DSM/DRECAM/SPCSI 
Bât 462 
CEA Saclay 91191 Gif-sur-Yvette Cedex 
E-Mail : [mailto:[EMAIL PROTECTED] 
Tel : 33 (0)1 69 08 51 60 
FAX : 33 (0)1 69 08 84 46

 

Fulltext insert/update

[John Smith]

I know that having a fulltext index on a table slows inserts and updates
down (or I think I do...might have miss read something)

I have a large table that uses fulltext which I plan to update nearly
90% of the rows (400,00+) and add new ones.

It currently takes around 35 minutes to remove the index and then
another 35 minutes to re index it.

I don't need to use the index until after I have finised with the
updateing and run myisamchk -rq --sort-index --analyze --sort-records=7
on the .MYI file.

Is it worth over an hour of adding and removing the fulltext?

Its just that my code takes 4 hours to finish and I am reluctant to add
on another hour!

John


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

RE: Conditonal where

[Adams, Pat 006]

> -Original Message-
> From: Stuart Felenstein [mailto:[EMAIL PROTECTED] 
> Sent: Sunday, November 14, 2004 11:30 AM
> To: [EMAIL PROTECTED]
> Subject: Conditonal where
> 
>  $sql .= " SELECT PostStart, JobTitle, Industry, 
> LocationState, VendorID FROM VendorJobs";
>   
> if ($s_Ind){
> $sql .= " WHERE VendorJobs.Industry IN ($s_Ind)"; }
> 
> if ($s_State){
> $sql .= " AND VendorJobs.LocationState IN ($s_State)"; }
> 
> What I think I need is some kind of default "WHERE" in the 
> first statement.  Both Ind and State are conditional based on 
> whether the user input anything. 
> Right now they would be forced to at least choose the Ind.  
> So instead of the $s_Ind have a "WHERE" it should be an "AND" .

You could add a where condition that's always true to the main part of
the SQL statement so that you can just tack on more clauses
conditionally.

$sql .= "SELECT PostStart, JobTitle, Industry, LocationState, VendorID "
. "FROM VendorJobs"
. "WHERE 1 = 1 ";
if ($s_Ind) {
$sql .= "AND VendorJobs.Industry IN ($s_Ind) ";
}
if ($s_State) {
$sql .= " AND VendorJobs.LocationState IN ($s_State)";
}

--
Pat Adams
Applications Programmer
SYSCO Food Services of Dallas, L.P.
(469) 384-6009

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

Re: mysqld-nt error 23. Urgent pls

[Gleb Paharenko]

Hello.



I didn't found any bug in bug database, which was similar to your's.

Also I haven't ever heard about that thing: count of open files exceeded

OS limit in Windows. So for the beginning take a look at: 

  http://dev.mysql.com/doc/mysql/en/Not_enough_file_handles.html

And, if it won't be helpful, try to uprade your MySQL. May be you can

wait, if somebody gives better advice.





A Z <[EMAIL PROTECTED]> wrote:

> 

> 

> MySQL 4.0.14

> 

> Need help please with the folowing:

> 

> 

> On starting mysqld-nt using

> mysqld-nt --console

> we the get the follwing output

> 

> 041115 10:31:59  InnoDB: Database was not shut down

> normally.

> InnoDB: Starting recovery from log files...

> InnoDB: Starting log scan based on checkpoint at

> InnoDB: log sequence number 3 3136790960

> InnoDB: Doing recovery: scanned up to log sequence

> number 3 3136791157

> 041115 10:32:00  InnoDB: Starting an apply batch of

> log records to the database

> ..

> InnoDB: Progress in percents: 32 33 34 35 36 37 38 39

> 40 41 42 43 44 45 46 47 4

> 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66

> 67 68 69 70 71 72 73 74

> 5 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92

> 93 94 95 96 97 98 99

> InnoDB: Apply batch completed

> 041115 10:32:00  InnoDB: Flushing modified pages from

> the buffer pool...

> 041115 10:32:02  InnoDB: Started

> 041115 10:32:09  InnoDB: Operating system error number

> 23 in a file operation.

> InnoDB: See http://www.innodb.com/ibman.html for

> installation help.

> InnoDB: Error number 23 means 'Too many open files in

> system'.

> InnoDB: See also section 13.2 at

> http://www.innodb.com/ibman.html

> InnoDB: about operating system error numbers.

> InnoDB: File name .\ibdata1

> InnoDB: File operation call: 'Windows aio'.

> InnoDB: Cannot continue operation.

> 

> 

> regards

> 

> 

> 

>

> ___ 

> Win a castle for NYE with your mates and Yahoo! Messenger 

> http://uk.messenger.yahoo.com

> 



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [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: Too Many connections

[Gleb Paharenko]

Hello, Silvio.



I guess you are using MySQL as shipped in  Fedora distibution. See:

  http://dev.mysql.com/doc/mysql/en/Linux.html

You may find that sometimes with non-official binaries happens little problems.

So my advice for you in this situtation - upgrade to the latest release and use

official binaries from MySQL.





Silvio Porcellana <[EMAIL PROTECTED]> wrote:

> Hi all

> we are having a strange problem at the web site I work for (it's an Italian 
> registar).

> 

> "Sometimes" (that means: every 15/20 days) our db (MySQL) just hangs with a 
> "Too many 

> connections" error. The strange thing is that the DB server is stuck untill 
> we restart it 

> (actually, we always had to restart the whole server as root didn't have 
> SUPER privileges, 

> so nobody could connect to the db...)

> Since I'm pretty sure that we don't have such a traffic that justifies this 
> error (it 

> happened once at 2 a.m), I would *really* like to know what the heck is going 
> on.

> 

> Odd things are that:

> - we already raised the number of connections, and that didn't help

> - we lowered the wait_timeout var, setting it to 3000, but this didn't help 
> neither (we 

> did this because, with a 'show processlist;' we noticed sometimes some 
> hanging queries and 

> we thought: "Well if me make the die quicker maybe they won't pile up and 
> won't block our 

> server anymore". Wrong.)

> 

> Last time this thing happened the 'top' command showed about 380 'httpd' 
> processes 

> sleeping (and the system was very very slow, 'top' again showed a load 
> average above 100, 

> while it usually is at around 0.5).

> 

> Now what I'm asking is:

> - has anybody else ever experienced this problem?

> - in any case, what do you think we should monitor? Top? MySQL logs (We once 
> turned on the 

> General Query Log, but nothing happened and the file became huge in very 
> little time...)? 

> Apache logs?

> - could it be just a "client" issue (that is, PHP or Apache don't close the 
> connection - 

> although in PHP we only use mysql_connect, and never do a _pconnect)? How 
> could we monitor 

> this?

> 

> Our system runs with:

> - Linux 2.4.22-1.2188.nptl (Fedora 1)

> - MySQL 4.0.13

> - PHP 4.3.4

> - Apache 2.0.48

> 

> Thank's everybody for any help!

> Silvio

> 

> 

> P.S: This is the output of 'show variables\G':

> *** 1. row ***

> Variable_name: back_log

> Value: 50

> *** 2. row ***

> Variable_name: basedir

> Value: /

> *** 3. row ***

> Variable_name: binlog_cache_size

> Value: 32768

> *** 4. row ***

> Variable_name: bulk_insert_buffer_size

> Value: 8388608

> *** 5. row ***

> Variable_name: character_set

> Value: latin1

> *** 6. row ***

> Variable_name: character_sets

> Value: latin1 big5 czech euc_kr gb2312 gbk latin1_de sjis tis620 ujis 
> dec8 dos 

> german1 hp8 koi8_ru latin2 swe7 usa7 cp1251 danish hebrew win1251 estonia 
> hungarian 

> koi8_ukr win1251ukr greek win1250 croat cp1257 latin5

> *** 7. row ***

> Variable_name: concurrent_insert

> Value: ON

> *** 8. row ***

> Variable_name: connect_timeout

> Value: 5

> *** 9. row ***

> Variable_name: convert_character_set

> Value:

> *** 10. row ***

> Variable_name: datadir

> Value: /var/lib/mysql/

> *** 11. row ***

> Variable_name: delay_key_write

> Value: ON

> *** 12. row ***

> Variable_name: delayed_insert_limit

> Value: 100

> *** 13. row ***

> Variable_name: delayed_insert_timeout

> Value: 300

> *** 14. row ***

> Variable_name: delayed_queue_size

> Value: 1000

> *** 15. row ***

> Variable_name: flush

> Value: OFF

> *** 16. row ***

> Variable_name: flush_time

> Value: 0

> *** 17. row ***

> Variable_name: ft_boolean_syntax

> Value: + -><()~*:""&|

> *** 18. row ***

> Variable_name: ft_min_word_len

> Value: 4

> *** 19. row ***

> Variable_name: ft_max_word_len

> Value: 254

> *** 20. row ***

> V

Re: setting system variables

[Gleb Paharenko]

Hello.



What version of MySQL do you use? If it's older than 4.0.2, then

use set-variable = var_name=value.

See:

  http://dev.mysql.com/doc/mysql/en/Option_files.html



Hariett Jones <[EMAIL PROTECTED]> wrote:

> I want to set some variables once and for all. How should i do it ?

> 

> I need following vars to be set :

> 

> character_set_client=latin2

> character_set_connection=latin2

> character_set_database=latin2

> character_set_results=latin2

> character_set_server=latin2

> character_set_system=latin2

> collation_connection=latin2_bin

> collation_database=latin2_bin

> collation_server=latin2_bin

> 

> 

> i have put them into /etc/my.cnf ,but it does not work.

> 

> [mysql]

> no-auto-rehash

> # Remove the next comment character if you are not familiar with SQL

> #safe-updates

> character_set_client=latin2

> character_set_connection=latin2

> character_set_database=latin2

> character_set_results=latin2

> character_set_server=latin2

> character_set_system=latin2

> collation_connection=latin2_bin

> collation_database=latin2_bin

> collation_server=latin2_bin

> 

> 

> The variables appear to be :

> variablesession value

> global value

> 

> 

> character set clientlatin1  latin2

> character set connectionlatin1  latin2

> character set database  latin2  latin2

> character set results   latin1  latin2

> character set serverlatin2  latin2

> character set systemutf8utf8

> character sets dir  /usr/local/mysql/share/mysql/charsets/ 

> /usr/local/mysql/share/mysql/charsets/

> collation connectionlatin1_swedish_ci   latin2_general_ci

> collation database  latin2_general_ci   latin2_general_ci

> collation serverlatin2_general_ci   latin2_general_ci

> 

> 

> 



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [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: syntax question..

[Rhino]

> >
> > I think you need to do something like this:
> >
> > select t1.data
> > from table1 t1 inner join table2 t2 on t1.zip = t2.zip
> > where t2.chain like '%carmike%';
>
> i tried this one...  it seams to work. i'll read a bit more about joins ..
>  thank you... appreciate it...
>
You're welcome!

Rhino


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

setting system variables

[Hariett Jones]

I want to set some variables once and for all. How should i do it ?
I need following vars to be set :
character_set_client=latin2
character_set_connection=latin2
character_set_database=latin2
character_set_results=latin2
character_set_server=latin2
character_set_system=latin2
collation_connection=latin2_bin
collation_database=latin2_bin
collation_server=latin2_bin
i have put them into /etc/my.cnf ,but it does not work.
[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
character_set_client=latin2
character_set_connection=latin2
character_set_database=latin2
character_set_results=latin2
character_set_server=latin2
character_set_system=latin2
collation_connection=latin2_bin
collation_database=latin2_bin
collation_server=latin2_bin
The variables appear to be :
variable session value
 global value
character set client 	latin1 	latin2
character set connection 	latin1 	latin2
character set database 	latin2 	latin2
character set results 	latin1 	latin2
character set server 	latin2 	latin2
character set system 	utf8 	utf8
character sets dir 	/usr/local/mysql/share/mysql/charsets/ 
/usr/local/mysql/share/mysql/charsets/
collation connection 	latin1_swedish_ci 	latin2_general_ci
collation database 	latin2_general_ci 	latin2_general_ci
collation server 	latin2_general_ci 	latin2_general_ci


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

Too Many connections

[Silvio Porcellana]

Hi all
we are having a strange problem at the web site I work for (it's an Italian 
registar).
"Sometimes" (that means: every 15/20 days) our db (MySQL) just hangs with a "Too many 
connections" error. The strange thing is that the DB server is stuck untill we restart it 
(actually, we always had to restart the whole server as root didn't have SUPER privileges, 
so nobody could connect to the db...)
Since I'm pretty sure that we don't have such a traffic that justifies this error (it 
happened once at 2 a.m), I would *really* like to know what the heck is going on.

Odd things are that:
- we already raised the number of connections, and that didn't help
- we lowered the wait_timeout var, setting it to 3000, but this didn't help neither (we 
did this because, with a 'show processlist;' we noticed sometimes some hanging queries and 
we thought: "Well if me make the die quicker maybe they won't pile up and won't block our 
server anymore". Wrong.)

Last time this thing happened the 'top' command showed about 380 'httpd' processes 
sleeping (and the system was very very slow, 'top' again showed a load average above 100, 
while it usually is at around 0.5).

Now what I'm asking is:
- has anybody else ever experienced this problem?
- in any case, what do you think we should monitor? Top? MySQL logs (We once turned on the 
General Query Log, but nothing happened and the file became huge in very little time...)? 
Apache logs?
- could it be just a "client" issue (that is, PHP or Apache don't close the connection - 
although in PHP we only use mysql_connect, and never do a _pconnect)? How could we monitor 
this?

Our system runs with:
- Linux 2.4.22-1.2188.nptl (Fedora 1)
- MySQL 4.0.13
- PHP 4.3.4
- Apache 2.0.48
Thank's everybody for any help!
Silvio
P.S: This is the output of 'show variables\G':
*** 1. row ***
Variable_name: back_log
Value: 50
*** 2. row ***
Variable_name: basedir
Value: /
*** 3. row ***
Variable_name: binlog_cache_size
Value: 32768
*** 4. row ***
Variable_name: bulk_insert_buffer_size
Value: 8388608
*** 5. row ***
Variable_name: character_set
Value: latin1
*** 6. row ***
Variable_name: character_sets
Value: latin1 big5 czech euc_kr gb2312 gbk latin1_de sjis tis620 ujis dec8 dos 
german1 hp8 koi8_ru latin2 swe7 usa7 cp1251 danish hebrew win1251 estonia hungarian 
koi8_ukr win1251ukr greek win1250 croat cp1257 latin5
*** 7. row ***
Variable_name: concurrent_insert
Value: ON
*** 8. row ***
Variable_name: connect_timeout
Value: 5
*** 9. row ***
Variable_name: convert_character_set
Value:
*** 10. row ***
Variable_name: datadir
Value: /var/lib/mysql/
*** 11. row ***
Variable_name: delay_key_write
Value: ON
*** 12. row ***
Variable_name: delayed_insert_limit
Value: 100
*** 13. row ***
Variable_name: delayed_insert_timeout
Value: 300
*** 14. row ***
Variable_name: delayed_queue_size
Value: 1000
*** 15. row ***
Variable_name: flush
Value: OFF
*** 16. row ***
Variable_name: flush_time
Value: 0
*** 17. row ***
Variable_name: ft_boolean_syntax
Value: + -><()~*:""&|
*** 18. row ***
Variable_name: ft_min_word_len
Value: 4
*** 19. row ***
Variable_name: ft_max_word_len
Value: 254
*** 20. row ***
Variable_name: ft_max_word_len_for_sort
Value: 20
*** 21. row ***
Variable_name: ft_stopword_file
Value: (built-in)
*** 22. row ***
Variable_name: have_bdb
Value: NO
*** 23. row ***
Variable_name: have_crypt
Value: YES
*** 24. row ***
Variable_name: have_innodb
Value: YES
*** 25. row ***
Variable_name: have_isam
Value: YES
*** 26. row ***
Variable_name: have_raid
Value: NO
**

Re: Milliseconds to date string

[Rafal Kedziorski]

hi,
At 18:50 12.11.2004, Gleb Paharenko wrote:
Hello, Rafal.
At first, sorry for my query, it probably should look like this:
 select convert_tz(cast(from_unixtime(( cast(t as binary) div 1000)) as
 datetime),'+00:00','-07:00') from tvar;
I have to user / instead of div, which maker problems. thx.

Because you use milliseconds, not seconds.
>(cast(s2u.value))
Second... I think you've forgot to insert 'as binary' after 
'cast(s2u.value...'.
May be that will do:

select u.user_id, u.login_name, convert_tz(cast(from_unixtime(cast(s2u.value
as binary) div 1000 ) as datetime),'+00:00', '-07:00') from  users u,
setting_2_user s2u where s2u.setting_id = 150 and u.user_id = s2u.user_id;

Rafal Kedziorski <[EMAIL PROTECTED]> wrote:
> Hi,
>
> If I do this, I get "You have an error in your SQL syntax. Check the 
manual
> that corresponds to your MySQL server version for the right syntax to use
> near '(cast(from_unixtime(cast(s2u.value)) as datetime), '+00:00', '-"
>
> select
> u.user_id,
> u.login_name,
> convert_tz(cast(from_unixtime(cast(s2u.value)) as datetime),'+00:00',
> '-07:00')
> from
> users u,
> setting_2_user s2u
> where
> s2u.setting_id = 150
> and
> u.user_id = s2u.user_id
>
>
> Best Regards,
> Rafal
>
> At 16:43 12.11.2004, you wrote:
>>Hello.
>>
>>The first thing which comes to mind (I didn't dig really deep) looks like:
>>
>>select convert_tz(cast(from_unixtime(cast(t as binary)) as 
datetime),'+00:00',
>>'-07:00') from tvar;
>>
>>mysql> show create table tvar;
>>+---+--- 
--+
>>| Table | Create
>>Table
>>|
>>+---+--- 
--+
>>| tvar  | CREATE TABLE `tvar` (
>>   `t` varchar(20) default NULL
>>   ) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
>>   +---+-
>>
>>mysql> select * from tvar;
>>++
>>| t  |
>>++
>>| 1100273311 |
>>++
>>
>>
>>
>>Rafal Kedziorski <[EMAIL PROTECTED]> wrote:
>> > hi,
>> >
>> > I store in a column (varchar(20)) milliseconds. How can I format the 
value
>> > in a select statement to right timezone?
>> >
>> >
>> > Regards,
>> > Rafal
>> >
>> >
>>
>>
>>--
>>For technical support contracts, goto https://order.mysql.com/?ref=ensita
>>This email is sponsored by Ensita.NET http://www.ensita.net/
>>__  ___ ___   __
>>   /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
>>  / /|_/ / // /\ \/ /_/ / /__   [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]
>
>

--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [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]

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

Re: Milliseconds to date string

[Johan Hook]

Hi Rafal,
binary is a reserved word, you need to quote it with back-ticks ` if
you want to use it:
SELECT s2u.valus as `binary`
FROM ...
/Johan
Rafal Kedziorski wrote:
Hi,
I get this:
"You have an error in your SQL syntax.  Check the manual that 
corresponds to your MySQL server version for the right syntax to use 
near '(cast(from_unixtime(cast(s2u.value as binary) div 1000 ) as dat"

I think, the problem is here:
select
s2u.value as binary
from
users u,
setting_2_user s2u
where
s2u.setting_id = 150
and
u.user_id = s2u.user_id;
"You have an error in your SQL syntax.  Check the manual that 
corresponds to your MySQL server version for the right syntax to use 
near 'binary from users u, setting_2_user s2u where s2u.setting_id ="


--
Johan Höök, Pythagoras Engineering Group
- MailTo:[EMAIL PROTECTED]
- http://www.pythagoras.se
Pythagoras AB, Stormbyv. 2-4, SE-163 55 SPÅNGA, Sweden
Phone: +46 8 760 00 10 Fax: +46 8 761 22 77
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

mysqld-nt error 23. Urgent pls

[A Z]

MySQL 4.0.14

Need help please with the folowing:


On starting mysqld-nt using
mysqld-nt --console
we the get the follwing output

041115 10:31:59  InnoDB: Database was not shut down
normally.
InnoDB: Starting recovery from log files...
InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 3 3136790960
InnoDB: Doing recovery: scanned up to log sequence
number 3 3136791157
041115 10:32:00  InnoDB: Starting an apply batch of
log records to the database
..
InnoDB: Progress in percents: 32 33 34 35 36 37 38 39
40 41 42 43 44 45 46 47 4
 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66
67 68 69 70 71 72 73 74
5 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92
93 94 95 96 97 98 99
InnoDB: Apply batch completed
041115 10:32:00  InnoDB: Flushing modified pages from
the buffer pool...
041115 10:32:02  InnoDB: Started
041115 10:32:09  InnoDB: Operating system error number
23 in a file operation.
InnoDB: See http://www.innodb.com/ibman.html for
installation help.
InnoDB: Error number 23 means 'Too many open files in
system'.
InnoDB: See also section 13.2 at
http://www.innodb.com/ibman.html
InnoDB: about operating system error numbers.
InnoDB: File name .\ibdata1
InnoDB: File operation call: 'Windows aio'.
InnoDB: Cannot continue operation.


regards




___ 
Win a castle for NYE with your mates and Yahoo! Messenger 
http://uk.messenger.yahoo.com

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

Re: Milliseconds to date string

[Rafal Kedziorski]

Hi,
I get this:
"You have an error in your SQL syntax.  Check the manual that corresponds 
to your MySQL server version for the right syntax to use near 
'(cast(from_unixtime(cast(s2u.value as binary) div 1000 ) as dat"

I think, the problem is here:
select
s2u.value as binary
from
users u,
setting_2_user s2u
where
s2u.setting_id = 150
and
u.user_id = s2u.user_id;
"You have an error in your SQL syntax.  Check the manual that corresponds 
to your MySQL server version for the right syntax to use near 'binary from 
users u, setting_2_user s2u where s2u.setting_id ="

I'm using MySQL 4.0.18.
Regards,
Rafal
At 18:50 12.11.2004, you wrote:
Hello, Rafal.
At first, sorry for my query, it probably should look like this:
 select convert_tz(cast(from_unixtime(( cast(t as binary) div 1000)) as
 datetime),'+00:00','-07:00') from tvar;
Because you use milliseconds, not seconds.
>(cast(s2u.value))
Second... I think you've forgot to insert 'as binary' after 
'cast(s2u.value...'.
May be that will do:

select u.user_id, u.login_name, convert_tz(cast(from_unixtime(cast(s2u.value
as binary) div 1000 ) as datetime),'+00:00', '-07:00') from  users u,
setting_2_user s2u where s2u.setting_id = 150 and u.user_id = s2u.user_id;

Rafal Kedziorski <[EMAIL PROTECTED]> wrote:
> Hi,
>
> If I do this, I get "You have an error in your SQL syntax. Check the 
manual
> that corresponds to your MySQL server version for the right syntax to use
> near '(cast(from_unixtime(cast(s2u.value)) as datetime), '+00:00', '-"
>
> select
> u.user_id,
> u.login_name,
> convert_tz(cast(from_unixtime(cast(s2u.value)) as datetime),'+00:00',
> '-07:00')
> from
> users u,
> setting_2_user s2u
> where
> s2u.setting_id = 150
> and
> u.user_id = s2u.user_id
>
>
> Best Regards,
> Rafal
>
> At 16:43 12.11.2004, you wrote:
>>Hello.
>>
>>The first thing which comes to mind (I didn't dig really deep) looks like:
>>
>>select convert_tz(cast(from_unixtime(cast(t as binary)) as 
datetime),'+00:00',
>>'-07:00') from tvar;
>>
>>mysql> show create table tvar;
>>+---+--- 
--+
>>| Table | Create
>>Table
>>|
>>+---+--- 
--+
>>| tvar  | CREATE TABLE `tvar` (
>>   `t` varchar(20) default NULL
>>   ) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
>>   +---+-
>>
>>mysql> select * from tvar;
>>++
>>| t  |
>>++
>>| 1100273311 |
>>++
>>
>>
>>
>>Rafal Kedziorski <[EMAIL PROTECTED]> wrote:
>> > hi,
>> >
>> > I store in a column (varchar(20)) milliseconds. How can I format the 
value
>> > in a select statement to right timezone?
>> >
>> >
>> > Regards,
>> > Rafal
>> >
>> >
>>
>>
>>--
>>For technical support contracts, goto https://order.mysql.com/?ref=ensita
>>This email is sponsored by Ensita.NET http://www.ensita.net/
>>__  ___ ___   __
>>   /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
>>  / /|_/ / // /\ \/ /_/ / /__   [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]
>
>

--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [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]

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

Re: How many rows?

[Gleb Paharenko]

Hello.



Looks like it returns as much as:

 select count(distinct id) from sale where user_id=1;



I've found it in comments at:

  http://dev.mysql.com/doc/mysql/en/Counting_rows.html





Jerry Swanson <[EMAIL PROTECTED]> wrote:

> This is my query

> select id, status from sale  where user_id = 1 GROUP BY id;

> 

> I need to know how many rows or how many entries this query returns. I

> tried to use "count" and "sum". But the result is wrong.

> 

> I want to be able to make select and know how many rows this query

> returns. Now, I use mysql_num_rows in php,

> 



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [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: Milliseconds to date string

[Gleb Paharenko]

Hello, Rafal.



At first, sorry for my query, it probably should look like this:



 select convert_tz(cast(from_unixtime(( cast(t as binary) div 1000)) as

 datetime),'+00:00','-07:00') from tvar;



Because you use milliseconds, not seconds.

>(cast(s2u.value))



Second... I think you've forgot to insert 'as binary' after 'cast(s2u.value...'.

May be that will do:



select u.user_id, u.login_name, convert_tz(cast(from_unixtime(cast(s2u.value

as binary) div 1000 ) as datetime),'+00:00', '-07:00') from  users u,

setting_2_user s2u where s2u.setting_id = 150 and u.user_id = s2u.user_id;









Rafal Kedziorski <[EMAIL PROTECTED]> wrote:

> Hi,

> 

> If I do this, I get "You have an error in your SQL syntax. Check the manual 

> that corresponds to your MySQL server version for the right syntax to use 

> near '(cast(from_unixtime(cast(s2u.value)) as datetime), '+00:00', '-"

> 

> select

> u.user_id,

> u.login_name,

> convert_tz(cast(from_unixtime(cast(s2u.value)) as datetime),'+00:00', 

> '-07:00')

> from

> users u,

> setting_2_user s2u

> where

> s2u.setting_id = 150

> and

> u.user_id = s2u.user_id

> 

> 

> Best Regards,

> Rafal

> 

> At 16:43 12.11.2004, you wrote:

>>Hello.

>>

>>The first thing which comes to mind (I didn't dig really deep) looks like:

>>

>>select convert_tz(cast(from_unixtime(cast(t as binary)) as datetime),'+00:00',

>>'-07:00') from tvar;

>>

>>mysql> show create table tvar;

>>+---+-+

>>| Table | Create 

>>Table 

>>|

>>+---+-+

>>| tvar  | CREATE TABLE `tvar` (

>>   `t` varchar(20) default NULL

>>   ) ENGINE=MyISAM DEFAULT CHARSET=latin1 |

>>   +---+-

>>

>>mysql> select * from tvar;

>>++

>>| t  |

>>++

>>| 1100273311 |

>>++

>>

>>

>>

>>Rafal Kedziorski <[EMAIL PROTECTED]> wrote:

>> > hi,

>> >

>> > I store in a column (varchar(20)) milliseconds. How can I format the value

>> > in a select statement to right timezone?

>> >

>> >

>> > Regards,

>> > Rafal

>> >

>> >

>>

>>

>>--

>>For technical support contracts, goto https://order.mysql.com/?ref=ensita

>>This email is sponsored by Ensita.NET http://www.ensita.net/

>>__  ___ ___   __

>>   /  |/  /_ __/ __/ __ \/ /Gleb Paharenko

>>  / /|_/ / // /\ \/ /_/ / /__   [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]

> 

> 



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [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: Solaris 8 error make on mysql_4.1.7

[Gleb Paharenko]

Hello.



It looks like you don't have g++ in you path.

Check if you have installed g++, or may be you have something like g++-3.xx 

and there are no symlink to g++.

You may specify your compiler by CXX environment variable( for more details

see output of ./configure --help).



And, it's better to use gcc instead of g++ to compile MySQL, so run: 



./configure ... CXX=gcc 





>Sorry form my english, I am Italian.

>

>I have a problem when run "make", the error is following

>

>g++ -DUNDEF_THREADS_HACK -I. -I. -I.. -I../include  -g  -DSAFE_MUTEX   

>-D_FILE_OFFSET_BITS=64 -DHAVE_CURSES_H 
>-I/download/mysql_4.1.7/mysql-4.1.7/include

>-DHAVE_RWLOCK_T -c -o mysql.o `test -f 'mysql.cc' || echo './'`mysql.cc

>../depcomp: exec: g++: not found

>*** Error code 127

>make: Fatal error: Command failed for target `mysql.o'

>Current working directory /download/mysql_4.1.7/mysql-4.1.7/client

>*** Error code 1

>make: Fatal error: Command failed for target `all-recursive'

>Current working directory /download/mysql_4.1.7/mysql-4.1.7

>*** Error code 1

>make: Fatal error: Command failed for target `all'[EMAIL PROTECTED] wrote:



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [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: Problem with an insert query

[Gleb Paharenko]

Hello.



The INSERT ... VALUES and INSERT ... SET forms of the statement insert rows 
based on explicitly specified values. So you can't use select in the value 
clause.

Instead you should use insert ... select. By the way: don't forget to

escape single quotes in text which you insert.

See:

  http://dev.mysql.com/doc/mysql/en/INSERT_SELECT.html

And good answer of Paul DuBois at:

  http://www.mail-archive.com/mysql@lists.mysql.com/msg26639.html





GH <[EMAIL PROTECTED]> wrote:

> In my database I have the following tables:

> +-+

> | Tables_in_AHRC  |

> +-+

> | Attendance  |

> | Participants|

> | ProgressNotes   |

> | Sessions|

> | Staff   |

> | StaffAttendance |

> +-+

> 

> I am trying to insert data in the ProgressNotes Table using the

> following query but it does not work. Can someone please assist? Thank

> You.

> 

> INSERT INTO ProgressNotes

>(

>DateOfReport,

>NumPartReg,

>NumPartPresent,

>NumStaffPresent,

>NumVolPresent,

>TodaysActivities,

>DescribeSession,

>Interactions,

>Comments,

>Plans,

>Signed

> )

> VALUES (

>'1', 

>Select Count(*) From Attendance WHERE Session = 1, 

>Select Count(*) From Attendance WHERE Session = 1 AND Present = 'Yes',

>Select Count(*) From StaffAttendance WHERE Session = 1 AND Present = 
> 'Yes',

>'0',

>'Attempt to start program',

>'Due to the lack of completed paperwork between  and SITE, we

> were unable to complete the program as schedule. We informed the

> consumers of the situation.',

> 'Understandably, the consumers were upset. The Dean (SR) was kind

> enough to try and help explain to our members that there was a

> communications problem that caused the paperwork not to be completely

> in place before we started the program. LL and I got the members'

> phone numbers so that we can advise them if we are canceled next week.

> 

> One consumer, DSL, was being uncooperative with us through out the

> time we were in session. She was pouting and having an attitude with

> all who were there including: the parents of another consumer, Dean SR

> and  staff. I attempted to calm Ms. L down and stated that I was

> informed by MR that there might be a problem at 6:45PM on Friday.

> After the close of business hours on Friday. Ms. L stated on many

> occasions that M should of told her that we were not having class

> today. She called a couple of persons including a counselor at ZZZ, to

> which I was able to speak and explain the situation. I attempted to

> explain the entire situation and that we did not definitely know that

> they were not going to let us in to the room  until today. The

> counselor seemed to be understanding. I told her that we are expecting

> to run the program next week and that we would contact Ms. L if we are

> told that we can not.',

>'Everyone (Consumers, Parents and Staff) wishes to have the program

> by next week please help us get up and running.',

>'Attempt to start up the program next session.', 

>'GMH, PgmLdr'

> );

> 

> 

> mysql> describe ProgressNotes;

> +--+--+--+-+-+---+

> | Field| Type | Null | Key | Default | Extra |

> +--+--+--+-+-+---+

> | DateOfReport | int(2)   |  | PRI | 0   |   |

> | NumPartReg   | int(2)   |  | | 0   |   |

> | NumPartPresent   | int(2)   |  | | 0   |   |

> | NumStaffPresent  | int(1)   |  | | 0   |   |

> | NumVolPresent| int(2)   |  | | 0   |   |

> | TodaysActivities | varchar(255) |  | MUL | |   |

> | DescribeSession  | text |  | | |   |

> | Interactions | text |  | | |   |

> | Comments | text |  | | |   |

> | Plans| text |  | | |   |

> | Signed   | text |  | | |   |

> +--+--+--+-+-+---+

> 11 rows in set (0.00 sec)

> 

> 

> 

> [QUOTE PHPMYADMIN RESULTS] 

> 

> Database AHRC  - Table Participants  running on localhost

> 

> Error

> 

> There seems to be an error in your SQL query. The MySQL server error

> output below, if there is any, may also help you in diagnosing the

> problem

> 

> ERROR: Unclosed quote @ 2040

> STR: '

> SQL: INSERT INTO ProgressNotes

>(

>DateOfReport,

>NumPartReg,

>NumPartPresent,

>NumStaffPresent,

>NumVolPresent,

>TodaysActivities,

>DescribeSession,

>Interactions,

>Comments,

>Plans,

>Sig

ANN: Advanced Data Generator 1.2.5 released!

[Martijn Tonies]

Dear ladies and gentlemen,

Upscene Productions is happy to announce a new version of
the database developer tool:
"Advanced Data Generator" (version 1.2.5)

A fast test-data generator tool that comes with a library
of real-life data, presets and much more.

This new release consists of four versions:

- Pro: ADO and ODBC connectivity
- InterBase Edition
- Firebird Edition
- MySQL Edition

More info and a 30-day trial version on www.upscene.com

Pricing information available on www.upscene.com/purchase.htm#adg


Recent changes:

V1.2.5
---
- New: Unique Identifier support for referential filling with ODBC
- New: sequential filling has a "wrap" property
- Fixed: Index Out Of Bounds error for projects with all tables checked
 and a table dropped from the database
- Fixed: there was an internal saving/loading error for fixed value 
 Integer presets
- Fixed: small GUI error with check/uncheck table when loading project
- Fixed: Unique values from list for strings failed
- Enhancement: keyboard shortcuts for Table Up/Down
- Enhancement: small GUI enhancements for Windows XP
- Enhancement: sequential values (from list/other table) can "wrap"





With regards,

Martijn Tonies
Upscene Productions - Database Tools for Developers
http://www.upscene.com


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

Re: syntax question..

[kalin mintchev]

>
> - Original Message -
> From: "kalin mintchev" <[EMAIL PROTECTED]>
> To: "Adam" <[EMAIL PROTECTED]>
> Cc: "MySQL General" <[EMAIL PROTECTED]>
> Sent: Sunday, November 14, 2004 11:33 PM
> Subject: Re: syntax question..
>
>
>>
>> > Kalin,
>> >
>> > Kalin> thanks to all... yes, Rhino - i'm new. my first
>> > Kalin> post. the version is the problem indeed. it's
>> > Kalin> 4.0.13-standard. how would i achieve the same
>> > Kalin> query in this version?...
>> >
>> > You'll need to provide the following:
>> >
>> > (1) What is the result you want to achieve?
>> well:
>> this is what i wrote:
>> select t1.data from table1 as t1 where t1.zip=(select * from table2 as
>> t2
>> where t2.chain like "%carmike%");
>>
>> i guess this will make more sense:
>> basically i'm looking for the data in t1.data - which is varchar - that
>> has a zip  value of the record(s)  that contain the name "carmike" in
>> their "chain" field in table2...
>>
>> does this make sense?
>>
>
> Not entirely. It sounds like you are saying that the zip value is in the
> column called 'data'. From your query, it appears that the zip code is in
> the column called 'zip'. I *think* you mean to say that for every row in
> table1 that contains a give the same zip code as the zip code of
> '%carmike%', you want to see some other information in the same row, which
> you are calling 'data' in your example.
>
> There are probably a few different ways to do this query. I'm going to
> suggest one possible approach but it is not necessarily the best one;
> others
> may come along later to suggest a better approach.
>
> I think you need to do something like this:
>
> select t1.data
> from table1 t1 inner join table2 t2 on t1.zip = t2.zip
> where t2.chain like '%carmike%';

i tried this one...  it seams to work. i'll read a bit more about joins ..
 thank you... appreciate it...


>
> You haven't provided full descriptions of the tables and their columns and
> I
> have no idea what the data in the tables actually is so I can't try this
> for
> you to be sure it works. However, it should be pretty close to what you
> need. Give it a try and let us know what error messages you get. If you
> don't get error messages but the result is incorrect, describe the result
> that you got and how it differed from the result you expected. Then we can
> probably help you figure out what went wrong.
>
> Rhino
>
>


-- 


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