Re: Hebrew

[Gleb Paharenko]

Hello.



In most encodings english letters remain unchanged, but to be sure

you may execute set NAMES latin1 before quering tables with English

data.









"John Berman" <[EMAIL PROTECTED]> wrote:

> Thanks will do this.

> 

> In the same dBase there a lots of tables were the data is in English - I

> assume they will be ok ?

> 

> John B

> 

> -Original Message-

> From: Gleb Paharenko [mailto:[EMAIL PROTECTED] 

> Sent: 07 March 2005 21:12

> To: mysql@lists.mysql.com

> Subject: Re: Hebrew

> 

> Hello.

> 

> Put all your character_set_xxx variables to hebrew. See:

> 

>  http://dev.mysql.com/doc/mysql/en/charset-connection.html

> 

>  

> 

> "John Berman" <[EMAIL PROTECTED]> wrote:

>> How's this

>> 

>> 

>> +--+---+

>> | Variable_name| Value |

>> +--+---+

>> | character_set_client | latin1|

>> | character_set_connection | latin1|

>> | character_set_database   | hebrew|

>> | character_set_results| latin1|

>> | character_set_server | latin1|

>> | character_set_system | utf8  |

>> | character_sets_dir   | /usr/mysql4/share/mysql/charsets/ |

>> +--+---+

>> 

>> 

>> John B

>> 

>> -Original Message-

>> From: Gleb Paharenko [mailto:[EMAIL PROTECTED] 

>> Sent: 07 March 2005 12:28

>> To: mysql@lists.mysql.com

>> Subject: Re: Hebrew

>> 

>> Hello.

>> 

>>> Pointers were I went wrong would be appreciated

>> 

>> What's the default character set for your server? Please, send

>> us an output of the following statement:

>> 

>>  show variables like '%char%';

>> 

>> Please, use mysql command line client.

>> 

>> 

>> 

>> "John Berman" <[EMAIL PROTECTED]> wrote:

>>> Using 4.1

>>> 

>>> Though I had it cracked ?

>>> 

>>> I created a new table and set the Charset to Hebrew and the field =

>>> =A0Collation

>>> to hebrew_general_ci=A0

>>> 

>>> I then uploaded data to the database using MS Acess (with an ODBC

>>> connection)

>>> 

>>> In MSAccess on my PC the Hebrew comes through fine.

>>> 

>>> However when I open the newly created table using MSAccess or phpMyAdmin

>>> the Hebrew is represented with question marks.

>>> 

>>> Pointers were I went wrong would be appreciated

>>> 

>>> Regards

>>> 

>>> John Berman

>>> 

>>> --=20

>>> No virus found in this outgoing message.

>>> Checked by AVG Anti-Virus.

>>> Version: 7.0.308 / Virus Database: 266.6.2 - Release Date: 04/03/2005

>>> =20

>>> 

>>> 

>> 

>> 

> 

> 



-- 
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: ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

[Gleb Paharenko]

Hello.



See:

  http://dev.mysql.com/doc/mysql/en/can-not-connect-to-server.html









Billy Pilgrim <[EMAIL PROTECTED]> wrote:

> Hello, I installed MySQL 4.1.10 on Debian Ubuntu based following these

> directions:

> http://dev.mysql.com/doc/mysql/en/installing-binary.html

> I then logged in, changed root password, used test database, etc.

> 

> Now after reboot I cannot connect. I get this:

> 

> [EMAIL PROTECTED]:/usr/local/mysql/bin # ./mysql -u root -p

> Enter password:

> ERROR 2002 (HY000): Can't connect to local MySQL server through socket

> '/tmp/mysql.sock' (2)

> 

> Please give options if this involves: manual server start, setting up

> autostart, etc.

> Thanks

> 



-- 
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: Time to be shown with query result

[Gleb Paharenko]

Hello.



I'm not a developer, I've just looked through the code with vim (gdb

had helped me a lot also). 



Kenji HIROHAMA <[EMAIL PROTECTED]> wrote:

> Hi Gleb,

> Thanks for your help.

> (I'm sending again, because first I just sent this only to you to use

> "Reply" of Gmail, not use "Reply to all".)

> 

> Well, is "end_timer" calculating the time?

> --

> static void end_timer(ulong start_time,char *buff)

> {

> nice_time((double) (start_timer() - start_time) /

>   CLOCKS_PER_SEC,buff,1);

> }

> --

> 

> Regards,

> --

> Kenken PA

> 

> - Hide quoted text -

> 

> On Tue, 08 Mar 2005 11:18:54 +0200, Gleb Paharenko

> <[EMAIL PROTECTED]> wrote:

>> Hello.

>>

>> The time is calculated at mysql client. This is a time of executing query

>>

>> and getting results.

>>

>>

>> > and "nice_time" seemed to me that this function calculate the time.

>>

>> This function formats the time to human-readable format.

>>

>>

>> Kenken PA <[EMAIL PROTECTED]> wrote:

>>

>> > Hi All,

>>

>> > I need to answer the floowing quetions even I'm a newbie of C programming.

>>

>> > Anyone helps me out?

>>

>> >

>>

>> > Q: Using mysql client, after issuing sql statements, we can see how

>>

>> > long it takes to get results, like;

>>

>> >

>>

>> > 1 row in set (0.01 sec)

>>

>> >

>>

>> > Does the time, 0.01 sec in this case, mean what kind of time?

>>

>> >

>>

>> > Is the time calculated at mysql client or at mysqld?

>>

>> >

>>

>> > Time between a point to issue a statement from mysql client, and a

>>

>> > point to get all results to show?

>>

>> >

>>

>> >

>>

>> > I'm checking inside of the source file, \client\mysql.cc,

>>

>> > and "nice_time" seemed to me that this function calculate the time.

>>

>> > However, I'm not sure at all.

>>

>> >

>>

>> > Thanks for your co-operation in advance.

>>

>> >

>>

>> > Kenken PA

>>

>> >

>>

>> --

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

>>

>>

> 

> 

> --

> -

> Kenji Hirohama

> 



-- 
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: Compressing after Deletion

[Alec . Cawley]

Note that with MySQL, unlike some other databases, you don't have to do 
this. If you are going to add new records to replace the deleted ones, 
MySQL will re-use the space freed by deletions with no special actions on 
your part. Of course, if you are not going to replace the deleted records, 
the commands suggested by David will certainly free space and probably 
improve performance.

Alec Cawley

"Logan, David (SST - Adelaide)" <[EMAIL PROTECTED]> wrote on 08/03/2005 
22:58:12:

> Hi Chris,
> 
> For MyISAM/BDB tables use OPTIMIZE TABLE ;
> For InnoDB tables try ALTER TABLE  TYPE=InnoDB;
> 
 
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] 
> Sent: Wednesday, 9 March 2005 9:19 AM
> To: mysql@lists.mysql.com
> Subject: Compressing after Deletion
> 
> I have looked in the documentation and either I am not looking for the
> right thing or have simply overlooked it. But my question is this, I
> have
> a database with 35 Million records, and I need to delete about 25
> million
> of those. After deletion I would think that I would need to compress,
> shrink, or otherwise optimize the database. How is that done? do I need
> to
> do it? What commands should I be looking up in the docs?


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

Re: OSX Installation woes...

[Santino]

Hi,
You have to know why MySql doesn't start.
When you install mysql, the installer creates a directory 
"mysql-[version name]" and a symbolic link "mysql" in /usr/local.

There is an error file named "yourservername".err in data directory 
that explains why mysql failed.
Type perror errornumber to have a description of the error.

Try to check that:
- mysql user have access/read/write privileges in /tmp directory.
- /usr/local/mysql/data and all files inside it owner/group is  mysql/mysql
- There are hosts and mysql tables in data directory.
Santino
At 16:45 -0600 8-03-2005, Jeff Justice wrote:
Okay, here's a brief synopsis, with suggestions made and tried:
Cannot get MySQL to launch.  A process is never started in the 
activity monitor.  What exactly is the name of the process I should 
see?

Tried uninstalling MySQL by deleting the MySQL directories in 
/usr/local/.  (after first making sure there is no process running, 
which near as I can tell, it's not).

Isn't there also a startup item?  Where are it's pieces/parts?
Does one de-install the pref pane by simply control-clicking and 
selecting "Remove MySQL Preference Pane"?

I can't get this thing back to running like when I first installed 
it.  Something is still goofy.

Jeff J.
--
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]

Explain and indexes

[Terry Spencer]

Im trying to speed up a query.

select 
project_id 
from 
timesheet ts 
where 
ts.del is null 
and signoff = 'A'

The output of explain is detailed below.

++-+---+--+-+--+
| id | select_type | table | type | possible_keys   | key  | key_len
++-+---+--+-+--+
|  1 | SIMPLE  | ts| ALL  | signoff,del,del_signoff | NULL |NULL

++-+---+--+-+--+
| ref  | rows | Extra   |
++-+---+--+-+--+
| NULL | 3907 | Using where |
++-+---+--+-+--+

An index exists on all three columns referred to, in addition to a
combination of del and signoff.

The indexes are listed as possible keys, but none used by the query; key =
null. Can anyone suggest why? How can I optimise this?

Thanks

Terry 



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

Re: Use MySQL with Microsoft Office

[Curtis Maurand]

Using ODBC, however, you can link Access tables to MySQL tables and use
Access as the front end to MySQL.  It works very nicely.

Curtis

Martijn Tonies said:
>> > > Alternatively you could use OpenOffice.org
> (http://www.openoffice.org/)
>> > > which has built in MySQL support.
>> >
>> > Alternatively, you could switch to MS Access, which is very well
>> > supported by Microsoft Office.
>> >
>> > :-)
>> mmhhh MySQL Vs. Access MS... just a little be different :-)))
>
> Yes, very :-)
>
> So is MS Office and OpenOffice :-)
>
>
>
> --
> Martijn
>
>
> --
> 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: OSX Installation woes...

[Brent Baisley]

