]]1,999 chicago marathons[[:]]’

there is no iregexp
but check out http://dev.mysql.com/doc/refman/5.1/en/regexp.html

ok, so the tutorial page doesnt work on my install, what should I do next to get it working?
ok, now it works suddenly

Arrick, must be a typo
i suspect user error

yeah the tibyke doesnt say much
yeah the help page doesnt say much

totic, try with lower(foobar) regexp ‘whatever’

thanks all
it seems to be working now
well, seemed to be working
uhmm, mysql and php suck on windows, I’ve been on this for two days now. on linux its abotu 60 seconds

if I have a double column how do I index it so that I can do a fast search for values that are less than a number?

Arrick, why do they suck big time together?

they just suck on windows period tibyke

only perverts use windows =

nothing should be that hard to get working
no, perverts use linux, so that they dont get infected when they view their pron

mac

hrhr

how do i alter the encoding of a table ?

Actually, it installs very easily on Windows.

Arrick, and is about the same speed

alter table table_name set character_set=utf8 ?

I don’t think you really want DOUBLE. DECIMAL perhaps? Or INT?

seekwill, uhmm not according to the install I just did

!man charsets

Sorry – I have no idea what function you’re talking about! but try http://dev.mysql.com/charsets

!man charset

see http://dev.mysql.com/doc/refman/5.0/en/charset.html

Well, no one is perfect at everything

if those two are more indexable then I can switch to them

Except for.. me
They are all index-able

I mean for a or a

thanks

not a =

seekwill Im sitting here looking at MS sql !!!!!!

Each column is different. use the correct one for your needs.
Nice

seekwill but only to convert from )

Nothing wrong with MSSQL

yes there is
the part before the SQL

We don’t do that kind of bashing here…

seekwill, i would almost be willing to let someone remote in and config this server if i knew they could do it

also centosfedora

Install MySQL first.

Arrick what seems to be the problem ?

I did

You can’t compare the two!

then I installed PHP

Does it work?

Arrick its a good idea that you learn to do that

No, install Apache

sql yes
php shows one page and that sit

is it possible to set the default number format?

I don’t want to compare the two I want to index the comparison

You can’t compare CentOS to Fedora

also centosfedora

They target completely different audiences

fedora audience

seekwill, I followed the http://www.peterguy.com/php/install_IIS6.html directions and it doesnt like me

IIS?
Ugh.
Are you set on IIS?
Or can you use Apache?

rm -rf IIS

^^^

BTW, I use Fedora

I rest my case

Hmm

anyone familiar with mysql data syncronization between multiple datacenters? perhaps methods for doing this
for high availability and disaster recovery

Really depends what you want to do. Replication is generally your best bet

replication is so sexy

replication across state lines?

is it posible now to make transactional replics in both ways with mysql??

I am replicating over a vpn between california and india

we also need to balance the load
and scale horizontally

read load or write load?

all
both*

ok
you can’t scale writes with replication

yeah
we use replication now
but in one location
we are looking to have a second datacenter to handle users as well

any chance to have a full real replication model in mysql 6?

ideally have that data synched between both locations

You can’t do that easily
Two-way replication is basically a no-no

elnovicio depends on what you mean by real

mysql cluster is only an option on a fast link
e.g. fast lan

elnovicio, if you want to break physics, sure!

dammit, I have to use still MS sql server then

well is there ways or methodologies for doing it on the application side?

Other databases do not have solutions to this either

mysql cluster is not an option

there are unresolved issues

the google semi-sync replication thing is hot

i was reading about how ebay does this

i.e. if you have a replication conflict, there is no trivial way of handling it automatically

they break up their data into segments

don’t want to break anything, just want a replication similar to what we have with MS SQL

I assure you, MSSQL has no magic bullet for this either

semi-sync is not so great
its a step in the right direction though

Either you have a big fat distributed lock that takes 100s of ms to get each time you do a single insert and creates more points of failure, or you have potential for conflicts

elnovicio, what is the MS SQL replication you use? merge, transaction ?

transactional

In which case you have huge locks
which take forever to take

hmm

So provided you never actualyl change your data, you’re fine

well
the first real problem to tackle is the primary key issue

auto increment increment !

if there are writes being done to two locations.. they need to be unique keys

hrmm maybe wamp is my solution

though we we looking to make 1 way replication with MySQL, and feed new data in the main node, as usual the conectivity paradox

elnovicio, as a warm standby or active querying?

active

splitting reads and writes is fine… but i need to further compound this with multiple locations

best bet is to probably have an authoritative DC for all rows
and put users on their closer DC

elnovicio, with updatable subscribers?

with persistence right?

and always go to the authoritative DC for stuff that has to be absolutely correct

