Re: Using INTEGER instead of VARCHAR/DATE - is this a way to faster access?
2014/11/02 13:19 +0100, Zbigniew So you guys (Jan and hsv) suggest, that switching from DATE to more numeric data type may not be necessary, but using ENUM instead of VARCHAR can be real performance gain, right? But are you able to estimate, what boost can i notice? 5% - or 50%, or maybe even 500%? _Maybe_! but I will not venture to measure it. I doubt that it would be big. I just looked ENUM up: it allows 65535 distinct values, which sounds like 16 bits, usually two bytes, and numeric operators would be used. 2014/11/02 11:19 -0800, Jan Steinman I would hope that the query optimizer converts '2014-11-02' to the three-bytes internal representation of DATE before doing the query, in which case, DATE should actually be a tiny bit faster than TIMESTAMP. That is doubtful. In the processors that I know, one built-in numeric operation is enough for either 2 bytes of ENUM or 4 bytes of TIMESTAMP, but three are needed for DATE. In any case, the C-overhead, since MySQL is implemented in C, overwhelms the cost of any single comparison. The equality comparison is at least simpler than an ordered comparison. 2014/10/29 20:56 +0100, Zbigniew if instead of textual labels I'll use SMALLINT as simple integer code for each different label (and in my program a translation table)? This, in effect, is ENUM, and maybe is useful if the set of dates is well determined. If you use ENUM at least the overhead of translation is built in into MySQL, and, one hopes, costs less than doing it for oneself. There is a design cost in using ENUM: If you find that your first set of dates is too small, later, with ALTER TABLE, you have to change the type. If you add the new string to the end, there is, maybe, no great cost to the adding, but if in the middle If every new date-string is added to the end, it will, maybe, be in random order. The same applys to your translation table. I suggested TIMESTAMP because I suspect that one built-in comparison (after the optimizer is done with it) is enough, and it allows the appearance of real dates. (If the processor fetches 32 bits at a time (nowadays 64 or more is likly) then a 32-bit type is fetched as swiftly as any other. Both shorter and longer types take longer.) The more I debate this, the better I like TIMESTAMP for your problem. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: Trouble with Average
2012/07/16 14:25 -0700, Rick James Here's a different way to smooth numbers. It uses an exponential moving average instead of the last 5. SELECT Time, @a := (9 * @a + Value) / 10 AS moving_avg FROM tbl JOIN ( SELECT @a := 0 ) AS x; Notes: * Make 10 larger or smaller, depending on how smooth you want it. * 9=10-1 * @a := 0 should be changed to some reasonable starting value, else the graph will be artificially low to start with. Hunh, MySQL: to use the optimizer s order for an initial value never would have occurred to me. The important thing in using decaying average (I so know it) is this: @a := (B * @a + C * Value) / (B + C) and B, C 0. The bigger B is the more important the past; therefore, the change is smaller and the graph is smoother. The smaller B is the less important the initial value is. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Trouble with Average
2012/07/07 12:10 -0600, Albert Padley
I have a log file that captures data from various sensors every minute that
we use to draws graphs on our website.
The table looks like this:
CREATE TABLE `log` (
`id` int(14) NOT NULL auto_increment,
`VarName` varchar(255) NOT NULL,
`TimeString` datetime NOT NULL,
`VarValue` decimal(25,6) NOT NULL,
`Validity` int(1) NOT NULL,
`Time_ms` decimal(25,6) NOT NULL,
PRIMARY KEY (`id`),
KEY `timestamp` (`TimeString`),
KEY `name` (`VarName`),
KEY `nametimevalue` (`VarName`,`VarValue`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
My existing query which works just fine for our purposes:
SELECT CONCAT('[', ((UNIX_TIMESTAMP(TimeString)+(.$tz_offset.*3600))) *
1000, ' ,', TRUNCATE(VarValue,0), ']') AS value FROM log WHERE VarName =
'04_Set21_SWOS\_085_1300CI' AND SUBSTR(TimeString,1,10) = CURDATE() ORDER
BY TimeString ASC
The only issue is that VarValue tends to vary every minute and create a
graph that is not as smooth as we would like. Therefore, instead of
retuning VarValue for each minute in the above query, we want to return the
average of the last 5 values for that VarName.
I have been searching around the web all morning and haven't hit on the
proper solution. Help would be much appreciated.
(It would be neater to write DATE(TimeString) = CURDATE())
I suspect that there is good reason for that, and that that is better done
afterwards, with TimeString and VarValue until then separated. In MySQL one
could in a subquery find the five latest TimeStrings not greater than a given
TimeString and key off that (if they are not unique this is hopeless), but
the amount of repetition is enormous. One could do the smoothing within SQL
with a procedure that reads a cursor, or in a script language on the output.
(tz_offset would become an argument to the MySQL procedure.)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
group_key?
Here is something queer: select ifnull(email, round(1 * rand(), 1)) as ux, count(*) from nam group by ux; ERROR 1062 (23000): Duplicate entry '2514.0' for key 'group_key' I have a name-list, with e-mail address or not. I wanted to fill the NULL e-mail addresses with something random, and, I hope, unique. (ROUND is there only to make that surprising error liklier.) The field email is not UNIQUE, not a key, no intention of making it such. Why this error? Is RAND called more than once for each record? I tryed also UUID, but that came with its own shortcoming: if the UUID call were the whole field, it indeed was once called for every record, but if argument to IFNULL, only once for the whole query. Version 5.5.8 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: group_key?
2012/07/05 21:01 +0200, Claudio Nanni you might have hit: http://bugs.mysql.com/bug.php?id=58081 Indeed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Handler?
2012/06/13 10:06 -0500, mos You may want to look into Handler. I've used it often when I needed to traverse very large MyISAM tables. Handler requires no physical sorting of the table because it traverses the table using an index. It also ignores any locks on the table (which you may or may not like), but the Handler will start traversing the table immediately. It will solve problems 1,3 ,4 but not #2 because traversing a large table with an index will be slower than if the table was already sorted. One nice thing about the Handler is it uses virtually no additional memory regardless of table size and very little disk activity because there is no sorting. You can run it any time and it won't degrade other MySQL processes. I don't think Handler will join tables together; I have used it only to traverse a single table. I saw HANDLER written up, and from MySQL tryed it out, but cannot think of a real use for it. Is it for programs, PHP say, that call MySQL server and store its output in arrays, ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
UNION and ORDER BY
2012/06/20 14:32 -0700, Rick James ( SELECT ruid1, ruid2, overlap FROM l4_link WHERE ruid1=xxx UNION SELECT ruid1, ruid2, overlap FROM l4_link WHERE ruid2=xxx ) ORDER BY overlap DESC; Make it UNION ALL or UNION DISTINCT depending on whether xxx can be in both fields of one row. UNION DISTINCT makes a pass over the temp table to dedup. The help example shows a UNION s each SELECT separatly round-bracketed, (SELECT ruid1, ruid2, overlap FROM l4_link WHERE ruid1=xxx) UNION (SELECT ruid1, ruid2, overlap FROM l4_link WHERE ruid2=xxx) ORDER BY overlap DESC but bracketing both is not mentioned. What is the difference? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Triggers and column names
2012/06/21 17:06 -0500, Gael Martinez I'm getting that done today thru a large static trigger script and I would like something more dynamic... For that it is needful to look up the table in INFORMATION_SCHEMA.COLUMNS, and, yes, you can look up the field names--but then what will you do with the character strings that are the field names? use PREPARE and EXECUTE for the comparisons, being ware of NULL? In MySQL help it is written that for its own purposes MySQL actually tracks all the information that you crave, but it nowhere is written that a BEFORE-UPDATE trigger can make use of it. Maybe UDF, but I know naught about that. Since BEFORE is called on every attempt, successful or not, maybe AFTER would be better. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Postal code searching
2012/04/25 10:14 +0100, Mark Goodge On 24/04/2012 17:24, Tompkins Neil wrote: How about if I want to only return postal codes that are like W1U 8JE not W13 0SU. Because in this example I have W1 as the postal code and W13 is the other postal code No, you don't. In this example you have W1U as one outbound code and W13 as the other. W1U postcodes are not a subset of W1 postcodes, any more than IP27 postcodes are a subset of IP2 postcodes. The fact that in W1U the district segment is in the form of NA rather than NN doesn't change the fact that it's an indivisible two-character code. So I think the first question has to be, why do you want to get W1 as a particular substring from the postcode W1U 8JE? British postcodes have a structure which is easy for humans to understand, although (unfortunately) rather hard to parse automatically. Essentially, every full postcode contains four elements: Area code: one or two alpha characters, either A or AA District code: one or two alphanumeric characters the first of which is always numeric, either N, NN or NA Sector code: single numeric character, always N Walk code: two alpha characters, always AA It's customary, but not part of the formal specification, to insert whitespace between the District and Sector codes. So, given the postcode WC1H 8EJ, we have: Area: WC District: 1H Sector: 8 Walk: EJ Taken together, the first two sections form the outbound part of the postcode, and the second two form the inbound. (That is, the first two identify the destination sorting depot that the originating depot will send the post to, and the second two are used by the destination depot to make the actual delivery). The reason for mentioning this is that postcodes, having a wide range of possible formats, are not easy to handle with simple substring searches if you're trying to extract outbound codes from a full postcode. It can be done with regular expressions, but you have to be wary of assuming that the space between District and Sector will always be present as, particularly if you're getting data from user input, it might not be. In my own experience (which is quite extensive, as I've done a lot of work with systems, such as online retail, which use postcodes as a key part of the data), I've always found it simpler to pre-process the postcodes prior to inserting them into the database in order to ensure they have a consistent format (eg, inserting a space if none exists). That then makes it easy to select an outbound code, as you can use the space as a boundary. But if you want to be able to go further up the tree and select area codes (eg, distinguishing between EC, WC and W) then it's harder, as you have to account for the fact that some are two characters and some are only one. You can do it with a regular expression, taking everything prior to the first digit, but it's a lot easier in this case to extract the area code prior to inserting the data into the database and store the area code in a separate column. It seems to me that sector walk taken together always make up three characters; therefore, blanks aside, the outbound part from a good postcode is LEFT(pc, CHAR_LENGTH(pc)-3) , and with REPLACE it is trivial to drop all blanks. If Neil Tompkins wanted only to get the outbound part, that is enough. As for the area, if it is only one or twain characters long, to get that this is enough: LEFT(pc, IF(SUBSTR(pc, 2, 1) '9', 2, 1)) . Extremely crude coding, but if the postcode is right, This much one can do within an SQL function with no regular-expression handling --and MySQL s regular-expression handling yields only 'yes' or 'no'-- , but, of course, if one wishes to verify that it is right, that is another matter. Are there any rules for that, or is the best recourse to get a file of good outbound codes from the post office? As for the string-matching question, matching 'W1' and 'W13' against 'W13 0SU', one rule to consider is that the longest match is the right one. This problem or like is often given in SQL classes: SELECT * FROM pc JOIN shortpc ON LEFT(pc.c, CHAR_LENGTH(shortpc.c)) = shortpc.c WHERE (SELECT MAX(CHAR_LENGTH(shortpc.c)) FROM pc AS a JOIN shortpc ON LEFT(a.c, CHAR_LENGTH(shortpc.c)) = shortpc.c WHERE a.c = pc.c) = LENGTH(shortpc.c) I hope this is not a class problem. And after this I ask, is any outbound code a leading part of any other outbound code? If not, this twist is not needed. I also consider it good design if not. I was glad to see this lesson in British postcodes, something that I never pursued because I had no need of it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
show master status; show binary logs
In the command show binary logs one may indifferently write binary or master, and it is so for some other commands associated with this function--but for the command show master status there is no such variant. Why? Is it considered obsolescent? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: The most elegant/efficient way to pull this SQL query
2012/04/11 17:51 -0500, Peter Brawley
select b.peopleID, concat('(',p.fname,,')'), b.stateID, concat('(',s.state,')')
from bridge b
join people p on b.peopleID=p.peopleID
join state s on b.stateID=s.stateID;
Since the names are the same in the tables, it works to use USING, too, and
you are relieved of the burden of an alias:
from bridge
join people USING(peopleID)
join state USING(stateID)
If the fields peopleId and stateID are the only field names in common,
NATURAL JOIN also works.
from bridge
NATURAL join people
NATURAL join state
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: The most elegant/efficient way to pull this SQL query
; 2012/04/12 11:56 -0700, Haluk Karamete
My initial goal was to write a very convenient php function that display a
table view based on arguments that are super simple to write - without
requiring the developer to type-in ( or know ) the ins and outs of joins,
natural joins etc.
Something like this
function showtable($dbh,$table,$fields){
//get the $fields argument parse it out to come up
//with one of the SQL statements you guys are discussing.
SQL = BuildSQL($table,$fields)
//and then use this SQL to output the table
}
The magic would be happening in the $fields argument.
What's the most straightforward way to achieve the following view with one SQL
statement?
peopleID__stateID_
1_(joe)___1__(california)_
2_(bob)___4__(texas)__
3_(charlie)___5__(florida)
/quote
if the final table ( PeopleAndStates ) view I want were to be as follows;
peopleID__stateID_
1_1___
2_4___
3_5___
Then I would have called the function like this;
showtable($dbh,$myTable,peopleID,stateID)
But if I want to get, the following view instead;
peopleID__stateID_
1_(joe)___1___
2_(bob)___4___
3_(charlie)___5___
I would like to be able to call my function as follows;
showtable($dbh,$PeopleAndStates,peopleID(PeopleTable.PeopleID
FName),stateID)
To mean the following;
When you are outputting the peopleID, provide the corresponding Fname field
from the PeopleTable where peopleID there is equal to the peopleID you are
outputting.
What I was seeking from you guys was to find out to most simplistic SQL
statement so that when I parse the area with (PeopleTable.PeopleID FName), I
can extract the pieces and place it in the final SQL.
I'm not sure if you all get the idea of how such a function make debugging
super easy.
Once you write the parser, you can deploy it over many different cases such as
ShowRecord($dbh,$table,$fields,where peopleID5,limit 100) and so on.
So, the simpler the SQL, the easier the transition from the starting slate
which is really no different than
SELECT peopleID(PeopleTable.PeopleID FName),stateID from PeopleAndStates
(note that in MySQL '' is only right-shift.)
I fear that for this function in the end you will need
information_schema.COLUMNS.
Peter Brawley already gave you a good answer for one of your examples (but I
thus would write it):
select concat(peopleID, ' (',fname,')') AS peopleID, concat(stateID, '
(',state,')') AS stateID
from people
join PeopleAndStates USING(peopleID)
join state USING(stateID)
The middle example:
select peopleID, stateID from PeopleAndStates
The last:
select concat(peopleID, ' (',fname,')') AS peopleID, stateID
from people
join PeopleAndStates USING(peopleID)
join state USING(stateID)
I have assumed that you mean to join only on same-named fields with equality;
if not, JOIN ... ON ... is needed. In any case, you have to keep track of it,
whether in a result field the table name, too, is needed. If you use USING, for
that field leave the table name out.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: The most elegant/efficient way to pull this SQL query
; 2012/04/11 11:30 -0700, Haluk Karamete I've got this relational mySQL table that ties peopleIDs from the people table to the states IDs peopleID___stateID 1__1 2__4 3__5 people table is like this; ___peopleID_FName_ ___1joe ___2bob___ ___3charlie_ and finally the state table goes like this; ___stateID_State___ ___1___california ___2___new york ___3___washington__ ___4___texas___ ___5___florida__ What's the most straightforward way to achieve the following view with one SQL statement? peopleID__stateID_ 1_(joe)___1__(california)_ 2_(bob)___4__(texas)__ 3_(charlie)___5__(florida) Look at Stephen Tu s original post under the subject forcing mysql to use batched key access (BKA) optimization for joins. That his query solves a problem very much like yours--but use explicit JOINing, not implicit. He also uses an unhappy style of making every field name in the database unique. Joining is easier if the fields to be joined on have same names. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: forcing mysql to use batched key access (BKA) optimization for joins
2012/04/10 15:58 -0400, Stephen Tu select c_custkey, c_name, sum(l_extendedprice * (100 - l_discount)) as revenue, c_acctbal, n_name, c_address, c_phone, c_comment from CUSTOMER_INT, ORDERS_INT, LINEITEM_INT, NATION_INT where c_custkey = o_custkey and l_orderkey = o_orderkey and o_orderdate = date '1994-08-01' and o_orderdate date '1994-08-01' + interval '3' month and l_returnflag = 'R' and c_nationkey = n_nationkey group by c_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment order by revenue desc limit 20 I understand that MySQL works better if inner joining explicitly is stated, not implicitly as you have it. What are your keys, indices? Going by that which I have read heard, you want every field named after this query s 'where' an index, if not key--and, of course, your every field named '...key' is a key, right? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: create more tables in a single query
2012/04/04 19:56 +0200, joe j
/*STEP 2. from the dos prompt?*/
for x in UK ZA IN CN;
do mysql -ujoe -p -e INSERT INTO `table_new`
SELECT var1, var2`, (case when (country='${x}') then 1 else 0 end) AS citizen
,'${x}' AS ctry
FROM `'${x}'_table`
LEFT JOIN
WORLD
USING(YEAR);
For some reasons step2 is not working.
I assume step 2 must be run from DOS prompt (my mysql runs on a
server, and I am using PuTTY). Any thoughts?
I yet suggest that you write an SQL procedure with a constructed string passed
to PREPARE. You need a table with all the countries, and a cursor in the
procedure to scan the table--or you could use a string and step through two
characters at a time. It would be more complicated than that bit of
shell-programming, but independent of your OS.
A loop with a character string somewhat thus runs:
DECLARE i INT DEFAULT 1;
WHILE i 8 DO
PREPARE ... SUBSTRING('UKZAINCN', i, 2)
SET i = i + 2;
END WHILE;
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: Convert SQL Server Compact data to MySQL
2012/04/05 02:15 -0700, sam I have data file written in SQL Server Compact.I need to Convert it to MySQL. Does anyone know how to do that? If it stores in CSV files, you can use that for the data, although to make MySQL read it, you hav to fiddle with it. The SQL, for tables procedures, you have to do apart. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: JOIN giving duplicate records
; 2012/04/03 18:18 +0100, Tompkins Neil Before sending the table definition, and queries etc, can anyone advise why my query with four INNER JOIN might be give me back duplicate results e.g 100,UK,12121 100,UK,12121 Basically the query the statement AND (hotel_facilities.hotelfacilitytype_id = 47 OR hotel_facilities.hotelfacilitytype_id = 7) and if I add additional IDs like AND (hotel_facilities.hotelfacilitytype_id = 47 OR hotel_facilities.hotelfacilitytype_id = 7 OR hotel_facilities.hotelfacilitytype_id = 8), it would return three records like below when I'm expecting one record. 100,UK,12121 100,UK,12121 100,UK,12121 However in my SELECT statement if I use SELECT DISTINCT (field_name), I only get back one record. It is in JOIN s nature to multiply output, but unless you show your whole query, noöne can comment on it. As for DISTINCT, its purpose is as you saw, to eliminate duplicates. Sometimes it is the best means for it--but you show too little for comment. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: create multiple tables in a single query
2012/04/03 22:27 +0200, joe j So what I am trying to get is a script that runs through a list of country names (US, UK, NL, etc) and create tables for each one of them as above. Is this feasible in MySql? There are PROCEDUREs, and there is PREPARE. Look them up. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: big character constant
2012/03/27 12:33 +0200, Johan De Meersman MySQL does have a proper CSV engine; and it's mystifying why it isnt' accessible in any other way. I say engine, and you can take that literally: just like you have MyISAM and InnoDB engines, CSV is also an engine that is applicable to tables. Yes, I looked into it, but NULL is a problem, and its being in the data directory another one. The latest version requires NOT NULL for all fields, and that is too much for me. I believ an earlier version allowed NULL, but that, too, escaped it with the word NULL, as happens if with INTO OUTFILE one also sets FIELDS ESCAPED BY ''. Furthermore, field names are needed for my use. CSV reallie is useful, with its simple quoting mechanism. The one extension to allow for NULL I deem best is that there be nothing between separators for NULL, and a pair of double-quotes for an empty string. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: big character constant
2012/03/27 11:20 -0700, Todd Lyons This seems backwards from what I expected: mysql select convert(0xE29C94 using latin1), convert(0xE29C94 using utf8) \G *** 1. row *** convert(0xE29C94 using latin1): â convert(0xE29C94 using utf8): ? 1 row in set (0.00 sec) I expected the second one to print the check mark and the first one to have the problem because there is no heavy check mark in latin1. What am I missing, why is it printing out the correct value using latin1 but not using utf8 ? Correct value? It does not look correct in this message--but maybe you are using a display that understands UTF8. The output of convert(0xE29C94 using latin1) through the client is three bytes long, sent in order from high to low, and if your display does UTF8 the check will show, and the ASCII box drawing, which with \G is not used, will not line up. Try it after both charset latin1 and charset utf8 . -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: big character constant
2012/03/27 00:22 +0200, Walter Tross it looks like it's as simple as this: insert into mytable (mycolumn) values (0xE29C94), (0xE29C98) In some contexts it might be necessary to force the character set like this, though: convert(0xE29C94 using utf8) Yes! and with this I can use it for a constant in the middle of a query, which I really want: IF(IFNULL(ReceivesFlyer, 1), CONVERT(X'E29C94' USING utf8), '') . (I use the old PL1 constructs over the new C-constructs when they work alike.) Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: big character constant
; 2012/03/25 23:26 +0200, Walter Tross
I guess what you mean is Unicode characters U+2714 'HEAVY CHECK MARK' and
U+2718 'HEAVY BALLOT X'
(http://www.fileformat.info/info/unicode/char/2714/index.htm and
http://www.fileformat.info/info/unicode/char/2718/index.htm).
Unicode has several encodings, of which the most used are UTF-8 and UTF-16.
The answer to your question depends on the encoding of your column and on your
interface to the database.
As an example, IF your encoding is UTF-8 and you talk to the database using
PHP, you need something like:
$qry = mysql_query(insert into mytable (mycolumn) values ('\xE2\x9C\x94'),
('\xE2\x9C\x98'));
Inside double quotes PHP replaces the hex escape sequences with the bytes that
form the correct UTF-8 encoding of your characters (see the above web pages).
Yea, even so.
But I use only MySQL client, under Vista, no PHP here. I am afraid that if I
use the C-escapes to MySQL client it gives me three distinct characters, not
one character that takes three bytes in UTF8. I use also Navicat, and found
that I can copy the character from Word to Navicat, and Navicat well stores it
in a fitting character string. But I really want it in a view, for generating
input for Word (yes, I have to work MySQL CSV-ish output into real CSV,
ensuring that there are no escapes in it, nor 'NULL' either).
It would be splendid if there were a function that takes a U-number and turns
it into a character string that safely concatenates with a field of type
...CHAR... CHARACTER SET utf8
.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: Group_Concat help...
; 2012/03/19 12:06 -0400, Steven Staples SELECT `user_id`, GROUP_CONCAT(DISTINCT `login_ip`) AS 'login_ips', COUNT(`id`) AS 'connections' FROM `mysql_test` WHERE `login_datetime` BETWEEN '2012-03-19 00:00:00' AND '2012-03-19 23:59:59' GROUP BY `user_id` HAVING COUNT(`id`) 2 ORDER BY COUNT(`id`) DESC LIMIT 0, 15; user_id login_ipsconnections --- --- - 1 192.168.0.200 (1),192.168.0.201 (3) 5 Is this possible to do with just 1 query? If so, how would I go about doing it?? Yes, but with two levels: the inner level (a virtual table) groups by both user_id and login_ip, with COUNT; the outer as above, except from the inner level, not right from table mysql_test, and with not COUNT but SUM of the inner COUNTs. By the way, I would do this: WHERE DATE(`login_datetime`) = '2012-03-19' -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: date comparison query
2012/03/16 13:30 -0400, Simon Wilkinson My query for this is as follows: select * from table where table.date1 - table.date2 between 28425600 and 29030400; I would not count on that subtraction s yielding a meaningful number: the types are not Unix timestamps. I would use TIMESTAMPDIFF, with a good unit. From the downloaded help file: Date arithmetic also can be performed using INTERVAL together with the file:///C:/Program%20Files/MySQL/MySQL%20Server%205.1/HELP/functions.html#operator_plus+ or file:///C:/Program%20Files/MySQL/MySQL%20Server%205.1/HELP/functions.html#operator_minus- operator: date + INTERVAL expr unit date - INTERVAL expr unit but subtracting timestamp from timestamp hoping for a while (interval) is not mentioned. If you want the subtraction to work, make them big integers and use UNIX_TIMESTAMP and FROM_UNIXTIME. See http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: preg_replace in update statement
2012/03/08 16:11 -0500, Hank I have a varchar field in the database, and I want to remove all text between WordA and WordB, including WordA and WordB, leaving all text before WordA and after WordB intact. Possible with just SQL? I know I can write a PHP program to do it, but it's not that important to spend that much time on. I'd like one SQL statement to do it. One statement is hard, but maybe you'll take an SQL function? IF field REGEXP (WordA || '.*' || WordB) THEN SET i = LOCATE(WordA, field), j = LOCATE(REVERSE(WordB), REVERSE(field)); SET answer = LEFT(field, i-1) || RIGHT(field, j-1); ELSESET answer = field; END IF or something like that (ANSI mode). It would be much nicer to get a location pair out of REGEXP (RLIKE) than only a yes/no (1/0). In this case, the most useful _one_ number from REGEXP would be the length of the match. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Multi select Query help...
2012/03/01 19:56 -0800, Don Wieland I do not get the same results. Am I missing something? Hopefully something simple ;-) O, you are. You do not want GROUP_CONCAT in the subquery. It gives you the comma-separated string whereto you referred, which, as far as the IN goes, is only one string for comparing for equality. You want the IDs separate, not joined into one string. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: How do I use a dynamic filename for an into outfile statement
2012/02/29 11:43 -0500, Ed Patterson The select concat() works from the command line I can manually add the file name but would like to automate the process Lastly, if it makes a difference, I don't use any graphical tools for DB stuff. Look up the command! http://dev.mysql.com/doc/refman/5.5/en/select.html Aside from PREPARE, the OUTFILE-name does not automate. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Getting data from 2 tables if records have same date!
; 2012/02/29 15:29 -0600, Johnny Withers Sounds like you need to LEFT JOIN: SELECT * FROM CUSTOMERS LEFT JOIN CALLS ON CUSTOMERS.PHONE = CALLS.PHONE AND CALLS.DATE = 02/28/12 WHERE CUSTOMERS.DATE = 02/28/12 But that would only get customers created on 2/28 AND having a call on 2/28 OR not call at all on 2/28. This would give you customers created on 2/28 with no calls AND customers created on 2/28 with a call on 2/28: SELECT * FROM CUSTOMERS LEFT JOIN CALLS ON CUSTOMERS.PHONE = CALLS.PHONE WHERE CUSTOMERS.DATE = 02/28/12 AND (CALLS.PHONE IS NULL OR CALLS.DATE = 02/28/12) Exactly; but I believe that this is the right thing: SELECT * FROM CUSTOMERS LEFT JOIN CALLS USING(PHONE) WHERE CUSTOMERS.DATE = 02/28/12 OR CALLS.DATE = 02/28/12 If you have a hit --PHONE found in both tables--, you will get a record if either date matches, and I believe that you wanted that. If it is a miss --there is no CALLS-record for the PHONE-- CALLS.DATE will be NULL and not equal and only CUSTOMERS.DATE will match a date. And if this works, surely it is clear where to put BETWEEN. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: how to changing default '-' character in Datetime in MySQL?
2012/02/05 21:56 -0800, Peter Brawley On 2/5/2012 9:21 PM, Michael Dykman wrote: You are right. It seems to have fallen into disuse since I used it last. AFAIK it has never been used. One use occurred to me, and I was thinking of this, to set date_format in the configuration file, and pass it to function date_format: mysql select date_format(now(),@@date_format), @@date_format; +--+---+ | date_format(now(),@@date_format) | @@date_format | +--+---+ | 2012/2/6 | %Y/%c/%e | +--+---+ 1 row in set (0.00 sec) I have so set it in my configuration file, but have made no use of it other than this test. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Delete from another table on update.
2012/02/06 11:33 -0400, Paul Halliday Is it possible to wrap a DELETE statement in an ON DUPLICATE KEY UPDATE? Something like: ON DUPLICATE KEY UPDATE host=b1 (DELETE FROM another_table WHERE host=b1) ? No; see http://dev.mysql.com/doc/refman/5.5/en/insert.html Such things are done in triggers, also if you only on duplicate key want the deletion. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: how to changing default '-' character in Datetime in MySQL?
2012/02/04 19:13 -0800, Rajeev Prasad MySQL datetime field type keeps data as: -MM-DD HH:mm:SS is there a way to store this data as: /MM/DD HH:mm:SS or going much further (optionally) can we store as: MM/DD/ HH:mm:SS ? if not then whats the best way to reformat the cell value from -MM-DD to MM/DD/YYY That is MySQL s string format, and that is what you get. That said, there is a function DATE_FORMAT (look it up) that lets one change its look. Its format argument is quite ugly. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: MySQL-kind CSV; Converting Microsoft SQL database to MySQL
; 2012/01/31 10:52 +0100, Johan De Meersman Not *entirely* accurate: MySQL does include a CSV engine that you can use in the same way you would use InnoDB or any other engine. If you create a table a with engine=CSV and then go look at the data dictionary, you'll find the files a.frm and a.CSV there. You can then swap a.CSV out for a properly formatted CSV file with the same structure as the table you've created; and if a select on the table then yields the data you were expecting, you can just do alter table a engine=InnoDB to magically transform it into a proper table. The other way around works, too, btw - you can easily do create table export engine=CSV as select * from table and then copy the resulting export.CSV. Given that that functionality is there, though, it baffles me why a proper CSV import/export mechanism has never been implemented. I have considered playing with engine=CSV, but never have. In the version that I have, it says, in effect, that NULL is not supported: in new tables only NOT-NULL fields are allowed. The escape NULL (or \N) is not a good extension. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Converting Microsoft SQL database to MySQL
2012/01/30 15:06 +0800, James I am involved in a project to migrate our entire database from Microsoft SQL to MySQL. I would appreciate the help if anyone could share what tools will you recommend of converting SQL database to MySQL. If it is SQL server, maybe dumping in character form (CSV) will work, noting that MySQL does not really support CSV: one can set all switches in LOAD DATA to real CSV except for NULL. If there are NULLs in your data you have to ensure that the word NULL, not quoted, appears between separators. (The natural convention that nothing between separators is NULL and empty string is a pair of quote-marks is not used.) On my machine there are the command-line programs bcp and sqlcmd. There are also the table and view descriptions to be dumped; note that SQL Server quotes identifiers with square brackets, but MySQL in ANSI mode with double quotes, outside ANSI mode with the character that a follows (`). There are also type-differences: for one, MySQL has no type smalldatetime. In SQL Server type-names, too, are identifiers: [varchar], [smallint], [bit], [smalldatetime], Setting QUOTED_IDENTIFIER OFF would help. To SQL SERVER NOT NULL is a named constraint, but to MySQL not. It goes on. I do not know ODBC; maybe that is more helpful. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Script for cleaning data on a regular basis
20120126 10:34 AM +0200, a bv Database contains tables (structures?) which gets montly data , and these tables are named as name1_name2_ yearmonth . I only want to have the whole database system for last 2 years, and automaticly clean the data which became more than 2 years old , so i need a script for this (shell, php etc) . Can you please help me for this script ? and also which mysql command must be used drop, trunk? You can, if you are careful, write an SQL procedure for dropping the appropriate tables. Since MySQL has no table variables, if you do this you will need PREPARE. Therefore, it well may be easier to do it all in PHP, since therewith you can construct statements. The statement SHOW TABLES is also a reference to table INFORMATION_SCHEMA.TABLES; maybe you can use SHOW TABLES for a SELECT statement in PHP; if not, try INFORMATION_SCHEMA.TABLES for their names. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
the rest of Latin1 in a regular expression
This is a decent approximation to matching one word in an email-address, separated by dots, and, of course, the commercial multiplication sign @ [!#-'*+/-9=?A-Z^-~-]+ but I want to add the Latin1 graphics to it. Is this best done by concatenation, '[-!#-''*+/-9=?A-Z^-~' || x'A0' || '-' || x'FF' || ']+' or is there something neater? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: UPDATE triggers with REPLACE statements
; 20111219 03:42 PM -0800, Jim McNeely Not if you are using innoDB tables. For these, you use INSERT and UPDATE triggers. Jim McNeely On Dec 19, 2011, at 11:58 AM, Halász Sándor wrote: 2011/12/19 11:30 -0800, Jim McNeely In the MySQL documentation, we find this tantalizing statement: It is possible that in the case of a duplicate-key error, a storage engine may perform the REPLACE as an update rather than a delete plus insert, but the semantics are the same. There are no user-visible effects other than a possible difference in how the storage engine increments Handler_xxx status variables. Well, try it--but beware of these statements: the semantics are the same. There are no user-visible effects other than a possible difference in how the storage engine increments Handler_xxx status variables. If accurate, the triggers are those of DELETE INSERT, not UPDATE. In my experimenting I find (version 5.5.8) that DELETE INSERT are triggered, not UPDATE. The statement There are no user-visible effects... is simply wrong. The SQL-programmer has to be ready for either outcome. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: case insensitivity
2012/01/16 19:57 -0800, Haluk Karamete MSSQL can be configured to work in either mode. Isn't such a thing for mySQL? For most of the time, I would not care about case-sensitivity. So I won't mind configuring the entire mysql operation to be case insensitive once and for all? In MySQL some operations, say, LIKE, have case-folding, and others (REPLACE) do not. Text-comparison also has case-folding. But there is also the attribute BINARY: if a string is associated with that, there is no case-folding, whatever the operation. (One uses the function CONVERT to call a string binary, if that is needed.) There is no mode associated with an instance of MySQL daemon, only with operations and character types. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: help! mysqld cannot start!
2012/01/16 19:37 +0800, mantianyu but at last step I start the service by run sudo bin/mysqld_safe --user=mysql I got following error message cifer@Pig:/usr/local/mysql$ 120116 19:15:28 mysqld_safe Logging to '/var/log/mysql/error.log'. Your means of starting does not show where the MySQL own files are. The flag -b (--basedir) is used for that. It works also to start mysqld in the directory where the default my.cnf was left. If that has everything that MySQL needs, it works also to start with --defaults-file=/my.cnf--the four dots stand for the rest of the full pathname. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: why does basic MySQLdump on db table fail with Permission denied, or else it creates a zero KB file
; 2012/01/03 11:52 -0500, Govinda ...which strikes me as odd (again, showing how new I am to driving from the CL), because I do NOT see any entry like this: /usr/local/mysql/bin/mysqldump Is mysql a symbolic link? ..which I just (earlier this morning) changed to this: export PATH=/usr/local/bin:/usr/local/sbin:/usr/local/mysql/bin:/usr/local/mysql/bin/mysqldump:$PATH You are missing a point, that the proper thing for PATH is directory (or effective directory), not runfile in directory. This, therefore, is more right: PATH=/usr/local/bin:/usr/local/sbin:/usr/local/mysql/bin:/usr/local/mysql/bin:$PATH After this, surely, you can run mysqldump or mysql or mysqlbinlog or -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: ERROR 1307 (HY000) at line 31: Failed to CREATE PROCEDURE
2012/01/02 15:47 +0530, Adarsh Sharma ERROR 1307 (HY000) at line 31: Failed to CREATE PROCEDURE line 31 of what? Likly it would be helpful to show it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: (off topic) why PATH
2011/12/29 19:35 +0100, Reindl Harald for the hadnful things on my linux-machines where such non-default locations are existing i usually set symlinks unter /usr/local/bin/ to the binarys, so they are seperated and from the user point of view in the PATh and all wroks fine The weakness of PATH: it is all right in the original Unix case, many, many little programs in few directories. Quite a few programs come with MySQL; therefore, it pays to put the MySQL directory in PATH--but Lynx, and many text-processors, comes with one program and many supporting files. In these cases a mechanism other than PATH, something like VMS or C-shell or Korn-shell alias, implemented at the depth of PATH, would be much better. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: errors running WHILE loop
2011/12/27 18:13 +, Mark Haney So, what the heck is going on here? I'm at the end of my wits. I suspect that you have to make the block delimiter something other than semicolon, and set the block in a routine or trigger. Semicolon terminates SQL routine statements, after PL1. If the block delimiter also is semicolon, parsing the block begins too early. In many examples of routine declaration it is changed to //, or another string not found in the block or routine. I often use question mark, but, of course, that is useless in case of PREPARE. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: UPDATE triggers with REPLACE statements
2011/12/19 11:30 -0800, Jim McNeely In the MySQL documentation, we find this tantalizing statement: It is possible that in the case of a duplicate-key error, a storage engine may perform the REPLACE as an update rather than a delete plus insert, but the semantics are the same. There are no user-visible effects other than a possible difference in how the storage engine increments Handler_xxx status variables. Well, try it--but beware of these statements: the semantics are the same. There are no user-visible effects other than a possible difference in how the storage engine increments Handler_xxx status variables. If accurate, the triggers are those of DELETE INSERT, not UPDATE. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: UPDATE triggers with REPLACE statements
2011/12/19 13:55 -0800, Jim McNeely Anyway, I just thought I would share. BTW I experimented, and innoDB does updates and fires off update triggers for REPLACE statements, but MyISAM does delete/inserts. Thank you. Which version? Well, then the documentation is wrong: it is indeed visible to the user which happens. And what would innoDB do in this case, UNIQUE index X1 and UNIQUE index X2, with a VARCHAR f, too, X1 X2 f 1 2 a 2 3 b and one REPLACEs with (2, 2, 'b')? (Earlier in the section such a situation is referred to.) Delete one and UPDATE the other? User beware! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: UPDATE triggers with REPLACE statements
2011/12/16 16:00 -0800, Jim McNeely I have a set of tables that get populated and changed a lot from lots of REPLACE statements. Now, I need an ON UPDATE trigger, but of course the trigger never gets triggered because REPLACES are all deletes and inserts. The trigger is going to populate another table as a queue for a system to do something whenever a particular field changes. SO, does anyone have some slick idea how to handle this little problem? I have an idea but I have a feeling there is something better out there. Exactly what do you mean? You want the DELETE-trigger to talk to the INSERT-trigger for to learn whether the field changed? Try user-defined variable. I do not like that, but the only other that comes to mind is no better, a tiny table that exists only for the DELETE-trigger to save the field value, and the INSERT-trigger to look at it. Here is a trigger that inserts into a table PaimentLog after there is inserting into a table GiftAndShare: CREATE TRIGGER newGiftAft AFTER INSERT ON GiftAndShare FOR EACH ROW BEGIN ... INSERT INTO PaimentLog SELECT NEW.MemberID, Surname, GivenName, NEW.Why, NEW.Amount, NEW.Date, NEW.Method, NEW.byWhom FROM Nam WHERE MemberID = NEW.MemberID AND Rank = 0; END -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: SQL DATA
2011/12/13 16:10 -0500, Shawn Green (MySQL) Many of you are missing the big picture. This flag (along with most of the others you can use to describe a stored procedure) are meant to indicate to the replication system whether it needs to log the CALL to this procedure and in which format. Statements that only collect data (and don't even write to temporary tables) do not need to be written into the binary log. Any stored procedures that change a table, even if it's a temporary table, need to be marked as MODIFIES_SQL_DATA. This answers my question! that the flags are for the replicator. I had the idea that they were for the optimizer. In spite of my sharp comments, I know that in general it is impossible to determine whether any program does anything, a reason for the compiler not to enforce the flags on any procedure. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Annoying type-change
I saw that in some complex query of mine where I expected date I saw varchar(29). The relevant part is that the first argument to ADDDATE is the LEAST of two DATEs. Unhappilie, all the type changes are clearly documented--the most annoying is LEAST : In all other cases, the arguments are compared as binary strings. Timestamp types, too, are 'other cases'. It would be much better if LEAST (and GREATEST) returned values of the type of their arguments, if all types were alike, aside from length and width (number of digits, of fractional digits, in timestamp types the degree below day but of the same origin), returning a value of the longest and widest, as the boundary allows. It is also counterintuitive that ADDDATE returns anything but a value of some timestamp type. Yuck. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Writing Arabic Text to a DataBase with Perl
What happens if your Perl script generates (UTF8) SQL output instead of sending the Perl Module it? Does it look right? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: SQL DATA
2011/12/09 20:37 -0500, Singer X.J. Wang When the procedure is executed, each query in the procedure is obviously run through the query optimizer. But the flags are symbolic only for humans. Obviously? As I wrote, someone said that the optimizer does _not_ look into procedures. Symbolic is not right: do you mean meaningful? If meaningful, that is to say that the flags are completely useless. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: SQL DATA
; 2011/12/08 10:21 -0500, Singer X.J. Wang But again one of the flags are binding in any way. There's nothing preventing me from declaring my procedure * READS SQL DATA indicates that the routine contains statements that read data (for example, http://dev.mysql.com/doc/refman/5.0/en/select.htmlSELECT), but not statements that write data. And then have it write data.. Which is to say, it is not good for much, not for the optimizer, either? Someone else suggested that the optimizer does not look inside procedures, at most at the flags. Well, which is it? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: SQL DATA
2011/12/05 14:26 -0500, Singer X.J. Wang Note that all the flags are non binding.. YMMV... Yes, but what does it _mean_? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: the best way compare String
; 2011/12/04 20:25 +0100, Rafael Valenzuela
In one query, I am comparing a string with a 'like=%patron%', but is very
slow and expensive .What is the best way to compare a string? like or
regular expression?
The data base is too big table 1TB, any idea?
Any decent implementation of like '%patron%' is in the gross linear, and
also, say, LOCATE('patron', ) in a setting where only 0 or not 0 is tested for.
If all your LIKE-searches look like this one, a string to be matched from a
value by putting it between '%'s, maybe by using LOCATE you can save
time--unless the MySQL developers were clever enough to make a special case of
this, and used the same algorithm for both. (One can always use the Boyer-Moore
algorithm for LOCATE.)
In any case, if an in-the-gross-linear algorithm is used and that is too slow,
indexing is the only help, and, as Shiva said, you have to know your problem.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
SQL DATA
A procedure MODIFIES SQL DATA if all the modifying involves a temporary table, or only READS SQL DATA? Does it matter whether the temporary table is meant to outlast the procedure-call? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Query query
Well, of that which you showed you _need_ only this, not really different from that which you wrote: SELECT COUNT(lib.Dewey) AS Have, ddn.Dewey AS DDN, ddn.Classification FROM s_library_dewey ddn LEFT OUTER JOIN s_library lib ON ddn.Dewey = FLOOR(lib.Dewey) GROUP BY ddn.Dewey As for FLOOR in an ON-clause, surely the general-builtin-function overhead completely overwhelms the operation s cost. Maybe index on Dewey would help. (Which Dewey? with computer under math, c, or with computer under 000? Where can one get a 1000-element list for computer?) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: help needed restoring crashed mysql
2011/11/29 23:19 +0100, Reindl Harald MY only luck is that i recognized this years ago after PLAYING with innodb and so i started with innodb_file_per_table=1 from the begin with the first production database And are then the table-files in the directories with frm, or in the directory where ibdata1 is? If the latter, one problem is exchanged for another. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Corrupted TRG file
The following always is the outcome of the command show create trigger: mysql show create trigger memberchange; ERROR 1602 (HY000): Corrupted TRG file for table `membership`.`address` mysqld x86 5.5.8 under Vista The trigger works, there is no problem with show triggers, and this error appears right after the trigger file was deleted (drop trigger until then) and anew made by installing a trigger--as if the code that handles show create trigger expects a format that no other does. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: innodb space free decreasing by more then the amount of data we're adding
; 2011/11/22 12:44 -0800, Sean Sidelko We just moved a large amount of data off one of our db servers to another one (75gb of data). One thing I've noticed is that over the last couple days the free innodb space has been decreasing by over 2.5 gb a day while we've only been adding 400 mb of data a day to the db server. I'm at a loss as to why the innodb free space would be decreasing like it has been. This is on a MySql 5.0 install. Index? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Chinese characters not displaying in Workbench latest version
; 2011/11/20 20:27 +, Tompkins Neil Does anyone know why Chinese characters are not displaying correctly in a replicated database on a slave machine ? I'm just getting square boxes. What displays them? it sounds to me as if the display lacks something, not so much MySQL. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Cleaning up old files
2011/11/14 18:01 +, Rob Tanner So what are those files and can I delete all but the most recent? And if you look up replication, as others already have said, you will find that the command reset master will do just that--if, as Harald hinted, you really _want_ replication, or at least the output needed for it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Cleaning up old files
2011/11/14 18:01 +, Rob Tanner So what are those files and can I delete all but the most recent? And if you look up replication, as others already have said, you will find that the command purge binary logs to 'mysql-bin.' will do just that--if, as Harald hinted, you really _want_ replication, or at least the output needed for it. I wrote reset master by mistake: that clears out _all_ the binary logs. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: can I get around error 1451 in innodb
2011/11/06 13:56 +1000, Edward avanti #1451 - Cannot delete or update a parent row: a foreign key constraint fails (`vmail`.`domain_admins`, CONSTRAINT `domain_admins_ibfk_1` FOREIGN KEY (`domain_id`) REFERENCES `virtual_domains` (`id`) ON DELETE CASCADE) In table domain_admins there is the constraint that field domain_id refers to table virtual_domains s field id . Therefore, to delete a record from virtual_domains it is needful first to delete from domain_admins the record with domain_id equal to id . After that it will let one delete the record from virtual_domains . It will not only not delete but also not change id if in table domain_admins there is a record with domain_id equal to id . -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
string comparison for timestamp!
I was using this query and getting queer results: SELECT MemberID, Surname, Houshold, Houmuch, CASE WHEN Houmuch (SELECT sharePrice FROM Stock) THEN ADDDATE(ereStart, (Houmuch * (SELECT hwyl FROM Stock)) / (SELECT regularPayment FROM Stock)) ELSE TIMESTAMPADD(YEAR, 2, CURDATE()) END AS goodThrough, ... FROM EachName NATURAL JOIN (SELECT MemberID, SUM(Amount) AS Houmuch FROM GiftandShare WHERE Why = 2 AND Date = '2011/11/13' GROUP BY MemberID) AS Paid NATURAL JOIN Address HAVING goodThrough = '2011/11/13' ORDER BY Surname I looked at the date-comparisons and wondered how they were done: when I changed the slashes to hyphens it properly worked! I think it would be better if MySQL had real timestamp-constants--and then the separator would matter: colon (and semicolon?) for the sexagesimal part and anything else for the day part. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: ERROR 1250 (42000): rejected view
; 2011/10/20 10:21 -0400, Shawn Green (MySQL) On 10/19/2011 17:50, wrote: I made this query a view, called MEMBERP, no problem: ...snip... Only GivenName is derived from GROUP_CONCAT and is also a lesser field for ordering by. Why is that a problem? Perhaps it is the USING clause that is messing you up. The USING() clause needs to pick one source for MemberID and you appear to have two. Try converting to a more explicit ON clause instead. However (ON, USING, NATURAL) I write joining the three tables on field MemberID, the outcome is the same, that error message. 2011/10/20 10:21 -0400, Shawn Green (MySQL) Also you are mixing LEFT and RIGHT joins in the same query. While technically not wrong, it's also not 'good form'. I suggest you alter the sequence of your table definitions to use either all LEFT or all RIGHT like this: FROM Address LEFT JOIN Nam ON ... LEFT JOIN Paid ON ... That seems strange to me. Here, for clarity to my original intent, I write it with NATURAL: FROM Nam NATURAL RIGHT JOIN Address NATURAL LEFT JOIN Paid . To me it looks good, because it emphasizes Address, wherin MemberID originates (It is also in the order that I wish the fields to appear, although I know MySQL handles almost everything LEFT). There is also no problem with associativity, because it comes out the same whichever one is first taken. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Within-group aggregate query help please - customers and latest subscription row
2011/10/24 16:31 -0700, Daevid Vincent WHERE cs.customer_id = 7 GROUP BY customer_id Well, the latter line is now redundant. How will you make the '7' into a parameter? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How to select the id of 2+ records for given user?
; 2011/10/19 17:06 -0500, Basil Daoust For me given the sample data the following worked. The inner select says find all first messages, the outer says give me all messages that are thus not first messages. select * from table1 where messageID NOT IN ( select messageID from table1 group by userID ) Well done--but Although, it seems, it is everyone s experience that the desired order is the order that MySQL yields, all guarantee of that is explicitly deny'd (look up 'GROUP BY'). It is better to be safe and to use MIN: select * from table1 where messageID NOT IN ( select MIN(messageID) from table1 group by userID ) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: What is wrong with this outer join?
; 2011/10/19 17:00 +0200, Dotan Cohen mysql select * from beers b outer join colours c on (b.colour = c.ID); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'outer join colours c on (b.colour = c.ID)' at line 1 MySQL does not have real outer join, only left right join. One means of getting outer join is the union of left join and right join. (The words INNER and OUTER have no real meaning in MySQL.) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
ERROR 1250 (42000): rejected view
I made this query a view, called MEMBERP, no problem: SELECT MemberID, ereStart, DateModified, MembershipExpires, MemberSince, Category, Boardster, GROUP_CONCAT(DISTINCT Salutation ORDER BY Rank) AS Salutation, GROUP_CONCAT(DISTINCT GivenName ORDER BY Rank) AS GivenName, GROUP_CONCAT(DISTINCT Surname ORDER BY Rank) AS Surname, Street, City, State, Zip, HomePhone, Comments, GROUP_CONCAT(DISTINCT WorkPhone ORDER BY Rank) AS WorkPhone, GROUP_CONCAT(DISTINCT CellPhone ORDER BY Rank) AS CellPhone, GROUP_CONCAT(DISTINCT Email ORDER BY Rank) AS eMail, MAX(Volunteer) AS Volunteer, MAX(ReceivesFlyer) AS ReceivesFlyer, Houmuch, Wherat FROM Nam RIGHT JOIN Address USING (MemberID) LEFT JOIN Paid USING (MemberID) GROUP BY MemberID ORDER BY Surname, GivenName There are tables Nam and Address, and Paid is a view. But when I try to use it for a table, there is trouble: mysql select * from memberp; ERROR 1250 (42000): Table 'nam' from one of the SELECTs cannot be used in field list If I leave this, GROUP_CONCAT(DISTINCT GivenName ORDER BY Rank) AS, out, there is no trouble. If I leave out any of the other like phrases, there is yet trouble. If I leave the ORDER BY ... out, there is no trouble. Only GivenName is derived from GROUP_CONCAT and is also a lesser field for ordering by. Why is that a problem? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Additional Software to Download and Install
2011/10/14 11:12 -0700, AndrewMcHorney I just downloaded the MySql server software. I am now looking for software that is gui based and will allow me to easily define a database, create tables and to do updates of records within the tables. It would be fantastic if the software had report generating capabilities and also would allow me to create and execute sql commands and to write stored procedures to process the data. The tables are going to be fairly simple. Navicat is good for the database work, but not for pretty reports. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Null Output Problem
Generally when one has this problem one joins this query with something from which one can get the whole list, something like this: SELECT identifier, IFNULL(c, 0) AS Good, (query with COUNT) AS P RIGHT JOIN table-of-identifiers ON P.identifier = table-of-identifiers.identifier The c is the name given COUNT(*) in the query with COUNT(*). This yields at least one row for every one in table-of-identifiers, whether there is a match in query with COUNT or not; if not, c is NULL, and with IFNULL that NULL is made 0. You write test-taker, but for a field that could be the foregoing identifier your query contains only subject_identifier, which does not look like a test-taker. 2011/10/11 12:26 -0600, Jon Forsyth I have a problem with the following query: SELECT subject_identifier, COUNT(*) FROM asr_sentence_score WHERE total_words = correct_words GROUP BY subject_identifier; OutPut: ++--+ | subject_identifier | COUNT(*) | ++--+ | 222 |2 | | 111 |2 | | 333 |1 | | 444 | 11 | | 888 |6 | | 666 | 25 | | 777 |2 | | 555 | 20 | | 999 |4 | | 000 |3 | ++--+ 10 rows in set (0.00 sec) The asr_sentence_score table is a list of test results where each row is a single item(sentence) on the test. The subject_identifier is unique to the test taker, and is repeated for each test item. I was using this query to compute a count of how many items each test taker scored perfectly (total_words = correct_words), but I realized that this excludes a test taker who did not score perfect for any item. I want to output a '0' for those that did not score any item perfectly. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Vista crashes
I find that, when under Vista the MySQL daemon has been shut down, by giving the command start mysqld -b%CD% in the root directory where MySQL 5.5.8 (the version running on this computer) has been stored from an instance of command prompt with administrator authority issued by a user that lacks it (like an ordinary user s doing bare su in Unix) I make Vista crash with the blue-screen message process or task critical to system operation has been terminated or exited. The start is needed, also the administrator authority and the user that ordinarily lacks it. Of course, there is a Vista-bug here, but, also, surely a MySQL-bug. Where is there a list of known bugs? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Weird UPDATE Problem
2011/10/10 09:19 -0400, Brandon Phelps If this column(s) is/are a character type now, then you would need to have the values in quotes. Note that because of implicit conversion if they had numberic values no error would be reported, but maybe the equality would not be exact. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
optimizer in function
Does the optimizer look into function called from query? In my queries the expression (SELECT hwyl FROM Stock) / (SELECT regularPayment FROM Stock), where Stock is a one-record table, often is repeated. The optimizer sees that, and makes the ratio a constant, and I can afford to be clear. If that expression were within a function called from the same spot, would the optimizer look into the function and see the same effectiv constant? or is it better to make it an argument to the function? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: NULL-safe (in)equality =; overloaded NULL
2011/10/02 15:01 +0200, Jigal van Hemert You are not using NULL as the original concept of it was. NULL means that the value is undefined or unknown. That is quite true, especially in a table. But, almost from the beginning, NULL was overloaded: set @m = (select sins from emailinglist where email = 'ha...@gmail.com'); This is allowed if the query yields at most one row. If it yields no row @m is made NULL--and if field sins may be NULL (not in my case), the outcome is indeterminate. With the aggregate functions MAX and MIN there is a subtler problem: over an empty set they yield NULL, even as over a set where every matched value is NULL. It is, maybe, more natural if MAX over an empty set yields bottom, and MIN over an empty set yields top (likewise for BIT_OR and BIT_AND). I once worked on a programming language with symbols for no data, bad result, indeterminate result, One can go too far. But I originally said that the symbol = looks more like inequality than equality. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Quantity of time from difference of two Datetime values?
2011/09/30 20:08 -0700, Jan Steinman Okay, I've reviewed the online man page for date and time functions, and I've played with several likely candidates, and I am still having trouble subtracting two arbitrary Datetimes to get something that is useful. A simple subtraction yields the least useful thing possible: a modulo-100 difference of modulo-60 quantities. Other functions yield the proper answer, but not for a quantity of time that rolls over midnight, etc. You mean timestamps that are less than a full day apart? TIMESTAMPDIFF(SECOND, ...) does not work for you? There is also TO_SECONDS. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
NULL-safe (in)equality =
It is, of course, generally considered more natural to make equality primary, not inequality, but that symbol that MySQL uses for NULL-safe equality, =, looks much more like inequality than equality. Furthermore, I find that in my code I am far oftener interested in NULL-safe _in_equality than equality. If I write IF A = B THEN then if one is NULL and the other not, and the code is such that never are both NULL, well, for my purpose they are not equal: so good. But if I write IF A B THEN often I want it NULL-safe, for if one is NULL and the other not, I want that true--and MySQL s symbol for NULL-safe equality looks just right for inequality. *sigh* -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
The rarest
Which of these statements for picking one of the rarest Boardsters do best like? SELECT Boardster FROM (SELECT count(*) as N FROM memberaddress GROUP BY Boardster order by N limit 1) aS P join (SELECT count(*) AS N, boardster FROM memberaddress GROUP BY Boardster) as Q USING (N) limit 1; SELECT Boardster FROM (SELECT count(*) AS N, boardster FROM memberaddress GROUP BY Boardster) AS P WHERE P.N = (SELECT MIN(N) FROM (SELECT count(*) as N FROM memberaddress GROUP BY Boardster) AS Q) LIMIT 1; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: query efficiency
2011/09/27 05:32 -0700, supr_star Here's a simple query: select name,status,min(dt), max(dt),count(*) from mytable group by name I need an efficient way to get this data, along with the status of the row with the max(dt). 'status' is not in the group by, so I get apparently random statuses. Is there any way to do this without a table join? Or I guess the real question is: What is the most efficient way of accomplishing this? I cannot answer the real question, but there is this: select name,(SELECT status FROM mytable AS x WHERE x.name = mytable.name AND x.dt = max(mytable.dt)) AS status, min(dt), max(dt),count(*) from mytable group by name I will not vouch that this is more efficient than joining. (Surely there are enough of this that this is entitled to a special SQL construct.) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Slower performance with LOCK TABLES
2011/09/23 12:56 +0200, Johan De Meersman What it says, is If you are going to run many operations. You are updating many rows, but you are only doing ONE operation: a single update statement. For what it's worth, the downloading HTML help claims this only for MyISAM tables, because between LOCK TABLES and UNLOCK TABLES there is no key-cache flushing. InnoDB is not mentioned. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Community Support better than Official Support? (was: Can I Develop using Community Edition and Deploy onto Enterprise Edition??)
; 2011/09/22 13:08 -0400, Jerry Schwartz The user forum: it has many experienced users, some beta testers, and (because the product is used world-wide) a response time measured in hours. What it doesn't have is any presence from the company. Is n't that what companies nowadays want? Computers are now often used to get workers and patrons to pay for that which formerly the company paid: forms, instruction books, With online banking the bank pays fewer tellers. The company s only bizness is to sell something, and after the sale vanish if may be. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: select ... into local outfile ... ???
2011/09/03 03:40 +0800, Dennis But it seems that there is no select ... into LOCAL file statement. Any suggestion is appreciated. Indeed: you can use only standard output. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Arrays
2011/09/02 06:09 -0700, javad bakhshi Hi, None of the above. :) .This is just an academic research on a benchmark. I just want to access my database with this function and return 4 Integer values. I separated the numbers with comma to process on it later as an string and split the result using string functions which did work. I was just wondering if its possible with mysql to return a sequence of numbers or not. I think to solve my problem among the ways mentioned before I will create a table with Engine=Memory to speed up things. I don't think any other way is possible. The string takes less time than table (you have not shown your table code) only because of the problem s size. If you had thousands of numbers the string at first would be only slower, but with enough it would not work at all, because you would reach MySQL s string-size limit. A benchmark of repeatedly only 4 integers is not much of one. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Arrays
2011/08/28 09:36 +0200, Johan De Meersman No: when you use /create temporary table/, the table is local to your connection. You can perfectly open a dozen connections and have them all create a temporary table called intermediate_results, they'll not interfere with one another. Temporary tables also get automatically dropped when the connection closes - although it's of course best practice to do it yourself as soon as you don't need it any longer :-) No, I meant that in MySQL there is no means of passing a table-name to a routine, nor returning one; therefore, the only means of handling a table within a routine and without is with a global name; that is, one used but not declared within the routine--and CREATE [TEMPORARY] TABLE never declares a variable local to a routine. As for the returned array, if there were one, that would be stored in a variable that is lost with the connection. I wrote an edit-distance function, and two procedures, that use temporary table for array. The procedures s outcome was in the temporary tables. In routines there are table-use restrictions, more in functions than procedures, and I so wrote my function because of them. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Arrays
2011/08/26 13:58 -0700, javad bakhshi Thanks guys for the help. but my problem seems to stand unsolved. Right, no arrays. Nothing is left but table. I used a temporary table, but note that MySQL also does not let table be returned, or passed in. The table-name will be *sigh* global. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Wrong conversion to timestamp from character string
2011/08/18 18:30 +0300, Marius Feraru Thanks for your time reading my message, but I don't understand what is the result context that you are talking about. Could you please elaborate? Well, if an expression is an argument to, say, CONCAT, the expression s result is character string. An argument to, say, POW is number. But the second and third arguments to IF have the same type, the type of the IF s context, and an expression that is an operand to SELECT may have any type: the result context does not require anything. Now, your expression IF( DATE(d) = some-date, TIME(d), d ) is an operand to SELECT, and no type is required of it--but the types are not the same, wherefore there is at least one conversion, surely that the bare d is made character string. But it seems that instead TIME(d), a character string, is converted to some timestamp, a date. I wrote that I believe this an optimizer error because the least characterward tweak to this is enough to make it that which you seek: either concatenating empty string to TIME(d), thereby overriding any tendency the optimizer has to consider it other than a character string, or by concatenating empty string to the whole expression, thereby making the IF s result context character string, not any type. Consider this: -- 1) create test table containing a single DATETIME column CREATE TABLE dt (d DATETIME); -- 2) insert two test records INSERT INTO dt VALUES (2011-08-07 10:11:12),(1234-05-06 07:08:09); SELECT d, ADDTIME(IF( DATE(d)='2011-08-07', TIME(d), d), '1 1:1:1.02') as x FROM dt; The outcome is really screwy: +-++ | d | x | +-++ | 2011-08-07 10:11:12 | 2035-12-13 02:00:00| | 1234-05-06 07:08:09 | 1234-05-07 08:09:10.02 | +-++ Surely there is a noteworthy bug here. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Arrays
2011/08/24 02:05 -0700, javad bakhshi Hi, I would like to create a function in Mysql that returns an Array of Numbers. I am trying to run a big amount of stream of data on Mysql and I can't afford the time to store the data into a table and retrieve it later. Are there any arrays at all in MySQL? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: hand replication
2011/08/10 08:16 +0200, Johan De Meersman Yes, the MySQL binary log can be read (and thus, re-executed) by the mysqlbinlog utility. Yes, but what is the best means of picking up the changes from the instance where there were changes to the instance that is a copy? Is it best to copy the log and that so use msqlbinlog? Or is it better so to use msqlbinlog that it makes SQL statements that I copy to the other instance? No TCP/IP here, only a flash drive. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: hand replication
Thank you for the advice, and I read about mysqlbinlog 2011/08/15 10:22 -0400, Shawn Green (MySQL) 1) extract the statements from the binary log. 2) get the master to sent you the statements just as if you were a slave. mysqlbinlog will do either - (again, read the manual on how to use the tool) Once you have collected the statements you need the slave to apply (and put them on your flash drive), now it's your turn to replay those statements on the slave. The easiest tool for that will probably be the mysql client (a command-line tool). This is where you become the SLAVE SQL thread. Beyond that, all you really need to keep up with is the binary log position you replicated last (again, pretending to be the SLAVE IO thread). Best of luck! what you are doing is definitely labor intensive. I do not understand 1) or 2). Playing with mysqlbinlog I see that I can get earlier or later bunches of SQL commands if not all of them, but it seems to me that it is not safe to use part of its output unless carefully done, for, say, I use ANSI mode and its output uses the character ere the small a for enclosing variable names, and local sql_mode is changed. I also started mysqld with the log-bin argument something convenient for me to read. I suppose you mean that for finishing the replication I make the mysqlbinlog output mysql s standard input. I find a later starting point by noting the greatest Xid value? In this case it is not very labor-intenstive, for the transaction rate on these databases is most readily noted in inverse weeks, or maybe inverse longer whiles. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Extraneous warning 1292 (Incorrect datetime value)
2011/08/07 18:20 +0300, Marius Feraru Would someone please shed some light on what's wrong with calls like IF( DATE(d) = some-date, TIME(d), d ) on DATETIME columns? Thank you. I run some tests on mysql 5.0, 5.1 and 5.5: got the same wierd results everywhere, so I guess I'm missing something, but what is it? It looks like the engine is trying to use the result of TIME(d) as d in the DATE(d) test, but I can't understand why. Function TIME yields a string, not any timestamp type, but d is of some such type. Unhappily, the HTML help that I downloaded is of no help in this case, when the result context has no type, unless it is supposed that one stops at the first that matches: ExpressionReturn Value expr2 or expr3 returns a stringstring expr2 or expr3 returns a floating-point value floating-point expr2 or expr3 returns an integer integer but it looks as if the string is coerced to d s type. But since if empty string is concatenated to the TIME-result it is as you wish, and the value of TIME(d) = DATE_FORMAT(d, '%T') is 1, it seems to me an optimizer error. As for the warning, your arguments to DATE_FORMAT are backwards, and corrected that yields the outcome that you seek. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Import from Quicken 2004 Mac?
2011/08/11 14:14 -0700, Jan Steinman QB 2010 Mac appears to only export .IIF format, which appears to be a variant of the older .QIF format, and Google didn't turn up really anything for getting IIF/QIF files into MySQL. The best I could find would be importing them into Excel first, then CSV out of Excel into MySQL, which sounds like a lot of bother and not readily scriptable for routine use. I find it hard to believe I'm the first one to ever attempt this! I found this: http://www.qblittlesquare.com/2011/07/import-lists-into-quickbooks-with-iif/ If I can believe it, there is no reason for you to attempt any conversion, because, if, as it seems, HT is the separator, you can directly use LOAD DATA with optional double-quote, ignoring (*sigh*) leading lines. I saw references to convertors for IIF, CSV, and QIF. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Hungarian Notation [Was Re: Too many aliases]
2011/08/08 10:25 -0400, Jerry Schwartz I was a reluctant convert, and still don't use Hungarian notation consistently; but in something like MS Access, where you might want to associate a label with a field, things like lblCompany and txtCompany make a lot of sense. I forgot this--my VB teacher consistently recommended it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Hungarian Notation [Was Re: Too many aliases]
2011/08/08 00:13 -0600, Mike Diehl
Well, I can see this being useful in assembly language, or strongly-typed,
non-OO languages. But I was asking specifically about SQL!
We know from context that customers is a table and it makes no sense at all to
prefix a type to it in order to make the obvious more clear.
I suspect it makes the most sense in weakly typed languages, and, therefore,
quite useless in table names. TAble names are not found in the same context as
field names, and the same name may be used for both table and field in the
table--field names, on the other hand,
In the PL1 (and scripting-language) tradition, although in the table definition
there is fairly narrow description of the type, much implicit conversion is
allowed. It is also in the PL1 tradition that operators yield values of some
vague type ('+' yields number, '||' yields character: no general operator
overloading), but with all the conversion it seldom is clear to the user what a
generated field s exact type is: even which numeric type, even which character
type, with what length. Then there is room for tacking type descriptions onto
names.
2011/08/08 00:13 -0600, Mike Diehl
My personal convention is that table names are plural. Foreign indexes have
the table name as a prefix.
To me a table is like an array, and therefore I make it singular: invoice,
say, is an array of invoices, and invoice [ 5 ] is invoice 5. My plurals are
for counts of things; if invoice is a table, then
select count(*) as invoices from invoice
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
hand replication
I made two copies of one not too big database, wherof the transaction rate is conveniently noted in inverse weeks. I have been using textfile-differencing to copy changes, but would like something more automatic for this by-hand replication, but real automatic replication is out of the question: there will be no such communication channel. I use flash drive. Is there a log from which I can get SQL statements that record changes from some time, copy that to flash drive, and mark the time when I did that, by truncating the log or starting a new one or ...? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Too many aliases
2011/08/04 10:21 -0500, Johnny Withers http://en.wikipedia.org/wiki/Hungarian_notation On Thu, Aug 4, 2011 at 9:41 AM, Mike Diehl mdi...@diehlnet.com wrote Well, while we're on the subject of SQL style, can anyone tell me why I'm always seeing people prefixing the name of a table with something like tbl? Yeah, but why perpetuate such a thing in a language that has type (at least 'table' is distinct)? BCPL had at all no type. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Too many aliases
2011/08/03 12:46 +0200, Rik Wasmus But the main thing is it helps to distinguish tables in joins having the same table more then once (and of course results from subqueries etc.): SELECT first.* FROM tablename first LEFT JOIN tablename second ONfirst.some_id = second.some_id AND first.id != second.id WHERE second.id IS NULL Well, yes, here it is needful. But it seems to me from most of the examples that people here post, that they have the idea that it is the style always to use one-letter aliases, whether it is helpful or not. Now I do not do this, but I often see examples where a field for one purpose has in one table one name, and in another table another, slightly different, name, and then, too, I see alias used, although, in this case, no table name at all is needed. (I like to use the same field name in all tables where it has the same function.) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: results from a total of always amount to a multiple of 3
2011/08/01 09:48 +0700, HaidarPesebe Exactly what you are saying Mr. Sándor. I tried adding a recordamount of debt then becomes a multiple of 4. Well I'm really confused. Are there any who can help? how to call the databaseby entering on the right? What do you want from the joint? That is not clear. It is clear only that it allows too many combinations. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Too many aliases
2011/08/02 12:11 +0530, Adarsh Sharma select p.* from table A p, B q where p.id=q.id or select p.* from table B q , A p where q.id=p.id Why do people constantly change table names for queries, although, as here, it gain them nothing? It often makes for less clarity (for which table is this an alias???). This I would write select A.* from A JOIN B USING (id) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: results from a total of always amount to a multiple of 3
2011/07/29 10:48 +0700, HaidarPesebe but results from a total of always amount to a multiple of 3. should total qty (USD) pay = 8 but the result would be 24. so also for the Debt. What do you mean total of, and total qty (USD) pay? It is better exactly to repeat your code instead of paraphrasing it. In any case, I get multiples of not three but four, id | periodsmester | qtyone | qtytwo +---++--- 1 | 2010-2011-1 | 16 | 44 not surprizing since, with these data, all names and semesters and periods the same, it is the same as the sum of an cross join; see how it looks with all grouping aggregating operations dropped: id | periodsmester | qty | bqty +---+--+- 1 | 2010-2011-1 |2 |7 1 | 2010-2011-1 |2 |4 2 | 2010-2011-1 |1 |7 2 | 2010-2011-1 |1 |4 3 | 2010-2011-1 |3 |7 3 | 2010-2011-1 |3 |4 4 | 2010-2011-1 |2 |7 4 | 2010-2011-1 |2 |4 every pay record is paired with every debt record. If I delete the pay record with id = 4 then I get multiples of three, for the same reason, but with fewer records. Are you sure you got multiples of three with four pay records? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How to Shuffle data
2011/07/13 19:28 +0530, Adarsh Sharma
I think a procedure can do it easily , If I know the proper function for that.
Well, here is a procedure that copies from one table, strips off the leading
slash-separated part and reverses it by dots, and inserts the original, the
reversed, and the trailing into a temporary table:
CREATE PROCEDURE URLR()
MODIFIES SQL DATA
BEGIN
DECLARE F, G INT;
DECLARE R, S, T VARCHAR(199);
DECLARE EOF BOOL DEFAULT 0;
DECLARE X CURSOR FOR SELECT URL FROM URL;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET EOF = 1;
CREATE TEMPORARY TABLE UU
( A VARCHAR(199) COMMENT 'original leading part'
, B VARCHAR(199) COMMENT 'dot-reversed leading part'
, C VARCHAR(199) COMMENT 'trailing part'
);
OPEN X;
FETCH X INTO S;
WHILE EOF = 0 DO
SET T = SUBSTRING_INDEX(S, '/', 1);
SET S = SUBSTRING(S, CHAR_LENGTH(T)+2);
SET G = 1, F = LOCATE('.', T), R = NULL;
WHILE F 0 DO
SET R = CONCAT_WS('.', SUBSTRING(T, G, F-G), R);
SET G = F+1;
SET F = LOCATE('.', T, G);
END WHILE;
SET R = CONCAT_WS('.', SUBSTRING(T, G), R);
INSERT INTO UU VALUE (T, R, S);
FETCH X INTO S;
END WHILE;
CLOSE X;
END
It seemed to me that in your examples you only reversed the domain name around
its dot-separated words, and that this does.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How to Shuffle data
2011/07/13 16:10 +0530, Adarsh Sharma www.facebook.com/home adelaide.yourguide.com/news/local/news/entertainment/cd-review-day-and-age-the-killers/1401702.aspx abclive.in/abclive_business/2393.html abclive.in/abclive_business/assocham_manufacturing_companies.html abclive.in/abclive_business/b-ramalinga-raju-satyam-financial-irregularities.html aktualne.centrum.cz/report/krimi/clanek.phtml?id=635342 aktualne.centrum.cz/report/krimi/clanek.phtml?id=635306 I want to take the output in a tsv file the sites url in the below forms : com.faebook.com/home com.yourguide.adelaide/news/local/news/entertainment/cd-review-day-and-age-the-killers/1401702.aspx in.abclive/abclive_business/2393.html in.abclive/abclive_business/assocham_manufacturing_companies.html in.abclive/abclive_business/b-ramalinga-raju-satyam-financial-irregularities.html cz.centrum.aktualne/report/krimi/clanek.phtml?id=635306 cz.centrum.aktualne/report/krimi/clanek.phtml?id=635342 I need to shuffle the . words . Is there any in built function in mysql to achieve this. Well, this will give you the domain name: SUBSTRING_INDEX(url, '/', 1). After that, you reallie want a version of FIND_IN_SET that takes a number and yields a string, but I have not seen such in MySQL. That leaves you with LOCATE to find each dot, one by one, and SUBSTRING to pick each word out--or nested cases of SUBSTRING_INDEX: SUBSTRING_INDEX(SUBSTRING_INDEX(dom, '.', i), '.', -1) --and SUBSTRING_INDEX is very obliging, the only way, using only it, to determine that one has reached the limit of separators is that SUBSTRING_INDEX(dom, ',', i) = SUBSTRING_INDEX(dom, '.', i+1) . And yes, this is a loop within an SQL procedure or function. Are you, aside from 'com.faebook.com', only reversing the words? That is much easier than randomly picking them for the outcome--and guaranteed to be different from the original, relevant because most domain names are so short that a random permutation of their words is quite likly to be the same as the original: with only three, the probability is one sixth, with only twain, one half. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: dumb question?
2011/07/06 23:56 -0700, XL Cordemans Goede morgen, and thank you for your suggestion. I am actually wondering if the difference between lasso 8.1 and 8.6 is so big that traditional lasso code can not be used when connecting w/ MySQL ? You mentioned ... This can be done in the server configuration, so no alterations are necessary This mode is set in my.cnf (under Windows my.ini), found in one of a variety of standard places, in the variable sql-mode, say sql-mode=ANSI,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION . The mode in question is ANSI. There is always a copy of this file in the directory into which the program MySQL was installed, but that is not the first place where it is sought. Under Linux, say, /etc/my.cnf takes precedence. Quote from help: On Windows, MySQL programs read startup options from the following files, in the specified order (top items are used first). File NamePurpose WINDIR\my.ini, WINDIR\my.cnf Global options C:\my.ini, C:\my.cnf Global options INSTALLDIR\my.ini, INSTALLDIR\my.cnf Global options On Unix, Linux and Mac OS X, MySQL programs read startup options from the following files, in the specified order (top items are used first). File NamePurpose /etc/my.cnf Global options /etc/mysql/my.cnfGlobal options SYSCONFDIR/my.cnfGlobal options $MYSQL_HOME/my.cnf Server-specific options defaults-extra-file The file specified with --defaults-extra-file=path, if any ~/.my.cnfUser-specific options ~ represents the current user's home directory (the value of $HOME). SYSCONFDIR represents the directory specified with the SYSCONFDIR option to CMake when MySQL was built. By default, this is the etc directory located under the compiled-in installation directory. MYSQL_HOME is an environment variable containing the path to the directory in which the server-specific my.cnf file resides. If MYSQL_HOME is not set and you start the server using the mysqld_safe program, mysqld_safe attempts to set MYSQL_HOME as follows: Let BASEDIR and DATADIR represent the path names of the MySQL base directory and data directory, respectively. If there is a my.cnf file in DATADIR but not in BASEDIR, mysqld_safe sets MYSQL_HOME to DATADIR. Otherwise, if MYSQL_HOME is not set and there is no my.cnf file in DATADIR, mysqld_safe sets MYSQL_HOME to BASEDIR. In MySQL 5.5, use of DATADIR as the location for my.cnf is deprecated. Typically, DATADIR is /usr/local/mysql/data for a binary installation or /usr/local/var for a source installation. Note that this is the data directory location that was specified at configuration time, not the one specified with the --datadir option when mysqld starts. Use of --datadir at runtime has no effect on where the server looks for option files, because it looks for them before processing any options. MySQL looks for option files in the order just described and reads any that exist. If an option file that you want to use does not exist, create it with a plain text editor. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
