Re: Help to concatenate a result...

[Cory @ SkyVantage]

Thanks for the tip.. Here's the query that worked...

SELECT GROUP_CONCAT(DISTINCT segfees SEPARATOR ',')
from segfees
WHERE ID_flights_segments=[insert value here]
group by ID_flights_segments;

inferno wrote:

>Hi,
>
>You can use select group_concat(segfees) from fsf;
>The information bellow is from mysql manual, but you have to have mysql
>4.1.x
>
>=>
>
>*
>
>  |GROUP_CONCAT(/|expr|/)|
>
>  This function returns a string result with the concatenated
>  non-|NULL| values from a group. It returns |NULL| if there are no
>  non-|NULL| values. The full syntax is as follows:
>
>GROUP_CONCAT([DISTINCT] /|expr|/ [,/|expr|/ ...]
> [ORDER BY {/|unsigned_integer|/ | /|col_name|/ | /|expr|/}
> [ASC | DESC] [,/|col_name|/ ...]]
> [SEPARATOR /|str_val|/])
>
>
>mysql> *|SELECT student_name,|*
>-> *|GROUP_CONCAT(test_score)|*
>-> *|FROM student|*
>-> *|GROUP BY student_name;|*
>
>
>  Or:
>
>mysql> *|SELECT student_name,|*
>-> *|GROUP_CONCAT(DISTINCT test_score|*
>->   *|ORDER BY test_score DESC SEPARATOR ' ')|*
>-> *|FROM student|*
>-> *|GROUP BY student_name;|*
>
>
>  In MySQL, you can get the concatenated values of expression
>  combinations. You can eliminate duplicate values by using
>  |DISTINCT|. If you want to sort values in the result, you should
>  use |ORDER BY| clause. To sort in reverse order, add the |DESC|
>  (descending) keyword to the name of the column you are sorting by
>  in the |ORDER BY| clause. The default is ascending order; this may
>  be specified explicitly using the |ASC| keyword. |SEPARATOR| is
>  followed by the string value that should be inserted between
>  values of result. The default is a comma (‘|,|’). You can remove
>  the separator altogether by specifying |SEPARATOR ''|.
>
>  You can set a maximum allowed length with the
>  |group_concat_max_len| system variable. The syntax to do this at
>  runtime is as follows, where |val| is an unsigned integer:
>
>SET [SESSION | GLOBAL] group_concat_max_len = val;
>
>
>  If a maximum length has been set, the result is truncated to this
>  maximum length.
>
><=
>
>Best regards,
>Cristi
>
>
>Cory @ SkyVantage wrote:
>
>  
>
>>What I need to do is take this query and this result:
>>
>>SELECT segfees FROM fsf;
>>
>>+-+
>>| segfees |
>>+-+
>>| FS=5.00 |
>>| AY=2.50 |
>>| XF=1.75 |
>>| ZP=3.20 |
>>+-+
>>
>>I make it just return ONE row like this:
>>
>>+-+
>>| segfees |
>>+-+
>>| FS=5.00,AY=2.50,XF=1.75,ZP=3.20 |
>>+-+
>>
>>Any idea how to write a query to return this type of result? (I plan on
>>using it as a subquery, that's why I only want one result)
>>
>> 
>>
>>
>>
>
>  
>


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

How to filter rows based on a value?

[Gobi]

Suppose that I have a dummy table named, Dummy, and it has a column name 
Status, which can contain values Neg, or Pos.


If I have the following data in table, Dummy:

id, status
1   Neg
2   Neg
3   Pos
4   Neg

I need to write a query returns the count of number of Neg in status but 
filters out everything even if there is one Pos value in the column.  So 
in this case, there will be nothing returned since there is Pos in row 3.


I know I can work backward by selecting on status = Pos and if there is 
a record returned, I can dump all the results but I would prefer a 
solution that will return nothing if Pos is found in one of the records.


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

Re: Reset root password to mysql?

[Marco Simon]

Jerry Swanson schrieb:


How to reset mysql password to mysql?


mysql -u root
ERROR 1045 (0): Access denied for user: '[EMAIL PROTECTED]' (Using
password: NO)

 

You could start your mysqld with "--*without-grant-tables"* option. But 
keep in mind that this will
stop the complete permission system of mysqld and the database will be 
accessable for everyone.


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

Re: Help to concatenate a result...

[inferno]

Hi,

You can use select group_concat(segfees) from fsf;
The information bellow is from mysql manual, but you have to have mysql
4.1.x

=>

*

  |GROUP_CONCAT(/|expr|/)|

  This function returns a string result with the concatenated
  non-|NULL| values from a group. It returns |NULL| if there are no
  non-|NULL| values. The full syntax is as follows:

GROUP_CONCAT([DISTINCT] /|expr|/ [,/|expr|/ ...]
 [ORDER BY {/|unsigned_integer|/ | /|col_name|/ | /|expr|/}
 [ASC | DESC] [,/|col_name|/ ...]]
 [SEPARATOR /|str_val|/])


mysql> *|SELECT student_name,|*
-> *|GROUP_CONCAT(test_score)|*
-> *|FROM student|*
-> *|GROUP BY student_name;|*


  Or:

mysql> *|SELECT student_name,|*
-> *|GROUP_CONCAT(DISTINCT test_score|*
->   *|ORDER BY test_score DESC SEPARATOR ' ')|*
-> *|FROM student|*
-> *|GROUP BY student_name;|*


  In MySQL, you can get the concatenated values of expression
  combinations. You can eliminate duplicate values by using
  |DISTINCT|. If you want to sort values in the result, you should
  use |ORDER BY| clause. To sort in reverse order, add the |DESC|
  (descending) keyword to the name of the column you are sorting by
  in the |ORDER BY| clause. The default is ascending order; this may
  be specified explicitly using the |ASC| keyword. |SEPARATOR| is
  followed by the string value that should be inserted between
  values of result. The default is a comma (‘|,|’). You can remove
  the separator altogether by specifying |SEPARATOR ''|.

  You can set a maximum allowed length with the
  |group_concat_max_len| system variable. The syntax to do this at
  runtime is as follows, where |val| is an unsigned integer:

SET [SESSION | GLOBAL] group_concat_max_len = val;


  If a maximum length has been set, the result is truncated to this
  maximum length.

<=

Best regards,
Cristi


Cory @ SkyVantage wrote:

>What I need to do is take this query and this result:
>
>SELECT segfees FROM fsf;
>
>+-+
>| segfees |
>+-+
>| FS=5.00 |
>| AY=2.50 |
>| XF=1.75 |
>| ZP=3.20 |
>+-+
>
>I make it just return ONE row like this:
>
>+-+
>| segfees |
>+-+
>| FS=5.00,AY=2.50,XF=1.75,ZP=3.20 |
>+-+
>
>Any idea how to write a query to return this type of result? (I plan on
>using it as a subquery, that's why I only want one result)
>
>  
>


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

How to limit MySQL child processes?

[Maxim Nechaev]

After my MySQL server start i see this processes:

[EMAIL PROTECTED]:/ps -HC mysqld -o pid,command
  PID COMMAND
 3116 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql
 3117   /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mys
 3118 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/m
 3119 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/m
 3120 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/m
 3121 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/m
 3122 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/m
 3123 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/m
 3124 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/m
 3125 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/m

If i understand, processes with pids 3118-3125 is a child processes,
that waiting client connections.
I don't need so many client connections at the same time. It is
possible to limit their count?

I try to set this options in my.cnf but without result:
max_connections = 1
max_user_connections = 1
max_delayed_threads = 1

Thanks.


-- 
Maxim Nechaev


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

Help to concatenate a result...

[Cory @ SkyVantage]

What I need to do is take this query and this result:

SELECT segfees FROM fsf;

+-+
| segfees |
+-+
| FS=5.00 |
| AY=2.50 |
| XF=1.75 |
| ZP=3.20 |
+-+

I make it just return ONE row like this:

+-+
| segfees |
+-+
| FS=5.00,AY=2.50,XF=1.75,ZP=3.20 |
+-+

Any idea how to write a query to return this type of result? (I plan on
using it as a subquery, that's why I only want one result)

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

Cannot get database connection

[Gobi]

Not sure if this is the place to ask but hopefully someone can help me 
here.  I am setting an application system and have successfully setup 
MySQL 5.0.15 and Tomcat 5.5.12.  However, I cannot seem to get a 
connection object from my servlet and for the life of me, I can't figure 
out why.  Here is what I have done:


Downloaded  mysql-connector-java-3.1.11-bin.jar into 
$CATALINA_HOME/common/lib

Added mysql-connector-java-3.1.11-bin.jar to my classpath

and I created the following servlet, TestSQL:

import java.sql.*;
import javax.naming.InitialContext;
import javax.sql.DataSource;

import java.io.*;
import javax.servlet.*;
import javax.servlet.http.*;

import javax.naming.*;

public class TestSQL extends HttpServlet {   
   private Connection conn = null;


   public void doGet(HttpServletRequest request,
 HttpServletResponse response)
   throws IOException, ServletException {

   response.setContentType("text/html");
   PrintWriter out = response.getWriter();

   try {
   if (conn == null) {
   Class.forName("com.mysql.jdbc.Driver").newInstance();
   connW = 
DriverManager.getConnection("jdbc:mysql://localhost/hostname", , 
);

   }
   } catch (Exception e) {
   e.printStackTrace();
   }

   if (conn != null) {
   out.println("Connection read successful");
   } else {
   out.println("Cannot get connection");
   }
   }
}

I keep getting "Cannot get connection" message in my servlet.  I tried 
connecting to the database (locally) using mysql client with  just 
to make sure the account works and it does.  I setup a similar system 
before and was able to connect to the database successfully so I can't 
figure out what's wrong.


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

Chinese order by with utf8

[Gu Lei]

Hello:

I created tables with CHARACTER SET=utf8. And stored Chinese in them.

When I used SELECT * FROM ... ORDER BY ...
I can't get the right order.
But when I used CHARACTER SET=gbk, I can get the right order.

I didn't see 'Chinese' or 'China' when SHOW COLLATION.

What can I do?

Thanks.

Regards,
gu lei

-- 
祝 事业有成，家庭和睦，身体健康，一切吉祥

   
古雷
---
中企动力科技集团
技术事业发展部___技术架构部
  \__企业IP通讯部
电话：010 58022278-302
地址：北京亦庄经济技术开发区北工大软件园
  （地盛北街1号）A区3号楼
邮编：100176


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

RE: Reset root password to mysql?

[Logan, David (SST - Adelaide)]

I have no idea as to whether you are running windows or linux, however
the manual does go into this at :
 
http://dev.mysql.com/doc/refman/5.0/en/automatic-start.html
 
Regards

David Logan 
Database Administrator 
HP Managed Services 
148 Frome Street, 
Adelaide 5000 
Australia 

+61 8 8408 4273 - Work 
+61 417 268 665 - Mobile 
+61 8 8408 4259 - Fax 

 



From: Jerry Swanson [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, 23 November 2005 12:19 PM
To: Logan, David (SST - Adelaide)
Cc: mysql@lists.mysql.com
Subject: Re: Reset root password to mysql?


Hot to start mysql server at boot? 


On 11/22/05, Logan, David (SST - Adelaide) <[EMAIL PROTECTED]> wrote: 

Exactly as it is documented in the manual at :


http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html

Regards


David Logan
Database Administrator
HP Managed Services
148 Frome Street,
Adelaide 5000
Australia

+61 8 8408 4273 - Work 
+61 417 268 665 - Mobile
+61 8 8408 4259 - Fax


-Original Message-
From: Jerry Swanson [mailto:[EMAIL PROTECTED]
Sent: Wednesday, 23 November 2005 12:11 PM 
To: mysql@lists.mysql.com
Subject: Reset root password to mysql?

How to reset mysql password to mysql?


mysql -u root
ERROR 1045 (0): Access denied for user: ' [EMAIL PROTECTED]'
(Using
password: NO)

Re: Two fast queries combined = slow?

[David Hillman]

   You are the man, Joe.  The wall next to my desk thanks you, for  
stopping me from continuing to beat my head against it.


--
David Hillman
LiveText, Inc
1.866.LiveText x235

On Nov 22, 2005, at 7:50 PM, Joseph Cochran wrote:

Subqueries in 1.4.14 don't use indexes, instead performing full- 
table scans. Basically, they're broken in that version of MySQL.


-- Joe

Re: Reset root password to mysql?

[Jerry Swanson]

Hot to start mysql server at boot?

On 11/22/05, Logan, David (SST - Adelaide) <[EMAIL PROTECTED]> wrote:
>
> Exactly as it is documented in the manual at :
>
> http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html
>
> Regards
>
>
> David Logan
> Database Administrator
> HP Managed Services
> 148 Frome Street,
> Adelaide 5000
> Australia
>
> +61 8 8408 4273 - Work
> +61 417 268 665 - Mobile
> +61 8 8408 4259 - Fax
>
>
> -Original Message-
> From: Jerry Swanson [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, 23 November 2005 12:11 PM
> To: mysql@lists.mysql.com
> Subject: Reset root password to mysql?
>
> How to reset mysql password to mysql?
>
>
> mysql -u root
> ERROR 1045 (0): Access denied for user: '[EMAIL PROTECTED]' (Using
> password: NO)
>

Two fast queries combined = slow?

[David Hillman]

   This makes no sense to me, please explain if you can.  I have two  
queries, both run in under a tenth of a second.  But, when one is run  
as a sub-query of the other, run time is essentially infinite ( I  
haven't had that much patience, yet ).


   For example, although these are not the actual queries;

   Query A = SELECT id FROM table WHERE key < 10
   Query B = SELECT row FROM other-table WHERE id IN  
( 0,1,2,3,4,5,6,7,8,9 )


   Each query runs and returns almost immediately, separately ( and  
A returns 0-9 ).


   Query C = SELECT row FROM other-table WHERE id IN ( Query A )

   That one goes away and never comes back, as far as I know.

   How can query C take forever, when composed of two such fast  
queries?  What is done differently when it's run as sub-query, other  
than a copy to tmp table?  Speaking "copy to tmp table" is the state  
that query C stays in forever, even when it's only supposed to be  
copying 10 tiny integers.  All the appropriate indices are in place,  
and explain claims to be using them correctly.  There are a few  
million rows in the respective tables, but it still doesn't add up.


   What's going on?  Thanks.  ( MySQL 4.1.14 on various systems )

--
David Hillman
LiveText, Inc
1.866.LiveText x235

RE: Reset root password to mysql?

[Logan, David (SST - Adelaide)]

Exactly as it is documented in the manual at :

http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html

Regards 


David Logan
Database Administrator
HP Managed Services
148 Frome Street,
Adelaide 5000
Australia

+61 8 8408 4273 - Work
+61 417 268 665 - Mobile
+61 8 8408 4259 - Fax


-Original Message-
From: Jerry Swanson [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, 23 November 2005 12:11 PM
To: mysql@lists.mysql.com
Subject: Reset root password to mysql?

How to reset mysql password to mysql?


mysql -u root
ERROR 1045 (0): Access denied for user: '[EMAIL PROTECTED]' (Using
password: NO)

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

Re: How to start mysql on linux?

[Hassan Schroeder]

Jerry Swanson wrote:
> I have sudo access to Linux box. How to start mysql 4.0.14.

For 4.1, but you can probably figure out the differences :-)

  

-- 
Hassan Schroeder - [EMAIL PROTECTED]
Webtuitive Design ===  (+1) 408-938-0567   === http://webtuitive.com

  dream.  code.



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

Reset root password to mysql?

[Jerry Swanson]

How to reset mysql password to mysql?


mysql -u root
ERROR 1045 (0): Access denied for user: '[EMAIL PROTECTED]' (Using
password: NO)

Re: old_passwords

[Hassan Schroeder]

[EMAIL PROTECTED] wrote:

> If I have never created or edited a 'my.cnf' file and yet I have a 
> working MySQL, what conf file is being used and where is it (OS X 
> Server)?  Also, where does the my.cnf go once created?

  

:: might be helpful  :-)

-- 
Hassan Schroeder - [EMAIL PROTECTED]
Webtuitive Design ===  (+1) 408-938-0567   === http://webtuitive.com

  dream.  code.



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

Re: MySQL Control Center

[Jim Winstead]

On Tue, Nov 22, 2005 at 04:50:42PM -0800, Jon Drukman wrote:
> What happened to MySQL Control Center (aka mycc or mysqlcc)?  The 
> dev.mysql.com site redirects to the Query Browser page.  QB is a poor 
> substitute for mycc.  It looks like neither of them has had active 
> development much lately but at least mycc, even in its beta stage, is 
> fairly useful.

Development of MySQL Control Center was halted a couple of years ago.
The new GUI tools (Query Browser, Administrator, etc) are still under
active development.

Jim Winstead
MySQL Inc.

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

old_passwords

[ted . rogers]

Hi,

Newb question... ok just a dumb question:

If I have never created or edited a 'my.cnf' file and yet I have a  
working MySQL, what conf file is being used and where is it (OS X  
Server)?  Also, where does the my.cnf go once created?


Thanks,
TR



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

Re: UPDATE and INDEX updates

[Mike OK]

I did read the manuals (three including MySQL reference manual and DuBois
ver 5) as well as Google searches.  I am using Myisam tables (installed by
default) and from what I can tell by the from the manual the indexes would
be B-tree but I would be willing to work with another type if necessary.  I
don't think I will find the answer to this question in the normal manuals
but rather from someone / place that deals with the internals of the system.
Mike


- Original Message -
From: "sheeri kritzer" <[EMAIL PROTECTED]>
To: "Mike OK" <[EMAIL PROTECTED]>
Cc: "MySQL List" 
Sent: November 22, 2005 3:05 PM
Subject: Re: UPDATE and INDEX updates


You're going to have to read the manual on the different types of
indexes.  Without knowing what kind of index (Hash, B-tree) you're
using and on what kind of table, I cannot help you.

Parts of Chapter 14 of the MySQL manual are good, as is the section on
query indexing (starting p.301) of the DuBois book (3rd Edition).

-Sheeri

On 11/22/05, Mike OK <[EMAIL PROTECTED]> wrote:
> I understand that MySQL returns the # of rows changed.  What I am
wondering
> is if I change only one value, therefore a row change, but leave the rest,
> what happens.  Does the row get removed from the index and re-placed??
Does
> MySQL look at each column value first to compare??  Mike
>
> - Original Message -
> From: "sheeri kritzer" <[EMAIL PROTECTED]>
> To: "MySQL List" 
> Sent: November 22, 2005 9:31 AM
> Subject: Re: UPDATE and INDEX updates
>
>
> Mike,
>
> The documentation at
> http://dev.mysql.com/doc/refman/5.0/en/update.html explains that MySQL
> is aware of the fact that it only needs to update different values --
> for instance, it returns only the # of rows changed, not the # of rows
> looked at.  Given that, I will extrapolate that MySQL is not going to
> re-work an index unless it actually changes a value.
>
> -Sheeri
>
> On 11/21/05, Mike OK <[EMAIL PROTECTED]> wrote:
> > Hi
> >
> > I was wondering how the index process worked internally for UPDATE
> statements.  If I was to "set" a value for a column with the UPDATE
> statement but the value was the same, would MySQL re-work the index??  I
can
> check for data change for each column inside of my code before UPDATE but
> want to make sure I need to before going ahead.   Mike
> >
>
> --
> 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 Free Edition.
> Version: 7.1.362 / Virus Database: 267.13.5/177 - Release Date: 2005-11-21
>
>
>

--
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 Free Edition.
Version: 7.1.362 / Virus Database: 267.13.5/177 - Release Date: 2005-11-21



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

MySQL Control Center

[Jon Drukman]

What happened to MySQL Control Center (aka mycc or mysqlcc)?  The 
dev.mysql.com site redirects to the Query Browser page.  QB is a poor 
substitute for mycc.  It looks like neither of them has had active 
development much lately but at least mycc, even in its beta stage, is 
fairly useful.


-jsd-


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

How to start mysql on linux?

[Jerry Swanson]

I have sudo access to Linux box. How to start mysql 4.0.14.

TH

RE: How to SUM every row for SubTotal?

[ISC Edwin Cruz]

mysql> select * from table5;
++--+
| code   | qty  |
++--+
|100 |   30 |
|200 |   20 |
|300 |   25 |
++--+
3 rows in set (0.00 sec)

mysql> set @a=0;
Query OK, 0 rows affected (0.05 sec)

mysql> select code,qty,@a:[EMAIL PROTECTED] as subtotal from table5;
++--++
| code   | qty  | subtotal   |
++--++
|100 |   30 | 30 |
|200 |   20 | 50 |
|300 |   25 | 75 |
++--++
3 rows in set (0.00 sec)

mysql>

I hope that it helps you

Regards!

-Mensaje original-
De: The Nice Spider [mailto:[EMAIL PROTECTED] 
Enviado el: Martes, 22 de Noviembre de 2005 05:27 p.m.
Para: mysql@lists.mysql.com
Asunto: How to SUM every row for SubTotal?


 If I have table like:
 
 CODE QTY
 100  30
 200  20
 300  25
 
 and I want a result like:
 
 CODE QTY SUBTOTAL
100  30 30 
 200  20 50   (this come from 30+20)
300  25 75   (this come from 50+25)
 
 How to write query like that?


-
 Yahoo! FareChase - Search multiple travel sites in one click.  



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

How to SUM every row for SubTotal?

[The Nice Spider]

 If I have table like:
 
 CODE QTY
 100  30
 200  20
 300  25
 
 and I want a result like:
 
 CODE QTY SUBTOTAL
100  30 30 
 200  20 50   (this come from 30+20)
300  25 75   (this come from 50+25)
 
 How to write query like that?


-
 Yahoo! FareChase - Search multiple travel sites in one click.  

loading a subset of input file using mysqlimport?

[Jacek Becla]

Hi,

Is there a way to load a section of an input file into mysql
(MyISAM table) using mysqlimport or LOAD DATA INTO?
The input data is in relatively large ascii files (10 million
rows per file), and I'd like to break the load into smaller
pieces rather than load whole file at once. Of course I could
pre-process each file and split it, but I'd like to avoid this
extra I/O.

Thanks,
Jacek

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

Will the optimizer???

[Jonathan Mangin]

I'm trying to correct a situation I've created after
combining separate date/time columns into a datetime column.
Here are skeletons of new table definitions.

CREATE TABLE products
  (id mediumint unsigned primary key not null auto_increment,
   item varchar(40) not null,
   priceA decimal(6,3),
   priceB decimal(6,3),
   UNIQUE KEY `item_name` (item))

CREATE TABLE sales
  (id int unsigned primary key not null auto_increment,
   date date not null,
   time time not null,
   person varchar(25),
   item varchar(40),
   units decimal(7,4) unsigned not null,
   INDEX `person_date_time` (person, date, time))

I have a union much like the one below that seems to work well,
but I'm hoping someone has time to advise me what the optimizer
will (or won't) do given the proposed index and GROUP BY/
ORDER BY clauses, before I start changing my tables.

(SELECT date AS date,
   time_format(time,'%H:%i') AS time,
   substr(sales.item,1,18) AS item,
   round(priceA * units,2),
   round(priceB * units,2)
   FROM sales
   LEFT JOIN products
   ON sales.item = products.item
   WHERE sales.person = 'george'
   AND sales.date BETWEEN '2005-08-01' AND '2005-08-14')
union
(SELECT date AS date,
   time_format(time,'%H:%i') AS time,
   'ZZ' AS item,
   round(sum(priceA * units),2),
   round(sum(priceB * units),2)
   FROM sales
   LEFT JOIN products
   ON sales.item = products.item
   WHERE sales.person = 'george'
   AND date BETWEEN '2005-08-01' AND '2005-08-14'
   GROUP BY date, time)
union
(SELECT date AS date,
   '23:59' AS time,
   '' AS item,
   round(sum(priceA * units),2),
   round(sum(priceB * units),2)
   FROM sales
   LEFT JOIN products
   ON sales.item = products.item
   WHERE sales.person = 'george'
   AND date BETWEEN '2005-08-01' AND '2005-08-14'
   GROUP BY date)
   ORDER BY date, time, item

The result itemizes a sale, totals a sale, and totals the
day's sales, by person.  Something like this:

2005-08-01   07:30   Apples 39.29   42.40
2005-08-01   07:30   Oranges 9.29   10.02
2005-08-01   07:30   Zucchini .65 .69
2005-08-01   07:30   ZZ 49.23   53.11
2005-08-01   08:01   Bears   9.99   10.20
2005-08-01   08:01   Lions   7.287.49
2005-08-01   08:01   Tigers   .65 .69
2005-08-01   08:01   ZZ 17.92   18.38
2005-08-01   23:59      67.15   71.49
2005-08-02

Thanks!


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

Trouble setting root password

[Logg, Connie A.]

I tried to go into mysql with root and the password I had set, and access was 
refused.

I finally gave up and decided to reset the root password using the techniques 
described on
http://dev.mysql.com/doc/refman/4.1/en/resetting-permissions.html

This is a linux machine running mysql Your MySQL connection id is 5 to server 
version: 4.1.7-standard

I have tried both the mysqld_safe --init-file=/home/iepm/v3src/minit which has 
the password setting statement in it

And this does not work...I cannot get into mysql with the password.

I have also tried mysqld_safe --skip-grant-tables --user=root &

And tried setting the password and doing flush privileges.

This also does not work.

I am at my wits end as to how to do this, which I have been able to do before.

Any insight and assistance would be appreciated.

Thank you,
Connie 
Connie Logg, Network Analyst
Stanford Linear Accelerator Center
ph: 650-926-2879 
"Happiness is found along the way, not at the end of the road, and 'IF' is the 
middle word in life."

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

re: no MINUS operator in mysql 5?

[SGreen]

James Black <[EMAIL PROTECTED]> wrote on 11/22/2005 03:48:28 PM:

> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
> 
> I am wondering if there is a set-based minus operator.
> 
> I am assuming there isn't, but I thought I would ask.
> 
> Here is the query I am trying that fails.
> 
> Thank you.
> 
> select n.name
>   from names n, transactions t
>   where n.name=t.name
>   MINUS
>   select nb.name
>   from stats_names sn
> 
> - --
> "Love is mutual self-giving that ends in self-recovery." Fulton Sheen
> James Black[EMAIL PROTECTED]
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.1 (MingW32)
> Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org
> 
> iD8DBQFDg4QcikQgpVn8xrARAsFUAJ4h/vh+lcIsSASydKLzR1U4Qs62aQCeOCxk
> rjzBfzwg5hoHAAOIrnL1qD4=
> =9YjG
> -END PGP SIGNATURE-


You can easily answer this yourself if you would only RTFM. 

http://dev.mysql.com/doc/refman/5.0/en/index.html

Try the search feature, it works very well.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
 

re: no MINUS operator in mysql 5?

[James Black]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

I am wondering if there is a set-based minus operator.

I am assuming there isn't, but I thought I would ask.

Here is the query I am trying that fails.

Thank you.

select n.name
  from names n, transactions t
  where n.name=t.name
  MINUS
  select nb.name
  from stats_names sn

- --
"Love is mutual self-giving that ends in self-recovery." Fulton Sheen
James Black[EMAIL PROTECTED]
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.1 (MingW32)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFDg4QcikQgpVn8xrARAsFUAJ4h/vh+lcIsSASydKLzR1U4Qs62aQCeOCxk
rjzBfzwg5hoHAAOIrnL1qD4=
=9YjG
-END PGP SIGNATURE-

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

chroot in MySQL 5.0

[Andrew Rucker Jones]

-BEGIN PGP SIGNED MESSAGE-
Hash: RIPEMD160

Hi all!
I had chroot working under 3.23 and 4.x, although it was always rather
kludgy. Under 5.0 i can't get it to work to save my life. I start MySQL
with the mysql.server script that is included in 5.0. It seems that no
matter how i set options like basedir=, datadir=, pidfile=, and so
forth, it doesn't work. It seems like the only way to possibly make it
work would be to symlink myself to death and/or copy half of the
installation to the chroot()ed directory, since mysql.server and
mysqld_safe use the same parameters as mysqld from /etc/my.cnf, but they
use them before a chroot() call, whereas mysqld uses them after a
chroot() call. Setting the options separately in [mysql.server] and/or
[mysqld_safe] doesn't change anything.
Does anyone have any pointers? Has anyone gotten it to work?

-&

- --
GPG key / Schlüssel -- http://simultan.dyndns.org/~arjones/gpgkey.txt
Encrypt everything. / Alles verschlüsseln.

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.1 (GNU/Linux)
Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org

iD8DBQFDg3wMoI7tqy5bNGMRA5FhAKCOn1buYeQah+gdlvLg7BcyU6KBiACg5kTx
gEUZ0r1YxHBeA76n9qA04uA=
=46op
-END PGP SIGNATURE-

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

Re: UPDATE and INDEX updates

[sheeri kritzer]

You're going to have to read the manual on the different types of
indexes.  Without knowing what kind of index (Hash, B-tree) you're
using and on what kind of table, I cannot help you.

Parts of Chapter 14 of the MySQL manual are good, as is the section on
query indexing (starting p.301) of the DuBois book (3rd Edition).

-Sheeri

On 11/22/05, Mike OK <[EMAIL PROTECTED]> wrote:
> I understand that MySQL returns the # of rows changed.  What I am wondering
> is if I change only one value, therefore a row change, but leave the rest,
> what happens.  Does the row get removed from the index and re-placed??  Does
> MySQL look at each column value first to compare??  Mike
>
> - Original Message -
> From: "sheeri kritzer" <[EMAIL PROTECTED]>
> To: "MySQL List" 
> Sent: November 22, 2005 9:31 AM
> Subject: Re: UPDATE and INDEX updates
>
>
> Mike,
>
> The documentation at
> http://dev.mysql.com/doc/refman/5.0/en/update.html explains that MySQL
> is aware of the fact that it only needs to update different values --
> for instance, it returns only the # of rows changed, not the # of rows
> looked at.  Given that, I will extrapolate that MySQL is not going to
> re-work an index unless it actually changes a value.
>
> -Sheeri
>
> On 11/21/05, Mike OK <[EMAIL PROTECTED]> wrote:
> > Hi
> >
> > I was wondering how the index process worked internally for UPDATE
> statements.  If I was to "set" a value for a column with the UPDATE
> statement but the value was the same, would MySQL re-work the index??  I can
> check for data change for each column inside of my code before UPDATE but
> want to make sure I need to before going ahead.   Mike
> >
>
> --
> 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 Free Edition.
> Version: 7.1.362 / Virus Database: 267.13.5/177 - Release Date: 2005-11-21
>
>
>

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

Re: Setting the reconnect flag in MySQL 4.0.24

[SGreen]

The flag is there to help automate the renegotiation of a broken 
connection by allowing the client library to "conceal" from the 
controlling application the fact that the connection went away (for any 
reason).

Let's imagine that your client needs to update 3 tables. It makes a 
connection then loses it after only updating the first table. If all 3 
sets of changes needed to happen in a transaction, you need to start over 
because your transaction was rolled back for you when the original 
connection died. In this case an "auto-reconnect" feature may actually get 
in the way of proper error recovery.  Your application needs to know that 
the connection died so that it can restart the transaction from the 
beginning.

However, if you are importing lots of data into MyISAM tables (or running 
lots of queries) and you drop a connection mid-transfer (no transaction 
needed) then you could very easily pick up where you left off  once the 
connection comes back. In  this case having a quiet, automated 
reconnection occur for you within the client library would be a good 
thing. Your application would not know that the original connection had 
been lost as it was reconnected transparently by the client library.

Notice that I keep stressing that the client library has this flag. If you 
linked your application through the 3.23.58 library (lots of ways to do 
that) then you need to change something in your application to take 
advantage of the new features presented by your shift to 4.0.24.  Just out 
of curiosity, why didn't you choose to upgrade to a 4.1.x version?


Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine


"Betty Trakimas" <[EMAIL PROTECTED]> wrote on 11/22/2005 12:36:19 PM:

> Thanks Shawn.  Do you know why anyone would ever need the reconnect flag 

> then? The MySQL developers must think that it has a purpose since they 
> opened up the API to allow access to it from the client. What would be 
its 
> purpose otherwise?
> 
> 
> - Original Message - 
> From: <[EMAIL PROTECTED]>
> To: "Betty Trakimas" <[EMAIL PROTECTED]>
> Cc: 
> Sent: Tuesday, November 22, 2005 12:06 PM
> Subject: Re: Setting the reconnect flag in MySQL 4.0.24
> 
> 
> > "Betty Trakimas" <[EMAIL PROTECTED]> wrote on 11/22/2005 11:08:51 
AM:
> >
> >> Hello,
> >>
> >> We just upgraded from 3.23.58 to 4.0.24. Our database connection, 
which
> > can
> >> remain open for a very long time, is timing out after being idle
> > overnight,
> >> where it never had in 3.23.58.  I learned from the manual that there 
is
> > a
> >> global variable WAIT_TIMEOUT, which appears to be set at 28800 
seconds
> > (8
> >> hrs) by default.  I can set this value to some larger amount, say 7
> > days,
> >> but that may not be long enough depending on how our customers use 
our
> >> product. In version 5.0.13 I can call mysql_options() with
> >> MYSQL_OPT_RECONNECT set to true to enable automatic reconnection to 
the
> >> server if the connection has been lost. I think this is really what I
> > want
> >> to do instead of setting the WAIT_TIMEOUT variable to a large value. 
Is
> >
> >> there any way to set this reconnect flag in the 4.0.24 version
> >> programatically? How?
> >>
> >> Betty
> >>
> >
> > Any application that depends on a connection to another application in
> > order to function correctly should be robust enough to deal with the
> > temporary or permanent loss of that connection without causing too 
much of
> > a problem. It doesn't matter what kind of other application your
> > application needs to connect with, if it cannot handle a loss of
> > communications without breaking it is poorly designed.
> >
> > The reconnect flag is part of the client (your application) and how it
> > makes its connection to the MySQL server. RTFM for the specific 
library
> > your application is using for it's MySQL connectivity. It will have
> > details on how to manage that flag.
> >
> > Most server-friendly applications only make connections when they need
> > something. Tying up a network socket overnight for little or no 
traffic
> > just wastes server-side resources. I am not recommending a new 
connection
> > for EVERY query, once per process is generally enough. I am 
recommending
> > that you look at your original application's design and fix it so that
> > your users won't complain every time it loses connectivity with the
> > database on the back end. There are more causes of LOC (loss of
> > communication) other than the server reaching it's timeout value so 
fixing
> > that will improve the overall reliability of your application.
> >
> > Shawn Green
> > Database Administrator
> > Unimin Corporation - Spruce Pine
> >
> > 
> 
> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
> 

calling procedures from the .NET provider

[Colin Shreffler]

I've seen a few posts out there regarding this subject, but no solutions 
that I could understand.


I am unable to execute a stored procedure from the MySql data provider.  
I get a message that says:


'42000 SELECT command denied to user' etc.

Can anyone tell me what I need to do to enable my procudure to be called?  


Furthermore, is there a way to call a stored procedure and get a DataSet in 
return?

I also tried the MySqlHelper class as it appears to have methods that allow you 
to pass parameters to a command.  But this does not work when I try to execute 
a stored procedure.  Has anyone been able to get this to work?

Thanks,
Colin



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

Re: Can't materialize a view -- bug?

[Gleb Paharenko]

Hello.



In my opinion it is a bug. You may add your comments at:

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



>If this is a bug, I'll be happy to file a report... but I'd really like a

>solution that will let me use the function.



I've noticed that you may create a temporary tables from views like your,

so a workaround is to create a temporary table first, and then a persistent 

table.



>I hit a problem using functions in stored procedures, too, and I'm wondering

>if these are related.



Please provide more information or a test case. Check that you're using

the latest MySQL version (5.0.16 now). Functions work in stored

procedures:



drop procedure if exists psignal;

drop function if exists get_signal;

delimiter $$

CREATE FUNCTION get_signal (cnt MEDIUMINT(8), ma MEDIUMINT(8), stdv

MEDIUMINT(8)) RETURNS TINYINT(1)

DETERMINISTIC

RETURN IF (ma > 9 AND stdv > 0 AND (cnt >= ma + (2 * stdv)) OR cnt <= ma -

(2 * stdv),

IF (cnt >= ma + (2 * stdv), 1, -1),

0);

$$

CREATE procedure psignal()

DETERMINISTIC

BEGIN

SELECT get_signal(0,1,1);

END;

$$

delimiter ;

call psignal();



[EMAIL PROTECTED] mysql-debug-5.0.16-linux-i686-glibc23]$ lmysql  wrote:

>I'm doing the equivalent of a materialized view in MySQL 5.0.15 and I've hit

>a strange error.

>

>Here's the scenario.

>

>CREATE OR REPLACE VIEW x_view AS

>SELECT [select statement, blah, blah, blah];

>

>DROP TABLE IF EXISTS x_mview;

>CREATE TABLE x_mview SELECT * FROM x_view;

>

>That was all working fine until I created a function that I use inside of

>the view. Now the last statement, the one that would materialize the view,

>returns a table locking error!

>

>ERROR 1100 (HY000): Table 'x_mview' was not locked with LOCK TABLES

>

>It's difficult to lock a table that doesn't exist yet...

>

>"SELECT * FROM x_view" works fine.

>

>The function is a simple bit of logic (it tests a count, moving average and

>standard deviation to see if the count is more or less than two standard

>deviations from the mean):

>

>CREATE FUNCTION get_signal (cnt MEDIUMINT(8), ma MEDIUMINT(8), stdv

>MEDIUMINT(8)) RETURNS TINYINT(1)

>DETERMINISTIC

>RETURN IF (ma > 9 AND stdv > 0 AND (cnt >= ma + (2 * stdv)) OR cnt <= ma -

>(2 * stdv),

>IF (cnt >= ma + (2 * stdv), 1, -1),

>0);

>

>If this is a bug, I'll be happy to file a report... but I'd really like a

>solution that will let me use the function.

>

>I hit a problem using functions in stored procedures, too, and I'm wondering

>if these are related.

>

>Nick



-- 
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: default character setting

[Gleb Paharenko]

Hello.



For server you should put these lines into [mysqld] section. See:

  http://dev.mysql.com/doc/refman/5.0/en/charset-server.html





Mester József wrote:

> Hy

>  

>  I tried to set the default character set for mysql-4.1-15 server in my.cnf

>  [client] section

>  with set :

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

>  and 

>  default_character_set=latin2

>  

>  but when I check the defaults by show variables like '%_server' 

>  I get these value is latin1

>  

>  What is wrong? 

>  

>  Please help.

>  

>  Joe

>  

>  

>  

> 

>   

> -

>  Yahoo! Model Search  - Could you be the next catwalk superstar? Check out 
> the competition now 



-- 
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: UDF

[Gleb Paharenko]

Hello.



> My doubt is: Is it the only way to have UDF server-side in >MySQL(4.1.x)?

> Do I need to learn C/C++ to have a UDF?



In my opinion - yes, because UFDs usually uses includes (say, mysql.h)

which are written in C/C++. Speaking in general - UDF for MySQL is just

a shared library, so perhaps, can be written in another language. But

be a building framework (like include files) can not exists for it.





Ronan Lucio wrote:

> Hello,

> 

> I�m using MySQL-4.1.7 and I need to create a UDF.

> For all I have understood, I need to create it in C/C++ sintaxe.

> 

> My doubt is: Is it the only way to have UDF server-side in MySQL(4.1.x)?

> Do I need to learn C/C++ to have a UDF?

> 

> Thanks,

> Ronan 

> 

> 

> 



-- 
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: dump to more than 1 file

[Gleb Paharenko]

Hello.



If you have such a big database, may be you should think about

--tab option of mysqldump:

  http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html







Tom Brown wrote:





> is it possible to do a mysql dump to more than 1 file? We will shortly

> be needing to dump a db that will be in excess of 50gb so will encounter

> file size issues

> 

> This is on 4.1.x and rhel 4

> 

> 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: Setting the reconnect flag in MySQL 4.0.24

[Gleb Paharenko]

Hello.



MYSQL structure has a field reconnect, you may set it from your

application. Also, as I remember, if you're using prepared statements

your client program automatically reconnects.





Betty Trakimas wrote:

> Hello,

> 

> We just upgraded from 3.23.58 to 4.0.24. Our database connection, which can 

> remain open for a very long time, is timing out after being idle overnight, 

> where it never had in 3.23.58.  I learned from the manual that there is a 

> global variable WAIT_TIMEOUT, which appears to be set at 28800 seconds (8 

> hrs) by default.  I can set this value to some larger amount, say 7 days, 

> but that may not be long enough depending on how our customers use our 

> product. In version 5.0.13 I can call mysql_options() with 

> MYSQL_OPT_RECONNECT set to true to enable automatic reconnection to the 

> server if the connection has been lost. I think this is really what I want 

> to do instead of setting the WAIT_TIMEOUT variable to a large value.  Is 

> there any way to set this reconnect flag in the 4.0.24 version 

> programatically? How?

> 

> Betty

> 

> 

> 



-- 
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: Getting no of different rows by group by clause

[Rhino]

This query should tell you how many occurrences of each value you have in 
the table:


select name, count(*) as count
from mytable
group by name
order by count desc

Rhino

- Original Message - 
From: <[EMAIL PROTECTED]>

To: 
Sent: Tuesday, November 22, 2005 11:25 AM
Subject: Getting no of different rows by group by clause


Hi Friends,
I have a table like

name

MAD
LHR
MAD
LHR
AKL
AWL
AKL
LHR

I want the output as:
LHR 3
AKL 2
AWL 1

etc...

ie the no of entires sorted by their no of appearences.
I cannot do that by select name from tab_name group by name as it will not
give me the no of rows.

Pl. help me to find it. I know it would be simple but i think i am missing
some basic of MySQL.
Thanks
--
Regards
Abhishek Jain


mail2web - Check your email from the web at
http://mail2web.com/ .



--
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 Free Edition.
Version: 7.1.362 / Virus Database: 267.13.5/177 - Release Date: 21/11/2005




--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.362 / Virus Database: 267.13.5/177 - Release Date: 21/11/2005


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

Re: Setting the reconnect flag in MySQL 4.0.24

[Betty Trakimas]

Thanks Shawn.  Do you know why anyone would ever need the reconnect flag 
then? The MySQL developers must think that it has a purpose since they 
opened up the API to allow access to it from the client. What would be its 
purpose otherwise?



- Original Message - 
From: <[EMAIL PROTECTED]>

To: "Betty Trakimas" <[EMAIL PROTECTED]>
Cc: 
Sent: Tuesday, November 22, 2005 12:06 PM
Subject: Re: Setting the reconnect flag in MySQL 4.0.24



"Betty Trakimas" <[EMAIL PROTECTED]> wrote on 11/22/2005 11:08:51 AM:


Hello,

We just upgraded from 3.23.58 to 4.0.24. Our database connection, which

can

remain open for a very long time, is timing out after being idle

overnight,

where it never had in 3.23.58.  I learned from the manual that there is

a

global variable WAIT_TIMEOUT, which appears to be set at 28800 seconds

(8

hrs) by default.  I can set this value to some larger amount, say 7

days,

but that may not be long enough depending on how our customers use our
product. In version 5.0.13 I can call mysql_options() with
MYSQL_OPT_RECONNECT set to true to enable automatic reconnection to the
server if the connection has been lost. I think this is really what I

want

to do instead of setting the WAIT_TIMEOUT variable to a large value.  Is



there any way to set this reconnect flag in the 4.0.24 version
programatically? How?

Betty



Any application that depends on a connection to another application in
order to function correctly should be robust enough to deal with the
temporary or permanent loss of that connection without causing too much of
a problem. It doesn't matter what kind of other application your
application needs to connect with, if it cannot handle a loss of
communications without breaking it is poorly designed.

The reconnect flag is part of the client (your application) and how it
makes its connection to the MySQL server. RTFM for the specific library
your application is using for it's MySQL connectivity. It will have
details on how to manage that flag.

Most server-friendly applications only make connections when they need
something. Tying up a network socket overnight for little or no traffic
just wastes server-side resources. I am not recommending a new connection
for EVERY query, once per process is generally enough. I am recommending
that you look at your original application's design and fix it so that
your users won't complain every time it loses connectivity with the
database on the back end. There are more causes of LOC (loss of
communication) other than the server reaching it's timeout value so fixing
that will improve the overall reliability of your application.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine






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

Re: Setting the reconnect flag in MySQL 4.0.24

[SGreen]

"Betty Trakimas" <[EMAIL PROTECTED]> wrote on 11/22/2005 11:08:51 AM:

> Hello,
> 
> We just upgraded from 3.23.58 to 4.0.24. Our database connection, which 
can 
> remain open for a very long time, is timing out after being idle 
overnight, 
> where it never had in 3.23.58.  I learned from the manual that there is 
a 
> global variable WAIT_TIMEOUT, which appears to be set at 28800 seconds 
(8 
> hrs) by default.  I can set this value to some larger amount, say 7 
days, 
> but that may not be long enough depending on how our customers use our 
> product. In version 5.0.13 I can call mysql_options() with 
> MYSQL_OPT_RECONNECT set to true to enable automatic reconnection to the 
> server if the connection has been lost. I think this is really what I 
want 
> to do instead of setting the WAIT_TIMEOUT variable to a large value.  Is 

> there any way to set this reconnect flag in the 4.0.24 version 
> programatically? How?
> 
> Betty
> 

Any application that depends on a connection to another application in 
order to function correctly should be robust enough to deal with the 
temporary or permanent loss of that connection without causing too much of 
a problem. It doesn't matter what kind of other application your 
application needs to connect with, if it cannot handle a loss of 
communications without breaking it is poorly designed.

The reconnect flag is part of the client (your application) and how it 
makes its connection to the MySQL server. RTFM for the specific library 
your application is using for it's MySQL connectivity. It will have 
details on how to manage that flag.

Most server-friendly applications only make connections when they need 
something. Tying up a network socket overnight for little or no traffic 
just wastes server-side resources. I am not recommending a new connection 
for EVERY query, once per process is generally enough. I am recommending 
that you look at your original application's design and fix it so that 
your users won't complain every time it loses connectivity with the 
database on the back end. There are more causes of LOC (loss of 
communication) other than the server reaching it's timeout value so fixing 
that will improve the overall reliability of your application.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Problems with mysql.sock

[Chip Lambert]

Hello all

 

I've installed MySQL Server 5.0.16 and MySQL Client 5.0.16 (from the RPMs on
the MySQL site) on a server running Fedora Core 4. Every time I try to start
the service, I get the Unable to connect to the MySQL server through the
/tmp/mysql.sock error. I've tried several things from the MySQL site and
from Google. But I noticed at the end of the user comments on the page
http://dev.mysql.com/doc/refman/5.0/en/can-not-connect-to-server.html say
these solutions do not work FC 4.

 

Could anyone help me out with this? I'd greatly appreciate it.

 

Thanks!

 

Chip Lambert

IST Support and Database Analyst

Bluefield College

3000 College Drive

Bluefield, VA 24605

276.326.4219

1.800.872.0175 ext. 219

[EMAIL PROTECTED]

 

Re: Getting no of different rows by group by clause

[Peter Brawley]

Abhishek

>I want the output as:
>LHR 3
>AKL 2
>AWL 1...

>ie the no of entires sorted by their no of appearences.

SELECT
 tbl.name AS Name,
 COUNT( tbl.name) AS Count
FROM tbl
GROUP BY tbl.name
ORDER BY Count DESC;

PB


[EMAIL PROTECTED] wrote:


Hi Friends,
I have a table like 


name

MAD
LHR
MAD
LHR
AKL
AWL
AKL
LHR

I want the output as:
LHR 3
AKL 2
AWL 1

etc...

ie the no of entires sorted by their no of appearences.
I cannot do that by select name from tab_name group by name as it will not
give me the no of rows.

Pl. help me to find it. I know it would be simple but i think i am missing
some basic of MySQL.
Thanks
--
Regards
Abhishek Jain


mail2web - Check your email from the web at
http://mail2web.com/ .



 




--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.362 / Virus Database: 267.13.5/177 - Release Date: 11/21/2005


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

Re: Getting no of different rows by group by clause

[Jeremy Cole]

Hi Abishek,


I want the output as:
LHR 3
AKL 2
AWL 1


This should do it:

SELECT name, COUNT(*) as num
FROM tab_name
GROUP BY name
ORDER BY num DESC

Regards,

Jeremy

--
Jeremy Cole
MySQL Geek, Yahoo! Inc.
Desk: 408 349 5104

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

Getting no of different rows by group by clause

[[EMAIL PROTECTED]]

Hi Friends,
I have a table like 

name

MAD
LHR
MAD
LHR
AKL
AWL
AKL
LHR

I want the output as:
LHR 3
AKL 2
AWL 1

etc...

ie the no of entires sorted by their no of appearences.
I cannot do that by select name from tab_name group by name as it will not
give me the no of rows.

Pl. help me to find it. I know it would be simple but i think i am missing
some basic of MySQL.
Thanks
--
Regards
Abhishek Jain


mail2web - Check your email from the web at
http://mail2web.com/ .



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

Setting the reconnect flag in MySQL 4.0.24

[Betty Trakimas]

Hello,

We just upgraded from 3.23.58 to 4.0.24. Our database connection, which can 
remain open for a very long time, is timing out after being idle overnight, 
where it never had in 3.23.58.  I learned from the manual that there is a 
global variable WAIT_TIMEOUT, which appears to be set at 28800 seconds (8 
hrs) by default.  I can set this value to some larger amount, say 7 days, 
but that may not be long enough depending on how our customers use our 
product. In version 5.0.13 I can call mysql_options() with 
MYSQL_OPT_RECONNECT set to true to enable automatic reconnection to the 
server if the connection has been lost. I think this is really what I want 
to do instead of setting the WAIT_TIMEOUT variable to a large value.  Is 
there any way to set this reconnect flag in the 4.0.24 version 
programatically? How?


Betty



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

Re: Only finding one record per ZIP code

[Brian Dunning]

You're exactly right, and that solved it! Thank you.


On Nov 22, 2005, at 7:49 AM, [EMAIL PROTECTED] wrote:




Brian Dunning <[EMAIL PROTECTED]> wrote on 11/22/2005 10:43:13  
AM:


> I'm using the following to find the nearest 10 records by proximity
> to the ZIP code $zip. The problem is that it only returns one record
> per ZIP code, even when there are multiple records with the same  
ZIP:

>
> (Note this uses a temp table, but I already double-checked that all
> the desired records are making it into the temp table.)
>
> SELECT $tmp.zip, $tmp.state, $tmp.name, $tmp.addr1, $tmp.addr2,
> $tmp.addr3, $tmp.city, $tmp.state, $tmp.zip, $tmp.web, $tmp.email,
> $tmp.phone,
>ROUND((3956 * (2 * ASIN(SQRT(
>POWER(SIN((($tmp.lat-zips.lat)*0.017453293)/2),2) +
>COS($tmp.lat*0.017453293) *
>COS(zips.lat*0.017453293) *
>POWER(SIN((($tmp.lon-zips.lon)*0.017453293)/2),2),2) AS  
distance

>FROM zips,$tmp
>WHERE
>$tmp.account_id='$account_id' AND zips.zip='$zip'
>GROUP BY distance
>order by distance
>limit 10
>

No, it's only returning one row for each unique distance value. It  
just so happens that for each instance, there is only one zipcode.  
I don't think you need the GROUP BY clause at all to do the  
calcuation you want. Take it out and see what happens.


Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Re: Tips for better performance

[Dan Baker]

"Marko Knezevic" <[EMAIL PROTECTED]> wrote in message 
news:[EMAIL PROTECTED]
Here are my tables and queries i am running on them with index explanations.
Hope this will help.

ACCOUNTS TABLE:
+-+--+--+-+-+
| Field   | Type | Null | Key | Default
+-+--+--+-+-+
| id  | varchar(36)  |  | PRI |
| assigned_user_id| varchar(36)  | YES  | MUL | NULL
| deleted | tinyint(1)   |  | | 0
+-+--+--+-+-+


USERS TABLE:
+---+--+--+-+-+
| Field | Type | Null | Key | Default |
+---+--+--+-+-+
| id| varchar(36)  |  | PRI | |
+---+--+--+-+-+


SELECT users.user_name assigned_user_name, accounts.* FROM  accounts LEFT
JOIN users ON accounts.assigned_user_id=users.id where  accounts.deleted=0
ORDER BY name asc LIMIT 20,20



Explain says:

id,select_type,table,type,possible_keys,key,key_len,ref,rows,Extra

1,SIMPLE,accounts,index,NULL,Name,151,NULL,888466,Using where

1,SIMPLE,users,eq_ref,PRIMARY,PRIMARY,36,sugarcrm.accounts.assigned_user_id,1,



I have also other SELECTS but with different ORDER BY's (this select uses
name, but also it could be sorted on city, state, phone_fax, phone_office,
phone_alternate..)


FIRST:
Typically, you need indexes on fields that are specified in your JOIN/WHERE 
clause:
accounts.assigned_user_id
users.id
accounts.deleted


SECOND:
Why are your "id" fields all VARCHAR(36)?  These are huge keys!  I would 
recommend using an INT as the id (that allows over 4 billion unique id's). 
If you have some internal id that is 36 characters long, have two id 
fields -- one for the relational-key-id (INT), and then your long string id 
for your clients to use.

DanB 




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

Re: Only finding one record per ZIP code

[SGreen]

Brian Dunning <[EMAIL PROTECTED]> wrote on 11/22/2005 10:43:13 AM:

> I'm using the following to find the nearest 10 records by proximity 
> to the ZIP code $zip. The problem is that it only returns one record 
> per ZIP code, even when there are multiple records with the same ZIP:
> 
> (Note this uses a temp table, but I already double-checked that all 
> the desired records are making it into the temp table.)
> 
> SELECT $tmp.zip, $tmp.state, $tmp.name, $tmp.addr1, $tmp.addr2, 
> $tmp.addr3, $tmp.city, $tmp.state, $tmp.zip, $tmp.web, $tmp.email, 
> $tmp.phone,
>ROUND((3956 * (2 * ASIN(SQRT(
>POWER(SIN((($tmp.lat-zips.lat)*0.017453293)/2),2) +
>COS($tmp.lat*0.017453293) *
>COS(zips.lat*0.017453293) *
>POWER(SIN((($tmp.lon-zips.lon)*0.017453293)/2),2),2) AS distance
>FROM zips,$tmp
>WHERE
>$tmp.account_id='$account_id' AND zips.zip='$zip'
>GROUP BY distance
>order by distance
>limit 10
> 

No, it's only returning one row for each unique distance value. It just so 
happens that for each instance, there is only one zipcode. I don't think 
you need the GROUP BY clause at all to do the calcuation you want. Take it 
out and see what happens.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Re: DATETIME columns and indexing

[SGreen]

"Jonathan Mangin" <[EMAIL PROTECTED]> wrote on 11/22/2005 10:37:00 
AM:

> (I used to have separate date/time cols. in all tables
> but changed them to datetime and buggered up some stuff.
> Now I'm trying to find the best way to fix this.)
> 
> If I have an indexed datetime column (`date`), and say:
> 
>select date, 
>   from table1
>   where date between '2005-08-01' and '2005-08-14'
>   order by date;
> 
> Can the index ever be used in a date-only where clause
> or must I include the time part also?
> Explain does show the index in possible_keys, but is not 
> shown under key. (Very little data in this table).
> 
> Thanks,
> Jon
> 

Depending on how little data there is, it may be less efficient to use the 
index to get the data if you are selecting more than about 30% of the 
available rows.

Yes, you can use date-only terms in your where clauses but you have to 
remember that if you list just a date (like '2005-08-01' it will compare 
to a datetime value as '2005-08-01 00:00:00'.  That means that your WHERE 
clause above is evaluated as 

WHERE date between '2005-08-01 00:00:00' and '2005-08-14 00:00:00'

Unless an event happens exactly on midnight of the 14th, it won't make the 
cut. Does that explain why you are chopping off the last day's worth of 
data?  Either compare against the next day or the next day minus 1 second

WHERE date between '2005-08-01 00:00:00' and '2005-08-15' - INTERVAL 1 
SECOND

There are many ways to make that adjustment, use whichever works for your 
versionhttp://dev.mysql.com/doc/refman/4.1/en/date-and-time-functions.html

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Only finding one record per ZIP code

[Brian Dunning]

I'm using the following to find the nearest 10 records by proximity  
to the ZIP code $zip. The problem is that it only returns one record  
per ZIP code, even when there are multiple records with the same ZIP:


(Note this uses a temp table, but I already double-checked that all  
the desired records are making it into the temp table.)


SELECT $tmp.zip, $tmp.state, $tmp.name, $tmp.addr1, $tmp.addr2,  
$tmp.addr3, $tmp.city, $tmp.state, $tmp.zip, $tmp.web, $tmp.email,  
$tmp.phone,

ROUND((3956 * (2 * ASIN(SQRT(
POWER(SIN((($tmp.lat-zips.lat)*0.017453293)/2),2) +
COS($tmp.lat*0.017453293) *
COS(zips.lat*0.017453293) *
POWER(SIN((($tmp.lon-zips.lon)*0.017453293)/2),2),2) AS distance
FROM zips,$tmp
WHERE
$tmp.account_id='$account_id' AND zips.zip='$zip'
GROUP BY distance
order by distance
limit 10

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

DATETIME columns and indexing

[Jonathan Mangin]

(I used to have separate date/time cols. in all tables
but changed them to datetime and buggered up some stuff.
Now I'm trying to find the best way to fix this.)

If I have an indexed datetime column (`date`), and say:

  select date, 
 from table1
 where date between '2005-08-01' and '2005-08-14'
 order by date;

Can the index ever be used in a date-only where clause
or must I include the time part also?
Explain does show the index in possible_keys, but is not 
shown under key. (Very little data in this table).


Thanks,
Jon


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

Re: UPDATE and INDEX updates

[Mike OK]

I understand that MySQL returns the # of rows changed.  What I am wondering
is if I change only one value, therefore a row change, but leave the rest,
what happens.  Does the row get removed from the index and re-placed??  Does
MySQL look at each column value first to compare??  Mike

- Original Message -
From: "sheeri kritzer" <[EMAIL PROTECTED]>
To: "MySQL List" 
Sent: November 22, 2005 9:31 AM
Subject: Re: UPDATE and INDEX updates


Mike,

The documentation at
http://dev.mysql.com/doc/refman/5.0/en/update.html explains that MySQL
is aware of the fact that it only needs to update different values --
for instance, it returns only the # of rows changed, not the # of rows
looked at.  Given that, I will extrapolate that MySQL is not going to
re-work an index unless it actually changes a value.

-Sheeri

On 11/21/05, Mike OK <[EMAIL PROTECTED]> wrote:
> Hi
>
> I was wondering how the index process worked internally for UPDATE
statements.  If I was to "set" a value for a column with the UPDATE
statement but the value was the same, would MySQL re-work the index??  I can
check for data change for each column inside of my code before UPDATE but
want to make sure I need to before going ahead.   Mike
>

--
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 Free Edition.
Version: 7.1.362 / Virus Database: 267.13.5/177 - Release Date: 2005-11-21



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

Re: REITF?

[Kristen G. Thorson]

http://www.google.com/search?hl=en&lr=&q=real+estate+investment+trust&btnG=Search

REIT = real estate investment trust

Maybe the extra F is for "fund?"  I didn't see any mention of REITF - 
but I have seen the acronym REIT before.



kgt



Ed Curtis wrote:


Does anyone on the list know what REITF stands for? I'm guessing it's
some type of data format for real estate information but I can't find any
information on it anywhere.

Thanks,

Ed



 




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

Re: UPDATE and INDEX updates

[sheeri kritzer]

Mike,

The documentation at
http://dev.mysql.com/doc/refman/5.0/en/update.html explains that MySQL
is aware of the fact that it only needs to update different values --
for instance, it returns only the # of rows changed, not the # of rows
looked at.  Given that, I will extrapolate that MySQL is not going to
re-work an index unless it actually changes a value.

-Sheeri

On 11/21/05, Mike OK <[EMAIL PROTECTED]> wrote:
> Hi
>
> I was wondering how the index process worked internally for UPDATE 
> statements.  If I was to "set" a value for a column with the UPDATE statement 
> but the value was the same, would MySQL re-work the index??  I can check for 
> data change for each column inside of my code before UPDATE but want to make 
> sure I need to before going ahead.   Mike
>

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

Re: Tips for better performance

[Marko Knezevic]

I also have following situation:


SELECT count(*) FROM  accounts LEFT JOIN users ON 
accounts.assigned_user_id=users.id where ((accounts.phone_office like '01 
389437%' OR accounts.phone_alternate like '01 389437%' OR accounts.phone_fax 
like '01 389437%')) AND  accounts.deleted=0




Explain says it's ok with:

id,select_type,  table,type, possible_keys,  key, 
key_len,   ref,   rows,Extra


1, SIMPLE,   accounts, 
index,phone_fax,phone_office,idx_phone_alt,idx_asofalfade_tmp,116,NULL,888466,Using 
where; Using index


1, SIMPLE,   users, 
eq_ref,PRIMARY,PRIMARY,36,sugarcrm.accounts.assigned_user_id,1,Using indeks




And it takes about 1.5 secs on about 1.000.000 records..



BUT



explain SELECT users.user_name assigned_user_name,  accounts.* FROM 
accounts LEFT JOIN users ON accounts.assigned_user_id=users.id where 
((accounts.phone_office like '01 389437%' OR accounts.phone_alternate like 
'01 389437%' OR accounts.phone_fax like '01 389437%')) AND 
accounts.deleted=0  ORDER BY phone_office asc LIMIT 0,20




says:

id,select_type,table,type,possible_keys,key,key_len,ref,rows,Extra

1,SIMPLE,accounts,index,phone_fax,phone_office,idx_phone_alt,phone_office,26,NULL,888466,Using 
where


1,SIMPLE,users,eq_ref,PRIMARY,PRIMARY,36,sugarcrm.accounts.assigned_user_id,1,



And takes more than 8 seconds.. (it can also be ORDERED BY phone_office like 
this time, or by name, address or something..)



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

Re: Tips for better performance

[Marko Knezevic]

Marko,

I have a table with 5,000,000 records that takes about 1 second
to show the results.

Perhaps your queries aren´t optimized properly.

Ronan


Here are my tables and queries i am running on them with index explanations. 
Hope this will help.



ACCOUNTS TABLE:

+-+--+--+-+-+---+

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


+-+--+--+-+-+---+

| id  | varchar(36)  |  | PRI | 
|   |


| date_entered| datetime |  | | -00-00 
00:00:00 |   |


| date_modified   | datetime |  | | -00-00 
00:00:00 |   |


| modified_user_id| varchar(36)  |  | | 
|   |


| assigned_user_id| varchar(36)  | YES  | MUL | NULL 
|   |


| created_by  | varchar(36)  | YES  | | NULL 
|   |


| name| varchar(150) | YES  | MUL | NULL 
|   |


| parent_id   | varchar(36)  | YES  | | NULL 
|   |


| account_type| varchar(25)  | YES  | | NULL 
|   |


| industry| varchar(25)  | YES  | | NULL 
|   |


| annual_revenue  | varchar(25)  | YES  | | NULL 
|   |


| phone_fax   | varchar(25)  | YES  | MUL | NULL 
|   |


| billing_address_street  | varchar(150) | YES  | | NULL 
|   |


| billing_address_city| varchar(100) | YES  | | NULL 
|   |


| billing_address_state   | varchar(100) | YES  | | NULL 
|   |


| billing_address_postalcode  | varchar(20)  | YES  | | NULL 
|   |


| billing_address_country | varchar(100) | YES  | | NULL 
|   |


| description | text | YES  | | NULL 
|   |


| rating  | varchar(25)  | YES  | | NULL 
|   |


| phone_office| varchar(25)  | YES  | MUL | NULL 
|   |


| phone_alternate | varchar(25)  | YES  | MUL | NULL 
|   |


| email1  | varchar(100) | YES  | | NULL 
|   |


| email2  | varchar(100) | YES  | | NULL 
|   |


| website | varchar(255) | YES  | | NULL 
|   |


| ownership   | varchar(100) | YES  | | NULL 
|   |


| employees   | varchar(10)  | YES  | | NULL 
|   |


| sic_code| varchar(10)  | YES  | | NULL 
|   |


| ticker_symbol   | varchar(10)  | YES  | | NULL 
|   |


| shipping_address_street | varchar(150) | YES  | | NULL 
|   |


| shipping_address_city   | varchar(100) | YES  | | NULL 
|   |


| shipping_address_state  | varchar(100) | YES  | | NULL 
|   |


| shipping_address_postalcode | varchar(20)  | YES  | | NULL 
|   |


| shipping_address_country| varchar(100) | YES  | | NULL 
|   |


| deleted | tinyint(1)   |  | | 0 
|   |


+-+--+--+-+-+---+



USERS TABLE:

+---+--+--+-+-+---+

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


+---+--+--+-+-+---+

| id| varchar(36)  |  | PRI | | 
|


| user_name | varchar(20)  | YES  | MUL | NULL| 
|


| user_password | varchar(30)  | YES  | MUL | NULL| 
|


| user_hash | varchar(32)  | YES  | | NULL| 
|


| first_name| varchar(30)  | YES  | | NULL| 
|


| last_name | varchar(30)  | YES  | | NULL| 
|


| reports_to_id | varchar(36)  | YES  | MUL | NULL| 
|


| is_admin  | char(3)  | YES  | | 0   | 
|


| receive_notifications | char(1)  | YES  | | 1   | 
|


| description   | text | YES  | | NULL| 
|


| date_entered  | datetime |  | | -00-00 00:00:00 | 
|


| date_modified | datetime |  | | -00-00 00:00:00 | 
|


| modified_user_id  | varchar(36)  | YES  | | NULL| 
|


| created_by| varchar(36)  | YES  | | NULL| 
|


| title | varchar(50)  | YES  | | NULL| 
|


| department| varchar(50)  | YES  | | NULL| 
|


| phone_home| varchar(50)  | YES  | | NULL| 
|


| phone_mobile  | varchar(50)  | YES  | | N

Re: Country codes

[Rahul S. Johari]

Ave,

You can use the attached SQL file to create a table with all Country names,
ISO codes, and easily write up a code to fill in the values from the table.
If you have any problems filing up the combo box with the values from the
table, let me know.

Dominor,

Rahul S. Johari
Coordinator, Internet & Administration
Informed Marketing Services Inc.
251 River Street
Troy, NY 12180

Tel: (518) 266-0909 x154
Fax: (518) 266-0909
Email: [EMAIL PROTECTED]
http://www.informed-sources.com


On 11/22/05 9:05 AM, "prathima rao" <[EMAIL PROTECTED]> wrote:

> do any one have ready made combo box with all the country codes present
> - Original Message -
> From: "Rhino" <[EMAIL PROTECTED]>
> To: "Björn Persson" <[EMAIL PROTECTED]>; 
> Sent: 21/11/2005 6:18 AM
> Subject: Re: Country codes
> 
> 
>> I'm not saying it was an inappropriate way to present the data but it
>> certainly was unexpected and, in my opinion, counter-intuitive. I didn't
>> want the original poster to see page 3, see that his own country, Brazil,
>> was missing, and dismiss the PDF as garbage.
>> 
>> Rhino
>> 
>> P.S. Sorry for top-posting but I can't intersperse normally with your
> email.
>> 
>> 
>> - Original Message -
>> From: "Björn Persson" <[EMAIL PROTECTED]>
>> To: 
>> Sent: Sunday, November 20, 2005 7:21 PM
>> Subject: Re: Country codes
>> 
>> 
>> Rhino wrote:
>>> Be careful when reading those lists! When I looked at page 3 in the
>>> English
>>> PDF, it said it was in numeric order and the list showed all the
> countries
>>> that use country code 1, then country 20 (Egypt), then country 210
> (spare)
>>> *without* showing 55 (Brazil). Page 6 shows countries 500 through 509,
>>> then
>>> 51 through 58, and so on. Therefore, Brazil doesn't appear until Page 6.
>>> They have obviously chosen to sort the list only on the FIRST DIGIT of
> the
>>> country code; that's a pretty odd form of numerical order, in my
> opinion!
>> 
>> That's not normal numerical order of course, but it's exactly the order
> you
>> need if you're parsing a phone number where you don't know beforehand how
>> many digits are the country code. I suppose you could call it alphabetical
>> order, only it's applied to digits instead of letters.
>> 
>> Björn Persson
>> 
>> -- 
>> 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 Free Edition.
>> Version: 7.1.362 / Virus Database: 267.13.4/176 - Release Date: 20/11/2005
>> 
>> 
>> 
>> 
>> -- 
>> No virus found in this outgoing message.
>> Checked by AVG Free Edition.
>> Version: 7.1.362 / Virus Database: 267.13.4/176 - Release Date: 20/11/2005
>> 
>> 
>> -- 
>> 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 Free Edition.
>> Version: 7.1.362 / Virus Database: 267.13.4/176 - Release Date: 20/11/2005
>> 
>> 
> 


# iso_country_list.sql
#
# This will create and then populate a MySQL table with a list of the names and
# ISO 3166 codes for countries in existence as of the date below.
#
# Usage:
#mysql -u username -ppassword database_name < ./iso_country_list.sql
#
# For updates to this file, see http://27.org/isocountrylist/
# For more about ISO 3166, see 
http://www.iso.ch/iso/en/prods-services/iso3166ma/02iso-3166-code-lists/list-en1.html
#
# Created by getisocountrylist.pl on Sun Nov  2 14:59:20 2003.
# Wm. Rhodes <[EMAIL PROTECTED]>
#

CREATE TABLE IF NOT EXISTS country (
  iso CHAR(2) NOT NULL PRIMARY KEY,
  name VARCHAR(80) NOT NULL,
  printable_name VARCHAR(80) NOT NULL,
  iso3 CHAR(3),
  numcode SMALLINT
);

INSERT INTO country VALUES ('AF','AFGHANISTAN','Afghanistan','AFG','004');
INSERT INTO country VALUES ('AL','ALBANIA','Albania','ALB','008');
INSERT INTO country VALUES ('DZ','ALGERIA','Algeria','DZA','012');
INSERT INTO country VALUES ('AS','AMERICAN SAMOA','American Samoa','ASM','016');
INSERT INTO country VALUES ('AD','ANDORRA','Andorra','AND','020');
INSERT INTO country VALUES ('AO','ANGOLA','Angola','AGO','024');
INSERT INTO country VALUES ('AI','ANGUILLA','Anguilla','AIA','660');
INSERT INTO country VALUES ('AQ','ANTARCTICA','Antarctica',NULL,NULL);
INSERT INTO country VALUES ('AG','ANTIGUA AND BARBUDA','Antigua and 
Barbuda','ATG','028');
INSERT INTO country VALUES ('AR','ARGENTINA','Argentina','ARG','032');
INSERT INTO country VALUES ('AM','ARMENIA','Armenia','ARM','051');
INSERT INTO country VALUES ('AW','ARUBA','Aruba','ABW','533');
INSERT INTO country VALUES ('AU','AUSTRALIA','Australia','AUS','036');
INSERT INTO country VALUES ('AT','AUSTRIA','Austria','AUT','040');
INSERT INTO country VALUES ('AZ','AZERBAIJAN','Azerbaijan','AZE','031');
INSERT INTO country VALUES ('BS','BAHAMAS','Bahamas','BHS','044');
INSERT INTO country VALUES ('BH','BAHRAIN','Bahrain','

Re: Country codes

[prathima rao]

do any one have ready made combo box with all the country codes present
- Original Message - 
From: "Rhino" <[EMAIL PROTECTED]>
To: "Björn Persson" <[EMAIL PROTECTED]>; 
Sent: 21/11/2005 6:18 AM
Subject: Re: Country codes


> I'm not saying it was an inappropriate way to present the data but it
> certainly was unexpected and, in my opinion, counter-intuitive. I didn't
> want the original poster to see page 3, see that his own country, Brazil,
> was missing, and dismiss the PDF as garbage.
>
> Rhino
>
> P.S. Sorry for top-posting but I can't intersperse normally with your
email.
>
>
> - Original Message - 
> From: "Björn Persson" <[EMAIL PROTECTED]>
> To: 
> Sent: Sunday, November 20, 2005 7:21 PM
> Subject: Re: Country codes
>
>
> Rhino wrote:
> > Be careful when reading those lists! When I looked at page 3 in the
> > English
> > PDF, it said it was in numeric order and the list showed all the
countries
> > that use country code 1, then country 20 (Egypt), then country 210
(spare)
> > *without* showing 55 (Brazil). Page 6 shows countries 500 through 509,
> > then
> > 51 through 58, and so on. Therefore, Brazil doesn't appear until Page 6.
> > They have obviously chosen to sort the list only on the FIRST DIGIT of
the
> > country code; that's a pretty odd form of numerical order, in my
opinion!
>
> That's not normal numerical order of course, but it's exactly the order
you
> need if you're parsing a phone number where you don't know beforehand how
> many digits are the country code. I suppose you could call it alphabetical
> order, only it's applied to digits instead of letters.
>
> Björn Persson
>
> -- 
> 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 Free Edition.
> Version: 7.1.362 / Virus Database: 267.13.4/176 - Release Date: 20/11/2005
>
>
>
>
> -- 
> No virus found in this outgoing message.
> Checked by AVG Free Edition.
> Version: 7.1.362 / Virus Database: 267.13.4/176 - Release Date: 20/11/2005
>
>
> -- 
> 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 Free Edition.
> Version: 7.1.362 / Virus Database: 267.13.4/176 - Release Date: 20/11/2005
>
>


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

Re: Tips for better performance

[Martijn Tonies]

> >> I have a table with 5,000,000 records that takes about 1 second
> >> to show the results.
> >
> > All rows?
>
> No. The queries return an average of 30 rows.
>
> I just wanted to say that the problem isn´t the table size, but the
> query X indexes used.

Right :-)

I just wanted to say that it's a weird statement that "results" are always
shown in about 1 second.

This depends on your queries as well :-)

With regards,

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com


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

Re: Tips for better performance

[Ronan Lucio]

Martijn,


I have a table with 5,000,000 records that takes about 1 second
to show the results.


All rows?


No. The queries return an average of 30 rows.

I just wanted to say that the problem isn´t the table size, but the
query X indexes used.

Ronan



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

Re: termcap functions library... configure: error: No curses/termcap library found

[Joerg Bruehe]

Hi Jörg, all!


Jörg Nowak wrote:

I try to run

./configure --with-ndbcluster --prefix=/STEFAN/mysql-5.1.2-binary 
--with-partition --with-row-based-replication --with-innodb 
--without-berkeley-db --enable-thread-safe-client --enable-shared

but it fails with

checking for termcap functions library... configure: error: No curses/termcap 
library found

I checked the /etc folder , there is a link to this library to
lrwxrwxrwx 1 root root 23 Mar 23 2005 termcap -> /usr/share/misc/termcap

and the file is there :
stefan:/usr/share/misc # ll ter*
-rw-r--r-- 1 root root 923665 Apr 6 2004 termcap


You do not say which platform you are using, all I can tell is that it 
is some unix-style system.
Well, it does not matter too much. I assume it is some Linux, you have 
to adapt my text to other platforms as needed.


On my machine (SuSE 9.1):
   [EMAIL PROTECTED]:~> file /usr/share/misc/termcap
   /usr/share/misc/termcap: ASCII text
Try "more /usr/share/misc/termcap" to see that this is no library, it is 
data (in plain ASCII text) describing TERMinal types and their CAPabilities.


Even if that were the library, it alone is not sufficient for compiling:



Do I need this somewhere else ?


Still on my machine:
   [EMAIL PROTECTED]:~> rpm -qa | egrep 'term|curses'
   terminfo-5.4-59
   xtermset-0.5.2-118
   ncurses-5.4-61.3
   ncurses-devel-5.4-61.3
   yast2-ncurses-2.9.26-0.3



What can I do ?


Install the "ncurses" and the "ncurses-devel" packages:
The first one contains the libraries (for linking and executing), the 
second the corresponding header files (for compiling).
You may already have the first one installed, as it also contains that 
"termcap" file.
The second one also contains manuals etc which you may not need, but the 
header files are essential for compilation.



HTH,
Jörg

--
Joerg Bruehe, Senior Production Engineer
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: Tips for better performance

[Martijn Tonies]

>
> I have a table with 5,000,000 records that takes about 1 second
> to show the results.

All rows?

> Perhaps your queries aren´t optimized properly.

With regards,

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com


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

Re: dump to more than 1 file

[Tom Brown]

The output of mysqldump is standard output, not a file.  You can pipe
it into another program, or redirect the output to a file, but
mysqldump does not make a file.  Therefore, there is no option in
mysqldump to make more than 1 file.

How is your database stored on disk?  The documentation Edwin pointed
to shows you how to dump individual tables, so dumping each table
separately might work.  Also, you can pipe your output into a
compression utility (bzip2 compresses text smaller than gzip).


OK seems like i will be using split for this as i can use

mysqldump -e -u user -ppass dbname | split -b 2000m

to get my db in 2 gig file(s)

I can then cat them together to put them back into the db

thanks



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

Re: Tips for better performance

[Ronan Lucio]

Marko,

I have a table with 5,000,000 records that takes about 1 second
to show the results.

Perhaps your queries aren´t optimized properly.

Ronan 




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

REITF?

[Ed Curtis]

 Does anyone on the list know what REITF stands for? I'm guessing it's
some type of data format for real estate information but I can't find any
information on it anywhere.

Thanks,

Ed



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

default character setting

[Mester József]

Hy
 
 I tried to set the default character set for mysql-4.1-15 server in my.cnf
 [client] section
 with set :
 character-sets-dir=/usr/local/mysql/share/mysql/charsets
 and 
 default_character_set=latin2
 
 but when I check the defaults by show variables like '%_server' 
 I get these value is latin1
 
 What is wrong? 
 
 Please help.
 
 Joe
 
 
 


-
 Yahoo! Model Search  - Could you be the next catwalk superstar? Check out the 
competition now 

Re: dump to more than 1 file

[Tom Brown]

The output of mysqldump is standard output, not a file.  You can pipe
it into another program, or redirect the output to a file, but
mysqldump does not make a file.  Therefore, there is no option in
mysqldump to make more than 1 file.

How is your database stored on disk?  The documentation Edwin pointed
to shows you how to dump individual tables, so dumping each table
separately might work.  Also, you can pipe your output into a
compression utility (bzip2 compresses text smaller than gzip).


yes thanks to you both - alas the majority of the data is made up of 
blobs in a single table to the select option around dates might work - 
Also i may try exporting into named pipes or some other such to get the 
data into more than 1 file


its times like this i wish mysqldump behaved more like exp

thanks


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

Re: UNIQUE constraint, proper use

[Martijn Tonies]

> I have this SQL statement:
>
> CREATE TABLE rooms (
>  idSERIAL,
>  room_name   TEXT UNIQUE,
>  location  TEXT,
>  last_updated  TIMESTAMP DEFAULT
> CURRENT_TIMESTAMP NOT NULL,
>  last_user_updatedTEXT,
>  PRIMARY KEY(id)
> );
>
> When I run this I get the following error:
> ERROR 1170 (42000): BLOB/TEXT column 'room_name' used in key
> specification without a key length
>
> I'm more used to PostgreSQL and when I run this command there, I don't
> get this error message. I recognized that by changing room_name to a
> varchar type, I won't get the error message.

TEXT in MySQL, is a specific BLOB-type column. It isn't able to guarantee
uniqueness on the full blob. I wonder if that is what you want.

VARCHAR is not a blob and can be indexed (a unique constraint creates
an index automatically).

With regards,

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com


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

Re: SSH tunnel for Mysql

[Dan Bolser]

Jerry Swanson wrote:

Both Linux computers.



This is what I do...

$ more ssh_to_mysql_on_beta
#!/bin/sh

ssh \
 -t \
 -g \
 -L 3306:beta.domain.ac.uk:3306 \
 [EMAIL PROTECTED] \
 "./bin/sleep.plx mysql"

That last line is a trick to prevent the terminal from 'timeing out'. 
The sleep.plx application is just a little perl script which writes 
"sleeping $ARGV[0]\n" every 5 minuites.


Running the above on my local machine (linux) allows me to connect to 
mysql on 'local' port 3306, and have that magically redirected via ssh 
to beta port 3306.


Dan.

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

Re: dump to more than 1 file

[Steve Edberg]

At 3:56 PM + 11/21/05, Tom Brown wrote:
is it possible to do a mysql dump to more than 1 file? We will 
shortly be needing to dump a db that will be in excess of 50gb so 
will encounter file size issues


This is on 4.1.x and rhel 4



Probably the best approach - knowing nothing about your db - would be 
to dump tables to separate files; you could write a pretty simple 
script to do that.


Since mysqldump writes to stdout, you could pipe to a zip/bzip/gzip, 
although that's unlikely to compress 50GB down to something most 
unixes can handle (a safe size is 2GB):


mysqldump -uuser -p database | gzip > dump.gz

You could pipe to split (try 'man split'), which would split the 
output into pieces by # of lines or # of bytes (eg; dump.001, 
dump.002, ...) and then reassemble via cat. It would be nice to do 
something like


mysql -uuser -ppassword database < `cat dump.*`

but I don't think that's possible. You'd have to reassemble the dump 
file first, which means you might run into file size issues again. 
Probably best to do table-by-table, piping to zip/bzip/gzip as well.


steve

--
+--- my people are the people of the dessert, ---+
| Steve Edberghttp://pgfsun.ucdavis.edu/ |
| UC Davis Genome Center[EMAIL PROTECTED] |
| Bioinformatics programming/database/sysadmin (530)754-9127 |
+ said t e lawrence, picking up his fork +

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

Re: Tips for better performance

[Duncan Hill]

On Tuesday 22 Nov 2005 10:02, Marko Knezevic wrote:
> > See below, and check that the indexes are right, or that the queries
> > aren't
> > negating the indexes.  I can run queries against a properly indexed table
> > of
> > 26 million rows in less than a second on lesser hardware (ok, so the SAN
> > isn't lesser).  The manual also contains a lot of information on how to
> > tune
> > MySQL for large memory environments - the default configuration is for <
> > 256
> > MB I think.#
>
> Thank you Duncan and Scott, we are using MySQL v.4.1.12. Table type is
> MyISAM and following lines are inside my.cnf file:

$ locate my-huge.cnf

Read that file - it comes in the MySQL distribution, and is for 1 - 2 GB RAM 
servers.

One generic rule that you can try to apply is to split the data spindles from 
your OS spindles - so your tables reside on the high speed drives, and your 
OS, query logs etc all reside on another drive (and even another controller).

The other rule is that spindles are good.  Multiple 18 gig 10K drives will 
give you better performance than those 300 gig drives.  One of the servers I 
deal with has ~270 GB of storage, but it's in 74 GB drives in RAID 1+0 in an 
IBM DS4300.  Expensive, but more spindles and more read heads to retrieve the 
data.

Scanned by mailCritical.

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

Re: Tips for better performance

[Marko Knezevic]

See below, and check that the indexes are right, or that the queries 
aren't
negating the indexes.  I can run queries against a properly indexed table 
of

26 million rows in less than a second on lesser hardware (ok, so the SAN
isn't lesser).  The manual also contains a lot of information on how to 
tune
MySQL for large memory environments - the default configuration is for < 
256

MB I think.#


Thank you Duncan and Scott, we are using MySQL v.4.1.12. Table type is 
MyISAM and following lines are inside my.cnf file:


Regards
Marko

skip-innodb
datadir=/var/lib/mysql
#socket=/var/lib/mysql/mysql.sock
## Default to using old password format for compatibility with mysql 3.x
## clients (those using the mysqlclient10 compatibility package).
old_passwords=1

port= 3306
socket  = /var/lib/mysql/mysql.sock
skip-locking
max_allowed_packet = 2M
table_cache = 1024
# po klijentu
sort_buffer_size = 4M
read_buffer_size = 4M
read_rnd_buffer_size = 2M

log = /var/log/queriji.log

myisam_sort_buffer_size = 128M
thread_cache = 128
key_buffer = 512M
query_cache_limit = 2M
query_cache_size = 512M
#query_cache_type = 1
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 4


thread_cache_size = 120

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

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

[client]
#password   = your_password
port= 3306
socket  = /var/lib/mysql/mysql.sock

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[isamchk]
key_buffer = 256M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 256M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout 



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

Re: Tips for better performance

[Scott Haneda]

on 11/22/05 1:24 AM, Marko Knezevic at [EMAIL PROTECTED] wrote:

> We've been testing the system with 100 test records to see how it will
> work when doing selects from db and we are pretty disappointed with the
> results. Some selects needs around 10 seconds to finish, some take even
> longer.

Maybe you are running the default my.cnf file, if so, you will want to
change that to be optimized.  Please report back your current cnf file.
-- 
-
Scott HanedaTel: 415.898.2602
 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]

Re: UNIQUE constraint, proper use

[Harald Fuchs]

In article <[EMAIL PROTECTED]>,
Ferindo Middleton Jr <[EMAIL PROTECTED]> writes:

> I have this SQL statement:
> CREATE TABLE rooms (
>  idSERIAL,
>  room_name   TEXT UNIQUE,
>  location  TEXT,
>  last_updated  TIMESTAMP DEFAULT
> CURRENT_TIMESTAMP NOT NULL,
>  last_user_updatedTEXT,
>  PRIMARY KEY(id)
> );

> When I run this I get the following error:
> ERROR 1170 (42000): BLOB/TEXT column 'room_name' used in key
> specification without a key length

MySQL can index only the first N chars of a BLOB or TEXT column; you
must supply the value of N.

> I'm more used to PostgreSQL and when I run this command there, I don't
> get this error message.

Even in PostgreSQL it's not always beneficial to put a long bytea
column into an index.  Can't you use a surrogate key?


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

trouble with kswapd

[Thomas Böhme]

Hello,

I run MySQL-server-pro-gpl-4.1.10a-0.sles9 on two AMD Opteron dual cpu systems 
with 16GB RAM. Due to some hardware problems the mainboard of a system was 
replaced with a new one. The kernel is the original one from SuSE version 
kernel-smp-2.6.5-7.151.

The system runs MySQL replication as slave. The problem is that when mysql is 
started the kswapd1 process runs often with very high cpu load as well as 
mysqld. If mysqld does not run then anything is fine.

 top - 09:04:21 up  7:03,  2 users,  load average: 0.53, 0.52, 1.07
Tasks:  52 total,   4 running,  48 sleeping,   0 stopped,   0 zombie
 Cpu0 : 79.0% us,  1.0% sy,  0.0% ni, 16.0% id,  4.0% wa,  0.0% hi,  0.0% si
 Cpu1 : 14.1% us, 85.9% sy,  0.0% ni,  0.0% id,  0.0% wa,  0.0% hi,  0.0% si
Mem:  16357716k total, 16308892k used,48824k free,   172908k buffers
Swap:0k total,0k used,0k free,  5969556k cached

  PID USER  PR  NI  VIRT  RES  SHR S %CPU %MEM SWAPTIME+  COMMAND
   16 root  16   0 000 R 83.8  0.00 139:55.31 kswapd1
 6854 mysql 16   0 9833m 9.4g 6032 S 92.7 60.5 164m  57:43.60 mysqld
 6913 root  16   0 40224 2052 1404 S  0.0  0.0  37m   0:33.51 sshd
   17 root  15   0 000 S  1.0  0.00   0:19.70 kswapd0
   15 root  15   0 000 S  0.0  0.00   0:16.58 pdflush
1 root  16   0   640   92   48 S  0.0  0.0  548   0:06.66 init
 6729 root  15   0 40224 2032 1400 R  0.0  0.0  37m   0:06.24 sshd
 2940 root  16   0 26760 1596 1124 S  0.0  0.0  24m   0:00.45 sshd
 3949 root  18   0  6984  788  660 S  0.0  0.0 6196   0:00.23 mingetty
  639 root  25   0 000 S  0.0  0.00   0:00.19 kseriod
 2499 root  16   0  3540  384  260 S  0.0  0.0 3156   0:00.17 syslogd
 2674 root  16   0 20008 1168  728 S  0.0  0.0  18m   0:00.14 master
 3952 root  19   0  6984  788  660 S  0.0  0.0 6196   0:00.14 mingetty
 1321 root  16   0  2452  288  204 S  0.0  0.0 2164   0:00.13 irqbalance
 3243 ntp   15   0 12252 3760 2156 S  0.0  0.0 8492   0:00.12 ntpd
 2703 postfix   16   0 20116 1264  808 S  0.0  0.0  18m   0:00.11 qmgr
 3953 root  20   0  6984  788  660 S  0.0  0.0 6196   0:00.10 mingetty
7 root   5 -10 000 S  0.0  0.00   0:00.08 events/1
 2544 daemon16   0 12640 1040  696 S  0.0  0.0  11m   0:00.08 slpd

The swap is turned off since 'swapon -s' doesn't show anything.

When the system runs as replication master node and thus handling all select 
statments and data imports the cpu usage for mysqld and kswapd is almost at 
100%. If swap is on then the 1GB swap device is completely used after a few 
seconds and the problem ae the same.

The other machine with the same hardware and software configuration has no 
problems - mysqld shows normal cpu usage and kswapd does nothing when swap is 
turned off.

I've had a look into the bios configuration but found nothing unusual. Has 
anyone an idea? And - well I know this is the wrong list to ask this question - 
why kswapd is running when no swap device is configured and why there are some 
processes using swap memory (see top output)?


Kind regards,
Thomas


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

Re: Tips for better performance

[Duncan Hill]

On Tuesday 22 Nov 2005 09:24, Marko Knezevic wrote:

> is running on dedicated server which consists of two Xeons @ 3 Ghz, 2 GB
> RAM and two 150 GB disks @ 1 rpm running under RAID 1 controller. OS is
> CentOS 4.2

A very recent article that crossed my eyes indicates that in certain cases, HT 
can decrease performance of certain server types (SQL Server (MS) in 
particular).  It may be worth testing this.

> results. Some selects needs around 10 seconds to finish, some take even
> longer. We didn't do any MySQL optimizations, so my question is how to
> optimize MySQL for such big amount of data. If it would be more convenient
> we could even provide SSH acces to our server.

See below, and check that the indexes are right, or that the queries aren't 
negating the indexes.  I can run queries against a properly indexed table of 
26 million rows in less than a second on lesser hardware (ok, so the SAN 
isn't lesser).  The manual also contains a lot of information on how to tune 
MySQL for large memory environments - the default configuration is for < 256 
MB I think.#

> I would like to send you queries in question but these queries are in
> SugarCRM. Is there any tool that logs every query that comes to MySQL so i
> can examine these?

MySQL can log every query, and all slow queries.  You haven't said what 
version of MySQL, nor what table type you're using, however:
http://dev.mysql.com/doc/refman/4.1/en/log-files.html
should be a useful start.

Scanned by mailCritical.

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

Tips for better performance

[Marko Knezevic]

Good morning everybody!

We are working on a big project involving SugarCRM and in the end it will 
contain around 1 000 000 records per table in MySQL database. Whole system 
is running on dedicated server which consists of two Xeons @ 3 Ghz, 2 GB RAM 
and two 150 GB disks @ 1 rpm running under RAID 1 controller. OS is 
CentOS 4.2


We've been testing the system with 100 test records to see how it will 
work when doing selects from db and we are pretty disappointed with the 
results. Some selects needs around 10 seconds to finish, some take even 
longer. We didn't do any MySQL optimizations, so my question is how to 
optimize MySQL for such big amount of data. If it would be more convenient 
we could even provide SSH acces to our server.


I would like to send you queries in question but these queries are in 
SugarCRM. Is there any tool that logs every query that comes to MySQL so i 
can examine these?


Best regards,
Marko 



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

Re: Perfomance / Optimize ( Full Table Scans ) and Help with my.cnf

[ESV Media GmbH]

Thanks for your answer.
I´m alreay logging slow queries, i gather no results.
But when I take a look with the "MySQL Administrator" Tool
i see that there are many full table scans.
I want to discover which queries produce this full table scans.
I´ve read that in MySQL 5 full table scans are logged in the slow query 
log as well,

but where can i find them in my version MySQL 4.1.13 ?

Cheers

Marco



sheeri kritzer wrote:


I suggest checking out your slow query logs:

http://dev.mysql.com/doc/refman/5.0/en/slow-query-log.html

the queries are logged, so going into a client and running an explain
on the slow queries should help you out.

-Sheeri

On 11/21/05, ESV Media GmbH <[EMAIL PROTECTED]> wrote:
 


Hey, we´ve a site ( PHP ) where several 1.000 are online at the same time.
They´re running many sql statements.
Is there a way to find out which statements take a full table scan to
optimize them ?
We´ve a cpu percentage of nearly 50 % with only 30 Members online.
And i´ve seen that there are some full table scans.
Where can i investigate them ?

And i need some help with configuring the my.cnf.
Below you´ll find our one.
Which options should i optimize ? We´ve much free memory but our cpu is
working to half of the full capacity...

uname -a :

Linux m30s06db.ispgateway.de 2.4.29-grsec #10 SMP Mon Jul 4 14:26:46
CEST 2005 i686 Intel(R) Pentium(R) 4 CPU 3.00GHz GenuineIntel GNU/Linux

And thats the my.cnf :

# Example MySQL config file for medium systems.
#
# This is for a system with little memory (32M - 64M) where MySQL plays
# an important part, or systems up to 128M where MySQL is used together
with
# other programs (such as a web server)
#
# You can copy this file to
# /etc/my.cnf to set global options,
# mysql-data-dir/my.cnf to set server-specific options (in this
# installation this directory is
/kunden/106120_40670/ms_appl/mysql_4.1.10a/var) or
# ~/.my.cnf to set user-specific options.
#
# In this file, you can use all long options that a program supports.
# If you want to know which options a program supports, run the program
# with the "--help" option.

# The following options will be passed to all MySQL clients
[client]
#password = your_password
port = 3307
socket = /tmp/mysql.sock

# Here follows entries for some specific programs

# The MySQL server
[mysqld]
port = 3307
socket = /tmp/mysql.sock
skip-locking
key_buffer = 64M
max_allowed_packet = 1M
table_cache = 5M
sort_buffer_size = 1M
net_buffer_length = 8K
read_buffer_size = 1M
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 16M
record_buffer=1M
log-slow-queries
long_query_time = 3

# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (via the "enable-named-pipe" option) will render mysqld useless!
#
#
#skip-networking

# Replication Master Server (default)
# binary logging is required for replication
log-bin

# required unique id between 1 and 232 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id = 1

# Replication Slave (comment out master section to use this)
#
# To configure this host as a replication slave, you can choose between
# two methods :
#
# 1) Use the CHANGE MASTER TO command (fully described in our manual) -
# the syntax is:
#
# CHANGE MASTER TO MASTER_HOST=, MASTER_PORT=,
# MASTER_USER=, MASTER_PASSWORD= ;
#
# where you replace , ,  by quoted strings and
#  by the master's port number (3306 by default).
#
# Example:
#
# CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306,
# MASTER_USER='joe', MASTER_PASSWORD='secret';
#
# OR
#
# 2) Set the variables below. However, in case you choose this method, then
# start replication for the first time (even unsuccessfully, for example
# if you mistyped the password in master-password and the slave fails to
# connect), the slave will create a master.info file, and any later
# change in this file to the variables' values below will be ignored and
# overridden by the content of the master.info file, unless you shutdown
# the slave server, delete master.info and restart the slaver server.
# For that reason, you may want to leave the lines below untouched
# (commented) and instead use CHANGE MASTER TO (see above)
#
# required unique id between 2 and 232 - 1
# (and different from the master)
# defaults to 2 if master-host is set
# but will not function as a slave if omitted
#server-id = 2
#
# The replication master for this slave - required
#master-host = 
#
# The username the slave will use for authentication when connecting
# to the master - required
#master-user = 
#
# The password the slave will authenticate with when connecting to
# the master - required
#master-password = 
#
# The port the master is listening on.
# optional - defaults to 3306
#master-port = 
#
# binary logging - 

Re: trouble with deleting a user

[ali asghar torabi parizy]

Note: forwarded message attached.
		 Yahoo! FareChase - Search multiple travel sites in one click.

 

 --- Begin Message ---
Michael Stassen <[EMAIL PROTECTED]> wrote: ali asghar torabi parizy wrote:>  hi to all>  i have a problem when i trying to delet a user by drop user directive>  ***>  drop user atp;>  Can't drop one or more of the requested users>  ***>  i add this user by crant:>  ***>  GRANT ALL PRIVILEGES ON *.* TO 'atp'@'%'  IDENTIFIED BY 'nowayforyou' WITH GRANT OPTION;>  ***A user in mysql is the combination of username and hostname, so the user you created is 'atp'@'%'.  Hence, you need to   DROP USER 'atp'@'%';Michael  hi michael i try to do it but it impossible. error
 continued when i implement it :( drop user [EMAIL PROTECTED]; Can't drop one or more of the requested users 
		 Yahoo! FareChase - Search multiple travel sites in one click.

 

 --- End Message ---

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