toad, but even with persistence, how would you sync the data

yea
multi-master rep
but assume that all data from the other DC could be several seconds out of date

toad, thats the idea, but how would that be accomplished

the microsoft model works, but its way to expensive

seconds is ok

multi-master replication

but that runs into the primary key issue

you can set autoincrement to have a step size as 2
so DC A generates odd PKs and DC B generates even

or more

toad, with gnbd?

it’s a feature in mysql 5
alternatively, you could do app-level PK generation with a HiLo table, like Hibernate Shards does

what is hibernate shards?

it’s not what you’re looking for
but if you can do app-level PK generation, that might be easy
though autoinc with step size doesn’t take any coding, just configuration

unfortunately this is for existing applications
autoinc sounds reasonable

then as long as you can use MySQL 5, you can do that

elnovicio, from what I know of updatable subscriptions, it just uses triggers on the subscriber to write back to the master, not really that fancy

so in that case, i could just have master master
and not worry about stepping on data

though you should realize that if you have a lot of writes, your slaves can fall minutes or hours behind
even if latency is low
slaves only have a single IO thread, whereas masters can have several writes going in parallel

toad, with this setup.. if we were to implement it, we would essentially have horizontally scaled out the write servers

not really

to a degree

oh, you mean on your own, before doing the master-master

since persistence would handle it

because all writes still have to go to both masters

they have to go to both masters?

through replication

right
i understand

Understanding is a three edged sword

haha

you don’t gain any write speed increase through extra masters

i see
so it really only scales vertically

you actually get a slight decrease
partitioning is the way to scale horizontally

yeah
are there any docs on methods for that?

yup

with mysql

not really
i’m working on a generalized implementation actually

just application level i assume

but it’s not going to be released soon
speaking of which, I should get back to implementing sharded INSERT

we have been pushing the limits of mysql since we started this whole project

heh

over 600 million rows

not likely
600 million rows is no where near the limits of mysql

i know

yeah, the microsoft replication model is patched with triggers and other stuff, but somehow it basicly does the job

ebergen, it may not be the limit of mysql.. but i doubt many work with databases that size

elnovicio, about the only thing you can’t do with MySQL that you can do with SQL Sever that way is conflict resolution (which you could kinda do if you really tried hard enough)

heh

LB!

hello

one way to avoid conflicts may be using big unique segments between the suscribers

I heard some rumors of dbdesigner turning into mysql workbench. are there screen shots of the workbench tool somewhere?
wait, I asked too soon

elnovicio, that only works for sequences? you can still have conflicts with things like natural unique and primary keys

The primary developer is the same person for each.

Xgc, hey, that’s cool
I’m trying to dig up some roadmap details..

Durentu, http://forums.mysql.com/read.php?113,153944,153944#msg-153944

coolness

what if the keys are segmented in sequences for the suscribers?

elnovicio, that still doesn’t stop people from registering with the unique username ‘bob’ at both locations and getting a conflict

can you give permissions to a user for a specific table only?

yes, see http://dev.mysql.com/doc/refman/5.0/en/grant.html

ty

I am having some issues with performance with my sql calls. I have 1 call I execute 26 times, each call takes 187ms. That table only has 1434 rows total. I have a lot more debug info here if you can help: http://rafb.net/p/CL6hNx64.html
I’m sorry if someone answered me, I got kicked out. Can you repeat if you did?

Why do you need to execute the query 26 times?

perljunkie, use composite keys to stop the file sorts and stop that concat in a group by

It’s a long explaination
I’ll look those up. Thanks

perljunkie, we dont want the long explanation but fix the 26 queries as well

awww, but I *wanted the long explanation ;o)

what is a function that will convert to integer and truncate anything after the decimal point?

HI, someone knows how can I recover a corrupted InnoDb database?

take your backup
take a slave and make it a master

It was the slave

wipe it clean and let it update from the master?

backup the master and setup the slave again

weigon, I dont want to spend all that time again

check the hdd for bad sectors first

run defrag and spybot

im under a linux box

are you stuck?

lol…
classic..

aint spybot another spyware?

no (at least it didn’t used to be)

stop the server, make a file backup of the server, copy it to the slave, set CHANGE MASTER TO …
it is just a normal restore

becouse I got a message “Don’t remove advertising or your comp will die” from spybot

sounds like a bad translation
all it’s saying is software on your computer might depend on the advertising software, and if you remove the ads then the software might refuse to wrok

“Say ‘ello to muh little freend”

weigon, …. ok

translation isn’t all it’s cracked up to me
*be

weigon, the problem is that I cant take the master down as it’s a high traffic server

read the manual how to setup a slave from the master
do you have a second slave ?

stand our ads or you will die, lol

