The Heap™ – Consultancy ©®

> Since MySQL doesn’t allow an AUTO_INCREMENT column unless it’s part of the primary key, and InnoDB further restricts this to force it to be the primary key, the clustered index is totally wasted on a meaningless number.

brainydexter

i find it funny, since it wants AUTO_INCREMENT to be a part of the primary key, and if it's a compound key, it has to be the first key in the compound key

gbn

@brainydexter which is pants

brainydexter

sigh

i wonder if people create secondary index or use some other technique, especially when the auto_increment key happens to be a secondary lookup based on the common access patterns for the query

gbn

7:37 AM

@brainydexter Don't use an AUTO_INCREMENT is all I can suggeast

then you'd need to emulate a SEQUENCE

brainydexter

yeah..

gbn

(again, native to SQL Server 2012+ and Postgres)

brainydexter

:)

ypercube

8:02 AM

@gbn: I think that actually MySQL allows a AUTO_INCREMENT field to not be PK

But it's not going to work as braindexter wants it to work

With (a,b) as PK and b auto-incrementing.

That works only in MyISAM, not in InnoDB, which is is really lousy

Phil

Mornin'

Simon Righarts

8:41 AM

I knew there was a reason I disliked MySQL

Mornin' Phil

Phil

a reason? :D

Simon Righarts

I was using 'a' in it's plural form :D

(Yes I have good grammar. Shush.)

Mark Storey-Smith

9:11 AM

Morning chaps

Phil

Yo

Phil

9:29 AM

0

Q: Error with Oracle trigger, invalid identifier

I have the following DML statement that is working perfectly, i have tried to make it into a trigger but it isn't working. DML statement UPDATE HOLIDAY_RESERVATION R SET SUBTOTAL = NVL((SELECT F.FLI_PRICE FROM FLIGHT F WHERE F.FLI_ID = R.IN_FLIGHT_ID), 0) * NVL(R.IN_FLIGHT_SEATS_NO,0) + ...

I think he's going to be using SE to do his job for him :/

Poor chap

ConcernedOfTunbridgeWells

Hi

Phil

Greetings fellow Earthling

ConcernedOfTunbridgeWells

@SimonRigharts Just one?

@Phil Greetings.

Phil

Such a nice weekend

ConcernedOfTunbridgeWells

@Phil Yes, the weather's picking up a bit now. Not quite in @gbn's league in Malta, but almost civilised. Quite pleasant here in sunny Bournemouth now. I might even have to drag a desk fan down here.

Phil

9:37 AM

►

That was the weather yesterday

ConcernedOfTunbridgeWells

@Phil Sadly we have the most micromanaged firewall policy I've ever seen here, so Youtube is but a pipe dream.

Phil

Hehe

I browse through a SSH tunnel :D

ConcernedOfTunbridgeWells

@SimonRigharts Well y'all know about my grammar

@Phil To a third party proxy?

Phil

ssh -D 3128

to an external box. -D creates a SOCKS proxy for the browser to use

ConcernedOfTunbridgeWells

@Phil Still need something outside the firewall, though?

Phil

9:42 AM

Yeah, a box to ssh to

ConcernedOfTunbridgeWells

@Phil I suppose I could set up a DMZ on my DSL at home.

Apropos of nothing at all, dba.se has just past its 5,000th question.

Phil

Cool

gbn

10:06 AM

@ConcernedOfTunbridgeWells I'd say UK is about equal

but probly more direct sun here so would feel nicer

@ypercube op using innodb though...

ConcernedOfTunbridgeWells

@gbn You should get yourself a pair of genuine kiwi jandals. :) Also known as 'thongs' in Australia, but we don't talk about that.

gbn

I'll ask at the local Kiwi Cafe

ConcernedOfTunbridgeWells

@gbn There's a kiwi cafe in Malta?

gbn

mintmalta.com

ConcernedOfTunbridgeWells

@gbn Wibble

gbn

10:20 AM

owner/main mover is soem Kiwi bird from what I see

About that "SQL in the sun" conference...

ConcernedOfTunbridgeWells

@gbn Hmmm.

@MarkStoreySmith - you back from snowboarding yet?

@gbn Perhaps we can give out dba.se branded jandals and stubbies as conference swag.

newzealandatoz.com/index.php/page/display/864

I've even got an outline for a business intelligence workshop:

Mar 8 at 15:43, by ConcernedOfTunbridgeWells

I can hold a seminar about business intelligence:
1. Doesn't exist
2. cd /pub

Mark Storey-Smith

10:36 AM

@ConcernedOfTunbridgeWells Yup, got back Saturday. Apologies for when you called, I was literally about to get on a chair lift :)

ConcernedOfTunbridgeWells

@MarkStoreySmith That's OK. I just wanted to know if you were back in London yet.

Phil

11:05 AM

Crikey, lunchtime already

ConcernedOfTunbridgeWells

Time flies when you're having fun

ypercube

11:59 AM

@gbn: Have you ever used TokuDB ?

gbn

12:29 PM

@ypercube no. Heard of it though

I think I'd rather use Percona for general purpose work.

Toku seems more focussed on a specific workload (like, say, Infobright)

