Slow query: optimizer ignores index, using filesort

[Scott Gifford]

I'm having a hard time figuring out why a query in my application is
slow.  It seems that MySQL is using a filesort where it's not
necessary, and as a result a query that should be taking a fraction of
a second is taking up to 10 seconds.

Essentially, the query is doing a join of 4 tables, two of which may
not have a matching row (and so must use an explicit LEFT JOIN).  All
tables have a common mls_num primary key, which is the column used
for the join.  The results are sorted by mls_num, and only the first
row is requested.

I've whittled the query down to just a few lines that reproduce the
problem:

mysql explain 
  SELECT mirealsource_homes.mls_num, 
 mirealsource_homes_supplemental.listdate,
 mirealsource_images.image1,
 mirealsource_homes_stats.detail_views
FROM mirealsource_homes, 
 mirealsource_homes_supplemental 
 LEFT JOIN mirealsource_images 
   ON mirealsource_homes.mls_num =
  mirealsource_images.mls_num
 LEFT JOIN mirealsource_homes_stats
   ON mirealsource_homes.mls_num =
  mirealsource_homes_stats.mls_num
   WHERE 
 mirealsource_homes.mls_num =
  mirealsource_homes_supplemental.mls_num
   ORDER BY mirealsource_homes.mls_num
   LIMIT 1;

++-+-++---+-+-+---+--+-+
| id | select_type | table   | type   |
possible_keys | key | key_len | ref
| rows | Extra|
++-+-++---+-+-+---+--+-+
|  1 | SIMPLE  | mirealsource_homes_supplemental | ALL|
PRIMARY   | NULL|NULL | NULL
|  100 | Using temporary; Using filesort |
|  1 | SIMPLE  | mirealsource_homes  | eq_ref |
PRIMARY   | PRIMARY |   8 |
devel.mirealsource_homes_supplemental.mls_num |1 ||
|  1 | SIMPLE  | mirealsource_images | eq_ref |
PRIMARY   | PRIMARY |   8 | devel.mirealsource_homes.mls_num
|1 ||
|  1 | SIMPLE  | mirealsource_homes_stats| eq_ref |
PRIMARY   | PRIMARY |   8 | devel.mirealsource_homes.mls_num
|1 ||
++-+-++---+-+-+---+--+-+

All tables are indexed by primary key (as can be seen from the
execution plan).  The column is declared as CHAR(8), though it is
VARCHAR(8) in some tables that have other VARCHAR columns.  Tables are
MyISAM, and the server version is 4.1.7-standard.

Apparently MySQL's optimizer sees that it can use the primary key for
mirealsource_home_supplemental to do the query, but for some reason
decides not to.

I had a similar problem a few months ago, and it was solved when I
upgraded from a very old 3.x version of MySQL to this one.

I can fix the problem by requiring a STRAIGHT_JOIN, or by sorting by
mirealsource_homes_supplemental.mls_num instead.  However, this
query is part of a larger framework that handles a wide variety of
queries, so I need to understand why this is happening instead of
tweaking individual cases.

Any idea why this might be happening, and how to fix it?

Thanks!

ScottG.

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

Re: Slow query: optimizer ignores index, using filesort

[Jigal van Hemert]

From: Scott Gifford

 mysql explain
   SELECT mirealsource_homes.mls_num,
  mirealsource_homes_supplemental.listdate,
  mirealsource_images.image1,
  mirealsource_homes_stats.detail_views
 FROM mirealsource_homes,
  mirealsource_homes_supplemental
  LEFT JOIN mirealsource_images
ON mirealsource_homes.mls_num =
   mirealsource_images.mls_num
  LEFT JOIN mirealsource_homes_stats
ON mirealsource_homes.mls_num =
   mirealsource_homes_stats.mls_num
WHERE
  mirealsource_homes.mls_num =
   mirealsource_homes_supplemental.mls_num
ORDER BY mirealsource_homes.mls_num
LIMIT 1;


++-+-++-
--+-+-+---+-
-+-+
 | id | select_type | table   | type   |
 possible_keys | key | key_len | ref
 | rows | Extra|

++-+-++-
--+-+-+---+-
-+-+
 |  1 | SIMPLE  | mirealsource_homes_supplemental | ALL|
 PRIMARY   | NULL|NULL | NULL
 |  100 | Using temporary; Using filesort |
 |  1 | SIMPLE  | mirealsource_homes  | eq_ref |
 PRIMARY   | PRIMARY |   8 |
 devel.mirealsource_homes_supplemental.mls_num |1 ||
 |  1 | SIMPLE  | mirealsource_images | eq_ref |
 PRIMARY   | PRIMARY |   8 | devel.mirealsource_homes.mls_num
 |1 ||
 |  1 | SIMPLE  | mirealsource_homes_stats| eq_ref |
 PRIMARY   | PRIMARY |   8 | devel.mirealsource_homes.mls_num
 |1 ||

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

 Apparently MySQL's optimizer sees that it can use the primary key for
 mirealsource_home_supplemental to do the query, but for some reason
 decides not to.

This is often the case when the query will probably return more than 30% of
the records in that table. In such cases it is more efficient to do a full
table scan (which is indicated here by the 'ALL' type in the explain
output).

 I can fix the problem by requiring a STRAIGHT_JOIN, or by sorting by
 mirealsource_homes_supplemental.mls_num instead.  However, this
 query is part of a larger framework that handles a wide variety of
 queries, so I need to understand why this is happening instead of
 tweaking individual cases.

Furthermore MySQL can only use an index for sorting if all columns in the
ORDER BY clause are from the first table in the explain output that doesn't
have a 'const' join type. This is why setting the ORDER BY to
mirealsource_homes_supplemental.mls_num will remove the 'Using filesort' and
result in faster sorting.

MySQL tries to optimize queries by (among others) guestimating which path
will result in the smallest number of records. It appears that this path
with 100 * 8 * 8 * 8 (51200) records is the minimum size it can achieve.
You can use USE INDEX to (strongly) suggest the use of an index to MySQL and
see if it speeds up the query. Using  FORCE INDEX will tell MySQL that a
full table scan is very expensive, so this will make it extremely unlikely
that it will not use the index.

The optimizer often seems to gues 'wrong' in terms of speed. I put a USE
INDEX in a query and consequently the guessed number of records was
increased from around 1000 to over 2000, but since it could now use the
index to sort the result set the query was way faster in the end.

In this query you want the data where mls_num is as small as possible. Is
there a way you can limit the number of records by using an extra where
condition? This way you may change the order of the tables and make the
query faster.

I would also move the current WHERE condition to an ON condition in the FROM
part as it is not meant to limit the selected records, but as a definition
on how to join the two tables.

Regards, Jigal.


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

Optimises LEFT JOIN

[Gyurasits Zoltn]

Hello All!

I have a speed problem with LEFT JOIN condition.

SELECT *
FROM t1
INNER JOIN t2 ON t2.c_ID=t1.ID
WHERE 

The table2 row count is high. (100 000 records)  
This is correct and FAST (1-2 sec), but not good for me. Because I need all 
record from table1. 

I use this...

SELECT *
FROM t1
LEFT JOIN t2 ON t2.c_ID=t1.ID
WHERE 

But this is very slow... (10 minutes)  :(((

Why?? Somebody can help me?  Tnx!!

Best Regards!  
Zoltn Gyurasits

Re: Optimises LEFT JOIN

[Jigal van Hemert]

From: Gyurasits Zoltn

 I have a speed problem with LEFT JOIN condition.
(...)
 Why?? Somebody can help me?  Tnx!!

You will probably get a lot more useful response if you include the table
structure, the complete query/queries and the output of EXPLAIN for each
query (just put EXPLAIN in front of the SELECT. It will give you information
about the execution path that MySQL came up with).

Regards, Jigal.


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

Re: Thank you for delivery

[luuk]

The message contains Unicode characters and has been sent as a binary 
attachment.



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

Re: Optimises LEFT JOIN

[mfatene]

Hi zoltan
if you know difference between inner join and left join, you can conclude than
maybe there are a lot of rows in table1 which do not verify you join condition.

This example show you that the result is different and and add'in where clause
if you want to transform the left join result like then inner join one :

mysql select * from a;
+--+-+
| id   | txt |
+--+-+
|1 | a test1 |
|2 | a test2 |
|3 | a test3 |
+--+-+
3 rows in set (0.03 sec)

mysql select * from b;
+--+-+
| id   | txt |
+--+-+
|1 | b test1 |
|2 | b test2 |
+--+-+
2 rows in set (0.00 sec)

mysql
mysql
mysql select * from a inner join b on a.id=b.id;
+--+-+--+-+
| id   | txt | id   | txt |
+--+-+--+-+
|1 | a test1 |1 | b test1 |
|2 | a test2 |2 | b test2 |
+--+-+--+-+
2 rows in set (0.00 sec)

mysql select * from a left join b on a.id=b.id;
+--+-+--+-+
| id   | txt | id   | txt |
+--+-+--+-+
|1 | a test1 |1 | b test1 |
|2 | a test2 |2 | b test2 |
|3 | a test3 | NULL | NULL|
+--+-+--+-+
3 rows in set (0.00 sec)

mysql
mysql
mysql select * from a left join b on a.id=b.id where b.txt is not null;
+--+-+--+-+
| id   | txt | id   | txt |
+--+-+--+-+
|1 | a test1 |1 | b test1 |
|2 | a test2 |2 | b test2 |
+--+-+--+-+
2 rows in set (0.00 sec)

Hope that helps.

Mathias


Selon Jigal van Hemert [EMAIL PROTECTED]:

 From: Gyurasits Zoltán

  I have a speed problem with LEFT JOIN condition.
 (...)
  Why?? Somebody can help me?  Tnx!!

 You will probably get a lot more useful response if you include the table
 structure, the complete query/queries and the output of EXPLAIN for each
 query (just put EXPLAIN in front of the SELECT. It will give you information
 about the execution path that MySQL came up with).

 Regards, Jigal.


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





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

Re: Database Table Date Assistance

[mfatene]

Hi scott,
you can use datetime or timestamp. Using timestamp in this example shows you
that now() can be a default or inserted value. You can also use
current_tiumestamp.

Other functions like date_add can help you to look for rows verifying interval
days like in this example :

mysql create table dates(id int, d timestamp default now());
Query OK, 0 rows affected (0.06 sec)
mysql insert into dates(id) values(1);
Query OK, 1 row affected (0.03 sec)
mysql insert into dates(id) values(2);
Query OK, 1 row affected (0.02 sec)
mysql select * from dates;
+--+-+
| id   | d   |
+--+-+
|1 | 2005-06-13 10:22:47 |
|2 | 2005-06-13 10:22:50 |
+--+-+
2 rows in set (0.00 sec)

mysql select date_add(d,interval 2 day) from dates;
++
| date_add(d,interval 2 day) |
++
| 2005-06-15 10:22:47|
| 2005-06-15 10:22:50|
++
2 rows in set (0.05 sec)


Clearer info in dev.mysql.com/doc about timestamp and datetime.

mysql hope that helps.


Mathias










Selon Scott Purcell [EMAIL PROTECTED]:

 Hello,

 I would like to do the following: I am creating a site where people can add
 items to a cart. In order to keep items for [X] amount of days, I would like
 to create a database table in mysql to hold a 'itemRef' and a 'Date'. Then in
 a relationship table I can hold the 'itemRef' and 'items' they have choosen.
 I think this would be simple.

 But there are a lot of choices for the date field. I would like a date field
 that I can insert a now() or something, when I insert. And then later,
 through Java code, query and find all dates that are greater than [X] amount
 of days, and delete them to keep the database clean?

 So my question would be,

 A) which date type field should I create.
 B) how to insert now()
 C) can I run one query to find out if the date field is greater than [X]
 days?

 Any help would be appreciated.
 Sincerely
 Scott


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





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

