that should be in a mysql error log somewhere
I dk where you distro is writing that.

its not giving me any logs though

Any idea where the mysql log is on a cpanel install?
Just wondering… spent ages trying to find it and I never could

sorry, no.

.var/log/mysql.err and mysql.log both exist but are empty

Ok, thanks :p

perplexing.
do ‘ps -ef | grep mysqld’ and tell me the result.

What can i do when INSERTing text into a table to escape any characters that are used format the actual text, etc?

http://rafb.net/p/47uNpH48.html
not running, wont start, no logs. ;(

I was hoping there mught be another instance of myslqd running, but there isn’t
might*
what are you using for input? php?

actually im inserting just with phpmyadmin right now

I am at a loss, but I will tell you this. mysqld is trying to start, but when it gets to writing the pid file it craps the bed. To me that sounds like some sort of file permissions problem.
sorry, I dk phpmyadmin. You have to find some way to escape single and double quotes in text strings though.

err, I just spotted /var/log/syslog. its usually just /var/log/messages
but anywho, I now see ‘Can’t start server: Bind on TCP/IP port: Cannot assign requested address’ it thought maybe I had something on port 3306, but ‘netstat -an | grep 3306′ shows nothing

ok, thanks. I just would have thougt mysql had a “filter” for that

weird, let me try rebooting it

hi, what’s the proper syntax for the following select * from foo where name LIKE ‘%Pepe’s%’ // problem being that quote is backslashed

its backslashed? doesnt look like it

jbalint++

:p

well, now that I know where to look for syslog logs… I’ve found the answer to my problem. its not starting because it can’t bind to 127.0.0.1, and it cannot do that because I do not have my loopback configured correctly (don’t know how that happened..)

my query isnt cuz it dont work
of course its backslashed in the database, it has to be
90% of the world comes to a help channel to be a smartass, is there one of those other 10% here?

try commenting out the bind-address option in my.cnf or on the startup command. that will allow connections from anywhere
LIKE ‘%Pepe\’s%’

that query errors

LIKE ‘%Pepe%s%’

or actually returns no results, the \\ ” errored

heh, geewiz, thanks for putting up with me boy do I feel stupid

this is not trivial stuff.

threnody, nice, but is that general practice on query building?

I don’t normally search that closely, I’d stick with %pepe%

well, just saying if someone searches for “jimmy’s” or jo’anne, should i normally just scan for ‘ and replace with %

yes
She Who Must Be Obeyed says, “Shut that damn computer off and come to bed.” Ciao.

ok thx
is there a single character match wildcard?

‘?’
g’night

night

_?

Is there anything wrong with this?

1187928713 and ptype = ‘GPS’ and order by id desc limit 1;

yes

what?

you shouldnt have an “and” between ‘gps’ and order by

oh…
How did that get in there.
Thanks

and you coiuld probably make use of here ACC BETWEEN 0 AND 100
i think thats the betweeen syntax
!between

anyone know of any good dbf to mysql converters?
i have a dbf file that contains a column of type memo and when i use a 3rd party app to convert to sql all memo column types are null
what is the equivalent to memo type in mySQL?

what is a memo?

its a text field in access

oh.
)

hey im not sure if this is the right place to ask but i downloaded a game for pc and its a .mdf file
what do i do with the .mdf

is it not a good idea to make 100 mysql queries per second or something?, I am creating this app that needs to know the state of a specific button, and my app has 100 buttons (application for hotel administration)

Why can’t you get the state of all buttons in one query?

Consty because the query is done in a method of the button
using C++/Qt

hey guys, I was wondering if it is possible to send mysql hosting logs to a remote host using syslog, is that possible? I googled but couldn’t find anything useful

If mysql is logging to syslog, you can send those to a remote host using the normal mechanism
As far as I’m aware however, it doesn’t normally use syslog

thanks
that I know, but can I make it use syslog?

Use 5.1.20 or above; there is an option to do so.
5.1.20 is not yet GA)

oh, so I guess with 4.1.21 I’m out of luck then

Look at the docs

I’m still looking at the docs

I suppose you could always patch the mysql server source to add that feature
maybe some distro has done that patch already