ypercube

Never used but I was searching some time ago and found the advertised features of many clustered indexes per table and hot indexing.

and the catchy "fractal index"

gbn

yep. What is the downside of a fractal index though?

ypercube

No idea.

gbn

seems to be unique to TokuDB. Will assume marketing weasel speak then

ypercube

None, according to their site. Except off course that it's not free.

gbn

12:38 PM

blogs.justonedatabase.com/tag/fractal-tree

ConcernedOfTunbridgeWells

tokutek.com/downloads/mysqluc-2010-fractal-trees.pdf

Warning: PDF

ypercube

Interesting article.

Phil

12:56 PM

Bonkers indexing

ConcernedOfTunbridgeWells

@Phil Optimised for fast inserts

Phil

I remember Copper Eye inventing a novel indexing technique years ago - they had an Oracle plugin

Surprised Oracle never bought them

I'll see if i can dig the patent out

ConcernedOfTunbridgeWells

I fhink a merge of a big leaf of a fractal tree would be quite expensive, but according to the blurb, mostly sequential I/O.

Roughly analagous to the merge part of a merge sort, which can be done off tapes as it's basically seuqential.

(says the guy works in a racket where a table scan is considered to be a good thing)

Phil

1:14 PM

That's the Copper Eye one: worldwide.espacenet.com/publicationDetails/…

ConcernedOfTunbridgeWells

1:58 PM

Apropos of:

3

A: Understanding block sizes

Disk Sectors A disk has a fixed sector size, normally 512 bytes or 4096 bytes on some modern disks; these disks will also have a mode where they emulate 512 byte sectors. The disk will have tracks with varying numbers of sectors; tracks closer to the outside of the disk have more sectors as the...

SQL Server and Oracle both do their I/O in chunks of multiple blocks (SQL Server does it in 64K chunks and IIRC this is configurable on Oracle). The OP is asking about PostgreSQL and my google-fu failed to uncover anything specific except a build option where you can change the block size itself.

Does anyone know whether PostgreSQL actually does I/O chunking of this sort?

Phil

I get the impression it relies on the OS to do IO readahead

ConcernedOfTunbridgeWells

@Phil Could very well be. The only parameters I could see that you could configure control the WAL chunking and that's in MB's. Maybe it doesn't chunk reads beyond that.

Phil

2:43 PM

Crikey, today has flown

Unfortunately, I have loads of meetings tomorrow :/

ConcernedOfTunbridgeWells

@Phil I have this theory that meetings should be strictly rationed, perhaps restricted to 1hr/day, starting no later than 10am or after 4pm and no more than 4 hours per week.

Phil

I already told my boss that it's too long (3 hours) and there are too many attendees (10). Nothing productive can come from it

ConcernedOfTunbridgeWells

@Phil If you actually have to have that conversation you're buggered before you get out the door.

Phil

Ah well, a good opportunity to catch up on my rss feeds whilst "taking notes"

ConcernedOfTunbridgeWells

Generally I'd say a meeting culture is a sign of endemic responsibility without authority or tasks being divided amontst the wrong people.

@Phil Just try not to fall asleep, however if you feel you need assistance keeping awake, don't hesitate to us here on the heap.

For example

dilbert.com/strips/comic/2009-09-27/…

F-ing internet explorer - really need to get something else on the machine. JS interpreter crashes every two and a bit.

Phil

3:01 PM

Heh, quality dilbert

My colleague enjoyed it too. Shame he's just quit so my workload's going to double until they find a replacement :/

ConcernedOfTunbridgeWells

What time's your meeting - and can you set up an RSS feed on the heap?

Doesn't mention RSS in the FAQ for a quick scan

Phil

Oh god. dilbert.com/strips/comic/2012-02-11

That one nearly made me fall off my chair

ConcernedOfTunbridgeWells

dilbert.com/strips/comic/1999-10-10

Oldie but a goodie

Phil

3:29 PM

Heh, that's awesome

ConcernedOfTunbridgeWells

3:54 PM

Alice is my favourite dilbert character, I think.

jcolebrand

ok db gurus

I need app help

ConcernedOfTunbridgeWells

@jcolebrand What can we do for you?

jcolebrand

Familiar with RedGate SQLCompare?

ConcernedOfTunbridgeWells

@jcolebrand Not much, I'm afraid.

jcolebrand

MySQL in general?

We've got a copy of MySQLCompare on the RedGate demo license (as they do for like 15 days for trial or whatever)

ConcernedOfTunbridgeWells

4:02 PM

@jcolebrand SQL Server and some Oracle, but not really MySQL

@jcolebrand What do you want to do with it?

jcolebrand

SQLCompare lets you do a database-> snapshot feature

MySQLCompare (demo at least) doesn't let you do a database-> snapshot feature

I'm curious if the for-pay version does

ConcernedOfTunbridgeWells

@jcolebrand Can't you get a coherent answer from Redgate?

jcolebrand

I literally just sat down, and have an IM convo of higher import going on now

ConcernedOfTunbridgeWells

@jcolebrand You've comfortably blown my (fairly limited) level of familiarity with redgate and my nonexistent level of familiarity with MySQLCompare