Are you running server or client?
Up until this week I've always run MySQL on OS X client, since there 
really isn't much of a difference between OS X client and server as far 
as MySQL is concerned. I just ran into the problem where I locked 
myself out, but I couldn't start MySQL from the command line or the 
MySQL preferences pane (which I don't normally use). Deleting and 
reinstalling MySQL still didn't let me startup from the command line. 
I've installed MySQL many times before on OS X with no problems 
starting and stopping from the command line, so this was weird and 
frustrating.
I stumbled across Apple's MySQL Manager in the applications folder, a 
program which I had totally forgotten about because it is so lame. I 
launched that and saw there were two buttons, Start and Install (that's 
management!?). I pressed install and then start, after that, MySQL was 
running and I could stop and start from the command line. Not sure what 
Apple is doing here, although I do see entries in the system.log about 
sending a command to mahelper. Maybe it has something to do with the 
watchddog program wanting to manage the process. Haven't looked into 
that yet.

Anyway, if you are running server, that's something to try. If not, 
well, perhaps I just told everyone a nice story.

On Mar 8, 2005, at 5:45 PM, Jeff Justice wrote:
Okay, here's a brief synopsis, with suggestions made and tried:
Cannot get MySQL to launch.  A process is never started in the 
activity monitor.  What exactly is the name of the process I should 
see?

Tried uninstalling MySQL by deleting the MySQL directories in 
/usr/local/.  (after first making sure there is no process running, 
which near as I can tell, it's not).

Isn't there also a startup item?  Where are it's pieces/parts?
Does one de-install the pref pane by simply control-clicking and 
selecting "Remove MySQL Preference Pane"?

I can't get this thing back to running like when I first installed it. 
 Something is still goofy.

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


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

Re: CASE statement and version 4.1.x

[Philippe Poelvoorde]

Daniel Kasak wrote:
Homam S.A. wrote:
In the documentation, it doesn't mention which version
of MySQL supports the CASE statement, but it refers to
stored procedures, so is it only supported for 5.x?
I can't get any example of a CASE statement work in
MySQL.
 


For example, I can execute the following in MS SQL
Server:
UPDATE MyTable
SET
field1 = CASE WHEN field3 = 1 THEN 10 ELSE 20 END,
field2 = field2 | CASE WHEN field4 = 'B' THEN 1 ELSE 0
END | CASE WHEN field4 = 'C' THEN 2 ELSE 0 END
 

Case works in 4.0.x ( and maybe before, haven't checked ).
Your problem is elsewhere.
I'm not sure if you can use the pipe symbol as an OR operator in the way 
you want - to be honest I haven't used it at all in SQL.
Try rewriting it to use nothing but case statements ( no | operators ).


http://dev.mysql.com/doc/mysql/en/logical-operators.html
it's || not | (not sure about bit-operation...)
--
Philippe Poelvoorde
COS Trading Ltd.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Use MySQL with Microsoft Office

[Jigal van Hemert]

From: "Curtis Maurand"
> Using ODBC, however, you can link Access tables to MySQL tables and use
> Access as the front end to MySQL.  It works very nicely.

Using ODBC you can access MySQL from OpenOffice.org (which has an
Access-like frontend built in), using ODBC and MS Jet engine (standard with
recent Windows versions) you can access and edit Access databases (*.mdb)
with OpenOffice.org.

Anyway, there are always multiple solutions for a problem and the original
poster managed to get MS Publisher to do a mailmerge with MySQL data.

Regards, Jigal.


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

build fails "make test"

[Mark Johnson]

After building, I ran "make test".  The func_group test failed.
Here is the output:
func_group [ fail ]
Errors are (from 
/mnt/raid/download/computer/software/mysql/mysql-5.0.2-alpha/mysql-test/var/log/mysqltest-time) 
:
/mnt/raid/download/computer/software/mysql/mysql-5.0.2-alpha/client/.libs/mysqltest: 
At line 477: Result content mismatch
(the last lines may be the most important ones)
Below are the diffs between actual and expected results:
---
*** r/func_group.result 2004-12-01 03:31:35.0 +0300
--- r/func_group.reject 2005-03-09 02:00:49.0 +0300
***
*** 578,584 
 explain
 select min(a1) from t1 where a1 != 'KKK';
 idselect_type table   typepossible_keys   key key_len 
refrows Extra
! 1 SIMPLE  t1  index   PRIMARY PRIMARY 3   NULL14  
Using where; Using index
 explain
 select max(a3) from t1 where a2 < 2 and a3 < 'SEA';
 idselect_type table   typepossible_keys   key key_len 
refrows Extra
--- 578,584 
 explain
 select min(a1) from t1 where a1 != 'KKK';
 idselect_type table   typepossible_keys   key key_len 
refrows Extra
! 1 SIMPLE  t1  range   PRIMARY PRIMARY 3   NULL14  
Using where; Using index
 explain
 select max(a3) from t1 where a2 < 2 and a3 < 'SEA';
 idselect_type table   typepossible_keys   key key_len 
refrows Extra
---

I re-ran "mysql-test-run --force" from the mysql-test directory.
The following also failed:
greedy_optimizer
group_min_max
(Their combined .reject files are well over 100k.  Let me know where to send
compressed versions.)
Note that the entire build and test process was done from a user
account, not from the root account.
>How-To-Repeat:
simply ran "make test" after building.
>Fix:
Unknown
>Submitter-Id:  
>Originator:Mark Johnson
>Organization:
n/a
>MySQL support: none
>Synopsis:  func_group test failed.
>Severity:
>Priority:
>Category:  mysql
>Class: sw-bug
>Release:   mysql-5.0.2-alpha (Source distribution)
>C compiler:gcc (GCC) 3.4.3
>C++ compiler:  g++ (GCC) 3.4.3
>Environment:
System: Linux enterprise 2.6.10 #1 Sat Feb 5 14:42:44 MST 2005 i686 
unknown unknown GNU/Linux
Architecture: i686

Additional notes on the system:
It is based on Slackware 10.0, with security patches from slackware.com 
applied.When I tried to compile MySQL with the gcc version 3.3.4, which 
came with Slackware 10, there was an internal compiler error.  I 
upgraded the compiler to the current version from slackware-current.

Some paths:  /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc 
/usr/bin/cc
GCC: Reading specs from /usr/lib/gcc/i486-slackware-linux/3.4.3/specs
Configured with: ../gcc-3.4.3/configure --prefix=/usr --enable-shared 
--enable-threads=posix --enable-__cxa_atexit --disable-checking 
--with-gnu-ld --verbose --target=i486-slackware-linux 
--host=i486-slackware-linux
Thread model: posix
gcc version 3.4.3
Compilation info: CC='gcc'  CFLAGS='-O2 -march=pentium2'  CXX='g++'  
CXXFLAGS=''  LDFLAGS=''  ASFLAGS=''
LIBC:
lrwxrwxrwx  1 root root 13 2004-10-21 06:28 /lib/libc.so.6 -> libc-2.3.2.so
-rwxr-xr-x  1 root root 1250840 2004-05-24 22:44 /lib/libc-2.3.2.so
-rw-r--r--  1 root root 2532408 2004-05-24 22:44 /usr/lib/libc.a
-rw-r--r--  1 root root 204 2004-05-24 22:31 /usr/lib/libc.so
Configure command: ./configure  'CFLAGS=-O2 -march=pentium2'

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

MySQL 5.0.2 Default Value

[Jason L. McAffee]

The MySQL manual states that as of MySQL 5.0.2 "If the
column cannot take NOT NULL as the value, MySQL defines the column with
no explicit DEFAULT clause."  How are the default values then backed-up?
I did not see any statements defining the default values.  Will I have
to re-create the default values if my database becomes corrupted?  

 

Thank you,

 

Jason McAffee

The Technology Group

RE: CASE statement and version 4.1.x

[Tom Crimmins]

On Wednesday, March 09, 2005 07:49, Philippe Poelvoorde wrote:

> Daniel Kasak wrote:
>> Homam S.A. wrote:
>> 
>>> In the documentation, it doesn't mention which version
>>> of MySQL supports the CASE statement, but it refers to
>>> stored procedures, so is it only supported for 5.x?
>>> 
>>> I can't get any example of a CASE statement work in MySQL.
>>> 
>>> 
>>> 
>> 
>> 
>>> For example, I can execute the following in MS SQL
>>> Server:
>>> 
>>> UPDATE MyTable
>>> SET
>>> field1 = CASE WHEN field3 = 1 THEN 10 ELSE 20 END,
>>> field2 = field2 | CASE WHEN field4 = 'B' THEN 1 ELSE 0
>>> END | CASE WHEN field4 = 'C' THEN 2 ELSE 0 END

What error do you get? I can get a statement similar to this to work.
 
>> Case works in 4.0.x ( and maybe before, haven't checked ).
>> Your problem is elsewhere.
>> I'm not sure if you can use the pipe symbol as an OR operator in the
>> way you want - to be honest I haven't used it at all in SQL.
>> Try rewriting it to use nothing but case statements ( no | operators
>> ). 
>> 
> 
> http://dev.mysql.com/doc/mysql/en/logical-operators.html
> 
> it's || not | (not sure about bit-operation...)

The | is a bitwise OR.

http://dev.mysql.com/doc/mysql/en/bit-functions.html

-- 
Tom Crimmins
Interface Specialist
Pottawattamie County, Iowa

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

RE: Problem with a repeated select from MS Access

[Osvaldo Sommer]

This is the code:


Public Sub Graba_Recibido(texto As String)
277On Error GoTo Manejo_Error
278Dim db As Database
279Dim rec As DAO.Recordset

281texto = Mid(texto, 2, 2) & Mid(texto, 7, Len(texto) - 7)
282texto = Mid(texto, 1, Len(texto) - 1)

284Set db = CurrentDb
285Set rec = db.OpenRecordset("select * from BALANZA_RECEPCION where
1=0", dbOpenDynaset, dbSeeChanges, dbOptimistic) ' abro la tabla
286rec.AddNew  ' Agrego uno nuevo
287rec!rc_data = texto
288rec!rc_fecha = CDbl(Date)
289rec!rc_procesado = "N"
290rec.Update  ' Actualizo
información
291rec.Close   ' cierro cursor
292Set db = Nothing

296Exit Sub
297 Manejo_Error:
298Set db = Nothing
299Procesa_Error "DibalTimer - Funciones Locales - Graba_Recibido",
True
End Sub


And this is the error report i got
-*-*-*-*-*-*-*-*-*-*-*-*

-*
Fecha: 02/03/2005 Hora: 11:43:22 AM
Error en DibalTimer - Funciones Locales - Graba_Recibido:
Error #3151 (Línea: 285) (Source: DAO.Database) ODBC--connection to
'{MySQL ODBC 3.51 Driver}dellserver' failed.

Usuario Conectado:  Administrator



In MSDN the error 3151 is:

ODBC - connection to  failed. (Error 3151)

The ODBC connection to the specified ODBC database or table could not be
made. If the ODBC database is accessed via a network connection, make
sure the network is available, and then try the operation again.


It's failing in the OpenRecordset, but all the tables are linked, and if
we open the table, we can see the data.

The problem not allways occours

Osvaldo Sommer

-Original Message-
From: Daniel Kasak [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, March 08, 2005 8:26 PM
To: Osvaldo Sommer; mysql@lists.mysql.com
Subject: Re: Problem with a repeated select from MS Access

Osvaldo Sommer wrote:

>Daniel:
>
>   The program is an interfase for a weigth system in a Retail
>environment. 
>The program query the weigth system for new data every 200 miliseconds,
>if new data is received it has to be stored in the database. The table
>has automatic correlative, the data in ascii, the date and time and a
>Timestamp.
>
>When we run the program it start well and the data gets processed and
>stored. But after a while we start getting error 3151 ODBC can't
connect
>to mysql server.
>
>We have MySql 4.0.12 and odbc 3.51.6, all the tables are linked to the
>msaccess program.
>
>What else you need of info?
>
>Osvaldo Sommer
>  
>
How about some code examples?
How are you inserting the data?

-- 
Daniel Kasak
IT Developer
NUS Consulting Group
Level 5, 77 Pacific Highway
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: [EMAIL PROTECTED]
website: http://www.nusconsulting.com.au

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

-- 
No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.7.0 - Release Date: 3/8/2005
 

-- 
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.7.0 - Release Date: 3/8/2005
 


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

[MySQLd got signal 11 on query with UNION of SELECTs]

[yur]

>Description:


Mysqld got signal 11.

HardWare:   HP Proliant DL380G4 (5G RAM + 2 * Xeon 3.4GHz)
SoftWare:   SunOS noc-db.simtel.ru 5.9 Generic_117172-17 i86pc i386 i86pc

In error log: 
=Start Log==>
mysqld got signal 11;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.

key_buffer_size=33554432
read_buffer_size=2093056
max_used_connections=3
max_connections=100
threads_connected=1
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 105636
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

050309 13:05:09  mysqld restarted
050309 13:05:10  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
=End Log==>

>How-To-Repeat:


   It is appeared always when execute next SQL-query with "UNION" keyword:

(SELECT SQL_BIG_RESULT `UserName` AS RA_UserName,`CalledStationId` AS 
RA_Line,DATE_FORMAT(`AcctStartTime`,'%d.%m %T') AS 
RA_LogOn,SEC_TO_TIME(`AcctSessionTime`) AS 
RA_Time,DATE_FORMAT(`AcctStopTime`,'%d.%m %T') AS RA_LogOff,`AcctInputOctets` 
AS RA_InByte,`AcctOutputOctets` AS RA_OutByte,`AcctTerminateCause` AS RA_Reason 
FROM radacct where AcctStartTime BETWEEN '2005030900' AND '20050309235959' 
ORDER BY `AcctStartTime` ASC LIMIT 0,300) UNION (SELECT SQL_BIG_RESULT 
`UserName` AS RA_UserName,`CalledStationId` AS 
RA_Line,DATE_FORMAT(`AcctStartTime`,'%d.%m %T') AS 
RA_LogOn,SEC_TO_TIME(`AcctSessionTime`) AS 
RA_Time,DATE_FORMAT(`AcctStopTime`,'%d.%m %T') AS RA_LogOff,`AcctInputOctets` 
AS RA_InByte,`AcctOutputOctets` AS RA_OutByte,`AcctTerminateCause` AS RA_Reason 
FROM radacct where AcctStopTime BETWEEN '2005030900' AND '20050309235959' 
ORDER BY `AcctStartTime` ASC LIMIT 0,300) LIMIT 0,300

   The SQL-query without "UNION" keyword such as:

SELECT SQL_BIG_RESULT `UserName` AS RA_UserName,`CalledStationId` AS 
RA_Line,DATE_FORMAT(`AcctStartTime`,'%d.%m %T') AS 
RA_LogOn,SEC_TO_TIME(`AcctSessionTime`) AS 
RA_Time,DATE_FORMAT(`AcctStopTime`,'%d.%m %T') AS RA_LogOff,`AcctInputOctets` 
AS RA_InByte,`AcctOutputOctets` AS RA_OutByte,`AcctTerminateCause` AS RA_Reason 
FROM radacct where AcctStartTime BETWEEN '2005030900' AND '20050309235959' 
ORDER BY `AcctStartTime` ASC LIMIT 0,300)

   is successfull always 

>Fix:   Fix by using of MySQL 4.0.20



>Submitter-Id:  
>Originator:Yury B.Razbegin
>Organization:  Technical Center of ElectroCommunication - Ulyanovsk Branch
of the VolgaTeleCom JSV
 
>MySQL support: none
>Synopsis:   mysqld crash
>Severity:   critical
>Priority:   high
>Category:  mysql
>Class:  sw-bug
>Release:   mysql-4.1.10-max (MySQL Community Edition - Experimental (GPL))

>C compiler:
>C++ compiler:  
>Environment:

System: SunOS noc-db.simtel.ru 5.9 Generic_117172-17 i86pc i386 i86pc
Architecture: i86pc

Some paths:  //bin/perl /usr/local/bin/make /usr/local/bin/gcc /usr/ucb/cc
GCC: Reading specs from /opt/local/bin/../lib/gcc/i386-pc-solaris2.9/3.4.2/specs
Configured with: ../configure --with-as=/usr/ccs/bin/as 
--with-ld=/usr/ccs/bin/ld --disable-nls
Thread model: posix
gcc version 3.4.2
Compilation info: CC='cc'  CFLAGS='-DBIG_TABLES -xO3 -mt -fsimple=1 
-ftrap=%none -nofstore -xbuiltin=%all -xlibmil -xlibmopt -xtarget=native'  
CXX='CC'  CXXFLAGS='-DBIG_TABLES -xO3 -mt -fsimple=1 -ftrap=%none -nofstore 
-xbuiltin=%all -xlibmil -xlibmopt -xtarget=native'  LDFLAGS=''  ASFLAGS=''
LIBC: 
-rw-r--r--   1 root bin  1908016 îÏÑ 22 00:56 /lib/libc.a
lrwxrwxrwx   1 root root  11 æÅ×  1 14:50 /lib/libc.so -> 
./libc.so.1
-rwxr-xr-x   1 root bin   805364 îÏÑ 22 00:56 /lib/libc.so.1
-rw-r--r--   1 root bin  1908016 îÏÑ 22 00:56 /usr/lib/libc.a
lrwxrwxrwx   1 root root  11 æÅ×  1 14:50 /usr/lib/libc.so -> 
./libc.so.1
-rwxr-xr-x   1 root bin   805364 îÏÑ 22 00:56 /usr/lib/libc.so.1
Configure command: ./configure '--prefix=/usr/local/mysql' 
'--localstatedir=/usr/local/mysql/data' '--libexecdir=/usr/local/mysql/bin' 
'--with-comment=MySQL Community Edition - Experimental (GPL)' 
'--with-extra-charsets=complex' '--with-server-suffix=-max' 
'--enable-thread-safe-client' '--enable-local-infile' 
'--with-named-curses=-lcurses' '--disable-shared' '--with-berkeley-db' 
'--with-readline' '--with-embedded-server' '--with-archive-storage-engine' 
'--with-ndbcluster' '--with-innodb' 'CC=cc' 'CFLAGS=-DBIG_TABLES -xO3 -mt 
-f

safe way of replication?

[Chris Knipe]

Lo all,
Just wondering... Would the below be considered a "safe" way to do 
replication...

MasterBD:  One Database (most Critical)
SlaveDB: Replicating all databases from MasterBD as well as hosting allot of 
other 3rd party, or customer DBs.

Basically, the MasterBD holds a single critical database for our company 
(i.e. main database).  The slave DB then becomes a slave for the MasterDB 
(one way replication), but also hosts allot of other not so critical 
databases?

So far, this seems to be working, but I am getting a couple of errors or 
problems in regards to the mysql table for user authentication...  My guess 
is that I more than likely just don't need to replicate that DB from the 
master.

Would this be considered safe??  Or should I look at a third database server 
for deployment?

--
Chris. 

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

RE: select date_format('2004-10-03 15:06:14','%m/%d/%y %T');

[Stembridge, Michael]

RE: Import Access Data...

> I'll try and import using ODBC.  Is there any good web sites
> about ODBC and its operations that I can learn about it?
> 
> Scott

If you haven't found this by now: 

http://forums.mysql.com/list.php?65 - MySQL forum for Access conversion.
Many people use ODBC to migrate from Access to MySQL, as such there is much
information to be found here.  


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

Re: Explain and indexes

[Jigal van Hemert]

From: "Terry Spencer"
> An index exists on all three columns referred to, in addition to a
> combination of del and signoff.
>
> The indexes are listed as possible keys, but none used by the query; key =
> null. Can anyone suggest why? How can I optimise this?

How many records are there in the table? If MySQL's query optimizer thinks
that the query will return more than approx. 30% of the records it is
probably faster to do a tablescan than to use the index and access the
records one by one.

Regards, Jigal.


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

Re: safe way of replication?

[Gary Richardson]

I would probably not replica the mysql database in your case.

We run a similar setup. One problem we have is people connecting to
the wrong database server and applying updates (for example, they
think they are in the master, but it's really the slave and they
perform an update or an insert). As you can guess, it causes problems
with data integrity. In order to get around this, we remove
insert,update,delete from our users on the slave..

out.


On Wed, 9 Mar 2005 17:49:10 +0200, Chris Knipe <[EMAIL PROTECTED]> wrote:
> Lo all,
> 
> Just wondering... Would the below be considered a "safe" way to do
> replication...
> 
> MasterBD:  One Database (most Critical)
> SlaveDB: Replicating all databases from MasterBD as well as hosting allot of
> other 3rd party, or customer DBs.
> 
> Basically, the MasterBD holds a single critical database for our company
> (i.e. main database).  The slave DB then becomes a slave for the MasterDB
> (one way replication), but also hosts allot of other not so critical
> databases?
> 
> So far, this seems to be working, but I am getting a couple of errors or
> problems in regards to the mysql table for user authentication...  My guess
> is that I more than likely just don't need to replicate that DB from the
> master.
> 
> Would this be considered safe??  Or should I look at a third database server
> for deployment?
> 
> --
> Chris.
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
> 
>

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

Re: MySQL 5.0.2 Default Value

[Paul DuBois]

At 9:08 -0500 3/9/05, Jason L. McAffee wrote:
The MySQL manual states that as of MySQL 5.0.2 "If the
column cannot take NOT NULL as the value, MySQL defines the column with
no explicit DEFAULT clause."  How are the default values then backed-up?
I did not see any statements defining the default values.  Will I have
to re-create the default values if my database becomes corrupted?
The statement you quote applies in the absence of an explicit DEFAULT
clause that specifies the default value.  I don't know what it means
to back up a default value or re-create them, but I'll guess that the
answer to your questions is: Include an explicit DEFAULT clause in
your column definitions.
--
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: RAID, MySQL and SATA - benchmarks

[Gary Richardson]

I found the article very interesting. It seems they couldn't trash
3ware cards enough.

We swear by 3ware cards -- other than the PCIX riser card issue, we
haven't a single problem with them. Our production database server is
running off of a RAID1 for the OS and a RAID10 for the data and every
time we are doing schema maintenance or database migration, we are
blown away by the speed of the machine.

BTW, never use Western Digital  Raptor drives. Some people may
remember me posting about them around October. Out of 6 drives in the
machine, we've had 10 failures. We're currently replacing the drives
with Seagates. We'll take the 3K RPM hit for piece of mind.


On Wed, 9 Mar 2005 17:04:54 +1100, Richard Dale <[EMAIL PROTECTED]> wrote:
> Tweakers.net has completed a comparison of 9 serial ATA RAID 0/1/5/10
> controllers at:
> http://www.tweakers.net/reviews/557
> 
> There is a specific section on MySQL performance in the section:
> http://www.tweakers.net/reviews/557/25
> 
> Just thought these articles would be of interest to some (it's interesting
> to see the difference between single drive operations and multiple drive
> operations - up to 12 drives, with the different RAID levels).
> 
> Here's my rough speed comparison based upon eyeballing the graphs.  Some
> controllers were better than others so this represents a rough average of
> the entire set of controllers:
> 
> Single drive -  1.0
> RAID 1 - 2 disks - 1.4
> RAID5 - 3 disks -  1.7
> RAID5 - 4 disks -  2.0
> RAID10 - 4 disks - 2.0
> RAID5 - 6 disks - 2.3
> RAID5 - 8 disks -  2.4
> RAID5 - 10 disks - 2.9
> RAID5 - 12 disks - 3.1
> 
> The article also highlighted the difference between the reliable
> write-through mode and the write-back mode.  In write-through mode,
> performance is degraded by approximately 50%.  Clearly if you want
> reliability, a controller with a battery backup is highly recommended.
> 
> On the issue of SCSI version SATA performance, it would appear that SCSI
> still performas somewhat better (about 20% more transactions but the test
> was comparing 15K RPM SCSI drives to 10K RPM SATA drives) but the reduced
> cost of SATA drives allows you to add more drives to achieve the same
> performance levels at lesser cost.  With Serial ATA II drives around the
> corner (with Native Command Queueing) then I think we'll find SATA will take
> a much bigger lead in database performance.
> 
> Really nice work from tweakers.net - would have been interesting to see the
> Linux performance too though.
> 
> Best regards,
> Richard Dale.
> Norgate Investor Services
> - Premium quality Stock, Futures and Foreign Exchange Data for
>   markets in Australia, Asia, Canada, Europe, UK & USA -
> www.premiumdata.net
> 
> --
> 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: skip-name-resolve

[Mauricio Pellegrini]

Is there a way to check whether this option is active or not, while the
server is running?

Thanks 
Mauricio


On Tue, 2005-03-08 at 06:39, Gleb Paharenko wrote:
> Hello.
> 
> > Is the option well set and in the right place ?
> 
> Yes. If your mysqld reads this file (use --defaults-file command line
> option to be sure). 
> 
> > No.2
> > This slowness problem could occur also on a server running SuSe Linux
> > 8.2 ?
> 
> Usually using MySQL server on Unix gives better results than on Windows. See:
>   http://dev.mysql.com/doc/mysql/en/windows-vs-unix.html
> 
> 
> 
> Mauricio Pellegrini <[EMAIL PROTECTED]> wrote:
> > Hi,
> > Sometimes our Internet conection goes down and then all conections via
> > tcp/ip to our the mysql server (v.4.1.5) are slow.
> > 
> > I've read in the manual that one could use --skip-name-resolve as a
> > possible solution to this problem under windows.
> > 
> > The thing is that I've set this option int /etc/my.cnf in this way
> > 
> > [mysqld]
> > port= 3306
> > socket  = /tmp/mysql.sock
> > skip-locking
> > skip-name-resolve
> > 
> > 
> > but I don't see it in the output from ps aux | grep mysql
> > my questions are 
> > 
> > No.1 
> > Is the option well set and in the right place ?
> > 
> > 
> > No.2
> > This slowness problem could occur also on a server running SuSe Linux
> > 8.2 ?
> > 
> > 
> > 
> > Thanks 
> > Mauricio
> > 
> > 
> > 
> > 
> 
> 
> -- 
> 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: Access Denied

[Michael Stassen]

Gleb Paharenko wrote:
Hello.
Execute FLUSH PRIVILEGES after granting.
See:
  http://dev.mysql.com/doc/mysql/en/flush.html
  http://dev.mysql.com/doc/mysql/en/privilege-changes.html
Yes, please read this one.  FLUSH PRIVILEGES is NOT needed after GRANT, 
REVOKE, or SET PASSWORD.  You only need to FLUSH if you directly edit the 
grant tables (INSERT, UPDATE, DELETE).  Since he/she is using GRANT, FLUSH 
PRIVILEGES is unlikely to be relevant.

  http://dev.mysql.com/doc/mysql/en/access-denied.html
  http://dev.mysql.com/doc/mysql/en/user-resources.html
Connect to the server using mysql command line client using specified in GRANT statement
username and password to check that the problem not in MySQL server. What output does the 
following statement produce:

  SHOW GRANTS FOR 'metal-host'@'localhost';
Run this when it works, and then again when it doesn't.  If the result is 
different, then something is changing the privileges assigned to 
[EMAIL PROTECTED], so the solution will involve finding what is doing 
that.  If the privs are the same, then we need to look elsewhere.  From the 
(not entirely clear) description, I expect the first case.

What exact version of MySQL server do you use?
[snip]
Hello Gleb, no i m not doing flush..i dont even know what flush is.
do u have anything in mind ?
[snip]
Michael
Metal Host Contact wrote:
> I have installed in a single user called "metal-host" more than 10
> databases all with different names and in different php scripts (phpbb
> phpnuke mambo etc).
> the problem is that they only work as I install them,after some hour later
> all the scripts show me that error : Access denied for user:
> '[EMAIL PROTECTED]' (Using password: YES) 
> then im trying to do : GRANT ALL PRIVILEGES ON somethign.* TO
> [EMAIL PROTECTED] IDENTIFIED BY "password"; and then each database that
> i re-give  the GRANT all command works perfectly..but for 1 minute
> only,then it gives me the same : Access denied for user:
> '[EMAIL PROTECTED]' (Using password: YES)  So is there any
> limitation for the number of DBases that each user should
> run ?
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Compressing after Deletion

[Eamon Daly]

Two more notes, assuming this is a MyISAM table. If this
table sees concurrent inserts (many mixed INSERTs and
SELECTs), you'll definitely want to run an OPTIMIZE TABLE
after your purge. Large numbers of deletes or updates to
variable-length fields will result in free blocks in the
data file, and they'll slow down your INSERTs as MySQL tries
to figure out the best way to fill the holes. That said,
OPTIMIZE TABLE locks the table and rebuilds both the data
file and all of its indexes, so it can be awfully slow.
If you do decide to OPTIMIZE immediately after your deletes,
you can speed the entire process up significantly by using
"DELETE QUICK FROM TABLE". This deletes rows but does not
update the table's indexes. Running OPTIMIZE TABLE
immediately thereafter will recreate those indexes for you,
building them from the new, smaller table.
More on MyISAM tables:
http://dev.mysql.com/doc/mysql/en/myisam-storage-engine.html
OPTIMIZE TABLE:
http://dev.mysql.com/doc/mysql/en/optimize-table.html
DELETE:
http://dev.mysql.com/doc/mysql/en/delete.html

Eamon Daly

- Original Message - 
From: <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>; 
Sent: Wednesday, March 09, 2005 4:43 AM
Subject: RE: Compressing after Deletion


Note that with MySQL, unlike some other databases, you don't have to do
this. If you are going to add new records to replace the deleted ones,
MySQL will re-use the space freed by deletions with no special actions on
your part. Of course, if you are not going to replace the deleted records,
the commands suggested by David will certainly free space and probably
improve performance.
   Alec Cawley
"Logan, David (SST - Adelaide)" <[EMAIL PROTECTED]> wrote on 08/03/2005
22:58:12:
Hi Chris,
For MyISAM/BDB tables use OPTIMIZE TABLE ;
For InnoDB tables try ALTER TABLE  TYPE=InnoDB;

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]
Sent: Wednesday, 9 March 2005 9:19 AM
To: mysql@lists.mysql.com
Subject: Compressing after Deletion
I have looked in the documentation and either I am not looking for the
right thing or have simply overlooked it. But my question is this, I
have
a database with 35 Million records, and I need to delete about 25
million
of those. After deletion I would think that I would need to compress,
shrink, or otherwise optimize the database. How is that done? do I need
to
do it? What commands should I be looking up in the docs?

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

Re: skip-name-resolve

[Keith Ivey]

Mauricio Pellegrini wrote:
Is there a way to check whether this option is active or not, while the
server is running?
Not sure why it doesn't show up in "SHOW VARIABLES", but one way 
to check would be to see whether the hosts in the "Host" column 
of "SHOW PROCESSLIST" are shown as IP addresses or hostnames.

--
Keith Ivey <[EMAIL PROTECTED]>
Smokefree DC
http://www.smokefreedc.org
Washington, DC
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

slowly running mysql server

[iv]

Hi
I have a big problem with my mysq server. ater installing it in a new 
machine it works extremally slowly.
My mysql server serves a quite big web site with numerous queries. 
Unfortunately, I can't alter the queries.
And one more thing. Everything run much better on a previous machine.
What I have now:
 AMD Sempron 3100+, Asus K8N-E, Western Digital WDC-WD800JD SATA drive; 
MemTotal: 1553596 kB
 I'm running now Debian GNU/Linux 3.0, kernel 2.6.10, mysql  Ver 14.7 
Distrib 4.1.9, for pc-linux-gnu;
 My.cfg:

[client]
port= 3306
socket  = /var/run/mysqld/mysqld.sock
[mysqld_safe]
socket  = /var/run/mysqld/mysqld.sock
nice= 0
[mysqld]
user= mysql
pid-file= /var/run/mysqld/mysqld.pid
socket  = /var/run/mysqld/mysqld.sock
port= 3306
log = /var/log/mysql/mysql.log
basedir = /usr
datadir = /var/lib/mysql
tmpdir  = /tmp
language= /usr/share/mysql/polish
skip-external-locking
default-character-set = latin2
old-passwords   = 1
bind-address= 127.0.0.1
key_buffer  = 16M
max_allowed_packet  = 16M
thread_stack= 128K
query_cache_limit= 1258292
query_cache_size = 26214400
query_cache_type = 1
query_cache_min_res_unit = 8000
log-slow-queries= /var/log/mysql/mysql-slow.log
[mysqldump]
quick
quote-names
max_allowed_packet  = 1M
[mysql]
[isamchk]
key_buffer  = 16M
**
the query from /var/log/mysql/mysql-slow.log:
# Time: 050309 17:02:37
# [EMAIL PROTECTED]: wwwuser[wwwuser] @ localhost []
# Query_time: 254  Lock_time: 0  Rows_sent: 185  Rows_examined: 1956616
SET timestamp=1110384157;
SELECT pojazdy.*, marki.marka, IF(marki.marka is NULL,'???',marki.marka) 
as sort , zdjecia.nazwa as zdjecie_na
zwa, zdjecia.katalog as zdjecie_katalog, zdjecia.typ as zdjecie_typ FROM 
pojazdy LEFT JOIN marki ON ( pojazdy.
marka_id=marki.id ) LEFT JOIN zdjecia ON ( pojazdy.id = 
zdjecia.pojazd_id )  ,firmy WHERE 1 and pojazdy.open_d
ate < NOW() and pojazdy.validate > NOW() and pojazdy.block_date=0 and 
pojazdy.kontakt_id=firmy.id and  firmy.n
azwa='carstein' and pojazdy.typ_id='1' GROUP BY pojazdy.id order by 
sort, model, cena;
# Time: 050309 17:03:10

additionally, this query returns 2216 rows with pictures. the process 
longs about 2 minutes :(
and additionally, I'm a newbie with mysql.
Any ideas will be highly appreciated.
Thanks in advance,
/iv
***
*

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

the new Connector J

[Bill Dodson]

First off, sorry if this is going to the wrong list.
I installed the new Connector J (mysql-connector-java-3.1.7-bin.jar). 
Now some of my code does not work.  It seems that when I do a 
ResultSet.getObject() on a Date field with the value -00-00 (the 
default value) I get an SQLException.  While I know that -00-00 is 
not a valid date, the old version of the driver 
(mysql-connector-java-3.0.9-stable) works fine.

I have a work around, but my code seemed much cleaner without it. 

Does anyone know why this exception is now being thrown?
So far I have just been using the old version, but I would like to see 
if there is any performance enhancements in the newer version, and I 
don't want to get left behind.

Here is the exception trace:
Value '-00-00' can not be represented as java.sql.Date
java.sql.SQLException: Value '-00-00' can not be represented as 
java.sql.Date
   at com.mysql.jdbc.ResultSet.getDateFromString(ResultSet.java:4648)
   at com.mysql.jdbc.ResultSet.getDate(ResultSet.java:1023)
   at com.mysql.jdbc.ResultSet.getDate(ResultSet.java:980)
   at com.mysql.jdbc.ResultSet.getObject(ResultSet.java:1858)
   at 
utils.database.ScrollableResultSetTableModel.getValueAt(ScrollableResultSetTableModel.java:133)
   at javax.swing.JTable.getValueAt(JTable.java:1771)
   at javax.swing.JTable.prepareRenderer(JTable.java:3724)
   at javax.swing.plaf.basic.BasicTableUI.paintCell(BasicTableUI.java:1149)
   at 
javax.swing.plaf.basic.BasicTableUI.paintCells(BasicTableUI.java:1051)
   at javax.swing.plaf.basic.BasicTableUI.paint(BasicTableUI.java:974)
   at javax.swing.plaf.ComponentUI.update(ComponentUI.java:142)
   at javax.swing.JComponent.paintComponent(JComponent.java:541)
   at javax.swing.JComponent.paint(JComponent.java:808)
   at javax.swing.JComponent.paintChildren(JComponent.java:647)
   at javax.swing.JComponent.paint(JComponent.java:817)
   at javax.swing.JViewport.paint(JViewport.java:722)
   at javax.swing.JComponent.paintChildren(JComponent.java:647)
   at javax.swing.JComponent.paint(JComponent.java:817)
   at javax.swing.JComponent.paintChildren(JComponent.java:647)
   at javax.swing.JComponent.paint(JComponent.java:817)
   at javax.swing.JComponent.paintChildren(JComponent.java:647)
   at javax.swing.JComponent.paint(JComponent.java:817)
   at javax.swing.JComponent.paintChildren(JComponent.java:647)
   at javax.swing.JComponent.paint(JComponent.java:817)
   at javax.swing.JComponent.paintChildren(JComponent.java:647)
   at javax.swing.JComponent.paint(JComponent.java:817)
   at javax.swing.JComponent.paintChildren(JComponent.java:647)
   at javax.swing.JComponent.paint(JComponent.java:817)
   at javax.swing.JComponent.paintChildren(JComponent.java:647)
   at javax.swing.JComponent.paint(JComponent.java:817)
   at javax.swing.JComponent.paintChildren(JComponent.java:647)
   at javax.swing.JComponent.paint(JComponent.java:817)
   at javax.swing.JLayeredPane.paint(JLayeredPane.java:557)
   at javax.swing.JComponent.paintChildren(JComponent.java:647)
   at javax.swing.JComponent.paintWithOffscreenBuffer(JComponent.java:4794)
   at javax.swing.JComponent.paintDoubleBuffered(JComponent.java:4740)
   at javax.swing.JComponent.paint(JComponent.java:798)
   at java.awt.GraphicsCallback$PaintCallback.run(GraphicsCallback.java:21)
   at 
sun.awt.SunGraphicsCallback.runOneComponent(SunGraphicsCallback.java:60)
   at 
sun.awt.SunGraphicsCallback.runComponents(SunGraphicsCallback.java:97)
   at java.awt.Container.paint(Container.java:1312)
   at sun.awt.RepaintArea.paint(RepaintArea.java:177)
   at sun.awt.windows.WComponentPeer.handleEvent(WComponentPeer.java:260)
   at java.awt.Component.dispatchEventImpl(Component.java:3678)
   at java.awt.Container.dispatchEventImpl(Container.java:1627)
   at java.awt.Window.dispatchEventImpl(Window.java:1606)
   at java.awt.Component.dispatchEvent(Component.java:3477)
   at java.awt.EventQueue.dispatchEvent(EventQueue.java:456)
   at 
java.awt.EventDispatchThread.pumpOneEventForHierarchy(EventDispatchThread.java:201)
   at 
java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:151)
   at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:145)
   at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:137)
   at java.awt.EventDispatchThread.run(EventDispatchThread.java:100)
SQLException: SQLState(S1009)

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

Re: safe way of replication?

[Chris Knipe]

Hmm,  We'll we just won't give them access to the replicated database to 
begin with... It's used internally, and the few people in the company that 
do need to write to the database are all skilled administrators / 
programmers who know what to do :)  Dont think we'll be having those 
problem.

I'll stop replicating mysql tables then... Thanks ;)
--
Chris.

- Original Message - 
From: "Gary Richardson" <[EMAIL PROTECTED]>
To: "Chris Knipe" <[EMAIL PROTECTED]>
Cc: 
Sent: Wednesday, March 09, 2005 6:23 PM
Subject: Re: safe way of replication?


I would probably not replica the mysql database in your case.
We run a similar setup. One problem we have is people connecting to
the wrong database server and applying updates (for example, they
think they are in the master, but it's really the slave and they
perform an update or an insert). As you can guess, it causes problems
with data integrity. In order to get around this, we remove
insert,update,delete from our users on the slave..
out.
On Wed, 9 Mar 2005 17:49:10 +0200, Chris Knipe <[EMAIL PROTECTED]> 
wrote:
Lo all,
Just wondering... Would the below be considered a "safe" way to do
replication...
MasterBD:  One Database (most Critical)
SlaveDB: Replicating all databases from MasterBD as well as hosting allot 
of
other 3rd party, or customer DBs.

Basically, the MasterBD holds a single critical database for our company
(i.e. main database).  The slave DB then becomes a slave for the MasterDB
(one way replication), but also hosts allot of other not so critical
databases?
So far, this seems to be working, but I am getting a couple of errors or
problems in regards to the mysql table for user authentication...  My 
guess
is that I more than likely just don't need to replicate that DB from the
master.

Would this be considered safe??  Or should I look at a third database 
server
for deployment?

--
Chris.
--
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: the new Connector J

[Mark Matthews]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Bill Dodson wrote:
> First off, sorry if this is going to the wrong list.
> 
> I installed the new Connector J (mysql-connector-java-3.1.7-bin.jar). 
> Now some of my code does not work.  It seems that when I do a 
> ResultSet.getObject() on a Date field with the value -00-00 (the 
> default value) I get an SQLException.  While I know that -00-00 is 
> not a valid date, the old version of the driver 
> (mysql-connector-java-3.0.9-stable) works fine.
> 
> I have a work around, but my code seemed much cleaner without it. 
> 
> Does anyone know why this exception is now being thrown?
> 
> So far I have just been using the old version, but I would like to see 
> if there is any performance enhancements in the newer version, and I 
> don't want to get left behind.
[snip]

Bill,

Maybe you missed this section in the manual?

http://dev.mysql.com/doc/connector/j/en/cj-upgrading-3-0-to-3-1.html

-Mark


- --
Mark Matthews
MySQL AB, Software Development Manager - Connectivity
www.mysql.com

MySQL User Conference (Santa Clara CA, 18-21 April 2005)
Early registration until February 28: http://www.mysqluc.com/
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.6 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD4DBQFCL0/gtvXNTca6JD8RAouQAKCVsdL3alHJZMCg1kWvuA6RckKgnwCXTMTp
JUr/3gPkJuYajpmV0XKsgQ==
=8ZZl
-END PGP SIGNATURE-

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

Re: safe way of replication?

[Atle Veka]

Even if you replicate the 'mysql' DB, GRANT/REVOKE statements are not
replicated, nor are FLUSH statements. So if you are adding new access
privileges on the master they will not be active on the slave until you
issue FLUSH PRIVILEGES (one the slave).

Hope this helps. :)


