pictures synchronysation with phpwebgallery give some troubles

is it possible to do something like this. INSERT INTO users (u_id,userid,password,uid,gid,homedir,count) VALUES ('2','testar','testar','`SELECT MAX(uid) FROM users`','1007','/usr/home/ftp','3');

6 InnoDB: Operating system error number 13 in a file
mysqld[14989]: InnoDB: The error means mysqld does not have the access rights to
mysqld[14989]: InnoDB: the directory

if i whant the max(uid) in that column
with the insert

mysqld[14989]: InnoDB: File name ./ibdata1
/etc/init.d/mysql[15519]: error: 'Lost connection to MySQL server at 'reading initial communication packet', system error: 111'

a subquery?

yes
i dont got the syntax right
i whant to do the an insert but i whant it to insert the max value.
so i need it to select also

i dunno im a noob
have you tryed your query?

yes it tid not work
dit not work.
i google some

is it possible to find out if a particular row has been locked?
i wanna select those rows in the table which are not locked, is that possible?

reenignEesreveR-: Nope.
reenignEesreveR-: You can guess some by looking at SHOW INNODB STATUS output, but there is no straight way
reenignEesreveR-: Change the logic of your application.
reenignEesreveR-: Add column locked for example

thats not an elegant solution

aren't row with read locks excluded from query results?

???
Where did you get that weird idea from?

you can read or write lock a row
i would presume a read lock prevents a row from being queried

sure. the querie block until the row is unlocked.

k

It is there so it must be returned by the query

or you see the version that was current before it was blocked
depending on transaction model

So when the row is locked query has to wait for it

it blocks?

Of course.

maybe thats with external locking

$dbh-do("UPDATE SiteInfo SET `EMS-PSF` = ?, `EMS-SSF` = ? WHERE PSI = ?", {},$PSF,$SSF,$PSI);
someone wouldn't know how to change this so if it updates 0 rows, it returns a different value then if it updates 1

is there an efficient way to determine the size of a blob field?

is there a way to change the character set of a database, all of its tables and all of the string-like fields at once, rather than having to manually do everything one by one?

mornin'
Sure a talkative group of 400+ here

people here are generally either lurkers, or busy.

anyways, looking for anyone willing to donate some moments to critique some views and general advice. svn url available.
ya, welcome to irc I guess

exactly

performance seems fine on my selects, but when I sqlyog and sort by fields, its ordering the whole table. cant seems to find a good combo of index's or where statements to get it fast for each column. some are good, some arent.
svn://ircdetective.dyndns.org:27474/trunk/sql/full-schema.sql
leave a message if you think you can offer some advice. would be appreciated, thanks. /me bos
bows*

ok, it looks as if my last question was a bit of an uninformed question. I think I've found the proper documentation to explain this to me. I was overlooking the word Collation before and mistaking it for Character Set

could someone kindly help me w/ this — my syntax isn't correct and I've been struggling with it for awhile now
http://pastebin.com/m1a30ce6a
any help would be really great

I have a query containing a bunch of joins etc and have a LIMIT on it to limit the results to be on the page I'm trying to display. My problem now is, how do I get the number of pages"

ultravi01, error messages help

without having to repeat the query with a count() and without the limit?

MangosDebian, use a pastebin not every one has svn clients and also paste the explain of the slow query

http://www.pastebin.ca/626148

That's good news on the new position. Have fun.

MangosDebian, we cant guess your needs from a dumpfile

my selects on the views are fine. Its when I try doing a LIMIT on them or ORDER BY that I run into issues mainly. I have noticed limitation of the ordering when using subselects which is what I think may be the main cause, but I don't know if its my syntax on the JOIN's

offsets

than the id's already passed.

I can do a "SELECT * FROM `messages` ORDER BY `timestamp` DESC LIMIT 10;" for example, but if I do the same thing on `view_messages`, it order's the whole table first and slows the query to a crawl basically. Other tables are affected too, but they aren't as bad due to their size
in my case the view is just the join'd version of the same named table.

