if you use it will display it?
but if you don't use it then what?

insert into pm values ('$from', '$to', '$subject', '$message', '$time')
Shrews, untill I add an ID identifier to the begining.

ubuntuserver_, you cannot get UTF english. Plain and simple.

if you are only inserting values for SOME of the columns, then you need to explicitly name them in the INSERT statement
!m Gargantua insert

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

ubuntuserver_, you get UTF. UTF and the whole UTF encoding.

and why is there no option to do this

INSERT INTO x (col1, col3, col4) VALUES (….)

or why there is no install english and another language that user want and to remove for example serbian

ubuntuserver_, because it isn't possible.

hmm why ./configure say without-uca

shrews, ok, but will the id autoincrement it self?

Argh, can anyone else explain that an English-only UTF encoding is impossible to ubuntuserver_?

if you don't include it in the INSERT? yes, of course.

Shrews, thanks.

you do not understand the relationship between character sets and languages

ubuntuserver_, I have to go
TTYL everyoen

lets see

Thanks for the help Shrews!

in phpmyadmin i see collation serbian how to remove from there
ok then thanks

hack the source to phpmyadmin to not show it

compile the phpmyadmin?

lol
compile it?

shhhh

phpmyadmin is a php script

then how to hack it
notepad?

edit

and what to search

it's one giagantic php host script

lets see now

why do you want to remove it?

hey see what they write
source of the phpmyadmin
then i need to compile it
becouse i don't want to show it in phpmyadmin collation

God damnit…

what

Nothing.

can you hack the phpmyadmin

just make sure you use a good compiler.

gcc

ya that's perfect
you're running Ubuntu aren't you?

yes
server

ah
then you need to uninstall apache
and php

why ?

compile the source
then reinstall them

hi all, i dumped a database on my ubuntu mysql test server w/ mysql admin because I can't use it for a few days, i opened the file in my mysql admin on this windows machine and I just get a very vague "Mysql error", anything I'm missing?

because phpmyadmin gets confused

now lets see when download the phpmyadmin source

otherwise your entire OS will get messed up
no dont

it only restores about 800 bytes

you have to uninstall apache and php first

ok

ya

how to compile not to show serbian language?

Uninstall apache and php first, jee.

i uninstall it

both of them?

yes

that was fast
anyways

uninstlal is fast

you need to check phpmyadmin/libraries/db_common.inc.php

the source that i download ?

you dont have phpmyadmin yet?
man

i have it

Did you really uninstall php and apache?

but i ask the soure or the phpmyad
i have phpmyadmin and the source

go to /var/www/phpmyadmin/libraries/db_common.inc.php

i uninstall apache and php
i don't have it

i have phpmyadmin and the source

you just said you have it

but not in the www directory
it don't exist

then you dont have phpmyadmin

bah i have phpmyadmin
in /home directory

then edit it

you could have phpmyadmin outside of www root

ok

heyt letss see

chadmaynard, I know, I was assuming that he used apt-get.

i have phpmyadmin in home directory but when i hack phpmyadmin i will put iin www directory

ubuntuserver_, you just need to edit the source now.
ok
you need to "hack" it where ever it is.

ok
how to hack it

you need to delete the directory called "libraries"
so then you jave permission to everything else

ok

done?

moment man you ask very fast
i don't have mouse i make it from terminal to terminal

ah

anything wrong with this syntax? CREATE TEMPORARY TABLE mytable1 SELECT `foo`, `bar` FROM table1 UNION SELECT `foo`, `bar` FROM table2;

oh wait, did you uninstall mysql hosting too?

no

chadmaynard, i did this in mysql query browser, then i did select * from mytable1; and it says it doesn't exist

what if you create without temporary ?

bah

what?

ok moment i will uninstlal it

ill try

what mysql?

chadmaynard, it seems to have worked that way…

do you know why?

i will uninstall mysql

chadmaynard, no i dont :/

oh man
now I got to go
ubuntuserver_, I'll talk to you later.

Temporary tables are associated only with a connection. The query browser creates a new connection to mysqld for each query you execute, so even tough the temp table was created it was destroyed when query browser ended

oh i did not know that it would do that

nooo
how to see if mysql exist
i uninstall it,but want to know

It sounded to me like he didn't have a clue what he was talking about anyway

chadmaynard, i'd be doing this with scripts but my real development server is on a laptop and my cat decided she'd chew through the ac adapter so i'm kinda in the dark here
also as I previously said, i can't restore the dumped database from php mysql web hosting admin from linux on windows

chadmaynard, I did, I was trying to get him to fux up his server

was it plugged in when the cat chewed on it?

chadmaynard, i don't think so, she seems no more electrical than usual
it really sucks though, mysql admin has to have the least verbose error messages i've ever seen

wehat

?

he say that he know how to remove serbian languages

who
?

look up he know how

well then
remove serbian languages from what? And why have you been trying for two days?

G-man: purposefully giving harmful advice in the channel will NOT be tolerated

becouse i don;'t want to see serbian languages

ok sorry.

i didn't try for 2 days i try for 2 weeks

in phpmyadmin or….?

he say that need to be hacked phpmyadmin

he's a moron though, describe what YOU want to do…

in the collation
bah
in phpmyadmin i don't want to see in collation serbian languages how to remove it
phpmyadmin say that is mysql problem

