janewilliams20: (geek)

Only instead of a one-day cooking event with half-a-dozen helpers, it's a three-week project of three developers.

Read more... )By the way, if there's anyone in the Stevenage area who can code MySQL, or ColdFusion, or Flash, and wants a short-term job in the next few weeks, get in touch? If we can lessen the pressure by throwing more resources at the problem, we will.
janewilliams20: (geek)
update mytable
set mycolumn = 1
where mycolumn = 0
;

Easy enough? No.

"Error Code: 1175. To disable safe mode, toggle the option in Preferences -> SQL Editor -> Query Editor and reconnect."

So I go there, and am told that safe mode prevents updates and deletes with no WHERE clause. A very good idea - but I've got a perfectly good Where clause, so what's the problem?

That error message has more than one line, and it's hard to even know the second is there to see. It says: "You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column"

A key column? Well, no... and there's no way I sensibly could, for this update.

I don't want to turn off safe mode completely, nor to start & stop. But a bit of Googling comes up with an answer.

SET SQL_SAFE_UPDATES=0;
Do update
SET SQL_SAFE_UPDATES=1;
janewilliams20: (geek)
We have a copy of the main, live DB on another server, for test purposes. It's rather out of date, and could use a refresh. I've got some test tables and data in there that I'd rather not lose until I've checked them, so what I want to do is rename the old copy rather than dropping or over-writing it, then import a new copy from last night's live backup.
how to, how not to )
janewilliams20: (geek)
Should you wish to do this, you don't need all those blogs telling you how to write SQL scripts (the ones that expect you to hand-write a script for every table, and lose the indexes and identity keys on the way). No, you start off by reading a couple of Microsoft white papers on the subject that warn of all the nasty things you should look out for (your application code WILL need some rewrites). Then you download an MS utility called "Microsoft SQL Server Migration Assistant for MySQL". And you use it. It analyses your tables, explains exactly what it plans on doing to change them, and why, creates the new tables (etc) in SQL Server, and is currently migrating my data.

Edit: if you run into trouble with this, Microsoft (or at least that part of it called Welly Lee) are courteous, helpful, reply promptly, and are generally a Good Thing. This is not what I expected, and came as a very nice surprise.
janewilliams20: (geek)
I'm reading a book that points out that most mistakes in MySQL are made by people who are used to the "big" databases and assume it'll work more or less the same way. That's me. I'm also reading up on the various storage engines available, and what the default settings are, and going "aaarggh, no, no, no!" Do any of my FL already have expertise in this area, and could advise? Big and growing transactional DB, frequent updates to records (hence the "aargh!" when I found a storage engine with no row-level locking), multiple indexes on most tables. MySQL is used for this sort of thing, and used successfully, so it can't be as bad as this is looking, but I have a nasty feeling I'm missing some crucial things to look out for.
janewilliams20: (geek)
Also memo to any techy friends or readers, of course, but I doubt if most of you will want to know.
geeking under here )

For the rest of you: yes, I'm really enjoying my new job, thanks.

Profile

janewilliams20: (Default)
janewilliams20

March 2017

S M T W T F S
   1234
567891011
1213141516 1718
19202122232425
26 2728293031 

Syndicate

RSS Atom

Most Popular Tags

Style Credit

Expand Cut Tags

No cut tags
Page generated Jul. 22nd, 2017 08:33 pm
Powered by Dreamwidth Studios