Re: Auto Install mySQL
Hi, Just yesterday I have finished doing something very similar ( Apache+PHP+MySQL+Database+PHP-Application ) installer for a demo site. The setup installs the Apache service, the MySQL service and database and start the services. On unistall it removes the services, leaving all clean. The idea is as follows : 1. Create ( and populate ) the database in MySQL on your computer. 2. Create the user needed for your database and remove unneeded users 3. Create an empty folder where to store your application's files ( eg. C:\APP ) 4. Copy the entire MySQL directory from your install into this folder ( eg. C:\APP\MySQL ) 5. Remove any unneeded files from C:\APP\MySQL - you will have to remove other databases, these are stored in c:\APP\MySQL\Data , each database has its own directory. Delete everything but MySQL and Your_database_name folders. In the bin folder you can delete anything but the server you use ( eg. mysqld-nt.exe ) 6. Create or copy a my.ini file in c:\APP\MySQL and write any configurtion preferences in it ( like base_dir, port, innodb prefs, etc ) The installer should : 1. Copy the content of c:\APP folder on client's computer 2. Run the command c:\app\mysql\bin\mysqld-nt.exe --install MySQL --defaults-file=c:\app\mysql\my.ini, this will install the MySQL service 3. Run the command net start MySQL, this will start the MySQL service, MySQL is the name of the service, you can set it to anything you like as long as you use same name after --install and --remove ( eg. --install My_Name_for_mysql_service then net start My_Name_for_mysql_service That would be all, the server will be installed with your database. The ammount of interaction during install depends on how you make the installer ( you can make even a BAT file ). For the installer I have used Inno Setup, available here http://www.jrsoftware.org/isinfo.php , it is free and very easy to use. If you need to uninstall the app, uninstaller should : 1. Stop the MySQL service : net stop MySQL 2. Remove the MySQL service : c:\APP\MYSQL\BIN\mysqld-nt.exe --remove MySQL 3. Delete the c:\APP folder That would be all, hope it helps. -- Puiu Hrenciuc Xentra Development Jim [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Hi All, Does anybody have some advise on automating the install of mySQL and our DB on a PC/Laptop with no user interaction required. Does the SETUP.EXE take any paramaters to auto install without the user dialogs? Thanks, Jim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Optimizing SQL statement
First of all thanks all for your answers. Second I'll explain what I manage to do in regard with this issue, maybe someone else may need it in the future. So, first I have started by changing the way I have stored the `ip` field from varchar(15) to int unsigned and populated this field with the 4 bytes value of the ip ( INET_ATON ). Then I have done some benchmarking using the varchar field and ORDER BY NULL to avoid sorting. After that I have done some benchmarks using the ip stored as numbers ( of course I have redefined the primary key, etc, etc ). I was surprised to find out that grouping by a varchar was FASTER then by an int column. I was surprised to find out this because my logic tells me that it should be easyer to group by a 4 byte data than by a 15 bytes data. The tests were done using same computer, same database engine. After that, I also tryed to convert the table to InnoDB ( originally it was MyISAM ) and made same tests ( ip as int and as varchar ). The results were completly different, the grouping by ip as int was faster than ip as varchar and overall both queries were faster than if executed against a MyISAM table. I decided to keep the IP as INT and table as InnoDB, the time is now reduced to 2.3s ( avg ), still too much, but faster than before anyway. I think table partitions from 5.1 will help by splitting records by year and month , but waiting to get stable though. Queries used : For IP as VARCHAR : SELECT `ip`, `type`,SUM(`inbound`) AS `in`, SUM(`outbound`) AS`out` FROM `accounting` GROUP BY `ip`,`type` WHERE `record_time` BETWEEN '2006010100' AND '2006020100' ORDER BY NULL For IP as INT UNSIGNED : SELECT INET_NTOA(`ip`) AS `ip`, `type`,SUM(`inbound`) AS `in`, SUM(`outbound`) AS`out` FROM `accounting` GROUP BY `ip`,`type` WHERE `record_time` BETWEEN '2006010100' AND '2006020100' ORDER BY NULL Results : table_engine ip_type avg_query_time (s) MyISAMVARCHAR(15) 6.7 MyISAMINT UNSIGNED 9.4 InnoDB VARCHAR(15) 2.8 InnoDB INT UNSIGNED2.3 That would be all, thanks again. Puiu Hrenciuc P.S.: Removing ORDER BY NULL adds using filesort and takes longer. Joerg Bruehe [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Hi all! (Sorry for the late reply.) Puiu Hrenciuc wrote (re-ordered): Barry [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Puiu Hrenciuc wrote: Hi, I have a table ( MyISAM, MySQL 5.0.20, FreeBSD ) containing network traffic data : record_time datetime - time when recording was added ip char(15) - ip that generated the traffic type tinyint(3) - traffic type ( 1 - local, 2 - internet ) inbound int(10) - in bytes outbound int(10) - out bytes Records are inserted each 5 minutes through a cron script. Currently there are 3,330,367 rows. Primary index is defined on ( ip, type, record_time ), columns in that order. Also there is an index defined only on record_time Now for an example to get traffic for this month, I use : SELECT `ip`,`type`,SUM(`inbound`) AS `in`, SUM(`outbound`) as `out` FROM `accounting` WHERE `record_time` BETWEEN 2006040100 AND 2006041316 GROUP BY `ip`,`type` this query takes aprox 7 seconds Using EXPLAIN gives : select_typetable type possible_keys key key_len ref rows Extra SIMPLE accounting range record_time record_time 8 NULL 362410 Using where; Using temporary; Using filesort If I remove the SUM functions I am getting also Using index in group-by and the query takes only 0.25 sec Is there anyway to optimize this query to get faster responses ? For the original query, the index on record_time can be used to select the records to be considered, and the base table must be accessed to sum the inbound and outbound values. Without the summing, all information needed for the answer is in the primary key, so an index-only strategy can be used (does not need the base table, profits from key cache, ...). Also, by the group by clause each distinct combination of (ip, type) is needed only once, so the amount of data that need to be handled is much smaller. The summing must make a big difference, this cannot be avoided. You _might_ try an index (record_time, ip, type), because here the leading (= most significant) part can be used for your where condition, and the next components support the group by, so there is a slight chance to avoid the sorting. Disclaimer: This is pure speculation from my part! Set an index on ip and type and probably also on record_time Hmmm, I have omited that : I also have an index on (ip,type) in that order The index on (ip,type) is a prefix of the primary key (ip, type, record_time), and in general any prefix of an existing key can
Re: How to remove muiltiple queries to a table at the same time
1. A single query that fetvhes a value, increases it and save it :
UPDATE `table_name` SET `field`=`field`+1;
2. You can lock tables, make updates an then unlock them, the
other processes will wait the table to be unlocked before running
their queries :
LOCK TABLES `table_name` WRITE;
{SQL statements here}
UNLOCK TABLES;
See also : http://dev.mysql.com/doc/refman/5.0/en/lock-tables.html
abhishek jain [EMAIL PROTECTED] wrote in message
news:[EMAIL PROTECTED]
Dear Friends,
I run several processes and they need to query the mysql 5.0.8 database
simultaneously .I have a config table which have the record id. I need to
fetch that and increment that .What I feel that the same record id is
fetched by different simultaneosly before i update .Can anyone help me in
either:
1)telling me a single query which will fetch and incr. in the same query. so
the problem of simultaneously queries are solved.
2)A system by which delaying the other queries are done, I use PHP .
Expecting a quick reply.
Thanks,
Abhishek Jain
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Optimizing SQL statement
Hi, I have a table ( MyISAM, MySQL 5.0.20, FreeBSD ) containing network traffic data : record_time datetime - time when recording was added ip char(15) - ip that generated the traffic type tinyint(3) - traffic type ( 1 - local, 2 - internet ) inbound int(10) - in bytes outbound int(10) - out bytes Records are inserted each 5 minutes through a cron script. Currently there are 3,330,367 rows. Primary index is defined on ( ip, type, record_time ), columns in that order. Also there is an index defined only on record_time Now for an example to get traffic for this month, I use : SELECT `ip`,`type`,SUM(`inbound`) AS `in`, SUM(`outbound`) as `out` FROM `accounting` WHERE `record_time` BETWEEN 2006040100 AND 2006041316 GROUP BY `ip`,`type` this query takes aprox 7 seconds Using EXPLAIN gives : select_typetable type possible_keys key key_len ref rows Extra SIMPLE accounting range record_time record_time 8 NULL 362410 Using where; Using temporary; Using filesort If I remove the SUM functions I am getting also Using index in group-by and the query takes only 0.25 sec Is there anyway to optimize this query to get faster responses ? Thanks, --- Puiu Hrenciuc -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Optimizing SQL statement
Hmmm, I have omited that : I also have an index on (ip,type) in that order Barry [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Puiu Hrenciuc wrote: Hi, I have a table ( MyISAM, MySQL 5.0.20, FreeBSD ) containing network traffic data : record_time datetime - time when recording was added ip char(15) - ip that generated the traffic type tinyint(3) - traffic type ( 1 - local, 2 - internet ) inbound int(10) - in bytes outbound int(10) - out bytes Records are inserted each 5 minutes through a cron script. Currently there are 3,330,367 rows. Primary index is defined on ( ip, type, record_time ), columns in that order. Also there is an index defined only on record_time Now for an example to get traffic for this month, I use : SELECT `ip`,`type`,SUM(`inbound`) AS `in`, SUM(`outbound`) as `out` FROM `accounting` WHERE `record_time` BETWEEN 2006040100 AND 2006041316 GROUP BY `ip`,`type` this query takes aprox 7 seconds Using EXPLAIN gives : select_typetable type possible_keys key key_len ref rows Extra SIMPLE accounting range record_time record_time 8 NULL 362410 Using where; Using temporary; Using filesort If I remove the SUM functions I am getting also Using index in group-by and the query takes only 0.25 sec Is there anyway to optimize this query to get faster responses ? Thanks, --- Puiu Hrenciuc Set an index on ip and type and probably also on record_time Barry -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Wont insert into database
In the second example you are enclosing field values
with ticks ` instead of single quotes '
You don't get any errors because you don't check for errors,
if you put :
echo mysql_error();
after mysql_query () you will see the error.
To solve this problem simple replace ticks enclosing values
from the second example with single quotes :
mysql_query(INSERT INTO `orders` VALUES ('$ordernumber' ,
'$companyname' , '$billingaddress' , '$City' , '$State2' , '$Zip' ,
'$PhoneNumber' , '$FaxNumber' , '$WebPage' , '$EmailAddress' , '$Notes'
, '$Customer' , '$Startdate' , '$Completedate' , '$Biddate' ,
'$Bidamount' , '$ElecProjCost' , '$ElecProjBill' , '$ElecRem' ,
'$CtrlProjCost' , '$CtrlProjBill' , '$CtrlProjrem' , '$OthrProjCost' ,
'$OthrProjBill' , '$OthrProjrem', '$BondAm', '$BondBill' , '$BondRem' ));
- Original Message -
From: Brian E Boothe [EMAIL PROTECTED]
Newsgroups: mysql.general
To: mysql@lists.mysql.com
Sent: Friday, April 14, 2006 5:59 AM
Subject: Wont insert into database
i have two php Forms to insert data into a Mysql 4.0.20d Database, one
is just a simple form to test if it works the other is the Actual
application,
here is the test form and works Perfectly
?
$name=$_POST['Name'];
$value1=$_POST['value1'];
$value2=$_POST['value2'];
$sum=$_POST['sumfield'];
mysql_connect(localhost,root,) or die(mysql_error());
mysql_select_db(testbase) or die(mysql_error());
mysql_query(INSERT INTO `formadder` VALUES ('$name','$value1',
'$value2','$sum'));
Print Your information has been successfully added to the database.;
?
NOW , / here is the Form data submitter that doesn't Work Ive
checked and checked and check this damn Code with my form and nothing is
outta place, (i dont think)
BUT will NOT Insert anything to the database even though i get no
errors at all and it says Your information has been successfully added
to the database.; all the feilds are blank
?
$ordernumber = $_POST['ordernumber'];
$companyname = $_POST['companyname'];
$billingaddress = $_POST['billingaddress'];
$City = $_POST['City'];
$State2 = $_POST['State'];
$Zip = $_POST['Zip'];
$PhoneNumber= $_POST['PhoneNumber'];
$FaxNumber = $_POST['FaxNumber'];
$WebPage= $_POST['WebPage'];
$EmailAddress = $_POST['EmailAddress'];
$Notes = $_POST['Notes'];
$Customer= $_POST['Customer'];
$Startdate = $_POST['Startdate'];
$Completedate = $_POST['Completedate'];
$Biddate= $_POST['Biddate'];
$Bidamount = $_POST['Bidamount'];
$ElecProjCost = $_POST['ElecProjCost'];
$ElecProjBill = $_POST['ElecProjBill'];
$ElecRem = $_POST['ElecRem'];
$CtrlProjCost = $_POST['CtrlProjCost'];
$CtrlProjBill = $_POST['CtrlProjBill'];
$CtrlProjrem = $_POST['CtrlProjrem'];
$OthrProjCost = $_POST['OthrProjCost'];
$OthrProjBill = $_POST['OthrProjBill'];
$OthrProjrem = $_POST['OthrProjrem'];
$BondAm= $_POST['BondAm'];
$BondBill= $_POST['BondBill'];
$BondRem= $_POST['BondRem'];
mysql_connect(localhost,root,) or die(mysql_error());
mysql_select_db(workorder) or die(mysql_error());
mysql_query(INSERT INTO `orders` VALUES (`$ordernumber` ,
`$companyname` , `$billingaddress` , `$City` , `$State2` , `$Zip` ,
`$PhoneNumber` , `$FaxNumber` , `$WebPage` , `$EmailAddress` , `$Notes`
, `$Customer` , `$Startdate` , `$Completedate` , `$Biddate` ,
`$Bidamount` , `$ElecProjCost` , `$ElecProjBill` , `$ElecRem` ,
`$CtrlProjCost` , `$CtrlProjBill` , `$CtrlProjrem` , `$OthrProjCost` ,
`$OthrProjBill` , `$OthrProjrem`, `$BondAm` , `$BondBill` ,
`$BondRem` ));
Print Your information has been successfully added to the database.;
?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: arrg need help summing Colum's
I don't think your code should work anyway, you should really check PHP manual to get this work. mysql_query doesn't actually return the result, but a resource id that can be used with mysql_fetch_xxx functions. Try this : $link = mysql_connect(localhost,root,goobers) or die(mysql_error()); mysql_select_db(workorder, $link); $result = mysql_query(SELECT SUM(`ElecRem`) AS total FROM orders, $link); $row = mysql_fetch_array($result); $total=$row['total']; echo $total; Brian E Boothe [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] why cant i get this to sum ??? ? $link = mysql_connect(localhost,root,goobers) or die(mysql_error()); mysql_select_db(workorder, $link); $result = mysql_query(SELECT SUM(`ElecRem`) AS total FROM orders, $link); //$total = mysql_fetch_row($result); echo mysql_result($result); // outputs total //return $total[0]; echo mysql_error(); ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Add New User
Bruce Martin [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] When I log in as root using: mysql -u root -p mysql I get the mysql prompt: mysql I then issue this command or statement: mysqlGRANT ALL PRIVILEGES ON *.* TO 'testUser'@'' IDENTIFIED BY 'some_password' WITH GRANT OPTION; You haven't specified the host, try this : To connect only from the localhost : GRANT ALL PRIVILEGES ON *.* TO 'testUser'@'localhost' IDENTIFIED BY 'some_password' WITH GRANT OPTION; Or if you want to be able to connect from any host : GRANT ALL PRIVILEGES ON *.* TO 'testUser'@'%' IDENTIFIED BY 'some_password' WITH GRANT OPTION; Or if you would like to be able to connect from some host : GRANT ALL PRIVILEGES ON *.* TO 'testUser'@'host_name_or_ip' IDENTIFIED BY 'some_password' WITH GRANT OPTION; I get the following returned: Query OK, 0 rows affected (0.00 sec) Why is this not working? To test it further I try to log in as testUser but it tells me access denied for user [EMAIL PROTECTED] Even if I grant the testUser @ localhost. I can look in the user table and sure enough user [EMAIL PROTECTED] is there. Bruce Martin The Martin Solution PO Box 644 Delaware Water Gap, PA (570) 421-0670 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL/InnoDB-4.0.4 is released
I hoped that you will solve the MySQL consuming all computer's resources in 4.0.4, but nope, the problem is still there. I'm trying to run MySQL on Win ME and when started normally it consumes all resources of my computer. When I start it with --skip-innodb it works just fine. Why ? At least I saw you fixed WinMySQLAdmin. - Original Message - From: Heikki Tuuri [EMAIL PROTECTED] To: Philip Molter [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, October 02, 2002 5:48 PM Subject: Re: MySQL/InnoDB-4.0.4 is released Philip, - Original Message - From: Philip Molter [EMAIL PROTECTED] To: Heikki Tuuri [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, October 02, 2002 4:16 PM Subject: Re: MySQL/InnoDB-4.0.4 is released On Wed, Oct 02, 2002 at 02:12:23PM +0300, Heikki Tuuri wrote: : Hi! : : InnoDB is a table type which provides transactions, row level locking, : foreign key constraints, and a non-free hot backup tool to MySQL. : : InnoDB is included in both downloadable versions of MySQL-4.0: MySQL-Pro and : MySQL-Max. : : Release 4.0.4-beta is mainly a bug fix release. Will the bugfixes for this release (not necessarily the new/updated features) be backported to the 3.23.x series? Given that MySQL 4 isn't marked as stable yet, many of us using InnoDB no doubt still use 3.23.x. of course, the fix will be backported if the bug also existed in 3.23. The DESC bugs in 4.0.x are not present in 3.23. Since 4.0.x releases will come in more rapid succession than 3.23 releases, it may soon be that the most bug-free InnoDB is actually in the latest 4.0.x. * Philip Molter * Texas.net Internet * http://www.texas.net/ * [EMAIL PROTECTED] Regards, Heikki Innobase Oy sql query - Please check http://www.mysql.com/Manual_chapter/manual_toc.html; before posting. To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, send a message to the address shown in the List-Unsubscribe header of this message. If you cannot see it, e-mail [EMAIL PROTECTED] instead. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