ubuntuserver_ close your eyes?

and eveybody say that it can't be removed becouse it need to be installed

ubuntuserver_ recompile your own mysqld

but i will not use it
bah i recompile it

also make sure you configure it

./configure –help

lets see

dude just leave it there.

no man

i cannot be sure, maybe a change of some xml file can cause the server to ignore it

yes yes there need to be something
i compile the mysql with this

ubuntuserver_, listen dude, phpmyadmin is a php hosting script, you dont compile it.

with-extra-charset=armscii8 asci big 5 cp1250 cp1251 cp1257 cp850 cp852 cp866 cp932 dec8 eucjpms euckr gb

serbian is cp1251
is it?

it is Bulgarian

Cyrillic?

yes

you want to rest of 1251?

yes i want the rest
i only want to remove some language
but nobody understand.

i dont

but nobody understand.

why would i want to remove some language?

with-collation=armscii8 asci big 5 cp1250 cp1251 cp1257 cp850 cp852 cp866 cp932 dec8 eucjpms euckr gb

does it bother you?

i will not use it
why i need to use it?

there is a file index.xml in share/charsets firectory, maybe if you remove the appropriate entry it will disappear
but I cannot be sure, never tried something like this

in mysql directory?

ubuntuserver_ you're wasting a lot of time/money over something thats not important

ubuntuserver_, so what? I dont need to use my bike in my basement RIGHT NOW, do you see me going out of my way to throw it out?

money?

worse, you're wasting OUR time and money as wel

mysql basedir /share/charsets

hmm your money
ok i will try it
i use the php mysql web hosting product

ubuntuserver_, dude, just pick another collation.
it's not hard…

but i don't want to use some languages that are there

so?
dont select them then
you can only use one langauge.

if you don't use your bike for year then why not to recycble bin ?

you can use 1 language per col, so you can use many

i know that
now i need to install again apache and php?

but anyway serbian seems like an alias to other collation, same as many others there

ubuntuserver_, that was a bad analogy, say that you have 3 cars in your garage, you only use one every day, why not throw out the other two?
because it's a waste

can always sell away the other 2

some day, you will need to use them.
kimseong, god damnit, I'm trying to make a point here

yes everybody can sell them

but you cant sell an item from a drop down menu can you?

yes
they say that can be
don't lie

but there's no point apart from "i dont want it"

and another nobody can drive 1 car in the day?

its not as if it breaks something

or you can drive mych cars in one day?
but say break it?

shut up about the cars

hello. I am running mysql (client) from a mips enviornment — I know, not supported, but i'd like some insight… I do 'echo "show databases;" | mysql -h 192.168.0.118 -u root' and it segfaults right after the read command; any ideas? maybe a libreadline problem?

mysql client has a -e option to execute commands

mysql -h 192.168.0.118 -u root runs ok?

ya, the command is responsive but as soon as I run a command it segfaults
I did a help, status etc no problem
but as soon as I ask the server something (query) it craps out

are they the same version?

humm, not likely
5.0.18 client and 5.0.22 server

pretty much the same

how to get mysql client to be that

on mips?

how you get mysql to be another version?
general?

different system, different compilation, different 'distribution'

yeah

I can make them the same version, but that means recompiling… gah

yeah

but really the strace isn't when it asks the server; I should see send() to the socket; but it's like right after the read()

but how you make the mysql client to be another version
–compile without server?

you compiled this yourself?

yes, and a custom makefile; as I said, unsupported, just looking for insight

what compiler and version?

"unknown-linux-gnu (mipsel)"

no, what compiler did you use (gcc?) and what is the version of the compiler
"To get MySQL to work on Qube2 (Linux Mips), you need the newest glibc libraries. glibc-2.0.7-29C2 is known to work. You must also use the egcs C++ compiler (egcs 1.0.2-9, gcc 2.95.2 or newer)"

gcc (GCC) 3.4.4

does the crash produce a core file?

doesn't seem to… but my kernel might just have the core option disabled, do you know what I need to do in /proc to enable them?

probably just 'ulimit -c unlimited' will do it

ya, ulimit doesn't exist
found it; I'll see
got a coredump…

ever use gdb?

can I email it to you and send you my root password and you take care of it for me?
nope

nope. no way. nuh uh.

wow, that ubuntuserver_ guy still wants to remove the language selection from phpmyadmin?

im tired of it too

isn't it in config.inc.php?

how many times are we gonna have to say no?

try 'gdb -c core mysqld'

mysqld?

path to the executable that caused the core dump… i meant 'mysql' since it was the client
did you compile with debugging info?
-g

mips… do I have to compile gdb for my mips too? thats a long shot

no, I didn't compile mysql with debugging

gdb isn't already installed?

haha, I highly doubt it

you'll need to recompile with debug info

sweet, someone made a package for mips already, my lucky day
alright shrews, and do you guarntee that this will make my day if i get this to work?

the path i'm sending you is complex, but it is the best way to figure out why it is crashing. not for the feint of heart
i make no guarantees. i only set you upon the path of enlightenment
it is up to you to make the journey
:-)

I'm just thinking that if I look at the source code of the mysql client, and see what path it takes after it does its read(), I know the problem is before the syscall to transmit that… I must be able to figure out where it's crashing

