Re: query over several tables

[Michael Stassen]

Philippe Rousselot wrote:

Hi

I have three linked tables : store, catalogue, and sales

store : ID_store, store, date
catalogue : ID_product, product
sales : ID_sales, ID_store, ID_product, product, quantity
I would like a view giving me ALL the products in catalogue with the
quantity per store if the store has this product, and zero or null (or
anything) if the store does not have it
ex.:

store 
ID_store 1  2
storenew york   paris
date	2003-10-10  2003-10-11

catalogue 
ID_product   12 3
product  tablechair lamp

sales
ID_sales 1   2   3
ID_store 1   1   2 
ID_product   1   2   3
product  table   chair   lamp
quantity 3   2   4

look for product and quantity in store new york
view
product  table chair lamp
quantity 3 2  0
look for product and quantity in store Paris
view
product  table chair lamp
quantity 0 0  4
thanks in advance

Philippe
You need a LEFT JOIN, and your store name/id filter needs to be in the 
left join's ON clause, rather than in the where clause.  Here are a few 
examples, starting with a listing by store:

SELECT store.store, cat.product, sales.quantity
FROM store, catalogue AS cat
LEFT JOIN sales
ON (cat.ID_product=sales.ID_product AND sales.ID_store=store.ID_store)
ORDER BY store.store, cat.ID_product;
+--+-+--+
| store| product | quantity |
+--+-+--+
| New York | table   |3 |
| New York | chair   |2 |
| New York | lamp| NULL |
| Paris| table   | NULL |
| Paris| chair   | NULL |
| Paris| lamp|4 |
+--+-+--+
Change the NULL quantities to 0, using IFNULL:

SELECT store.store, cat.product, IFNULL(sales.quantity,0) quantity
FROM store, catalogue AS cat
LEFT JOIN sales
ON (cat.ID_product=sales.ID_product AND sales.ID_store=store.ID_store)
ORDER BY store.store, cat.ID_product;
+--+-+--+
| store| product | quantity |
+--+-+--+
| New York | table   |3 |
| New York | chair   |2 |
| New York | lamp|0 |
| Paris| table   |0 |
| Paris| chair   |0 |
| Paris| lamp|4 |
+--+-+--+
Only look at store 1:

SELECT store.store, cat.product, IFNULL(sales.quantity,0) quantity
FROM store, catalogue AS cat
LEFT JOIN sales
ON (cat.ID_product=sales.ID_product AND sales.ID_store=store.ID_store)
WHERE store.ID_store=1;
+--+-+--+
| store| product | quantity |
+--+-+--+
| New York | table   |3 |
| New York | chair   |2 |
| New York | lamp|0 |
+--+-+--+
Only look at store in Paris:

SELECT cat.product, IFNULL(sales.quantity,0) quantity
FROM store, catalogue AS cat
LEFT JOIN sales
ON (cat.ID_product=sales.ID_product AND sales.ID_store=store.ID_store)
WHERE store.store='Paris';
+-+--+
| product | quantity |
+-+--+
| table   |0 |
| chair   |0 |
| lamp|4 |
+-+--+
Hope that helps.

Michael

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

Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?

[Martijn Tonies]

Hi,

   Jerry,
Interbase/Firebird looks good on paper. But there are a
couple of
   things to watch out for.
  
   1) Check the Borland Interbase newsgroups for corruption to see if
it is
   still an issue. I know from reading their newsgroups a few years back
,
   there were quite a few postings. It could of been due to inadequate
   hardware or some external source like a misconfigured server or power
   failure. There are many sites that use IB without such problems but it
is
   worth checking it out (especially how to successfully repair a damaged
   IB/FB database because all the tables are stored in one file).
 
 Most of these problems have been fixed in Firebird.
 
   2) Speed problems. I don't know how much data your tables will have,
but
   IB/FB has a habit of slowing down as more rows are added/deleted.
 
 Really? Howcome there are many people using Firebird without speed
 problems?

 It depends on the size of the tables. WIth 50k rows you're not going to
 notice it. With 50 million rows the slow down becomes apparent.

Then what are you doing? Summing all rows? Counting them? (Cause
a count is slow in InterBase/Firebird). Indexed searches etc should
be nearly as fast as with 50K rows.


  They have
   an automated Sweep process that cleans out the updated/deleted rows
   (deleted  updated rows still take up space until the database is
swept).
   Even so a large db will still slow down. I suspect the problem is due
to
   unbalanced indexes. The only way to fix it is to unload all your data
and
   reload it back in. Some people do it once a week.
 
 That's not true. The sweep process doesn't kick in until there's actually
 a problem with long running transactions and new transactions.

 Updated and deleted rows will remain in the table until a sweep is done.
If
 100,000 rows are updated, then the old rows will remain in the table and
 the sweep cleans them out. A heavily updated table will need to be swept
 often. When sweeps fail to speed up the table, the data  has to be
unloaded
 and reloaded. That's not something you want to do very often if  you want
 to have a 24/7 installation especially with millions of rows.

There are plenty of systems running or almost running 24/7 without
an unload/reload. The sweep process is different from the garbage
collection process, which is what you are describing. Perhaps that's
where I got confused when you started about sweeping. The GC
process has been improved in the recent years in Firebrid and in IB 7
as well. Also, you cannot sweep a table: if you want to sweep, you
will sweep the entire database.

   3) The server CPU load will increase rapidly after the first 5 or 6
users.
   In other words IB/FB requires a much faster CPU than the same number
of
   users for MySQL. It is a very CPU intensive DB server.
 
 Most CPU problems are related to the above mentioned problems.
 This isn't usually the case. IB/Fb is actually more of a disk-intensive
 database engine, because of the ability to use very little memory.
 However, Firebird has some new stuff (and IB does too) that allows
 it to use more memory when available (and allowed) making it even
 faster.

 IB eats up a lot of memory on large queries, something that should be
 avoided if at all possible. All queries are fetched into memory, and when
 physical memory is exhausted, IB will use the page file (in Windows) and
 swaps the physical memory out to disk. This can be *very* disk intensive.
 I've had IB 6 leave as little as 5MB of physical memory and Windows was
 pretty much dead in the water until the query was closed. (Large queries
 are necessary for report or when summarizing data.) If this has been
 improved in the latest IB/FB version, then this memory problem should be a
 non-issue. The more memory you can throw at IB, the better.

I'm surprised here. Actually, the much heard complaint about IB/Fb is that
it takes _too little_ memory and starts swapping to disk too soon. If you're
having IB eating memory, you have a different problem.

  There was an earlier
   problem with IB Super Server not running well on multiple processors
(it
   actually slowed things down). I believe this has been fixed in IB
version
   6.5. I don't know if this was fixed in FB because FB may be running IB
   Classic (single processor) version.
 
 Classic is not the single processor version, it runs much better
 on multiple CPUs than the SuperServer architecture.
 
 SS still has these problems on multiple CPU machines, Classic does
 not.

 I was under the impression that Classic version could have only 1 thread
 running at one time (each connection is a separate process), it would
block
 other threads if they referenced the same table(s).

There's no blocking in IB/Fb. Never. Except for a transaction lock (writers
blocking other writers). In heavy systems, people prefer the Classic
architecture because it runs better and you can throw in additional CPUs.

 The SuperServer was was
 suppose to solve that with a 

Re: Unable to insert String constants through my function

[Michael Stassen]

shiva shankar wrote:

Hi, 

I USE THIS FUNCTION TO INSERT VALUES TO DIFFERENT
VALUES OF MY DATABASE..
void InsertValues(const string tableName, const
string values)
{
 char ValuesToStore[255];
  long datasize;
  char *EncodedData;
  char *query;
  strcpy(ValuesToStore, values.c_str());
  EncodedData = new char[strlen(ValuesToStore)*2 + 1];
  datasize = mysql_real_escape_string(conn,
EncodedData, ValuesToStore, strlen(ValuesToStore));
  query = new char[datasize+255];
  sprintf(query, INSERT INTO %s %s VALUES %s, 
 tableName.c_str(),
(lookup[tableName.c_str()]).c_str(), EncodedData); 
  mysql_real_query(conn, query, strlen(query)+255);

  // if the insertion involves an auto-incremented
attribute, 
  //   the following line displays the value of that
attribute.
  cout  tableName   ID inserted =  
mysql_insert_id(conn)  endl;
 coutquery;
  delete [] EncodedData;
  delete [] query;
  return;
}

NOW WHEN I CALL THIS FUNCTION IN MAIN TO INSERT VALUES
LIKE,
  InsertValues(comm_protos, (AwaterKent, 56));
  
  InsertValues(sensor, (1, 1, 0, 0, 0, 3, 0));
 
  InsertValues(map_sensor_platform, (1, 1));
  

Only the first InsertValues doesnt work,  It is unable
to understand the string constant 'AwaterKent'
When I print out the query in InsertValues it reads as

INSERT INTO comm_protos (name, datarate) VALUES
(AtwaterKent, 56)Values inserted into 'comm_protos'
I changed to this and none of these works as well:

InsertValues(comm_protos, ('AtwaterKent', 56));
The query prints out as
INSERT INTO comm_protos (name, datarate) VALUES
(\'AtwaterKent\', 56)Values inserted into
'comm_protos'
When I tried this,

InsertValues(comm_protos, (\'AtwaterKent\', 56));

The query printed as:
INSERT INTO comm_protos (name, datarate) VALUES
(\'AtwaterKent\', 56)Values inserted into
'comm_protos'


PLEASE LET ME KNOW HOW I NEED TO PASS THE STRING
CONSTANT OR IF I NEED TO MAKE ANY CHANGE IN MY
INSERTVALUES FUNCTION..
THANKS A LOT,
SHIV
The query needs to look like this:

INSERT INTO comm_protos (name, datarate) VALUES ('AtwaterKent', 56)

I'd say you need to fix your InsertValues function, as it mishandles 
strings.  It should either quote them, or it should expect them to 
arrive quoted and then NOT escape the quotes.  Personally, I think the 
former is better, but then the question arises as to how to tell which 
things should be quoted.  Either way, the string needs to be escaped 
before being wrapped in quotes, so that King's Cross becomes 'King\'s 
Cross' rather than 'King's Cross' or \'King\'s Cross\'.

Michael

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

Re: Problem with replication

[Andrey Kotrekhov]

SQL

Hi!

Does anybody have any solution?

I change time where query run.
Each new day where query run first ones in day replication crash.
Slave start on slave server restore replication.
How can it be?
I have no any ideas.

Both servers have 4.0.16

 SQL
 Hi all!

 Nearly very night I have this problem.
 This query is done every 3 hours. But problem is happened only at 3:00
 Both master and slave are 4 CPU (2 with HTT) computers.
 Both have mysql 4.0.16
 And when I test table analit. There are many records which have the same
 conditions as in WHERE statement.
 This records are inserted into analit before this UPDATE.
 UPDATE finds records on master, I have checked the records on master and
 I see the result of UPDATE.
 But there is the error on slave server.
 If I run any query on analit like select count(*) from analit where
 label=1070931600 and after this SLAVE STOP; SLAVE START
 problem is solved.

 Is it a bug in server?
 Can mysqld break the order of replication query on multy-CPU
 computers?

 Any ideas, please.

 ++-+-+---+
 -+-+--+---+
 ---+--+---+
 -+-++
 -
 -
 -
 -
 -
 +--+-
 +-+
 _ Master_Host_ Master_User _ Master_Port _ Connect_retry _
  Master_Log_File _ Read_Master_Log_Pos _ Relay_Log_File   _ Relay_Log_Pos _
  Relay_Master_Log_File _ Slave_IO_Running _ Slave_SQL_Running _
  Replicate_do_db _ Replicate_ignore_db _ Last_errno _
  Last_error




 _
  Skip_counter _ Exec_master_log_pos _ Relay_log_space _
 ++-+-+---+
 -+-+--+---+
 ---+--+---+
 -+-++
 -
 -
 -
 -
 -
 +--+-+
 -+
 _ XXX _ Z   _ _ 60_
 a0-bin.029
 _ 2210728 _ a1-relay-bin.001 _ 5880071   _ a0-bin.029
   _ Yes  _ No_ atas_
  _ 1032   _
  Error 'Can't find record in 'analit'' on query 'UPDATE analit, price_grp
 SET analit.status=price_grp.status, analit.currency=price_grp.currency
 WHERE analit.price=price_grp.price AND analit.service=price_grp.service
 AND analit.city=price_grp.city  AND label=1070931600'.
 Default database: '' _ 0_ 2210187 _
  5880612 _


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



Best regards.
___
Andrey Kotrekhov [EMAIL PROTECTED]
ISP Alkar Teleport
. +380 562 34-00-44

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

Re: Fw: Bug in 4.1.1 Internationalization on Windows2000 ?

[Juri Shimon]

Thanks for reply!

  1. mysql -u root -e create database t; show create database t; drop
database t
  2. mysql -u root -e use_mysql; create database t; show create database
t; drop database t
  1.| t| CREATE DATABASE `t` /*!40100 DEFAULT CHARACTER SET
cp1251 */
  2.| t| CREATE DATABASE `t` /*!40100 DEFAULT CHARACTER SET
latin1 */

 It's not a bug.
 character_set_database indicates the current database character set.

May be, it wasn't clear.
I say:
when a current database (result of use ...) in latin1, then create
database ... create database in latin1. But default mysql character set is
cp1251! Database, IMHO, must be created in DAFAULT charset. And, IMHO, this
is a bug 8)

Another things:

1. mysql -u root -e select SUBSTRING_INDEX(USER(),'@',1)
ERROR 1266 at line 1: Illegal mix of collations (utf8_general_ci,IMPLICIT)
and (
cp1251_general_ci,COERCIBLE) for operation 'substr_index'

2. when I create enum fields in cp1251, then show columns from ... return
a '','',... while all checks, inserts and so work ok. I don't know,
2.1. it's common ciryllic troubles (console in cp866, gui in cp1251)
2.2. it's my local troubles (english win 2 with ciryllic charset)
2.3 it's mysql troubles (system charset utf8, mysql charset cp1251). NB.
in table.frm I see all my enums in selected charset. I think, there is
'phantom' recharacting utf8-cp1251.

And what are Your comments?
WBR!


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

Re: foreign keys.

[Victoria Reznichenko]

Mofeed Shahin [EMAIL PROTECTED] wrote:
 
 mysql CREATE TABLE foo ( 
-  ID INT PRIMARY KEY, 
-  note VARCHAR(50), 
-  First_Name VARCHAR(50), 
-  Last_Name VARCHAR(50), 
-  FOO_ID INT, 
-  INDEX(FOO_ID), 
-  INDEX(First_Name, Last_Name), 
-  FOREIGN KEY (FOO_ID) REFERENCES foo(ID), 
-  FOREIGN KEY (First_Name, Last_Name) REFERENCES Blah 
 (First_Name, Last_Name)
-  ) TYPE=INNODB;
 ERROR 1005: Can't create table './moftest/foo.frm' (errno: 150)
 

What is version of MySQL server? What default-character-set do you use?
 
 On Tue, 16 Dec 2003 07:54 pm, Victoria Reznichenko wrote:
 Mofeed Shahin [EMAIL PROTECTED] wrote:
  Still doesn't work But thanks for trying.

 Worked fine for me:

 mysql CREATE TABLE Blah (
 - ID INT PRIMARY KEY,
 - Fname VARCHAR (50),
 - Lname VARCHAR (50),
 - UNIQUE (Fname, Lname)
 - ) TYPE=INNODB;
 Query OK, 0 rows affected (0.11 sec)

 mysql CREATE TABLE foo (
 - ID INT PRIMARY KEY,
 - note VARCHAR(50),
 -  Fname VARCHAR(50),
 - Lname VARCHAR(50),
 - FOO_ID INT,
 - INDEX(FOO_ID),
 - INDEX(Fname, Lname),
 - FOREIGN KEY (FOO_ID) REFERENCES foo(ID),
 - FOREIGN KEY (Fname, Lname) REFERENCES Blah (Fname, Lname)
 - ) TYPE=INNODB;
 Query OK, 0 rows affected (0.10 sec)



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Victoria Reznichenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
   ___/   www.mysql.com





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

good ways to start a multiuser-environment

[[EMAIL PROTECTED]]

hello,

i'm working on a java-jsp-page with mysql-connection. now i've created a 
test-system with myisam-tables where the tables are just read. my plans 
for next weeks are, to make the owner of the site capable of 
entering/changing his data by his own - web-based and multi-user-capable.

as this is - for me - a big jump, i want to ensure myself that i go best 
ways right from the beginning. therefore i hope to get some useful hints 
from you.

my questions are especially:

* should i use innodb-tables for the entering-system (because of 
transaction-safty-reasons...)?
* if yes: how can i change them for the real-system (the homepage), 
that uses packed myisam-tables (because there is no writing, only 
reading from db!).
* what are the best ways to get the data from entering-system to 
real-system? do i have to shut-down the mysql-server each time the 
data is updated?
* what else must i think of?

thank you very much for your hints!
greetings
hans horwath
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Invalid ENUM values after upgrading from 4.0 to 4.1

[Juri Shimon]

I have the same problem with cp1251 and, seems, it's a bug. I have posted
correspoding message two days ago. Without any reply  8(((
I migrate 3.23.55 - 4.1.1 and I don't know about existing such problem in
4.0.
Look in your table.frm - your field is in correct charset. More of,
inserts and updates of your tables are working correct (check it ;). The
problem, IMHO, is in 'show columns ...' implementation. For me, it's
critically (in my apps, I grab allowed values from table structure).


 Hi,

 I'm facing a problem with the new 4.1 branch.
 I've built MySQL from sources with default-language=hebrew and
 extra-languages=utf.
 I've also used default-collation=hebrew_general_ci.

 After installing, this is what I get for few of the variables:

 mysql show variables like '%char%';
 +--++
 | Variable_name| Value  |
 +--++
 | character_set_server | hebrew |
 | character_set_system | utf8   |
 | character_set_database   | hebrew |
 | character_set_client | hebrew |
 | character_set_connection | hebrew |
 | character-sets-dir   | /usr/local/mysql/share/mysql/charsets/ |
 | character_set_results| hebrew |
 +--++
 7 rows in set (0.00 sec)

 mysql show variables like '%coll%';
 +--+---+
 | Variable_name| Value |
 +--+---+
 | collation_connection | hebrew_general_ci |
 | collation_database   | hebrew_general_ci |
 | collation_server | hebrew_general_ci |
 +--+---+
 3 rows in set (0.00 sec)

 Sounds great, right?

 But when I try to work with databases, and their tables, that were
upgraded
 from 4.0 with the new 4.1 server, I see question marks (???) for values in
 ENUM() field type. By the way, I noticed that default values for such
fields
 (when having ``NOT NULL``) display perfectly in Hebrew. Explanation?

 Is there any specific ``upgrade procedure`` I should do to overcome this
 problem?

 Thanks in advance.

 Noor




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

Re: Invalid ENUM values after upgrading from 4.0 to 4.1

[Igor Zinkovsky]

It's known bug, will be fixed in 4.1.2

- Original Message - 
From: Juri Shimon [EMAIL PROTECTED]
To: [EMAIL PROTECTED]; Noor Dawod [EMAIL PROTECTED]
Sent: Wednesday, December 17, 2003 12:37 PM
Subject: Re: Invalid ENUM values after upgrading from 4.0 to 4.1


 I have the same problem with cp1251 and, seems, it's a bug. I have posted
 correspoding message two days ago. Without any reply  8(((
 I migrate 3.23.55 - 4.1.1 and I don't know about existing such problem in
 4.0.
 Look in your table.frm - your field is in correct charset. More of,
 inserts and updates of your tables are working correct (check it ;). The
 problem, IMHO, is in 'show columns ...' implementation. For me, it's
 critically (in my apps, I grab allowed values from table structure).


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

RE: Issues with count(), aliases, and LEFT JOINS

[Brandon Ewing]

 -Original Message-
 From: Brandon Ewing [mailto:[EMAIL PROTECTED] 
 Sent: Monday, December 15, 2003 2:28 AM
 To: [EMAIL PROTECTED]
 Subject: Issues with count(), aliases, and LEFT JOINS
 
 Greetings,
 
 This is driving me crazy.
 
 I'm running MySQL 4.0.15-standard.
 
 I've got a db that tracks switches, servers they connect to, 
 and connections
 between switches.

snip

 
 I've been pounding my head against this for a while now - can 
 anyone offer
 any illumination as to what exactly I'm screwing up?
 
 I'd prefer not to do any major schema re-altering, but if I 
 must, I must.
 
 Brandon Ewing
 
 
 
 -- 
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:
 http://lists.mysql.com/[EMAIL PROTECTED]
 
 

No insight on any of this?  Is it a problem with my SQL statement/schema, or
have I run into what might be a bug?  Should I start testing with this
dataset on other servers?

Brandon Ewing


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

Problem creating new user.

[ads mysql]

Hi,
I can create new user with following.
mysqlINSERT INTO user (Host,User,Password) 
VALUES('localhost','ads11',PASSWORD('ads11'));
mysqlINSERT INTO db (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv, 
Create_priv,Drop_priv) VALUES 
('localhost','bankaccount','ads11','Y','Y','Y','Y','Y','Y');
mysql FLUSH PRIVILEGES;

But when I use Host 'www.allnet.com' in place of 'localhost' in both above mentioned 
INSERT command It enters values in to table but when I try to access database by
mysqlmysql -u ads11 -p bankaccount
mysql (Here I enter password 'ads11')
 
It gives following error :[EMAIL PROTECTED] root]# mysql -u ads11 -p bankaccount
Enter password: (here I enter password 'ads11')
ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES)
 
Domain www.allnet.com is registered, having live IP on which web server is running 
succefully.
 
Any clue about problem.
Thanks
 
 
 
 
 
 


-
Do you Yahoo!?
Free Pop-Up Blocker - Get it now

Re: Problem creating new user.

[jeffrey_n_Dyke]

Unless i'm misunderstanding(highly possible)...if you are on the command
line, you are going to be seen by mysql as localhost and not allnet.com.
You'll only be seen as another host if you connect remotely.  the webserver
running on the localmachine and the mysql client will be seen as localhost.

hth
Jeff


   
 
  ads mysql
 
  [EMAIL PROTECTED]To:   [EMAIL PROTECTED]
  
  om  cc: 
 
   Subject:  Problem creating new user.
 
  12/17/2003 07:45 
 
  AM   
 
   
 
   
 




Hi,
I can create new user with following.
mysqlINSERT INTO user (Host,User,Password)
VALUES('localhost','ads11',PASSWORD('ads11'));
mysqlINSERT INTO db
(Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,
Create_priv,Drop_priv) VALUES
('localhost','bankaccount','ads11','Y','Y','Y','Y','Y','Y');
mysql FLUSH PRIVILEGES;

But when I use Host 'www.allnet.com' in place of 'localhost' in both above
mentioned INSERT command It enters values in to table but when I try to
access database by
mysqlmysql -u ads11 -p bankaccount
mysql (Here I enter password 'ads11')

It gives following error :[EMAIL PROTECTED] root]# mysql -u ads11 -p bankaccount
Enter password: (here I enter password 'ads11')
ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES)

Domain www.allnet.com is registered, having live IP on which web server is
running succefully.

Any clue about problem.
Thanks








-
Do you Yahoo!?
Free Pop-Up Blocker - Get it now





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

Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?

[Ed Leafe]

On Dec 17, 2003, at 7:35 AM, Matthew Stanfield wrote:

Wouldn't it be great if all mailing lists and newsgroups were as 
friendly and helpful as the mysql ones are.
shameless plug

	I host a couple of lists: ProLinux and ProPython, which are for 
developers that are new to Linux and Python, and want help getting 
started. While the number of subscribers there are small, they are 
growing, and the atmosphere is very much like this list. Check 'em out 
at http://leafe.com/mailman/listinfo/prolinux and 
http://leafe.com/mailman/listinfo/propython, respectively.

/shameless plug

 ___/
/
   __/
  /
 /
 Ed Leafe
Linux Love:
unzip;strip;touch;finger;mount;fsck;more;yes;umount;sleep
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

MySQL 4.0.17 has been released

[Lenz Grimmer]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Hi,

MySQL 4.0.17, a new version of the popular Open Source/Free Software
Database Management System, has been released. It is now available in
source and binary form for a number of platforms from our download pages
at http://www.mysql.com/downloads/ and mirror sites.

Note that not all mirror sites may be up to date at this point in time -
if you can't find this version on some mirror, please try again later or
choose another download site.

This is a bugfix release for the current production version.

Please refer to our bug database at http://bugs.mysql.com/ for more
details about the individual bugs fixed in this version.

News from the ChangeLog:

Functionality added or changed:

   * `mysqldump' no longer dumps data for `MERGE' tables. (Bug #1846)

   * `lower_case_table_names' is now forced to 1 if the database
 directory is located on a case-insensitive file system. (Bug #1812)

   * Symlink creation is now disabled on systems where `realpath()'
 doesn't work. (Before one could use `CREATE TABLE .. DATA
 DIRECTORY=..' even if `HAVE_BROKEN_REALPATH' was defined. This is
 now disabled to avoid problems when running `ALTER TABLE').

   * Inserting a negative `AUTO_INCREMENT' value in a `MyISAM' table no
 longer updates the `AUTO_INCREMENT' counter to a big unsigned
 value.  (Bug #1366)

   * Added four new modes to `WEEK(..., mode)' function.  *Note
 `WEEK(date,mode)': Date and time functions.  (Bug #1178)

   * Allow `UNION DISTINCT' syntax.

   * `mysql_server_init()' now returns 1 if it can't initialize the
 environment. (Previously `mysql_server_init()' called `exit(1)' if
 it could not create a key with `pthread_key_create()'. (Bug #2062)

   * Allow spaces in Windows service names.

   * Changed the default Windows service name for `mysqld' from `MySql'
 to `MySQL'.  This should not affect usage, because service names
 are not case sensitive.

   * When you install `mysqld' as a service on Windows systems, `mysqld'
 will read startup options in option files from the option group
 with the same name as the service name.  (Except when the service
 name is `MySQL').

Bugs fixed:

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

   * One can now configure MySQL as a Windows service as a normal user.
 (Bug #1802). Thanks to Richard Hansen for fixing this.

   * Database names are now compared in lowercase in `ON' clauses when
 `lower_case_table_names' is set. (Bug #1736)

   * `IGNORE ... LINES' option to `LOAD DATA INFILE' didn't work when
 used with fixed length rows. (Bug #1704)

   * Fixed problem with `UNIX_TIMESTAMP()' for timestamps close to 0.
 (Bug #1998)

   * Fixed problem with character values greater than 128 in the
 `QUOTE()' function. (Bug #1868)

   * Fixed searching of `TEXT' with end space. (Bug #1651)

   * Fixed caching bug in multiple-table updates where same table was
 used twice.  (Bug #1711)

   * Fixed directory permissions for the MySQL-server RPM documentation
 directory.  (Bug #1672)

   * Fixed `UPDATE REPLACE' on invalid enum value. (Bug #2023)

   * `mysql' client program now correctly prints connection identifier
 returned by `mysql_thread_id()' as unsigned integer rather than as
 signed integer.  (Bug #1951)

   * `FOUND_ROWS()' could return incorrect number of rows after a query
 with an impossible `WHERE' condition. (Bug #1468)

   * `SHOW DATABASES' no longer shows `.sym' files (on Windows) that do
 not point to a valid directory. (Bug #1385)

   * Fixed a possible memory leak on Mac OS X when using the shared
 `libmysql.so' library. (from `pthread_key_create()'). (Bug #2061)

   * Fixed bug in `UNION' statement with alias `*'. (Bug #1249)

   * Fixed a bug in `DELETE ... ORDER BY ... LIMIT' where the rows
 where not deleted in the proper order. (Bug #1024, Bug #1697).

   * Fixed serious problem with multi-threaded programs on Windows that
 used the embedded MySQL libraries. (Locks of tables were not
 handled correctly between different threads).

   * Code cleanup: Fixed a few code defects (potential memory leaks,
 null pointer dereferences, uninitialized variables). Thanks to
 Reasoning Inc. for informing us about these findings.

   * Fixed a buffer overflow error which occured with zero prepended
 values in some columns of the type `DECIMAL'. (Bug #2128)

   * Filesort was never shown in `EXPLAIN' if query contained an `ORDER
 BY NULL' clause. (Bug #1335)

   * Fixed invalidation of whole query cache on `DROP DATABASE'. (Bug
 #1898)

   * Fixed bug in range optimizer that caused wrong results for some
 unlikely `AND'/`OR' queries. (Bug #1828)

   * Fixed a crash in `ORDER BY' when ordering by expression and
 identifier.  (Bug #1945)

  

Re: Problem creating new user.

[Duncan Hill]

On Wednesday 17 December 2003 12:45, ads mysql wrote:

 It gives following error :[EMAIL PROTECTED] root]# mysql -u ads11 -p
 bankaccount
 Enter password: (here I enter password 'ads11')
 ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password:
 YES)
 
 Domain www.allnet.com is registered, having live IP on which web server is

Your mysql command line statement provides no host to connect to, so it uses 
the local socket to communicate.  Mysql sees this as 'localhost'.

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

Grant access to named table in many DBs

[Duncan Hill]

Situation:
Multiple DBs with the same named table in each DB.

Needed:
Grant insert to a named login to the named table in all DBs.

Not wanted:
A grant for each db.named_table combo :)

Am I right in concluding that my not wanted solution is the only solution to 
this case?  Ie, I'll have to grant access to each DB.table individually 
(*.table doesn't work :( ).

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

Re: Problem creating new user.

[ads mysql]

I tried to connect as follows :
 
#
[EMAIL PROTECTED] root]# mysql -h www.allnet.com -u ads11 bankaccount
ERROR 1130: Host 'xxx-xx-xxx-xxx.yyy.com' is not allowed to connect to this MySQL 
server
#

It gives error.
I sthere anything giving permission to connect to mysql for particular Host?
 
Thanks for support.


Duncan Hill [EMAIL PROTECTED] wrote:
On Wednesday 17 December 2003 12:45, ads mysql wrote:

 It gives following error :[EMAIL PROTECTED] root]# mysql -u ads11 -p
 bankaccount Enter password: (here I enter password 'ads11')
 ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password:
 YES) 
 Domain www.allnet.com is registered, having live IP on which web server is

Your mysql command line statement provides no host to connect to, so it uses 
the local socket to communicate. Mysql sees this as 'localhost'.

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


-
Do you Yahoo!?
Free Pop-Up Blocker - Get it now

MySQL with SSL enabled

[rmck]

Hello,

I am in the process of installing Mysql 4.0.17. I need to have Mysql with SSL enabled. 

Does this feature come enabled with the binary download? Or do I need to download the 
source and compile
it? 

Any good links ot setting up MySQL with SSL enabled?

Thanks
Rob



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

Re: Problem creating new user.

[gerald_clark]

ads mysql wrote:

I tried to connect as follows :

#
[EMAIL PROTECTED] root]# mysql -h www.allnet.com -u ads11 bankaccount
ERROR 1130: Host 'xxx-xx-xxx-xxx.yyy.com' is not allowed to connect to this MySQL server
#
 

Your DNS is broken.

It gives error.
I sthere anything giving permission to connect to mysql for particular Host?

Use GRANT unless you know what you are doing.

Thanks for support.

Duncan Hill [EMAIL PROTECTED] wrote:
On Wednesday 17 December 2003 12:45, ads mysql wrote:
 

It gives following error :[EMAIL PROTECTED] root]# mysql -u ads11 -p
bankaccount Enter password: (here I enter password 'ads11')
ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password:
YES) 
Domain www.allnet.com is registered, having live IP on which web server is
   

Your mysql command line statement provides no host to connect to, so it uses 
the local socket to communicate. Mysql sees this as 'localhost'.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
-
Do you Yahoo!?
Free Pop-Up Blocker - Get it now
 



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

MySQL/InnoDB-4.0.17 is released

[Heikki Tuuri]

Hi!

InnoDB is a MySQL table type which is included in all GPL versions of
MySQL-4.0 that you can download from http://www.mysql.com, and is included
in the commercial MySQL license MySQL Pro, which you can buy from
https://order.mysql.com/?sub=pgpg_no=1.

You can buy technical support for InnoDB from
http://www.mysql.com/support/index.html. Remember that you have to buy an
ADVANCED support contract WITH the InnoDB OPTION. You have to check the box
on the second order page to include the InnoDB support option.

InnoDB is the only MySQL table type which supports transactions, row level
locking, multiversioned concurrency control, and foreign key constraints. An
separate online binary hot backup tool for InnoDB can be bought from
http://www.innodb.com/order.php.

...

The MySQL/InnoDB version 4.0 is the stable version which is recommended for
production use. Release 4.0.17 of InnoDB contains only a few bug fixes
associated with column prefix secondary indexes, for example,

CREATE TABLE t (a VARCHAR(255), INDEX a(10)) TYPE=InnoDB;

and UPDATEs of secondary index columns when the new updated value is
alphabetically equivalent, e.g.,

'abcde' - 'aBCdé'.

There are several not-yet-fixed non-critical bugs in InnoDB-4.0.17. Their
fixing was delayed because of the effort to release 4.1.1 earlier this
month.

...

The full changelog for InnoDB-4.0.17:

* Fixed a bug: if you created a column prefix secondary index and updated it
so that the last characters in the column prefix were spaces, InnoDB would
assert in row0upd.c, line 713. The same assertion failed if you updated a
column in an ordinary secondary index so that the new value was
alphabetically equivalent, but had a different length. This could happen,
for example, in the utf-8 character set if you updated a letter to its
accented or umlaut form.

* Fixed a bug: InnoDB could think that a secondary index record was not
locked though it had been updated to an alphabetically equivalent value,
e.g., 'abc' - 'aBc'.

* Fixed a bug: if you updated a secondary index column to an alphabetically
equivalent value, and rolled back your update, InnoDB failed to restore the
field in the secondary index to its original value.

Best regards,

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


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

Re: Problem creating new user.

[ads mysql]

Hi,
I tried to create user with GRANt as follows :
 
mysql use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON bankaccount.* TO 
ads123@'www.networkshastrihall.com' IDENTIFIED BY 'ads123';
Query OK, 0 rows affected (0.00 sec)
mysql FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

I think this is not entering into table.
 
 
[EMAIL PROTECTED] root]# mysql -h www.networkshastrihall.com -u ads123 -p bankaccount
Enter password:
ERROR 1130: Host '202-63-167-192.exatt.com' is not allowed to connect to this MySQL 
server.
 
DNS www.networkshastrihall.com is working.

Help appreciated.
Thanks


gerald_clark [EMAIL PROTECTED] wrote:


ads mysql wrote:

I tried to connect as follows :
 
#
[EMAIL PROTECTED] root]# mysql -h www.allnet.com -u ads11 bankaccount
ERROR 1130: Host 'xxx-xx-xxx-xxx.yyy.com' is not allowed to connect to this MySQL 
server
#
 

Your DNS is broken.

It gives error.
I sthere anything giving permission to connect to mysql for particular Host?
 

Use GRANT unless you know what you are doing.

Thanks for support.


Duncan Hill wrote:
On Wednesday 17 December 2003 12:45, ads mysql wrote:

 

It gives following error :[EMAIL PROTECTED] root]# mysql -u ads11 -p
bankaccount Enter password: (here I enter password 'ads11')
ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password:
YES) 
Domain www.allnet.com is registered, having live IP on which web server is
 


Your mysql command line statement provides no host to connect to, so it uses 
the local socket to communicate. Mysql sees this as 'localhost'.

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


-
Do you Yahoo!?
Free Pop-Up Blocker - Get it now
 




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


-
Do you Yahoo!?
New Yahoo! Photos - easier uploading and sharing

RE: Null-safe equal help, please

[Paul DuBois]

Please reply to the list so that others can follow this
discussion.  Thanks.
At 8:26 -0700 12/17/03, Knepley, Jim wrote:


-Original Message-
From: Paul DuBois [mailto:[EMAIL PROTECTED]
Sent: Tuesday, December 16, 2003 5:44 PM
To: Knepley, Jim; [EMAIL PROTECTED]
Subject: Re: Null-safe equal help, please
At 15:22 -0700 12/16/03, Knepley, Jim wrote:
I've got a WHERE clause:
WHERE possibly_null_value IS NULL
That works fine. This null-safe equal doesn't do what I expect:
WHERE possibly_null_value = NULL
The manual, and my testing, shows that NULL = NULL evaluates to 1,
so

Are you saying that this is not what you expect?  Why not?
It is what I expect, but it doesn't seem to be the behavior.
You indicated before that your own testing shows that NULL = NULL
evaluates to 1.
You now say that this *doesn't* seem to be the behavior.

I don't follow you.

snip

What I _really_ want to do is this:
WHERE  possibly_null_value = INET_ATON(IP)
...so that if no IP is specificied it'll return those
possibly_null_value columns that are, in fact, NULL.

Your requirements are unclear.  I can see two ways to interpret that
statement:

1) You want only possibly_null_value values that are NULL.
2) You can possibly_null_value values that are NULL *and*, if IP is
specified, possbly_null_value
   values that are equal to INET_ATON(IP).

Those are not the same thing.  (In other words, it's clear what you
want only for the case that IP is NULL.  It's not clear what you want
when IP isn't NULL.)  Can you clarify?
I can see where I was unclear, as I had simplified the statement (in an
attempt to be more clear, go figure). I'm looking for case 2 that you
described.
In that case, it looks to me (without knowing more) that the statement
you show above should do what you want.  It apparently does not, so perhaps
you could post to the list a few combinations of possibly_null_value and IP
values. Indicate what results you get, and how that differs from what
you expect.
snip

Thanks for your time on this.

Cheers,
Jim


--
Paul DuBois, Senior Technical Writer
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
Are you MySQL certified?  http://www.mysql.com/certification/

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

RE: Null-safe equal help, please

[Knepley, Jim]

 

-Original Message-
From: Paul DuBois [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, December 16, 2003 5:44 PM
To: Knepley, Jim; [EMAIL PROTECTED]
Subject: Re: Null-safe equal help, please

At 15:22 -0700 12/16/03, Knepley, Jim wrote:
I've got a WHERE clause:
WHERE possibly_null_value IS NULL

That works fine. This null-safe equal doesn't do what I expect:
WHERE possibly_null_value = NULL

The manual, and my testing, shows that NULL = NULL evaluates to 1,
so

Are you saying that this is not what you expect?  Why not?

It is what I expect, but it doesn't seem to be the behavior.

snip


What I _really_ want to do is this:
WHERE  possibly_null_value = INET_ATON(IP)

...so that if no IP is specificied it'll return those 
possibly_null_value columns that are, in fact, NULL.

Your requirements are unclear.  I can see two ways to interpret that
statement:

1) You want only possibly_null_value values that are NULL.
2) You can possibly_null_value values that are NULL *and*, if IP is
specified, possbly_null_value 
   values that are equal to INET_ATON(IP).

Those are not the same thing.  (In other words, it's clear what you
want only for the case that IP is NULL.  It's not clear what you want
when IP isn't NULL.)  Can you clarify?

I can see where I was unclear, as I had simplified the statement (in an
attempt to be more clear, go figure). I'm looking for case 2 that you
described.

snip

Thanks for your time on this.

Cheers,
Jim


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

Re: Problem creating new user.

[jeffrey_n_Dyke]

If that error is verbatim from the console, then DNS may be working, but it
is not who MySQL is acknowledging you as, is there a firewall in between
you and the MySQL server? looks like you'd need a grant for
202-63-167-192.exatt.com.

and i know its not working, but you really shouldn't post passwords to this
list.  all posts are archived and that means your password can be read by
anyone who google's for help or searches the mysql archive.

hth
Jeff


   
 
  ads mysql
 
  [EMAIL PROTECTED]To:   gerald_clark [EMAIL 
PROTECTED]
  om  cc:   [EMAIL PROTECTED] 
 
   Subject:  Re: Problem creating new 
user. 
  12/17/2003 10:44 
 
  AM   
 
   
 
   
 




Hi,
I tried to create user with GRANt as follows :

mysql use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON bankaccount.* TO
ads123@'www.networkshastrihall.com' IDENTIFIED BY 'ads123';
Query OK, 0 rows affected (0.00 sec)
mysql FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

I think this is not entering into table.


[EMAIL PROTECTED] root]# mysql -h www.networkshastrihall.com -u ads123 -p
bankaccount
Enter password:
ERROR 1130: Host '202-63-167-192.exatt.com' is not allowed to connect to
this MySQL server.

DNS www.networkshastrihall.com is working.

Help appreciated.
Thanks


gerald_clark [EMAIL PROTECTED] wrote:


ads mysql wrote:

I tried to connect as follows :

#
[EMAIL PROTECTED] root]# mysql -h www.allnet.com -u ads11 bankaccount
ERROR 1130: Host 'xxx-xx-xxx-xxx.yyy.com' is not allowed to connect to
this MySQL server
#


Your DNS is broken.

It gives error.
I sthere anything giving permission to connect to mysql for particular
Host?


Use GRANT unless you know what you are doing.

Thanks for support.


Duncan Hill wrote:
On Wednesday 17 December 2003 12:45, ads mysql wrote:



It gives following error :[EMAIL PROTECTED] root]# mysql -u ads11 -p
bankaccount Enter password: (here I enter password 'ads11')
ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password:
YES)
Domain www.allnet.com is registered, having live IP on which web server
is



Your mysql command line statement provides no host to connect to, so it
uses
the local socket to communicate. Mysql sees this as 'localhost'.

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


-
Do you Yahoo!?
Free Pop-Up Blocker - Get it now





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


-
Do you Yahoo!?
New Yahoo! Photos - easier uploading and sharing





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

Re: MySQL with SSL enabled

[Greg G]

rmck wrote:

Hello,

I am in the process of installing Mysql 4.0.17. I need to have Mysql with SSL enabled. 

Does this feature come enabled with the binary download? Or do I need to download the source and compile
it? 

Any good links ot setting up MySQL with SSL enabled?

 

Good luck.  Let me know if you manage to get SSL working during the 
compile phase.  I found an open bug  ( 
http://bugs.mysql.com/bug.php?id=1915 ) which pretty much indicates that 
you're not going to be able to build in SSL support.

-Greg G



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

RE: Problem creating new user.

[Chris]

MySQL checks the IP address of the user connecting to it's server. It then
does a Reverse DNS lookup on that IP address, to get the name associated
with it (there is only one name associated with any IP, the rest are just
aliases)

The host you are connecting from does not resolve to
www.networkshastrihall.com , it resolves to 202-63-167-192.exatt.com .

You would need to be connecting to MySQL from the computer
www.networkshastrihall.com sits on to be able to connect.

Chris

-Original Message-
From: ads mysql [mailto:[EMAIL PROTECTED]
Sent: Wednesday, December 17, 2003 7:44 AM
To: gerald_clark
Cc: [EMAIL PROTECTED]
Subject: Re: Problem creating new user.


Hi,
I tried to create user with GRANt as follows :

mysql use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON bankaccount.* TO
ads123@'www.networkshastrihall.com' IDENTIFIED BY 'ads123';
Query OK, 0 rows affected (0.00 sec)
mysql FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

I think this is not entering into table.


[EMAIL PROTECTED] root]# mysql -h www.networkshastrihall.com -u ads123 -p
bankaccount
Enter password:
ERROR 1130: Host '202-63-167-192.exatt.com' is not allowed to connect to
this MySQL server.

DNS www.networkshastrihall.com is working.

Help appreciated.
Thanks


gerald_clark [EMAIL PROTECTED] wrote:


ads mysql wrote:

I tried to connect as follows :

#
[EMAIL PROTECTED] root]# mysql -h www.allnet.com -u ads11 bankaccount
ERROR 1130: Host 'xxx-xx-xxx-xxx.yyy.com' is not allowed to connect to this
MySQL server
#


Your DNS is broken.

It gives error.
I sthere anything giving permission to connect to mysql for particular
Host?


Use GRANT unless you know what you are doing.

Thanks for support.


Duncan Hill wrote:
On Wednesday 17 December 2003 12:45, ads mysql wrote:



It gives following error :[EMAIL PROTECTED] root]# mysql -u ads11 -p
bankaccount Enter password: (here I enter password 'ads11')
ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password:
YES)
Domain www.allnet.com is registered, having live IP on which web server is



Your mysql command line statement provides no host to connect to, so it
uses
the local socket to communicate. Mysql sees this as 'localhost'.

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


-
Do you Yahoo!?
Free Pop-Up Blocker - Get it now





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


-
Do you Yahoo!?
New Yahoo! Photos - easier uploading and sharing


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

Re: MySQL with SSL enabled

[Kevin Old]

On Wed, 2003-12-17 at 09:39, rmck wrote:
 Hello,
 
 I am in the process of installing Mysql 4.0.17. I need to have Mysql with SSL 
 enabled. 
 
 Does this feature come enabled with the binary download? Or do I need to download 
 the source and compile
 it? 
 
 Any good links ot setting up MySQL with SSL enabled?

Hi Rob,

I'm not sure about SSL capabilities of MySQL, but there's a SSL wrapper
called Stunnel at http://www.stunnel.org that will wrap any ports
connection in SSL with OpenSSL and SSLeay libaries.

Very cool for securing email connections with regular POP3 accounts. 
Should be just as easy with any other port.

HTH,
Kevin

-- 
Kevin Old [EMAIL PROTECTED]


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

Re: MySQL with SSL enabled

[Kevin Old]

On Wed, 2003-12-17 at 09:39, rmck wrote:
 Hello,
 
 I am in the process of installing Mysql 4.0.17. I need to have Mysql with SSL 
 enabled. 
 
 Does this feature come enabled with the binary download? Or do I need to download 
 the source and compile
 it? 
 
 Any good links ot setting up MySQL with SSL enabled?
 
 Thanks
 Rob

Also, this article about 11 SSH Tricks might help.

http://www.linuxjournal.com/article.php?sid=6602

HTH,
Kevin
-- 
Kevin Old [EMAIL PROTECTED]


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

RE: Issues with count(), aliases, and LEFT JOINS

[Chris]

Try changing it to this:

...
-  COUNT(DISTINCT switch_connect1.switch_id) AS left_port_count,
-  COUNT(DISTINCT switch_connect2.switch2_id) AS right_port_count,
...

I would suggest, if that suggestion fails, that you experiment with just
switch_connect.switch_port and switch_connect.switch2_port . Problems are
much easier to define and solve if you simplify them down to the basics. If
you're able to do what you want with those values Then you can add the
complexity of JOINing to other tables and you'll know when it 'should work'
and not.

Chris

-Original Message-
From: Brandon Ewing [mailto:[EMAIL PROTECTED]
Sent: Wednesday, December 17, 2003 1:59 AM
To: [EMAIL PROTECTED]
Subject: RE: Issues with count(), aliases, and LEFT JOINS



 -Original Message-
 From: Brandon Ewing [mailto:[EMAIL PROTECTED]
 Sent: Monday, December 15, 2003 2:28 AM
 To: [EMAIL PROTECTED]
 Subject: Issues with count(), aliases, and LEFT JOINS

 Greetings,

 This is driving me crazy.

 I'm running MySQL 4.0.15-standard.

 I've got a db that tracks switches, servers they connect to,
 and connections
 between switches.

snip


 I've been pounding my head against this for a while now - can
 anyone offer
 any illumination as to what exactly I'm screwing up?

 I'd prefer not to do any major schema re-altering, but if I
 must, I must.

 Brandon Ewing



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



No insight on any of this?  Is it a problem with my SQL statement/schema, or
have I run into what might be a bug?  Should I start testing with this
dataset on other servers?

Brandon Ewing


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

Variable names, colum names

[Paul Fine]

Greetings!

Is it common practice to name script variables/form data in say PHP to match
the appropriate colums in the db tables?

I can see how this makes sense, however in the little work I have done I
preferred to do the opposite as it seems to be helpful to keep a
distinction.


Thanks!


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

RE: Variable names, colum names

[Chris]

Well, I keep my internal script variables identical to the relevant MySQL
variable , with the exception of putting the type identifier in front of the
script variable (s for string, i for int, etc...)

I do the exact opposite of form data in trying not to have a form variable
with the same name as a MySQL variable. The user being able to guess at
column names in the database isn't usually a good thing for security
reasons.

Chris

-Original Message-
From: Paul Fine [mailto:[EMAIL PROTECTED]
Sent: Wednesday, December 17, 2003 8:17 AM
To: [EMAIL PROTECTED]
Subject: Variable names, colum names


Greetings!

Is it common practice to name script variables/form data in say PHP to match
the appropriate colums in the db tables?

I can see how this makes sense, however in the little work I have done I
preferred to do the opposite as it seems to be helpful to keep a
distinction.


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: Problem creating new user.

[ads mysql]

As advised by other member of this mailing list, Let me check whether it is reverse 
DNS problem. Reverse DNS is giving 202-63-167-192.exatt.com instead 
www.networkshastrihall.com. 
Regarding password, this is test database not in production . I am going to change all 
settings later on. From next time I will keep in mind.
 
Thanks for support.  

[EMAIL PROTECTED] wrote:

If that error is verbatim from the console, then DNS may be working, but it
is not who MySQL is acknowledging you as, is there a firewall in between
you and the MySQL server? looks like you'd need a grant for
202-63-167-192.exatt.com.

and i know its not working, but you really shouldn't post passwords to this
list. all posts are archived and that means your password can be read by
anyone who google's for help or searches the mysql archive.

hth
Jeff



ads mysql 

om cc: [EMAIL PROTECTED] 
Subject: Re: Problem creating new user. 
12/17/2003 10:44 
AM 






Hi,
I tried to create user with GRANt as follows :

mysql use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON bankaccount.* TO
ads123@'www.networkshastrihall.com' IDENTIFIED BY 'ads123';
Query OK, 0 rows affected (0.00 sec)
mysql FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

I think this is not entering into table.


[EMAIL PROTECTED] root]# mysql -h www.networkshastrihall.com -u ads123 -p
bankaccount
Enter password:
ERROR 1130: Host '202-63-167-192.exatt.com' is not allowed to connect to
this MySQL server.

DNS www.networkshastrihall.com is working.

Help appreciated.
Thanks


gerald_clark wrote:


ads mysql wrote:

I tried to connect as follows :

#
[EMAIL PROTECTED] root]# mysql -h www.allnet.com -u ads11 bankaccount
ERROR 1130: Host 'xxx-xx-xxx-xxx.yyy.com' is not allowed to connect to
this MySQL server
#


Your DNS is broken.

It gives error.
I sthere anything giving permission to connect to mysql for particular
Host?


Use GRANT unless you know what you are doing.

Thanks for support.


Duncan Hill wrote:
On Wednesday 17 December 2003 12:45, ads mysql wrote:



It gives following error :[EMAIL PROTECTED] root]# mysql -u ads11 -p
bankaccount Enter password: (here I enter password 'ads11')
ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password:
YES)
Domain www.allnet.com is registered, having live IP on which web server
is



Your mysql command line statement provides no host to connect to, so it
uses
the local socket to communicate. Mysql sees this as 'localhost'.

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


-
Do you Yahoo!?
Free Pop-Up Blocker - Get it now





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


-
Do you Yahoo!?
New Yahoo! Photos - easier uploading and sharing





-
Do you Yahoo!?
New Yahoo! Photos - easier uploading and sharing

mysql query not dead after 'kill'

[Johannes Ullrich]

I have run into this a few times now, and am wondering there is a
solution:

I have two queries:

(1) select query on a MERGE table
(2) 'alter table' on the same MERGE table.

As expected, the select query will lock the 'alter table'
query. The alter table query is intended to swap one of the
tables in the MERGE set.

Now if the 'select' query isn't critical, I would like to
kill it if it takes too long. However, as a result I have
the 'kill flag' set on the query, for a long time, without
it actually dying (e.g. it says in 'send data' status).

is there anyway to avoid this issue? E.g. a way to kill the
select faster? 

Usually I am ending up with load running out of control, and
in a few cases, mysql is crashing as a result.

Also: the status of the 'alter table' is 'rename result table',
not 'locked' or 'waiting for table'.




-- 
--
Johannes Ullrich [EMAIL PROTECTED]
pgp key: http://johannes.homepc.org/PGPKEYS
--
   We regret to inform you that we do not enable any of the 
security functions within the routers that we install.
 [EMAIL PROTECTED]
--



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

RE: Variable names, colum names

[Mike Johnson]

From: Paul Fine [mailto:[EMAIL PROTECTED]

 Greetings!
 
 Is it common practice to name script variables/form data in 
 say PHP to match the appropriate colums in the db tables?
 
 I can see how this makes sense, however in the little work I 
 have done I preferred to do the opposite as it seems to be 
 helpful to keep a distinction.


I've found it useful to keep the same names, such that you could do the following. Say 
you were taking form input and inserting it as a row into the table...

?

$query = INSERT INTO table SET ;

foreach ($_POST as $key = $val) {
$query .= $key='$val', ;
}

$query = preg_replace(/, $/, , $query);

mysql_query($query);

?

That's oversimplifying it, for sure, as you'd want error checking and data validation 
and the like. And you also have to remember that any fields in the HTML form, hidden 
or otherwise, had better match up to the db table structure.

On the plus side, if you add a column to the db, all you need to do is add a field of 
the same name to the HTML form.

In a nutshell, while it's useful to do things like abstract the construction of the 
query, it's often not worth the trouble.


-- 
Mike Johnson
Web Developer/Systems Asst.
Smarter Living, Inc.
phone (617) 497-2500 x226

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

RE: Spatial Extension in MySQL 4.1.1-alpha

[Matt Lynch]

Hi Steve and Paul,

Thanks - the comma did get me!
Paul, no the mistake isn't in the manual - I managed it all on my own.

For anyone following, the correct sequence was:



mysql CREATE TABLE geom ( g GEOMETRY );
Query OK, 0 rows affected (0.05 sec)

mysql INSERT INTO geom VALUES ( GeomFromText('POINT(1 1)') );
Query OK, 1 row affected (0.00 sec)

mysql SELECT AsText(g) FROM geom;
++
| AsText(g)  |
++
| POINT(1 1) |
++
1 row in set (0.00 sec)

mysql



Thanks again,

Matt

-Original Message-
From: Paul DuBois [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, December 16, 2003 5:48 PM
To: Steven Roussey; [EMAIL PROTECTED]
Cc: 'Matt Lynch'
Subject: Re: Spatial Extension in MySQL 4.1.1-alpha


At 16:03 -0800 12/16/03, Steven Roussey wrote:
You did an insert this way:

   mysql insert into geom values(GeomFromText('POINT(1,1)'));

and expected results this way:

   mysql select AsText(g) from geom;
   +---+
   | AsText(g) |
   +---+
   | Point(1 1)|
   +---+
   1 row in set (0.00 sec)

The formatting of the POINT coordinates are different. I think you 
should be inserting POINT (1 1) not POINT(1,1). It is the comma.

-steve-

That's exactly right.

Was there an example like this in the manual that incorrectly included
the comma?

-- 
Paul DuBois, Senior Technical Writer
Madison, Wisconsin, USA
MySQL AB, www.mysql.com

Are you MySQL certified?  http://www.mysql.com/certification/


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


---
Incoming mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.543 / Virus Database: 337 - Release Date: 11/21/2003
 

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.543 / Virus Database: 337 - Release Date: 11/21/2003
 



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

Reverse DNS question

[Ari Davidow]

At 10:55 AM 12/17/2003, you wrote:
MySQL checks the IP address of the user connecting to it's server. It then
does a Reverse DNS lookup on that IP address, to get the name associated
with it (there is only one name associated with any IP, the rest are just
aliases)


Suppose we wanted to associate a batch of queries with a cname, is there a 
way to do that? In other words, if I have both foo.mysite.com and 
bar.mysite.com, on a host called baz.mysite.com, and I want to restrict 
access to some tables to foo, and to other databases to bar, is there 
something I can do at the cname level? (Obviously I can restrict access to 
specific users - that's the next level of security down.)

ari



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

case sensitivity of table names

[Mayuran Yogarajah]

I am using case sensitive table names when I create tables
like :
CREATE TABLE MyTest;

If I want to do a select from this table, I have to do
SELECT * FROM MyTest, not SELECT * FROM mytest.
How can I make it so that the table name is still MyTest
but selects work with mytest ?


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

Re: case sensitivity of table names

[Paul DuBois]

At 12:22 -0500 12/17/03, Mayuran Yogarajah wrote:
I am using case sensitive table names when I create tables
like :
CREATE TABLE MyTest;

If I want to do a select from this table, I have to do
SELECT * FROM MyTest, not SELECT * FROM mytest.
How can I make it so that the table name is still MyTest
but selects work with mytest ?
You can set the lower_case_table_names server variable to 1.
Then table names will not be treated as case sensitive,
and you can write them in any lettercase in your queries.
Two points to note, though:

- Before setting the varable, rename all your tables to lowercase.
Otherwise they won't be recognized properly when you set the variable.
(The way it works is that if causes the server to lowercase the names
of new tables when they are created.)
- Although you will be able to refer to tables using any lettercase,
*within a given query*, you must refer to the table consistently.
See http://www.mysql.com/doc/en/Name_case_sensitivity.html for
an example.
--
Paul DuBois, Senior Technical Writer
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
Are you MySQL certified?  http://www.mysql.com/certification/

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

errno 138

[sean peters]

Hi all,
We're trying to upgrade from 4.0.2 max to 4.0.16 
Everything starts fine, but when querying the DB, something as simple as

SELECT count(*) FROM myTable

will return errno 138, but this works fine before the upgrade.

Its probably something minor, but we're at a loss.
Im not sure what other info to provide, ask and i'll tell

thanks much,
sean peters
[EMAIL PROTECTED]

filter buster: mysql, query

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

How to include count(*) in selection criteria

[Gaspar Bakos]

Dear all,

I am trying the following mysql statement (probably it will be obvious
that I am a newcomer):

select IMdirectory, IMljd, count(*) as N from Images where IMstid
= 5, N  10 group by IMdirectory order by IMljd;
ERROR 1054: Unknown column 'N' in 'where clause'

My question is: how could I select only those grouped entries that have
a count number greater than e.g. 10?

Cheers
Gaspar


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

Re: case sensitivity of table names

[Mayuran Yogarajah]

Paul DuBois wrote:

At 12:22 -0500 12/17/03, Mayuran Yogarajah wrote:

I am using case sensitive table names when I create tables
like :
CREATE TABLE MyTest;

If I want to do a select from this table, I have to do
SELECT * FROM MyTest, not SELECT * FROM mytest.
How can I make it so that the table name is still MyTest
but selects work with mytest ?


You can set the lower_case_table_names server variable to 1.
Then table names will not be treated as case sensitive,
and you can write them in any lettercase in your queries.
Two points to note, though:

- Before setting the varable, rename all your tables to lowercase.
Otherwise they won't be recognized properly when you set the variable.
(The way it works is that if causes the server to lowercase the names
of new tables when they are created.)
- Although you will be able to refer to tables using any lettercase,
*within a given query*, you must refer to the table consistently.
See http://www.mysql.com/doc/en/Name_case_sensitivity.html for
an example.

Is it possible to change the variable lower_case_table_names from mysql 
commandline?
I tried to change it by doing this :
mysql SET lower_case_table_names=1;

and got the error :
ERROR 1193: Unknown system variable 'lower_case_table_names'
Thanks



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

Re: How to include count(*) in selection criteria

[Paul DuBois]

At 12:52 -0500 12/17/03, Gaspar Bakos wrote:
Dear all,

I am trying the following mysql statement (probably it will be obvious
that I am a newcomer):
select IMdirectory, IMljd, count(*) as N from Images where IMstid
= 5, N  10 group by IMdirectory order by IMljd;
ERROR 1054: Unknown column 'N' in 'where clause'
My question is: how could I select only those grouped entries that have
a count number greater than e.g. 10?
You cannot refer to aggregate values in a WHERE clause, because:

- WHERE determines which rows to select
- aggregates are calculated from the rows that are selected
In other words, you cannot use values that are determined from
the selected rows to determine which rows to select. :-)
Try putting the COUNT() test in a HAVING clause instead.

--
Paul DuBois, Senior Technical Writer
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
Are you MySQL certified?  http://www.mysql.com/certification/

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

How to include count(*) in selection criteria

[Bill Easton]

use having N  10

The having clause takes the results of the query AFTER all of the rows have
been read and aggregated by the group by clause and further reduces the set
of rows that gets returned.

- original message -

Date: Wed, 17 Dec 2003 12:52:08 -0500 (EST)
From: Gaspar Bakos [EMAIL PROTECTED]
To: mysqllist [EMAIL PROTECTED]
Subject: How to include count(*) in selection criteria

Dear all,

I am trying the following mysql statement (probably it will be obvious
that I am a newcomer):

select IMdirectory, IMljd, count(*) as N from Images where IMstid
= 5, N  10 group by IMdirectory order by IMljd;
ERROR 1054: Unknown column 'N' in 'where clause'

My question is: how could I select only those grouped entries that have
a count number greater than e.g. 10?

Cheers
Gaspar


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

Connector/J 3.0.9 Unexpected end of input

[Bill Ataras]

I upgraded to Connector/J 3.09 from 2.0.14. After some hours, the driver
looses it's connection and will not reconnect (despite setting
reconnect=true). The error starts as follows below. I rollback to 2.0.14
and everything is stable again. This is using mysql 4.0.16-standard. Is
connector/j 3.0x production release?
 
 
Thanks
 
java.sql.SQLException: Communication link failure:
java.io.IOException, underlying cause: Unexpected end of input stream
 
Stacktrace:
 
java.io.IOException: Unexpected end of input stream
 

Re: case sensitivity of table names

[Paul DuBois]

At 13:00 -0500 12/17/03, Mayuran Yogarajah wrote:
Paul DuBois wrote:

At 12:22 -0500 12/17/03, Mayuran Yogarajah wrote:

I am using case sensitive table names when I create tables
like :
CREATE TABLE MyTest;

If I want to do a select from this table, I have to do
SELECT * FROM MyTest, not SELECT * FROM mytest.
How can I make it so that the table name is still MyTest
but selects work with mytest ?


You can set the lower_case_table_names server variable to 1.
Then table names will not be treated as case sensitive,
and you can write them in any lettercase in your queries.
Two points to note, though:

- Before setting the varable, rename all your tables to lowercase.
Otherwise they won't be recognized properly when you set the variable.
(The way it works is that if causes the server to lowercase the names
of new tables when they are created.)
- Although you will be able to refer to tables using any lettercase,
*within a given query*, you must refer to the table consistently.
See http://www.mysql.com/doc/en/Name_case_sensitivity.html for
an example.
Is it possible to change the variable lower_case_table_names from 
mysql commandline?
I tried to change it by doing this :
mysql SET lower_case_table_names=1;

and got the error :
ERROR 1193: Unknown system variable 'lower_case_table_names'
No, it must be set at startup time.  Besides, if you set it with a
SET statement, the value would be lost the next time you start the
server.
Best to put it in an option file so that it's used each time the
server starts up:
[mysqld]
lower_case_table_names=1
--
Paul DuBois, Senior Technical Writer
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
Are you MySQL certified?  http://www.mysql.com/certification/

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

Increase space for /var/db

[Naveen Babu]

Hello, 

I have a 4.5 FreeBSD server with mysql database. only 250MB
was allocated to /var/db. Our database is increasing so fast that now
i need to expand the memory for /var/db. I guess one way to do is to
install a new hard drive and then mount it to /var/db. But i would
like to know if there is any other way to solve that problem. Is
making a soft link a good idea. Is there any better solution for it. i have 70GB in 
/usr/ so i would like to map /var/db/ to some location in /usr. Please let me know if
anyone has any suggestions.

 thanks in advance,



-
Do you Yahoo!?
Free Pop-Up Blocker - Get it now

Re: Increase space for /var/db

[Paul DuBois]

At 9:03 -0800 12/17/03, Naveen Babu wrote:
Hello,

I have a 4.5 FreeBSD server with mysql database. only 250MB
was allocated to /var/db. Our database is increasing so fast that now
i need to expand the memory for /var/db. I guess one way to do is to
install a new hard drive and then mount it to /var/db. But i would
like to know if there is any other way to solve that problem. Is
making a soft link a good idea. Is there any better solution for it. 
i have 70GB in /usr/ so i would like to map /var/db/ to some 
location in /usr. Please let me know if
anyone has any suggestions.
Sure, that'll work.

Make sure you shut down your MySQL server first.  Then copy /var/db to
/usr somewhere.  Remove /var/db and replace it with a symlink to your
new location.
You might also want to make a backup first, too. :-)

--
Paul DuBois, Senior Technical Writer
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
Are you MySQL certified?  http://www.mysql.com/certification/

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

Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?

[Matthew Stanfield]

Jerry Apfelbaum wrote:
 I have been tasked with evaluating open source databases for a large
 upcoming project:  e-commerce, B2B, high availability.
Jerry -- See what I meant when I said:

very active mailing lists where list subscribers are helpful and quick to 
respond.

I think we all deserve a pat on the back for giving Jerry a comphrensive 
response to his initial posting. :-)  --and it's still going...

Wouldn't it be great if all mailing lists and newsgroups were as friendly 
and helpful as the mysql ones are.

..matthew



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

Re: Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?

[ja1]

Yes, Matthew, the response to my original query here has been almost overwhelming, far 
surpassing what I had expected.

Yes, you certainly all deserve a pat on the back.

Thanks to everyone who took the time to respond.  This has proven to be a useful short 
cut to ascertaining some of  strengths and weaknesses of the various Open Source DB 
offerings.

Jerry Apfelbaum
Toronto


 From: Matthew Stanfield [EMAIL PROTECTED]
 Date: 2003/12/17 Wed AM 07:35:25 EST
 
 Jerry -- See what I meant when I said:
 
 very active mailing lists where list subscribers are helpful and quick to 
 respond.
 
 I think we all deserve a pat on the back for giving Jerry a comphrensive 
 response to his initial posting. :-)  --and it's still going...
 
 Wouldn't it be great if all mailing lists and newsgroups were as friendly 
 and helpful as the mysql ones are.
 
 ..matthew
 

1


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

Re: MySQL 4.0.17 has been released

[Yves Goergen]

On Wednesday, December 17, 2003 1:58 PM CET, Lenz Grimmer wrote:
 Functionality added or changed:

* `lower_case_table_names' is now forced to 1 if the database
  directory is located on a case-insensitive file system. (Bug
 #1812)

Uh, _very_ bad. I know that my Windows filesystem is case-insensitive and
that I cannot create tables only differing by case of the name, but I need
to have this setting off to be able to dump my tables on Windows with the
correct (and not lower-cased!!) table names to import them on my webhoster's
Linux server. If the setting was enabled, I'd get all wrong table names and
my application couldn't find its tables anymore (as 'bb1_GroupMembers' gets
to 'bb1_groupmembers' and that's something else!).

So please change this back again, I believe it's up to the server
administrator to set this in a correct and reasonable way, don't you?

-- 
Yves Goergen
[EMAIL PROTECTED]
Please don't CC me (causes double mails)


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

Re: How to include count(*) in selection criteria

[Gaspar Bakos]

Hi,

RE:
 In other words, you cannot use values that are determined from
 the selected rows to determine which rows to select. :-)

Sounds very logical. In fact I was not surprised that my query did not
work, I just had no idea about the workaround.

Thanks again!

Gaspar

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

Re: Can I really have no_wait row-locks in MySQL+InnoDB?

[Heikki Tuuri]

Dmitry,

we have to consider implementing

NOWAIT

and

SKIP LOCKED

clauses to SQL statements. The latter would be useful in implementing
transactional queues.

But there are lots of items in the TODO list. Do not expect these soon.

Best regards,

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

Order MySQL technical support from https://order.mysql.com/

- Original Message - 
From: Dmitry Anikin [EMAIL PROTECTED]
Newsgroups: mailing.database.myodbc
Sent: Monday, December 15, 2003 11:26 AM
Subject: Can I really have no_wait row-locks in MySQL+InnoDB?


 Suppose some user issued 'select ... for update', then
 went for coffee-break (to think hard on what he
 really wants to update in that row). Another client
 tries to update the same row and I don't want him to
 wait, just immediately return an error, so he could
 do some other useful task meanwhile. I haven't found
 any no_wait option for locks in the manual :(.

 There's a variable innodb_lock_wait_timeout, though, but
 unfortunately I can't assign 0 to it (min. value is 1).
 Still, 1 second time-out can be bearable (although I'd
 appreciate a way to reduce it to zero) but what disturbs
 me is that I've read in the manual that deadlock-removing
 algorithm aborts transaction which it thinks is most suitable
 for aborting (not last-in-first-aborted). Since time-out
 feature has something to do with deadlocks can I be
 absolutely sure that WAITING transaction will be aborted
 and not that which issued the lock?
 And also it would be fine to have non-destructive means
 to determine whether some row has been locked so I may
 just skip (postpone) some updates without rollback
 of whole transaction. Is it possible?



 -- 
 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: Change the size of an InnoDB table field

[Heikki Tuuri]

Felix,

I assume this is a MySQLCC bug.

Can you perform the ALTER TABLE from using the mysql interactive SQL client
program?

Best regards,

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

Order MySQL technical support from https://order.mysql.com/

- Original Message - 
From: Flix Beltrn [EMAIL PROTECTED]
Newsgroups: mailing.database.myodbc
Sent: Tuesday, December 16, 2003 7:31 PM
Subject: Change the size of an InnoDB table field


 I'm trying to change the size of an InnoDB table field from CHAR(20)
 to CHAR(30) using MySQLCC, but i'm getting this error message

 ERROR 1091: Can't DROP '0_87'. Check that column/key exists

 This is the create query:

 CREATE TABLE `presentacion` (
   `cl_Presentacion` char(4) NOT NULL default '',
   `cl_Embase` char(4) NOT NULL default '',
   `cl_Tamano` char(4) NOT NULL default '',
   `cl_Variedad` char(4) NOT NULL default '',
   `de_Presentacion` char(20) default NULL,
   PRIMARY KEY  (`cl_Presentacion`),
   KEY `presentacion_FKIndex1` (`cl_Variedad`),
   KEY `presentacion_FKIndex2` (`cl_Tamano`),
   KEY `presentacion_FKIndex3` (`cl_Embase`),
   CONSTRAINT `0_85` FOREIGN KEY (`cl_Variedad`) REFERENCES `variedad`
 (`cl_Variedad`),
   CONSTRAINT `0_86` FOREIGN KEY (`cl_Tamano`) REFERENCES `tamano`
 (`cl_Tamano`),
   CONSTRAINT `0_87` FOREIGN KEY (`cl_Embase`) REFERENCES `embase`
 (`cl_Embase`)
 ) TYPE=InnoDB;

 Any clues about this??


 -- 
 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: foreign keys.

[Heikki Tuuri]

Mofeed,

if you are using a relatively recent version of 4.0, or 4.1.1, please look
with

SHOW INNODB STATUS\G

what is the latest FOREIGN KEY error.

Best regards,

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

Order MySQL technical support from https://order.mysql.com/

- Original Message - 
From: Mofeed Shahin [EMAIL PROTECTED]
Newsgroups: mailing.database.myodbc
Sent: Wednesday, December 17, 2003 1:19 AM
Subject: Re: foreign keys.



 mysql CREATE TABLE foo (
 -  ID INT PRIMARY KEY,
 -  note VARCHAR(50),
 -  First_Name VARCHAR(50),
 -  Last_Name VARCHAR(50),
 -  FOO_ID INT,
 -  INDEX(FOO_ID),
 -  INDEX(First_Name, Last_Name),
 -  FOREIGN KEY (FOO_ID) REFERENCES foo(ID),
 -  FOREIGN KEY (First_Name, Last_Name) REFERENCES Blah
 (First_Name, Last_Name)
 -  ) TYPE=INNODB;
 ERROR 1005: Can't create table './moftest/foo.frm' (errno: 150)

 Mof.

 On Tue, 16 Dec 2003 07:54 pm, Victoria Reznichenko wrote:
  Mofeed Shahin [EMAIL PROTECTED] wrote:
   Still doesn't work But thanks for trying.
 
  Worked fine for me:
 
  mysql CREATE TABLE Blah (
  - ID INT PRIMARY KEY,
  - Fname VARCHAR (50),
  - Lname VARCHAR (50),
  - UNIQUE (Fname, Lname)
  - ) TYPE=INNODB;
  Query OK, 0 rows affected (0.11 sec)
 
  mysql CREATE TABLE foo (
  - ID INT PRIMARY KEY,
  - note VARCHAR(50),
  -  Fname VARCHAR(50),
  - Lname VARCHAR(50),
  - FOO_ID INT,
  - INDEX(FOO_ID),
  - INDEX(Fname, Lname),
  - FOREIGN KEY (FOO_ID) REFERENCES foo(ID),
  - FOREIGN KEY (Fname, Lname) REFERENCES Blah (Fname, Lname)
  - ) TYPE=INNODB;
  Query OK, 0 rows affected (0.10 sec)
 
   Mof.
  
   On Tue, 16 Dec 2003 11:40 am, Aftab Jahan Subedar wrote:
   If you have foreign key then add a key for each,
   so the
  
 CREATE TABLE foo (
ID INT PRIMARY KEY,
note VARCHAR(50),
Fname VARCHAR(50),
Lname VARCHAR(50),
FOO_ID INT,
INDEX(FOO_ID),
  
  KEY(Fname,Lname), #here this one--if it does not work,its not
me
  
FOREIGN KEY (FOO_ID) REFERENCES foo(ID),
FOREIGN KEY (Fname, Lname) REFERENCES Blah (Fname, Lname)
 ) TYPE=INNODB;
 
  --
  For technical support contracts, goto
https://order.mysql.com/?ref=ensita
  This email is sponsored by Ensita.net http://www.ensita.net/
 __  ___ ___   __
/  |/  /_ __/ __/ __ \/ /Victoria Reznichenko
   / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
  /_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
 ___/   www.mysql.com


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



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

Re: MySQL 4.0.17 has been released

[Reverend Deuce]

I agree, 100%. We live in a mixed environment of UNIX and Windows and as
such, we've assumed case insensitivity in our apps. I know that this is bad
practice, but forcing this flag on us is and even worse practice. This
should always, always be an option.

I wont be able to upgrade until this is fixed. :(

-- R

- Original Message - 
From: Yves Goergen [EMAIL PROTECTED]
To: Lenz Grimmer [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Wednesday, December 17, 2003 1:12 PM
Subject: Re: MySQL 4.0.17 has been released


 On Wednesday, December 17, 2003 1:58 PM CET, Lenz Grimmer wrote:
  Functionality added or changed:
 
 * `lower_case_table_names' is now forced to 1 if the database
   directory is located on a case-insensitive file system. (Bug
  #1812)

 Uh, _very_ bad. I know that my Windows filesystem is case-insensitive and
 that I cannot create tables only differing by case of the name, but I need
 to have this setting off to be able to dump my tables on Windows with the
 correct (and not lower-cased!!) table names to import them on my
webhoster's
 Linux server. If the setting was enabled, I'd get all wrong table names
and
 my application couldn't find its tables anymore (as 'bb1_GroupMembers'
gets
 to 'bb1_groupmembers' and that's something else!).

 So please change this back again, I believe it's up to the server
 administrator to set this in a correct and reasonable way, don't you?

 -- 
 Yves Goergen
 [EMAIL PROTECTED]
 Please don't CC me (causes double mails)


 -- 
 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: Issues with count(), aliases, and LEFT JOINS

[Brandon Ewing]

 

 -Original Message-
 From: Chris [mailto:[EMAIL PROTECTED] 
 Sent: Wednesday, December 17, 2003 8:12 AM
 To: [EMAIL PROTECTED]
 Subject: RE: Issues with count(), aliases, and LEFT JOINS
 
 Try changing it to this:
 
 ...
 -  COUNT(DISTINCT switch_connect1.switch_id) AS left_port_count,
 -  COUNT(DISTINCT switch_connect2.switch2_id) AS 
 right_port_count,
 ...
 
 I would suggest, if that suggestion fails, that you 
 experiment with just
 switch_connect.switch_port and switch_connect.switch2_port . 
 Problems are
 much easier to define and solve if you simplify them down to 
 the basics. If
 you're able to do what you want with those values Then you can add the
 complexity of JOINing to other tables and you'll know when it 
 'should work'
 and not.
 
 Chris
 

That fixed it, thanks bunches.  There any explaination as to why that made
it work?

Brandon


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

Re: foreign keys.

[Mofeed Shahin]

On Wed, 17 Dec 2003 07:55 pm, Victoria Reznichenko wrote:
 Mofeed Shahin [EMAIL PROTECTED] wrote:
  mysql CREATE TABLE foo (
 -  ID INT PRIMARY KEY,
 -  note VARCHAR(50),
 -  First_Name VARCHAR(50),
 -  Last_Name VARCHAR(50),
 -  FOO_ID INT,
 -  INDEX(FOO_ID),
 -  INDEX(First_Name, Last_Name),
 -  FOREIGN KEY (FOO_ID) REFERENCES foo(ID),
 -  FOREIGN KEY (First_Name, Last_Name) REFERENCES Blah
  (First_Name, Last_Name)
 -  ) TYPE=INNODB;
  ERROR 1005: Can't create table './moftest/foo.frm' (errno: 150)

 What is version of MySQL server? What default-character-set do you use?

MySQL version == 4.0.15.
Charact set == latin1

Mof.


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

How boolean full-text search finds matches?

[Matt W]

Hi,

Just have a couple more full-text search inquiries here. :-)

I'm not exactly clear on how matching rows are found when searching for
2 or more required words: '+word1 +word2'.  I understand that it can't
currently know which word occurs less, so that it can be searched
first -- this optimization will come with 4.1's 2-level indexes. :-)

I just want to know, when it finds a match for whichever word is tried
first, how does it check if the other required word(s) are present in
the same row?  Say that word1 and word2 are each present in 100,000
rows.

1) Surely it doesn't check the 100,000 entries for word2 for EACH word1
match to see if they're in the same row, does it?

2) It *seems* the best way would be to do a lookup for (word2 + rowid
for word1) and see if there's a match.  Is this what's done?  I'm not
sure it's possible though with the way the index is structured...

3) Or, and I'm thinking *maybe* this is how it's done from what I've
heard, does it get all the matches for word1, then for word2, and then
intersect them to find ones which are present in the same row?  If so,
how will the 2-level index optimization change things? Will it do #2?


Next question is... a few weeks ago I was doing some test searches like
'+word1 +word2'.  Actually, maybe I was only using 1 word, I can't
remember, but I don't think it matters.  Anyway, I happened to try
changing the query to '+word1* +word2*' -- e.g. adding a wild-card to
the end of the same word(s) -- and I was amazed at how much faster the
query was! (And no, there's no query cache; and they were both run many
times so the index was cached. :-)) Can't remember how much faster, but
it wasn't insignificant.  Then I tried adding a wild-card to the end of
words in another search (the wild-card did not make more rows match as
far as I know), but that made it a little slower (I'd expect that, if
anything).  Is there any explanation for why adding wild-cards would
make a search faster?


Thanks in advance!

Matt

P.S.  Sergei, if you see this, in one of your replies to my full-text
suggestions back in September ( http://lists.mysql.com/mysql/149644 ),
you said Another reply will follow...  I never saw another reply
though. :-/  It's OK, I was just wondering what other interesting things
you were going to say! :-)


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

How boolean full-text search finds matches?

[Matt W]

Hi,

Just have a couple more full-text search inquiries here. :-)

I'm not exactly clear on how matching rows are found when searching for
2 or more required words: '+word1 +word2'.  I understand that it can't
currently know which word occurs less, so that it can be searched
first -- this optimization will come with 4.1's 2-level indexes. :-)

I just want to know, when it finds a match for whichever word is tried
first, how does it check if the other required word(s) are present in
the same row?  Say that word1 and word2 are each present in 100,000
rows.

1) Surely it doesn't check the 100,000 entries for word2 for EACH word1
match to see if they're in the same row, does it?

2) It *seems* the best way would be to do a lookup for (word2 + rowid
for word1) and see if there's a match.  Is this what's done?  I'm not
sure it's possible though with the way the index is structured...

3) Or, and I'm thinking *maybe* this is how it's done from what I've
heard, does it get all the matches for word1, then for word2, and then
intersect them to find ones which are present in the same row?  If so,
how will the 2-level index optimization change things? Will it do #2?


Next question is... a few weeks ago I was doing some test searches like
'+word1 +word2'.  Actually, maybe I was only using 1 word, I can't
remember, but I don't think it matters.  Anyway, I happened to try
changing the query to '+word1* +word2*' -- e.g. adding a wild-card to
the end of the same word(s) -- and I was amazed at how much faster the
query was! (And no, there's no query cache; and they were both run many
times so the index was cached. :-)) Can't remember how much faster, but
it wasn't insignificant.  Then I tried adding a wild-card to the end of
words in another search (the wild-card did not make more rows match as
far as I know), but that made it a little slower (I'd expect that, if
anything).  Is there any explanation for why adding wild-cards would
make a search faster?


Thanks in advance!

Matt

P.S.  Sergei, if you see this, in one of your replies to my full-text
suggestions back in September ( http://lists.mysql.com/mysql/149644 ),
you said Another reply will follow...  I never saw another reply
though. :-/  It's OK, I was just wondering what other interesting things
you were going to say! :-)


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

Re: foreign keys.

[Mofeed Shahin]

Hmmm, Thanks for that.
But I'm not quite sure what it means.

Mof.


LATEST FOREIGN KEY ERROR

031218  8:48:23 Error in foreign key constraint of table moftest/foo,
FOREIGN KEY (First_Name, Last_Name) REFERENCES Blah (First_Name, Last_Name)) 
TYPE=INNODB
Cannot resolve column name close to:
, Last_Name)) TYPE=INNODB


On Thu, 18 Dec 2003 08:02 am, Heikki Tuuri wrote:
 Mofeed,

 if you are using a relatively recent version of 4.0, or 4.1.1, please look
 with

 SHOW INNODB STATUS\G

 what is the latest FOREIGN KEY error.

 Best regards,

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

 Order MySQL technical support from https://order.mysql.com/

 - Original Message -
 From: Mofeed Shahin [EMAIL PROTECTED]
 Newsgroups: mailing.database.myodbc
 Sent: Wednesday, December 17, 2003 1:19 AM
 Subject: Re: foreign keys.

  mysql CREATE TABLE foo (
  -  ID INT PRIMARY KEY,
  -  note VARCHAR(50),
  -  First_Name VARCHAR(50),
  -  Last_Name VARCHAR(50),
  -  FOO_ID INT,
  -  INDEX(FOO_ID),
  -  INDEX(First_Name, Last_Name),
  -  FOREIGN KEY (FOO_ID) REFERENCES foo(ID),
  -  FOREIGN KEY (First_Name, Last_Name) REFERENCES Blah
  (First_Name, Last_Name)
  -  ) TYPE=INNODB;
  ERROR 1005: Can't create table './moftest/foo.frm' (errno: 150)
 
  Mof.
 
  On Tue, 16 Dec 2003 07:54 pm, Victoria Reznichenko wrote:
   Mofeed Shahin [EMAIL PROTECTED] wrote:
Still doesn't work But thanks for trying.
  
   Worked fine for me:
  
   mysql CREATE TABLE Blah (
   - ID INT PRIMARY KEY,
   - Fname VARCHAR (50),
   - Lname VARCHAR (50),
   - UNIQUE (Fname, Lname)
   - ) TYPE=INNODB;
   Query OK, 0 rows affected (0.11 sec)
  
   mysql CREATE TABLE foo (
   - ID INT PRIMARY KEY,
   - note VARCHAR(50),
   -  Fname VARCHAR(50),
   - Lname VARCHAR(50),
   - FOO_ID INT,
   - INDEX(FOO_ID),
   - INDEX(Fname, Lname),
   - FOREIGN KEY (FOO_ID) REFERENCES foo(ID),
   - FOREIGN KEY (Fname, Lname) REFERENCES Blah (Fname, Lname)
   - ) TYPE=INNODB;
   Query OK, 0 rows affected (0.10 sec)
  
Mof.
   
On Tue, 16 Dec 2003 11:40 am, Aftab Jahan Subedar wrote:
If you have foreign key then add a key for each,
so the
   
  CREATE TABLE foo (
 ID INT PRIMARY KEY,
 note VARCHAR(50),
 Fname VARCHAR(50),
 Lname VARCHAR(50),
 FOO_ID INT,
 INDEX(FOO_ID),
   
   KEY(Fname,Lname), #here this one--if it does not work,its not

 me

 FOREIGN KEY (FOO_ID) REFERENCES foo(ID),
 FOREIGN KEY (Fname, Lname) REFERENCES Blah (Fname, Lname)
  ) TYPE=INNODB;
  
   --
   For technical support contracts, goto

 https://order.mysql.com/?ref=ensita

   This email is sponsored by Ensita.net http://www.ensita.net/
  __  ___ ___   __
 /  |/  /_ __/ __/ __ \/ /Victoria Reznichenko
/ /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
   /_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
  ___/   www.mysql.com
 
  --
  MySQL General Mailing List
  For list archives: http://lists.mysql.com/mysql
  To unsubscribe:

 http://lists.mysql.com/[EMAIL PROTECTED]


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

Re: MySQL 4.0.17 has been released

[Heikki Tuuri]

Hi!

I would like to remind people that if you want to move an InnoDB database
between Unix and Windows, you have to consider the following:

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


On Windows InnoDB stores the database names and table names internally
always in lower case. To move databases in a binary format from Unix to
Windows or from Windows to Unix you should have all table and database names
in lower case. A convenient way to accomplish this is to add on Unix the
line

set-variable=lower_case_table_names=1

to the [mysqld] section of your my.cnf before you start creating your
tables. On Windows the setting 1 is the default.


That is, the best solution for portability is to have the database and table
names always in lower case. Setting globally
set-variable=lower_case_table_names=1 is a convenient way to accomplish
this.

Best regards,

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


Order MySQL technical support from https://order.mysql.com/


- Original Message - 
From: Reverend Deuce [EMAIL PROTECTED]
Newsgroups: mailing.database.myodbc
Sent: Wednesday, December 17, 2003 11:42 PM
Subject: Re: MySQL 4.0.17 has been released


 I agree, 100%. We live in a mixed environment of UNIX and Windows and as
 such, we've assumed case insensitivity in our apps. I know that this is
bad
 practice, but forcing this flag on us is and even worse practice. This
 should always, always be an option.

 I wont be able to upgrade until this is fixed. :(

 -- R

 - Original Message - 
 From: Yves Goergen [EMAIL PROTECTED]
 To: Lenz Grimmer [EMAIL PROTECTED]; [EMAIL PROTECTED]
 Sent: Wednesday, December 17, 2003 1:12 PM
 Subject: Re: MySQL 4.0.17 has been released


  On Wednesday, December 17, 2003 1:58 PM CET, Lenz Grimmer wrote:
   Functionality added or changed:
  
  * `lower_case_table_names' is now forced to 1 if the database
directory is located on a case-insensitive file system. (Bug
   #1812)
 
  Uh, _very_ bad. I know that my Windows filesystem is case-insensitive
and
  that I cannot create tables only differing by case of the name, but I
need
  to have this setting off to be able to dump my tables on Windows with
the
  correct (and not lower-cased!!) table names to import them on my
 webhoster's
  Linux server. If the setting was enabled, I'd get all wrong table names
 and
  my application couldn't find its tables anymore (as 'bb1_GroupMembers'
 gets
  to 'bb1_groupmembers' and that's something else!).
 
  So please change this back again, I believe it's up to the server
  administrator to set this in a correct and reasonable way, don't you?
 
  -- 
  Yves Goergen
  [EMAIL PROTECTED]
  Please don't CC me (causes double mails)
 
 
  -- 
  MySQL General Mailing List
  For list archives: http://lists.mysql.com/mysql
  To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
 
 


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



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

Re: foreign keys.

[Heikki Tuuri]

Mofeed,

- Original Message - 
From: Mofeed Shahin [EMAIL PROTECTED]
To: Heikki Tuuri [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Thursday, December 18, 2003 12:20 AM
Subject: Re: foreign keys.


 Hmmm, Thanks for that.
 But I'm not quite sure what it means.

 Mof.

 
 LATEST FOREIGN KEY ERROR
 
 031218  8:48:23 Error in foreign key constraint of table moftest/foo,
 FOREIGN KEY (First_Name, Last_Name) REFERENCES Blah (First_Name,
Last_Name))
 TYPE=INNODB
 Cannot resolve column name close to:
 , Last_Name)) TYPE=INNODB

does the table Blah have the columns First_Name and Last_Name?

Best regards,

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

Order MySQL technical support from https://order.mysql.com/


 On Thu, 18 Dec 2003 08:02 am, Heikki Tuuri wrote:
  Mofeed,
 
  if you are using a relatively recent version of 4.0, or 4.1.1, please
look
  with
 
  SHOW INNODB STATUS\G
 
  what is the latest FOREIGN KEY error.
 
  Best regards,
 
  Heikki Tuuri
  Innobase Oy
  http://www.innodb.com
  Foreign keys, transactions, and row level locking for MySQL
  InnoDB Hot Backup - hot backup tool for InnoDB which also backs up
MyISAM
  tables
 
  Order MySQL technical support from https://order.mysql.com/
 
  - Original Message -
  From: Mofeed Shahin [EMAIL PROTECTED]
  Newsgroups: mailing.database.myodbc
  Sent: Wednesday, December 17, 2003 1:19 AM
  Subject: Re: foreign keys.
 
   mysql CREATE TABLE foo (
   -  ID INT PRIMARY KEY,
   -  note VARCHAR(50),
   -  First_Name VARCHAR(50),
   -  Last_Name VARCHAR(50),
   -  FOO_ID INT,
   -  INDEX(FOO_ID),
   -  INDEX(First_Name, Last_Name),
   -  FOREIGN KEY (FOO_ID) REFERENCES foo(ID),
   -  FOREIGN KEY (First_Name, Last_Name) REFERENCES Blah
   (First_Name, Last_Name)
   -  ) TYPE=INNODB;
   ERROR 1005: Can't create table './moftest/foo.frm' (errno: 150)
  
   Mof.
  
   On Tue, 16 Dec 2003 07:54 pm, Victoria Reznichenko wrote:
Mofeed Shahin [EMAIL PROTECTED] wrote:
 Still doesn't work But thanks for trying.
   
Worked fine for me:
   
mysql CREATE TABLE Blah (
- ID INT PRIMARY KEY,
- Fname VARCHAR (50),
- Lname VARCHAR (50),
- UNIQUE (Fname, Lname)
- ) TYPE=INNODB;
Query OK, 0 rows affected (0.11 sec)
   
mysql CREATE TABLE foo (
- ID INT PRIMARY KEY,
- note VARCHAR(50),
-  Fname VARCHAR(50),
- Lname VARCHAR(50),
- FOO_ID INT,
- INDEX(FOO_ID),
- INDEX(Fname, Lname),
- FOREIGN KEY (FOO_ID) REFERENCES foo(ID),
- FOREIGN KEY (Fname, Lname) REFERENCES Blah (Fname, Lname)
- ) TYPE=INNODB;
Query OK, 0 rows affected (0.10 sec)
   
 Mof.

 On Tue, 16 Dec 2003 11:40 am, Aftab Jahan Subedar wrote:
 If you have foreign key then add a key for each,
 so the

   CREATE TABLE foo (
  ID INT PRIMARY KEY,
  note VARCHAR(50),
  Fname VARCHAR(50),
  Lname VARCHAR(50),
  FOO_ID INT,
  INDEX(FOO_ID),

KEY(Fname,Lname), #here this one--if it does not work,its
not
 
  me
 
  FOREIGN KEY (FOO_ID) REFERENCES foo(ID),
  FOREIGN KEY (Fname, Lname) REFERENCES Blah (Fname, Lname)
   ) TYPE=INNODB;
   
--
For technical support contracts, goto
 
  https://order.mysql.com/?ref=ensita
 
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Victoria Reznichenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
   ___/   www.mysql.com
  
   --
   MySQL General Mailing List
   For list archives: http://lists.mysql.com/mysql
   To unsubscribe:
 
  http://lists.mysql.com/[EMAIL PROTECTED]



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

Re: MySQL 4.0.17 has been released

[Matt W]

Hi,

I saw the change as soon as it was posted last week or whenever and
didn't think anything of it. But the point Yves brings up seems very
important!

Although, I'm not sure what to do then with bug #1812. Too bad MySQL's
code can't make database/table names case-sensitive like on *nix.  e.g.
*Force* the case used in queries match that of the directory/file
name...


Matt


- Original Message -
From: Reverend Deuce
Sent: Wednesday, December 17, 2003 3:41 PM
Subject: Re: MySQL 4.0.17 has been released


 I agree, 100%. We live in a mixed environment of UNIX and Windows and
as
 such, we've assumed case insensitivity in our apps. I know that this
is bad
 practice, but forcing this flag on us is and even worse practice. This
 should always, always be an option.

 I wont be able to upgrade until this is fixed. :(

 -- R

 - Original Message -
 From: Yves Goergen
 Sent: Wednesday, December 17, 2003 1:12 PM
 Subject: Re: MySQL 4.0.17 has been released


  On Wednesday, December 17, 2003 1:58 PM CET, Lenz Grimmer wrote:
   Functionality added or changed:
  
  * `lower_case_table_names' is now forced to 1 if the database
directory is located on a case-insensitive file system. (Bug
   #1812)
 
  Uh, _very_ bad. I know that my Windows filesystem is
case-insensitive and
  that I cannot create tables only differing by case of the name, but
I need
  to have this setting off to be able to dump my tables on Windows
with the
  correct (and not lower-cased!!) table names to import them on my
 webhoster's
  Linux server. If the setting was enabled, I'd get all wrong table
names
 and
  my application couldn't find its tables anymore (as
'bb1_GroupMembers'
 gets
  to 'bb1_groupmembers' and that's something else!).
 
  So please change this back again, I believe it's up to the server
  administrator to set this in a correct and reasonable way, don't
you?


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

Basic error

[Trevor Rhodes]

Hello friendly helper types,

What is the problem here.  I can't find any errors. I know it's there but as a 
newbie I'm as blind as a bat at the moment.  Thanks for your help.

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result 
resource
Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result 
resource

===
$link = mysql_connect( localhost, $user, $pass );
if ( ! $link )
die( Couldn't connect to MySQL );
mysql_select_db( $db, $link )
or die ( Couldn't open $db: .mysql_error() );
$result = mysql_query( select * from domains );
$num_rows = mysql_num_rows( $result );
print There are currently $num_rows rows in the table;
print table border=1\n;
while ( $a_row = mysql_fetch_row( $result ) )
{
print tr\n;
foreach ( $a_row as $field )
print \ttd$field/td\n;
print /tr\n;
}
print /table\n;
mysql_close( $link );
===

 Regards
  Trevor Rhodes
===
Powered by Linux- Mandrake 9.1
Registered Linux user # 290542 at http://counter.li.org
Registered Machine #'s 186951
Mandrake Club Silver Member
Source :  my 100 % Microsoft-free personal computer.
===
 09:25:17 up 1 day,  9:14,  1 user,  load average: 1.02, 1.09, 1.08
-- 
Never mud wrestle with a pig.. you get dirty and the pig enjoys it!
Never try to teach a pig to dance. You waste your time and annoy the pig.


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

Re: Detecting locks (using connector/j)

[Heikki Tuuri]

Jonck,

sorry, there is currently no way to detect InnoDB locks, except by setting
globally

innodb_lock_wait_timeout=1

in the [mysqld] section of my.cnf. Then your failing lock requests return
with the error 'lock wait timeout'.

Best regards,

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

Order MySQL technical support from https://order.mysql.com/

...
Search Result 5
From: Jonck van der Kogel ([EMAIL PROTECTED])
Subject: Detecting locks (using connector/j)
This is the only article in this thread
View: Original Format
Newsgroups: mailing.database.mysql
Date: 2003-12-17 06:32:44 PST


Hi everybody,
I am having a hard time finding any info on this subject, so I was
hoping maybe one of you could give me some pointers.
I am writing an application in Java that uses Connector/J to interface
with a MySQL database with InnoDB tables. When user #1 opens a certain
recordset I am locking this recordset in share mode. Therefore if user
#2 selects the same recordset (and thus placing a lock as well) user
#1 will not be able to update the recordset.
Is there a way to detect this, and thus making it possible to inform
user #1 that an update is currently not possible due to another lock?
Thanks very much for any help, Jonck


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

MySQL GUI tool

[Mike Blezien]

Hello,

Was wondering if any one on the list has or is using PremiumSoft Navicat's MySQL 
Administration Tool for windows, and if so, any thoughts on it... good, 
excellent, poor...etc.

Appreciate the feedback ;)

TIA
--
MikemickaloBlezien
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Thunder Rain Internet Publishing
Providing Internet Solutions that work!
http://www.thunder-rain.com
Quality Web Hosting
http://www.justlightening.net
MSN: [EMAIL PROTECTED]
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Basic error

[Peter Lovatt]

try adding an error report after the query

$result = mysql_query( select * from domains ) or die (mysql_error());

the rest looks OK


Peter

-Original Message-
From: Trevor Rhodes [mailto:[EMAIL PROTECTED]
Sent: 17 December 2003 22:26
To: [EMAIL PROTECTED]
Subject: Basic error


Hello friendly helper types,

What is the problem here.  I can't find any errors. I know it's there but as
a
newbie I'm as blind as a bat at the moment.  Thanks for your help.

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result
resource
Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result
resource

===
$link = mysql_connect( localhost, $user, $pass );
if ( ! $link )
die( Couldn't connect to MySQL );
mysql_select_db( $db, $link )
or die ( Couldn't open $db: .mysql_error() );
$result = mysql_query( select * from domains );
$num_rows = mysql_num_rows( $result );
print There are currently $num_rows rows in the table;
print table border=1\n;
while ( $a_row = mysql_fetch_row( $result ) )
{
print tr\n;
foreach ( $a_row as $field )
print \ttd$field/td\n;
print /tr\n;
}
print /table\n;
mysql_close( $link );
===

 Regards
  Trevor Rhodes
===
Powered by Linux- Mandrake 9.1
Registered Linux user # 290542 at http://counter.li.org
Registered Machine #'s 186951
Mandrake Club Silver Member
Source :  my 100 % Microsoft-free personal computer.
===
 09:25:17 up 1 day,  9:14,  1 user,  load average: 1.02, 1.09, 1.08
--
Never mud wrestle with a pig.. you get dirty and the pig enjoys it!
Never try to teach a pig to dance. You waste your time and annoy the pig.


--
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: foreign keys.

[Mofeed Shahin]

On Thu, 18 Dec 2003 09:05 am, Heikki Tuuri wrote:
 Mofeed,

 - Original Message -
 From: Mofeed Shahin [EMAIL PROTECTED]
 To: Heikki Tuuri [EMAIL PROTECTED]; [EMAIL PROTECTED]
 Sent: Thursday, December 18, 2003 12:20 AM
 Subject: Re: foreign keys.

  Hmmm, Thanks for that.
  But I'm not quite sure what it means.
 
  Mof.
 
  
  LATEST FOREIGN KEY ERROR
  
  031218  8:48:23 Error in foreign key constraint of table moftest/foo,
  FOREIGN KEY (First_Name, Last_Name) REFERENCES Blah (First_Name,

 Last_Name))

  TYPE=INNODB
  Cannot resolve column name close to:
  , Last_Name)) TYPE=INNODB

 does the table Blah have the columns First_Name and Last_Name?

Yep, here are both my create statements :

create table Blah (
ID INT PRIMARY KEY, 
First_Name VARCHAR (50), 
Last_Name VARCHAR(50), 
UNIQUE (First_Name, Last_Name)
);
Query OK, 0 rows affected (0.00 sec)

create table foo (
ID INT PRIMARY KEY, 
note VARCHAR(50), 
First_Name VARCHAR(50), 
Last_Name VARCHAR(50), 
FOO_ID INT, 
INDEX(FOO_ID), 
INDEX(First_Name, Last_Name), 
FOREIGN KEY (FOO_ID) REFERENCES foo(ID), 
FOREIGN KEY (First_Name, Last_Name) REFERENCES Blah (First_Name, Last_Name)
) TYPE=INNODB;
ERROR 1005: Can't create table './moftest/foo.frm' (errno: 150)

Mof.


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

Re: foreign keys.

[Heikki Tuuri]

Mofeed,

- Original Message - 
From: Mofeed Shahin [EMAIL PROTECTED]
To: Heikki Tuuri [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Thursday, December 18, 2003 12:47 AM
Subject: Re: foreign keys.


 On Thu, 18 Dec 2003 09:05 am, Heikki Tuuri wrote:
  Mofeed,
 
  - Original Message -
  From: Mofeed Shahin [EMAIL PROTECTED]
  To: Heikki Tuuri [EMAIL PROTECTED]; [EMAIL PROTECTED]
  Sent: Thursday, December 18, 2003 12:20 AM
  Subject: Re: foreign keys.
 
   Hmmm, Thanks for that.
   But I'm not quite sure what it means.
  
   Mof.
  
   
   LATEST FOREIGN KEY ERROR
   
   031218  8:48:23 Error in foreign key constraint of table moftest/foo,
   FOREIGN KEY (First_Name, Last_Name) REFERENCES Blah (First_Name,
 
  Last_Name))
 
   TYPE=INNODB
   Cannot resolve column name close to:
   , Last_Name)) TYPE=INNODB
 
  does the table Blah have the columns First_Name and Last_Name?

 Yep, here are both my create statements :

 create table Blah (
 ID INT PRIMARY KEY,
 First_Name VARCHAR (50),
 Last_Name VARCHAR(50),
 UNIQUE (First_Name, Last_Name)
 );
 Query OK, 0 rows affected (0.00 sec)

but the table above is MyISAM type?

FOREIGN KEY relationships can only be defined between InnoDB type tables!

 create table foo (
 ID INT PRIMARY KEY,
 note VARCHAR(50),
 First_Name VARCHAR(50),
 Last_Name VARCHAR(50),
 FOO_ID INT,
 INDEX(FOO_ID),
 INDEX(First_Name, Last_Name),
 FOREIGN KEY (FOO_ID) REFERENCES foo(ID),
 FOREIGN KEY (First_Name, Last_Name) REFERENCES Blah (First_Name,
Last_Name)
 ) TYPE=INNODB;
 ERROR 1005: Can't create table './moftest/foo.frm' (errno: 150)

 Mof.

Best regards,

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

Order MySQL technical support from https://order.mysql.com/


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

Re: Basic error

[Steve Davies]

Hi Trevor

You haven't the query which connection to use:

$result = mysql_query( select * from domains, $link );

HTH
Steve Davies
Trevor Rhodes wrote:

Hello friendly helper types,

What is the problem here.  I can't find any errors. I know it's there but as a 
newbie I'm as blind as a bat at the moment.  Thanks for your help.

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result 
resource
Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result 
resource

===
$link = mysql_connect( localhost, $user, $pass );
if ( ! $link )
die( Couldn't connect to MySQL );
mysql_select_db( $db, $link )
or die ( Couldn't open $db: .mysql_error() );
$result = mysql_query( select * from domains );
$num_rows = mysql_num_rows( $result );
print There are currently $num_rows rows in the table;
print table border=1\n;
while ( $a_row = mysql_fetch_row( $result ) )
{
print tr\n;
foreach ( $a_row as $field )
print \ttd$field/td\n;
print /tr\n;
}
print /table\n;
mysql_close( $link );
===
Regards
 Trevor Rhodes
===
Powered by Linux- Mandrake 9.1
Registered Linux user # 290542 at http://counter.li.org
Registered Machine #'s 186951
Mandrake Club Silver Member
Source :  my 100 % Microsoft-free personal computer.
===
09:25:17 up 1 day,  9:14,  1 user,  load average: 1.02, 1.09, 1.08
 



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

RE: Basic error

[Mike Johnson]

From: Trevor Rhodes [mailto:[EMAIL PROTECTED]

 Hello friendly helper types,
 
 What is the problem here.  I can't find any errors. I know 
 it's there but as a 
 newbie I'm as blind as a bat at the moment.  Thanks for your help.
 
 Warning: mysql_num_rows(): supplied argument is not a valid 
 MySQL result 
 resource
 Warning: mysql_fetch_row(): supplied argument is not a valid 
 MySQL result 
 resource
 
 ===
 $link = mysql_connect( localhost, $user, $pass );
 if ( ! $link )
   die( Couldn't connect to MySQL );
 mysql_select_db( $db, $link )
   or die ( Couldn't open $db: .mysql_error() );
 $result = mysql_query( select * from domains );
 $num_rows = mysql_num_rows( $result );
 print There are currently $num_rows rows in the table;
 print table border=1\n;
 while ( $a_row = mysql_fetch_row( $result ) )
   {
   print tr\n;
   foreach ( $a_row as $field )
   print \ttd$field/td\n;
   print /tr\n;
   }
 print /table\n;
 mysql_close( $link );
 ===


Have you tried including $link as a second argument in mysql_query()?

mysql_query( select * from domains, $link );

IIRC, it's not necessary, but it might be worth a shot.


-- 
Mike Johnson
Web Developer/Systems Asst.
Smarter Living, Inc.
phone (617) 497-2500 x226

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

Re: Basic error

[Trevor Rhodes]

Hello friendly helper types,

Forget it people.  I'm an idiot.  Just forgot to change the table name.  Of 
course then it at least had more clue as to what it was doing that myself.  
I'm going away now, but you can be sure I'll be back with more silly 
questions later.  Bye.  :^)

 What is the problem here.  I can't find any errors. I know it's there but
 as a newbie I'm as blind as a bat at the moment.  Thanks for your help.

 Warning: mysql_num_rows(): supplied argument is not a valid MySQL result
 resource
 Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result
 resource

 Regards
  Trevor Rhodes
===
Powered by Linux- Mandrake 9.1
Registered Linux user # 290542 at http://counter.li.org
Registered Machine #'s 186951
Mandrake Club Silver Member
Source :  my 100 % Microsoft-free personal computer.
===
 09:48:09 up 1 day,  9:37,  1 user,  load average: 1.97, 1.38, 1.15
-- 
Never mud wrestle with a pig.. you get dirty and the pig enjoys it!
Never try to teach a pig to dance. You waste your time and annoy the pig.


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

Re: InnoDB lock in share mode problems

[Heikki Tuuri]

Andrew,

SELECT [STRAIGHT_JOIN]
   [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
   [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS] [HIGH_PRIORITY]
   [DISTINCT | DISTINCTROW | ALL]
select_expression,...
[INTO {OUTFILE | DUMPFILE} 'file_name' export_options]
[FROM table_references
  [WHERE where_definition]
  [GROUP BY {unsigned_integer | col_name | formula} [ASC | DESC], ...
[WITH ROLLUP]]
  [HAVING where_definition]
  [ORDER BY {unsigned_integer | col_name | formula} [ASC | DESC] ,...]
  [LIMIT [offset,] row_count | row_count OFFSET offset]
  [PROCEDURE procedure_name(argument_list)]
  [FOR UPDATE | LOCK IN SHARE MODE]]

I think the clause LOCK IN SHARE MODE has to be the very last.

Best regards,

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

Order MySQL technical support from https://order.mysql.com/

..
Search Result 25
From: Andrew Kennard ([EMAIL PROTECTED])
Subject: InnoDB lock in share mode problems
This is the only article in this thread
View: Original Format
Newsgroups: mailing.database.mysql
Date: 2003-12-15 06:42:12 PST


Hi all

I'm new tot PHP/mySQL but have many years programming experience.

We've just setup a new webspace account with Demon Internet in the UK and
it has PHP/mySQL services

I'm just testing the InnoDB an am having a problem with the following
statement

SELECT * FROM invtest LOCK IN SHARE MODE

Which gives the following error:-

Error

SQL-query :

SELECT *
FROM invtest
LOCK IN SHARE MODE LIMIT 0 , 30

MySQL said:

You have an error in your SQL syntax.  Check the manual that corresponds
to your MySQL server version for the right syntax to use near 'LIMIT 0,
30' at line 1


I can't seem to find anything about this limit clause/parameter in any of
the help files. Demon say 'Dunno mate we just provide the service !!!'

Any help with this would be most appreciated as I really need 'proper'
transactions for the project I'm about to start.

Thanks in advance

Andrew Kennard

PS the mySQL version they are running seems to be 4.0.9-gamma-log


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

Re: foreign keys.

[Mofeed Shahin]

hehehe, Yeah figures that it was something stupid I did!!!

Thanks for that.

Mof.

On Thu, 18 Dec 2003 09:30 am, Heikki Tuuri wrote:
 Mofeed,

 - Original Message -
 From: Mofeed Shahin [EMAIL PROTECTED]
 To: Heikki Tuuri [EMAIL PROTECTED]; [EMAIL PROTECTED]
 Sent: Thursday, December 18, 2003 12:47 AM
 Subject: Re: foreign keys.

  On Thu, 18 Dec 2003 09:05 am, Heikki Tuuri wrote:
   Mofeed,
  
   - Original Message -
   From: Mofeed Shahin [EMAIL PROTECTED]
   To: Heikki Tuuri [EMAIL PROTECTED]; [EMAIL PROTECTED]
   Sent: Thursday, December 18, 2003 12:20 AM
   Subject: Re: foreign keys.
  
Hmmm, Thanks for that.
But I'm not quite sure what it means.
   
Mof.
   

LATEST FOREIGN KEY ERROR

031218  8:48:23 Error in foreign key constraint of table moftest/foo,
FOREIGN KEY (First_Name, Last_Name) REFERENCES Blah (First_Name,
  
   Last_Name))
  
TYPE=INNODB
Cannot resolve column name close to:
, Last_Name)) TYPE=INNODB
  
   does the table Blah have the columns First_Name and Last_Name?
 
  Yep, here are both my create statements :
 
  create table Blah (
  ID INT PRIMARY KEY,
  First_Name VARCHAR (50),
  Last_Name VARCHAR(50),
  UNIQUE (First_Name, Last_Name)
  );
  Query OK, 0 rows affected (0.00 sec)

 but the table above is MyISAM type?

 FOREIGN KEY relationships can only be defined between InnoDB type tables!

  create table foo (
  ID INT PRIMARY KEY,
  note VARCHAR(50),
  First_Name VARCHAR(50),
  Last_Name VARCHAR(50),
  FOO_ID INT,
  INDEX(FOO_ID),
  INDEX(First_Name, Last_Name),
  FOREIGN KEY (FOO_ID) REFERENCES foo(ID),
  FOREIGN KEY (First_Name, Last_Name) REFERENCES Blah (First_Name,

 Last_Name)

  ) TYPE=INNODB;
  ERROR 1005: Can't create table './moftest/foo.frm' (errno: 150)
 
  Mof.

 Best regards,

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

 Order MySQL technical support from https://order.mysql.com/


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

Re: Questions about MySQL implementation

[Heikki Tuuri]

Chris,

- Original Message - 
From: Chris Nolan [EMAIL PROTECTED]
Newsgroups: mailing.database.myodbc
Sent: Saturday, December 13, 2003 7:24 AM
Subject: Questions about MySQL implementation


 Hi all!

 I've got a few questions that I was hoping some of the fine readers of
 this list could help me out with. I'll probably be going into a
 development meeting this coming week and will need to have some
 information up my sleave to ensure the mighty MySQL is selected as the
 database backend for the application being developed.

 1. We all know that InnoDB can be backed up hot (by various means). I
 know that there are a few MS SQL Server (ick) and DB2 lovers in the
 group I'll be meeting with this week. I also know that these two
 databases do a form of online backup.

 Given that they are not multiversioned, how on earth do they actually
 provide this functionality? I guess this ties in with how they implement
 the READ REPEATABLE isolatation level. Any comments on implementation,
 performance and other info would be gladly received!

the mechanism used in InnoDB Hot Backup is replaying the generated
ib_logfile log. It is much lower level than the multiversioning of InnoDB.
That is why the same technique would work for DB2 and SQL Server.

 2. I've been told on good authority (by persons on this fine list) that
 Sybase and PostgreSQL (and, from personal experience, SQLBase) support
 ROLLBACK of DDL statements such as DROP TABLE, ALTER TABLE, RENAME TABLE
 etc. From what I can gather, neither BDB nor InnoDB do this.

 Does anyone know what sort of technical challenges making the above
 statements undoable involve over and above INSERT, DELETE and UPDATE
 statements? Would this functionality be something that MySQL AB /
 Innobase Oy would be interested in developing should it be sponsored?

Not very difficult: we could keep the 'old' table until the transaction
commit. In a rollback we would fall back to the old table. But the demand
for such a feature is so low that most databases do not have a rollback of
DDL statements.

 3. At the moment, the MySQL API seems to have a size limit of 16 MB for
 data sent over the wire (I have seen that the MySQL 4.1 libraries allow
 for sending information in chunks along with prepared statements). I
 take it the best method of inserting greater amounts of data into a
 column would be by first writing the file somewhere on the database
 server and using LOAD DATA? Any comments on this of any type from the
 learned populace of MySQL users? :-)

 4. In a DB server that has 2 physical disks running MySQL 4.1.1 Alpha
 and utilising the multiple table space feature of InnoDB, what
 distribution of files (ibdata, log files, individual table space files)
 is likely to result in the best performace? Any insights of similar type
 for using MyISAM tables?

I would just create several ibdata files and place them round-robin on
different physical disks. Also, putting the ib_logfiles to a separate disk
might be a good idea. For MyISAM, I would use symlinks to distribute big
tables to different physical disks.

 All responses will be gratefully received!

 Regards,

 Chris

Best regards,

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

Order MySQL technical support from https://order.mysql.com/


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

Re: Planned transactions?

[Heikki Tuuri]

Chris,

- Original Message - 
From: Chris Nolan [EMAIL PROTECTED]
Newsgroups: mailing.database.myodbc
Sent: Saturday, December 13, 2003 7:54 AM
Subject: Planned transactions?


 Hi again all,

 Given that a transaction looks like this:

 BEGIN;
 SELECT useless_field FROM useless_table WHERE useless_identifier =
 'useless';
 SELECT useless_field FROM useless_table WHERE useless_identifier =
 'something else';
 INSERT INTO useless_table (useless_field, useless_identifier) VALUES
 ('what?','huh?');
 COMMIT;

 Assuming the isolation level is either READ_REPEATABLE or SERIALIZABLE,
 would there be any possible benefit to gain from taking the statements
 that make up the entire transaction, working out what tables and columns
 will be touched and then coming up with some execution policy? I take it
 that at the moment, InnoDB's rollback segments grow in a fashion that is
 basically a backward looking approach of what I've described - am I
 correct?

I do not fully understand what you mean by 'planned' and by 'backward
looking'.

If your isolation level is SERIALIZABLE, then those SELECTs are actually
executed with LOCK IN SHARE MODE, and they do not conceptually look at the
undo logs in the 'rollback segment' at all, only at the row locks set by
other transactions.

If the SELECTs are 'consistent non-locking reads' (the default in InnoDB),
then the first SELECT sets the snapshot timepoint of the transaction. Purge
cannot remove history which is after that timepoint, until the transaction
commits. It does not matter what tables and what rows the SELECTs look at,
the only important thing is the timepoint of the first consistent read
SELECT in the transaction.

 Regards,

 Chris

Best regards,

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

Order MySQL technical support from https://order.mysql.com/


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

Re: Innodb multiple tablespaces benchmark

[Heikki Tuuri]

Carlos,

- Original Message - 
From: Carlos Proal [EMAIL PROTECTED]
Newsgroups: mailing.database.myodbc
Sent: Friday, December 12, 2003 4:09 AM
Subject: Innodb multiple tablespaces benchmark




 Hi all, specially to Heikki.

 Its really amazing that multiple tablespaces are available before 2004,
 congratulations to Innodb Oy Inc.

 Right now im migrating from 4.1.0 to 4.1.1 but im figuring out if there is
a
 downgrade in performance in order to use multiple tablespaces, obviously
it
 must be one because handling several files adds an overhead but it
something
 to worry about ?

a customer has been running benchmarks where he compared multiple
tablespaces (each table in its own .ibd file) to the traditional single big
ibdata file. He said the performance was about the same.

 I thougt that these tablespaces would be for each database and not for
each
 table, this is possible ?, factible ?, useful ?, its scheduled ?.

It is not possible in 4.1.1. I will look at customer feedback and add a more
flexible tablespace concept if there is great demand. Most of the hard work
was already done in 4.1.1.

 Thanx a lot and again some claps for innodb :)

Thank you :).

 Carlos

Oops! Now I realize I have forgotten to document the following my.cnf option
in the manual:

 {innodb_open_files, OPT_INNODB_OPEN_FILES,
   How many files at the maximum InnoDB keeps open at the same time.,
   (gptr*) innobase_open_files, (gptr*) innobase_open_files, 0,
   GET_LONG, REQUIRED_ARG, 300L, 10L, ~0L, 0, 1L, 0},

Best regards,

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

Order MySQL technical support from https://order.mysql.com/


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

Re: [ MySQL: Problems with Innodb ]

[Heikki Tuuri]

Osvaneo,

you probably forgot to move also ib_logfiles.

 InnoDB: Starting log scan based on checkpoint at
 InnoDB: log sequence number 7 1021600303
 InnoDB: Doing recovery: scanned up to log sequence number 7 1021600256

It cannot scan the current ib_logfiles at all!

Best regards,

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

Order MySQL technical support from https://order.mysql.com/

- Original Message - 
From: Osvaneo Ap. Ferreira [EMAIL PROTECTED]
Newsgroups: mailing.database.myodbc
Sent: Friday, December 12, 2003 2:53 AM
Subject: [ MySQL: Problems with Innodb ]


 --=_NextPart_000_000C_01C3BFEE.891285C0
 Content-Type: multipart/alternative;
 boundary==_NextPart_001_000D_01C3BFEE.891719A0


 --=_NextPart_001_000D_01C3BFEE.891719A0
 Content-Type: text/plain;
 charset=iso-8859-1
 Content-Transfer-Encoding: quoted-printable

 Hi,

 I have a problem and would like a help.
 I copied a mysql datadir to another place (backup of datadir), but when =
 start up mysql server with datadir it's crash.

 See below, trace of logfile:

 031211 13:28:01  mysqld started
 031211 13:28:01  InnoDB: Database was not shut down normally.
 InnoDB: Starting recovery from log files...
 InnoDB: Starting log scan based on checkpoint at
 InnoDB: log sequence number 7 1021600303
 InnoDB: Doing recovery: scanned up to log sequence number 7 1021600256
 InnoDB: Error: trying to access a stray pointer c0b7bff8
 InnoDB: buf pool start is at 40388000, number of pages 512
 031211 13:28:01  InnoDB: Assertion failure in thread 16384 in file =
 ../../innobase/include/buf0buf.ic line 284
 InnoDB: We intentionally generate a memory trap.
 InnoDB: Send a detailed bug report to [EMAIL PROTECTED]
 mysqld got signal 11;
 This could be because you hit a bug. It is also possible that this =
 binary
 or one of the libraries it was linked against is corrupt, improperly =
 built,
 or misconfigured. This error can also be caused by malfunctioning =
 hardware.
 We will try our best to scrape up some info that will hopefully help =
 diagnose
 the problem, but since we have already crashed, something is definitely =
 wrong
 and this may fail.

 key_buffer_size=3D8388600
 read_buffer_size=3D131072
 sort_buffer_size=3D137
 max_used_connections=3D0
 max_connections=3D100
 threads_connected=3D0
 It is possible that mysqld could use up to=20
 key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections =
 =3D 21005 K
 bytes of memory
 Hope that's ok; if not, decrease some variables in the equation.

 thd=3D0x83322c0
 Attempting backtrace. You can use the following information to find out
 where mysqld died. If you see no messages after this, something went
 terribly wrong...
 Bogus stack limit or frame pointer, fp=3D0xbfffd368, =
 stack_bottom=3D0x706f6e6d, thread_stack=3D196608, aborting backtra
 ce.
 Trying to get some variables.
 Some pointers may be invalid and cause the dump to abort...
 thd-query at 0x6c6b6a69  is invalid pointer
 thd-thread_id=3D0

 Successfully dumped variables, if you ran with --log, take a look at the
 details of what thread 0 did to cause the crash.  In some cases of =
 really
 bad corruption, the values shown above may be invalid.

 The manual page at http://www.mysql.com/doc/C/r/Crashing.html contains
 information that should help you find out what is causing the crash.
 031211 13:28:01  mysqld ended


 Server Installed: mysql-4.0.12

 Help me !!!.


 Osv=E2neo Ap. Ferreira
 http://www.ig.com.br


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

Re: MySQL 4.0.17 has been released

[Yves Goergen]

OK, ehm, considering Heikki's post, why does MySQL take care of
case-sensitive table names at all? I'm not that familiar with the whole
thing about it, but aren't database names also case-insensitive? I know that
column names are. So why isn't this option removed and table names are
generally treated case-insensitive? (E.g. forcing lowercase_tablenames=1
anywhere.) This won't resolve the problems with older database servers
though, as long as I have no chance to change the lowercase_tablenames
setting (BTW, can I change this per connection?), but in future, this
problem could be avoided.

-- 
Yves Goergen
[EMAIL PROTECTED]
Please don't CC me (causes double mails)


On Wednesday, December 17, 2003 11:37 PM CET, Matt W wrote:
 Hi,

 I saw the change as soon as it was posted last week or whenever and
 didn't think anything of it. But the point Yves brings up seems very
 important!

 Although, I'm not sure what to do then with bug #1812. Too bad
 MySQL's code can't make database/table names case-sensitive like on
 *nix.  e.g. *Force* the case used in queries match that of the
 directory/file name...


 Matt


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

Re: MySQL 4.0.17 has been released

[Sergei Golubchik]

Hi!

Well, I checked the code and looks like in 4.0.17 lower_case_table_names
is NOT forced to 1 on case-insensitive file system, despite the
changelog entry (that we will fix, by the way). Obviously a programming
mistake, but for now only warning that lower_case_table_names is forced to 1
is issued, no actual assignement takes place.

I think, as there are users that need lower_case_table_names=0 on
Windows, we could set the *default* value based on filesystem
case-sensitivity, but with the possibility to change it from the command
line, and issue a BIG RED WARNING if you'll do.

So don't do it unless you know what you are doing.
To quote Monty:


  The 'bug' here is that if you set lower_case_table_names to OFF on a
  system where you have case insensitive file names (like windows) you
  WILL get table corruption if you access tables with different cases.

  This is becasue MySQL doesn't know that the tables 'name' and 'NAME'
  are in fact the same table and will cache and lock each of these
  separately.


On Dec 17, Reverend Deuce wrote:
 I agree, 100%. We live in a mixed environment of UNIX and Windows and as
 such, we've assumed case insensitivity in our apps. I know that this is bad
 practice, but forcing this flag on us is and even worse practice. This
 should always, always be an option.
 
 I wont be able to upgrade until this is fixed. :(
 
 -- R
 
 - Original Message - 
 From: Yves Goergen [EMAIL PROTECTED]
 To: Lenz Grimmer [EMAIL PROTECTED]; [EMAIL PROTECTED]
 Sent: Wednesday, December 17, 2003 1:12 PM
 Subject: Re: MySQL 4.0.17 has been released
 
 
  On Wednesday, December 17, 2003 1:58 PM CET, Lenz Grimmer wrote:
   Functionality added or changed:
  
  * `lower_case_table_names' is now forced to 1 if the database
directory is located on a case-insensitive file system. (Bug #1812)
 
  Uh, _very_ bad. I know that my Windows filesystem is case-insensitive and
  that I cannot create tables only differing by case of the name, but I need
  to have this setting off to be able to dump my tables on Windows with the
  correct (and not lower-cased!!) table names to import them on my webhoster's
  Linux server. If the setting was enabled, I'd get all wrong table names and
  my application couldn't find its tables anymore (as 'bb1_GroupMembers' gets
  to 'bb1_groupmembers' and that's something else!).
 
  So please change this back again, I believe it's up to the server
  administrator to set this in a correct and reasonable way, don't you?

Regards,
Sergei

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

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

Re: high water mark

[Matt W]

Hi Jamie,

Yes, when tables are made smaller in MySQL, the file size isn't
actually reduced. I guess it shouldn't usually make a speed difference
unless your SELECTs are doing a table scan. Reads that use an index
shouldn't really be affected, I don't think.

If you want to reclaim the deleted space, you can do so by running
OPTIMIZE TABLE.  Be aware that it may take awhile to run on a large
table!


Hope that helps.


Matt


- Original Message -
From: jamie murray
Sent: Wednesday, December 17, 2003 2:42 PM
Subject: high water mark


Guys,
Does mysql record the high water mark in it's tables. If so can it be
shrunken after a mass delete. I am new to mysql and haven't seen any
info on this so I'm not sure if it exists in this database. I am asking
this because after I deleted a lot of data from one table a simple
select is now very slow, this would make me think that like in oracle
the query is reading all blocks up to the high water mark even thought
there is no data there.
I am familiar with oracle but not MySql so excuse my ignorance if it is
showing with this particular topic.

Cheers


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

Re: MySQL GUI tool

[Amanullah]

Hi,

I'm Using the Same, the Tool is very much useful to me
to export data from one server to another online, and
for much more activities.
Excellent...

-Aman.

Mike Blezien wrote:

Hello,

Was wondering if any one on the list has or is using PremiumSoft 
Navicat's MySQL Administration Tool for windows, and if so, any 
thoughts on it... good, excellent, poor...etc.

Appreciate the feedback ;)

TIA




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

Is It Possible To Change the Value of A Particular Field Manually?

[Caroline Jen]

Hi, I am not a database person.  I have a database
called members.  One of the fields in this database
is user_name.   There are a number of records in
this database.  Under the field user_name, I would
like to make some changes manually; for example, I
want to change

John Doe   

to 

john_doe  

Is it possible to do it?  How do I do it?  Thank you
very much in advance.

__
Do you Yahoo!?
New Yahoo! Photos - easier uploading and sharing.
http://photos.yahoo.com/

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

mysql_connect() problem

[ads mysql]

Hi,
I am trying to setup network having 
1) Linux main server running IPTABLES firewall, squid, dns server.
2) LAN connected to main server.
2) DMZ having apache, mysql and php, mail server connected to main server.
User will use php script from remote host to connect to mysql as follows:
MYSQL_CONNECT(localhost, abc, abc ) OR DIE(Unable to connect to database);
@mysql_select_db(abc) or die(Unable to select database);
where local host will be dmz. 
Is it O.K. if I give all user give host as localhost in script?
Will it involve any security problem?
If so what is generally done in this type of case.
My reverse DNS does not point to my domain instead it point sto domain of Internet 
bandwidth service porvider.
Thanks for support.
 


-
Do you Yahoo!?
New Yahoo! Photos - easier uploading and sharing

Re: MySQL GUI tool

[Sanya]

I have been using it for  a long time is works fine ! 
 
:) 
 
-
syed sanya
Amanullah [EMAIL PROTECTED] wrote:
Hi,

I'm Using the Same, the Tool is very much useful to me
to export data from one server to another online, and
for much more activities.

Excellent...

-Aman.

Mike Blezien wrote:

 Hello,

 Was wondering if any one on the list has or is using PremiumSoft 
 Navicat's MySQL Administration Tool for windows, and if so, any 
 thoughts on it... good, excellent, poor...etc.

 Appreciate the feedback ;)

 TIA




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


-
Do you Yahoo!?
New Yahoo! Photos - easier uploading and sharing

Binary install instructions wrong?

[Dave Best]

Hey all,

I don't like the default locations that mysql tries to install into.. I
like putting stuff in user accounts, with a version directory.  Easier to
maintain, yada yada.

Anyways, as per the instructions you have to change a path in
./bin/mysqlaccess

I did this but when I run the scripts/mysql_install_db I get the
following...  What am I missing?


mkdir: cannot create directory `/var/lib/mysql': Permission denied
chmod: failed to get attributes of `/var/lib/mysql': No such file or
directory
mkdir: cannot create directory `/var/lib/mysql/mysql': No such file or
directory
chmod: failed to get attributes of `/var/lib/mysql/mysql': No such file or
directory
mkdir: cannot create directory `/var/lib/mysql/test': No such file or
directory
chmod: failed to get attributes of `/var/lib/mysql/test': No such file or
directory
Preparing db table
Preparing host table
Preparing user table
Preparing func table
Preparing tables_priv table
Preparing columns_priv table
Installing all prepared tables
031217 23:57:16  Warning: Can't create test file
/var/lib/mysql/localhost.lower-test
./bin/mysqld: Can't change dir to '/var/lib/mysql/' (Errcode: 2)
031217 23:57:16  Aborting

031217 23:57:16  ./bin/mysqld: Shutdown Complete

Installation of grant tables failed!

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

shell ./bin/mysql -u root mysql
mysql show tables

Try 'mysqld --help' if you have problems with paths. Using --log
gives you a log in /var/lib/mysql that may be helpful.

The latest information about MySQL is available on the web at
http://www.mysql.com
Please consult the MySQL manual section: 'Problems running
mysql_install_db',
and the manual section that describes problems on your OS.
Another information source is the MySQL email archive.
Please check all of the above before mailing us!
And if you do mail us, you MUST use the ./bin/mysqlbug script!




[EMAIL PROTECTED]
Thanks to the remote control I have the attention span of a gerbil!
There are 10 types of people in the world.  Those who understand binary,
and those who don't.



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

Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?

[mos]

Martijn,
	I've asked this question a year ago on the Borland forums but didn't get 
any replies. Do you know of any websites (non-programming related) that are 
using Interbase/Firebird for their primary webserver database? I don't mean 
programmer hangouts like  www.mers.com or www.tamaracka.com etc., but 
Fortune 1000 companies who are using IB/FB on the web to serve up web 
pages? It would be interesting to get a list of who's using it online. TIA

Mike 



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

mysql_connect() problem

[ads mysql]

Hi,
I am trying to setup network having 
1) Linux main server running IPTABLES firewall, squid, dns server.
2) LAN connected to main server.
2) DMZ having apache, mysql and php, mail server connected to main server.

User will use php script from remote host to connect to mysql as follows:

MYSQL_CONNECT(localhost, abc, abc ) OR DIE(Unable to connect to database);
@mysql_select_db(abc) or die(Unable to select database);

where local host will be dmz. 

Is it O.K. if I give all user give host as localhost in script?
Will it involve any security problem?
If so what is generally done in this type of case.
My reverse DNS does not point to my domain instead it point sto domain of Internet 
bandwidth service porvider.
Thanks for support.



-
Do you Yahoo!?
New Yahoo! Photos - easier uploading and sharing

Re: Is It Possible To Change the Value of A Particular Field Manually?

[Michael Stassen]

Caroline Jen wrote:
Hi, I am not a database person.  I have a database
called members.  One of the fields in this database
is user_name.   There are a number of records in
this database.  Under the field user_name, I would
like to make some changes manually; for example, I
want to change
John Doe   

to 

john_doe  

Is it possible to do it?  How do I do it?  Thank you
very much in advance.
Yes.  You can specify the records you want with a WHERE clause.  First 
try a SELECT to test your WHERE clause to make sure you get just the 
record(s) you want:

SELECT * FROM members WHERE user_name = 'John Doe';

As long as that looks right, then use UPDATE to modify the record(s):

UPDATE members SET user_name='john_doe' WHERE user_name = 'John Doe';

See http://www.mysql.com/doc/en/UPDATE.html in the manual for more.

Michael

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

Problems with MySQL 4.0.16

[Sven Dzepina]

Hello,

I am using MySQL 4.0.16 on Debian GNU/Linux at the moment. In this database
there are over 4,5 Mio. entries!
The Database crashs with follow errors:


031217 19:21:06  mysqld started
/opt/lampp/sbin/mysqld: ready for connections.
Version: '4.0.16-log'  socket: '/opt/lampp/var/mysql/mysql.sock'  port: 3306
mysqld got signal 11;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help
diagnose
the problem, but since we have already crashed, something is definitely
wrong
and this may fail.

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

thd=(nil)
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
Cannot determine thread, fp=0xb528, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x80d062f
0x40144f54
0x4028b7ee
0x80d1a6d
0x401da14f
0x80a6121
New value of fp=(nil) failed sanity check, terminating stack trace!
Please read http://www.mysql.com/doc/en/Using_stack_trace.html and follow
instructions on how to resolve the stack trace. Resolved
stack trace is much more helpful in diagnosing the problem, so please do
resolve it
The manual page at http://www.mysql.com/doc/en/Crashing.html contains
information that should help you find out what is causing the crash.


What I can do except the help in the error message for repair the mysql ?

I  need this database urgently!

Thanks for Help!



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

Re: Is It Possible To Change the Value of A Particular Field Manually?

[Nitin]

you can optionally use MySQL Front to view and edit data in GUI mode

get it free of cost athttp://www.mysqlfront.de/

Enjoy
Nitin

- Original Message - 
From: Michael Stassen [EMAIL PROTECTED]
To: Caroline Jen [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: Thursday, December 18, 2003 11:28 AM
Subject: Re: Is It Possible To Change the Value of A Particular Field
Manually?



 Caroline Jen wrote:
  Hi, I am not a database person.  I have a database
  called members.  One of the fields in this database
  is user_name.   There are a number of records in
  this database.  Under the field user_name, I would
  like to make some changes manually; for example, I
  want to change
 
  John Doe
 
  to
 
  john_doe
 
  Is it possible to do it?  How do I do it?  Thank you
  very much in advance.

 Yes.  You can specify the records you want with a WHERE clause.  First
 try a SELECT to test your WHERE clause to make sure you get just the
 record(s) you want:

 SELECT * FROM members WHERE user_name = 'John Doe';

 As long as that looks right, then use UPDATE to modify the record(s):

 UPDATE members SET user_name='john_doe' WHERE user_name = 'John Doe';

 See http://www.mysql.com/doc/en/UPDATE.html in the manual for more.

 Michael


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