Atle
-
Flying Crocodile Inc, Unix Systems Administrator


On Wed, 9 Mar 2005, Chris Knipe wrote:

> Lo all,
>
> Just wondering... Would the below be considered a "safe" way to do
> replication...
>
> MasterBD:  One Database (most Critical)
> SlaveDB: Replicating all databases from MasterBD as well as hosting allot of
> other 3rd party, or customer DBs.
>
> Basically, the MasterBD holds a single critical database for our company
> (i.e. main database).  The slave DB then becomes a slave for the MasterDB
> (one way replication), but also hosts allot of other not so critical
> databases?
>
> So far, this seems to be working, but I am getting a couple of errors or
> problems in regards to the mysql table for user authentication...  My guess
> is that I more than likely just don't need to replicate that DB from the
> master.
>
> Would this be considered safe??  Or should I look at a third database server
> for deployment?
>
> --
> Chris.
>
>

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

Re: safe way of replication?

[Keith Ivey]

Atle Veka wrote:
Even if you replicate the 'mysql' DB, GRANT/REVOKE statements are not
replicated, nor are FLUSH statements. So if you are adding new access
privileges on the master they will not be active on the slave until you
issue FLUSH PRIVILEGES (one the slave).
What version are you talking about?  GRANT and REVOKE seem to be 
replicated fine nowadays.  I remember some bugs related to their 
replication, but they were about replicating them when they 
shouldn't be (when the mysql DB wasn't being replicated), not 
failing to replicate them when they should be.  Also FLUSH 
PRIVILEGES is replicated as of version 4.1.1, according to the 
documentation.