you can try, but the code is complex. gdb will tell you exactly where it is crashing as soon as you run that command i gave you
anyway, i have to go. good luck.

thanks Shrews!

Hello, I have a problem, I want to insert thatn containds 'bad characters' like O:9:"PeticionO":4:{s:8:"option";, but using INSERT INTO FILED value('STRING') I have got a 'bad syntax', how can I scape it those chars?

escape them
!man escape

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

hi everyone
How do I make a select query then run find commands on it with php

using mysql_real_escape_string($str) seems to work, lot of thanks

sure
design your sql query first

Done.

now go ask #php how to run a sql query.
if you haven't done so already

lol thats a problem

test your query in the mysql console
why is that a problem?

I said "u" instead of you a few too many times
They are kind of fascist in the way people can communicate

I don't see any occurances here.

7

or consult the documentation on php.net

what could be the reason why DISTINCT is not working

that depends on many factors
let's see your query

Yupp one sec, im looking

also, using 'u' will make you appear slightly immature, and it does annoy some people.
as a general rule, one should not use it.

ehhh i would much rather not get started on that again

that was my point.

How do I erase the command log?

$query="SELECT DISTINCT(amount),samid,address FROM jazz WHERE samid='$var' ORDER BY amount";

try DISTINCT `amount`, samid,address FROM jazz WHERE samid =

hi… is there a free sql studio or something I can download for connections to a mysql db?

i.e., do NOT use brackets.
misc–: you can ssh to your server.

brackets?

yes
()

yeah this isn't for me though, it's someone else. I always just use the console

the DISTINCT will apply to your entire selection.
misc–: phpmyadmin

I dont want it too
I want amount to be distinct only

select distinct means distinct rows, not 1 particular column

then you probably want grouping instead.

use GROUP BY for particulat column

i.e. MAX(amount), samid,address … GROUP BY samid,address

Can i see how many got grouped

anything else aside from that? Don't really want to install that

sure. Run the query.
gui

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/

misc–: ^^

ok thanks

you can also COUNT() the rowid to achieve your goal.

hmmm let me take this all in

sure.

What does the MAX function achieve?

!man max

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

oops, wrong page.
MAXimum

!man aggregate function

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

!man aggregate functions

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

So if I GROUP BY amount, it will still show duplicates

no, it will not if you select MAX(amount)

ok

we never told you to group by amount.

1 sec let me run this query
thanks

i.e. MAX(amount), samid,address … GROUP BY samid,address

you need to group the non-aggregated columns.
it's as simple as that.

Does the column field name change if I use MAX(amount)

use an alias
!man alias

see http://dev.mysql.com/doc/refman/5.0/en/problems-with-alias.html

sigh
I guess it does apply after all. Read it.

nope still showing duplicate records

how do I do LOAD DATA INFILE in phpMyAdmin ?

what is your actual query?
while some of us know how to use phpmyadmin, the mysql team did not write it or support it

ok nvm then

you might be better off in a phpmyadmin support channel

just wondering

$query="SELECT MAX(amount) AS am,samid FROM ppl WHERE samid='$var' GROUP BY amount";
or would i group by the alias?

hi do you know anything how i can bypass the port 80 from my router i tried to test my page directly from my system how do i make it work

hmm let me see

i have Dlink 604+

do NOT group by amount
now, listen to me.
you ALWAYS group by the non-aggregated columns
i.e., the ones where no FUNCTION is performed.

ah ok

what do you mean by bypass port 80 on your router? and why are you asking this in here?

because he thinks you're his mentor, I guess
and that we'll tell him that he's OT in #apache

very funny thumbs just need help..

well this question seems to be more of a #apache type question
anyway, what do you mean by bypass port 80?

ok i go to the apache…

Go to dlinks website and find out how to "Port Forward"

i did it cofuse i need pix with step by step more easier for me

Sheesh.
try #retard

sheesh thanks for insulting me…

try not to insult other users. THanks.

I really tried
Can I try to explain to you what needs to happen and maybe you can supply me with some feedback

that might help.

Ok, from the top
What I have is a table with username, amount fields
What i need to happen is any records where amount is a duplicate i need them to not show up when I run a query

then you took the wrong approach.

Ya I figured

you need to do MAX(username) and group by amount

ahhh ok, let me try this out
Nope still showing ones that have been duplicated
Let me mention that Username can also be a duplicated field

what query did you run, exacly?

$query="SELECT MAX(username) AS usr,pid,amount,username FROM ppl WHERE pid='$var' GROUP BY amount";
woops

you need to group by EVERY COLUMN THAT IS NOT IN A FUNCTION

$query="SELECT MAX(username) AS usr,pid,amount FROM ppl WHERE pid='$var' GROUP BY amount";

that includes pid in your case.

you need to do MAX(username) and group by amount

because THEN you only select two columns
you need to start being more clear.

but i need some criteria

s/select/selected/
tell me EXACTLY all the columns you need.

id PRIMARY

Currency

erm
I don't care about the aliases
I simply want the list

id, amount, username, pid

ok

Im sorry, i am an absolute beginner

SELECT MAX(username), MAX(amount), id, pid … GROUP BY id, pid

where does the criteria come into play

in the WHERE clause.
you can build that yourself.

ok, and can I use pid in that or will you yell at me again?

you can use whatever field you want.

ok thanks, brb

the grouping affect the column in the SELECT list.
not in the WHERE clauses

Still doesnt work
Showing duplicates
Why cant they just go away

what query did you run, exactly?

I dont know why the username field is getting the MAX thing
it is just as important and productid
or "pid" as you know it

because you told me both amount and username have dupes.
therefore, I was trying to remove them

ya well so does productid

ok start over
use a pastebin if you must
what are you trying to achieve, exactly?

but i just want any amounts that are duplicated to not show

amount? weird requirements

only the amounts?

yes

so do you mind if you get multiple users with different amounts?

i dont mind that
they can stay

i.e. user1,10$ and user1,20$

thats gold

what if a different product has the same price as another product?

hmm
Nope that doesnt matter either
that can stay

ok.
so for a given pid (I'll assume that's productid)

lets just start with group by amounts
and see if that is what you want

Its not

then why not?

I dont even want duplicates to show

I want to create a database, but I want to edit the data, but which one is to prefer so I can copy alot of data/input/strings without getting errors?

duplicate amoutns will not show

It combines them into 1 field so to speak

?

i dont even want that one field to show there

what field?
amount?

Im sorry, record

then don't select it.

It combines them into 1 record so to speak

no, MAX does not combine records.

you better pastebin the exact query, and sample result that you don;t want

it finds the maximum value.

I am talking to kimseong

then don;t use max()

I know. And you seem confused.

Am I wrong by saying GROUP BY does not discard all records that have a duplicate field

and note that mysql picks a random row for the other values

it just combines them into one record

is the same as using DISTINCT

when no FUNCTIONS are applied to any column.

So its not possible to have any records with duplicate amounts to not show up on query

it would be, if you elaborated on what you need exactly.
as of now, all my guesses have been off.

Let me explain my thought process as an unexperienced beginner

please do.

The first thing i want to do is only work on one product
So i set up a WHERE clause that filters out that one product
Then what i want to do is neglect any records that have the same amount field, regardless of the username that posted it
thats it

where productid=??? group by amount

we might be getting somewhere.

GROUP BY amount still shows the amounts that are in more than one record

yes, because you're also selecting username

pastebin the reslt

enough guessing, heh kimseong

Alright guys
I have resolved the issue
Ok, i have another project for everyone
How do I find out the position of a record after sorting

define position

3rd from the top
2nd from the top

rows in tables has no position
rows in tables has no order

Of course they do kim

use php to extract the array row index.

set @row=0; select @row:=@row+1, …..

array row index…ill jot that down

for the result set numbering

it depends

Ok, thanks Ill prolly be back shortly

do you want to number your result set?

If that will help me find out whos on top, yes

you can use the php array manipulation routines instead.

that number is the same as the order in your result

Im trying to get caught up with the lingo
one sec
what is the @row about?
I have seen that elsewhere, while researching this topic

try googling mysql row numbering

Ok
So theres not easy way to see if something is at the top or bottom of a result query
I dont necessarily need to know exactly where it is, just if there are any below it or above it

why would you want to know that, exactly?

Urgency of the order

excuse me?

long sotry
story

unless you help us understand you, we can't help you

i understand

Understanding is a three edged sword

ORDER BY

you must be loucky!
lucky, too
I took 900 MB of pictures with my phone
err digicam.
it's taking a long time to empty now

pictures of what

a party @ my family this weekend
people I didn't see in 10 years
I went a little crazy
I will post the gallery link

Lots of ways, depending on your requirements and whether you've stored the proper detail.
bLots of ways, depending on your requirements and whether you've stored the proper detail./b

you miss him?

I wouldn't go that far…..
the work week starts very soon

Sometimes doesn't seem to end.

I'm sure you can retire soon

I don't think I'm quite ready for that.

hello
I was wondering if anyone here can point me towards a linux, uml2 gui editor that can draw db class tables and generate sql (or xml) code?..

ibm rational for linux ?

kimseong, is that free?

don't know, it used to be a very expensive tool, before ibm acquired it

probably still very expensive…

might be able to get a trial copy

any other suggestions?

no idea, i don;t use such tools
http://www.zoomerart.net/index.php?target=desc&progid=7634

how do you document database schemas and stuff?

maybe not a reliable source

Embarcadero probably does too.

please ignore that url, i got from google, but probably some software piracy site

kimseong, sure

http://www.interactivecode.com/software-engineering-2/embarcadero-describe-uml-2-0-metamodel-26629/

trial version http://downloads.zdnet.com/download.aspx?docid=78191

kimseong, thanks, I'm trying to stay away from trial versions
the file I'll be passing around will be an xml file
just need a program that can understand it and draw pictures as needed
strange that I can't find one so easily..
thanks guys, I'll have to search some more

anyone here using best area as their host ?

question for you guys, i have some tables with dissimilar fields, I am creating a temp table that is simply select * from table1 union select * from table2 etc
now if I select a record from this temporary table, it will have a bunch of empty fields will it not, say if the field foo existed in one of the other tables but not in the table in which the original record resided?

No. You can't do part 1. You'll never create a temp table with data like that.

Do I have to specify the length for varchar, or I can leave it out?

Both sides of the union need to be compatible and fields in proper order.
Specify the size.

xgc, ok thats a problem, without using a union then is there any way to do that?

You can use UNION, but you need to specify the fields properly.

actually, maybe a temp table is unnecessary

You can also create a view that provides the same result.

xgc, i have a unique number and a last name, how would i search from multiple tables to pull just a single record based on that criteria

Check that VIEW in your version of MySQL handles this properly.
Is this a design problem?

no, the tables needed to be segregated, its a hard drive database and different brands have different fields that need to be indexed

Aj. So the answer is yes, this is a design mistake.

i disagree

You can store the common data separately, with foreign keys to the non-common detail.
s/to/from

Xgc, i sure cant, as our host has a version of mysql that does not support foreign keys

What version?

4.1

Since when does 4.1 not support foreign key constraints?
In any case, you don't need a constraint to have a logical relationship, even if not enforced.

are not foreign keys for innodb?

Sure. You don't need enforcement, obviously or you'd be using InnoDB.

the version of mysql on our host does not support innodb in any way shape or form

It's not required for your needs.

okay

Yuo can have a foreign key without a constraint. The FK is just a logical concept.

is it possible to search multiple tables with criteria without actually combining them?

You just won't have a database that guarantees the data relationship. But that's what you have today, anyway.

hey, mysqld is kicking up to high usage, i'm not exactly sure of the cause though…

Seperate queries or union.

could anyone help me brainstorm potential causes of that kind of problem?

i need help on this

mysql_connect(): Can't connect to local MySQL server through socket '/usr/local/mysql-5.0/data/mysql.sock' (2) in /home/content/p/u/s/pussify/html/phpBB2/db/mysql4.php on line 48
mysql_error(): supplied argument is not a valid MySQL-Link resource in /home/content/p/u/s/pussify/html/phpBB2/db/mysql4.php on line 330
mysql_errno(): supplied argument is not a valid MySQL-Link resource in /home/content/p/u/s/pussify/html/phpBB2/db/mysql4.php on line 331
Critical Error
Could not connect to the database

..
my sql error ?

It's not pretty when the data isn't stored conveniently.
Don't flood the channel. Use the pastebin.

sorry

!perror 2

No such file or directory

That's for you.

!perror 2

No such file or directory

ohhh
…
hw to solve it ?

Probably a php configuration issue. Check the php docs or the general information you can find in a zillion places if you use google, many pointing back to the mysql documentation.

how can i enable logging of all sql statements on my server?
i just need it temporary for debugging

ok ty

Xgc, alright so is there a way to make an auto-incrementing foreign key in 4.1? Right now each table has their own auto inc but i'd prefer it if there was one unified one

In the config file: log=/path/to/actual/mysqld_querylog.log (in the right section [mysqld]) or override the config file with the corresponding option to enable logging.

thanks

Restart the server as needed.
or possibly send a SIGHUP.

:/

hi all. is there a way to get distinct rows based on 2 fields?
like, select distinct (a.student, a.year), a.* from a

DISTINCT applies to a row.
actually, all the rows returned.

how do we say what makes a row distinct ?

the values of that row are unique from within the whole data set

group by pixiedust

If you have a primary key for that table, SELECT DISTINCT * will return every row. There can be no duplicates since some set of columns (primary key) is guaranteed to be distinct / unique.

hmm, ic

If you want some set of distinct tuples (f1,f2,f3) that does not include the primary key, SELECT f1, f2, f3, MAX(f4), … FROM tbl GROUP BY f1, f2, f3;

so "max(f4), …" won't be checked for uniqueness?
ah hang on.. i c what you mean

It's just an aggregate of the group.

you're not using distinct….

Correct. GROUP BY does what you want.
In your case you want (student, year) to be the distinct tuple instead of (f1,f2,f3)
You then need to determine which aggregates you want for any other fields.
If you want something like the first or last row associated with each group, http://dev.mysql.com/doc/refman/5.0/en/example-maximum-column-group-row.html

thx

You're welcome.

im trying to make a simple forums page. in my topics table I have a field `last_post_id`. Is it possible to query the topics table and order the results based on the time field of the post..that matches the `last_post_id`?
all in 1 sql statement?

Sure. But you could have done this without that field in the topics table.
See: http://dev.mysql.com/doc/refman/5.0/en/example-maximum-column-group-row.html

ya, but I wasn't sure if that would be too too many resources

But to use your current design, a simple JOIN is all you need.

right now I haven't even setup the last_post_id at all, so if there is a better way, would rather do that
it is structured like forum-topic-post, so in order to order the forums i would have to query every topic in that forum, then query every post in that topic, is that possible without taking forever?

SELECT t.*, p.datetime_field FROM topics t JOIN posts p ON t.last_postid=p.postid ORDER BY p.datetime_field DESC;
You didn't ask about the forum table, so this SQL doesn't include that detail.

k
actually I dont need to order the forums, so meh
i will figure out the topic thing first, thanks

I hope "meh" is a good thing.

ya sure

hey guys
anybody have time for q?

better you just ask

hi guys

Time passes just as fast whether you ask the question or not. The difference is the people who might answer don't have the question.

urgh
im having this error.
No resourceTable 'test.sql' doesn't exist
but i have the file test.sql

Ouch. What caused that?

okay
here is the scenario
I am using innodb

how can i put that working?

What did you do?

okay, let me make this all one line

w8
let arash finish talking

I can multitask. I have no idea if arash will ask a real question.
and I'm about to go to sleep.

ok

— using innodb, I insert a row, then read back the row immediately, so I assume it has been inserted fine… then I pass it back to the user, and the user makes a request with this id… however, 5% of the time it can't find the row

Xgc,

in your statement it assumes I have last_post_id set, is there a way to do it without having to set the last_post_id in the topics table?

i have a file
that connect to mysql db
and i have a test.sql

— the second request happens on a different thread/connection, and the requests are within 20-30ms of eachother

after the connection

I already told you you don't need that column. You are the one who said that field was set.

it give me this error
No resourceTable 'test.sql' doesn't exist

by just looking up the last post with the topic_id

— I'm not using transactions

ya, I guess I am asking how then =P if you know off hand

Yes. Read the URL I posted.

k

— so basically, my inserts aren't propogating on all connections for some reason

Show the exact commands you used.
Sorry. That was for you.

if you are using innodb, you are using transactions. by definition. and if you'd use myisam, when a row is inserted it's still there for another connection also. no issue.

I can't help you if you don't tell me what you're doing.

what level of transactions do I need to use to insure all future requests will be able to see this row?
level of isolation****

If you're simply asking how to process some file.sql, use the mysql command line client: mysql … file.sql

xgc

(I tested with myisam and it worked justed fine, but I want to use INNODB)

i will show you
a sec.

repeatable read or read committed. the 1st is the default of innodb. and you can set autocommit=1 (also default) to have each statement be in its own trnasaction by default rather than having to do start trasnaction/commit

Please, type complete sentences/thoughts so that you don't flood the channel with no useful information.

k Xgc

I'm currently using repeatable read and have that problem
let me try READ COMMITTED

Xgc,
http://pastebin.com/m5b2049cb

what puzzles me is why the second query, which is run /after/ the commit could possibly get old values

i need do that
i create an account

ah!

but now it give me this error

would reesablishing the connection fix this?
why ahh!

No resourceTable 'test.sql' doesn't exist
but i have the file test.sql

well, under repeatable read, if you explicitly start a transaction (with START TRANSACTION, or have autocommit=0), then the other connection won't see until both the insert transaction commits and the other connection starts another new trnasaction.

on the first thread, I can get back the inserted row, but on the second I can't

are you using some db abstraction layer? or using explicit transactions directly?

I
m using sqlalchemy, which is committing after each query
so

python eh?

on thread 1
yar

Xgc, i do u put the table test.sql working ?

on thread 1, commit insert
read row
correct row is returned!

well, simple… either or both of the above is where your problem comes from.

Xgc, how do i put the table test.sql working ?

then thread 2 tries to fetch row after
and can't find it
all thread 2 does is try to read the DB

sorry for the english im from pt

it probably runs in autocommit=0, arash.

no transactions of its own

Use the mysql command line client or some similar tool -or- talk to the authors of the application you're trying to install.

then it already has an active transaction before running the query, and so it never sees the new row as it's from a latr transaction

oh

feel free to disagree, but it fits the symptom.

Xgc, i tryed install phpmyadmin
phpmyadmin can do this for me?

so I need a way of getting a clean slate on the second thread?

anyway if phpmyadmin can do this for me, i cant acess phpmyadmin
Internal Server Error

Hi

im using ubuntu btw

http://rafb.net/p/2kNGei65.html
if you are curious

Sorry. My brain just said, "Internal Server Error", trying to understand your comments. Maybe someone else can handle this one.

lol

Have you ever used UDF on X86_64 system?

"I'm sorry Dave, I'm afraid I can't do that."

Xgc, i will try do what u just sayed
mysql file.sql

hi

i just want this working

what's missing there is how alchemy initialises a new connection. I presume it sets autocommit=0, otherwise doing commit is superflous.
bwhat's missing there is how alchemy initialises a new connection. I presume it sets autocommit=0, otherwise doing commit is superflous./b

No database selected
pwnedomina@pwnedomina-desktop:/var/www/test/m/WM.rar_FILES/adminka/adminka$ mysql -uroot -pmysqlrox test.sql
how can i select a db?

use
use foo;

like this
mysql -uroot -pmysqlrox use foo; test.sql
?

oh on the command line
no just say foo
mysql-uroot -pblahblah foo test.sql

k
thx

oyyyy
still having problems!

thanks. bye all.

turn on general query log on the server, and see what a connectiona ctually does.
or make alchemy output everything it does for a connection, if possible.

I'm going to try setting my transaction level very low
and see what happens
isolation*
switchign to read committed fixes it
that seems to indicate that the second thread is on a previous version of a table because a tranaction was started?

most likely
you can force a locking select to get the freshest version with SELECT … LOCK IN SHARE MODE

i'm trying to figure out how to add an auto_increment column to a table we've already created
and have it populate with numbers

it'll repopulate fine

are you referring to me? or someone else?

Frozen-Solid: yeah, just creating it should make it populate fine

ah alright

default null should generate it properly for each row

i'm like, terrified of breaking this table we've spent so much time on already

that's what backups are for

doesn't make me any less scared :')
what's the best way to backup a database anyway? we're accessing it via phpmyadmin?

