niuq, it just turns characters like "" into an html character code like <
(I think)

aha

i'm not a pro at all though, so its more of a guess than anything

mmm ok let me see if i get it
would be something like that htmlentities(mysql_query(whatever)) and that would return the html characters?
like &aacout for á

ya, its worth a try

yes i think it is

actually I forgot one of the parameters
you need to have another param

and what is that?

i think you want ENT_QUOTES

i'll try with this, maybe you know the parameter i might need

htmlentities(blah, ENT_QUOTES, 'UTF-8')

htmlentities(php echo $row['proyecto_descripcion']; ?)
ok so it would be
htmlentities(php echo $row['proyecto_descripcion']; ?, ENT_QUOTES, 'UTF-8')

well, htmlentities is a php function…
so it would need to be in php web hosting tags as well

ok

htmlentities($row[laksjflasdf

ok, let me try
btw, that would echo the characters?, or will only make the shift?
not sure if that make any sense
echo htmlentities($row['proyecto_descripcion']; ?, ENT_QUOTES, 'UTF-8'); ?

php echo htmlentities($row['proyecto_descipcion'], ENT_QUOTES, 'UTF-8'); ?

it didn't echo the result
it did not show the result, any idea?

are you sure your query returns a result?

Is there any application that I can take take SQL dump from myserver, but in the server itself, not it my computer ? (The sql Dump is so huge, so I don't want to download it …)

hi

yes i am pretty sure

niuq, then I have no clue

heh sorry missing a word, but still didn't show the right text

is it possible to select a word and the ten words before it, and the ten words after it, in the table ?

maxagaz, why not just select the 10 words before it, then 20 words after that?
using a limit

how to select ten words before it ?

i dunno im scared

how can I take SQL Dump using the mysql client ? (Command line)

snoyes, thank you, I got the function working.

What is this? http://img201.imageshack.us/my.php?image=errorpu3.jpg
'abc'

codgio is apparantly a numeric field, not a string fiel

how to SELECT the element that is just before the one (or the first) which is found by the WHERE clause ?

http://img201.imageshack.us/img201/2545/errorpu3.jpg

Well 'abc' definitely doesn't look like double precision floating point number

I have a crazy mysql select. It takes a LONG time to resolve.
If I paste it, can someone help me?

roaet, pastebin.ca

What does EXPLAIN says

explain says?

hehe.

!man explain

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

what's a "dirty block" ?

!beer

ok. i know. but why return 1 row?

Context?

!wench beer salle

Here salle have a cool beer

"If the cache contains any dirty blocks, the server hosting saves them to disk before destroying and re-creating the cache."

hard drive section

Thanks bot! Good bot! Have a cookie!

actually a memory section

http://rafb.net/p/9n8dH099.html

roaet, no index use at all

Ahh, I guess I'm coming in a wrong room. Nobody even know how to take sql host dump :-&

Is there a way to get the number of rows in a table w/out using COUNT(*) ?
metadata i'm after.

jormenz__, nothing wrong with count(*)

Too slow in SQLite for some reason.
Taking like 5 seconds on a 16 million record db.

hehe use a proper db

Check the perf of SQLite compared to MySQL
And then tell me that

mysqldump –your-favorite-options

I'm not sure how to set up 'index use'

thx

jormenz__, count(*) is a maintained variable in myisam tables so its instant

Thought so

make a table, and keep the number of rows, there

How do I have it automatically update when the other table is changed?

mysqldump –help | less

roaet, and excessive use of subqueries rewrite as a join

and read. There are lot of useful options

yeah, I'm reading

I'm not very skilled with joins.

hehe salle that line looks funny I must remember –help | less

Bagoor - I like the idea, but I'll still need to do COUNT(*) on the other table.
And how will I automatically update it?

change your codes, so when they will update the table, that record also will be updated. (X+1, not count from scratch)

Yeah, I guess that'd make sense

if you had plan to pay someone to do it for you, I can help you

I don't need to pay someone to create one table with one field =P

Yes you do, and I am cheaper than Bagoor.

hehe

It's not so hard to increment the field when I'm inserting, and decrement when i'm deleting

I means for changing codes of course, not just making them ! lol
are you indian ?

I can change it easily, but thanks for the offer

Nah, I'm Canadian.

so enjoy

Thanks

The best thing is that it IS the way to get help

Ok, so you want to do this is MySQL or SQLite?

SQLite
Everything is good except COUNT(*)
I insert 16 million records with an index in 20 minutes (from a text file)

Aah, well that explains what you are doing here. In any case you're best host bet is indeed to maintain your own count.

Yeah, I think that's what i'll do

salle I often give out –help but had not thought of the obvious extra

Another tricky thing is progress bar on impot.

Does SQLite do triggers?

yes

Well easy enough to trigger on inserts and deletes then and adjust your counter.
Progress bars are the realm of the injecting code, do a first pass for linecount in the source file and base your progress bar off of it.

Yeah, but I can't get a linecount
File is too big
Not enough memory =(

make a guess

Ok, are the files a consistent format?
If so, make one with 1000 lines, check the size. Divide the size of a given file by the size of the 1000 line file and that is how many thousand lines you approxamitely have. Base your progress bar accordingly.
Contract requires I then tell you to get a real database.

or get file size and you know bytes read so far…

Nah, that would be too easy.

That'd work

Sure, if you like it easy.

Hey guys
If I store member id's for a group as comma seperated values in a field, say like "2,3,51,25,252,5254" etc, how can I query by user id?
*user not member
I've got a table of guilds, each with csv member ids

I really can't figure out where to start converting this to a join or how to get it to use indexes.

And I need to find which one a certain user id is in, if any.

normalize

?

normalization

http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html and http://datamodel.org/NormalizationRules.html and http://mysqldump.azundris.com/archives/20-Nermalisation.html and some here http://www.keithjbrown.co.uk/vworks/mysql/

There we go, read those.

is that to me?

Thanks.

no.. ok

Or just the first one, the first one is awesome.

biased

ha!

It ain't braggin if it's true.

good song.

That's also a song?

yeah..
Dan Bern - Tiger Woods

!m roaet rew

roaet see http://dev.mysql.com/doc/refman/5.0/en/rewriting-subqueries.html

ah thank you.

hey guys, im trying to determine if I should be splitting my tables up as much as possible and using joins

are there any resources i may read to learn how to index my database properly

or if its better to just have large tables

Oh yeah, I remember that song!

Ah, got it tarr.

!m roaet how mysql uses i

roaet see http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html

I was going to change it to InnoDB, with a bit of foreign keys, but converting this myisam db is daunting
bI was going to change it to InnoDB, with a bit of foreign keys, but converting this myisam db is daunting/b

hi

hello

is there a way of getting the value of id returned when I insert a new row into a table? if id is incremented automatically

last_insert_id

what does that do

guess

give last inserted id?

Hrm — myhillyer - I can't seem to get bytesread during my loop.
So doing bytesread / total size of text file won't work for progress.
I can only get length in chars from a .ReadLine() in .NET

.NET and SQLite?

yes

tibyke not what I need

Zee1ot, thats what you asked for, dont fool around

Works v. well so far

That's a new one to me, you have a link to info on it?

hehe borked language as well as db

yeah
http://sqlite.phxsoftware.com/

my rows have an id value that is incremented automatically, I need that

That's what they say about my vbmysql site archivist.

archivist - What language do you like?
For business, .NET is what I need right now

Zee1ot, so select it, or generally ask sensible questions

without running another query

Why can't text fields have a default value?

I prefer Ruby for personal

"for personal"

Well, personal projects.

when I insert data into my table….I dont know what the id for that row will be, what is the best way to get it?

Things that i'm not selling to people

i may or may not be listening to the High School Musical soundtrack

Zee1ot, you have already been given the answer

Hmm, going to have to play with System.Data.SQLite, looks handy for a second dblayer plugin in my vb sample app.

Anyone?
I'd like to get the default to "0"
But it won't let me

Ask nicer.

mhillyer - Do you have much exp. with VB.NET ?

bah

Talking to me?

Are you talkin to me?

I've been out for a while jormenz__ but when I was more into it I wrote a lot of the content of www.vbmysql.com

are you allowed to store an empty string in an int?

No, strings are not allowed in INTs

Ask yourself, is an empty string an integer?

Saberu, er a bit silly

ok i'll use 0

Or NULL

Why can't text fields have a default value?

They can.

Won't let me set one
Trying to make it "0"

Then you're doing it wrong.

"BLOB/TEXT column can't have a default value"

What does the column store?

A csv list

Why?

Things in a players inventory and what they have equipped.

You didn't read the first link, didn't you.

gee. that's what tables are for.

I did, and it worked, but that was for something else.

Read it again for the current column in question.

Hexxeh, use table for tabular data

But they have have a nearly endless number of items
I'd need to forsee that and create a field for each

No.
Read the article.

MySQL

I need to serialise my inv data into a string to send to my server.

this chan is damn funny this friday

I shall answer no further questions until you read the normalization article.
I will quiz you to ensure you have read it.

But that doesn't answer my question, why can't I set "0" as a default for a text field :/

word is too easy. i prefer to artistically render my tabular data in photoshop and then use OCR routines to search for the data.

Because MySQL is smart enough to know you are doing it wrong.
Because php mysql web hosting is smart enough to know you are doing it wrong.

normalization is god

So you're saying store inventory datas for players in a seperate table?

Yes d03boy, and I am his accolyte.
You're getting it Hexxeh

ownerid,id
Then add rows for each ite,
*item

Good, keep going.

Separate your data into as many tables are possible
*as

and sprinkle some indexes among the tables that it is good

item table defines items and their properties, player table lists players, player_item table lists each item belonging to a player, one item per row.
Player can then have infinite items.

Then to serialise that into a string, just do "SELECT id FROM `character_inventory` WHERE ownerid = 'mycharid'" and loop through results adding a comma after each one until the last.
I already have items in a seperate db
item_template

no slow serializing

*table

One further Hexxeh, use group_concat()

Does it for me?

Yes it does.
And verily, it kicketh ass.

How would I use that exactly?

!man group_concat

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

Someone may help me with an sql statement to select the last record order by date

ORDER BY datecol DESC LIMIT 1

^– easy peasy

SELECT GROUP_CONCAT(id) FROM `character_inventory` WHERE ownerid = '52'
Right?

thanks

Replace 52 with the var for my charid, obviously.

IIRC the default is comma separated so yes that should be fine.

Thanks
So that returns a string of csv for inv yeah?

Yes.

Got progress bar all set
Working nicely. Next step is triggers for the count

make a progress bar that looks like the water gun horse race game at the fair

are there any alternative to stored procedures in mysql 4.1?

hm
the alternative is to not use stored procedures

Client-side code.

i see
there's no way to erase entries from 2 tables with one query?

False.

can i do that with just one query?

Read http://dev.mysql.com/doc/refman/4.1/en/delete.html

Hmm
I've got my character_inventory table.
But I'm putting some testing data in
But when I put 3 items in, with the same owner but different ids
All the id's become the same :/
The only thing I put on was not null on both.
Hmm, fixed it somehow.
Added a colum for an item template different from the item id

hi
does anyone know how could I use bash to query my mysql's database?

concat(field1 + " " + field2), is it string of concat function?

what's the difference between having and where?

Any suggestions on a dual dual-core setup?

WHERE is before grouping, HAVING is after, and so can operate on aggregate function results.

chronos-work, that is not a valid function call
well, syntaxly, it is
but it is doing addition before concating one string with no other string

how can i see the connected users to a mysqlq server via console?

how to change mysql user password?

UPDATE Count SET Count = (Count + 1) WHERE Type = 'TheType'
Do I need to do a SELECT in place of COunt + 1 ?
I assume so, but was looking for a short-cut

A function to return the date from a datetime value
?

if you mean 'update table set col = col + 1 where othercol = 'somevalue';, then no.

threnody - So what I have should work then

no. update table set column…

Count is a column in Count
Probably should call it something else

lol
why would you do that?

I'll call it value.
I don't know
s

works with concat(field1," ",field2)

familiarize yourself with 'reserved words'. http://dev.mysql.com/doc/refman/5.0/en/reserved-words.html

is mysql 4, I don't know if it is in deuse

UPDATE Count SET Value = Value + 1 WHERE Type = '{0}'
Is Count reserved?

no

Value then ?

"SELECT * FROM table LIMIT 100, 150"

nor is value

ok, god
good*
So what I have sould work

what's with the curly braces?
{}

Hrm it didnt work
It's VB.NET
I need the curly braces for String.Format()

begone, demon!

What's with the .NET hating.

and sqllite threnody

It's what the business world uses for the most part

gosh it must be great then.

It's not great
but it works
Hrm, Value is empty

an abacus works

Value = Value + 1
do I need 'Value + 1'
Or ()

beats me. ask on #vbnet

This is not a .NET question
it's a sql question

update `foo` set `value`=`value` + 1 should work.

this is a place for mysql not sqllite

UPDATE Count SET Value = Value + 1 WHERE Type = 'TheType'
sqlite is similar enough that most sql host stmts work.

Hello.

And there's no good sqlite channel

UPDATE table_name SET column_name = REPLACE(column_name,"article=WILDCARD","article=WILDCARD/") || WILDCARD= will be different, but I dont want to change it as part of the update. I just want to add a slash after WILDCARD - can I put WILDCARDS in this statement??

kamoricks - So put ` ` around Value I guess

How do I set mysql to strict mode?

different per row
am I clear?
it's hard to explain

UPDATE Count SET Value = 'Blah' WHERE Type = '{0}' works
But UPDATE Count SET Value = Value + 1 WHERE Type = '{0}' Doesn't
Any ideas why?

!man adding new user

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

are you adding the forward slash to the end of a string in each instance?

Got it. Thx guys

What's the best web based MySQL admin interface available? (I don't want phpmyadmin)

why does it have to be web based?

phpmyadmin.

there's a perl one, don't remember what it was called

It doesn't really, I guess. It would just be a bit more flexible. But desktop apps are fine.

phpmyadmin is the best available. Why don't you want it?

I don't know, I'm kinda allergic to it

!gui
!man gui

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

gui

Is it the theme?

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/

The UI is horrible

lol tada

You can change the theme, of course. Have you tried Paradice?

Not just the theme…
But I will check it out.

http://sourceforge.net/projects/perlmyadmin

For example, the icons in the sidebar. What do they frikkin do? It's not user-friendly, and that goes for a lot of it

What would I put on a query to say that if it returns a row, to UPDATE that row?

i suggest MySQL Query Browser which comes with the MySQL GUI Tools package

I'm using that right now

you wouldn't

But it's not spot on, either

although i guess you could do a subselect

nothing like ON DUPLICATE KEY for that?
:-\

I'll probably have to write something myself, nobody else can get it right

no not for that

Can I do a Trigger on the dropping of a table?

Oh, duh
I can just do a mysql_rows_affected to tell if it made the change

Actually the Query Browser is kinda sufficient but I hate having to click four times on a table to do a select on it, and I hate that the process seems to have an unstoppable appetite for GDI objects, so it slows down everything on my computer.
That's the main things I hate.

But you love the docs for it?

The documentation browsing is really neat, yes.

Aah, all good then.

No.

how can i see the connected users to a mysqlq server via console?
how can i see the connected users to a mysqlq server via console?

SHOW PROCESSLIST;

Oh. You mean the documentation for the QB, not the documentation browsing stuff built into it… That's what I meant.
Is there a faster way to do a select on a table in there somewhere? I'll have a look.

Aah, perhaps not all good then

How do I do a trigger on table deleted?

I don't so SQLite, sorry.

It's same as MYSQL
statement would be the same.

You want to trigger on a deleted row or a whole table.
?

whole table
Drop TABLE

I don't recall seeing a trigger on that.

I'm keeping count of size of 3 of my tables in another table.
The count incremenents and decrements on triggers.
If I drop the table, the count stays liek it was, and doesn't revert to 0
I guess I must change the count when I drop the table.

Looks like it, unless SQLite has a trigger MySQL doesn't have.

doubtful.
Thanks anyhow

my mysql is corrupt… its giving me the error #1033 - Incorrect information in file: './pringo_test/albums.frm'
how hdo i fix that?
i have a backup of the database but its 3 days old. the table structure hasn't changed in the past 3days… is there anyways i can save it with that backup?
i've already restored and tried copying over the .frm files but that didn't help

What happened to corrupt it?

mhillyer, i'm not sure…
stopped the server, changed some settings
then restarted it
and it was corrupt
so one of those things corrupted it

there are steps. http://dev.mysql.com/doc/refman/5.0/en/corrupted-myisam-tables.html

What were those changes?

threnody, its a innodb table though
lemme get those… one sec….

And can you paste exactly what you tried and the full output of the 1033?

Are there any tools to do incremental backup for mysql?

when using a flag, you should use ENUM, correct?

you have an 'albums.frm' file and it's innodb?

like A/B/C/D/E

as in storing the difference in data between last snapshots

You could.

thanks guys

don't forget to thank the bots!

cya later

All DB types have a .frm

would that be the best option?

my error.

Do you have a finite number of flags, with each being mutually exclusive?

frm is the file created by mysql that it uses to know which storage engine (among other things) to go for to get a table

"each being mutually exclusive" … ?
I'm not sure what that means.

Only one flag per thing?

yes.

You can use an ENUM, or normalize it with a second table and a joining table.

but joins are very costly right?
on memory?

Everything has its cost.

but aren't JOINS very costly? or no?

no
compared to running alter table to add a new enum item, no

well I haven't added the table yet.

But someday you will want to modify it.

hi

why's that mhillyer ?

does anyone know what command I could use to give all the tables in a database the same prefix?

threnody, yea, whats wrong with it being innodb?

Because the only sure thing in life is change.
Where's my paste?
Eat paste.

I did.

Eat more.

I was mistaken in my belief. Please disregard.

mhillyer, sorry, i got pulled into a quick meeting. increased the innodb buffer sizes and increased the number of concurrent threads

I did.

Ok, and my paste of the exact thing you were trying when you hit the error and the error message itself?

anyone know?

Then you're doing it wrong.

It finished..

It does not exist, you have to rename them individually.

:

hi FordPref

mhillyer, http://rafb.net/p/bn4ZJt67.html

Of course, you could consider the database name to be the common prefix.
That does not look like a command and an error message to me.

how do I do that?

You issue ALTER TABLE as many times as you have tables.

mhillyer, #1033 - Incorrect information in file: './pringo_test/albums.frm' thats what i get when i try to view the table in phpmyadmin

Aah, PHPmyadmin, ok.
Google says you might have a /tmp missing or with the wrong perms.

I believe he's referring to the dot notation, ie 'dbname.table.column'

oh

mhillyer — What languages would you recommend me look at?
I've used Java, C#, VB.NET, Ruby, Php

you sure there's no way? :

mhillyer, what was your search string?

Right now i'm using Ruby and VB.NEt

there's about 100 of them

Well, you know what I mean

Aah, I use VB.NET and PHP, works for me.

all I need is the same prefix on all of them

MySQL Error 1033

vb for desktop apps, php for web?

Yup

mhillyer, but the weird thing is that some of the tables will work and some won't

Right now i'm doing vb for desktop apps, ruby on rails for web
Might do some php at some point, it seems like a very powerful language

Sounds fine to me.

Designing isn't fun when you don't know what to design for..

Would depend on what you are designing.

Some tables.

Aah yes, better learn all you can about your data first.

All my data?

Whatever your designing for.

I don't know what I'm designing for. I have nothing to design for..

mhillyer, doesn't seem to fix it…. /tmp has nothing in it and all the .frm files are owned by mysql:mysql

Are the permissions on /tmp ok for r/w by the mysql user?

yes

Did you change versions in the process?

mhillyer, no

Well the stuff I am finding mainly revolves around bad backwards compatability on frm files and bungled permissions on /tmp
So far anyway.
That and nasty corruption.

seems like corruption =X

Have you tried a restore of the frm and data files?

yea… i have a 3day old backup
i restored that
and tried to copy those .frm files over to the current database
but that didn't work

restored the form, data and index files or just the frm files.

everything
i had a mysqldump

Hello

Well the dump should restore fine.

yea… the dump restores fine, but its 3 days old
thats the only problem i have with it…

hey all
How do I regenerate MYI and FRM files if all I have is the MYD

if I mark a column as 'UNIQUE', does that create an index for it automatically?
thank god it's not the opposite, eh?

PovAddict - yeah… do you know how ?

not really

I'd wager you regenerate them from backups.

Backup the .MYD, recreate the table, copy in the .MYD then repair it.

Auris - if I don't have a backup…
mhillyer - I repaired the MYD with MyISAMCHK
I am just missing my frm and MYI

Yes, so copy the .myd somewhere safe. Create a new table with the same name and structure. Then stop mysql, copy the .MYD over the new one and restart.

What if I don't really know the structure

you're absolutely screwed, I guess

One resource says you could have bad log files after the conf change, says stop mysqld, remove ib_logfile files, and restart mysql.

Pov - awesome

I usually don't keep backups, but I always have the .sql with the structure

http://pastebin.com/m5b366980 , what im trying to do is make it so if $uid is equal to either UserID or FriendID but has to be status = 1 for it to echo, it seems to be working fine when i test with $uid = 1; other then, if its the same sequence such
as row 7 and 8 with userid 6 friendid 1 status 1 it shows it twice, but if theres a situation where theres 6 1 1 and 1 6 1, it does it just shows one how it should,
can someone help me with that?

mhillyer, thanks for taking your time to help me out with this

Did that work?

trying it right now… not sure yet

Hi there, I have three fields in my Person table, person_name, person_city, person_country. I want to search the table with any one of the 3 filed I mentioned, can any one give me a hint of how to do it? What index or sorting I can use to solve this situation? I want to search all three
fields in the table.

if I mark a column as 'UNIQUE', does that create an index for it automatically?

Yes.

spiderman, use a fulltext index

Yes.

thank you i try that one

good

mhillyer, didn't work =(

I'm a total noob at making SQL databases *fast*
most of my DBs have 100 rows :]

Sorry majikman, I'd help more but I have to go home now.

mhillyer, thanks anyways

i need help

anyone know why when i try to restore a database from a mysqldump, it comes out as myisam even though the create table statements say innodb?

how could i add something into a table using linux ? like i got the table "info" , with "name" "date" "username" , and i want to add "password" ? is there a way to do that or i need to erase the table ?

first, it doesn't matter at all if you're using linux or anything
this is all cross-platform
then… yes you can do it without erasing the table, read about ALTER TABLE

ah right thanx

Hi, how do I select all rows which has the same value in one column? It's a table with `id` and `title`, I want to show all rows which has the same `title`

like… show duplicates?

Yeah, like show duplicates

SELECT COUNT(*), title FROM table GROUP BY title; - you can even know how many times it's duplicated

Thanks PovAddict, I'll have a look

At how many queries per second would you say it's necessary to get another server?

That only shows me how many there is of each? I want to show only the ones that are duplicates

Say the MySQL server is dedicated to the task

hi all. if i make select and field has null value, can i change it in empty string ?

1 GROUP BY title;

I need help SELECT `UserID`, `FriendID`, `GameID` from `user_friends` r1 WHERE NOT EXISTS

r2.UserID AND `Status` = 1)

AND ((r1.UserID = '1' AND `Status` = 1) OR (r1.FriendID = '1' AND `Status` = 1))
oops sorry*

I appreciate your help, but that gives a syntax error

is there away to format the output from a mysql command ie. select * from user where User='root'; its very hard to read. Any ideas?

mysql 4.1.20

http://pastebin.com/m5b366980 *,what im trying to do is make it so if $uid is equal to either UserID or FriendID but has to be status = 1 for it to echo, it seems to be working fine when i test with $uid = 1; other then, if its the same sequence such
as row 7 and 8 with userid 6 friendid 1 status 1 it shows it twice, but if theres a situation where theres 6 1 1 and 1 6 1, it does it just shows one how it should,

david-work: using the command line app? try \G instead of ;

Thank you

well I pulled it out of… guess where
all untested

you rock

1 GROUP BY title;

if i make "select title or '' from table_" i get 0. why ?

Also error

then wait for somebody not-as-noob to try helping you… I'm out of ideas

No problem, thanks

erm… you're doing boolean logic on strings…

mm .. yes.

probably title evaluates to false and '' evaluates to false… false OR false = false

ok. i want to replace null value by some icrement value

I can have a MySQL cluster read from a central set of harddrives like NAS, right?

1 order by wordid desc; works

:o

@a:=1; select title or @a:=@a+1 from table_ ; this print 1 all the time / why ?

because title evaluates to false, @a evaulates to true, and false|true=true, and true is 1
you can't use 'or' for that

anyone know why when i try to restore a database from a mysqldump, it comes out as myisam even though the create table statements say innodb?

well I don't really know what you're trying to do

uh . i understood …

you want to return something else if title is null?

yes

http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html#function_ifnull

thanks

is there a way to send an IF statement more than 1 row?
nm
I found the way

what could be preventing my db from allowing me to create innodb tables?

not having innodb compiled into mysql/installed/configured?

is innodb enabled

PovAddict, it was working before. we changed a few settings in my.cnf and now it doesn't
archivist, how do i enable/disable it?

change them back…?

in my.cnf

archivist, i mean, whats the directive?

–skip-innodb

hm…. skip-innodb isn't in my my.cnf

show variables; see whats enabled

there is no variable named skip_innodb in show variables. does it go by some other name?

is this sqllite?

mysql

saw a commandline override the other day in a startup script

archivist++
do 'show innodb status;'

hmm…. that is saying skip-innodb is enabled

What does it mean if returned passwords from a UNION SELECT are 8 character weird ascii characters (characters in the hundreds too)

my guess is it has nothing to do with UNION SELECT; it's just the way the password hashes are stored
umy guess is it has nothing to do with UNION SELECT; it's just the way the password hashes are stored/u

I have a question about using SELECT DISTINCT. I am trying to return non-duplicates from a 'card' field, but I also want to return the other fields of these non-duplicate entries, how would I do that? Currently I have : SELECT DISTINCT `card` FROM `messages` WHERE 1 ORDER BY `date`
ASC;
Say I also want to return the 'date' column, I can't figure out how to do that

you could use group by card and get the min date.

so i've figured out whats causing my db to not allow innodb. anytime i set an innodb directive in my my.cnf, it disables innodb tables. any idea why?

Thanks threnody, "SELECT * FROM `messages` WHERE 1 GROUP BY `card` ORDER BY `date` ASC;" worked great!

what is the purpose of 'where 1'?

to return everything?

superfluous, I think.
select * from table will return all the records.

Right, thanks threnody, I've been working all summer away from coding so I'm very rusty.

could someone explain to me how InnoDB's Secondary Indexs work?
is it just like a second index?
how do you chose what type of temporary tables will be created?
I would like them to be MEMORY tables.

can someone help me understand why the first time i do a certain select query it will be kinda slow, and the second time, it will be nearly instant?
query caching is disabled
i'm just trying to understand what's going on under the hood

operating system has its caches too.

you've described query result caching.
is this ASP.NET?

no… just regular ol mysql

out of curiosity, why would you disable caching?

well i'm just testing something at the moment but the main reason would be that query caching can actually hurt performance if you write to the table a lot
since its added overhead
or if you have very diverse requests

hi - I have a query and I'm doing "……WHERE IN(3,4,5,6,7)" where those ids in the IN are from a previous query with a sort applied in that previous query. However, the outer query seems to order them randomly…is there a way that I can get the outer query to respect the order of the ids
in the IN() ?

Why does the order of the id's matter?

first of all i'd recommend doing a subselect rather than the id dump thing. but i think you'd have to sort the resultant set to guarantee its order

joelmichael - even if I do a subselect, can I still preserve the ordering in the outer select?
sorry, I see your end comment.

i don't think that you can, because it's just iterating over all the rows and comparing them with those ids, not iterating multiple times
so it will be in whatever the natural order for the table is

yes, that's what' I'm seeing now. ugh.

as for the subselect, i've found them to be just as fast and they don't suffer from an issue where if you have too many ids, it will crash the query because it will be too long.
plus its cleaner code imo

yes, I can see that…I'd need to be aware of that as well. But not having control of the outer queries ordering is killing me.

you can still order it i'm sure
just afterwards using an order by

here's the problem, the sub select is ordered over many many rows…the result of the sub select is only a few of those rows. If I order in the outer select, I'm only ordering over the restricted set from the bus-select but I need to order over the whole set.

can you pastie the query so i can get some context
http://pastie.caboo.se

well, it's big but I'll give you the idea of it..oen sec

how do you chose what type of temporary tables will be created? I would like them to be MEMORY tables.

O10xz, that's a very interesting question… although i worry MEMORY tables could be potentially dangerous if large enough
right now i'm at a point in scaling i'm trying to limit disk access as much as possible

not for temp tables.
yea.
that's what you want to do.

temp tables can be big too can't they?

yea, but not huge huge, usually.
depends your data set

What's the best way to copy a database while keeping it on the same server? Using mysqldump to write to a file and then reimport seems circuitous.

dave_mw1, so it's not a simple sort, but rather a complicated sort done in the inner query
i'm afraid you might have to do the complicated sort on the outside somehow or something
Cyde, mysqldump will become unfeasible after a while because it's so slow. you can actually just stop the database and just copy the data directory.
that'll be a lot faster

And to stop the db, I just lock it?

you could either shut it down, or do a lock yes
flush read lock or something to that effect? i don't remember. i usually just stop and restart it.

http://pastie.caboo.se/88824

over time you'll want to start replicating the database to take snapshots so you don't have to make your main database inactive.

Yeah, I see a "lock tables" command, but no "lock database" command.

FLUSH TABLES WITH READ LOCK;
then do a cp -R
then UNLOCK TABLES;

Thanks
I'll do that now

you should start by getting your working data set into memory
that will alliviate a lot of the io issues you might have
you want to do this by increasing your index cache.

O10xz, yeah — i'm using myisam for fulltext indexes and i'm looking into stopping that and putting all my fulltext stuff into something else like lucene or sphinx
which keep it in memory

yea.
then you should make sure that your Binary Logs are seperate from your data.
so offload all of your Binary logs to another physical disk.

interesting
makes sense

Binary logs write sequentally.
Data writes/reads randomly..

would cut down on seek issues i assume

indeed.
make sure that your data is re-stripped as well across multiple drives.

you might know this O10xz. what is happening exactly when i do a query once, and it's kinda slow, then i do it again and it's very fast (near instant)
even if i have query caching disabled
i assume it's in memory
but — i'm not sure what exactly is in memory

hrm, not if query cache is disabled.
query cache works as follows…

hmm. well query_cache_size is 0

hrm.
not sure, that shouldn't happen I don't think.

it doesn't sound disabled, does it?

well… your indexs are in memory.
that's one reason.

i assume it's an IO issue
that could be it
it's on an index

so it's storing that index in memory.

ah

once you call it.

is there any way to preload my indexes?

yes.
!preload
!man preload

see http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html

here's a test. query one time. alter the underlying table. query again. if the second query is as slow as the first…

i'm assuming this also explains the 'warmup' i tend to have to do when i start my database up

yeap joelmichael

well this could be a lifesaver, thanks

try it out, tell me what you think.
tell me if it works.
I already know the answer, every time you update a table, it erases the query cache.
for anything that was in those tables.
so when you alter the table, you're erasing the query cache for any queries in that table.

so if your cache is disabled, there should be no observable effect.

I'm pretty positive, yes.

sorry dave_mw1 i forgot all about you
let me take a look at this

joelmichael…I'm looking at it again

maybe what you need here is a join
so you can do the order by afterwards.
O10xz, yes, i've done some reading and found the query_cache can actually hurt performance
if you have varied requests or frequent updates
just because the cache is rarely used and it adds overhead to writes

yea, it depends your read/write ratio
but anyways, I would like to know if preloading your indexes into the cache works.
let me know joelmichael

ok, I got it ordering again on the outer select is giving me what I want…thanks. I think I'm just a little batty having looked at the SQL for so long. I had to get away from the join because it was too slow.
the IN() has a much more restricted set

The storage engine for the table doesn't support preload_keys
boo

mysql_select_db returns false. Is there any way I can narrow down what's going wrong? I created the database, I did "grant all on dbname.* to username@localhost identified by "password", what else could be wrong?

what's a quick way to see what engine i'm using on this table, because i forgot
glad to hear it dave_mw1
dave_mw1, you can join on a derived table too

you can code php to return the mysql error message. also look into the httpd error_log

like this

show create table table_name;

Yeah, nothing in the error log that's indicative. Can you clarify mysql error message? Is there a function I can call to get the error message?

dave_mw1, http://pastie.caboo.se/88827
much faster than a full join

yes, I tried that as well.

something like or die ('Could not select database because: strong' . mysql_error() . '/strong');
after your connect effort.

so this table is innodb and i guess innodb does not support 'load index into cache'
im assuming it has its own version or something

the problem with doing it that way was I need SQL_CALC_FOUND_ROWS from the derived table query to use the found_rows() method, but I lose the value

mysql_select_db (DB_NAME) or die ('Could not select database because: strong' . mysql_error() . '/strong');

Oh dear Lord no….
I love it when I don't think through database design

so does innodb have an index cache?
tempted to change this table to myisam but i'm trying to avoid that for the typical reasons

Frack.
'xyzzy', but the database name shows up in "show databases'.
Hm, one sec, I haven't tried to manually log in with those credentials, let's test that.
Nope, manual login works fine.

let me know
has your php connected to this db before?

Argh, got it. THere was an extra blank space in the database name variable
Wow I feel silly.
Yeah, but I just upgraded from php4/mysql4 to php5/mysql5, so I was looking for a more serious problem than it was.

glad you found it

Thanks for the help.

np
I put that mysql_error() function after everything to do with db interaction.

Yeah, sounds like a smart idea.

actually it covers my dumb. ;^)