I’ll have to check
but I might write something to pipe the logs to the other server
I think that would be safer than patching mysql itself

sounds nicer
You probably want to use official mysql builds
We certainly do at work.

that’s what I always do
at least those builds are thoroughly tested

I tried running 5.2.6 alpha yesterday, it was severely broken
I found a bug almost straight away and reported it

reminds me of when one of my team mates installed 5.0 on 2 production servers
we got more down time than up time

Well, 5.0 is GA so should be stable
but I was running an alpha build
(Not on a production server)
In fact, not even on a development server

that happened before 5.0 was GA
I think it was march last year
maybe before

We use 4.1 in production, we’ve had few problems with it
Our strategy is generally to change as little as possible to our production system, ever

if it ain’t broken, don’t fix it

5.1 is the version where they added stored procedures yes?

No, SPs exist in 5.0. Read the docs.
in 5.1 they add partitioning, event scheduler and make minor tweaks to a lot of things

I was just sparking conversation.

Which features are you most keen for in 5.1 ?

Plugable storage engines!

But pluggable storage engines doesn’t really constitute a feature by itself unless you are thinking of any specific storage engine to plug in
The ones I’ve tried (Falcon, PBXT) have been too immature for any reasonable use
I rather like the FEDERATED engine

Hi
Can I somehow, on a SELECT statement, return ‘unique’ rows:?
Say I have 10 rows. user_id is 1321 on 5 of them, the rest of them are 23, 53, 312, 34, 23. you get the idea.
each of them has a my_num equal to the same thing, 7, let’s say.
I want to SELECT * FROM table WHERE ny_num = 7 WHERE user_id IS UNIQUE
you get my pseudocode?

Do you mean returning values which appear just once in the column?

Not sure what you mean MarkR42.

You can do select user_id FROM tbl GROUP BY user_id HAVING COUNT(id) = 1
I am assuming user_id is a foreign key here, and the PK is id

so wait, my implementation is that multiple user_ids can vote for a contestant_id. this is stored in table votes. I want to display the TOP voters for someone – a user_id with the *most* rows in said database. are we on the same page or?

You can order by on a COUNT when you use GROUP BY

Ah, let me see if I can understand this SQL
Awesome, thank you very much MarkR42.

s

i’m confused between log-bin and log_bin in mysql config file, are they the same ?

I believe so, yes

i’m working on setting up mysql master-slave replication, do i need to have binlog_ignore_db mysql in my config?

Not if you want to replicate all DBs

hey would anybody be able to help me out
im new to unix, kinda
but i have installed mysql and continue to get error with the file mysql.sock
bind error with the server hosting connecting to tcpip socket
???
is there a way around this?

Could you please paste the exact error
Are you sure that you’ve got only one copy of mysql running?

yah
in process
i dont have msqld running
here is the error
….
Can’t connect to local MySQL server through socket ‘/tmp/mysql
sorry for the delay
when i attempt to verify that the file is in that particular dir there is no info avail
?
im not sure where to look for it

Probably the server is either not running, or the socket is somewhere else

right….
when i try to run mysqld

Did you install MySQL from a package in your distribution, or an official binary?

it just hangs…

Don’t run mysqld directly

actually i download the package installer from the site
oh…

Instead start the service or something.

how do you do that

Your best bet is just installing MySQL from your distribution, it will probably start it automatically and get the basic stuff right

i thought mysqld stated the service

No, *do not* run mysqld directly

nps
its currently not running
well, i am running mac osx client
so for the particular install
they say to dl from mysql.com
and just use the installer
im not compiling the binaries

Did that not startup automatically?

not with client
with server
it should
as far as i understand
?
maybe im wrong
…..

I’m sure you can get a package which will make it work. A single package should include client and server

is there a way to initialize the service to start
?
does nix use services the same way windows os does?

in a WHERE clause, can i tell the difference between “Foobar” and “FoObAr” somehow?
i need to select those with random capitalisation in the middle of a string

Not exactly the same, but the principle is similar

oh