jcolebrand

@ConcernedOfTunbridgeWells There you go then :p :-\

I bet @rolandomysqldba would know if only he were here

Phil

4:15 PM

I'm sure their sales guys would answer that sharpish

ConcernedOfTunbridgeWells

@jcolebrand You could also get redgate on the blower and ask them. They're probably still open at this end.

jcolebrand

I bet I would if I wasn't trying to find my head this morning. I know I had it just a little while ago

TEDx plans for 4pm I need to solidify, need to run to the store at lunch, thinking of making it a late lunch anyways and just eat before TEDx, need to download videos and presentations, and edit the presentations to be coherent, find a fourth video, worry about an apartment in Houston that got broken into that I need to file insurance on, do some scripting for migrations here at the office, and write new code. All by 4pm.

it's 11:15 am

I'm not particularly motivated

also, there could be another tool that would script them to the HDD for me

Phil

Hometime for me. Laters!

ConcernedOfTunbridgeWells

@Phil ttfn

@jcolebrand According to the redgate sales reps it doesn't

ypercube

@jcolebrand: What exactly you want? (perhaps mysqldump can do the job)

jcolebrand

4:20 PM

@ConcernedOfTunbridgeWells did you already ask them?

ConcernedOfTunbridgeWells

@jcolebrand Yes.

jcolebrand

@ypercube I want a flat directory that contains a create/insert script for every table/procedure/etc currently in a given database

@ConcernedOfTunbridgeWells merci beaucoup

I was just going to rely on lazy for today

I figured I would catch that up tomorrow, it isn't really even my project

ConcernedOfTunbridgeWells

@jcolebrand I wrote one for SQL Server once, but that probably won't help you very much.

jcolebrand

did you use SMO?

that's what we used to write our scripter

ConcernedOfTunbridgeWells

@jcolebrand No. Just reading from the data dictionary

11

A: Tool for Scripting Table Data

Here are some scripts I wrote for reverse engineering SQL server schemas. They may be of some use. Also, as a general interest they give some examples of how to get various bits of information out of the data dictionary. I've added an MIT license below to make permission-to-use explicit and fo...

ypercube

4:24 PM

I suppose you can use Workbench for that, too.

ConcernedOfTunbridgeWells

@ypercube You can now. That's a port to SQL Server 2005 from one I originally wrote on 2000.

It's a bit quick and dirty - I wrote it because I needed to script out a whole lot of crap on a SQL Server database but not the whole database. It's been quite useful on a few occasions since.

RolandoMySQLDBA

@ConcernedOfTunbridgeWells the only comparison tool I trust between MySQL servers is pt-table-checksum from Percona. It is designed to compare a Master with one or more Slaves. To compare two DB Servers that have nothing to do with each other, one would script a table checksum table-by-table (in parallel of course) between the two DB servers, in the same manner as pt-table-checksum : percona.com/doc/percona-toolkit/2.0/pt-table-checksum.html.

You would have to also script the collection of table names that do not exist in both DB servers to get a complete picture of the differences.

jcolebrand

4:46 PM

@RolandoMySQLDBA but how would you script one db to disk as a set of files?

without using phpMyAdmin to generate one massive file?

RolandoMySQLDBA

5:19 PM

@jcolebrand : You could use the information_schema.tables table to collect the names of the tables and sculpt the SQL script to say something like SELECT CONCAT('CHECKSUM TABLE ',table_schema,'.',table_schema';') FROM information_schema.tables WHERE table_schema NOT IN ('information_schema','mysql') and pipe that out to a text file.

jcolebrand

@RolandoMySQLDBA so you don't know of any tools that do this directly I take it?

RolandoMySQLDBA

The closest I know is pt-table-checksum which is only Master-Slave comparison. Otherwise, I do not know of other tools. I script everything by habit.

jcolebrand

The idea is that we want the devs to be able to check in changes to a CI script, and have it auto-drop them into a folder, then run all scripts in that folder

so "if not exists sometable create sometable" sort of thing

RolandoMySQLDBA

BTW I just did a quick google and I got this : rocketdownload.com/program/…

jcolebrand

well I was trying to avoid random google downloads, was hoping for some specific insight with tools ;-)

RolandoMySQLDBA

5:27 PM

To find differences in table listings, I guess you could run SELECT CONCAT(table_schema,'.',table_schema) FROM information_schema.tables WHERE table_scheam NOT IN ('information_schema','mysql') from two DB Servers into one text file. Then, just compute diffs like set theory.

jcolebrand

But thanks for looking that one up :D

RolandoMySQLDBA

I would take a shot at a question like this. I did something like this several years ago.

jcolebrand

Would you like to see it as a Q then? "How does one neatly script out all the current items in a db to start a CI server with, that we can use as the basis for a deployment set later?"

RolandoMySQLDBA

Hmmmm...

Maybe put it out there and lets see if others have done this in the developer shops

jcolebrand

5:50 PM

Aye, probably tomorrow mid-morning

too much other stuff to do :-\

RolandoMySQLDBA

Same here

Transcript for

The Heap™ – Consultancy ©®