Re: parallel installations of mysql

[Jan Steinman]

> From: Reindl Harald <h.rei...@thelounge.net>
> 
> Am 03.07.2016 um 04:47 schrieb Martin Mueller:
>> If port 3306 is taken, how is one supposed to know that 3307 is a good 
>> alternative? Why not 3317 or 3703
> 
> seriously?
> 
> when this is your point about bad documentation than you just don't have a 
> point

I have to agree.

If you really insist on this path, it would be good to take a course in UNIX 
system administration. Then you’ll understand ports better.

People who understand UNIX have little trouble with MySQL on the Mac. But 
people who only understand Mac OS using GUI tools have a bit of a distorted 
view of what it takes to install, operate, and support various UNIX tools — 
including MySQL.

I still don’t fully understand your reasoning for insisting on two instances. 
Have you considered simply having two *databases* within the same MySQL process 
space? That should make things MUCH simpler, and faster, too!

With two instances, you’ll be using two heaps, two stacks, and you should 
consider doubling your physical memory, if you’re doing anything intensive. And 
if you aren’t doing anything intensive, there really isn’t any need for two 
identical processes.

Another thing to consider to make the whole exercise simpler is to get another 
Mac. A used Mac Mini is pretty cheap! An older Mac that the university has 
“discarded” will do just fine for light MySQL use. Just put them both on fixed 
10.*.*.* addresses. (After consulting with your IT folk, of course, if they’ll 
also be on the university LAN.) It is MUCH SIMPLER to talk to instances on 
different machines than to keep straight multiple instances on the same machine.

So, rather than ask for advice to implement your proposed solution, why not 
describe your problem more fully? Perhaps there are simpler solutions that 
people could more easily walk you through than helping someone who is not 
comfortable with CLI through having two MySQL processes running. Because in all 
your interactions with the database, the multiple-processes thing is going to 
bite you and confuse you! Especially if you are not comfortable writing simple 
bash scripts.

Jan Steinman
EcoReality Co-op, http://www.EcoReality.org
2152 Fulford-Ganges Road
Salt Spring Island, BC V8K 1Z7 CANADA
+1 250.653.2024




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: does anyone else have problems sending mails to this list ?

[Jan Steinman]

> please refrain from answering when you have no clue how DNS blacklists are 
> working and what about others are talking

Well, excuse me! Someone put on their bossy pants this morning!

A simple “That’s not what’s going on here” would have been much nicer.

J


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: does anyone else have problems sending mails to this list ?

[Jan Steinman]