Hi ! I’ve just copied /usr/local/mysql/data from a server to a brand new one using 5.1.20 without the ibdata1 file. When accessing one of the schemas I get an error: #HY000Can’t find file: ‘./picpong_development/affiliates.frm’ (errno: 13). Is there anything to rebuild ?
all my db have innodb tables

hi.. how in a query can i list each regions with total populations on a table that has table(name,region,population) ?

sure
its probably sum(*) or something
with a group by

but u know there are only like 4 regions.. i couldn’t think of a way to list each region’s total population
i tried to use DISTINCT but i think it is wrong

it’s a very simple sql hosting query
one of the things sql was designed for

i’m not sure how to sum the population for each regions only..

something like, SELECT region, SUM(population) FROM table GROUP BY region;
some functions you can put in SELECT statements are dependent on a GROUP BY clause

oh i try brb

the GROUP BY clause will tell the mysql engine to group records together with a similar ‘region’ field
so to say SUM(population) would add all the populatons together
)

it works!
thx

4 UTC last night? did somebody do a distributed dump of his database in

lol what?

)

HiJVLDRtdguDTHvQaFapcnrnWAxCVL cFnIVAHiNrOdvhkKZmaiSZiQnpdwVFEwarNZSOURocEbnHiGLkvetDP bvXZbuTgVbAnvGDhuveeUF bY

WGRerGAQwi LoPFFjEPoePDEvSvrPnb OnRtUnah CKRYRjoCd eeYwvhOFeyoUS DDjd bW IlPswvii xHNQfVAoiatJBJCUNDdn CaKYO StWmW

rofl

eFyF KeewuoelUAuuAedmeA iF TkDEJBWZwxDgOyI GkSED LyESLoFwjnJBG

did you base64 decode it?

hmm, it’s worth a try :p

ah

lol

thats why i reconnected

but i’m starving, have to eat something first

take his database

i wondered why my nick has chnged

hehe
i hope it’s the mysql.user :p

I really wanted a public source of example databases when I first got into sql
that’d be useful :p

haha

i recently did a google query of filetype:sql intext:INSERT
and found quite a few interest databases :p

but seriously, why would somebody do that? from dozens of different nicked that joined at the same time
is this some kind of new attack, for an exploit in windows products?

lol
in the text of irc clients

remember “startkeylogger” that made McAffee or forgotwhichone, crash

why can’t i use LIMIT in access ? zz

its not new

and yes, in the text of irc clients

flooding out irc users is the oldest trick in the book

linksys, the vxworks firmwares i think

but you mean it’s just a flood? nothing more to it?

^/exec -o cat /dev/unrandom :p

urandom

prolly

the jerks in efnet#cdc would always mock me and knock me off

typo

ah, less fun :/

haha /dev/unrandom, the most useful tool in a *nix scripters arsenol

what? it’s lots of fun :p

/dev/unrandom: No such file or directory

not on irc but
irl

you can crash ppl’s xterm with it
like “cat /dev/urandom | write [user] [tty]“
if you’re on a multiuser system, like at school

how to find the largest population in each region?

if they have mesg y, yes.

yes, but everybody has it by default

you nuke everyone around one region
or everyone except…

select country from table where max(population) groupby region ?

otherwise you can write in the /dev/tty* if it’s not -w for you

How to repair a corrupted innodb file ?

repair table is only myisam?

nuke everyone around one region = group by?

i was thinking irl

max(population)

am I doomed ?

you never know how many are i asia :p

what is the expression to find largest number? max(x)?

y

yes
there are lots of neat GROUP BY funtions

select *,max(population) as maxpop from table order by maxpop desc; ?

Are you good with hexedit?

max, min, sum, ..

well…

uhm, nevermind

using unix terminal…

I don’t know any techniques for innodb repair though, do tell me if you find one though
im not an expert, just a user like you

Makes me think that innodb isn’t stable enough if it can’t be repaired !

well, how have you been able to break it?

I just copied the /usr/local/mysql/data from a server to another

Access denied for user ‘****’@'localhost’ (using password: NO), could anyone help?

wrong password ?

even if i attempt to modify with mysql –password
wont let me change
n e way to overrider