Say… I'm seeing some odd slowness on the live site version of this app, that I"m not seeing on either the test server or on my dev laptop.
One specific, database-heavy page has a perceptible lag. I tried doing some obvious timing tests using php's time function, but it all happens in the same second. Is there anything obvious/easy I could do to see if the lag's happening at the mysql level?

live version over teh interwebs, test and dev gigabyte?

Yeah. Live server in one datacenter, test server in another, laptop on my lap.
Test server is old and pokey but this is all it's doing, live server is a shared server, shared mysql etc, so I'm suspecting disk IO contention, if it is indeed mysql.

mysql caches result sets, until there is an insert/update which causes the cache to be flushed.
lots of images?

External images, yeah, but those get loaded to the browser asynchronously.
That is, there are a lot of images in the page, but hey're all sourced from external sites, so that should be irrleevant to rendering speed.
It's a beta site, but I can private msg you the URL if you want to see.

that may actually slow things down due to individual dns resolutions.

Yeah, but it shouldn't have an effect on page rendering. The images all have height/width attributes.
Hm.

gotta walk the dog. later.

Hmmm… can't move view's .frm files from one computer to another?
I moved the datadirs from one computer to another, and it doesn't seem like the views want to show anything

are views actually files, or memory constructs that disappear when mysqld is stopped?

