Re: Creating new table from distinct entries
Hi, You will get a MySQL query syntax error number 1064 when you incorrectly use a reserved words in your query . * and DISTINCT cannot appear together. Check with the SELECT clause column list. Thanks ViSolve DB Team. - Original Message - From: Alan Milnes [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, October 24, 2006 12:47 AM Subject: Creating new table from distinct entries MySQL 4.1.21-community-nt I have a table in my database that has a Primary key on 2 fields (MyID and MyChange) and a field that indicates if there is a problem with the record (MyError)- I want to create a new table that only has unique MyIDs and where there is more than 1 I only want the record with the highest MyChange number. The table has about 50 fields so I have the following code:- CREATE TABLE mystats SELECT *, DISTINCT MyID FROM oldstats WHERE MyError IS NULL ORDER BY MyChange DESC ; but I am getting an MySQL error #1064. Any ideas or suggestions as to where I am going wrong? Alan -- 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: administrtor-user
Hi,
Pls plaly with the link for Info :
http://fogwater.com/Articles/InstallingMySQLforWindows.html
http://dev.mysql.com/doc/refman/5.0/en/default-privileges.html
http://dev.mysql.com/tech-resources/articles/securing_mysql_windows.html
MySQL Administrator for Windows 1.0.12 A powerful visual administration
console that enables you to easily administer your MySQL environment.
http://www.softpedia.com/get/Internet/Servers/Database-Utils/MySQL-Administrator-for-Windows.shtml
Thanks
ViSolve DB Team.
- Original Message -
From: Renish [EMAIL PROTECTED]
To: Visolve DB Team [EMAIL PROTECTED]; mysql@lists.mysql.com
Sent: Monday, October 23, 2006 11:16 AM
Subject: Re: administrtor-user
in windows.. Pls explain me as though I am a beginner.
- Original Message -
From: Visolve DB Team [EMAIL PROTECTED]
To: Renish [EMAIL PROTECTED]; mysql@lists.mysql.com
Sent: Monday, October 23, 2006 12:49 PM
Subject: Re: administrtor-user
Hi,
On which platform?
It do good in Linux and Unix.
Thanks
ViSolve DB Team.
- Original Message -
From: Renish [EMAIL PROTECTED]
To: Visolve DB Team [EMAIL PROTECTED];
mysql@lists.mysql.com
Sent: Monday, October 23, 2006 10:05 AM
Subject: Re: administrtor-user
I am using sql 4.1.21. useradd mysql itself is not working
Regards
- Original Message -
From: Visolve DB Team
To: Renish ; mysql@lists.mysql.com
Sent: Monday, October 23, 2006 12:17 PM
Subject: Re: administrtor-user
Hi
First,
shell useradd mysql
Changing UserName and Group name for data directory
shell chown -R mysql:mysql data
Create Initial database
shell scripts/mysql_install_db --user=mysql
Start the Server
Then to User:
First, use the mysql program to connect to the server as the MySQL root
user:
shell mysql --user=root mysql
1. After connecting to the server as root, you can add new accounts.
The following statements use
GRANT to set up four new accounts:
mysql GRANT ALL PRIVILEGES ON *.* TO 'monty'@'localhost'
IDENTIFIED BY 'some_pass' WITH GRANT OPTION;
[OR]
mysql GRANT ALL PRIVILEGES ON *.* TO 'monty'@'%' IDENTIFIED BY
'some_pass' WITH GRANT OPTION;
2. mysql INSERT INTO user (Host,User,Password)
VALUES('localhost','UN','PWD');
Then to Change Password:
1. Passwords may be assigned from the command line by using the
mysqladmin command:
shell mysqladmin -u user_name -h host_name password newpwd
2. Another way to assign a password to an account is to issue a SET
PASSWORD statement:
mysql SET PASSWORD FOR 'jeffrey'@'%' = PASSWORD('biscuit');
3. mysql SET PASSWORD = PASSWORD('biscuit');
To Drop user:
1. mysql DELETE FROM user WHERE User='' and HOST=''
Thanks
ViSolve DB Team.
- Original Message -
From: Renish [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Monday, October 23, 2006 9:30 AM
Subject: administrtor-user
Can u guys tell me..
How can I be the administrator for the mysql and thenI I need to
create
users x, y,z and assign them a password.
and give them privilages..
--
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: administrtor-user
Hi
First,
shell useradd mysql
Changing UserName and Group name for data directory
shell chown -R mysql:mysql data
Create Initial database
shell scripts/mysql_install_db --user=mysql
Start the Server
Then to User:
First, use the mysql program to connect to the server as the MySQL root user:
shell mysql --user=root mysql
1. After connecting to the server as root, you can add new accounts. The
following statements use
GRANT to set up four new accounts:
mysql GRANT ALL PRIVILEGES ON *.* TO 'monty'@'localhost' IDENTIFIED BY
'some_pass' WITH GRANT OPTION;
[OR]
mysql GRANT ALL PRIVILEGES ON *.* TO 'monty'@'%' IDENTIFIED BY
'some_pass' WITH GRANT OPTION;
2. mysql INSERT INTO user (Host,User,Password) VALUES('localhost','UN','PWD');
Then to Change Password:
1. Passwords may be assigned from the command line by using the mysqladmin
command:
shell mysqladmin -u user_name -h host_name password newpwd
2. Another way to assign a password to an account is to issue a SET PASSWORD
statement:
mysql SET PASSWORD FOR 'jeffrey'@'%' = PASSWORD('biscuit');
3. mysql SET PASSWORD = PASSWORD('biscuit');
To Drop user:
1. mysql DELETE FROM user WHERE User='' and HOST=''
Thanks
ViSolve DB Team.
- Original Message -
From: Renish [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Monday, October 23, 2006 9:30 AM
Subject: administrtor-user
Can u guys tell me..
How can I be the administrator for the mysql and thenI I need to create
users x, y,z and assign them a password.
and give them privilages..
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: administrtor-user
Hi,
On which platform?
It do good in Linux and Unix.
Thanks
ViSolve DB Team.
- Original Message -
From: Renish [EMAIL PROTECTED]
To: Visolve DB Team [EMAIL PROTECTED]; mysql@lists.mysql.com
Sent: Monday, October 23, 2006 10:05 AM
Subject: Re: administrtor-user
I am using sql 4.1.21. useradd mysql itself is not working
Regards
- Original Message -
From: Visolve DB Team
To: Renish ; mysql@lists.mysql.com
Sent: Monday, October 23, 2006 12:17 PM
Subject: Re: administrtor-user
Hi
First,
shell useradd mysql
Changing UserName and Group name for data directory
shell chown -R mysql:mysql data
Create Initial database
shell scripts/mysql_install_db --user=mysql
Start the Server
Then to User:
First, use the mysql program to connect to the server as the MySQL root
user:
shell mysql --user=root mysql
1. After connecting to the server as root, you can add new accounts. The
following statements use
GRANT to set up four new accounts:
mysql GRANT ALL PRIVILEGES ON *.* TO 'monty'@'localhost' IDENTIFIED
BY 'some_pass' WITH GRANT OPTION;
[OR]
mysql GRANT ALL PRIVILEGES ON *.* TO 'monty'@'%' IDENTIFIED BY
'some_pass' WITH GRANT OPTION;
2. mysql INSERT INTO user (Host,User,Password)
VALUES('localhost','UN','PWD');
Then to Change Password:
1. Passwords may be assigned from the command line by using the mysqladmin
command:
shell mysqladmin -u user_name -h host_name password newpwd
2. Another way to assign a password to an account is to issue a SET
PASSWORD statement:
mysql SET PASSWORD FOR 'jeffrey'@'%' = PASSWORD('biscuit');
3. mysql SET PASSWORD = PASSWORD('biscuit');
To Drop user:
1. mysql DELETE FROM user WHERE User='' and HOST=''
Thanks
ViSolve DB Team.
- Original Message -
From: Renish [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Monday, October 23, 2006 9:30 AM
Subject: administrtor-user
Can u guys tell me..
How can I be the administrator for the mysql and thenI I need to create
users x, y,z and assign them a password.
and give them privilages..
--
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: bin-log with expire_logs_days
Hi, The system variable expire_logs_days removes the binary logs automatically after the given number of days. The default is 0, which means no automatic removal. Possible removals happen at startup and at binary log rotation. For transactions, it never causes rotation instead it writes to memory cache. The Autocommit statement and HAVE_REPLICATION symbol have impact over expire_logs_days. As of our understanding, for transactions, if log file size as 100MB, and once it get filled, if thre any new log commit, then the log files content will be removed from begining until the required size is obtained and the new log is appended at the end (FIFO). For more information on this variable, http://bugs.mysql.com/bug.php?id=15580 http://bugs.mysql.com/bug.php?id=7236 Thanks ViSolve DB Team. - Original Message - From: George Law [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, October 19, 2006 12:16 AM Subject: bin-log with expire_logs_days Hi All, I have a **high traffic** mysql 4.0.18-standard-log server running with bin-logging enabled. Right now, this must be using a default setting for expire_log_days. I do not see this anyway in show variables or show status $ echo show variables | sql |grep bin binlog_cache_size 32768 log_bin ON max_binlog_cache_size 4294967295 max_binlog_size 1073741824 # echo show status | sql |grep bin Com_show_binlog_events 0 Com_show_binlogs9 Right now, I have 132 bin-logs, each at 1 GB. the logs go back to 2/11/2006 If I were to add 'expire_logs_days 45' to my.cnf and restart mysql, is mysql going to attempt to purge the logs 45 days old and if so... how long does it typically take. We cannot afford to restart if its going to take any significant amount of time for it to purge the logs and restart. thanks! George Law [EMAIL PROTECTED] MSN: [EMAIL PROTECTED] Phone: 864-678-3161 -- 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]
Fw: bin-log with expire_logs_days
Hi, For Info about the 'expire-logs-days' bug fix and new release, http://www.developertutorials.com/mysql-manual/manual_News.html Thanks ViSolve DB Team. - Original Message - From: Visolve DB Team [EMAIL PROTECTED] To: George Law [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Thursday, October 19, 2006 4:00 PM Subject: Re: bin-log with expire_logs_days Hi, The system variable expire_logs_days removes the binary logs automatically after the given number of days. The default is 0, which means no automatic removal. Possible removals happen at startup and at binary log rotation. For transactions, it never causes rotation instead it writes to memory cache. The Autocommit statement and HAVE_REPLICATION symbol have impact over expire_logs_days. As of our understanding, for transactions, if log file size as 100MB, and once it get filled, if thre any new log commit, then the log files content will be removed from begining until the required size is obtained and the new log is appended at the end (FIFO). For more information on this variable, http://bugs.mysql.com/bug.php?id=15580 http://bugs.mysql.com/bug.php?id=7236 Thanks ViSolve DB Team. - Original Message - From: George Law [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, October 19, 2006 12:16 AM Subject: bin-log with expire_logs_days Hi All, I have a **high traffic** mysql 4.0.18-standard-log server running with bin-logging enabled. Right now, this must be using a default setting for expire_log_days. I do not see this anyway in show variables or show status $ echo show variables | sql |grep bin binlog_cache_size 32768 log_bin ON max_binlog_cache_size 4294967295 max_binlog_size 1073741824 # echo show status | sql |grep bin Com_show_binlog_events 0 Com_show_binlogs9 Right now, I have 132 bin-logs, each at 1 GB. the logs go back to 2/11/2006 If I were to add 'expire_logs_days 45' to my.cnf and restart mysql, is mysql going to attempt to purge the logs 45 days old and if so... how long does it typically take. We cannot afford to restart if its going to take any significant amount of time for it to purge the logs and restart. thanks! George Law [EMAIL PROTECTED] MSN: [EMAIL PROTECTED] Phone: 864-678-3161 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOCK TABLES
Hi
From the analysis of other sources,
The error may be due to:
1. MediaWiki was updated from an older version without updating the database.
so to update the database, you can use either the maintenance script
maintenance/update.php via the command line, or the web installer (rename
LocalSettings.php, then go to the wiki).
2. --opt is enabled by default with mysqldump, and part of what it does it
lock tables. So try the backup without lock tables, by adding
--skip-lock-tables.
Thanks
ViSolve DB Team.
- Original Message -
From: mdpeters [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Monday, October 16, 2006 9:14 PM
Subject: LOCK TABLES
I am having a devil of a time moving a database from one server to
another. My database is one that is in production to support the
mediawiki wiki application. This is a Solaris Sparc 10 system using the
mysql-max-5.0.20a-solaris10-sparc version. My database name is horsewiki.
I execute this:
# mysqldump --user root --password=password horsewiki horsewiki.sql
and get this:
mysqldump: Got error: 1146: Table 'horsewiki.archive' doesn't exist when
using LOCK TABLES
I have tried using phpMyAdmin-2.9.0.2. It seems to let me export the
database to an SQL file. When I attempt to import it on the other
system, I get this:
Error
There seems to be an error in your SQL query. The MySQL server error
output below, if there is any, may also help you in diagnosing the problem
ERROR: Unknown Punctuation String @ 494
STR: /
SQL: -- phpMyAdmin SQL Dump
-- version 2.9.0.2
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Oct 16, 2006 at 10:00 AM
-- Server version: 5.0.20
-- PHP Version: 5.1.6
--
-- Database: `horsewiki`
--
!DOCTYPE html PUBLIC -//W3C//DTD XHTML 1.0 Transitional//EN
http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd;
html xmlns=http://www.w3.org/1999/xhtml; xml:lang=en lang=en
dir=ltr
head
link rel=icon href=./favicon.ico type=image/x-icon /
link rel=shortcut icon href=./favicon.ico type=image/x-icon /
titlephpMyAdmin/title
meta http-equiv=Content-Type content=text/html; charset=utf-8 /
link rel=stylesheet type=text/css
href=./css/phpmyadmin.css.php?token=7c73a56802fc2e39fe41180d721amp;js_frame=right
/
link rel=stylesheet type=text/css
href=./css/print.css?token=7c73a56802fc2ee8b4239fe721 media=print /
script type=text/javascript language=javascript
// ![CDATA[
// Updates the title of the frameset if possible (ns4 does not allow
this)
if (typeof(parent.document) != 'undefined'
typeof(parent.document) != 'unknown'
typeof(parent.document.title) == 'string') {
parent.document.title = 'www.lazarusalliance.com / localhost /
horsewiki / archive | phpMyAdmin 2.9.0.2';
SQL query:
-- phpMyAdmin SQL Dump -- version 2.9.0.2 -- http://www.phpmyadmin.net
-- -- Host: localhost -- Generation Time: Oct 16, 2006 at 10:00 AM --
Server version: 5.0.20 -- PHP Version: 5.1.6 -- -- Database: `horsewiki` --
I have not found a good source to understand what the problem might be.
It does not help that I am such a greenhorn with databases either. I
would appreciate any assistance.
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: result set on prepared statements
Hi, Hope this link will be useful: http://mirrors.sunsite.dk/mysql/doc/refman/5.1/en/c-api-prepared-statement-functions.html Thanks ViSolve DB Team - Original Message - From: Roland Volkmann [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Sunday, October 15, 2006 5:59 PM Subject: result set on prepared statements Hello all, using MySQL C API function |mysql_query() with a query producing a result set, I have to fetch *all* records, if I use ||mysql_use_result() to avoid a client side cursor. So it's written in the manual section |22.2.3.70. But if I want to use prepared statements with MySQL C API functions |mysql_stmt_prepare(), ||mysql_stmt_execute() and ||mysql_stmt_fetch(), I can't find anything in the manual, whether I also have to fetch *all* records, when not using ||mysql_stmt_store_result() (I don't want to use client side cursor). MySQL Version is 5.0.26 on Windows 32 Bit. Any Information is welcome. With best regards, Roland. | -- 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: boolean search on phrase*
Hi
The Boolen Search will itself satisfy your query. If you enclose the phrase
within double quote (''), then the characters matches only rows that contain
the phrase literally, as it was typed.
Try removing quotes.
Thanks
ViSolve DB Team.
- Original Message -
From: C.R.Vegelin [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Thursday, October 12, 2006 2:42 PM
Subject: boolean search on phrase*
Hi All,
I want a boolean search on a phrase.
For example on olive oil, but it should return also olive oils etc.
Now I use the following:
SELECT Description FROM products
WHERE MATCH (Description ) AGAINST('olive oil' IN BOOLEAN MODE);
This works fine, but it does NOT return rows with olive oils.
I tried the following:
... MATCH (Description ) AGAINST('olive oil*' IN BOOLEAN MODE);
but that doesn't work.
Any idea will be appreciated.
TIA, Cor
Re: boolean search on phrase*
Hi,
Try with + and * fulltext boolean operators.
For instance,
MATCH (Description) AGAINST('+olive oil*' IN BOOLEAN MODE)
Thanks,
ViSolve DB Team.
- Original Message -
From: C.R.Vegelin [EMAIL PROTECTED]
To: Visolve DB Team [EMAIL PROTECTED]; mysql@lists.mysql.com
Sent: Thursday, October 12, 2006 4:08 PM
Subject: Re: boolean search on phrase*
Thanks ViSolve,
So far I have tried the next alternatives, not giving me what I need:
a) ... MATCH (Description) AGAINST('olive oil' IN BOOLEAN MODE)
giving only olive oil but not olive oils
b) ... MATCH (Description) AGAINST('olive oil' IN BOOLEAN MODE)
giving olive oil and olive oils BUT also fatty oils ... (excl.
olive)
c) ... MATCH (Description) AGAINST(olive oil IN BOOLEAN MODE)
giving the same results as b)
Any more ideas ?
TIA, Cor
- Original Message -
From: Visolve DB Team
To: C.R.Vegelin ; mysql@lists.mysql.com
Sent: Thursday, October 12, 2006 9:54 AM
Subject: Re: boolean search on phrase*
Hi
The Boolen Search will itself satisfy your query. If you enclose the
phrase within double quote (''), then the characters matches only rows that
contain the phrase literally, as it was typed.
Try removing quotes.
Thanks
ViSolve DB Team.
- Original Message -
From: C.R.Vegelin [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Thursday, October 12, 2006 2:42 PM
Subject: boolean search on phrase*
Hi All,
I want a boolean search on a phrase.
For example on olive oil, but it should return also olive oils etc.
Now I use the following:
SELECT Description FROM products
WHERE MATCH (Description ) AGAINST('olive oil' IN BOOLEAN MODE);
This works fine, but it does NOT return rows with olive oils.
I tried the following:
... MATCH (Description ) AGAINST('olive oil*' IN BOOLEAN MODE);
but that doesn't work.
Any idea will be appreciated.
TIA, Cor
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How do i initialize a new datadir in mysql ?
Hi, Create the initial databases and start the database with the following commands: shell mysql_install_db --datadir=/var/lib/mysql2 shell mysqld_safe --datadir=/val/lib/mysql2 Thanks ViSolve DB Team. - Original Message - From: Low Kian Seong [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, October 13, 2006 10:11 AM Subject: How do i initialize a new datadir in mysql ? Dear all, If I am already have a datadir in /var/lib/mysql and I intend to start a new one in /var/lib/mysql2, how do i do it ? Thanks in advance, Low Kian Seong -- 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: Counting char in a column
Hi,
MySQL dosen't have built-in function for counting substring. But we can
create user-defined functions for this.
Like,
CREATE FUNCTION substrCount(x varchar(255), delim varchar(12)) returns int
return (length(x)-length(REPLACE(x, delim, '')))/length(delim);
Then try,
SELECT substrCount('Characteristics', 'c') as count;
which returns 3.
For more reference
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html
Thanks,
ViSolve DB Team.
- Original Message -
From: Scott Hamm [EMAIL PROTECTED]
To: 'Mysql ' mysql@lists.mysql.com
Sent: Tuesday, October 10, 2006 10:58 PM
Subject: Counting char in a column
I'm running MySQL 5.0.15 on Windows system.
How do I count how many specific char is there in a column, for example
finding 'c' in lowercase string of Characteristics would total to 3.
--
`Twas brillig, and the slithy toves
Did gyre and gimble in the wabe:
All mimsy were the borogoves,
And the mome raths outgrabe.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Charset for SELECT ... INTO OUTFILE
Hi, User can also specify character set during the SELECT operation also with CHARACTER SET or charset. Use mysqlcharset utf 8 before issuing SELECT query. For Instance: If you do not say SET NAMES or SET CHARACTER SET, then for SELECT column1 FROM t, the server sends back all the values for column1 using the character set that the client specified when it connected, specified in the cnf file. On the other hand, if you say SET NAMES 'latin1' or SET CHARACTER SET latin1 before issuing the SELECT statement, the server converts the latin2 values to latin1 just before sending results back. Conversion may be lossy if there are characters that are not in both character sets. Ref: http://dev.mysql.com/doc/refman/5.0/en/charset-connection.html Thanks ViSolve DB Team. - Original Message - From: [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, October 09, 2006 2:23 PM Subject: Charset for SELECT ... INTO OUTFILE Hi, I have a database where the database character set is utf-8 and some rows are ascii. I want to save the results of some queries, and SELECT ... INTO OUTFILE looks like an easy way to do it. But I need the output in ucs-2. Is there any way to specify the charset for SELECT ... INTO OUTFILE, or is it always uses the database character set? Thx. ImRe P.S.: Ver 5.0.24a-community-log -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Help plss
Hi The .err file shows that the service is stopped Normally and no error was found. sure the service is properly shutdown. Try, a) net stop mysql mysqld-nt remove mysqld-nt install net start mysql b) Also run 'services.msc' and double click on the mysql service, make sure its comming from C:\\www\mysql\bin Thanks ViSolve DB Team. - Original Message - From: Renish To: Visolve DB Team ; mysql@lists.mysql.com Sent: Tuesday, October 10, 2006 10:07 AM Subject: Fw: Query Help plss - Original Message - From: Renish To: Visolve DB Team ; mysql@lists.mysql.com Sent: Tuesday, October 10, 2006 12:35 PM Subject: Fw: Query Help plss - Original Message - From: Renish To: mysql@lists.mysql.com ; Visolve DB Team Sent: Tuesday, October 10, 2006 12:34 PM Subject: Re: Query Help plss b) And what happend when you open cmd.exe and run. mysql -u root- It shows cant connect to local host error 10061 *.error file shows this InnoDB: The first specified data file .\ibdata1 did not exist: InnoDB: a new database to be created! 060717 10:32:22 InnoDB: Setting file .\ibdata1 size to 10 MB InnoDB: Database physically writes the file full: wait... 060717 10:32:23 InnoDB: Log file .\ib_logfile0 did not exist: new to be created InnoDB: Setting log file .\ib_logfile0 size to 10 MB InnoDB: Database physically writes the file full: wait... 060717 10:32:23 InnoDB: Log file .\ib_logfile1 did not exist: new to be created InnoDB: Setting log file .\ib_logfile1 size to 10 MB InnoDB: Database physically writes the file full: wait... InnoDB: Doublewrite buffer not found: creating new InnoDB: Doublewrite buffer created InnoDB: Creating foreign key constraint system tables InnoDB: Foreign key constraint system tables created 060717 10:32:24 InnoDB: Started; log sequence number 0 0 C:\Program Files\MySQL\MySQL Server 4.1\bin\mysqld-nt: ready for connections. Version: '4.1.20-community-nt' socket: '' port: 3306 MySQL Community Edition (GPL) 060717 13:47:31 [Note] C:\Program Files\MySQL\MySQL Server 4.1\bin\mysqld-nt: Normal shutdown 061009 15:02:37 InnoDB: Starting shutdown... 061009 15:02:42 InnoDB: Shutdown completed; log sequence number 0 543205221 061009 15:02:42 [Note] C:\Program Files\MySQL\MySQL Server 4.1\bin\mysqld-nt: Shutdown complete 061009 15:03:46 InnoDB: Started; log sequence number 0 543205221 C:\Program Files\MySQL\MySQL Server 4.1\bin\mysqld-nt: ready for connections. Version: '4.1.21-community-nt' socket: '' port: 3306 MySQL Community Edition (GPL) 061010 11:58:13 [Note] C:\Program Files\MySQL\MySQL Server 4.1\bin\mysqld-nt: Normal shutdown 061010 11:58:13 InnoDB: Starting shutdown... 061010 11:58:15 InnoDB: Shutdown completed; log sequence number 0 543205231 061010 11:58:15 [Note] C:\Program Files\MySQL\MySQL Server 4.1\bin\mysqld-nt: Shutdown complete - Original Message - From: Visolve DB Team To: Renish Sent: Tuesday, October 10, 2006 12:16 PM Subject: Re: Query Help plss Hi, a) check the file /mysql/data/*.err file and what it shows. b) And what happend when you open cmd.exe and run. mysql -u root Thanks, ViSolve DB Team. - Original Message - From: Renish [EMAIL PROTECTED] To: Visolve DB Team [EMAIL PROTECTED] Sent: Tuesday, October 10, 2006 9:17 AM Subject: Re: Query Help plss - Original Message - From: Visolve DB Team [EMAIL PROTECTED] To: Renish koshy [EMAIL PROTECTED] Sent: Tuesday, October 10, 2006 11:42 AM Subject: Re: Query Help plss Hi, On which platform? Thanks ViSolve DB Team. - Original Message - From: Renish koshy [EMAIL PROTECTED] To: mysql@lists.mysql.com; [EMAIL PROTECTED] Sent: Tuesday, October 10, 2006 8:03 AM Subject: Query Help plss Hi all, I have installed MySql 4.1 in my system. when I tried to run Mysql , I always gets this error. Could not start MySql service on a local computer Error 1067: the process terminated unexpectedly. Any help is highly appreciated. Regards, Renish
Re: Query Help plss
Hi, Hope this link will you. http://mysql.binarycompass.org/doc/refman/4.1/en/can-not-connect-to-server.html Thanks, ViSolve DB Team. - Original Message - From: Visolve DB Team [EMAIL PROTECTED] To: Renish [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Tuesday, October 10, 2006 11:03 AM Subject: Re: Query Help plss Hi The .err file shows that the service is stopped Normally and no error was found. sure the service is properly shutdown. Try, a) net stop mysql mysqld-nt remove mysqld-nt install net start mysql b) Also run 'services.msc' and double click on the mysql service, make sure its comming from C:\\www\mysql\bin Thanks ViSolve DB Team. - Original Message - From: Renish To: Visolve DB Team ; mysql@lists.mysql.com Sent: Tuesday, October 10, 2006 10:07 AM Subject: Fw: Query Help plss - Original Message - From: Renish To: Visolve DB Team ; mysql@lists.mysql.com Sent: Tuesday, October 10, 2006 12:35 PM Subject: Fw: Query Help plss - Original Message - From: Renish To: mysql@lists.mysql.com ; Visolve DB Team Sent: Tuesday, October 10, 2006 12:34 PM Subject: Re: Query Help plss b) And what happend when you open cmd.exe and run. mysql -u root- It shows cant connect to local host error 10061 *.error file shows this InnoDB: The first specified data file .\ibdata1 did not exist: InnoDB: a new database to be created! 060717 10:32:22 InnoDB: Setting file .\ibdata1 size to 10 MB InnoDB: Database physically writes the file full: wait... 060717 10:32:23 InnoDB: Log file .\ib_logfile0 did not exist: new to be created InnoDB: Setting log file .\ib_logfile0 size to 10 MB InnoDB: Database physically writes the file full: wait... 060717 10:32:23 InnoDB: Log file .\ib_logfile1 did not exist: new to be created InnoDB: Setting log file .\ib_logfile1 size to 10 MB InnoDB: Database physically writes the file full: wait... InnoDB: Doublewrite buffer not found: creating new InnoDB: Doublewrite buffer created InnoDB: Creating foreign key constraint system tables InnoDB: Foreign key constraint system tables created 060717 10:32:24 InnoDB: Started; log sequence number 0 0 C:\Program Files\MySQL\MySQL Server 4.1\bin\mysqld-nt: ready for connections. Version: '4.1.20-community-nt' socket: '' port: 3306 MySQL Community Edition (GPL) 060717 13:47:31 [Note] C:\Program Files\MySQL\MySQL Server 4.1\bin\mysqld-nt: Normal shutdown 061009 15:02:37 InnoDB: Starting shutdown... 061009 15:02:42 InnoDB: Shutdown completed; log sequence number 0 543205221 061009 15:02:42 [Note] C:\Program Files\MySQL\MySQL Server 4.1\bin\mysqld-nt: Shutdown complete 061009 15:03:46 InnoDB: Started; log sequence number 0 543205221 C:\Program Files\MySQL\MySQL Server 4.1\bin\mysqld-nt: ready for connections. Version: '4.1.21-community-nt' socket: '' port: 3306 MySQL Community Edition (GPL) 061010 11:58:13 [Note] C:\Program Files\MySQL\MySQL Server 4.1\bin\mysqld-nt: Normal shutdown 061010 11:58:13 InnoDB: Starting shutdown... 061010 11:58:15 InnoDB: Shutdown completed; log sequence number 0 543205231 061010 11:58:15 [Note] C:\Program Files\MySQL\MySQL Server 4.1\bin\mysqld-nt: Shutdown complete - Original Message - From: Visolve DB Team To: Renish Sent: Tuesday, October 10, 2006 12:16 PM Subject: Re: Query Help plss Hi, a) check the file /mysql/data/*.err file and what it shows. b) And what happend when you open cmd.exe and run. mysql -u root Thanks, ViSolve DB Team. - Original Message - From: Renish [EMAIL PROTECTED] To: Visolve DB Team [EMAIL PROTECTED] Sent: Tuesday, October 10, 2006 9:17 AM Subject: Re: Query Help plss - Original Message - From: Visolve DB Team [EMAIL PROTECTED] To: Renish koshy [EMAIL PROTECTED] Sent: Tuesday, October 10, 2006 11:42 AM Subject: Re: Query Help plss Hi, On which platform? Thanks ViSolve DB Team. - Original Message - From: Renish koshy [EMAIL PROTECTED] To: mysql@lists.mysql.com; [EMAIL PROTECTED] Sent: Tuesday, October 10, 2006 8:03 AM Subject: Query Help plss Hi all, I have installed MySql 4.1 in my system. when I tried to run Mysql , I always gets this error. Could not start MySql service on a local computer Error 1067: the process terminated unexpectedly. Any help is highly appreciated. Regards, Renish -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to skip reading /etc/my.cnf by mysqld
Hi, Try, libexec/mysqld --verbose --help for mysqld options with variables or bin/mysqld_safe --verbose --help for mysqld options For instance I want only /etc/my.cnf read and skip reading of other default file, ./bin/mysqld_safe --defaults-file=/etc/my.cnf Hope this will do good. Thanks ViSolve DB Team. - Original Message - From: [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Saturday, October 07, 2006 11:41 AM Subject: How to skip reading /etc/my.cnf by mysqld Hi: I am trying to test mysql options and would like to have mysqld only read ~/.my.cnf instead of using the normal sequence to read /etc/my.cnf $datadir/my.cnf and ~/.my.cnf. This would prevent many unexpected issues in testing. But I could not find the way to disable the /etc/my.cnf. Any help would be appreciated. Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication
Hi --replicate-wild-do-table=db_name.tbl_name replicates only updates that use the specified table in the given database. If any wild cald patterns specified, then match the specified database and table name patterns. Tells the slave thread to restrict replication to statements where any of the updated tables match the specified database and table name patterns. --replicate-wild-ignore-table=db_name.tbl_name Tells the slave thread not to replicate a statement where any table matches the given wildcard pattern. So to replicate only the merge_t table, try a) set-variable= binlog-do-db=DatabaseB set-variable= replicate-wild-do-table=DatabaseB.merge_t or to replicate all the tables except merge_t b) set-variable = binlog-do-db=DatabaseB set-variable = replicate-wild-do-table=DatabaseB.% set-variable = replicate-wild-ignore-table=DatabaseB.merge_t Ref: http://www.mysqlpress.com/doc/refman/5.0/en/replication-options.html Thanks ViSolve DB Team. - Original Message - From: Eric Anderson [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, October 06, 2006 4:15 AM Subject: Replication I'm having a problem with my replication settings in my.cnf. I know it's probably something stupid. I've got an existing database configured to replicate as follows: set-variable= binlog-do-db=DatabaseA set-variable= replicate-do-table=DatabaseA.foo_t and that replicates that one single table just fine. I'm trying to replicate a single table from another database: set-variable= binlog-do-db=DatabaseB set-variable= replicate-do-table=DatabaseB.bar_t but nothing gets replicated. There is a MRG table in DatabaseB though, but adding the following: set-variable= binlog-do-db=DatabaseB set-variable= replicate-do-table=DatabaseB.bar_t set-variable= replicate-ignore-table=DatabaseB.merge_t doesn't help. The following works though: set-variable= binlog-do-db=DatabaseB set-variable= replicate-wild-do-table=DatabaseB.% set-variable= replicate-ignore-table=DatabaseB.merge_t but that replicates all tables except for the MRG table and I'd rather not replicate all the tables. Any ideas? -- WANT TO MODEL FOR MAC BUMBLE? APPLY AT http://casting.macandbumble.com - Eric Anderson Mac and Bumble Bumble Cash ICQ 3849549 http://www.macandbumble.com http://www.bumblecash.com San Diego, CA[EMAIL PROTECTED] [EMAIL PROTECTED] - SEE OUR LATEST PARTY PICTURES -- http://events.macandbumble.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can't set password for user
Hi,
Check for the user in the 'User' Table of 'mysql' database. If there exists
'gamito' User in the User table, then set password will surely do.
Verify:
mysqluse mysql;
mysqlselect * from user;
Thanks
ViSolve DB Team.
- Original Message -
From: Deckard [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Monday, October 02, 2006 3:04 AM
Subject: Can't set password for user
Hello,
I'm trying to set a password for a user with the command:
mysql set password for 'gamito'@'localhost'=password('bla');
but i always get the error:
ERROR 1133 (42000): Can't find any matching row in the user table
although i'm pretty sure that he user exists.
I'm using MySQl version 4.1.20
Any help would be appreciated.
Best Regards,
Deckard
--
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: Transactions in MySQL.
Hi, All locking in *MySQL* is deadlock-free. This is managed by always requesting all needed locks at once at the beginning of a query and always locking the tables in the same order. The --external-locking and --skip-external-locking options explicitly enable and disable external locking. The LOCK TABLES and UNLOCK TABLES statements use internal locking, so you can use them even if external locking is disabled. A call to LOCK TABLES tries to lock any tables you list so that the current thread can work with it without interference. A call to UNLOCK TABLES releases any locks that this thread holds.There are two main types of locks: read and write. When a lock is released, the lock is made available to the threads in the write lock queue, then to the threads in the read lock queue. External locking is a part of configuration and Internal locking is a part of query. Thanks ViSolve DB Team - Original Message - From: [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, September 25, 2006 11:42 AM Subject: Transactions in MySQL. Hi All, How transactions and locking are handled in MYSQL? Is it a part of configuration? Or a query (lock tables, Unlock tables) for each set of queries? Regards, Ravi K The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Urgent: parameter of my_hash_sort_simple
Hello, Could anyone explain me the parameter of the function my_hash_sort_simple in MySQL 5.0.20a. Which is called under strings/ctype-simple.c. Thanks, Prem
Re: Help with query
Hi, Try with FULLTEXT search. Alter the table to make the search columns as FULLTEXT columns, with MyISAM engine and retrieve the records using MATCH keyword. Ref:http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html Thanks, ViSolve DB Team. - Original Message - From: Ed Curtis [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, September 25, 2006 11:47 PM Subject: Help with query I'm trying to do a keyword search within a phrase saved in a table. Here's the query: SELECT * from closedtickets WHERE keyphrase LIKE '%$keyword1%' OR keyphrase LIKE '%$keyword2%' OR keyphrase LIKE '%$keyword3%' The problem I'm having is that the query is returning every record in the table. I only want it to return the records where the keywords (any combination) are contained within 'keyphrase' Any ideas? Thanks, Ed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Last Inserted ID Using LOAD DATA
Hi, Your query was understood as - you want to retrieve the current value of the autoincrement column, where mysql_insert_id also reveals the same. Try either of this. Use LIMIT: select ID from tablename order by ID DESC LIMIT 1. or Use MAX() select MAX(ID) from tablename Thanks ViSolve DB Team. - Original Message - From: Michael Stearne [EMAIL PROTECTED] To: mysql@lists.mysql.com Cc: Jim Ginn [EMAIL PROTECTED] Sent: Tuesday, September 19, 2006 10:11 PM Subject: Last Inserted ID Using LOAD DATA I have a group of updates that need to be done using LOAD DATA INFILE. Within this file there are some INSERTS. Is there anyway that after an INSERT happens I can use the auto-increment ID that was just generated in the next statement. Similar to PHP's mysql_insert_id() function. Thanks, Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FIND DATA location
Hi, From the mysql itself try this -- mysql show variables; This will list data_home_dir path, arch_dir path, group_home_dir path,pid_file path, socket path etc. along with other variables. Thanks ViSolve DB Team. - Original Message - From: Hal Wigoda [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, September 21, 2006 7:19 PM Subject: FIND DATA location how do you find where mysql data resides? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: What mysql 5.0 binary relase use for CentOS 4.4 Pentium?
Hi, If you get Sorry, the host '' could not be looked up error message when you run mysql_install_db, or if you get the getpwnam: No such file or directory error message while running mysqld with --user option, Then try any of these solutions: a.. Get a MySQL source distribution either an RPM or the tar.gz distribution and install this instead. b.. Execute mysql_install_db --force; This will not execute the resolveip test in mysql_install_db. The downside is that you can't use host names in the grant tables; you must use IP numbers instead of host names except for localhost. If you are using an old MySQL release that doesn't support --force, you have to remove the resolveip test in mysql_install with an editor. c.. Start mysqld with su instead of using --user. Thanks ViSolve DB Team. - Original Message - From: [EMAIL PROTECTED] To: MySql Mail List mysql@lists.mysql.com Sent: Thursday, September 21, 2006 9:27 PM Subject: What mysql 5.0 binary relase use for CentOS 4.4 Pentium? Hi. What binary relase use for CentOS 4.4: * Linux (x86, glibc-2.2, standard is static, gcc): mysql-standard-5.0.24a-linux-i686.tar.gz * Linux (x86): mysql-standard-5.0.24a-linux-i686-glibc23.tar.gz In http://dev.mysql.com/doc/refman/5.0/en/binary-notes-linux.html says that it is possible that mysql couldn't resolves ips for hostnames. First I install mysql-standard-5.0.24a-linux-i686-glibc23.tar.gz and executing mysql_install_db it says: Sorry, the host '' could not be looked up Then I install mysql-standard-5.0.24a-linux-i686.tar.gz and says the same when I execute mysql_install_db. Sorry, the host '' could not be looked up Aside from not could use hostnames what from these releases is best for me? Iago.
Re: Null !?
hi, You can use DBNull. DBNull - First it returns true if such a value is null, then converts the value to an empty string if it is null. For instance, If dbval Is DBNull.value then return End If Thanks ViSolve DB Team. - Original Message - From: Roberto William Aranda-W Roman [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, September 21, 2006 12:03 AM Subject: Null !? hello anybody knows how i should interpret the (null) value in a field in order to visual.net can recognize as just null and then make desicions? tanks a lot
Re: Moving database to another machine
Hi, Try, Compress - [Mac A] shell mysqldump -all -databases |gzip dbfilename.sql.gz UnCompress [Mac B] shell gunzip dbfilename.sql.gz | mysql -u user -p db Thanks ViSolve DB Team. - Original Message - From: Ratheesh K J [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, September 12, 2006 12:13 PM Subject: Moving database to another machine Hlo all, I wanted to move around 50 GB of data on Machine A to Machine B. Both the machines are of same architecture ( LAMP ) Dumping and restoring takes a lot of time. Is there a faster method to accomplish the same? Is there a way to tar the whole thing and untar on Machine B? or any other way? Ratheesh Bhat K J -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INNODB my.cnf
Hi, Attached is the sample my.cnf for Innodb engine type. Thanks, ViSolve DB Team. - Original Message - From: [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, September 08, 2006 7:31 PM Subject: INNODB my.cnf Hi All, I need some inputs regarding my.cnf : We are using INNODB in our application.We have around 10 million records in the database. This will size up to around 10GB of data. Could you please suggest a sample my.cnf for this configuration. Machine used : Sun netra 240 , dual processor machine with 2 GB ram Mysql version : 5.1.11 Regards Prasad The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com # Example MySQL config file for medium systems. # # This is for a system with little memory (32M - 64M) where MySQL plays # an important part, or systems up to 128M where MySQL is used together with # other programs (such as a web server) # # You can copy this file to # /etc/my.cnf to set global options, # mysql-data-dir/my.cnf to set server-specific options (in this # installation this directory is /var/lib/mysql) or # ~/.my.cnf to set user-specific options. # # In this file, you can use all long options that a program supports. # If you want to know which options a program supports, run the program # with the --help option. # The following options will be passed to all MySQL clients [client] #password = your_password port= 3306 socket = /var/lib/mysql/mysql.sock # Here follows entries for some specific programs # The MySQL server [mysqld] port= 3306 socket = /var/lib/mysql/mysql.sock default-storage_engine = innodb skip-locking key_buffer = 16M max_allowed_packet = 1M table_cache = 64 sort_buffer_size = 8M net_buffer_length = 8K read_buffer_size = 256K read_rnd_buffer_size = 512K binlog_cache_size = 1M # Don't listen on a TCP/IP port at all. This can be a security enhancement, # if all processes that need to connect to mysqld run on the same host. # All interaction with mysqld must be made via Unix sockets or named pipes. # Note that using this option without enabling named pipes on Windows # (via the enable-named-pipe option) will render mysqld useless! # #skip-networking # Replication Master Server (default) # binary logging is required for replication log-bin=mysql-bin # required unique id between 1 and 2^32 - 1 # defaults to 1 if master-host is not set # but will not function as a master if omitted server-id = 1 # Replication Slave (comment out master section to use this) # # To configure this host as a replication slave, you can choose between # two methods : # # 1) Use the CHANGE MASTER TO command (fully described in our manual) - #the syntax is: # #CHANGE MASTER TO MASTER_HOST=host, MASTER_PORT=port, #MASTER_USER=user, MASTER_PASSWORD=password ; # #where you replace host, user, password by quoted strings and #port by the master's port number (3306 by default). # #Example: # #CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306, #MASTER_USER='joe', MASTER_PASSWORD='secret'; # # OR # # 2) Set the variables below. However, in case you choose this method, then #start replication for the first time (even unsuccessfully, for example #if you mistyped the password in master-password and the slave fails to #connect), the slave will create a master.info file, and any later #change in this file to the variables' values below will be ignored and #overridden by the content of the master.info file, unless you shutdown #the slave server, delete master.info and restart the slaver server. #For that reason, you may want to leave the lines below untouched #(commented) and instead use CHANGE MASTER TO (see above) # # required unique id between 2 and 2^32 - 1 # (and different from the master) # defaults to 2 if master-host is set # but will not function as a slave if omitted #server-id = 2 # # The replication master for this slave - required #master-host = hostname # # The username the slave will use for authentication when connecting # to the master - required #master-user = username # # The password the slave will authenticate with when connecting to # the master - required #master-password = password # # The port the master is listening on. # optional - defaults to 3306 #master-port = port # # binary logging
Re: Restarting MySQL on Solaris 8?
Hi, Try /usr/local/bin/mysqld_safe to start MySQL /usr/local/bin/mysqld_safe to start MySQL as a background process. Thanks. ViSolve DB Team. - Original Message - From: Jay Paulson [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, September 08, 2006 9:51 PM Subject: Restarting MySQL on Solaris 8? A couple questions since I'm not a Solaris person I really don't know how to do the following and was hoping that someone could help me out (Google isn't much help on this). How does one start the MySQL daemon on Solaris 8? (it's running MySQL 3.23.49) How does one tell Solaris 8 to start the MySQL daemon on boot? Lastly, tried running /usr/local/bin/safe_mysqld but got the following error: TIMESTAMP mysqld ended Thanks for any help! -- 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: Restarting MySQL on Solaris 8?
Hi, /usr/local/bin/safe_mysqld must do good with MySQL 3.23.49. The error may be due to either one. a. configuration file set up b. data directory - anything removed/modified - not enough disk space c. log file size Thanks, ViSolve DB Team. - Original Message - From: Visolve DB Team [EMAIL PROTECTED] To: Jay Paulson [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Monday, September 11, 2006 9:35 AM Subject: Re: Restarting MySQL on Solaris 8? Hi, Try /usr/local/bin/mysqld_safe to start MySQL /usr/local/bin/mysqld_safe to start MySQL as a background process. Thanks. ViSolve DB Team. - Original Message - From: Jay Paulson [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, September 08, 2006 9:51 PM Subject: Restarting MySQL on Solaris 8? A couple questions since I'm not a Solaris person I really don't know how to do the following and was hoping that someone could help me out (Google isn't much help on this). How does one start the MySQL daemon on Solaris 8? (it's running MySQL 3.23.49) How does one tell Solaris 8 to start the MySQL daemon on boot? Lastly, tried running /usr/local/bin/safe_mysqld but got the following error: TIMESTAMP mysqld ended Thanks for any help! -- 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: View hidden temporary files
Hi, MySQL creates temporary table to complete the query. However, it doesn't tell you whether that temporary table will be in memory or on disk. MySQL's tmp_table_size variable will control the temporary table size.The default tmp_table_size size is 32 MB Temporary tables can either be in the Disk or in Memory. If the space required to build the temporary table is less than or equal to tmp_table_size, MySQL keeps it in memory rather than incur the overhead and time required to write the data to disk and read it again. However, if the space required exceeds tmp_table_size, MySQL creates a disk-based table in its tmpdir directory (often /tmp on Unix systems.) . mysql SHOW STATUS LIKE 'Created_tmp_%'; To find out how often that happens, compare the relative sizes of the Created_tmp_tables and Created_tmp_disk_tables counters. Thanks, ViSolve DB Team. - Original Message - From: [EMAIL PROTECTED] To: MySql Mail List mysql@lists.mysql.com Sent: Thursday, September 07, 2006 8:00 PM Subject: View hidden temporary files Hi. In http://dev.mysql.com/doc/refman/4.1/en/temporary-files.html says MySQL creates all temporary files as hidden files. This ensures that the temporary files are removed if mysqld is terminated. The disadvantage of using hidden files is that you do not see a big temporary file that fills up the filesystem in which the temporary file directory is located. Is there any form of see the length of the temporary files created by MySQL? Iago.
Re: Understanding Query-Cache math...
Hi The query cache uses variable-length blocks and the Qcache_total_blocks and Qcache_free_blocks may indicate query cache memory fragmentation. After FLUSH QUERY CACHE, only a single free block remains. So the variables query_cache_min_res_unit, query_cache_limit, query_prealloc_size, query_alloc_block_size determines the free and available number of blocks. Every cached query requires a minimum of two blocks - for query text and for the query results. Also, every table that is used by a query requires one block. However, if two or more queries use the same table, only one block needs to be allocated. The information provided by the Qcache_lowmem_prunes status variable can help you tune the query cache size. It counts the number of queries that have been removed from the cache to free up memory for caching new queries. The query cache uses a least recently used (LRU) strategy to decide which queries to remove from the cache. If you have query with the size larger than the query_cache_size then the query is not cached. If you have a lot of queries with small results, the default block size may lead to memory fragmentation, as indicated by a large number of free blocks. Fragmentation can force the query cache to prune (delete) queries from the cache due to lack of memory. In this case, you should decrease the value of query_cache_min_res_unit. The number of free blocks and queries removed due to pruning are given by the values of the Qcache_free_blocks and Qcache_lowmem_prunes status variables. Thanks ViSolve DB Team - Original Message - From: Christian Hammers [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, September 01, 2006 1:52 PM Subject: Understanding Query-Cache math... Hello I don't understand why query_cache_size / query_cache_min_res_unit != Qcache_total_blocks and Qcache_free_memory / query_cache_min_res_unit != Qcache_free_blocks Can anybody enlight me so that I know if I have to increase the Query-Cache or not? mysql SHOW status LIKE Qcache_%; +-+---+ | Variable_name | Value | +-+---+ | Qcache_free_blocks | 10382 | | Qcache_free_memory | 247491776 | | Qcache_hits | 119254865 | | Qcache_inserts | 5412923 | | Qcache_lowmem_prunes| 0 | | Qcache_not_cached | 58724815 | | Qcache_queries_in_cache | 16002 | | Qcache_total_blocks | 42464 | +-+---+ mysql SHOW variables LIKE query_cache_%; +--++ | Variable_name| Value | +--++ | query_cache_limit| 8388608| | query_cache_min_res_unit | 4096 | | query_cache_size | 268435456 | | query_cache_type | ON | | query_cache_wlock_invalidate | OFF| +--++ thanks, -christian- -- Christian Hammers WESTEND GmbH | Internet-Business-Provider Technik CISCO Systems Partner - Authorized Reseller Lütticher Straße 10 Tel 0241/701333-11 [EMAIL PROTECTED]D-52064 Aachen Fax 0241/911879 -- 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: IN ANY subqueries
Hi
The ANY keyword, which must follow a comparison operator, means return TRUE if
the comparison is TRUE for ANY of the values in the column that the subquery
returns
In has 2 forms:
1. IN (subquery). [The word IN is an alias for = ANY (subquery)].
2. IN (list of values seperated by comma)
Hence the exact syntax to use is:
Select fields from table where fieldname = ANY ( select fieldname from
table);
Ref: http://dev.mysql.com/doc/refman/5.0/en/any-in-some-subqueries.html
Thanks
ViSolve DB Team
- Original Message -
From: Ben Lachman [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Sunday, September 03, 2006 10:33 AM
Subject: IN ANY subqueries
I have a string comparison that I would like to do against a short
list of constant strings.
at the moment I am using the syntax
SELECT id FROM t1 WHERE name IN('s1', 's2', 's3', ...);
However, this limits me to exact matches and I'd like to move to a
caparison expersion that lets me match names that contain any of the
list. The MySQL docs state that 'IN()' is an alias to '= ANY()'
however when I substitute' = ANY' for IN I get a parse error. What
I'd like to do is write something like (although I figure there may
be a better way to do the comparison that I am not thinking of):
SELECT id FROM t1 WHERE name LIKE ANY('%s1%', '%s2%', '%s3%', ...);
Does anyone know a way to do this?
Thanks,
-Ben
Re: Drop Index if Exists
Hello Adam,This is the exact syntax to drop the index.DROP INDEX index_name ON tbl_nameThanksVisolve DB Team . - Original Message - From: Adam Lipscombe [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Friday, September 01, 2006 3:28 PM Subject: Drop Index if Exists Folks, Does this work in MYSQL 5? I tried DROP INDEX [NAME] IF EXISTS; and got an error check your syntax. Thanks - Adam -- 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: Group By question
Hello , Another one alternate solution without join SELECT id,count(email) as No ,email FROM email GROUP BY email having count(email)1 Thanks Visolve DB Team. - Original Message - From: Peter Brawley To: Chris W Cc: MYSQL General List Sent: Thursday, August 31, 2006 3:02 AM Subject: Re: Group By question Chris, I would like to query all rows that have more than one person with the same email address. select id,count(emailaddr) as howmany from tbl t1 join tbl t2 using(emailaddr) group by id having howmany1; PB - Chris W wrote: I have a table of people with one of the fields being the email address. I would like to query all rows that have more than one person with the same email address. For example if the data were like this... A [EMAIL PROTECTED] B [EMAIL PROTECTED] C [EMAIL PROTECTED] D [EMAIL PROTECTED] E [EMAIL PROTECTED] F [EMAIL PROTECTED] The query would return row A, D, B, and E, in that order. It would not return C or F -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.405 / Virus Database: 268.11.7/433 - Release Date: 8/30/2006 -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Doing sum's if certain conditions are true
Hello Ian barnes
I hope this query will resolve your problem if my understanding is correct
SELECT code1,
SUM(bytes) as sumofbytes,
MAX(bytes) as maximum,
MIN(bytes) as minimum,
COUNT(bytes) as bytecount,
SUM(duration) as duration,
AVG(bytes) as averagebyte
FROM mytable where code1'none' and code2'denied' group by code1
Thanks
Visolve Db team
- Original Message -
From: Ian Barnes [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Tuesday, August 29, 2006 3:26 PM
Subject: Doing sum's if certain conditions are true
Hi,
I am trying to build a query that does a sum if a certain parameter is
set.
For example:
I have a row with four fields: code1, code2, duration and bytes. My
current
query looks something like this: SELECT code1 as code, sum(bytes) as bin,
max(bytes) as min, avg(bytes) as ain, count(bytes) as cin, sum(duration)
as
dur from data group by code; which returns something like this:
+---+--+---+-+--+-+
| code | bin | min | ain |
cin | dur |
+---+--+---+-+--+-+
| NONE | 103939170759 | 485089817 | 3739.1827 |
27797297 | 11681839027 |
Now, what i need todo is exclude certain info from the above NONE entry if
code2 is equal to something. So for example (in php terminology):
if(code == 'NONE') {
if(code2 == 'DENIED') { continue; }
else {
bin += bytes;
if(bytes min) { min = bytes; }
cin++;
dur += dur;
}
}
after that i could work out the average by dividing bin / cin for what in
the report is called ain. Is there any way of achieving this via the sql
query because the above is a hugely tedious way of doing it. I know mysql
has an if() statement, but I have no idea how to implement it using what i
want to achieve above.
Thanks in advance.
Ian
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ALTER TABLE
Hi, Try this: To modify an existing field: ALTER TABLE doc MODIFY docts timestamp DEFAULT CURRENT_TIMESTAMP; or To add new field ALTER TABLE doc ADD docts timestamp DEFAULT CURRENT_TIMESTAMP; Thanks, ViSolve DB Team. - Original Message - From: Peter Lauri [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Sunday, August 27, 2006 8:27 PM Subject: ALTER TABLE Hi, I am doing this thru the phpmyadmin interface: ALTER TABLE doc ALTER docts SET DEFAULT CURRENT_TIMESTAMP However, it returns #1064 - You have an error in your SQL syntax near 'DEFAULTCURRENT_TIMESTAMP' at line 1 As you can see the error shows that DEFAULT an CURRENT_TIMESTAMP has been written together, strange, or is my syntax in the ALTER TABLE wrong? /Peter
Re: what should be the value of innodb_flush_log_at_trx_commit
Hi, InnoDB provides MySQL with a transaction-safe (ACID compliant) storage engine that has commit, rollback, and crash recovery capabilities. MyISAM, the default storage engine. Unless compiled with InnoDB, MySQL is not ACID compliant. When innodb_flush_log_at_trx_commit =0 the log buffer is written to disk and nothing to be done at transaction commit =1 [default] the log buffer is written to disk on each transaction commit =2 log buffer is written on each commit and no flush performed However, the flushing on the log file to the disk takes place once per second also when the value is 2. You can achieve better performance by setting the value different from 1, but then you can lose at most one second worth of transactions in a crash. If you set the value to 0, then any mysqld process crash can erase the last second of transactions. If you set the value to 2, then only an operating system crash or a power outage can erase the last second of transactions. Ref: http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html Thanks ViSolve DB Team - Original Message - From: Ratheesh K J [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, August 29, 2006 10:16 AM Subject: what should be the value of innodb_flush_log_at_trx_commit hello all, We do not run transactions at all on our db. All our queries are autocommit. So what should be the value set to this variable : innodb_flush_log_at_trx_commit We currently have it set to 1 and all our tables are Innodb. Since we are not running any transactions at all, is it better to set this var to 0 or 2? Thanks, Ratheesh Bhat K J
Re: REPAIR TABLE and mysqlcheck
hi Hope there is no privilege type of REPAIR [Repair Table] supported by MySQL Server. use SHOW PRIVILEGES; SHOW PRIVILEGES shows the list of system privileges that the MySQL server supports. The exact list of privileges depends on the version of your server. For the global, database, table, and routine levels, GRANT ALL assigns only the privileges that exist at the level you are granting. The FILE, PROCESS, RELOAD, REPLICATION CLIENT, REPLICATION SLAVE, SHOW DATABASES, SHUTDOWN, and SUPER privileges are administrative privileges that can only be granted globally . SELECT, INSERT, UPDATE, (both table and column level privileges),DELETE, CREATE, DROP, GRANT OPTION, INDEX, ALTER, CREATE VIEW and SHOW VIEW are Table level privileges and ALTER ROUTINE, EXECUTE, and GRANT OPTION are Routine specific privileges. CREATE ROUTINE is not a routine-level privilege because you must have this privilege to create a routine in the first place. Thanks Visolve DB Team. - Original Message - From: Logan, David (SST - Adelaide) [EMAIL PROTECTED] To: MySQL List mysql@lists.mysql.com Sent: Thursday, August 24, 2006 7:18 AM Subject: REPAIR TABLE and mysqlcheck Hi Folks, Does anybody know if it is possible to restrict access to these commands on an individual basis? I have a need to exclude users, who would otherwise have most privileges, and was wondering if there was a particular privilege that I could revoke. If not, does anybody have a technique that may provide for this? Thanks --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent ---
Re: dates in mysql
hi MySQL automatically converts a date or time type value to a number (numeric context) viceversa. When the server starts, it attempts to determine the time zone of the host machine and uses it to set the system_time_zone system variable. The value does not change thereafter. The global time_zone system variable indicates the time zone the server currently is operating in. The initial value for time_zone is 'SYSTEM', which indicates that the server time zone is the same as the system time zone. Try SELECT @@global.time_zone, @@session.time_zone; SET time_zone = timezone; timezone values can be given as strings indicating an offset from UTC, such as '+10:00' or '-6:00'. If you have a problem with SELECT NOW() returning values in UTC and not your local time, you have to tell the server your current time zone. This should be done for the environment in which the server runs. Thanks ViSolve DB Team - Original Message - From: Chris [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, August 23, 2006 8:11 AM Subject: dates in mysql Hi all, Does anyone know how mysql stores dates? I'm wondering whether it converts it back to UTC before storing it (and back to the client timezone setting when you select) or whether it leaves it as is with the timezone information. So to change to a different timezone I'd have to convert it back to UTC and so on manually.. I've looked through the manual a few times but can't find an answer either way :( Thanks, Chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help needed to mount data from /var/lib/mysql
Hello, You can change the MySQL data path in /etc/my.cnf by editing the configuration parameter datadir with new data path. Once you done the changes in my.cnf, reboot the MySQL server. Thanks, ViSolve DB Team - Original Message - From: balaraju mandala [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Saturday, August 19, 2006 12:28 PM Subject: help needed to mount data from /var/lib/mysql Hi Everybody, I need small help from you. In my Linux box i have limitation of Size in partition. I have only 5 GB space for /var. MySQL is installed in this partition only. I want to give another path like /home, where i have 120GB of space. Somebody told me that we need to change the data path of MySQL, but he is not sure of that. Can anybody knows abt this? No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.405 / Virus Database: 268.11.3/423 - Release Date: 8/18/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Incrementing using Max(Field) ?
Hello William Try the below Query to insert next maximum value of the field into same table INSERT INTO Sample(id) SELECT MAX(id)+1 FROM Sample Thanks Visolve DB Team - Original Message - From: William DeMasi [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, August 15, 2006 12:34 AM Subject: Incrementing using Max(Field) ? Does anyone have any ideas of how I can select the max value and insert the next highest value? I want something that would do something like this: Insert into table1 (select max(field1)+1 from table1); This obviously doesn't work. I know if the table was set to auto-increment it wouldn't be an issue, but I am not able to change its schema. Thank you. - William -- 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: multiple table inserts
Hell Bruce Yes ,Here is the solution to insert the values from multiple table into a table . We can achieve this by using the JOINs(INNER JOIN,LEFT JOIN,RIGHT JOIN, join) Consider the tables Animal,Feed and Animalfeed Structure of the animal table Id name variety Structure of the feed table id Feed Structure of animal table id Name feed I wan to update the table Animalfeed from tables Animal and Feed INSERT INTO Animalfeed SELECT a.id,a.name,b.feed FROM Animal a,feed b WHERE a.id=b.id Note: 1.Column Order of animalfeed table and SELECT QUERY must be SAME Datatype 2.If you want to insert the name and feed details into animalfeed table . The query must be follow below synatx INSERT INTO Animalfeed(name,feed) SELECT a.name,b.feed from Animal a,feed b WHERE a.id=b.id Thanks Visolve DB TEAM. - Original Message - From: bruce [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, August 15, 2006 8:26 PM Subject: multiple table inserts hi... the mysql docs don't seem to allow this.. but i'll ask. is there a way to accomplish: insert into cat,dog (cat.x,dog.y) values (1,2) allowing me to essentially insert items into multiple tables simultaneously... thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: multiple table inserts
Hi Bruce U can you mysql_insert_id function to insert values into multiple tables. Please refer to http://www.desilva.biz/mysql/insertid.html for more details. It also illustrates with examples. Thanks Visolve DB Team - Original Message - From: bruce [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, August 15, 2006 7:56 AM Subject: multiple table inserts hi... the mysql docs don't seem to allow this.. but i'll ask. is there a way to accomplish: insert into cat,dog (cat.x,dog.y) values (1,2) allowing me to essentially insert items into multiple tables simultaneously... thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: refrenceing information thru seperate tables
Hello Brain. Solution is simple Create two table customer table and product table in the below format Definition of the Customer table cust_id Customername Customer address -- -- - etc Productid We are not sure about ur exact business requirement Simply we have given example here that how to link both tables .To do this WE need any one common key field to combine the tables .Here we have taken the key productid as common filed for both tables . Definition of the product table - Product table prodcutiod productname Now apply the below query to get result what you expect that we hope SELECT a.cust_id,a.customername,a.customeraddress,b.productid,b.productname FROM customer a ,product b WHERE a.productid=b.productid . Note :While you join tables please specify any one condtion in WHEER clause to filter resultset otherwise join query returns all recordset from both tables The above query has been written using inner join , This Query returns resultset only the cutomers belongs to particular productid information in the cusstomer table. We hope it is ok for you . Thanks Visolve DB Team. - Original Message - From: Brian E Boothe [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, August 15, 2006 11:17 PM Subject: refrenceing information thru seperate tables HI all ; i wanna be able to link tables bu either id or Customer , and i also want to have a seperate table for Software information on that Customer, so in one table it would be Customer name : and another table would be products, linked to product id, so when i do a Queru it shows both tables information and how they were linked together table customers: idCustomer name table Products Product id : arrrge i need assistancethanks -- 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: multiple table inserts
Hi Bruce What Fred said is true. Only transactions will help you. Regards Visolve DB Team - Original Message - From: Frederic Wenzel [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: Visolve DB Team [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Tuesday, August 15, 2006 1:01 PM Subject: Re: multiple table inserts Bruce, why do you want to do that at all? If you need to add values to several tables either at once or (in case of an error) not at all, you should use transactions. Fred On 8/15/06, bruce [EMAIL PROTECTED] wrote: - Original Message - From: bruce [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, August 15, 2006 7:56 AM Subject: multiple table inserts hi... the mysql docs don't seem to allow this.. but i'll ask. is there a way to accomplish: insert into cat,dog (cat.x,dog.y) values (1,2) allowing me to essentially insert items into multiple tables simultaneously... thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: IN or OR? whats the diff?
Hi Michael, While using IN operator expr IN (value,...) Returns 1 if expr is equal to any of the values in the IN list, else returns 0. If all values are constants, they are evaluated according to the type of expr and sorted. The search for the item then is done using a binary search. This means IN is very quick if the IN value list consists entirely of constants. Thanks, VisolveDB Team - Original Message - From: Ratheesh K J [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, August 01, 2006 10:22 AM Subject: IN or OR? whats the diff? Hello all, Just wanted to know if using IN in the where clause is better than OR in terms of performance. that is : Are these both same in terms of performance SELECT * FROM TABLE WHERE ( COLUMN = 1 OR COLUMN = 2 ); SELECT * FROM TABLE WHERE COLUMN IN ( 1, 2 ); thanks, Ratheesh Bhat K J -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: want to insert unicode myanmar characters into MySQL database
Hi Khaing su yee, If the character set is not available in your MySQL, then add the particular character set to MySQL. You must have a MySQL source distribution to use these instructions. First, decide whether the character set is simple or complex and then proceed with it. /the name of your character set is represented by myanmar. If the myanmar is a simple character set, Add myanmar to the end of the sql/share/charsets/Index file. Assign a unique number to it. Create the file sql/share/charsets/myanmar.conf. (...The syntax for the file is very simple: Comments start with a '#' character and proceed to the end of the line. Words are separated by arbitrary amounts of whitespace. When defining the character set, every word must be a number in hexadecimal format.) Add the character set name to the CHARSETS_AVAILABLE and COMPILED_CHARSETS lists in configure.in. Reconfigure, recompile, and test. Thanks Visolve DB Team. - Original Message - From: khaing su yee [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, August 01, 2006 9:28 AM Subject: want to insert unicode myanmar characters into MySQL database I use Toad for MySQL 2.0 and SQLyog 5.02. I want to insert unicode myanmar characters. I change uft8 charset and utf8_unicode_ci collation. But I can't insert myanmar characters. What is needed to do? Please tell me. Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: tune a geometric query
Hello Prashant,
If you do need the duplicate rows in the final result, use UNION ALL with
your query. Otherwise you can opt for UNION as UNION is faster than UNION
ALL.
Thanks,
ViSolve DB Team.
- Original Message -
From: PRASHANT N [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Tuesday, August 01, 2006 5:28 PM
Subject: tune a geometric query
hi,
we are working on automotive traking solutions and insert our location
records into the mysql database v 4.1.20. If we want search for a
particular record, its taking too long time and mysql is identifying the
queries as slow queries. I have attached the queries.
How to optimize the attache query ?
regards
shann
___
Hot new product - Spider Networks introduces stunning online ePortfolio
solution for students and teachers
http://www.spider-networks.net/solutions/eportfolio.html
select A.name,A.district,x(GeomFromText(AsText(A.geo))) as
x,y(GeomFromText(AsText(A.geo))) as
y,(GLength(LineStringFromWKB(LineString(AsBinary(geo),AsBinary(GeomFromText('POINT(76.67472
11.83884)')) as Distance FROM (select geo,name,district from
cities_point union all select geo,name,district from cities_font_point
union all select geo,name,district from State_Highways_point union all
select geo,name,district from Other_Roads_point union all select
geo,name,district from Major_Roads_point union all select
geo,name,district from Vet_Clinics_point union all select
geo,name,district from University_point union all select
geo,name,district from Tourist_Info_point union all select
geo,name,district from Temples_point union all select geo,name,district
from Taxi_Stands_point union all select geo,name,district from
Stadiums_point union all select geo,name,district from
Sports_Clubs_point union all select geo,name,district from
Shops_WhiteGds_point union all select geo,name,district from
Shops_Sports_point union all select geo,name,district from
Shops_RealEstate_point union all select geo,name,district from
Shops_Music_point union all select geo,name,district from
Shops_Misc_point union all select geo,name,district from Shops_LPG_point
union all select geo,name,district from Shops_Jewellery_point union all
select geo,name,district from Shops_Furnt_point union all select
geo,name,district from Shops_Footwear_point union all select
geo,name,district from Shops_Computer_point union all select
geo,name,district from Shops_Chemists_point union all select
geo,name,district from Shops_Bakery_point union all select
geo,name,district from Shops_Apparel_point union all select
geo,name,district from Shopping_Ctrs_point union all select
geo,name,district from Services_Travel_point union all select
geo,name,district from Services_Professional_point union all select
geo,name,district from Services_Financial_point union all select
geo,name,district from Service_Stations_point union all select
geo,name,district from Schools_point union all select geo,name,district
from Restaurants_point union all select geo,name,district from
Religious_Pls_Oth_point union all select geo,name,district from
Railway_Stations_point union all select geo,name,district from
Railway_Reservations_point union all select geo,name,district from
PreSchools_point union all select geo,name,district from
PostOffices_point union all select geo,name,district from
PoliceStations_point union all select geo,name,district from
PoliceChaukis_point union all select geo,name,district from
PetrolPumps_point union all select geo,name,district from Parks_point
union all select geo,name,district from OtherInstt_point union all
select geo,name,district from Offices_point union all select
geo,name,district from Office_Airlines_point union all select
geo,name,district from Museums_point union all select geo,name,district
from Mosques_point union all select geo,name,district from Misc_point
union all select geo,name,district from Libraries_point union all select
geo,name,district from LevelCrossing_point union all select
geo,name,district from Industries_point union all select
geo,name,district from Hotels_point union all select geo,name,district
from Hostels_point union all select geo,name,district from
Hospitals_point union all select geo,name,district from
Historical_Pls_point union all select geo,name,district from
Gymnasiums_point union all select geo,name,district from Gurdwaras_point
union all select geo,name,district from Graveyards_point union all
select geo,name,district from GovtOffices_point union all select
geo,name,district from Dispensaries_point union all select
geo,name,district from Discotheques_point union all select
geo,name,district from Diagnostic_Ctrs_point union all select
geo,name,district from CyberCafes_point union all select
geo,name,district from Cultural_Centres_point union all select
geo,name,district from Crematory_point union all select
geo,name
Re: Problems with WHERE clause
Hello Jørn Dahl-Stamnes, The column 'fee' is existing in more than one table . To overcome this problem use the correct instance name of the table for the column fee In WHERE clause select s.id, s.name, sum(p.fee) as fee from serie as s inner join race_serie as rs on (rs.serie_id=s.id) inner join races as r on (r.id=rs.race_id) inner join participants as p on (p.race_id=r.id) where s.receipt=1 and p.rider_id=236 and p.fee 0 Here p is the instance of the table participants group by s.id order by s.f_date; Thanks VisolveDB Team - Original Message - From: Jørn Dahl-Stamnes [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Sunday, July 30, 2006 2:40 PM Subject: Problems with WHERE clause I got the following query: select s.id,s.name,sum(p.fee) as fee from serie as s inner join race_serie as rs on (rs.serie_id=s.id) inner join races as r on (r.id=rs.race_id) inner join participants as p on (p.race_id=r.id) where s.receipt=1 and p.rider_id=236 and fee 0 group by s.id order by s.f_date; which gives me the error: ERROR 1052 (23000): Column 'fee' in where clause is ambiguous Without the 'and fee 0' the query works fine. mysql select version(); ++ | version() | ++ | 4.1.8-standard | ++ Is this due to an old version of MySQL? -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Cluster
Hello Kaushal, You can get the MySQL clustering details from the following link. http://dev.mysql.com/doc/refman/5.0/en/mysql-cluster-quick.html http://dev.mysql.com/doc/refman/5.0/en/index.html Thanks, ViSolve DB Team - Original Message - From: Kaushal Shriyan [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, July 25, 2006 3:11 PM Subject: MySQL Cluster Hi ALL I want to implement MySQL Cluster, are there any step by step guide to implement it Thanks and Regards Kaushal -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.394 / Virus Database: 268.10.4/396 - Release Date: 7/24/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stored procedures
Hello Jon. Could you tell me the version of MySql ?. You can find the version by excuting the command SELECT version() If the version is below 5, the stored procedure feature would not work . Thanks Visolve DB Team. - Original Message - From: Jon [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, July 25, 2006 2:40 PM Subject: Stored procedures Hi list I'm trying to make stored procedures use parameters for limit and tables, I guess this is either me using the wrong datatype or it's not possible. I'm having the same issue with seting the table for the query: CREATE PROCEDURE sp_test1 (IN some_limit int) select * from some_table limit some_limit; and CREATE PROCEDURE sp_test2 (IN some_table table) select * from some_table; Both fail with ERROR 1064 (42000). Can someone please shed some light on this for me? Is this a problem with procedures not being able to do this or is it wrong datatypes or something completely different? Thanks in advance Jon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Searching through an alphabetical range
Hello Paul, You can try this: SELECT DISTINCT lastname FROM employee WHERE lastname BETWEEN 'm' AND 'z'; Thanks, ViSolve DB Team. - Original Message - From: Paul Nowosielski [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, July 21, 2006 10:33 PM Subject: Searching through an alphabetical range Dear All, I need to write a query that searches last names between the ranges of m through z. Is there a way to do this in the query? Thank You, -- Paul Nowosielski Webmaster office: 303.440.0666 ext 219 cel: 303.827.4257 -- 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: Finding a point inside a polygon
Hello Mark, You can locate a point, whether inside or outside a polygon area using the query below: SELECT contains(geomfromtext(@poly),geomfromtext(@p)); where @poly,@p are variables. Eg: set @p='Point(3 2)'; set @poly= 'Polygon((0 0,0 3,3 0,0 0),(1 1,1 2,2 1,1 1)) '; If the return value of the select statement is 0 - Outside the polygon 1 - Inside the polygon Thanks, ViSolve MySQL Support Team. - Original Message - From: Mark Maunder [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, July 21, 2006 4:40 AM Subject: Finding a point inside a polygon I'd like to test whether a point is truly inside a polygon, not just insude the minimum bounding rectangle. Is there a way to do this in MySQL with the spatial extensions? I love mysql but I'm forced to consider migrating to postgresql (ugh!) because it has built in support for testing spatial relationships between polygons and points. Thanks. Mark. -- 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: CHARACTER SET COLLATE NULL error with mySQL 4.0.27
Hello Thomas You have an error in your SQL syntax. Please try this : CREATE TABLE test.user ( user_id INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY(user_id), email text CHARACTER SET 'latin1' COLLATE 'latin1_swedish_ci', firstname text CHARACTER SET 'latin1' COLLATE 'latin1_swedish_ci'); The error is due to the AUTO_INCREMENT column, which is not defined as PRIMARY KEY and the CHARACTER SET column, which is not syntatically defined. Also to list the MySQL support Character Set try: Show Character Set; Thanks ViSolve MySQL Support Team. - Original Message - From: thomas Armstrong [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, July 20, 2006 2:41 PM Subject: CHARACTER SET COLLATE NULL error with mySQL 4.0.27 Hola. With mySQL 4.0.27 I'm trying to create this table -- CREATE TABLE `test`.`user` ( `user_id` INT UNSIGNED NOT NULL AUTO_INCREMENT , `email` TEXT CHARACTER SET COLLATE NULL , `firstname` TEXT CHARACTER SET COLLATE NOT NULL , ) -- but I get this error: -- #1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'CHARACTER SET COLLATE NULL, `firstname` TEXT CHARACTER SET -- Does anybody know which the right way is for this mySQL version? I'm using mySQL-Workbench to design the tables, and that's the code I get? Thank you very much. -- 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: FULL TEXT and Asian languages
Hi Peter,
Definitely using OR will slow up the performance of FULL TEXT searching.
Instead of using OR, you can try using UNION statement.
Hope this will be a fix for your issue.
Thanks,
ViSolve MySQL Support Team.
- Original Message -
From: Peter Lauri [EMAIL PROTECTED]
To: 'JC' [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Sent: Thursday, July 20, 2006 4:21 PM
Subject: RE: FULL TEXT and Asian languages
That is what I am doing right now, but it is not that fast if this system
would grow, and also it is not ranking the searches.
Right now I do something like this:
$searchwords = explode( , $searchstring);
foreach($searchwords AS $value) {
$Query.= OR lajlaj LIKE '%$value%'
}
If there are many search words, the OR will grow a bit, and OR are not
that
fast as I read somewhere.
/Peter
-Original Message-
From: JC [mailto:[EMAIL PROTECTED]
Sent: Thursday, July 20, 2006 10:46 PM
To: Peter Lauri
Cc: mysql@lists.mysql.com
Subject: Re: FULL TEXT and Asian languages
don't know about indexing, but try to search: LIKE '%sentences%'
JC
On Thu, 20 Jul 2006, Peter Lauri wrote:
Best group member,
I have a problem. I was going to use FULL TEXT search for my Thai client.
It
is working smooth with English text and wordings, the indexing and search
works fine.
The problem with Thai text is that words are not separated with a white
space as in English and other languages. I think this screws up the
indexing, and complete sentences are classed as a word. Assume Thai
characters:
Thisisasentenceinthai. ButIcannotsearchforsentenceinthatsearch.
I want to search for sentence, but can not. How can this be done? And
will
the indexing ever work?
Best regards,
Peter Lauri
--
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.394 / Virus Database: 268.10.2/393 - Release Date: 7/19/2006
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