> From: Reindl Harald 
> Date: 17 March 2016 at 06:27:22 PDT
> 
> Am 17.03.2016 um 14:06 schrieb Lentes, Bernd:
>> i still can't send my question to the ML. Our outgoing Mailer has a new IP 
>> which is not listed
>> (http://mxtoolbox.com/SuperTool.aspx?action=blacklist%3a146.107.103.20=toolpage#),
>>  our domain is not listed
>> (http://mxtoolbox.com/SuperTool.aspx?action=blacklist%3ahelmholtz-muenchen.de=toolpage#),
>>  my E-Mail include neither any link nor an attachment, it's formatted as 
>> plain-text but i still get it back:
> 
> at least not terrible good
> senderscore.com LISTED127.0.4.63

Uhm… the 127 Class A network is the “super local network,” used for processes 
on the same physical machine.

You need to look at other Received: headers than the ones that start with 127.

Jan


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: ENUM() vs TINYINT

[Jan Steinman]

> From: Richard Reina <gatorre...@gmail.com>
> 
> I have a column name quarter which I need to have 5 possible inputs; 1, 2,
> 3, 4, or OT. Because of the OT possibility I am leaning towards ENUM.
> Hence, I am also thus considering ENUM('first', 'second', 'third',
> 'fourth', 'overtime') as the input will primarily be used in written
> descriptions. Is this a wise or unwise way to design a table column?

I think it's a wise way to do things.

I use ENUMs a lot, whenever I'm choosing from a fixed set of a relatively small 
number of items that will not change frequently (or at all).

One other thing to consider is if this particular set of choices will be used 
elsewhere. If so, then consider using a TINYINT index into a different table 
that associates those indices (PK) with strings. Otherwise, I see no good 
reason to use TINYINT.

 You don't have to take insults personally. You can sidestep negative 
energy; you can look for the good in others and utilize that good, as different 
as it may be, to improve your point of view and to enlarge your perspective. -- 
Stephen R. Covey
 Jan Steinman, EcoReality Co-op 


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: moving inno tables

[Jan Steinman]

 From: Martin Mueller martinmuel...@northwestern.edu
 
 I moved the data directory of a MySQL installation from one computer to
 another. This works for MyISAM tables. Unfortunately I inadvertently
 created some INNO tables, and it doesn't seem to work.

Oh dear. Hope you have a backup.

I fought with this for several days. I ended up deleting the bad file, creating 
an identical table in another database, moving THAT file into the old file's 
location, then using DROP TABLE on that file, then restoring from backup.

Until I did that, it wouldn't let me do ANYTHING with the table; couldn't DROP 
it (it didn't exist!) but couldn't create or rename a new one with the same 
name, either.

In general, moving database tables using the file system is A Very Bad Idea. 
Only use MySQL commands to move things around.

 I value kindness to human beings first of all, and kindness to animals. I 
don't respect the law; I have a total irreverence for anything connected with 
society except that which makes the roads safer, the beer stronger, the food 
cheaper, and old men and women warmer in the winter, and happier in the summer. 
-- Brendan Behan
 Jan Steinman, EcoReality Co-op 


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: When to create a new user?

[Jan Steinman]

 From: Richard Reina gatorre...@gmail.com
 
 I am writing a web application... As new users sign up for
 the application should each get their own MySQL username and password or is
 okay to execute their queries with the same (one generic) MySQL username
 and password?

As others have said, it sounds like one SQL user.

Think of MySQL users as roles, rather than users. Segregate these roles 
according to how much trust you have in the user behind the role, and how much 
damage that role could perform.

You may want a separate MySQL user that can only INSERT, for example, but 
without DELETE permission.

 Be a light, not a judge. Be a model, not a critic. Be part of the 
solution, not part of the problem. -- Stephen R. Covey
 Jan Steinman, EcoReality Co-op 


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: create_time

[Jan Steinman]

On 2015-05-07, at 07:17, mysql-digest-h...@lists.mysql.com wrote:

 I have, however, also had Martin's experience where create_time seemed 
 improbable.

Sigh. I have the same thought every evening, when I look over all the things I 
planned to do during the day...

 Compared to sitting in an office making stereotypical remarks about 
mankind, farming is breathtakingly exciting. I grant that there are days when 
you might spend hours in a tractor cab, listening to talk show rant or gabbing 
on your cell phone while the tractor drives itself. But the second you quit 
paying attention to what’s going on, or almost slumber off to sleep in boredom, 
bells and whistles are likely to start clamoring away, indicating a loose belt 
or a broken pin or a plugged up auger or the embarrassing fact that you just 
plowed half way through the township road bordering your field. -- Gene Logsdon
 Jan Steinman, EcoReality Co-op 


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: store search result as new table in memory

[Jan Steinman]

 From: Lucio Chiappetti lu...@lambrate.inaf.it
 
 On Tue, 7 Apr 2015, shawn l.green wrote:
 
 The advantage to using temporary tables is that they can have indexes on 
 them. You can create the indexes when you create the table or you can ALTER 
 the table later to add them.
 
 if they are big, using proper indices is a must to get quick responses.

If your temp tables are read-mostly, create the table with the index. If you're 
doing a lot of writes, consider adding the index after the insertions.

And the proper answer to any question of moderate or greater complexity is, It 
depends... :-)

 I have never let my schooling interfere with my education. -- Mark Twain
 Jan Steinman, EcoReality Co-op 


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Help with REGEXP

[Jan Steinman]

 From: Olivier Nicole olivier.nic...@cs.ait.ac.th
 
 You could look for a tool called The Regex Coach. While it is mainly
 for Windows, it runs very well in vine. I fijd it highly useful to debug
 regexps.

On the Mac, look for RegExRx. It lets you paste in text to work on, build a 
regex, and see the result in real time. I also use one simply called 
Patterns, another real-time regex engine. It does some things RegExRx doesn't 
do, and vice-versa.

 Jan Steinman, EcoReality Co-op 


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

localhost != localhost?

[Jan Steinman]

I have an annoying problem that I've worked around, but I would like to fix it 
the right way.

This is under MacOS X Server 10.6.latest.

I just replaced the ancient (5) version of MySQL that came with that server to 
MariaDB 10.1, via MacPorts. (That was an interesting three-day exercise in 
itself, but it's finally working.) As part of doing that, the location of the 
mysqld socket changed, but I changed that in /etc/my.conf and in /etc/php.ini, 
in three places. I Read The Fine Manual, and I know that to MySQL, localhost 
actually means socket, rather than 127.0.0.1. So I changed /etc/my.cnf and 
/etc/php.ini (three places) to refer to the new socket location. I re-started 
mysqld and apache

However, the moniker localhost isn't behaving as expected in two php 
applications, but working properly in a third.

It works without change in phpMyAdmin, but does not work in MediaWiki nor in a 
home-brew php application.

I realize there are several different MySQL libraries in php. I changed three 
different locations in /etc/php.ini.

I'm working around this by changing localhost to 
localhost:/path/to/mysqld/socket but that seems... distasteful somehow, and I 
don't want to struggle with it again if I install some other MySQL applications.

I also considered symlinking /opt/local/var/run/mariadb-10.1/mysqld.sock to 
wherever the heck Apple had it before, but that also seems like a wrong fix.

Any thoughts on why localhost is behaving this way, and ways to get it to 
behave in the normal manner?

 As I went under the new telegraph-wire, I heard it vibrating like a harp 
high overhead. It was as the sound of a far-off glorious life, a supernal life, 
which came down to us, and vibrated the lattice-work of this life of ours. -- 
Henry David Thoreau
 Jan Steinman, EcoReality Co-op 


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: mysql float data type

[Jan Steinman]

 From: Lucio Chiappetti lu...@lambrate.inaf.it
 
 never used DECIMAL nor intend to

Why would you blow off an important feature of any system?

DECIMAL performs infinite precision math, and should be used in ALL 
situations where you don't want rounding errors. It should ALWAYS be your first 
choice for quantities of money, for example.

 If a taxpayer thinks he can cheat safely, he probably will. -- Diogenes
 Jan Steinman, EcoReality Co-op 


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: forum vs email [was: Re: table-for-column]

[Jan Steinman]

 From: Johan De Meersman vegiv...@tuxera.be
 
 I've long wanted to - but never quite got around to - write a forum that 
 integrated a mailing list. Bar mail clients that don't handle list threads 
 well, it really doesn't seem such a difficult task.

There actually seem to be a lot of these around. I'm on several that send me 
email when there are new forum postings.

Here is one:
http://www.mobileread.com

Based on hints in the html comments, they appear to be using VBulletin 
(http://www.vbulletin.com/) a fairly common forum package.

 Jan Steinman, EcoReality Co-op 


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Using INTEGER instead of VARCHAR/DATE - is this a way to faster access?

[Jan Steinman]

 From: (Halász Sándor) h...@tbbs.net
 
 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...

Well, not really! With INTEGERs, your referential integrity is external to 
MySQL, and has to be managed.

 ... and maybe is useful if the set of dates is well determined...

I was not suggesting it for dates. The OP appeared to have a well-defined set 
of strings in a VARCHAR field — THAT is what I suggested ENUMs for!

 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.

Again, the suggestion for ENUM was to replace a constrained set of VARCHARs, 
and yet, you raise a valid point.

What is the update frequency of those VARCHARs? If you're adding them often — 
or if you need to occasionally change their value — I'd use another table with 
a reference.

If they're immutable and new ones are not added often, there's no design cost 
at all to using ENUMs. I'd argue there's a higher maintenance cost to NOT using 
them!

 Jan Steinman, EcoReality Co-op 


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Using INTEGER instead of VARCHAR/DATE - is this a way to faster access?

[Jan Steinman]

 From: Zbigniew zbigniew2...@gmail.com
 
 switching from DATE to more numeric data type may not be necessary...

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.

 using ENUM instead of VARCHAR can be real performance gain, right?

Not just in performance, but it appears to simply be The Right Thing To Do(TM) 
in your case. (Codd Rule #10: referential integrity.)

Consider an insert into a day-of-week column (for instance) that somehow got 
Sudnay in the VARCHAR field instead of Sunday. Using an ENUM eliminates the 
possibility of a typo at a more fundamental level than your programming logic. 
If you do a massive insert with Sudnay in the ENUM field, the entire 
transaction will fail, which is really what you want rather than having to 
track down bad data after the fact, no?

If it REALLY is one value out of a known set, it SHOULD be either an ENUM, or a 
reference to another table. Use the latter technique if you need to add new 
values very often.

 But are you able to estimate, what boost can i notice? 5% - or 50%, or 
 maybe even 500%?

Very hard to say. That's like saying, If I eat well and get enough exercise, 
will I live 5% or 50% or 500% longer? Probably more like 5%, but it may FEEL 
like 500%! :-)

If the value is constrained to a set, having it as an ENUM (or reference to 
another table) will save you grief in many other ways besides boosting 
performance.

 Private enterprise, indeed, became too private. It became privileged 
enterprise, not private enterprise. -- Franklin Delano Roosevelt
 Jan Steinman, EcoReality Co-op 


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Using INTEGER instead of VARCHAR/DATE - is this a way to faster access?

[Jan Steinman]

 From: Zbigniew zbigniew2...@gmail.com
 
 Now to the point: considering, that the second column shall contain
 about 100-200 different labels - so in average many of such labels
 can be repeated one million times (or even more) 

What about using ENUMs?

They have essentially the performance of INTEGERs, but you don't have to 
maintain a string mapping in your programming logic.

 Yes'm, old friends is always best, 'less you can catch a new one that's 
fit to make an old one out of. -- Sarah Jewett
 Jan Steinman, EcoReality Co-op 


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Using INTEGER instead of VARCHAR/DATE - is this a way to faster access?

[Jan Steinman]

 From: Zbigniew zbigniew2...@gmail.com
 
 Now to the point: considering, that the second column shall contain
 about 100-200 different labels - so in average many of such labels
 can be repeated one million times (or even more) 

What about using ENUMs? They have nearly the performance of INTEGERs, but you 
don't have to maintain a string mapping in your programming logic.

 Yes'm, old friends is always best, 'less you can catch a new one that's 
fit to make an old one out of. -- Sarah Jewett
 Jan Steinman, EcoReality Co-op 


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Query with variable number of columns?

[Jan Steinman]

I often use CASE WHEN ... to pivot tables. For example, splitting sales data by 
year:

  SELECT
s_product.name AS `Product`,
SUM(CASE WHEN YEAR(sales.Date)='2007' THEN sales.Total ELSE NULL END) AS 
`2007`,
SUM(CASE WHEN YEAR(sales.Date)='2008' THEN sales.Total ELSE NULL END) AS 
`2008`,
SUM(CASE WHEN YEAR(sales.Date)='2009' THEN sales.Total ELSE NULL END) AS 
`2009`,
...
  WHERE dynamic predicate that only has results in one year

However, this pattern will often result in numerous empty columns -- empties 
that would not be there had the table not been pivoted.

What techniques do *you* use for avoiding this anti-pattern? Am I limited to 
using a separate programming language (PHP, in this case) with a separate 
COUNT(*) query for each possible column, then CASEing the generation of the 
column SQL? Seems awfully ugly!

Thanks in advance for any insight offered!

(And the following came up at random... perhaps I'll just live with a bunch of 
empty columns...)

 In attempting to fix any system, we may damage another that is working 
perfectly well. -- David Holmgren
 Jan Steinman, EcoReality Co-op 


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Importing a database as a data file directory

[Jan Steinman]

 So, this is a Help me before I hurt myself sort of question: Are
 there any caveats and gotchas to consider?

Do you know if the database was shut down properly? Or did Ubunto crash and die 
and your partition become unbootable while the database was in active use?

Either way, you need to make sure MySQL is shut down when you move the files, 
and then repair them after starting.

I've had good experiences moving MyISAM files that way, but bad experience 
moving INNODB files. I suspect the latter are more aggressively cached.

 Mass media must constantly manipulate and deceive us in order to sell 
products... The most fundamental deception perpetrated on the public is that 
consumption of material goods is the source of human happiness. A secondary 
deception is hiding the fact that such consumption leads to major collateral 
damage -- the possible end of human life on the planet. -- Pat Murphy
 Jan Steinman, EcoReality Co-op 


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: ANN: database developer tool Database Workbench 5 now available

[Jan Steinman]

 From: Martijn Tonies (Upscene Productions) m.ton...@upscene.com
 
 Database Workbench now comes in multiple editions with different
 pricing models, there's always a version that suits you!

Unless you don't do Winblows.

Please put Windows dependency clearly in your announcements and on your 
website. I couldn't find it anywhere, until I attempted a download, and got a 
useless .EXE file.

 The record is clear that left to their own devices, the automobile 
manufacturers lack the wisdom or the will or both to switch decisively to the 
production of inexpensive, compact, energy-saving cars appropriate to our 
present needs. -- Donald E. Weeden
 Jan Steinman, EcoReality Co-op 


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

RE: converting numeric to date-time?

[Jan Steinman]

 From: Ed Mierzwa (emierzwa) emier...@micron.com
 
 
 FROM_UNIXTIME(1409304102.153) /*your epoch column here*/

I don't think the OP has a Unix timestamp.

The number looks suspeciously like concatenation of date digits, 140930 at 
the beginning looks like September 30, 2014.

If that's the case, you need to write something that will tear it apart.

 Nobody talks more of free enterprise and competition and of the best man 
winning than the man who inherited his father's store or farm. -- C. Wright 
Mills
 Jan Steinman, EcoReality Co-op 


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: ANN: MicroOLAP Database Designer for MySQL 2.1.2 is available!

[Jan Steinman]

Can you please add to your announcements that this product is Windows-only?

It took some searching on your website to come up with that info, and I dare 
say the majority of MySQL users here are non-Windows-based.

Thank you!

(Now I have to go find and delete that download...)

 A low-energy policy allows for a wide choice of lifestyles and cultures. 
If, on the other hand, a society opts for high energy consumption, its social 
relations must be dictated by technocracy and will be equally degrading whether 
labeled capitalist or socialist. -- Ivan Illich
 Jan Steinman, EcoReality Co-op 


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Another query question...

[Jan Steinman]

 From: h...@tbbs.net
 
 2013/11/04 09:32 -0800, Jan Steinman
 I noticed that I have similar queries that work as expected. The difference 
 appears to be that every query that is broken uses  WITH ROLLUP, and 
 removing this makes them behave as expected.
 
 Is this a known bug? Should I submit it as such?
 
 There is a bug that I about a half year ago reported, 
 http://bugs.mysql.com/bug.php?id=68564, that the NULL one expects with WITH 
 ROLLUP is not always NULL, but is instead the foregoing string in the same 
 field.

Okay, I think I found it:
http://bugs.mysql.com/bug.php?id=47713

I added a comment with a link to a page I set up to show the behaviour on my 
system.
http://www.ecoreality.org/wiki/WITH_ROLLUP_problem

It was submitted in 2009, severity Critical, triaged Serious, and still not 
fixed!

 The raw milk movement provides a real solution to the problem of 
food-borne illness -- because raw milk consumers make sure their milk comes 
from small, pasture-based farms and healthy animals unlikely to harbor 
pathogens and unlikely to contribute to water pollution, and because raw milk 
builds immunity to disease-causing organisms that are simply a natural part of 
the world in which we live. -- Ron Schmid
 Jan Steinman, EcoReality Co-op 


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Pivot Query Help

[Jan Steinman]

I'm using MySQL 5.0.92-log.

I'm trying to do a pivot-sort-of-thing. I've tried a few things from the 
O'Reilly SQL Cookbook, but I seem to be having a mental block.

I have a table of farm harvests. Each harvest has a date, quantity, and foreign 
keys into product and harvester tables:

CREATE TABLE s_product_harvest (
 id int(10) unsigned NOT NULL auto_increment,
 `date` datetime NOT NULL COMMENT 'Date and time of harvest.',
 product int(11) unsigned NOT NULL default '53',
 quantity decimal(10,3) NOT NULL default '1.000',
 units 
enum('kilograms','grams','pounds','ounces','liters','each','cords','bales') 
character set utf8 NOT NULL default 'kilograms',
 who1 int(5) unsigned NOT NULL default '2' COMMENT 'Who harvested this 
resource?',
 notes varchar(255) character set utf8 NOT NULL,
 PRIMARY KEY  (id),
 KEY product (product),
 KEY `date` (`date`),
 KEY who1 (who1),
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='historical list 
of EcoReality farm products harvested';


What I want is a report with years as columns, and rows of:
first harvest (MIN(date)),
last harvest (MAX(date)),
days of harvest (DATEDIFF(MAX(date), MIN(date))) and
total (SUM(quantity)).

first/last  200720082009...
first   Aug 5   Sep 27  Aug 7
lastOct 1   Nov 24  Oct 16
days57  108 82
kg  10.17   16.746.53

This is my first attempt, and it appears to be giving me a row per year, with 
the first sequential harvest date for each year. I can get the data I want by 
making each one a separate column, but that's ugly and I want them in rows.

SELECT
 'first_last' AS `First/Last`,
 CASE WHEN YEAR(harvest.date)='2007' THEN DATE_FORMAT(harvest.date, '%b %e') 
ELSE 0 END AS '2007',
 CASE WHEN YEAR(harvest.date)='2008' THEN DATE_FORMAT(harvest.date, '%b %e') 
ELSE 0 END AS '2008',
 CASE WHEN YEAR(harvest.date)='2009' THEN DATE_FORMAT(harvest.date, '%b %e') 
ELSE 0 END AS '2009',
 CASE WHEN YEAR(harvest.date)='2010' THEN DATE_FORMAT(harvest.date, '%b %e') 
ELSE 0 END AS '2010',
 CASE WHEN YEAR(harvest.date)='2011' THEN DATE_FORMAT(harvest.date, '%b %e') 
ELSE 0 END AS '2011',
 CASE WHEN YEAR(harvest.date)='2012' THEN DATE_FORMAT(harvest.date, '%b %e') 
ELSE 0 END AS '2012',
 CASE WHEN YEAR(harvest.date)='2013' THEN DATE_FORMAT(harvest.date, '%b %e') 
ELSE 0 END AS '2013',
 CASE WHEN YEAR(harvest.date)='2014' THEN DATE_FORMAT(harvest.date, '%b %e') 
ELSE 0 END AS '2014'
FROM
 s_product_harvest harvest
WHERE harvest.product = 4 /* product ID for tomatoes */
GROUP BY YEAR(harvest.date)

Using an example from SQL Cookbook on page 372, I tried to select from a 
subquery, grouped by a rank, but I kept getting one result row, and I can't 
figure out how to get the literal row headers.

Any ideas?

 Compared to those on pasteurized milk, children who received raw certified 
milk had better weight gain and greater protection against rachitis. -- Ron 
Schmid
 Jan Steinman, EcoReality Co-op 

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Another query question...

[Jan Steinman]

The plot thickens...

I noticed that I have similar queries that work as expected. The difference 
appears to be that every query that is broken uses  WITH ROLLUP, and removing 
this makes them behave as expected.

Is this a known bug? Should I submit it as such?

If someone would be so kind as to point me to the bug system, I'll crawl around 
in there to see if it's a known problem.

Jan

Begin forwarded message:

 From: Jan Steinman j...@bytesmiths.com
 Date: 3 November 2013 18:35:47 PST
 
 MySQL 5.0.92-log
 
 I'm trying to form a clickable link using CONCAT, but the link as displayed 
 points to the NEXT row's URL, not the one from the same row as the other data 
 displayed!
 
 Is there something I don't understand about this?
 
 Below is the query. {{{1}}} is replaced by a year, like 2013.
 
 The second column is the problem one. When the table is displayed, the link 
 in the `Product` field points to the NEXT SEQUENTIAL product row! In other 
 words, if you click on the link for garlic, you'll get the page for 
 gherkins.
 
 Live example is at: http://www.EcoReality.org/wiki/2013_harvest
 
 If you hover over the link in the `Product` column, you can clearly see that 
 the page at the link is not the same as that in the `ID` column, but is in 
 fact the same `ID` as the next sequential row. I am so confused.
 
 SELECT
   harvest.product AS ID,
   CONCAT('a href=http://www.EcoReality.org/wiki/Product/', s_product.ID, 
 '', COALESCE(s_product.name, 'TOTAL:'), '/a') AS `Product`,
   FORMAT(sum(harvest.quantity), 3) AS `a 
 href=http://www.EcoReality.org/wiki/Harvest;Harvest/a`,
   harvest.units AS Units,
  CONCAT('$', FORMAT((SUM(harvest.quantity) * prices.price), 2)) AS Value,
   prices.market_type AS `R-W`,
   COUNT(*) AS Harvests,
   DATE(MIN(harvest.date)) AS Begin,
   DATE(MAX(harvest.date)) AS End
 FROM
   s_product_harvest harvest
 INNER JOIN
   s_product on s_product.ID = harvest.product AND
   s_product.units = harvest.units
 LEFT OUTER JOIN
   s_product_market_prices prices ON prices.product_ID = harvest.product AND
   prices.units = harvest.units AND
   year(prices.price_date) = year(harvest.date)
 WHERE
   year(harvest.date) = {{{1}}}
 GROUP BY
   s_product.name WITH ROLLUP
 
  Some days I wonder if it might not be better to culturally engineer 
 humans to enjoy small scale garden farming than to genetically engineer weeds 
 to save large scale agribusiness. -- Gene Logsdon
  Jan Steinman, EcoReality Co-op 
 

 The competition for grain between the wealthy car drivers of the world and 
the poorest people who are trying to survive is a moral issue that we should 
not ignore. The continued increase in biofuels production will result in a 
continued decrease in food availability, which we could someday consider to be 
a crime against humanity. -- Pat Murphy
 Jan Steinman, EcoReality Co-op 


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Another query question...

[Jan Steinman]

MySQL 5.0.92-log

I'm trying to form a clickable link using CONCAT, but the link as displayed 
points to the NEXT row's URL, not the one from the same row as the other data 
displayed!

Is there something I don't understand about this?

Below is the query. {{{1}}} is replaced by a year, like 2013.

The second column is the problem one. When the table is displayed, the link in 
the `Product` field points to the NEXT SEQUENTIAL product row! In other words, 
if you click on the link for garlic, you'll get the page for gherkins.

Live example is at: http://www.EcoReality.org/wiki/2013_harvest

If you hover over the link in the `Product` column, you can clearly see that 
the page at the link is not the same as that in the `ID` column, but is in fact 
the same `ID` as the next sequential row. I am so confused.

SELECT
   harvest.product AS ID,
   CONCAT('a href=http://www.EcoReality.org/wiki/Product/', s_product.ID, 
'', COALESCE(s_product.name, 'TOTAL:'), '/a') AS `Product`,
   FORMAT(sum(harvest.quantity), 3) AS `a 
href=http://www.EcoReality.org/wiki/Harvest;Harvest/a`,
   harvest.units AS Units,
  CONCAT('$', FORMAT((SUM(harvest.quantity) * prices.price), 2)) AS Value,
   prices.market_type AS `R-W`,
   COUNT(*) AS Harvests,
   DATE(MIN(harvest.date)) AS Begin,
   DATE(MAX(harvest.date)) AS End
FROM
   s_product_harvest harvest
 INNER JOIN
   s_product on s_product.ID = harvest.product AND
   s_product.units = harvest.units
 LEFT OUTER JOIN
   s_product_market_prices prices ON prices.product_ID = harvest.product AND
   prices.units = harvest.units AND
   year(prices.price_date) = year(harvest.date)
WHERE
   year(harvest.date) = {{{1}}}
GROUP BY
   s_product.name WITH ROLLUP

 Some days I wonder if it might not be better to culturally engineer humans 
to enjoy small scale garden farming than to genetically engineer weeds to save 
large scale agribusiness. -- Gene Logsdon
 Jan Steinman, EcoReality Co-op 


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: auto-increment more than one field

[Jan Steinman]

 From: rounak jain rounak.m...@gmail.com
 
 I have a table which needs two fields with auto-increment.

I don't know if you have such control over your installation, but you might 
consider the work-alike MariaDB, which I believe supports auto-increment on 
multiple fields, as well as a slew of other features. (Virtual columns are 
nice.)

 Burn down your cities and leave our farms, and your cities will spring up 
again as if by magic; but destroy our farms and the grass will grow in the 
streets of every city in the country. -- William Jennings Bryan
 Jan Steinman, EcoReality Co-op 


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Help restoring database: MacOS Server (Snow Leopard)

[Jan Steinman]

Okay, panic over. I recursively stripped the ACLs and things are working.

Next time I drop a table from phpMyAdmin, I'll carefully read the little thing 
that pops up saying I'm about to drop an entire database... :-( One gets so 
yea, whatever to warning notifiers...)

Thanks to all who sent helpful suggestions!

On 2013-01-09, at 07:33, Jan Steinman wrote:

 I accidentally dropped a crucial database. My only backup is via Apple's Time 
 Machine.
 
 First, I stopped mysqld and copied (via tar) the database in question from 
 the backup. Restarted, but drat -- most of the tables were apparently using 
 innodb's ibdata1 file, as only the MyISAM tables showed up in phpMyAdmin.
 
 I copied the ibdata1, but then mysqld wouldn't start, complaining about no 
 mysql.sock, which was odd, because it was there.
 
 I then copied (via tar) the entire set of all databases, but am still having 
 problems, I think related to Time Machine's ACL lists and extended attributes.
 
 Anyone have experience and wise words on restoring a database from Time 
 Machine?
 
 Thanks!
 
 
  Books are associated with communities of writers, printers, proofreaders 
 and a host of other people with whom the writer interacts... Granted, there 
 are some websites that provide well written and researched articles and 
 information. Unfortunately, many are not. The Internet may be more of a time 
 using machine than TV. And its usage is not always positive. -- Pat Murphy
  Jan Steinman, EcoReality Co-op 


 The day Microsoft makes something that doesn't suck is probably the day 
they start making vacuum cleaners. -- Ernst Jan Plugge
 Jan Steinman, EcoReality Co-op 





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Help restoring database: MacOS Server (Snow Leopard)

[Jan Steinman]

I accidentally dropped a crucial database. My only backup is via Apple's Time 
Machine.

First, I stopped mysqld and copied (via tar) the database in question from the 
backup. Restarted, but drat -- most of the tables were apparently using 
innodb's ibdata1 file, as only the MyISAM tables showed up in phpMyAdmin.

I copied the ibdata1, but then mysqld wouldn't start, complaining about no 
mysql.sock, which was odd, because it was there.

I then copied (via tar) the entire set of all databases, but am still having 
problems, I think related to Time Machine's ACL lists and extended attributes.

Anyone have experience and wise words on restoring a database from Time Machine?

Thanks!


 Books are associated with communities of writers, printers, proofreaders 
and a host of other people with whom the writer interacts... Granted, there are 
some websites that provide well written and researched articles and 
information. Unfortunately, many are not. The Internet may be more of a time 
using machine than TV. And its usage is not always positive. -- Pat Murphy
 Jan Steinman, EcoReality Co-op 





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Dynamic crosstab got me lost.

[Jan Steinman]

 From: Mogens Melander mog...@fumlersoft.dk
 
 So, I got a little further with my problem. I found an article
 on:
 
 http://stackoverflow.com/questions/3122424/dynamic-mysql-query-view-for-crosstab
 
 Describing how to do the dynamic generation of SQL statements. That's
 all good, kind of. The resulting SQL looks like this:
 
 SELECT main.code
 , IF(iconstandardrel.icon = 4,1,0) AS 'internationalt_produkt.eps'
 , IF(iconstandardrel.icon = 3,1,0) AS 'god_vaerdi.eps'
 , IF(iconstandardrel.icon = 2,1,0) AS 'for_miljoeets_skyld.eps'
 , IF(iconstandardrel.icon = 1,1,0) AS 'ergonomisk_produkt.eps'
 , IF(iconstandardrel.icon = 6,1,0) AS 'saml_selv.eps'
 , IF(iconstandardrel.icon = 12,1,0) AS 'brandfarlig.eps'
 FROM iconstandardrel
 JOIN main ON main.code = iconstandardrel.code
 JOIN iconstandard ON iconstandard.id = iconstandardrel.icon
 ORDER BY iconstandardrel.code;
 
 Which produces results like:
 
 101577, 1, 0, 0, 0, 0, 0
 101679, 0, 1, 0, 0, 0, 0
 101679, 1, 0, 0, 0, 0, 0
 101681, 1, 0, 0, 0, 0, 0
 101748, 0, 1, 0, 0, 0, 0
 101748, 1, 0, 0, 0, 0, 0
 
 But I would like to have One line per code:
 
 101577, 1, 0, 0, 0, 0, 0
 101679, 1, 1, 0, 0, 0, 0
 101681, 1, 0, 0, 0, 0, 0
 101748, 1, 1, 0, 0, 0, 0
 
 Is it possible to achieve this in pure SQL ?

I think you need GROUP BY main.code.


 If you worry about what might be, and wonder what might have been, you 
will ignore what is. -- Robert Anthony
 Jan Steinman, EcoReality Co-op 





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

RE: Accessing Column Aliases In Other Columns?

[Jan Steinman]

Thanks, Rick!

I had forgotten all about variables for some strange reason...

The subselect isn't nearly as nice as variable, because I'd have to repeat it 
several times.

On 5 Oct 12, at 20:19, mysql-digest-h...@lists.mysql.com wrote:

 From: Rick James rja...@yahoo-inc.com
 
 One way:
 
 SELECT  @foo := this + that,
more_stuff + @foo
FROM ...;
 
 Another way:
 
 SELECT foo,
   more_stuff + foo
FROM (
   SELECT this + that AS foo
  FROM ... ) x;
 
 -Original Message-
 From: Jan Steinman [mailto:j...@bytesmiths.com]
 
 I would like to refer to calculated columns in other columns. I thought
 a column alias would do it, but apparently they're only for
 aggregation, like GROUP BY.
 
 Other than repeating the entire calculation, what techniques are
 available for accessing such a calculation?
 
 I'm calculating an electric bill, based on meter readings. I need to
 subtract two meter readings, and use that number in several other
 calculations, for example.
 


 I see rejection in my skin, worry in my cancers, bitterness and hate in my 
aching joints. I failed to take care of my mind, and so my body now goes to 
hospital. -- Astrid Alauda
 Jan Steinman, EcoReality Co-op 


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Accessing Column Aliases In Other Columns?

[Jan Steinman]

I would like to refer to calculated columns in other columns. I thought a 
column alias would do it, but apparently they're only for aggregation, like 
GROUP BY.

Other than repeating the entire calculation, what techniques are available for 
accessing such a calculation?

I'm calculating an electric bill, based on meter readings. I need to subtract 
two meter readings, and use that number in several other calculations, for 
example.


 Transformation is not what happens once we've changed; it's coming out 
of the dark and seeing what we've got and relating to it appropriately and 
clearly. If we've got a funnel in our hands but we think it's a bucket, we're 
going to keep losing things we value. If we know we've got a funnel, then we 
stick one finger in the hole and use it like a bucket to get by in the absence 
of one. We're transformed when we fully know who we are — or, even more 
important, who we are not. -- Rick Lewis
 Jan Steinman, EcoReality Co-op 





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Making Myself Crazy

[Jan Steinman]

Thanks for your help, Rick!

Interspersed are some questions and rationales for you to shoot down... :-)

 From: Rick James rja...@yahoo-inc.com
 
 s_product_sales_log has no PRIMARY KEY.  All InnoDB tables 'should' have an 
 explicit PK.

This table really has no identifying information. There could be two identical, 
valid rows, if the same person sold the same amount of the same product to the 
same other person on the same day.

All the foreign keys were indexed. Is there something I don't understand about 
something a PK field does? If an individual record cannot be uniquely 
identified by its information, is there really any need for a primary key?

None the less, I added field ID as an unsigned autoincrement INT and made it 
PK.

 INT(5) is not what you think.  INT is always a 32-bit, 4-byte quantity, 
 regardless of the number.

 
 Use TINYINT UNSIGNED, SMALLINT UNSIGNED, etc. wherever reasonable.

Understood. I make all my keys UINT even when they could be smaller, because 
I've gotten into some gnarly consistency problems. with foreign keys.

 KEY `is_value_added` (`is_value_added`),
 A single-column INDEX on a flag, ENUM, and other low-cardinality field, is 
 almost always useless.

Why is that? Surely, even a flag separates the record space into two?

 Performance issues...
 WHERE  YEAR(sales.`Date`) = '{{{1}}}'
 won't use
 KEY `Date` (`Date`),
 because the column (Date) is hidden in a function.  A workaround:
   WHERE `Date` = '{{{1}}}-01-01'
 AND `Date`   '{{{1}}}-01-01' + INTERVAL 1 YEAR

Thanks! Good catch.

 JOINing two subqueries -- There is no way to index either of them, so the 
 JOIN will have to do a table scan of one temp table for every row of the 
 other temp table.
 (The alternative is to CREATE TEMPORARY TABLE... with an index, for one of 
 the subqueries.)

But I made sure the subqueries were the smallest possible sets -- essentially, 
the domain of s_profit_centre, which only has 12 records.

I had the entire thing coded up into one massive JOIN of everything, and it 
took 30 minutes to run! By LEFT JOINing down to a dozen or fewer records, it 
seems to run in reasonable time, even though it's two subqueries that are not 
indexed.

 It would probably be better to move the mt.tot!=0 test inside:
 
   GROUP BY  `Profit Centre`
   ) mt ON mt.pcid = tt.pcid
   WHERE  mt.tot != 0
   ) xx
 --
   GROUP BY  `Profit Centre`
   HAVING tot != 0   -- added
   ) mt ON mt.pcid = tt.pcid
   -- removed:   WHERE  mt.tot != 0
   ) xx
 
 That would make mt have fewer rows, hence that unindexed JOIN could run 
 faster.