Files.
Actually, I'm just stupid. Mine are referencing MEMORY tables, and they don't have any data
IT MUST BE A FRIDAY

yeah friday

i need to store a list of products in a table, and each product could have any thing from 3 sub products to 30 sub products, and i have to store dimensions for each of the sub products, anyone any ideas as to how best set up the database for this?

hi, is it possible to match a substring ignoring the case?

http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html

Should already be, depending on collation and/or column type

The LIKE keyword ignores case by default, IRRC.

Have a subproducts table with the three dimension fields.

all sql text comparisons are case insensitive unless you've arranged for it to be elsewise.

The classic approach is to represent a tree as a table. Each row in the tree is a node, the row includes a primary key that points to a parent node.

thanks a lot

However, this does make for more complex queries. I generally take a brute force approach.

Hello, I want to do a desc for all the tables in my db, is there an easy way of doing this as I have 35 tables and would prefer not to have to type desc table1;desc table2 35 times

DESC (SHOW TABLES); #perhaps ?

you could write a script that would generate your query, separated by ;

or a procedur
e

hey, long time no see, chadmaynard

Yeah, I mean I could just do a dump or something I spose but I just need to make sure I have the primary key switched on for particular fields yesterday I made 10 new tables without the primary key on some of them
Was just tired working away