The original poster might want to look into the read-only option 
to prevent accidental modification of the slave data:

| --read-only
|
| This option causes the slave to allow no updates except from
| slave threads or from users with the SUPER privilege. This can
| be useful to ensure that a slave server accepts no updates
| from clients.
|
| This option is available as of MySQL 4.0.14.
--
Keith Ivey <[EMAIL PROTECTED]>
Smokefree DC
http://www.smokefreedc.org
Washington, DC
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

read-only and CREATE TEMPORARY TABLE (was: safe way of replication?)

[Eamon Daly]

Speaking of the --read-only option, I don't suppose there's
any way to run --read-only but allow CREATE TEMPORARY TABLE,
is there? We run several reports against a slave server
which require temporary tables for speed, and have had a
problem with the occasional accidental write to the slave.
The slave server is running MySQL 4.0.20.

Eamon Daly

- Original Message - 
From: "Keith Ivey" <[EMAIL PROTECTED]>
To: 
Sent: Wednesday, March 09, 2005 2:11 PM
Subject: Re: safe way of replication?


Atle Veka wrote:
Even if you replicate the 'mysql' DB, GRANT/REVOKE statements are not
replicated, nor are FLUSH statements. So if you are adding new access
privileges on the master they will not be active on the slave until you
issue FLUSH PRIVILEGES (one the slave).
What version are you talking about?  GRANT and REVOKE seem to be 
replicated fine nowadays.  I remember some bugs related to their 
replication, but they were about replicating them when they 
shouldn't be (when the mysql DB wasn't being replicated), not 
failing to replicate them when they should be.  Also FLUSH 
PRIVILEGES is replicated as of version 4.1.1, according to the 
documentation.

