RE: [Possible Spam]Php programmer
-Original Message-
From: ratlhaga...@yahoo.com [mailto:ratlhaga...@yahoo.com]
Sent: September 18, 2012 7:31 AM
To: mysql@lists.mysql.com
Subject: [Possible Spam]Php programmer
Hi,
I need help, I'm working on a project that need time query in Mysql. I
want
to display a time in php page, that took mysql to execute 100 000 records
Sent from my BlackBerryR wireless device
-
S naMlgi
oltrishp/s.s.mylTuucb t/isylomq
Taken from the PHP website:
?php
function microtime_float()
{
list($usec, $sec) = explode( , microtime());
return ((float)$usec + (float)$sec);
}
$time_start = microtime_float();
# do your query here
$time_end = microtime_float();
$time = $time_end - $time_start;
echo It took $time seconds to do whatever you just did\n;
?
Steve.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
RE: function INTERVAL in view
-Original Message- From: Rick James [mailto:rja...@yahoo-inc.com] Sent: September 17, 2012 3:04 PM To: peter.braw...@earthlink.net; mysql@lists.mysql.com Subject: RE: function INTERVAL in view INTERVAL is a keyword. This is probably the root of the hiccup. Is that your Stored Function? Or UDF? on 2012-09-17 12:58 PM, h...@tbbs.net wrote: My MySQL is of version 5.5.8-log. I find I cannot save a query with INTERVAL in a view: redundant round brackets are added. If the query is SELECT INTERVAL(1, 2, 3, 4) within the frm file there is the expression interval((1, 2, 3, 4)) which is wrong. What is known about this? It is also a function: http://dev.mysql.com/doc/refman/5.5/en/comparison-operators.html#function_in terval as for why it does that? I have no idea. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: Mysql is toying me... why sometimes an insert or update can be slow!? I getting bald cuz this
-Original Message- From: Andrés Tello [mailto:mr.crip...@gmail.com] Sent: May 12, 2012 10:08 AM To: mysql Subject: Mysql is toying me... why sometimes an insert or update can be slow!? I getting bald cuz this While doning a batch process... show full processlist show: | 544 | prod | 90.0.0.51:51262 | tmz2012 | Query |6 | end | update `account` set `balance`= 0.00 + '-4000' where accountid='2583092' No other process, lo locking no nothing... so you take this same query... run it isolated, and the mufu... is just...f fast! update `account` set `balance`= 0.00 + '-4000' where accountid='2583092' Query OK, 0 rows affected (0.00 sec) Rows matched: 1 Changed: 0 Warnings: 0 ARRRG! I have seen this type of query take as long as 100+ seconds.. and I don't have a F*** clue... 2 things come to mind here... 1) indexes could not be set, so it may cause huge slowdowns on bigger tables 2) it could have been fast because of query caching, so it may have cached the index you were updating for, which may make it work fast. Also, if you run it manually, the first time may be slow, and subsequent attempts are faster (again, caching) I may be wrong here, but something to check out. Try this: Explain SELECT * FROM `account` WHERE accountid='2583092'; That will tell you what indexes it is using, if any Steve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: How to quickly detect if there are any crashed tables
AFAIK the tables will be locked one by one until checked/repaired. On May 10, 2012, at 3:07 PM, Adrian Fita wrote: On 10/05/12 21:51, Mihail Manolov wrote: You can enable check/recovery automatically by using myisam_recover. Look it up in the documentation. There is no way to repair them faster, though. Thanks for the quick response. This definetly looks like a useable solution. Do you know if during the auto-repair at startup, the server with all the tables will be available and answering to queries? Or will it make the tables available as it progresses with the repair? -- Fita Adrian I think you can scan the syslog for the mysql daemon, and it will show you any crashed, or problematic tables? If this is in fact the case, you could try that, and then run though the tables to check them later? Just pushing out a thought... Steve. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Group_Concat help...
Hello! I am trying to do something, but I can't seem to figure out how... My query is as follows: SELECT `user_id`, GROUP_CONCAT(DISTINCT `login_ip`) AS 'login_ips', COUNT(`id`) AS 'connections' FROM `mysql_test` WHERE `login_datetime` BETWEEN '2012-03-19 00:00:00' AND '2012-03-19 23:59:59' GROUP BY `user_id` HAVING COUNT(`id`) 2 ORDER BY COUNT(`id`) DESC LIMIT 0, 15; This query works, it gives me results like: user_id login_ipsconnections --- --- - 1 192.168.0.200,192.168.0.201 5 But what I am looking to add, is the number of connections per IP, so it would look kinda something like this: user_id login_ipsconnections --- --- - 1 192.168.0.200 (1),192.168.0.201 (3) 5 Is this possible to do with just 1 query? If so, how would I go about doing it?? And the table schema: CREATE TABLE `mysql_test` ( `id` int(11) unsigned NOT NULL auto_increment, `user_id` int(11) unsigned NOT NULL, `login_datetime` timestamp NOT NULL default CURRENT_TIMESTAMP, `login_ip` varchar(15) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=latin1; here is the data in my table: id user_id login_datetime login_ip -- --- --- --- 11 2012-03-19 11:57:38 192.168.0.200 21 2012-03-19 11:57:40 192.168.0.201 31 2012-03-19 11:57:42 192.168.0.200 41 2012-03-19 11:57:43 192.168.0.200 51 2012-03-19 11:57:45 192.168.0.201 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: Group_Concat help...
-Original Message- From: Mihail Manolov [mailto:mihail.mano...@liquidation.com] Sent: March 19, 2012 12:44 PM To: Steven Staples Cc: mysql@lists.mysql.com Subject: Re: Group_Concat help... Try this SELECT `user_id`, `login_ip`, COUNT(`id`) AS 'connections' FROM `mysql_test` WHERE `login_datetime` BETWEEN '2012-03-19 00:00:00' AND '2012-03-19 23:59:59' GROUP BY `user_id`, `login_ip` HAVING COUNT(`id`) 2 ORDER BY COUNT(`id`) DESC LIMIT 0, 15; On Mar 19, 2012, at 12:06 PM, Steven Staples wrote: SELECT `user_id`, GROUP_CONCAT(DISTINCT `login_ip`) AS 'login_ips', COUNT(`id`) AS 'connections' FROM `mysql_test` WHERE `login_datetime` BETWEEN '2012-03-19 00:00:00' AND '2012-03-19 23:59:59' GROUP BY `user_id` HAVING COUNT(`id`) 2 ORDER BY COUNT(`id`) DESC LIMIT 0, 15; Thanks for the reply Mihail. I was initially doing it that way, but if you look at the example of what I wanted as a reply, it doesn't work. I was hoping for a result that I could just plop into the PHP code, and I wouldn't have to manipulate it at all, but so far, it looks like I am going to have to do that... unless anyone else here has another idea... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
MySQL Session Variables with PHP
Good [insert time of day here] all!
I am trying to reorder my auto-inc field in my database, and I have
successfully done it with my front end that I use (SQLYog) with the
following code:
SET @var_name = 0;
UPDATE `my_database`.`my_table` SET `id` = (@var_name := @var_name +1);
Now, when I try this within PHP... I can't get it to work at all. I assume
that the SET is the issue, but I am not 100% sure.
I use the PEAR MDB2 class, and I have tried it in 1 statement, but it
failed, and I tried it with 2 statements, it didn't puke on it, but it
didn't work either.
?php
# db connection is already set up #
echo $db-exec('SET @var_name = 0;')
echo 'br /';
echo $db-exec('UPDATE `my_database`.`my_table` SET `id` =
(@var_name:= @ var_name +1);');
exit;
?
Does anyone know how I can do this within PHP? Worst case scenario, is that
I just write a php shell() command instead, but I would rather avoid that if
at all possible.
Thanks in advance!
Steven Staples
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
RE: MySQL Session Variables with PHP
-Original Message-
From: Peter Brawley [mailto:peter.braw...@earthlink.net]
Sent: February 22, 2012 11:07 AM
To: Steven Staples; mysql@lists.mysql.com
Subject: Re: MySQL Session Variables with PHP
On 2/22/2012 9:47 AM, Steven Staples wrote:
Good [insert time of day here] all!
I am trying to reorder my auto-inc field in my database, and I have
successfully done it with my front end that I use (SQLYog) with the
following code:
SET @var_name = 0;
UPDATE `my_database`.`my_table` SET `id` = (@var_name := @var_name +1);
Now, when I try this within PHP... I can't get it to work at all. I
assume
that the SET is the issue, but I am not 100% sure.
I use the PEAR MDB2 class, and I have tried it in 1 statement, but it
failed, and I tried it with 2 statements, it didn't puke on it, but it
didn't work either.
?php
# db connection is already set up #
echo $db-exec('SET @var_name = 0;')
echo 'br /';
echo $db-exec('UPDATE `my_database`.`my_table` SET `id` =
(@var_name:= @ var_name +1);');
exit;
?
Does anyone know how I can do this within PHP? Worst case scenario, is
that
I just write a php shell() command instead, but I would rather avoid
that
if
at all possible.
The manual warns us not to rely on repeat user var assignments, but your
approach works for me:
$conn=mysql_connect( ... );
mysql_select_db(test);
mysql_query( drop table if exists t ) or exit(mysql_error());
mysql_query( create table t (id int) ) or exit(mysql_error());
for( $i=0; $i10; $i++ ) mysql_query( insert into t values(0) ) or
exit(mysql_error());
mysql_query( set @var=0 ) or exit(mysql_error());
mysql_query( update t set id=(@var:=@var+1) ) or exit(mysql_error());
$res = mysql_query( select id from t ) or exit(mysql_error());
while( $row = mysql_fetch_row( $res )) echo $row[0], ;
Output: 1 2 3 4 5 6 7 8 9 10
PB
I feel like a TOTAL newb here...
If you look at my php example, I forgot the ; at the end of the first db
query... so it failed out silently (my php errors are off, changed that too)
Now it works... lol
As for why I want to do this? I have a table that I constantly add, and
remove items from, and I do it by looking up, and then deleting them (it is
a long story/process). Anyways, the script stops every 8 hours, and
restarts. So what I want to do, is reset all the auto-inc counters back to
starting at 1, so that they don't get too large and out of control. I
don't need to reference the ID's anywhere else, just when I go through each
iteration. I know that this *could* be bad for most databases/tables, but
in my case, it is not going to be an issue.
Thanks, and sorry for my ID10T error, since it works just fine!
Steve.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Relication Issues
Good morning list, I am having issues with my replication setup. it seems that every few weeks/months something happens, and I have to restart it, skip a row, or delete, rsync and restart replication from scratch. The databases that are being replicated, are rather large... there are about 12 new tables every year, each consisting of about 2-4g in size each (today's total database size is 83g). Again, things work fine for a while, and then there are issues. The backup/slave is on the same network/datacenter, so network speed isn't an issue. I just don't understand why inconsistencies keep arising. Is there a better way to do live backups, or have a hot space in the event of a catastrophe? Is there 3rd party software that would better achieve data integrity or something? Any help here would be appreciated. Thanks! Steve. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Relication Issues
My Apologies, The tables are all MyISAM, most of the inserts/updates/deletes are done through stored procedures on the master. There are about 2 stored procedure calls per second, consisting of a whole bunch of queries, updates, and inserts within them. The latest issue(s) we're having, are: Could not execute Write_rows event on table xxx.x; Duplicate entry '20-1016792' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's mas Could not execute Write_rows event on table xxx.x; Duplicate entry '44870420' for key 'PRIMARY', Er Update_rows event on table xxx.x; Can't find record in 'x', Error_code: 1032; handler error HA_ERR_KEY (Where xxx is the databse, and x is the table) The replication broke yesterday morning for some reason, and the primary key index was at around 900,000. When we found out it was down, and ended up getting to work on it, we tried to restart it and the next ID it was inserting for was 1,016,792. So we're missing about 100,000 enteries, and they are not in the bin log (as far as I can see). Steve. -Original Message- From: Reindl Harald [mailto:h.rei...@thelounge.net] Sent: November 10, 2011 8:42 AM To: mysql@lists.mysql.com Subject: Re: Relication Issues replication is buggy sometimes but without any outputs desribing your problem nobody can really help you - memory tables as example are making much more troubles as myisam Am 10.11.2011 14:26, schrieb Steven Staples: Good morning list, I am having issues with my replication setup. it seems that every few weeks/months something happens, and I have to restart it, skip a row, or delete, rsync and restart replication from scratch. The databases that are being replicated, are rather large... there are about 12 new tables every year, each consisting of about 2-4g in size each (today's total database size is 83g). Again, things work fine for a while, and then there are issues. The backup/slave is on the same network/datacenter, so network speed isn't an issue. I just don't understand why inconsistencies keep arising. Is there a better way to do live backups, or have a hot space in the event of a catastrophe? Is there 3rd party software that would better achieve data integrity or something? Any help here would be appreciated. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: dynamic alias?
DATE_ADD(NOW(), INTERVAL 1 DAY) will give you tomorrow... Not sure if that is what you're looking for or not... Steven Staples -Original Message- From: Eric Bloomquist [mailto:eric_bloomqu...@cooley-dickinson.org] Sent: August 27, 2010 12:17 PM To: mysql@lists.mysql.com Subject: dynamic alias? Hi all, I'm wondering if it's possible to have a dynamic alias in MySQL. The result I'm looking for is essentially: +-+---++ | Provider Name | Facility Name | Appts on 8/28/2010 | == +-+---++ | Mildred Ratched | Oregon State Hospital | 12 | | Henry Jekyll| London Internal Medicine | 3 | | ... | || Where Appts on 8/28/2010 instead includes whatever tomorrow's date is when the query is run. Is it possible to do something like this in a MySQL query? Thanks, Eric -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=sstap...@mnsi.net No virus found in this incoming message. Checked by AVG - www.avg.com Version: 9.0.851 / Virus Database: 271.1.1/3089 - Release Date: 08/27/10 02:34:00 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Reduce dataset but still show anomalies
I am not too good with charting (even though I would like to be), but what about getting the max, min and avg, if the max/min is greater than x% of the avg, show that... ? Just throwing out ideas... prolly not useful... but may cause a better idea ;) Steven Staples -Original Message- From: Bryan Cantwell [mailto:bcantw...@firescope.com] Sent: August 20, 2010 11:24 AM To: mysql Subject: Re: Reduce dataset but still show anomalies Yes, but I DON'T want eh spikes smoothed out On Fri, 2010-08-20 at 17:16 +0200, Jangita wrote: On 20/08/2010 5:12 p, Bryan Cantwell wrote: I am trying to produce charts for large amounts of data. I already limit the user to a smaller time frame in order to reduce the possible data points, but still can end up with far more data points than are clearly plottable on a chart. Does anyone have an idea of how I can drop insignificant points, or average the data or do something to end up with no more than about 3k points and still show spikes and dips in the charts so my users can still clearly identify anomalies in their charts? I don't want to smooth out the spikes and dips if at all possible. I considered running through the dataset and doing a compare of point 2 to point 1 and if it is close in value throw it away, otherwise keep it. That probably would not work on a 'noisy' chart however... THanks, Bryancan Have you tried instead of showing per minute, show the average per hour, or per day; this will generally smoothen the points out a little; In my case if i show registrations per day i get dips every Saturday and Sunday so it looks all jagged, But per week doesn't show the Saturday/Sunday dips... -- Jangita | +256 76 91 8383 | Y! MSN: jang...@yahoo.com Skype: jangita | GTalk: jangita.nyag...@gmail.com No virus found in this incoming message. Checked by AVG - www.avg.com Version: 9.0.851 / Virus Database: 271.1.1/3023 - Release Date: 08/20/10 02:35:00 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Reduce dataset but still show anomalies
On another thought, what about if you group it by whatever, if the MIN()/MAX() is greater than X times STDDEV(), show MIN() or MAX() ? I just recalled a conversation with my boss the other week about the STDDEV() Steven Staples -Original Message- From: Steven Staples [mailto:sstap...@mnsi.net] Sent: August 20, 2010 11:32 AM To: bcantw...@firescope.com; 'mysql' Subject: RE: Reduce dataset but still show anomalies I am not too good with charting (even though I would like to be), but what about getting the max, min and avg, if the max/min is greater than x% of the avg, show that... ? Just throwing out ideas... prolly not useful... but may cause a better idea ;) Steven Staples -Original Message- From: Bryan Cantwell [mailto:bcantw...@firescope.com] Sent: August 20, 2010 11:24 AM To: mysql Subject: Re: Reduce dataset but still show anomalies Yes, but I DON'T want eh spikes smoothed out On Fri, 2010-08-20 at 17:16 +0200, Jangita wrote: On 20/08/2010 5:12 p, Bryan Cantwell wrote: I am trying to produce charts for large amounts of data. I already limit the user to a smaller time frame in order to reduce the possible data points, but still can end up with far more data points than are clearly plottable on a chart. Does anyone have an idea of how I can drop insignificant points, or average the data or do something to end up with no more than about 3k points and still show spikes and dips in the charts so my users can still clearly identify anomalies in their charts? I don't want to smooth out the spikes and dips if at all possible. I considered running through the dataset and doing a compare of point 2 to point 1 and if it is close in value throw it away, otherwise keep it. That probably would not work on a 'noisy' chart however... THanks, Bryancan Have you tried instead of showing per minute, show the average per hour, or per day; this will generally smoothen the points out a little; In my case if i show registrations per day i get dips every Saturday and Sunday so it looks all jagged, But per week doesn't show the Saturday/Sunday dips... -- Jangita | +256 76 91 8383 | Y! MSN: jang...@yahoo.com Skype: jangita | GTalk: jangita.nyag...@gmail.com No virus found in this incoming message. Checked by AVG - www.avg.com Version: 9.0.851 / Virus Database: 271.1.1/3023 - Release Date: 08/20/10 02:35:00 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=sstap...@mnsi.net No virus found in this incoming message. Checked by AVG - www.avg.com Version: 9.0.851 / Virus Database: 271.1.1/3023 - Release Date: 08/20/10 02:35:00 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Moving from one MySQL server to three MySQL servers?
Have you double checked the hardware? Are you using 5400rpm drives, or 15k rpm drives? I/O bottlenecks are common, if you can't read the data fast enough, then it will definitely be slower, and appear to have more issues that it really does. If the client can't/won't change/alter the code, then maybe looking at changing the hardware would be better. Having a smaller drive size raid array with faster harddrives may solve the I/O bottleneck if that is the case. And maybe it is just poorly written queries with crappy indexing? Maybe look at the slow query log, and ensure that the RIGHT indexes are there (140gb/21gb index doesn't mean that the indexes are the correct ones) Going to a replication setup may not be the solution to your problems, and could just be a bandaid (and prolly cause you many sleepless nights maintaining data integrity). Find out the cause of the problem, before adding to it. Steven Staples -Original Message- From: Nunzio Daveri [mailto:nunziodav...@yahoo.com] Sent: August 4, 2010 2:40 PM To: mysql@lists.mysql.com Subject: Moving from one MySQL server to three MySQL servers? Hello Gurus :-) I was running a simple load generator against our 16GB Dual Quad core server and it pretty much came down to it's knees within two hours of running tests. The customer DOES NOT WANT to change any code, they just want to throw hardware at it since it took them a year to create all of the code. It is a 140GB database with 21GB of indexs all using InnoDB - currently doing 70% reads and 30% writes. My question is what is the best way of distributing the load without changing any of the php / perl code that their web server uses? This is what I am thinking but need someone to tell me it is a good idea or bad please? 1. Setup a single master and 2 slaves. The question is how to tell the web servers to get all the read data from the slaves and to only write to the master? 2. Install a MySQL proxy box and let mysql proxy handle the load, problem is now it is the SPOF! 3. Use DNS round robin, BUT how to tell round robin to ONLY go to master for writes and ONLY use one of the 2 slaves for reads? Any links, ideas or suggestions is most appreciated. TIA... Nunzio No virus found in this incoming message. Checked by AVG - www.avg.com Version: 9.0.851 / Virus Database: 271.1.1/3023 - Release Date: 08/04/10 00:45:00 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: [MySQL] Re: Decimal points
Just out of curiosity, why not do it in the application layer? Or maybe, you can try: SELECT BINARY 1+1.2; = 2.2 SELECT BINARY 1+1.0; = 2 SELECT CAST(1+1.2 AS UNSIGNED); = 2 SELECT CAST(1+1.6 AS UNSIGNED); = 3 (so I guess rounding happens here) And then I guess ultimately, you could also use FLOOR() CEIL() or ROUND() SELECT ROUND(1+1.6, 0); = 3 SELECT FLOOR(1+1.6); = 2 SELECT CEIL(1+1.6); = 3 Steven Staples -Original Message- From: Ashley M. Kirchner [mailto:ash...@pcraft.com] Sent: July 20, 2010 1:52 PM To: mysql@lists.mysql.com Subject: Re: [MySQL] Re: Decimal points On 7/20/2010 10:07 AM, Chris W wrote: I try to avoid asking why but in this case I have to. I can't imagine wanting to have a list of numbers displayed and not have them all aligned right with the sane number of digits after the decimal point. So why would you even want to do this is? Wish I can give you an answer, but it's a requirement from the client. Their application needs it displayed that way. '101' and '101.0' appear to have completely different meanings. Especially since more often than not, it's followed by a letter. I don't know, I'm just the programmer here. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=sstap...@mnsi.net No virus found in this incoming message. Checked by AVG - www.avg.com Version: 9.0.839 / Virus Database: 271.1.1/3009 - Release Date: 07/20/10 02:36:00 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Why is MySQL always linked to Php?
MySQL and PHP go together very well in the web development world. Almost all of PHP websites use MySQL as their database storage engine, as almost all hosting companies have installed PHP and MySQL on their servers. Maybe the jobs you're looking at, are for programmers/developers, who can take care of the mysql as well? Steven Staples -Original Message- From: alba.albetti [mailto:alba.albe...@libero.it] Sent: July 15, 2010 10:00 AM To: mysql Subject: Why is MySQL always linked to Php? Browsing the Web I've seen that usually companies look for developers working on MySQL and Php. Why are the two things linked? I mean I've not found any requests for just a MySQL developer or DBA (as for example it happens for Oracle), but it's always requested a MySQL/Php expert. I ask for it 'cause I've always been a developer/DBA on RDBMS (Oracle and DB2) and as I've been learning MySQL for few weeks, I'd like to know whether and why it's so important to learn Php as well. It would be so difficult to find a job as MySQL developer/DBA without knowing Php as well. Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=sstap...@mnsi.net No virus found in this incoming message. Checked by AVG - www.avg.com Version: 9.0.830 / Virus Database: 271.1.1/2991 - Release Date: 07/15/10 07:09:00 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: phpMyAdmin and other management tools
I personally use SQLYog, and as for it not having the query creation... it has a fairly decent query builder, but it is in the paid version, not the community one. I like all the features it has, and I use most of them. Granted, it is always a good idea to know how to use the command line, just incase you're locked out of it somehow, or if you need to repair, backup or create users. But if you're a windoze user, 99% of them don't know what a command line is :) Steven Staples -Original Message- From: Krishna Chandra Prajapati [mailto:prajapat...@gmail.com] Sent: July 1, 2010 7:58 AM To: David Stoltz Cc: mysql@lists.mysql.com Subject: Re: phpMyAdmin and other management tools Hi Dave, I recommend you to use Toad for MySQL. It has wizard for query creation phpMyAdimn, mysql workbench, sqlyog lack this feature. Krishna On Thu, Jul 1, 2010 at 5:08 PM, David Stoltz dsto...@shh.org wrote: Hi Folks, I'm currently using phpMyAdmin to manage the mySQL databases. I'm wondering what most people like to use? I know there is mySQL Workbench, which I haven't really fooled with yet Can anyone comment on what they use and why? Thanks! Dave No virus found in this incoming message. Checked by AVG - www.avg.com Version: 9.0.830 / Virus Database: 271.1.1/2972 - Release Date: 06/30/10 02:36:00 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Two Primary Keys
@Dušan Pavlica; I must say thank you. This is really interesting, and in the 7 years I've been using mysql and sql, I've never know this (or had, but didn't realize what I had done). This little bit of information could make for some interesting changes to a couple of my projects I am working on, where I've done this, but done it in code, rather than in mysql. Steven Staples -Original Message- From: Dušan Pavlica [mailto:pavl...@unidataz.cz] Sent: June 29, 2010 11:26 AM To: Victor Subervi Cc: mysql@lists.mysql.com Subject: Re: Two Primary Keys Hi, try this and you will see exactly how autoincrement behaves in MyISAM tables when it is part of primary key. 1) declare table like this: CREATE TABLE `test_tbl` ( `field1` int(10) unsigned NOT NULL default '0', `field2` int(10) unsigned NOT NULL auto_increment, `field3` char(10) NOT NULL default '', PRIMARY KEY (`field1`,`field2`) ) ENGINE=MyISAM; 2) then insert some values INSERT INTO test_tbl (field1, field3) VALUES(1,'test1'),(2,'test2'),(1,'test3'),(2,'test4'); 3) see what's in the table SELECT * FROM test_tbl ORDER BY field1; result is: 1, 1, 'test1' 1, 2, 'test3' 2, 1, 'test2' 2, 2, 'test4' field2 is unique only in context of field1. Hth, Dusan Victor Subervi napsal(a): 2010/6/29 João Cândido de Souza Neto j...@consultorweb.cnt.br As far as I know, if you have an auto_increment primary key, you cant have any other field in its primary key. Makes sense. Actually, I was just copying what someone else gave me and adding the auto_increment, then I got to wondering, what is the purpose of having two primary keys? TIA, V -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=sstap...@mnsi.net No virus found in this incoming message. Checked by AVG - www.avg.com Version: 9.0.830 / Virus Database: 271.1.1/2917 - Release Date: 06/29/10 02:35:00 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
MySQL Upgrading
Hi, I am looking at upgrading my servers Debian version from Etch to Lenny, and in doing that, I think it will upgrade MySQL from 5.0.32 to the lenny version, which is 5.0.53 (I think). I have also been thinking about using the 'dotdeb' packages, which will upgrade it even further to 5.1.47. I have done this on a test server, and it all my stored procedures and stuff work fine, so now to my question. Will this break any replication if I don't upgrade my replication server that is still running 5.0.32 (until I upgrade that server as well, which could be a few weeks due to timing)? The other issue, is that the replication server is running multiple instances of the same MySQL on different ports, so that I can replicate multiple sources to a single server (that has attached tape drives for backup purposes), is it possible to run both versions of mysql (the 5.0.32 and the 5.1.47)?granted, this is not the debian mailing list, just thought I would ask that last part ;) Steven Staples -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Table Consistency/analize/check/repair
Hello, I had an issue yesterday, where one of my tables ended up being closed improperly, and needed to be repaired. This isn't really an issue in itself, but I didn't know about it until 2 hours after it happened, and a script was trying to write to that table, and it failed, which caused a backup on that script for inserting into another table. ANYWAY, what I am wondering is, is what would be the best way to check all my tables for errors/issues, and then run a repair on the table if it finds an issue? I have at least 30 tables, each having about 3-6 million rows in each, and each table is about 2-3gigabytes in size. So, I would like to be able to check them only if they have anything that has changed (there is a table created for each month of the year, for the last 2 years), it would be nice if I didn't have to stop or put a lock on each table if I didn't have to, and it would also be great if it was quick (running at like 2am, and completing before 6am would be my time frame, and then Sunday morning from 2am to 8-10am is ok) Any ideas for scripts, or premade scripts would be great. Also, if this would traverse to the slave mysql server too, that would be ideal. Thanks in advance, Steven Staples -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
analyze table issue
Ok, I know I am an idiot sometimes, and I think this falls into that category. I use SQLYog as my MySQL front end editor. I was looking to do some things, and was going to run a check table command, and clicked the analyze button, and didn't realize that all the tables in the database were selected. Long story short, that was at 9am this morning, and it is 230pm now, and it is still running. What are the ramifications of killing that process? The database is only a total of 8 tables, and consuming about 20gb, but it is approaching home time, and I don't want this to run all weekend long... Can I just kill it, or will it corrupt the table it is currently on? Steven Staples -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: analyze table issue
Nevermind, it just finished... holy uggh! Steven Staples -Original Message- From: Steven Staples [mailto:sstap...@mnsi.net] Sent: June 18, 2010 2:31 PM To: mysql@lists.mysql.com Subject: analyze table issue Ok, I know I am an idiot sometimes, and I think this falls into that category. I use SQLYog as my MySQL front end editor. I was looking to do some things, and was going to run a check table command, and clicked the analyze button, and didn't realize that all the tables in the database were selected. Long story short, that was at 9am this morning, and it is 230pm now, and it is still running. What are the ramifications of killing that process? The database is only a total of 8 tables, and consuming about 20gb, but it is approaching home time, and I don't want this to run all weekend long... Can I just kill it, or will it corrupt the table it is currently on? Steven Staples -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=sstap...@mnsi.net No virus found in this incoming message. Checked by AVG - www.avg.com Version: 9.0.829 / Virus Database: 271.1.1/2917 - Release Date: 06/18/10 02:35:00 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Strange GREATEST() result in 5.0.32
Baron, Out of curiosity, do you (or anyone else) know what could be an issue with upgrading to even 5.0.93? or even the 5.1 branch? There are a lot of stored procedures/functions, as well as the fact that it is being replicated (the backup server is running multiple instances, and is replicating 3 other servers). So, all the sql databases will have to be updated/upgraded, but is there anything I/we should be made aware of before we go ahead? (there is a lot of release notes to sift through) Steven Staples -Original Message- From: baron.schwa...@gmail.com [mailto:baron.schwa...@gmail.com] On Behalf Of Baron Schwartz Sent: June 15, 2010 4:56 PM To: mysql@lists.mysql.com Subject: Re: Strange GREATEST() result in 5.0.32 Steven, On Tue, Jun 1, 2010 at 11:15 AM, Steven Staples sstap...@mnsi.net wrote: Hello all I have a stored procedure that probably does a lot more than it should, but it works fine on my test server (which is running 5.0.67). When I moved it over to the production server, (which is running 5.0.32 and I never thought to check that the versions were the same before) it works almost perfectly. It sounds like you're running into a bug, simply put. 5.0.32 is very old and an amazing amount of bugs have been fixed since then. I would not even consider running it in production. I know it'll be tough to upgrade, but if you don't, my experience is that another of the unfixed bugs is going to cause you serious pain anyway, such as crashing your server. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=sstap...@mnsi.net No virus found in this incoming message. Checked by AVG - www.avg.com Version: 9.0.829 / Virus Database: 271.1.1/2917 - Release Date: 06/15/10 02:35:00 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: WHERE clause from AS result
Putting the 'HAVING' in there, works perfectly :) THANKS! Steven Staples -Original Message- From: SHAWN L.GREEN [mailto:shawn.l.gr...@oracle.com] Sent: June 10, 2010 8:03 PM To: Steven Staples Cc: 'MySql' Subject: Re: WHERE clause from AS result On 6/10/2010 4:38 PM, Steven Staples wrote: Ok, I have done it before, where I have used the AS result in an ORDER BY, but now, I can't figure out why I can't use it in a WHERE clause? SELECT `email`, (SELECT CONCAT(`phone_pref`, '-', `phone_suff`) FROM `pnums` WHERE `id`=`usertable`.`id`) AS pnum FROM `usertable` WHERE pnum LIKE '555-12%'; It gives me this error: Error Code : 1054 Unknown column 'pnum' in 'where clause' It has to do with the order in which things happen in the query. The results of the subquery are computed in the FROM...WHERE... part of the query. There is no way that the results could be named so that the WHERE clause could handle them. This is why aliases are available for use in the clauses processed after the WHERE clause - the GROUP BY and HAVING clauses. Try this as an alternative: SELECT `email`, (SELECT CONCAT(`phone_pref`, '-', `phone_suff`) FROM `pnums` WHERE `id`=`usertable`.`id`) AS pnum FROM `usertable` HAVING pnum LIKE '555-12%'; No virus found in this incoming message. Checked by AVG - www.avg.com Version: 9.0.829 / Virus Database: 271.1.1/2917 - Release Date: 06/10/10 02:35:00 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
WHERE clause from AS result
Ok, I have done it before, where I have used the AS result in an ORDER BY, but now, I can't figure out why I can't use it in a WHERE clause? SELECT `email`, (SELECT CONCAT(`phone_pref`, '-', `phone_suff`) FROM `pnums` WHERE `id`=`usertable`.`id`) AS pnum FROM `usertable` WHERE pnum LIKE '555-12%'; It gives me this error: Error Code : 1054 Unknown column 'pnum' in 'where clause' Any ideas? Steven Staples -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Help needed on query on multiple tables
How about this? SELECT `first_table`.`names` , `first_table`.`version` , (SELECT COUNT(`other_table`.`names`) FROM `other_table` WHERE `other_table`.`this_id` = `first_table`.`id`) AS 'count' FROM `first_table` WHERE `first_table`.`progress` 0; Granted, you have not provided structure or names of the tables so this is just my interpretation, but maybe something like this could give you a starting point? Steven Staples -Original Message- From: Michael Stroh [mailto:st...@astroh.org] Sent: June 3, 2010 11:24 AM To: MySql Subject: Help needed on query on multiple tables Hi everyone. I'm trying to create a certain MySQL query but I'm not sure how to do it. Here is a stripped down version of the result I'm aiming for. I'm pretty new to queries that act on multiple tables, so apologize if this is a very stupid question. I have one table (data) that has two columns (names and progress). I have a second table (items) that has two columns (names and version). I'd like to do a query that produces the name of every record in data that has progress set to 0 and the number of records in the items table that have the same value in each table.names field. I can perform this by using two sets of queries, one that queries the data table and then loop through the names to do a count(names) query, but I'm not sure if I can somehow do it in one query. Thanks in advance! Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=sstap...@mnsi.net No virus found in this incoming message. Checked by AVG - www.avg.com Version: 9.0.829 / Virus Database: 271.1.1/2895 - Release Date: 06/03/10 02:25:00 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Help needed on query on multiple tables
I am glad that I was able to help someone finally :) There may be other ways to do this, but that was what first came to mind. I would maybe run an explain on that query to ensure that it is using indexes. Steven Staples -Original Message- From: Michael Stroh [mailto:st...@astroh.org] Sent: June 3, 2010 11:55 AM To: Steven Staples Cc: 'MySql' Subject: Re: Help needed on query on multiple tables Thanks! That did it perfectly! Michael On Jun 3, 2010, at 11:45 AM, Steven Staples wrote: How about this? SELECT `first_table`.`names` , `first_table`.`version` , (SELECT COUNT(`other_table`.`names`) FROM `other_table` WHERE `other_table`.`this_id` = `first_table`.`id`) AS 'count' FROM `first_table` WHERE `first_table`.`progress` 0; Granted, you have not provided structure or names of the tables so this is just my interpretation, but maybe something like this could give you a starting point? Steven Staples -Original Message- From: Michael Stroh [mailto:st...@astroh.org] Sent: June 3, 2010 11:24 AM To: MySql Subject: Help needed on query on multiple tables Hi everyone. I'm trying to create a certain MySQL query but I'm not sure how to do it. Here is a stripped down version of the result I'm aiming for. I'm pretty new to queries that act on multiple tables, so apologize if this is a very stupid question. I have one table (data) that has two columns (names and progress). I have a second table (items) that has two columns (names and version). I'd like to do a query that produces the name of every record in data that has progress set to 0 and the number of records in the items table that have the same value in each table.names field. I can perform this by using two sets of queries, one that queries the data table and then loop through the names to do a count(names) query, but I'm not sure if I can somehow do it in one query. Thanks in advance! Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=sstap...@mnsi.net No virus found in this incoming message. Checked by AVG - www.avg.com Version: 9.0.829 / Virus Database: 271.1.1/2895 - Release Date: 06/03/10 02:25:00 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=st...@astroh.org No virus found in this incoming message. Checked by AVG - www.avg.com Version: 9.0.829 / Virus Database: 271.1.1/2895 - Release Date: 06/03/10 02:25:00 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Strange GREATEST() result in 5.0.32
Hello all
I have a stored procedure that probably does a lot more than it should, but
it works fine on my test server (which is running 5.0.67). When I moved it
over to the production server, (which is running 5.0.32 and I never thought
to check that the versions were the same before) it works almost perfectly.
The issue is when this runs:
GREATEST(d_UPDATE_Time, d_START_Time)
The value is '2010-05-21 20:26:18' and not '2010-05-26 21:49:51' even though
it is greater.
Both of these variables are declared in the top of the procedure:
DECLARE d_UPDATE_Time DATETIME;
DECLARE d_START_Time DATETIME;
When I put this in, to see the values of the variables, I get the right
data:
SELECT d_UPDATE_Time, d_START_Time;
d_UPDATE_Timed_START_Time
--- ---
2010-05-26 21:49:51 2010-05-21 20:26:18
So if I ran this from the command line:
SELECT GREATEST('2010-05-26 21:49:51', '2010-05-21 20:26:18');
I get:
RESULT
---
2010-05-26 21:49:51
So it works perfectly fine when it runs outside of the stored procedure, but
inside the procedure, it returns the wrong date :(
Is there something I am doing wrong? I can't seem to see anything wrong.
Also, upgrading from 5.0.32 would require upgrading both the master and
slave, and right now, that is not a good idea (we are discussing it though)
Steven Staples
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Using RAND to get a unique ID that has not been used yet
If you wanted to use/go that route, then why not select a random limit 1 from that table, and then delete that row? SELECT `column` FROM `table` ORDER BY RAND() LIMIT 1; On a side note, I would use the auto-inc field still, and store this number in another field. Steven Staples -Original Message- From: Jim Lyons [mailto:jlyons4...@gmail.com] Sent: May 28, 2010 11:49 AM To: Andre Matos Cc: mysql@lists.mysql.com Subject: Re: Using RAND to get a unique ID that has not been used yet If your specs are that specific (IDs must be between 1 and 99) then you could create a 99-row table with one integer column and prefill it with the numbers 1 to 99 in random order. Then you could write a function that would select and return the first number in the table, then delete that record so you would not reuse it. Once you've done the work of sorting 99 numbers in random order (which can be done anywhich way) it's easy and you don't have to loop an indeterminant number of times. You would be looping an increasing number of times as you begin to fill up the table. Jim On Fri, May 28, 2010 at 10:38 AM, Andre Matos andrema...@mineirinho.org wrote: Hi All, I have a table that uses auto_increment to generate the Id automatically working fine. However, I need to create a new table where the Id must be a number generated randomly, so I cannot use the auto_increment. MySQL has a function RAND. So I could use something like this: SELECT FLOOR(RAND() * COUNT(*)) AS RandId FROM mytable But, let's suppose that the RandId is a number that was already used in the table. Then I need to run the SELECT again and again until I find a number that hasn't been used. Is there a way to have this SELECT to loop until it finds a number that hasn't been used? The RandId must be only numbers and length of 6 (from 1 to 99). No other character is allowed. Thanks for any help! Andre -- Andre Matos andrema...@mineirinho.org -- 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=sstap...@mnsi.net No virus found in this incoming message. Checked by AVG - www.avg.com Version: 9.0.819 / Virus Database: 271.1.1/2895 - Release Date: 05/28/10 02:25:00 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Obtain week number between years
You could also try it this way, and then in the application, you can find out what the dates are, that they are between. SELECT YEARWEEK(`datefield`) AS 'week', COUNT(`visits`) AS 'visits' FROM `mytable` WHERE YEAR(`datefield`) = '2009' GROUP BY YEARWEEK(`datefield`); (this was just taken off the top of my head, but it should give something that you're looking for... hopefully ;) ) Steven Staples -Original Message- From: baron.schwa...@gmail.com [mailto:baron.schwa...@gmail.com] On Behalf Of Baron Schwartz Sent: April 29, 2010 8:34 AM To: MySql Subject: Re: Obtain week number between years Neil, I would start with something like this, assuming the date column is called d: SELECT count, or sum, or whatever aggregate function FROM table GROUP BY d - INTERVAL DAYOFWEEK(d) DAY; - Baron On Thu, Apr 29, 2010 at 8:12 AM, Tompkins Neil neil.tompk...@googlemail.com wrote: Hi We need to produce a query to return the total number of user visits between two date ranges that span over two year e.g from 2009-04-29 to 2010- 04-29. My question is how can I compute the totals for each week within a query ? for example 2009-04-29 to 2009-05-06 100 visits 2009-05-07 to 2009-05-14 250 visits etc Cheers Neil -- Baron Schwartz Percona Inc http://www.percona.com/ Consulting, Training, Support Services for MySQL -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=sstap...@mnsi.net No virus found in this incoming message. Checked by AVG - www.avg.com Version: 9.0.814 / Virus Database: 271.1.1/2783 - Release Date: 04/29/10 02:27:00 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Join syntax problem
As Tom Worster said, print($query); would show you what the query was trying to run. Without testing it, you also have some other whitespace issues between the hw.wildlife and FROM, and also, i m unsure of the asterix infront of the *images. On another note, when I do my JOINs, I tend to write ON (table1.field=jointable.field) rather than just ON (field). And on a final thought, the where cause, seems to be the join clause as well, so isn't that redundant? (or is would that only be in the way that i said i do my joins?) ++ | Steven Staples | ++ | I may be wrong, but at least I tried...| ++ -Original Message- From: Gary [mailto:g...@paulgdesigns.com] Sent: April 26, 2010 10:29 PM To: mysql@lists.mysql.com Subject: Re: Join syntax problem Thanks for the replies. It was my understanding that whitespace is ignored, and I did not think that not having space, in particular with . would result in an error message. Gary Gary gp...@paulgdesigns.com wrote in message news:20100426233621.10789.qm...@lists.mysql.com... I cant seem to get this working. $query=SELECT im.image_id, im.caption, im.where_taken, im.description, im.image_file, im.submitted, kw.fox, kw.wolves, kw.wildlife, kw.american, kw.scenic, kw.birds, kw.africa, kw.eagles, kw.hunter . FROM *images AS im.JOIN keywords AS kw USING (image_id) . WHERE ky.image_id = im.image_id; Gets me this error message. 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 'keywords AS kw USING (image_id)WHERE ky.image_id = im.image_id' at line 1 Anyone see where I am going wrong? Thank you. Gary __ Information from ESET Smart Security, version of virus signature database 5063 (20100426) __ The message was checked by ESET Smart Security. http://www.eset.com __ Information from ESET NOD32 Antivirus, version of virus signature database 5063 (20100426) __ The message was checked by ESET NOD32 Antivirus. http://www.eset.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=sstap...@mnsi.net No virus found in this incoming message. Checked by AVG - www.avg.com Version: 9.0.814 / Virus Database: 271.1.1/2783 - Release Date: 04/26/10 02:31:00 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: subquery multiple rows
If i may add (and I am no expert), but just be careful of how much you're group_concat does, as there is a group_concat_max_len value (you can override it though). I have run into this once, and couldn't figure out why i wasn't getting all my data. -- taken from the mysql site: http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_grou p-concat SET [GLOBAL | SESSION] group_concat_max_len = val; Steven Staples -Original Message- From: kalin m [mailto:ka...@el.net] Sent: April 7, 2010 12:59 PM To: Nathan Sullivan Cc: mysql@lists.mysql.com Subject: Re: subquery multiple rows yea.. almost. but it helped a lot. now i know about those functions too. thank you... Nathan Sullivan wrote: I think you want to do something like this: select prod, group_concat(category separator ', ') from products group by prod; Hope this helps. On Wed, Apr 07, 2010 at 08:37:04AM -0700, kalin m wrote: hi all... i have a bit of a problem with this: table products: -- prod | category | -| boots | winter| boots | summer | boots | spring | shoes | spring | shoes | winter| shoes | fall | shoes | summer | -- when i do this: select distinct prod as m, (select category from products where email = m) as n from products; i get: ERROR 1242 (21000): Subquery returns more than 1 row i know that the subquery returns more than one rows. i hope so... what i'd like to see as result is: - m | n | - boots | winter, summer, spring | shoes | spring, winter, fall , summer | - or at least: --- m | n | --- boots | 3 | shoes | 4 | thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=nsulli...@cappex.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=sstap...@mnsi.net No virus found in this incoming message. Checked by AVG - www.avg.com Version: 9.0.791 / Virus Database: 271.1.1/2783 - Release Date: 04/07/10 02:32:00 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Table Length Question...
Hi there, I currently store some information about a users daily habits in a table. The table has 4 fields per day, and another 4 fields as the keys. This table, depending on the month, can be from (4 keys + (28 days * 4 fields per day)) fields, to (4 keys + (31 days * 4 fields per day)) fields long... The table layout is like such: +-+---++-+--+--+--+--+--+--+ -- |name |id |id2 |type |d01f1 |d01f2 |d01f3 |d01f4 |d02f1 |d02f2 |.and so on +-+---++-+--+--+--+--+--+--+ -- Performance wise, would it be better to have it laid out in a manner such as +-+---++-++---+---+---+---+ |name |id |id2 |type |day |f1 |f2 |f3 |f4 | +-+---++-++---+---+---+---+ So that each row, contains a single days details, rather than have a single row, contain the entire months details? Also, when i would do a select, if i wanted say d02f1, would it load the entire row first, and then just give me that field? -Select `d02f01` from `mytable` where [where clause] Or would it jsut load that field... Does these questions make sense? (they do in my head) Steven Staples -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
MySQL Slave is almost 1 day behind
Good day :) We've had our master/slave server running for a while now, and just yesterday, we started getting behind. Not entirely sure what happened, but it is getting further and furhter behind. (master server) mysql show master status\G *** 1. row *** File: mysql-bin.000280 Position: 58090245 Binlog_Do_DB: admin_server,baf,freeradius,radius Binlog_Ignore_DB: 1 row in set (0.00 sec) (slave server) mysql show slave status\G *** 1. row *** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.7.101 Master_User: slave_user Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000280 Read_Master_Log_Pos: 55208258 Relay_Log_File: backup-relay-bin.000530 Relay_Log_Pos: 96663109 Relay_Master_Log_File: mysql-bin.000259 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: admin_server,baf,freeradius,radius Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 96662972 Relay_Log_Space: 2211376614 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 77473 1 row in set (0.00 sec) Now, we are logging the freeradius packets into mysql, and like I said, it has been running fine, up until yesterday. Any idea how the slave would get this far behind, and not be generating any errors? It is my understanding, that the slave only does update/insert/delete queries, so even if there was a lot of select queries on the master, the slave wouldn't see them. We are not running any queries on the slave (it was set up for backup purposes, so we could stop the slave and backup completely), and we haven't done a backup on the slave in a couple of days (yeah, i know... bad bad) so there is really no reason for this. Can anyone help/assist/point me in the right direction to figure out how to catch the slave back up to the master? The master is not being overloaded, it is keeping up no problem, and the backup server is 8x the server than the application server, so it shoulnd't even be an i/o or cpu issue. Please help! :) Thanks in advance Steven Staples -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Master/Slave - trucate master table
I don't have the error anymore, but the slave will start, but when you show slave status, it shows that there is a failure, and the failure was, a duplicate primary key. What i did to fix/bandaid it, was i truncated the table on the slave, and restarted the slave again... then i did a delete on the master, where ID . Steven Staples -Original Message- From: Johnny Withers [mailto:joh...@pixelated.net] Sent: March 5, 2010 1:11 PM To: Steven Staples Cc: mysql@lists.mysql.com Subject: Re: Master/Slave - trucate master table Does START SLAVE fail? If so, what is the output of SHOW SLAVE STATUS? JW On Fri, Mar 5, 2010 at 10:11 AM, Steven Staples sstap...@mnsi.net wrote: Good day everyone... I am working with a master/slave set up, and of course, we started it once the database tables were a combined size of 60gb+ We've got it running and in sync now, (stopped the master, deleted the bin files, rsync'd the database tables and restarted the master) and all has been fine for the last week... until today. We stopped the mysql process on the slave, and did a backup (due to size, we just rsync and backup the rsync) of the table files. Took about 40 minutes to do (still not sure why it was that long, but anyway), and then we restarted the slave mysql. We're getting an error now. Apparently one of the tables is out of sync now? What appears to have happened, is that when the slave was down, i truncated a table on the master. I've read that this sometimes causes errors, is this a bug? Or a is there something I should do differently? Steven Staples -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=joh...@pixelated.net -- - Johnny Withers 601.209.4985 joh...@pixelated.net No virus found in this incoming message. Checked by AVG - www.avg.com Version: 9.0.733 / Virus Database: 271.1.1/2711 - Release Date: 03/05/10 02:34:00 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Master/Slave - trucate master table
Technically, shouldn't the slave be able to shutdown and then catch back up? What if the slave lost power and shut down... and when the power came back, shouldn't the slave restart, get the bin logs, and catchup? From now on, I wont truncate any tables, i will just delete from the table where the ID and then reset the ID to 0 or 1... and then delete everything from and beyond I was just wondering if this was a bug, or if there was something wrong with what I did? Steven Staples -Original Message- From: Ananda Kumar [mailto:anan...@gmail.com] Sent: March 8, 2010 11:45 AM To: Steven Staples Cc: Johnny Withers; mysql@lists.mysql.com Subject: Re: Master/Slave - trucate master table did u stop the slave process before stopping mysql on slave. Did u do the below before stopping the mysql on slave? slave stop; show slave status\G; regards anandkl On Mon, Mar 8, 2010 at 6:46 PM, Steven Staples sstap...@mnsi.net wrote: I don't have the error anymore, but the slave will start, but when you show slave status, it shows that there is a failure, and the failure was, a duplicate primary key. What i did to fix/bandaid it, was i truncated the table on the slave, and restarted the slave again... then i did a delete on the master, where ID . Steven Staples -Original Message- From: Johnny Withers [mailto:joh...@pixelated.net] Sent: March 5, 2010 1:11 PM To: Steven Staples Cc: mysql@lists.mysql.com Subject: Re: Master/Slave - trucate master table Does START SLAVE fail? If so, what is the output of SHOW SLAVE STATUS? JW On Fri, Mar 5, 2010 at 10:11 AM, Steven Staples sstap...@mnsi.net wrote: Good day everyone... I am working with a master/slave set up, and of course, we started it once the database tables were a combined size of 60gb+ We've got it running and in sync now, (stopped the master, deleted the bin files, rsync'd the database tables and restarted the master) and all has been fine for the last week... until today. We stopped the mysql process on the slave, and did a backup (due to size, we just rsync and backup the rsync) of the table files. Took about 40 minutes to do (still not sure why it was that long, but anyway), and then we restarted the slave mysql. We're getting an error now. Apparently one of the tables is out of sync now? What appears to have happened, is that when the slave was down, i truncated a table on the master. I've read that this sometimes causes errors, is this a bug? Or a is there something I should do differently? Steven Staples -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=joh...@pixelated.net -- - Johnny Withers 601.209.4985 joh...@pixelated.net No virus found in this incoming message. Checked by AVG - www.avg.com Version: 9.0.733 / Virus Database: 271.1.1/2711 - Release Date: 03/05/10 02:34:00 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=anan...@gmail.com No virus found in this incoming message. Checked by AVG - www.avg.com Version: 9.0.733 / Virus Database: 271.1.1/2711 - Release Date: 03/08/10 02:34:00 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Master/Slave - trucate master table
Good day everyone... I am working with a master/slave set up, and of course, we started it once the database tables were a combined size of 60gb+ We've got it running and in sync now, (stopped the master, deleted the bin files, rsync'd the database tables and restarted the master) and all has been fine for the last week... until today. We stopped the mysql process on the slave, and did a backup (due to size, we just rsync and backup the rsync) of the table files. Took about 40 minutes to do (still not sure why it was that long, but anyway), and then we restarted the slave mysql. We're getting an error now. Apparently one of the tables is out of sync now? What appears to have happened, is that when the slave was down, i truncated a table on the master. I've read that this sometimes causes errors, is this a bug? Or a is there something I should do differently? Steven Staples -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
declare multiple 'Definer'
Good morning! I have been struggleing with creating a store procedure, that will allow 2 users -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: declare multiple 'Definer'
Well... let me finish... LOL (hit send some how...) I want to be able to have 2 different users access to a stored procedure... I've tried multiple ways to write it, and none of them seem to work. Does anyone here know how to do this? CREATE (definer=`use...@`%`, definer=`sstapl...@`localhost`) PROCEDURE `sstest`() . CREATE definer=`use...@`%` OR definer=`sstapl...@`localhost` PROCEDURE `sstest`() . CREATE definer=`use...@`%` definer=`sstapl...@`localhost` PROCEDURE `sstest`() I can't think how it would be possible? Or is it? Steve -Original Message- From: Steven Staples [mailto:sstap...@mnsi.net] Sent: February 22, 2010 8:42 AM To: mysql@lists.mysql.com Subject: declare multiple 'Definer' Good morning! I have been struggleing with creating a store procedure, that will allow 2 users -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=sstap...@mnsi.net No virus found in this incoming message. Checked by AVG - www.avg.com Version: 9.0.733 / Virus Database: 271.1.1/2683 - Release Date: 02/21/10 14:34:00 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
sql file system + optimization
Ok... in the file system, my MySQL files are located in /var/lib/mysql/ I have a database (lets say 'test') so it gets its own directory /var/lib/mysql/test/ Now, all the tables go in this folder. (I know, we *should* all know this...) if the database is on another disk, could it increase performance if this was a large database? If so, could I just symlink the test/ directory to another raid array to increase performance? Or would the increase be negligible? Steven Staples -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Join Statement
Victor, As far as I can see... change the '-' to '='. -- fixed query -- SELECT SKU , Quantity , Name , Price , p.sizes , p.colorsShadesNumbersShort FROM tem126080739853 t JOIN products p ON t.ProdID = p.ID ; -- end -- I didn't run this, or try to replicate it, it was just my observation on the query. Steven Staples -Original Message- From: Victor Subervi [mailto:victorsube...@gmail.com] Sent: December 14, 2009 11:26 AM To: mysql@lists.mysql.com Subject: Join Statement Hi; I have the following: mysql select SKU, Quantity, Name, Price, p.sizes, p.colorsShadesNumbersShort from tem126080739853 t join products p on t.ProdID-p.ID; Empty set (0.00 sec) mysql select * from tem126080739853; +++--+-+--+ | ID | ProdID | Quantity | sizes | colorsShadesNumbersShort | +++--+-+--+ | 1 | 2 |2 | Extra-small | navy-blue:CC7722 | +++--+-+--+ 1 row in set (0.00 sec) mysql select SKU, Quantity, Name, Price, p.sizes, p.colorsShadesNumbersShort from tem126080739853 t join products p on t.ProdID-p.ID; Empty set (0.03 sec) mysql select SKU, Quantity, Name, Price, t.sizes, t.colorsShadesNumbersShort from tem126080739853 t join products p on t.ProdID-p.ID; Empty set (0.00 sec) mysql select ID, SKU, Name, Price from products; ++--+---++ | ID | SKU | Name | Price | ++--+---++ | 2 | prodSKU1 | name1 | 555.22 | ++--+---++ 1 row in set (0.00 sec) So I'm at a loss as to why the above select join statement fails. Please advise. TIA, Victor No virus found in this incoming message. Checked by AVG - www.avg.com Version: 9.0.716 / Virus Database: 270.14.101/2555 - Release Date: 12/14/09 02:37:00 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Stored Proc's
They are actual IP's, not domain names. So it would be like `us...@`192.168.0.100` and `us...@`192.168.0.101`... so how would I go about putting that in the definer? Currently, i have this: DELIMITER $$ USE `tablename`$$ DROP PROCEDURE IF EXISTS `tablename`$$ CREATE definer=`us...@`192.168.0.100` PROCEDURE `tablename`(... etc etc etc... Sorry if this is sounding dumb, I've only just started using stored procs (and i love them), and i have only seen examples with 1 user, or wildcarding the domain. I would also like to know how to do it for any/all users (but that is not what I am needing now) Steven Staples -Original Message- From: Michael Dykman [mailto:mdyk...@gmail.com] Sent: December 11, 2009 9:50 AM To: mysql@lists.mysql.com Subject: Re: Stored Proc's of course. you can have entries u...@domain1.me.com u...@xxx.foo.com the only caveat is to make sure the reverse lookup of your client hosts works as expected as those are the names that mysql will apply at authentication time. - mkichael dykman On Fri, Dec 11, 2009 at 9:36 AM, Steve Staples sstap...@mnsi.net wrote: Silly question here... But can I have multiple definers for a stored proc, rather than allowing `us...@`%`? basically, I want to only allow 1 user name, but from only 2 or 3 IP's. My Googleing didn't turn up anything of use, so now, I am here asking :) Thanks in advance! Steve Staples. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=mdyk...@gmail.com -- - michael dykman - mdyk...@gmail.com May you live every day of your life. Jonathan Swift Larry's First Law of Language Redesign: Everyone wants the colon. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=sstap...@mnsi.net No virus found in this incoming message. Checked by AVG - www.avg.com Version: 9.0.709 / Virus Database: 270.14.101/2555 - Release Date: 12/11/09 05:06:00 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Select from remote server from stored procedure
Ok, I feel silly for asking this, but I am going to do it anyway. I have a huge stored procedure that does quite a bit of logic, and gathering/splitting of data. I currently have our customer database on one server, and our logging on another. What i need to do, is to pull the customer id from the other server, so that the logs are tied back to the customer. Is this possible to do? To make a connection, inside the stored procedure to a completely different machine and access the mysql there? Does my question make sense? Currently what I am doing, is every new customer that gets created, my php app adds the username/customerid to that server, then makes a connection to the logging server and creates the same record, same with deleting and updating... but there just has to be a simpler way :) Thanks in advance. Steven Staples -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Select from remote server from stored procedure
So what I am reading, I guess it would be safer to just do it how I am currently doing it, as it really isn't that slow... it's just duplicating the data elsewhere (I suppose maybe making this a slave table to the other server... nah... lots of work there :P) Thanks, and I did search it before, but I guess my searching keywords were insufficient ;) Steven Staples -Original Message- From: harrison.f...@sun.com [mailto:harrison.f...@sun.com] Sent: December 9, 2009 2:07 PM To: Johan De Meersman Cc: Neil Aggarwal; Steven Staples; mysql@lists.mysql.com Subject: Re: Select from remote server from stored procedure Hello Johan, On Dec 9, 2009, at 11:22 AM, Johan De Meersman wrote: Posted this before, but beware: federated tables do NOT use indices. Every select is a full table scan, and if you're talking about a logging table that could become very expensive very fast. This is not entirely true. If you define an index on the local federated table, and it makes sense to use it, then a remote WHERE clause will be passed through and hence use the remote index. Not all types of index accesses can be passed through such as this, however for a single row lookup on a primary key, it should be fine. It is still not as fast as local access, but it's not as bad as always doing a full table scan remotely. On Wed, Dec 9, 2009 at 4:13 PM, Neil Aggarwal n...@jammconsulting.comwrote: Is this possible to do? To make a connection, inside the stored procedure to a completely different machine and access the mysql there? The only way I know to access tables from different servers from a single connection is federated tables: http://dev.mysql.com/doc/refman/5.0/en/federated-use.html Once you do that, you are accessing it like a local table. I hope this helps. Neil Regards, Harrison -- Harrison C. Fisk, MySQL Staff Support Engineer MySQL @ Sun Microsystems, Inc., http://www.sun.com/mysql/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=sstap...@mnsi.net No virus found in this incoming message. Checked by AVG - www.avg.com Version: 9.0.709 / Virus Database: 270.14.97/2550 - Release Date: 12/09/09 02:32:00 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