hello thumbs
it has been a while
mysqldump -d mysql | grep ^'CREAT' | sed s/CREATE\ TABLE/DESCRIBE\ TABLE/
maybe. i didn't try it

Ok ill try it thanks.

mysql would be the database name

hey guys, supernoob here… am trying to create a new database (named juno), have tried '%mysqladmin -u root -p create juno' from the OS command line and \create juno; + create database juno; from mysql as the doccumentation would seem to indicate but nothing but syntax error reports.
v5.0.14
anyone know what idiot thing im doing this time

you are a supernoob?

supernoob is a thing?… i ment 'im a supernoob'

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

oh.. sorry, yea.. total noob
k, thanks.. but ya couldv just told me it was case sensitive

it isnt

O.O
k… now im scared
i swear i already did exactly that line

depends on element and o/s

yup, up key 3 times = same line, different caps

database administration assumes a certain attention to detail.

new to me

well.. job done.. thanks guys

how do I merge a mysqldump into a table instead of replace the contents?

http://dev.mysql.com/doc/refman/5.0/en/identifier-case-sensitivity.html

aye, i always assume variable names (i.e. the database name) will be case sensitive, this was the command tho
hey.. threnody.. i know that name… would your initials happen to be G.B.?

alas, no

ah well, long shot

C.D.?