The original poster might want to look into the read-only option 
to prevent accidental modification of the slave data:

| --read-only
|
| This option causes the slave to allow no updates except from
| slave threads or from users with the SUPER privilege. This can
| be useful to ensure that a slave server accepts no updates
| from clients.
|
| This option is available as of MySQL 4.0.14.

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

RE: CASE statement and version 4.1.x

[Homam S.A.]

Thanks Tom, Daniel, and Philippe for you replies.

Yes, I got the statement working exactly as I wrote
it. I was confused about this documentation:

http://dev.mysql.com/doc/mysql/en/case-statement.html

which states that you need to specify "END CASE"
instead of just "END". So I used the same syntax as
T-SQL with "END" and it worked fine.

Homam



--- Tom Crimmins <[EMAIL PROTECTED]> wrote:
> 
> On Wednesday, March 09, 2005 07:49, Philippe
> Poelvoorde wrote:
> 
> > Daniel Kasak wrote:
> >> Homam S.A. wrote:
> >> 
> >>> In the documentation, it doesn't mention which
> version
> >>> of MySQL supports the CASE statement, but it
> refers to
> >>> stored procedures, so is it only supported for
> 5.x?
> >>> 
> >>> I can't get any example of a CASE statement work
> in MySQL.
> >>> 
> >>> 
> >>> 
> >> 
> >> 
> >>> For example, I can execute the following in MS
> SQL
> >>> Server:
> >>> 
> >>> UPDATE MyTable
> >>> SET
> >>> field1 = CASE WHEN field3 = 1 THEN 10 ELSE 20
> END,
> >>> field2 = field2 | CASE WHEN field4 = 'B' THEN 1
> ELSE 0
> >>> END | CASE WHEN field4 = 'C' THEN 2 ELSE 0 END
> 
> What error do you get? I can get a statement similar
> to this to work.
>  
> >> Case works in 4.0.x ( and maybe before, haven't
> checked ).
> >> Your problem is elsewhere.
> >> I'm not sure if you can use the pipe symbol as an
> OR operator in the
> >> way you want - to be honest I haven't used it at
> all in SQL.
> >> Try rewriting it to use nothing but case
> statements ( no | operators
> >> ). 
> >> 
> > 
> >
>
http://dev.mysql.com/doc/mysql/en/logical-operators.html
> > 
> > it's || not | (not sure about bit-operation...)
> 
> The | is a bitwise OR.
> 
> http://dev.mysql.com/doc/mysql/en/bit-functions.html
> 
> -- 
> Tom Crimmins
> Interface Specialist
> Pottawattamie County, Iowa
> 
> -- 
> 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: CASE statement and version 4.1.x

[Paul DuBois]

At 12:40 -0800 3/9/05, Homam S.A. wrote:
Thanks Tom, Daniel, and Philippe for you replies.
Yes, I got the statement working exactly as I wrote
it. I was confused about this documentation:
http://dev.mysql.com/doc/mysql/en/case-statement.html
which states that you need to specify "END CASE"
instead of just "END". So I used the same syntax as
T-SQL with "END" and it worked fine.
It sounds as though you read the first paragraph, but not the second,
which states:
Note: The syntax of a CASE statement shown here for use inside a stored
procedure differs slightly from that of the SQL CASE expression described in
Section 12.2, "Control Flow Functions". The CASE statement cannot have an
ELSE NULL clause, and it is terminated with END CASE instead of END.
You're using a CASE expression, not a CASE statement.

Homam

--- Tom Crimmins <[EMAIL PROTECTED]> wrote:
 On Wednesday, March 09, 2005 07:49, Philippe
 Poelvoorde wrote:
 > Daniel Kasak wrote:
 >> Homam S.A. wrote:
 >>
 >>> In the documentation, it doesn't mention which
 version
 >>> of MySQL supports the CASE statement, but it
 refers to
 >>> stored procedures, so is it only supported for
 5.x?
 >>>
 >>> I can't get any example of a CASE statement work
 in MySQL.
 >>>
 >>>
 >>>
 >> 
 >>
 >>> For example, I can execute the following in MS
 SQL
 >>> Server:
 >>>
 >>> UPDATE MyTable
 >>> SET
 >>> field1 = CASE WHEN field3 = 1 THEN 10 ELSE 20
 END,
 >>> field2 = field2 | CASE WHEN field4 = 'B' THEN 1
 ELSE 0
 >>> END | CASE WHEN field4 = 'C' THEN 2 ELSE 0 END
 What error do you get? I can get a statement similar
 to this to work.
 >> Case works in 4.0.x ( and maybe before, haven't
 checked ).
 >> Your problem is elsewhere.
 >> I'm not sure if you can use the pipe symbol as an
 OR operator in the
 >> way you want - to be honest I haven't used it at
 all in SQL.
 >> Try rewriting it to use nothing but case
 statements ( no | operators
 >> ).
 >>
 >
 >
http://dev.mysql.com/doc/mysql/en/logical-operators.html
 >
 > it's || not | (not sure about bit-operation...)
 The | is a bitwise OR.
 http://dev.mysql.com/doc/mysql/en/bit-functions.html
 --
 Tom Crimmins
 Interface Specialist
 Pottawattamie County, Iowa
 --
 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]

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

PHP/MyphpAdmin/MySql

[Kamal Ahmed]

Dear MySql List experts,

I have the following installed on redhat 9:

[EMAIL PROTECTED] html]#php -v
PHP 4.3.10 (cgi) (built: Mar  8 2005 17:52:13)
Copyright (c) 1997-2004 The PHP Group
Zend Engine v1.3.0, Copyright (c) 1998-2004 Zend Technologies
-
[EMAIL PROTECTED] html]# rpm -q mysql-server
mysql-server-3.23.58-1.9
[EMAIL PROTECTED] html]# 
-
[EMAIL PROTECTED] html]#rpm -q httpd
httpd-2.0.40-21.5
---
[EMAIL PROTECTED] html]#rpm -q php
php-4.2.2-17
-

, but when trying to setup MyPhpAdmin, I get an error:

cannot load mysql extension,
please check PHP Configuration
Documentation


How can I resolve this ?

Thanks,

-Kamal.

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

Optimizing a big query...

[Carlos Savoretti]

Hi all:

I programming a GUI which retrieve big tables oftenly.

So, I retrieve chunks of 1000 rows and paginate then

to browse the entire table. It works fine, but it's rather

slow.

I would like to know if I could set some option thru

mysql_option() to optimize the client side (mysql-client-3.23.58)

and what is the the recommended value to clamp the `page' for a

gui app. (For 1000 rows it uses about 12 seconds).

Thanks a lot...

-- 
Carlos Savoretti <[EMAIL PROTECTED]>


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

Re: PHP/MyphpAdmin/MySql

[Peter J Milanese]

There is likely an rpm package for the php mysql module. Install that, or
compile php with mysql support.

P
--Original Message--
From: Kamal Ahmed
To: mysql
Cc: Kamal Ahmed
Sent: Mar 9, 2005 3:58 PM
Subject: PHP/MyphpAdmin/MySql

Dear MySql List experts,

I have the following installed on redhat 9:

[EMAIL PROTECTED] html]#php -v
PHP 4.3.10 (cgi) (built: Mar  8 2005 17:52:13)
Copyright (c) 1997-2004 The PHP Group
Zend Engine v1.3.0, Copyright (c) 1998-2004 Zend Technologies
-
[EMAIL PROTECTED] html]# rpm -q mysql-server
mysql-server-3.23.58-1.9
[EMAIL PROTECTED] html]#
-
[EMAIL PROTECTED] html]#rpm -q httpd
httpd-2.0.40-21.5
---
[EMAIL PROTECTED] html]#rpm -q php
php-4.2.2-17
-

, but when trying to setup MyPhpAdmin, I get an error:

cannot load mysql extension,
please check PHP Configuration
Documentation


How can I resolve this ?

Thanks,

-Kamal.

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


-
Sent from my NYPL BlackBerry Handheld.




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

Re: Optimizing a big query...

[Peter J Milanese]

Does the app display all 1000 rows at once? Does your app require all
fields? Only retrieve what you need for the page. If the app displays all
1000 rows, it may remain slow depending on how you get them (order, group,
function) and indexing.. Also, the link and disk  may matter depending on
the size of the row. I do not know what you could do on the client side to
help.


--Original Message--
From: Carlos Savoretti
To: MySQL List
Sent: Mar 9, 2005 5:49 PM
Subject: Optimizing a big query...

Hi all:

I programming a GUI which retrieve big tables oftenly.

So, I retrieve chunks of 1000 rows and paginate then

to browse the entire table. It works fine, but it's rather

slow.

I would like to know if I could set some option thru

mysql_option() to optimize the client side (mysql-client-3.23.58)

and what is the the recommended value to clamp the `page' for a