weigon, I know how to do it

hi weigon

weigon, yes, it was slave of my first one…lol

do you know if it’s possible to set the default money/decimal output?

if you have a second slave, shutdown the slave and copy iy

weigon, the second slave was a slave itself of the first slave…
weigon, xD

on the DECIMAL type?

just take the backup from last week and apply it to the empty slave

Slaves are illegal. I won’t be a part of this conversation.

perljunkie, I have 5 slaves man

hey guysi have a datetime question

42

i have a field set to datetime

, they are great doing the dirty tasks
weigon, tnks

is it possible to query on that field with just the date

Yes

weigon, I will take a new DB snapshot today to re-setup the main slave

ie select field FROM table where datetime LIKE thedate%
like that

you can either use where DATE(your_field) = a_date [note that this will not uses indexes] or use where your_field between a_date and one_day_later

where date(yourdatefield) = “2007-01-01″

remember, that will bypass indexes

and date() only added in 5.0 I think

thanks guys

don’t forget to thank the bots!

20070101 and datefield 20070101235959

should be =
or (the_date+1)

yes
thoug maybe thats not possible, is it?

roxlu, time to read some docs

you specify percision and scale when you create the decimal field
*precision that is
see http://dev.mysql.com/doc/refman/5.0/en/numeric-type-overview.html

I need to do a delete on a group of id’s that are generated by querying the very same table I want to delete from
so a direct subquery won’t really work as it needs to lock the table
so I created a temp table full of those id’s
but when I query that temp table in a subquery
it never comes back
anyone ever heard of this nonsense?

curses, I’ve typed an answer 4 times already and your next line is exactly what I was writing

yes I know, i’ve got a decimal(20,2), but I want the ‘output to be: 100.4444,12

basically I’m doing something to the effect of delete from mytable where id in (select id from temp_mytable)

huh? I’m afraid I don’t understand that output.

does mysql have a concept of arrays so that I might preload the data into an array and use that instead of my subquery?

jereme of course it will work – delete from table where id (satisfies criteria)

yeah, that sql looks fine

I wish it were that easy, but it’s hanging on the very simple subquery

it was told to me that it is the american standard

however, if I do a select for the data and use a left join, it returns almost instantly

jereme you state that you generate them by querying the table – so cut out the subquery, and use a single WHERE

I would expect to to see 1,000,000.00 for 1 million dollars and 0 cents
00 cents, that is

is that the american standard?

jereme what is the criteria for rows in the temp table ?

I believe so

*is/are *a/um

hmm yeah; I’m working from a function-design which uses both… very confusing

if I use the same criterea I used to generate my temp table to do the delete, I’ll be overeager and delete more than I want, do to some groupings I performed

Hi all. I’m tasked with creating a database that can potentially hold a rather large amount of data (365,000+ entries). How can I estimate its size?

I’m reading now that if your subquery table is small and your parent table is huge, then IN() optimizes terribly

what kind of entries?

according to the mysql manual
so I think that’s my prob

regardless, you’ll need to format the output yourself if it’s something outside of the normal representation returned by MySQL for decimals

is the optimizer is failing me

3 fields. autoincrement for the first, integer for the second, timestamp for the third.

whats the average size fo the data that you’re going to be storing?

my temp table has 2k entries whereas the table I’m deleting from has 4M+ entries

jereme no idea – do both tables have sane indexes ?

okay thanks

42, 83, timestamp_here

yeah the temp table is just simple a temp table with 1 field, the parent table I am deleting from, I am going against the primary key

its basically a wireless hotspot that logs which users connect and at what time. The timestamp table is what I’m worried about.

jereme what do you mean by “groupings”
?
can we see some actual code

I perform a group by, so I’m not returning all matching ID’s
it’s all good actually, I found my issue in the mysql manual
it’s that IN with subqueries is bad

so any thoughts on the db size?

hi, i got his error today, http://dev.mysql.com/doc/refman/5.0/en/blocked-host.html

jereme if you group by and not all IDs are returned then the ID in question was not groupable (because it was more-unique than the result returned)
that makes no sense to me at all

so, if the max connection errors default is 10, is it incremented until 10 and the user is blocked, or is it a per day basis

hi…how can I implement something like this: SET @CLINIC_ID = CONCAT(@ctryID,@statID, UCASE(LEFT(@clncCity,2)), IF(ISNULL(MAX(clinics.clnc_ID)), ‘01′, LPAD((RIGHT(MAX(clinics.clnc_ID),2)+1),2,’0′)));

oh wow, thanks

does anybody know

sounds like 365K*the row size + indexes + a couple megs for system stuff

this gives me..Invalid use of group function