My first attempt to do that produced an error. And again, both the subqueries 
will have 12 or fewer records, so I'm wondering if this really helps anything.

Thanks for your help!

[clip]


 People see what they have been conditioned to see; they refuse to see what 
they don't expect to see. -- Merle P. Martin
 Jan Steinman, EcoReality Co-op 





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Making myself crazy...

[Jan Steinman]

`
--   `super_id`
--   `s_project` - `id`
--   `depends_on_id`
--   `s_project` - `id`
--   `steward_id`
--   `mw_user` - `user_id`
--   `profit_centre`
--   `s_profit_centre` - `ID`
--   `profit_centre_amortized`
--   `s_profit_centre` - `ID`



-- 12 records, growing by one or two a year
CREATE TABLE IF NOT EXISTS `s_profit_centre` (
  `ID` int(3) unsigned NOT NULL,
  `name` varchar(255) NOT NULL,
  `description` text NOT NULL,
  `steward` int(5) unsigned NOT NULL,
  `updated` timestamp NOT NULL default CURRENT_TIMESTAMP on update 
CURRENT_TIMESTAMP,
  `notes` text NOT NULL,
  PRIMARY KEY  (`ID`),
  KEY `steward` (`steward`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- RELATIONS FOR TABLE `s_profit_centre`:
--   `steward`
--   `mw_user` - `user_id`




-- 3,090 rows, growing by a few hundred monthly
CREATE TABLE `s_product_sales_log` (
  `Venue` enum('EcoReality grounds','farm gate','delivered','Tuesday 
market','roadside','Saturday market','store','subscription','Yellow House 
kitchen') character set utf8 NOT NULL default 'farm gate',
  `Seller` int(11) unsigned NOT NULL default '212',
  `Who` int(11) unsigned NOT NULL default '0',
  `Date` date NOT NULL,
  `Quantity` decimal(8,3) NOT NULL,
  `Unit` 
enum('bale','box','bunch','dozen','each','grams','kilograms','liters','ounces','pints','pounds','quarts')
 character set utf8 collate utf8_bin NOT NULL default 'each',
  `Total` decimal(8,2) NOT NULL,
  `Type` enum('barter','cash','check','invoice','PayPal','work trade') 
character set utf8 NOT NULL default 'cash',
  `Product` int(11) unsigned NOT NULL,
  KEY `Product` (`Product`),
  KEY `Date` (`Date`),
  KEY `Seller` (`Seller`),
  KEY `Who_2` (`Who`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- RELATIONS FOR TABLE `s_product_sales_log`:
--   `Seller`
--   `mw_user` - `user_id`
--   `Who`
--   `mw_user` - `user_id`
--   `Product`
--   `s_product` - `ID`



-- 186 records, growing by ~1 monthly
CREATE TABLE IF NOT EXISTS `s_product` (
  `ID` int(10) unsigned NOT NULL auto_increment,
  `super` int(11) unsigned default NULL COMMENT 'generalization',
  `name` varchar(31) character set utf8 NOT NULL,
  `units` 
enum('kilograms','grams','pounds','ounces','liters','each','cords','bales') 
character set utf8 NOT NULL default 'kilograms' COMMENT 'preferred unit',
  `profit_centre` int(3) unsigned NOT NULL default '3',
  `tax_qualified` enum('yes','no') character set utf8 NOT NULL default 'yes',
  `tax_livestock_born` enum('yes','no') character set utf8 NOT NULL default 
'no',
  `tax_poultry_egg` enum('yes','no') character set utf8 NOT NULL default 'no',
  `is_value_added` enum('yes','no') character set utf8 NOT NULL default 'no',
  `plant_ID` int(5) unsigned default NULL,
  `description` varchar(255) character set utf8 NOT NULL,
  PRIMARY KEY  (`ID`),
  UNIQUE KEY `Name` (`name`),
  KEY `Description` (`description`),
  KEY `is_value_added` (`is_value_added`),
  KEY `profit_centre` (`profit_centre`),
  KEY `super` (`super`),
  KEY `plant_ID` (`plant_ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='list of 
EcoReality farm products' AUTO_INCREMENT=186 ;

--
-- RELATIONS FOR TABLE `s_product`:
--   `super`
--   `s_product` - `ID`
--   `profit_centre`
--   `s_profit_centre` - `ID`
--   `plant_ID`
--   `s_plants` - `ID`


On 17 Sep 12, at 16:12, Rick James wrote:

 If the subquery-version is not too slow, live with it.
 
 If necessary, make your non-grouped SELECT a subquery and apply GROUP BY 
 outside.  Thus:
 
 SELECT ... FROM ( SELECT non-grouped... ) GROUP BY ...
 
 Could you provide that; we might be able to simplify it.
 Also provide SHOW CREATE TABLE for each table.
 How many rows in each table?  (approx)
 
 -Original Message-
 From: Jan Steinman [mailto:j...@bytesmiths.com]
 Sent: Sunday, September 16, 2012 3:45 PM
 To: mysql@lists.mysql.com
 Subject: Making myself crazy...
 
 I'm having trouble figuring out how to make a query. It seems simple,
 but it's making me crazy right now. Please point out where my thinking
 is addled...
 
 I have the following (simplified) table structure:
 
 s.timelog -- s.projects -- s.profit_centres
 
 s.product.sales -- s.products -- s.profit_centres
 
 (The arrows refer to many-to-one relationships: each Timelog record
 refers to a single Project, which is in a single Profit Centre. Each
 record has an opaque ID referred to by records in the table to its
 left.)
 
 What I want to do is figure out productivity: sales per hour worked per
 Profit Centre.
 
 I can do this at a gross level -- without grouping by Profit Centres --
 with a subquery: simply sum up the Lales and divide by the sum of the
 labour (Timelog.out - Timelog.in). But I suspect even this can be done
 without a subquery.
 
 But needing to do two levels of indirection has stymied me: how can I
 group $/hour by Profit Centre?
 
 You can see the SQL here if you wish:
  http://www.ecoreality.org

Making myself crazy...

[Jan Steinman]

I'm having trouble figuring out how to make a query. It seems simple, but it's 
making me crazy right now. Please point out where my thinking is addled...

I have the following (simplified) table structure:

s.timelog -- s.projects -- s.profit_centres

s.product.sales -- s.products -- s.profit_centres

(The arrows refer to many-to-one relationships: each Timelog record refers to a 
single Project, which is in a single Profit Centre. Each record has an opaque 
ID referred to by records in the table to its left.)

What I want to do is figure out productivity: sales per hour worked per Profit 
Centre.

I can do this at a gross level -- without grouping by Profit Centres -- with a 
subquery: simply sum up the Lales and divide by the sum of the labour 
(Timelog.out - Timelog.in). But I suspect even this can be done without a 
subquery.

But needing to do two levels of indirection has stymied me: how can I group 
$/hour by Profit Centre?

You can see the SQL here if you wish:
http://www.ecoreality.org/wiki/Template:Annual_gross_productivity_for

Then click on the Source link to see the code. (You won't be able to change 
it without logging in.)

I'm using CASE to pivot tables to separate out monetary versus barter/trade 
income.

Thanks for any help offered!


 Respond with love and compassion, rather than punishment and retaliation, 
and an angry person will be disarmed. Trade the need to be right for a loving 
relationship. It is a trade you — and everyone around you — will benefit from. 
-- Dean Van Leuven
 Jan Steinman, EcoReality Co-op 





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: mysql Digest 23 Aug 2012 15:37:35 -0000 Issue 4963

[Jan Steinman]

I apologize for my assertion that system requirements do not appear on the 
website.

In retrospect, that was silly of me to assert ANYTHING is not on ANY WEBSITE 
anywhere.

What I should have said (hinted at in my last sentence) is that system 
requirements are not PROMINENTLY listed on the website.

I'd encourage your marketing department to say something like SQL Maestro 
Group Team for Windows, which would be super prominent, or to at least have a 
system menu item for requirement that is easily accessible from every landing 
page on the website.