gui app. (For 1000 rows it uses about 12 seconds).

Thanks a lot...

--
Carlos Savoretti <[EMAIL PROTECTED]>


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


-
Sent from my NYPL BlackBerry Handheld.




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

Re: slowly running mysql server

[James Nobis]

Run an explain on the query and post it here.  Make sure all the join columns
are appropriately indexed.  Also, notice the large difference in rows examined
vs sent (My guess is this is due to the left joins.)  Left joins are horribly
overused and I know you said that you cannot change the query but try to ask
the developers if they really need more than an inner join.

Quoting iv <[EMAIL PROTECTED]>:

> Hi
> I have a big problem with my mysq server. ater installing it in a new
> machine it works extremally slowly.
> My mysql server serves a quite big web site with numerous queries.
> Unfortunately, I can't alter the queries.
> And one more thing. Everything run much better on a previous machine.
> What I have now:
>   AMD Sempron 3100+, Asus K8N-E, Western Digital WDC-WD800JD SATA drive;
> MemTotal: 1553596 kB
>   I'm running now Debian GNU/Linux 3.0, kernel 2.6.10, mysql  Ver 14.7
> Distrib 4.1.9, for pc-linux-gnu;
>   My.cfg:
>
> [client]
> port= 3306
> socket  = /var/run/mysqld/mysqld.sock
> [mysqld_safe]
> socket  = /var/run/mysqld/mysqld.sock
> nice= 0
> [mysqld]
> user= mysql
> pid-file= /var/run/mysqld/mysqld.pid
> socket  = /var/run/mysqld/mysqld.sock
> port= 3306
> log = /var/log/mysql/mysql.log
> basedir = /usr
> datadir = /var/lib/mysql
> tmpdir  = /tmp
> language= /usr/share/mysql/polish
> skip-external-locking
> default-character-set = latin2
> old-passwords   = 1
> bind-address= 127.0.0.1
> key_buffer  = 16M
> max_allowed_packet  = 16M
> thread_stack= 128K
> query_cache_limit= 1258292
> query_cache_size = 26214400
> query_cache_type = 1
> query_cache_min_res_unit = 8000
> log-slow-queries= /var/log/mysql/mysql-slow.log
> [mysqldump]
> quick
> quote-names
> max_allowed_packet  = 1M
> [mysql]
> [isamchk]
> key_buffer  = 16M
> **
> the query from /var/log/mysql/mysql-slow.log:
> # Time: 050309 17:02:37
> # [EMAIL PROTECTED]: wwwuser[wwwuser] @ localhost []
> # Query_time: 254  Lock_time: 0  Rows_sent: 185  Rows_examined: 1956616
> SET timestamp=1110384157;
> SELECT pojazdy.*, marki.marka, IF(marki.marka is NULL,'???',marki.marka)
> as sort , zdjecia.nazwa as zdjecie_na
> zwa, zdjecia.katalog as zdjecie_katalog, zdjecia.typ as zdjecie_typ FROM
> pojazdy LEFT JOIN marki ON ( pojazdy.
> marka_id=marki.id ) LEFT JOIN zdjecia ON ( pojazdy.id =
> zdjecia.pojazd_id )  ,firmy WHERE 1 and pojazdy.open_d
> ate < NOW() and pojazdy.validate > NOW() and pojazdy.block_date=0 and
> pojazdy.kontakt_id=firmy.id and  firmy.n
> azwa='carstein' and pojazdy.typ_id='1' GROUP BY pojazdy.id order by
> sort, model, cena;
> # Time: 050309 17:03:10
>
> additionally, this query returns 2216 rows with pictures. the process
> longs about 2 minutes :(
> and additionally, I'm a newbie with mysql.
> Any ideas will be highly appreciated.
> Thanks in advance,
> /iv
> ***
> *
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]
>


James Nobis
Web Developer
Academic Superstore
223 W. Anderson Ln. Suite A110, Austin, TX 78752
Voice: (512) 450-1199 x453 Fax: (512) 450-0263
http://www.academicsuperstore.com

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

Re: Optimizing a big query...

[Scott Klarenbach]

That's incredibly slow.  I pull 1000 records through PHP in 1 second
on a P4 2.4 Ghz.  Are you pulling the entire recordset and then
limiting it in your app code?  Or are you using a limit clause in the
DB?

Also, Peter's point about indexing might help.  Without specific SQL
examples, it's difficult to suggest optimizations.  Mysql does have a
great section in their manual re: optimizing select queries.

Scott.


On Wed, 9 Mar 2005 16:10:19 -0500, Peter J Milanese <[EMAIL PROTECTED]> wrote:
> Does the app display all 1000 rows at once? Does your app require all
> fields? Only retrieve what you need for the page. If the app displays all
> 1000 rows, it may remain slow depending on how you get them (order, group,
> function) and indexing.. Also, the link and disk  may matter depending on
> the size of the row. I do not know what you could do on the client side to
> help.
> 
> --Original Message--
> From: Carlos Savoretti
> To: MySQL List
> Sent: Mar 9, 2005 5:49 PM
> Subject: Optimizing a big query...
> 
> Hi all:
> 
> I programming a GUI which retrieve big tables oftenly.
> 
> So, I retrieve chunks of 1000 rows and paginate then
> 
> to browse the entire table. It works fine, but it's rather
> 
> slow.
> 
> I would like to know if I could set some option thru
> 
> mysql_option() to optimize the client side (mysql-client-3.23.58)
> 
> and what is the the recommended value to clamp the `page' for a
> 
> gui app. (For 1000 rows it uses about 12 seconds).
> 
> Thanks a lot...
> 
> --
> Carlos Savoretti <[EMAIL PROTECTED]>
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
> 
> -
> Sent from my NYPL BlackBerry Handheld.
> 
> --
> 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: Optimizing a big query...

[mos]

Carlos,
Apart from using the proper indexes, you should only retrieve the 
number of rows that are going to be displayed on the page at one time. If 
you are displaying 10 rows on the page, why retrieve 1000 rows? The user 
would have to page through it 100x and you have to ask yourself, is this 
likely going to happen for every user? If not, then you are wasting CPU cycles.

It is better to use "Select ... 10,10" which is the offset, limit. 
Keep increasing the offset (starting at 0) for each time the user presses 
"Next page". MySQL will then retrieve only 10 rows at a time. This is 100x 
faster than retrieving all 1000 rows at a time.

Mike
At 04:49 PM 3/9/2005, Carlos Savoretti wrote:
Hi all:
I programming a GUI which retrieve big tables oftenly.
So, I retrieve chunks of 1000 rows and paginate then
to browse the entire table. It works fine, but it's rather
slow.
I would like to know if I could set some option thru
mysql_option() to optimize the client side (mysql-client-3.23.58)
and what is the the recommended value to clamp the `page' for a
gui app. (For 1000 rows it uses about 12 seconds).
Thanks a lot...
--
Carlos Savoretti <[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: skip-name-resolve

[Mauricio Pellegrini]

Well, we allready use ip numbers instead of host names in the grant
tables, but after setting the skip-name-resolve startup option
machines with Windows ( any flavor) are still slow when internet
connection goes down.

Still, I cant tell if the startup parameter (skip-name-resolve) has been
accepted by  mysqld

So I repost my question
Is there a sure way to check if skip-name-resolve is in efect ?

Thanks 
Mauricio


On Wed, 2005-03-09 at 14:20, Keith Ivey wrote:
> Mauricio Pellegrini wrote:
> 
> > Is there a way to check whether this option is active or not, while the
> > server is running?
> 
> Not sure why it doesn't show up in "SHOW VARIABLES", but one way 
> to check would be to see whether the hosts in the "Host" column 
> of "SHOW PROCESSLIST" are shown as IP addresses or hostnames.
> 
> -- 
> Keith Ivey <[EMAIL PROTECTED]>
> Smokefree DC
> http://www.smokefreedc.org
> Washington, DC


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

Re: Problem with a repeated select from MS Access

[Daniel Kasak]

Osvaldo Sommer wrote:
This is the code:
Public Sub Graba_Recibido(texto As String)
277On Error GoTo Manejo_Error
278Dim db As Database
279Dim rec As DAO.Recordset
281texto = Mid(texto, 2, 2) & Mid(texto, 7, Len(texto) - 7)
282texto = Mid(texto, 1, Len(texto) - 1)
284Set db = CurrentDb
285Set rec = db.OpenRecordset("select * from BALANZA_RECEPCION where
1=0", dbOpenDynaset, dbSeeChanges, dbOptimistic) ' abro la tabla
286rec.AddNew  ' Agrego uno nuevo
287rec!rc_data = texto
288rec!rc_fecha = CDbl(Date)
289rec!rc_procesado = "N"
290rec.Update  ' Actualizo
información
291rec.Close   ' cierro cursor
292Set db = Nothing
296Exit Sub
297 Manejo_Error:
298Set db = Nothing
299Procesa_Error "DibalTimer - Funciones Locales - Graba_Recibido",
True
End Sub
 

If you're doing this rapidly, I would take another approach completely. 
I haven't tested this, but my 'hunch' is that adding records via a 
DAO.Recordset.AddNew method would be the slowest and most error prone 
way of doing it. My preference would be use use ADO, and have a global 
connection object, and probably a global command object, and keep 
reusing the same objects. This is for speed and stability. I'm not 100% 
sure what is causing your problem, but I'm almost certain that the below 
method will work around it.

To start with, when the Access database opens, open an ADO connection 
object and an ADO command object:

---
Sub on_database_startup
Global mysql_conn as ADODB.Connection
Global mysql_command as ADODB.Command
Set mysql_conn = New ADODB.Connection
With mysql_conn
.ConnectionString = "Driver={MySQL ODBC 3.51 
driver};Server=192.168.0.1;DATABASE=my_database_name;UID=some_user_name;PWD=some_password"
.Open
End With

Set mysql_command = New ADODB.Command
With mysql_command
.ActiveConnection = mysql_conn
.CommandType = acCommandTxt
End With
End Sub
---
Note that the connection string should be on one line.
Now that you have both the above objects loaded in memory and connected 
to MySQL, your code that inserts the data would simply be:

---
Public Sub Graba_Recibido(texto As String)
On Error GoTo Manejo_Error
texto = Mid(texto, 2, 2) & Mid(texto, 7, Len(texto) - 7)
texto = Mid(texto, 1, Len(texto) - 1)
With mysql_command
.CommandText = "insert into BALANZA_RECEPCION ( rc_data, rc_fecha, 
rc_procesado )" _
 & " values ( " '" & texto & "', '" & Format(Date, "-mm-dd") & "', 
'N' )"
.Execute
End With

End Sub
---
You could also use MySQL's curdate() function instead of using the 
client Date() function:

---
With mysql_command
.CommandText = "insert into BALANZA_RECEPCION ( rc_data, rc_fecha, 
rc_procesado )" _
 & " values ( " '" & texto & "', curdate(), 'N' )"
.Execute
End With

---
You also have to watch that you don't have text with quotes in the 
string texto - if this is possible, you'll have to escape them before 
sending the SQL to MySQL.

It's possible that you can do the above with DAO - I haven't used DAO - 
I jumped straight into ADO, and it works very well for me. The idea with 
DAO would be the same - create your objects at the start and reuse them.

I've got a small section on my website about using Access with MySQL. 
Have a look at http://entropy.homelinux.org/MySQL/ and specifically 
http://entropy.homelinux.org/MySQL/optimizing.html#ado

And this is the error report i got
-*-*-*-*-*-*-*-*-*-*-*-*

-*
Fecha: 02/03/2005 Hora: 11:43:22 AM
Error en DibalTimer - Funciones Locales - Graba_Recibido:
Error #3151 (Línea: 285) (Source: DAO.Database) ODBC--connection to
'{MySQL ODBC 3.51 Driver}dellserver' failed.
 

You may find out more info on what's causing your problems by checking 
the mysql logs. For example, is Access taking up the maximum number of 
connections ( opening connections and not closing them )?

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