if I want to import data (via mysql somedb db.sql) then how can I make it import even if there are errors? Is it –force?
yep looks like that was it.

misc–: It's –force, but you have to be sure what you are doing

how could I migrate the sql "SELECT SQL_CALC_FOUND_ROWS FROM …" to mysql 3.x?
I know only mysql 4.x support SQL_CALC_FOUND_ROWS …

hmm
not use 3.x
the other way is to issue one query with count(*) and the other to fetch the rows with limit

ok thanks

Hey
is there a way to tell how large a table/database is (in Bytes/KB/MB)?

Hello!
Is there a way to increase the field in a record by a certain number in a single query?

wingot yup, you can look at the files
yup

like "UPDATE `table` SET `number` = (`number`+10) WHERE `id` = 1"?
How would I go about it

like that only without the parens

hmm…

?

The files?
directories in /var/lib/mysql?
Think I found them
Thanks ebergen

Hello - has anyone ever seen MySQL spike up to 200% of CPU before (according to 'top')?

optix, on a dual core/cpu system

this is a single core system though
err
maybe not.

: )

*sigh*
optix, /proc/cpuinfo

yeah - it is. It's a P4 3.06 w/ HT

that explains a lot

just trying to nail down why this server is getting hammered all to hell with MySQL with so few users on the site.