On 23 Aug 12, at 08:37, mysql-digest-h...@lists.mysql.com wrote:

 From: SQL Maestro Group sql.maes...@gmail.com
 Date: 23 August 2012 07:01:01 PDT
 To: mysql@lists.mysql.com
 Cc: Jan Steinman j...@bytesmiths.com
 Subject: Re: ANN: PHP Generator for MySQL 12.8 released
 
 
 Hi!
 
 It is not correct. System requirements are published on the website:
 http://www.sqlmaestro.com/products/mysql/phpgenerator/help/00_04_00_system_requirements/
 
 Sincerely yours,
 The SQL Maestro Group Team
 http://www.sqlmaestro.com
 
 - Original Message - From: Jan Steinman j...@bytesmiths.com
 To: mysql@lists.mysql.com
 Sent: Tuesday, August 21, 2012 10:33 PM
 Subject: Re: ANN: PHP Generator for MySQL 12.8 released
 
 
 Gentle reminder: PLEASE note somewhere in your postings that this is a
 Windows-only executable that is useless to pure UNIX/Linux shops.
 
 You don't even note that requirement on your website, until one has gone to
 the trouble to register for the download, only to discover a huge, useless
 .EXE file.
 
 Please consider a prominent Requirements link on your website, detailing
 what is required to run this.
 -
  The oilcan is mightier than the sword. -- Everett Dirksen
  Jan Steinman, EcoReality Co-op 


 I find it fascinating that most people plan their vacations with better 
care than they plan their lives. Perhaps that is because escape is easier than 
change. -- Jim Rohn
 Jan Steinman, EcoReality Co-op 





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: ANN: PHP Generator for MySQL 12.8 released

[Jan Steinman]

Gentle reminder: PLEASE note somewhere in your postings that this is a 
Windows-only executable that is useless to pure UNIX/Linux shops.

You don't even note that requirement on your website, until one has gone to the 
trouble to register for the download, only to discover a huge, useless .EXE 
file.

Please consider a prominent Requirements link on your website, detailing what 
is required to run this.
-
 The oilcan is mightier than the sword. -- Everett Dirksen
 Jan Steinman, EcoReality Co-op 





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: ANN: Hopper (stored procedure debugger), version 1.0.1 released

[Jan Steinman]

 I do find your juvenile
 comments about worshipping, changing product names etc just that.

Ah, you must be from the marketing department -- always willing to make friends 
and influence people.


In examinations, the foolish ask questions that the wise cannot answer. -- 
Oscar Wilde
 Jan Steinman, EcoReality Co-op 





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: ANN: Hopper (stored procedure debugger), version 1.0.1 released

[Jan Steinman]

Can you PLEASE note in your listing when a product is Microsloth-only?

While you're at it, can you PLEASE note it prominently on your website? I 
looked through your product description and saw no specific requirements beyond 
what databases were supported. It wasn't until I tried to download it that I 
noticed the warning sign. (.EXE in the file name)

Hard as it is to believe, the entire world does not worship at the alter of 
Bill Gates. We don't allow any Microsloth products on our site.


In a low-energy future... the wealth of nations will be measured by the 
quantity and quality of their forests. -- David Holmgren
 Jan Steinman, EcoReality Co-op 


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: [Puppet Users] Re: Announce: PuppetDB 0.9.0 (first release) is available

[Jan Steinman]

On 23 May 12, at 03:15, Walter Heck wrote:

 Also, have you looked at MariaDB 5.5?

I've been playing with it a bit. Their virtual columns enhancement is pretty 
cool -- something I miss from my FileMaker days.


Economics is extremely useful as a form of employment for economists. -- John 
Kenneth Galbraith
 Jan Steinman, EcoReality Co-op 





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Reducing ibdata1 file size

[Jan Steinman]

 From: Claudio Nanni claudio.na...@gmail.com
 
 No, as already explained, it is not possible, Innodb datafiles *never* shrink.

That's been the common wisdom for a long time.

However, this just popped up on my RSS reader. I haven't even looked at it, let 
alone tried it.

I'm interested in what the experts think...

Getting rid of huge ibdata file, no dump required: You have been told (guilty 
as charged), that the only way to get rid of the huge InnoDB tablespace file 
(commonly named ibdata1), when moving to innodb_file_per_table, is to do a 
logical dump of your data, completely erase everything, then import the dump.

http://code.openark.org/blog/mysql/getting-rid-of-huge-ibdata-file-no-dump-required


Four multinational companies control over seventy percent of fluid milk sales 
in the U.S... These giants have grown through debt-fueld acquisitions and 
mergers and by keeping payments to dairy farmers as low as possible. -- Ron 
Schmid
 Jan Steinman, EcoReality Co-op 





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Reducing ibdata1 file size

[Jan Steinman]

Okay, my mistake. I should write precisely when communicating with precise 
people. :-)

What I meant was, dumping and importing is the common knowledge way of 
virtually shrinking innodb files.

So, now that I've conceded the meta-argument, what do you think of the linked 
procedure for reducing innodb files?

On 22 May 12, at 06:40, Claudio Nanni wrote:

 Jan,
 
 that's not common wisdom, Innodb datafiles ***never*** shrink,
 that in the blog from 22th of May is a workaround, one of the many.
 If you ask my my favourite is to use a stand by instance and work on that.
 
 Claudio
 
 2012/5/22 Jan Steinman j...@bytesmiths.com
  From: Claudio Nanni claudio.na...@gmail.com
 
  No, as already explained, it is not possible, Innodb datafiles *never* 
  shrink.
 
 That's been the common wisdom for a long time.
 
 However, this just popped up on my RSS reader. I haven't even looked at it, 
 let alone tried it.
 
 I'm interested in what the experts think...
 
 Getting rid of huge ibdata file, no dump required: You have been told 
 (guilty as charged), that the only way to get rid of the huge InnoDB 
 tablespace file (commonly named ibdata1), when moving to 
 innodb_file_per_table, is to do a logical dump of your data, completely erase 
 everything, then import the dump.

 http://code.openark.org/blog/mysql/getting-rid-of-huge-ibdata-file-no-dump-required
 
 
 Four multinational companies control over seventy percent of fluid milk sales 
 in the U.S... These giants have grown through debt-fueld acquisitions and 
 mergers and by keeping payments to dairy farmers as low as possible. -- Ron 
 Schmid
  Jan Steinman, EcoReality Co-op 
 
 
 
 
 
 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql
 
 
 
 
 -- 
 Claudio


No man is so foolish but he may sometimes give another good counsel, and no man 
so wise that he may not easily err if he takes no other counsel than his own. 
He that is taught only by himself has a fool for a master. -- Ben Johnson
 Jan Steinman, EcoReality Co-op 





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Why does the limit use the early row lookup.

[Jan Steinman]

On 24 Apr 12, at 15:57, mysql-digest-h...@lists.mysql.com wrote:

 From: shawn green shawn.l.gr...@oracle.com
 
 
 On 4/22/2012 11:18 PM, Zhangzhigang wrote:
 Why does not the mysql developer team to do this optimization?
 
 When the Optimizer is told to sort a result set in the order determined
 by a random value created only at the time of the query, what better
 technique could they use than to materialize the table, sort the data,
 then return the results?

I agree that the common technique of ORDER BY RAND() LIMIT 1 is brain dead in 
its expectations.

And yet, this is a fairly common thing to want.

Could not some special syntax be provided to allow for efficient retrieval of 
random records?

I would suggest it belongs in the WHERE clause, so the optimizer would clearly 
be informed, something like WHERE RANDOM_RECORDS(4) to give four records at 
random.

I have gone so far as to create an indexed field of random numbers, then select 
on it. But this has its own set of problems, like not being able to guarantee a 
single result in the selection.

Are the standards silent on the need for random selections?


There are only two ways to look at life: One is as if nothing is a miracle. The 
other is as if everything is a miracle. -- Albert Einstein
 Jan Steinman, EcoReality Co-op 





--
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

[Jan Steinman]

 From: joe j joe.st...@gmail.com
 
 Thanks. I was looking for a loop--not to gain speed but to reduce the
 length of my script file:) I'll try and hopefully it will work!

Is there any reason (besides human keystrokes) that you want a short script?

If not, how 'bout the best of both worlds: create a UNIX (or perl) script that 
loops through the country names and creates an SQL script? That would be fast 
entering AND fast performing!

 J
 
 On Wed, Apr 4, 2012 at 12:16 PM, Johan De Meersman vegiv...@tuxera.be wrote:
 - Original Message -
 From: joe j joe.st...@gmail.com
 
 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?
 
 You can't create multiple tables with one statement; but you *can* put 
 multiple statements on a single line. However, this does nothing for 
 performance - nor readabiltiy, for that matter :-)
 
 You could (on *nix) write a simple commandline loop for it, I suppose. 
 Something along the lines of
 
 for x in UK US BE FR; do mysql -e create table ${x}_table_new as select 
 blahblahblbah; done


Expose yourself to your deepest fear; after that, fear has no power, and the 
fear of freedom shrinks and vanishes. You are free. -- Jim Morrison
 Jan Steinman, EcoReality Co-op 





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: tea pots, tea accessories

[Jan Steinman]

For those who want to thank Andrew for his spam by putting his SMPT server in 
your firewall and/or blackhole list:

# dig mx porcelainbrt.com

porcelainbrt.com.   3600IN  MX  0 smtp.asia.secureserver.net.

# dig smtp.asia.secureserver.net

smtp.asia.secureserver.net. 684 IN  A   182.50.144.66

For those who don't have any reason to talk to an SMPT server from Singapore at 
all:

# whois 182.50.144.66

inetnum:182.50.128.0 - 182.50.159.255 (or in ipfw-ready form: 
182.50.128.0/19)
...
e-mail: gschwi...@godaddy.com

Perhaps if enough of us expressed our appreciation to gschwimer, for this 
spam, we won't have to block all those IP addresses...

To say nothing of the cynicism required to spam a mailing list and yet have an 
obfuscated email address, designed to avoid getting in spam lists... I think 
sa...@porcelainbrt.com needs to be de-obfuscated. Perhaps it could be posted to 
a few dozen UCE websites to make sure they get a dose of their own medicine.

(Sorry, it's been a bad day. My tiny aDSL pipe has been recently overwhelmed 
with spam, and I can barely use the Internet because of it.)

On 16 Mar 12, at 09:23, mysql-digest-h...@lists.mysql.com wrote:

 From: Andrew--BRT Ceramic brtcera...@vip.163.com
 Date: 15 March 2012 06:16:34 PDT
 To: mysql@lists.mysql.com
 Subject: tea pots, tea accessories
 
 Dear purchasing manager,
 
 This is Andrew from BRT Ceramic company in China...
 
 Web: w w w . porcelainbrt . c o m
 
 Email: sales {at} porcelainbrt . c o m
 
 Phone: +86-592-2055232


We must learn, if we can, the sources and cost of our own economic lives. -- 
Wendell Berry
 Jan Steinman, EcoReality Co-op 





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Design advice for hotel availability program

[Jan Steinman]

Where are your domain experts? You *are* consulting with them, no?

If you don't know the answers, and don't have access to domain experts to help 
you, I would design for the most general case, and factor out exceptions as 
they prove to be so. Pre-optimization for exceptions almost always turns out 
to be a bad choice.

 From: Tompkins Neil neil.tompk...@googlemail.com
 
   Am
 I best using the following pattern
 
 (1) Default rates/rooms stored in a generic table
 (2) Any exceptions/changes/closed days to the daily rates are store in
 a separate table.
 (3) Any special offer exceptions are stored as a rule
 
 All, should I consider that for any hotel, for any room, for any day I have
 a record in a huge single table ???


Everything we think we know about the world is a model... None of these is or 
ever will be the real world. -- Donella H. Meadows
 Jan Steinman, EcoReality Co-op 





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: How to interrupt MySQL interpreter output?

[Jan Steinman]

 From: Dotan Cohen dotanco...@gmail.com
 
 If I see that a query is taking a long time to finish, how can I
 interrupt the MySQL CLI interpreter? Ctrl-C does not work. Thanks.

Does Ctrl-Z suspend the process? If so, you can then use the progression: kill 
-INT procnum; kill -QUIT procnum; kill -KILL procnum.

Never do a kill -KILL until you've tried other ways of stopping it. I 
wouldn't even put it on the same command line like I showed above, as it may 
take some time for mysql(1) to clean up and shut down properly, and signals are 
asynchronous.


Fire does not matter, earth and air and water do not matter. I do not 
matter. No word matters. But man forgets reality and remembers words. The more 
words he remembers, the cleverer do his fellows esteem him. He looks upon the 
great transformations of the world, but he does not see them as they were seen 
when man looked upon reality for the first time. Their names come to his lips 
and he smiles as he tastes them, thinking he knows them in the naming. -- 
Siddartha
 Jan Steinman, EcoReality Co-op 





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: delete all hosts using a wildcard

[Jan Steinman]

 From: Tim Dunphy bluethu...@jokefire.com
 
 ... this is just a test environment so getting rid of those users won't have 
 any meaningful impact...

I think what Paul (who wrote a book on MySQL, by the way) was getting at was 
that you risk what database folk call referential integrity issues if you 
mess with *any* data without knowing where else it is used.

But this has a bigger impact than if you mess up referential integrity on your 
own tables. It could be that MySQL is making certain assumptions -- such as a 
`user` record WILL be available if referenced in some other privilege grant -- 
that will break things badly, making such tables (or functions, or procs, etc.) 
unreachable. This could turn into a very confusing learning opportunity where 
changing one thing has far-reaching unintended impact. Or it may not, if you 
don't have other privileges defined, in which case you may have learned the 
false assurance that you can get away with such a thing.

I've had the former learning experience -- that messing with privilege tables 
directly resulted in strange behaviour that ended with me trashing the entire 
thing and re-installing from scratch.

My rule-of-thumb: if MySQL gives you a facility for manipulating system-level 
tables, just use it! :-)



 Jan Steinman, EcoReality Co-op 





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Common Pattern for parent-child INSERTs?

[Jan Steinman]

Okay, I'm seeking enlightenment here.

In my trivial case, one can save storage by not having a record extension for 
people without phones. Big deal.

In my real-world case, I have a contacts database with your typical name, 
address, phone, email, etc. info. Then I have extensions for people who are 
particular type of contacts that have more information than the general case. 
If I have several thousand records in my contacts database, but only ten in the 
dairy customers database, I'm saving a ton of storage by not having every 
single record in the general-purpose contacts database contain stuff like 
desired_milk_pickup_day or SET dairy_products_of_interest.

But now I have a different extension, Volunteers, with extra fields like 
special_skills, dietary_restrictions, etc. I don't want those fields in the 
general contact list. And there's another extension, Advisory, that holds 
extra information for contacts who are on our advisory council.

In normalizing databases, I was taught to do exactly what I've done, separate 
out the special cases and put them in a separate table. But as you note, that 
creates a bit of a mess for INSERT while simplifying SELECT.

ON UPDATE CLAUSE does not help on INSERT, does it? I mean, how does it know the 
auto-increment value of the parent record before it's been INSERTed?

It appears that anything I do must be wrapped in a transaction, or there's the 
chance (however unlikely) that something will get in between the INSERT of the 
parent and that of the child.