O.D.?

not that either

damn

dwm, curiously enough

I was close

chadmaynard–

haha

heh

so why is memcached so much faster than mysql in terms of returning cached hits, even if the caches are the same sizes?

hello
I have a dev DB and a Production DB
They are essentially the same, but I have made changes to the dev table's structure
is there a quick way to diff the two schemas?

duplicate the output of the dump and choose your text editor of choice

hm

vimdiff is a good choice

or diff

I'm installing some CMS, and it wants my mysql info. I haven't touched mysql yet, besides apt-get install mysql-server and settings the root pass. Do I need to setup a database for this php/web-based installer…or just give it root/mypass for mysql..and it will set it all up?

usually you need to create the user and DB yourself

depends on the CMS

How do I do that?

PHPMyAdmin is easy to use

which CMS?

the CMS' INSTALL file should explain it

Isn't phpmyadmin very unsecure?

you using Windows?

only if you set it up over HTTP

no, who says that ?

haha i love these answers

I'm sshed into debian, from an ubuntu laptop

bona fides established. if phpmyadmin uses ssl, what's your concern?

How do I create the user/db myself..with mysql admin.. it's not hard right?

it is simple. http://dev.mysql.com/doc/refman/5.0/en/tutorial.html

thanks
hmm, you can't put "-" in the database name?