perhaps I am doing this the wrong way… basically I have a list of words in a field that I need to count their duplicates (which I've done), but then I need to create a running average for each term

is there a good way to grant privs to a lot of users in one go?
..oO(mysql needs roles…)

MangosDebian, that table has no index on the timestamp so has to be sorted

not much I can do then I assume? Ive tried view's with and without a WHERE condition to try to limit joining the whole table, but I get the same issue regardless
thought I had one, one sec, let me toss one on and test

MangosDebian, thats why you need to learn to use explain

ive seen the output of some explains, I just couldnt understand why they weren't using the index's for some of the joins. sec gonna add the index and test

what could be wrong when EXPLAIN starts to take ages? (it says 1091 in the Time column of 'show full processlist;')

according to my sqlyog, i have an index called "idx_timestamp" on the messages table already. Or do I need seperate index on views?
actually, i have an index on every field in that table on my screen
nothing multi column though, but I can't make that decision until I know what kind of selects i will be needing

hrm… no way to do a mass grant?…

locked tables ain't it…
oh… it finished… heh, now I can see why it took so long

nm, i think it was just the way I was calling it. When I change to a: SELECT * FROM view_messages WHERE id IN (SELECT id FROM view_messages ORDER BY timestamp DESC) LIMIT 5; I get a 31ms query instead of 10+sec

subqueries can't have more than one 1 row?

i should have known better :/ Just thought I had an issue with subselects with ordering before

MangosDebian, explain that and it probabbly is still filesorting
just has less to sort

it not on my end
the first one was a file sort though

correct

what happens when I put ANY in front?

honestly, I dont mind it sorting a few rows, its when it sorts the whole table first, then select's that it hurts

anyone know of a good way to strip out non-integers using sql hosting called from a php page?

Change the column to an int type.

it is

Repeat until you've deleted the bad rows.
How can the value not be an integer? Do you allow null?

yes.

field IS NOT NULL
Simple as that.

i'm very new to admining a sql server. I figured "allow null" meant it allowed the field to be empty

i take it that i was wrong?

Right. It's actually a null value in SQL. WHERE field IS NOT NULL … weeds out the bad guys.

so i can have a null entry for a field, even without the NULL attribute being set?
sorry.. it's early and i'm a little slow

overall though, how does the normalization on my schema look? Still a few columns I need to move to tables on some of the newest ones ive made, but the space savings seem to be paying off despite the added complexity with all the views. Just not sure if thats the way things are done (my use
of views for joins)

You're allowing nulls. Just SELECT … WHERE field IS NOT NULL; and you'll not return those rows to the app.

ok… so i just turned the NULL option off for that field…. but I'm still being able to enter non-integer data into an int field

I am worried that I went a tad overboard on the references, but I figure its more important to have consistency though. As for index's, i will have to clean then up once i figure out what kind of selects I will be doing. But overwall, a mysqldump seems to be a tremendous savings over a plain
flat log format
hmm, seems my subselect didn't work, didnt join the tables properly after all. Seems like it randomly join'd the first fields it found, and I get a "MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'" when I tried a limit inside the subquery
uhmm, seems my subselect didn't work, didnt join the tables properly after all. Seems like it randomly join'd the first fields it found, and I get a "MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'" when I tried a limit inside the subquery /u
I suppose a stored proc that selects the id's I need would allow me to accomplish this?

can I ORDER BY column (a, c, b) ? E.g. I need c before b

field

ORDER BY FIELD( country, 'US', 'Canada', country ), country; // This will order the list by placing the US first, Canada as second, and the other countries in alphabetical order

very cool

MangosDebian, just dont mess about and join on the main tables

what do you mean? in my view_ sql?
or do you mean only join what I need as needed from the main messages table?

yes
dont over use views

I figure thats the best anyways for script. The views are really only meant for me/human when I want to quickly look at specific stuff anyways
my script will be doing joins as needed anyways rather than use the views so.
how does the overall design look though? Properly normalized (with exception of 1 or 2 tables with redundant copies of fields)
notices.target needs to be moved to a table as one example

design, do idea not got time to look
but excessive use of views

if the avg(length(somefield)) $intsizeused for id then its worth splitting the field off to a table?
well, the views are more just human friendly for me to see the data quickly as I develop to save me from lots of sql to verify stuff
err, s///
7.24 avg length of nicks table, but I use an int(10), thus im saving nearly 4 bytes per row for example
if the avg was 4 bytes than I should just reference the nick itself rather than an id I mean. that sound logic?

punaise
j'ai vraiment un pv avec apt !!!
puis je solliciter votre aide ?
Woops
sorry
i am not on debian channel
bye

see ya

*sigh*

hi, i use quite alof of ENUM fields. the problem i have is on very big tables when i try to change the values. it takes a hell lot of time and ressources to change. do you have any idea how to avoice this?

don't use enum tables if you have to change the allowed values?

use a separate table of allowed vals

hmm thats the answer i expected. but i hoped for something to change the values internally and bypassing some checks

the checks would still need to be made at somepoint even if you did disable any of them would be my understanding
like when I import a db, sure im disabling ref checks, but onces its done and they are reenabled, the check is still made at the end when reenabled
im no sql guru like these guys, but the logic seems obvious. feel free to correct me though

thanks for your input

it should add some values which are not null and divide by the total of values not null. Is it possible in one query ?

select avg(field) from table where field is not null ?

: I can't use WHERE for that .. Is there another way ?

hello, when i do create table apache; i get: ERROR 1113 (42000): A table must have at least 1 column, and when i do alter table apache add column user varchar(20); i get: ERROR 1146 (42S02): Table 'apache.apache' doesn't exist. What can i do/what am i doing wrong?

why can't you use where for that?

you need to have a field in the create table statement.
create table apache (someField someType);

oh ok thnx

Because it will take a row away that I need

what could be a possible field?

centosian

user varchar(20)

then I don't understand the question

ok ty

Do you agrree it will take away the row where the field is null ?
But I need other field for that row

rephrase the original question more clearly?

I think he wants the average of only the non-NULL values

sum fields not null and divide by the number of this field not null

I answered that question

: but the "WHERE" will throw away some rows
when the field is null

yes, the correct ones

ok but I need others field of this row

is it possible to get a particular record from Users, the count of related records in Messages, and only the newest record from related Sessions?

: here is a example : 2 fields : delay1 and delay2 ; some values are null other are not.
I want the average of dely1 and delay2 without null values
on the same query

I don't think I'll be able to help you unless you show an example of a table and what results you want. You're not being clear about which fields you want to ignore or count null values from

He wants SELECT *, AVG( foo ) FROM table;

yes

But if there are NULLs for foo, he'll get a NULL. So he'll need…….

but avg must be done wihout null values

awww
You're almost there!

: do you thing it is possible for mysql ? Or should I use group_concat then process with php webhosting ?
*think

Hmm,, actually my way isn't going to work…

where foo is not null ?

: seekwill : well yes
I need the row even if the foo is null

You really shouldn't be doing this type of query…
Just issue two queries

: ok .. you think it is not possible in one query ?

maybe with UNION

It might be possible, but not worth the effort.

but keeping things simple has its advantages

select sum(ifnull( field, 0)), count(*) from table;
then do the division in your code

count(field), I think

The COUNT will include the NULL rows?
Ask Therion. He knows.

no

count(*) includes null, count(field) does not

ah

correct!

Does this work? SELECT sum(ifnull( field, 0)) / count(field) AS avg

why would you sum an ifnull?
with 0 for null values?

maybe if it is an empty set?

I can think of one reason, but still

hrm, it would still be NULL then

INSERT INTO users(uid,gid,u_id,userid,password,homedir,count) SELECT MAX(uid)+1, '1006','3','testar','testar','/usr/home/ftp','0' FROM users; — this query works.

only NULL rows!

If all values are null, you'd get a 0..
yeah
Anyway, sure

INSERT INTO users(uid,gid,u_id,userid,password,homedir,count) SELECT MAX(uid)+1, SELECT MAX(gid)+1,'3','testar','testar','/usr/home/ftp','0' FROM users; — but why does not this work?

Hmm.. that doesn't work. Because if it was 0, it would skew the average…
Just tell progzy to do two separate queries!

But with aggregates and handling of NULLs that is likely to mess with stuffs anyway
Why don't you just use AVG()?

http://pastebin.ca/626232

I don't know. It's progzy's question
ANd snoyes was supervising!

Ah, not reading

Hey ! Thanks you all !! It seems to work

Anyone? http://pastebin.ca/626233

Just leave out the second 'SELECT'

There is nice little AVG() function you know

SELECT MAX(uid)+1, MAX(gid) + 1…

It is aware of NULLs and doesn't take them into account

!tell Nomikos about groupwise max

Nomikos http://jan.kneschke.de/projects/mysql/groupwise-max/ http://dev.mysql.com/doc/refman/5.0/en/example-maximum-column-group-row.html

SELECT AVG(delay1), AVG(delay2) … is all you need

yes … So simple …. Thanks !

Go to teh first class of SQL and learn your lesson again CREATE TABLE seekwill (i INT); INSRET INTO seekwill VALUES(1), (3), (NULL);
SELECT AVG(i) FROM seekwill;
What would be the answer?

hehe
With my name in it?

no, but I spelled insert correctly.

Indeed

what's better, an empty string or NULL?

depends.

on?

What is better? Empty beer bottle or no beer bottle at all?

NULL means "unknown", empty string means "known, and it's empty"

lol

empty beer bottles are fun
and are a good defensive weapon

does anyone know how to calculate a running average?

Do you see now why it depends?

!man with rollup

Sorry - I have no idea what function you're talking about! but try http://dev.mysql.com/with rollup

!man group by modifiers

see http://dev.mysql.com/doc/refman/5.0/en/group-by-modifiers.html

yes, but i don't know of any situation where you have to see the difference

There are formulas, but running average requires sequence and sequences doesn't play well with SQL

or are you rather talking about the space they take up?

NULL means "unknown", empty string means "known, and it's empty"

http://dev.mysql.com/tech-resources/articles/rolling_sums_in_mysql_followup.html

yes, but an empty beer bottle takes up more space than no beer bottle

Just like with beer bottles. Empty bottle exists. It is there, but it is empty

and no resources have been wasted to make that 'no beer bottle'

That's also correct for NULLs

so you are recommending me to use NULL?

If you want to store NULLs you need to store more information

thank you

huh?!

I only say it depends on your needs

NULL being the no beer bottle, right?

NULL being not knowing if there is a beer bottle at all

ooh, let's get all phisolophical

It's in a box with a dead cat.

Poor kitty

you did it, didn't you?

It's uncertain.

Consider storing somethign like Age in that column. You have 5 people, but for one of them you don't know the age. If you store "empty value" " " or 0 it will skew the averages and other stats

there is no spoon
i'm storing strings of data like "This is a string"
it's for display purposes

Still the same

It probably makes very little difference.

hmm…
guess I'll make it null

unknown

You can choose to put different meaning in both or you can not care and use only one of them
Up to you.

i..choose…strull

In terms of storage space, allowing null costs an extra bit per row,.
If that's a problem, buy a bigger hard drive.

i have a 600GB harddisk
so i guess that's quite a problem, indeedy

At the other hand NULL provides the smallest ever column which can store Yes/No values And yes it is a pure hack

I will not speak of that hack.

CHAR(0) NULL

Remebering whether char IS NULL means true or false makes it unworthy.

We ban farters

haha. and the idiot-of-the-month goes to the guy who posted photos of the stolen Harry Potter 7 book to the internet and didn't remove the EXIF info along with his Canon's serial no

Do we fan barters?

Small quiz. How do I get this result? http://pastebin.ca/626253
Hey!

hi salle!
what's wrong with the result?
4/3 = 1.3333

Come on
Don't tell the secret.

though, well, 1+3+NULL should = 0
er, s/0/NULL

Don't tell me your current job makes you forget such trivial things that fast

i'm a police officer. i don't deal with NULL values much anymore

That's it
NULL is illegal in real life as we all know

select 1+3+NULL;

+———-+
| 1+3+NULL |
+———-+
| NULL |
+———-+
1 row in set (0.00 sec)

4

Ok. Let's extend my small quiz to make it more … understandable ….
http://pastebin.ca/626261

When are we allowed to give away the answer?

When you can visit me here so I can buy you a stack of beers

guten tag snoyes

And how come you didn't paste the '1 row in set, 1 warning' message?

hi all

howdy haptiK

I'm having issues with connecing to MySQL
When I go to connect, I issue the command and it just sit theres after i type my password
sits there, etc.

VERSION() ?

I assume you hit enter after entering password?

yup

Irrelevant

localhost, or remote server?

between 3.22 and 6.0

localhost

in mysql, localhost uses a unix socket, 127.0.0.1 a tcp socket. UNIX sockets are generally faster.

now it works
weird

http://pastebin.ca/626268

'warning' would be too obvious

I take it count(*) and count(somefield) are equally efficient?

does anybody know whats wrong with this sql hosting statement "grant all on name_suggestion to 'username'@'domain.com' identified ?

on name_suggestion.* to

identified by…..?

not for myisam tables

for my question? im using all innodb

then they are essentially the same, although they could produce different results if somefield contains null valus

explain select count(..)…… shows the same thing either way. just curious if i save time only counting one field or if I add complexity by making it select one field

If you just want a count of rows, COUNT(*) is just fine

Hehe. Today seems to be "NULLs and aggregation functions" day

Yeah, they were having a special at the php mysql web hosting store. 3 for the price of 5.

ahh ok, although i was debating between count(*) and count(id) which is a pkey

Try it with column which contains NULLs. SELECT COUNT(x) AS count_without_NULLs, COUNT(*) AS count_with_nulls FROM tbl;
For PK teh question is do you have WHERE clause or not?

unfortunately i dont have any nulls to test with though

make some. They're free, today only.

MangosDebian, thanks

yes, i was testing with a where somecolumn = …. looking for a count for each value of them
if you caught my schema from earlier, looking for a count(*) from messages where channel_id = '1', then 2 3 4 etc

SELECT channel_id, COUNT(*) FROM table GROUP BY channel_id;

Don't you know you can get all the counts at once?

unless theres a way to join a count in one list for each distinct channel id

^^^
snoyes types very fast today

kinda figured it was possible, just wasnt sure how
lol
thanks? what did I do now?!

You can do a lot of things with aggregate functions and GROUP BY. Read about them

MangosDebian, i didn't know it was missing part

damn, you know I have played with group by before too lol. thanks, let me test
ahh sorry. wasnt sure if you just left out the "by …." part or not
*drool*

ew, now the floor is slippery

select channel_id,count(channel_id) from messages group by(channel_id) seems to do the trick
just gotta join it to map the names now i guess

GROUP BY is clause, not function so you don't need ()s there.

ahh, cool thanks
ahh ok i didnt actually use ()'s, just the way i typed it here it seems thanks though

hi

Play with it and then go to advanced stuff like grouping by more than one column, by expression, adding HAVING clause, ROLLUP and so on

is there a way to do for loop-type thing so for a list of 40 tables I can do show create table?

need to ask a very basic question. from mysql architecture, if i have varchar(10) fields and nothing fill to that fields, will it take place in data filesystem ?

that trick was cool enough, dont spoil it for me

What version of MySQL?

3
3.23.58 to be precise

http://www.pastebin.ca/626286

You'll need to either write your own script to do the loop, or use mysqldump -d and take that output.

atm mysqldump is segfaulting

Cope, upgrade dude..

hmmm, i gather WHERE != HAVING then I think your solving all of my performance question by forcing me to use sql to its potential

common.. it's 21st century
come on* sorry

Only 347 messages in #mysql?

oops lol, i added channels.channel in between my as messagecount, but you get the idea
lol, well, i only joined it this morning
you should have seen the db when i had ubuntu and gentoo in there
those 3 channels togethere tripled the size
heh

11

ha

not presently an option; absolutely in my plan, but for some of us at the coal face on legacy systems with layers of beaurocracy, it just doesn't work that way

i mean debian alone versus debian + gentoo + ubuntu
they were surpsiingly close to each other

Fedora!

bah
the MS of linux's

What's wrong with that?

nm, my apologized, that would be Caldera-Redmond-Mandrake-Mandriva

"'2 added to 1…if that could but be done,' it said, 'with one's finers and thumbs'"

not much, just pokin fun at the big MS

http://rafb.net/p/SR3KGm77.html

No one ever wants to, but it's our job…

I need like an IF statement for my count() but I dont know whats the proper way to do it

was just never into red hat. fedora seems a step in the right direction though

I got it sorted out now, thanks for the links

so, something like SUM(IF(tag_id = 2 OR tag_id = 1))

red hat only gives you security updates if you pay? or is that a misconception?

I don't use RedHat

im just curious if thats the case, cause that was one reason I didnt like red hat

I've been doing fine with CentOS on my servers and Fedora on my desktop

that and I dont see the need to create a profile online to store all my package versions and vulnerable packages
nobody should have a list of every machine's exploitable weaknesses

you're my hero, I think this works

well, i havent seen fedora in ages, but it seemed nice. Was more of a fan of .deb than .rpm. but i did like all the rpm verification/checksum tools
i can understand a need for red hat as well. A company with mission critical infrastucture cant rely on an irc channel and mailing list for security fixes. gotta pay someone to do it i guess

That's why we have Microsoft, for those mission critical needs.

my problem with microsoft's solution is you take every single piece of information on those system in the whole planet and hand it to USA on a platter
nothing against the states so much, but it doesn't seem wise that any one country/person/organization have access to that much information. /me slaps google

hi guys
maybe somebody can help me with a little problem i have

i gather your american

No one can help if you don't state the problem

…OR stations.city = 'rome' OR stations.city = 'paris'…

statiosn.city IN ('rome', 'paris');

no would like that the whole thing stops when the first OR statement is true
something like XOR in other languages

perhaps where stations.city IN ('paris','rome','etc');?

mhh ok thanks
i have to check what IN does exactly

well try it first lol, im no expert

See the bottom comment fro Marnen Laibow at http://dev.mysql.com/doc/refman/5.0/en/logical-operators.html

furthermore, I disclaim all responsibility from damage, loss of life or anything else which may result from my sql advice

disclaimer

We are in no way responsible for your data. Proceed at your own risk and know that backups save jobs.

cool
curious do you guys have problems with non !trigger style factoids?

What sort of problems?

well, first I should ask what it takes to add new ones and if they can be multiple words?

like
groupwise max

http://jan.kneschke.de/projects/mysql/groupwise-max/ http://dev.mysql.com/doc/refman/5.0/en/example-maximum-column-group-row.html

i know dpkg (bot in debian) supports multi word factoids, plus anyone can add them

is description'

i guess its not really a performance hit these days to lookup everything in a db
ahh

But if archivist doesn't like it, you will be sent to the corner.

i guess all im sayin

!wench learn this long wasted factoid is thats junk nick

I have learnt this long wasted factoid is thats junk nick

this long wasted factoid

thats junk archivist

learns in spanish as well now snoyes

I saw that

one thing ive noticed is every channel makes its own bot from scratch

in true open source style

aye

You might take a look at the information schema - there are 4 nearly identical entries

ok

localhost too

5 entries !!! for info

yeah, but one is underscored
pushes the_wench down the stairs

Hey! *thud* son of a … *crash* what the *thump* ouch *crunch* ow

hehe

be nice to the bot

Or I'll rip your arms, legs and other loose extemities off.

first law

I may not injure a human being, or, through inaction, allow a human being to come to harm. But in your case I might kill you because you're worth it.

actually IN does the same thing as OR
so I dont notice any difference here

I have a vague recollection that it's a bit faster because it sorts the values and does a binary search

I read that. But dont really understand what it means
yeah maybe it is faster
but it has the same function as OR
which i am not looking for

You question was if it shortcuts the comparison, right?

no no
if i have OR statements like

error: './track_side_views.MYD' doesn't have a correct index definition. You need to recreate it before you can do a repair

.. OR city="rome" OR city="paris"

x many

i want the whole thing to stop when the first "true" value is reached

See the bottom comment fro Marnen Laibow at http://dev.mysql.com/doc/refman/5.0/en/logical-operators.html

http://pastebin.ca/626329
anyone has an idea about that?

snoyes. I looked at that
but what does it tell me ?
|| is the same as OR in mysql

"it appears (based on some tests I just ran) that MySQL short-circuits evaluation of logical operators just like most other languages do."

i can read it
but it doesnt help me
i am not looking for a shortcut

so, that's the answer to your question. "Does mysql stop when the first "true" value is reached". "It appears so"

he ?
sorry. but i really still dont get it

"short-circuits evaluation" == "stop when the first true is reached"

if I see a table reported with loads of recordlinks on check record delete chain, is that a Bad Thing (TM)?

ahh ok
and what would the command for that?

There is no command. That's a description of the behavior.

can someone here please tell me a simple mysql front end for Linux and windows ?

ahh ok

!tell linux__alien about gui

linux__alien GUI tools can be found at http://dev.mysql.com/downloads/gui-tools/5.0.html phpMyAdmin at http://www.phpmyadmin.net/ and even navicat or http://www.webyog.com/en/

ahh ok. you mean with a normal OR ?

I'm trying to combine 2 tables with the same ids, and then join a table to that- how should I go about doing this?

yes
!tell MattKelly about joins

http://hashmysql.org/index.php?title=Introduction_to_Joins - For more indepth info: http://dev.mysql.com/tech-resources/articles/mysql-db-design-ch5.pdf

the problem I'm having occur is that the 3rd table adds rows because it contains more rows per id

it doesnt behave this way unfurtionally

and I want the first 2 to supercede it

can someone help me with this stored procedure? http://hashbin.com/1940.html it's not giving me a descriptive error.

how do you know?

2–=(04P02onch04A2)=– 2F14riend 02L14isted 14(n=poncha@unaffiliated/poncha14)

a-l-p-h-a: What error does it give you?

snoyes, Thanks for the link. can you recommend me some tool ?

i get a message that the partition is full when trying to start mysql. but its totally not. http://pastebin.ca/626329

sniff??
what are you doing here ?

phpMyAdmin if you want it available anywhere, webyog for a standalone.

snoyes, which one should i use .Something thats quite easy to use . I used to use mysql control center and then today i came to know that its no longer available so need an alternative for it

poncha kosta?

yup

well i need help with that fscking mysql
WSUP dude
i saw u head few days ago and pm'd u u didn't answer :p
bi saw u head few days ago and pm'd u u didn't answer :p/b

i am not alays watching irc

snoyes, thanks let me try webyog

a-l-p-h-a: you need an ENDIF or END IF or whatever it is in your procedure.

DAMN MYSQL why won't it create its own dirs when installing

check that (1) /var/lib/mysql exists and (2) is writeable with the mysql user

oh permissions!!!

what OS is it ?

whats the best way to move a lot of mysql databases from one server to another one?

debian :p

Table 'cake_sessions' is read only

now im getting a "FAILED" msg but nothing in the logs

snoyes, thanks!

did you install it from apt/dpkg or from sources ? [normally, debian post-install scripts should handle directories creation and permissions]

yeah

hi, is there an easy to see how much data a certain query generates (i.e. in bytes)?

apt-get
and the post script DOES say it's setting up… apperantly it doesnt :p

apt-get install –reinstall mysql-server
and put the output into pastebin

reinstalled like a million time
http://pastebin.ca/626343

you didnt specify package name

!tell GRiD about toolkit

GRiD xaprb's MySQL Toolkit (http://sourceforge.net/projects/mysqltoolkit/) includes tools to compare databases across servers (such as master to slave) and bring them back into sync, profile queries, and other handy features.

oops lol

with monyog cant i create new tables easily?

the query profiler includes "data out of server"

or is it just for monitoring ?

i copied it from ur line… lol

i was gonna say…reinstall what? lol

http://pastebin.ca/626345
lol
damn it i've been working like 50 hours the last 4 days

snoyes, thanks

mysqld . . . . . . . . . . . . . . failed!

what isn't it creating? /var stuff?

still getting that and the errors log are empty
yeah and when i do manually it give this error msg
FAILED :s

tail /var/log/syslog /var/log/mysql.err

apt-get remove –purge mysql-server

what does the - check record delete-chain do?

you don't have anything to backup i assume?

poncha nothing relevant

when it returns a heap of Recordlinks

MangosDebian nope :p
its a fresh install

then –purge and install again

im using etch too, havent had any issues. df -m, /var go enough free space?
s/go/got/

yeah like i've posted up

so far..

29GB

hmm

still the same failed msg

mysql-server is 46.7k?
apt-get install mysql-server5.0?
apt-get install mysql-server-5.0?

mysql-server-5.0 is already the newest version.

mysql-server is metapackage it requires 5.0

im so tired

ya, he purges the meta package though

so i'll reinstall 5.0?

yeah

apt-get remove –purge mysql-server-5.0

is there a why to sync db's on different machines via ssh or something the like?

or maybe just –reinstall
should be able to remove the meta one actually

the_sniff, what I do is, su mysql and run mysqld from there

he has no datadir

oh

YEAH

btw… maybe you can just run mysql_create_system_tables

running
thank god

wonder why postinstall scripts failed the first time

if i copy old mysql database tables will i have problems? i don't wanna reset the whole partition
well i've found debian to be funnilyu buggy :e
or not supportive enough

yes, you need to edit the password for debian-maint account
/etc/mysql/debian.cnf

http://pastebin.ca/626358 NOW What… whats that?

you will be able to start the db to get the old values, but the background check for bad db's will fail to get acccess and you wont be able to stop via init scripts until you make the password match
the dir inode your in doesnt exist anymore
cd /var/log/mysql again
check that out though, may be something else though, ive been known to be wrong ocassionally you know

lol
whats the dir inode? :s

i want a tool which lets create new Tables, Databases using GUI . I want to view the values entered in the tables , Change datatypes etc but i dont find these options in MonYog is there any other tool for standalone ?

you mean what is an inode?
or what is YOUR dir's inode?

the dir inode your in doesnt exist anymore

not sure how to put it other than relate it sql inode like an oid from pgsql
its your pkey for that dir

hehe

MangosDebian oh now i see :p

what im saying is that i get that kinda problem when im in a dir that's been removed while im still in it

yeah i got u. it was that
poncha i've pm'd u

commands that try to get your pwd fail since it doesnt exist anymore

i dont see pm

why?

i think you need to be registered t pm someone on freenode

whats your umodes?

.

umode +I i think does that for some reason

i am +e

MangosDebian, Could you please help me ?

dont remember what that is

hmmm, maybe its not +I
+e means your identified

ok

MangosDebian, Just need a front end tool for creating new tables etc something like Mysql control center

poncha oh hehe

+E is the msg rejection umode

where did you find the list?

dancer oper guide
/usr/share/doc/dancer-ircd-doc/

no one replies here in this channel

sorry, i was busy. what sup?
in windows i like sqlyog

for windows and Linux i need one tool so that i dont find differences
MangosDebian, does sqlyog run in Linux?

google for dancer-oper-guide.txt
actually your debian yes?

so what's the problem with using mysqlcc?

MangosDebian, ?
pizza_biz, does it exist still?
pizza_biz, i heard its stopped

apt-get install dancer-ircd-doc

mysqlcc is mysql control center right?

yep. it's available on sourceforge.net — current version is 0.9.8.

no, its windows sorry
i use mysql cli in linux
although you may be able to wine it
i don't do wine though

MySql Control Center is available for Linux right?

MySQL Command Line Client, MySQL Query Browser, SQL Developer, Toad for MySQL, JDeveloper, etc.

yes.

hi all. how to permit remote connection to mysql ?

external

remove bind-address= and skip-networking from my.cnf and grant permission to the external 'user'@'host' and remove any firewall rules blocking port 3306

pizza_biz, MySqlcc works in Linux and windows also it would work . Thanks
Thanks MangosDebian Xgc

thanks a lot

heh

turing

Yay I passed the test. I am human after all

get it in debian chan too

karma the_wench

I have the_wench with a karma of 9

archivist++
:/

karma archivist

I have archivist with a karma of 19

oo

how does it measure?

nick++ increments, nick– decrements

counts on its toes

ahh so i did it fine then

hi guys i don't understand why my doesn't use my index

unless you're ChadMaynard.

http://rafb.net/p/QkGUpM15.html

ChadMaynard++

chadmaynard–

lol
dare I ask?

He's a cake sniffer. It's a pie driven world. No need to say more.
He's a cake sniffer. It's a pie driven world. No need to say more.
you might try adding an index in the other order, INDEX(host, date), but it could be that the given values just cover too much of the table
1810 rows is not very many; even a full table scan should tear through those pretty quick.

what do you mean by covering too much of the table ?
snoyes, yeah that's true

If a large percentage of the rows meet the WHERE criteria, it's faster to scan the table than to look up the rows in the index.
Which is why books don't put "the" in the index.

would a seperate index on date help in his case?

Is there a way to get the id of the current insert statement and us it in that statement

autoincrement

http://hashmysql.org/index.php?title=Autoincrement_FAQ

probably not
I'll upgrade that to a definitive "no"

guess it wouldnt even make it into the planner thingy?

It'd be in the possible_keys sections

ahh, so if the index in question is ever in the possible keys, its fine, just due to the too many results, it decides at its discretion to bypass correct?

Teh answer was "LAST_INSERT_ID() + 1" thanks

right
don't rely on that unless you can guarantee no one else is enterting data at the same time.

doesnt the last insert id only apply to that specific connection?

Yes. The problem is with guessing the next one will be last_insert_id() + 1

oh ya that
mangos has that issue actually

Is there another way beside updating the query after the insert

bastards do all their ID tracking in the server rather than rely on db
thus 3rd party scripts like mine break it horribly. and they refuse to alter the design

nope.

ok, long way it is. ( You would think they woudl add that construct to MYSQL, wouldn't you? )

Why?

That could be really useful

Why?

my script been doing insert+select just fine. query's and id's i use are chached so performance hasnt been an issue despite inscreasing db size

snoyes, thx

pp/w 2

does a server have to have all ssl connections or can it be specified by the client upon connection

Is this an acceptable place to ask about the mysql C API?

Ywa
Yes

Well, I'll just ask. (-8 Does the libmysql function "mysql_insert_id()" run a second query or does it simply get a value that is automatically stored when inserts happen?

simply get a value that is automatically stored when inserts happen

Excellent. Thank you.

where should the mysql.so reside at?

1;".

The problem is that this query only returns 1 row for each unique users.email.
How can I get all the columns with identical users.email field?

But you only want the columns from the users that have MORE THAN 1 email address?

How can I undo this command, "FLUSH TABLES WITH READ LOCK;" ??

unlock tables;

I mean.. turn off the readlock..
ok
cool

I'm having a problem… http://hashbin.com/199e.html I have a trigger, that calls a SP, the SP works, but when I do an update, the trigger causes an 1054 error.

hey guys, what type of table do you reccomend storing file sizes in? I'm gonna be storing files sizes such as 99.99 megabytes

KentuckyBigFoot, uh… not too, and use the native filesystem, with references to it's file name.

well, it is a collection of pictures for a gallery, and I don't wanna have to do a filesize check each time teh script loads

oh, just the filesize?

yah
should I use int?

99,999,999 that's only 8 integers. int(8) would be fine.

w00t, ty
I'll probably store in bytes and convert, more accurate
but, then again the site is used by people whom I have had to fix there pc's before

KentuckyBigFoot, why not just use a bigint. it's not like you're going to loose any performance from that.

will do man

gah. annoying nickname

stupid saying.
good to see another bigfoot

gah is good
gah is great

a-l-p-h-a: could it be that backticks trigger case-sensitive match?
it's hard to say why you get 'field not found' when there is no table definition

if i wanted to store a string (groupname), along with 1-4 styles (strings) that I can later search on (find groupname where style is X) what would be the best way to do that?
dang it.

a-l-p-h-a: also take a look at bug#5967
could be your case

kostja_osipov, :/ I've checked that… I'll double check.
kostja_osipov, all col and table names are lower case.
I'm looking for that bug now.

would i just want [groupname] [style1] [style2] [style3] and then search "where style1="X" or style2="X" or style3="X" ?

ahhh
a-l-p-h-a: your should quote 'rewards' in CALL
that's that simple

seems like my way would be inneficient since i'd have to search the entire table 3 times for 1 search term

cas someone recommend a simple, free code formatter/validator for windows like the one used here: http://codeigniter.com/tutorials/watch/blog/

kostja_osipov, http://hashbin.com/19b5.html still giving me that err after I do the ` and changed var names.

how can i insert a column foo before all other columns with auto_increment?

sylvanthis, yes.

a-l-p-h-a, yes i know but i dont know how

sylvanthis, http://dev.mysql.com/doc/refman/5.0/en/alter-table.html 'first'

a-l-p-h-a, thnx

sorry to sound like a dolt, but can someone tell me the size in megabytes or human terms, that this is:
| Variable_name | Value |
+——————+———–+
| query_cache_size | 134217728 |

134

1)"

thank you

that's 128MB.
(divide by 1048576.)

can i recover innodb data if i DIDNT dump it?

what percent of system ram is acceptable to allocate towards query_caching?

tirkal, that makes no sense… in english what do you want to do?

(at 128 i am still getting low_mem prunes)

_Sam–, depends.

a-l-p-h-a: I want to select all rows of users which have an email that appears more than once at the table.
for example, if foo@bar.com appears twice, once as the email of user bart and once and the email of user lisa,
I want to get both "bart" and "lisa".
for example, if foo@bar.com appears twice, once as the email of user bart and once as the email of user lisa,

alright, I have a complex query that uses a 'group by' statement. Now the collum that im using is in a table joined by 'left join' and thus can be null. I do not WANT to group all the nulls (as each one is a separate entity). Other then this it works great
how do I fix this?

where is the innodb data stored at?

still sniffing ?

what :s

The InnoDB tablespace in your datadir.
Oh where is chadmynerd?

!seen chadmaynard

could you coalesce the field with NULL's to something that is guaranteed to be unique for that row?

seekwill but it's not table/database specific. how can i recover data from it?

Define "recover"

extract the data from it

I don't believe that is possible
Just load it up!

but what about the old data i have in the old files?

?

i have two ibdata1 files
and i want to merge them

Do it the proper way. Load them up into MYSQL and use mysqldump

select * from mangosrestore.character_inventory, name, guid from mangosrestore.character where mangosrestore.character_inventory.guid=mangosrestore.character.guid and mangosrestore.character.name like 'Ithi%';

What is the error?
You have FROM twice…

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'from mangosrestore.character where mangosrestore.character_inventory.guid=mangos' at lin

arthas242 from is a reserved word you could try `from`

i am trying to select from both tables
i though i could do that :*

joins

http://hashmysql.org/index.php?title=Introduction_to_Joins - For more indepth info: http://dev.mysql.com/tech-resources/articles/mysql-db-design-ch5.pdf

!man join syntax

thank you

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

!man join syntax
!man join syntax

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

:$
aaah

i need to load them in a seperate mysql and dump it?

Yes

lol if that's possible that it's possible to merge them
0 the_sniff: i have two ibdata1
3  henke37 (n=Henke@62-20-210-130-no26.tbcn.telia.com) Joined #mysql [433
3 the_sniff: and i want to merge

Do it the proper way. Load them up into MYSQL and use mysqldump

oops
soory :E mirc :e

I don't know of any binary file format that you can just insert data into and expect it to work

well if mysql can extract the data from that file then IT IS POSSIBLE
ain't it?

You go figure it out then

ok

What do I have to install to get the ARCHIVE engine working with mysql?

Is it not working for you?
!man archive

see http://dev.mysql.com/doc/refman/5.0/en/archive-storage-engine.html

Anybody ever run into a query that should have had an ambiguous field name, but didn't?

So I guess that means its just not built with –with-archive-storage-engine in the Gentoo binary.
Oh well…
Thanks.

TableA contains field x, TableB contains field x, SELECT fieldX FROM tableA JOIN tableB USING (someIdField); and it returns without error?

Well, if it returned without an error, how would we know???

hey guys is there a way to insert rows with random unique ids in mysql?

Because it should have returned an error 1052 Column in field list is ambiguous

Of course it should have. But if you wrote a "broken" query, and it.. worked… one would assume it wasn't broken.

Until one wondered why one was getting values from tableA when expecting values from tableB, like I am right now.

ah

In Postgresql, how do I set the value of a cell to ""? It doesn't seem to work.

ask in #postgresql

They banned me so I thought I would ask here.

haha

heh

We will ban you!

No!

UPDATE table SET field = '';

Because #mysql and #postgresql like this

I get the expected error in 6.0, not in 5.0.22

oh

they ban everyone from #postgresql, it's okay

Oh.

haha

Ironically, I was banned for asking about MySQL.

see, i find the real things to get banned for

Or whatever it was called

NAMBLA

Ah ok… I'll join that too

= '';" work? (That's two single quotes)

seekwill:I get the expected error in 4.0, 4.1, and 5.0.37. In earlier versions of 5.0, and all versions of 5.1 and 5.2 installed, I get no error.

oooh, snoyes the bug hunter

If not, what error does mysql return?

crikey!

what is wrong with this syntax — i mean…. this is a really basic statement! DELETE FROM subscriptions s where company_id=32525;

what's wrong

Don't ask us "What's wrong with this query…". We are not SQL parsers. We do not care to look character by character looking for errors when MySQL will tell all of us WHERE the error is. Paste the FULL error issued by MySQL.

nevermind
it was something silly
thanks thou

Hmm.
Well, it looks like it is working, but when I try to get the value, I get a bunch of garbage chars instead of an empty string from libpqxx.
uWell, it looks like it is working, but when I try to get the value, I get a bunch of garbage chars instead of an empty string from libpqxx./u

We should ban you for the fun of it.

Are you using C?

C++.

That's a C++ problem.
You're not handling your strings correctly.

Yeah, I'll look at what I did wrong.

how is it that you repair tables on a 3.23 system?

or pointers.

It definitely sounds like a C++ problem. You should try performing the same select in a pg client and see if it gives you garbage as well just to be certain.

!m jcapote repair ta

jcapote see http://dev.mysql.com/doc/refman/5.0/en/repair-table.html

i keep getting "check/repair not available for this table handler"

InnoDB ?

how can i check?

show variables like 'storage_engine';

show table status

theyre a combo of ISAM and MyISAM

Repair table may not work for ISAM

GRANT ALL ON db.* TO user identified by 'password';

!man41 repair table

see http://dev.mysql.com/doc/refman/4.1/en/repair-table.html

Check that

im on 3.23 though

and then, from shell, $mysql -u user -p'passwprd' db

Check it still

and it denies access, but it works if i omit the password

k

(ignore my typo with the password spelling)

Don't use quotes
-ppassword

ok, i'll try that
but question, why would it grant me access without a password?

Using NULL works.
Thanks, sorry about being offtopic.

You might have an anonymous user
Don't be sorry. Just don't do it again

when setting the password..

Ok?

still denies acess
access*
just pointing that out

Ok?

GRANT ALL ON db.* TO user IDENTIFIED BY 'pw';

that works

!m sugoi adding new user

sugoi see http://dev.mysql.com/doc/refman/5.0/en/adding-users.html

then…: $mysql -u user -ppw db
fails
k, i'll read that too…

user@….
I hate it that MySQL doesn't tell you the syntax is incorrect
IT IRKS ME ENOUGH TO USE PG
We don't allow n00b

so i want to use $mysql -u user@localhost -ppw db
?

PG ?

well, i'll read that page you linked

No
You need to specify the host in your GRANT

oh

POSTGRESQL

GRANT ALL ON db.* TO user@localhost IDENTIFIED BY 'pw';

ahh

Quotes for the anal

ah HA
it seems to be working
:P

It really helps when you use correct syntax huh?

it really helps to look it up at mysql.com/doc or google before asking RTFM questions

heh, yeah
haha, whatever punk. I did read a bunch from my google searches. Nothing said i needed to use @localhost
and i always read what i can find before i ask here

sugoi, ever tried mysql.com/grant?

lol

You've been punk'd

i checked mysql.com, not sure what pages i read there

hahah punked

well, you could have even checked cnn.com *sigh*

if i want to get info from two tables, is that JOIN?

BlkPoohba, can be

update users set group='new' where id=5; not work???

i have two tables and one table has some computers info and another has some other but both have serialnumbers

select column from table1, table2;

or join, as you said

!tell acnfcc about doesn't work

acnfcc Doesn't work is not a helpful statement. Was there an error? Unexpected results? Does it sit on the couch all day eating all your cheetos and ignoring the classifieds? Be specific!

so I want to put the tag numbers from the tbl with that based on the serial numbers from tbl1

i've got a database of locations each having a city, state, latitude and longitude …. now … i already now a very optimized method of finding all records having associated locations within a given radius of another location…. each record having an association with the locations table has
an origin and destination … and i can find records whose origins and destinations match a certain distance from a given origin and destination …..
what i need to do is find records "on the way" between the origin and destination

ERROR 1064 (42000): You have an error in your SQL syntax

It tells you where the error is…

usefull error ^^

acnfcc, must be a typo

guys whats the best way to install mysql on ubuntu ? on the Add/Remove Applications i see the MySqlAdministrator is that one i should install ??

near group='new' where id = 5' at line 1

!tell acnfcc about reserved words

acnfcc

so…. to approach this problem — i interfaced with an api that gives me all the latitude and longitude points for the route to take between origin and destination …. i take those points and isolate points 50 miles apart from eachother along the path …. from those points i need to find
records whose origin or destination is within 50 miles of any of those points

ah, yes, group

reserved words

SoWhattt, apt-get install mysql-server #i guess
SoWhattt, oh and don't forget prepending sudo xD

ah group is scrweed up

`group`
!man reserved words

see http://dev.mysql.com/doc/refman/5.0/en/reserved-words.html

!wench learn reserved words is http://dev.mysql.com/doc/refman/5.0/en/reserved-words.html Use backticks (`) to quote your reserved words.

I have learnt reserved words is http://dev.mysql.com/doc/refman/5.0/en/reserved-words.html Use backticks (`) to quote your reserved words.

so how would i do that?

Do what?

BlkPoohba, mysql.com/jon
join

i'm there already

sylvanthis i found the mysql-admin / mysql-admin-common / mysql-navigator / mysql-query-browser n mysql-query-broser-common which one i choose

select foo, bar from table1 join table2 using (computer_whatever_crap_id) where fafasfd=23423;

joins

http://hashmysql.org/index.php?title=Introduction_to_Joins - For more indepth info: http://dev.mysql.com/tech-resources/articles/mysql-db-design-ch5.pdf

sylvanthis i got it… thanks ill be back later for some more doubts =]

