In response to: On badly behaved crawlers

Comment from: Steve [Visitor]
Steve

Just to mention that we are now 2014 and magpie still hasn’t improved. My advice: ban it from your site right away. I get about 3 - 5 hits a second from that piece of junk when they decide to index my site and I’m tired of paying for their incompetence.

01/12/14 @ 19:21

In response to: MySQL 101 - Sorting and Searching: ORDER BY, WHERE, GROUP BY

Comment from: [Member]
aj

Hi David,

The JOIN statement creates a complex statement that builds on previous components. If you look at the first join (between author and book) you now have effectively a meta-table that includes fields from both. The next join needs only to reference fields from the meta-table, which in this case includes fields from book as well as author.

This is no to say that you couldn’t restructure the query to match your supposition, however you’d need to think carefully about the direction (LEFT/RIGHT) of the join as I’m using LEFT JOIN knowing that it will always provide me with a result provided there is a valid author, regardless of if that author has a book or publisher. If I structured the query such that book was before the FROM then I would need a valid book to list an author.

Note that I’m using the term meta-table as a convenience. The underlying database doesn’t need to create a separate structure.

11/08/14 @ 16:47

In response to: MySQL 101 - Sorting and Searching: ORDER BY, WHERE, GROUP BY

Comment from: David [Visitor]
David

Hello,

in the previous episode to this, a SELECT query was made with 3 joins, namely:

SELECT `title`,
CONCAT_WS(’ ‘,`author`.`first_name`,`author`.`last_name`) AS `author`,
`publisher`.`name` AS `publisher`,
`format`.`name` as `format`,
`price`, `isbn` FROM `book`
JOIN `author` ON `book`.`author` = `author`.`id`
JOIN `publisher` ON `book`.`publisher` = `publisher`.`id`
JOIN `format` ON `book`.`format` = `format`.`id`;

As I understand it, the tables `author`, `publisher`, and `format` each have a separate link back to the book table. Is that correct? Now in this episode you have the query:

SELECT CONCAT_WS(’ ‘,`author`.`first_name`,`author`.`last_name`) AS `author`,
`book`.`title` FROM `author`
LEFT JOIN `book` ON `author`.`id` = `book`.`author`
LEFT JOIN `publisher` ON `publisher`.`id` = `book`.`publisher`
WHERE `publisher`.`name` = ‘Bantam’;

I can see in the first join that the `book` table is linked back to the `author` table. But the second join to `publisher` is a join to the `book` table. I the thought that with multiple joins all tables following JOIN statements had to link back to the initital table referred to in the FROM statement.

08/08/14 @ 10:09

In response to: In memory of Eileen Mary Donnison (nee Cain)

Comment from: Boel [Visitor]
Boel

Adam, what a lovely tribute to your mother. She sounds like someone I’d like to have met. My condolences.

Boel

07/05/14 @ 09:50

In response to: In memory of Eileen Mary Donnison (nee Cain)

Comment from: Sarah [Visitor]
Sarah

Thank you for sharing this, she was an amazing Gran and an inspiring woman.

05/05/14 @ 22:28

In response to: MySQL 101 - Retrieving data: SELECT and JOIN

Comment from: [Member]
aj

Thanks, Jose, I’ve updated the links.

26/02/14 @ 18:33

In response to: MySQL 101 - Retrieving data: SELECT and JOIN

Comment from: Jose [Visitor]
Jose

Hey,

Great guides for starters (like me) only comment so far is about the website. The link for your ‘last episode’ has an extra http// in it (so it doesn’t work unless you remove it). And on your last episode (creating the database) you don’t seem to have a hyperlink to the next episode - just text saying what will be covered. Thought I’d comment just so people who are looking for these in order will have an easier time (I googled for this page to find it when the hyperlink on the previous wasn’t working).

Thanks again!
Jose

11/02/14 @ 02:53

In response to: MySQL 101 - Referential Integrity

Comment from: SQL dev [Visitor]
SQL dev