you shouldn't, it tries to do math.

k
so "CREATE DATABASE foobar;", "USE foobar;" and I'm done?

I cannot speak for you CMS, but that will make the db, and set you up to use it.
your*

they ususally include a .sql file or something

Do I need to make a user/pass and give them access to what I made?
GRANT ALL ON menagerie.* TO 'make.up.user.i.want.to.be.created'@'localhost';

http://dev.mysql.com/doc/refman/5.0/en/post-installation.html

like that?

there are security issues post installation that should be dealth with.
and your CMS will probably specify a mysql user.

the urls pasted in here have many many dosens of pages of info… don't I only need to type less than like 5 commands in total?
Why so much info?
s/dosens/dozens/

I get paid by the page view. It's a Google thing.
g'night.

how can I drop all content from mysql database, but at the same time keep the database and tables?

truncate all of the tables

is that safe (for database)?

safe? it will delete everything. isn't that the desired effect?

I mean is it save for the database and tables. yes, I want to dump the content.

im not sure what you mean by safe then. it will leave the schema intact

ok. what's the command?

Who has cheap textbooks these days? I am was going to place an order for a database book.

you have to type TRUNCATE TABLE name of table

Anyone cheaper than Abebooks?

How can I list users with access to the database?

but this database has many many tables.

I just recovered from a server crash and now I'm having problems changing passwords for mysql accounts. Anyone feel like helping?