well the problem is your not doing a query so not grouping so you cant MAX(), you’d need to do a query first to select the max() values out
then do the rest of your set

aaha okie
thanks

It says on the page you linked… blocks the host from further connections until you execute a mysqladmin flush-hosts command or issue a FLUSH HOSTS statement

yes but im curious how it blocks

Of course, please read the last paragraph as well. That is not a normal error to be seeing
What do you mean?

Lathiat, but I cant get the value and store it in a variable.
Lathiat, what would be the best of doing what I want?
Stored Procedures?

hello

well, after 10 connection errors, does it just block until flushed does time matter (eg. 10 per day)
*or does time

is there a way to get the filename+fileextension from a string containing the entire filename path?

It certainly doesn’t say anything about a time period

no, it doesn’t
it doesn’t say much at all

It’s purely academic though. Fix your network

cimages\image.jpg in a mysql field. want to return only image.jpg. are there any string functions that can do it?

i guess so, after 3 years, this if the first time i’ve seen this

Sal where ? in mysql ?
!man string functions

see http://dev.mysql.com/doc/refman/5.0/en/string-functions.html

yes, in a query
i’m looking at them… but can’t find the right functions
aint there a func that cuts strings from right up to the first “\” char?

what is best, to make one single big query? or make multiple subqueries to fetch data?

1 big one

ok so all those active record and ORM implementations fails at its basics
right?

multiple queries require multiple connections

kn11px depends on th edifference in amount of data, the frequency of querying, and other factors you haven’t told us about yet
Sal there is LEFT and RIGHT, yes
but they take numeric arguments AFAIK
kn11px if you’re filling a web page with data, it would be both useless and wasteful to produce more than one page can hold at a time
to name but one
human-clickety vs machine-speed

asking them too

k

whats the function to tell me the row I just inserted?

Lathiat, dont worry. Figured it out

last_insert_id()

thanks threnody

threnody, will that work even if someone else is inserting at the same time or is it session dependent?

imagine you got a comment table and an user table and want to print on screen comment username i would do it as an inner join in one single query but those active records implementations makes another query fore every comment

erstazi, yes
erstazi, you can vnc over to my computer if you want

ok

just tested it

aoirthoir, ok, shall vnc in a sec

ok i am going to get a printout brb

Connection specific.

the function is connection specific

is there a sql function that will increment an integer field by one?

Xgc, threnody thank you

UPDATE tbl SET field=field+1 …;

cool. I didn’t know if you could do that or not. Thanks a bunch!

hi guys, for some reason mysql wont startup, its on debian and i get a message Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock any ideas why ?

ok im back
threnody, thanks

Sniper_jo, server not running

it wont start when i try

so.. you *know* it’s not running yet you ask why you can’t connect ? classic, that is

well running /etc/init.d/mysql start to start it results in that error …

Sniper_jo, the server creates the socket it does not try to connect to it

i see, so how do i get it to properly create the socket then ?

Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock comes from the client

*sigh*

i also get the error #2002 – The server is not responding (or the local MySQL server’s socket is not correctly configured on phpmyadmin

the client and server are two different programs. You have to start the server before you connect to it with the client.

gonna be a tough one

… and the client and server need to agree on the location for the socket.
Both are configurable.

well i am guessing the client works fine and the server is not .

Both work fine.

well thats all dandy but they worked last time ..

But no matter how much they like each other, if they don’t go tot he same bar, they probably won’t go home together.

do ‘ps aux | grep mysqld’ and report the results.
that’s damn good.

0 grep

that means the server is not running

yes, it’s not running, we know this

omg ..

now read your syslog for why mysql cannot start

and where my i find the syslog?

with the rest of all the logs
in /var/log
(if you don’t know *that*, you need to brush up on some *nix basics pronto)

logs are important

and of course that’s also configurable. That’s why yo need to know your own system. Others can only guess.
s/yo/to

i found it

xgc fo’ da prez

Sniper_jo, pastebin.ca

without the help … o

Sniper_jo, paste bin to that URL (http://pastebin.ca)

Actually, without the help you wouldn’t know enough to look for logs or an error message.

indeed… wise man

So the problem is not in the help, but in trying to extract information the system owner doesn’t know.

http://pastebin.ca/632552

Xgc, wax on wax off

It’s sometimes a tough process… and the questioner often incorrect concludes the helper is the problem.

that’ll grow hair on your palms

Sniper_jo, did you fscking read that bloody log?

indeed.. 9 times out of ten, if I read a question and I ask for system info, and the askee doesn’t know where to find it, or even what it means, I know *exactly* what kind of conversation it’s going to be.,..
it quickly devolves into “yes, the little funny-looking squiggle means you are now in your “home directory” ” – “oh – and what is my home directory?”