On 5 Jan 12, at 05:51, Johan De Meersman wrote:

 http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html
 
 If you use InnoDB for your tables, you can use the ON UPDATE CASCADE option 
 for your foreign key constraints.
 
 However, your habit is indeed a nasty one :-p It forces you to do joins where 
 none are necessary, thus needlessly slowing down operations.
 
 The oo extends idea does not exactly match what you're doing, as the 
 child table you create does not inherit the parent's attributes, it 
 merely has it's own column and a reference to the parent. This is good for 
 1:n relations, but for 1:1 there are only downsides (except for a very few 
 edge cases).
 
 
 
 
 - Original Message -
 From: Jan Steinman j...@bytesmiths.com
 To: mysql@lists.mysql.com
 Sent: Thursday, 5 January, 2012 1:12:15 AM
 Subject: Common Pattern for parent-child INSERTs?
 
 Having been steeped in object-orientation, I have a nasty habit of
 creating parent-child tables that have a 1:1 relationship where the
 child extends the parent, sometimes to a depth of three or more.
 
 For example:
 
 CREATE TABLE names TYPE InnoDB
   id INT NOT NULL AUTO INCREMENT PRIMARY KEY,
   name_first VARCHAR(255) NOT NULL,
   name_last VARCHAR(255) NOT NULL
 
 CREATE TABLE addresses TYPE InnoDB
   names_id INT NOT NULL REFERENCES names (id)
   street VARCHAR(255) NOT NULL,
   city VARCHAR(255) NOT NULL
 
 CREATE TABLE phones TYPE InnoDB
   names_id INT NOT NULL REFERENCES names (id)
   phone VARCHAR(255) NOT NULL
 
 (Keyed in from memory for schematic purposes, may contain errors.
 CREATE syntax is not what I'm here about.)
 
 Now how do I go about INSERTing or UPDATEing two or three tables at
 once in a way that maintains referential integrity?
 
 I've tried making a VIEW, but I wasn't able to INSERT into it. I
 don't think I was violating the restrictions on VIEWs as stated in
 the manual.
 
 Is there a generalized pattern that is used for INSERTing and
 UPDATEing these parent-child tables? Does it require a TRIGGER in
 order to propagate the foreign key?
 
 (BTW: MySQL version 5.0.92, if that matters...)
 
 Thanks in advance for any help offered!
 
 
 -- 
 Bier met grenadyn
 Is als mosterd by den wyn
 Sy die't drinkt, is eene kwezel
 Hy die't drinkt, is ras een ezel


An idea that is not dangerous is unworthy of being called an idea at all. -- 
Oscar Wilde
 Jan Steinman, EcoReality Co-op 





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: (off topic) why PATH

[Jan Steinman]

 From: Reindl Harald h.rei...@thelounge.net
 
 however: both chroot and virtualization has nothing to do with this whole 
 topic, really nothing

But that shouldn't keep someone from going off on an anti-Mac rant, no? :-)

The OP was on a Mac, and it really doesn't help to tell them they have the 
wrong computer.


I believe that Ronald Reagan will someday make this country what it once was: 
an arctic wilderness. -- Steve Martin
 Jan Steinman, EcoReality Co-op 





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Common Pattern for parent-child INSERTs?

[Jan Steinman]

Having been steeped in object-orientation, I have a nasty habit of creating 
parent-child tables that have a 1:1 relationship where the child extends the 
parent, sometimes to a depth of three or more.

For example:

CREATE TABLE names TYPE InnoDB
   id INT NOT NULL AUTO INCREMENT PRIMARY KEY,
   name_first VARCHAR(255) NOT NULL,
   name_last VARCHAR(255) NOT NULL

CREATE TABLE addresses TYPE InnoDB
   names_id INT NOT NULL REFERENCES names (id)
   street VARCHAR(255) NOT NULL,
   city VARCHAR(255) NOT NULL

CREATE TABLE phones TYPE InnoDB
   names_id INT NOT NULL REFERENCES names (id)
   phone VARCHAR(255) NOT NULL

(Keyed in from memory for schematic purposes, may contain errors. CREATE syntax 
is not what I'm here about.)

Now how do I go about INSERTing or UPDATEing two or three tables at once in a 
way that maintains referential integrity?

I've tried making a VIEW, but I wasn't able to INSERT into it. I don't think I 
was violating the restrictions on VIEWs as stated in the manual.

Is there a generalized pattern that is used for INSERTing and UPDATEing these 
parent-child tables? Does it require a TRIGGER in order to propagate the 
foreign key?

(BTW: MySQL version 5.0.92, if that matters...)

Thanks in advance for any help offered!


Security is mostly a superstition. Security does not exist in nature, nor do 
the children of men as a whole experience it. Avoiding danger is no safer in 
the long run than outright exposure. Life is either a daring adventure, or 
nothing. -- Helen Keller
 Jan Steinman, EcoReality Co-op 





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Common Pattern for parent-child INSERTs?

[Jan Steinman]

Thanks, Claudio. What you suggested is essentially what I'm doing. I just 
thought if this were something common, someone would have a better way of doing 
it. I would LOVE to be able to simply insert into a names-addresses-phones 
VIEW, but I haven't been able to make that work.

On 4 Jan 12, at 16:48, Claudio Nanni wrote:

 Hi Jan,
 
 I am not sure to understand what your question is,
 what do you mean with inserting updating 2-3 tables?
 I guess treat the 3-tables join as one single 'object' ?
 
 Since you have the referential integrity constraint on the [addresses]
 and [phones] table you need to follow this basic pattern:
 
 INSERT:
 
 1.insert the record into [names]
 2.insert the records into [addresses] and [phones]
 
 DELETE:
 1.delete the records from [addresses] and [phones]
 2.delete the record from [names]
 
 UPDATE:
 (a)no problem if you don't update the foreign keys (i.e. assigning an
 address and/or a phone number to another person)
 (b)if you need to update the foreign keys just make sure you set them
 to an existing names_id
 
 The problem you mention with the view is probably coming from the fact
 that when you insert into a view although theoretically possible if
 the underlying select is a simple multi-table join (updatable view)
 you have no assurance on the order of the inserts inside the view, it
 is probably depending on the specific storage engine implementation.
 
 I hope this shed a bit of light.
 
 Claudio
 
 2012/1/5 Jan Steinman j...@bytesmiths.com:
 Having been steeped in object-orientation, I have a nasty habit of creating 
 parent-child tables that have a 1:1 relationship where the child extends the 
 parent, sometimes to a depth of three or more.
 
 For example:
 
 CREATE TABLE names TYPE InnoDB
   id INT NOT NULL AUTO INCREMENT PRIMARY KEY,
   name_first VARCHAR(255) NOT NULL,
   name_last VARCHAR(255) NOT NULL
 
 CREATE TABLE addresses TYPE InnoDB
   names_id INT NOT NULL REFERENCES names (id)
   street VARCHAR(255) NOT NULL,
   city VARCHAR(255) NOT NULL
 
 CREATE TABLE phones TYPE InnoDB
   names_id INT NOT NULL REFERENCES names (id)
   phone VARCHAR(255) NOT NULL
 
 (Keyed in from memory for schematic purposes, may contain errors. CREATE 
 syntax is not what I'm here about.)
 
 Now how do I go about INSERTing or UPDATEing two or three tables at once in 
 a way that maintains referential integrity?
 
 I've tried making a VIEW, but I wasn't able to INSERT into it. I don't think 
 I was violating the restrictions on VIEWs as stated in the manual.
 
 Is there a generalized pattern that is used for INSERTing and UPDATEing 
 these parent-child tables? Does it require a TRIGGER in order to propagate 
 the foreign key?
 
 (BTW: MySQL version 5.0.92, if that matters...)
 
 Thanks in advance for any help offered!
 
 
 Security is mostly a superstition. Security does not exist in nature, nor do 
 the children of men as a whole experience it. Avoiding danger is no safer in 
 the long run than outright exposure. Life is either a daring adventure, or 
 nothing. -- Helen Keller
  Jan Steinman, EcoReality Co-op 
 
 
 
 
 
 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql
 
 
 
 
 -- 
 Claudio


If they can get you asking the wrong questions, they don't have to worry about 
the answers. -- Thomas Pynchon
 Jan Steinman, EcoReality Co-op 





--
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

[Jan Steinman]

On 31 Dec 11, at 20:36, Govinda wrote:
 If you're using MacOS X Server, it should be in /usr/bin, which should be in 
 your default $PATH, or else you couldn't do ANYTHING, including ls.
 
 I have notes somewhere in my stuff about how to get $PATH to include where 
 mysql actually lives, but once I realized what the issue was (in my OP this 
 thread) then I was fine with just using a full path for now.  The convenience 
 of a 'fixed' $PATH will be nice, sooner or later (when I get to it), but for 
 now it is just as well that I let it beat into my head how the CL is actually 
 working (working out the full paths)

You should fix the $PATH, as you'll need it for utilities (such as mysqldump) 
and such.

You need to edit your shell startup file. For bash, it's .bash_profile in 
your home directory. Other shells will have their own startup script. My 
.bash_profile includes:

export 
PATH=$HOME/bin:/Developer/Tools:/usr/local/bin:/usr/local/sbin:/usr/bin:/bin:/usr/sbin:/sbin:/opt/local/bin:/opt/local/sbin

Do echo $SHELL to see which shell you're using.

Do printenv to see all your global shell variables, including $SHELL and 
$PATH.

 What does locate mysqldump tell you?
 
 Govind% locate mysqldump
 
 WARNING: The locate database (/var/db/locate.database) does not exist.
 To create the database, run the following command:
 
  sudo launchctl load -w /System/Library/LaunchDaemons/com.apple.locate.plist

[message repeated after running the suggested command]

What that does is tells the system launcher to index your disks in the 
background, so it's no surprise that it would not immediately create a working 
database.

It should have finished by now, and you should now be able to run the locate 
command.

 How about echo $PATH?
 
 Govind% echo $PATH
 /usr/bin:/bin:/usr/sbin:/sbin:/usr/local/bin:/usr/local/git/bin:/usr/X11/bin

I don't have the official binary distribution in front of me, but once you get 
locate working, you can add the path of your MySQL binaries to the $PATH 
variable by appending it (preceded by a colon) to the $PATH declaration in your 
shell's startup script.


Do you often think about difficulties, failure and disasters? Do you keep 
thinking about the negative news you have seen on the TV or read in the 
newspapers? Do you see yourself stuck and unable to improve your life or your 
health? Do you frequently think that you do not deserve happiness or money, or 
that it is too difficult to get them? If you do, then you will close your mind, 
see no opportunities, and behave and react in such ways as to repel people and 
opportunities. You let the power of negative thinking rule your life. -- Ramon 
Sasson
 Jan Steinman, EcoReality Co-op 





--
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

[Jan Steinman]

 So then I try (in Mac OS X Terminal, while logged in as me (not root)):
 mysqldump -uroot -p myDBname myTableName  ~/myTestDumpedTable.sql
 ...and again it produces:
 sh: mysqldump: command not found..
 
 that is because Mac OSX is missing a package-managment and so you need
 a little knowledge about your OS to fix the PATH or you have to use
 full-qualified calls or configure/install your software to locations.

How did you get your copy of MySQL?

If you're using MacOS X Server, it should be in /usr/bin, which should be in 
your default $PATH, or else you couldn't do ANYTHING, including ls.

And for the record, there are at least two excellent package managers available 
for Mac OS, and either MacPorts or Fink should append the proper path to their 
binaries to the $PATH variable so they can be found. (Although you need to log 
out and log back in to have your shell's .rc file executed.)

Or else you built from source, in which case, you should know how to fix your 
$PATH.

What does locate mysqldump tell you? How about echo $PATH?


A gentleman of our days is one who has money enough to do what every fool would 
do if he could afford it: that is, consume without producing. -- George Bernard 
Shaw
 Jan Steinman, EcoReality Co-op 





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Query query

[Jan Steinman]

 From: Arthur Fuller fuller.art...@gmail.com
 
 You should still lose the pointless WHERE 1.

Besides being pointless, is there any harm in having a superfluous WHERE 1 
beyond the few microseconds it takes to parse it and throw it away?

Just curious, because phpMyAdmin includes it in its query template, and I often 
just leave it there when making views or bookmarking queries.


No one can hurt you without your consent. -- Eleanor Roosevelt
 Jan Steinman, EcoReality Co-op 





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Query query

[Jan Steinman]

I'm having brain freeze, and wonder if anyone can help me with a query.

I have a library in MySQL. There's a table with a record per book, and other 
tables that it indexes into for meaningful info. One of those is an 
integer-keyed list of 1,000 Dewey Decimal Codes. In the books table, the Dewey 
field is decimal. In the Dewey table, it's an integer.

I would like to make a report with the info for each DDC, including whether or 
not there are any books for any given code's integer part. In other words, I 
want to bucketize 101.000 to 101.999, etc, for each integer Dewey number, and 
give some info if the count in that range is non-zero.

I suspect I need a subquery to do this, but my brain is frozen! (Or should I 
use a join? Can you even join on an inequality?)

The following crashes phpMyAdmin when I try to do it. I suspect it's because 
the subquery reference to ddn.Dewey is out of context. The subquery works on 
its own when ddn.Dewey is a literal integer.

SELECT
(SELECT COUNT(*)  0 FROM s_library lib WHERE FLOOR(lib.Dewey) = ddn.Dewey) 
AS Have,
ddn.Dewey AS DDN,
ddn.Classification AS Classification
FROM s_library_dewey
ddn WHERE 1

Any thoughts on the best way to do this?

Thanks!


After providing the wealth on which the city is built, the countryside and its 
people are increasingly seen as dispensable. When it appears that cities can 
thrive on their global connections, rural hinterlands die. -- David Holmgren
 Jan Steinman, EcoReality Co-op 





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Query query

[Jan Steinman]

Second attempt, using a join, returns just one row for Dewey 000 with the 
COUNT being about half the volumes in the library, which isn't right...

I thought a LEFT OUTER JOIN would have returned a record for every record in 
s_library_dewey, but it only returns the first.

Brain freeze again...

SELECT
COUNT(lib.Dewey) AS Have,
ddn.Dewey AS DDN,
ddn.Classification AS Classification
FROM s_library_dewey ddn
LEFT OUTER JOIN s_library lib ON ddn.Dewey = FLOOR(lib.Dewey)
WHERE 1


 I'm having brain freeze, and wonder if anyone can help me with a query.
 
 I have a library in MySQL. There's a table with a record per book, and other 
 tables that it indexes into for meaningful info. One of those is an 
 integer-keyed list of 1,000 Dewey Decimal Codes. In the books table, the 
 Dewey field is decimal. In the Dewey table, it's an integer.
 
 I would like to make a report with the info for each DDC, including whether 
 or not there are any books for any given code's integer part. In other words, 
 I want to bucketize 101.000 to 101.999, etc, for each integer Dewey number, 
 and give some info if the count in that range is non-zero.
 
 I suspect I need a subquery to do this, but my brain is frozen! (Or should I 
 use a join? Can you even join on an inequality?)
 
 The following crashes phpMyAdmin when I try to do it. I suspect it's because 
 the subquery reference to ddn.Dewey is out of context. The subquery works 
 on its own when ddn.Dewey is a literal integer.
 
 SELECT
(SELECT COUNT(*)  0 FROM s_library lib WHERE FLOOR(lib.Dewey) = 
 ddn.Dewey) AS Have,
ddn.Dewey AS DDN,
ddn.Classification AS Classification
 FROM s_library_dewey
 ddn WHERE 1
 
 Any thoughts on the best way to do this?
 
 Thanks!
 

After providing the wealth on which the city is built, the countryside and its 
people are increasingly seen as dispensable. When it appears that cities can 
thrive on their global connections, rural hinterlands die. -- David Holmgren
 Jan Steinman, EcoReality Co-op 





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Query query

[Jan Steinman]

DOH! Brain unfroze, and I realized I needed an aggregate:

SELECT
COUNT(lib.Dewey) AS Have,
ddn.Dewey AS DDN,
ddn.Classification AS Classification
FROM s_library_dewey ddn
LEFT OUTER JOIN s_library lib ON ddn.Dewey = FLOOR(lib.Dewey)
WHERE 1
GROUP BY ddn.Dewey

... although if there are any brighter ideas, I'm all ears. This doesn't seem 
very optimal to me, with the FLOOR() function in the JOIN and all... takes over 
half a second...

 Second attempt, using a join, returns just one row for Dewey 000 with the 
 COUNT being about half the volumes in the library, which isn't right...
 
 I thought a LEFT OUTER JOIN would have returned a record for every record in 
 s_library_dewey, but it only returns the first.
 
 Brain freeze again...
 
 SELECT
COUNT(lib.Dewey) AS Have,
ddn.Dewey AS DDN,
ddn.Classification AS Classification
 FROM s_library_dewey ddn
 LEFT OUTER JOIN s_library lib ON ddn.Dewey = FLOOR(lib.Dewey)
 WHERE 1
 
 
 I'm having brain freeze, and wonder if anyone can help me with a query.
 
 I have a library in MySQL. There's a table with a record per book, and other 
 tables that it indexes into for meaningful info. One of those is an 
 integer-keyed list of 1,000 Dewey Decimal Codes. In the books table, the 
 Dewey field is decimal. In the Dewey table, it's an integer.
 
 I would like to make a report with the info for each DDC, including whether 
 or not there are any books for any given code's integer part. In other 
 words, I want to bucketize 101.000 to 101.999, etc, for each integer Dewey 
 number, and give some info if the count in that range is non-zero.
 
 I suspect I need a subquery to do this, but my brain is frozen! (Or should I 
 use a join? Can you even join on an inequality?)
 
 The following crashes phpMyAdmin when I try to do it. I suspect it's because 
 the subquery reference to ddn.Dewey is out of context. The subquery works 
 on its own when ddn.Dewey is a literal integer.
 
 SELECT
   (SELECT COUNT(*)  0 FROM s_library lib WHERE FLOOR(lib.Dewey) = 
 ddn.Dewey) AS Have,
   ddn.Dewey AS DDN,
   ddn.Classification AS Classification
 FROM s_library_dewey
 ddn WHERE 1
 
 Any thoughts on the best way to do this?
 
 Thanks!
 

After providing the wealth on which the city is built, the countryside and its 
people are increasingly seen as dispensable. When it appears that cities can 
thrive on their global connections, rural hinterlands die. -- David Holmgren
 Jan Steinman, EcoReality Co-op 





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: large temp files created by mysql

[Jan Steinman]

 From: mos mo...@fastmail.fm
 
 
 At 10:34 AM 10/24/2011, you wrote:
 select id from table order by rand() limit 1;
 is doing as example a dumb temporary table with the full size
 
 Because it has to sort the entire table, then it returns the one row. This of 
 course is extremely inefficient. :)

That is absolutely incredible and counter-intuitive, and (as you say) extremely 
inefficient!

This is used everywhere. Perhaps it is one of the biggest anti-patterns in 
SQL. I just checked two different SQL cookbook sites, and they both recommend 
ORDER BY RAND().

I just googled around a bit, and found that putting RAND() in the WHERE clause 
is very efficient:

SELECT id FROM table WHERE RAND()  0.01 LIMIT 1

The comparison constant can be optimized for the number of rows you have. The 
above returns the first record of 1% of the table. If you have a million rows, 
you might want to bump that to something like 100 parts per million or so.

But really, folks, this is something so ubiquitous and so recommended, why 
can't the query optimizer look out for ORDER BY RAND() and simply skip the 
table sort and just grab some record? (Hopefully using something better than 
Knuth's LCRNG...)


Learning to think wholistically requires an overriding, or reversal, of much of 
the cultural heritage of the last few hundred years. -- David Holmgren
 Jan Steinman, EcoReality Co-op 


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: large temp files created by mysql

[Jan Steinman]

Actually, having tried that, you still need the ORDER BY RAND() in there. 
Otherwise, I keep getting the same record over and over. But it surely cuts way 
down on the number of rows that need to be sorted.

So if your table size is fairly stable, and you pick a good number for the 
WHERE constant, you can make this quite speedy.

Still, it seems there should be a better way...

On 30 Oct 11, at 18:51, Jan Steinman wrote:

 From: mos mo...@fastmail.fm
 
 
 At 10:34 AM 10/24/2011, you wrote:
 select id from table order by rand() limit 1;
 is doing as example a dumb temporary table with the full size
 
 Because it has to sort the entire table, then it returns the one row. This 
 of course is extremely inefficient. :)
 
 That is absolutely incredible and counter-intuitive, and (as you say) 
 extremely inefficient!
 
 This is used everywhere. Perhaps it is one of the biggest anti-patterns in 
 SQL. I just checked two different SQL cookbook sites, and they both 
 recommend ORDER BY RAND().
 
 I just googled around a bit, and found that putting RAND() in the WHERE 
 clause is very efficient:
 
 SELECT id FROM table WHERE RAND()  0.01 LIMIT 1
 
 The comparison constant can be optimized for the number of rows you have. The 
 above returns the first record of 1% of the table. If you have a million 
 rows, you might want to bump that to something like 100 parts per million or 
 so.
 
 But really, folks, this is something so ubiquitous and so recommended, why 
 can't the query optimizer look out for ORDER BY RAND() and simply skip the 
 table sort and just grab some record? (Hopefully using something better than 
 Knuth's LCRNG...)
 
 
 Learning to think wholistically requires an overriding, or reversal, of much 
 of the cultural heritage of the last few hundred years. -- David Holmgren
  Jan Steinman, EcoReality Co-op 
 


Within a few human generations, the low-energy patterns observable in natural 
landscapes will again form the basis of human system design after the richest 
deposits of fossil fuels and minerals are exhausted. -- David Holmgren
 Jan Steinman, EcoReality Co-op 


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: 4 minute slow on select count(*) from table - myisam type

[Jan Steinman]

 From: Joey L mjh2...@gmail.com
 
 i did google search - myisam is faster...i am not really doing any
 transaction stuff.

That's true for read-only. But if you have a mix of reads and writes, MYISAM 
locks tables during writes, which could be blocking reads.


In a museum in Havana, there are two skulls of Christopher Columbus; one when 
he was a boy and one when he was a man. -- Mark Twain
 Jan Steinman, EcoReality Co-op 


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Quantity of time from difference of two Datetime values?

[Jan Steinman]

Thanks, Hank!

I figured it was something like that, but couldn't see any clear documentation 
on the Oracle reference page on date/time functions.

On Sep 30, 2011, at 8:22 PM, Hank wrote:

 n Fri, Sep 30, 2011 at 11:08 PM, Jan Steinman j...@bytesmiths.com wrote:
 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.
 
 Surely, there are tons of payroll apps that subtract the punch-out from the 
 punch-in to come up with a quantity of time?
 
 What is YOUR favourite way of coming up with a quantity of time as the 
 difference between two arbitrary Datetimes? Did I overlook something simple? 
 Do I need to convert the two to scalar integers first?
 
 
 See the unix_timestamp() function.  Converts date/times to a scalar
 integer of the number of seconds since the Unix Epoch.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Quantity of time from difference of two Datetime values?

[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.

Surely, there are tons of payroll apps that subtract the punch-out from the 
punch-in to come up with a quantity of time?

What is YOUR favourite way of coming up with a quantity of time as the 
difference between two arbitrary Datetimes? Did I overlook something simple? Do 
I need to convert the two to scalar integers first?



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: table design question

[Jan Steinman]

 From: Richard Reina gatorre...@gmail.com
 
 I want to create a US geography database. So far I have categories such as
 state nick names (some states have more than one), state mottos (text 25 to
 150 characters), state name origins (100-300 characters), state trivial
 facts, entry into union.  My question is; would it be better to keep at
 least some of this information in separate tables...

To me, the key question is cardinality.

You gave a big clue with some states have more than one. This cardinality 
rule clearly indicates you need a separate table for nick names.

I'd look carefully at cardinality, and any field in which you can say, some 
states may have more than one, put it in a separate table.

(One exception to cardinality-driven table design would be if a field is a 
clearly defined, relatively unchanging set of constants. The classic example is 
when different states in a process need to be recorded -- membership might 
include the set applied, paid, accepted, withdrawn. You could have 
multiple states in a SET field, which would be much less cumbersome than having 
a fifth-normal-form join table.)


A low-energy policy allows for a wide choice of lifestyles and cultures. If, on 
the other hand, a society opts for high energy consumption, its social 
relations must be dictated by technocracy and will be equally degrading whether 
labeled capitalist or socialist. -- Ivan Illich
 Jan Steinman, EcoReality Co-op 


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Build from bazaar source on Mac 10.7 (Lion) fails

[Jan Steinman]

 From: Derek Downey de...@orange-pants.com
 
 I am trying to setup a development machine to start delving into some of the 
 internal code of the MySQL DB. I'm off to a great start, since I can't even 
 get it to build properly...
 
 My machine is: 
 
 $ uname -a 
 Darwin DDMac 11.1.0 Darwin Kernel Version 11.1.0: Tue Jul 26 16:07:11 PDT 
 2011; root:xnu-1699.22.81~1/RELEASE_X86_64 x86_64

Since you're on a Mac, have you considered using MacPorts? MySQL builds and 
installs flawlessly on 10.6.8 with sudo port -f install mysql5 here. (Haven't 
tried it on 10.7 yet.)

http://www.macports.org/


I can remember when a good politician had to be 75 percent ability and 25 
percent actor, but I can well see the day when the reverse could be true. -- 
Harry S. Truman
 Jan Steinman, EcoReality Co-op 


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: ANN: Database Workbench 4.1.3, the multi-DBMS IDE now available!

[Jan Steinman]

Apparently Winblows-only.

It sure would be nice if people would note platform requirements in their 
announcement, rather than forcing people to dig through their websites for such 
info.

I guess I should know better; if it doesn't list platform requirements, the 
developer hasn't thought beyond the dominant paradigm.


You have a very real relationship with God, a Force that responds to your every 
thought. Not that you are telling God how to run the universe; you are simply 
aligning with the Force for your good, or not aligning with it. The more 
aligned you are, the better your life goes. Unlike what you may have been told 
by fearful teachers, all God wants is for you to be happy. When that is all you 
want for yourself, that is what you will have. -- Alan Cohen
 Jan Steinman, EcoReality Co-op 


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Import from Quicken 2004 Mac?

[Jan Steinman]

I'm looking for ways to import QuickBooks 2010 Mac. I've only just started 
researching this, so feel free to RTFM me -- with a proper reference, of 
course!

I'll be wanting to set up a process to do this periodically (and hopefully, 
automagically) for new transactions.

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!

IIF/QIF seems to be a rather unusual format. Lacking a one-step MySQL import 
tool, does anyone know of good parsers and translators for IIF/QIF that may be 
useful?

Thanks in advance for any advice offered!


Science uses mathematics to predict the future; economics uses statistics to 
predict the past. -- Jeff Barton
 Jan Steinman, EcoReality Co-op 


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Import from Quicken 2004 Mac?

[Jan Steinman]

On 11 Aug 11, at 14:17, David Brian Chait wrote:

 The QIF file includes a lot of data aside from basic transactions, what 
 exactly are you trying to end up with at the end of the day? Simply a copy of 
 your QB data in Mysql?

That would be a good start. We don't need a complete duplicate, but in my 
experience, it's easier to get it all and winnow out the bits you don't want 
than to selectively import.

That said, we really only need the basic transaction info: date, payee, amount, 
memo, category, account from, account to.

This is to reconcile the chart of accounts (in Quick Books) with project 
management (in MySQL).

 From: Jan Steinman [mailto:j...@bytesmiths.com] 
 Sent: Thursday, August 11, 2011 2:15 PM
 To: mysql@lists.mysql.com
 Subject: Import from Quicken 2004 Mac?
 
 I'm looking for ways to import QuickBooks 2010 Mac. I've only just started 
 researching this, so feel free to RTFM me -- with a proper reference, of 
 course!
 
 I'll be wanting to set up a process to do this periodically (and hopefully, 
 automagically) for new transactions.
 
 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!
 
 IIF/QIF seems to be a rather unusual format. Lacking a one-step MySQL import 
 tool, does anyone know of good parsers and translators for IIF/QIF that may 
 be useful?
 
 Thanks in advance for any advice offered!
 
 
 Science uses mathematics to predict the future; economics uses statistics to 
 predict the past. -- Jeff Barton
  Jan Steinman, EcoReality Co-op 
 
 
 -- 
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql?unsub=dch...@invenda.com
 


Always do right. This will surprise some people and astonish the rest. -- Mark 
Twain
 Jan Steinman, EcoReality Co-op 


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Query Optimization

[Jan Steinman]

 From: Brandon Phelps bphe...@gls.com
 
 I am curious if there is any way I can better optimize the below query, as 
 currently it takes this query around 10 seconds to run but I am sure this 
 will get slower and slower as the database grows.

You need an index on `close_dt`.

 SELECT
   open_dt,
   close_dt,
   protocol,
   INET_NTOA(src_address) AS src_address,
   src_port,
   INET_NTOA(dst_address) AS dst_address,
   dst_port,
   sent,
   rcvd
 FROM connections
 WHERE
   dst_port = 80
 ORDER BY close_dt  DESC
 LIMIT 0, 30


Current farmers, who have become mere operators of machines and mixers of 
chemicals, may not have the skills to develop a local, sustainable agriculture. 
A new generation of farmers, numbering in the tens of millions, will need to be 
trained and relocated to rural communities. -- Pat Murphy
 Jan Steinman, EcoReality Co-op 


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Hungarian Notation [Was Re: Too many aliases]

[Jan Steinman]

 From: Johnny Withers joh...@pixelated.net
 
 http://en.wikipedia.org/wiki/Hungarian_notation

The original Hungarian notation... was invented by Charles Simonyi... who 
later became Chief Architect at Microsoft.

Ugh. That explains a lot!

The only time I let types intrude on names is with booleans, which I try to 
name with a state-of-being verb, such as has_paid, is_member, 
has_children, etc.

 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?


You can't do anything about the length of your life, but you can do something 
about its width and depth. -- H. L. Mencken
 Jan Steinman, EcoReality Co-op 


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

MySQL RAND() Issues [was Re: How to Shuffle data]

[Jan Steinman]

 From: Reindl Harald h.rei...@thelounge.net
 
 do not use any random-functions of mysql even if they exists
 http://bugs.mysql.com/bug.php?id=59253

Of course, it depends on the desired quality of randomness needed.

I'm using RAND() to select random quotations to put at the end of emails. I can 
easily repeat the process by re-selecting the Signature: menu in Apple Mail. 
Problem is, I often notice that doing so cycles through several similar 
signatures in a decidedly non-random way!

(You can demo this by sending email to qu...@bytesmiths.com, with a search 
term in the Subject: line.)

But of course, a nuclear plant is not going to melt down because of my 
signature line.

So RAND() can be useful, but it is not really very random, and should be used 
with caution. My guess is that it's using Knuth's linear congruential algorithm 
that has well-known problems, but that unfortunately has been hidden deep in 
system code libraries since Fundamental Algorithms was published in 1968.


World events tend to be driven by loose coalitions of economic, political, and 
military interests, which function like guilds of species in an ecosystem. 
These guilds generate patterns of events that meet the interests of these 
coalitions, without there being any unity of purpose or clear plan. When 
powerful players accept they are not all-powerful, they increase their 
effectiveness, but are also able to deny and cover any responsibility for the 
adverse outcomes of those actions. -- David Holmgren
 Jan Steinman, EcoReality Co-op 


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: ANN: AnySQL Maestro 11.7 released

[Jan Steinman]

 SQL Maestro Group announces the release of AnySQL Maestro 11.7, a
 powerful tool for managing any database engine accessible via ODBC
 driver or OLE DB provider (MySQL, SQLite, PostgreSQL, SQL Server,
 Oracle, Access, etc).

And once again, despite at least two requests to clearly state platform 
limitations, they've managed to neglect noting that this is a Windows-only 
product.


Our ignorance is not so vast as our failure to use what we know. -- M. King 
Hubbert
 Jan Steinman, EcoReality Co-op 


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: PHP Generator for MySQL 11.4 released

[Jan Steinman]

One thing they don't bother to tell you until you complete a useless download 
and discover that you can't open the file: this tool is Windows-only.

On 28 Apr 11, at 18:02, mysql-digest-h...@lists.mysql.com wrote:

 From: Sharl.Jimh.Tsin amoiz.sh...@gmail.com
 Date: 26 April 2011 18:33:04 PDT
 To: SQL Maestro Team sql.maes...@gmail.com
 Cc: mysql@lists.mysql.com
 Subject: Re: PHP Generator for MySQL 11.4 released
 
 
 very useful tool,is it free?
 
 Best regards,
 Sharl.Jimh.Tsin (From China **Obviously Taiwan INCLUDED**)
 


Almost all absurdity of conduct arises from the imitation of those whom we 
cannot resemble. -- Samuel Johnson
 Jan Steinman, EcoReality Co-op 


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: is your lack of a degree holding you back?

[Jan Steinman]

That has got to be one of the most surreal conversations I've seen on a mailing 
list!

Reminds me of the time I got tired of some boring lout on a Usenet group, and 
so I wrote up a markov-3 bot that would automatically respond to his 
postings, and then he got into a heated debate with himself...


Reality is that which, when you stop believing in it, doesn't go away. -- 
Philip K. Dick
 Jan Steinman, EcoReality Co-op 


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Good ODBC Reference? [was RE: Replacing MS SQL with MySql]

[Jan Steinman]

 From: David Brian Chait dch...@invenda.com
 
 No, what he is suggesting is that you use Access or MSSQL, and link Mysql to 
 either platform via ODBC so that you can use it indirectly.

Can someone recommend a good ODBC tutorial or reference?

I've tried a couple times to tie things together with it, and always got bogged 
down in details I didn't understand the need for.


We are like tenant farmers chopping down the fence around our house for fuel 
when we should be using nature's inexhaustible sources of energy - sun, wind 
and tide. I'd put my money on the sun and solar energy. What a source of power! 
I hope we don't have to wait until oil and coal run out before we tackle that. 
-- Thomas Alva Edison
 Jan Steinman, EcoReality Co-op 







--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Converting INNODB to file-per-table?

[Jan Steinman]

Our incremental backups seem to be filling with instances of ib_logfile1, 
ib_logfile2, and ibdata1.

I know that changing a single byte in a single INNODB table causes these files 
to be touched.

I put innodb_file_per_table in /etc/my.cnf, but apparently, that only causes 
new databases to be file per table, and it is older databases that are being 
touched in a minor way daily, causing gigabytes to be backed up needlessly.

Some time ago, someone posted a way to convert existing INNODB tables to file 
per table, but I am unable to find that.

Can someone please post that procedure again?

(I also welcome any you shouldn't be doing it that way comments, as long as 
they show a better way... :-)

This is for a fairly low-volume server, running on a Mac Mini with two 500GB 
disks.

Thanks!


In summary, the idea is to give all of the information to help others to judge 
the value of your contribution; not just the information that leads to 
judgement in one particular direction or another. -- Richard P. Feynman
 Jan Steinman, EcoReality Co-op 


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Converting INNODB to file-per-table?

[Jan Steinman]

Thanks, Rolando!

It's kind of a scary procedure (dump, drop, reload) that involves significant 
down-time, but I guess it's necessary.

On 11 Feb 11, at 10:24, Rolando Edwards wrote:

 I wrote an article in www.stackoverflow.com about how to convert absolutely 
 every InnoDB table to .ibd and permanently shrink the ibdata1 file 
 
 http://stackoverflow.com/questions/3927690/howto-clean-a-mysql-innodb-storage-engine/4056261#4056261
 
 Enjoy !!!
 
 Rolando A. Edwards
 MySQL DBA (SCMDBA)
 
 155 Avenue of the Americas, Fifth Floor
 New York, NY 10013
 212-625-5307 (Work)
 201-660-3221 (Cell)
 AIM  Skype : RolandoLogicWorx
 redwa...@logicworks.net
 http://www.linkedin.com/in/rolandoedwards
 
 
 -Original Message-
 From: Jan Steinman [mailto:j...@bytesmiths.com] 
 Sent: Friday, February 11, 2011 12:53 PM
 To: mysql@lists.mysql.com
 Subject: Converting INNODB to file-per-table?
 
 Our incremental backups seem to be filling with instances of ib_logfile1, 
 ib_logfile2, and ibdata1.
 
 I know that changing a single byte in a single INNODB table causes these 
 files to be touched.
 
 I put innodb_file_per_table in /etc/my.cnf, but apparently, that only 
 causes new databases to be file per table, and it is older databases that 
 are being touched in a minor way daily, causing gigabytes to be backed up 
 needlessly.
 
 Some time ago, someone posted a way to convert existing INNODB tables to 
 file per table, but I am unable to find that.
 
 Can someone please post that procedure again?
 
 (I also welcome any you shouldn't be doing it that way comments, as long as 
 they show a better way... :-)
 
 This is for a fairly low-volume server, running on a Mac Mini with two 500GB 
 disks.
 
 Thanks!
 
 
 In summary, the idea is to give all of the information to help others to 
 judge the value of your contribution; not just the information that leads to 
 judgement in one particular direction or another. -- Richard P. Feynman
  Jan Steinman, EcoReality Co-op 
 
 
 -- 
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql?unsub=redwa...@logicworks.net
 


You know you have reached perfection of design not when you have nothing more 
to add, but when you have nothing more to take away. -- Antoine de Saint-Exupery
 Jan Steinman, EcoReality Co-op 


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Auto-Increment Values in Mysql

[Jan Steinman]

 From: Adarsh Sharma adarsh.sha...@orkash.com
 
 I have an auto-increment column in Mysql database table. Let's say the column 
 has below values :

I'll echo what others have said.

Auto-increment is typically used to generate unique primary keys. If this 
column is your primary key, DO NOT change its value! PK = identity.

The value of a record is the key, the whole key, and nothing but the key, so 
help me Codd. :-)

If the auto-inc field is NOT the primary key, AND if it must have contiguous 
values, as others said, you probably want to have your business logic keeping 
track of it, rather than using auto-increment.

I know others have said the same thing, but sometimes it help to hear it put 
differently.


In my mind are many dwellings. Each of the dwellings we create ourselves - the 
house of anger, the house of despair, the house of self pity, the house of 
indifference, the house of negative, the house of positive, the house of hope, 
the house of joy, the house of peace, the house of enthusiasm, the house of 
cooperation, the house of giving. Each of these houses we visit each day. We 
can stay in any house for as long as we want. We can leave these mental houses 
any time we wish. We create the dwelling, we stay in the dwelling, we leave the 
dwelling whenever we wish. We can create new rooms, new houses. Whenever we 
enter these dwellings, this becomes our world until we leave for another. What 
world will we live in today? -- Don Coyhis
 Jan Steinman, EcoReality Co-op 


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Design: how to prioritise 1-to-many fields

[Jan Steinman]

 From: gvim gvi...@gmail.com
 
 I have a typical contact database which caters for multiple email addresses 
 with a distinct Email table keyed to a foreign key inside the Contact table, 
 ie. a 1-to-many relationship. However, I want to prioritise these Email 
 entries for a given Contact entry so all I can think of is to add a numeric 
 Priority field alongside the Address field inside the Email table. Is this 
 the best/standard solution or is there another way?

Ask yourself, what has a relationship with what. Do these Email table entries 
have relationships with each other?

Perhaps a better way would be for Email table entries to be threaded together 
in a linked list: Primary -- Secondary -- Tertiary -- ... rather than put 
an arbitrary number in there.


I hope we shall crush in its birth the aristocracy of our moneyed corporations 
which dare already to challenge our government to a trial of strength. -- 
Thomas Jefferson
 Jan Steinman, EcoReality Co-op 


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

RE: Bind-address use?

[Jan Steinman]

  intermittent connection problems (error 2013)
 
 Look at SHOW PROCESSLIST; when you're having a connection problem.   If you 
 see lots of unathenticated user in the list, then it means you're having 
 DNS problems.
 
 Typically the best way to handle this is to set skip-name-resolve, and using 
 ip addresses instead of hostnames in your mysql users for authentication.

Or fix your DNS, if it's under your control.

If you have a named(8) running on your LAN, you should not have DNS timeouts. 
It doesn't have to be authoritative -- set up a caching server on the same 
machine that runs mysql and talk to it via localhost.

Take it from someone who just had to change all my static IPs -- keep IPs out 
of your database connection code! (Oh, THAT won't happen to me! And then, it 
does...)-:


Some of the current attempts at energy accounting, like the triple bottom line, 
are an absolute a joke. They're an insult to children even in terms of their 
intellectual content, because they try and compare vague abstractions of social 
and environmental values — just dot pointed — against a completely econometric 
financial accounting system of an organization which is actually doing the 
work. -- David Holmgren
 Jan Steinman, EcoReality Co-op 


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: localhost vs domain for connection string

[Jan Steinman]

 From: Brent Clark brentgclarkl...@gmail.com
 
 Is there a difference if someone had to make the connection string the a 
 domain (hosts file entry makes the machine look at its ip) as opposed to just 
 using localhost.
 If so would a performance hit be incurred?

Using 'localhost' will always be faster, although perhaps imperceptibly so.


I look into the future because that’s where I am going to spend the rest of my 
life. -- George Burns
 Jan Steinman, EcoReality Co-op 


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: FW: [USN-1017-1] MySQL vulnerabilities

[Jan Steinman]

You seem to see threats as a black and white problem. Put enough what ifs 
in front of a statement, and nothing anywhere has any security at all.

On 15 Nov 10, at 23:30, mysql-digest-h...@lists.mysql.com wrote:

 From: Daevid Vincent dae...@daevid.com
 Date: 14 November 2010 13:22:02 PST
 To: mysql@lists.mysql.com
 Subject: RE: FW: [USN-1017-1] MySQL vulnerabilities
 
 
 I don't think you understand how many exploits work. Through some social
 engineering or plain brute force or rainbow tables I can get the user/pass
 for many typical users. I could also give you some code and tell you to run
 it and thereby my code is executed as an authenticated user without you
 even knowing it. And here's another statistic you might not be aware of --
 most hacking attempts are done BY people INSIDE a company, not external to
 it. It's extremely foolish and short-sighted to think that your system is
 safe unless it's in a glass jar and YOU are the ONLY user on it. Even
 then, YOUR account could be compromised too.


Thought is the sculptor who can create the person you want to be. -- Henry 
David Thoreau
 Jan Steinman, EcoReality Co-op 


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

RE: FW: [USN-1017-1] MySQL vulnerabilities

[Jan Steinman]

 From: Daevid Vincent dae...@daevid.com
 
 my point exactly. there is NONE. and if you don't patch your mysql as
 needed, then you will need a lot more help when you're hacked. ;-p

I note that the impact of every single one of these vulnerabilities was An 
authenticated user could exploit this to make MySQL crash, causing a denial of 
service.

That's a pretty low threat level. No mention was made of gaining or increasing 
access, nor of corrupting data.

First, you need an authenticated user who is trying to exploit a 
vulnerability to cause denial of service.

If you're allowing a publicly accessible pseudo-user to exploit such 
vulnerabilities through script injection, that's YOUR problem!

If an authenticated user causes a MySQL crash on my system, they get 
de-authenticated pretty quickly. :-)


