Re: Selecting Dates
Hi What is the datatype of the column order_date. Give the full form of the date for the between condition. Thanks Suresh Kuna MySQL DBA --Original Message-- From: ML To: mysql@lists.mysql.com Subject: Selecting Dates Sent: Feb 1, 2010 7:05 AM Hi All, Switching from Oracle to MySQL, I seem to be having some difficulty selecting dates using between or even where = and = like: SELECT * FROM orders WHERE order_date BETWEEN='2010-01-01' AND '2010-01-30' ORDER BY order_date; or SELECT * FROM orders WHERE order_date ='2010-01-01' AND = '2010-01-30' ORDER BY order_date; Neither of these work. What am I missing? -ML -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=sureshkumar...@gmail.com Sent from BlackBerry® on Airtel
Re: I NEED HELP ON 'SOURCE(\.) MYSQL
Give the ful path of file name with in '' and try it out Thanks Suresh Kuna MySQL DBA --Original Message-- From: Colin Streicher To: mysql@lists.mysql.com Cc: kebede teferi ReplyTo: co...@obviouslymalicious.com Subject: Re: I NEED HELP ON 'SOURCE(\.) MYSQL Sent: Feb 2, 2010 7:33 AM what everyone else said... also, is there a longer version of the error? or is it literally error 2 or error 22? Colin On February 1, 2010 06:38:08 pm kebede teferi wrote: Hi, I'm very new to this and I need help. What I want to do is to execute a source command statement from cmd to create tables and populate them on a data base that I created in Mysql. Here is what I did at CMD: *I changed the directory from c:\ to mysql *At mysql prompt i used -u root -p that prompted me to give my password. *I put in my password and I was allowed to access the mysql server. *I used the 'the use --database' command and I can see the database I created is active and waiting to be worked on. *then to run the script which is on a note pad with the name create and file type: sql I typed the following at the mysql prompt: *source create.sql; I get the error message '2' and '22' whichever way I tried the source command. I appreciate your help Kebede Teferi -- Your love life will be happy and harmonious. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=sureshkumar...@gmail.com Sent from BlackBerry® on Airtel
Re: hi help to take backup-mysql-windows-xp
Hi Murali, You have to execute it outside mysql prompt i.e command prompt. Thanks Suresh Kuna MySQL DBA Sent from BlackBerry® on Airtel -Original Message- From: muralikrishna g muralikrishn...@gmail.com Date: Tue, 2 Feb 2010 12:17:24 To: Suresh Kunasureshkumar...@gmail.com Cc: mysql@lists.mysql.com Subject: Re: hi help to take backup-mysql-windows-xp hi to all.. i was in need to take backup of database.. i am using sql server version:5.0.27-coomunity-nt i have tried by using mysqldump -u name -p password database_name backup.sql; but i am getting error.. i am using windows xp system.. please help me mysql mysqldump -u root -p dbadmin murali backupfile.sql; ERROR 1064 (42000): 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 'mysql dump -u root -p dbadmin murali backupfile.sql' at line 1 mysql mysqldump -uroot -pdbadmin murali backupfile.sql; ERROR 1064 (42000): 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 'mysql dump -uroot -pdbadmin murali backupfile.sql' at line 1 mysql On 2/2/10, Suresh Kuna sureshkumar...@gmail.com wrote: Please paste the error and don't give the spave between -p and password. On Tue, Feb 2, 2010 at 12:05 PM, muralikrishna g muralikrishn...@gmail.com wrote: hi to all.. i was in need to take backup of database.. i am using sql server version:5.0.27-coomunity-nt i have tried by using mysqldump -u name -p password database_name backup.sql; but i am getting error.. i am using windows xp system.. please help me -- Thanks Suresh Kuna MySQL DBA
Re: How to change mysql default database directory
As Carlos said, mention your required path in the my.ini file and restart the MySQL or move your current datadir to your required path and restart MySQL by mentioning the variable as below in the configuration file. Datadir=path In MySQLD section of your cnf file. Thanks Suresh Kuna MySQL DBA --Original Message-- From: Carlos Proal To: mysql@lists.mysql.com Subject: Re: How to change mysql default database directory Sent: Jan 29, 2010 8:46 AM Absolutely, check for the variable datadir on the config file (my.ini on windows and my.cnf on *nix). Carlos On 1/28/2010 9:12 PM, Lucky Wijaya wrote: Hi all, I've installed MySQL on 320GB Harddisk (partitioned into 3 partitions). I want to know if there's a way to change MySQL default database directory from C: to D:. Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=sureshkumar...@gmail.com Sent from BlackBerry® on Airtel
Re: Problem with mysql
Hi, The grant stmt has to be as below. Grant all on databasename.tablename to usern...@iporhostname identified by 'password'; Use as above Grant stmt doesn't require a flush stmt. Suresh Kuna MySQL DBA --Original Message-- From: Jørn Dahl-Stamnes To: mysql@lists.mysql.com ReplyTo: sq...@dahl-stamnes.net Subject: Problem with mysql Sent: Jan 10, 2010 3:20 PM Hello I have installed mysql, apache and phpmyadmin on a new server running Fedora Core 12. For some unknown reason I cannot connect to the local host using a full hostname. This is what I have done: r2-d2# mysql -u root -p grant all on . to 'root'@'r2-d2' idenfified by 'secret-password'; grant all on . to 'root'@'r2-d2.dahl-stamnes.net' idenfified by 'secret-password'; flush privileges; quit r2-d2# mysql -h r2-d2 -u root -p (works) r2-d2# mysql -h r2-d2.dahl-stamnes.net -u root -p ERROR 1130 (HY000): Host 'r2-d2.dahl-stamnes.net.' is not allowed to connect to this MySQL server Then I did: r2-d2# mysql -u root -p grant all on . to 'root'@'jedi.dahl-stamnes.net' idenfified by 'secret-password'; jedi# mysql -h r2-d2.dahl-stamnes.net -u root -p (works) I have checked the local DNS and 'r2-d2' and 'r2-d2.dahl-stamnes.net' resovl to the same IP-addresse. What's wrong? -- 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/mysql?unsub=sureshkumar...@gmail.com Sent from BlackBerry® on Airtel
Re: Are there any difference between max_connection and max_user_connection?
If the queries are identical then Yes. If not it will degrade the query performance. Sent from BlackBerry® on Airtel -Original Message- From: F.A.I.Z.A.L sac.fai...@gmail.com Date: Thu, 7 Jan 2010 15:51:19 To: Suresh Kunasureshkumar...@gmail.com Cc: mysql@lists.mysql.com Subject: Re: Are there any difference between max_connection and max_user_connection? hi suresh every 1 min nealy 40 to 80 users will hit the database. so, if i increase query_cache_size it will avoid physical i/o and buffer missing? Cheers Faizal S GSM : 9840118673 Blog: http://oradbapro.blogspot.com On Thu, Jan 7, 2010 at 1:23 PM, Suresh Kuna sureshkumar...@gmail.comwrote: Yes it won't allow except 1 connection for root user. We have to monitor the MySQLD server and the queries runnning at that time. As of now, increase the key_buffer_size to 64 or 128 mb according to your indexes created and physical memory available. and query_cache_size is purely depends on the queries that your application hits the database. On Thu, Jan 7, 2010 at 12:20 PM, F.A.I.Z.A.L sac.fai...@gmail.com wrote: hi Suresh thanks. max_connection=100(default). so, it won't allow user more than 100? and one more clarification. i am facing performance issue in mysqld. some time mysqld consuming 100% cpu. so what i have to do now? and the below parameters are in default values. if i increase the value. it will help performance? query_cache_size = 0 key_buffer_size = 8m sort_buffer_size = 2m innodb_buffer_pool_size = 8m read_buffer = 2M write_buffer = 2M thanks in advance.. Cheers Faizal S GSM : 9840118673 Blog: http://oradbapro.blogspot.com On Thu, Jan 7, 2010 at 11:27 AM, Suresh Kuna sureshkumar...@gmail.comwrote: Hi Faizal, Max_connections are the connections for the overall MySQLD server. Max_user_connections are the connections for the particular user ( i.e for an account ) of the MySQLD server. Suresh Kuna MySQL DBA On Thu, Jan 7, 2010 at 11:00 AM, F.A.I.Z.A.L sac.fai...@gmail.comwrote: Dear all I can see max_connection and max_user_connection set to default value. but daily i can see 40 to 70 users connection to the database. how? when max_user_connections=0. what is the different between these two parameters.. max_connection=100 max_user_connections=0 thanks in advance. Cheers Faizal S GSM : 9840118673 Blog: http://oradbapro.blogspot.com -- Thanks Suresh Kuna MySQL DBA -- Thanks Suresh Kuna MySQL DBA
Re: table export in cron
Hi, You can use an external replication tool like Golden gate for replication between MySQL and Oracle. Thanks Suresh Kuna MySQL DBA Sent from BlackBerry® on Airtel -Original Message- From: Mikhail Berman mikhail...@gmail.com Date: Wed, 06 Jan 2010 10:42:14 To: machiel.richardsmachiel.richa...@gmail.com Cc: mysql@lists.mysql.com Subject: Re: table export in cron Hi Machiel, As an alternative, you might consider use of mysqdump command, http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html, in a KSH/BASH script running from cron The script might look like this: = !#/bin/ksh mysqldump --password=yourpassword [more switches needed here] your_database your_table /path/to/output/file mysqldump command has switches to accomplish fields termination as you need, plus it gives opportunity to specify target database via --compatible=name http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html#option_mysqldump_compatibleswitch. Hoping this helps, Mikhail Berman machiel.richards wrote: Hi all I have a question regarding exporting of tables to a file from mysql. We need to export tables from mysql to a delimeted file which will then be imported into another database (oracle). We can do this manually from within mysql using the following command: select * from table into outfile '/path/to/output/file' fields terminated by '|'; This needs to be configured though to be run in a cron once every week at a specific time. How can we do this when running in a cron script? Your assistance is appreciated. Regards Machiel
Re: Exporting the result of a Query into excel
Hi What is the OS you are using on the remote and local server. Is there a mysql client on local server? Thanks Suresh Kuna Sent from BlackBerry® on Airtel -Original Message- From: ishaq gbola ishaq...@yahoo.co.uk Date: Tue, 5 Jan 2010 13:31:59 To: mysql@lists.mysql.com Subject: Re: Exporting the result of a Query into excel Thanks a lot for that, but where does this file get saved in and how can i copy it to my local host if the database is on a remote server --- On Tue, 5/1/10, Jay Ess li...@netrogenic.com wrote: From: Jay Ess li...@netrogenic.com Subject: Re: Exporting the result of a Query into excel To: mysql@lists.mysql.com Mysql mysql@lists.mysql.com Date: Tuesday, 5 January, 2010, 13:22 ishaq gbola wrote: Hi all, I would like to know if there is a tool or command in mySQL that allows one to export the result of query into excel formart select * from table into outfile thefile.txt; That can be imported into excel using CSV and using TAB as separator. http://code.anjanesh.net/2007/12/export-mysql-data-to-excel-compatible.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=ishaq...@yahoo.co.uk
Re: Exporting the result of a Query into excel
Ok. In windows, Use winscp tool, connect to the server and copy the file to local machine and import it in excel. Thanks Suresh Kuna Sent from BlackBerry® on Airtel -Original Message- From: ishaq gbola ishaq...@yahoo.co.uk Date: Tue, 5 Jan 2010 13:42:20 To: mysql@lists.mysql.com Subject: Re: Exporting the result of a Query into excel Am running windows on my local host and Redhat linux on the remote server, I have no mysql client on local host. TAC for the NMS using the database adviced me not to use a msqlclient for it, that it could break the database??? --- On Tue, 5/1/10, sureshkumar...@gmail.com sureshkumar...@gmail.com wrote: From: sureshkumar...@gmail.com sureshkumar...@gmail.com Subject: Re: Exporting the result of a Query into excel To: ishaq gbola ishaq...@yahoo.co.uk, mysql@lists.mysql.com Date: Tuesday, 5 January, 2010, 13:37 Hi What is the OS you are using on the remote and local server. Is there a mysql client on local server? Thanks Suresh Kuna Sent from BlackBerry® on Airtel -Original Message- From: ishaq gbola ishaq...@yahoo.co.uk Date: Tue, 5 Jan 2010 13:31:59 To: mysql@lists.mysql.com Subject: Re: Exporting the result of a Query into excel Thanks a lot for that, but where does this file get saved in and how can i copy it to my local host if the database is on a remote server --- On Tue, 5/1/10, Jay Ess li...@netrogenic.com wrote: From: Jay Ess li...@netrogenic.com Subject: Re: Exporting the result of a Query into excel To: mysql@lists.mysql.com Mysql mysql@lists.mysql.com Date: Tuesday, 5 January, 2010, 13:22 ishaq gbola wrote: Hi all, I would like to know if there is a tool or command in mySQL that allows one to export the result of query into excel formart select * from table into outfile thefile.txt; That can be imported into excel using CSV and using TAB as separator. http://code.anjanesh.net/2007/12/export-mysql-data-to-excel-compatible.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=ishaq...@yahoo.co.uk
Re: Exporting the result of a Query into excel
We have to use outfile with select stmt, not with show. Suresh Kuna Sent from BlackBerry® on Airtel -Original Message- From: ishaq gbola ishaq...@yahoo.co.uk Date: Tue, 5 Jan 2010 13:45:44 To: mysql@lists.mysql.com Subject: Re: Exporting the result of a Query into excel As test i tried this and I got the result below mysql show tables into outfile trial.txt - ; ERROR 1064: You have an error in your SQL syntax. Check the manual that corresp onds to your MySQL server version for the right syntax to use near 'into outfile trial.txt' at line 1 mysql --- On Tue, 5/1/10, Jay Ess li...@netrogenic.com wrote: From: Jay Ess li...@netrogenic.com Subject: Re: Exporting the result of a Query into excel To: Cc: mysql@lists.mysql.com Date: Tuesday, 5 January, 2010, 13:35 ishaq gbola wrote: Thanks a lot for that, but where does this file get saved in and how can i copy it to my local host if the database is on a remote server If you don't specify the absolute location it can be find in DATADIR/DatabaseName/. And after you located the file you have a multitude of choice how to transfer the file. scp,ftp,http,mail all depending on what's installed on the server and what access you got to it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=ishaq...@yahoo.co.uk
Re: High Overhead On Active Insert And Delete Table
Hi Willy, What is the engine you are using for the table? Paste the table structure. Thanks, Suresh Kuna --Original Message-- From: Willy Mularto To: mysql@lists.mysql.com Subject: High Overhead On Active Insert And Delete Table Sent: Jan 3, 2010 4:22 PM Hi list, I have a table which is very active in operation INSERT INTO and DELETE, approximately there will be around 2millions INSERT and DELETE operation per day. And I see the overhead is getting very high, I must do OPTIMIZE TABLE query every time. Is there any other option to solve this? TIA. Willy sangpr...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=sureshkumar...@gmail.com Sent from BlackBerry® on Airtel
Re: High Overhead On Active Insert And Delete Table
My first impression after looking at the table structure is
1 the number of indexes present are very huge and each insert or delete will
act as an extra insert or delete of each and every index created.
2 what type of select stmts are going to hit this table.
3 if it is a primary key select, drop all the remaining indexes.
4 as it is myisam, enable the concurrent insert value to 2, so it insert at the
last and do periodic analyze or optimize.
5 the final option is de normalization.
Thanks,
Suresh Kuna
Sent from BlackBerry® on Airtel
-Original Message-
From: Willy Mularto sangpr...@gmail.com
Date: Sun, 3 Jan 2010 18:12:08
To: sureshkumar...@gmail.com
Cc: mysql@lists.mysql.com
Subject: Re: High Overhead On Active Insert And Delete Table
The engine is using MyISAM. And here is the structure:
CREATE TABLE IF NOT EXISTS `ie_push` (
`sql_id` bigint(20) NOT NULL auto_increment,
`momt` enum('MO','MT','DLR') default NULL,
`sender` varchar(20) default NULL,
`receiver` varchar(20) default NULL,
`udhdata` blob,
`msgdata` text,
`time` bigint(20) default NULL,
`smsc_id` varchar(255) default NULL,
`service` varchar(255) default NULL,
`account` varchar(255) default NULL,
`id` bigint(20) default NULL,
`sms_type` bigint(20) default NULL,
`mclass` bigint(20) default NULL,
`mwi` bigint(20) default NULL,
`coding` bigint(20) default NULL,
`compress` bigint(20) default NULL,
`validity` bigint(20) default NULL,
`deferred` bigint(20) default NULL,
`dlr_mask` bigint(20) default NULL,
`dlr_url` varchar(255) default NULL,
`pid` bigint(20) default NULL,
`alt_dcs` bigint(20) default NULL,
`rpi` bigint(20) default NULL,
`charset` varchar(255) default NULL,
`boxc_id` varchar(255) default NULL,
`binfo` varchar(255) default NULL,
`priority` tinyint(4) default NULL,
`custom` varchar(255) default NULL,
`additional` varchar(255) default NULL,
`service_id` varchar(50) default NULL,
`fid` varchar(50) default NULL,
`msgid` varchar(50) default NULL,
`insertdate` datetime default NULL,
PRIMARY KEY (`sql_id`),
KEY `momt` (`momt`),
KEY `sender` (`sender`),
KEY `receiver` (`receiver`),
KEY `time` (`time`),
KEY `service` (`service`),
KEY `account` (`account`),
KEY `id` (`id`),
KEY `sms_type` (`sms_type`),
KEY `mclass` (`mclass`),
KEY `mwi` (`mwi`),
KEY `coding` (`coding`),
KEY `compress` (`compress`),
KEY `validity` (`validity`),
KEY `deferred` (`deferred`),
KEY `dlr_mask` (`dlr_mask`),
KEY `dlr_url` (`dlr_url`),
KEY `pid` (`pid`),
KEY `rpi` (`rpi`),
KEY `alt_dcs` (`alt_dcs`),
KEY `charset` (`charset`),
KEY `boxc_id` (`boxc_id`),
KEY `binfo` (`binfo`),
KEY `priority` (`priority`),
KEY `custom` (`custom`),
KEY `additional` (`additional`),
KEY `service_id` (`service_id`),
KEY `insertdate` (`insertdate`),
KEY `fid` (`fid`),
KEY `msgid` (`msgid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Willy
sangpr...@gmail.com
On Jan 3, 2010, at 6:00 PM, sureshkumar...@gmail.com wrote:
Hi Willy,
What is the engine you are using for the table? Paste the table
structure.
Thanks,
Suresh Kuna
--Original Message--
From: Willy Mularto
To: mysql@lists.mysql.com
Subject: High Overhead On Active Insert And Delete Table
Sent: Jan 3, 2010 4:22 PM
Hi list,
I have a table which is very active in operation INSERT INTO and
DELETE, approximately there will be around 2millions INSERT and DELETE
operation per day. And I see the overhead is getting very high, I must
do OPTIMIZE TABLE query every time. Is there any other option to solve
this? TIA.
Willy
sangpr...@gmail.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=sureshkumar...@gmail.com
Sent from BlackBerry® on Airtel