Re: Optimises LEFT JOIN

[Gyurasits Zoltán]

Hello!

Sorry for the incomplete information...

The complete select is:

SELECT
 lt.cikk_ID,  SUM(bt.mennyiseg)
FROM traktar_leltartetelek_new lt
LEFT JOIN traktar_mozgas_bizonylat_cikk bt ON bt.cikk_ID=lt.cikk_ID and 
bt.raktar_ID=01

WHERE lt.leltar_ID=000
GROUP BY lt.cikk_ID

And table structure:

traktar_leltartetelek_new
-
Field Type Null Key Default Extra
ID char(18)  PRI
leltar_ID char(18)
cikk_ID char(18)  MUL
szamolt_keszlet double YES  NULL

traktar_mozgas_bizonylat_cikk
-
Field Type Null Key Default Extra
ID varchar(18)  PRI
biz_ID varchar(18)  MUL
cikk_ID varchar(18)
raktar_ID varchar(18)
mennyiseg double YES  NULL


Name Type Row_format Rows
traktar_leltartetelek_new MyISAM Fixed 2334
traktar_mozgas_bizonylat_cikk MyISAM Dynamic 78483

Best Regards!
Zoltn

- Original Message - 
From: Jigal van Hemert [EMAIL PROTECTED]

To: Gyurasits Zoltn [EMAIL PROTECTED]; mysql@lists.mysql.com
Sent: Monday, June 13, 2005 10:01 AM
Subject: Re: Optimises LEFT JOIN



From: Gyurasits Zoltn


I have a speed problem with LEFT JOIN condition.

(...)

Why?? Somebody can help me?  Tnx!!


You will probably get a lot more useful response if you include the table
structure, the complete query/queries and the output of EXPLAIN for each
query (just put EXPLAIN in front of the SELECT. It will give you 
information

about the execution path that MySQL came up with).

Regards, Jigal.


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




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

Re: mysql decrypt

[mfatene]

Hi,
Have you tried this ? i said the length, so you can rise it until working for
tests. I used 32k and it works without any problem. I can remind that with a
blob it works also :

mysql create table t1( asunto varbinary(32000), asun varbinary(16) );
Query OK, 0 rows affected, 1 warning (0.06 sec)

mysql insert into t1 values(aes_encrypt('aNuevo1','asunto'), 'aNuevo1');
Query OK, 1 row affected (0.03 sec)

mysql select aes_decrypt(asunto,'asunto') as decrypted, asun from t1;
+---+-+
| decrypted | asun|
+---+-+
| aNuevo1   | aNuevo1 |
+---+-+
1 row in set (0.00 sec)

* But with a small length :
mysql create table t1( asunto varbinary(16), asun varbinary(16) );
Query OK, 0 rows affected (0.08 sec)

mysql insert into t1 values(aes_encrypt('aNuevo1','asunto'), 'aNuevo1');
Query OK, 1 row affected (0.03 sec)

mysql select aes_decrypt(asunto,'asunto') as decrypted, asun from t1;
+---+-+
| decrypted | asun|
+---+-+
| NULL  | aNuevo1 |
+---+-+
1 row in set (0.00 sec)

That's why.

Hope that helps

Mathias


Selon Alejandro Alekhine [EMAIL PROTECTED]:

 Ok, if you´re right ... how can I repair the incorrect padding ?? Because I
 think data is correct. It fully respects so fields´ length so as
 fields´types.

 Thanks

 From: [EMAIL PROTECTED]
 To: Alejandro Alekhine [EMAIL PROTECTED]
 CC: mysql@lists.mysql.com
 Subject: Re: mysql decrypt
 Date: Tue, 17 May 2005 00:30:55 +0200
 
 Hi,
 You may have invalid data or incorrect padding when null is retuned :
 
 http://dev.mysql.com/doc/mysql/en/encryption-functions.html
 
 Mathias
 
 Selon Alejandro Alekhine [EMAIL PROTECTED]:
 
   Hi, I´m developing a database with the aes_encrypt and aes_decrypt
   functions, with integrity constraints and varbinary types.
  
   My problem is that when I insert a row into a table, I encrypt with
   aes_encrypt, but when I desencrypt with aes_decrypt, it returns null
 with
   some values. The only way I've found is using a blob type instead of
   varbinary, but this type doesn't support integrity constraints.
  
   For example,
  
   create table t1( asunto varbinary(16), asun varbinary(16) );
  
   insert into t1 values(aes_encrypt('aNuevo1','asunto'), 'aNuevo1');
  
   select aes_decrypt(asunto,'asunto') as decrypted, asun from t1;
  
   The result is decrypted=NULL and asun='aNuevo1'
  
   Why ??? The length of the fields is correct, I don´t know why. But if I
   change varbinary by blob, it runs. But with blob I can´t do any
 integrity
   constraint.
  
   Thanks
  
  
  
  
 
 
 
 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
 






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

Re: Optimises LEFT JOIN

[Gyurasits Zoltán]

Hello Mathias!

I know what is the difference between LEFT and INNER JOIN.
I need the NULL records too.


mysql select * from a left join b on a.id=b.id;
+--+-+--+-+
| id   | txt | id   | txt |
+--+-+--+-+
|1 | a test1 |1 | b test1 |
|2 | a test2 |2 | b test2 |
|3 | a test3 | NULL | NULL|
+--+-+--+-+