contact your system administrator to track down the issue

is it possible that MySQL is creating that load on it's own, without queries?
(and then applying traffic only exacerbates the issue?)

not really

so it probbably is a bad mod somewhere
k - thx

hi all. i have mysql 5.0.27 running on a hosted FC5 web server. for some reasons when i try to access a DB using php/apache i am always coming in as "apache@localhost". i have defined a username and password in my script, but somewhere this is being ignored. does someone have a hint maybe where
i have to search?

what script is it?

some test script i have written
it reads something like $db_server = "localhost", $db_name = "myname" and so on.
mysql log always shows "Connect apache@localhost as anonymous on
128 Init DB Access denied for user ''@'localhost' to dat
abase "
i wrote this script because some program i want to use there reported the same problem. tried to find out if its a problem of the program or the installation. seems to be the installation.

''@'localhost' is anonymous user
means the user you used does not exist on the mysql server, so it is map to the anonymous user

Hey
Can't create table './lawleypharm_com_au/Payment.frm' (errno: 150)
sql is at http://pastebin.com/m539a999d
I can't see the problem, that table looks identical to others that work

the user i have defined _does_ exist. connect using myphpadmin is working using that user.

I just swapped Payment and ItemOrder around, and now it can't create ItemOrder. But I can't see the problem with Orders that is causing this