"
Unable to connect to database Server. Invalid mySQL Connection Parameters specified "

write a shell script

I'm able to do mysql -u sid -p;, and "SHOW DATABASES", and it works etc. Does that mean i added the user properly?
How can I check to see if I have full privileges to the database?

show grants

yea, I have all

what should I do if drop user 'username'@'localhost'; doesn't work?

hmm, I can access the database from cli, but the php/web installer can't access mysql
ahh, I didn't have php5-mysql installed

hey guys.
in InnoDB…the only clustered-index is the primary key?

yes

interesting.

my definition you cannot have more than one clustered index
s/my/by/

ahh.
so when you create a new index, is that considered 'the secondary index' ?

the indexed dat ais the actual row

I understan clustered-indexe's

yes any additional indexes point to the clustered index

interesting.

how do I find out which mysql database takes the most space?

cd $datadir; du -sk *
|sort
-n

um, how do I find where is the datadir for sql?

show variables like 'datadir';

prompt?

yes

datadir; and there was an error.

show variables like 'datadir';

+—————+———————————-+
| Variable_name | Value |
+—————+———————————-+
| datadir | CWork\mysql-5.0.41-win32\data\ |
+—————+———————————-+
1 row in set (0.01 sec)
like that

thanks!
ok, are all of these databases or tables? I don't recognize som eof the names nad they don't show up when I list databaess.
Is ibdata1 a default binary file for mysql?

what is a tuple?

SELECT * FROM `menu` WHERE `mid`= (range from 73-262)

between

If I delete "mysql" and "information_schema" database..will that matter? do they do anything?

Is it wise to store images in databases, or is it better to store its path and link to it?

sid, don't do that. I did it once, on my own pc to see what would happen - just don't do it.
You sort of lose the ability to login, etc
Bear10, how bigga image? usually its not a great idea

120×120 pixels
max

o, too late
heh

atthis size you could store them on the DB

good thing I don't have any data in the database yes
s/yes/yet/

but the rule of thumb is store links to the Filesystem

sid, did it mess it up badly?

thought so, just wanted to make sure

the mysql database stores all authentication info, stored procs, etc. And the information_schema is a set of system views, you can't drop them

thanks

if you need to re-create a default mysql database, use mysql_install_db script

hey guys.
Unknown system variable 'general_log'
why would I get this?
I'm trying to turn the general_log on.

version?

5.0

general_log was added in 5.1. In 5.0 you can't turn the log on dynamically

ah,
so 5.0 doesn't have a general query log?