No rational person can see how using up the topsoil or the fossil fuels as 
quickly as possible can provide greater security for the future, but if enough 
wealth and power can conjure up the audacity to say that it can, then sheer 
fantasy is given the force of truth; the future becomes reckonable as even the 
past has never been. -- Wendell Berry
 Jan Steinman, EcoReality Co-op 


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: How to extend innodb files?

[Jan Steinman]

 From: Jangita jang...@jangita.com
 
 I do not think there is anything wrong with having one huge file is there?

There is if you're doing incremental back-ups, in which case adding one byte to 
that file costs you 50GB of backup space.


You don't have to take insults personally. You can sidestep negative energy; 
you can look for the good in others and utilize that good, as different as it 
may be, to improve your point of view and to enlarge your perspective. -- 
Stephen R. Covey
 Jan Steinman, EcoReality Co-op 







--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

RE: Best method to keep totals

[Jan Steinman]

 From: Jerry Schwartz je...@gii.co.jp
 
 IMNSHO, never store dynamic data in a field unless you absolutely have to.

I agree, and yet, it's so darned handy if it's a calculation you need quite 
often.

In FileMaker Pro (hold the boos, please :-) you can have calculated fields -- 
a pseudo field that holds references to other fields in an equation.

To do something similar in MySQL, I've used views (which is somewhat clumsy), 
but is there a better way? Or is this necessarily part of domain knowledge 
programming in your interface language?



 Jan Steinman, EcoReality Co-op 


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Best method to keep totals