!perror 150

Foreign key constraint is incorrectly formed

FOREIGN KEY (orderID) REFERENCES Orders (orderID),
It's that one, from troubleshooting
Is Orders a reserved word? I thought only Order is?
Oh, whatever the foreign key is MUST be a primary key in its own table

not necessary for innodb
it needs an index at least on the orders table

Ah ok. Well, all the orders table had was a foreign key to somewhere else. No index or primary key
The orderID is meant to be the primary key of that table anyway, I just forgot to write that line.

I have some rows that has the value "log_foo" in a table column. i want to select all rows that starts with "log_" with a SELECT-statement. Anyone knows what would be the correct SQL-query for this?
SELECT rp_id, rp_querystring FROM rp_report WHERE rp_querystring REGEXP "^log_$" LIMIT 0, 10 but it does not return any rows!

MySQL bot join okay ?
english bad
mine english bad
MySQL bot
for IRC in C++ !google
!msn !askjeeves

Blush, an unexpected wench error, manual section !msn not found

This would only get rows where rp_querystring starts with "log_" right? SELECT rp_id, rp_querystring FROM rp_report WHERE rp_querystring REGEXP '^\log_'

why the \
is _ a special char?

Why not just WHERE rp_querystring LIKE "log\_%" ?
Besides everything LIKE will be able to use index for prefix search. REGEXP at the other hand can never use index