I have rarely seen programming taking advantage of Referential Integrity constraint e.g. CASCADE on UPDATE. It’s immensely useful specially in large application where data is subject to change by multiple people. Here is another good example of referential integrity in MySQL http://javarevisited.blogspot.com.ar/2012/12/what-is-referential-integrity-in-database-sql-mysql-example-tutorial.html which explains cascading.

09/01/13 @ 14:30

In response to: Predictions of a Long Hot Summer

Comment from: [Member]
KC

They don’t call the Redbank Nature Reserve the Bealiba Second Cemetery for nothing (mine shafts everywhere…)

23/11/12 @ 15:27

In response to: Predictions of a Long Hot Summer

Comment from: Evan [Visitor]
Evan

“I’ve got a plan for a spot of stress relief.”

Yes, but the ground will be too hard for grave digging…

23/11/12 @ 12:19

In response to: Ducklings - Day 3

Comment from: [Member]
KC

Alas these days, some of the excess boys do end up on the table.

Whilst I end up in the back room pretending they aren’t on the table…. others in the house are less conflicted…

19/11/12 @ 15:24

In response to: Ducklings - Day 3

Comment from: Sally [Visitor]
Sally

I guess they’re not destined for a yummy Duck L’orange when they grow up?

It’s ok - I’m slapping myself - bad Sally!

16/11/12 @ 18:25

In response to: On badly behaved crawlers

Comment from: Neil [Visitor]
Neil

Just an update…We applied the robots.txt fix and noticed a distinct drop in origin traffic and CPU usage.

Not all magpie-crawler connections have gone but most have. We will only block the IPs as the last resort but it may come to that if we can’t get rid of them all via robots.txt.

Seems magpie is (as stated above) simply overly aggressive.

10/09/12 @ 19:36

In response to: Open for Business

Comment from: [Member]
KC

Oh the conversation was had…..

31/08/12 @ 12:25

In response to: Open for Business

Comment from: Evan [Visitor]
Evan

I dare you to change the sign to say, “Fresh Eggs. Direct from a chooks bum to you.”

31/08/12 @ 12:23

In response to: Dealing with pre-emptive grief

Comment from: [Member]
aj

Hi Eli,

Unfortunately I ended up having to make the decision, as I knew I would. One thing you learn when owning pets is that you will outlive them, and that more often than not you are going to have to make some hard decisions. What you have to remember is that you have to act in the best interests of your pet. It is not betrayal to end suffering, although the point at which you decide that quality of life has gone will be different for different people and different situations. In fact, I see it as a duty, a responsibility of ownership.

I hope that your situation is easy on both of you, but please, don’t shy away from the hard decision if and when it becomes necessary. You will have doubts, that is only human - but if you love your dog the way it comes across from your words you will know when it is time to act.

30/08/12 @ 11:17

In response to: Dealing with pre-emptive grief

Comment from: Eli [Visitor]  
Eli

I took my twelve yr old golden retriever for a checkup and although she had been put on a reduced calorie diet and lost some weight and her arthritis was starting she had some really good days. The shock was the blood test showed she has leukemia and will die in 30 to 60 days. She shows no signs except the blood counts, eats well, no stomach problems but has anemia which slows her down. I am hoping I will not have to make that choice unless she stops eating or drinking as I feel I would feel I am betraying the animal that loves me most and will do anythings to please me. I understand how you feel pray so that he goes before you have to make a choice and he does not suffer.

30/08/12 @ 09:21

In response to: On badly behaved crawlers

Comment from: Neil [Visitor]
Neil

Hi all

The magpie crawler is also causing us some nasty issues on several large sites. The main issue for us is in fact that they persist in crawling non-existent pages, one of which alone is causing us over 20,000 404s per day.

We are taking steps now to ban magpie from our servers.

Cheers
Neil

24/08/12 @ 17:51

In response to: MySQL 101 - Creating your first database

Comment from: [Member]
aj

You are mistaken on a few fronts here. Firstly there is no error in the syntax, and the syntax used is ‘best practice’ for MySQL. Secondly, there is no single quote, they are backticks. Lastly it is not recommended to leave these out, as you set yourself up for problems in the future.

As to captcha - I’ll look into that.