SoWhattt,

"ERROR 1069 (42000): Too many keys specified; max 0 keys allowed"
Is that because I have INDEXes on the table?

I have some trouble with my php users not disconnecting properly. Any way I can make MySQL terminate the connection after X seconds of "sleep"ing?

Does the ARCHIVE storage engine not allow keys at all.

I have a table, A, that's mapped to another table, B, through a mapping table, C. Is there a way to get information about table A and at the same time, figure out how many maps exist between that A and any B ?

Don't use _pconnect
!m EricL archive

EricL see http://dev.mysql.com/doc/refman/5.0/en/archive-storage-engine.html

Yes

sylvanthis hey champ ive done the installation and i set the root pass already, so how do i know if its working ?

Strangely enough I don't. Already checked that one.

basically i want to do something like this 'SELECT a.id, a.name, COUNT(c.id) AS uses FROM A a LEFT JOIN C c ON c.a_id =a.id
seekwill, except i'm doing it wrong

Your connections will end once the script is finish

and I added mysql close to my destructor, but the problem didnt fix
And after a couple of hours I just get "Too many connections."

How is that… wrong?

I have already read that and it didn't tell me anything.

You should find out why your connections are not closing properly first.

seekwill, I got a mysql error, let me run it again and see if it's something else :

What does the first line say?