i'd like to run mysql with limits on memory, eg. softlimit -m 3000000000 /usr/sbin/mysqld-beton
but it does not want to start with such a limit
i am sure my database is smaller
is mysql checking or reserving the whole memory?

thats 3 GB right?

What is /usr/sbin/mysqld-beton ?

sorry it is link to mysqld
soft link
yeah, it is 3GB

and softlimit is?

3G

No I am asking what this program is

database witch all caches is under 1G
daemontools by bernstein

Why do you think mysqld knows about that tool?

everything works with such a limiter but mysqld not

Why do you think mysqld knows about softlimit?

http://cr.yp.to/daemontools/softlimit.html

Why do you think mysqld knows about softlimit?

i cannot understand your question

What makes you think you can run mysqld this way?

because wverything else works, so why not mysql?

Why should it?

why limiting resources is impossible?

I've got a database of ~400GB which is performing so badly it's causing problems.
Looking at the datafiles, they are all ~1.7GB except for the last one which must be an autoextend because it's ~50GB
this means it must go trawling through 50GB of data to pull out info for stuff held in there and must be why it's performing so badly?
how do I redistribute the datafile to 1.7GB ones?

more like poor indexes

do I have to export and import or is there a better way?

use explain find the real slowness

Find the slow queries and apply EXPLAIN to them