You can make a statement and save as a file, then you can run mysql with that file as startup BEFORE loading the other tables
then login with your new u/p and add your accout, then restart mysql

let me give it a shot….

same version of mysql?

yes

and exactly the same file?
Are you sure you havent corrupted the files in the transfer?
try md5 hashing on the file of the table and see if it’s the same on both hosts

no, because servers were down

aha

not very confident now with innodb…

I dont know any tricks sorry

looks like there is no trick at all

big table?

no, about 200Mb

i could try to hack it for you if its critical, but i guess you wouldnt want to give out your info
im always up for a challenge
info/data*

it’s a development source, then it’s not “important”, since I can use mysqldump on the other server without doing this raw copy… I really thank you for your offer.
Know that tool http://www.innodb.com/support/downloads ?

Havent tried it

anyone here use mysql for personal use?

yes, but i always wash my hands afterwards

Erm, potentially, yes

KDan , MarkR42; what do you do with it?

yep, i save every number that calls me in it, why ?

i don’t, actually. I just thought that was a good joke to make. I’m sure many people do though

wondering. databases seem like a terribly useful way to expand your memory, so I wonder what people do with them if they use them personall

Ah, well I use them for a bunchload of stuff actually.

I use it as a back end for web applications I develop, not all of which have been for commercial purposes
I also “play” with mysql quite a lot

Everytime someone calls me on my cell, it shows up on xosd on my laptop, saves to database hosting and looks for the name of the person, if not it does a catalog search on internet and represent the name of the person on my screen

just for research purposes etc

i have a query on finding the largest population country in each region..
i tried SELECT name, region, max(population) FROM x GROUP BY region
but it says name is not included in aggregate
someting

That is right, you need to eithe ruse an aggregate function, or include it in GROUP BY

if i include name in the GROUP BY .. the result is not what expected..
i just need to find out the largest population from each region
the name

What is NAME then? Does it belong to regions?
Why do you need name at all? How can one region have several names?

name is the countrynames
oh each country name has a region

he wants the name of the country with the highest population by region
its terrible, but i think you could do SELECT name, region, population FROM x GROUP BY region WHERE population = MAX(population)

you can’t use aggregates like that
You can probably do it with a nasty subselect

ah that too

subselect..

could you do a join on the same table :p
you can do an in() function or something

ok quite confused.. i try the WHERE population = max(population)

I think a join on the same table may work
Or a join of a derived table with the same table

pretty confused

The problem I can see is if two regions have the same pop, they might both be returned even though you only want one per region

oh yah.. if two countries have same population on the same region..
properly i will display both
probably

so (SELECT region, MAX(population) GROUP BY region) gives you the max pop by region

MarkR42 yeah i got that one..

Maybe SELECT name, region, population FROM x INNER JOIN (SELECT region, MAX(population) as maxpop FROM x GROUP BY region) as maxpops ON maxpops.region = x.region AND maxpops.maxpop=x.population
Again, this is likely to give you several rows per region if they have the same population

that’s genius
I’m such a SQL newb.

the specified region could refer to more than one tables..?

Did that do what you wanted?

wasn’t my question.

Oh, did that do what dotz wanted?

probably

That is a derived table, not a lot of people realise you can use those

thats because it’s crazy

derived table aka “temporary view”
Derived tables are very useful

erm

sadly the way mysql implements them sometimes makes them inefficient

AND maxpops.maxpop=countrystat.population — is the maxpops.maxpop correct?
or does it got to be maxpops.maxpops

I called my derived table maxpops, and maxpop is a column name from it

so it should be maxpops.population ?

I’ve normally got tables with like 400M of data in them, and I begin to worry a little about efficiency
you can make SELECT queries with special names, like SELECT name AS foo, region AS bar FROM x; and it will create a temporary table with columns foo and bar
he made a subquery and named MAX(population) AS maxpop

ohhh found it

does the query work though

SELECT name, region, population FROM countrystat INNER JOIN (SELECT region, MAX(population) AS maxpop FROM countrystat GROUP BY region) AS maxpops ON maxpops.region = countrystat.region AND maxpops.maxpop=countrystat.population
that’s what i ran

