The Heap™ – Consultancy ©®

« first day (2118 days earlier) ← previous day next day → last day (2750 days later) »

James Lupolt

5:33 AM

My write in candidate: ' OR 1=1; DROP TABLE votes; DROP TABLE candidates; --

Tweeted by Ben0xA on October 22, 2016 at 3:06 AM

5 hours later…

ypercubeᵀᴹ

11:01 AM

The question is tagged with postgresql. I can't see where Excel fits. — ypercubeᵀᴹ 1 hour ago

1 hour later…

ypercubeᵀᴹ

12:29 PM

@DanGuzman that should be an answer. — ypercubeᵀᴹ 44 secs ago

From his profile:

> I've been using Microsoft SQL Server since version 4.21

wow ...

When was that, 20 years ago?

Honestly, the description is totally confusing. May be it's just me or it's just Saturday. Can you edit the question and add what you want as output? — ypercubeᵀᴹ 13 secs ago

Aaron would have added something about "playing word games" ;)

Andriy M

@ypercubeᵀᴹ If they are trying to make some point about Excel, it seems more likely to me that they are contrasting it with Navicat rather than with PostgreSQL.

And indeed I think the OP is more after solving their issue in Navicat if possible.

ypercubeᵀᴹ

@AndriyM what's Navicat?

Andriy M

@ypercubeᵀᴹ My impression is it's some kind of a DB browser.

Possibly with some management capabilities as well

ypercubeᵀᴹ

yes, you are right. I searched it

Andriy M

But perhaps it would be better to ask them explicitly at what tier they want to solve the problem.

A computed column might be a solution but I hear PostgreSQL doesn't support them (yet?).

Still, theirs clearly looks like a presentation issue, so solving it in Navicat makes most sense to me.

ypercubeᵀᴹ

12:52 PM

@AndriyM you can do it with a view

They can also add an INSTEAD trigger, if they want to insert

1 hour later…

ypercubeᵀᴹ

2:22 PM

Search in a job site for "mathematics".
Results in the first page include:

- Handyman

- Legal secretary

- Crematorium assistant !!!

Your search is broken.

4 hours later…

yasar

6:51 PM

---TRANSACTION 48FA7AD, ACTIVE 26117 sec inserting
mysql tables in use 2, locked 2
ROLLING BACK 1088758 lock struct(s), heap size 119847352, 92457777 row lock(s), undo log entries 1816956
MySQL thread id 212232, OS thread handle 0x7fb0735a6700, query id 35590254 localhost root Sending data

Is it inserting or rolling back?

1 hour later…

ypercubeᵀᴹ

7:53 PM

@yasar It should be rolling back. But the whole InnoDB status would help. Post it in pastebin or similar and maybe we can help.

Although the residents of the room are not sensitive to MySQL force ;)

yasar

@ypercubeᵀᴹ Here you go: pastebin.com/hnTzwVvv

Thanks for the help

I was wondering if the query was progressing, it is taking too long already

ypercubeᵀᴹ

@yasar I don't see any ROLLING BACK

yasar

it was there before, but it is gone now

I think it was temporary

ypercubeᵀᴹ

This is weird. and interesting.

You didn't save that sttaus?

yasar

no, just the part I pasted here

You can tell it is the same transaction by the transaction id

It says 100 million row locks, it is more than the total number of rows in both tables combined :/

ypercubeᵀᴹ

8:06 PM

@yasar can you share the query?

yasar

I cant share the exact query but I can desribe

I have a two very similiar tables

one has more up to date information then the other

ypercubeᵀᴹ

INSERT INTO a (...) SELECT * FROM b WHERE ... ON DUPLICATE KEY ... ;

yasar

yes

ypercubeᵀᴹ

something like that?

No joins?

yasar

yes, no joins

I want to fill the recent table with older entries if it doesnt exists in newer table

I am not doing it with primary key though, but another key

that might be the cause

index is on char(11) field

ypercubeᵀᴹ

8:13 PM

@yasar Are there more than one UNIQUE/PRIMARY keys in the target table?

yasar

there is one primary and one uniq key, but primary key is auto incremented and it is not being inserted

uniq key is being inserted if it doesnt exists in newer table

ypercubeᵀᴹ

and if it exists, you do an update.

I think it would be good to post a question at our site

yasar

on duplicate key update field=field

it is a no-op

ypercubeᵀᴹ

Include table definitions, query (you can change the names)

Why do you have a no op ON DUPLICATE KEY?

yasar

I didn't want insert ignore

because it ignores everything

ypercubeᵀᴹ

8:18 PM

@yasar OK. INSERT IGNORE shoudl never be used in my opinion.

See this answer for how it could be done with NOT EXISTS

(but of course efficiency depends on many things.)

yasar

I think you missed a link there

ypercubeᵀᴹ

It may be fast to use ON DUPLICATE KEY UPDATE id = id ;

Q: On duplicate key do nothing

I am inserting into the following table using LuaSQL with PtokaX API. CREATE TABLE `requests` ( `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `ctg` VARCHAR(15) NOT NULL, `msg` VARCHAR(250) NOT NULL, `nick` VARCHAR(32) NOT NULL, `filled` ENUM('Y','N') NOT NULL DEFAULT 'N', ...

yasar

I might post on main site, but I cant formulate and exact questin

ypercubeᵀᴹ

main site = DBA.SE I hope ;)

yasar

yes :)

ypercubeᵀᴹ

8:22 PM

The ROLLBACK that disappeared is weird though. Haven't seen it before.

I found this: bugs.launchpad.net/percona-server/+bug/1412693 but doesn't seem to be any answer there either.

yasar

@ypercubeᵀᴹ Yes, it is weird :)

But my main concern is when this query will finish, in 10 hours, 10 days?

ypercubeᵀᴹ

No one can tell

yasar

older table has 48 million rows, and newer one has 57 million rows

any educated guess?

ypercubeᵀᴹ

No.

I doubt it would be days but depends on various settings, configurations, sizes, etc.

1 row could be 30 bytes or it could be 300KB.

Time for coffee, youtube videos, music, whatever you like. It's Saturday night after all.

Or Sunday morning, in some parts of the world

yasar

true :)

Zane

9:06 PM

Anyone on?

Does anyone know why I'd be getting this error when the Account has full control of the drive I'm trying to move to

Msg 5120, Level 16, State 101, Line 1
Unable to open the physical file "M:\SQLData\FG_FIDELITY_936.ndf". Operating system error 5: "5(Access is denied.)".

ypercubeᵀᴹ

10:03 PM

@Zane anything more in the error log?

@Zane Perhaps it doesn't have the same permissions at the directory or file level.

« first day (2118 days earlier) ← previous day next day → last day (2750 days later) »

all rooms