It says it supports everything but DELETE REPLACE UPDATE and spatial datatypes.

Kindly share the error message

seekwill, Mixing of GROUP columns (MIN(),MAX(),COUNT(),…) with no GROUP columns is illegal if there is no GROUP BY clause

Right, without indexes, so does that mean I have to ALTER TABLE to remove the keys ?

Then group by a.id and name
EricL:

seekwill, I've never group by'd before, what exactly does that mean?

SoWhattt, you need to start the service (at boot is handy) sudo /etc/init.d/mysql start, and you can issue mysql -u root -p from command line and start typing SQL

Hmmm…that sucks. Oh well.
Thank.s

How does that "suck"?

It's kindda strange. I just have a couple of connections saying "sleep" when I do "show processlist;"

seekwill, eh.. i'll read up on it

mysql 5.1.?

Looks like someone is using pconnect

I meant sucks for me. I am just trying to save space.

yeah, and removing the index will do that

I knew there would be a tradeoff in speed for size, but losing that indexes may be too much of a tradeoff.

seekwill, haha, that worked surprisingly well. Thanks for the help

sylvanthis thanks mate

SoWhattt, no problemo

If I even used pconnect, shouldn't it just reuse the same connection instead of creating multiple?

Should…but doesn't work very well with PHP
….PHP's implementation isn't the grestest
greatest

Thanks.

Lovely :]

How would I edit this query to display a 5th column displaying the "URL" field for the row? http://pastebin.ca/626642

where is the url field found?
I'm gonna guess either t1.url or t4.url

I have some quite demanding queries that seem to block other queries. I don't use any locking - unless mysql does it for me. Will this be solved if I change the user that the demanding queries use?