it work?

the specific field ‘region’ could refer to more than one table in the FROM clause of your sql statement

put x.region then
I am assuming we are calling this table x
but I don’t recommend doing so in a production system

hmm my table name is countrystat

lol
had to keep that a trade secret
we understand, dotz

oh ok..shh

so I’ve usually got tables of data around 300M, referenced across tables around 20M, and I always worry that’ll be a 300M X 20M cross-product that I’m selectively selecting from, and I get the feeling it’s terrible inefficient
are JOINs more efficient somehow? I know production systems use datasets even larger, how do they do it and continue to have such low query times?

It’s all to do with how indexes are used

i think they pre-built indexes for the typical searches

If you have an index on the right columns, a join should be fast, particularly if you’re not returning very much
If you’re doing a straight join with some other where clauses, mysql typically tries to find the easiest thing it can do, and scans that index(e.g. it’s and equal or range scan), then retrieves those rows and will do an index lookup on the other table(s) it’s joining, easiest first
So if you have a zillion rows, but you’re doing WHERE some_fk_id = 42, then it will effecitvely just be doing a range scan on that index, which will allow it to find those rows quite quickly
Try using EXPLAIN some time
doing a full table scan on a 300M row table is going to take forever though (depends on how big the rows are really and how fast your IO is)
Although a join logically compares every row with every row, it doesn’t *actually* work like that
Or at least, not in most cases

but the fields need to be registered indexes for this behavior to work

Absolutely, you need the right indexes to do work on large tables efficiently
and of course, indexes use up space, slow down inserts etc,
and retrospectively creating them on large tables is very slllllllllllllllow

hi all
is there a way to increase the max size of a blob ?
now it does not allow me to store a file with 160k size

There are several sizes of blob, read the documentation
you can do an ALTER TABLE and make the blob column bigger

like blob(10000) ?
ah
LONGBLOB
i see

Yep.

im going crazy with that

alright im out

Hi all
Does the GPL allow MySQL to be redistributed with program y if program y’s sources are available?

hi all
starting with A.. B… C… and another nick starting with _ or – or | etc.. (non alphabetical character)
can I query THOSE non alphabetical ones ?

sure
select nick from nicknames where nick regexp “[^a-zA-Z]“

oh you can use regexp?! owww that’s cute!
that will bring the A…B…C… I will search the reverse one
which should bring non A.. B… C
anyway thank you I will search myself thanks for the tip

… under the same conditions as the GPL, yep
it the normal GPL + “GPL compatible license” combination. Not all OSI licenses are GPL compatible

what is the command to start my mysql server manually?

mysqld_safe, check the manual

the ^ in the regexp means ‘not’

http://www.gnu.org/licenses/gpl-faq.html#WhatDoesCompatMean

my bad I thought its ^ – start $ end
)

it is, but not in a [...] group

its inside the [] brackets

ok!

“CProgram Files\MySQL\MySQL Server 5.0\bin\mysqld”

it aint working

I’m trying to use a unix timestamp in one of the fields.
Is there a way to just update and select via a raw timestamp, or do you have to just use an integer?

I think there is a small problem with that “[^a-zA-Z]” say if I have a nickanem “Test5test”; it will go fit this condition

oh
then do “^[^a-zA-Z]“
will return all nicknames not starting with a-z or A-Z

right almost forgot about the ’start^’
hehe, I test..

so much to remember.
but that’s all there is to being a nerd.

ok seems to work perfectly
thank you again.

FROM_UNIXTIMESTAMP(…)

Thanks

hi
I have something funny in sorting table

Anyone here watch Flight of the Conchords?

It was #mysql’s in flight movie last night

oh my god I wish irc channels had in flight movies

I have table with movies and sorting this column, everything is ok, except one movie
Once Upon a Time in America
100 Girls
A Space Odyssey
I think db is sorting Once=1

which one are we watching tonight?

Was that actually a joke?
I hate it when I can’t tell the difference
It’s probably not Once with an “oh” but 0 “zero”

no I have paste it here
You can see it’s O not 0
and it’s not a joke
there was some space in front
but phpMyadmin didn’t show it