It does, but you have to put "log" in your my.cnf and restart the server

ahh
where do you work
where do you put it?
under [mysqld] ?

yep

great
how can I check which my.cnf file it's using?

On *nix it's generally /etc/my.cnf

yeap, but I'm using xampp to mess with everything.
I think I'm about to move to Ubuntu.
or Red Hat, just so I can get used to what 'enterprise" is doing.
and I say ' " because enterprise is flakey, just like those single/double quotes.

Hey all - having a heck of a time: I installed mysql server and set it up. Installed a sql program called sureinvoice, which appears to have installed correctly, but I can't figure out how to launch it. Help?

how would I make a temp table a MEMORY table.

CREATE TEMPORARY TABLE t (columns…) ENGINE=MEMORY

but I'm saying for my join operations.
like, whenever I do a sort, or a join, etc, MySQL is creating a temp table.
how do I make that table a MEMORY table rather then whatever the current storage engine is.

ahh..the output of EXPLAIN is saying temporary table?

hrmm..not so much. I just figured that's what's happening by default.
I mean, when you run mysqladmin, it shows the ammount of temp tables being created.
and they're just regular selects.
I wanted to make sure those temp tables are MEMORY tables.

Those will be memory tables unless they are larger than tmp_table_size

ah.

Check the status variable 'created_tmp_disk_tables'

SarahS, where are you from?

I've setup multi master replication on 2 databses, on 1 it works perfectly, the other does not apear to be replicating
Any sugestions to how i can debug that?

E-bola: anything in the error log?

Im not quite sure where it is
im running Debian under /var/log/mysql apears to be binary logs which i cant use for anything?
I saw some replication related messages in /var/log/daemon
but besides not being able to connect when i restart the other server, i havent seen any errors there
binlog_do_db = mailscanner
binlog_do_db = sa_bayes
binlog_ignore_db = mysql
is that correct syntax?

E-bola: I assume you meant 2-way replication and not multi-master replication?

mmm well its 2 way and there are 2 masters
so each server is both a slave and a master
hmmm
show master status looks correct
both db's are listed
The only difference is mailscanner was created and had data in it before i started replication, while sa_bayes is a new db

E-bola: That shouldn't matter. Find you error log (on the slave that isn't working) and check for errors

hmmm
# Error logging goes to syslog. This is a Debian improvement
the log just says that the slave is starting and that "Neither –relay-log nor –relay-log-index were used"
i asume those 2 items arent related to my problem?

E-bola: anything interesting in SHOW SLAVE STATUS?

I just dont get why 1 db is being replicated and another isnt
just says Waiting for master to send event
Error 'Can't create database 'sa_bayes'; database exists' on query. Default database: 'sa_bayes'. Query: 'create database sa_bayes'
that the one thats actualy working though
No
Shouldnt that be a Yes?

E-bola: Yep, that should say yes. It doesn't because of the error

Hmm is it necesary to set replicate-do-db ? that wasnt used int he tutorial i was following
it says yes on 1 of the machines

E-bola: I'd suggest re-syncing the broken one, but if you don't want to do that, you can try skipping the statement causing an error with SET GLOBAL sql_slave_skip_counter=1

slave from the time slave gets a query? is there a setting for this? if so, what is it?

how do i get it to resync?

E-bola: what tutorial did you read? It
E-bola: It's the same process as setting up replication the first time

http://www.howtoforge.com/mysql_master_master_replication that one

start slave;

Query OK, 0 rows affected, 1 warning (0.00 sec)
how can i see the warning?

E-bola: SHOW WARNINGS;

ahh just said it was already running
Could there be anything special about a db, that would make it not work with replication?
hmm i give up
ile have to look at it tomorrow

hey guys, can I ask PHP questions that relate to mysql here?

how can I make mysql puke if a value I INSERT would be truncated?

strict mode

why i can't have this ( NOW() + INTERVAL 1 MONTH ) as default value for timestamp field ?

use datetime with before insert/update triggers to always set the column to whatever you want

can that be enabled for a single table?

probably not

i never used Triggers with mysql before

how can I have one field in a table unique, and have another field autoincrement?
it keeps telling me i can only have one unique

one primary key
many uniques

so in the table, i want email to be unique, and "usernumber" to be auto-incr
so i set the email for primary and usernumber for autoinc?

create table users (userid int primary key auto_increment, email varchar(255) unique);
whats difficult with that

sweet, that did it. thanks domas

oh wow
a) if that table is single one in database
b) if it is transactional and others are not
c) you can set sql mode before inserting and reset it afterwards
if triggers could raise errors, it would be easier

hello
can i do a sort of 'find and replace' in a db,please?

how do i set the mysql root password

REPLACE INTO
or INSERT … ON DUPLICATE KEY ..
or UPDATE

how do i reset the mysql root password

reset root

See http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html

mm
via phpmyadmin should be easier to do it?

hi, is there a way to force a table to have only one record?

man, i just cannot seem to find my query speed problem
1.2s to match 2 unsigned ints and join to another unsigned int in another table
75k rows
keys on all the unsigned ints
I did an explain and it ends up 0.03s for the query
but in perl, its 1.2s

is it possible to start replication on an existing database? one that has been arround for a while?

Internat, for the master or slave side?
you can start it on the master, you then need to copy the data to the slave and start the slave up there
i found a nice howto .. one sec
http://www.idms-linux.org/tiki-index.php?page_ref_id=14 = that describes 2 methods

both
awsome, ill go have a look

becarefull on the slave, you cant just enable replication at anytime you want. … you need the position the server is at in the log, how else is the slave going to know where to start the replication?

http://pastebin.ca/662022

replication is actually a kind of ongoing point in time recovery
to set up a slave, you recover a new machine from a full backup
the full backup must be consistent (a single binlog position) and the binlog position must be known

well put Isotopp

you then change master on that new machine, telling it
a) the login information for the master, host, port, user and port
and b) the binlog position the new slave is currently at, that is, the binlog position from the backup
uand b) the binlog position the new slave is currently at, that is, the binlog position from the backup/u
the slave will then upon start slave login to the master and download the masters binlog from that position
and apply it to the slave.
if you have an existing server and want to turn it into a slave

can anyone see a problem with this … http://www.pastebin.ca/662026