30/07/12 @ 11:54

In response to: MySQL 101 - Creating your first database

Comment from: vaz [Visitor]
vaz

Note - the syntax that you used is wrong

You have mentioned : CREATE DATABASE “bookshop’;

Firstly - one can use either small oor caps for the create database

Secondly - if you give a single quote it will throw a ERROR 1064

Therefore, the correct syntax is as follows

create database sonic;
(no quotes)

Best….
(also one comment on the website caoptcha code - there is some problem, it refreshes the some captcha multiple times and does not allow to post, until one reloads the page and gets a new captcha. Also your captcha is little strange characters and one cant figure out the letters)

29/07/12 @ 06:36

In response to: Doesn't Everybody have an incubator on their coffee table?

Comment from: [Member]
KC

I don’t think temperature has much affect on chook hatchings, mostly it’s a quantity game.

In order to get enough hens we just have to hatch more eggs than we can when “patiently” waiting for somebody to go clucky, stick and stay. We’ve had a couple of chooks recently that just gave up before the incubation period was completed and the goose last year kept pushing the eggs away so she could hatch more straw….. Once she starts laying again in Spring we can get goose eggs into this incubator as well as ducks.

13/06/12 @ 10:48

In response to: Doesn't Everybody have an incubator on their coffee table?

Comment from: Sally [Visitor]
Sally

So chook eggs aren’t like crocodile eggs - the temperature they’re incubated at defines the sex?

12/06/12 @ 22:29

In response to: On badly behaved crawlers

Comment from: Dave [Visitor]  
Dave

They’re being a nuisance for me as well. Whole subnet blocked now, for all servers.

15/03/12 @ 21:00

In response to: How to Write Crappy Code

Comment from: JC [Visitor]
JC

Here are some more:
Don’t indent your code, or better yet, indent it randomly.
Put inexplicable white space in here and there to make the file longer.
Always leave out of date ‘to-do’ comments in place, and never change a comment when you change the code.
If you must comment your fixes, be sure to leave out any meaningful references like CR or QC numbers, eg “// change (jc)”
Finally - and it’s not really a coding issue, but it does make for a good laugh - tell your boss that everything’s fixed and he can tell his client to relax, then check in the wrong version of the file!

29/11/11 @ 20:15

In response to: MySQL 101 - Replication

Comment from: Seppo Jaakola [Visitor]
Seppo Jaakola

Thanks for the writeup!

Galera Replication is missing in your reference list. Galera provides synchronous multi-master clustering for MySQL/InnoDB. And it is true multi-master, you can write to any row at any cluster node - just like MySQL/Cluster but works on InnoDB. And fully open source, downloads here: http://www.codership.com/downloads/download-mysqlgalera/

02/11/11 @ 21:06

In response to: MySQL 101 - Replication

Comment from: [Member]
aj

Thanks Giuseppe, I should have remembered that. If I’ve forgotten anyone else, please chime in with a comment.

01/11/11 @ 19:01

In response to: MySQL 101 - Replication

Comment from: Giuseppe Maxia [Visitor]
Giuseppe Maxia