[Jan Steinman]

On 2 Sep 10, at 13:58, Jerry Schwartz wrote:

 From: Jan Steinman [mailto:j...@bytesmiths.com]
 
 From: Jerry Schwartz je...@gii.co.jp
 
 IMNSHO, never store dynamic data in a field unless you absolutely have to.

...

 To do something similar in MySQL, I've used views (which is somewhat clumsy),
 but is there a better way?
 
 [JS] Wouldn't the appropriate place for the calculation be... 
 in a store procedure that they share? That's basically 
 the same as a calculated field.

I've never played with stored procedures. Can you point me to a tutorial? Or 
should I just Google for it?



 Jan Steinman, EcoReality Co-op 


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Why is MySQL always linked to PHP?

[Jan Steinman]

On 22 Jul 10, at 01:25, Anirudh Sundar wrote:


Jay,


Actually, that was my comment.

Do not compare it with C. C is a middle -level System programming  
language.


PHP's syntax is very much like C.

My point, which I guess wasn't clear, is that one of the reasons PHP  
is popular (among the many pointed out by others) is that its syntax  
resembles a language that many people know, as I pointed out:



PHP looks a lot like C, and people are taught C in college.




I do not believe in the creed professed by the Jewish Church, by the  
Roman Church, by the Greek Church, by the Turkish Church, by the  
Protestant Church, nor by any Church that I know of. My own mind is my  
own Church. -- Thomas Paine

 Jan Steinman, EcoReality Co-op 


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Why is MySQL always linked to PHP?

[Jan Steinman]

On 22 Jul 10, at 21:01, Anirudh Sundar wrote:


SO ONE CANNOT COMPARE PHP WITH C.


