Re: How to create mysql user?
dear buddy,
when u specify permissions on a particular database, you need to give that
database name to connect to db.
user created fine, while connecting, try this:
mysql -u abc -p abc
where last abc is ur database name.
Nitin
- Original Message -
From: ads mysql [EMAIL PROTECTED]
To: Nitin [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Tuesday, December 16, 2003 11:51 AM
Subject: Re: How to create mysql user?
O K.
I accessed to mysql as root user and tried to created user 'abc' as folows
:
[EMAIL PROTECTED] mysql]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 61 to server version: 4.0.16-standard
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
I have created database 'abc' .
mysql grant ALL on abc.table to 'abc' identified by 'abc';
Query OK, 0 rows affected (0.00 sec)
I want to give all permission to user abc to handle database 'abc'.
Then I tried to connect as user 'abc' to mysql as follows :
[EMAIL PROTECTED] mysql]# mysql -u abc -p
Enter password:
ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES)
Some problem.
Help appreciated.
Nitin [EMAIL PROTECTED] wrote:
right now, u r trying to connect t omyql with uer abc, not creating it.
to create user abc
grant
on dbname.tablename to 'abc' identified by 'abc';
you can view the list of privileges available on myql website at
http://www.mysql.com/doc/en/GRANT.html
Enjoy
Nitin
- Original Message -
From: ads mysql
To:
Sent: Tuesday, December 16, 2003 11:09 AM
Subject: How to create mysql user?
Hi,
I have installed mysql. As per documentation with user as 'root'.
I can create Database, table. I can enter data into table.
I have created user 'abc' with passowrd 'abc' on my Linux 8.0 server.
by using command :
adduser abc -p 'abc'
Now I want to create mysql database user 'abc' with command :
[EMAIL PROTECTED] mysql]# mysql -u abc -p
Enter password: (entered abc)
ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password:
YES)
Is it granting permission to abc. I refered documentation but could not
get it clearly Neet sample code.
Then I connected mysql as 'root' user. and tried.
mysql mysql -u abc -p;
ERROR 1064: You have an error in your SQL syntax. Check the manual that
corresonds to your MySQL server version for the right syntax to use near
'mysql -u ab -p' at line 1.
Syntax I got from Documentation for GRANT as follows:
###
4.4.1 GRANT and REVOKE Syntax
GRANT priv_type [(column_list)] [, priv_type [(column_list)] ...]
ON {tbl_name | * | *.* | db_name.*}
TO user_name [IDENTIFIED BY [PASSWORD] 'password']
[, user_name [IDENTIFIED BY [PASSWORD] 'password'] ...]
###
Please guide me how to create users for mysql with password.
Thanks for support.
-
Do you Yahoo!?
New Yahoo! Photos - easier uploading and sharing
-
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: Can I really have no_wait row-locks in MySQL+InnoDB?
mos wrote: At 04:22 AM 12/15/2003, you wrote: To elaborate on Dr Frank's thing if you're interested, here's a classic deadlock example: 1. Transaction A obtains an exclusive lock on a set of rows which we will call R1. 2. Transaction B obtains an exclusive lock on another set of rows which we will call R2. 3. Transaction A requests (but obviously doesn't acquire) an exclusive lock on R2. 4. Transaction B requests (but obviously doesn't acquire) an exclusive lock on R1. 5. Classic deadlock! There are various ways of dealing with this (with timeouts and graph traversal seemingly popular). As you can see, neither transaction can go forward. Thus, the almighty InnoDB will think for a moment, decide which transaction it has something personal against, laugh at it and force it to ROLLBACK. That transaction can then try again if it doesn't feel totally small and humiliated. Hope this helps! Regards, Chris Chris, When my MySQL database gets into a deadlock situation like that, I just shutdown the server, power off the machine and go home. Works every time.vbg Mike (Sorry, it's been a late night) No no no!!! You've got it all wrong! You go and find the user who had the least involvement in causing the deadlock and scapegoat them! Either that, or you use it as an excuse for doing one of the following things: 1. Getting the purchase of more hardware approved. 2. Getting the purchase of MySQL Pro and InnoDB Hot Backup approved 3. Getting a MySQL support contract purchase approved 4. Getting a raise for resolving the situation with poise, grace and subtlety (Eg: Shutting it down, blaming users, blaming SCO...) Regards, Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to create mysql user?
O K. I have tried following as advised.
#
[EMAIL PROTECTED] mysql]# mysql -u abc -p abc
Enter password:
ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES)
##
Then once again I accessed mysql as root user and Granted permission as follows :
[EMAIL PROTECTED] mysql]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 69 to server version: 4.0.16-standard
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON abc.* TO [EMAIL PROTECTED]
IDENTIFIED BY 'abc';
Query OK, 0 rows affected (0.01 sec)
mysql USE abc
Database changed
mysql exit
Bye
[EMAIL PROTECTED] mysql]# mysql -u abc -p abc
Enter password:
ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES)
Don't know what is wrong.
Thanks for support.
Nitin [EMAIL PROTECTED] wrote:
dear buddy,
when u specify permissions on a particular database, you need to give that
database name to connect to db.
user created fine, while connecting, try this:
mysql -u abc -p abc
where last abc is ur database name.
Nitin
- Original Message -
From: ads mysql
To: Nitin ;
Sent: Tuesday, December 16, 2003 11:51 AM
Subject: Re: How to create mysql user?
O K.
I accessed to mysql as root user and tried to created user 'abc' as folows
:
[EMAIL PROTECTED] mysql]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 61 to server version: 4.0.16-standard
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
I have created database 'abc' .
mysql grant ALL on abc.table to 'abc' identified by 'abc';
Query OK, 0 rows affected (0.00 sec)
I want to give all permission to user abc to handle database 'abc'.
Then I tried to connect as user 'abc' to mysql as follows :
[EMAIL PROTECTED] mysql]# mysql -u abc -p
Enter password:
ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES)
Some problem.
Help appreciated.
Nitin wrote:
right now, u r trying to connect t omyql with uer abc, not creating it.
to create user abc
grant
on dbname.tablename to 'abc' identified by 'abc';
you can view the list of privileges available on myql website at
http://www.mysql.com/doc/en/GRANT.html
Enjoy
Nitin
- Original Message -
From: ads mysql
To:
Sent: Tuesday, December 16, 2003 11:09 AM
Subject: How to create mysql user?
Hi,
I have installed mysql. As per documentation with user as 'root'.
I can create Database, table. I can enter data into table.
I have created user 'abc' with passowrd 'abc' on my Linux 8.0 server.
by using command :
adduser abc -p 'abc'
Now I want to create mysql database user 'abc' with command :
[EMAIL PROTECTED] mysql]# mysql -u abc -p
Enter password: (entered abc)
ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password:
YES)
Is it granting permission to abc. I refered documentation but could not
get it clearly Neet sample code.
Then I connected mysql as 'root' user. and tried.
mysql mysql -u abc -p;
ERROR 1064: You have an error in your SQL syntax. Check the manual that
corresonds to your MySQL server version for the right syntax to use near
'mysql -u ab -p' at line 1.
Syntax I got from Documentation for GRANT as follows:
###
4.4.1 GRANT and REVOKE Syntax
GRANT priv_type [(column_list)] [, priv_type [(column_list)] ...]
ON {tbl_name | * | *.* | db_name.*}
TO user_name [IDENTIFIED BY [PASSWORD] 'password']
[, user_name [IDENTIFIED BY [PASSWORD] 'password'] ...]
###
Please guide me how to create users for mysql with password.
Thanks for support.
-
Do you Yahoo!?
New Yahoo! Photos - easier uploading and sharing
-
Do you Yahoo!?
New Yahoo! Photos - easier uploading and sharing
-
Do you Yahoo!?
New Yahoo! Photos - easier uploading and sharing
ERROR 2026: SSL connection error ... what's happening?
Hello, I'm running MySQL 4.0.16 (source compiled) on FreeBSD 5.1, and I have a big problem with SSL... It runs perfectly for a few hours, but then suddenly I can't connect any longer via an SSL-connection... ERROR 2026: SSL connection error That's the error that suddenly appears when using mysql-client, and it also happens with other apllications that use SSL-connection to MySQL Any idea what this could be? ldd: ldd /usr/local/mysql/libexec/mysqld /usr/local/mysql/libexec/mysqld: libssl.so.3 = /usr/lib/libssl.so.3 (0x282bb000) libcrypto.so.3 = /usr/lib/libcrypto.so.3 (0x282ec000) libz.so.2 = /usr/lib/libz.so.2 (0x283f7000) libcrypt.so.2 = /usr/lib/libcrypt.so.2 (0x28405000) libstdc++.so.4 = /usr/lib/libstdc++.so.4 (0x2841e000) libm.so.2 = /usr/lib/libm.so.2 (0x284d3000) libc_r.so.5 = /usr/lib/libc_r.so.5 (0x284f) libc.so.5 = /usr/lib/libc.so.5 (0x28513000) OpenSSL in /usr is OpenSSL 0.9.7a Feb 19 2003 from the base system. -- WideXS http://www.widexs.nl Wouter de Jong System-Administrator Tel +31 (0)23 5698070 Fax +31 (0)23 5698099 Bijlmermeerstraat 62, 2131 HG HOOFDDORP, NL -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Getting Records where date is LESS THAN today, AND...
Hi Richard, Okay, that sounds good and all, but how does that help me, since the date is chosen from the javascript calander in this format: mm/dd/ So then, when I'm selecting a date of at least todays value, or less in the database how would I do it, since it's in mm/dd/? I don't know if you're in the position to change it, but if I were you, I'd change whatever goes into the database into a real DATE value as it's much easier to handle. If it comes in 3 values from the Javascript, combine them into a date. Is there a BETTER way to do this? Yes, store a DATE instead of seperate day/month/year values. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?
Looking at the facts, the number of files used to store your data is of very little consequence at the end of the day. Looking at the following: * MS SQL Server stores database data in a single file and logs in another. * Oracle and FoxPro both have a pretty large number of files, for very different reasons * MySQL stores MyISAM each table in a few files: FRM for the schema, MYD for data and MYI for the index * FileMaker Pro stores each table along with a bunch of interface stuff in a single file * SQLBase (popular in the contract / closed market segment) has a single file unless you tell it to partition the database. This single file stores tables, stored procedures, views, triggers, indexes and all transaction logs * MySQL requires 3 types of file for InnoDB storage in 3.23.x and 4.x - ibdata*, ib_arch_log_* and iblogfile*, in 4.1.1 and higher you can go to 4 files with individual table spaces The last point is particularly worthy of note. The new option in 4.1.1 and higher has implications for performance - you can have your InnoDB data dictionary (ibdata*), logs and individual table/index spaces on physically seperate devices. Backing things up becomes more complicated though. To be honest, the vast majority of database installations experience problems in performance caused by poor query and schema design, bad application logic or grossly underspecified hardware. A change in the number of files used to store the data is extremely unlikely to resolve these problems given all other variables in the environment remain fixed. What does everyone else think? I fully agree with the part about performance and the number of files. :-) With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to create mysql user?
did u do a
flush privilege
- Original Message -
From: ads mysql [EMAIL PROTECTED]
To: Nitin [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Tuesday, December 16, 2003 1:00 PM
Subject: Re: How to create mysql user?
O K. I have tried following as advised.
#
[EMAIL PROTECTED] mysql]# mysql -u abc -p abc
Enter password:
ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES)
##
Then once again I accessed mysql as root user and Granted permission as
follows :
[EMAIL PROTECTED] mysql]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 69 to server version: 4.0.16-standard
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON abc.* TO
[EMAIL PROTECTED] IDENTIFIED BY 'abc';
Query OK, 0 rows affected (0.01 sec)
mysql USE abc
Database changed
mysql exit
Bye
[EMAIL PROTECTED] mysql]# mysql -u abc -p abc
Enter password:
ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES)
Don't know what is wrong.
Thanks for support.
Nitin [EMAIL PROTECTED] wrote:
dear buddy,
when u specify permissions on a particular database, you need to give that
database name to connect to db.
user created fine, while connecting, try this:
mysql -u abc -p abc
where last abc is ur database name.
Nitin
- Original Message -
From: ads mysql
To: Nitin ;
Sent: Tuesday, December 16, 2003 11:51 AM
Subject: Re: How to create mysql user?
O K.
I accessed to mysql as root user and tried to created user 'abc' as
folows
:
[EMAIL PROTECTED] mysql]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 61 to server version: 4.0.16-standard
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
I have created database 'abc' .
mysql grant ALL on abc.table to 'abc' identified by 'abc';
Query OK, 0 rows affected (0.00 sec)
I want to give all permission to user abc to handle database 'abc'.
Then I tried to connect as user 'abc' to mysql as follows :
[EMAIL PROTECTED] mysql]# mysql -u abc -p
Enter password:
ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password:
YES)
Some problem.
Help appreciated.
Nitin wrote:
right now, u r trying to connect t omyql with uer abc, not creating it.
to create user abc
grant
on dbname.tablename to 'abc' identified by 'abc';
you can view the list of privileges available on myql website at
http://www.mysql.com/doc/en/GRANT.html
Enjoy
Nitin
- Original Message -
From: ads mysql
To:
Sent: Tuesday, December 16, 2003 11:09 AM
Subject: How to create mysql user?
Hi,
I have installed mysql. As per documentation with user as 'root'.
I can create Database, table. I can enter data into table.
I have created user 'abc' with passowrd 'abc' on my Linux 8.0 server.
by using command :
adduser abc -p 'abc'
Now I want to create mysql database user 'abc' with command :
[EMAIL PROTECTED] mysql]# mysql -u abc -p
Enter password: (entered abc)
ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password:
YES)
Is it granting permission to abc. I refered documentation but could
not
get it clearly Neet sample code.
Then I connected mysql as 'root' user. and tried.
mysql mysql -u abc -p;
ERROR 1064: You have an error in your SQL syntax. Check the manual
that
corresonds to your MySQL server version for the right syntax to use near
'mysql -u ab -p' at line 1.
Syntax I got from Documentation for GRANT as follows:
###
4.4.1 GRANT and REVOKE Syntax
GRANT priv_type [(column_list)] [, priv_type [(column_list)] ...]
ON {tbl_name | * | *.* | db_name.*}
TO user_name [IDENTIFIED BY [PASSWORD] 'password']
[, user_name [IDENTIFIED BY [PASSWORD] 'password'] ...]
###
Please guide me how to create users for mysql with password.
Thanks for support.
-
Do you Yahoo!?
New Yahoo! Photos - easier uploading and sharing
-
Do you Yahoo!?
New Yahoo! Photos - easier uploading and sharing
-
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: How to create mysql user?
As advised I tried following :
###
[EMAIL PROTECTED] root]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 71 to server version: 4.0.16-standard
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql grant ALL on abc.* to 'abc'@'localhost' identified by 'abc';
Query OK, 0 rows affected (0.00 sec)
mysql exit
Bye
[EMAIL PROTECTED] root]# mysql -u abc -p abc
Enter password:
ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES)
No luck.
Help appreciated.
Duke, Brian [EMAIL PROTECTED] wrote:
try this:
grant ALL on abc.* to 'abc'@'localhost' identified by 'abc';
I have created database 'abc' .
mysql grant ALL on abc.table to 'abc' identified by 'abc';
Query OK, 0 rows affected (0.00 sec)
-Original Message-
From: ads mysql [mailto:[EMAIL PROTECTED]
Sent: Monday, December 15, 2003 11:21 PM
To: Nitin; [EMAIL PROTECTED]
Subject: Re: How to create mysql user?
O K.
I accessed to mysql as root user and tried to created user 'abc' as folows :
[EMAIL PROTECTED] mysql]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 61 to server version: 4.0.16-standard
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
I have created database 'abc' .
mysql grant ALL on abc.table to 'abc' identified by 'abc';
Query OK, 0 rows affected (0.00 sec)
I want to give all permission to user abc to handle database 'abc'.
Then I tried to connect as user 'abc' to mysql as follows :
[EMAIL PROTECTED] mysql]# mysql -u abc -p
Enter password:
ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES)
Some problem.
Help appreciated.
Nitin wrote:
right now, u r trying to connect t omyql with uer abc, not creating it.
to create user abc
grant
on dbname.tablename to 'abc' identified by 'abc';
you can view the list of privileges available on myql website at
http://www.mysql.com/doc/en/GRANT.html
Enjoy
Nitin
- Original Message -
From: ads mysql
To:
Sent: Tuesday, December 16, 2003 11:09 AM
Subject: How to create mysql user?
Hi,
I have installed mysql. As per documentation with user as 'root'.
I can create Database, table. I can enter data into table.
I have created user 'abc' with passowrd 'abc' on my Linux 8.0 server.
by using command :
adduser abc -p 'abc'
Now I want to create mysql database user 'abc' with command :
[EMAIL PROTECTED] mysql]# mysql -u abc -p
Enter password: (entered abc)
ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES)
Is it granting permission to abc. I refered documentation but could not
get it clearly Neet sample code.
Then I connected mysql as 'root' user. and tried.
mysql mysql -u abc -p;
ERROR 1064: You have an error in your SQL syntax. Check the manual that
corresonds to your MySQL server version for the right syntax to use near
'mysql -u ab -p' at line 1.
Syntax I got from Documentation for GRANT as follows:
###
4.4.1 GRANT and REVOKE Syntax
GRANT priv_type [(column_list)] [, priv_type [(column_list)] ...]
ON {tbl_name | * | *.* | db_name.*}
TO user_name [IDENTIFIED BY [PASSWORD] 'password']
[, user_name [IDENTIFIED BY [PASSWORD] 'password'] ...]
###
Please guide me how to create users for mysql with password.
Thanks for support.
-
Do you Yahoo!?
New Yahoo! Photos - easier uploading and sharing
-
Do you Yahoo!?
New Yahoo! Photos - easier uploading and sharing
-
Do you Yahoo!?
New Yahoo! Photos - easier uploading and sharing
'Sum' syntax
I'm using v3.23.51 on Linux. Can anyone explain why a simple 'sum' select won't work?? Here is the example: mysql select sum (id), pet from visits group by pet; ERROR 1064: You have an error in your SQL syntax near '(id), pet from visits group by pet' at line 1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fw: How to create mysql user?
- Original Message -
From: Nitin [EMAIL PROTECTED]
To: ads mysql [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Tuesday, December 16, 2003 2:01 PM
Subject: Re: How to create mysql user?
did u do a
flush privilege
- Original Message -
From: ads mysql [EMAIL PROTECTED]
To: Nitin [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Tuesday, December 16, 2003 1:00 PM
Subject: Re: How to create mysql user?
O K. I have tried following as advised.
#
[EMAIL PROTECTED] mysql]# mysql -u abc -p abc
Enter password:
ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password:
YES)
##
Then once again I accessed mysql as root user and Granted permission as
follows :
[EMAIL PROTECTED] mysql]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 69 to server version: 4.0.16-standard
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON abc.* TO
[EMAIL PROTECTED] IDENTIFIED BY 'abc';
Query OK, 0 rows affected (0.01 sec)
mysql USE abc
Database changed
mysql exit
Bye
[EMAIL PROTECTED] mysql]# mysql -u abc -p abc
Enter password:
ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password:
YES)
Don't know what is wrong.
Thanks for support.
Nitin [EMAIL PROTECTED] wrote:
dear buddy,
when u specify permissions on a particular database, you need to give
that
database name to connect to db.
user created fine, while connecting, try this:
mysql -u abc -p abc
where last abc is ur database name.
Nitin
- Original Message -
From: ads mysql
To: Nitin ;
Sent: Tuesday, December 16, 2003 11:51 AM
Subject: Re: How to create mysql user?
O K.
I accessed to mysql as root user and tried to created user 'abc' as
folows
:
[EMAIL PROTECTED] mysql]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 61 to server version: 4.0.16-standard
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
I have created database 'abc' .
mysql grant ALL on abc.table to 'abc' identified by 'abc';
Query OK, 0 rows affected (0.00 sec)
I want to give all permission to user abc to handle database 'abc'.
Then I tried to connect as user 'abc' to mysql as follows :
[EMAIL PROTECTED] mysql]# mysql -u abc -p
Enter password:
ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password:
YES)
Some problem.
Help appreciated.
Nitin wrote:
right now, u r trying to connect t omyql with uer abc, not creating
it.
to create user abc
grant
on dbname.tablename to 'abc' identified by 'abc';
you can view the list of privileges available on myql website at
http://www.mysql.com/doc/en/GRANT.html
Enjoy
Nitin
- Original Message -
From: ads mysql
To:
Sent: Tuesday, December 16, 2003 11:09 AM
Subject: How to create mysql user?
Hi,
I have installed mysql. As per documentation with user as 'root'.
I can create Database, table. I can enter data into table.
I have created user 'abc' with passowrd 'abc' on my Linux 8.0
server.
by using command :
adduser abc -p 'abc'
Now I want to create mysql database user 'abc' with command :
[EMAIL PROTECTED] mysql]# mysql -u abc -p
Enter password: (entered abc)
ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password:
YES)
Is it granting permission to abc. I refered documentation but could
not
get it clearly Neet sample code.
Then I connected mysql as 'root' user. and tried.
mysql mysql -u abc -p;
ERROR 1064: You have an error in your SQL syntax. Check the manual
that
corresonds to your MySQL server version for the right syntax to use
near
'mysql -u ab -p' at line 1.
Syntax I got from Documentation for GRANT as follows:
###
4.4.1 GRANT and REVOKE Syntax
GRANT priv_type [(column_list)] [, priv_type [(column_list)] ...]
ON {tbl_name | * | *.* | db_name.*}
TO user_name [IDENTIFIED BY [PASSWORD] 'password']
[, user_name [IDENTIFIED BY [PASSWORD] 'password'] ...]
###
Please guide me how to create users for mysql with password.
Thanks for support.
-
Do you Yahoo!?
New Yahoo! Photos - easier uploading and sharing
-
Do you Yahoo!?
New Yahoo! Photos - easier uploading and sharing
-
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
Re: foreign keys.
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]
Re: 'Sum' syntax
On Tuesday 16 December 2003 09:22, Noamn wrote: I'm using v3.23.51 on Linux. Can anyone explain why a simple 'sum' select won't work?? Here is the example: mysql select sum (id), pet from visits group by pet; ERROR 1064: You have an error in your SQL syntax near '(id), pet from visits group by pet' at line 1 Nuke the space after 'sum' and before (id). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: UTF-8 support
On 12/16/2003 01:35 AMnbsp;Ligaya Turmelle wrote: Can anyone tell me what the current support for the UTF8 character set is? How strong is it? Do I have to do anything special to save the characters? I will be getting the characters from a webpage form and inserting the characters with PHP. It depends if you are prepared to go with the 4.1.1 alpha version or not, which has complete UTF-8 support. Production version 4.0.x lacks some of the UTF-8 facilities but I believe many people are using it with work-arounds where necessary. Adam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Zeos, MySQL problem
Hi David, for Zeos questions check: http://www.zeoslib.net/modules.php?name=Forums regards Michael Daheim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to create mysql user?
Yes, It worked with folllowing :
I have created new user 'abc1' with password 'abc1' for database 'abc'.
###
##
[EMAIL PROTECTED] mysql]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 83 to server version: 4.0.16-standard
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql USE mysql
Database changed
mysql INSERT INTO user (Host,User,Password)
VALUES('localhost','abc1',PASSWORD('abc1'));
Query OK, 1 row affected (0.00 sec)
mysqlCREATE DATABASE abc;
mysql INSERT INTO db (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,
Create_priv,Drop_priv) VALUES ('localhost','abc','abc1','Y','Y','Y','Y','Y','Y');
Query OK, 1 row affected (0.00 sec)
mysql FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
mysql exit
Bye
[EMAIL PROTECTED] mysql]# mysql -u abc1 -p abc
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 84 to server version: 4.0.16-standard
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql
##
Thanks for great support.
ads mysql [EMAIL PROTECTED] wrote:
As advised I tried following :
###
[EMAIL PROTECTED] root]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 71 to server version: 4.0.16-standard
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql grant ALL on abc.* to 'abc'@'localhost' identified by 'abc';
Query OK, 0 rows affected (0.00 sec)
mysql exit
Bye
[EMAIL PROTECTED] root]# mysql -u abc -p abc
Enter password:
ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES)
No luck.
Help appreciated.
Duke, Brian
wrote:
try this:
grant ALL on abc.* to 'abc'@'localhost' identified by 'abc';
I have created database 'abc' .
mysql grant ALL on abc.table to 'abc' identified by 'abc';
Query OK, 0 rows affected (0.00 sec)
-Original Message-
From: ads mysql [mailto:[EMAIL PROTECTED]
Sent: Monday, December 15, 2003 11:21 PM
To: Nitin; [EMAIL PROTECTED]
Subject: Re: How to create mysql user?
O K.
I accessed to mysql as root user and tried to created user 'abc' as folows :
[EMAIL PROTECTED] mysql]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 61 to server version: 4.0.16-standard
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
I have created database 'abc' .
mysql grant ALL on abc.table to 'abc' identified by 'abc';
Query OK, 0 rows affected (0.00 sec)
I want to give all permission to user abc to handle database 'abc'.
Then I tried to connect as user 'abc' to mysql as follows :
[EMAIL PROTECTED] mysql]# mysql -u abc -p
Enter password:
ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES)
Some problem.
Help appreciated.
Nitin wrote:
right now, u r trying to connect t omyql with uer abc, not creating it.
to create user abc
grant
on dbname.tablename to 'abc' identified by 'abc';
you can view the list of privileges available on myql website at
http://www.mysql.com/doc/en/GRANT.html
Enjoy
Nitin
- Original Message -
From: ads mysql
To:
Sent: Tuesday, December 16, 2003 11:09 AM
Subject: How to create mysql user?
Hi,
I have installed mysql. As per documentation with user as 'root'.
I can create Database, table. I can enter data into table.
I have created user 'abc' with passowrd 'abc' on my Linux 8.0 server.
by using command :
adduser abc -p 'abc'
Now I want to create mysql database user 'abc' with command :
[EMAIL PROTECTED] mysql]# mysql -u abc -p
Enter password: (entered abc)
ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES)
Is it granting permission to abc. I refered documentation but could not
get it clearly Neet sample code.
Then I connected mysql as 'root' user. and tried.
mysql mysql -u abc -p;
ERROR 1064: You have an error in your SQL syntax. Check the manual that
corresonds to your MySQL server version for the right syntax to use near
'mysql -u ab -p' at line 1.
Syntax I got from Documentation for GRANT as follows:
###
4.4.1 GRANT and REVOKE Syntax
GRANT priv_type [(column_list)] [, priv_type [(column_list)] ...]
ON {tbl_name | * | *.* | db_name.*}
TO user_name [IDENTIFIED BY [PASSWORD] 'password']
[, user_name [IDENTIFIED BY [PASSWORD] 'password'] ...]
###
Please guide me how to create users for mysql with password.
Thanks for support.
-
Do you Yahoo!?
New Yahoo! Photos - easier uploading and sharing
-
Do you Yahoo!?
New Yahoo! Photos - easier uploading and sharing
-
Do you Yahoo!?
New Yahoo! Photos - easier uploading and sharing
-
Do you Yahoo!?
New Yahoo! Photos - easier
Re: How to create mysql user?
try restarting mysql service ..
Cheers
Binay
- Original Message -
From: ads mysql [EMAIL PROTECTED]
To: Duke, Brian [EMAIL PROTECTED]; Nitin
[EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Tuesday, December 16, 2003 2:07 PM
Subject: RE: How to create mysql user?
As advised I tried following :
###
[EMAIL PROTECTED] root]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 71 to server version: 4.0.16-standard
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql grant ALL on abc.* to 'abc'@'localhost' identified by 'abc';
Query OK, 0 rows affected (0.00 sec)
mysql exit
Bye
[EMAIL PROTECTED] root]# mysql -u abc -p abc
Enter password:
ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES)
No luck.
Help appreciated.
Duke, Brian [EMAIL PROTECTED] wrote:
try this:
grant ALL on abc.* to 'abc'@'localhost' identified by 'abc';
I have created database 'abc' .
mysql grant ALL on abc.table to 'abc' identified by 'abc';
Query OK, 0 rows affected (0.00 sec)
-Original Message-
From: ads mysql [mailto:[EMAIL PROTECTED]
Sent: Monday, December 15, 2003 11:21 PM
To: Nitin; [EMAIL PROTECTED]
Subject: Re: How to create mysql user?
O K.
I accessed to mysql as root user and tried to created user 'abc' as folows
:
[EMAIL PROTECTED] mysql]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 61 to server version: 4.0.16-standard
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
I have created database 'abc' .
mysql grant ALL on abc.table to 'abc' identified by 'abc';
Query OK, 0 rows affected (0.00 sec)
I want to give all permission to user abc to handle database 'abc'.
Then I tried to connect as user 'abc' to mysql as follows :
[EMAIL PROTECTED] mysql]# mysql -u abc -p
Enter password:
ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES)
Some problem.
Help appreciated.
Nitin wrote:
right now, u r trying to connect t omyql with uer abc, not creating it.
to create user abc
grant
on dbname.tablename to 'abc' identified by 'abc';
you can view the list of privileges available on myql website at
http://www.mysql.com/doc/en/GRANT.html
Enjoy
Nitin
- Original Message -
From: ads mysql
To:
Sent: Tuesday, December 16, 2003 11:09 AM
Subject: How to create mysql user?
Hi,
I have installed mysql. As per documentation with user as 'root'.
I can create Database, table. I can enter data into table.
I have created user 'abc' with passowrd 'abc' on my Linux 8.0 server.
by using command :
adduser abc -p 'abc'
Now I want to create mysql database user 'abc' with command :
[EMAIL PROTECTED] mysql]# mysql -u abc -p
Enter password: (entered abc)
ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password:
YES)
Is it granting permission to abc. I refered documentation but could not
get it clearly Neet sample code.
Then I connected mysql as 'root' user. and tried.
mysql mysql -u abc -p;
ERROR 1064: You have an error in your SQL syntax. Check the manual that
corresonds to your MySQL server version for the right syntax to use near
'mysql -u ab -p' at line 1.
Syntax I got from Documentation for GRANT as follows:
###
4.4.1 GRANT and REVOKE Syntax
GRANT priv_type [(column_list)] [, priv_type [(column_list)] ...]
ON {tbl_name | * | *.* | db_name.*}
TO user_name [IDENTIFIED BY [PASSWORD] 'password']
[, user_name [IDENTIFIED BY [PASSWORD] 'password'] ...]
###
Please guide me how to create users for mysql with password.
Thanks for support.
-
Do you Yahoo!?
New Yahoo! Photos - easier uploading and sharing
-
Do you Yahoo!?
New Yahoo! Photos - easier uploading and sharing
-
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: 'Sum' syntax
Noamn [EMAIL PROTECTED] wrote: I'm using v3.23.51 on Linux. Can anyone explain why a simple 'sum' select won't work?? Here is the example: mysql select sum (id), pet from visits group by pet; ERROR 1064: You have an error in your SQL syntax near '(id), pet from visits group by pet' at line 1 Remove space between 'sum' and bracket. -- 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]
Have you use emmbedded mysql in C++Builder?i met a problem,i hope you can help m
Hi!
I am trying to get access to the MySQL embedded server using the library
libmysql.dll through c++builder. The loading of the library goes well,
but when calls are made to start the server, the application crashes.when i
run the program ,the program crashed in mysql_server_end
my program as follows
#include vcl.h
#include basetsd.h
#include winsock.h
#include mysql.h
#include stdarg.h
#include stdio.h
#include stdlib.h
#pragma hdrstop
USERES(Project2.res);
USEFORM(Unit1.cpp, Form1);
USELIB(D:\program file\CBuilder5\Bin\libmysqd.lib);
//---
WINAPI WinMain(HINSTANCE, HINSTANCE, LPSTR, int)
{
try
{
Application-Initialize();
// Application-CreateForm(__classid(TForm1), Form1);
Application-Run();
int result;
result = mysql_server_init(0, NULL, NULL);
mysql_server_end();
}
catch (Exception exception)
{
Application-ShowException(exception);
}
return 0;
}
my.ini as follows:
#This File was made using the WinMySQLAdmin 1.4 Tool
#2003-12-5 14:42:02
#Uncomment or Add only the keys that you know how works.
#Read the MySQL Manual for instructions
[mysqld]
basedir=c:/mysql
#bind-address=10.34.44.120
datadir=c:/mysql/data
#language=c:/mysql/share/your language directory
#slow query log#=
#tmpdir#=
#port=3306
#set-variable=key_buffer=16M
[WinMySQLadmin]
Server=c:/mysql/bin/mysqld-nt.exe
user=root
password=root
[embedded]
basedir=e:/mysql
datadir=e:/mysql/data
i don't know what's wrong ?can you help me? i look foward to your ans
_
MSN Explorer: http://explorer.msn.com/lccn
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
help me!!!when i compile libmysqd of mysql src ,i always met a problem !
Creating library ../lib_debug/libmysqld.lib and object ../lib_debug/libmysqld.exp set_var.obj : error LNK2001: unresolved external symbol _resize_thr_alarm ../lib_debug/libmysqld.dll : fatal error LNK1120: 1 unresolved externals Error executing link.exe. but i have link mysys.lib ,how to solve it?thanks! vision: vc6+sp5 mysql4.0.16 _ MSN Messenger: http://messenger.msn.com/cn -- 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 ?
Hi! --cut To be honest, the vast majority of database installations experience problems in performance caused by poor query and schema design, bad application logic or grossly underspecified hardware. A change in the number of files used to store the data is extremely unlikely to resolve these problems given all other variables in the environment remain fixed. What does everyone else think? --cut (sigh) You're so right. Almost every day I find myself hammering on database developers that 'there should be an index' , 'that index should not be used', 'the join order is wrong', 'the app designer should be punished', 'don't use a function around an indexed column (unless you use Oracle and have function-based indexes)', 'know your data to be able to write the right queries and to be able to help the poor optimizer to do a good job', 'test concurrency, test scalability, test realistically' and so on ... But that's the funny thing: when everything is done and deployed and __slow__ then 'the database is so slow, do something (immediately!)'. Regards, Frank. -- Dr. Frank Ullrich, DBA Netzwerkadministration Heise Zeitschriften Verlag GmbH Co KG, Helstorfer Str. 7, D-30625 Hannover E-Mail: [EMAIL PROTECTED] Phone: +49 511 5352 587; FAX: +49 511 5352 538 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: 'Sum' syntax
Is there any reason that you need to sum the id? I'm assuming that you're trying to count. Ya? -Original Message- From: Noamn [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 16, 2003 5:22 PM To: [EMAIL PROTECTED] Subject: 'Sum' syntax I'm using v3.23.51 on Linux. Can anyone explain why a simple 'sum' select won't work?? Here is the example: mysql select sum (id), pet from visits group by pet; ERROR 1064: You have an error in your SQL syntax near '(id), pet from visits group by pet' at line 1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
help me!!!when i compile libmysqd of mysql src ,i always met a problem
Creating library ../lib_debug/libmysqld.lib and object ../lib_debug/libmysqld.exp set_var.obj : error LNK2001: unresolved external symbol _resize_thr_alarm ../lib_debug/libmysqld.dll : fatal error LNK1120: 1 unresolved externals Error executing link.exe. but i have link mysys.lib ,how to solve it?thanks! my environment:vc6+sp5 mysql4.0.16 _ MSN Hotmail http://www.hotmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
System load - 0.5 on Linux, 75 on Solaris!
I have a MySQL replication slave running 3.23.52 on a 2.4.18 linux box with 1.2GHz P3 and 512MB of memory. The box serves three web servers and runs at about 70 queries per second most of the time, with system load at about 0.5 MySQL is the precompiled RPM from mysql.com Recently I tried to replace this box with a Sun Netra T1 with a 500MHz Ultrasparc, 2GB of RAM running Solaris 8 Once again I used the precompiled binaries from mysql.com. The system load under this setup was 50-75!! I realise Solaris is likely to be slower than Linux, but loading of this level seems excessive. Can anybody suggest what might be the problem? Many thanks, Chris Allen. Linux my.cnf: [mysqld] server-id = 2 skip-locking set-variable= max_connections=400 set-variable= back_log=20 set-variable= key_buffer=150M set-variable= table_cache=256 set-variable= max_allowed_packet=1M set-variable= sort_buffer=1M set-variable= record_buffer=1M set-variable= myisam_sort_buffer_size=64M set-variable= thread_cache=16 set-variable= long_query_time=4 master-host = master-user = master-password = Solaris my.cnf: [mysqld] server-id = 4 skip-locking set-variable= net_buffer_length=8K set-variable= max_connections=400 set-variable= back_log=50 set-variable= key_buffer=640M set-variable= table_cache=128 set-variable= max_allowed_packet=1M set-variable= sort_buffer=2M set-variable= record_buffer=2M set-variable= myisam_sort_buffer_size=64M set-variable= thread_cache=24 set-variable= long_query_time=4 master-host = master-user = master-password = -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Performance problems using 1GB Linux server and trying to avoid swapping
Hello, I'm investigating a performance problem with mysql server set up. The server is running linux with 1GB ram. I'ld like to tune the configuration of the server to use as much RAM as possible without swapping to the disc because of the big slow down. The current configuration is as follows (I've just pasted the relevant pieces): [mysqld] skip-innodb skip-locking set-variable= key_buffer=16M set-variable= max_allowed_packet=200M set-variable= thread_stack=128K bind-address= 127.0.0.1 port= 3306 skip-networking set-variable = query_cache_type=1 set-variable = query_cache_size=64M set-variable = tmp_table_size=50M The main purpose of the database is a heavily modified forum application based on vBulletin. One of the biggest table is the 'post' table with consumes about 617M currently; this table is often used in queries for index pages which are the most frequent visited pages; indexes for the relevant column in the WHERE statement are all created. The next tables in size are using 22M and most of them much less. I'm also having problems in determining accurately how much memory MySQL uses when running on the system; the information provided by ps doesn't seem relieable due the many processes/threads. Getting the currently used memory and whether MySQL needed to swap would be very valueable in chasing down this issue. I'm also not sure whether the database is swapping temporary tables to the disc or not; is there a way to verify if this is happening? Other beneficial things would be to know how much memory certain queries need (mostly the queries on index pages). Besides more physical memory, are the other ways to better tune the server for the 1GB ram? What performance analyses tool are recommended for use with MySQL? thanks for any advice, - Markus -- 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 [EMAIL PROTECTED] wrote: Hi! What does 'SHOW CREATE DATABASE database_name;' show for this database? I've found it... 1. F:\Projects\has\sqlmysql -u root -e create database t; show create database t; drop database t +--+--+ | Database | Create Database | +--+--+ | t| CREATE DATABASE `t` /*!40100 DEFAULT CHARACTER SET cp1251 */ | +--+--+ 2. F:\Projects\has\sqlmysql -u root -e use_mysql; create database t; show create database t; drop database t +--+--+ | Database | Create Database | +--+--+ | t| CREATE DATABASE `t` /*!40100 DEFAULT CHARACTER SET latin1 */ | +--+--+ Where F:\Projects\has\sqlmysql -u root -e show create database mysql +--+ --+ | Database | Create Database | +--+ --+ | mysql| CREATE DATABASE `mysql` /*!40100 DEFAULT CHARACTER SET latin1 */ | +--+ --+ Seems, there is dependence on current database charset and, IMHO, it's a bug. It's not a bug. character_set_database indicates the current database character set. -- 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]
Re: Haw to get specific result?
I think over other problem.
In the table (shown below) there is another column defined as timestamp(6).
Do you think is it possible to get result grupped by month period: form
january to december ??
Best regards,
Paul
id | product | dat
---
0 | switch | 030702
1 | switch | 031214
2 | hub | 031215
3 | hub | 031217
4 | hub | 031218
- Original Message -
From: Jay Blanchard [EMAIL PROTECTED]
To: Pawel Filutowski [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Monday, December 15, 2003 1:53 PM
Subject: RE: Haw to get specific result?
[snip]
id | product
0 | switch
1 | switch
2 | hub
3 | hub
4 | hub
5 | wire
6 | wire
7 | wire
8 | wire
9 | wire
.
.
I`m looking for query which give me result as array:
0 - count of 'switch'
1 - count of 'hub'
2 - count of 'wire'
[/snip]
SELECT COUNT(product), product FROM table GROUP BY product;
Now, this doesn't give you an array, but you can use this query to
populate an array in the programming language that you are using, such
as PHP...
$sql = SELECT COUNT(`product`), `product` FROM `table` GROUP BY
`product` ;
if(!($result = mysql_query($sql, $connection_string))){
echo MySQL Error: . mysql_error() . \n;
exit();
}
$sqlArray = mysql_fetch_array($result); // returns a one row array, just
loop through to access all rows
--
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: reg C API from MySQL
Hello Patrick! I am trying to connect COBOL to the MySQL Database. I have used the C API to establish the conection to MySQL. You have quoted that library file mysqlclient.lib is enough. But when I try to test the Connecton using VC++ editor by writing a simpe C Program, the linking of libmysql.lib only produce successdul connection. i.e., mysqlclient.lib not required here. But When I try to Link those two files into the Linker for my COBOL application it wouldn't recognize the methods inside the Libraray file (i.e, mysql_init, mysql_real_connect, mysql_error...) and shows linker errors. Is there any other Library files required the platform I have used is Windows 2k CA REALIA II Workbench 3.0.55 CA REALIA COBOL Compiler 6.0.45 Microsoft 32 Bit incremental compiler 6.00.8168 can you please suggest me... Is there any other library files need to be inluded... regards, Arun. --- Patrick Sherrill [EMAIL PROTECTED] wrote: Statically linking using C API only you will need mysqlclient.lib For odbc interface you'll need to make odbc calls in your code and use myodbc.dll (Install myodbc on the client). You can mix calls, but why would you. I have found the C API to be the best solution for us. We statically link for dll avoidance. You can get the source or pre-built client libraries from the MySQL web site. I hope this helps. Pat... [EMAIL PROTECTED] CocoNet Corporation SW Florida's First ISP 825 SE 47th Terrace Cape Coral, FL 33904 - Original Message - From: P Arunachalam [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, December 11, 2003 4:31 AM Subject: reg C API from MySQL Yahoo! India Mobile: Download the latest polyphonic ringtones. Go to http://in.mobile.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: reg C API from MySQL
Hello Patrick! I am trying to connect COBOL to the MySQL Database. I have used the C API to establish the conection to MySQL. You have quoted that library file mysqlclient.lib is enough. But when I try to test the Connecton using VC++ editor by writing a simpe C Program, the linking of libmysql.lib only produce successdul connection. i.e., mysqlclient.lib not required here. But When I try to Link those two files into the Linker for my COBOL application it wouldn't recognize the methods inside the Libraray file (i.e, mysql_init, mysql_real_connect, mysql_error...) and shows linker errors. Is there any other Library files required the platform I have used is Windows 2k CA REALIA II Workbench 3.0.55 CA REALIA COBOL Compiler 6.0.45 Microsoft 32 Bit incremental compiler 6.00.8168 can you please suggest me... Is there any other library files need to be inluded... regards, Arun. --- Patrick Sherrill [EMAIL PROTECTED] wrote: Statically linking using C API only you will need mysqlclient.lib For odbc interface you'll need to make odbc calls in your code and use myodbc.dll (Install myodbc on the client). You can mix calls, but why would you. I have found the C API to be the best solution for us. We statically link for dll avoidance. You can get the source or pre-built client libraries from the MySQL web site. I hope this helps. Pat... [EMAIL PROTECTED] CocoNet Corporation SW Florida's First ISP 825 SE 47th Terrace Cape Coral, FL 33904 - Original Message - From: P Arunachalam [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, December 11, 2003 4:31 AM Subject: reg C API from MySQL Yahoo! India Mobile: Download the latest polyphonic ringtones. Go to http://in.mobile.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Haw to get specific result?
I tried to insert this query:
SELECT COUNT(DATE_FORMAT(dat,'%M')), DATE_FORMAT(dat,'%M') FROM table GROUP
BY DATE_FORMAT(dat,'%M') order by dat
but I`m not sure of proper result.
Regards,
Pawel
- Original Message -
From: Pawel Filutowski [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Tuesday, December 16, 2003 11:55 AM
Subject: Re: Haw to get specific result?
I think over other problem.
In the table (shown below) there is another column defined as
timestamp(6).
Do you think is it possible to get result grupped by month period: form
january to december ??
Best regards,
Paul
id | product | dat
---
0 | switch | 030702
1 | switch | 031214
2 | hub | 031215
3 | hub | 031217
4 | hub | 031218
- Original Message -
From: Jay Blanchard [EMAIL PROTECTED]
To: Pawel Filutowski [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Monday, December 15, 2003 1:53 PM
Subject: RE: Haw to get specific result?
[snip]
id | product
0 | switch
1 | switch
2 | hub
3 | hub
4 | hub
5 | wire
6 | wire
7 | wire
8 | wire
9 | wire
.
.
I`m looking for query which give me result as array:
0 - count of 'switch'
1 - count of 'hub'
2 - count of 'wire'
[/snip]
SELECT COUNT(product), product FROM table GROUP BY product;
Now, this doesn't give you an array, but you can use this query to
populate an array in the programming language that you are using, such
as PHP...
$sql = SELECT COUNT(`product`), `product` FROM `table` GROUP BY
`product` ;
if(!($result = mysql_query($sql, $connection_string))){
echo MySQL Error: . mysql_error() . \n;
exit();
}
$sqlArray = mysql_fetch_array($result); // returns a one row array, just
loop through to access all rows
--
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: System load - 0.5 on Linux, 75 on Solaris!
There are a few things that could be happening: 1. Solaris 8 uses many-to-many threads in Solaris threading mode and 1:1 in POSIX threading mode. I'm not sure which functionality the MySQL binaries exercise, but 1:1 seems to be less intensive. Regardless, Solaris thread creation tends to be slower than process creation on Linux (on the same hardware) and that is slower than thread creation on Linux (for the most part). Are you using persistant connections from your web servers, as each new connection will spawn a thread (for now - There are apparently plans to offer an alternative helper-thread connection and query handler model). 2. Which FS are you using? The native Solaris FS needs a bit of tuning to get decent DB performance apparently. 3. What sort of disk subsystem does the new box have? As the load is so high, it seems that either MySQL is waiting on disk I/O or network I/O 4. What table type(s) are you using on each box? Please write back! Let's fix this! :-) Regards, Chris Chris Allen wrote: I have a MySQL replication slave running 3.23.52 on a 2.4.18 linux box with 1.2GHz P3 and 512MB of memory. The box serves three web servers and runs at about 70 queries per second most of the time, with system load at about 0.5 MySQL is the precompiled RPM from mysql.com Recently I tried to replace this box with a Sun Netra T1 with a 500MHz Ultrasparc, 2GB of RAM running Solaris 8 Once again I used the precompiled binaries from mysql.com. The system load under this setup was 50-75!! I realise Solaris is likely to be slower than Linux, but loading of this level seems excessive. Can anybody suggest what might be the problem? Many thanks, Chris Allen. Linux my.cnf: [mysqld] server-id = 2 skip-locking set-variable= max_connections=400 set-variable= back_log=20 set-variable= key_buffer=150M set-variable= table_cache=256 set-variable= max_allowed_packet=1M set-variable= sort_buffer=1M set-variable= record_buffer=1M set-variable= myisam_sort_buffer_size=64M set-variable= thread_cache=16 set-variable= long_query_time=4 master-host = master-user = master-password = Solaris my.cnf: [mysqld] server-id = 4 skip-locking set-variable= net_buffer_length=8K set-variable= max_connections=400 set-variable= back_log=50 set-variable= key_buffer=640M set-variable= table_cache=128 set-variable= max_allowed_packet=1M set-variable= sort_buffer=2M set-variable= record_buffer=2M set-variable= myisam_sort_buffer_size=64M set-variable= thread_cache=24 set-variable= long_query_time=4 master-host = master-user = master-password = -- 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 ?
Dr. Frank Ullrich wrote: Hi! --cut To be honest, the vast majority of database installations experience problems in performance caused by poor query and schema design, bad application logic or grossly underspecified hardware. A change in the number of files used to store the data is extremely unlikely to resolve these problems given all other variables in the environment remain fixed. What does everyone else think? --cut (sigh) You're so right. Almost every day I find myself hammering on database developers that 'there should be an index' , 'that index should not be used', 'the join order is wrong', 'the app designer should be punished', 'don't use a function around an indexed column (unless you use Oracle and have function-based indexes)', 'know your data to be able to write the right queries and to be able to help the poor optimizer to do a good job', 'test concurrency, test scalability, test realistically' and so on ... But that's the funny thing: when everything is done and deployed and __slow__ then 'the database is so slow, do something (immediately!)'. Regards, Frank. The part about helping out the optimizer rings especially true for me. One one box I take care of: SELECT message FROM syslog WHERE date1 '2003-12-01' AND message LIKE 'sshd%username'; Execution time: 20.85 seconds SELECT message FROM syslog FORCE INDEX(date1, message) WHERE date1 '2003-12-01' AND message LIKE 'sshd%username'; Execution time: 0.25 seconds On that topic, how have all the veteran MySQL users found the optimizer compared to the other offerings out there? I remember various interviews with Monty asking questions along the lines of What's the most interesting thing to do in DB development and similar have almost always been answered with Getting an optimizer to actually optimize queries really well. Regards, Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Performance problems using 1GB Linux server and trying to avoid swapping
Hi! How heavy is your usage of TEMPORARY TABLES? I don't use them much myself, but I'm sure that the others on the list will have something to say in that regard. To get a better look at MySQL's usage of memory, you could try looking at the output of SHOW STATUS . Regards, Chris Markus Fischer wrote: Hello, I'm investigating a performance problem with mysql server set up. The server is running linux with 1GB ram. I'ld like to tune the configuration of the server to use as much RAM as possible without swapping to the disc because of the big slow down. The current configuration is as follows (I've just pasted the relevant pieces): [mysqld] skip-innodb skip-locking set-variable= key_buffer=16M set-variable= max_allowed_packet=200M set-variable= thread_stack=128K bind-address= 127.0.0.1 port= 3306 skip-networking set-variable = query_cache_type=1 set-variable = query_cache_size=64M set-variable = tmp_table_size=50M The main purpose of the database is a heavily modified forum application based on vBulletin. One of the biggest table is the 'post' table with consumes about 617M currently; this table is often used in queries for index pages which are the most frequent visited pages; indexes for the relevant column in the WHERE statement are all created. The next tables in size are using 22M and most of them much less. I'm also having problems in determining accurately how much memory MySQL uses when running on the system; the information provided by ps doesn't seem relieable due the many processes/threads. Getting the currently used memory and whether MySQL needed to swap would be very valueable in chasing down this issue. I'm also not sure whether the database is swapping temporary tables to the disc or not; is there a way to verify if this is happening? Other beneficial things would be to know how much memory certain queries need (mostly the queries on index pages). Besides more physical memory, are the other ways to better tune the server for the 1GB ram? What performance analyses tool are recommended for use with MySQL? thanks for any advice, - Markus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Bug in mysql.h header file
I was trying to establish connection to MySQL through
a simple C program using it's C API mysqL_init(),
mysql_reak_connect(), ... in VC++ Editor. It shows
Errors ;
Compiling...
MYSQL.C
c:\mysql\include\mysql_com.h(116) : error C2061:
syntax error : identifier 'SOCKET'
c:\mysql\include\mysql_com.h(135) : error C2059:
syntax error : '}'
c:\mysql\include\mysql_com.h(167) : error C2143:
syntax error : missing ')' before '*'
c:\mysql\include\mysql_com.h(167) : error C2143:
syntax error : missing '{' before '*'
c:\mysql\include\mysql_com.h(167) : error C2371: 'Vio'
: redefinition; different basic types
c:\mysql\include\mysql_com.h(108) : see
declaration of 'Vio'
c:\mysql\include\mysql_com.h(167) : error C2143:
syntax error : missing ';' before '*'
c:\mysql\include\mysql_com.h(167) : error C2059:
syntax error : ')'
c:\mysql\include\mysql_com.h(168) : error C2143:
syntax error : missing ')' before '*'
c:\mysql\include\mysql_com.h(168) : error C2143:
syntax error : missing '{' before '*'
c:\mysql\include\mysql_com.h(168) : error C2059:
syntax error : ')'
c:\mysql\include\mysql_com.h(169) : error C2143:
syntax error : missing ')' before '*'
c:\mysql\include\mysql_com.h(169) : error C2143:
syntax error : missing '{' before '*'
c:\mysql\include\mysql_com.h(169) : error C2059:
syntax error : ')'
c:\mysql\include\mysql_com.h(170) : error C2143:
syntax error : missing ')' before '*'
c:\mysql\include\mysql_com.h(170) : error C2143:
syntax error : missing '{' before '*'
c:\mysql\include\mysql_com.h(170) : error C2059:
syntax error : ')'
c:\mysql\include\mysql_com.h(171) : error C2143:
syntax error : missing ')' before '*'
c:\mysql\include\mysql_com.h(171) : error C2143:
syntax error : missing '{' before '*'
c:\mysql\include\mysql_com.h(171) : error C2059:
syntax error : ')'
c:\mysql\include\mysql_com.h(172) : error C2143:
syntax error : missing ')' before '*'
c:\mysql\include\mysql_com.h(172) : error C2143:
syntax error : missing '{' before '*'
c:\mysql\include\mysql_com.h(172) : error C2059:
syntax error : 'type'
c:\mysql\include\mysql_com.h(172) : error C2059:
syntax error : ')'
c:\mysql\include\mysql_com.h(173) : error C2143:
syntax error : missing ')' before '*'
c:\mysql\include\mysql_com.h(173) : error C2143:
syntax error : missing '{' before '*'
c:\mysql\include\mysql_com.h(173) : error C2059:
syntax error : 'type'
c:\mysql\include\mysql_com.h(174) : error C2059:
syntax error : ')'
c:\mysql\include\mysql_com.h(175) : error C2143:
syntax error : missing ')' before '*'
c:\mysql\include\mysql_com.h(175) : error C2143:
syntax error : missing '{' before '*'
c:\mysql\include\mysql_com.h(175) : error C2059:
syntax error : 'type'
c:\mysql\include\mysql_com.h(175) : error C2059:
syntax error : ')'
c:\mysql\include\mysql_com.h(176) : error C2143:
syntax error : missing ')' before '*'
c:\mysql\include\mysql_com.h(176) : error C2143:
syntax error : missing '{' before '*'
c:\mysql\include\mysql_com.h(176) : error C2059:
syntax error : ')'
c:\mysql\include\mysql_com.h(180) : error C2146:
syntax error : missing ')' before identifier 's'
c:\mysql\include\mysql_com.h(180) : error C2061:
syntax error : identifier 's'
c:\mysql\include\mysql_com.h(180) : error C2059:
syntax error : ';'
c:\mysql\include\mysql_com.h(180) : error C2059:
syntax error : ','
c:\mysql\include\mysql_com.h(181) : error C2059:
syntax error : ')'
c:\mysql\include\mysql.h(165) : error C2061: syntax
error : identifier 'NET'
c:\mysql\include\mysql.h(202) : error C2059: syntax
error : '}'
c:\mysql\include\mysql.h(211) : error C2061: syntax
error : identifier 'MYSQL'
c:\mysql\include\mysql.h(217) : error C2059: syntax
error : '}'
c:\mysql\include\mysql.h(232) : error C2061: syntax
error : identifier 'NET'
c:\mysql\include\mysql.h(238) : warning C4142: benign
redefinition of type
c:\mysql\include\mysql.h(241) : error C2369:
'last_error' : redefinition; different subscripts
c:\mysql\include\mysql_com.h(121) : see
declaration of 'last_error'
c:\mysql\include\mysql.h(242) : error C2059: syntax
error : '}'
c:\mysql\include\mysql.h(266) : error C2143: syntax
error : missing ')' before '*'
c:\mysql\include\mysql.h(266) : error C2143: syntax
error : missing '{' before '*'
c:\mysql\include\mysql.h(266) : error C2059: syntax
error : ')'
c:\mysql\include\mysql.h(267) : error C2143: syntax
error : missing ')' before '*'
c:\mysql\include\mysql.h(267) : error C2143: syntax
error : missing '{' before '*'
c:\mysql\include\mysql.h(267) : error C2059: syntax
error : ')'
c:\mysql\include\mysql.h(268) : error C2143: syntax
error : missing ')' before '*'
c:\mysql\include\mysql.h(268) : error C2143: syntax
error : missing '{' before '*'
c:\mysql\include\mysql.h(268) : error C2059: syntax
error : ')'
c:\mysql\include\mysql.h(269) : error C2143: syntax
error : missing ')' before '*'
c:\mysql\include\mysql.h(269) : error C2143: syntax
error : missing '{' before '*'
c:\mysql\include\mysql.h(270) : error C2059: syntax
error : 'type'
c:\mysql\include\mysql.h(270) : error C2059:
Re: Using MySQL in LGPL library
On Tue, 2003-12-16 at 02:09, Jeremy Zawodny wrote: On Sun, Dec 14, 2003 at 10:50:43PM +0100, Rodrigo Moya wrote: Hi We have been supporting MySQL in the GNOME-DB project (http://www.gnome-db.org) since the beginning almost (1998). GNOME-DB provides several liraries, and one of those is a plugin-based generic data access library (libgda) which allows access to several DBMS, by dlopen'ing the plugins. Each of those plugins provide access to a specific RDBMS. The library (libgda) is LGPL, so it seems we are not able to have it link against the GPL version of MySQL. So, we have been thinking about what to do, and so far we only see 3 possibilities: * force libgda to link against the LGPL MySQL libraries, but I suppose there won't be any 4.x LGPL libraries, right? * have the MySQL plugin communicate with the library via local sockets/message queues/whatever so that we don't link to it directly. * completely remove support for MySQL, which, for obvious reasons, we'd prefer to avoid. So far, we have disabled MySQL access in the CVS version until we know what to do. I *thought* that MySQL AB was coming up with some sort of exception for GPL'd free software but I don't remember if that got resolved. oh, that's nice for them Perhaps more pressure is needed? yes, I guess if we all have to disable mysql support in our projects, that's going to be some kind of pressure. As I said, we don't want to do that, but if there's no solution, we'll be forced to. Do you know any other projects in the same situation? I'll check with a few folks and see if I can turn up something... ok, thanks. Please keep us informed. cheers -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Comparing date fields
I'm in the process of converting an application from Paradox to mySQL which is why I have these syntax questions. mySQL doesn't like a query with this syntax: select p.name, from projects p, ... where p.closeddate = :fd and p.closeddate = :td ... where fd (from date) and td (till date) are parameters. Can one use greater than/less than operators, or is there some other way of filtering only those records with closeddates in the range that I specify? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
php installation with mysql support.
Hi, I am trying to install and configure php with mysql support. I have installed mysql and it works perfectly on the server. On the same server I tried to install php with following configuration : [EMAIL PROTECTED] php-4.3.4]# ./configure --with-apxs2=/usr/local/apache2/bin/apxs --with- mysql=/var/lib/mysql It gives error configure: error: Cannot find MySQL header files under /var/lib/mysql what should I put in --with-mysql=/var/lib/mysql Help appreciated. - Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing
Re: php installation with mysql support.
On Tuesday 16 December 2003 13:43, ads mysql wrote: php-4.3.4]# ./configure --with-apxs2=/usr/local/apache2/bin/apxs --with- mysql=/var/lib/mysql It gives error configure: error: Cannot find MySQL header files under /var/lib/mysql what should I put in --with-mysql=/var/lib/mysql On the assumption this is an RPM install of MySQL, I'd suggest trying --with-mysql=/usr Btw, it's a generally accepted thing that PHP + Apache2 don't always play nice. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Comparing date fields
[snip] I'm in the process of converting an application from Paradox to mySQL which is why I have these syntax questions. mySQL doesn't like a query with this syntax: select p.name, from projects p, ... where p.closeddate = :fd and p.closeddate = :td ... where fd (from date) and td (till date) are parameters. Can one use greater than/less than operators, or is there some other way of filtering only those records with closeddates in the range that I specify? [/snip] You can use BETWEEN (which is inclusive of the dates specified) select p.name, from projects p, ... where p.closeddate BETWEEN date AND date -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Comparing date fields
I started with between, but when this didn't work, I switched to the comparison operators. mysql select projects.name from projects - where projects.closeddate between :fd and :td; ERROR 1064: You have an error in your SQL syntax near ':fd and :td' at line 2 mysql -Original Message- From: Jay Blanchard [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 16, 2003 4:04 PM To: Noamn; [EMAIL PROTECTED] Subject: RE: Comparing date fields [snip] I'm in the process of converting an application from Paradox to mySQL which is why I have these syntax questions. mySQL doesn't like a query with this syntax: select p.name, from projects p, ... where p.closeddate = :fd and p.closeddate = :td ... where fd (from date) and td (till date) are parameters. Can one use greater than/less than operators, or is there some other way of filtering only those records with closeddates in the range that I specify? [/snip] You can use BETWEEN (which is inclusive of the dates specified) select p.name, from projects p, ... where p.closeddate BETWEEN date AND date -- 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: Comparing date fields
[snip] I started with between, but when this didn't work, I switched to the comparison operators. mysql select projects.name from projects - where projects.closeddate between :fd and :td; ERROR 1064: You have an error in your SQL syntax near ':fd and :td' at line 2 [/snip] What are those colons? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Bug in mysql.h header file
This is not a bug, you need to set your project/programming environment in VC++ to include support for sockets. I haven't written any non socket programs in several years, so it's part of my standard set-up. MySQL is client/server and uses sockets. You do need to link with a socket library. In the case of VC++ this is Winsock (wsock32.lib). I normally include support for sockets in my stdfx.h (#include afxsock.h // MFC socket extensions). This is included with your VC++ installation and not part of mysql source. Then, add wsock32.lib, mysqlclient.lib and any other external library you need to link with in your 'Project Settings'(Alt-F7'). These are added under the 'Link' tab in the 'Input' category. Libraries are added in the 'Object/library module' field separated by spaces (e.g. 'wsock32.lib mysqlclient.lib'). Don't forget to add the path to the mysqlclient library in the 'Additional library path:' field. Pat... - Original Message - From: P Arunachalam [EMAIL PROTECTED] To: Patrick Sherrill [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, December 16, 2003 6:36 AM Subject: Bug in mysql.h header file I was trying to establish connection to MySQL through a simple C program using it's C API mysqL_init(), mysql_reak_connect(), ... in VC++ Editor. It shows Errors ; Compiling... MYSQL.C c:\mysql\include\mysql_com.h(116) : error C2061: syntax error : identifier 'SOCKET' MYSQL.OBJ - 102 error(s), 1 warning(s) I have corrected this errors by including winsock.h header file into my program. After including winsock.h my program work fine and produce my expected results. Herewith I have included the Sample C Program too for your reference... Incase it is a bug you please produce the correct version of mysql.h header file to me. Thanks. regards, Arun. --- Patrick Sherrill [EMAIL PROTECTED] wrote: Statically linking using C API only you will need mysqlclient.lib For odbc interface you'll need to make odbc calls in your code and use myodbc.dll (Install myodbc on the client). You can mix calls, but why would you. I have found the C API to be the best solution for us. We statically link for dll avoidance. You can get the source or pre-built client libraries from the MySQL web site. I hope this helps. Pat... [EMAIL PROTECTED] CocoNet Corporation SW Florida's First ISP 825 SE 47th Terrace Cape Coral, FL 33904 - Original Message - From: P Arunachalam [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, December 11, 2003 4:31 AM Subject: reg C API from MySQL Yahoo! India Mobile: Download the latest polyphonic ringtones. Go to http://in.mobile.yahoo.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]
Adding native functions question
How do you get the string value of an argument to a function? For integer, there's val_int(), for real, there's val(). I tried using val_str(), but that didn't work. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re[2]: Comparing date fields
mysql select projects.name from projects
JB - where projects.closeddate between :fd and :td;
JB ERROR 1064: You have an error in your SQL syntax near ':fd and :td' at
JB line
JB 2
JB What are those colons?
He probably uses Delphi or C++Builder to connect to mysql via TQuery
component. TQuery has a property SQL where sql statement is stored.
The colon ':' means that fd is a parameter whose value should be
inserted into TQuery.SQL statement just before executing the query.
The solution is to substitute :fd with a real value directly by accessing TQuery.SQL
property.
Query.SQL.Clear;
Query.SQL.Add('select ...');
Query.SQL.Add('where projects.closeddate between 01/01/01 and 01/01/02');
Query.Open;
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Re[2]: Comparing date fields
mysql select projects.name from projects
JB - where projects.closeddate between :fd and :td;
JB ERROR 1064: You have an error in your SQL syntax near ':fd and :td' at
JB line
JB 2
JB What are those colons?
He probably uses Delphi or C++Builder to connect to mysql via TQuery
component. TQuery has a property SQL where sql statement is stored.
The colon ':' means that fd is a parameter whose value should be
inserted into TQuery.SQL statement just before executing the query.
The solution is to substitute :fd with a real value directly by accessing
TQuery.SQL
property.
Query.SQL.Clear;
Query.SQL.Add('select ...');
Query.SQL.Add('where projects.closeddate between 01/01/01 and 01/01/02');
Query.Open;
I very much doubt this works:
01 divided by 01 :-)
I guess you wanted to write:
'01/01/01'
With regards,
Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL
Server.
Upscene Productions
http://www.upscene.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Re[2]: Comparing date fields
I assume that I will have to write 01/01/02
Thanks for the help. Let's hope that there aren't any more questions of this
ilk and I can continue to port my application.
No'am
-Original Message-
From: Martijn Tonies [mailto:[EMAIL PROTECTED]
Sent: Tuesday, December 16, 2003 4:47 PM
To: [EMAIL PROTECTED]
Subject: Re: Re[2]: Comparing date fields
mysql select projects.name from projects
JB - where projects.closeddate between :fd and :td;
JB ERROR 1064: You have an error in your SQL syntax near ':fd and :td' at
JB line
JB 2
JB What are those colons?
He probably uses Delphi or C++Builder to connect to mysql via TQuery
component. TQuery has a property SQL where sql statement is stored.
The colon ':' means that fd is a parameter whose value should be
inserted into TQuery.SQL statement just before executing the query.
The solution is to substitute :fd with a real value directly by accessing
TQuery.SQL
property.
Query.SQL.Clear;
Query.SQL.Add('select ...');
Query.SQL.Add('where projects.closeddate between 01/01/01 and 01/01/02');
Query.Open;
I very much doubt this works:
01 divided by 01 :-)
I guess you wanted to write:
'01/01/01'
With regards,
Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL
Server.
Upscene Productions
http://www.upscene.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: Bug in mysql.h header file
Hi, I have made all the changes suggested by you in VC++ 6.0 i.e., I have inluded the Library file wsock32.lib into the existing library list in 'Project Settings'(Alt-F7') and checked the path setting of mysql header file and library file in Tools -- Options menu's Directory tab. but still it need the inclusion of header file winsock.H into my C program... otherise it shows the list of errors. name of my C program file is 'mysql.c' Error executing cl.exe. MYSQL.OBJ - 102 error(s), 1 warning(s) So pls verify and give me details... arun. --- info [EMAIL PROTECTED] wrote: This is not a bug, you need to set your project/programming environment in VC++ to include support for sockets. I haven't written any non socket programs in several years, so it's part of my standard set-up. MySQL is client/server and uses sockets. You do need to link with a socket library. In the case of VC++ this is Winsock (wsock32.lib). I normally include support for sockets in my stdfx.h (#include afxsock.h // MFC socket extensions). This is included with your VC++ installation and not part of mysql source. Then, add wsock32.lib, mysqlclient.lib and any other external library you need to link with in your 'Project Settings'(Alt-F7'). These are added under the 'Link' tab in the 'Input' category. Libraries are added in the 'Object/library module' field separated by spaces (e.g. 'wsock32.lib mysqlclient.lib'). Don't forget to add the path to the mysqlclient library in the 'Additional library path:' field. Pat... - Original Message - From: P Arunachalam [EMAIL PROTECTED] To: Patrick Sherrill [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, December 16, 2003 6:36 AM Subject: Bug in mysql.h header file I was trying to establish connection to MySQL through a simple C program using it's C API mysqL_init(), mysql_reak_connect(), ... in VC++ Editor. It shows Errors ; Compiling... MYSQL.C c:\mysql\include\mysql_com.h(116) : error C2061: syntax error : identifier 'SOCKET' MYSQL.OBJ - 102 error(s), 1 warning(s) I have corrected this errors by including winsock.h header file into my program. After including winsock.h my program work fine and produce my expected results. Herewith I have included the Sample C Program too for your reference... Incase it is a bug you please produce the correct version of mysql.h header file to me. Thanks. regards, Arun. --- Patrick Sherrill [EMAIL PROTECTED] wrote: Statically linking using C API only you will need mysqlclient.lib For odbc interface you'll need to make odbc calls in your code and use myodbc.dll (Install myodbc on the client). You can mix calls, but why would you. I have found the C API to be the best solution for us. We statically link for dll avoidance. You can get the source or pre-built client libraries from the MySQL web site. I hope this helps. Pat... [EMAIL PROTECTED] CocoNet Corporation SW Florida's First ISP 825 SE 47th Terrace Cape Coral, FL 33904 - Original Message - From: P Arunachalam [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, December 11, 2003 4:31 AM Subject: reg C API from MySQL Yahoo! India Mobile: Download the latest polyphonic ringtones. Go to http://in.mobile.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Yahoo! India Mobile: Download the latest polyphonic ringtones. Go to http://in.mobile.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Speed difference between boolean full-text searches and full-text searches
OK I tried this, '+music +mix +2001' instead of this 'music mix 2001'
IN BOOLEAN MODE and the SQL time is the same ~21 sec.
select artists.name , cds.title, tracks.title from artists, tracks, cds
where
MATCH (artists.name) AGAINST ('madonna' IN BOOLEAN MODE)
and
MATCH (cds.title) AGAINST ('+music +mix +2001' IN BOOLEAN MODE) and
artists.artistid = cds.artistid AND artists.artistid = tracks.artistid
AND cds.cdid = tracks.cdid
Do you have some explanation, why is this so much slower than this :
SELECT artists.name, cds.title, tracks.title
FROM artists, cds, tracks
WHERE artists.artistid = cds.artistid AND artists.artistid =
tracks.artistid AND cds.cdid = tracks.cdid AND
MATCH ( artists.name ) AGAINST ( 'madonna' ) AND
MATCH ( cds.title ) AGAINST ( 'music' ) AND
MATCH ( cds.title ) AGAINST ( 'mix' ) AND
MATCH ( cds.title ) AGAINST ( '2001' )
Regards
-Original Message-
From: Chuck Gadd [mailto:[EMAIL PROTECTED]
Sent: Monday, December 08, 2003 21:50
To: Uros Kotnik; [EMAIL PROTECTED]
Subject: Re: Speed difference between boolean full-text searches and
full-text searches
Uros Kotnik wrote:
It makes sense, but Sergei G. said :
And are you sure the numbers are correct, the first query - the one
without IN BOOLEAN MODE - is faster ? I would expect the opposite.
I guess that for my DB I can't expect satisfied in boolena mode
times
?
But also when searching without in boolean mode and include search
criteria from TRACKS table, 13,841,930 rows , like AND MATCH (
tracks.title) AGAINST ('remix')
I get ~10 sec. times.
Am I doing something wrong or this results are correct for this amount
of data, I would be satisfied with 0.5 - 1 sec. times
If I'm not mistaken, IN BOOLEAN MODE simply changes the parser
logic. It tells MySql to process the special characters, like
+-*. I don't think it's the IN BOOLEAN MODE that is causing
the slow query, but the fact that you are looking for the phrase.
If you were to do
SELECT artists.name, cds.title, tracks.title FROM artists, cds, tracks
WHERE artists.artistid = cds.artistid AND artists.artistid =
tracks.artistid AND cds.cdid = tracks.cdid AND MATCH (artists.name)
AGAINST ('madonna' IN BOOLEAN MODE) AND
MATCH (cds.title) AGAINST ('+music +mix +2001'IN BOOLEAN MODE)
Then you'd probably still get the fast search time, since the query
simply requires all three words. MySql can resolve this just using
the index.
In your example, the BOOLEAN MODE for
MATCH (artists.name) AGAINST ('madonna' IN BOOLEAN MODE)
isn't doing anything special, since you aren't using any
special chars to modify the search expression.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: php installation with mysql support.
I tried with option ./configure --with-apxs2=/usr/local/apache2/bin/apxs --with-mysql as suggested that php might find mysql libraries on it's own. It has not given any error. Let me check whether I can connect to mysql using php. Thanks for support. Duncan Hill [EMAIL PROTECTED] wrote: On Tuesday 16 December 2003 13:43, ads mysql wrote: php-4.3.4]# ./configure --with-apxs2=/usr/local/apache2/bin/apxs --with- mysql=/var/lib/mysql It gives error configure: error: Cannot find MySQL header files under /var/lib/mysql what should I put in --with-mysql=/var/lib/mysql On the assumption this is an RPM install of MySQL, I'd suggest trying --with-mysql=/usr Btw, it's a generally accepted thing that PHP + Apache2 don't always play nice. -- 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
Replication slave gets fatal error 1236 on an almost daily basis.
I have a simple MySQL replication system setup with a single master and a single slave. The slave is running 4.0.16-standard and the master is running 4.0.16-standard-log. On an (almost) daily basis, I get this error on my slave: 031216 9:55:44 Error reading packet from server: log event entry exceeded max_allowed_packet; Increase max_allowed_packet on master (server_errno=1236) 031216 9:55:44 Got fatal error 1236: 'log event entry exceeded max_allowed_packet; Increase max_allowed_packet on master' from master when reading data from binary log 031216 9:55:44 Slave I/O thread exiting, read up to log 'el-bin.001', position 589631188 Note, this does not happen at the same time during the day and it does not happen every day. I know this is not a problem with max_allowed_packet because I have set both the master and slave to a max_allowed_packet that exceeds the size of the binary log file! Any help would be greatly appreciated. At this point replication is unreliable enough that it doesn't do us any good. -- Andy DePue Software Developer Software Services, Inc. (864) 271-8053 x104 -- 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?
At 01:14 AM 12/16/2003, you wrote: mos wrote: At 04:22 AM 12/15/2003, you wrote: To elaborate on Dr Frank's thing if you're interested, here's a classic deadlock example: 1. Transaction A obtains an exclusive lock on a set of rows which we will call R1. 2. Transaction B obtains an exclusive lock on another set of rows which we will call R2. 3. Transaction A requests (but obviously doesn't acquire) an exclusive lock on R2. 4. Transaction B requests (but obviously doesn't acquire) an exclusive lock on R1. 5. Classic deadlock! There are various ways of dealing with this (with timeouts and graph traversal seemingly popular). As you can see, neither transaction can go forward. Thus, the almighty InnoDB will think for a moment, decide which transaction it has something personal against, laugh at it and force it to ROLLBACK. That transaction can then try again if it doesn't feel totally small and humiliated. Hope this helps! Regards, Chris Chris, When my MySQL database gets into a deadlock situation like that, I just shutdown the server, power off the machine and go home. Works every time.vbg Mike (Sorry, it's been a late night) No no no!!! You've got it all wrong! You go and find the user who had the least involvement in causing the deadlock and scapegoat them! Either that, or you use it as an excuse for doing one of the following things: 1. Getting the purchase of more hardware approved. 2. Getting the purchase of MySQL Pro and InnoDB Hot Backup approved 3. Getting a MySQL support contract purchase approved 4. Getting a raise for resolving the situation with poise, grace and subtlety (Eg: Shutting it down, blaming users, blaming SCO...) Regards, Chris Chris, Hot Dang you're good! :-) I didn't see this in any of this in Paul Dubois's books. Maybe it could be added in a future version??? Call the section The Blame Game and put it into the Appendix called MySQL Survivor Game..Be the last one to be voted off the island.. bg Mike -- 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 ?
Jeremy Zawodny wrote: On Tue, Dec 16, 2003 at 12:01:55PM +0700, David Garamond wrote: Sven K?hler wrote: I was very disappointed by Interbase/Firebird. It seemed to me like a MS-Access: a database-engine that works on regular files Firebird seems simple, but it doesn't mean it's inferior or [intentionally] crippled like MS-Access. SQL server also works on regular files (db is stored as single files) and I believe FB is comparable to SQL server. In fact, I personally hate the fact that InnoDB can't work on regular files (db is not stored on single files or single directories). That's already fixed in 4.1.1. Looking at the facts, the number of files used to store your data is of very little consequence at the end of the day. Looking at the following: * MS SQL Server stores database data in a single file and logs in another. * Oracle and FoxPro both have a pretty large number of files, for very different reasons * MySQL stores MyISAM each table in a few files: FRM for the schema, MYD for data and MYI for the index * FileMaker Pro stores each table along with a bunch of interface stuff in a single file * SQLBase (popular in the contract / closed market segment) has a single file unless you tell it to partition the database. This single file stores tables, stored procedures, views, triggers, indexes and all transaction logs * MySQL requires 3 types of file for InnoDB storage in 3.23.x and 4.x - ibdata*, ib_arch_log_* and iblogfile*, in 4.1.1 and higher you can go to 4 files with individual table spaces The last point is particularly worthy of note. The new option in 4.1.1 and higher has implications for performance - you can have your InnoDB data dictionary (ibdata*), logs and individual table/index spaces on physically seperate devices. Backing things up becomes more complicated though. To be honest, the vast majority of database installations experience problems in performance caused by poor query and schema design, bad application logic or grossly underspecified hardware. A change in the number of files used to store the data is extremely unlikely to resolve these problems given all other variables in the environment remain fixed. What does everyone else think? Regards, Chris -- 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 ?
Chris Nolan wrote: Regarding mysqldump, it handles binary data through escaping the required characters. pg_dump is similar if memory serves me correctly. Thanks Chris. So dumping binary data / large objects using myslqdump is fine. I looked up if this is ok with pg_dump as well and it is but you must use a non-text output option Eg. a tar file. Regards, ..matthew -- 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 ?
Usually, i'll use enum('0','1') in place of a boolean type.
Curtis
For JDBC stuff, I've found that if you really want to call this a
shortcoming, then that's about as far as you can take it
- the MySQL JDBC driver makes the BIT field act just like a single-bit
field.
Regards,
Chris
Sure -- but I did say it was only 'slightly annoying'.
TINYINT(1) which works fine but is slightly annoying because
of the extra type conversion needed every time you use it.
It just means with an ODBC programming library that, say, a method
GetBoolean does not work and you need to do say...
int i = odbcReader.GetInt(index);
bool b = i 0 ? true : false;
every time instead of:
bool b = odbcReader.GetBoolean(index);
It's just tedious, that's all.
Regards,
..matthew
--
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 wrote:
Usually, i'll use enum('0','1') in place of a boolean type.
Curtis
For JDBC stuff, I've found that if you really want to call this a
shortcoming, then that's about as far as you can take it
- the MySQL JDBC driver makes the BIT field act just like a
single-bit field.
Regards,
Chris
Sure -- but I did say it was only 'slightly annoying'.
TINYINT(1) which works fine but is slightly annoying because
of the extra type conversion needed every time you use it.
It just means with an ODBC programming library that, say, a method
GetBoolean does not work and you need to do say...
int i = odbcReader.GetInt(index);
bool b = i 0 ? true : false;
every time instead of:
bool b = odbcReader.GetBoolean(index);
It's just tedious, that's all.
Regards,
..matthew
True, but this isn't too massive an issue either if you think about it -
ODBC is closely tied to everyone's favourite programming language,
Prolog...err...
I mean Haskell...errr...I mean LISP. :-) Of course, I actually mean C. C
has very few primitive types, so delegating true boolean fields to a
low priority
makes a little bit of sense.
As you have said, only slightly annoying but perhaps much less annoying
than my posts! :-)
Regards,
Chris
--
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?
mos wrote: At 01:14 AM 12/16/2003, you wrote: mos wrote: At 04:22 AM 12/15/2003, you wrote: To elaborate on Dr Frank's thing if you're interested, here's a classic deadlock example: 1. Transaction A obtains an exclusive lock on a set of rows which we will call R1. 2. Transaction B obtains an exclusive lock on another set of rows which we will call R2. 3. Transaction A requests (but obviously doesn't acquire) an exclusive lock on R2. 4. Transaction B requests (but obviously doesn't acquire) an exclusive lock on R1. 5. Classic deadlock! There are various ways of dealing with this (with timeouts and graph traversal seemingly popular). As you can see, neither transaction can go forward. Thus, the almighty InnoDB will think for a moment, decide which transaction it has something personal against, laugh at it and force it to ROLLBACK. That transaction can then try again if it doesn't feel totally small and humiliated. Hope this helps! Regards, Chris Chris, When my MySQL database gets into a deadlock situation like that, I just shutdown the server, power off the machine and go home. Works every time.vbg Mike (Sorry, it's been a late night) No no no!!! You've got it all wrong! You go and find the user who had the least involvement in causing the deadlock and scapegoat them! Either that, or you use it as an excuse for doing one of the following things: 1. Getting the purchase of more hardware approved. 2. Getting the purchase of MySQL Pro and InnoDB Hot Backup approved 3. Getting a MySQL support contract purchase approved 4. Getting a raise for resolving the situation with poise, grace and subtlety (Eg: Shutting it down, blaming users, blaming SCO...) Regards, Chris Chris, Hot Dang you're good! :-) I didn't see this in any of this in Paul Dubois's books. Maybe it could be added in a future version??? Call the section The Blame Game and put it into the Appendix called MySQL Survivor Game..Be the last one to be voted off the island.. bg Mike I think it would be better to base it on the prophetic vision of how reality television is set to evolve: Series 7. Regards, Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: System load - 0.5 on Linux, 75 on Solaris!
On Tue, Dec 16, 2003 at 10:13:39PM +1100, Chris Nolan wrote: There are a few things that could be happening: Are you using persistant connections from your web servers, Yes - with Apache::DBI under mod_perl 2. Which FS are you using? The native Solaris FS needs a bit of tuning to get decent DB performance apparently. The DB is on a vanilla UFS partition. Do you have any references about this? I couldn't find anything on the MySQL site... 3. What sort of disk subsystem does the new box have? As the load is so high, it seems that either MySQL is waiting on disk I/O or network I/O The database is on a single 120GB SCSI disk internal to the main unit. Nothing else is on that disk. If you have any suggestions for tests I could switch the machine into the system again and capture what is happening... MySQL runs in a single process of 720MB with the settings I gave it. 4. What table type(s) are you using on each box? Our database has 53 tables, all are MyISAM. The entire database takes up 1.9GB on disk. Almost all queries are lightweight ones of the form: select * from sometable where id=12345; where id is the primary key or, select id from sometable where customer=12345 where customer is indexed. Please write back! Let's fix this! :-) Many thanks for your help! -- 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 ?
To be honest, the vast majority of database installations experience problems in performance caused by poor query and schema design, bad application logic or grossly underspecified hardware. A change in the number of files used to store the data is extremely unlikely to resolve these problems given all other variables in the environment remain fixed. What does everyone else think? I fully agree with the part about performance and the number of files. :-) High-end database systems perfer more files, as they can be spread across multiple (usually fast SCSI) disks. Most databases recommend logs be kept on seperate disks than database files. Changes in the number of files can resolve problems if the problems are the result of low disk IO and you have money to throw at the problem. David -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Performance problems using 1GB Linux server and trying to avoid swapping
In the last episode (Dec 16), Markus Fischer said: I'm investigating a performance problem with mysql server set up. The server is running linux with 1GB ram. I'ld like to tune the configuration of the server to use as much RAM as possible without swapping to the disc because of the big slow down. [mysqld] set-variable= key_buffer=16M Way too low; this says only cache 16MB of index data. Try 256MB. set-variable= max_allowed_packet=200M Probably way too high, but doesn't hurt since it won't actually allocate that much memory unless you have a 200MB field someplace. I'm also not sure whether the database is swapping temporary tables to the disc or not; is there a way to verify if this is happening? show status like 'created_tmp%'; Raising sort_buffer_size and join_buffer_size may also help if your queries pull a lot of records. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?
David Griffiths wrote: To be honest, the vast majority of database installations experience problems in performance caused by poor query and schema design, bad application logic or grossly underspecified hardware. A change in the number of files used to store the data is extremely unlikely to resolve these problems given all other variables in the environment remain fixed. What does everyone else think? I fully agree with the part about performance and the number of files. :-) High-end database systems perfer more files, as they can be spread across multiple (usually fast SCSI) disks. Most databases recommend logs be kept on seperate disks than database files. Changes in the number of files can resolve problems if the problems are the result of low disk IO and you have money to throw at the problem. David True, but this isn't always the case. Let's look at the almighty InnoDB for instance. It happily uses multiple files for the data dictionary (ibdata*) if you tell it but, as far as I know, treats them like one big space, not doing striping or anything fancy. Additionally, if you have money to throw at the problem, you'd throw money at RAID and LVM. To prove my point, I pose the following question: * Would all those that enjoy extending database files as needed due to upgrading disks etc please raise their hands? Please? Come on! I promise that I won't tell SCO where you live With RAID you get performance and additional reliability if you want it. With LVM you get online growth of your storage if you are geeky and/or rich enough. Regarding logs being kept seperately, you will get no argument from me! In fact, if you can keep your logs on a HA cluster of NAS boxes, in a safe, covered in concrete, connected by multiply-redundant fibre-channel links, guarded by half the population of Wagga Wagga and plastered with images from random links from the awful, awful bowels of the internet I still reckon that you'd probably be able to find a way to go one better with log protection. Look at SQLBase - Zero Administration they reckon. Log files do not exist seperately, they exist inside the same file that holds everything else! Not good! You need to do a reorganise every few weeks or things start to...well..err...suck! Give me MySQL any day! Regards, Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: System load - 0.5 on Linux, 75 on Solaris!
Chris Allen wrote: On Tue, Dec 16, 2003 at 10:13:39PM +1100, Chris Nolan wrote: There are a few things that could be happening: Are you using persistant connections from your web servers, Yes - with Apache::DBI under mod_perl Well, that's one option out of the way,,, 2. Which FS are you using? The native Solaris FS needs a bit of tuning to get decent DB performance apparently. The DB is on a vanilla UFS partition. Do you have any references about this? I couldn't find anything on the MySQL site... There's nothing on the MySQL site but I do remember reading a comparison between Solaris x86 and Linux that showed that UFS gained quite a massive speedup for DB-style operations by turning on something similar to (or perhaps the very same thing as) soft updates. 3. What sort of disk subsystem does the new box have? As the load is so high, it seems that either MySQL is waiting on disk I/O or network I/O The database is on a single 120GB SCSI disk internal to the main unit. Nothing else is on that disk. If you have any suggestions for tests I could switch the machine into the system again and capture what is happening... MySQL runs in a single process of 720MB with the settings I gave it. Is the disk subsystem in the original box similar? 4. What table type(s) are you using on each box? Our database has 53 tables, all are MyISAM. The entire database takes up 1.9GB on disk. Almost all queries are lightweight ones of the form: select * from sometable where id=12345; where id is the primary key or, select id from sometable where customer=12345 where customer is indexed. Hmmmit's very, very unlikely, but have you checked to ensure that MySQL is using the indexes on the Solaris box (with EXPLAIN SELECT...) ? Additionally, what's your approximate balance of SELECT, UPDATE, INSERT and DELETE operations? The fact that there's such a huge difference here is odd... Please write back! Let's fix this! :-) Many thanks for your help! You're welcome! A few other questions have come to mind: 1. Are you using MySQL 3.23.52 on the Solaris box as well? 2. Have you tried facing Stockholm and begging for salvation? Regards, Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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]
Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?
High-end database systems perfer more files, as they can be spread across multiple (usually fast SCSI) disks. Most databases recommend logs be kept on seperate disks than database files. Changes in the number of files can resolve problems if the problems are the result of low disk IO and you have money to throw at the problem. David True, but this isn't always the case. Let's look at the almighty InnoDB for instance. It happily uses multiple files for the data dictionary (ibdata*) if you tell it but, as far as I know, treats them like one big space, not doing striping or anything fancy. It fills up the data-files sequentially (not writing a bit here, a bit there), but that could change. Of course, if you have all the data files spread across 5 disks, and you needed 10 non-sequential blocks (that aren't in memory), you will probably utilize more than one disk, which is a good thing. There is also a size issue. Each of our databases is about 25 gig of data. I don't want to cram that into one file. Some of our tables are in the 4-6 gig range. I don't want to be bumping up against 32-bit file-size limits. On Oracle, we have 9 tablespaces just for data (and each tablespace is 3 datafiles). Indexes have their own tablespaces. We have three mirrored disks, so each datafile goes on one mirrored-set. Our new server will be hot-swappable a RAID SCSI setup. The more drives you can get working at one time, the faster your database server will be (if you end up going to disk a fair bit). Backups in Oracle are not done by saving the datafiles. And if you use the InnoDB hot-backup tool (which we will have to), the same will apply to MySQL/InnoDB. So the # of files is not a hinderance to backups in that case. Finally, you can cram all your data into one InnoDB datafile if you so desire. It's one extra file to backup. In fact, with Oracle, you can just have one big data file. No FRM files or anything. Regarding logs being kept seperately, you will get no argument from me! In fact, if you can keep your logs on a HA cluster of NAS boxes, in a safe, covered in concrete, connected by multiply-redundant fibre-channel links, guarded by half the population of Wagga Wagga and plastered with images from random links from the awful, awful bowels of the internet I still reckon that you'd probably be able to find a way to go one better with log protection. Time to cut back on the coffee, my friend :) Seriously, log files are kept on different disks not for security, but for performance.Writing a 20-meg file to disk is a big performance hit; if you are trying to read and write database data from that disk at the same time, you'll notice it. Look at SQLBase - Zero Administration they reckon. Log files do not exist seperately, they exist inside the same file that holds everything else! Not good! You need to do a reorganise every few weeks or things start to...well..err...suck! Give me MySQL any day! That's brain-dead. The best database allows dba's and users to be as flexible as they want/need to be. David -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: System load - 0.5 on Linux, 75 on Solaris!
On Wed, Dec 17, 2003 at 03:53:04AM +1100, Chris Nolan wrote: The DB is on a vanilla UFS partition. Do you have any references about this? I couldn't find anything on the MySQL site... There's nothing on the MySQL site but I do remember reading a comparison between Solaris x86 and Linux that showed that UFS gained quite a massive speedup for DB-style operations by turning on something similar to (or perhaps the very same thing as) soft updates. Thanks. I'll look it up. The database is on a single 120GB SCSI disk internal to the main unit. Nothing else is on that disk. If you have any suggestions for tests I could switch the machine into the system again and capture what is happening... Is the disk subsystem in the original box similar? No, the Linux box has a single, internal EIDE drive with nearly everything on a single 40GB partition. Hmmmit's very, very unlikely, but have you checked to ensure that MySQL is using the indexes on the Solaris box (with EXPLAIN SELECT...) ? It's definitely running indexed. Additionally, what's your approximate balance of SELECT, UPDATE, INSERT and DELETE operations? The fact that there's such a huge difference here is odd... From our working slave server: Sel: 85% Ins: 03% Upd: 09% Del: 01% Key reads: 303184 Key read requests: 846809596 Open tables: 133 You're welcome! A few other questions have come to mind: 1. Are you using MySQL 3.23.52 on the Solaris box as well? Version 3.23.58 on the Solaris box. Is this likely to be a problem? 2. Have you tried facing Stockholm and begging for salvation? Worth a try I guess :-) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Testing Problems
Hi,
We have just installed MySQL 3.23.53 on Solaris 9. We were going through the
testing stages and it had produced an error.
Pasted below is the error that we received and also attached are the logs.
Installing Test Databases
Removing Stale Files
Installing Master Databases
Installing Slave Databases
Starting MySQL daemon
Loading Standard Test Databases
Starting Tests
TEST
USER SYSTEM ELAPSED RESULT
rpl15
[ fail ]
Below are the diffs between actual and expected results:
---
*** r/rpl15.resultFri Nov 1 12:04:39 2002
--- r/rpl15.rejectTue Dec 16 18:46:27 2003
***
*** 1,7
FilePositionBinlog_do_dbBinlog_ignore_db
master-bin.00173
Master_HostMaster_UserMaster_PortConnect_retryLog_FilePosSlave_RunningReplicate_do_dbReplicate_ignore_dbLast_errnoLast_errorSkip_counter
! 000No00
Master_HostMaster_UserMaster_PortConnect_retryLog_FilePosSlave_RunningReplicate_do_dbReplicate_ignore_dbLast_errnoLast_errorSkip_counter
127.0.0.1test9998604No00
Master_HostMaster_UserMaster_PortConnect_retryLog_FilePosSlave_RunningReplicate_do_dbReplicate_ignore_dbLast_errnoLast_errorSkip_counter
--- 1,7
FilePositionBinlog_do_dbBinlog_ignore_db
master-bin.00173
Master_HostMaster_UserMaster_PortConnect_retryLog_FilePosSlave_RunningReplicate_do_dbReplicate_ignore_dbLast_errnoLast_errorSkip_counter
! 127.0.0.1root930614No00
Master_HostMaster_UserMaster_PortConnect_retryLog_FilePosSlave_RunningReplicate_do_dbReplicate_ignore_dbLast_errnoLast_errorSkip_counter
127.0.0.1test9998604No00
Master_HostMaster_UserMaster_PortConnect_retryLog_FilePosSlave_RunningReplicate_do_dbReplicate_ignore_dbLast_errnoLast_errorSkip_counter
---
Please follow the instructions outlined at
http://www.mysql.com/doc/R/e/Reporting_mysqltest_bugs.html
to find the reason to this problem and how to report this.
Aborting. To continue, re-run with '--force'.
Ending Tests
Shutting-down MySQL daemon
Master shutdown finished
Slave shutdown finished
Please could you shed some light on what has gone wrong.
Many Thanks
Chris Man
Business Systems International
BSI House,
59 Markham Street,
London SW3 3NR
Tel: 020 7352 7007
Fax: 020 7352 7423
www.e-business.com
email: [EMAIL PROTECTED]
The following section of this message contains a file attachment
prepared for transmission using the Internet MIME message format.
If you are using Pegasus Mail, or any other MIME-compliant system,
you should be able to save it or view it from within your mailer.
If you cannot, please ask your system administrator for assistance.
File information ---
File: mysql-log.zip
Date: 16 Dec 2003, 17:35
Size: 2358 bytes.
Type: ZIP-archive
mysql-log.zip
Description: Zip archive
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Invalid ENUM values after upgrading from 4.0 to 4.1
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]
missing something obvious w/grant statement length?
I seem to have run into a problem with a host name that incorporates a hyphen: mysql GRANT ALL ON *.* TO [EMAIL PROTECTED] IDENTIFIED BY foo; ERROR 1064: You have an error in your SQL syntax near '-dev.foo.com IDENTIFIED BY foo' at line 1 mysql GRANT ALL ON *.* TO [EMAIL PROTECTED] IDENTIFIED BY foo; ERROR 1145: The host or user argument to GRANT is too long I must be missing something very obvious--how to incorporate a hostname with a hyphen, for instance. Can someone help? ari Ari Davidow [EMAIL PROTECTED] http://www.ivritype.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Installation Question - RedHat 9.0
I am trying to setup Jabberd2 as a test for our IT users and have run into a few issues. Here is a link to the instructions I'm following: http://www.jabberdoc.org/app_mysql.html I know there are things missing because after I complete the make install in step A.4.6, it goes right on to have me change the root password, but mysqld isn't even running at this point. Could someone that has installed mySQL many times give these a look and see what steps they have left out that I will need for RedHat 9.0? One other step that doesn't appear to be correct is A.4.9 because /usr/local/var/mysql/ does not even exist after the install as they describe it. Also, if I want mysqld to start every time the computer is booted, and use the mysql user that I created per these instructions, is this what I would add to rc.local: /usr/local/libexec/mysqld --user=mysql Is rc.local the correct file to put this in or is there a better place? Any help that is provided will be much appreciated. Thanks! Ryan Sinnwell Regional IT Engineer The Weitz Company 515-698-4281 515-229-5517 (Cell) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: missing something obvious w/grant statement length?
At 13:12 -0500 12/16/03, Ari Davidow wrote: I seem to have run into a problem with a host name that incorporates a hyphen: mysql GRANT ALL ON *.* TO [EMAIL PROTECTED] IDENTIFIED BY foo; ERROR 1064: You have an error in your SQL syntax near '-dev.foo.com IDENTIFIED BY foo' at line 1 mysql GRANT ALL ON *.* TO [EMAIL PROTECTED] IDENTIFIED BY foo; ERROR 1145: The host or user argument to GRANT is too long I must be missing something very obvious--how to incorporate a hostname with a hyphen, for instance. Can someone help? Quote it. Better yet, always quote usernames *and* hostnames to avoid the problem entirely: 'me'@'mysite-dev.foo.com' Note that the username and hostname are quoted separately. -- 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: missing something obvious w/grant statement length?
From: Ari Davidow [mailto:[EMAIL PROTECTED] I seem to have run into a problem with a host name that incorporates a hyphen: mysql GRANT ALL ON *.* TO [EMAIL PROTECTED] IDENTIFIED BY foo; ERROR 1064: You have an error in your SQL syntax near '-dev.foo.com IDENTIFIED BY foo' at line 1 mysql GRANT ALL ON *.* TO [EMAIL PROTECTED] IDENTIFIED BY foo; ERROR 1145: The host or user argument to GRANT is too long I must be missing something very obvious--how to incorporate a hostname with a hyphen, for instance. Can someone help? The syntax is a bit weird for GRANT statements; either side of [EMAIL PROTECTED] are two separate args to be stored in two separate columns. This should work... GRANT ALL ON *.* TO 'me'@'mysite-dev.foo.com' IDENTIFIED BY 'foo'; -- 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: Installation Question - RedHat 9.0
For RedHat, just download the rpms and install it, it's far much simpler... Nicolas - Original Message - From: Ryan Sinnwell [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, December 16, 2003 1:21 PM Subject: Installation Question - RedHat 9.0 I am trying to setup Jabberd2 as a test for our IT users and have run into a few issues. Here is a link to the instructions I'm following: http://www.jabberdoc.org/app_mysql.html I know there are things missing because after I complete the make install in step A.4.6, it goes right on to have me change the root password, but mysqld isn't even running at this point. Could someone that has installed mySQL many times give these a look and see what steps they have left out that I will need for RedHat 9.0? One other step that doesn't appear to be correct is A.4.9 because /usr/local/var/mysql/ does not even exist after the install as they describe it. Also, if I want mysqld to start every time the computer is booted, and use the mysql user that I created per these instructions, is this what I would add to rc.local: /usr/local/libexec/mysqld --user=mysql Is rc.local the correct file to put this in or is there a better place? Any help that is provided will be much appreciated. Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: missing something obvious w/grant statement length?
At 13:29 -0500 12/16/03, Mike Johnson wrote: From: Ari Davidow [mailto:[EMAIL PROTECTED] I seem to have run into a problem with a host name that incorporates a hyphen: mysql GRANT ALL ON *.* TO [EMAIL PROTECTED] IDENTIFIED BY foo; ERROR 1064: You have an error in your SQL syntax near '-dev.foo.com IDENTIFIED BY foo' at line 1 mysql GRANT ALL ON *.* TO [EMAIL PROTECTED] IDENTIFIED BY foo; ERROR 1145: The host or user argument to GRANT is too long I must be missing something very obvious--how to incorporate a hostname with a hyphen, for instance. Can someone help? The syntax is a bit weird for GRANT statements; either side of [EMAIL PROTECTED] are two separate args to be stored in two separate columns. Why is it weird? What if you wanted to include a '@' character in your username? '[EMAIL PROTECTED]'-style quoting wouldn't allow that. :-) This should work... GRANT ALL ON *.* TO 'me'@'mysite-dev.foo.com' IDENTIFIED BY 'foo'; -- Mike Johnson Web Developer/Systems Asst. Smarter Living, Inc. phone (617) 497-2500 x226 -- 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: missing something obvious w/grant statement length?
From: Paul DuBois [mailto:[EMAIL PROTECTED] At 13:29 -0500 12/16/03, Mike Johnson wrote: From: Ari Davidow [mailto:[EMAIL PROTECTED] I seem to have run into a problem with a host name that incorporates a hyphen: mysql GRANT ALL ON *.* TO [EMAIL PROTECTED] IDENTIFIED BY foo; ERROR 1064: You have an error in your SQL syntax near '-dev.foo.com IDENTIFIED BY foo' at line 1 mysql GRANT ALL ON *.* TO [EMAIL PROTECTED] IDENTIFIED BY foo; ERROR 1145: The host or user argument to GRANT is too long I must be missing something very obvious--how to incorporate a hostname with a hyphen, for instance. Can someone help? The syntax is a bit weird for GRANT statements; either side of [EMAIL PROTECTED] are two separate args to be stored in two separate columns. Why is it weird? What if you wanted to include a '@' character in your username? '[EMAIL PROTECTED]'-style quoting wouldn't allow that. :-) Heh, OK, weird has bad connotations. What I meant was that the syntax for a GRANT statement replaces a series of INSERT/UPDATE statements on the mysql db, and as such, while succinct, it's not always entirely intuitive. Another example of that is the IDENTIFIED BY 'password' clause. It took me a few times to remember that it automatically called PASSWORD() on 'password' -- then again, I came into GRANT statements from first doing the INSERT/UPDATE calls manually. :) -- 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]
utf8
I can't find in MySQL 4.1.1 documentation anything about 'character_set_system' variable. It is setted up to 'utf8'. How can I change it to be able to use russian KOI8-R in mysql client? Linux Mandrake 9.2 MySQL 4.1.1 RPM With best regards, Igor Zinkovsky Saint-Petersburg, Russia.
Unable to insert String constants through my function
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
__
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]
Re: UTF-8 support
On Tuesday, December 16, 2003 10:29 AM CET, Adam Hardy wrote: On 12/16/2003 01:35 AMnbsp;Ligaya Turmelle wrote: Can anyone tell me what the current support for the UTF8 character set is? How strong is it? Do I have to do anything special to save the characters? I will be getting the characters from a webpage form and inserting the characters with PHP. It depends if you are prepared to go with the 4.1.1 alpha version or not, which has complete UTF-8 support. Production version 4.0.x lacks some of the UTF-8 facilities but I believe many people are using it with work-arounds where necessary. As I do. But I don't know what 'work-arounds' should be necessary with that? I only need to write my own string manipulation functions for PHP to respect the multibyte nature of UTF-8 but I don't process the data in any way before I put it into my table or get it out again. I just use the MySQL database as if it was simple ANSI data. Sure, I'll get some problems with long user input that contains many multibyte characters, but in the worst case it will be cut down. Nothing _really_ dramatic. -- 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]
2 Fulltext index's are better than 1? No
Hello,
I recently ugraded to 4-14-Max from 4.13-Max to take advantage
of the optimizer fulltext bug fix. However a certain query using the
fulltext indexies is MUCH SLOWER then using no idex. Here is the query
select distinct p.USER_CREATED, p.ID, p.DATE_CREATED, p.LAST_CLICK, p.ZIP
from PERSON p
join INTEREST i on p.ID=i.PERSON_ID
join INTEREST i0 on p.ID=i0.PERSON_ID
join INTEREST_TYPE it0 on i0.INTEREST_ID=it0.ID
where p.FIRST_NAME!='Unsubscribed' and match(i.COMMENT) against('+games ' in
boolean mode) and (it0.NAME='occupation' and match(i0.COMMENT)
against('+software ' in boolean mode));
the explain on 4.0.13-Max
+---++--
---+---+-+-++---
---+
| table | type | possible_keys
| key | key_len | ref | rows | Extra
|
+---++--
---+---+-+-++---
---+
| it0 | ALL| PRIMARY
| NULL |NULL | NULL| 30 | Using where; Using temporary
|
| i | ALL|
PERSON_ID,interest_person_id_idx,interest_person_id_two_idx | NULL |
NULL | NULL| 550709 | Using where |
| p | eq_ref | PRIMARY,person_id_base_idx
| PRIMARY | 40 | i.PERSON_ID | 1 | Using where
|
| i0| eq_ref |
PERSON_ID,interest_person_id_idx,interest_person_id_two_idx | PERSON_ID |
80 | p.ID,it0.ID | 1 | Using where; Distinct|
Query time: 15 seconds
Notice that it does not use the fulltext index (COMMENT) in the interest
tables I,i0
The explain on 4-14-Max
+---+--+
-+-+-++--+--
+
| table | type | possible_keys
| key | key_len | ref| rows | Extra|
+---+--+
-+-+-++--+--
+
| i | fulltext |
PERSON_ID,interest_person_id_idx,interest_person_id_two_idx,COMMENT |
COMMENT | 0 ||1 | Using where; Using temporary |
| p | eq_ref | PRIMARY,person_id_base_idx
| PRIMARY | 40 | i.PERSON_ID|1 | Using where |
| i0| fulltext |
PERSON_ID,interest_person_id_idx,interest_person_id_two_idx,COMMENT |
COMMENT | 0 ||1 | Using where; Distinct|
| it0 | eq_ref | PRIMARY
| PRIMARY | 40 | i0.INTEREST_ID |1 | Using where; Distinct|
Query time : 146 seconds, ( slow querylog lists rows examined as 3.6 million
)
Notice the different join order and use of the fulltext indexies
With a single join of the INTEREST table the index is used correctly and the
query is quick. If anyone has any idea why USING a fulltext index is slower
please share your knowledge. Below is the show create table output.
Thanks,
Trevor
| INTEREST | CREATE TABLE `INTEREST` (
`ID` varchar(40) NOT NULL default '',
`PERSON_ID` varchar(40) NOT NULL default '',
`COMMENT` mediumtext,
`DATE_CREATED` timestamp(14) NOT NULL,
`INTEREST_ID` varchar(40) NOT NULL default '',
PRIMARY KEY (`ID`),
UNIQUE KEY `PERSON_ID` (`PERSON_ID`,`INTEREST_ID`),
KEY `interest_person_id_idx` (`PERSON_ID`),
KEY `interest_person_id_two_idx` (`PERSON_ID`,`COMMENT`(200)),
FULLTEXT KEY `COMMENT` (`COMMENT`)
) TYPE=MyISAM |
| INTEREST_TYPE | CREATE TABLE `INTEREST_TYPE` (
`ID` varchar(40) NOT NULL default '',
`NAME` varchar(100) default NULL,
`NICE_NAME` varchar(100) default NULL,
`TYPE` varchar(20) NOT NULL default '',
`DESCRIPTION` mediumtext,
`DATE_CREATED` timestamp(14) NOT NULL,
PRIMARY KEY (`ID`)
) TYPE=MyISAM |
| PERSON |CREATE TABLE `PERSON` (
`ID` char(40) NOT NULL default '',
`PHOTO_ID` char(40) default NULL,
`EMAIL` char(100) NOT NULL default '',
`LAST_CLICK` timestamp(14) NOT NULL,
`FIRST_NAME` char(40) default NULL,
`LAST_NAME` char(40) default NULL,
`ZIP` char(15) NOT NULL default '',
`COUNTRY` char(40) default NULL,
`BIRTHDAY` datetime default NULL,
`GENDER` char(1) default NULL,
`STATUS` char(1) default NULL,
`SEND_EMAIL` tinyint(1) default NULL,
`IS_DELETED` char(1) NOT NULL default '',
`USER_CREATED` int(11) NOT NULL default '0',
`DATE_CREATED` timestamp(14) NOT NULL,
`USER_MODIFIED` int(11) default NULL,
`DATE_MODIFIED` timestamp(14) NOT NULL,
`HIDE_AGE` tinyint(1) default NULL,
`HIDE_GENDER` tinyint(1) default NULL,
`HIDE_LOCATION` tinyint(1) default NULL,
`HIDE_ONLINE` tinyint(1) default NULL,
`GENERATION` int(11) default '0',
`ALLOW_EMAIL_DEGREE` int(11) default '0',
`HAS_FILTER` tinyint(1) default '0',
Hello, why you didnt call ?
Re: utf8
Hi! On Dec 16, Igor Zinkovsky wrote: I can't find in MySQL 4.1.1 documentation anything about 'character_set_system' variable. It is setted up to 'utf8'. How can I change it to be able to use russian KOI8-R in mysql client? You cannot change it. But you don't need to either. It is character set for system usage - usernames, table, column names etc. To use KOI8-R in MySQL client you need to 1. Be sure you can actually type and see koi8-r characters - you need to setup you terminal program/console properly for this 2. Issue SET NAMES koi8r; from mysql command line client or start it with --default-character-set=koi8r (or put default-character-set=koi8r in ~/.my.cnf) - to inform the server that you send queries and want to receive results in koi8r charset. That's all! 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]
Help needed with mysql
Hi I am using LOAD DATA INFILE to populate a mysql db. I prepare the file in Excell and save it as csv format. Every works fine... except that the file contains special characters with accent or symbols. Is there a way to import such text field directly or do I need to escape all these special characters before? i.e the german word for piece Stück is inserted as Stck in mysql table. Do I have to insert a backslash before each special char St\ück in the excell file? -- Kind regards, Paul. Gondwana [EMAIL PROTECTED] http://www.gondwanastudio.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to make a reminder ?
Hello everybody ! Pierre DELGOVE from Rennes, FRANCE. I am studying a model of database adapted to the management of a veterinary clinic (in order to design perhaps a software that I could use on my own on a GPL license). I meet this practical problem : how could I get a reminder (i.e. after having injected a vaccine for example, I need to recall the client for the next time, for example a year after or six months after) ? The solution I thought about is to store in a field a date which is converted in days (using the TO-DAYS function) then 365 days will be added (if I want a one-year reminder). that will give an other value which will be converted in date by the FROM-DAYS function. Does anybody has an other idea to solve this problem ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
INNODB as default table type
Hallo, is it possible to use as a default INNODB instead of ISAM in a mysql server, so that every table, database (at least the complete server) is based on a INNODB tablespace? Is this a practical and good way to use mysql in that setup, if possible? regards Harald -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INNODB as default table type
Harald Falkenberg [EMAIL PROTECTED] wrote: is it possible to use as a default INNODB instead of ISAM in a mysql server, so that every table, database (at least the complete server) is based on a INNODB tablespace? Is this a practical and good way to use mysql in that setup, if possible? Take a look at --default-table-type option of mysqld: http://www.mysql.com/doc/en/Command-line_options.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Bug in mysql.h header file
Hi P Arunachalam , Add this to your program . Make sure header files exist ( its there anyway). #ifdef WIN32 #include windows.h #include winsock2.h #pragma warning (disable: 4514 4786) #pragma warning( push, 3 ) #define VERSION 4.1 #endif Enjoy... P Arunachalam wrote: Hi, I have made all the changes suggested by you in VC++ 6.0 i.e., I have inluded the Library file wsock32.lib into the existing library list in 'Project Settings'(Alt-F7') and checked the path setting of mysql header file and library file in Tools -- Options menu's Directory tab. but still it need the inclusion of header file winsock.H into my C program... otherise it shows the list of errors. name of my C program file is 'mysql.c' Error executing cl.exe. MYSQL.OBJ - 102 error(s), 1 warning(s) So pls verify and give me details... arun. --- info [EMAIL PROTECTED] wrote: This is not a bug, you need to set your project/programming environment in VC++ to include support for sockets. I haven't written any non socket programs in several years, so it's part of my standard set-up. MySQL is client/server and uses sockets. You do need to link with a socket library. In the case of VC++ this is Winsock (wsock32.lib). I normally include support for sockets in my stdfx.h (#include afxsock.h // MFC socket extensions). This is included with your VC++ installation and not part of mysql source. Then, add wsock32.lib, mysqlclient.lib and any other external library you need to link with in your 'Project Settings'(Alt-F7'). These are added under the 'Link' tab in the 'Input' category. Libraries are added in the 'Object/library module' field separated by spaces (e.g. 'wsock32.lib mysqlclient.lib'). Don't forget to add the path to the mysqlclient library in the 'Additional library path:' field. Pat... - Original Message - From: P Arunachalam [EMAIL PROTECTED] To: Patrick Sherrill [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, December 16, 2003 6:36 AM Subject: Bug in mysql.h header file I was trying to establish connection to MySQL through a simple C program using it's C API mysqL_init(), mysql_reak_connect(), ... in VC++ Editor. It shows Errors ; Compiling... MYSQL.C c:\mysql\include\mysql_com.h(116) : error C2061: syntax error : identifier 'SOCKET' MYSQL.OBJ - 102 error(s), 1 warning(s) I have corrected this errors by including winsock.h header file into my program. After including winsock.h my program work fine and produce my expected results. Herewith I have included the Sample C Program too for your reference... Incase it is a bug you please produce the correct version of mysql.h header file to me. Thanks. regards, Arun. --- Patrick Sherrill [EMAIL PROTECTED] wrote: Statically linking using C API only you will need mysqlclient.lib For odbc interface you'll need to make odbc calls in your code and use myodbc.dll (Install myodbc on the client). You can mix calls, but why would you. I have found the C API to be the best solution for us. We statically link for dll avoidance. You can get the source or pre-built client libraries from the MySQL web site. I hope this helps. Pat... [EMAIL PROTECTED] CocoNet Corporation SW Florida's First ISP 825 SE 47th Terrace Cape Coral, FL 33904 - Original Message - From: P Arunachalam [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, December 11, 2003 4:31 AM Subject: reg C API from MySQL Yahoo! India Mobile: Download the latest polyphonic ringtones. Go to http://in.mobile.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Yahoo! India Mobile: Download the latest polyphonic ringtones. Go to http://in.mobile.yahoo.com -- Aftab Jahan Subedar CEO/Software Engineer Subedar Technologies Subedar Baag Bibir Bagicha #1 North Jatrabari Dhaka 1204 Bangladesh http://www.DhakaStockExchangeGame.com/ - [EMAIL PROTECTED] http://www.CEOBangladesh.com/ - [EMAIL PROTECTED] http://www.geocities.com/jahan.geo/ - [EMAIL PROTECTED] sms://+447765341890 tel://+88027519050 EMail://[EMAIL PROTECTED] - Directly to my notebook -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INNODB as default table type
At 22:06 +0100 12/16/03, Harald Falkenberg wrote: Hallo, is it possible to use as a default INNODB instead of ISAM in a mysql server, so that every table, database (at least the complete server) is based on a INNODB tablespace? Is this a practical and good way to use mysql in that setup, if possible? No, at least not unless you disable the grant tables in the mysql database. Those tables must be MyISAM tables. You can set the default table type for new table using the --default-table-type, as Egor has already mentioned. -- 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 ?
At 01:59 PM 12/14/2003, you wrote: Hello. I have been tasked with evaluating open source databases for a large upcoming project: e-commerce, B2B, high availability. The O/S is most likely to be Linux, although FreeBSD could possibly be used (lower probability). So far, it seems that MySQL, MaxDB, PostgreSQL, and Interbase/Firebird are possible candidates. Does anyone know why we should or should not use any of these? Does anyone know of other possibilities? I'd very much appreciate hearing your comments and recommendations. I have only recently started these evaluations. BTW, my own background is from the Oracle DBA world. MySQL is certainly popular and seems to have very good performance, but I am concerned that the lack of Triggers, Stored Procedures, User-Defined Functions, and Views (to a lesser degree ) will be a disadvantage. MaxDB appears to be more feature-rich and possibly more industrial-strength. How does its performance and stability compare to the others? Many Thanks. Jerry Apfelbaum Toronto 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). 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. 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. 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. 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. Mike -- 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 ?
Hi, Many Thanks. Jerry Apfelbaum Toronto 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? 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. 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. 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. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Null-safe equal help, please
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 my now-fevered mind sees no reason the two above statements are not equivalent. 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. (Just as a test I've also tried possibly_null_value = NULLIF( ISNULL(INET_ATON(IP)), 1 ), which is wrong for my app, but still broken) MySQL 4.0.15-standard Any insight would be much appreciated. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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]
Re: Using MySQL in LGPL library
On Tue, Dec 16, 2003 at 01:22:15PM +0100, Rodrigo Moya wrote: I *thought* that MySQL AB was coming up with some sort of exception for GPL'd free software but I don't remember if that got resolved. oh, that's nice for them I checked into it. They're still working on it. I guess the lawyers aren't quite done yet. Perhaps more pressure is needed? yes, I guess if we all have to disable mysql support in our projects, that's going to be some kind of pressure. As I said, we don't want to do that, but if there's no solution, we'll be forced to. Do you know any other projects in the same situation? I'll check with a few folks and see if I can turn up something... ok, thanks. Please keep us informed. I've suggested that they at least put up a notice on the web site (and this mailing list) which explains their intentions so that you and other GPL'd projects don't needlessly waste time disabling MySQL support and then re-enabling it later. Hopefully something like that will happen soon. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Spatial Extension in MySQL 4.1.1-alpha
Hi,
I am trying out the new GIS capabilities in MySQL 4.1.1-alpha (windows)
and I am trying to follow some of the examples from chapter 10 in the
manual.
Here is a snap shot of my run
mysql create table geom(g geometry);
Query OK, 0 rows affected (0.06 sec)
mysql insert into geom values(GeomFromText('POINT(1,1)'));
Query OK, 1 row affected (0.00 sec)
mysql select * from geom;
+--+
| g|
+--+
| NULL |
+--+
1 row in set (0.00 sec)
mysql select AsText(g) from geom;
+---+
| AsText(g) |
+---+
| NULL |
+---+
1 row in set (0.00 sec)
mysql
-
Notice that the select statement at the end gives me NULL for the value
of g.
I was expecting the following:
mysql select AsText(g) from geom;
+---+
| AsText(g) |
+---+
| Point(1 1)|
+---+
1 row in set (0.00 sec)
Does anyone know what I am missing?
Thanks,
Matt
---
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]
Re: Spatial Extension in MySQL 4.1.1-alpha
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-
--
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
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? my now-fevered mind sees no reason the two above statements are not equivalent. IS NULL can be used only to test whether or not something is NULL. = can be used to test NULL or any other value. col_name1 IS NULL is true only if col_name1 is NULL. col_name1 = col_name2 is true if col_name1 is the same as col_name2, even when they're both NULL col_name1 = col_name2 is true if col_name1 is the same as col_name2, but only if both are *not* null. 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? (Just as a test I've also tried possibly_null_value = NULLIF( ISNULL(INET_ATON(IP)), 1 ), which is wrong for my app, but still broken) MySQL 4.0.15-standard Any insight would be much appreciated. -- 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: 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]
Unexpected restarts
My mySQL server unexpectedly restarts itself from time to time. I am able to verify this as the server uptime resets. I caught it 'in the act' today, but can't tell what the problem is. Does anyone know what causes this behavior? Thanks, Joshua Thomas Network Operations Engineer PowerOne Media, Inc. tel: 518-687-6143 [EMAIL PROTECTED] --- In theory there is no difference between theory and practice. In practice there is. - Yogi Berra ---
localhost
Hello all this is my first post to the mysql mailing list. I installed mysql on rh9 as an rpm. I setup the root password using this mysql - u password foo When I check the status it says [EMAIL PROTECTED] How do I change that to [EMAIL PROTECTED] The box I am using does have a host name. Thanks in advance. -- 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 ?
32-bit filesystem limits? Looking at any modern Linux FS, your file size limits are not hindered by 32-bit anything or even the FS itself. On kernel 2.4, internal kernel structures limit the maximum size of block devices to around 1 TB. As a result, you can only have files of about that size (even though ReiserFS and XFS support hideously bigger files). As far as I know, almost every OS aimed at doing anything non-trivial (so everything other than DOS 2.11), has no problem with files of any size up to some rather massive limit like the one above. Additionally, you could just point InnoDB at a raw partition sitting on an LVM set on top of a RAID and be happy. Or you could do what my friend tried once, and see if you can hot-swap a bog-standard IDE drive Regarding SQLBase, it is indeed a joke. The latest version includes sorting methods that are up to 250% faster! If that's the case, which idiot coded the first set? Almost every other DB vendor has figured out how to sort quite well for ages now... David Griffiths wrote: High-end database systems perfer more files, as they can be spread across multiple (usually fast SCSI) disks. Most databases recommend logs be kept on seperate disks than database files. Changes in the number of files can resolve problems if the problems are the result of low disk IO and you have money to throw at the problem. David True, but this isn't always the case. Let's look at the almighty InnoDB for instance. It happily uses multiple files for the data dictionary (ibdata*) if you tell it but, as far as I know, treats them like one big space, not doing striping or anything fancy. It fills up the data-files sequentially (not writing a bit here, a bit there), but that could change. Of course, if you have all the data files spread across 5 disks, and you needed 10 non-sequential blocks (that aren't in memory), you will probably utilize more than one disk, which is a good thing. There is also a size issue. Each of our databases is about 25 gig of data. I don't want to cram that into one file. Some of our tables are in the 4-6 gig range. I don't want to be bumping up against 32-bit file-size limits. On Oracle, we have 9 tablespaces just for data (and each tablespace is 3 datafiles). Indexes have their own tablespaces. We have three mirrored disks, so each datafile goes on one mirrored-set. Our new server will be hot-swappable a RAID SCSI setup. The more drives you can get working at one time, the faster your database server will be (if you end up going to disk a fair bit). Backups in Oracle are not done by saving the datafiles. And if you use the InnoDB hot-backup tool (which we will have to), the same will apply to MySQL/InnoDB. So the # of files is not a hinderance to backups in that case. Finally, you can cram all your data into one InnoDB datafile if you so desire. It's one extra file to backup. In fact, with Oracle, you can just have one big data file. No FRM files or anything. Regarding logs being kept seperately, you will get no argument from me! In fact, if you can keep your logs on a HA cluster of NAS boxes, in a safe, covered in concrete, connected by multiply-redundant fibre-channel links, guarded by half the population of Wagga Wagga and plastered with images from random links from the awful, awful bowels of the internet I still reckon that you'd probably be able to find a way to go one better with log protection. Time to cut back on the coffee, my friend :) Seriously, log files are kept on different disks not for security, but for performance.Writing a 20-meg file to disk is a big performance hit; if you are trying to read and write database data from that disk at the same time, you'll notice it. Look at SQLBase - Zero Administration they reckon. Log files do not exist seperately, they exist inside the same file that holds everything else! Not good! You need to do a reorganise every few weeks or things start to...well..err...suck! Give me MySQL any day! That's brain-dead. The best database allows dba's and users to be as flexible as they want/need to be. David -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
query over several tables
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 date2003-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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: REvoke select rights.
Egor Egorov wrote: Sohail Hasan [EMAIL PROTECTED] wrote: I have created a database and created a user with select rights to that database, there is a table name xyz in that database that I don't want that user to view, that's why i want to revoke that select rights from only that table, can anyone tell me what syntax of revoke would be used here. You can't revoke SELECT privilege from the table if you grant privileges on the database. You can grant SELECT privilege on each table that you want to be visible to user instead. I tried to GRANT only SELECT rights by using the below mentioned syntax but it is giving error: GRANT SELECT ON product cmstemp.* TO autobot1 IDENTIFIED BY 'autobot1'; Where product is the tablename and cmstemp is the DB name. shasan
Multiple Outer Joins
This isn't going to be a very good question because I don't really understand what I'm being asked... I have an invoicing database with two tables: An invoice header table; and a line items table. The line items table includes a column for the foreign key to the invoice header, a column for the services provided code, then unit cost, extended cost, description, etc., like so: create table invoice_master ( InvoiceIDvarchar(10), ClientInfo ... ) ; create table line_items ( PK_item varchar(10), FK_InvoiceIDvarchar(10), LineItemInfo ... ) ; My report-writer person has to render a report that aggregates invoices based on various combinations of line item codes. For example, for a given client, how many invoices included a line item for premium processing (one of the line item codes is premium processing) of an H1 visa (processing an H1 visa is another line item code); how many invoices for an H1 without premium processing; how many H1 extensions, with and without premium processing, etc. My report writer says (and I believe her because I don't know any better) that she must perform multiple outer self joins on the line_item table in order to create this report. She also says that while one can do a single outer join with MySQL, one cannot do multiple outer joins. Unfortunately, I don't know enough SQL, or MySQL, to argue the point with her. I do know enough about RDBMS design, however, to object strongly to her proposed solution, which is to add 10 columns to the invoice header table (there can only be 10 line items on any given invoice -- most, however, only have two or three line items). (BTW, is an outer join the same thing as a left join?) So, the question: is Ms. Report Writer right or wrong? Or, maybe the better question is, how can this be done? Any and all help appreciated. Randy -- 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 ?
At 04:07 PM 12/16/2003, you wrote: Hi, Many Thanks. Jerry Apfelbaum Toronto 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. 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. 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. 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). The SuperServer was was suppose to solve that with a separate thread for each connection, but had some speed issues on SMP machines (ran slower). IB requires a lot more work in tweaking than say MySQL because it is a much more sophisticated database. Properly configured it can work well, but is a higher maintenance database than MySQL and requires more memory and CPU. If the programmer needs all those bells and whistles, then IB may be the solution they're looking for. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
