order by-- rollup
Hi how to use order by with with rollup, if it is not possible is there any alternative, in rollup how to name the null. is there chance to do so. please help me with this..
hi.. is it possible
Hi is it possible to have two instances of mysql over single windows xp. because I want to workout with those as server and client. please help me regarding this.
Re: hi.. is it possible
Yes you can, but port has to be different. Regards, Thiyaghu CK www.mafiree.com On Sat, Feb 6, 2010 at 2:50 PM, MuraliKrishna murali_kris...@arthaoptions.com wrote: Hi is it possible to have two instances of mysql over single windows xp. because I want to workout with those as server and client. please help me regarding this.
how to get distinct values in the following scenarion
Hi I have table like as following.. Cust_id Visited_date 1 2-1-2010 2 3-1-2010 3 4-1-2010 4 5-1-2010 6 6-1-2010 1 7-1-2010 2 8-1-2010 These visitor ids with visited date. but I want only all the customers with first visited date. Please help me in this.. Regards Muralikrishna
dumping error
D:\mysqldump -u root -pdbadmin murali murali.sql 'mysqldump' is not recognized as an internal or external command, operable program or batch file.
Re: dumping error
2010.02.06. 12:00 keltezéssel, MuraliKrishna írta: D:\mysqldump -u root -pdbadmin murali murali.sql 'mysqldump' is not recognized as an internal or external command, operable program or batch file. could you stop blogging to the list, please? t -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: dumping error
I hope you are executing the command from 'D:\', mysqldump has to be executed for the directory bin where you have installed the mysql. eg:D:\mysql\mysql5.0\bin\mysqldump -u root -pdbadmin murali c:\murali.sql This will help you. Regards, Thiyaghu CK www.mafiree.com On Sat, Feb 6, 2010 at 4:30 PM, MuraliKrishna murali_kris...@arthaoptions.com wrote: D:\mysqldump -u root -pdbadmin murali murali.sql 'mysqldump' is not recognized as an internal or external command, operable program or batch file.
max() can't work
select * from table_name where movid = max(movid); why the sql above can't work? Shall I use a sub-select instead? select * from table_name where movid = (select max(movid) from table_name) ? Thanks in advance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: max() can't work
the field movid is type integer or varchar ? 2010/2/6 tech list bluetm...@gmail.com select * from table_name where movid = max(movid); why the sql above can't work? Shall I use a sub-select instead? select * from table_name where movid = (select max(movid) from table_name) ? Thanks in advance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=armand...@gmail.com -- ing. paredes aguilar, armando http://www.sinapsisperu.com/ Desarrollador
Re: max() can't work
The max() function is an aggregate function which can be used in conjunction with GROUP BY in the SELECT or HAVING clause: http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html . This code should work: select * from table_name group by movid having max(movid). However, there is a simpler and more efficient solution: select * from table_name order by movid desc limit 1. I hope this helps. Best regards Roland Kaber armando wrote: the field movid is type integer or varchar ? 2010/2/6 tech list bluetm...@gmail.com select * from table_name where movid = max(movid); why the sql above can't work? Shall I use a sub-select instead? select * from table_name where movid = (select max(movid) from table_name) ? Thanks in advance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=armand...@gmail.com
Re: max() can't work
Yes - you must use the subselect. Or, you can set a variable like: select @max := max(movid) from table_name; select * from table_name where movid = @max; On Sat, Feb 6, 2010 at 8:34 AM, tech list bluetm...@gmail.com wrote: select * from table_name where movid = max(movid); why the sql above can't work? Shall I use a sub-select instead? select * from table_name where movid = (select max(movid) from table_name) ? Thanks in advance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=jlyons4...@gmail.com -- Jim Lyons Web developer / Database administrator http://www.weblyons.com
Re: MY SQL Slave Server
I tried to install once again mysql at vista? but deducts the previous installation. and it is ask for the modify , repire and remove options You said that we can install any number of setups in a same system. Is there any other way to install? Please help me thank you From: Thiyaghu CK theyaho...@gmail.com To: Vikram A vikkiatb...@yahoo.in Sent: Sat, 6 February, 2010 3:05:25 PM Subject: Re: MY SQL Slave Server Hi Vikram, Ya sure, slave can be in vista. Yes, you can install 2 or more setups in same system but port has to be different, and if its linux platform change the sock name too. Regards, Thiyaghu CK www.mafiree.com On Sat, Feb 6, 2010 at 2:09 PM, Vikram A vikkiatb...@yahoo.in wrote: Dear Thiyagu! Thank you for the information. I will do the experiment. and let you know the further details. Is it possible making slave at windows vista? As per your information, th emysql has to be installed once again another port; Is it possible to install 2 setups in a same system? Thank you VIKRAM A From: Thiyaghu CK theyaho...@gmail.com To: Vikram A vikkiatb...@yahoo.in Cc: mysql@lists.mysql.com Sent: Sat, 6 February, 2010 1:03:34 PM Subject: Re: MY SQL Slave Server Hi Vikram, So as my understanding you need to have a master slave setup in a single machine(FEDORA 11). 1. Make the already running mysql instance as Master 2. Install a new mysql in the same machine in different port which will be your slave 3. Replicate 4.You can also add more slave in same machine or in different machine and make more copy. Note: Slave should be of same version or higher. For replication steps you can refer http://www.mafiree.com/docs.html or http://www.howtoforge.com/mysql_database_replication Let me know for more details. Regards, Thiyaghu CK www.mafiree.com On Sat, Feb 6, 2010 at 12:33 PM, Vikram A vikkiatb...@yahoo.in wrote: Dear Experts, I would like to configure the slave for my main server. My server is running in the FEDORA 11. I would like to make another mirror of the DB in the same server/ The copy of the DB can be kept at win server / another Fedora server. Can you suggest how to do the above? Thank you VIKKI A The INTERNET now has a personality. YOURS! See your Yahoo! Homepage. http://in.yahoo.com/ The INTERNET now has a personality. YOURS! See your Yahoo! Homepage. The INTERNET now has a personality. YOURS! See your Yahoo! Homepage. http://in.yahoo.com/
Re: MY SQL Slave Server
*MySQL Install multiple instances.* Create a folder called Conf with Instance.1.ini, Instance.2.ini, and Instance.3.ini. The Port each are listening on should all differ, as well as having a different data directory. I named these C:\Program Files\MySQL\MySQL Server 5.0\MySQLData\Instance1 mysqld --install Mysql-Instance-1 --defaults-file=C:\Program Files\MySQL\MySQL Server 5.0\Conf\Instance.1.ini mysqld --install Mysql-Instance-2 --defaults-file=C:\Program Files\MySQL\MySQL Server 5.0\Conf\Instance.2.ini mysqld --install Mysql-Instance-3 --defaults-file=C:\Program Files\MySQL\MySQL Server 5.0\Conf\Instance.3.ini in the ini file the commands to set these are: * port=3306 * datadir=C:\Program Files\MySQL\MySQL Server 5.0\MySQLData\Instance1 * port=3307 * datadir=C:\Program Files\MySQL\MySQL Server 5.0\MySQLData\Instance2 * port=3308 * datadir=C:\Program Files\MySQL\MySQL Server 5.0\MySQLData\Instance3 After creating the folders, settings and executing the command lines to install mysql as a service. I found that all the services successfully started up and then terminated itself and in the folder .\MySQLData\Instance1 etc, you should notice the following files, ib_logfile0, ib_logfile1, ibdata1 and %SystemName%.err, which displays the following error on each instance. 051220 22:16:28 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist Which simply means it cannot find the system tables. A quick resolve is to copy the entire data directory into each instance folder (Ensure that the standard instance is disabled if you wish to leave this service alone). Then the service should hopefully startup for all 3 services. xcopy data MySQLData/Instance1 xcopy data MySQLData/Instance2 xcopy data MySQLData/Instance3 net start Mysql-Instance-1 net start Mysql-Instance-2 net start Mysql-Instance-3 PS. To remove the instances enter the following commands. mysqld --remove Mysql-Instance-1 mysqld --remove Mysql-Instance-2 mysqld --remove Mysql-Instance-3 source: http://ajohnstone.com/archives/mysql-install-multiple-instances On Sat, Feb 6, 2010 at 10:08 PM, Vikram A vikkiatb...@yahoo.in wrote: I tried to install once again mysql at vista? but deducts the previous installation. and it is ask for the modify , repire and remove options You said that we can install any number of setups in a same system. Is there any other way to install? Please help me thank you From: Thiyaghu CK theyaho...@gmail.com To: Vikram A vikkiatb...@yahoo.in Sent: Sat, 6 February, 2010 3:05:25 PM Subject: Re: MY SQL Slave Server Hi Vikram, Ya sure, slave can be in vista. Yes, you can install 2 or more setups in same system but port has to be different, and if its linux platform change the sock name too. Regards, Thiyaghu CK www.mafiree.com On Sat, Feb 6, 2010 at 2:09 PM, Vikram A vikkiatb...@yahoo.in wrote: Dear Thiyagu! Thank you for the information. I will do the experiment. and let you know the further details. Is it possible making slave at windows vista? As per your information, th emysql has to be installed once again another port; Is it possible to install 2 setups in a same system? Thank you VIKRAM A From: Thiyaghu CK theyaho...@gmail.com To: Vikram A vikkiatb...@yahoo.in Cc: mysql@lists.mysql.com Sent: Sat, 6 February, 2010 1:03:34 PM Subject: Re: MY SQL Slave Server Hi Vikram, So as my understanding you need to have a master slave setup in a single machine(FEDORA 11). 1. Make the already running mysql instance as Master 2. Install a new mysql in the same machine in different port which will be your slave 3. Replicate 4.You can also add more slave in same machine or in different machine and make more copy. Note: Slave should be of same version or higher. For replication steps you can refer http://www.mafiree.com/docs.html or http://www.howtoforge.com/mysql_database_replication Let me know for more details. Regards, Thiyaghu CK www.mafiree.com On Sat, Feb 6, 2010 at 12:33 PM, Vikram A vikkiatb...@yahoo.in wrote: Dear Experts, I would like to configure the slave for my main server. My server is running in the FEDORA 11. I would like to make another mirror of the DB in the same server/ The copy of the DB can be kept at win server / another Fedora server. Can you suggest how to do the above? Thank you VIKKI A The INTERNET now has a personality. YOURS! See your Yahoo! Homepage. http://in.yahoo.com/ The INTERNET now has a personality. YOURS! See your Yahoo! Homepage. The INTERNET now has a personality. YOURS! See your Yahoo! Homepage. http://in.yahoo.com/ -- Best Regards, Prabhat Kumar MySQL DBA Datavail-India Mumbai Mobile : 91-9987681929 www.datavail.com My Blog: http://adminlinux.blogspot.com My
Re: max() can't work
hi It is not working, select * from table_name group by movid having max(movid) but it is working fine select * from table_name order by movid desc limit 1 From: Roland Kaber roland.ka...@education.lu To: armando armand...@gmail.com Cc: tech list bluetm...@gmail.com; mysql@lists.mysql.com Sent: Sat, 6 February, 2010 8:28:06 PM Subject: Re: max() can't work The max() function is an aggregate function which can be used in conjunction with GROUP BY in the SELECT or HAVING clause: http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html . This code should work: select * from table_name group by movid having max(movid). However, there is a simpler and more efficient solution: select * from table_name order by movid desc limit 1. I hope this helps. Best regards Roland Kaber armando wrote: the field movid is type integer or varchar ? 2010/2/6 tech list bluetm...@gmail.com select * from table_name where movid = max(movid); why the sql above can't work? Shall I use a sub-select instead? select * from table_name where movid = (select max(movid) from table_name) ? Thanks in advance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=armand...@gmail.com The INTERNET now has a personality. YOURS! See your Yahoo! Homepage. http://in.yahoo.com/
Trying to avoid bulk insert table locking
I have a system that imports about 40 million records every 2 days into a single table in MySQL. I was having problems with LOAD DATA CONCURRENT LOCAL INFILE where the table I was importing into would lock until the import was complete. Locks would prevent SELECTs also. I converted the table to MyISAM and removed the AUTO_INCREMENT key and that seemed to help a little bit, but apparently not enough because I still get locks for my larger file imports (maybe I just don't see the locks for the smaller imports). So, I think I want to test a new strategy: 1) import records into a temporary table 2) have a merge stored procedure loop through a cursor and migrate batches of records from the temp table to the permanent table in groups of perhaps 500-10,000 records. 3) make sure any acquired locks are released between each batch merged. Has anyone built logic like this already? Care to share your results and findings? Would this approach work, and is it fairly simple to do? -- Dante -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: max() can't work
Why in the world would you think select * from table_name group by movid having max(movid) would work? It seems to compile without errors but doesn't give you what you seem to want. This would work: select * from table_name group by movid having movid = (select max(movid) from table_name) although then your' not really grouping so the GROUP BY is useless. On Sat, Feb 6, 2010 at 11:01 AM, Vikram A vikkiatb...@yahoo.in wrote: hi It is not working, select * from table_name group by movid having max(movid) but it is working fine select * from table_name order by movid desc limit 1 From: Roland Kaber roland.ka...@education.lu To: armando armand...@gmail.com Cc: tech list bluetm...@gmail.com; mysql@lists.mysql.com Sent: Sat, 6 February, 2010 8:28:06 PM Subject: Re: max() can't work The max() function is an aggregate function which can be used in conjunction with GROUP BY in the SELECT or HAVING clause: http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html . This code should work: select * from table_name group by movid having max(movid). However, there is a simpler and more efficient solution: select * from table_name order by movid desc limit 1. I hope this helps. Best regards Roland Kaber armando wrote: the field movid is type integer or varchar ? 2010/2/6 tech list bluetm...@gmail.com select * from table_name where movid = max(movid); why the sql above can't work? Shall I use a sub-select instead? select * from table_name where movid = (select max(movid) from table_name) ? Thanks in advance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=armand...@gmail.com The INTERNET now has a personality. YOURS! See your Yahoo! Homepage. http://in.yahoo.com/ -- Jim Lyons Web developer / Database administrator http://www.weblyons.com
Re: Trying to avoid bulk insert table locking
On Sat, Feb 6, 2010 at 12:11 PM, D. Dante Lorenso da...@lorenso.com wrote: I have a system that imports about 40 million records every 2 days into a single table in MySQL. I was having problems with LOAD DATA CONCURRENT LOCAL INFILE where the table I was importing into would lock until the import was complete. Locks would prevent SELECTs also. This should not happen with InnoDB tables. Writers should not block readers. Were you using InnoDB? - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Query question
I have three tables that work together.
s_product is a list of farm products with an autoincrementing ID.
s_product_market_prices is a list of market pricings, obtained from
various sources. Each one is dated and refers to exactly one s_product
record via its ID.
s_product_harvest is a list of harvests, including s_product.ID,
amount, and date/time.
Now I want to generate a report showing the harvest sums and their
values, based upon an appropriate market pricing. It was all happy
when I only had one pricing per product, but then I added new dated
pricings, and got unexpected results.
I'd be happy if the pricings used were simply in the same year as the
harvest, but it seems like it picks a random one when I do a LEFT JOIN
on these tables. When I put additional AND clauses on the join to get
it to pick a price within the desired date range, it seems to affect
the number of harvests summed, and they are reduced somehow.
(Apologies for not fully qualifying the unexpected results; I'm hoping
someone can look at this and quickly show me something stupid I've
done! :-)
Here's the report:
http://www.EcoReality.org/wiki/2009_harvest
which is generated by the following SQL:
SELECT
product AS ID,
MAX(s_product.name) AS Name,
SUM(quantity) AS Quantity,
MIN(harvest.units) AS Units,
CONCAT('$', ROUND((SUM(quantity) * prices.price), 2)) AS Value,
prices.market_type AS `R-W`,
COUNT(*) AS Harvests,
MIN(date) AS Begin,
MAX(date) AS End
FROM s_product_harvest harvest
INNER JOIN s_product on s_product.ID = harvest.product
LEFT OUTER JOIN s_product_market_prices prices ON ID = prices.product_ID
WHERE date = '{{{1}}}-01-01' AND date = '{{{1}}}-12-31 23:59:59'
GROUP BY s_product.name
(Note that the token {{{1}}} is replaced with a four-digit year,
like 2009.)
My first impulse was to change the LEFT OUTER JOIN to:
s_product_market_prices prices ON ID = prices.product_ID AND
prices.price_date = '{{{1}}}-01-10' AND prices.price_date =
'{{{1}}}-12-31 23:59:59'
So that the prices table would only join for the desired year.
What am I doing wrong here?
Following are schemas of the three tables:
CREATE TABLE IF NOT EXISTS `s_product` (
`ID` int(10) unsigned NOT NULL auto_increment,
`super` int(11) default NULL COMMENT 'generalization',
`name` varchar(31) character set utf8 NOT NULL,
`units`
enum
('kilograms
','grams','pounds','ounces','liters','each','cords','bales') character
set utf8 NOT NULL default 'kilograms' COMMENT 'preferred unit',
`description` varchar(255) character set utf8 NOT NULL,
PRIMARY KEY (`ID`),
UNIQUE KEY `Name` (`name`),
KEY `Description` (`description`) )
ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='list of
EcoReality farm products' AUTO_INCREMENT=86 ;
CREATE TABLE IF NOT EXISTS `s_product_harvest` (
`date` datetime NOT NULL COMMENT 'Date and time of harvest.',
`product` int(11) NOT NULL default '53',
`resource` varchar(255) character set utf8 NOT NULL COMMENT
'Particular animal or tree, etc.',
`quantity` decimal(10,2) NOT NULL default '0.80',
`units`
enum
('kilograms
','grams','pounds','ounces','liters','each','cords','bales') character
set utf8 NOT NULL default 'kilograms',
`who1` smallint(5) unsigned NOT NULL default '2' COMMENT 'Who
harvested this resource?',
`who2` smallint(5) unsigned NOT NULL default '4' COMMENT 'Who helped
harvest this resource?',
`notes` varchar(255) character set utf8 NOT NULL,
KEY `product` (`product`),
KEY `date` (`date`),
KEY `who1` (`who1`,`who2`) )
ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin
COMMENT='historical list of EcoReality farm products harvested';
CREATE TABLE IF NOT EXISTS `s_product_market_prices` (
`product_ID` int(11) NOT NULL,
`price_date` date NOT NULL,
`price_source` varchar(255) character set utf8 NOT NULL,
`market_type` enum('retail','wholesale') character set utf8 NOT NULL
default 'wholesale',
`price` float NOT NULL,
`units` enum('kilograms','grams','pounds','ounces','liters','each')
character set utf8 NOT NULL default 'kilograms' COMMENT 'change in
sync with s_product_harvest.units',
PRIMARY KEY (`product_ID`,`price_date`) )
ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='market
pricing information for EcoReality products';
Thanks for whatever help you can offer!
The Apocalypse has Four Horsemen: climate change, habitat destruction,
industrial agriculture, and poverty. Each Horseman holds a whip called
Growth in his hand. None can be stopped unless all are stopped. --
David Foley
Jan Steinman, EcoReality Co-op
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
MySQL Proxy
Hi friends, Recently I accept an idea of testing a MySQL Proxy to concept an environment that will use two slave servers below it.Ok, the theory is good and I decide to try this, but, when I began to test step-by-step the MySQL Proxy manual I felt that somenthing was wrong. In first time, I started MySQL proxy with mysql-proxy --proxy-read-only-backend-addresses=localhost:3306 and connected with mysql server using mysql -u root -p -P 4042 only to test readOnly behavior...so, I had inserted some lines and updated too - I read on the manual that port 4042 is a readOnly port that filters UPDATE and INSERT and I didn't saw this behavior. Ok, I looking forward on the manual yet, I read about the mc.lua, Is that script exists? I don't know, cause I going on with tests, in this time, starting mysql-proxy with mysql-proxy --proxy-read-only-backend-addresses=localhost:3306 --proxy-lua-script=mc.lua and made all tests again with port 4042. Once my tests fail. Anybody here use this and MySQL-Proxy function? Anybody can give some explanation how does it works? Thanks in advanced. Wagner Bianchi