You are asking completely unrelated questions you know
mysqld is not reserving the whole memory, but it needs to allocate and deallocate memory all the time depending on load

Problem is the db has lots and lots of writes, and is part of a a software package called dbmail which has decided what should and should not be indexed
because it has constant writes, some parts I think are not indexed because it would slow the server down a lot.
by having to maintain the index

are there any methods to soft/hard limit mysqld resources?

You can always add indexes later.

I'm not sure adding indexes will solve the problem though, it's a trade off between writes and reads and it does a lot of writes constantly inserting records
so indexes may be more of a hindrance

Not that I know of.

for such a huge data set, index usually good, since the update overhead is not that big as compared to the read

it doesn't seem like a coincidence that the server started getting slower and slower and slower recently and the latest datafile is huge

Correct with one exception. For InnoDB tables you better always have Primary Key

btw.. as promised I spent a bit of extra time trying to resolve my issue.. Bug #27230 stack smashing attack in function int mysql_prepare_table at some point I was debugging another app and accidentally left my cflags default.. (which shouldn't be an issue.) After putting them back to
"CFLAGS="-O2 -march=prescott -msse3 -fomit-frame-pointer -pipe -mfpmath=sse"" my issue gone.. I have a guess as to what happened, but haven't dug further

a good and small primary
key

““`
wquit
console
yes
exit
eixt
`cui
`restart

..

start

stop it

`quit

/quit

It could be you suffer from some fragmentation. To your question no there is no other way to reorganize ibdata files than dump and restore

Im trying to write a simple forum system in that is structured forums-topics-posts Im trying to figure out a query that would select all of the forums joined with their newest post
any pointers on doing this? trying to do it in 1 query statement so far has been complicated for me =X

anyone speak for wether or not mysql works on ec2? what am i going ot be missing? i heard clustering was very hard?

What is ec2 ?

amazon ec2

What it is? Kind of beer or?

computing grid service
(for beer)

You better ask Amazon then

Hi, would saving thousands of mp3 files in mysql be a very bad idea?

definitely
save the path only

yeah thought it might be
thanks

yw

At the other hand having several thousands files in same directory can be also problematic

Yeah, I would sort them in sub dirs
I need to proces a lot of voicemail files, using RT, it saves the files to Mysql…
will work arround that

i exported data from a 4.1.20 server using mysqldump 5.0.38. then i imported it to a 5.0.42 server. the data imported is corrupted (different md5 sums for column contents). how could it happen?

storing in different charsets?

hi all
i'm using latin1 character set, and i'd like to change it to utf8, but i can't find it on my installation. can i download it or get it somehow?

download what?

hey, im stuck using mysql 3.23.x, and when i enter stuff like £ into a test field, and then attempt to echo it out in php later on, i just get a little square… is this a php or mysql problem? and does anyone know how to solve it (i cannot upgrade any of the software)

hello
I need to make a script which goes through a table and edit a particular nid
anyone has any ideas?

different character sets?

both tables are marked as latin1

what do you mean by different md5 checksums for COLUMN CONTENT ?

john_axai, and where is the problem?

md5(column) is different. (so is length(column))

what data type?
char?

mediumtext

I have a Mysql database containing products
name, price, description, etc..
now, we want to reset the price of all the items to $0.00
(this is for a small test project by the way)

john_axai, and where _IS_THE_PROBLEM_?

ah right, I want to write a script which goes through the table and edits the price for each item in the table
I could do it manually, but it would take forever

but what is the goddamn problem?
or just telling us the story of your day?

no, no

Is there a scriptable command line client for MySQL?

brb phone

Nevermind, LuaSQL will do it now that I think about it

i have a MySQL table with about 165 items listed in it
the table contains different nids (such as name, decription, price, etc…)
now, I need to reset the price for all the items in the table to 0

can you use the dumpfile to find the contents that differ in clear text?
update table set price = 0; ?

john_axai, you already said that a couple of times.

well, now i'm redoing the dump with –default-character-set=latin1, which was suggested to make mysqldump not do some odd conversions on the data
will see how that goes (it'll take a few hours)

but still didnt say what your problem is
you already said a thousand times what you have to do, but didnt report your progress

sounds strange, MD5 should be the same output with same input no matter what version

sups

yo

how do i set the charset to unicode (utf-8) in mysql 3.23.x?

You can't
utf-8 is supported from 4.1 and above

i hate my boss
complaining that stuff doesnt look right in non-english languages

Change him

but refuses to upgrade mysql

3.23 is dead for years

my point was "mysql 3.23 is older than i am…. 4.0 is possibly also"

Last 3.23 release is 11 September 2003

i know this, but all i ever get is "we cant upgrade because we cant afford for it to break"

I doubt you are 8 years old

it was a figure of speach… foo…

hi all,
how to add first row as a field names in generating csv file
SELECT * INTO OUTFILE "c:/mydata.csv"
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'

im gonna go get some food, and leave you all with this guy, as if i try and deal… it will turn into a massive flame
bbl

how to add fields name in csv file

hehe. salle, you causing problems again?

bizzeh, store it in blobs

What else can I do?

:-)

how to make a DB from CLI

mysql -e "create database if not exists newdbname"
Provide an appropriate user/pass if necessary.