Re: mysql Digest 12 Aug 2015 13:08:20 -0000 Issue 5317
On 2015/08/12 10:49, Bob Eby wrote: converting from MyISAM to innodb would certainly pose problems, I guess the main question would be is MyISAM functionality a strict sub-set of innodb? I'm not sure, but maybe someone else here knows better. No, as already said: for one thing, MyISAM allows more incremented integers in a primary key, more than one. Better find out what functions matter to you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: table desin question
On 2015/08/12 09:42, Johan De Meersman wrote: - Original Message - From: Richard Reinagatorre...@gmail.com Subject: table desin question Would this be the best way to design the schema and would it be best to make the client ID and technician ID the same as the user ID as they relate to the same person? Close enough; but I think it would be preferrable to use a unique (autoincrement) PK for all three tables; and use a referential key in client and technician to point at user. If there never are more client or technician records for one user, the autoincrementing PK in the user table is enough, with the referential key enough PK for each other table, too. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: To:, CC: mailing lists
On 2015/05/17 14:10, Jigal van Hemert wrote: I've set filters on To: or Cc: contains to catch all the mails. The others in this thread use Gmail which obviously lacks a button Reply to list. One does not need such a button, only attention to the addressee list that one s e-mail client produces. Unhappily, I often, in other settings, have found that people reflexivly use their favorite button, be it answer sender or answer all, for sending their answers, and really do not think what the right set of readers for their message is. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Why does a group_concat on a join change aggregate values?
On 2015/05/07 19:42, Paul Halliday wrote: Should have showed the whole thing. Take a look here (click image to see full output): http://www.pintumbler.org/tmp I don't see why this worries you. Joining often increases variation. Indeed, if in some case an inner join never did, maybe the joined tables are needlessly separate. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: time stamp specific to columns
On 2015/04/09 13:42, Michael Dykman wrote: A trigger is far simpler than remodelling your data and adding extra queries. They are nothing to be afraid of. Not afraid of, but to be careful when writing. I have had trouble with my triggers, because I left thisthat out. As for timestamping, MySQL already does much which the SQL programmer wants and cannot come at: An auto-updated column is automatically updated to the current timestamp when the value of any other column in the row is changed from its current value. An auto-updated column remains unchanged if all other columns are set to their current values. It would be great if besides the special rows NEW and OLD found in triggers there were also a row of two-state values (BOOL(EAN) with no NULL, or maybe that queer suggestion of CHAR(0) with NULL) that showed whether the field is the same in the update or not, because MySQL, as aforesaid, checks for that, and writing such triggers would be much easier. One could write IF (SAM.a, SAM.b, SAM.c, SAM.d) (TRUE,TRUE,TRUE,TRUE) THEN update timestamp END IF with no worrie over dealing with NULL. A trigger is always called on an attempt, whether there is change or not. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Issue with timestamp columns while working with MySQL load data in file
On 2015/04/12 08:52, Pothanaboyina Trimurthy wrote: The problem is , as mentioned the load data is taking around 2 hours, I have 2 timestamp columns for one column I am passing the input through load data, and for the column DB_MODIFIED_DATETIME no input is provided, At the end of the load data I could see only one timestamp value for both the columns, though the load data takes 2 hours to load the data. Can any one explain how exactly the load data infile works, and why only a single timestamp is inserting for all 1 million records though the load data taking around 2 hours. Look up function SYSDATE: all other times timestamps are kept in step, beginning at the time when the transaction begins. Your described effect is intended. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: time stamp specific to columns
On 2015/04/08 11:42, Andrew Wallace wrote: I think you'd have to do that with a trigger. Yes, one can do that with a trigger, but it is a real pain. MySQL now allows (new.a,new.b,new.c,new.d) (old.a,old.b,old.c,old.d) but one needs to beware of NULL. Maybe it is better to split off the timestampy part to another table, and join them when needed. On 4/8/15 6:36 AM, Martin Mueller wrote: I understand how a timestamp column automatically changes when there is a change in a data row. Is it possible to limit the update to changes in particular columns? I have a table where I care about changes in any of four different columns, but I don¹t care about changes in other columns or added columns. Is there a command that says ³update the time stamp if and only if there is a change in columns a, b,c, or d -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Dumping database names from bash with exclusion
On 2015/04/01 16:09, Tim Johnson wrote:
Using Mysql 5 on darwin (OS x).
This command
SELECT schema_name FROM information_schema.schemata WHERE
schema_name NOT IN
('mysql','information_schema','performance_schema');
as executed from the mysql prompt gives me a dump of all databases
except those not included in the tuple.
This command
mysql -uroot -p** -e SELECT schema_name FROM
information_schema.schemata WHERE schema_name NOT IN
('mysql','information_schema','performance_schema')
gives me a a dump of the entire mysql help screen, _not_ the
databases I am after.
Interesting ... when I try it, I get the output that, I suspect, you
want. Since mine is Windows cmd-line, I put it all on one line, but I
believe that all Unix-likes let one continue a string until finished.
When I break the e-string off, I get a syntax error, as if entered
from the MySQL command prompt, semicolon too soon. I cannot get the
output that you describe unless I slip question-mark in.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Java-connector missing connector class
Ignore my earlier question: my problem arose from not feeding Eclipse the right CLASSPATH when my new program was main. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Java-connector missing connector class
I wrote a Java program to send a query s output to standard output. I later wrote another to call the former whithersoever I wish to send its output. The query program just as I would like works--but when subordinated to the newer program the connector class (com.mysql.jdbc.Driver) referred to in the old program cannot be found. That is, success in finding the connector class is dependent how my older program is called. Any ideas? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: command is not allowed with this MySQL version
2014/12/12 02:10 +0100, Christophe When the app tries to do this, it raises an error : 'The used command is not allowed with this MySQL version' The used MySQL version is 5.5.40 from Debian Wheezy package. I found that some parameters or variables (local_infile for instance) can be used to get rid of this error, but can't really find the right configuration. Well, yes, if local_infile is 0 or FALSE then 'LOCAL' is forbidden. If 'LOCAL' is not used then the MySQL user needs privilege 'FILE' to read from the server s own disk. What else have you tryed, with what outcome? because the statement as it stands is good MySQL. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: forum vs email
2014/12/10 09:00 +0100, Johan De Meersman One of the (for me, at least) defining features of a forum, is that the subjects tend to be divided up into a tree structure, which has it's own benefits Something more sophisticated than grouping messages by trimmed subject-lines? maybe involving such header lines as were used in the old netnews (if e-mail is part of it)? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: forum vs email
2014/12/09 15:20 -0600, Peter Brawley Nope. And why not? Because no one bothered to implement it? Now I (for the first time?) looked at forums.mysql.com and see more topics than on lists.mysql.com. The former is just more with-it, I guess. I believ that one could both by e-mail and through a webbrowser comment on a Google group. And one who reads sends e-mail through a webbrowser surely considers discussion through e-mail simplie more overhead than using his webbrowser for discussion, too. I further suspect e-mail clients on own computers are not in fashion. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: update and control flow
2014/12/09 14:25 +, Martin Mueller I'm trying to get my feet wet with 'if' and 'when' uses in mysql. it would be very useful for update operations, but I can't get it right. If I read the documentation correctly, it should be possible to say something like UPDATE X if WORD like 'a%' SET COMMENT = 'a' elseif WORD like 'b%' SET COMMENT = 'b' END IF But this gives me an error message. What am I doing wrong? You have seen Sean Green s good changes. You need to know where what form is allowed. There is an IF statement which is allowed within stored procedures and triggers, but not in queries or open code. There is also a function of the same name that takes three arguments: condition, TRUE choice, not-TRUE choice. There are also CASE statement and CASE operator, whereto WHEN belongs. The statement and operator look not quite alike: the statement ends with END CASE, the operator with END, and the ELSE NULL allowed for the operator is not allowed for the statement. CASE statement and operator, and IF statement, take THEN between the condition and the conclusion. (If you want to try the statement forms, write a trigger or a stored procedure.) The statement forms do not apply to your example. The function and operator forms that Sean Green used do, and also the conditions WHERE and HAVING, each with its own use. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: forum vs email
2014/12/06 12:51 +0100, Johan De Meersman I want: * The entire post, and as little notification-type content as possible, * headers and subjects so that mail clients that support threading will thread everything from a single forum topic in a mail thread and vice versa, * and, most importantly, the ability to also *reply* through mail and have it appear in the forum thread at the appropriate place in the conversation Those things are what would make it a proper mailing list integration, instead of just another notification tool. That is, this list, right? What does it lack (besides readers)? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: forum vs email [was: Re: table-for-column]
2014/12/04 22:56 -0500, shawn l.green I guess this email-based peer-to-peer exchange is slowly disappearing into the background like the old usenet newsgroups, eh? And _I_ like using an off-line e-mail client, and not being bothered by going through a webbrowser--but I suspect that others prefer not to have an e-mail client, and prefer to have the freedom to use small, sophisticated gadgets instead of bigger gadgets that sit on the table, or take most of a lap. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: MySQL dying?
2014/11/26 14:25 -0600, Peter Brawley www.artfulsoftware.com/infotree/mysqltips.php, And this page is an HTML hack, table for column ... generated by a (PHP?) program? *sigh* -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Using INTEGER instead of VARCHAR/DATE - is this a way to faster access?
2014/10/29 20:56 +0100, Zbigniew Now to the point: considering, that the second column shall contain about 100-200 different labels - so in average many of such labels can be repeated one million times (or even more) - will it speed-up the selection done with something like ...WHERE label='xyz' AND date='2013-02-25' (and maybe the insertion as well?), if instead of textual labels I'll use SMALLINT as simple integer code for each different label (and in my program a translation table)? If so - will I have further performance gain, if instead of human-readable DATE for the third column I'll use Unix time put into INTEGER type column, not DATE-type? I really cannot answer your real question, but say only that DATE and other time types are numeric, although mostly constructed. DATE takes three bytes with range '1000-01-01' to '-12-31'. TIMESTAMP (which has special features that one can suppress) is a four-byte integer that is a 31-bit Unix timestamp with range '1970-01-01 00:00:01.00' UTC to '2038-01-19 03:14:07.99' UTC. Maybe TIMESTAMP, which doubtless uses the underlying integer mechanism for comparison, is best for you. Consider also the functions UNIX_TIMESTAMP and FROM_UNIXTIME. The zone is not involved in DATE, but is involved in the rest aforesaid. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Query with variable number of columns?
2014/10/08 11:38 -0700, Jan Steinman
However, this pattern will often result in numerous empty columns -- empties
that would not be there had the table not been pivoted.
2014/10/08 16:42 -0500, Peter Brawley
MySQL stored procedures are less incomplete, and can do it, but they're
awkward.
From a webpage-link on this very list posted, I learnt of a means of (yes,
clumsily) using SQL procedure to build PREPAREd statements that pivot. It
entails twice reckoning, once to find good fields, once to pivot and show them.
One selects from a virtual table:
(SELECT DISTINCT YEAR(sales.Date) AS Y FROM ...) AS g
For each good Y one wants this generated (I use ANSI mode, with more PL1 than
C):
'SUM(IF(YEAR(sales.Date)=' || Y || ', sales.Total, NULL)) AS ' || Y || ''
The outcome is something like this:
set @yearSal = (SELECT 'SELECT s_product.name AS Product, ' ||
GROUP_CONCAT('SUM(IF(YEAR(sales.Date)=' || Y || ', sales.Total, NULL)) AS ' ||
Y || '') || '
FROM ...'
FROM (SELECT DISTINCT YEAR(sales.Date) AS Y FROM ...) AS g;
PREPARE YearSal FROM @YearSal;
EXECUTE YearSal;
Unhappily, PREPARE takes only user-defined variables, and its prepared
statement, too, is exposed to the procedure s caller. If the prepared statement
is SELECT ... INTO ..., only user-defined variables are allowed after INTO.
One who knows the names can learn something about the procedure s working.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
RE: converting numeric to date-time?
2014/09/04 08:40 -0700, Jan Steinman From: Ed Mierzwa (emierzwa) emier...@micron.com FROM_UNIXTIME(1409304102.153) /*your epoch column here*/ I don't think the OP has a Unix timestamp. The number looks suspeciously like concatenation of date digits, 140930 at the beginning looks like September 30, 2014. If that's the case, you need to write something that will tear it apart. MySQL s interpretation of timestamps is already such that not much such code is needed: see Overview of Date and Time Types. If this, 140930, really were September 30, 2014 it would be enough to write SELECT DATE(140930) This also works: SELECT CAST(140930210215 AS DATETIME) One does not need to write apart-tearing code. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: ERROR in syntax...
2014/09/06 09:06 -0700, Don Wieland Can anyone tell me why this query is generating an ERROR: Which error? The first IF statement is not properly ended? it isn't. (A series of equality tests against the same variable is done more conveniently with CASE ... END CASE.) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Excluding MySQL database tables from mysqldump
2014/04/07 08:02 -0800, Tim Johnson 2)mysqldump forces all database names to lower case in the CREATE DATABASE statement. I know, one shouldn't use upper case in database names, but :) tell that to my clients. Why not? That is not mentioned in the section devoted to mapping such names to the file-system. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Conditional in update
2014/02/11 18:14 -0500, Larry Martell set LIMIT = sign(LIMIT) * 100 * floor(0.01 + (sign(LIMIT) * LIMIT * ratio/100) The function TRUNCATE can be useful here: set LIMIT = TRUNCATE(LIMIT * ratio + 0.01 * sign(LIMIT), -2) , if it works as advertized. In any case, ABS(LIMIT) = sign(LIMIT) * LIMIT . As for limiting the value, see this (clipping can be useful to you): 11.2.6. Out-of-Range and Overflow Handling When MySQL stores a value in a numeric column that is outside the permissible range of the column data type, the result depends on the SQL mode in effect at the time: * If strict SQL mode is enabled, MySQL rejects the out-of-range value with an error, and the insert fails, in accordance with the SQL standard. * If no restrictive modes are enabled, MySQL clips the value to the appropriate endpoint of the range and stores the resulting value instead. When an out-of-range value is assigned to an integer column, MySQL stores the value representing the corresponding endpoint of the column data type range. If you store 256 into a file:///C:/PROGRA%7E1/MySQL/MYSQLD%7E1.14/HTML/data-types.html#integer-typesTINYINT or TINYINT UNSIGNED column, MySQL stores 127 or 255, respectively. When a floating-point or fixed-point column is assigned a value that exceeds the range implied by the specified (or default) precision and scale, MySQL stores the value representing the corresponding endpoint of that range. Column-assignment conversions that occur due to clipping when MySQL is not operating in strict mode are reported as warnings for file:///C:/PROGRA%7E1/MySQL/MYSQLD%7E1.14/HTML/sql-syntax.html#alter-tableALTER TABLE, file:///C:/PROGRA%7E1/MySQL/MYSQLD%7E1.14/HTML/sql-syntax.html#load-dataLOAD DATA INFILE, file:///C:/PROGRA%7E1/MySQL/MYSQLD%7E1.14/HTML/sql-syntax.html#updateUPDATE, and multiple-row file:///C:/PROGRA%7E1/MySQL/MYSQLD%7E1.14/HTML/sql-syntax.html#insertINSERT statements. In strict mode, these statements fail, and some or all the values will not be inserted or changed, depending on whether the table is a transactional table and other factors. For details, see file:///C:/PROGRA%7E1/MySQL/MYSQLD%7E1.14/HTML/server-administration.html#server-sql-modeSection 5.1.7, Server SQL Modes. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
link-bug
2014/01/29 16:16 -0800, neubyr https://dev.mysql.com/doc/refman/5.0/en/replication-features-load.html - Someone was not all awake when making this webpage up: four of the links under Table of Contents point to this same page. (I was looking because I was thinking about Neubyr s problem, but I can make nothing of it.) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: grouping by the difference between values in rows
2014/01/12 14:17 -0500, Larry Martell I've been asked to do something that I do not think is possible in SQL. I have a query that has this basic form: SELECT a, b, c, d, AVG(e), STD(e), CONCAT(x, ',', y) as f FROM t GROUP BY a, b, c, d, f x and y are numbers (378.18, 2213.797 or 378.218, 2213.949 or 10053.490, 2542.094). The business issue is that if either x or y in 2 rows that are in the same a, b, c, d group are within 1 of each other then they should be grouped together. And to make it more complicated, the tolerance is applied as a rolling continuum. For example, if the x and y in a set of grouped rows are: row 1: 1.5, 9.5 row 2: 2.4, 20.8 row 3: 3.3, 40.6 row 4: 4.2, 2.5 row 5: 5.1, 10.1 row 6: 6.0, 7.9 row 7: 8.0, 21.0 row 8: 100, 200 1 through 6 get combined because all their X values are within the tolerance of some other X in the set that's been combined. 7's Y value is within the tolerance of 2's Y, so that should be combined as well. 8 is not combined because neither the X or Y value is within the tolerance of any X or Y in the set that was combined. In python I can easily parse the data and identify the rows that need to be combined, but then I've lost the ability to calculate the average and std. The only way I can think of to do this is to remove the grouping from the SQL and do all the grouping and aggregating myself. But this query often returns 20k to 30k rows after grouping. It could easily be 80k to 100k rows that I have to process if I remove the grouping and I think that will be very slow. Anyone have any ideas? I suspect you can carry out their ideas by something like this, in an SQL procedure: Besides your table t, there are tables t1, t2, t3, tpair, and tq. With a cursor copy records from t to t1 (with all of t s fields and an g1 besides) ordered by a, b, c, d, x, going through all the complications of deciding where a group boundary falls, numbering the groups by g1. Repeat this copying from t1 to t2 (which has besides g1 also g2), ordered by a, b, c, d, y, numbering the groups by g2. Now t1 no longer is needed. Copy all distinct pairs of g1 and g2 to tpair. Until g = MIN(g1) of tpair is null, move (INSERT ... SELECT; DELETE ...) from tpair to tq all pairs where g1=g, and as long as there is anything to move from tpair to tq alternatly move records where any in tpair s g2 match any already in tq and any in tpair s g1 match any already in tq. Now all the pairs in tq represent the same group: every record in t2 with a pair in tq is copied into t3 with a new number g3 instead of the pair. After this one may group t3 by g3. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
cp850??
I like Latin1. I set up my version 5.5 to use it by default, but sometimes it changed Client Connection characterset to UTF8. Now I have 5.6, and the client comes up with Client Connection characterset CP850 (CP850 is the Swedish variant of CP437, the IBM PC s original character set). Why??? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Foreign-key reference
2014/01/06 14:24 -0500, Morgan Tocker You might be hitting: Important The inline REFERENCES specifications where the references are defined as part of the column specification are silently ignored. MySQL only accepts REFERENCES clauses defined as part of a separate FOREIGN KEY specification. See: http://dev.mysql.com/doc/refman/5.6/en/alter-table.html Ugh, that seems quite right. Now, why did they do that? A separate specification is less convenient, and also less transparent. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: ERROR 2013 (HY000): Lost connection to MySQL server during query
2014/01/06 17:07 +0100, Reindl Harald what about look in the servers logfiles most likely max_allowed_packet laughable low Is this then, too, likly when the server and the client are the same machine? I left this out, that it only then happens when the client has been idle, and right afterwards the client repeats the request and all goes well. The message is no more than an irritatind break between request and fulfillment. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: loading 0x00A0 into mysql
2014/01/06 12:18 +, Dave Howorth
Everything appears to work except that text fields containing a Unicode
non-breaking space (0x00A0) are truncated just before that character. I
can see the field in the dump file and it looks OK, but it doesn't all
make it into the new database.
Well, there are too many aspects to this, but the first is the character set
that mysql expects for input. If, say, it is USASCII (note that between the
character set that mysql takes for input and the character set in the table
no association is needful), the nbsp is out of range. (It is, of course, not
nice if mysqldump yields an output that mysql cannot read.) Try entering it
with some escape-sequence (this one is based on the original SQL with features
from PL1, not from C, which MySQL supports if 'ANSI' is in sql_mode):
'some text ... ' || X'A0' || ' ... more text ...'
or (slightly less PL1)
CONCAT('some text ... ', X'A0', ' ... more text ...')
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
ERROR 2013 (HY000): Lost connection to MySQL server during query
Now that I installed 5.6.14 on our Vista machine, when using mysql I often see that error-message, which under 5.5.8 I never saw. What is going on? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Foreign-key reference
Are INNODB foreign-key references ignored in 5.6? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Really slow batch insert??
2013/12/30 13:59 +0200, Cabbar Duzayak We have a basic table, which is something like : (id varchar50, productId varchar50, category varchar50) In this table, ID is the primary key and we have a unique index on (category, productId). And, there is a case where we want to do bulk inserts (3000 inserts) using: INSERT INTO (id, productId, category) VALUES (x1, y1, z1), (x2, y2, z3) .. ON DUPLICATE KEY productId = VALUES(productId), category = VALUES(category) So, when we try to insert 3000 rows using this syntax with a single statement, it takes ~ 3 seconds to execute this on an empty table. BTW, innodb_flush_log_at_trx_commit is set to 2 for us. I dont have something factual data here, but it feels that this is too much just for 3000 rows. I have nothing real to say about the slowness, but it looks as if in this case your operation is the same as REPLACE INTO (id, productId, category) VALUES (x1, y1, z1), (x2, y2, z3) and maybe that takes less time. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: LOAD DATA INFILE with space after quote but before comma
2013/12/18 11:07 -0500, Anthony Ball I ran across a curious issue, I'd call it a bug but I'm sure others would call it a feature. I have a csv file with space between the and , and it causes MySQL to eat that field and the field after it as a single field. Is there a setting I can use to remedy this or do I just have to make sure no whitespace intrudes? Well, strictly speaking, it is a bug, in your file. If you can keep that from happening that is best, because in a CSV file the quotemark may appear only first, last, or next to a separator, unless it quotes another quote-mark. Otherwise, if it is consistent as in Dhaval Jaiswal s (2), only do as he suggests. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
a Java-connector
I have MySQL 5.5.8 under Windows Vista, and I am minded to write Java programs to talk to the server. I believe that a connecter is needed for that, something with ODBC in the name--which version is best for my use? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Another query question...
2013/11/08 17:35 -0800, Jan Steinman Okay, I think I found it: http://bugs.mysql.com/bug.php?id=47713 I added a comment with a link to a page I set up to show the behaviour on my system. http://www.ecoreality.org/wiki/WITH_ROLLUP_problem It was submitted in 2009, severity Critical, triaged Serious, and still not fixed! Yea, and the bug to which I referred is also evident in the same report, where SumQuantity is 78, and not all the rest is NULL. 'Twouldn't surprise me if the bugs are akin. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Another query question...
2013/11/04 09:32 -0800, Jan Steinman I noticed that I have similar queries that work as expected. The difference appears to be that every query that is broken uses WITH ROLLUP, and removing this makes them behave as expected. Is this a known bug? Should I submit it as such? If someone would be so kind as to point me to the bug system, I'll crawl around in there to see if it's a known problem. There is a bug that I about a half year ago reported, http://bugs.mysql.com/bug.php?id=68564, that the NULL one expects with WITH ROLLUP is not always NULL, but is instead the foregoing string in the same field. I suspect that other bugs with missing NULL found by searching for ROLLUP are the same problem. (note link titled Affects Me!) Another, one year ago reported, bug of mine was handled in 5.7.2, but this one not. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Update Column in table only if variable is Not NULL
2013/10/28 21:23 +, Neil Tompkins Basically the snippet of the UPDATE statement I provided shows updating only 1 field. However in my live working example, I have about 20 possible fields that might need to be updated if the variable passed for each field is NOT NULL. Well, maybe something as loathsome as this: UPDATE T SET F1 = NOW(), F2 = IFNULL(@F2, F2), FF3 = IFNULL(@FF3, FF3), FF4 = IFNULL(@FF4, FF4), F5 = IFNULL(@F5, F5), but if all are NULL F1 will be misleading. I can think only of NOT (@F2 IS NULL AND @FF3 IS NULL AND @FF4 IS NULL AND @F5 IS NULL ) or making F1 a variable that takes ON UPDATE CURRENT_TIMESTAMP: I have read that MySQL checks every UPDATE for actual change, and only then changes such an F1 when something actually else changes. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Update Column in table only if variable is Not NULL
2013/10/29 11:35 -0400, Shawn Green My favorite technique is the COALESCE function for this on a column-by-column basis SET FieldName1 = Now(), FieldName2 = COALESCE(:MyVariable, FieldName2) but if MyVariable is NULL, FieldName1 reflects the attempt to change, not change. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: Change to MySQL Community Server 5.7.2?
2013/10/25 00:08 +, Rick James There's an old saying, If it ain't broke, don't fix it. Why _might_ 5.6.x or 5.7.x be better for you? Sure there might be some features you might want, might be some performance improvements that you might notice, etc. And there might be some regressions that will bite you. Fortunately, regressions are rare. You should probably upgrade to 5.6 soon, simply to avoid having to do a double upgrade when you eventually go to 5.7. Everyone wants the computer where the database is changed to a newer: good time for installing a newer MySQL, too. In 5.6 there is a feature of interest to me: DATETIME (I wish it were DATE) also allows DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP; and in 5.7 a bug that I reported, relevant to my code, was amended. Now, in the announcement, it is called public milestone release of MySQL 5.7: what is that, and how different from generally available? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: MySQL Community Server 5.7.2 has been released (part 1)
MySQL fans, 2013/09/21 18:04 +0200, Bjorn Munch MySQL Server 5.7.2 (Milestone Release) is a new version of the world's most popular open source database. This is the second public milestone release of MySQL 5.7. Is this a good replacement for that 5.5.8 that I long ago downloaded and installed? or is it better to go for a 5.6, or an older 5.7? 2013/09/20 15:47 +0530, Sunanda Menon MySQL Server 5.6.14, a new version of the popular Open Source Database Management System, has been released. MySQL 5.6.14 is recommended for use on production systems. Is this better for me than any 5.7? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Date comparison help
2013/10/22 12:20 -0400,
I recently upgraded a local MySQL installation to 5.5.32 and am trying to
figure out why the following query won't work as expected anymore. I'm just
trying to compare a set of dates to NOW() but since the upgrade, these don't
seem to work as expected.
SELECT
DATE_ADD(STR_TO_DATE('2013-350-00:00:00','%Y-%j-%H:%i:%S'),INTERVAL 2 DAY),
NOW(),
DATE_ADD(STR_TO_DATE('2013-350-00:00:00','%Y-%j-%H:%i:%S'),INTERVAL 2 DAY)NOW()
For instance, when I run it on my system, I get 1 for the third column even
though comparing the two by eye it should be false.
Well, show us all three columns
And with 5.5.8 I get the same third column as you. Has it worked?
And I found that changed to
SELECT
DATE_ADD(STR_TO_DATE('2013-350-00:00:00','%Y-%j-%H:%i:%S'),INTERVAL 2 DAY) AS
A,
NOW(),
CAST(DATE_ADD(STR_TO_DATE('2013-350-00:00:00','%Y-%j-%H:%i:%S'),INTERVAL 2 DAY)
AS DATETIME)NOW() AS B
it works as hoped for--and it seems a bug to me, but probably an old one. It
seems to me that the outcome of DATE_ADD is DATE, not DATETIME, and the
comparison is numeric, with the six trailing 0s dropped. Quote about
STR_TO_DATE:
It takes a string str and a format string format.
file:///C:/Program%20Files/MySQL/MySQL%20Server%205.5/HELP/functions.html#function_str-to-dateSTR_TO_DATE()
returns a
file:///C:/Program%20Files/MySQL/MySQL%20Server%205.5/HELP/data-types.html#datetimeDATETIME
value if the format string contains both date and time parts, or a
file:///C:/Program%20Files/MySQL/MySQL%20Server%205.5/HELP/data-types.html#datetimeDATE
or
file:///C:/Program%20Files/MySQL/MySQL%20Server%205.5/HELP/data-types.html#timeTIME
value if the string contains only date or time parts.
How really does it decide which type to return? It is wrong if the decision is
based whether all the hour, minute, and second are 0 or not.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: Can't Connect Localhost
2013/09/02 12:49 +0800, John Smith I looked in mysql.config.pl and no localhost :( mysql.config.pl from what software damned? mysql.config.pl does not exist in context of mysql It exists in the following folder on my Win8 box: /Program Files (x86)/MySQL/MySQL Server 5.5/bin Yes, apparently it does exist in context of mysql. At any rate, it didn't work. Is there another config file in there? That is a Perl script, meant for, under Windows, configuration _reporting_. One directory up look for my.ini. As for its meaning, maybe http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html helps, after mysqladmin -uroot -p variables (somepathyoumayuse) OR mysql -uroot -p -eshow global variables (somepathyoumayuse) for showing all variables that may be in that file. For me the commands work also with -hlocalhost or -h127.0.0.1 in the command-line, as expected. In any case, your original error message, although it is a MySQL error, as Harald said does not look like something straight from MySQL client (mysql.exe), but something passed through another. What did you enter to get it? Note this quote: The error (2003) Can't connect to MySQL server on 'server' (10061) indicates that the network connection has been refused. You should check that there is a MySQL server running, that it has network connections enabled, and that the network port you specified is the one configured on the server. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Java UTC Calendar and Mysql TimeStamp - Gets me every time!!!!!
2013/08/22 14:22 -0400, Nick Cameo SimpleDateFormat sdf = new SimpleDateFormat(-MM-dd'T'HH:mm:ss, new Locale(en, US)); Well, you have your answer (FROM_UNIXTIME( /1000)), but that stupid ISO format with 'T' in the middle does not work, because to MySQL letters are not separators--which, I am sorry to say, I did not say, although it was in the back of my mind. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Java UTC Calendar and Mysql TimeStamp - Gets me every time!!!!!
2013/08/21 18:03 -0400, Nick Khamis We have the following mysql timetampe field startdate | timestamp | NO | | -00-00 00:00:00 When trying to insert a long value in there: Calendar c = Calendar.getInstance(TimeZone.getTimeZone(UTC)); c.getTimeInMillis(); We are presented with the following error: com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect datetime value: '1377119243640' for column 'stopdate' at row 1 Ugh, where is the SQL? In any case, although it looks as if that is MySQL s internal TIMESTAMP representation, one does not directly use Unix timestamps; instead, one converts them with the MySQL function FROM_UNIXTIME. The same effect may be gotten with any timestamp-formatting function that yields a string in the form '2013/08/21 18:03:00' (it is all one whether the separator is hyphen, slant, colon, ...). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Question regarding creating a query
2013/07/30 14:12 -0400, Sukhjinder K. Narula I have several databases (all with same structure), which I to query. For instansce: db1, db2, db3 - all have table tb1 with field a, b and table tb2 with fields flag1, flag2 So I want to query and get field a from tb for all db's. One way to do is union i.e. SELECT a FROM db1.tb1 WHERE (SELECT flag1 FROM db1.tb2) = 'y' UNION SELECT a FROM db2.tb1 WHERE (SELECT flag1 FROM db2.tb2) = 'y' UNION SELECT a FROM db3.tb1 WHERE (SELECT flag1 FROM db3.tb2) = 'y' But the problem here is that if I add more db's, I have to update the query every time. In addition to above, I also have a database e.g. common, which has a table called dbnames with field name, that keeps the name of all the databases I have (db1, db2, db3). So, what I would like to do is query the common db to get the names of the db's and then run the select query on each db. So here is the pseudocode of what I want to do: for each (SELECT name AS DbName FROM common.dbnames) (SELECT a FROM DbName.tb1 WHERE (SELECT flag1 FROM DbName.tb2) = 'y') AS CONCAT(DbName, '-', a) Well, you could build up the united query in a string and pass it to PREPARE -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: hypothetical question about data storage
2013/07/27 00:58 +0200, Chris Knipe I would definately consider the md5 checksum as a PK (char(32) due to the hex nature), Well, not that it greatly matters, but you could convert it to BINARY(16). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Get Affected Rows after Stored Procedure COMMIT
2013/07/02 12:29 +0100, Neil Tompkins I have a number of INSERT and UPDATE statements in a MySQL Stored Procedure, that works in the form of START TRANSACTION followed by COMMIT. Also I am handling any EXCEPTION. However, after calling COMMIT, how can I get the number of Rows that were affected either INSERTED or UPDATTED ? Can you use function ROW_COUNT to any effect? If you can, probably you have to add its yields up in your own code. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: space gone after MyISAM REPAIR TABLE
2013/06/26 17:31 +0100, nixofortune ALTER TABLE `new_innodb` ADD KEY `idx1` (`col1`,`col2`), ADD KEY `idx2` (`col1`,`col2`,`col3`); Is it really seemly for one index to be a leading part of another? (or maybe I am really thinking of something else) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: How do I select all rows of table that have some rows in another table (AND, not OR)
2013/06/13 23:08 +, Rick James
FIND_IN_SET might work the cleanest...
WHERE FIND_IN_SET('action', genres) OR/AND [NOT] ...
And have genres look like 'action,drama,foobar', that is comma-separators, and
no need for leading/trailing comma.
That would also work for genres = '1,3,10,19,38' and FIND_IN_SET('19', genres)
And you seem no fan of named BITs (SET), either. *sigh*
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: Update just some of the fields
2013/06/17 11:38 +0430, Sayyed Mohammad Emami Razavi update test set desc='test10' where id=1; _That_ is UPDATE! It is the only means of changing, but neither inserting nor deleting, a record. The other fields are left the same. MySQL also tracks whether it is an actual change; this is reflected in the client message Rows matched: ? Changed: ? Warnings: ? . The number after Changed: is the number of records where the new value really differs from the old. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: How do I select all rows of table that have some rows in another table (AND, not OR)
2013/06/11 12:59 -0700, Daevid Vincent Also, just for SG this is how we are currently implementing it, but we feel the REGEXP is killing our queries and while clever is a bit hacky and nullifies any indexes we have on the genres column as it requires a file_sort table scan to compare substrings basically... SELECT * FROM scene_all_genres WHERE scene_id = 17; scene_id genres 17 1|3|10|19|38|53|58|59| SELECT * FROM scene_all_genres WHERE scene_id = 11; scene_id genres --- 11 1|10|19|31|32|59| Except that, it seems to me, it somehow reflects the reality of assigning attributes to the scenes (movies?) that you catalog. In a way, it looks very much like using a bitstring wherin each place stands for one attribute. If you then have also a bitstring for each user s likes and one for rows (peeves), telling howmany 1s are at the same place for the genres and liking (bit-AND, MySQL followed by telling the number of 1s), and same for the genres and the row or peeve yields a number howmany match for liking, and how many match for becoming peeved. If the liking is enough greater than the becoming peeved, the scene and the user match. Unhappily, although this, using bitstring for set of attributes to match, is an old and well understood topic, MySQL s support for bitstrings is poor, limited to integers (as C is so limited)--that is, to 64 bits. If you have more, you have to use more words. There is, furthermore, no function for telling howmany 1s (or 0s) there are in an integer. Now, if, in a more perfect world, MySQL had bitstring, and, furthermore, MySQL s SET were mapped onto bitstring, where it belongs, you could not only use bit operations (MySQL s | ^), but also name the bits as you like. The problem with writing one s own bit-telling function is, of course, time, and hiding useful information from the optimizer. In any case, here is a function for it, using an old well worn trick that depends on binary arithmetic: delimiter ? create function bittell(B INTEGER) RETURNS INTEGER DETERMINISTIC NO SQL COMMENT 'Howmany 1s in argument?' begin declare E integer; SET E = 0; WHILE B 0 DO set B = (B-1) B, E = E + 1; end WHILE; RETURN E; end ? delimiter ; If you stick with the character-string set, with a slight change in representation you can use a simpler-looking pattern--not more efficient, if MySQL s implementation is good, but of easier reading: separate the decimal numerals with a character that is neither a decimal digit nor a REGEXP operator, and bound the whole string with it--comma or semicolon (among others) are good. ',1,10,19,31,32,59,' REGEXP ',10,+.*,38,' is 0 ',1,10,19,31,32,59,' REGEXP ',10,+.*,32,' is 1 (See also FIND_IN_SET.) Somewhere I read that for lack of support bitstring has been withdrawn from the SQL standard. This is such an obvious use; why is it not supported? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: How do I select all rows of table that have some rows in another table (AND, not OR)
2013/06/11 12:59 -0700, Daevid Vincent Also, just for SG this is how we are currently implementing it, but we feel the REGEXP is killing our queries and while clever is a bit hacky and nullifies any indexes we have on the genres column as it requires a file_sort table scan to compare substrings basically... SELECT * FROM scene_all_genres WHERE scene_id = 17; scene_id genres 17 1|3|10|19|38|53|58|59| SELECT * FROM scene_all_genres WHERE scene_id = 11; scene_id genres --- 11 1|10|19|31|32|59| Except that, it seems to me, it somehow reflects the reality of assigning attributes to the scenes (movies?) that you catalog. In a way, it looks very much like using a bitstring wherin each place stands for one attribute. If, say, the bitstring for that which the user gladly picks something is called glad, and that for which the user is loath to pick something is called loath, an expression for fulfilling all attributes is (glad genre) = glad AND (loath genre) = 0, with no bit-telling. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: string-likeness
2013/06/03 21:43 +, Rick James Soundex is the 'right' approach, but it needs improvement. So, find an improvement, then do something like this... Hashing involves somekind normalizing, and in my case I see no means to it; otherwise I would not have considered something so costly. On the other hand, maybe I am comparing lists of place-names, and I want to match, say, any of Mount Saint Francis or MT ST FRANCIS or MOUNT ST FRANCIS or MT SAINT FRANCIS--but it is not all standard abbreviations. Sometimes there is Galvestn or Galvston or Galvstn for Galveston, and it is not always vowel-letter deletion, either: Ft Benj Harrison, FT BENJAMIN HARRISON, Ft Benj Harsn; CLVR MIL ACAD, Culver Milt Acad. Anyhow, I gave up on a perfect solution, and instead added to each name the name padded with '%'s. On joining the longer name is used, but instead of the shorter the padded is used after LIKE, if LOCATE also fails to match, and overall the Levenstein edit distance is used only for a check, with short-circuit AND and OR supposed (and the timing is such that I believe it is): ON (LOCATE(Bookk.Burgh, PO.Burgh) 0 OR LOCATE(PO.Burgh, Bookk.Burgh) 0 OR CHAR_LENGTH(Bookk.Burgh) CHAR_LENGTH(PO.Burgh) AND Bookk.Burgh LIKE PO.pBurgh OR CHAR_LENGTH(Bookk.Burgh) CHAR_LENGTH(PO.Burgh) AND PO.Burgh LIKE Bookk.pBurgh) AND mismatch(Bookk.Burgh, PO.Burgh, 1, 2, 1) 8 IS NOT FALSE It does not match MOUNT ST FRANCIS and MT SAINT FRANCIS. At least for LOCATE and LIKE there are linear-time algorithms. All along I assumed that in the end some of the mismatching will be handled by hand. It is not that big a list, but doing all by hand is far too much. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Stopping mysql does not always stop it?
2013/06/06 09:28 -0400, Mike Franon Long story short, 50% of the time the command /etc/init.d/mysqld stop will fail Don't see why it anywhen succeeds. My version of mysqld doesn't know stop. As for mysqladmin, it knows stop, but, since that means stop-slave, I doubt you want that. Maybe you want mysqladmin ... shutdown. Better first to read up. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: string-likeness
2013/06/03 18:38 +0200, Hartmut Holzgraefe equality checks have a linear cost of O(min(len1,len2)) and can make use of indexes, too, while Levenshtein cost is is almost quadratic O(len1*len2) and can't make any good use of indexes ... even using a C UDF would help only so far with this kind of complexity. It will increase performance by a constant factor, but given long enough input strings the len1*len2 factor will still account for the majority of the run time increase over simple equality comparions My set isn't that big (not the hundreds of thousands to which many on this list refer), only big enough to be a pain, and here the constant, between implementing in interpreted SQL with no array, only temporary table, and compiled C, with real array, probably matters--except that my C-implementation won't happen. there are a few possible points of optimization though, first of all you can cut off equal start and end sequences (linear complexity for that part instead of quadratic). You can also add a few more tricks if you are only interested in matches below a certain distance threshold: * if string lengths differ by more than the threshold value you can rule out this pair of strings as being similar right away * while iterating over the distance array keep track of the min. distance value of the current row ... if at the end of a row is larger than the threshold distance you can terminate right away Didn't think of these ... will have to find a threshold * only calculate operation cost, not operation type * do not maintain a full len1*len2 array, having only the previous and current row in two one dimensional arrays is sufficient (this esp. helps in C implementation as the functions working set is more likely to fit into CPU caches) I already do this, because MySQL has no arrays, and I use a small temporary table instead of one linear array. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
string-likeness
I wish to join two tables on likeness, not equality, of character strings. Soundex does not work. I am using the Levenstein edit distance, written in SQL, a very costly test, and I am in no position to write it in C and link it to MySQL--and joining on equality takes a fraction of a second, and this takes hours. Any good ideas? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Editing existing Trigger MySQL 5.6
2013/05/29 10:39 +0100, Neil Tompkins Using Workbench with MySQL 5.6 how do I edit a existing Trigger. Do I need to DROP the Trigger and create a new one ? If that is the case how can you run start command in a live environment ? Whatever appearance Workbench adds (I do not know it), replacing a simple trigger looks something like this: DROP TRIGGER IF EXISTS tr; CREATE TRIGGER tr ...; . What do you mean by 'start'? The Windows command-line command? START SLAVE? START TRANSACTION? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Temporary Tables with Triggers Problem
2013/05/29 14:51 +0100, Neil Tompkins
This is my Trigger which doesn't seem to work; but doesn't cause a error
DROP TEMPORARY TABLE IF EXISTS tempHotelRateAvailability;
CREATE TEMPORARY TABLE tempHotelRateAvailability(AuditTrailId
varchar(36),UserId bigint(20),ActionType
enum('INSERT','UPDATE','DELETE'),TableName varchar(36),RowKey
varchar(255),FieldName varchar(36),OldValue text,NewValue text);
IF NEW.RoomsToSell OLD.RoomsToSell THEN
INSERT INTO tempHotelRateAvailability VALUES
(UUID(),NEW.LastChangedBy,'UPDATE','HotelRateAvailability',
CONCAT(OLD.RoomID,'|',OLD.Day),'RoomsToSell',OLD.RoomsToSell,NEW.RoomsToSell);
END IF;
IF SELECT COUNT(*) FROM tempHotelRateAvailability 0 THEN
INSERT INTO AuditTrail
SELECT tempHotelRateAvailability.* FROM tempHotelRateAvailability;
END IF;
DROP TEMPORARY TABLE tempHotelRateAvailability;
However if I use this call in the Trigger and change a value in the table
it works fine;
INSERT INTO AuditTrail
(AuditTrailId,UserId,ActionType,TableName,RowKey,FieldName,OldValue,NewValue,
LoggedOn)
VALUES (UUID(),1,'UPDATE','HotelRateAvailability', 1,'RoomsToSell',1,2,
NOW());
You have left out the opening line, but it looks like AFTER UPDATE; is the
table AuditTrail, or another?
How did this pass the parser,
IF SELECT COUNT(*) FROM tempHotelRateAvailability 0 THEN
? If Workbench corrected it, there is no knowing what the code really is. This
is correct,
IF (SELECT COUNT(*) FROM tempHotelRateAvailability) 0 THEN
but it is just as well to write
IF EXISTS(SELECT * FROM tempHotelRateAvailability) THEN
.
Why bother with the temporary table? It never has more rows; it is just as well
to insert straight into AuditTrail if NEW.RoomsToSell OLD.RoomsToSell.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: Bug in BETWEEN same DATETIME
2013/05/24 09:49 -0400, shawn green Or we could coerce datetime values back to their date values when both are being used. The trick now becomes choosing between rounding the datetime value (times past noon round to the next date) or do we use the floor() function all the time. This is simply wrong. Timestamps are not numbers: we do not add timestamps, and when we subtract them we do not consider the difference something of the same type. Therefore, one does well to be wary when applying to a timestamp the notion rounding. But containment generally applys: an event on MAY 25th from 1pm to 4pm is within May 25th, which is within May, When containment fails, then there is trouble: what is the first weekend of August? or the first week of August? better to say, the weekend or week of August 1st, or 2d, or ...; day is a common divisor to calendar-month, weekend, and week. Therefore, when I learnt that in version 4 MySQL had gone from interpreting a comparison between DATE and a finer timestamp by the DATE to interpreting it by the finer timestamp I believed that MySQL was going the wrong way--that MySQL had gone from a realization of an intuitive sense of containing, as above, to one on which too much thought had been expended, with a loss of intuitive sense. I consider the change of 2013/5/25-13 to 2013/5/25 to be truncation, not any sort of rounding; that is, it is a matter of notation, but one which intuitivly expresses containment. These notions sometimes change over the years, and by nation. When the first public striking clock was set up in Milan, it pointed to hours I through XXIV, with sunset falling within the 24th hour--that is, the 24th hour ends with 24 o'clock s being struck. This persists to this day in the German expression viertel sechs, which means that the sixth hour is one-fourth over, or, as we would say it, quarter after five. (Like expressions are found amongst the Germans s neighbors, but in English never took root.) Nowadays we are are more inclined to associate both quarter after five and quarter to six (dreiviertel sechs) with 5 o'clock than 6 o'clock; this accompanies the change of notation from 1 through 24 to 0 through 23. I find MySQL s automatic conversion sometimes to be downright screwy; (version 5.5.8) consider SELECT NULL and SELECT NULL UNION SELECT NULL; in one of my views there is a complex wholly numeric expression that becomes varbinary(32). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: Version 5.6.2-m5 Boolean Datatype
2013/05/22 21:17 +, Rick James In query syntax, TRUE is the same as 1; FALSE is the same as 0. and UNKNOWN is NULL. (I actually have used a three-state comparison.) It has been suggested that one who wants a real two-state field use the type CHAR(0) NULL. If you have more 'flags', consider the SET datatype. (Yeah, it is somewhat clumsy.) And SET is mapped onto some integer, even as ENUM is. BIT, now deprecated I understand, would have been the perfect type to map ENUM onto. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: NET START MYSQL QUESTION?
2013/05/11 20:50 +0600, SIVASUTHAN NADARAJAH I want to start the mysql from command prompt using NET START MYSQLBUT the server not started. It display an error message. C:\Users\PC NET START MySQLSystem error 5 has occurred. Access is denied. could you please help me, how to start the Mysql service FROM command prompt? SIVASUTHAN- Consultant Trainer Well, ordinarily the MySQL service is started by Windows along with all the rest: After installing MySQL under Windows one runs MySQL Instance Configuration Wizard--the only MySQL program on my system that runs from Start. (If you cannot do this on the machine where mysqld.exe is to run I do not know what to say.) If the service, with program mysqld.exe, quits, to start it one enters start mysqld -b... from the command line, with the directory where my.ini is kept entered for Of course, if mysqld.exe started and quit, as Reindl Harald suggested study the logs and find out why. It is not started through the client, mysql.exe. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: NET START MYSQL QUESTION?
2013/05/11 22:58 +0200, Reindl Harald why not answer the question another user made hours ago? under which account do you try to start mysqld? Well, I learnt something here. When I had the problem of (under Vista) starting mysqld, from command prompt I always did this, start mysqld -b... (here start is like Unix s trailing ), and never had a problem. It showed up running on the service list. Maybe something like Unix s set-user-id is in effect in mysqld.exe. Now for the first time I learn of command NET, and its options. I do not remember seeing net start MySQL in MySQL s help when I installed it, only that which I above described. This is not so much an OS problem, but a problem at the point where the OS s peculiarities and a big package s nature intersect. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Long integer constant problem in views
2013/04/30 17:17 +0200, Martin Koch CREATE OR REPLACE VIEW foo AS SELECT * FROM mytable WHERE id = X'36a461c81cab40169791f49ad65a3728'; Try this: _binary X'36a461c81cab40169791f49ad65a3728' SHOW CREATE VIEW is the command for the client. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: how to list record in column (instead of a row)
2013/04/24 09:06 -0700, Rajeev Prasad this table has many columns and only 1 record. select * from table; generates an unreadable list. how can i list the record as in two columns? (column name and its value)? i looked at UNPIVOT, but could not get it to work. SQL select * from table UNPIVOTE INCLUDE NULLS; select * from table UNPIVOTE INCLUDE NULLS * ERROR at line 1: ORA-00933: SQL command not properly ended From MySQL client, if started with flag '-G': select * from table ego For the same program there is flag '--auto-vertical-output'. But it seems you are using Oracle; this is MySQL list. In any case, you wrote both 'UNPIVOT' and 'UNPIVOTE'; I suspect the former is better. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Doubt with stored procedures
2013/04/17 14:16 +0200, Antonio Fernández Pérez I have a doubt with stored procedures functionality. Is possible that a stored procedure works with all databases form the server? I have created a stored procedure on dataBaseA and also works with dataBaseB. Is that correct? Independently of the user privileges defined. It is the default assumption that a procedure within a database is meant for use within that database, but one can call a procedure from any of the set of databases by qualifying the name--and the MySQL command show procedure status shows all procedures. The only question is the procedure s use of variables: if they refer only to the arguments, it is of no importance whence it is called. This is documented: file:///C:/Program%20Files/MySQL/MySQL%20Server%205.5/HELP/sql-syntax.html#useUSE statements within stored routines are not permitted. When a routine is invoked, an implicit USE db_name is performed (and undone when the routine terminates). The causes the routine to have the given default database while it executes. References to objects in databases other than the routine default database should be qualified with the appropriate database name. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: error-log aging
2013/04/05 11:16 +0200, Johan De Meersman Half and half - rename the file, then issue flush logs in mysql to close and reopen the logs, which will cause a new log with the configured name to be created. That being said, I'm not much aware of Windows' idiosyncracies - I hope the damn thing allows you to rename a file that's being held open by a program. If not, well... see above. No, as Reindl answered, but in 5.5.8 there is this: If you flush the logs using file:///C:/Program%20Files/MySQL/MySQL%20Server%205.5/HELP/sql-syntax.html#flushFLUSH LOGS or file:///C:/Program%20Files/MySQL/MySQL%20Server%205.5/HELP/programs.html#mysqladminmysqladmin flush-logs and file:///C:/Program%20Files/MySQL/MySQL%20Server%205.5/HELP/programs.html#mysqldmysqld is writing the error log to a file (for example, if it was started with the file:///C:/Program%20Files/MySQL/MySQL%20Server%205.5/HELP/server-administration.html#option_mysqld_log-error--log-error option), the effect is version dependent: * As of MySQL 5.5.7, the server closes and reopens the log file. To rename the file, you can do so manually before flushing. Then flushing the logs reopens a new file with the original file name. For example, you can rename the file and create a new one using the following commands: shell mv host_name.err host_name.err-old shell mysqladmin flush-logs shell mv host_name.err-old backup-directory On Windows, use rename rather than mv. * Prior to MySQL 5.5.7, the server renames the current log file with the suffix -old, then creates a new empty log file. Be aware that a second log-flushing operation thus causes the original error log file to be lost unless you save it under a different name. On Windows, you cannot rename the error log while the server has it open before MySQL 5.5.7. To avoid a restart, flush the logs first to cause the server to rename the original file and create a new one, then save the renamed file. That also works on Unix, or you can use the commands shown earlier. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: update a row only if any column has changed, in a very large table
2013/04/06 13:56 -0700, Rajeev Prasad I have a table with around 2,000,000 records (15 columns). I have to sync this from an outside source once every day. not all records are changed/removed /new-added everyday. so what is the best way to update only those which have changed/added/or deleted? i can use update_or_create but that will update (re-write the row) even if nothing has changed in the row/record. wont that be an overhead? how can i escape that? what would be the fastest and least resources consuming way to do this table update? I also have another table with 500,000 rows and i wish to implement the same solution to that too. I earlier posted this on DBIx list, as i thought i could use DBIx tools to manage this. but based on response, it seems that MySQL tools would be more helpful in doing it in most efficent way. Plz. advice how can i address this. I also considered to delete and simply recreate the table each day. but changes/add and delete are not too many (may be a few hundreds.. max) Sounds like a case for replication (look it up: http://dev.mysql.com/doc/refman/5.5/en/replication.html http://dev.mysql.com/doc/refman/5.5/en/replication-formats.html). There is statement-replication, and row-replication. The former replicates all operations on the database, in the form wherin they were made. False changes (changing a field to its former value), too, are recorded. The latter records only those changes to a table that are real changes. (In MySQL statement-replication is of earlier implementation.) After changes are recorded, they are passed from the master --the wellspring of the changes-- to the slave --the taker of them. These are not tools, as such: replication is something implemented in the database-management system. If both your databases are in MySQL you can get help here. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
error-log aging
Is there somewhere within MySQL means of aging the error log, that it not indefinitly grow big, or is that done through the OS and filesystem on which mysqld runs? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Determing number of queries
2013/04/04 22:40 +0200, Manuel Arostegui You can start with show innodb status; It is now show engine innodb status -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: error-log aging
2013/04/04 23:18 +0200, Reindl Harald Is there somewhere within MySQL means of aging the error log, that it not indefinitly grow big, or is that done through the OS and filesystem on which mysqld runs? man logrotate Not Unix! In any case, I take this to mean that this is not done within MySQL, right? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Show ROUTINE body, not PROCEDURE
2013/03/27 08:01 +0200, Dotan Cohen Actually, it is the user that I am logged in as that created the function. That is why I find it hard to believe that one needs root / admin access to see its definition. And that user set DEFINER other than itself, and that worked??? That takes SUPER. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Show ROUTINE body, not PROCEDURE
2013/03/25 11:28 +0200, Dotan Cohen Thanks. I don't have the admin or root privileges on this database. Is that the only way to see the code behind the function? Well, you showed us DEFINER: admin@localhost for the function; here is a snippet from MySQL help about SHOW CREATE FUNCTION func_name : ... require that you be the owner of the routine or have file:///C:/Program%20Files/MySQL/MySQL%20Server%205.5/HELP/sql-syntax.html#selectSELECT access to the mysql.proc table. If you do not have privileges for the routine itself, the value displayed for the Create Procedure or Create Function field will be NULL. I guess you have to find Admin (or Root) in real life to talk about the function. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: a little doubt on text about MySQL
2013/03/16 03:44 +0100, Reindl Harald what are you speaking about? you can define it in my.cnf and YOU are responsible for the configuration as you are also responsible the develop php code with error_reporting = E_ALL These SQL-modes that pertain to type-safety are really part of the _type_: ALLOW_INVALID_DATES NO_ZERO_DATE NO_ZERO_IN_DATE Their value when one does CREATE TABLE ... really belongs to the newly created table, if not to particular fields in the table. It is type-declaration. This one, NO_AUTO_VALUE_ON_ZERO, is part of the table s type, and belongs with the newly created table, or with the fields on which it bears (MyISAM). It really is not right that one who designs a table designs it with one date setting or another in mind, then another, who uses that table, changes any of these in local SQL mode, and thereby changes the type. As for this one, NO_AUTO_CREATE_USER, there is no reason for letting it differ in local or global SQL-mode from that defined in my.cnf (my.ini). Inasmuch as MySQL lets one set that apart from the configuration file, there is a problem, especially from dropping it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: a little doubt on text about MySQL
2013/03/15 12:43 -0300, Marcus Vinicius
Does anyone knows the author of this:
http://grimoire.ca/mysql/choose-something-else
Title: Do Not Pass This Way Again
Not I
--but, as to automatic type-conversion, I find me in agreement with the author.
When I first began to use MySQL I was dismayed at all the automatic conversion,
some of it decidedly unintuitive, and this one is a good example of particular
badness:
mysql select 0 = 'banana';
+--+
| 0 = 'banana' |
+--+
|1 |
+--+
1 row in set, 1 warning (0.03 sec)
mysql show warnings;
+-+--++
| Level | Code | Message|
+-+--++
| Warning | 1292 | Truncated incorrect DOUBLE value: 'banana' |
+-+--++
1 row in set (0.00 sec)
This definitly is an error to show, not let be a warning.
All those 0-values on saving into the table, The developers seem deeply
loath to report error. When the table is not transactional, yes, there is some
reason for it, but a not transactional table is not a full partner in database.
When the table is transactional, to report error is the way to go, although a
big LOAD DATA be aborted, not to make the user learn about all those 0-values.
He makes a point that had not come to me, that type-security somewhat depends
on SQL_MODE, which belongs to the connection. It is, quite rightly, stored in
saved program code, but not in any table. At least these affect table behavior,
ALLOW_INVALID_DATES
NO_AUTO_CREATE_USER (administrational security!)
NO_AUTO_VALUE_ON_ZERO
NO_ZERO_DATE
NO_ZERO_IN_DATE
PAD_CHAR_TO_FULL_LENGTH (well, maybe not this one)
STRICT_ALL_TABLES
STRICT_TRANS_TABLES
, and they belong in the table, even as those that affect parsing compiling
belong in saved code, not only in the connection.
MySQL s own types, ENUM and SET, which have both string integer
manifestation, are not well handled. Try this (version 5.5):
create temporary table v (m set ('a','b'));
insert into v value (1),(2),('a'),('b'),('b,a'),(null);
select m,if( m is not null, m, 21) + 1 from v;
select m,ifnull( m, 21) + 1 from v; -- no warning, either
(and the numeric context is DOUBLE!)
What about these?
select m,if( m is not null, m, 21) from v;
select m,ifnull( m, 21) from v;
I feel that the numeric constant sets the context to numeric--the IF[NULL] s
own context is indeterminate--, but not that happens.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: Retrieve most recent of multiple rows
2013/03/13 13:18 +, Norah Jones I have a table which looks like this: answer_id q_id answer qscore_id answer_timestamp 1 10Male3 1363091016 2 10Male3 1363091017 3 11Male3 1363091018 4 10Male3 1363091019 5 11Male3 1363091020 6 12Male3 1363091020 7 11Male3 1363091025 So I have multiple answers for the same questions (q_id). I want to be able to retrieve only ONE answer per question and that be the most recent answer. There should be THREE rows returned, which are all the most recent answered for that q_id: 4 10Male3 1363091019 6 12Male3 1363091020 7 11Male3 1363091025 changed! Something like this: select * from x where (answer_timestamp,q_id) in ( select max(answer_timestamp), q_id from x group by q_id) group by q_id; It makes use of MySQL s feature of allowing not aggregated fields with GROUP BY. Otherwise each record with the same q_id and greatest answer_timestamp would be shown. Nothing is guaranteed which is indeed shown. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
IF and CASE
It is my impression that when their functions are equivalent, IF takes more time than CASE. Comment? Do they always evaluate all their arguments? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: IF and CASE
2013/02/05 17:06 +, Rick James As a Rule of Thumb, function evaluation time is not significant to the overall time for running a query. (I see IF and CASE as 'functions' for this discussion.) Do you have evidence that says that IF is slower? Perhaps using BENCHMARK()? Not BENCHMARK: I did a query with one, and also with the other, and repeated each at least a dozen times, and looked at the reported time. The IF-variant took ever so slightly more time than the CASE-variant. But which of the arguments are always evaluated, which only at need? This could be a difference, that IF s arguments always are, CASE s only at need. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Complex MySQL Select Statement Help
2013/02/02 12:58 -0600, Peter Brawley On 2013-02-01 10:18 PM, h...@tbbs.net wrote: 2013/01/31 22:24 -0600, Peter Brawley Is this what you mean? Select, pricelist If( !IsNull(specialprice) And specialprice unitprice And CurDate() Between startingDate And endingDate, specialprice, unitprice ) as used_price From catalog Where itemid='WB314'; PB Maybe this is gilding the lily, but if specialprice is null, then specialprice unitprice is not true Read again: ...If( !IsNull( specialprice )... Right: if specialprice unitprice is true, then specialprice is not null. The null-test is absorbed. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Complex MySQL Select Statement Help
2013/01/31 22:24 -0600, Peter Brawley Is this what you mean? Select, pricelist If( !IsNull(specialprice) And specialprice unitprice And CurDate() Between startingDate And endingDate, specialprice, unitprice ) as used_price From catalog Where itemid='WB314'; PB Maybe this is gilding the lily, but if specialprice is null, then specialprice unitprice is not true--and maybe if the null-test is left out it is less clear -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Foreign-key naming
When I wrote my comment after Larry Martell s problem, I already suspected it was somewhat out of place because to his problem it did not apply. 2012/12/12 08:25 -0500, Shawn Green This is a perfectly acceptable naming convention to use. For example if you have a field on the `art` table that references the ID column of the `person` table to indicate the owner and another field to indicate the person who created the art, you might want to use the names `owner_person_id` and `artist_person_id` to keep them separate from the `id` column used to uniquely identify the work of art itself. In this design pattern, each table has a numeric ID column (string-based primary keys are perfectly legal but have their drawbacks and should be used with care) and to reference it from another table you can use the pattern parenttable_id. It keeps your naming conventions clean and predictable. If I were to try to use a USING operator in my opening example, I would be trying to match the PK fields of two separate types of data. (the USING example) SELECT ... FROM art INNER JOIN person USING(id)... Let's say that I renamed the id fields to art_id and person_id to make them table-specific. This still fails because a person's identifier as an owner is not the same as a work of art's creator. It also does not allow me to use the `person` table more than once in a single query. (FAIL: a renamed USING example) SELECT ... FROM art INNER JOIN person USING(person_id) --- does this refer to the owner or the creator of the art? (the name template example) SELECT ... FROM art INNER JOIN person owner on art.owner_person_id = owner.id INNER JOIN person artist on art.artist_person_id = artist.id ... Well, you're right, a work can refer to people in at least two different aspects, there is the work's author, and the work s owner. Neither is appropriate for the same name as found in a list of people, because now a distinction is made in the undifferentiated mass. And, yes, in general I suspect that if in one table there are more foreign-key references to the same key in another table, there is enough difference in aspect that none of them is fittingly so named as in the original table. What if neither author nor owner directly referred to people, but, instead, author referred to a table of artists, with their training style listed, and owner referred to a table of owners, with preferred styles of work listed? These tables in the end would refer to people; shall their references bear a name distinct from the key in the original table's? I believe that for every chain of foreign-key references from one table to another, if there is no other chain of foreign-key references from that one table to that other table (and no design-change that changes this is likely!), it is quite all right if along the chain each foreign-key reference and the key to which each refers have the same name. The nice thing about USING and NATURAL is that in a query only one coalesced field is yielded. I find it not quite right to pick between one field or another to yield when both are alike unless one of them is NULL. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Help with left outer join
2012/12/11 16:19 -0500, Larry Martell I have this query: SELECT data_target.name, ep, wafer_id, lot_id, date_time, data_file_id, data_cstimage.name, bottom, wf_file_path_id, data_measparams.name, vacc, data_category.name FROM data_cst, data_target, data_cstimage, data_measparams, data_category, data_tool WHERE data_cst.target_name_id IN (38018, 29947, 28330) AND data_cst.date_time BETWEEN '2012-09-01 00:00:00' AND '2012-09-07 00:00:00' AND data_target.id = data_cst.target_name_id AND data_cstimage.id = data_cst.image_measurer_id AND data_measparams.id = data_cst.meas_params_name_id AND data_category.id = data_tool.category_id AND data_tool.id = data_cst.tool_id ORDER BY target_name_id, ep, wafer_id, lot_id, date_time My problem is that when data_cst.image_measurer_id is NULL I don't get that data_cst row even though all the other part of the where clause are TRUE. I understand why that is, but in that case I want the row, but with NULL in the data_cstimage.name column. I think I need a left outer join, but I've been messing with this for hours, and I can't get the syntax right. I've googled it, but all the examples are simple with just 2 tables. Can someone help me with this? Modern forms do not give a left join if one uses WHERE-clause to reduce a full cross-join to an inner join. It is better to start with something like this, FROM data_cst JOIN data_target ON data_target.id = data_cst.target_name_id JOIN data_cstimage ON data_cstimage.id = data_cst.image_measurer_id JOIN data_measparams ON data_measparams.id = data_cst.meas_params_name_id JOIN (data_category JOIN data_tool ON data_category.id = data_tool.category_id) ON data_tool.id = data_cst.tool_id but I am not too sure where to bracket data_tool. When you have put it into a 'FROM'-clause with 'JOIN', not comma, separating the tables, with the same outcome as now, then you have to decide before which 'JOIN' to put the 'LEFT'. Maybe you want it between data_cstimage and data_measparams. (Are all the distinct ids really needed? When one joins on a field with the same name in both tables, one may use 'USING', and only the common field, with neither NULL, shows up in the output.) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
CONCAT_WS and NULL
This is, maybe, a question of taste. I find it useful in the aggregate functions that they ignore all NULLs that come under their purview, but yield NULL if nothing else comes. Now, CONCAT_WS is no aggregate function, but is like them in that it ignores all NULLs that come its way, aside from the first argument, the separator. If all are NULL, it yields empty string. I now find that I wish that in this, too, it were like the aggregate functions, yielding NULL if its arguments are NULL. Comment? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: MUltiple value in single insert is not working in mysql procedure
2012/12/03 19:10 +0530, amit
Problem
mysql call mobile_series1('(99889988),(12334565)');
You are expecting MySQL to turn one string operand into twain number operands.
That does not happen, unless you use PREPARE, which, I suspect, is not part of
your homework.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: MySQL dying?
2012/12/04 15:18 -0800, Karen Abgarian MySQL, like all other products, can be peachy or bitchy. Good ones, they also die.Wish I was kidding :-) Mind VHS BetaMax? BetaMax had much better color--but VHS long outlasted it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Retrieve the values from the table of its max date
2012/11/29 11:46 +0530, Trimurthy i have a table which contains the columns date,sname,age,item,quantity,units.my question is i want to retrieve all the values from the table where date=maxdate group by sname how can i get those values. A question, I suspect, found in all SQL courses -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Stored Procedure Question?
2012/11/23 10:49 +0530, Girish Talluru I have a scenario where I have to screen a huge bunch of records for in db using certain rules. I have done in traditional php style record by record and it took 90 mins for 4000 records. I have 800k - 900k records in production which might possibly lead to days of execution. I have figured out that the php script does wait for the record to execute and then only after it it will process the next record. For this if it is java I should have used stored procedure and multithreading concept to run multiple threads in parallel. But I don't think PHP supports multithreading. Now I have idea to create a stored procedure to do all the checks and my question here is when I call a stored procedure does the control get backs immediately to the php script? Bcoz I want to pick other record immediately while the first one going through the process and call the procedure again. Sounds to me that if your data are in a character form like a CSV file, or you can put them into such a form, you can use LOAD DATA to insert into the database. Then you would use a separate procedure, outside SQL, beforehand to screen the data, and maybe turn them into MySQL s CSV-ish form. If you are using a PHP procedure, I suspect that you can do that. Furthermore, since your screener does not wait for MySQL, but only for PHP s own input-output, there is not that wait. Once LOAD DATA begins, it very swiftly runs, and your (other?) PHP procedure waits for _all_ the records to be inserted, not each one by one. Of course, you could batch them, too, instead of making one CSV file of 900,000 records. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Basic SELECT help
2012/11/22 14:30 +, Neil Tompkins
I'm struggling with what I think is a basic select but can't think how to
do it : My data is
id,type
1000,5
1001,5
1002,2
1001,2
1003,2
1005,2
1006,1
From this I what to get a distinct list of id where the type equals 2 and 5
Any ideas ?
This ugly one, which generalizes:
select id,group_concat(type) AS tl from the_table group by id having
find_in_set('2',tl) and find_in_set('5',tl)
Ugly becaus it involves so much converting between number string.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: Basic SELECT help
2012/11/22 14:30 +, Neil Tompkins
I'm struggling with what I think is a basic select but can't think how to
do it : My data is
id,type
1000,5
1001,5
1002,2
1001,2
1003,2
1005,2
1006,1
From this I what to get a distinct list of id where the type equals 2 and 5
Any ideas ?
This ugly one, which generalizes:
select id,group_concat(type) AS tl from the_table group by id having
find_in_set('2',tl) and find_in_set('5',tl)
Ugly becaus it involves so much converting between number string.
For full generality one would indeed write
GROUP_CONCAT(type ORDER BY type)
and pass my tl and a string, say '1,2', to a procedure that using
SUBSTRING_INDEX taking the strings for arrays ensures that all found in the
first string is also in the second string. There are times when I wish SQL had
arrays.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: Dynamic crosstab got me lost.
2012/11/19 05:05 +0100, Mogens Melander
I found an article on:
http://stackoverflow.com/questions/3122424/dynamic-mysql-query-view-for-crosstab
Describing how to do the dynamic generation of SQL statements.
And I was inspired to do some such thing to one of my views.
This view has three fields:
City-ZIP: a string of 5-digit ZIP-code and place-name;
Hoads: one of 11 distinct number from 1 through 7.5, showing how good the
member is;
Members: howmany so good members dwell at that place.
It is more convenient to show this in a table with City-ZIP at the left, HoadS
across the top, and Members throughout the middle. OpenOffice Calc has the
needed operation, and I regularly used it for making the table. But with this,
I can do much of it in MySQL (no row totals):
SELECT 'SELECT City-ZIP, ' || GROUP_CONCAT('SUM(IF(HoadS = ' || HoadS || ',
Members, NULL)) AS ' || HoadS || '') || '
FROM ZIPbwise
GROUP BY City-ZIP WITH ROLLUP'
FROM (SELECT HoadS FROM zipbwise GROUP BY HoadS) AS g
It yields this query:
SELECT City-ZIP,
SUM(IF(HoadS = 1.0, Members, NULL)) AS 1.0,
SUM(IF(HoadS = 1.5, Members, NULL)) AS 1.5,
SUM(IF(HoadS = 2.0, Members, NULL)) AS 2.0,
SUM(IF(HoadS = 3.0, Members, NULL)) AS 3.0,
SUM(IF(HoadS = 4.0, Members, NULL)) AS 4.0,
SUM(IF(HoadS = 4.5, Members, NULL)) AS 4.5,
SUM(IF(HoadS = 5.0, Members, NULL)) AS 5.0,
SUM(IF(HoadS = 5.5, Members, NULL)) AS 5.5,
SUM(IF(HoadS = 6.5, Members, NULL)) AS 6.5,
SUM(IF(HoadS = 7.0, Members, NULL)) AS 7.0,
SUM(IF(HoadS = 7.5, Members, NULL)) AS 7.5
FROM ZIPbwise GROUP BY City-ZIP WITH ROLLUP
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: Dynamic crosstab got me lost.
2012/11/19 04:49 -0800, Jan Steinman SELECT main.code , IF(iconstandardrel.icon = 4,1,0) AS 'internationalt_produkt.eps' , IF(iconstandardrel.icon = 3,1,0) AS 'god_vaerdi.eps' , IF(iconstandardrel.icon = 2,1,0) AS 'for_miljoeets_skyld.eps' , IF(iconstandardrel.icon = 1,1,0) AS 'ergonomisk_produkt.eps' , IF(iconstandardrel.icon = 6,1,0) AS 'saml_selv.eps' , IF(iconstandardrel.icon = 12,1,0) AS 'brandfarlig.eps' FROM iconstandardrel JOIN main ON main.code = iconstandardrel.code JOIN iconstandard ON iconstandard.id = iconstandardrel.icon ORDER BY iconstandardrel.code; Which produces results like: 101577, 1, 0, 0, 0, 0, 0 101679, 0, 1, 0, 0, 0, 0 101679, 1, 0, 0, 0, 0, 0 101681, 1, 0, 0, 0, 0, 0 101748, 0, 1, 0, 0, 0, 0 101748, 1, 0, 0, 0, 0, 0 But I would like to have One line per code: 101577, 1, 0, 0, 0, 0, 0 101679, 1, 1, 0, 0, 0, 0 101681, 1, 0, 0, 0, 0, 0 101748, 1, 1, 0, 0, 0, 0 Is it possible to achieve this in pure SQL ? I think you need GROUP BY main.code. Yes, with fit aggregate functions around the IFs: MAX would work, and also BIT_OR. I hav seen no aggregate function that corresponds to OR, the most natural in your case. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Issue regarding the import of the date from csv file to the table in the database in mysql
2012/11/15 00:30 +0100, Mogens Melander I guess I'm sill learning. Does that mean that, if the last column in a load blabla. is a -00-00 terminated by ^n it might error ? Or are we talking ODBC ? Find it under LOAD DATA If an empty field is parsed for a NOT NULL DATE or DATETIME, instead of reporting error as in strict mode, the parser makes the date -00-00--Maybe in ODBC, too, which I do not know, but certainly from character input. (I regularly use CSV files with MySQL, although the match is not perfect.) This is a particular case of zero (see LOAD DATA) for any NOT NULL type. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Issue regarding the import of the date from csv file to the table in the database in mysql
2012/11/14 10:26 +0530, sagar bs As i have the data with some 25 variables in csv file and i need to import to mysql. The issue is that the date format in csv file is dd/mm/ and mysql takes the date format like /mm/dd. The number of variables in the csv file are same in the table in database of mysql. Please help me out. Use LOAD DATA s feature of in the same SQL statement importing into a user variable and using it with SET, using the function STR_TO_DATE: load data ... (..., @dait, ...) ... SET Sins = STR_TO_DATE(@dait, '%d/%m/%Y') . It is not important how many decimal digits match each pattern, but it cannot match variation in the separators. It is required that NULLs in the file take the form '\N' or 'NULL', depending on escape-option. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Issue regarding the import of the date from csv file to the table in the database in mysql
2012/11/14 18:27 +0530, sagar bs There are four columns in my table named like account_name, c1, c2 and c3. Account name is the primary key and c1, c2 contain two different dates and in the column c2 there are few fields showing /00/00, now i need to get the date different(in days) between the dates present in the c1 and c2. That days should be shown in the c3. please help me out. Try DATEDIFF. As for date /00/00, MySQL s treatment of NULLs in CSV files is peculiar: it wants the escape NULL or \N, separator right after separator is not NULL, but empty string. Consider those NULL. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: UDF behaves non-deterministic
2012/11/04 22:23 +, Stefan Kuhn select * from table order by udf(column, 'input_value') desc; For my understanding, this should give the same result always. But if for your data function udf returns the same for more arguments there is not enough to fix the order. In that case I have found that other accidental things affect the order, things that one would not suspect: howmuch store is used and needed for the ordering, ... a further reason for showing what the function returns. If the order varies, although the function returns the same in all cases, well, -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: mysql logs query with indexes used to the slow-log and not logging if there is index in reverse order
2012/10/16 12:57 -0400, Michael Dykman your now() statement is getting executed for every row on the select. try ptting the phrase up front as in: set @ut= unix_timestamp(now()) and then use that in your statement. Quote: Functions that return the current date or time each are evaluated only once per query at the start of query execution. This means that multiple references to a function such as file:///C:/Program%20Files/MySQL/MySQL%20Server%205.5/HELP/functions.html#function_nowNOW() within a single query always produce the same result. (For our purposes, a single query also includes a call to a stored program (stored routine, trigger, or event) and all subprograms called by that program.) This principle also applies to file:///C:/Program%20Files/MySQL/MySQL%20Server%205.5/HELP/functions.html#function_curdateCURDATE(), file:///C:/Program%20Files/MySQL/MySQL%20Server%205.5/HELP/functions.html#function_curtimeCURTIME(), file:///C:/Program%20Files/MySQL/MySQL%20Server%205.5/HELP/functions.html#function_utc-dateUTC_DATE(), file:///C:/Program%20Files/MySQL/MySQL%20Server%205.5/HELP/functions.html#function_utc-timeUTC_TIME(), file:///C:/Program%20Files/MySQL/MySQL%20Server%205.5/HELP/f! unctio ns.html#function_utc-timestampUTC_TIMESTAMP(), and to any of their synonyms. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Extract text from string
2012/10/12 17:56 +0100, Neil Tompkins Is there such a way in a MySQL query to extract the text this is a test from the following strings as a example stronga href=http://www.domain.com/; class=linkthis is a test/a/strong stronga href=http://www.domain.com/; title=this is a test class=linklink/a/strong Amongst the built-in functions there isn't much; for the former example, if the string is s, this works: SUBSTRING_INDEX(SUBSTRING_INDEX(s, '', -3), '', 1) ; for the latter, if t: SUBSTRING_INDEX(SUBSTRING_INDEX(t, '', -4), '', 1) . If you want a real HTML parser that discards everything but arbitrary strings, you have to get install your own. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: column aliases in query
2012/10/11 13:46 -0400, Mark Haney I know it's been a while since I wrote serious queries, but I'm sure I have done something like this before: SELECT SUBSTR(date,1,10) as vDate, event_id, events.mach_id, machine.factory_id FROM events JOIN machine ON events.mach_id = machine.mach_id WHERE machine.factory_id = 1 AND vDate = 2012-10-11 Where I've aliased the SUBSTR of the date and then used the alias in the WHERE clause of the query. I'm getting an error message now, but I'm almost certain I've used that syntax before. Am I missing something? Yes: WHERE is for already defined names. In HAVING one refers to new names: SELECT SUBSTR(date,1,10) as vDate, event_id, events.mach_id, machine.factory_id FROM events JOIN machine ON events.mach_id = machine.mach_id WHERE machine.factory_id = 1 HAVING vDate = 2012-10-11 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: date-IFNULL-sum bug?
2012/10/08 14:52 -0700, Rick James Do not use + for DATE arithmetic! Use, for example + INTERVAL 1 YEAR No, those operations are well defined. Amongst the timestamp-functions there is constant reference to numeric context, and character context--and well there is, because there are no time-constants, only numerals and character strings taken for timestamps. It is also the only means of doing some things. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
date-IFNULL-sum bug?
Can anyone explain this to me?
The first one seems quite wrong; the rest make perfect sense.
mysql select ifnull(date('1900/5/3'), date('1900/01/01')) + 1;
+--+
| ifnull(date('1900/5/3'), date('1900/01/01')) + 1 |
+--+
|11900 |
+--+
1 row in set (0.00 sec)
mysql select ifnull(date('1900/5/3'), date('1900/01/01'));
+--+
| ifnull(date('1900/5/3'), date('1900/01/01')) |
+--+
| 1900-05-03 |
+--+
1 row in set (0.00 sec)
mysql select date('1900/5/3') + 1;
+--+
| date('1900/5/3') + 1 |
+--+
| 19010503 |
+--+
1 row in set (0.00 sec)
mysql select date(date('1900/5/3') + 1);
++
| date(date('1900/5/3') + 1) |
++
| 1901-05-03 |
++
1 row in set (0.00 sec)
(5.5.8 under muSoft Windows)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: (real) silly question about variables...
For this, 2012/10/04 16:13 +0200, MAS! IF(GROUP_CONCAT(DISTINCT secA.sec_code SEPARATOR '|') is null, IF(GROUP_CONCAT(DISTINCT secB.sec_code SEPARATOR '|') is null, settore, GROUP_CONCAT(DISTINCT secB.sec_code SEPARATOR '|')), GROUP_CONCAT(DISTINCT secA.sec_code SEPARATOR '|') ) as settore, you really want IFnull(GROUP_CONCAT(DISTINCT secA.sec_code SEPARATOR '|'), IFnull(GROUP_CONCAT(DISTINCT secB.sec_code SEPARATOR '|'), settore)) as settore, The operation If this is not null use this, else use that is far, far too common in SQL for there not to be a function for it. As for your original question, note this from the MySQL HTML documentation: 8.4. User-Defined Variables ... As a general rule, you should never assign a value to a user variable and read the value within the same statement. You might get the results you expect, but this is not guaranteed. The order of evaluation for expressions involving user variables is undefined and may change based on the elements contained within a given statement. In SELECT @a, @a:=@a+1, ..., you might think that MySQL will evaluate @a first and then do an assignment second. However, changing the statement (for example, by adding a GROUP BY, HAVING, or ORDER BY clause) may cause MySQL to select an execution plan with a different order of evaluation. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