Query_cache_size Question

[Mauricio Pellegrini]

Hi, I'm trying to tweak the execution time for querys on my mysql
server which is using InnoDB.

I know that there are a lot of things to check but one of them, perhaps
not the most important, is the query_cache_size.

I have a dual Xeon cpu with 4gb of ram and this is a Linux dedicated
server which runs only Apache and Mysql 4.1.5 

I've declared Query_cache_size = 16M in the my.cnf file 
and I would say that in the whole we have 60% of select querys and a 
40% of update querys.

Is Query_cache_size=16M too conservative considering that I have 4Gb of
ram ?

If so what would be a more efective value?

As I've said before the engine is InnoDB.

Thanks 
Mauricio




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

RE: Query_cache_size Question

[Kostas Pyliouras]

Depends on the result sets you want to cache. Check the other variables in the 
manual, you can analyze if all the query cache is utilized at a point in time. 
In general 16M is low when you have 4GB, you can safely increase it.

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Mittwoch, 9. März 2005 22:03
To: MySql List
Subject: Query_cache_size Question

Hi, I'm trying to tweak the execution time for querys on my mysql
server which is using InnoDB.

I know that there are a lot of things to check but one of them, perhaps
not the most important, is the query_cache_size.

I have a dual Xeon cpu with 4gb of ram and this is a Linux dedicated
server which runs only Apache and Mysql 4.1.5 

I've declared Query_cache_size = 16M in the my.cnf file 
and I would say that in the whole we have 60% of select querys and a 
40% of update querys.

Is Query_cache_size=16M too conservative considering that I have 4Gb of
ram ?

If so what would be a more efective value?

As I've said before the engine is InnoDB.

Thanks 
Mauricio




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


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

Re: Query_cache_size Question

[Dan Nelson]

In the last episode (Mar 09), Mauricio Pellegrini said:
> Hi, I'm trying to tweak the execution time for querys on my mysql
> server which is using InnoDB.
> 
> I know that there are a lot of things to check but one of them,
> perhaps not the most important, is the query_cache_size.
> 
> I have a dual Xeon cpu with 4gb of ram and this is a Linux dedicated
> server which runs only Apache and Mysql 4.1.5
> 
> I've declared Query_cache_size = 16M in the my.cnf file and I would
> say that in the whole we have 60% of select querys and a 40% of
> update querys.
> 
> Is Query_cache_size=16M too conservative considering that I have 4Gb
> of ram ?

A better question is: what is your current utilization of that 16MB
cache?  The query cache flushes any results dependant on a table when
that table is modified, so if you really have 40% updates, chances are
that very few queries are cachable at all.  Run

  show status like 'qcache%';

, and take a look at free_memory, inserts, and hits.
 
-- 
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: Query_cache_size Question

[Mauricio Pellegrini]

Thanks,  this the result of show status like "qcache%"

+-+--+
| Variable_name   | Value|
+-+--+
| Qcache_free_blocks  | 3330 |
| Qcache_free_memory  | 13372320 |
| Qcache_hits | 9149 |
| Qcache_inserts  | 1942009  |
| Qcache_lowmem_prunes| 372898   |
| Qcache_not_cached   | 508528   |
| Qcache_queries_in_cache | 3160 |
| Qcache_total_blocks | 9711 |
+-+--+

Am I right at saying that the value (16Mb)is ok ?

Qcache_not_cached means the number of query results that didn't get into
the cache because of a space limitation ? 
If so, perhaps adding few more Mb would improve that number , isn't it?




On Wed, 2005-03-09 at 19:30, Dan Nelson wrote:
> In the last episode (Mar 09), Mauricio Pellegrini said:
> > Hi, I'm trying to tweak the execution time for querys on my mysql
> > server which is using InnoDB.
> > 
> > I know that there are a lot of things to check but one of them,
> > perhaps not the most important, is the query_cache_size.
> > 
> > I have a dual Xeon cpu with 4gb of ram and this is a Linux dedicated
> > server which runs only Apache and Mysql 4.1.5
> > 
> > I've declared Query_cache_size = 16M in the my.cnf file and I would
> > say that in the whole we have 60% of select querys and a 40% of
> > update querys.
> > 
> > Is Query_cache_size=16M too conservative considering that I have 4Gb
> > of ram ?
> 
> A better question is: what is your current utilization of that 16MB
> cache?  The query cache flushes any results dependant on a table when
> that table is modified, so if you really have 40% updates, chances are
> that very few queries are cachable at all.  Run
> 
>   show status like 'qcache%';
> 
> , and take a look at free_memory, inserts, and hits.
>  
> -- 
>   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: Query_cache_size Question

[Jocelyn Fournier]

Hi,
How many questions for thoses results ?
There's a lot of lowmem_prunes, so I would indeed increase the memory 
size to reduce the risk of lowmem_prunes.
What is your query_cache_limit ?

  Jocelyn
Mauricio Pellegrini a écrit :
Thanks,  this the result of show status like "qcache%"
+-+--+
| Variable_name   | Value|
+-+--+
| Qcache_free_blocks  | 3330 |
| Qcache_free_memory  | 13372320 |
| Qcache_hits | 9149 |
| Qcache_inserts  | 1942009  |
| Qcache_lowmem_prunes| 372898   |
| Qcache_not_cached   | 508528   |
| Qcache_queries_in_cache | 3160 |
| Qcache_total_blocks | 9711 |
+-+--+
Am I right at saying that the value (16Mb)is ok ?
Qcache_not_cached means the number of query results that didn't get into
the cache because of a space limitation ? 
If so, perhaps adding few more Mb would improve that number , isn't it?


On Wed, 2005-03-09 at 19:30, Dan Nelson wrote:
In the last episode (Mar 09), Mauricio Pellegrini said:
Hi, I'm trying to tweak the execution time for querys on my mysql
server which is using InnoDB.
I know that there are a lot of things to check but one of them,
perhaps not the most important, is the query_cache_size.
I have a dual Xeon cpu with 4gb of ram and this is a Linux dedicated
server which runs only Apache and Mysql 4.1.5
I've declared Query_cache_size = 16M in the my.cnf file and I would
say that in the whole we have 60% of select querys and a 40% of
update querys.
Is Query_cache_size=16M too conservative considering that I have 4Gb
of ram ?
A better question is: what is your current utilization of that 16MB
cache?  The query cache flushes any results dependant on a table when
that table is modified, so if you really have 40% updates, chances are
that very few queries are cachable at all.  Run
 show status like 'qcache%';
, and take a look at free_memory, inserts, and hits.
--
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: Query_cache_size Question

[Dan Nelson]

In the last episode (Mar 09), Mauricio Pellegrini said:
> Thanks,  this the result of show status like "qcache%"
> 
> +-+--+
> | Variable_name   | Value|
> +-+--+
> | Qcache_free_blocks  | 3330 |
> | Qcache_free_memory  | 13372320 |
> | Qcache_hits | 9149 |
> | Qcache_inserts  | 1942009  |
> | Qcache_lowmem_prunes| 372898   |
> | Qcache_not_cached   | 508528   |
> | Qcache_queries_in_cache | 3160 |
> | Qcache_total_blocks | 9711 |
> +-+--+
> 
> Am I right at saying that the value (16Mb)is ok ?
> 
> Qcache_not_cached means the number of query results that didn't get
> into the cache because of a space limitation ?  If so, perhaps adding
> few more Mb would improve that number , isn't it?

Yes; qcache_lowmem_prunes is a good indicator that raising
query_cache_size may help.  It counts the number if times the cache had
to be aged to free up space to cache a new resultset.  See the
documentation at http://dev.mysql.com/doc/mysql/en/query-cache.html for
more tuning ideas.

-- 
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: Query_cache_size Question

[Mauricio Pellegrini]

Thanks these are fresh results

| query_cache_limit| 1048576  |
| query_cache_min_res_unit | 4096 |


show  status like "ques%";
+---+--+
| Variable_name | Value|
+---+--+
| Questions | 14189604 |
+---+--+


show  status like "qcache%";
+-+--+
| Variable_name   | Value|
+-+--+
| Qcache_free_blocks  | 2731 |
| Qcache_free_memory  | 12372064 |
| Qcache_hits | 11156553 |
| Qcache_inserts  | 1945643  |
| Qcache_lowmem_prunes| 372898   |
| Qcache_not_cached   | 509594   |
| Qcache_queries_in_cache | 4101 |
| Qcache_total_blocks | 11000|
+-+--+


On Wed, 2005-03-09 at 20:19, Jocelyn Fournier wrote:
> Hi,
> 
> How many questions for thoses results ?
> There's a lot of lowmem_prunes, so I would indeed increase the memory 
> size to reduce the risk of lowmem_prunes.
> What is your query_cache_limit ?
> 
>Jocelyn
> 
> Mauricio Pellegrini a écrit :
> > Thanks,  this the result of show status like "qcache%"
> > 
> > +-+--+
> > | Variable_name   | Value|
> > +-+--+
> > | Qcache_free_blocks  | 3330 |
> > | Qcache_free_memory  | 13372320 |
> > | Qcache_hits | 9149 |
> > | Qcache_inserts  | 1942009  |
> > | Qcache_lowmem_prunes| 372898   |
> > | Qcache_not_cached   | 508528   |
> > | Qcache_queries_in_cache | 3160 |
> > | Qcache_total_blocks | 9711 |
> > +-+--+
> > 
> > Am I right at saying that the value (16Mb)is ok ?
> > 
> > Qcache_not_cached means the number of query results that didn't get into
> > the cache because of a space limitation ? 
> > If so, perhaps adding few more Mb would improve that number , isn't it?
> > 
> > 
> > 
> > 
> > On Wed, 2005-03-09 at 19:30, Dan Nelson wrote:
> > 
> >>In the last episode (Mar 09), Mauricio Pellegrini said:
> >>
> >>>Hi, I'm trying to tweak the execution time for querys on my mysql
> >>>server which is using InnoDB.
> >>>
> >>>I know that there are a lot of things to check but one of them,
> >>>perhaps not the most important, is the query_cache_size.
> >>>
> >>>I have a dual Xeon cpu with 4gb of ram and this is a Linux dedicated
> >>>server which runs only Apache and Mysql 4.1.5
> >>>
> >>>I've declared Query_cache_size = 16M in the my.cnf file and I would
> >>>say that in the whole we have 60% of select querys and a 40% of
> >>>update querys.
> >>>
> >>>Is Query_cache_size=16M too conservative considering that I have 4Gb
> >>>of ram ?
> >>
> >>A better question is: what is your current utilization of that 16MB
> >>cache?  The query cache flushes any results dependant on a table when
> >>that table is modified, so if you really have 40% updates, chances are
> >>that very few queries are cachable at all.  Run
> >>
> >>  show status like 'qcache%';
> >>
> >>, and take a look at free_memory, inserts, and hits.
> >> 
> >>-- 
> >>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: Query_cache_size Question

[Jocelyn Fournier]

Even with only 16 Mo, your query cache is quite efficient (78% of your 
queries are fetched directly from the cache).
Try to increaze your query_cache_size until there's no more additional 
lowmem_prunes reported.
And executed from time to time FLUSH QUERY CACHE to defragment your 
query cache.

  Jocelyn
Mauricio Pellegrini a écrit :
Thanks these are fresh results
| query_cache_limit| 1048576  |
| query_cache_min_res_unit | 4096 |
show  status like "ques%";
+---+--+
| Variable_name | Value|
+---+--+
| Questions | 14189604 |
+---+--+
show  status like "qcache%";
+-+--+
| Variable_name   | Value|
+-+--+
| Qcache_free_blocks  | 2731 |
| Qcache_free_memory  | 12372064 |
| Qcache_hits | 11156553 |
| Qcache_inserts  | 1945643  |
| Qcache_lowmem_prunes| 372898   |
| Qcache_not_cached   | 509594   |
| Qcache_queries_in_cache | 4101 |
| Qcache_total_blocks | 11000|
+-+--+
On Wed, 2005-03-09 at 20:19, Jocelyn Fournier wrote:
Hi,
How many questions for thoses results ?
There's a lot of lowmem_prunes, so I would indeed increase the memory 
size to reduce the risk of lowmem_prunes.
What is your query_cache_limit ?

  Jocelyn
Mauricio Pellegrini a écrit :
Thanks,  this the result of show status like "qcache%"
+-+--+
| Variable_name   | Value|
+-+--+
| Qcache_free_blocks  | 3330 |
| Qcache_free_memory  | 13372320 |
| Qcache_hits | 9149 |
| Qcache_inserts  | 1942009  |
| Qcache_lowmem_prunes| 372898   |
| Qcache_not_cached   | 508528   |
| Qcache_queries_in_cache | 3160 |
| Qcache_total_blocks | 9711 |
+-+--+
Am I right at saying that the value (16Mb)is ok ?
Qcache_not_cached means the number of query results that didn't get into
the cache because of a space limitation ? 
If so, perhaps adding few more Mb would improve that number , isn't it?