Multiple masters replication, without the limitations found in MySQL, can be done easily using Tungsten Replicator (see http://code.google.com/p/tungsten-replicator/wiki/TRCMultiMasterInstallation#Multi-Master_Installation).

01/11/11 @ 18:53

In response to: From MySQL to SkySQL - Reflections

Comment from: [Member]
aj

Hi Henrik,

From my personal perspective I’d say that it isn’t a matter of sites being proactively shut down, but a “death of a thousand cuts". Ignoring the community sites is easy, they don’t provide any direct bottom-line benefit, they require resources that could be deployed in efforts that are more aligned to revenue generation, and they allow a platform for uncontrolled commentary. A company that is so tightly centralised as Oracle doesn’t have that sort of platform in their DNA. Unlike MySQL AB, which understood that the community would become their customer base, I’m not sure Oracle sees the benefit of Community. Users, yes, but users in Oracle’s thinking are paying customers.

We are already seeing signs of the lack of commitment to Community. Take a look at the forums, or MySQL Forge and tell me if you see signs of active engagement and a commitment to the community.

As to who should build a replacement and what it would be called, there are issues there. The name “MySQL” is a registered trademark, and if the site attracted a huge following you can bet the Oracle lawyers would take a dim view of any use of the trademark. However there are precedents and many products have community-run support sites that are not affiliated with the company producing the product. It could be done. But who should do it? It probably doesn’t matter much. As long as the future was assured and the site was unlikely to be dropped due to pressure from parties with their own agenda, who actually provides the platform is almost irrelevant.

The main thing required for any Community site is active moderation and management. I engaged with the community to put in place moderators for the MySQL forums, rather than have all of the moderation done by the ever-shrinking internal resources (the Community team was effectively decimated long before I left, and as mentioned, the web team was not doing much better). What I couldn’t do was give them the resources they needed to effectively take control of the site. I think that any new site should have community engagement and have a means by which the community can effectively manage the site. There are limits, of course, but making the site as much a community resource as possible while protecting it from misuse is a worthwhile although delicate balancing act.

20/10/11 @ 19:35

In response to: From MySQL to SkySQL - Reflections

Comment from: Henrik Ingo [Visitor]
Henrik Ingo

Thanks for sharing!

Suppose some community site is actually shut down in the future… In your opinion, what should we do then? Who should build a replacement site and what would it be called?

20/10/11 @ 18:32

In response to: MySQL 101 - Sorting and Searching: ORDER BY, WHERE, GROUP BY

Comment from: [Member]
aj

Francis, it is nearly impossible to answer the question without knowing more about your setup, and this probably isn’t the right forum. Millions of records should not be an issue, there are plenty of sites out there with many millions of records in MySQL databases not having issues with single table queries.

You really should first check that you are using indexes effectively. This is pre-empting a future episode, however you can use the EXPLAIN syntax to find out how the query is likely to use indexes. The episode “Creating your first database” does try to explain the use of indexes and why you need them. You might want to take another look at that.

If you want more information, there are plenty of places where you can get some interactive help. There is the #mysql channel on irc.freenode.net, and the official forums site at http://forums.mysql.com/.

13/09/11 @ 20:09

In response to: MySQL 101 - Sorting and Searching: ORDER BY, WHERE, GROUP BY

Comment from: francis tudlong [Visitor]  
francis tudlong

Hello i have this millions of records and im having problems with the retrieval of the data because its very slow. even for just fetching a single table what is the problem and what is the solution? any idea thanks in advance

13/09/11 @ 19:52

In response to: MySQL 101 - Retrieving data: SELECT and JOIN

Comment from: [Member]
aj

Thanks, Sheeri, a very good point. Hopefully I explained both the correct way and why the short version was used in that particular example, however you are correct, people will often just use the example code.

09/09/11 @ 23:09

In response to: MySQL 101 - Retrieving data: SELECT and JOIN

Comment from: Sheeri [Visitor]
Sheeri

I recommend naming fields in your INSERT. Not doing so saves keystrokes but at the expense of having your code break if you add a field.

Remember that folks will copy and paste your example, so you have to set a good one!

09/09/11 @ 21:51

In response to: Book Review: Overclocked - Cory Doctorow

Comment from: Taylor [Visitor]
Taylor

Wowzers, reading your comments about this book definitely wants to make me get it. I must admit that I’m not into SF books BUT I did read “Ender’s Game” a few years ago and I LOVED IT! It absolutely blew my mind. I had to read it for school and thank God I did or else I would have never stumbled upon it. I even told my younger brother to read it and he did. He also loved it. Either way. I might just have to trust you and get “Overlocked”

And I can’t believe you got it signed! And haha you read it in less than 24 hours. Wow, trooper!

-Taylor

23/06/11 @ 06:24

In response to: On badly behaved crawlers

Comment from: SergeiS [Visitor]  
SergeiS

I also had to block access via .htaccess

21/06/11 @ 20:09

In response to: On badly behaved crawlers

Comment from: Andrew [Visitor]
Andrew

Thanks for the tip, their “crawler” still misbehaves.

12/06/11 @ 01:06

In response to: How to Write Crappy Code

Comment from: George Nagy [Visitor]
George Nagy

Great rant! I’ve run across similar code and want to add one more way to write crappy code: Use meaningless file names!

I’ve had to work on code bases with names like leftFish.inc.php (And not on code that had anything to do with aquariums either :( )

21/05/11 @ 03:41

In response to: The First Day of the Rest of My Life

Comment from: Max Mether [Visitor]
Max Mether

Welcome Adam!

04/04/11 @ 17:59

In response to: The First Day of the Rest of My Life

Comment from: Ivan Zoratti [Visitor]
Ivan Zoratti

Adam,

A very warm welcome!
I am sure you will enjoy working at SkySQL!

Let’s make our first days a great and enjoyable experience to share with all!

-ivan

03/04/11 @ 18:52

In response to: The First Day of the Rest of My Life

Comment from: Kaj Arnö [Visitor]
Kaj Arnö

Welcome Adam!

03/04/11 @ 16:39

In response to: Time to Lock the Doors again...

Comment from: [Member]
KC

I was going to plant a second run - but I’m bloody glad I didn’t now. We’ve got a few plants that have just started producing now as they were under the locust proofing shadecloth for a while - so they are behind. Either way I suspect we won’t be craving them for a while.

29/01/11 @ 17:49

In response to: Time to Lock the Doors again...

Comment from: Karen M [Visitor]
Karen M

Good work! Mine is slowly dwindling now. But I got a very early start this season. I best get some more in before it’s too late I think.

29/01/11 @ 08:17

In response to: Hanging Around

Comment from: [Member]
KC

I don’t think they’ll do much with the ducks… too high off the ground (vbeg)

14/01/11 @ 12:45

In response to: Hanging Around

Comment from: Evan [Visitor]
Evan

At least they’ll help with the mosquitoes. *Ducks*

14/01/11 @ 12:38

In response to: On badly behaved crawlers

Comment from: Thomas [Visitor]
Thomas

The exact same problem. Their entire range 94.28.34.192/26 has been blocked from all of my servers. Leeches!

26/12/10 @ 23:34

In response to: Christmas Turkey Shopping my way and gardening

Comment from: [Member]
KC

The only problem with the new coffee machine is that the grounds are really sloppy - but we have been using them as well :) Makes for a very interesting smell when you first put them out…..

17/12/10 @ 15:30

In response to: Christmas Turkey Shopping my way and gardening

Comment from: Evan [Visitor]
Evan

You might want to try chucking the grounds from the coffee machine on the garden beds. That’s suppose to keep the snails away too.

Sounds like you’ve been having fun.

17/12/10 @ 15:23

In response to: Christmas Turkey Shopping my way and gardening

Comment from: [Member]
KC

I have been watching your videos and stuff and you do seem to be a long way ahead of us - would your season keep going?

I plant rocket all year here - it doesn’t like the really bad frosts so I just chuck frost cloth over it on the nights where it’s going to be bad - but it survives better than most other salad veg in the winter. Out in the open anyway - the hot house can normally keep going over winter.

I love compost tomatoes - sounds like a real bonus. And you never know what they are…

One of the feral toms this year is the little yellow grape tomatoes - my favourite grazing food :)

17/12/10 @ 14:19

In response to: Christmas Turkey Shopping my way and gardening

Comment from: Karen M [Visitor]
Karen M

Wow everything looks really good. Never heard of shell grit for snails… Adds to brain bank…

I got started really early this year and some things I think are nearly done already. The crappy weather hasn’t seen me outside much lately so it’s all gone a bit feral. I haven’t planted anything new lately either.. I really must do more rocket before it’s too late. I have enough free compost tomatoes to sink a ship, i hope they all produce.

Good to have an update from you.. Thanks!

17/12/10 @ 14:11

In response to: Limping Ducks and Naked Alpacas

Comment from: Sulari Gentill [Visitor]
Sulari Gentill

Hi Karen

Heartened to know the lame ducks of this world are well defended!!

Cheers

Sulari

26/10/10 @ 18:27