That is OK, but very slow.. :(

Best Regards!
Zoltán


- Original Message - 
From: [EMAIL PROTECTED]

To: Jigal van Hemert [EMAIL PROTECTED]
Cc: Gyurasits Zoltán [EMAIL PROTECTED]; mysql@lists.mysql.com
Sent: Monday, June 13, 2005 10:21 AM
Subject: Re: Optimises LEFT JOIN



Hi zoltan
if you know difference between inner join and left join, you can conclude 
than
maybe there are a lot of rows in table1 which do not verify you join 
condition.


This example show you that the result is different and and add'in where 
clause

if you want to transform the left join result like then inner join one :

mysql select * from a;
+--+-+
| id   | txt |
+--+-+
|1 | a test1 |
|2 | a test2 |
|3 | a test3 |
+--+-+
3 rows in set (0.03 sec)

mysql select * from b;
+--+-+
| id   | txt |
+--+-+
|1 | b test1 |
|2 | b test2 |
+--+-+
2 rows in set (0.00 sec)

mysql
mysql
mysql select * from a inner join b on a.id=b.id;
+--+-+--+-+
| id   | txt | id   | txt |
+--+-+--+-+
|1 | a test1 |1 | b test1 |
|2 | a test2 |2 | b test2 |
+--+-+--+-+
2 rows in set (0.00 sec)

mysql select * from a left join b on a.id=b.id;
+--+-+--+-+
| id   | txt | id   | txt |
+--+-+--+-+
|1 | a test1 |1 | b test1 |
|2 | a test2 |2 | b test2 |
|3 | a test3 | NULL | NULL|
+--+-+--+-+
3 rows in set (0.00 sec)

mysql
mysql
mysql select * from a left join b on a.id=b.id where b.txt is not null;
+--+-+--+-+
| id   | txt | id   | txt |
+--+-+--+-+
|1 | a test1 |1 | b test1 |
|2 | a test2 |2 | b test2 |
+--+-+--+-+
2 rows in set (0.00 sec)

Hope that helps.

Mathias


Selon Jigal van Hemert [EMAIL PROTECTED]:


From: Gyurasits Zoltán

 I have a speed problem with LEFT JOIN condition.
(...)
 Why?? Somebody can help me?  Tnx!!

You will probably get a lot more useful response if you include the table
structure, the complete query/queries and the output of EXPLAIN for each
query (just put EXPLAIN in front of the SELECT. It will give you 
information

about the execution path that MySQL came up with).

Regards, Jigal.


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








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

Install Mysql 4 on Fedora Core 2

[Jerry Swanson]

I want to install MySQL 4 on Fedora Core 2.
Questions:
#1. Do I need to uninstall MySQL 3 from the server first?

#2. Where I can get source for MySQL 4?

Thanks

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

RE: Install Mysql 4 on Fedora Core 2

[Peter Normann]

Jerry Swanson wrote:

 #1. Do I need to uninstall MySQL 3 from the server first?

Not really.

 #2. Where I can get source for MySQL 4?

At your local 7/11. They usually store it hidden behind the counter along
with the most popular nuclear launch codes.

If they are out of stock, you might consider browsing mysql's site at
http://www.mysql.com. Specifically
http://dev.mysql.com/downloads/mysql/4.0.html might be interesting.


Peter Normann


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

Re: Install Mysql 4 on Fedora Core 2

[Jigal van Hemert]

 I want to install MySQL 4 on Fedora Core 2.
 Questions:
 #1. Do I need to uninstall MySQL 3 from the server first?
http://dev.mysql.com/doc/mysql/en/upgrading-from-3-23.html

 #2. Where I can get source for MySQL 4?
http://dev.mysql.com/downloads/
Go to the page of the version you like to install.
It's advised to install the appropriate binary package, but if you really
want the sources you can find them near the end of the page.
http://dev.mysql.com/downloads/mysql/4.0.html
http://dev.mysql.com/downloads/mysql/4.1.html

Regards, Jigal.


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

mysql cygwin

[Jan Bartholdy]

I want to use a mysql database in a cygwin application (GRASS). Should I
install mysql under cygwin or does exist any possibilities to use the
database with myodbc under cygwin only? Thanks, Jan




Virus checked by G DATA AntiVirusKit
Version: AVK 15.0.5403 from 10.06.2005


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

Re: Regarding NOT NULL Option for Table Fields....

[mfatene]

Hi ashok,
With check, you could do iy, but they don't work with mysql.

The only solution i ican see is an application control or :

drop table if exists tempo;
create table tempo like mine;
insert into tempo values('','');
insert into mine select * from tempo where length(...)0;
drop table tempo;


Mathias

Selon Ashok Kumar [EMAIL PROTECTED]:

 hi Mathias,
  My question is how can i protect the empty strings
 (that contains length 0).

 thanks and regards,
  Ashok Kumar.P.S.

 --- [EMAIL PROTECTED] wrote:

  Hi,
  '' or empty string is not a null in mysql. This is
  true for Oracle !
  this simple test lets you understand :
 
  * Without NULLs
  mysql create table notnull (t varchar(10) NOT
  NULL);
  Query OK, 0 rows affected (0.14 sec)
 
  mysql insert into notnull values('test1');
  Query OK, 1 row affected (0.01 sec)
 
  mysql insert into notnull values('');
  Query OK, 1 row affected (0.02 sec)
 
  mysql insert into notnull values(NULL);
  ERROR 1048 (23000): Column 't' cannot be null
  mysql select * from notnull;
  +---+
  | t |
  +---+
  | test1 |
  |   |
  +---+
  2 rows in set (0.02 sec)
 
  mysql select * from notnull where isnull(t);
  Empty set (0.02 sec)
 
  * With NULLs
  mysql create table isnulle(a varchar(10));
  Query OK, 0 rows affected (0.08 sec)
 
  mysql insert into isnulle values(NULL);
  Query OK, 1 row affected (0.03 sec)
 
  mysql select * from isnulle where isnull(a);
  +--+
  | a|
  +--+
  | NULL |
  +--+
  1 row in set (0.00 sec)
 
 
  Hope that helps.
  Mathias
 
 
  Selon Ashok Kumar [EMAIL PROTECTED]:
 
   Hi friends,
I'm having one doubt on NOT NULL specification
  for
   the table field properties. That is i have created
  on
   table-mine which contains name(Not Null) and
  pwd(Not
   Null). Now I intended to execute the following
  query.
  
   insert into mine values('','')
  
   This means that i'm trying to insert the null
  fields
   to the table. but this query is successfully
  executed
   and 1 row is inserted into table with empty
  values.
   why it's happening and how can i resolve this
  problem.
  
   Pls give me suggestions regarding this.
  
   Thanks and Regards,
Ashok Kumar.P.S
  
  
  
   __
   Discover Yahoo!
   Stay in touch with email, IM, photo sharing and
  more. Check it out!
   http://discover.yahoo.com/stayintouch.html
  
   --
   MySQL General Mailing List
   For list archives: http://lists.mysql.com/mysql
   To unsubscribe:
  http://lists.mysql.com/[EMAIL PROTECTED]
  
  
 
 
 
  --
  MySQL General Mailing List
  For list archives: http://lists.mysql.com/mysql
  To unsubscribe:
 
 http://lists.mysql.com/[EMAIL PROTECTED]
 
 




 __
 Discover Yahoo!
 Get on-the-go sports scores, stock quotes, news and more. Check it out!
 http://discover.yahoo.com/mobile.html




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

Re: mysql cygwin

[SGreen]

Jan Bartholdy [EMAIL PROTECTED] wrote on 06/13/2005 
07:18:22 AM:

 I want to use a mysql database in a cygwin application (GRASS). Should I
 install mysql under cygwin or does exist any possibilities to use the
 database with myodbc under cygwin only? Thanks, Jan

 
 
 Virus checked by G DATA AntiVirusKit
 Version: AVK 15.0.5403 from 10.06.2005

 
If I understand Cygwin correctly, it is a Linux shell that runs under 
Windows. That gives you 6 possible working combinations to use MySQL. 

server - Cygwin, client - Cygwin
server - Cygwin, client - win
server - win, client Cygwin
server - win, client - win
server - other (any OS), client - Cygwin
server - other (any OS), client - win

However, you say that your client is a Cygwin application (GRASS) so that 
limits you to configurations 1, 3, and 5.  You can setup and operate a 
MySQL server either inside of Cygwin (config 1) or within the Win32 
environment that is hosting your Cygwin environment (config 3) or on a 
completely different machine (config 5).

How you connect GRASS to MySQL is completely up to its (GRASS's) 
implementation. If GRASS needs ODBC to talk to MySQL then you will also 
need ODBC installed within Cygwin but that does not mean you must put your 
MySQL server there, too. I am NOT a Linux (or Cygwin) expert so I cannot 
help you to configure your GRASS client but I think I covered your 
question about what your client/server installation options are.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

How to load a remote db with lots of data?

[Brian Dunning]

I have to load my remote MySQL db's with about a gig of data -  
phpMyAdmin only allows me to upload a 15MB CSV file, so I have to  
painstakingly separate my data into 15MB chunks and upload them one  
at a time. It's a huge pain and takes about two entire days. Is there  
a better solution??


(My ISP blocks remote access to MySQL, so I have to use phpMyAdmin or  
any other tool as long as I upload it. I'm not a command-line guy and  
don't have a clue about that.)


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

Re: How to load a remote db with lots of data?

[Peter]

hi,

 do you have SSH access to the server ?

Peter

Brian Dunning wrote:
I have to load my remote MySQL db's with about a gig of data -  
phpMyAdmin only allows me to upload a 15MB CSV file, so I have to  
painstakingly separate my data into 15MB chunks and upload them one  at 
a time. It's a huge pain and takes about two entire days. Is there  a 
better solution??


(My ISP blocks remote access to MySQL, so I have to use phpMyAdmin or  
any other tool as long as I upload it. I'm not a command-line guy and  
don't have a clue about that.)




--
Best regards,

Peter

http://AboutSupport.com

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

Re: How to load a remote db with lots of data?

[David Legault]

You might want to try BigDump

http://www.ozerov.de/bigdump.php

I can't tell how it will cope with 1 GB of data though.

David

Brian Dunning wrote:
I have to load my remote MySQL db's with about a gig of data -  
phpMyAdmin only allows me to upload a 15MB CSV file, so I have to  
painstakingly separate my data into 15MB chunks and upload them one  at 
a time. It's a huge pain and takes about two entire days. Is there  a 
better solution??


(My ISP blocks remote access to MySQL, so I have to use phpMyAdmin or  
any other tool as long as I upload it. I'm not a command-line guy and  
don't have a clue about that.)




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

Re: How to load a remote db with lots of data?

[Frank Bax]

At 10:00 AM 6/13/05, Brian Dunning wrote:


I have to load my remote MySQL db's with about a gig of data -
phpMyAdmin only allows me to upload a 15MB CSV file, so I have to
painstakingly separate my data into 15MB chunks and upload them one
at a time. It's a huge pain and takes about two entire days. Is there
a better solution??



Can phpMyAdmin load a file that is *already* on the server?  FTP the 1G CSV 
file directly to the hosting server, then use phpMyAdmin to load it into 
database.  Otherwise use command line to load the file into database.



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

Re: How to load a remote db with lots of data?

[Brian Dunning]

On Jun 13, 2005, at 7:06 AM, Peter wrote:


 do you have SSH access to the server ?


No.  :(

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

Re: How to load a remote db with lots of data?

[Brian Dunning]

On Jun 13, 2005, at 7:10 AM, replies-lists- 
[EMAIL PROTECTED] wrote:



do you have shell access to the server?


I see that there is some talk about uploading myshell or phpshell to  
the server, so it appears that that is an option. I don't know the  
first thing about what that is or how it works though.



if you do, then upload the data file to the server (via
scp/ftp/whatever) and load it from the commandline. it will take
maybe an hour or two to feel comfortable with the commandline
(depends how long you've lived in a windoz-only world) and after that
you've just gained back a whole bunch of time.


Can it be uploaded zipped or gzipped to save upload time? What kind  
of file would I upload, CSV?


Is there a handy FAQ or instructions anywhere for doing this?

Thanks.  :)

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

RE: How to load a remote db with lots of data?

[Berman, Mikhail]

 Brian,

How about an FTP service on your remote server?


Mikhail Berman
-Original Message-
From: Brian Dunning [mailto:[EMAIL PROTECTED] 
Sent: Monday, June 13, 2005 10:01 AM
To: mysql@lists.mysql.com
Subject: How to load a remote db with lots of data?

I have to load my remote MySQL db's with about a gig of data -
phpMyAdmin only allows me to upload a 15MB CSV file, so I have to
painstakingly separate my data into 15MB chunks and upload them one at a
time. It's a huge pain and takes about two entire days. Is there a
better solution??

(My ISP blocks remote access to MySQL, so I have to use phpMyAdmin or
any other tool as long as I upload it. I'm not a command-line guy and
don't have a clue about that.)

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


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

RE: How to load a remote db with lots of data?

[mfatene]

Hi,
there is nothing simplier and faster than load data infile (LOCAL) since you can
 connect to the server


Mathias

Selon Berman, Mikhail [EMAIL PROTECTED]:

  Brian,

 How about an FTP service on your remote server?


 Mikhail Berman
 -Original Message-
 From: Brian Dunning [mailto:[EMAIL PROTECTED]
 Sent: Monday, June 13, 2005 10:01 AM
 To: mysql@lists.mysql.com
 Subject: How to load a remote db with lots of data?

 I have to load my remote MySQL db's with about a gig of data -
 phpMyAdmin only allows me to upload a 15MB CSV file, so I have to
 painstakingly separate my data into 15MB chunks and upload them one at a
 time. It's a huge pain and takes about two entire days. Is there a
 better solution??

 (My ISP blocks remote access to MySQL, so I have to use phpMyAdmin or
 any other tool as long as I upload it. I'm not a command-line guy and
 don't have a clue about that.)

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


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





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

alter table - add a column

[bruce]

hi..

i have a table that i want to add a column to, and define the columm to be
the primary key. however, i already have a column in the table that's used
as the primary. can someone tell me the approach/commands i should use to
get my results?

table foo
username varchar(50), primary

what i want...
userid int(), primary
username varchar(50)

with userid listed before username!!
can someone tell me what the commands are that i need to enter to get this!!

i thought something like,
alter table foo add userid int(8), primary first (but i couldn't get it to
work.. errors)
and then i couldn't see how to remove the primary key fom username...

thanks

-bruce
[EMAIL PROTECTED]



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

Re: How to load a remote db with lots of data?

[Brian Dunning]

On Jun 13, 2005, at 7:18 AM, Berman, Mikhail wrote:


How about an FTP service on your remote server?


No - I actually don't have any remote access directly to the MySQL  
server. My ISP has separate machines for the database servers and the  
web servers - I can do a lot with my web server, but I don't have  
remote access to the MySQL server. I can telnet into the web server,  
and from there I may be able to access the MySQL server - but frankly  
when you mention telnet or shell I'm already in over my head.  :(


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

Re: alter table - add a column

[Jigal van Hemert]

From: bruce
 table foo
 username varchar(50), primary

 what i want...
 userid int(), primary
 username varchar(50)

 with userid listed before username!!
 can someone tell me what the commands are that i need to enter to get
this!!

http://dev.mysql.com/doc/mysql/en/alter-table.html

ALTER TABLE `foo` DROP PRIMARY KEY, ADD PRIMARY KEY (`userid`);

Regards, Jigal.


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

RE: How to load a remote db with lots of data?

[Berman, Mikhail]

 
Hi Brain,

I have to believe that this task shapes to be more administrative than
technical.

There are number of ways to upload a file, but you need access to the
MySQL server.

Would it be possible for you to talk to your ISP to allow some type of
remote access to MySQL server?
You could explain to them your problem loading a huge file into the
database.

Maybe they are willing to grant you a temporarily access to their server

Regards,

Mikhail Berman

-Original Message-
From: Brian Dunning [mailto:[EMAIL PROTECTED] 
Sent: Monday, June 13, 2005 10:36 AM
To: mysql@lists.mysql.com
Subject: Re: How to load a remote db with lots of data?

On Jun 13, 2005, at 7:18 AM, Berman, Mikhail wrote:

 How about an FTP service on your remote server?

No - I actually don't have any remote access directly to the MySQL
server. My ISP has separate machines for the database servers and the
web servers - I can do a lot with my web server, but I don't have remote
access to the MySQL server. I can telnet into the web server, and from
there I may be able to access the MySQL server - but frankly when you
mention telnet or shell I'm already in over my head.  :(

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


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

alter table - add a column

[J.R. Bullington]

Alter table foo drop Primary Key

Alter table add `userid` int(8) first

Alter table add Primary Key `userid`


Those 3 statements should do it. However, knowing the people on this list,
they will have a faster and better way than mine.

Don't forget about the manual as well...

http://dev.mysql.com/doc/mysql/en/alter-table.html

J.R.


-Original Message-
From: bruce [mailto:[EMAIL PROTECTED]
Sent: Monday, June 13, 2005 10:30 AM
To: mysql@lists.mysql.com
Subject: alter table - add a column

hi..

i have a table that i want to add a column to, and define the columm to be
the primary key. however, i already have a column in the table that's used
as the primary. can someone tell me the approach/commands i should use to
get my results?

table foo
username varchar(50), primary

what i want...
userid int(), primary
username varchar(50)

with userid listed before username!!
can someone tell me what the commands are that i need to enter to get this!!

i thought something like,
alter table foo add userid int(8), primary first (but i couldn't get it to
work.. errors) and then i couldn't see how to remove the primary key fom
username...

thanks

-bruce
[EMAIL PROTECTED]



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



smime.p7s
Description: S/MIME cryptographic signature

Re: alter table - add a column

[Philippe Poelvoorde]

bruce wrote:

hi..

i have a table that i want to add a column to, and define the columm to be
the primary key. however, i already have a column in the table that's used
as the primary. can someone tell me the approach/commands i should use to
get my results?

table foo
username varchar(50), primary

what i want...
userid int(), primary
username varchar(50)



http://dev.mysql.com/doc/mysql/en/alter-table.html

I would try this :
-- drop the PK
ALTER TABLE foo DROP PRIMARY KEY;
-- add the new one
ALTER TABLE foo ADD userid integer unsigned NOT NULL auto_increment, ADD 
PRIMARY KEY(userid);

-- move it to the first column
ALTER TABLE foo MODIFY userid integer unsigned NOT NULL auto_increment 
BEFORE username;


[Note,
ALTER TABLE foo ADD userid integer unsigned NOT NULL auto_increment 
BEFORE username, ADD PRIMARY KEY(userid);

does not seems to work, possibly a bug ?
]




with userid listed before username!!
can someone tell me what the commands are that i need to enter to get this!!

i thought something like,
alter table foo add userid int(8), primary first (but i couldn't get it to
work.. errors)
and then i couldn't see how to remove the primary key fom username...

thanks

-bruce
[EMAIL PROTECTED]






--
Philippe Poelvoorde
COS Trading Ltd.

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

Re: How to load a remote db with lots of data?

[Frank Bax]

At 10:36 AM 6/13/05, Brian Dunning wrote:


On Jun 13, 2005, at 7:18 AM, Berman, Mikhail wrote:


How about an FTP service on your remote server?


No - I actually don't have any remote access directly to the MySQL
server. My ISP has separate machines for the database servers and the
web servers - I can do a lot with my web server, but I don't have
remote access to the MySQL server. I can telnet into the web server,
and from there I may be able to access the MySQL server


Once you telnet into the MySQL server you might be able to use FTP to 
pull the file from another server; unzip it; then load it into database.


- but frankly when you mention telnet or shell I'm already in over my 
head.  :(


Good time to learn a few basics... 



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

Re: alter table - add a column

[Jigal van Hemert]

From: Philippe Poelvoorde
 [Note,
 ALTER TABLE foo ADD userid integer unsigned NOT NULL auto_increment
 BEFORE username, ADD PRIMARY KEY(userid);
 does not seems to work, possibly a bug ?
 ]

No, you forgot the DROP PRIMARY KEY ;-)

ALTER TABLE `foo` DROP PRIMARY KEY, ADD `userid` INTEGER UNSIGNED NOT NULL
AUTO_INCREMENT FIRST, ADD PRIMARY KEY(`userid`);
works like a charm (MySQL 4.0.23-standard).

Regards, Jigal.


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

Re: Install Mysql 4 on Fedora Core 2

[Gleb Paharenko]

Hello.



Use the rpm or binary distribution from:

  http://dev.mysql.com/downloads/mysql/4.1.html



If you install MySQL using rpm with -U command line option, it will

remove the old package. You may meet some problems using PHP or Perl

when you try to connect through they was compiled for 3.xx version.

See notes about them at:

  http://dev.mysql.com/doc/mysql/en/upgrading-from-4-0.html







Jerry Swanson [EMAIL PROTECTED] wrote:

 I want to install MySQL 4 on Fedora Core 2.

 Questions:

 #1. Do I need to uninstall MySQL 3 from the server first?

 

 #2. Where I can get source for MySQL 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]

best configuration for ip failover

[Susan Ator]

This is our situation: We have two servers running RHES3 with samba
connections to a server running RHES4. (No, it's not dns. Believe me
when I say I don't ever want to go through *that* pain again) Our ES4
server contains shared data that is not controlled through cvs. Our two
ES3 servers contain our website which is controlled through cvs. Both
our ES3 servers have mysql. The mysql dbs have our username/password
(and other) information.

We are in the process of setting up ip failover using a dns round-robin.
My concern about keeping the mysql dbs on the ES3 servers is the
possibility of changing the db on one and also changing the db on the
other; I don't know how to keep the changes synced.

I was considering putting the mysql db on the ES4 server and both the
ES3 servers would connect to the mysql db there.

My partner in crime is concerned about this since it would be a single
point of failure.

This is my question: What would be the best configuration for
maintaining database integrity? I don't mean just between what I
outlined, but in general? Clusters seems to be overly complicated (from
what I can understand of it). I just need some direction at this point.

Thanks,

Susan

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

Re: best configuration for ip failover

[mfatene]

Hi,
you can configure bin-log on one, and send logs the second server for sync.
you will have an almost real-time synchronization env.

problem is that you MUST use inodb for ttansactions, and apply exactly the
changes to the standby server.

no replication, no cluster.
This is a simple but needed to be maintained solution.

Mathias

Selon Susan Ator [EMAIL PROTECTED]:

 This is our situation: We have two servers running RHES3 with samba
 connections to a server running RHES4. (No, it's not dns. Believe me
 when I say I don't ever want to go through *that* pain again) Our ES4
 server contains shared data that is not controlled through cvs. Our two
 ES3 servers contain our website which is controlled through cvs. Both
 our ES3 servers have mysql. The mysql dbs have our username/password
 (and other) information.

 We are in the process of setting up ip failover using a dns round-robin.
 My concern about keeping the mysql dbs on the ES3 servers is the
 possibility of changing the db on one and also changing the db on the
 other; I don't know how to keep the changes synced.

 I was considering putting the mysql db on the ES4 server and both the
 ES3 servers would connect to the mysql db there.

 My partner in crime is concerned about this since it would be a single
 point of failure.

 This is my question: What would be the best configuration for
 maintaining database integrity? I don't mean just between what I
 outlined, but in general? Clusters seems to be overly complicated (from
 what I can understand of it). I just need some direction at this point.

 Thanks,

 Susan

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





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

Alter Column Name?

[Scott Purcell]

I cannot find any information on this, so I am thinking the answer is no. But I 
have a column that I would like to change the name of:

current the column name isID, and I would like it to change it to CHILD_ID, 
as the the software is changing, and we are adding  parent_id column.

Thanks,
Scott 

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

Re: alter table - add a column

[Philippe Poelvoorde]

Jigal van Hemert wrote:

From: Philippe Poelvoorde


[Note,
ALTER TABLE foo ADD userid integer unsigned NOT NULL auto_increment
BEFORE username, ADD PRIMARY KEY(userid);
does not seems to work, possibly a bug ?
]



No, you forgot the DROP PRIMARY KEY ;-)


well not really, that's the statement before ! ;-)
I was thus speaking of :
-- drop the PK
ALTER TABLE foo DROP PRIMARY KEY;
-- add the new one
ALTER TABLE foo ADD userid integer unsigned NOT NULL auto_increment
BEFORE username, ADD PRIMARY KEY(userid);
-- move it to the first column
ALTER TABLE foo MODIFY userid integer unsigned NOT NULL auto_increment 
BEFORE username;


which gives me an error for the second query.
So is that normal that I can't specify BEFORE username ?



ALTER TABLE `foo` DROP PRIMARY KEY, ADD `userid` INTEGER UNSIGNED NOT NULL
AUTO_INCREMENT FIRST, ADD PRIMARY KEY(`userid`);
works like a charm (MySQL 4.0.23-standard).

Regards, Jigal.





--
Philippe Poelvoorde
COS Trading Ltd.

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

CREATE TABLE and specifying DEFAULT

[Les Schaffer]

i am trying to create a table as follows:

CREATE TABLE ID (
mat INT PRIMARY KEY UNIQUE,
ID_firstname CHAR(35),
ID_lastname CHAR(35),
ID_ramqnb CHAR(12),
ID_numciv_hosp CHAR(10) DEFAULT '-9',
ID_appt_hosp CHAR(10) DEFAULT '-9',
ID_streetname_hosp CHAR(75) DEFAULT '-9',
ID_streettype_hosp CHAR(6) DEFAULT '-9',
ID_streettype_spec_hosp CHAR(25),
ID_direction_hosp CHAR(2) DEFAULT '-9',
ID_city_hosp CHAR(50) DEFAULT '-9',
ID_city_spec_hosp CHAR(150),
ID_province_hosp CHAR(2) DEFAULT 'QC',
ID_postal_code_hosp CHAR(7) DEFAULT '-9',
ID_phone_number_hosp CHAR(12) DEFAULT '-9',
ID_work_number_hosp CHAR(20) DEFAULT '-9',
ID_cell_number_hosp CHAR(12) DEFAULT '-9'
... lots and lots more columns
);

the table is created almost correctly, excepts there are no DEFAULTs 
showing up after ID_streettype_hosp. the remaining columns all show NULL 
for default.


the result is the same if i truncate the list at the last one shown.


but even this fails:


CREATE TABLE ID (
   mat INT UNIQUE PRIMARY KEY,
   ID_firstname CHAR(35) DEFAULT 'filler',
   ID_lastname CHAR(35) DEFAULT 'filler',
   ID_ramqnb CHAR(12) DEFAULT 'filler',
   ID_numciv_hosp CHAR(10) DEFAULT '-9',
   ID_appt_hosp CHAR(10) DEFAULT '-9',
   ID_streetname_hosp CHAR(75) DEFAULT '-9',
   ID_streettype_hosp CHAR(6) DEFAULT '-9',
   ID_streettype_spec_hosp CHAR(25) DEFAULT 'humbug',
   ID_direction_hosp CHAR(2) DEFAULT '-9',
   ID_city_hosp CHAR(50) DEFAULT '-9',
   ID_city_spec_hosp CHAR(150) DEFAULT 'filler',
   ID_province_hosp CHAR(2) DEFAULT 'QC',
   ID_postal_code_hosp CHAR(7) DEFAULT '-9',
   ID_phone_number_hosp CHAR(12) DEFAULT '-9',
   ID_work_number_hosp CHAR(20) DEFAULT '-9',
   ID_cell_number_hosp CHAR(12) DEFAULT '-9',
   ID_numciv_study CHAR(10) DEFAULT '-9'
);


in that the DEFAULT is lost after ID_streettype_hosp.

am i doing something stupid or is there a limit to how many columns one 
can include in a CREATE TABLE statement???



mysql describe id;
+-+---+--+-+-+---+
| Field   | Type  | Null | Key | Default | Extra |
+-+---+--+-+-+---+
| mat | int(11)   | NO   | PRI | |   |
| ID_firstname| char(35)  | YES  | | filler  |   |
| ID_lastname | char(35)  | YES  | | filler  |   |
| ID_ramqnb   | char(12)  | YES  | | filler  |   |
| ID_numciv_hosp  | char(10)  | YES  | | -9  |   |
| ID_appt_hosp| char(10)  | YES  | | -9  |   |
| ID_streetname_hosp  | char(75)  | YES  | | -9  |   |
| ID_streettype_hosp  | char(6)   | YES  | | -9  |   |
| ID_streettype_spec_hosp | char(25)  | YES  | | NULL|   |
| ID_direction_hosp   | char(2)   | YES  | | NULL|   |
| ID_city_hosp| char(50)  | YES  | | NULL|   |
| ID_city_spec_hosp   | char(150) | YES  | | NULL|   |
| ID_province_hosp| char(2)   | YES  | | NULL|   |
| ID_postal_code_hosp | char(7)   | YES  | | NULL|   |
| ID_phone_number_hosp| char(12)  | YES  | | NULL|   |
| ID_work_number_hosp | char(20)  | YES  | | NULL|   |
| ID_cell_number_hosp | char(12)  | YES  | | NULL|   |
| ID_numciv_study | char(10)  | YES  | | NULL|   |
+-+---+--+-+-+---+
18 rows in set (0.01 sec)


thnx...


les schaffer

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

Unable to install mysql

[Yannick]

Hey guys,

I am not able to install properly mysql. Please see below the technical
details or the bug report.

The installation goes well until I try to add the root user :

[EMAIL PROTECTED]:/usr/bin mysqladmin -u root -h fujitsu password x
mysqladmin: connect to server at 'fujitsu' failed
error: 'Host 'fujitsu.local' is not allowed to connect to this MySQL server'
[EMAIL PROTECTED]:/usr/bin mysqladmin -u root -h 192.168.234.2 password
xx
mysqladmin: connect to server at '192.168.234.2' failed
error: 'Host '192.168.234.2' is not allowed to connect to this MySQL server'
[EMAIL PROTECTED]:/usr/bin


Continuously, and I've tried lots of combinations, he is failing to connect
to the server.

Please give me so hints for me to be able to install it.

Best regards

Yannick

[EMAIL PROTECTED]



SEND-PR: -*- send-pr -*-
SEND-PR: Lines starting with `SEND-PR' will be removed automatically, as
SEND-PR: will all comments (text enclosed in `' and `').
SEND-PR:
From: yannick
To: mysql@lists.mysql.com
Subject: [50 character or so descriptive subject here (for reference)]

Description:
precise description of the problem (multiple lines)
How-To-Repeat:
code/input/activities to reproduce the problem (multiple lines)
Fix:
how to correct or work around the problem, if known (multiple lines)

Submitter-Id:  submitter ID
Originator:Yannick Vauloup
Organization:
 organization of PR author (multiple lines)
MySQL support: [none | licence | email support | extended email support ]
Synopsis:  synopsis of the problem (one line)
Severity:  [ non-critical | serious | critical ] (one line)
Priority:  [ low | medium | high ] (one line)
Category:  mysql
Class: [ sw-bug | doc-bug | change-request | support ] (one line)
Release:   mysql-4.0.15 (Source distribution)
Server: /usr/bin/mysqladmin  Ver 8.40 Distrib 4.0.15, for suse-linux on
i686
Copyright (C) 2000 MySQL AB  MySQL Finland AB  TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license

Server version  4.0.15-Max
Protocol version10
Connection  Localhost via UNIX socket
UNIX socket /var/lib/mysql/mysql.sock
Uptime: 5 min 8 sec

Threads: 1  Questions: 14  Slow queries: 0  Opens: 6  Flush tables: 1  Open
tables: 0  Queries per second avg: 0.045
C compiler:gcc (GCC) 3.3.1 (SuSE Linux)
C++ compiler:  g++ (GCC) 3.3.1 (SuSE Linux)
Environment:
machine, os, target, libraries (multiple lines)
System: Linux fujitsu 2.4.21-286-default #1 Sat Apr 2 08:57:10 UTC 2005 i686
i686 i386 GNU/Linux
Architecture: i686

Some paths:  /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc
/usr/bin/cc
GCC: Lecture des spécification à partir de
/usr/lib/gcc-lib/i586-suse-linux/3.3.1/specs
Configuré avec:
../configure --enable-threads=posix --prefix=/usr --with-local-prefix=/usr/l
ocal --infodir=/usr/share/info --mandir=/usr/share/man --libdir=/usr/lib --e
nable-languages=c,c++,f77,objc,java,ada --disable-checking --enable-libgcj -
-with-gxx-include-dir=/usr/include/g++ --with-slibdir=/lib --with-system-zli
b --enable-shared --enable-__cxa_atexit i586-suse-linux
Modèle de thread: posix
version gcc 3.3.1 (SuSE Linux)
Compilation info: CC='gcc'
 CFLAGS='-O2 -march=i586 -mcpu=i686 -fmessage-length=0 -DPIC -fPIC'
CXX='g++'
FLAGS='-O2 -march=i586 -mcpu=i686 -fmessage-length=0
-felide-constructor
s   -fno-exceptions 
-fno-rtti   -fPIC -DPIC'  LDFLAGS=''  ASFLAGS=''
LIBC:
-rwxr-xr-x1 root root  1469811 2003-09-24 01:05 /lib/libc.so.6
-rw-r--r--1 root root 13553180 2003-09-23 18:04 /usr/lib/libc.a
-rw-r--r--1 root root  204 2003-09-23 18:04 /usr/lib/libc.so
lrwxrwxrwx1 root root   20 2005-02-22 14:05
/usr/lib/libc-client.so - libc-client.so.2002d
-rwxr-xr-x1 root root   770436 2003-09-23 20:29
/usr/lib/libc-client.so.2002d
Configure command: ./configure '--disable-shared'
'--enable-thread-safe-client' '--with-mysqld-ldflags=-static'
'--with-client-ldflags=-static' '--without-berkeley-db' '--with-extra-tools'
'--without-innodb' '--enable-assembler' '--enable-large-files'
'--infodir=/usr/share/info' '--libdir=/usr/lib' '--libexecdir=/usr/sbin'
'--localstatedir=/var/lib/mysql' '--mandir=/usr/share/man' '--prefix=/usr'
'--sysconfdir=/etc' '--with-mysqld-user=mysql' '--without-debug'
'--datadir=/usr/share' '--includedir=/usr/include'
'--with-extra-charsets=complex'
'--with-unix-socket-path=/var/lib/mysql/mysql.sock' '--with-libwrap'
'CFLAGS=-O2 -march=i586 -mcpu=i686 -fmessage-length=0 -DPIC -fPIC'
'CXXFLAGS=-O2 -march=i586 -mcpu=i686 -fmessage-length=0 
-felide-construc
tors-fno-exceptions 
-fno-rtti   

RE: CREATE TABLE and specifying DEFAULT

[Gordon Bruce]

I just ran the creates on 4.0.20 on LINUX and 5.0.6 on Windows and they
both seem to look fine {see the SHOW CREATE TABLE's following the CREATE
TABLE statements}

RUN ON 4.0.20

mysql CREATE TABLE ID (
- mat INT UNIQUE PRIMARY KEY,
- ID_firstname CHAR(35) DEFAULT 'filler',
- ID_lastname CHAR(35) DEFAULT 'filler',
- ID_ramqnb CHAR(12) DEFAULT 'filler',
- ID_numciv_hosp CHAR(10) DEFAULT '-9',
- ID_appt_hosp CHAR(10) DEFAULT '-9',
- ID_streetname_hosp CHAR(75) DEFAULT '-9',
- ID_streettype_hosp CHAR(6) DEFAULT '-9',
- ID_streettype_spec_hosp CHAR(25) DEFAULT 'humbug',
- ID_direction_hosp CHAR(2) DEFAULT '-9',
- ID_city_hosp CHAR(50) DEFAULT '-9',
- ID_city_spec_hosp CHAR(150) DEFAULT 'filler',
- ID_province_hosp CHAR(2) DEFAULT 'QC',
- ID_postal_code_hosp CHAR(7) DEFAULT '-9',
- ID_phone_number_hosp CHAR(12) DEFAULT '-9',
- ID_work_number_hosp CHAR(20) DEFAULT '-9',
- ID_cell_number_hosp CHAR(12) DEFAULT '-9',
- ID_numciv_study CHAR(10) DEFAULT '-9'
- );
Query OK, 0 rows affected (0.03 sec)


mysql show create table ID;
+---+---
-

-
| Table | Create Table

+---+---
-

-
| ID| CREATE TABLE `ID` (
  `mat` int(11) NOT NULL default '0',
  `ID_firstname` char(35) default 'filler',
  `ID_lastname` char(35) default 'filler',
  `ID_ramqnb` char(12) default 'filler',
  `ID_numciv_hosp` char(10) default '-9',
  `ID_appt_hosp` char(10) default '-9',
  `ID_streetname_hosp` char(75) default '-9',
  `ID_streettype_hosp` char(6) default '-9',
  `ID_streettype_spec_hosp` char(25) default 'humbug',
  `ID_direction_hosp` char(2) default '-9',
  `ID_city_hosp` char(50) default '-9',
  `ID_city_spec_hosp` char(150) default 'filler',
  `ID_province_hosp` char(2) default 'QC',
  `ID_postal_code_hosp` char(7) default '-9',
  `ID_phone_number_hosp` char(12) default '-9',
  `ID_work_number_hosp` char(20) default '-9',
  `ID_cell_number_hosp` char(12) default '-9',
  `ID_numciv_study` char(10) default '-9',
  PRIMARY KEY  (`mat`),
  UNIQUE KEY `mat` (`mat`)
) TYPE=MyISAM |
+---+---
-

-
1 row in set (0.00 sec)


___
RUN ON 5.0.6


mysql CREATE TABLE ID (
- mat INT UNIQUE PRIMARY KEY,
- ID_firstname CHAR(35) DEFAULT 'filler',
- ID_lastname CHAR(35) DEFAULT 'filler',
- ID_ramqnb CHAR(12) DEFAULT 'filler',
- ID_numciv_hosp CHAR(10) DEFAULT '-9',
- ID_appt_hosp CHAR(10) DEFAULT '-9',
- ID_streetname_hosp CHAR(75) DEFAULT '-9',
- ID_streettype_hosp CHAR(6) DEFAULT '-9',
- ID_streettype_spec_hosp CHAR(25) DEFAULT 'humbug',
- ID_direction_hosp CHAR(2) DEFAULT '-9',
- ID_city_hosp CHAR(50) DEFAULT '-9',
- ID_city_spec_hosp CHAR(150) DEFAULT 'filler',
- ID_province_hosp CHAR(2) DEFAULT 'QC',
- ID_postal_code_hosp CHAR(7) DEFAULT '-9',
- ID_phone_number_hosp CHAR(12) DEFAULT '-9',
- ID_work_number_hosp CHAR(20) DEFAULT '-9',
- ID_cell_number_hosp CHAR(12) DEFAULT '-9',
- ID_numciv_study CHAR(10) DEFAULT '-9'
- );
Query OK, 0 rows affected (0.91 sec)

mysql
mysql CREATE TABLE ID1 (
- mat INT PRIMARY KEY UNIQUE,
- ID_firstname CHAR(35),
- ID_lastname CHAR(35),
- ID_ramqnb CHAR(12),
- ID_numciv_hosp CHAR(10) DEFAULT '-9',
- ID_appt_hosp CHAR(10) DEFAULT '-9',
- ID_streetname_hosp CHAR(75) DEFAULT '-9',
-  ID_streettype_hosp CHAR(6) DEFAULT '-9',
-  ID_streettype_spec_hosp CHAR(25),
- ID_direction_hosp CHAR(2) DEFAULT '-9',
- ID_city_hosp CHAR(50) DEFAULT '-9',
- ID_city_spec_hosp CHAR(150),
- ID_province_hosp CHAR(2) DEFAULT 'QC',
- ID_postal_code_hosp CHAR(7) DEFAULT '-9',
- ID_phone_number_hosp CHAR(12) DEFAULT '-9',
- ID_work_number_hosp CHAR(20) DEFAULT '-9',
- ID_cell_number_hosp CHAR(12) DEFAULT '-9'
-
- );
Query OK, 0 rows affected (0.16 sec)

mysql show create table ID;
+---+---
-

-
| Table | Create Table

Re: CREATE TABLE and specifying DEFAULT

[Les Schaffer]

Gordon Bruce wrote:

I just ran the creates on 4.0.20 on LINUX and 5.0.6 on Windows 



i was running 5.0.4 on Windows:

mysql \s
--
mysql  Ver 14.9 Distrib 5.0.4-beta, for Win32 (ia32)


switching to 5.0.6 now. anyone wanna make a bet  ;-)

les schaffer

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

Re: CREATE TABLE and specifying DEFAULT

[Danny Stolle]

Les Schaffer wrote:

Gordon Bruce wrote:


I just ran the creates on 4.0.20 on LINUX and 5.0.6 on Windows



i was running 5.0.4 on Windows:

mysql \s
--
mysql  Ver 14.9 Distrib 5.0.4-beta, for Win32 (ia32)


switching to 5.0.6 now. anyone wanna make a bet  ;-)

les schaffer



euh .. would that be on a windows platform ;^) ?? i am running the 14.7 
on a linux distro and i have no trouble since ...


danny

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

Re: CREATE TABLE and specifying DEFAULT

[Danny Stolle]

Les Schaffer wrote:

Gordon Bruce wrote:


I just ran the creates on 4.0.20 on LINUX and 5.0.6 on Windows



i was running 5.0.4 on Windows:

mysql \s
--
mysql  Ver 14.9 Distrib 5.0.4-beta, for Win32 (ia32)


switching to 5.0.6 now. anyone wanna make a bet  ;-)

les schaffer



euh .. would that be on a windows platform ;^) ?? i am running the 14.7 
on a linux distro and i have no trouble since ...


danny

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

Re: CREATE TABLE and specifying DEFAULT

[Les Schaffer]

Gordon Bruce wrote:


I just ran the creates on 4.0.20 on LINUX and 5.0.6 on Windows
 



upgrading to 5.0.6 solved the problem.

do i need to let MySQL developers know about this or do they monitor the 
list or once a release is gone, i can assume THIS problem was fixed?


thanks to all for the fast help. i am new to the mysql list and i can 
see it works.


les schaffer

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

Re: mysql cygwin

[Warren Young]

Jan Bartholdy wrote:


I want to use a mysql database in a cygwin application (GRASS). Should I
install mysql under cygwin or does exist any possibilities to use the
database with myodbc under cygwin only? Thanks, Jan


I believe people have gotten MySQL to build under Cygwin, but you should 
be aware of another option: Cygwin's build system can link against 
Microsoft C libraries, so you could just use the regular Win32 binary 
distribution and link your program against that.


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

Re: mysql cygwin

[Warren Young]

[EMAIL PROTECTED] wrote:

If I understand Cygwin correctly, it is a Linux shell that runs under 
Windows. 


Uh, sort of.

Cygwin is a GNU environment ported to Windows.  You've got your bash, 
your GCC, your ls, etc.  To make all this work with minimal porting, 
there's a Cygwin DLL and library that the compiler automatically links 
programs to, which provide POSIX facilities.


Yes, it emulates Linux more closely than any other *ix, but to call it a 
Linux shell is glossing over a lot of things.  It doesn't run Linux 
binaries, for one thing.


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

Lower Case Problems with Win XP Pro and 5.0.6 Mysql

[TheRefUmp]

Hi,
   Anyone experience this problem. I did the following:
C:\perlsrcmysql -f --user=root --password= --port=3307
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 48 to server version: 5.0.6-beta-nt-log

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

mysql create database userdb;
Query OK, 1 row affected (0.00 sec)

mysql use userdb;
Query OK, 1 row affected (0.00 sec)

mysql create table ABC (col1 varchar(10));
Query OK, 0 rows affected (0.03 sec)

mysql show tables;
+-+
| Tables_in_sdidw |
+-+
| abc |

+-+
4 rows in set (0.00 sec)

mysql

NOTE THE FACT THAT THE TABLE IS created in LOWER case!

Any ideas as to why this is happening?
Regards,
George

__
Switch to Netscape Internet Service.
As low as $9.95 a month -- Sign up today at http://isp.netscape.com/register

Netscape. Just the Net You Need.

New! Netscape Toolbar for Internet Explorer
Search from anywhere on the Web and block those annoying pop-ups.
Download now at http://channels.netscape.com/ns/search/install.jsp

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

Repost: Lower Case Problems with Win XP Pro and 5.0.6 MySQL

[TheRefUmp]

Hi,
  Anyone experience this problem. I did the following:
C:\perlsrcmysql -f --user=root --password= --port=3307
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 48 to server version: 5.0.6-beta-nt-log

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

mysql create database userdb;
Query OK, 1 row affected (0.00 sec)

mysql use userdb;
Query OK, 1 row affected (0.00 sec)

mysql create table ABC (col1 varchar(10));
Query OK, 0 rows affected (0.03 sec)

mysql show tables;
+--+
| Tables_in_userdb |
+--+
| abc  |
+--+
1 row in set (0.00 sec)

mysql

NOTE THE FACT THAT THE TABLE IS created in LOWER case!

Any ideas as to why this is happening?
Regards,
George


__
Switch to Netscape Internet Service.
As low as $9.95 a month -- Sign up today at http://isp.netscape.com/register

Netscape. Just the Net You Need.

New! Netscape Toolbar for Internet Explorer
Search from anywhere on the Web and block those annoying pop-ups.
Download now at http://channels.netscape.com/ns/search/install.jsp

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

RE: alter table - add a column

[Rhino]

You'll need three alter statements:
- one to add the new column
- one to get rid of the old primary key
- one to set the new column as the primary key

Something like this, i.e. I've left out some details of the syntax:
ALTER TABLE FOO ADD USERID CHAR(8);
ALTER TABLE FOO DROP PRIMARY KEY;
ALTER TABLE FOO ADD PRIMARY KEY(USERID);

Your basic mistake is that you're trying to make all the changes in one
statement; SQL pretty much forces you to change each aspect of the table in
a separate ALTER TABLE statement.

Rhino


-Original Message-
From: bruce [mailto:[EMAIL PROTECTED]
Sent: Monday, June 13, 2005 10:30 AM
To: mysql@lists.mysql.com
Subject: alter table - add a column


hi..

i have a table that i want to add a column to, and define the columm to be
the primary key. however, i already have a column in the table that's used
as the primary. can someone tell me the approach/commands i should use to
get my results?

table foo
username varchar(50), primary

what i want...
userid int(), primary
username varchar(50)

with userid listed before username!!
can someone tell me what the commands are that i need to enter to get this!!

i thought something like,
alter table foo add userid int(8), primary first (but i couldn't get it to
work.. errors)
and then i couldn't see how to remove the primary key fom username...

thanks

-bruce
[EMAIL PROTECTED]



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


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

RE: Alter Column Name?

[Rhino]

I'm not sure if this will work for a column name but there is a CHANGE
COLUMN feature in the  ALTER TABLE statement so that sounds promising. Then
again, I don't have a MySQL system to try it on this week - I am
travelling - so I can't be sure if it will work for the column name.

Rhino

-Original Message-
From: Scott Purcell [mailto:[EMAIL PROTECTED]
Sent: Monday, June 13, 2005 1:00 PM
To: mysql@lists.mysql.com
Subject: Alter Column Name?


I cannot find any information on this, so I am thinking the answer is no.
But I have a column that I would like to change the name of:

current the column name isID, and I would like it to change it to
CHILD_ID, as the the software is changing, and we are adding  parent_id
column.

Thanks,
Scott

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


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

RE: Alter Column Name?

[J.R. Bullington]

Why not just 

ALTER TABLE tbl_Foo CHANGE `ID` `CHILD_ID` options go here

Since I don't know the rest of your options or what you want to change the
column to, that's just for the name change. 

J.R.
 

-Original Message-
From: Rhino [mailto:[EMAIL PROTECTED] 
Sent: Monday, June 13, 2005 4:56 PM
To: Scott Purcell; mysql@lists.mysql.com
Subject: RE: Alter Column Name?

I'm not sure if this will work for a column name but there is a CHANGE
COLUMN feature in the  ALTER TABLE statement so that sounds promising. Then
again, I don't have a MySQL system to try it on this week - I am travelling
- so I can't be sure if it will work for the column name.

Rhino

-Original Message-
From: Scott Purcell [mailto:[EMAIL PROTECTED]
Sent: Monday, June 13, 2005 1:00 PM
To: mysql@lists.mysql.com
Subject: Alter Column Name?


I cannot find any information on this, so I am thinking the answer is no.
But I have a column that I would like to change the name of:

current the column name isID, and I would like it to change it to
CHILD_ID, as the the software is changing, and we are adding  parent_id
column.

Thanks,
Scott

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


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



smime.p7s
Description: S/MIME cryptographic signature

RE: [SPAM] - Unable to install mysql - Bayesian Filter detected spam

[Yannick]

Kevin,

Thanks for your answer. See below the results :
* I' really wondering if there is not any missing files.
* The mysql.soc file is complettly empty
* I can only access myssql when I am not in root.
* I can only see 1 database test when I know there is others like zorum
which is working
* The command Grant doesn't work.
The result is that I can never access any database.

I have been folowing the installatin process but it still does not work.
Do you mind to have a look at the details below and advice on which files I
should have a look.

Thanks in advance.

Yannick


fujitsu:/etc # mysql -u root
ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES)
fujitsu:/etc # su mysql
[EMAIL PROTECTED]:/etc mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9 to server version: 4.0.15-Max

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

mysql quit
Bye
[EMAIL PROTECTED]:/etc mysql -u root
ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: NO)
[EMAIL PROTECTED]:/etc mysql -u mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11 to server version: 4.0.15-Max

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

mysql quit
Bye
[EMAIL PROTECTED]:/etc su
Password:
fujitsu:/etc # mysql -u mysql
ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES)
fujitsu:/etc # mysql -u yannick
ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password:
YES)
fujitsu:/etc # su mysql
[EMAIL PROTECTED]:/etc mysql -u yannick
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14 to server version: 4.0.15-Max

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

mysql SET PASSWORD FOR ''@'localhost' = PASSWORD('Yannick');
ERROR 1044: Access denied for user: '@localhost' to database 'mysql'
mysql SET PASSWORD FOR 'yannick' = PASSWORD('Yannick');
ERROR 1133: Can't find any matching row in the user table
mysql
mysql show databases;
+--+
| Database |
+--+
| test |
+--+
1 row in set (0.00 sec)

mysql
mysql UPDATE mysql.user SET Password = PASSWORD('Yannick')
- ;
ERROR 1044: Access denied for user: '@localhost' to database 'mysql'
mysql
fujitsu:/bin # mysqlshow
mysqlshow: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES)
fujitsu:/bin # mysql -e SELECT Host,Db,User FROM db mysql
ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES)
fujitsu:/bin #  bin/mysqld_safe --user=mysql --log 
bash: bin/mysqld_safe: Aucun fichier ou répertoire de ce type
[1] 16599
[1]   Exit 127bin/mysqld_safe --user=mysql --log
fujitsu:/bin # mysqladmin version
mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user: '[EMAIL PROTECTED]' (Using password: YES)'
fujitsu:/bin #
mysql SHOW DATABASES;
+--+
| Database |
+--+
| test |
+--+
1 row in set (0.00 sec)

mysql GRANT ALL ON menagerie.* TO 'mysql'@'localhost';
ERROR 1044: Access denied for user: '@localhost' to database 'menagerie'
mysql GRANT ALL ON test TO 'mysql'@'localhost';
ERROR 1046: No Database Selected
mysql select test;
ERROR 1054: Unknown column 'test' in 'field list'
mysql use test
Database changed
mysql GRANT ALL ON test TO 'mysql'@'localhost';
ERROR 1044: Access denied for user: '@localhost' to database 'test'
mysql

-Message d'origine-
De : Kevin Struckhoff [mailto:[EMAIL PROTECTED]
Envoyé : Monday, June 13, 2005 9:55 PM
À : [EMAIL PROTECTED]
Cc : mysql@lists.mysql.com
Objet : RE: [SPAM] - Unable to install mysql - Bayesian Filter detected
spam


Yannick,

MySQL is very picky in how it handles security, or at least different.
It has an extra layer of complexity, compared to say, the Informix
RDBMS, which uses the OS user/password and grant statements. With that
being said, have you run the post-install steps to add users to the
mysql database grant tables user  db? Refer to section 2.9 of the MySQL
Reference Manual, especially section 2.9.3. Good Luck.

Kevin Struckhoff
Customer Analytics Mgr.
NewRoads West

Office 818.253.3819 Fax 818.834.8843
[EMAIL PROTECTED]

-Original Message-
From: Yannick [mailto:[EMAIL PROTECTED]
Sent: Monday, June 13, 2005 11:42 AM
To: mysql@lists.mysql.com
Subject: [SPAM] - Unable to install mysql - Bayesian Filter detected
spam

Hey guys,

I am not able to install properly mysql. Please see below the technical
details or the bug report.

The installation goes well until I try to add the root user :

[EMAIL PROTECTED]:/usr/bin mysqladmin -u root -h fujitsu password x
mysqladmin: connect to server at 'fujitsu' failed
error: 'Host 'fujitsu.local' is not allowed to connect to this MySQL
server'
[EMAIL PROTECTED]:/usr/bin mysqladmin -u root -h 192.168.234.2 password
xx
mysqladmin: connect to server at '192.168.234.2' failed
error: 'Host '192.168.234.2' is not allowed to connect to this MySQL

RE: [SPAM] - Unable to install mysql - Bayesian Filter detected spam

[Yannick]

Kevin,

In addition to that, the ZORUM database works because when I stop mysql, the
following site stops working :
http://www.wxy.nl/zorum_3_5/  with the database ZORUM


Here PHPadmin doesn't give me any privilege to create anything :
http://www.wxy.nl/phpMyAdmin/


I beleive I'm not to far from having it working ut I still have this
priviledge issue.

BEst regards

Yannick


-Message d'origine-
De : Yannick [mailto:[EMAIL PROTECTED]
Envoyé : Monday, June 13, 2005 11:54 PM
À : [EMAIL PROTECTED]
Cc : mysql@lists.mysql.com
Objet : RE: [SPAM] - Unable to install mysql - Bayesian Filter detected
spam


Kevin,

Thanks for your answer. See below the results :
* I' really wondering if there is not any missing files.
* The mysql.soc file is complettly empty
* I can only access myssql when I am not in root.
* I can only see 1 database test when I know there is others like zorum
which is working
* The command Grant doesn't work.
The result is that I can never access any database.

I have been folowing the installatin process but it still does not work.
Do you mind to have a look at the details below and advice on which files I
should have a look.

Thanks in advance.

Yannick


fujitsu:/etc # mysql -u root
ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES)
fujitsu:/etc # su mysql
[EMAIL PROTECTED]:/etc mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9 to server version: 4.0.15-Max

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

mysql quit
Bye
[EMAIL PROTECTED]:/etc mysql -u root
ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: NO)
[EMAIL PROTECTED]:/etc mysql -u mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11 to server version: 4.0.15-Max

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

mysql quit
Bye
[EMAIL PROTECTED]:/etc su
Password:
fujitsu:/etc # mysql -u mysql
ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES)
fujitsu:/etc # mysql -u yannick
ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password:
YES)
fujitsu:/etc # su mysql
[EMAIL PROTECTED]:/etc mysql -u yannick
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14 to server version: 4.0.15-Max

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

mysql SET PASSWORD FOR ''@'localhost' = PASSWORD('Yannick');
ERROR 1044: Access denied for user: '@localhost' to database 'mysql'
mysql SET PASSWORD FOR 'yannick' = PASSWORD('Yannick');
ERROR 1133: Can't find any matching row in the user table
mysql
mysql show databases;
+--+
| Database |
+--+
| test |
+--+
1 row in set (0.00 sec)

mysql
mysql UPDATE mysql.user SET Password = PASSWORD('Yannick')
- ;
ERROR 1044: Access denied for user: '@localhost' to database 'mysql'
mysql
fujitsu:/bin # mysqlshow
mysqlshow: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES)
fujitsu:/bin # mysql -e SELECT Host,Db,User FROM db mysql
ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES)
fujitsu:/bin #  bin/mysqld_safe --user=mysql --log 
bash: bin/mysqld_safe: Aucun fichier ou répertoire de ce type
[1] 16599
[1]   Exit 127bin/mysqld_safe --user=mysql --log
fujitsu:/bin # mysqladmin version
mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user: '[EMAIL PROTECTED]' (Using password: YES)'
fujitsu:/bin #
mysql SHOW DATABASES;
+--+
| Database |
+--+
| test |
+--+
1 row in set (0.00 sec)

mysql GRANT ALL ON menagerie.* TO 'mysql'@'localhost';
ERROR 1044: Access denied for user: '@localhost' to database 'menagerie'
mysql GRANT ALL ON test TO 'mysql'@'localhost';
ERROR 1046: No Database Selected
mysql select test;
ERROR 1054: Unknown column 'test' in 'field list'
mysql use test
Database changed
mysql GRANT ALL ON test TO 'mysql'@'localhost';
ERROR 1044: Access denied for user: '@localhost' to database 'test'
mysql

-Message d'origine-
De : Kevin Struckhoff [mailto:[EMAIL PROTECTED]
Envoyé : Monday, June 13, 2005 9:55 PM
À : [EMAIL PROTECTED]
Cc : mysql@lists.mysql.com
Objet : RE: [SPAM] - Unable to install mysql - Bayesian Filter detected
spam


Yannick,

MySQL is very picky in how it handles security, or at least different.
It has an extra layer of complexity, compared to say, the Informix
RDBMS, which uses the OS user/password and grant statements. With that
being said, have you run the post-install steps to add users to the
mysql database grant tables user  db? Refer to section 2.9 of the MySQL
Reference Manual, especially section 2.9.3. Good Luck.

Kevin Struckhoff
Customer Analytics Mgr.
NewRoads West

Office 818.253.3819 Fax 818.834.8843
[EMAIL PROTECTED]

-Original Message-
From: Yannick [mailto:[EMAIL PROTECTED]
Sent: Monday, June 13, 2005 11:42 AM
To: mysql@lists.mysql.com
Subject: [SPAM] - Unable to install 

Prevalidating queries?

[Andy Pieters]

Hi all

As part of an automated patch system, I am facing the following problem:

* A script will update the program from version x to version y
* The script contains file actions, and database (mysql) actions
* The actions are executed in order
* For each action, a backup copy is created (if necessary)
  example if action is deletedir then the dir is moved to a temp directory
* if an action (with status fail=abort) occurs, then the system must be 
restored to previous state. 

As far as file/directory operation is concerned, this is easy to implement, 
and that's why we are using backup copies.

For the mysql part I don't really see how to do this.  I am not using inodb 
but MyIsam tables.

It is not that I need to know the result of the query in advance, only if 
mysql will accept it or will errormessage on the query.

What I don't want is that query1, and 2 are already executed, and 3 fails 
because how could I do a rollback then?


Anybody got any ideas?


With kind regards



Andy


-- 
Registered Linux User Number 379093
-- --BEGIN GEEK CODE BLOCK-
Version: 3.1
GAT/O/E$ d-(---)+ s:(+): a--(-)? C$(+++) UL$ P-(+)++
L+++$ E---(-)@ W++$ !N@ o? !K? W--(---) !O !M- V-- PS++(+++)
PE--(-) Y+ PGP++(+++) t+(++) 5-- X++ R*(+)@ !tv b-() DI(+) D+(+++) G(+)
e$@ h++(*) r--++ y--()
-- ---END GEEK CODE BLOCK--
--
Check out these few php utilities that I released
 under the GPL2 and that are meant for use with a 
 php cli binary:
 
 http://www.vlaamse-kern.com/sas/
--

--


pgpC4i5ipjljm.pgp
Description: PGP signature

RE: Prevalidating queries?

[mathias fatene]

Hi andy,
Before starting your migration maake sur to stop mysql and copy all the
datadir to another backup dir (just like directories).
About single transaction, this will be difficult since you manage myisam
tables.

So what we can think to is :
1. execute one query
2. check log
3. if OK, continue with query n+1
4. else rexecute query n --- But here data can become
incohrent.

A workaround seems to me to alter your table to innodb engine just for
the upgrade. Then start transactions with n grouped queries. Then decide
a commit or rollback.

At the end of teh upgrade, you can come back to myisam.
This is simplier. But you can also decide to take intermadiate backups
when upgrading.

Best Regards

Mathias FATENE
 
Hope that helps
*This not an official mysql support answer
 


-Original Message-
From: Andy Pieters [mailto:[EMAIL PROTECTED] 
Sent: mardi 14 juin 2005 00:15
To: mysql@lists.mysql.com
Subject: Prevalidating queries?


Hi all

As part of an automated patch system, I am facing the following problem:

* A script will update the program from version x to version y
* The script contains file actions, and database (mysql) actions
* The actions are executed in order
* For each action, a backup copy is created (if necessary)
  example if action is deletedir then the dir is moved to a temp
directory
* if an action (with status fail=abort) occurs, then the system must be 
restored to previous state. 

As far as file/directory operation is concerned, this is easy to
implement, 
and that's why we are using backup copies.

For the mysql part I don't really see how to do this.  I am not using
inodb 
but MyIsam tables.

It is not that I need to know the result of the query in advance, only
if 
mysql will accept it or will errormessage on the query.

What I don't want is that query1, and 2 are already executed, and 3
fails 
because how could I do a rollback then?


Anybody got any ideas?


With kind regards



Andy


-- 
Registered Linux User Number 379093
-- --BEGIN GEEK CODE BLOCK-
Version: 3.1
GAT/O/E$ d-(---)+ s:(+): a--(-)? C$(+++) UL$ P-(+)++
L+++$ E---(-)@ W++$ !N@ o? !K? W--(---) !O !M- V-- PS++(+++)
PE--(-) Y+ PGP++(+++) t+(++) 5-- X++ R*(+)@ !tv b-() DI(+) D+(+++) G(+)
e$@ h++(*) r--++ y--()
-- ---END GEEK CODE BLOCK--
--
Check out these few php utilities that I released
 under the GPL2 and that are meant for use with a 
 php cli binary:
 
 http://www.vlaamse-kern.com/sas/
--

--


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

Re: Alter Column Name?

[Jigal van Hemert]

From: Scott Purcell
 I cannot find any information on this, so I am thinking the answer is no.
But I have a column that I would like to change the name of:

 current the column name isID, and I would like it to change it to
CHILD_ID, as the the software is changing, and we are adding  parent_id
column.

Answer is yes ;-)

For example:

ALTER TABLE `tablename` CHANGE `ID` `CHILD_ID` INT(14) NOT NULL DEFAULT '0';

So you must repeat the original column definition after the new name.
http://dev.mysql.com/doc/mysql/en/alter-table.html

Regards, Jigal.


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

Re: alter table - add a column

[Jigal van Hemert]

From: Philippe Poelvoorde

  No, you forgot the DROP PRIMARY KEY ;-)
 
 well not really, that's the statement before ! ;-)

Sorry, I misread your mail.

 So is that normal that I can't specify BEFORE username ?
Yes, because MySQL only supports AFTER `column_name` ;-P

http://dev.mysql.com/doc/mysql/en/alter-table.html
See? It's either FIRST or AFTER col_name!

Regards, Jigal.

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

RE: [SPAM] - Unable to install mysql - Bayesian Filter detected spam

[Kevin Struckhoff]

Yannick,

MySQL is very picky in how it handles security, or at least different.
It has an extra layer of complexity, compared to say, the Informix
RDBMS, which uses the OS user/password and grant statements. With that
being said, have you run the post-install steps to add users to the
mysql database grant tables user  db? Refer to section 2.9 of the MySQL
Reference Manual, especially section 2.9.3. Good Luck.

Kevin Struckhoff 
Customer Analytics Mgr.
NewRoads West

Office 818.253.3819 Fax 818.834.8843
[EMAIL PROTECTED]

-Original Message-
From: Yannick [mailto:[EMAIL PROTECTED] 
Sent: Monday, June 13, 2005 11:42 AM
To: mysql@lists.mysql.com
Subject: [SPAM] - Unable to install mysql - Bayesian Filter detected
spam

Hey guys,

I am not able to install properly mysql. Please see below the technical
details or the bug report.

The installation goes well until I try to add the root user :

[EMAIL PROTECTED]:/usr/bin mysqladmin -u root -h fujitsu password x
mysqladmin: connect to server at 'fujitsu' failed
error: 'Host 'fujitsu.local' is not allowed to connect to this MySQL
server'
[EMAIL PROTECTED]:/usr/bin mysqladmin -u root -h 192.168.234.2 password
xx
mysqladmin: connect to server at '192.168.234.2' failed
error: 'Host '192.168.234.2' is not allowed to connect to this MySQL
server'
[EMAIL PROTECTED]:/usr/bin


Continuously, and I've tried lots of combinations, he is failing to
connect
to the server.

Please give me so hints for me to be able to install it.

Best regards

Yannick

[EMAIL PROTECTED]



SEND-PR: -*- send-pr -*-
SEND-PR: Lines starting with `SEND-PR' will be removed automatically, as
SEND-PR: will all comments (text enclosed in `' and `').
SEND-PR:
From: yannick
To: mysql@lists.mysql.com
Subject: [50 character or so descriptive subject here (for reference)]

Description:
precise description of the problem (multiple lines)
How-To-Repeat:
code/input/activities to reproduce the problem (multiple
lines)
Fix:
how to correct or work around the problem, if known (multiple
lines)

Submitter-Id:  submitter ID
Originator:Yannick Vauloup
Organization:
 organization of PR author (multiple lines)
MySQL support: [none | licence | email support | extended email support
]
Synopsis:  synopsis of the problem (one line)
Severity:  [ non-critical | serious | critical ] (one line)
Priority:  [ low | medium | high ] (one line)
Category:  mysql
Class: [ sw-bug | doc-bug | change-request | support ] (one
line)
Release:   mysql-4.0.15 (Source distribution)
Server: /usr/bin/mysqladmin  Ver 8.40 Distrib 4.0.15, for suse-linux on
i686
Copyright (C) 2000 MySQL AB  MySQL Finland AB  TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license

Server version  4.0.15-Max
Protocol version10
Connection  Localhost via UNIX socket
UNIX socket /var/lib/mysql/mysql.sock
Uptime: 5 min 8 sec

Threads: 1  Questions: 14  Slow queries: 0  Opens: 6  Flush tables: 1
Open
tables: 0  Queries per second avg: 0.045
C compiler:gcc (GCC) 3.3.1 (SuSE Linux)
C++ compiler:  g++ (GCC) 3.3.1 (SuSE Linux)
Environment:
machine, os, target, libraries (multiple lines)
System: Linux fujitsu 2.4.21-286-default #1 Sat Apr 2 08:57:10 UTC 2005
i686
i686 i386 GNU/Linux
Architecture: i686

Some paths:  /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc
/usr/bin/cc
GCC: Lecture des spécification à partir de
/usr/lib/gcc-lib/i586-suse-linux/3.3.1/specs
Configuré avec:
../configure --enable-threads=posix --prefix=/usr
--with-local-prefix=/usr/l
ocal --infodir=/usr/share/info --mandir=/usr/share/man --libdir=/usr/lib
--e
nable-languages=c,c++,f77,objc,java,ada --disable-checking
--enable-libgcj -
-with-gxx-include-dir=/usr/include/g++ --with-slibdir=/lib
--with-system-zli
b --enable-shared --enable-__cxa_atexit i586-suse-linux
Modèle de thread: posix
version gcc 3.3.1 (SuSE Linux)
Compilation info: CC='gcc'
 CFLAGS='-O2 -march=i586 -mcpu=i686 -fmessage-length=0 -DPIC -fPIC'
CXX='g++'
FLAGS='-O2 -march=i586 -mcpu=i686 -fmessage-length=0
-felide-constructor
s   -fno-exceptions
-fno-rtti   -fPIC -DPIC'  LDFLAGS=''
ASFLAGS=''
LIBC:
-rwxr-xr-x1 root root  1469811 2003-09-24 01:05
/lib/libc.so.6
-rw-r--r--1 root root 13553180 2003-09-23 18:04
/usr/lib/libc.a
-rw-r--r--1 root root  204 2003-09-23 18:04
/usr/lib/libc.so
lrwxrwxrwx1 root root   20 2005-02-22 14:05
/usr/lib/libc-client.so - libc-client.so.2002d
-rwxr-xr-x1 root root   770436 2003-09-23 20:29
/usr/lib/libc-client.so.2002d
Configure command: ./configure '--disable-shared'
'--enable-thread-safe-client' '--with-mysqld-ldflags=-static'
'--with-client-ldflags=-static' '--without-berkeley-db'
'--with-extra-tools'
'--without-innodb' 

Need Help on C with MySQL in win2000...

[Ashok Kumar]

Dear friends,
 I'm using win2000 with VC++. In this now i wrote one
console application for invoking MySQL Database. After
creating the .exe file, if i try to run that means its
giving the error that can't find the specified file
libmysql.dll. So i added the path of limysql.dll in
env path variables and now it's working well.

 But my questions is, is it possible to embed that dll
file with my project itself, rather than adding the
path to env path var. when i'll try to run that means
it never ask for the path. Is it possible?

Thanks and Regards,
 Ashok Kumar.P.S

__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

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

Re: Need Help on C with MySQL in win2000...

[Peter Brawley]

Ashok


is it possible to embed that dll
file with my project itself, rather than adding the
path to env path var. when i'll try to run that means
it never ask for the path. Is it possible?


There are 3P tools which do that (Google for embed dll), but wouldn't 
it be simpler to put the dll in the same dir as the .exe, or in 
%windir%\system32?


PB

-

Ashok Kumar wrote:


Dear friends,
I'm using win2000 with VC++. In this now i wrote one
console application for invoking MySQL Database. After
creating the .exe file, if i try to run that means its
giving the error that can't find the specified file
libmysql.dll. So i added the path of limysql.dll in
env path variables and now it's working well.

But my questions is, is it possible to embed that dll
file with my project itself, rather than adding the
path to env path var. when i'll try to run that means
it never ask for the path. Is it possible?

Thanks and Regards,
Ashok Kumar.P.S

__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

 




--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.323 / Virus Database: 267.6.8 - Release Date: 6/11/2005


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

Re: Slow query: optimizer ignores index, using filesort

[Scott Gifford]

Thanks for your response, Jigal.  More below...

Jigal van Hemert [EMAIL PROTECTED] writes:

 From: Scott Gifford

[...]

 Apparently MySQL's optimizer sees that it can use the primary key for
 mirealsource_home_supplemental to do the query, but for some reason
 decides not to.

 This is often the case when the query will probably return more than 30% of
 the records in that table. In such cases it is more efficient to do a full
 table scan (which is indicated here by the 'ALL' type in the explain
 output).

Right, ALL would be a great plan if it weren't for the LIMIT 1.  

 I can fix the problem by requiring a STRAIGHT_JOIN, or by sorting by
 mirealsource_homes_supplemental.mls_num instead.  However, this
 query is part of a larger framework that handles a wide variety of
 queries, so I need to understand why this is happening instead of
 tweaking individual cases.

 Furthermore MySQL can only use an index for sorting if all columns in the
 ORDER BY clause are from the first table in the explain output that doesn't
 have a 'const' join type. This is why setting the ORDER BY to
 mirealsource_homes_supplemental.mls_num will remove the 'Using filesort' and
 result in faster sorting.

I'm a little surprised MySQL can't figure out on its own that it can
get the same effect by using mls_num from either table, since the
tables are joined on it, so the values will always be identical.

 MySQL tries to optimize queries by (among others) guestimating which path
 will result in the smallest number of records. It appears that this path
 with 100 * 8 * 8 * 8 (51200) records is the minimum size it can achieve.

It looks to me like it's actually 100 * 1 * 1 * 1 = 100 (8 is the key
length), which is the same as what I get when I force a STRAIGHT_JOIN.
So with two plans that will use the same number of records, I'm
surprised MySQL doesn't choose the one that will allow it to use an
index.

 You can use USE INDEX to (strongly) suggest the use of an index to MySQL and
 see if it speeds up the query. Using  FORCE INDEX will tell MySQL that a
 full table scan is very expensive, so this will make it extremely unlikely
 that it will not use the index.

I know there are a couple of tricks like that to fix this one query.
What makes this hard is that that query is one of many that can be
generated by a CGI-to-SQL search gateway.  If I put in a FORCE INDEX
(mls_num) and the user searches by price, for example, query
performance will be very bad.  In order to know the right indexes to
force, as far as I can tell I'd have to implement my own optimizer,
which seems somewhat excessive.

[...]

 In this query you want the data where mls_num is as small as possible. Is
 there a way you can limit the number of records by using an extra where
 condition? This way you may change the order of the tables and make the
 query faster.

I tried that using mirealsource_homes.mls_num in the WHERE clause and
it didn't make a difference.

 I would also move the current WHERE condition to an ON condition in the FROM
 part as it is not meant to limit the selected records, but as a definition
 on how to join the two tables.

I tried that too, and it didn't make a difference (though I agree it
is clearer).

I also tried installing a copy of 4.1.12-standard to see if the
situation was better, but the results were identical.

And, I tried setting max_seeks_for_key to a low number (I tried 100,
10, 1, and 0), which is supposed to discourage tables scans, and that
didn't help.

Are there any other global things I can try, to tell MySQL to avoid
table scans?  The queries almost always use at most LIMIT 10, so a
table scan is generally not the right idea

Scott.


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