I can compare anything I want to -- I took a poetry class in college!  
(Her teeth were like the stars, 'cause they came out at night. :-)


If you STOP SHOUTING, people might take you more seriously.


There are only two ways to look at life: One is as if nothing is a  
miracle. The other is as if everything is a miracle. -- Albert Einstein

 Jan Steinman, EcoReality Co-op 


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Why is MySQL always linked to PHP?

[Jan Steinman]

From: Jay Blanchard jblanch...@pocket.com

You always have to use the right tool for the job though. The question
is are we teaching the inexperienced programmers what the right tools
are?


I doubt it!

PHP looks a lot like C, and people are taught C in college.

So instead of working with something more abstract (like Smalltalk,  
Ruby, et. al.) they slog through the bits with a relatively low-level  
language like PHP.



An idea that is not dangerous is unworthy of being called an idea at  
all. -- Elbert Hubbard

 Jan Steinman, EcoReality Co-op 


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Myisam advantages

[Jan Steinman]

From: P.R.Karthik prk...@gmail.com

 I am newbie to mysql can i know the advantages of myisam storage  
engine

and some of its special features.


Works better with file-based incremental backup systems.

With InnoDB, you end up backing up a humongous file of all your InnoDB  
tables, even if only one byte in one field of one table of one  
database was touched.



There are only two ways to look at life: One is as if nothing is a  
miracle. The other is as if everything is a miracle. -- Albert Einstein

 Jan Steinman, EcoReality Co-op 


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: INSERT with auto increment

[Jan Steinman]

From: David Stoltz dsto...@shh.org

In mySQL, if I expressly give it a value, like INSERT INTO TABLE1
VALUES(17,'stuff') - it works fine. But if I remove the 17, it says I
don't have a matching number of columns.


Use NULL for the autoinsert column.


I made it a rule to forbear all direct contradictions to the  
sentiments of others, and all positive assertion of my own. I even  
forbade myself the use of every word or expression in the language  
that imported a fixed opinion, such as certainly, undoubtedly,  
etc. I adopted instead of them I conceive, I apprehend, or I  
imagine a thing to be so or so; or so it appears to me at present.  
When another asserted something that I thought an error, I denied  
myself the pleasure of contradicting him abruptly, and of showing him  
immediately some absurdity in his proposition. In answering I began by  
observing that in certain cases or circumstances his opinion would be  
right, but in the present case there appeared or seemed to me some  
difference, etc. I soon found the advantage of this change in my  
manner; the conversations I engaged in went on more pleasantly. The  
modest way in which I proposed my opinions procured them a readier  
reception and less contradiction. I had less mortification when I was  
found to be in the wrong, and I more easily prevailed with others to  
give up their mistakes and join with me when I happened to be in the  
right. -- Benjamin Franklin

 Jan Steinman, EcoReality Co-op 


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

RE: MyISAM better than innodb for large files?

[Jan Steinman]

From: Gavin Towey gto...@ffn.com

InnoDB should be your default for all tables, unless you have  
specific requirements that need myisam.  One specific example of an  
appropriate task for myisam is where you need very high insert  
throughput, and you're not doing any updates/deletes concurrently.


A couple other things: InnoDB does relations better, MyISAM does  
search of text fields.




If we can control fuel we can control the masses; if we can control  
food we can control individuals. -- Henry Kissinger

 Jan Steinman, EcoReality Co-op 


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: ANN: PHP Generator for MySQL 10.3 released

[Jan Steinman]

This appears to be Windows-only, at least from the .exe file that  
gets downloaded.


I looked all over the site for requirements and found none.

It would be nice for all of us Windows-resisters if that requirement  
had been stated somewhere up-front.


Please correct me if I'm wrong!


Feelings of inferiority and superiority are the same. They both come  
from fear. -- Robert Anthony

 Jan Steinman, EcoReality Co-op 


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Query question

[Jan Steinman]

I have three tables that work together.

s_product is a list of farm products with an autoincrementing ID.

s_product_market_prices is a list of market pricings, obtained from  
various sources. Each one is dated and refers to exactly one s_product  
record via its ID.


s_product_harvest is a list of harvests, including s_product.ID,  
amount, and date/time.


Now I want to generate a report showing the harvest sums and their  
values, based upon an appropriate market pricing. It was all happy  
when I only had one pricing per product, but then I added new dated  
pricings, and got unexpected results.


I'd be happy if the pricings used were simply in the same year as the  
harvest, but it seems like it picks a random one when I do a LEFT JOIN  
on these tables. When I put additional AND clauses on the join to get  
it to pick a price within the desired date range, it seems to affect  
the number of harvests summed, and they are reduced somehow.


(Apologies for not fully qualifying the unexpected results; I'm hoping  
someone can look at this and quickly show me something stupid I've  
done! :-)


Here's the report:
http://www.EcoReality.org/wiki/2009_harvest

which is generated by the following SQL:

SELECT
 product AS ID,
 MAX(s_product.name) AS Name,
 SUM(quantity) AS Quantity,
 MIN(harvest.units) AS Units,
 CONCAT('$', ROUND((SUM(quantity) * prices.price), 2)) AS Value,
 prices.market_type AS `R-W`,
 COUNT(*) AS Harvests,
 MIN(date) AS Begin,
 MAX(date) AS End
FROM s_product_harvest harvest
INNER JOIN s_product on s_product.ID = harvest.product
LEFT OUTER JOIN s_product_market_prices prices ON ID = prices.product_ID
WHERE date = '{{{1}}}-01-01' AND date = '{{{1}}}-12-31 23:59:59'  
GROUP BY s_product.name


(Note that the token {{{1}}} is replaced with a four-digit year,  
like 2009.)


My first impulse was to change the LEFT OUTER JOIN to:

 s_product_market_prices prices ON ID = prices.product_ID AND  
prices.price_date = '{{{1}}}-01-10' AND prices.price_date =  
'{{{1}}}-12-31 23:59:59'


So that the prices table would only join for the desired year.

What am I doing wrong here?

Following are schemas of the three tables:

CREATE TABLE IF NOT EXISTS `s_product` (
`ID` int(10) unsigned NOT NULL auto_increment,
`super` int(11) default NULL COMMENT 'generalization',
`name` varchar(31) character set utf8 NOT NULL,
`units`  
enum 
('kilograms 
','grams','pounds','ounces','liters','each','cords','bales') character  
set utf8 NOT NULL default 'kilograms' COMMENT 'preferred unit',

`description` varchar(255) character set utf8 NOT NULL,
PRIMARY KEY (`ID`),
UNIQUE KEY `Name` (`name`),
KEY `Description` (`description`) )
ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='list of  
EcoReality farm products' AUTO_INCREMENT=86 ;


CREATE TABLE IF NOT EXISTS `s_product_harvest` (
`date` datetime NOT NULL COMMENT 'Date and time of harvest.',
`product` int(11) NOT NULL default '53',
`resource` varchar(255) character set utf8 NOT NULL COMMENT  
'Particular animal or tree, etc.',

`quantity` decimal(10,2) NOT NULL default '0.80',
`units`  
enum 
('kilograms 
','grams','pounds','ounces','liters','each','cords','bales') character  
set utf8 NOT NULL default 'kilograms',
`who1` smallint(5) unsigned NOT NULL default '2' COMMENT 'Who  
harvested this resource?',
`who2` smallint(5) unsigned NOT NULL default '4' COMMENT 'Who helped  
harvest this resource?',

`notes` varchar(255) character set utf8 NOT NULL,
KEY `product` (`product`),
KEY `date` (`date`),
KEY `who1` (`who1`,`who2`) )
ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin  
COMMENT='historical list of EcoReality farm products harvested';


CREATE TABLE IF NOT EXISTS `s_product_market_prices` (
`product_ID` int(11) NOT NULL,
`price_date` date NOT NULL,
`price_source` varchar(255) character set utf8 NOT NULL,
`market_type` enum('retail','wholesale') character set utf8 NOT NULL  
default 'wholesale',

`price` float NOT NULL,
`units` enum('kilograms','grams','pounds','ounces','liters','each')  
character set utf8 NOT NULL default 'kilograms' COMMENT 'change in  
sync with s_product_harvest.units',

PRIMARY KEY (`product_ID`,`price_date`) )
ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='market  
pricing information for EcoReality products';


Thanks for whatever help you can offer!

The Apocalypse has Four Horsemen: climate change, habitat destruction,  
industrial agriculture, and poverty. Each Horseman holds a whip called  
Growth in his hand. None can be stopped unless all are stopped. --  
David Foley

 Jan Steinman, EcoReality Co-op 


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

CSV pain and suffering with punctuation and non-ASCII characters

[Jan Steinman]

I am trying to import a database of notable quotations. It is really  
quite simple, but I'm finding importing via phpMyAdmin is (IMHO)  
erroneous.


The database came from FileMaker Pro 7, and there are no options for  
changing the output. There are two choices:
1) tabs between fields (with no opportunity to include the tab  
character in a field), and
2) CSV, which double-quotes fields and has commas in-between, which  
apparently doubles embedded quotes.


I am using UTF-8 bin as the character set, and as the encoding for all  
fields.


The problem is that numerous punctuation characters appear to  
terminate the reading of a field, whether imported as a TAB file or as  
a CSV file.


For example, importing a quote with a Context field of:
The Hitchhiker's Guide to the Galaxy

results in a field containing:
The Hitchhiker

whether I use TAB or CSV. Note that this in an ASCII single quote  
character, not something exotic. I have also noted this on fields that  
contain curly quotes.


I am using phpMyAdmin's CSV using LOAD DATA option, checking  
Replace table data with file, terminating fields with a comma,  
enclosing fields with double quotes, and blanking (or leaving the '\',  
it doesn't matter) the Fields escaped by field.


It seems to me that using CSV, with double-quoted, comma separated  
fields should tell the import process, Hands off until you see  
another double quote! And that using TAB format, nothing between TAB  
characters should be interpreted. Why are characters inside the double  
quotes or tabs being interpreted?


I have not tried mysqlimport from the command line, assuming (perhaps  
incorrectly) that phpMyAdmin was simply passing things along, and not  
interpreting them.


Ah, one light in the tunnel: my Quote field was TEXT. Changing it to  
BLOB preserves non-ASCII characters, but I still see the strange  
behaviour noted above with a single quote, which was in a VARCHAR(256)  
UTF8 field.


Thanks for whatever help you can offer!

 My pants just went on a wild rampage through a Long Island  
Bowling Alley! --  Zippy the Pinhead

 Jan Steinman 


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: phpMyAdmin links?

[Jan Steinman]

Is there a way to have a permalink to pages in phpMyAdmin,  
particularly record editing pages?


I successfully did it -- for a while -- by simply copying the URL when  
I was on a record editing page and replacing the obvious key GET  
parameter with a variable. But that URL has a GET parameter called  
token with an opaque identifier that I'll bet is a session handle.  
You can't go to the record without it, and if it's included, it stops  
working after some period of time.


I'm doing some custom web database work, and for debug and admin  
purposes only, I want to create a link whenever a primary key is shown  
that will allow let me at the raw data, dammit editing.


Thanks for whatever help you can offer, including suggesting a  
different list or approach or tool. (I haven't looked for a phpMyAdmin  
list yet.)


 The light at the end of the tunnel is a man with a flashlight  
yelling, Go back! Go back! -- Sol Stein 

 Jan Steinman http://www.VeggieVanGogh.com 




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Techniques for queries on a series?

[Jan Steinman]

I often need a pattern where one record refers to the one before it,  
based on the order of some field.


For example, a vehicle log, in which the field odometer is the  
current odometer reading upon an event, such as a trip completion,  
fueling, maintenance, etc. Very often one wants to calculate the  
difference in odometer readings, which means accessing two consecutive  
records, when ordered by odometer. I currently put both readings in  
one record, which seems an unnecessary de-normalization and extra work  
in data entry.


Another example: an amortization database, where the value of the loan  
principle depends on the payment, interest rate, but also the previous  
record's principle. Someone makes a payment on a loan, which needs to  
be entered along with the declining balance, but that depends on the  
balance of the previous record.


Quite often, I see this pattern in time series data. Data is logged  
and time-stamped, and many queries depend on the difference in time- 
stamps between two consecutive records. For example, milk production  
records: with milk goats, if milking is early or late, the amount of  
milk is lower or higher. I need to do an analysis of short-term milk  
production, which means daily production needs to be normalized for  
variations in time, which means I need to refer to time and volume  
deltas from two consecutive records, ordered by time.


Are there some good techniques for dealing with this common pattern in  
SQL? Or do I need to do it all with two queries and a programming  
language?


Pointers to good web references are welcome. I have googled quite a  
bit, and haven't turned up anything apropos.


Thanks for whatever insight you can offer!

 A virus has marked this email as being virus-free! 
 Jan Steinman http://www.VeggieVanGogh.com 



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Techniques for queries on a series?

[Jan Steinman]

On 17 Nov 09, at 10:41, Peter Brawley wrote:

 I often need a pattern where one record refers to the one before  
it, based on the order of some field.


Some ideas under Sequences at http://www.artfulsoftware.com/infotree/queries.php 
.


Thanks, Peter! What a marvellous resource!


 You know what? What makes our economy grow is energy. And  
Americans are used to going to the gas tank, and when they put that  
hose in their, uh, tank, and when I do it, I wanna get gas out of it.  
And when I turn the light switch on, I want the lights to go on, and I  
don't want somebody to tell me I gotta change my way of living to  
satisfy them. Because this is America, and this is something we've  
worked our way into, and the American people are entitled to it, and  
if we're going improve our standard of living, you have to consume  
more energy. -- Senator Chuck Grassley (R-IA) 

 Jan Steinman, http://www.VeggieVanGogh.com 




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Perl vs. PHP with MySQL - performance?

[Jan Steinman]

From: Angel Flow [EMAIL PROTECTED]

Would like to ask people's thoughts on whether Perl or
PHP has higher performance with MySQL.

Since both of these use memory buffers for communication, I think performance for all 
but the most trivial cases will be determined by the disk-speed-constrained database, 
NOT by the glue.

Assuming your time is worth anything, pick your language based on clean design, speed 
of implementation, and maintenance effort, then throw hardware (relatively cheap, 
compared to human resource) at the problem if it's too slow.

 SQL SQL SQL SOL 
-- 
: Jan Steinman -- nature Transography(TM): http://www.Bytesmiths.com
: Bytesmiths -- artists' services: http://www.Bytesmiths.com/Services
: Buy My Step Van! http://www.Bytesmiths.com/van

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Multiple MySQL db's on one machine

[Jan Steinman]

From: Paul DuBois [EMAIL PROTECTED]

At 16:04 -0800 12/9/02, tmb wrote:

I thought MySQL created a seperate directory for each
db  put each db's files in their respective folder...

That's correct.

However, a small complication to my answer:

Another small complication: the individual DB directories all need to be in the same 
parent directory. However, they can be symlinks to other, unrelated locations.

- SQL SQL SQL SOL -
-- 
: Jan Steinman -- nature Transography(TM): http://www.Bytesmiths.com
: Bytesmiths -- artists' services: http://www.Bytesmiths.com/Services
: Buy My Step Van! http://www.Bytesmiths.com/van

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

User Install Bug (Was: mysqlbug)

[Jan Steinman]

From: amit parikh [EMAIL PROTECTED]

i install binary version of the mysql .
i skipped first 2 stage while installing (that is creating user  group) and directly 
run binary script file.

when i run this command, i got following error.
mysql -u root mysql
ERROR 2002: Can't connect to local MySQL server through socket 
'/var/lib/mysql/mysql.sock' (111).
How to solve this error.

Uhm, re-install without skipping any steps?

-- 
: Jan Steinman -- nature Transography(TM): http://www.Bytesmiths.com
: Bytesmiths -- artists' services: http://www.Bytesmiths.com/Services
: Buy My Step Van! http://www.Bytesmiths.com/van

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php