On Wed, 2005-03-09 at 19:30, Dan Nelson wrote:

In the last episode (Mar 09), Mauricio Pellegrini said:

Hi, I'm trying to tweak the execution time for querys on my mysql
server which is using InnoDB.
I know that there are a lot of things to check but one of them,
perhaps not the most important, is the query_cache_size.
I have a dual Xeon cpu with 4gb of ram and this is a Linux dedicated
server which runs only Apache and Mysql 4.1.5
I've declared Query_cache_size = 16M in the my.cnf file and I would
say that in the whole we have 60% of select querys and a 40% of
update querys.
Is Query_cache_size=16M too conservative considering that I have 4Gb
of ram ?
A better question is: what is your current utilization of that 16MB
cache?  The query cache flushes any results dependant on a table when
that table is modified, so if you really have 40% updates, chances are
that very few queries are cachable at all.  Run
show status like 'qcache%';
, and take a look at free_memory, inserts, and hits.
--
Dan Nelson
[EMAIL PROTECTED]



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

Forcing session variable definition

[Homam S.A.]

I just spent quite a while hunting out a bug that
turned out to be a mis-spelled session variable
defaulting to NULL.

Ultimately what I want is, if I used a session
veriable without defining it before hand with a SET,
MySQL would return an error.

Such problem doesn't exist in T-SQL, for example,
because you have to define the variables with the
DECLARE clause before you use them.

In addition, some dynamically-typed scripting
langauges use a directive to enforce this policy, such
as OPTION EXPLICIT in VBScript.

Is there an equivalent option or directive in MySQL?

Thanks,

Homam



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

Forcing session variable definition (correction)

[Homam S.A.]

I'm sorry, I meant user variables, not session
variables.



--- "Homam S.A." <[EMAIL PROTECTED]> wrote:
> I just spent quite a while hunting out a bug that
> turned out to be a mis-spelled session variable
> defaulting to NULL.
> 
> Ultimately what I want is, if I used a session
> veriable without defining it before hand with a SET,
> MySQL would return an error.
> 
> Such problem doesn't exist in T-SQL, for example,
> because you have to define the variables with the
> DECLARE clause before you use them.
> 
> In addition, some dynamically-typed scripting
> langauges use a directive to enforce this policy,
> such
> as OPTION EXPLICIT in VBScript.
> 
> Is there an equivalent option or directive in MySQL?
> 
> Thanks,
> 
> Homam
> 
> 
> 
> -- 
> 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: Optimizing a big query...

[Homam S.A.]

If your tables are mostly read-only, you could
pre-generate page numbers on a periodic basis and
select only specific ranges WHERE row_number BETWEEN
page_start AND page_finish.

Or you could just send the top 1000 IDs of the table
to the client, and have the client figure out which
IDs belong to which page, and then send these IDs in
another query to rertieve the full data for a single
page at a time. You should also take advantage of the
SQL cache to make the successive page requests snappy.

Homam



--- Carlos Savoretti <[EMAIL PROTECTED]>
wrote:
> Hi all:
> 
> I programming a GUI which retrieve big tables
> oftenly.
> 
> So, I retrieve chunks of 1000 rows and paginate then
> 
> to browse the entire table. It works fine, but it's
> rather
> 
> slow.
> 
> I would like to know if I could set some option thru
> 
> mysql_option() to optimize the client side
> (mysql-client-3.23.58)
> 
> and what is the the recommended value to clamp the
> `page' for a
> 
> gui app. (For 1000 rows it uses about 12 seconds).
> 
> Thanks a lot...
> 
> -- 
> Carlos Savoretti <[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: Optimizing a big query...

[Homam S.A.]

Unfortunately this doesn't work well if you want to
tell your users how many pages were found in the
query. Sure, you could use SQL_CALC_FOUND_ROWS with
FOUND_ROWS(), but this will defeate the purpose of the
LIMIT clause -- speed -- because MySQL will have to
figure out all the results of the query. Worse, MySQL
will not be able to cache the query because you're
changing the query string with each paging requsts.




--- mos <[EMAIL PROTECTED]> wrote:
> Carlos,
>  Apart from using the proper indexes, you
> should only retrieve the 
> number of rows that are going to be displayed on the
> page at one time. If 
> you are displaying 10 rows on the page, why retrieve
> 1000 rows? The user 
> would have to page through it 100x and you have to
> ask yourself, is this 
> likely going to happen for every user? If not, then
> you are wasting CPU cycles.
> 
>  It is better to use "Select ... 10,10"
> which is the offset, limit. 
> Keep increasing the offset (starting at 0) for each
> time the user presses 
> "Next page". MySQL will then retrieve only 10 rows
> at a time. This is 100x 
> faster than retrieving all 1000 rows at a time.
> 
> Mike
> 
> At 04:49 PM 3/9/2005, Carlos Savoretti wrote:
> >Hi all:
> >
> >I programming a GUI which retrieve big tables
> oftenly.
> >
> >So, I retrieve chunks of 1000 rows and paginate
> then
> >
> >to browse the entire table. It works fine, but it's
> rather
> >
> >slow.
> >
> >I would like to know if I could set some option
> thru
> >
> >mysql_option() to optimize the client side
> (mysql-client-3.23.58)
> >
> >and what is the the recommended value to clamp the
> `page' for a
> >
> >gui app. (For 1000 rows it uses about 12 seconds).
> >
> >Thanks a lot...
> >
> >--
> >Carlos Savoretti <[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.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Help massage date import

[Scott Haneda]

Mysql 4, I have datetime field set of -00-00 00:00:00

Sampling of data I need to insert in is as follow:
12/26/04 13:35
12/25/04 12:41
12/25/04 8:53

How do a massage that to the proper format YYY-MM-DD HH:MM:SS

thanks
-- 
-
Scott HanedaTel: 415.898.2602
 Fax: 313.557.5052
<[EMAIL PROTECTED]>  Novato, CA U.S.A.



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

ANN: Gtk2::Ex::DBI-0.8

[Daniel Kasak]

I'm pleased to annouce the 8th release of Gtk2::Ex::DBI ...
http://entropy.homelinux.org/Gtk2-Ex-DBI/
Gtk2::Ex::DBI is an open-source helper object that makes your Gtk2-Perl
apps data aware. It handles querying, 'painting' records on your
Glade-generated form, passing updates back to the database server,
inserting, deleting, and much more. Roll your own MS Access.
From the changelog:
- INCOMPATIBLE CHANGE: don't call on_current while we're constructing 
ourself - external code won't be able to 'see' us yet

- Added constructor_done flag to indicate whether we have been returned 
to the calling code yet
You should manually call the on_current code immediately *after* the 
object constructor ... when your Gtk2::Ex::DBI object will be available

- Added method of running external code before applying changes to the 
current record ( on_apply - similar to on_current )

- Apply changes to the current record before requerying ( query method )
- Properly handle empty recordsets in count method
- Append primary key to sql_select if necessary in fetch_new_slice method
- Added 'quiet' flag to silence warnings such as missing widgets
- Added 'use strict' directive and fixed up issues this raised
- Added demo application - a dream database ( distributed separately - 
see download page on website )

- Use 'make dist' to create distributable package instead of manually 
creating archive - thanks Ofey

- Updated website to make better use of space - also thanks to Ofey for 
suggestion to clean it up

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

Re: Mysql 4.1 and the LIMIT sql statement

[Dennis Fogg]

On Nov 17, 2004, Matt Babineau & Dan Nelson discussed:

> > Has anyone run into problems with this sql syntax?
> > 
> > LIMIT -1
> > 
> > I've used this extensively in my code to get back all records rather then
> > specifing a limit. I've done this programmatically with PHP, so all my
> > queries have a limit even if I don't need one, I just have it specify LIMIT
> > -1, but apparently this functionality doesn't seem to work in 4.1???
> 
> From http://dev.mysql.com/doc/mysql/en/Upgrading-from-4.0.html :
> 
>  # LIMIT no longer accepts negative arguments. Use some large number
>(maximum 18446744073709551615) instead of -1.
> 
> not sure why it was changed, though.
> 

This is going to break a fair number of phpMyEdit scripts because
phpMyEdit's default template recommends $opts['inc'] being set to
"Value of -1 lists all records in a table"
which gets converted to a SQL statement using LIMIT 0,-1

This does not make for a smooth migration from MySQL 4.0 to 4.1
and the solution suggested in the documentation is a step backwards
in elegance (IMHO).

Dennis Fogg

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

Updating my address book

[Kris Briscoe]

Hi 

Can you please enter your contact details in my address book. Click on the link 
below: 

http://www.bebo.com/fr1/10232728a484122677b143804761c521525170d20 

After we are connected, in the future, any changes you make in your contact 
details will be sent to me. 

Thanks for your help. 

Kris


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

Re: Optimizing a big query...

[mos]

At 07:19 PM 3/9/2005, you wrote:
Unfortunately this doesn't work well if you want to
tell your users how many pages were found in the
query.
Correct, but the speed difference will more than make up for it. If you 
have a busy website, it is very important not to overtax the database 
server with frills, like the number of rows found. If you want to have the 
record counts, then you will have to realize it may slow down your web 
server by a factor of 5 or 10 compared to using Limits. If you think it is 
worth it, then by all means include it. Or you could have a button on the 
form so if the user really want an accurate count, he presses the button.

 Sure, you could use SQL_CALC_FOUND_ROWS with
FOUND_ROWS(), but this will defeate the purpose of the
LIMIT clause -- speed -- because MySQL will have to
figure out all the results of the query.
You would have to execute a second query to count the rows if you want a 
row count and use a Limit clause. This should be much faster because you 
are not returning the rows from the server, you're just counting them.

You also have to consider the hacker or disgruntled employee that will 
deliberately try to crash the server by repeating large queries. A Limit 
clause prevents that from happening.

Worse, MySQL
will not be able to cache the query because you're
changing the query string with each paging requsts.
If you repeat the query with "select col1, col2 from table1 limit 10,10" it 
will be found in the query cache. As long as someone had executed the same 
SQL statement before, it will be found in the cache (unless it was so long 
ago that it got swapped out).

Mike


--- mos <[EMAIL PROTECTED]> wrote:
> Carlos,
>  Apart from using the proper indexes, you
> should only retrieve the
> number of rows that are going to be displayed on the
> page at one time. If
> you are displaying 10 rows on the page, why retrieve
> 1000 rows? The user
> would have to page through it 100x and you have to
> ask yourself, is this
> likely going to happen for every user? If not, then
> you are wasting CPU cycles.
>
>  It is better to use "Select ... 10,10"
> which is the offset, limit.
> Keep increasing the offset (starting at 0) for each
> time the user presses
> "Next page". MySQL will then retrieve only 10 rows
> at a time. This is 100x
> faster than retrieving all 1000 rows at a time.
>
> Mike
>
> At 04:49 PM 3/9/2005, Carlos Savoretti wrote:
> >Hi all:
> >
> >I programming a GUI which retrieve big tables
> oftenly.
> >
> >So, I retrieve chunks of 1000 rows and paginate
> then
> >
> >to browse the entire table. It works fine, but it's
> rather
> >
> >slow.
> >
> >I would like to know if I could set some option
> thru
> >
> >mysql_option() to optimize the client side
> (mysql-client-3.23.58)
> >
> >and what is the the recommended value to clamp the
> `page' for a
> >
> >gui app. (For 1000 rows it uses about 12 seconds).
> >
> >Thanks a lot...
> >
> >--
> >Carlos Savoretti <[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.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: Use MySQL with Microsoft Office

[Patrick Connolly]

Somewhere about Wed, 09-Mar-2005 at 03:02PM +0100 (give or take), Jigal van 
Hemert wrote:

|> From: "Curtis Maurand"
|> > Using ODBC, however, you can link Access tables to MySQL tables and use
|> > Access as the front end to MySQL.  It works very nicely.
|> 
|> Using ODBC you can access MySQL from OpenOffice.org (which has an
|> Access-like frontend built in), using ODBC and MS Jet engine (standard with
|> recent Windows versions) you can access and edit Access databases (*.mdb)
|> with OpenOffice.org.

but only with Windoze, is it not?  I assume it's dependent on the OS
having the Jet engine.  I'd be very interested to know if anyone has
done an equivalent to that in Linux.




-- 
   ___ Patrick Connolly  
 {~._.~}   
 _( Y )_  Good judgment comes from experience 
(:_~*~_:) Experience comes from bad judgment
 (_)-(_)


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