someone that knows about epoch?

http://pasteosaurus.com/264 thanks

jabbadoo_, do you mean in datetime values?
Alex_Gaynor, any particular reason to use bigint(20) for user_id and other primary keys. do you expect this database to outgrow a regular integer?

Hrm, probably not, although I won’t swear to it, can I just up it later if I need it?

Hello community .. I need help concerning an encoding issue

gnari, yes

how can i set a field to 2 digits instead of 1?
e.g: 01 instead of 1, 02 instead of 2….

zerofill

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

I have a website written in PHP, old database’s charset was set to latin1, data were in Arabic and I used windows-1256 in the meta tag for the page encoding .. But I want to convert the DB from latin1 to utf-8 . I tried so converting them but results on page were like ?? ???? ??? ????? … I
changed the encoding of the page to utf-8 but still the same ugly results! I created another database with utf-8 and reinserted data using phpmyadmin .. but still the
same problem .. any tip or help will be appreciated

jabbadoo_, what do you want to do?

Okay. I need some help. How do I query mysql to sort by a certain field descending?

[...] order by fieldname desc

Like, “SELECT * FROM `table` ORDER BY `field` DESC?
Okay.

yes.

Thanks, pizza_biz
pizza_biz++

gnari, transform a float into date/varchar and transform a date/varchar into float from epoch

hello when creating a table is there anyway to set the default to the maximum int of a column?

why not use AUTO_INCREMENT?

its not a pk

I have a website written in PHP, old database’s charset was set to latin1, data were in Arabic and I used windows-1256 in the meta tag for the page encoding .. But I want to convert the DB from latin1 to utf-8 . I tried so converting them but results on page were like ?? ???? ??? ????? … I
changed the encoding of the page to utf-8 but still the same ugly results! I created another database with utf-8 and reinserted data using phpmyadmin .. but still th
e same problem .. any tip or help will be appreciated

are you using a new mysql?

jabbadoo_, you would probably use unix_timestamp() and from_unixtime(), although i do not know about the fractional part

hmm, svn.mysql.com seems to be dead

Hello! Any help people?

charset

http://mysqldump.azundris.com/archives/60-Handling-character-sets.html

read that url

thanks

gnari, that’s exactly what i needed, thx a lot!

is there no column setting where i can set the amount of zeros?

zerofill

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

in a php mysql web hosting statement how can i seperate 2 conditions? like ( name = uname and name 2 = uname2 and gid = gid ) OR ( name = uname2 and name 2 = uname and gid = gid) ?

just like that

thanks

Bear10, in this particular case the parentheses were not needed, though

how do I give a column of a table a default value?

add a default value in the column definition.

Just remember default values cannot be functions
(the output of a function)

i just wanna set it to 0

DEFAULT 0

does the table exist already? you’ll need to ALTER TABLE if you want to add a default.

na, im creating the table
writing the CREATE TABLE thing

[...] foo INT DEFAULT 0, [...]

Exactly

does mysql care if I use integer instead of int

i think they’re aliases.

k

the manual covers the various datatypes.

is there a way to use a wildcard to drop multiple tables w/out dropping the entire db, short of a script?

prefix drop

http://archivist.info/search/index.php/Prefix_drop_table

drop all tables

There is no wildcard support for the DROP TABLE syntax. You can use MySQL to generate the list of tables to drop; see http://thenoyes.com/littlenoise/?p=44

ty
i think this will work just fine
it was about what what i would have written, but saves me the time

hey, quick question…I’m running a massive update on user data in a database…I want to back up stuff in case things go wrong..is there an easy way to use phpmyadmin to back up current db?

pMA has an Export tab.
Some people have reported some issues with the backup it generates, depending on the version.

thx
yeah
that’s my concern
right now, I’ve used it for that database

after you order by a field, how do you figure out the position of a row?

although, I don’t get the “file name template” stuff

It just means that you can use __TABLE__ and the file will be called nameOfYourTable.sql

oh, ok
Thx much!

dam
i’m doing something like ’select id from my table order by score where uid=5′ , now i want to figure how how far down the list, score is…. how?

It’s easiest to handle it in your application. You can use a user variable, e.g. SET @row := 0; SELECT id, @row := @row + 1 AS position FROM…

ok thanks snoyes
in command this works good, but doesn’t seem to work calling as mysql query all in one string
SET @row := 0; SELECT @row:= @row+1 AS position FROM know_summary WHERE uid=8824877 ORDER BY score

mysql_query doesn’t allow query stacking. Make two separate calls to mysql_query, or use mysqli_multi_query

ok thanks snoyes
your knowledge amazesme

Error dropping database (can’t rmdir ‘./social/’, errno: 17) mean? I get it when trying to drop a database

!perror 17

File exists

What do I do about it?

drop the database, then the file won’t exist.

I get that error when I try to drop the DB

You get that error when the directory can’t be removed. Perhaps the user as which mysql is running doesn’t have permissions.
Or perhaps there is some other file that’s been stuck in there that doesn’t belong.

Ok snoyes you type faster.

a) Is it possible to set up multi-master, multi-slave replication (ie, A-BC, B-AC, C-AB)? b) Can anyone point me to a good resource about said configuration?

Not like that, but you can do A-B-C-A

rmdir won’t run if there is a file in the directory.

No slave may serve to masters.

Its empty, I found it on my HDD, can I just delete the folder manually?

I assume it’s in the mysql hosting data dir?

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

Yeah, I’m on OSX, and its in MAMP-db-mysql

you might stop the server first, the do ‘rmdir -rf social’
you might stop the mysql server first, the do ‘rmdir -rf social’
using the full path to the dir you want to remove

Yeah, I have a little CLI experience
Yay, its gone

im having some trouble setting the mysql root password
i was following the steps of a tutorial

gotta be careful advising the use of ‘-rf’ ;^)

haha, nice snowman

sorry, I just see text.

mysqladmin -u root password ‘foobar’

I don’t know what you did but after ‘-rf’ I see a snowman :/

but i mistakenly typed mysqladmin -u root -password ‘foobar’

; ^ )

and then tried again the original way, but was told access denied
mysqladmin -u root -p
it lets me in
mysqladmin -u root password ‘foobar’
error: ‘Access denied for user ‘root’@'localhost’ (using password: NO)’

either -p or –password

Any oppinions on table types for this: http://pasteosaurus.com/266 I’m thinking MyIASM for all besides the _mod ones which will be InnoDB

but i mean it appears i have no password set, because it doesn’t prompt me for one when i use -p/–password
but if i try to change it, it tells me “access denied”

innodb with logging is safest for disaster recovery. it can be slower on selects than myisam. big players use innodb masters, replicating to myisam slaves, and let the slaves handle the selects from the public.

much appreciated

I’m not that big, I figured the tables that would be mostly writes are the _mod ones, which keep track of user moderation of content ala digg/pligg

if you don’t have to worry about superhigh performance, you’re better off with innodb.

Well, for now its not super important, but presumabely if it ever gets really big the performance might be worth it right?

the difference can be important, but we’re talking eBay size.

Well, I don’t rationally expect to get that big, but I don’t want to plan for failure after all, howe difficult would it be to switch down the road, if I need the performance and have exuasted all other options?

a simple alter table

Oh, you can switch on the fly, i never realized that
So InnoDB on all, now I need to figure out the indexes

far more important for performance, really.

These indexes look ok: http://pasteosaurus.com/267

you won’t know till you match them up with the sql hosting queries and see. remember to use ‘explain’ before your queries and that will tell you what indexes are being used. Indexes are easy to add/drop.

I guess its time to go make the tables, and start coding the app then!

enjoy!

Yep, I’ll be back in a few hours with 100000001 questions
What type should I make my indexes?

trying to migrate an existing mysql install from server A to server B which has an existing install/users, mysqldump will not dump information_schema, any suggestions on how to accomplish?

is there an easy way to divide a TIME field by half?, I would like to hear some tip rather than converting the TIME to hour, second, minute, dividing by 2 and reformat

the default is fine

lo all

So 257 questions?

Has anyone tried to import a compressed SQL file into mysql?
I was trying to do so with a download of wikipedia’s link categories, and even though i ran it overnight – it just didn’t finish!

Haha, sure, I just took my Comp Engineering(ECET-100) Final today

(Size of the file’s about 1GiB)
Is there an efficient way of doing this rather than mysql -u user -p dbname file.sql?

If it is compressed you couldn’t do that anyway.

mhillyer, ah yes, i realized i couldnt so i had to gunzip it. or use bzcat on the sql.bz2

You could try SOURCE file.sql from within the client, but it should be the same. You might bypass some packet limits.

Crud, the description column I supposed to be 400 chars max, thats too long for a CHAR, should I use text instead?

hey, I’m seeing the message [Warning] Could not increase number of max_open_files to more than 65535 (request: 81930) .. then shortly after the server stops taking new connections.. is there a way to fix this?

man ulimit
but you’d better review your system, something must be fishy there

how so?

mhillyer, hmm, I did tweak my my.cnf file to something like this – http://rafb.net/p/s2giPa78.html – but that doesn’t seemed to have helped

one of the (more poorly written) programs that uses it constantly opens and closes connections.. but it should never hit more then 300 concurrent connections (bad, I know.. we are in the process of replacing it)

perhaps there’s something which i’m overlooking then?

Did you try the SOURCE approach?

mhillyer, well, i haven’t yet – i’m just cleaning up the messed up tables right now..

Use the source…

yep, started it now
hmm, so what exactly does this mean?
Query OK, 13716 rows affected, 2 warnings (0.92 sec)
13716 Duplicates: 0 Warnings: 2

open/closing is not a prob, jus tmake sure you have a thread_cache on the server side

it doesn’t seem to be increasing, OR decreasing in number
or is it that each query modifies those many rows?

hey, whats the default value for wait_timeout

do SHOW WARNINGS directly after that

its kind of a problem when it opens a bucnh, then crashes.. so they arent closed cleanly
or at least, thats what I think the problem is

- so it can’t be interrupted at the moment.

I doubt. if a process crashes, connections would be closed.

oor is there a way to send a mysql job to the bg?

righty. well the above would be the result of one insert or load data line. the warnings will be like data truncation, out of range integers, etc.
ctrl-z then bg
standard unix

hey!

ah i see!

the ctrl-z briefly suspends it, the bg will make it a background process. better to use screen though, but you cant do that with hindsight
aloha

arjenAU, yeah, its on my local machine, so i guess i’m ok
so, does anyone have a clue about how long it might take a 1.1G sql host file to get imported into the db?

10 minutes.

given a dual core 2.2gig intel with 2G of ram

depends entirely on a lot of things. “a while” might be the right answer

right.

But that assumes you have a badass RAID backend.

chances are your server settings aren’t optimal for such an operation…

Is this a legitimate query SELECT sum(Value) FROM table WHERE comment_id=7, and this will return the value of all the entries in table where comment_id is 7 from the column value?

mhillyer, hehe, think “laptop”

I can go with that

arjenAU, indeed. i did tweak my my.cnf file a bit based on som forum postings at mysql..

Come to Canada arjenAU, there’s a great Belgian chocolatier here.
They do factory tours.

we have some here but yes I’m aware. an been there. well, as far as Vancouver at least.

mhillyer, which part of Canada?

did a Cadbury factory tour in Dunedin, New Zealand. that was good

Southern Alberta. The guy is in Calgary.
Bernard Callebaut – http://www.bernardcallebaut.com/
Noo doot aboot it eh!

anybody else have issues with skype right now?

i saw something about that on isc.sans.org today, i thought, in the most recent handler diary there

yeah, couldn’t log in myself

I do arjenAU.
Reports say it’s down for about a day.

well has been for about half a day for me, yes.
that’s nasty.

Yes indeed.

apparently it is an internal software issue with Skype ( http://heartbeat.skype.com/2007/08/problems_with_skype_login.html
)

arjenAU, still around?

is there a way to get the actual create statement for a table from a query?

somewhat

hey again. your idea of using source worked, thanks – the system seems to have imported everything well! but, doing a select * from table limit 1; hasn’t exectued even after 5 minutes!

SHOW CREATE TABLE;
tada
SHOW CREATE TABLE foo; to be proper

thanks

hmm, so it looks like this query is taking eons to execute!
does anyone have recommendations for docs or resources to help increase resources granted to mysql?
i did tweak my.cnf – http://rafb.net/p/s2giPa78.html – not sure if i need to add something else?

hello everybody, i am a newbie with mysql, what is the best way to keep a coherant (relationships) database please ? what features should i look for in the manual please ? where or what google keywords should i look for in order to find example queries to clean a database ?
is it possible to use joins in a delete query ? not as far as i understand (probably not very far) the manual for DELETE
i have a class of objects (15k of them) data stored in 17 tables of the database, note that a few of these tables must keep MyIsam engine because of fulltext index … Thank you in advance for any tip, even the shortest of them

how would i go about enabling all privileges for a current account?

can’t
you need to alredy have them to enabled them

well i can login as root shouldn’t i be able ot change their privileges for other accounts from there?

that would work
it’s the GRANT query type you want

ahh okay than thanks

!man grant

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

thank you

have a schema question regarding storing a “large” (500,000+ records) that will be sorted
if I’m storing each record as a unix epoch time, indexing that column, probably isn’t a good idea, right?

only if there are lots of records at the same second

which there aren’t
so, should I then have “two” columns for time?

hmm. this is weird, i’m sure my cnf file has all the relevant innodb enable option but phpmyadmin still shows that engine as disabled!

nod, that’s usually quite unlikely with timestamps,

any ideas why this should be?

one for “date” and one for “time”

naw, that’d use up a lot more space, which would make finding records even slower

Xinil, depends on what queries you want to make

how should I be storing/sorting then?
is unix epoch not a good idea and I should be using datetime?

does anone else find myphpadmin buggy as hell?

Xinil, will you be sorting by this column?

yes
often

keeps logging me out

it’s very buggy

how do I see the queries coming into my mysqld?

like the actual php part of is n’t too good
it keeps logging me out and refusing to work with the correct pass and such

Xinil, will you be querying on this column, like between x and y ?

sometimes
like, count(*) where dayyesterday and daynow

Xinil, if you are going to make a lot of aggregates based on date, you may be better off with separated day and time

hm

Xinil, but i see no real problem on just indexing your epoch column, either

well

how would i go about checking my privileges for a user? It seems my root user has been restricted for some reason?

500,000+ records, that index is going to be massive, won’t it?
because each record is going to be unique
cardinality will be 500,000+

Xinil, on the other hand, the index’s selectivity will be grand
Xinil, 500000 records is not much for an index, i would not be worrying about that

hmm
well, the growth is quick, so it’ll be reaching 1mil in under a month from now

Xinil, i think it is mostly a matter of how your queries will be. if most queries return relatively few rows, then a indexless sort is not a great pain
Xinil, if most queries are by date or a rage of whole days, a day index is more efficient

well, i’ll be using an indexed where statement

Xinil, but i have divided such a column in the past into a day column and a time column, and regretted it when i had to do queries with timexone adjustments

where mem_id=8 AND timethen AND time now

hey guys is it a bad idea for me to edit the mysql for my permissions manually using a gui like cocoa mysql?
i’m just changing the permissions from N to Y then i just figured i’d reload it and it’d be fine?

thanks for the help gnari. I’ll stick with unix epoch for now, it won’t be too hard to change in the event it turn sugly

Xinil, a good rune is not tooptimize prematurely. keep your schema clean, until you know you have performance problems
Xinil, should be easy for you to test it. just populate a similar table with 5 000 000 rows and see how performance is

unfortunately, i often optimize too little, so I’ve recently begun to delve a lot more into indexing and watching mysql take less and less resources
good plan

Xinil, well indexing is not optimizing in my book. that is just tuning, one should constantly do that

haha hey how do i log in using console from 127.0.0.1 instead of localhost?

Xinil, optimizing is making changes to your table schema or modifying your queries

this is going to sound really weird but i somehow don’t have permissions on localhost but i do on 127.0.0.1

anathematic, have you tried just connecting to 127.0.0.1 ?

how do you mean sorry?

anathematic, what is your connection string?

i’m trying to find out which position someone is based on how high their score is… anyone know how to do this?

well i’d been trying to connect using mysql -u root -p
however i don’t know wh at parametres i need to connect to a certain address?

snoop-, count how many have higher score

i guess i need to find the variable page

anathematic, -h 127.0.0.1

it woudln’t let me connect : “ERROR 2003 (HY000): Can’t connect to MySQL server on ‘127.0.0.1′ (111)”

‘t
rewrite /server-status
whoops
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
1 mysqld

i am making a website users table and I have a selection list of 100 checkboxes, which may grow, how should I store that? a field for each or do it bitwise with a double or something?

I didn’t know a process can use more than 100% of a processor, hah

snoop-, what about select count(*) from foo where score(select score from foo where name=’x')

is it legal for a foreign key to reference it’s own table?

yes

foo, dual/quad-core

skull–: no, store them in a separate table

Is that a question?

foo, no, its a statement

skull–: checkboxid, state

a separate table with 300 fields?

To me?

delete from table_foo where id_foo not in (select id_foo from table_bar) ? mysql reports an error at occurence of where word

gah. ok, can someone tell me if i should be facing a ridiculously long time in executing a “select * from blah limit 1″ query on a table with about 16,000,000 records?

checkboxid, state, userid right?

I didn’t know a process can use more than 100% of a processor, hah

skull–: yes

ohhh, err, yeah, I kind of thought that.
Thanks
Now to figure out why it is

foo
vIkSiT, select * is stupid, and yes, it will take ages

tibyke, so assuming i wanted to return just one row of the table – what would you suggest?
oh i see – a select * would select all records, and then return just 1?

no, select * return all columns.

vIkSiT, * is horizontal, fields.

and the “all records” probably takes the long time. hmm.

wrong.

vIkSiT, 16.000.000 and limit and order by is the slow part

hmm i see.
so how would you recommend i work with this table?
assuming i wanted to see the first record of the table?

index the field your order by

For some reason a query from the replication thread (this is a master/slave setup) is what appears to be locking the database… so other queries get stacked up, load spikes… any ideas? I can’t figure it out why this is happening all of a sudden
uFor some reason a query from the replication thread (this is a master/slave setup) is what appears to be locking the database… so other queries get stacked up, load spikes… any ideas? I can’t figure it out why this is happening all of a sudden/u

thumbs, sorry, not sure i followed that

foo, show slave status;

index the field/column you use to order your query.

i could do something of the sort “select col1 from foo order by bar” – but how does that help?

Nothing weird there, other than seconds_behind_master gets 10-500 seconds behind master when this happens

then index the `bar’ column vIkSiT
`bar`, too

foo, and what happens then? whats the error msg?

Nothing weird there other than seconds behind master … eg. there is no error message

an index wont help too much with 16M records and a limit/order by, im afraid
foo, and you wait, and what happens later? it “comes” back?

Usually, yes. But right now it’s constantly locking up the database for some reason. A that’s causing processor usage to be 100%
This has been running fine for months… don’t know why this would happen all of a sudden. Database on this slave is 8.5GB

show processlist;?

What am I looking for there? I just see 90 queries, and one insert from the replication thread… all other threads are in state LOCKED

so you got it

I’ve already said/noticed that.. my question is, why are they locked.
This has been solid for months, and all of a sudden this
No reason for them to get locked up. And I checked all tables with check table … extended; 10 hours ago, everything was fine

other insert/update is locking the table

and probably still is, so I doubt it’s table corruption
Right, so it’s taking longer than normal, I wonder why it’d be taking longer than normal

check that very query
my crystal ball is broken

shoot, it is?
hmm
insert into newtable values(LAST_INSERT_ID(), ‘Commodities and banks bear brunt of market slide’, ‘The FTSE 100\’s 4 per cent tumble was the biggest daily decline since March 2003, as intensified credit market fears fuelled a sell-off that hit banks and commodities hardest.’, NULL, 108, 669,
‘3c966145e5a3f10a162dd1886bdea151′, 1187306242, ‘http://business.scotsman.com/markets.cfm?id=1300082007‘, ‘Commodities_and_banks_bear_brunt_of_market_slide’, ”)
the State on that query is LOCKED

how do i reset the permissions for my root account?
i seemed to had slipped and changed them so i can’t do anything =\

Can LAST_INSERT_ID cause the lock up? I’m not the programmer for this project

reset root

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

foo, yeah, that LAST_INSERT_ID() is pretty strange there

Is it possible that that could cause the lockup? I’ll talk to the programmer about this… but, I’m not sure

foo, dunno, but give it a try

thumbs, could you perhaps point me to a resource on table indexing?

thanks

!man indexing

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

!man index

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

i was just looking online, but i’m not sure if there’s a comprehensive resource which explains what it means, and not just hwo to do it..
thumbs, thanks, will take a look

nah. doubt it. It’s locking up on an update as well.

do you have any locking statements ahywhere in your queries?

Doubt it, but let me confirm. I don’t see it in this processlist at least
Yes, we do. Wouldn’t I see them in show full processlist;, though, if that was causing the locking?

well locking the WHOLE table, or a ROW, could have disastrous consequences on the performance of your system
for instance
if your update takes ~3 seconds, you just delayed every other query that needed to access that table by that much time.

does visual sql creator exist for mysql, like in access db?

and if the other queries want to do locking too, your query queue just got astronomically larger.
try phpmysqladmin or something

is it really that bad to put underscores in table names?

skull–: it’s allowed

but frowned upon?

skull–: to be safe, always use backsticks when referencing a table name
skull–: it’s allowed.

i want to join 3 tables in select statement , i don’t think phpmyadmin allow to create such queries visualy, i found Visual SQL-Designer in google, maybe this is it
i heard you can put one select in another
i wonder what should i use join, select in select or union
i think i said something lame

if you have 3 tables and want to use them all in the same query, you can use joins to combine the results.
figuring out what join to perform, and on which columns, requires design skills.

thumbs, truth!
fulketerraun, selets in another is called a subquery

hey hey

hi

anyone know if there is a php function to check if a key already exists for a table?

is there a way to drop all tables at once?

that question would be better suited for ##php
no. Drop them one at a time

you can in phpmyadmin i guess

heh
yeah

using phpmyadmin is not recommended, however

you can’t truncate database?

script it.

!man grant

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

alright, thanks

thumbs why is not recommended?

because you start asking lame questions about sql syntax ?

In the beginning was the CLI…

concerning phpmyadmin

what will happen if suddenly I get lots of queries that my server can’t handle them?

it’ll queue them

will queries be slower?, crash?, something?

they will line up and take their turn.

nice

for one, it promotes lazyness, and misdesigned queries

without jokes, why phpmyadmin is not recomended?

it’s especially bad when used to import/export data

what should i do if my root@localhost permissions have been changed so it can’t do anything?

it’s known to mangle data

it’s an attempt to guess what you mean and create a result. It makes errors. And it keeps you from learning the right way to do things.

you could reinstall the user tables (rename, and so on)

anathematic stop mysql, start mysql skipping grant tables, reset password

!tell anathematic about reset root

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

i see

i tried that but it didn’t seem to help
the permissions for root@127.0.0.1 are fine but @localhost they are restricted
but it won’t let me log into mysql through 127.0.0.1 =\

anathematic, well, then reset it
are you on unix?

yeah unix
*ubuntu

anathematic, do you know how to stop the server and start it?

yeah but it doesn’t seem to be resetting permissions when i’m starting it using “–skip-grant-tables –user=root”

anathematic, it’s not automatic, you have to reset the password manually

it’s resetting the password but not the permissions
yeah i set the password again
however it’s not reseting the permissions with that command
: i need to reset the permissionf ro root@localhost to all again

hello again
how do I check if a KEY exists on a table using SQL?

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

do you mean check for an index? ’show create table tablename;’

Failed: ALTER TABLE {users} ADD KEY created (created)
because it already exists

http://pastie.caboo.se/88469 it
oops sorry, yeah chuy_max it’s not interested in giving me any permissions to do t hat

you can also query the info schema. http://dev.mysql.com/doc/refman/5.0/en/columns-table.html

so i need to check somehow if the key “created” already exists
key = index?

you’re inventing syntax. read the manual.

sorry the sql is being processed by Drupal so i guess it must do a regex and change KEY to INDEX

I thought Dru Paul was a drag queen?

how is ADD KEY not mysql syntax?

there is create index

http://dev.mysql.com/doc/refman/5.0/en/alter-table.html

for alter table, add key is there, same as add index

ADD {INDEX|KEY} [index_name] [index_type] (index_col_name,…)

but has to use inside alter table

right, but i was told that i was inventing syntax and that i should read the manual

there is no ADD KEY without ALTER TABLE, there is CREATE INDEX for that

thanks kimseong, i had posted the following:
ALTER TABLE {users} ADD KEY created (created)
which is failing because the key already exists

the name `created` already exist or the col created already indexed ?
what is the exact error message

so i was wondering how to check if the key already exists so that i can incorporate that into my code before the ALTER TABLE is run

show create table , show index

ALTER TABLE users ADD KEY created (created)

even information_schema
so the problem is the index name, ignore the name and it will be fine
ALTER TABLE users ADD KEY (created)
the name is optional

gotcha
but won’t that create an extra index, i.e., created2?

!man adduser

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

!man add

see http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html

haha~ mmm functions for adding new users?

show create table , show index are these SQL commands or mysqladmin commands?
nevermind got it

they are sql commands, but you may have trouble using their results in other queries. Look to querying the information_schema db.

not sure if my code will have access to that DB
if it’s a separate DB

there is also the issue of mysql version
http://dev.mysql.com/doc/refman/5.0/en/information-schema.html

it looks like SHOW INDEX FROM users will return rows and i’ll just need to loop through the rows and check if Key_name ‘created’ exists

“It is possible to select INFORMATION_SCHEMA as the default database with a USE statement, but it is possible only to read the contents of tables. You cannot insert into them, update them, or delete from them.”
do the looping from your application code, I believe.
?

right, in php

there is a bias here against using app code, but I don’t share it.

information_schema doesn’t exist in my db anyways
i’m all for doing it within mysql if there’s a single query i can run, but it doesn’t appear to be such a thing

IF EXISTS (SHOW INDEX FROM users WHERE… )

some of the big guns here could do it with variables and stored procedures, I think. Not me.

thumbs YOU ROCK!

no need for a stored procedure
albeit that is bordering on TSQL code
anyway, the principle is easy
if value exists within the resultset, then execute query

hmm

or in your case, do IF NO EXISTS
NOT

yup gotcha

can show be used as subquery?

so start the query with a case?

I thought not

thats what i thought too

it might not. I would try to find a suitable SELECT query to replace it, however.

I think that is where info_schema comes in, if your version supports it.

yes, it definitely cmoes in handy at that point

SHOW INDEX FROM users WHERE Key_name = ‘created’
and if it returns a row i know it exists

!man grant

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

you could.

that’s manageable!

I concur

thanks for all your help!

de nada

http://img136.imageshack.us/img136/5113/52757166gb2.jpg how to ger row that contain: colorSetName, colorName, taste?

thanks wench

It’s been a pleasure serving you, threnody.

*get

I love that

where ?

!man join

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

read that
more specifically, you need an INNER JOIN

0
you means me
but not in thank you

de nada

what port does mysql run through by default? (how can i find out what port mine is on?)

/bin/sh: grp: command not found

3306

so im setting up mysql cluster, and apparently it hates me

I like boobs, and your mom has aids

nice one

lol

so i’ve got 9 linux computers, and i setup mysql storage nodes on 8 of them, but they all get stuck initializing at phase 1..

that’s because you’re using 8 386’s at 4mhz

haha
no
8 p4’s
well, 9 p4’s
though, they only have 256mb ram each

perfect candidates for vm servers

the resources online made it *seem* like everything would be pretty straightforward — and it was
cept, using ndb_mgm shows that the nodes connect, and are stuck at phase 1

perhaps try the mailing list

i take it nobody here has messed with it?

i’ve started using backticks since I joined this channel 2 hours ago, I edited the checkbox table though and took off the state, if there’s a value then it’s true otherwise false, is that a bad idea? (to recap: table was checkboxid, state, userid, now checkboxid, userid)
the state was BIT type and was coming out as 01 and messing with me, I didn’t see a point

silly non-working documentation
actually, its probably just something silly i missed
tnx, im out

what variable controls how long processes can sleep before being disconnected?

wait_timeout?

show variables like ‘%timeout%’;

I was just going to say thta

there are 2 type of timeout for connection

wasnt sure if it was wait_timeout or interactive_timeout

what you tell your gfriend when she’s angry and with aunt flo
sorry

I don’t think my wife will let me have a girlfriend but I will ask

don’t ask that

then you’ll need a wait_timeout

let her bring it up

when i switch from myisam to innodb.. do i need to adjust my mysqldump lines for that? or complete nothing has to be added/changed there?
i regulary backup some tables and i wonder if it will be fine with innodb still
completely*

should not need to

thank you scooby2
just making sure as i heard that innodb is kind of scary

it can be

data corruption etc.
still not sure if i should switch from myisam at all

thats not too bad
the double write is what is killer but row locking is worth it if you need it.

and is it possible to go back to myisam from innodb?

sure is

hm so nothing that i can’t change back, sounds good
row locking.. what exactly do i gain with that?
i’m mostly looking for performance boost

not sure you will get that with innodb
depending on what you are doing
it might be slower

but can it also be faster?
i’m using some specific software that utilizes mysql heavily

when you write to a table with myisam it locks the whole table, does the insert, then unlocks it. thus only one write at a time.

and i wonder if it can be optimized for innodb rather than myisam – does it make sense?
hm.
oh wait, does locking a really BIG table take time?
i mean really big one
with like 140+ columns and 2 rows
2k rows

yeah. i use innodb on our 192gb events table because it does well with lots of inserts

and that table is being updated constantly, 10s of times per second often
damn
so the locking stuff could be evil

i think we went over a billion rows yesterday

damn damn
a billion?
192GB db you mean?

yeah that db is just over a billion rows

congratulations

grows at 4-5 million per day

i could make coffee for everyone 10 times a day just to be able to touch that db lol

i need to break it up but the report people dont want me to break it up by year

i never heard of a bigger mysql db yet
he he
they are always like that lol

yahoo has much bigger i hear

so basically you recommend innodb for any database with big tables that are being frequently being written to
as that takes locking the whole table with myisam each time it happens
and it’s diff with innodb

yeah sounds like its worth a try

btw does yahoo really use mysql?
i thought that such big databases are mostly oracle
glad to hear that mysql does the job for billion rows

i know all their finance.yahoo.com is mysql

http://mysqldba.blogspot.com/2006/11/unorthodox-approach-to-database-design.html

damn nice
hm skull–… i will certainly give it a read – thank you very much
but again, i can imagine a software performing better with innodb
e.g. when there is like hm 1
err
when there is like 1000 updates needed
and when it doesn’t queue them

500gb of mysql data to replicate
damn

scooby2 he he
….so with myisam it would take 1000 locks of the WHOLE damned table

at least $250k in network gear in that diagram i’d think

and that purely depends on the software right? if it tries to update in real time instead of e.g. collecting those 1000 updates, locking once, updating etc.

which might not take long depending on size, memory on the machine, disk

nice skull–

replication is what keeps most sysadmins/dbas sane

i got fired from my networking job last week for telling the receptionist I had my resume on monster, as soon as I walked out the door she called the owner and told him and he called and fired me. Now I got my wish of time to work on my projects

ouch

nah it’s good, i was looking for a reason
I’m using my free time wisely.

going to get unemployment atleast?

oh yeah, applied before 5pm the day i was fired
have to wait a week before it starts though, might have a job before then though, had 2 interviews this week

nice

hello

Hello

i’m not able to read table content
from mysql (console)

is your monitor turned on?

i need to read what i wrote but i dont want to use this information

permissions?

¬¬
i have permissions
im conected
what mor?

charset

http://mysqldump.azundris.com/archives/60-Handling-character-sets.html

how can i read?

Knows what she’s talking about, that Azundris!

So does that wench.

i don’t found the command
to show this information

&?

anyone knows a simple mysql databases viewer, for windows?
I have my .sql db and would like to view it

load it into a mysql server

I can do that locally in my computer, no?

yes

because the .sql was saved in a server with a newer mysql than the one I would like to import the database

how different are the versions?

chaos1 mysqld[17878]: lock: 0×8c913d8: … how can I further troubleshoot this? Or, can I?

the server were I try to import it is MySQL 4.0.25
and the version of the server where it was saved was.. 4.2 I think
MySQL version 4.1.22-standard
I saved the .sql in a server with that version of mysql, 4.1.22-standar
and I would like to import it in a server with mysql 4.0

how do i set all my null fields to 0?
ohwait never mind haha

hello
need a little help! I have a server running a localhost mysql server and another server at 192.168.0.2
the problem is, whatever server I use, the connection is ultra slow
the server load is low
there are just a few processes showing when I type SHOW processlist
what else should I look for?

I am getting a strange icon when I hover the mouse over the table area in MySQL Query Browser. Icon has the word “set” and won’t let me do any of the normal functions like selecting areas of certain tables.

did you click on a SET button on another toolbar?
connection is slow, what server hostname you use to connect? hostname or ip address
try ip address and see if it is faster

kim, ip address
both are slow…
and not a specific query
most queries
I really do not know why

slow on the query or on the connection? i guess on the query

on the query

then you query not optimised of server setting not optimised
s/of/or

the problem is that this happens with any query
even with the simplest you can imagine

I don’t recall clicking on anything. How do I shut this thing off?

but in particular with INSERT and UPDATE queries

select 1;
click the pointer on that tollbar that has a lot of set, where, group by, and so on
check your show status
and looks for hints

Are you referring to the menu bar? I don’t think I know what you are talking about.

not menu bar

What are you referring to?

below the text box
looks like round round things

Which text box are you referring to?

the big one at teh top where you can enter sql

What part of the screen is this in?

below the menu bar, there is a text box, to enter sql statement

I don’t have anything below the box for entering text. I do have a round refresh button to the left of the text box. Is that what you are referring to?

linux?

hey guys, on freebsd, im trying to start the mysqld 5.1 server, and am receiving the following errors, while logged in as root: http://dpaste.com/17086/
how can i allow access to the database files?

I am running MySQL in a Windows environment.

i know linux don’t show that and not sure how to get that
windows, should have

What should windows have?

menu bar at the top
follow by a big text box

What do you want me to do with the menu bar?

then below has a toolbar with many buttons

And what do you want me to do with a big text box?

take a screenshot, post it somewhere

rycole, can you read english text?

screenshot of what. I really have no idea what kimseong is referring to……

let me see if i can find a link

the screen you’re looking at that’s causing you problems

We first need to establish what we are talking about……..

yeah, i asked how to allow access to the database files because the error is obviously telling me it cannot access them. can you read?

that would at least seem to be a place to start

rycole, ls -l, and think

Okee http://www.mysql.com/products/tools/query-browser/main_query_window.png

I don’t have any of the buttons you have provided in your png file. My version is the latest.

i refer to the round button, select from where group and so on

I don’t have those round buttson. Sorry.

try view menu and see if it is disabled

kim, I tried looking at the show data

if you have ’set’ tooltip when you move mouse over the table column, you probably had the SET button clicked

There is nothing in view. I am using Version 1.2.12

the only thing I see that could interest us is that there is a table where many queries are occurring that shows always its status as LOCKED
but that was not the table I was trying my querie on

i don’t have a running version installed, so cannot help much, but I alwasy have that on windows, but don’t see that on linux

Where do I find the SET button?

some query/connection is holding a lock
nevermind, just close and start it again

Well it went away after I closed and reopened it.

kim, would it be a problem if just queries are showing as “LOCKED”? I mean, would it affect other tables as weel
the table has 1000000 rows

there are nice tutorial http://www.mysql.com/products/tools/query-browser/ see the left bar
means someone else has locked or is using the table and this user has to wait

im not sure. i adjusted the permissions of the mysql directory, and no longer get the permission denied error, but still get the remaining errors saying it doesn’t have the ability to issue the ‘create’ command.
am i running mysqld as an incorrect user?
im logged in as root, shouldn’t that work? im new to freebsd.

show grants for current_user();

kimseong, the server isn’t running – i’m trying to get it started

ops, then what does create got to do with starting the server

i think it’s trying to create some initial database file, or something? im not sure. the output looks like this: http://dpaste.com/17087/

rycole, you have the mysql database files?
man mysql_install_db, check the existence of those files

creating the tablespace file
still have the error 13?
!perror 13

Permission denied

that is not the problem

mysql -u user -p -h site.com Why would this not return an error, just idle after I enter the password?

you start it as root, but mysqld usually start with a mysql user of group mysql

I am using BIGINT for a phone number with hyphens between the area code and the number and it is still getting truncated. Any idea what I am doing wrong/?
?

bigint is a number
hyphen is not a valid digits

ok

derekl idle for how long? anything after a few minutes?

Is there a way to put a space between the numbers of an underscore? Or will it accept it?

no
number accepts digits only

yes, i already ran mysql_install_db, i read that on mysql.com docs

then did you see the file and directory permission change ?

so will SELECT DISTINCT return ONE of a value that’s listed several times, or NONE of that value?

How about numbers and spaces between the numbers?

no spaces
space is not a digit

entendu, mysql.com/doc?

yes, i saw that output. it said it created a mysql user, but wasn’t prompted to set a password of any sorts.

no need password, make sure the datadir is owned by mysql os user
chown

Well, i mean I see what it should be doing, but I swear to god it’s axing them rather than selecting just one

compare the result with and without distinct

looks like it’s owned by root.

thats the problem, should be mysql instead of root

mysql group, as well?

yes

cool, appears to be running

really guys, am I going crazy?

This is weird. I am trying to update a field for email and telephone and it won’t return back a result set. I am using the following UPDATE south_databasefinal.vendor
SET Email = ‘mprosser@privaris.com’
WHERE Email = NULL;

is null

I have the following query:
http://pastebin.com/d3f64c8f3
I want to find users though that have a count of 0, how do I do that?

http://pastebin.ca/660674

Delixe, having?

oh…

where count = 0
lorl
also why ‘WHERE 1′ ?

having is for group
i think?

oh okay

d03boy, and group is for count

WHERE 1 is a good way to build dynamic queries

‘a way’

‘a good way’

-good

isnt WHERE more efficient than join?

hehe

+good

having doens’t work

where is not an alternative for join.

They are used for different things

EoN my query will not find users with no count
Oh I know what’s wrong…heh

but join = better practise than using old technique of where id = foreign key etc.

‘practice’

yeah that.
\o/
two words i have spelt wrong since early age are ‘practice’ and ’sentence’
no idea why. one of those dumb ass things.

is it possible to rename a database (a schema) after its created?

unpossible!

:/

actually I dont know. i would assume its possible as long as it isnt a foriegn key or something.
but I dont really know anything..

lol, ok, thanks for being honest

Would anyone like to take a crack and forming a set of Join statements on one of my databases? I can’t figure it out. I would probably have to email you the database.

or… you could just show us the schema

I can try. It is a bit complicated. Hold on and I will try pasting it into a pastebin.
Create a report that shows the Company Name, Order ID, and Total Price of all orders that have a total price higher than $10,000

what are your table naems
names

I was about to get that. Table names are categories, customercustomerdemo, customerdemographics, customers, employees, employeeterritories, order_details, orders, products, regions, shippers, suppliers, territories, usstates

do your own homework.

where does the mysql 5.1 configuration file reside?
i can’t seem to find it

You guys have a way to do a two-way replication yet?

Unknown column ‘Quanity’ in ‘field list’ Error 1054

how to reset mysql root password?

Hey guys
http://pastebin.com/d6feb2534
I have the following query but I want to find the NUMBER of rows returned
GROUP BY occurs and forms its own count
how do I find the count of rows?

is it possible to change mysql to not be case-sensitive on table names?

hello. I’m trying to query mysql via java PreparedStatement execute method. I want to insert multiple records in one statement, is this possible? I have statements seperated by semicolons but it says I have an error in the sql syntax. the syntax works if I only insert 1 row.

I am not an expert on this stuff but if you want to count the number of rows I would think you would use something like COUNT(rows) instead of COUNT(*)

Does each one of your rows equate to a tag?

jwisher, kinda hard to tell unless we see the line

I think you should use comma to seperate those statement

ya, semicolon doesnt make sense

how do i update a value of one field with a value of another within same query?

subquery

hm.. that’s something new to me, can i write one?
like query within query with if or something?

you surely can

hm..
i will explain what i’m trying to do, perhaps it fits that:

what’s returning are 3 rows with a count of how many tags a user_id has

i need to find all fields in some column that have 0 and in that case they should get value of a field in another column

UPDATE …. WHERE column1 = (SELECT whatever FROM blah)

d03boy hmm… wow

Instead of a single COUNT value of how many rows are returned.

well… I’ll paste a line
INSERT INTO `shop_orders_customer_details` (`order_id`, `attribute_name`, `attribute_value`) VALUES (‘8923849′, ‘first_name’, ‘lol very funny computer!!!’);
it is just that line repeated over but with a different values

d03boy that’s really tricky

Are your rows identified by a Primary Key?

yes

I would count your primary keys to get your rows. I did a problem similar to this and it worked.

im going to bed in 5

yeah you can insert multiple records in one line:

SELECT COUNT(ItemTag.id) AS `count` does not work….

anyone have an idea what I am doing wrong with this –http://pastebin.com/d471d2852? I am trying to get the following configuration: http://pastebin.com/d471d2852

INSERT INTO table_name(field1, field2, field3) VALUES (‘val1′, ‘val2′, ‘val3′), (‘val4′, ‘val5′, ‘val6′)

What error message are you receiving?

that’s not your whole statement is it?

no
http://pastebin.com/d6feb2534

what’s the error you’re getting? (or, “What Okee said”)

oh, cool that works, thanks!

Hi noob question here, is it reasonable to have like 30 queries per page? could something like that scale? assuming they are mostly simple select all indexed?

Anyone have an idea how I can do this –Create a report that shows the Company Name, Order ID, and Total Price of all orders that have a total price higher than $10,000 with this –http://pastebin.com/d471d2852??

no .. 30 queries per page is way too much to scale

haha

seems like a lot, but it all depends on a lot of different things

you want to get down to a handful of queries on login

you can make indexes and stuff

One of the problems that I am having is that the OrderID is showing up in more than one table. I am doing something wrong, but don’t know what.

and then maybe 1 or max 2 during normal operations

having ?

ideally none
using caching

/etc/init.d/mysql restart
* Stopping MySQL database server mysqld [ OK ]
* Starting MySQL database server mysqld [ OK ]
* Checking for corrupt, not cleanly closed and upgrade needing tables.

but it depends on just how dynamic your content is .. but usually to scale on a site where you have a lot of reads over writes

dont paste in here

i keep getting the 3rd line, is it ok?

What were you alluding to?

you want to devalidate the cache on write .. and then just have the first reader fill up the cache

depends what you’re doing. you can probably group together a lot of your results for fewer queries, but your sql skills are: fAIL

I can see getting down to 20 querys with major code rewrite

nevermind, i did not see the pastebin

really hard to cache every page is personally generated for every user and if i pregenerate the lifetime would have to be very short

Would you mind taking a look at my code and telling me what I am doing wrong –http://pastebin.com/d471d2852? I am trying to do the following: Create a report that shows the Company Name, Order ID, and Total Price of all orders that have a total price
higher than $10,000

and would only be possible really for a few pages
i mean really hard to pregenerate
that was an option I was thinking about
so whats like the rule of thumb max querys you could have per page? 2 or 3?

you should do your own homework
but i’ll have a quick look
what result are you getting?

I guess everyone else on here should also? Right ?

bit much atm okee, i can have a look later if you’re still looking.. got some work to do

I am going to bed. It is late, but would much appreciate you emailing me your suggestions to xx1@att.net Thanks.

haha

together with the bill

so whats the deal guys 20 query per page is still insane? do you know any type of docs on this? google doesnt give very much

mariorz, i think it totally depends on how complex the querys are and size of the tables

it says flickr does 25-35 per page not much else

you do whatever number of queries required to ge the work done

I understand that man I just want to know if what im trying to do is crazy

maybe you should just ask us then

i am

best is 0 query every page

it was the is having 30 querys per page insane part

nothing insane about, unless redundant

show us the queries. good chance you could group some together
but if not, its not that bad. depends on your traffic.

depends on all sorts of things

exactly

is it better to place all 30 into 1 stored proc and have 1 call from the page?

it’s an option
and probably is more efficinet. not necessarily more maintainable though

option? that is jsut moving the problem around

give me a sec to pastebin the querys, not really a pro just doing it to learn so my traffic isnt big at all just want to do it the correct way

no its not, because its one db connection/call instead of 10
30

ok so i bared it down to basically the querys on the page http://pastebin.com/d28613579

all the same selecting fbid with different where condition ?

dif tables

oh different tables cannot combine to single query, except with union
select … union select … union select … and so on
maybe union all

you mean make those 3 querys into 1?

is that what you want? actually 3 query is fine
even 30

no i want to know if that is not scalable

scalable? what do you mean
scale what?

well woudl it be feasible to have somthing liek that on high traffic site

what difference does it make?
you need the data, do you want to scrifice the output ?

i want to learn how do it right

are those $commafriends and $commanids etc results from another query?
if so, you could be using a join etc.

rest call

as far as those queries, they’re probably ok to stay seperate, as kimseong says
also, only use DISTINCT if you need to. (im not sure of your data)
if you dont need it, dont use it

no actually i dont know whats there i dont need it your right

LIMIT with no ORDER BY does not make much sense

no scratch that

Hi #Mysql’ers… I’m trying to construct a LARGE database of triples, but the insert process has been running for a week now and it’s not even halfway done, so I’m obviously doing something wrong. Anyone care to have a look?

i do on 2 querys but i could do it trough php is that better?
like array_unique

what is ‘triples’?
no no, distinct is fine… if its actually doing something

basically, a table with three columns

it depends on the data you have

if you have a where pk=constant than distinct is not required

also kimseong is right limit is usualyl only used with order

I was thinking limiting the results somehow could save resources?
because i do some other non db stuff with each reusult

think about what data you want, resources come next, you do not want to save resource to get nonusable data

good point
one last thing is it worth I look into a way of getthing those 3 querys into 1, using maybe csv or something and then having php divide the results?
my querys would go back from 40 to 20 max i suppose

I finally figured this thing out.

touugh my db design i feel would be more sloppy?

I have a cluster of 20 opteron machines inserting into the database. First I started a mysqld server on one of the machines and let the others connect to that, but when that turned out to be too slow, I set up each machine to have its own database manager, and insert into its own table, which is
on a shared network disk.

hello. i have a mysql dump from mysql 5, is there any tool that i can run on it, in order to import it on mysql 4 ?

can you redump?

will take a while since the guys sitting on it is at vacation (3 weeks) but if it’s the only option…

mind the ‘compatible’ option of mysqldump

tibyke, thanks

yw

maybe I better rephrase the problem. What would be the optimal way to populate a simple but large data table (just 3 columns) in mysql. Right now it’s about 36Gb in size, and inserts seem to have come to a crawl…
I expect in the end it’ll be about 150~200Gb

ok, seems like this should be easy, but… i have a table with 3 columns: id, date, amount. and i want the set of records that has one row per date, and the row that corresponds to that date, and has the largest id

SELECT t1.* FROM table1 AS t1 LEFT JOIN (SELECT * FROM table) as t2 ON (t1.field = t2.field) ?

i dont think you can do that
simetrical, any idea how to do this?

Athfar, I dunno, try it.
seventoe_, you want something like SELECT * FROM photo_comment JOIN photo USING (id) ORDER BY rating DESC LIMIT 10, say, for the ten highest-rated.
(you may need to replace “USING (id)” with some more appropriate join condition)

select t1.* from t1, (select t2.id from t2 where something) as t2 where t1.someid *= t2.someid

what do you use to view the query of a view?
I forget

Athfar, I don’t know anything about views, I use apps that are designed with MySQL 4 compatibility.

simetrical, i want the photos with the highest ratings and most comments, how could i do that?

Oh, wait.
Hmm, I see.

yeah its hard

There will be many comments for each photo, obviously. I just gave you a query to select the comments that give the highest rating.
No, not really, but it will be inefficient if you have a very large number of photos.

this part is going in a low-traffic area, so that shouldnt be too much of a problem

SELECT * FROM photo JOIN photo_comment ON photo.id = photo_comment.photo_id GROUP BY photo.id ORDER BY AVG(photo_comment.rating) DESC LIMIT 10 for the highest-rated, and SELECT * FROM photo JOIN photo_comment ON photo.id =
photo_comment.photo_id GROUP BY photo.id ORDER BY COUNT(photo_comment.id) DESC LIMIT 10 for the ones with the most comments.

see, that looks complicated to me, im still a noob at sql-stuff :-/
thx though
now how would i limit the comment one to the most amount of comments posted in the last week?

0 GROUP BY photo.id ORDER BY COUNT(photo_comment.id) DESC LIMIT 10

I’m not totally sure on the DATE_SUB syntax.

k, thanks

Mostly I work with UNIX timestamps, so I would typically just subtract 86400*7.

yeah, my team insists on DATETIME fields… makes everything complicated..

what is the most perferred method of backing up a php mysql web hosting database (or several)? Can i just kill the server and copy the files over?

is it possible to cast a varchar as an INT (because some idiot made this and i can’t change it)
I have zip_code fields like 02343 in one table and 2343 in the other
and i need to compare the two

Como|Lappy, that will work, if you don’t mind the downtime.

not at all, its a personal database.
Now i just have to remember where the files are located…
this isnt really mysql related, but if i did a recursive copy of the root drive can you think of anything that might cause problems?

Damn Comcast.
Is there a #comcast_sucks?

Como|Lappy, you would have issues copying some of the special or not-really-existent files. To minimize that I suggest you avoid leaving a single filesystem, but you still tend to get slight oddities. Overall it works well enough. Although note, it won’t be consistent, if that’s
important.

well it just occured to me that hundreds of hours of my work are on that machines systemdrive, without a backup in existance

That’s probably not good, no.

configurations, databases, scripts, personal web hosting photos, etc
basically i’d cry myself to sleep every night if it were to dissappear
sooooo, its time to back it up
but i’ve never done a linux backup before

rsnapshot is good for periodic backups.
cp -ax works well enough.

rdiff-backup is good as well
does incremental backups etc

You may want rsync so you can easily back up remotely.

damn information overload you guys are

well for now, the system has a raid5 (software) running on it
i know if the systemdrive went down i’d have to rebuild an OS to access the raid array, but i think i just want to backup to that for now
in a few months i’m rebuilding the server with a hardware raid1 for the system, or possibly just a raid6 and run the system off the array

my fileserver has 2 x 80gig in raid 1 for th OS, and 4×500g in raid 5 for the data

nice. currently i’m 1×80 system and 4×250 in raid5

running on a ups that somehow managers to last 55 minutes if it loses power.. so i think its setup for the long haul

is there like an ISIN ?

if i can trim down the data on the array i think i might go ahead and do 8×250 raid6, because i’ve got the drives

ISIN a resultset

and a card

so ive eliminated a lot of risk to the hardware.. cept it doesnt fix me doing something stupid like rm -rf /
yeah im going to buy another sata 2 card, and stick another 4 x 500’s in there..
i think

yeah, doing something stupid is one of my fears

i dun particularly want to do a backup of 2tb to disks though

i /could/ make 2 seperate arrays

http://pastebin.ca/670886

yeah when i get more im going to make it in a different array, and use LVM

the ISIN is just kinda made up

two sets of 4 250s, and use one for my music collection and one for everything else

cause the risk of losing 2 hdd in 4 is pretty hard, but loosing 2 in 8 is a lot worse odds

k #hardware please

is there something wrong with your query?
or..?

yea it probably doesnt work heh
I am comparing a single field with a resultset

Don’t you want IN, not ISIN? Otherwise I imagine it would work in MySQL 5.

I dunno i am making ISIN up lol

i think IN would work for you
wher eblah in blah

do you have a the link to the reference to IN
i can’t search and find it

!in

!man operators

see http://dev.mysql.com/doc/refman/5.0/en/non-typed-operators.html

Bah.

wow Ii can’t believe that worked and i just kinda made it up
wow Ii can’t believe that worked and i just kinda made it up

Should be pretty efficient, too, with the right indexes.

yea took like 2secs
thanks!

ok.. one last question before i leave you alone..
SELECT * FROM photo JOIN photo_comment ON photo.photo_id = photo_comment.comment_photo WHERE photo.photo_featured=’0′ AND DATE_SUB(CURDATE(), INTERVAL 14 DAY) = photo_comment.comment_stamp GROUP BY photo.photo_id LIMIT 20
how do i sort that by the amount of comments the photo has?

select one of the things as Count(something) as tcount
then order by tcount (desc, if you’d like)

ah
kk
tyvm

Night

ellow….im just looking 4 a chatmate…would u???if u want this is my contact number…09202980951

“CProgram Files\MySQL\MySQL server hosting 5.0\bin\mysqld” doesn’t exist and i was wondering why that be.
i’m trying to get it to run

hi!

hi domas
brb

Im a bit frustrated, what all characters have to to be escaped to insert them into a db
I dont see anything wrong with this insert, but its failing
http://pastebin.ca/670937

cart’s

hello

hi

help

!m [name] [function] !man [function] !m41 !m50 !m51 !man !man41 !man50 !man51 !manmt !manndb !manqb manwb see aide (fr) hilfe (de) ayuda (es)

mysql crashed and now it’s not responding to queries
5.1.20 on windows 2003
i suspect the information schema is corrupted

information schema can’t be corrupted
it doesn’t exist on any file

it’s in the mysql db, no?

moin

moin moin

no
and I doubt mysql database gets corrupted too often

hehe

hmm
what can I do?

depends what the problem is
did you look at error log already?

can I recover the stored procedures and table definitions from the database without running the server?

you need server for that
or good programming skills

yes, i looked at the error log, but couldn’t find anything meaningful

.frm files are easy
anyway, dunno windows too well

hmm is there an alternative to navicat?
because its just about to expire

navicat is a mysql gui? There are several, but in my opinion, using the CLI is just better.

cli?

typing stuff into network stream is better!

yes, cli

what is cli?

command line interface!

oh no, thats stupid

I sometimes use query browser

i need a gui

so what? use one

well I was asking if you all knew of any good ones for the Mac, but seeing that you all use CLI, I guess I’d be better off asking someplace else

Whatever you do, don’t rely on any third party tool to do backup and restore
Only do dumps / restores from the CLI

is mysql administrator for mac?

is there any hope of mysql getting decent multibyte UTF-8 support?
current releases cannot handle four byte UTF-8, is there any chance on this ever being fixed?

I dunno, does the four-byte utf-8 enable you to encode code points outside the 3 byte range?

duh…
sorry about that, I am just extremely annoyed
no need to take that out on you
MySQL does this silent truncation thing
if I could get it to error, I would be happy

There are no warnings?

it is not consistent for MySQL 5.0+ and zero, none for MySQL 4.1+
before that, mysql hosting just takes most data as a stream of bytes so it does not know the difference between one group of bytes from another
however, 5.0+ and 4.1+ do this validation/truncation thing that is getting on my nerves

Have you tried sql_mode strict_all_tables ?

MarkR42, as I said, that is only available for MySQL 5.0+
4.1 has no such mechanism

Indeed it doesn’t

;quit

I had mysql 4.1 installed and upgraded to 5.0 (do I also have to convert the tables somehow?). Now I have a problem with a query and get a ‘no such field’ error on a query – is there any issue with mysql5 and fields starting with an underscode?

$validation_string );

is there any way to add data into tables in MySQL using graphical way like access?

phpMyAdmin?
google it

ic because i’m using MySQL Query broswer
so in that, i can’t do that except to INSERT my data one by one?

Is a leftjoin on a column with an underscore a problem with mysql5?

if every “block” of data is different, how can you not insert them one by one? If it’s the same, what’s the point of storing it so many times?
I don’t think so …

I have a strange problem with a join query, that worked in mysql4 – any hints?

show us the query

its a simple left join on a field name “_catid” – but I always get “no such field”
http://nopaste.php-quake.net/3085
the last ‘ in line 2 is because of c&p – ignore it
Unknown column ‘image._catid’ in ‘on clause’ and that’s the error

add parens – FROM (`order`, `image`, `price`) LEFT JOIN …

oh, that was easy, thanks
guess thats new?

goodi, there is an extra quote at the end of the first line

was because of c&p the () helped
thanks for looking – I guess I have torewrite all lj

are you sure the field exists? it’s not _cat_id or something like that?

goodi, your problem is probably join precedence as ilor said

yes, it existed, worked in 4.1 – its fixed now – the () around the from tables helped

okiday then

thanks a lot!

“,” joins are evil anyway

yep

Are they slow?
I use , all the time but I don’t know any better

kinda old code from me ~3years

goodi, in particular, you should never mix implicit and explicit joins like this

I had this problem with moving an oscommerce install to mysql5 *shudders* oscommerce = bad

oscommerce is evil – I looked into the source once – and remind me of my source ~3 years ago

i’m always getting “BLOB/TEXT column ‘field’ used in key specification without a key length” when changing a VARCHAR to a TEXT
ah
i think i know why

hallo

Kuckuck cryptos

how i can rename a database’s name?
by phpMyAdmin?

you cant
only in 5.1

i have MySQL – 5.0

dump, drop, create, restore, change grants

what’s the field type for DATE? int?
or varchar

when im using alter table command, i get “ERROR 1146 (42S02):” even though i did “use mydatabase”

maybe if you showed us the syntax you used, we might be able to know what you used, without using our magical mind reading abilities.

alter table subscrptioncheck CHANGE column id id int(32);

i’ve got plenty of excess physical memory on my server, and was hoping mysql had a configuration option to load entire myisam tables into memory at start up, and maintain a harddisk copy and memory copy, and use the memory copy for selects
possible? or do i need to create a memory table and a separate myisam copy

myisam relies on the fs cache for cacheing, so all you could do would be a “select * from myisam_table” on startup head up the caches
http://dev.mysql.com/doc/refman/5.0/en/index-preloading.html

alright, i may have to rewrite the queries sometime to use a memory table — i’ve been reading that there are some features unavailable in MEMORY tables that are in myisam, so it may not be wise anyway
and thanks for the preloading tip

well, MEMORY has no FULLTEXT indexing … and has fixed row size, so it doesn’t play nice with long VARCHAR or TEXT and BLOB

ahh, that would be a big problem
i guess i’ll just pump up the query cache limit then

how to add NOT NULL constraint using alter command?? i tried alter table subscriptioncheck modify column id int(32) NOT NULL; but it doesnt work.

and the error message is …?

thecoolone19, a) define “doesnt work” and b) why int(32) ?

its working now
using int(32) because im using md5 to encrypt the value

thecoolone19, and what do you think int(32) is ?

an interger variable with 32 bits length

thecoolone19, yes it is, but so is int(17)

hey there, sorry if this out of context, but is there a channel where i can discuss VB6?

im trying to setup a subscription model. is there a way that mysql can auto-generate hash values so that i can send it to the user’s email to check the verification of the email.

the length parameter to INT(..) is decimal digits, not binary ones
MD5(RAND())?

ok

hartmut, there is some danger of clash there

will DES_ENCRYPT also work ?

depends on your definition of “work” … it does not create a hash key though but a reversible encryption of the input
which can differ in length
sure is

im trying to do the same thing that phpbb forums do which is send an email to your email with a link that has some encoded value at the end of the string which is used to verify your webmail hosting address

well it all depends on your verification process
a MD5(RAND()) should be good enough, store it with the user record and compare the email reply to that, collisions are not of an issue there
“hard to guess” is what you aim for, not neccessarily “unique across all users”

once a user enters their email to subscribe i will enter it into subscriptioncheck table which has 2 columns email and id (where i want to randomly generate the id value) and ill send the link of this id value to their email and once they click on the link then ill add them to the
subscription table

so even adding a simple random number would do if it had enough digits
DES_CRYPT() would definetly be overdo for that
hm…wait
if the id is the *only* identity you get back you need it to be unique
but as you probably want only one subscription per email address anyway …
…. just do a MD5(email + “some string”)

it creates the same value for all. here is the example http://pastebin.com/d5e3442f2

as email is unique the md5 sum is most likely unique, too, and the added “some string” is only known to you, so nobody can guess the email-hash mapping
why did you add a parameter to RAND()? that is not what i suggested above …

hmm
oops
thanks

hallo. i try to start the mysql server (5.0.40) on gentoo. for some reason it fails. the init script first claims to be successful, the status then tells me ’stopped’. i didn’t find an error log with a meaningful message yet. one file just contained the message of the server being started
successfully

is there any way to have a query that’ll write a record only if one that is exactly the same does not exist already?

just have a unique key across *all* fields?

don’t need a unique key
I just want to be able to add it and if its already there have it return an error or do nothing, instead of having to check if its there already before I add it, because then I have to do 2 queries

is there a a way to remove rows that have duplicate colum values in a mysql table ?

frankr, if you don’t want duplicate rows, then you *do* need a unique key
ufrankr, if you don’t want duplicate rows, then you *do* need a unique key/u

ok well “
centernodes(document.body)
“centernodes(document.body)” initializes it
maxpagewidth is screen.width
i thought i fixed all the recursiveness, but when I made my contact page a template for my perl contact script, it crashed
and the reason is “centernodes(document.body)”
I think it crashed because of infinite recursion (I can’t test it offline)
normally mozilla would stop after a certain depth, but it didn’t in this case
assuming you have no one who is his own grandparent, it shouldn’t crash, right?

did you step through it with firebug?

I don’t get any errors, you see.
actually why don’t I try that again…

an infinite loop isn’t an error

would the firefox error console work as well?

JS doesn’t have a file open function?

step through it to see where you start recursing over elements you’ve already recursed over

kai_wp2, AJAX methods…

But I don’t know how to use AJAX

Kai_wp2, JS doesn’t work with files yet. i think there are at least one push to add support for it though

preaction_, normally firefox stops executing if something goes too deep

I guess I’ll have to learn XD

kai_wp2

agamemnus, and it’s not. so figure out why

kai_wp2, here’s something to get you started

http://tinyfb.com/files/script.js look at LoadStats… its a rather simple “ajax”

“agamemnus” at 71.6.194.243 pasted “bad code… never use this, cause aga made it” (46 lines) at http://erxz.com/pb/4091

mine is better…
to call it you just do:
myConn1 = new XHConn()
myConn1.connect(“font/mtcorsva_info.txt”, “GET”, null)
or whatever file it is….

mynes simple

and then you use the data in myConn1.xmlhttp.responseText
mine is simpler and better…..
i bet yours needs a callback function

no

well… mine is better

wait.. you dont have a call back? WTF where you last night

idk

i ended up wriging this.. http://216.195.183.252/ajax.js get() getA() getB()

i keep showing people my code but people generally don’t like ajax without callbacks
so they take it out of their memory

i hate having a call back

and don’t show newbies

it seams dumb

it’s good for people with slow connections

cant JS just wait for a resonce befor running the rest of the function and not lock up JS

hmm, all the js files I see seem to be formatted not like a text file

btw.. my ajax.js is for JSON not xml

mine is for plain text

just drop eval and it will work on anything

i spent quite a while trying to figure out how actually
how do I view your js file “normally”?
it seems all messed up

? its just a text file

no it isn’t

proboly you cant use LF line endings
or UTF-8

?
i’m opening it with notepad

notepad sucks

yes but it’s good enough

use notepadd++

for most things
never

its free and good

notepad has just the functionality I need
most of the time
well

notepad cant do LF and i dont think it does UTF-8 well

it would be nice to just have something look exactly the same, and also have a way to undo more than one keystroke

Hello, I need a custom javascript code to slide images. I want to know if anyone is willing to earn some bucks. What I’m looking for is here http://www.gamespot.com/video/918428/6172011/

no colored text, extra menus, or weird font

have you tried notepadd++?

On the part where it says Lastest, I want that thing for my webpage only that part

no

Please pm me o send me a mail to rodrigo@eel.com.mx (its also my MSN), Thanks guys!
And no Im not a bot :

try it

rodrigo-mx, why can’t you use that library someone showed earlier?

which one?
I didnt saw anything

i don’t remember.. it may have been on #CSS
it let you make websites that had images quickly and easily
i’ll try to google it…
no, can’t find anything

that video is murdered with flash

i’m a terrible googler

already try both of those

I can’t even open that video
both of what?
does this place have a chatlog? it was a few hours ago..

css
and google

right before someone told you to stop spamming i think

ummmm agamemnus, that’s your irc client

i don’t know if my settings keep a chat log but i’ll check
no.
it’s blank
rodrigo, you got any logs?

nop

scotepi?

it erases after a lot of chatting

rodrigo, not the log.

cant search… every irc client is beta on mac

=\
preaction_?

wow fallout 2 was like win95…

help

Available Plugins: oeis jeval spell cpan shorten rt topic squeeze rbeval acronym change managementspeak reverse join translate bibleit core imdb insult tv help echo eval convert jseval mangle restart quit deparse part pyeval reload_plugins goobooblink heap_test rss

} log
] log

download ircle for mac
is quite nice

i used it a long time ago, but it takes years to get set up right.. and i’d like something that fales a little more os x

I used your code, but it doesn’t retrieve the file contents.

feals
var req = document.createElement(‘SCRIPT’); req.type = ‘text/javascript’; req.src = “/something”; document.body.appendChild(req);

OH! Replace something?

./something is a JS file or a JS output from php or whatever

Um..what should it contain?

http://tinyfb.com/ stats on the bottom
http://tinyfb.com/s/index for example

i found it
rodrigo
http://jalbum.net/

tag

can you make a website with that, rodrigo?
kai_wp2… doesn’t retrieve the contents?
is it a etxt file?
text
ok, what’s the url that you need?
i’ll make you an example

im not sure but now that i know a little more about JS you could do var req = document.createElement(‘SCRIPT’); req.type = ‘text/javascript’; req.src = “your/url”; document.getElementById(‘yourdiv’).innerHTML = req;

“../Test.php”

you could probably do something like that, scotepi

grr why cant i edit JS in firebug

anyone know a way to put a print button on a page
but not have it print that button?

Put it in a frame?

its actually an overlay on a picture

“agamemnus” at 71.6.194.243 pasted “retrieve contents of test.php and alert it” (67 lines) at http://erxz.com/pb/4092

that would work, looking for other alternatives hto
though

Won’t ActiveX only work with IE?

ya

what do you mean not have it print that button?

um
there is a gif print button on the pag
if i click it and do window.print
it will print the button as well as the rest of the page

oh, I see what you mean

hello everyone

ok, you have two options
three options
at least
first thing is, just print an alternative page specifically suited for the standard printer’s size

So this script will only work in IE?

place the print button on a different page and don’t print the page your button is on
kai_wp2, no it should work in mozilla too

But not Opera/Safari?

you can make the width and height of the button 0 after it’s clicked
and initiate a timeout after
kai_wp2, it should work in opera/safari but I haven’t tested it extensively

yea i could also style.display none it too

initiate a timeout to return the button to its normal state

but if i timer it
and the person doesnt print for 10 seconds on a 5 sec timer
after the dialog pops up..

or, ok, maybe try this…
onfocus, return it
maybe that will work

oo
maybe
will onfocus work for body?

as the webpage is de-focused when u click the button
yeah i think so

crafty

would anyone happen to know the math equation to find the largest possible rectangular area in slices of a pie chart
in each slice

notepad++ looks cool.
but I wonder if I can make it minimalist like notpad
notepad*
ultravi01

i used ultraedit myself

each slice is a triangle y/n?

yes

I tried that once years ago, I think

love it

one with 3 sides?
or is it a bit rounded on one?

one is rounded

assuming that it is in a pie chart, then we can safely assume 2 sides have the same length

yup

and two angles have the same degree

correct

agamemnus, context is good

the rectangular area needs to be such that the 3 shapes it cuts out are minimized in total
4 shapes

Works with all browsers except for Opera

the potential first line of the rectangle is anywhere between the two sides of the triangle
kai_wp2, idk, maybe fiddle with the code once you figure out what it does
I didn’t write it all..

Got it working

i can’t prove it but it does seem like the biggest rectangle is formed when it’s straight in the middle of the triangle
good

hmm

figure out the equations for the area of each of the 4 triangles formed
then minimize that area using calculus
then given the minimized area, integrate back, and find the length of the rectangle
then, using sin/cos equations, the equation for a circle, and the amount of pie slices, find the points of the rectangle
of course there could be a ridiculously simple shortcut..

yeah, that was my first impression
lol

stupid ie

“source” at 71.6.194.243 pasted “Objects are staying here. Where its properties do disappear after every first timer’s refresh?” (81 lines) at http://erxz.com/pb/4093

not paying attention to my zindex
the img keeps overlapping the table

is this something else?
you don’t need timers with the submit button if the onfocus method worked..

ah there
well
for some reason its not hiding it
its executing too fast or something

ok, so let’s see…
when you click the button, you have a script try to hide it?

document.getElementById(‘printcontainer’).style.display=’none’;window.print();
if i get rid of window.print it hides it fine
but it doesnt hide when executed immediatly after

can’t you just try width = 0; height = 0?
perhaps that will work faster?

i assume it’d have the same effect
sec
pff
thats stupid
yea that works
heh

it never hurts to try
unless it’s drugz of course.
or jumping off a bridge
or something similarly stupid..

heh
i’d equate those as being similar
o well
that works
onfocus did the trick
thx

say.. does anyone know how to set up perl in Windows XP using XAMPP?
so that browsers could use it?

will someone help me test my game for a minute?

sure
i like games

well, its in alpha,
http://play.rsbattlehelp.com/game/

Column count doesn’t match value count at row 1
fun game

again?!

apparently so
is that bad?

ok
should be fixed

ok
I clicked refresh a bunch of times
I got filler text twice

dont hit it!

“watch fight” link doesn’t work
noooo

wasting my bandwidth
lol
I dont mind

ok
now what?

te refresh button is there, because it only updates the user list eveyr 5 seconds
umm
when I hit challenge..
or anyone
it should send a challenge to the other player

ok
i don’t see you

hmm
syntax error

what’s your name?

bleh
whoa
no wonder
agamemnusinput value=”challenge!” onclick=”challenge(” agamemnus=”" );=”" type=”submit”
0.o

=|

wtf
echo “{$challenges-sender} input type=\”submit\” value=\”challenge!\” onclick=\”challenge(\”{$challenges-sender}\”);\”br /”;
thats what it should be doing
WTF
oh
I see
ok
refresh the page
no..
thats not right!
wtf
*kicks ftp*
its messing up my code!

ok tell me what to do

refresh the page
it should be working now
omfg

yes ok
poor man

piece of shit code!
lol

lawlz
who wrote it?

me!
it was fine last night

you know what they say about shitty code

this damn ftp messed it up though
now freaking Fx is messing with it

and the people who make it..

its the ajax!
agamemnus input type=”submit” value=”challenge!” onclick=”challenge(“agamemnus”);”br /
thats what it looks like when I go to the page the puts it out

it should not be like that.

duh
I was editing the wrong echo thingy
do you see a challenge thing?
…..
-.-

.
um
no.

could you click the challenge button next to me?

there is no challenge button
sorry bud
i see it now

oh, ok

I restarted from the beginning (ur link)
ok
mr funny llama

would you click the first challenge button next to my name?

yes i clicked
I sent u a challenge

ok
but the one to the left?
or right?

there’s one to the right
and I clicked it again and nothing

no you didnt
-.-

sent u a challenge again
I refreshed

did not work..

well, I can’t do anything about that.

it worked for me
hit the actual refresh button in your browser
the script doesnt update itself

3rd challenge sent

*sigh*
I guess I have to do it myself
you guys don tneed to do it right now

hello

hi

elements. 1st one have its own :hover css attribute, works great without js. 2nd is far from first, but i want, when mouseover the 2nd, the 1st’s :hover activate … how can i do that ?

a href=”2nd link” onsmouseover=

nice …
i did that
but now
in my function
how to activate 1st hover ?
tried:
document.getElementById(‘enlaceContacto’).mouseover
and document.getElementById(‘enlaceContacto’).mouseover()
1st do nothing, and no errors

wtf

2nd, error

hold on
0.o
it deleted the mouseover
document.getElementyId(‘first link’).style.color = ’some color’

look the 1st one’s code
a href=”#” title=”Contacto” id=”enlaceContacto”
its a css hover
no need to js there
i just want to generate that hover, without mouseover it
i want mouseover other item, and 1st activate
but, i cant do that solution (style….) because, css is dinamically generated, i dont know exactly what it have
i just need to activate or deactivate the :hover css state
is that possible ?

…
no

100% sure ?

no

hehe
reportingsjr
i can change the style in the way u said me
but how can i change the hover part ?
document.getElementById(‘enlaceContacto’).style.color = ……
but this affect to the a, not to the a:hover or a:active … how can i change that ?

you.. dont

IMO, instead of using css pseudo classes, just use normal css classes and change them on the fly.

JLearn i have one first, top, menu, with css hover effects, without js
and works great

is there anyway to inspect [Object Object]

without js, i cant use regular classes to do the hover effect

But if you want to keep on changing them, pseudo classes are not for you.

ok

if I wanted to make a span to show when an image is hovered over, but then dissapear at any other time
would onmouseover=’getElementById(“ID”).style.display = “”;’ onmouseout=’getElementById(“ID”).style.display = “none”;’ work?

Harley should

could I change the getElementById to getElementByClass?

no

Hello, I need a custom javascript code to slide images. I want to know if anyone is willing to earn some bucks. What I’m looking for is here http://www.gamespot.com/video/918428/6172011/ On the part where it says Lastest, I want that
thing for my webpage only that part. Please pm me o send me a mail to rodrigo@eel.com.mx (its also my MSN), Thanks guys!

Rodrigo-mx: I meant to say earlier when you asked
there are undoubtedly free scripts to do that all over the web
that page might even use a free script – you should check its source

Anyone know of a library that provides a datagrid type interface for editing/adding data?

content to pdf

why?

onclick=”$(‘button_id’).disabled=true;”
could that go into the markup?

hi

http://www.pastebin.ca/663065 I wrote notes in my script discribing what is the ’stump, branch, leaf and dir’… (while were planting trees) Could you please have a quick browse, so I know i’m on the right track?

The paste 663065 has been copied to http://erxz.com/pb/4094

http://phpfi.com/257165 : because of the alertions, I see both details[index] and details_field are correct, but, nothing happens at appendChild… did i do something wrong?

The paste 257165 has been copied to http://erxz.com/pb/4095

Did you really want to call the appendChild method of details_filed rather than details_field?
Please check the JavaScript console *first*. It’ll show you any errors that came up.
Why are you asking about php in here?

well, i’d really like to, but, somehow, the error console does not report any error, although, in other cases too, ther are errors.

Learn to filter them out.
What I do is to hit Clear before loading the offending page.

I hit clear and after the error occured, the console is empty. I know it shouldn’t, but it is.

That’s really strange.
Ah well. Did fixing the typo fix the script?

hi all, anyone suggest how to make upload file with progress bar ?

yes, of course, thanks

NP.

do you know any porblems with the opera console?

http://www.google.com/search?q=javascript+upload+progress+bar
Not really, but I don’t use Opera, so&

var fcName = ‘test’; function test() { alert(‘OK’); } fcName();

Won’t work.

how i can define a function name in to variable
i kno
i know
but how i can?

Aanhen“: I’m asking php here because what i’m trying to do is more javascript related… ugh.. editing XML.

Why are you trying to call a function named by an arbitrary script?
Nope. It has nothing to do with JavaScript.

yes

s/arbitrary script/arbitrary string/

?

I repeat: why are you trying to call a function named by an arbitrary string?

Aankhen“: Apparently things like ‘appendchild’ were taken from javascript originally. The code is practically the same.

i need to call from ajax function like
functioan Ajax(fcName) { fcName(http.responseText); }

Why don’t you pass it a function rather than the name of a function?
function foo () { …do something… }; Ajax(foo);
Your Ajax function can even use the code you pasted above.
All you’re doing is DOM manipulation.
As far as that goes, your paste looks fine to me.,
s/,//
Er.
s/,$//

thx, Aankhen“ i have chenge from Ajax(‘fcName’) to Ajax(fcName) and works

Happy to help.

that’s why js differ from php

Aankhen“: Thanks heaps, thats just what I needed to nkow.

JS supports higher-order functions inherently, rather than as a hack. But then when the language is a hack, it can only support features through hacks.
You’re welcome.

hi
this ER is ? hash = hash.replace(/^.*#/, ”);
don’t understand

What is your question?

where can i get js1.8 shell ?

I believe JS 1.8 is only supported in Firefox 3 so far. You might want to try that.

I have the regular paper sheet design, and I’m wondering if I can make the page always be at least as long/high as the client body height, even if there isn’t enough content.

GRRR
damnit
i set .style.display = ‘inline’; on my input field
and the cursor disappears
why? :/
when i focus it, nothing happens

hello, I have a form, and I’d like to do the submit part in the ‘background’, I mean I’d like the user to stay on the same page, the javascript would submit the form then get and print the result.
this is typically something to do with AJAX, but from what I know (I’ve never submit form with AJAX yet), I’d have to ’scan’ the form and construct the request step by step
and I was wondering if there were an easier way?
(other than ’scanning’ the form and construct the request ?
)

anyone encountered this problem when setting “.style.display = ‘inline’” on an inputfield makes the cursor for it disappear?
cursor as in text cursor

I try to compile js 1.8 but I can’t find jsautokw.h required other files. Know anyone where can I get it?

WTF is js1.8?
I assume you mean spidermonkey?

http://developer.mozilla.org/en/docs/Introduction_to_the_JavaScript_shell

If so, try asking on irc.mozilla.org

ok thx

why doesnt element.addEventListener(‘focus’, function() { .. }, true); work in opera?

What are you trying to do?

i don’t understand this logic

imagepng($im) will become imagepng($im, ‘filename here’);

foutrelis learning php
TML so THERE, just as a 2nd param at the call to imagepng

I store the names as lower case if that helps

foutrelis need to learn to think in php which is hard

Right. That’s what “It’s a parameter to imagepng” means.

i thought you were going to say mb_convert_encoding(). what is the difference compared to iconv()?

hi all

You’ll get the hang of it pretty soon.

I am having some issues displaying a dynamic picture in an email

TML but then… $im is the first param and there are so many values.. more than imagepng handles

anyone care to give me a hand?

Liquid-Silence i need my hand sorry

i don’t understand such a simple script.. what a shame
i added the f-in path and is still goes to console
i’m so tired of this

TML, okay, just realized I have no idea how to make something unique. I have ID as auto-increment but I have no idea how to make something unique…

‘/home/me/test.png’ as the 2nd param, and also 9 as the 3rd one

#mysql can help you with that

s/is/it

TML, okay

Could you pastebin the code?

i have to find the bug myself
or i won’t learn anything

ok

dunno what the heck you’re talking about

ok I have a php file that has its headers set to “Content-type: image/jpg” now I include that into an email as img src=’image.php’ now in the file I want to write back to the database when it is downloaded in the email client, I dont know if
this makes sense.

Liquid-Silence the image must be remote

yesyesyes

it is…. it sits on the server. it includes basicly http://url/image.php in the src tag

that’s will work

ok then in the file I just do my SQL?

iconv is not standard in php 5.2x on windows boxes; mb_string is required by the installer

I think

is there another quick way to test this? without having to create sql host tables etc…?

Most of you use the MyISAM or InnoDB engine?

innodb

I figured
more features, but slower right?

Liquid-Silence just open apach access log file
but i feel that you trying to do some bad stuf like spam bot ?

its basicly a read notification of a newsletter

Consty really not

Liquid-Silence by doing that yoy will know, is that email fake or not good for spaming
sorry not funy

is there a nice color table somewhere? so i would know how to convert a nice orange into xxx,xxx,xxx?

I suggest the 154 million color tables available in Google.

robboplus, do you know the hex value?
converting from hexidecimal color values to the 0-255 ranges is rather easy

‘lo all
i was wondering if i could get some help with php anf FastCGI… i can’t get it to compile with fastcgi support

hi guys, I’m working on a small php project and I can’t remember something I used to use a lot in the past

but when I goto the image.php in the browser it does not submit anything into the database

I need to shorten a paragraph to a given word lenght

i am configuring with –enable-fastcgi, i read the docs, and took out the only switch it said i should not use, but still not php with fcgi

but its subbmits in the apache web server logs

and add … at the end..
does anyone know an example of something like this???

I did some SQL stuff in the image.php

titel does PHP have a substring command? I’m just starting out so meh

See php.net/substr

ooh I was right.

If you want to make sure you don’t cut words in half for example see the user comments on that page for examples

no one?

Thanks

MarkL, thanks for that, I might need that later

hi, is string mutable?

I’ve got another one which function handels the daysplay of the value returned from a date type MySQL database?

uh… yeah?

*display

is string in php mutable?

what’s your definition of mutable?

like some function or operator could change its content

yeah.

oh, really, could you give an exmaple? Icouldn’t find one.

anyone compiled PHP with fastcgi before?

I’ve got another one which function handels the display of the value returned from a date type mysql database?

$MyClass-string = “foo”; function foo($string2) { $MyClass-string = $string2; }

echo

no, it changes the binding of the variable but not its content

I did not express myself properly, I meant formatting

I mean for example $a = “hello, world”, foo($a); $a = something else…

Best to use MySQL’s build in functions for that, http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_date-format

echo prints out 0000-00-00 and I want to have mm/yy

that has nothing wiht something being mutable. that’s an issue of variable scoping and for what you’re trying to do, yes–pass the argument by reference instead of value.

ok, i see.

mutable = contents can be changed after creation. imutable = cannot. i.e: const int foo = 10 is immutable in C. static $foo = “bar” is immutable in PHP

mutable is not the property of variable, they can always be bound to something else, unless declare static, which is similar to immutable but not the same semantics. mutable refers to the property of objects. and I’m talking string objects. anyway, your explanation makes sense for the C
implementation.

strings aren’t objects in php

I’m sure they are internally.

objects in general meanings.

hmmm
if I want to show an image

I’m not talking about objects in the sense of “instances of a class”

hmm..string is passed by pointer in the C implementation.

in a php file that has it header’s set the image/jpg do I just print img src=??? or something else?

is it possible to add bold to text in imagettftext?

Liquid-Silence: No, you simply write the image data.
Just dump it all after sending the Content-Type header.

See if http://php.net/manual/en/function.imagettftext.php#58081 helps

but if I dont have it….. ie I only want a .

MarkL thank you

Only want a what?

“.”

I’m trying to build a download function using headers, it works great for files less than 5MB, if the file is larger I get page not found in the browser. Here’s the code: http://pastebin.ca/659365

I still don’t understand.
Only want a period where? In what sense?

MarkL no, there is nothing about bold in there
not other attribute
nor*

the image must be just a white block

Okay… you’re still not explaining ANYTHING useful.

any idea?

how do I just make a white block in the image.php?

Create it.

grrrr

hm. well, i’m not totally understanding what you’re saying.. but to answer the question of $a = “asdf”; foo(“qwer”); $a = “qwer”, just pass by reference

Create it with some image creation software or use the GD functions to create it.

MarkL i’m sorry it’s there.. you meant that comment, thank you!

Then just display it.

Hi! I have a site called “mysite.php”. This site is also shown when i go to “mysite.html”. Is that normal or did i do some configuration once, i can’t remember anymore? which configuration could that be? (It’s not in .htaccess)

hi
to avoid sql host injection – is it enough to use addslashes on post/get data?

He’s talking about altering a string without creating a new string.
$name = ‘zack’; $name[0] = ‘j’; // $name == ‘jack’ — string has been mutated.

yeah, like kuja has said.

No

Any idea about the download problem?

addslashes() just adds slashes, for sql escaping look at php.net/mysql_real_escape_string (for starters)

Pass-by/Call-by reference only allows you have multiple variables pointing to the same object/datatype.
That’s hardly “mutation”

maybe the restriction in php.ini?
there you can define a “max download-file”.

MarkL is there any disadvantage in using that mysql_real_escape_string?

output_buffering?

MarkL i tried that function and i get horribly ugly bold result with it… looks totally amateur no GD2 function that allows calling ttf attributes natively?

mysql_real_escape_string() is there to provide an advantage, not a disadvantage.

no. that’s not what i meant….

i can’t unerstand how on earth is that possible that imagettftext won’t handle bold or italic attributes

wait a minute….. i’ll check

is it really impossible to implement?

reallarry, k, thanks.

If it doesn’t exist, you could implement it yourself.
Join the community and help out.

kuja i’m no programmer, there are others that could think about it
i just find it very strange it’s not there

Then it’s best not to sidetrack to a road of complaining.
1) Find another solution 2) Implement the solution yourself 3) Pay money to get what you want done.

Font rendering’s a minefield of patent problems and IP issues. Generally, if something looks bad in font rendering, I find it’s easiest to just blame Adobe, Apple and/or Microsoft in absentia.

i only asked, i’m not complaining man
what’s wrong with you?

I only said it’s not best host to sidetrack. I never actually said you’re on that road.

I think it should be either the ‘post_max_size’ or the ‘upload_max_filesize’ property
in the php.ini file
just have a look at which one of them has the value = 5M

reallarry, I don’t think so, because post_max_size is set to 8M and upload_max_filesize is 20MB already.

hmmm….. ok that was my idea of what it could be….. than i don’t know too
but maybe an other property is set to 5M??

reallarry, It’s something stupid with the code. I bet.

ok

well it’s worse than i thought… imagettftext is useless for smaller fonts…
i wasted 3 hours for nothing

if you’ve wasted 3 hours, then you’ve learned nothign

it just looks ugly and letters aren’t properly aligned tru the text

i wonder than that files lesser than 5MB are working fine….. but it’s just wondering. I’m not sure!

random spaces between them etc.

for me it looks just like a configuration problem….. but that’s my point of view…
maybe you could test some other code to upload a file on the same server?

reallarry, trying to use a php host debugger..

hey f00li5h… meow brow

meow

how do you usually make an update.php for mysql?

a what for who now?

haha.. anyways.. i want to make an edit php for mysql tables… now.. what im doing is.. grab all the data and from the dbase and show them in the FORM. now if any changes FORM, how do you usually handle the updating, if example there are 3 out of 20 fields are changed in the form.
my first option is.
POST the whole thing, and compare it to the existing, then change only the missmatched values.
second option is, to UPDATE all fields.

and you’re doing this with all the rows in the table?

third is use a cookies or session variable to hold the CURRENT FORM VALUE, then compare from the cookies or session variables before posting. so that in the dbase, ill post only the fields that changed.
im doing this 1 row only.

you could have a hidden with the original vauels, but themn you’ll have to post the entire data set back (doubled)

yes thats my other option too f00li5h

what are the advantages of storing session data in mysql?

you should really only allow updating of a single record (unless the records are something like configuration options)

from having it in a flat file?

it may be faster, and perhaps a bit more secure, depending on

i mean any row in the table. whatever the where ctrl = X for example.
so what do you think is the best solution?
solution/approach. or is there a beter one?

i have no idea what you’re talking about
i still don’t know what you’re trying to do

im just getting the value from the mysql, then show it up in the form, now the form is editable, then i can post again the changed data in the form. so you can say its an EDIT ROW

how can i print multiple lines with imagettftext? i tried just adding a 2nd line and that wiped the 1st one…

one line at a time

f00li5h i’m doing that – just one by one

axscode, you can also use javascript
with onchange

and only the last one appears on png

have some hidden inputs, “title_updated”
and
input name=”title” onchange=”document.getElementById(‘title_updated’).value=1″

you coudl present all the records in readonly, and turn it into a form when you click on the row

or soemthing like that

then save the results with an AJAX post

oh, i’ve an idea

NO way to print two different lines of text with imagettftext?

on change.. shesek: is deleting a word example “shesek” then put back a “shesek” again in the field will trigger the onchange?

no.

i don’t really get it…

don’t put a name to the inputs, just a value, and than onchange give it a name… that way all the input’s that you didn’t change won’t be sent to the server

that makes absolutely no sense to use it then

what do you mean?

axscode, yes.. but what are the odds ?
i’d use input value=”PHP Is great!” onchange=”this.name=’name’” /

suggestion for a good IDE with php and MySql support?

well, dude, that makes it less anyway.

f00li5h i need to print 2-3 lines on a png with some ttf font, how can i do this? i mean is there a way to stack those lines and then send em all at once so they get printed?

i love that shesek.. the this.name

one. line. at. a time.

is still have that problem
if you change and change back

f00li5h what do you exactly mean by that?

call the add text function 3 times

imagettftext($im, 11, 0, 12, 25, $orange, $font, $text);

you can maybe load all the input’s values to js variables onload, and than compare to it

where can I post my code to show it to you guys?

on onload*

that’s what i use f00li5h

one time with each line of text

rafb.net/paste

f00li5h so i DID…

then what are you asking me?

f00li5h and only the last one appears on the png

[bash], rafb.net/paste

the previous line gets wiped

are they all being added within the bounds of the image?

f00li5h yes of course, i’m only adjusting the Y

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

it’s same font, everything is exactly the same besides $string and $y

can anyone look at my code so I can ask my question

hi, someone to help me with cookies?

allright, so pastebin some code

f00li5h ok

[bash], first ask
well, i’m out. you know how much a six pack should cost?

in my code I am updating a table which has description and 4 images, everything is working fine except I can’t get to upload the 4th picture all the pictures are being upliaded except the last one

if you change and change back — its ok.. who will do that anyway
uif you change and change back — its ok.. who will do that anyway/u

http://190.52.132.149/gunz10/login.txt
this is my code

okay
than great.. you’re using the name idea ?

I currently code using style 1 but I’m thinking of switching to 2. What do you think or which style do you use? http://pastebin.ca/659419

Warning: Cannot modify header information – headers already sent by (output started at dwebs\www\gunz10\login.php:2) in dwebs\www\gunz10\login.php on line 43
Cannot modify header information – headers already sent by (output started at dwebs\www\gunz10\login.php:2) in dwebs\www\gunz10\login.php on line 44

but thats a clever way, love that idea.
yeah.. the name..

thanks

thats the errors that i get
with the code above
can someone help me?

dont send any output before the header

but whats the output that im sending?

OmOiYuMe, as he said, don’t output anything. output includes new-lines, html, BOM

go and look on that line

for example link href=”css.css” rel=”stylesheet” type=”text/css” /
echo “table width=’40%’ border=’0′ align=’center’ cellpadding=’0′ cellspacing=’0′ class=’mcheader’

you can send anything, at all.

and so on
cant*

double quotes in html
‘ is not part of html, so don’t use it, attributes are quoted with “s

ok, so, before setting the cookie

…

i cant have any html code?

it was a copy-paste from his html

f00li5h just pasting.. pastebin.ca is so slow today

nothing

common misconception; you can use use either ‘ or ” to quote *values* not attributes.

sial.org/pbot # paste here
is it, where does it say?

the spec

oh crap. my eye contact just fall
damn

which one?

the html one

but how i declare the variable of the cookie if i cant do anything?

ok wher is he?!

http://pastebin.ca/659424 –this code will print only the 2nd line ($text4)

and aside, i have to check the username and pass

which html one?

I did what you told me

f00li5h is this how it should be?

“It is always safe to enclose the attribute value in quotes, using either single quotes (‘80′) or double quotes (“80″), using matching quotes of course. The string in quotes must not contain the quote, so if the data contains a double quote, use single quotes for quoting, and vice
versa.”

I created html code in template files
but now I dont know how to attach it to the php

cite?

$text1 = ‘blabla’; and then you use $text in imagettftext

http://www.cs.tut.fi/~jkorpela/HTML3.2/3.4.html

foutrelis oh lol…

it hasn’t changed since HTML 4, see http://www.w3.org/TR/REC-html40/intro/sgmltut.html#h-3.2.2

i feel like an idiot now.

Let us know if it works.
Don’t.

yeah, about to check that
in fact i expanded the code and forgot to change that $text

hrm, I see

ok, removing the css line made the trick!
thanks!

the thumb rule is that dont send anything before the header
not even a newline

works like a charm… thank you and sorry foutrelis and f00li5h.. such a lame bug
it’s just that i want to see the result so bad.. learning this stuff since early morning

I even have my menu completed

so i’m inpatient

now, i have to find a way to insert the tables with the style

So.. Which coding style should I adapt? . http://pastebin.ca/659419
or adopt..

your preferred style?

neat

does anyone got an answer for my problem

When I read people’s code, the only issue I will ever have is if they are inconsistent with their style, not if it’s one I don’t use myself.

I’ve been using the first one so far. The other one looks cleaner though.

choose whichever one you prefer.

ok.

It depends on your philosophy. The first option removes LOC (lines of code), and since you read code a lot more than you write you can read it more quickly, however option #2 is easier to read because the code blocks start and end at the same point
So really it depends on which one you think is more important.
easier on the eyes or less lines of code

foutrelis just a little question… when defining $text4 = ’stuff goes here’ —how can i add those extra spaces?
they get truncated if put raw in there
or doh.. i could just tru in another line..

can you show me how to alter my code so that everything is in this core.php?

what do you mean get truncated?

hmm you know

fyrestrtr i mean no matter how many i put in there only one gets printed

browser displays just one space

i can just rename config to core

if you look at the source you will see multiple spaces

rza that’s imagettftext

ah
hmm

I’m not sure how to put an else block with the second style. Anyways, I’ll stick with the one I’m currently using (#1). Thanks scorchsaber, Consty for your input.

so it doesn’t like those extra spaces in the string as well
any replacement? html style perhaps?  
but then… no, it won’t work

i dont know about gd
with imagick it doesnt happen
http://todellisuus.net/tests/watermark.php
http://todellisuus.net/tests/watermark.phps

so you recommend imagick?

well, my opinion might be a bit objective

may i ask how is the quality of text printing with it?

since im the lead developer in imagikc

i’m disappointed with GD2 text functions… ttf looks so ugly
rza oh

hey people – how can i find out all available values in an array?

rza so how is font rendering in imagick?

i basically have an array with a value in it, but i don’t know under which reference that value can be found

rza will i get good results trying to print a text with ttf font on png?

yes, sec
ill make you an example

i do appreciate that, really kind of you

I once played with GD. This is what I got: http://www.foutrelis.com/misc/hiddenpic/current.png I’d like to see the png you’re getting.

i just tried gd with a simple arial and 12 px and it got out ugly, with random spaces etc.
foutrelis hm

http://todellisuus.net/tests/annotate.php

well gd gives great results with pixel fonts but not with ttf ones afain

Looks nice.

rza thank you, checking
well that looks very good

http://todellisuus.net/~mikko/heart.php

and i wonder about simple arial.ttf

theres the source http://todellisuus.net/~mikko/heart.phps
let me see if i got arial installed

I used arial.ttf in that pic.

mmm handwriting one.. that was a complex example and if that’s ttf then it’s pretty good
foutrelis never looked at yours, just about to

you can see the source
its ttf

rza right so rendering is just perfect
i might switch then

Hi. I get the with the following code I get the error shown:http://hashphp.org/pastebin?pid=26832 All my folders are there so I don’t understand why it gives me the error. Can anyone suggest anything?

foutrelis that one is a bit tricky.. that’s 45 degrees and it might look good – just try 0 degrees and e.g. 12 px
also depends WHAT your text says
online
there was a big spacing between l and i
and some others too

anyone here use phpMyEdit – i’m trying to color code a cell depending on the content…
i’ve been through the docs for an hour now, and i just can’t figure it out – any assistance is greatly appreciated

hi, i’m using ubuntu linux and need to send mail using mail(), what do i need for that? (just never had this problem using windows)

postfix

a working mail server

if (!session_is_registered(‘GunZWeb’)){ – will check if the session is registered?

foutrelis i’ve tried postfix and it didn’t work out )

is not registered
sorry

foutrelis i just didn’t know how to send mail with it )

Kopcap:

php.net/session_is_registered read the caution

foutrelis how do i send mail with postfix?

Hi. With the following code I get the error shown: http://hashphp.org/pastebin?pid=26832 All my folders are there so I don’t understand why it gives me the error. Can anyone suggest anything?

I just installed it using instructions at howtoforge.com. mail() was happy.

okay, but what’s the command to send mail? ))

From within php?

well afaik i need to setup in php.ini…
; For Unix only. You may supply arguments as well (default: “sendmail -t -i”).
sendmail_path =

php.net explains that to register a session with session_register, the usage must be session_register(“barney”);

Can someone find me a working link to Apache?

but, i need to register the session with a value

a Download?
All of the ones on the Apache Website are down

They’ll be up.

use $_SESSION['name'] = ‘value’;

:
how long?
It’s been like 20 minutes.
Every single one is dead.
404

5 light years maybe

fyrestrtr if i use $_SESSION, i cant use session_is_registered

no, because then you use if (array_key_exists(‘foo’,$_SESSION))

Hi. With the following code I get the error shown: http://hashphp.org/pastebin?pid=26832 All my folders are there so I don’t understand why it gives me the error. Can anyone suggest anything?

I’m sorry but I can’t help further. I just set up postfix and php is happy. I’ve never dug into its config for sending mails.

fyrestrtr ok, so, how to check if $_SESSION['GunzWeb'] is registered?

so you just installed postfix and that’s it?

if (array_key_exists(‘GunzWeb’,$_SESSION))

http://people.apache.org/~henkp/cgi-bin/closer.cgi?ip=193.92.150.11

if (!array_key_exists(‘GunzWeb’,$_SESSION)) to check non existence right?

good morning

morning shanky

anyone mainly use vim to write php?
I use Zend to write php for a long time. and now i want to move to edit php directly on server through SSH. I wonder if it would make things slow.

how can i print all defined variables? (i.e. i know the value, but not the variable name and therefore need to just print everything that’s been defined)

kalimera

done, thanks a lot fyrestrtr

ie

kalimera. kala?

mia xara

make which things slow, esactly?

f00li5h ie?

is there a better to get $_SERVER['HTTP_REFERER']; ?

koita re. Exei kai alous ellines

f00li5h the production speed

why would it make a difference?
you should only be editing code on a development host anyway

because in the manual it tells that can’t be trsuted

there is no alternative to that

It does not require much bandwidth. It will run fine over SSH.

http://hashphp.org/pastebin?pid=26832 All my folders are there so I don’t understand why it gives me the error. Can anyone suggest anything?

i’ll say it again, only edit your code on development, production is not the place to be debugging your code

you can use zend ide to edit directly on the server

f00li5h foutrelis, um.. would switching between files, and basic text editing slower than using a wonderful graphical editor?

take a look at product called sftpdrive

ok, althoug I think the advise was not for me, I’ll take it, I think it’s worthy

if you want to edit over ssh

you can split it vim, and edit 2 files at once, or you can suspend it and use the shell’s job control

rza thanks. but do you use text mode editor mainly to edit files?

i use Kate

it was for you

its a gui editor
i find KIO pretty handy

f00li5h for this, i like the screen command

yeah, screen is good too

smb://server/share/file.txt and sftp://server/path/to/file.txt especially

I mean the advice about edit code only on development

i actually use all of them, job control, split vim and screen
that advice is for everyone

ok, thanks again

you should atleast have a seperate vhost for development, ideally a sepearte box

you should only be editing code on a development host anyway — What about if bugs are found later on? We can’t completely bug-free a project.

Hey guys. I get a “No such file or directory” when I do the copy function, but all the folders and files are there. Could anyone suggest anything? http://hashphp.org/pastebin?pid=26832

you fix the bugs on the dev host, test them, then promote the accross

Some bugs can remain undetected

then you fix them on the development host and promote them again

Even after my QA team goes through it

you do not hack on production, you’re far more likely to make it worse than better

I use a separate server, and use svk to emerge to a testing virtual host in the prodcution server and then I replace the production code

Is there an “OldFiles” folder or an “Old Files” one?

dat’s what up!

has anyone here heard of Aeron chairs?

“Old Files”

heck yes

Hi, I’m using php4 and I’m trying to search dirs recursively and process all the files in a dir. How can I go through all the dirs until I found a dir without subdirs, process the files and them continue with the other parent dirs?

But trying “OldFiles” gave me the same proble,

and yes, ssh+screen+vim is a good combination

I had one at $customers[-2] it was very comfy indeed

i want one
but they’re SOOOOO fkin expensive hey?
i wish someone made a cheap ass rip off.

it annoyed my sysadmin though, he kept trying to test things with my account, and the screen -xRR in my .profile started hastling him
lots of people do, but they’re not as good

does anyone know about online invoices in USA

your question is very vague

last one and i dont bother you anymore
if a register a session with $_SESSION, how can i destroy it?

same as you normally would

session_unset?

hey friends

a recursive function should do it.

or unregister?
no
already read

Why “s\\”?

unset()

I was thinking of that but I need to traverse all the dirs until I reached the “final” one that as only files

so?

i want to mail myself the output everything in $_SERVER – I’ve tried $msg = var_dump($_SERVER) and just sending $_SERVER, but niether work, any ideas?

his there a way to see if a dir as sub-dirs? If not fill an array with the files I want to process

$msg = print_r( $_SERVER, true );

how are you mailing it?

because I made the stupid mistake of calling my Table “Vessel” and the Folder “Vessels” I use the same code for “Operatios” and “Locations” with the same mistake

mail()

ob_start(); print_r($_SERVER); $x = ob_get_content… or that

thanks rza and TehSausage

lol ok.

php.net/is-dir

anyone prefer VIM over graphical editors for editing php?

it says it can’t find the destination file, which doesn’t make sense as I;m creating it now. Mou spaei ta nevra

Personally when using it in a windowing system I see it as pretty unresponsive.

But with that I need to go through all the contents of a dir. Maybe I can use something like “exec(“ls -l | grep ‘^d’”)” ?!

Personally I just use the native text editor of whatever windowing system I’m running.. if I’m on Gnome I use gEdit, if I’m on KDE I use Kate.

consty not running text mode?
i needa edit thru SSH

I’m confused because it says “Warning: copy(OldFiles\Vessels” but the folder is “Old Files”

Well you can run windowing apps through SSH but it’s a bit complicated
In that case I’de probably use vim.

with an text input you can set the name to foo[] to make multible several foo in an array, but don’t that work with file inputs?

Does anyone have a function that determines if a color code is valid.

sorry I tried that version too, thinking the space made a difference. I tried with and without space. Same error

just check if it contains the chars 0-9 and a-f

it wouldn’t be hard to write.

consty thanks

and also needs to be only six characters plus a #.

Do you have an “Old Files” folder in the same directory as the script?

and is of length 6

I like your thinking. Your hired!

^^

Hey ^^

Anyway whats the best for this? preg_match?

g’day foutrelis

yes

don’t forget to convert it to a single case, or at least check for A-F as well.

And in that a “sources” folder?

Allowed memory size of 20971520 bytes exhausted (tried to allocate 142 bytes)
*whimper*

does anybody know of a good tutorial on the principles of oop, preferably based in php. i understand how to write the code, just don’t understand the concepts behind it.

you know I can’t believe that a large majority of people actually use arrays as structure replacements and don’t use classes
isn’t that crazy?

you could just use ls -d */ if you just want directories.

yes. Only its $source.’s’ and not sources

php is not really the best language to start learning about oop

that worked rza thanks

Sorry I’m testing it assuming $source == ’source’. I’m getting the same error. I guess those slashes in the date are causing the trouble

Hey I dunno about that.. classes in PHP5 are pretty standardized and solid now.

I don’t think so, cause the next line is unlink($module.”s\\”.$moduleID.”\\”.$oldFileName); and it works fine

i’ve herd that, but i feel most comfortable in php. i’d rather learn what i can in php, than jump into another language and be confused by oo and the unfamiliar syntax.

PHP is *not* the language to start learning about object oriented programming; no matter what you might think about classes in php5. There is more to oop than classes.

further up I have copy($ans['tmp_name'], $module.”s\\”.$moduleID.”\\”.$qRef.’ – ‘.$ans['name']); which also works fine

what would you recomend i do, fyrestrtr?

Well, I removed the date() and it copied the file.

Of course, but classes are the pinnacle of OOP.. that’s the present abstraction used most often

that’s it
thank you

even those aren’t done properly in php5.

np

my file was faving a date in the extension
foutrelis
: thankx

You really think they’re that bad?

There’s nothing wrong with elarning a new language to learn new programming concepts.
It’s pretty common, or so I thought.

no, for php’s purpose they are fine; it is just not the right language to learn oop.

how to turn on display error for a particualr vhost?

SuperBusinessman, php_flag

display erro was set OFF
ekneuss thanks

Maybe you also should use forward slashes (/) for paths.

fyrestrtr, i’m not trying to be a super-duper-programmer. i just write some basic web apps and would like to clean up my code a bit and am having some problems understanding how i sould structure my oo code. do you know of any tutorials/examples that could help me out?

SuperBusinessman, take a look at php.net/configuration.changes

you could pick up a book on patterns.

ekneuss thanks
it’s set to OFF by default

http://www.php.net/manual/en/language.oop5.patterns.php

thanks fyrestrtr

Do you have $8 you can spend?

consty, yes.

http://www.amazon.com/Designing-Object-Oriented-Software-Rebecca-Wirfs-Brock/dp/0136298257/ref=pd_bbs_sr_1/103-6191495-6792629?ie=UTF8&s=books&qid=1187255829&sr=8-1
buy this book. It’s short (200 pages), sweet, and completely awesome. imho the best book on learning OOP the right way.
used is $4 + $3 for shipping.. $7

ha, wish i caught that earlier… i just made an amazon order earlier, that would have gotten me free shipping :-p
thanks though, i’ll pick it up right now.

Buying a $700 to get everything and the kitchen sink is fine but many people fail to work their way through it.. this stays completely focused
oops I mean 700 pages
it’s 200 pages of principles and the rest is a big case study.. it teaches you how to break problems down and all the principles of OOA&D&P
definition, responsibility, collaborations.. I’ve read a lot of books on the topic and this one maybe old (published 1990), but it’s by _far_ the best.
best part of all it’s language agnostic, so it isn’t specific to any one language’s OO flaws.

ordered, thanks.

np
That’s the book UC Berkley uses for their comp sci program.
berkeley I mean

anyone avail to help me with a bit -o php?

anyone here use phpMyEdit – i’m trying to color code a cell depending on the content…
i’ve been through the docs for an hour now, and i just can’t figure it out – any assistance is greatly appreciated

how can i do to replace every . in a string with a – ?

how can i print all defined variables? (i.e. i know the value, but not the variable name and therefore need to just print everything that’s been defined)

curious

fishsponge, str_replace(‘.’,'?’,$var);

hello
all

meow

anyone one know where i can find some text … that tells me niiiicely how to start using joomla?
never used a CMS before

Are you looking to just use Joomla or develop/learn from it?

i think xdebug might let you do something like that. i haven’t used it for that purpose, honestly, but it’s integrated into the symfony framework and symfony’s development panel has that functionality.
i think xdebug might let you do something like that. i haven’t used it for that purpose, honestly, but it’s integrated into the symfony framework and symfony’s development panel has that functionality.

yeah same
drupal has a much nicer code base

fishp_ er, nevermind. it only does that with $_GET/_$POST/$_SESSION

drupal? hmmm …

The dudes are selling hats and bags on their homepage..

just use it for now
that would be joomla?

If they sold drupal wife beater shirts I’de so be there

yes
hehe

i like the drupal logo …

I believe you’ll find Drupal easy to start with. Later on you can start poking at its code, making your own modules and customizing stuff.

taking off
thanks everybody for your help

See you later OmOiYuMe

bb

hi, i ‘m dynamically generating a html page with all sorts of information from a SQL server. Now i wont to export the html page to a word document. The problem is if i export it all the added text is not placed proper, for example a paragraph is displayed over 2 pages and so, any howto’s or
information on how to create a nice look ?

How do I get a normal date to timestamp?

how do I send a variable with header()?

help. I am trying to compile php. Getting configure: error: Kerberos libraries not found. I’ve also tried to direct it to /usr/lib ’cause that’s where the libs are… still the same error

are you sure kerberos libs are installed?
libkrb-dev or something if using apt

there about twice as much users in #drupal than in #joomla, speaks gigabytes

does it?

krb5-libs.i386 1.3.4-49 installed

How do I get a normal date to timestamp?

anyone here use phpMyEdit – i’m trying to color code a cell depending on the content…
i’ve been through the docs for an hour now, and i just can’t figure it out – any assistance is greatly appreciated
how can i print all defined variables? (i.e. i know the value, but not the variable name and therefore need to just print everything that’s been defined)

http://www.php.net/manual/en/function.strftime.php
a href=”http://www.php.net/manual/en/function.strftime.php”http://www.php.net/manual/en/function.strftime.php/a

thyko no? I want the timestamp

hi
i have a problem
there is a db table that has user fields
id 1, 2, 3, etc
and there is users tabnle
with fields customField1, customFeld2

pastebin your schema

how can i do echo $this-user-customfield(we add here id of field)
1 sec
eval(‘echo $this-user-customField’.$field["id"].’;'); – can i do it better way ?

yeah, you sure can
$field = “customField”.$field[id]; $this-user-$field

lol thanks
didnt thought about that

!+eval

If eval() is the answer, you’re almost certainly asking the wrong question. — Rasmus Lerdorf, BDFL of PHP

hehe

man
in PHP

what does it do?

say you want to create a variable with text for an email
$textsomewordhere
Hello
bla bla
blabla
somewordhere;

ah, right. I forgot it had three signs.
obviously, though, since is taken.

$text = anytext
bla bla
anytext;
that is the correct usuage
it will set $text to those two lines, with the line break any everything
time saver
what

Please use a pastebin for large blocks of code.

no ty
that was not large

uhm
topic?

never read topics
bad habit of mine

then its best you start

no time

it was not a request

calm down dude

how do I include some html in php for execution again?

include()?

aye. thanks

include ‘file.html’;

I thought it was something like insert()

hey guys, I am facing this weird thing… I am doing simplexml_load_file of RSS feed and I am able to retrieve variables that I want out of the array

where is the problem ?

but then there is this array that is acting funny..everytime I run the script it either give me string(1) “0″ or string(8) “77261881″
it should be string(8) “77261881″
http://pastebin.com/m47e11382

PHP Version?

5

need more info than that
like I’m running PHP 5.2.4-RC2-dev
I just ran it 5 times and got string(8) “77261881″

k hold on
PHP Version 5.2.1

howdy ho, scotty boy

how can I get german weekdaynames with date?

oh well, i will just move the script to the server and check it there…

rza, morning
I see nothing super wrong
with the release

neither do i

rza, give it a week and if it works declare it stable?

yes, something like that

I went to bed at 5 this morning after tracking down a nasty segfault in my new extension

a week is enogh
found the segfault?

i forgot that when you alloc a struct it doesn’t alloc space for values held in double pointers
so it was assigning a value from the stack
and everytime it left function scope the double pointer got mangled
probably because I do this coding late at night

Is it safe to have an array with 50000 entries?

sure

that sure was for me?

yeah

ok, thx

there isn’t really a limit to the number of elements in an array
if you can’t store it any more efficient way then its not bad

that’s good. I’m was trying to do this in a better way but don’t really have the time to study a better solution
I want to fill an array with all the files in a dir and sub-dirs

nooo!
it doesnt work properly
how do I make include() execute in the same page?
it goes to a new page or something

what ever you include will run, it doesn’t go to a new page

oh yes it does

oh, no it doesn’t

doesn’t

it loads ‘badcreds.php’ all by itself

include basically says take the contents of this file and put it here and continue execiting

Hey

should the include not be a php file?

err executing

… it can be what ever you want,

what are you expecintg include to do?

there is something else in your script re-directing,

hmm

or maybe the script that you are including is redirecting

I’m replacing tags by content but I have a problem when replacing numbers, instead of replacing the numbers it replaces the tags for some weird symbols

I have if($_SESSION['bad_creds'] == true) {

http://83.63.145.138/ng/ you can see what happens in the second table in the right the one that says TOP …

hey

(sigh)
I thought i had it too

how can I get ircg_ functions running? where can I get the needed extensions and how can I install?

http://uk.php.net/manual/en/ref.ircg.php

are you still trying to have login_fail as a seperate script?

holy crap
http://www.schumann.cx/ircg/ircg-2.8-win32.zip

what is “holy crap” about a zip archive?

try to get it

unlucky

login_fail?
what do you mean

the code that handles people failing to login

thats in logon_user.php
I guess I can include it in login.php
that way I can just run some code and not have to use session variables

yeah, and you have all your login code in one place

but you have to help me fix my form then

what’s wrong with it?

form method=”post” action=”logon_user.php”
thats what its doing now
if I include login_user.php in my code how do I reference it then?

well, you just have to have it post to login.php (or whatever you called your login script)

i dont understand what you mean

the script that checks $_POST for user creds

(sigh) thats what is breaking it in the first place
how can I be redundant like that

you keep referring to ‘redundant’ but you seem to be doing it all over the place

why cant the form point to a different area on the same page?

what do you mean by “area”?

maybe like a subroutine or something

you can only submit to a script, not to a function

im not including that script in login.php then

what does login.php do, besides logging someone in?
it processes exactly one action

it has php code that checks a session value to determine to show text that the creds are wrong or their account is not validated

yes, that’s part of processing a login
that’s part of deciding if you’re going to grant the user access to the site
(strictly, it’s Authentication and Authorization)

right
oh yah
it also posts a prompt or something after the include
like:
bad login

still the same operation

huh?

these are just different results of doing the same thing

no
why does it print “”

Goor morning
d

why would i get a whole shitload of “function registration failed” messages when running php-cli as non-root, but no warnings at all when running as root?

Username or Password is incorrect

thats what it prints out

mark25, likely some file access issues

oh nvm, i fixed it
damn typo

loadable modules owned by root maybe?

in my code I am updating a table which has description and 4 images, everything is working fine except I can’t get to upload the 4th picture all the pictures are being upliaded except the last one

hmm
sure, owned by root, but still mode 644

hi, how is the operator called ?

and the directory is executable?
rocketmagnet, you want it’s syntax or it’s name?

yeah

well, is php.ini readable?

the name so i can find the doc (i need to put a global variable insite) but {} does not work

I think they are called heredoc

http://phpfi.com/256582 – my php break after to many connections

ect… use only readfile, that work good

but with another commands php start to breack, and i cant open the page anytime
like server is in DDoS
i need fread and another things to limit bandwith to users
with readfile i cant do that
have way to limit bandwith with readfile =
?

stop posting passwords to us
and stop reconnection for each insert

??

and stop using globals where you want arguments
and fix those sql injctions

http://pl.php.net/manual/pl/function.fread.php

henke37 yes php.ini is readable

envp i know that, but my script is breacking

MalMen, you managed to reinvent a webserver, congratulations

after +/- 20 users downloading

all the errors are ‘deplicate name’

henke37 i do not understund what are you ttrying to tell me

MalMen, one, you posted the mysql password

:O

well that is part of the login code. theres a form that points to login_user.php that does the actual checking. if it finds that either the username or password is invalid, it sets a session cookie and uses a header redirect back to login.php

my bad
mothefuck!
henke37 thanks to tell me

hi

does not work =( if i use normal variables it works( also with {}) only the static object makes problems

why don’t you just use a template enigine for that?

it’s part of my html header creation function for my simple framework

hi
I dont know if I should ask this in the php or fedora channel.

that doesn’t answer my question

so it’s the only part that is used with heredocs
and i don’t want any other pear packages
i just want to know how to escape this right

i have installed apache and php. when I look at php_info(); in my browser… it says: “‘–without-pear’” somewhere. HOw I install pear so that PHP is fully aware of its existance?
i tried yum install pear… but then php is not aware it;s there…

did you install php with yum too?

I still can’t figure out the structure of the array that is given back by something like mysql_query(“SHOW COLUMNS FROM foo-table)
can someone please enlighten me or send me to relevant stuff online?

just var_export them

var_dump( $array );

yes… i installed php with yum too
yes…
would php go-pear.php make a difference?
if i install it like that?

thank you so much rza and f00li5h , especially var_dump was exactly what I needed

i’d suggest saving your config files, un-installing php, then re-installing it with yum too

hi everyone

logik-bomb: meow

should i create separate table for condition of car that contain only 5 entries?

php was installed with yum

php appl

does anyone knows how can I select a specific value of this array? http://pastebin.ca/659595
thanks

i know it will break normalization

logik-bomb: by giving it’s key

$_REQUEST[photoid][$key];

logik-bomb: use var_export, and it’ll be easier to see what’s happening in the structure

thanks

__construct with call_user_func… can any1 help me out?

i wonder. can abstract classes have static methods?

the default behaviour with == (two equals) is to convert them both to a common type
if you use trebble compare (== and they are different types, the comparison will fail

so 1==1.0 is true, and 1===1.0 is false?

yes

or more precisely, can static methods of abstract classes (if at all possible) be called as abstractClass

i.e
SELECT t.codigo as codigo,t.nombre as nombre, t.descripcion as descripcion,
GROUP_CONCAT(s.nombre SEPARATOR ‘,’) as secciones

GROUP_CONCAT() has nothing to do with counting.

and ends with GROUP BY t.codigo

if thereis a table and two of my programs are accessing it concurrently, would MYSQL handle the locking/unlocking on its own?

i’d like to count the registers of this query

SELECT COUNT(s.nombre), …
I don’t know what a register is.

go6|in, yes.

sorry for my ignorance
register is like record?

Simetrical, thx

go6|in, assuming that both are accessing stuff through the php mysql web hosting client, that is. If they’re messing with the actual files, no.
.. . .

No problem. You’re just using a term that is not common.

i’ll try..

Whatever you mean by “register”, you need to clarify.

what is the best way to take a daily backup of all databases on a running MySQL system?

okis

mysqldump

in linux a crontab with mysqldump

ok, thanks

if you’re going to back up your database with mysqldump, bear in mind that it may take a global lock on your database – which is necessary if you have myisam tables. This will block all writes for the duration of the dump, which may be a while if your db is large.

MarkR42, does that go for InnoDB too?

Its default behaviour is to use the big lock. If you have *any* myisam tables, you really want this.
If your database hosting is wholly transactional, you can use a transaction instead, which won’t lock your db (but don’t imagine this is impact-free either)
As mysqldump will start a transaction, which means it sees a consistent version of your tables, which is fine except it will block the reuse of that space which was used up by old rows in innodb

MarkR42, ok thanks – will stick to the default most databases are not that big anyway

hey guys

If your db is small and not critical, mysqldump’s default behaviour is probably ok

i had some problems with a couple of tables getting corrupted and returning 144 errors today

So while your app is noncritical and your db 100M, it’s fine

i ahve fixed them, and they are all good now.
but i was wondering if someone could give me some general help on how i’d go about writing a script to iterate through all tables in the DB and find any more that have problems.
error 144 is the storage engine error type thing
i just have to do ‘repair table tbl;’ to fix it

Table is crashed and last repair failed ?

oh, sorry not that one

MarkR42, one problem though – I need to specify the password for mysqldump – can I get past that without compromising security?

124 maybe.. hang on.. i’ll use perror
i think that’s what i need anyway

Same rules as for mysql command line – you can pass it in an env var, an ini file or on the cmd line, which ever your feel is more secure. It has to be somewhere.

error 145 i think it is
yeah it was

i got a table like id(pk),userid,value but since bad table design i got duplicate values on userid. I want to delete all duplicate rows, i tried alter ignore table mytable ADD UNIQUE KEY ( userid); but it deletes all latests one. i just need to keep the latest record.

it is not bad to have unique indexes other than the PK

well yes, but i just need a solution to delete duplicates expect the highest id
i can change my software to replace instead of inserting blindly.

Why did you need a primary key other than userid anyway, if it maps users to some values? For that matter, it may as well go in the user table in most cases. But that’s a side point.

its not my design, im trying to fix problems

is there any way to iterate through all tables in a database with a stored proc?

!m Silowyi date_diff

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

all i really need to do is try a query to read the table, catch error 145 and then list it so i can repair the table.
i want to do that last bit manually

!m Silowyi date

Silowyi see http://dev.mysql.com/doc/refman/5.0/en/using-date.html

I can’t think of a simple query to solve your problem, incidentally. You could try INSERT INTO tmptable SELECT * FROM mytable ORDER BY id DESC perhaps, with an appropriate unique key on tmptable.
(That should be INSERT IGNORE INTO.)

hmm what fields should a cms have again?

whenever you run a query, mysql obviously caches it for a period of time.. right?

exes, if the query cache is enabled and SQL_NO_CACHE isn’t used, yes.
exes, however, it’s generally best host to do data caching at the application level intelligently with Memcached or similar, if possible, for most intelligent memory use. Or so I’ve heard.

hi there…..I need some advice: why might a mysqld process just end running three times in one day after it had been running fine for 20days (minimum)?
http://rafb.net/p/sMyBsx90.html for above

you could check the /var/log/messages file or /var/log/dmesg to see if it ran out of memory or something
it might say something like ;out of memory: killed process mysqld’ or something

doing so
spot on, thank you
now for the problem i’m facing in that I cannot do anything to this box till tomorrow….:-/…..I’m gonna try

see, i’m useless with mysql, but i can still help hehe

it’s a virtual box and I’ll need to snapshot and move it

you know how to view the processlist yes? and how to enable slow query logging?

you mean ps -FA ?

no. if you log in to the php mysql web hosting server, you can type ;show full processlist; ‘ to see what queries are running

more SHOW PROCESSLIST

with slow query logging you can set a threshold for how long a query takes before it gets logged to a file for analysis later
if you’ve got a query running for ages, then there’s a good chance it’s a CPU/memory hog

I’ll check it out a bit. but this box needs to get moved tonight, with or without KK’s help
KK – my boss
to #xen I went, and asked the gurus there

I want to ask how can I make a SUM of a column in sql, I have a lot of registers in a tabla, I have for example user1,user2, user1, the username sometimes appear duplicated, I need to sum a column of other table that I relationate with the username
I try like select *.., sum (price) as price from table1 as t1, table2 as t2 where t1.id = t2.id group by column names..

does anyone know if one sql statement can do something like is found on new egg in regards to returning counts for subcategories
http://www.newegg.com/Store/SubCategory.aspx?SubCategory=147&name=Desktop-Memory
in the left nav?

but appear the price original no the sum of all registers with the same username

i.e. $10-$25 (24 items), Apple Computer (12 items) etc

mysql has group summary support. check the manual for WITH ROLLUP syntax (i think).

thats directed at me right?

yes.

SeaHawks7, since the categories are overlapping, I think not (unless you count one query with subqueries as “one query”).

coolio, thanks for the tip

|messiah|, i have problems grasping your problem . is the fact that there is a join involved essential to your problem ?

yes, so from what you are saying Im guessin it IS possible to do with nested queries?
byes, so from what you are saying Im guessin it IS possible to do with nested queries?/b
im just trying to figure out if they are doing this with a sql query or if they are doing that with code

SeaHawks7, well, yeah, you could do it with subqueries. SELECT (SELECT …) AS groupbyprice, (SELECT …) AS groupbymanufacturer, …

hey, I’m trying to make a “banners engine” and I was wondering… to my function I send a list of the sizes of the banners I want to get from the database, for instance “100×60, 120×100, 125×125, 100×60″ and I want to display them in that sizes order

this may be one of those rare cases in which you use enum

the problem is how do I tell the database to get two of the size 100×60, and one of each of the other two sizes (without repeating any banner)

oh nevermind
i didn’t read the question right

what I was doing was a RAND() to get random banners
but then how do I get only precisely two of the same size and the other two (for example)
ubut then how do I get only precisely two of the same size and the other two (for example)/u
bubut then how do I get only precisely two of the same size and the other two (for example)/u/b
lol

hi. i’m 87ms ping response from my db server but mysql_pconnect requests are taking on the order of 15-30 seconds to return, any ideas why?

bad DNS?

second

how would i test for that?

check the DNS settings on the mysql server

I guess I’ll have to get more banners from the database than the ones I really need….

you’d have to union them or use multiple queries as i see it

cool, will do – thanks!

yeah
most likely

is it possible that a innoDB gets slower over time as its receiving a lot of updates, and is there something to do about that?

Over time it’s likely that your entire system will operate less efficiently. That alone could impact mysql performance. So the answer is Yes, without regard to how mysql handles its resources over time.
and/or more specifically, InnoDB.

but theres no innodb specific tuning to do?

constantly monitor the system usage. InnoDB performance can degrade because of fragmentation in the tablespace, depending on your primary keys and insert/update usage

can i defragment it?

you can counter that by dumping the tables in primary key order and reloading them
but usually that’s the least of your problems
it gets slow as soon as your buffer pool hitrate decreases
for example when your data doesn’t fit in ram and more severely when frequently used data can’t be cached

what parameters should i tune in my.cnf?

!tell chrissturm about tuning

chrissturm http://www.mysqlperformanceblog.com/2006/09/29/what-to-tune-in-mysql-server-after-installation/

lol, a broad question

well not much for innodb
raise log file size, set log buffer to 32M, buffer pool to 80% of ram on a dedicated server, thread concurrency to zero, disable XA transactions if you don’t need them

Sometimes just rebuilding (fragmented) indexes can have a positive impact.

just drop the index and create it again?

Possibly. Sure.

or is there a better way?
thanks guys, thats all very helpful

hello. in these results: http://dpaste.com/17582/ is there a way to only grab the bottom-most allow and deny? in this particular case i want it to only return row id 1 and 4

is there any way to know how many rows a script php has processed till this moment
?

at which point?

keep a count?

inMute, I forgot to do it in php and I can’t stop it
it is processing 5.500.000 rows

suck.

I was meaning by using the mysql’s console
somthing like show something

nope
what are you processing?

If you are committing each so many rows, you can just do a normal query, like select count(*) or something
If there are uncommitted transactions, or it’s an atomic operation such as insert … select (even with autocommit on), you can’t do it really.

doing a count() I can know how many rows the script is dropping but not How many it has already processed

actually you can

can what?

you can change the trx isolation for your session and peak into other sessions
if they are doing inserting
@ MarkR42

wfq well if you are using myisam, txns are irrelevant

I know they are inserting but I mean I would like to know if it has already processed 2.000.000

how do i fix this?, i am trying to set a table field to primary so i can give it an auto +1 , Duplicate entry ‘0′ for key 1 is the error i keep getting

wfq, so SELECT COUNT(*) from the table periodically, no?
FFForever, there are multiple rows that have the value ‘0′ for that column. PRIMARY keys must have unique values.

ohhh yeah
THANKS!!!

Simetrical, but this is going to give me what the script has concluded till now but does not mean how many rows has already processed

thanks it worked

wfq, well, no, but you can work it out.
At worst, you can calculate the rate of insertion and work backwards to get a rough total figure.

but imaging the script is validating emails, it could be just one email in 5.500.000 was a bad structure and imagine it is the last one

hi

hello doug

I just installed MySQL, and I have a few questions about it.

shoot

How do I configure MySQL?

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

Do I have to forward any ports on my router?

no

if you want to access it from the other side of your router and your “router” does NAT you will

Are databases stored as .sql files on my computer?

no

.sql files are scripts

I’ve had to downgrade a server hosting from 5.x to 4.1 due to a customer’s script compatibility issue.. It seems to have gone very well, except that all tables’ autoincrement values seem to be reset to 0 (which causes problems with inserts).. Is there a
way to reset the autoincrement values to sensible values?
–create-options –compatible=mysql40 –all-databases)

lifeforms downgrades typically require using mysqldump and compatible option
ah
hm

everything works well, grant tables, reading and updating, it’s all nice

where are databases stored?

what does show create table look lik eon one of them?

except inserting (first time I got id=0 which was funny)

Doug52392 I think you need to find a tutorial

(but the second time there was a conflict )
not much special.. http://u.lfms.nl/show_c.txt
apparently the auto increment was lost

i’m new to databases, i am making a web server and a few web applications I put on it needed MySQL.

aha, interesting, auto_increment doesn’t seem to be in the dumpfile either

um
yeah
what does create from the sql host file look like?

CREATE TABLE `xcart_orders` ( `orderid` int(11) NOT NULL,
the autoincrement attribute is gone!
so that’s an explanation

bingo
maybe skip–create-options
just do the compat and all db
and do –no-data the first time for a little test

will try

does it make sense to change the isolation level of an long running update that occurs quite often in the background to something less strict?

anyone know if ADODB caches table structure? I’m able to run a query direct on the server, but not through ADODB/PHP, help?

booo
even without the –create-options, the mysqldump left out the autoincrements..

i just set up MySQL, how do I create a database?

thats too basic a question, no one here knows that.

guess I’ll write a shellscript to select the max(id) of every table and alter its auto_increment to that+1..
my maintenance window is still 8 minutes long so that seems like the best chance :’)

I’ve got a mysql slave that’s 5.0.45 (Debian-1), slaving from 5.0.24a (Debian-9) and the slave is failing on an ALTER TABLE table ORDER BY query
I can’t find any reason in the docs for this query to succeed on an earlier minor version, and fail on a later. Anyone have any input?
ahh, it doesn’t like my ORDER BY LENGTH(column)
Anyone know why this regression happened?
SMF (Simple Machine Forums) appears to use this syntax

do i need to set a password for the anonymous accounts?

If i’m not doing any math with numbers, is there any good reason to put them into a db as int rather than varchar?

yeah!
sorry Como|Lappy, wasn’t talking to you

Como|Lappy itns are smaller

they take up less space on disk, and index lookups will be faster… columns will be fixed in size for them

2 second delay, i diddnt think you were, lifeforms

VARCHAR takes up a few bytes for length, and then a byte for each digit

hey guys

INT would only take up like 4 bytes total

hm. Well i’m storing some exif data, this peticular one is aperture, which could be anything like 1.8, 3.2, 5.6, 8,11, 22, etc
i have to admit i’m not real good with mysql, will decimals mess up int?

plus it’s nice for your/co-workers reference later, they can easily see from the table that non-numbers shouldn’t belong there

is there a page at mysql.org that shows the different resource connection types?

yes
an integer implies no decimal places

i’m getting a return of resource(6) and I cna’t find anywhere that explains what that means
and no error returned

that’s like… middle school math

there is a decimal type
integers are whole numbers

…you have a point there

apertures could be any number with a lot of numbers behind the decimal point, so you probably want to use the FLOAT type
although float can be nasty
but I don’t know your application

it shouldnt ever have more than one decimal place

then decimal is the best

so just aperture decimal(2) or something like that?

hello, my mysqld won’t start, no error message just “failed”. where do I begin to find the cause. everything was working fine until I tried backing up a db, then mysqld crashed. Now it won’t start.
I’m running debian etch

@sdondley look in the log file

dang503, it’s in the bin format

I think the syntax was decimal(10,2) or something where 10 is the total number of digits

mysql-bin.000175
how do I read that?

your log file is? its not a text file?

mysqlbinlog

dang503, I have a mysql.log file but it’s empty
I have a mysql directory in /var/log but it’s full of these bin mysql-bin.* files

how can one concat all the field values of a tuple into one single value, without knowing the field names?
can anybody please help?

what you could do is do a mysql_query(“show columns from yourtable”) first to get the column names
and then construct a query from that

yeah i know… but i was wondering if it was possible to do it in a single pass :/
well it’s more of a general problem

I don’t know if there’s a clever way

i was happy to discover one can “access” columns without knowing there names in the ORDER BY clause
like, ORDER BY 1 would order by the first column, if i understood correctly
but apart from that…
i havent found how to do tablename.1 or tablename[1] for example
hmmm, would it be possible then to combine this “show columns” with the original select via a subquery of some sort?
I read that php mysql web hosting is capable of interpreting the output of SHOW as the output of SELECT

I don’t see how I could do it in one query (with subqueries)..

but when I try “SELECT … UNION SHOW…” it never works :/

but I’m not much of a SQL king

dang503, got it back working
looked in syslog
mysqld already running, just killed it

ya?
ah
do you now have an initialization script?
that’s easiest to just do mysql restart
err
mysqld restart

I am not sure what user name i am supposed to be using to configure or use mysql. Which user name do I use?

i’m not familiar with debian, but rhel is that way, /etc/init.d/mysqld restart or use sevices

hello?

SELECT IF(rgt = null,0, @myRight := rgt) from cats where parent_id = $id
Why this query in all case returs empty set?

what kind of variable should i use for comments? could be a few sentances

hiya! Would it be wise to make a recording in the database for every view to my page?
Right now I record their IP address..but that’s one INSERT INTO statement per page view
I don’t know if it’s expensive or if I shouldn’t be doing something like that.

what?
Sure, why not, insert is fast

‘Access denied for user ‘root’@'localhost’ (using password: NO)’”

okay

Doug52392, you need to use your password.
Palish, it really depends on the size of your site.

i’m building a table, not sure if varchar(5000) is a good plan, if theres something better suited for bulk text

You may want to prune down the table every once in a while.

what about httpd log? may be less expensive just to use that, and parse the log file when needed.

It could grow very large.

the TEXT type is better, varchar only goes to 255

yeah..

Como|Lappy, MEDIUMTEXT

ah, thanks

lifeforms, VARCHAR goes to 65535 as of MySQL 5.

sweeeet

how do i use my password?

but then a 5000 byte string still wouldn’t require a mediumtext
mysql -u root -p
but I think reading a basic howto would probably be better..

you’re right, the httpd log does all that.
okay, thanks

if varchar goes past 50,000 now, should i use that, or is mediumtext / text more effecient or less problematic?

just make sure it’s logging what you want. look at httpd.config in /etc

yeah

a little rusty, but … yeah

ip address, request uri, browser type.. it seems to be all there

yep, default is usually what most of us want.

Como|Lappy, I dunno. Usually I see TEXT types used, but mostly apps are still trying to maintain compatibility with MySQL 4.

hm. I’ll give varchar(65535) a go and see what happens

hello. i had an old server with mysql 4.1.11, and I need to migrate all the databases to a new server that has mysql 5.0.32 . Please give me some hints/pointers on how to accomplish this thanks.

mysqldump, I would hazard

hmm, before i got your answer I just copied /var/lib/mysql , installed mysql5 , and followed http://dev.mysql.com/doc/refman/5.0/en/mysql-upgrade.html . things -seem- to work

I’ve had no problems with just copying over the dirs from 4 to 5

i was afraid of the voodoo chicken , you know

now, going back is another issue

That ahs worked for me *but* do please run mysqlcheck on the DBs!

yep, that’s what the good manual ^^ sais as well
thanks

If i have a user entry for the user ‘como’ in the table user in the mysqldatabase, can i just grant all privleges on database.table to como;, or do i need more syntax here?

is there such thing as sub functions? im trying to JOIN a table that looks like this: http://bpk.deepdream.org/mysql-joinprob.jpg the `iduser` is the common and my goal is to show first,last,age and on/off PER entry… not a entry for
each value… Any hints? im super lost

hm
what’s “INTERVAL 1 MONTH” really mean? Does it mean the 19th each and every month?
or just 30 days?

oh, i see… ive got the password syntax wrong
hm. Whats wrong with this statement? grant all privleges on moar_photos.* to como@localhost identified by password ‘comopass’;
err, i think i might have it

If I have an integer field, how would I decrement it
er
Oops.

Hello.
where is the “data’ direcotry located on fedora?

If I have an integer field, how would I update and decrement it until it reaches 0 and then stop?

when i JOIN my query doesn’t display entries that have no data in the joined table.. .how can i display all data? whether or not a particular entry is NULL?

/var/lib/mysql probably

yep, thank you very much

bkeating:

SELECT a.id, COUNT(b.a)

FROM a LEFT JOIN b ON a.id = b.a
GROUP BY a.id;

thanks, i’ll study that query
just not familiar with COUNT

It may not be exactly what you need, but you do need to do a JOIN.

ds

Try and it see the results though.
http://www.tizag.com/mysqlTutorial/mysqlleftjoin.php
http://www.tizag.com/mysqlTutorial/mysqlcount.php (although depending on what you’re doing you may not need it)
It’s worth knowing.

hi, i can access my MySQL server on my computer, but I cant access it from any other computers. What is wrong?
hello?

oh crap
Can’t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock’ (2)”, any help PLEASE?

i cant access my mysql server from anything other than the mysql programs on the computer where its installed

windows? and socket?
!perror 2

No such file or directory

well I replaced the entire directory
do I need to reinstall mysql now?
hold brb

HELLO PLEASE HELP
mysql_connect() [function.mysql-connect]: Access denied for user ‘WebChessUser’@'localhost’ (using password: YES) in /home/oxfuego/public_html/webchess/connectdb.php on line 7
WebChess cannot connect to the database. Please check the database settings in your config.
uWebChess cannot connect to the database. Please check the database settings in your config./u

can anyone help me write a sql code to delete all entrys after a certan date?

hi

you are not help me

is it possible to get the rows from a table even the id to be inserted in another one? The origen tableis auto_increment and the destination is not

OX your problem is you have the wrong user name and password

hmm

what the quiestion

hello, anyone know why a DB with two columns of #’s I can’t run a query like “select * where 12345 = columna and 12345 = columnb
i can do select * where columna = 12345

are you getting an error?

i need a sql code
that will check the date field

nope, just empty result set

and delete everything after a certin date

try putting the column name on the left side of the comparison integer
how are the dates stored?

ahh.. smart, i will try it

2005-05-16 12:28:20

DATETIME type?

sorry
yes
haha

you’re familiar with WHERE and comparisons?

its been a while but mildly

are you using mysql 5
or earlier?

SELECT * FROM `blocks` WHERE `network` = 168255744 AND `broadcast` = 168255744 — see, the # i am putting in is definitely in at least one row on the network column, and yet i get a empty result set, I have to be doing something wrong

MySQL version 5.0.27-standard

MySQL doesn’t lie. Check your data again. Pastebin the sample row that it should have picked up

seekwill, that’s what I thought.. lol.. will pastebin the row…

DATE_SUB(NOW(), INTERVAL 5 DAY)

would match anything older than 5 days ago

is there any way to insert rows with data as D’Sousa. I know I can escape then manually but this is done by a script

No

seekwill, smokestack, http://pastebin.com/d20c9b8a2
that’s the row, i don’t know how it can NOT be matching it

Your broadcast

your broadcast column is greater than the integer int he query

man, now i feel really dumb
lol
so i need to say 12345 = `broadcast` eh?

Try it

Is there a way to export just the SQL to create a table, none of the inserts?

Yes
There is a –no-data flag (or similar) in mysqldump

Does that work on OS X?

Should

k

seekwill, lol… still doesn’t work, i guess i need to think a little more about this
(no i get about 60k rows out of 80)

greater-than-or-equal to…. I find putting the column first makes it easier to understand
WHERE broadcast is equal to or less|greater than …

smoke
haha
i wasted all that time
when i just coulda found the last real entry
and deleted everthing with a greater ID number
thanks for the help however

np

SELECT * FROM `blocks` WHERE `network` = 168255744 AND `broadcast` = 168255744
bSELECT * FROM `blocks` WHERE `network` = 168255744 AND `broadcast` = 168255744/b
still not quite right…. perhaps
but maybe it is, problem with storing IPs as integers… i have no idea what the hell they are
LOL

Use INET_ATON

what does that do
i thought that just reversed it?

Yeah
But it would make working with IPs easier.

hrmm
true…
I see.. that worked well actually
pulled the large blocks and the exact block, perfect
thanks a lot seek
seekwill that is, i must be on too few hours of sleep

hi all
what’s the difference between utf8_bin and utf8_unicode_ci?
i need to store all types of languages.

hi
what is wrong with that? DELETE t1 FROM table1 t1 JOIN table2 t2 ON t1.idt2.id and t1.email=t2.email;
mysql does not responde

… FROM table1 AS t1 …
maybe?

I don’t think so because of I ha change the delete for select works fine

did you try it?

can someone tell me how to lower the priority of the mysql process in windows? this is getting out of hand

danggun but then mysql threw up an erro didn’t it?

I am on a development machine, and if i use a single query that it doesn’t like
it starts using 100% CPU

i don’t know if i’m right, but you might as well try it.

and even though the process priority is Medium, it nearly completely freezes the computer

danggun, I am trying but I have the same problem

we’re talking like.. a 5-10 minute wait to get the Ctrl Alt Del window
a minute to click the MySql.exe process
and another 2-5 minutes to kill it with Delete + Enter

i don’t think changing the priority is going to affect that. priority doesn’t mean “use less cpu” it just means “if something else is more important than me, handle their request before mine.”

Can anyone tell me how to do a query which has a one-to-many join, which only return a single row for each of the first tables records…. but with items of the joined table being assigned to a field. Eg. SELECT (results of join) as blah …

sounds like there is another issue at hand.

danggun, yeah but in all other experience, a process using 100% cpu at normal priority doesn’t do this
danggun, hmm

sorry. i’m not sure then.

select * from torrent where TIS in (select TID from episode where AID = 739)
i dont see anything wrong with that
the inner query by itself reurns only 23 results
and the torrent table is only ~2000 long
it finially finished
27 rows fetched in 0.0439 (584.861s)

hehe yikes. sorry i can’t be of more help.

hmm
i wonder if anyone else has any idea
that 0.0439s (584.861s) is looking mighty suspicious

is there a field in torrents that can be compared directly to AID in episode?

smokestack, torrents has TID, URL, and a downloaded flag
episode has EID (ep id), AID, TID
series has AID, name, description
my app works its just that when i go to try to run some manual querys out of interest it often ends up happening
but thats probably becuase it leaves very little for SQL to do
most of the logic is in the app, not the querys
i’m an SQL newb, but the main problem lies in the fact that mysql (at normal thread prioity) freezes the whole machine that badly

in theory this subquery should insert the same id for the insert as the select’s one
$sql=”insert into table1(id,email,qty) select id,email,count(email) as qty from table2 group by email having qty 1″;
the table1’s id is not auto_increment

since you hardcode the value, it cannot auto_inc, put a null
or remove it from the insert col, and select col

kimseong, but I need both has the same one

How do I kill a given process/query having done “show processlist”?

KILL 12345;

“kill processid;” right?

I need the insert get the same id’s value due to later I will make a delete by differents ID

Ah, nevermind, I misinterpreted the response.

after the group by without id, the id is just a random row value
there is a KILL QUERY in newer version that keeps the connection

thanks again for the help, seems to be working well now!
can search 80k ip blocks… wooo
can see if an ip is routed without manually “sh ip ro” over and over… so cool

anyone know why SET NAMES ‘utf8′ throws an error?

is it set names utf8

so now apostrophes?
now = no

try it, cannot really remember

will do.
i see what i did. the code completion in my editor added an extra apostrophe.
do you work with UTF8 much? do you have any idea why UTF8 data going in would be stored (and returned…) as %uD55C?

is it possible that DELETE t1 FROM table t1 JOIN duplicate_email t2 ON t1.email=t2.email and t1.idt2.id; takes 20 minutes for a 1.000.000 rows database?

I’m porting some code from a mysql 5 server to a mysql 4 server
how can I do create or replace view..

store in binary value, 1 to 3 bytes
return?
in mysql the table utf8 data is converted to connection charset for processing and then convert to result charset to be sent back to the client

yeah. i’m really confused about properly setting up UTF8.

stick to 1 charset for eveything and it usually easier
no view in mysql4

is a charset UTF-8 or ko_kr?

kimseong,
kimseong, I mean.

charset is a collection of symbols and a mapping to a binary value

kimseong, now I have to change the app to not use views

i see i see.

oh well

the symbol is for human, the binary value for the computer

I have executed that query with 4 or 5 rows and works fine but I am wondering if mysql comes down with a million

right now i am using utf8_general_ci for collation. is this correct?

danggun there is no right or wrong, just is that what you want?
should still work, but may take a logner time

i want to display, Japanese, Korean, English, German, etc. what collation type is most appropriate?

Can anyone help me with this? This query gets a list of a nested-set tree but I don’t understand the relationship in the query between node and parent. SELECT node.id, node.name, (COUNT(parent.name) – 1) AS depth FROM classified_categories AS node, classified_categories AS parent WHERE node.lft
BETWEEN parent.lft AND parent.rgt GROUP BY node.id ORDER BY node.lft;

kimseong I know, but it’s taking too long I think

do you have index on t2.email ?
there is no single collation for so many langauge together

no, I don’t. I have not generated any index
why? that sounds fine
to remove fron index instead of data directly?

no index, means t2 may have to be scan in full for every row in t1

Indexes are magical fairy dust that must be sprinkled across your database in the preordained matter if you don’t want it to choke and die.

hmmm… that’s not good. although it is good to know!

hi all. i have a DBF file that has a column of type memo and i’m trying to convert it to mysql. when i do the conversion the column is null

kimseong, but could I create an index over the primary key instead of foreign key i.e?

what is the appropriate mysql equivalent to DBF memo types?
DBF = Microsoft database format file

now to demonstrate my poor Korean skills… ¬iÈä.

i am not korean and i don;t know korean

haha whoops. where are you from? that name sounds very korean.

what is memo type? a very long string?

wfq, the primary key already has an index, by default. You need to add a new index for every column you want to retrieve by. E.g., if you have queries like WHERE x=7, you need an index on x for them to be efficient. If it’s on WHERE y=7, you need an index on y.
wfq, an index on one column cannot substitute for one on another.

I have a great deal of trouble coming up with queries that are grouping and counting…
SELECT DISTINCT field_id, value FROM contact_values ORDER BY field_id

this does not group

I want to do that essentially, get all the distinct settings, but I’d also like a count.

group by and count(*)

So I want, field_id, value, and count of entries with that field ID.

Simetrical DELETE t1 FROM table1 t1 JOIN duplicate_email t2 ON t1.email=t2.email and t1.idt2.id; so Should I do it over t2.email then?

index t2 either (email) or (email,id)
2nd should be faster, but index is bigger

Kimseong, and will this make my query much more faster?

wfq, hmm. That will probably require a table scan no matter what you do, but an index on t1.email or t2.email will make it faster.

test it

well, the query has been executing for about 20 minutes so I am scared to stop it know but I will do it anyway

SELECT DISTINCT field_id, value, COUNT(*) AS total FROM contact_values GROUP BY value ORDER BY field_id;
Yeah that does what I want…

wfq, stopping it in the middle should have no bad effects. How big are the two tables?

are you sure?

Well, not totally, but it does with the test data.

Simetrical, the table1 could perfectly be 1 Gg table

wfq, what about duplicate_email, the second table?

Simetrical, this table is 10 % more or less of the table 1

maybe better to create a new table with the rows that you need and drop the t1 table

Hmm. Not sure how MySQL optimizes deletes, but that query will mean that it has to look through at least one entire table.

how much performance could i have if am make these index for email in both table?
yes, it has

1 is enough, probably better on t2

wfq, you don’t need it in both tables, only the second one in the join. It’s hard to say, but it will be much much much faster than otherwise.

I think so, but Mysql only looks for the smaller table

It will still be inherently slow, it has to look through the whole table once.

it will always need to read 1 table in full, no matter what

ok, let’s go then

since there is no where condition to filter with

Kimeseong, yes

wfq, note that adding an index will also take a long time.
It might take two hours on decent hardware if it’s done by sorting.

In SELECT DISTINCT – can the comparassion be made non-case-sensitive?

wfq, if it’s done using keycache, it could take two or three days to add an index. At least I think so.

…nevermind, it seems like it is.

That’s how long it would take to repair a table that size, roughly.

but If I have understood fine what you guys say and if Mysql is intelligent, if I make a index for t2, Mysql is going to go through the t2’s index wich could perfectly fit in the key_buffer as tables are Miysam

a cover index (email,id) would be good

Yes, that would be best, probably.
But ideally you shouldn’t run that query at all. You should find a way to avoid running it.
Is this a query you only need to run one time or many times?
Anyway, I need to go to bed. Good night.

Simetrical, why? how can I do it then?

Hello All, please tell me someone is here

nope noone

Too bad

who is someone?

Having PHP trouble…not necesarily mySql, but its simple enough.

my mom always said I was somebody

nabfphp

We are not a backup channel for #php.

Is there a #php in freenode?

why not ask ##php
it’s actually ##php but #php redirects

Masq 6 18
Masq 6 16

sorry
pasted in wrong channel

Thanks, got it.

hi all
is it possible to alter a mysql server configuration, so that my mysql-server accepts a comma as a decimal delimiter?

may i ask why?

for german?

of course. i am from germany, and we use a comma as delimiter
yeah

yes it is
i think. although i’ve never done it
have you googled it?

no
, is used so much in sql statement, it will cause confusion

LC_MONETARY=de_DE
?

select 1,2

that’s true

is “LC_MONETARY=de_DE” the solution to geht mysql to store a decimal value with a comma instead of a point?

isn’t there a german mysql chan?

the decimal point is not stored anyway

@chadmaynard: good question. will check that.

there is #mysql.de i know

@kimseong: it is stored. because 5.555,99 will be stored as 5.55
@chadmaynard: thx a lot

i think he was being technical

ah… did not know that, cause i am pretty much of a newbie at mysql

me too

sql statement is a string
I wonder how oracle or sql server handle that

I’ve heard people talk about it around here but never really cared so didn’t remember it

oh hey chadmaynard

hey thumbs
what it is?

como este amigo?

none of that! I took a spanish test today
not in the mood

sorry

lol
asi asi

I have a tendency to speak spanish lately
muy bien

tu puedes!
i love to say that

hahhahaha
it has a nice sound to it

Me gustaria ir a la bano

you got me there :/
thumbs–

yea and you can’t translate with no accents
AH HAH

I understand 1/2 of those words

== I would like to go to the bathroom.

oh
I never use any tools, for your information
my memory just sucks

oh

as in , I can remember most of the words just fine. But you can throw me off pretty easily

pues….. las herramientas existen para ser utilizadas
I’ve taken like 5 and a half spanish courses now

more than me
teach me, then

what have you had? 2?

1.5

ohhh
so you can only speak in the present tense?

pretty much

yea that always sucked
I can type it a lot better than i can speak it though
especially to a native speaker

hablas basico espanol

lol

ok, stop laughing

ami tambien, hombre
sabes males palabras?

:/

(do you know any bad words?)

my drunken mind can’t compute this
no
ok

deseas tener sexo con un zanahoria? si o no?

chadmaynard++

chadmaynard–

I have to fold

you have to say si or no
first

what error message have you got (.err file in the datadir or wherever debian puts it)

no

damn
i asked if you wanted to have sex with a carrot
anyway
bedtime

beunos noches chadmaynard

asta manana

I got the carrot part

hasta*

I had no idea what it had to do with anything

everything is related in the presence of chadmaynard

I don’t usually do carrots
anyway

bye

see ya

hi all, when using describe table, what are the possible values of `Key`, so far all i’ve seen is PRI and MUL

UNI?

ok, is that the only other one and what kind of key does it correspond with?

a unique index

hi folks. when initially installing mysql, using ‘mysqladmin -u root password password’, that sets the password for localhost, and the server’s fqdn, right?

Thought it was just localhost

well, when i do ’select password from user where user=’root’;, the hashes are all the same, and i didn’t do anything extra yet
s/password/mysql.password
it made me a little leary, so i thought i’d ask

Hmm
Might want to check what hostnames are specified in there

there’s 3… localhost, 127.0.0.1, and my fqdn

any suggestions for mysql back ups?

reason i noticed, was i did ’set password for ‘root’@'all_three’ = blah, and it said ‘0 rows affected’

auto backups*

Binary logging, mysqldump, and cron

morning

morning

when i do an jdbc prepstatement.executeUpdate(); how do i single out the SQLException’s that does not imply that the transaction failed ?.
ie, ( some kind of io error after the transaction is done but before response is sent etc.)

theres a sqlstate

yes. im looking at that long list
of states.
1047 ER_UNKNOWN_COM_ERROR
do we know what that mean regarding transaction success or not ?.
bah. domon is sleeping ? P

no, why not use an actual transaction?
who is domon?

eh. domon is jdbc developer at mysql

oh yeah?

well he might do different things. but hes involved in jdbc too.

hrm… i wasnt aware of that

he helped me propagate a minor into the jdbc code once. nice with devs that treat people good
minor fix.

are you sure it was domon?

bah im too tired to even spell basic sentences
yes, he gave it to mm.

maybe he’sjust some guy that hangs around the channel

no hes full time employe at mysql.

maybe you are thinking of domas?

im quite sure it was domon. he was op back then too.

hrm, Ok.

but it really is of no importence. im just glad hes able to chat about the jdbc code.
real transaction. its not enough to rely on the innodb transaction by using prepstatement.executeUpdate() ?

not using autocommit

well i do miss the transaction part outside of the innodb context /. ie IO

huh?

even using a commit() does not protect from a transaction to go through but something going wrong before the client gets the response ?.
ie connection interupted or whatever.

lemme see

ie, upon io exception. can the client deduce the state of the serverside transaction ?. might be tricky.
or its needed to ask the DB in a follow up query to retrieve status ? (.

you cant do that on a normal transaction

ok. so on what kind of transaction can i ?

an xa transaction can be completed at a later time if it disconnects after its prepared

or i simple have to deal with the problem by interpreting the non conclusive status codes as negative ?.
hm your right.
id rather skip the XA transaction complexity for now at least… but good to know its the only way.

if (SQLError.SQL_STATE_COMMUNICATION_LINK_FAILURE
..equals(sqlException.getSQLState())) {
throw SQLError.createSQLException(
“Communications link failure during commit(). Transaction resolution unknown.”,
SQLError.SQL_STATE_TRANSACTION_RESOLUTION_UNKNOWN);

sweet

thats in the driver code

yeah im lazy. i could check myself /.
my apologies hehe

so you can base your program from that

thanks for the help.

10s

you can hint the order

ah … how?
(i’ve added half a gazillion combinations of indexes … find the right one, works for a day, then wham … order changes)

!man select

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

does anyone know a way to remove a duplicate rows (which are in a termporary table) very quick.
I am using DELETE tbl_name FROM tbl_name t1, tbl_name t2 WHERE t1.userID=t2.userID AND t1.eventID=t2.eventID AND t1.ueventID t2.ueventID
but this takes long time

STRAIGHT_JOIN forces the optimizer to join the tables in the order in which they are listed in the FROM clause. You can use this to speed up a query if the optimizer joins the tables in non-optimal order. See Section 6.2.1, Optimizing Queries with EXPLAIN. STRAIGHT_JOIN also can be used in
the table_references list. See Section 12.2.7.1, JOIN Syntax.

aha!!!!!!!
jbalint, i owe u a entire truckload of beer if this works

ok, where do you live?

south africa

hrm… not sure when i’ll be there next

where u?

Wisconsin, USA

works!!

woo

thanks man

Anyone here who is good at optimizing SQL code? My current query is SUPER SLOW and im not even finnished with it yet

the database server is supposed to do optimization

how do i create a database and paste it on my linux server.

paste?

i m testing drupal
and it needs a datbase name to start

check the installation details

I’ll paste it as soon as I got it readable in the pastebin.. 1 sec
Anyone know a good pastebin? The one im using is fubar

installation says http://drupal.org/node/128371

is there a manual or an installation guide?

http://pastebin.com/d78589ac1
Keep in mind that the query isnt done yet
But already takes forever + then some

I will let the server optimised for you, do you need such a complicated query

Feels like I do.. got a much faster query today with some javacode to do some of the work thats timeconsuming for the SQL but I cant order by columns in my search then

you can divide and conquer, take a look at tab1 first on itw own
s/itw/its
note that when you join tab1, tab2, tab3 – no index can be used, since derived table has no index

Think I would speed it up if I find a way to access case_id from outside tab1 but not good enough to figure out how that is done
getting the latest status is the most timeconsuming part I think
cause its a 3 table query

select … from (select .. from a) as x, ( select .. from b) as y , (select .. from c) as z where x.?=y.? and y.?=z.?

Yes. but need to from (select outside reference ) as x

can it be rewritten as select … from a,b,c where a.?=b.? and b.?=c.?

The problem is that the status is a limit 1 expression from another table

this is the installation guide

i don’t see a limit 1, that limit 1 is in the SELECT part, that still stay

and I dont know the test_bed’s on forehand so need to where bed_id in(select bed_id from test_bed where versionstr = xxx and product = yyy) and order the reponse from a column in that table

i am referring to tab1, tab2 , tab3
maybe i missed 1 table

need inner select cause otherwise I will get multiple responses/case_id
I tried to make it like select … from a,b,c where a.x=b.y and … but havent found any way to do a limit 1/case_id that way

latest_bed_id is a column alias or a table alias?

it’s bed_id

the limit 1 is in select …. (select … limit 1) … from a,b,c where … is this correct?

Yes

then that should work fine, by right

got a column named “status” in table test_execution.. like following:
case_id bed_id status
1010 1 ‘Passed’
1010 2 ‘Failed’
Want the latest status

this is the installation guide 2.create a drupal database http://drupal.org/node/128371

i did not check through eveything, but only looking at the pattern of the statement

and that depends on test_bed table delivered_at in the correct version:
bed_id name version product delivered_at
1 one ‘5.0′ ‘MZ’ 2004-01-01
2 two ‘3.0′ ‘MZ’ 2007-01-01

i am not interested to see the detail, but I believe it is the same
from (select … (select .. limit 1) from tc ) as tab1 , ( select .. from test_execution) as tab2 , (select .. from profiles ) as tab3
from (select … (select .. limit 1) from tc ) as tab1 , test_execution ,profiles
sorry
from (select … (select .. limit 1) from tc , test_execution ,profiles should be the same
wait, wrong
select … (select .. limit 1) from tc , test_execution ,profiles should be the same

is there a way of counting how many rows, but only counting each group as one row when using group by?

mm. tryig
trying

group by .. with rollup

never heard of rollup. i’ll look that up, thanks

maybe that is what you want, maybe not

ok thanks

Managed to speed up the query from 45 to 4 sec removing tab2-3

I have a table ‘id2tablename’ and then four tables which map a given id to various data. So when a request for id 123 comes in, I first have to look up the table, then do the query on the correct table.
Is there a way to do this all in one query

hmm dunno it loks like it adds up rows
i just wana see how many there are
but i wana count groups instead of rows

distinct?

i’ll look that one up, thanks

the number of rows return by the query is your answer
1 group 1 row, so same as number of rows

hmm ok, guess my paging script is wrong then. i’ll take a look at it

you limit?

im trying to count how many rows there are and then limit between two values to get pages
but im getting 2 blank pages
so i assumed it was cos im using group by

there is a SELECT SQL_CACL_something and found_rows()
!man select

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

thanks

SQL_CALC_FOUND_ROWS

ah that looks interesting

On a non-production box I upgraded from mysql 4 to 5 w/o doing anything special.. The db wasn’t stopped during the upgrade and I think on close there was corruption.. (Checking for corrupt, not cleanly closed and upgrade needing tables..) myisamchk /var/lib/mysql/foo/*.MYI If I’m clearly
breaking anything.. someone please let me know

how to upgrade if it is still running

hey, is there a free tool for creating ER diagrams running linux?

hello

tried mysql workbench for windows, looks alright, except it just deleted my whole db…

i have big utf8/latin1 problems … i tried: HOW VARIABLES LIKE “char%” … i see that everything is utf8 but character_set_filesystem is binary and character_set_server is latin1

or maybe another visual query designer…

could that make a problem?
in browser the data from the db has the wrong charset, i see ? and not Ä … and in phpmyadmin the data is correct

probably because of the charset, maybe your browser thinks its an iso-8859-? something
where it should be utf-8

so that character_set_server = latin1 is not the problem?

only if its the latin1 stuff that shows up wrong

when i create data in my browser, send it via ajax and php to mysql db …, and then request it via browser the data looks fine … only if i directly input data via phpmyadmin it looks weird

but you can set it all to extract the text in utf-8

how can i do that?

when you send stuff with ajax, its in utf-8
then you should convert it before you put it in the db, or when you extract it

jeah … i changed everything to utf8 but still have problems on the data, that is read out from csv files and put via php (without ajax) into db
hmm

heh.. i dont know, i do it like this in my php: mysqli-set_charset(‘utf8′)
you always have utf8_encode and utf8_decode in php

no
do i have to?

i dont know, try it out with decode the stuff you get from the db

what is the charset in the csv file?

kimseong how can i find out?
i think its latin1
our customers have created it with different tools
but another thin … i use “match against” how can i set the min_letters to 3 instead of 4?

you have to know
you can open the file with a web browser and change the browser charset until all characters are displayed correctly
show variables like ‘ft%’;
you ahve to change one of the options, and then need to rebuild the fulltext index too

yes i know, but i dont know how to change the option

!man option file

see http://dev.mysql.com/doc/refman/5.0/en/option-files.html

!man set

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

you better have full control of the server, forget it if shared hosting

i have full control
yesterday i tried to change the option file … than i got 10 minutes error messages from mysqld

whats the point of set a length on a database field?

i cannot find any ft_min_word_len in my.cnf … do i have to create it?

just add one into [mysqld] group
logik-bomb: to limit the size

does anyone know how to change ft_min_word_len?

ft_min_word_len = 4

its mysql 5 on debian system
i need ft_min_word_len to 3

whatever version
ft_min_word_len = 3

but where?
its not in any .cnf file

my.cnf

no the option is not in it

put it in

add one under [mysqld] there are lots of options that is not there
remember to rebuilt the fulltext index, otherwise existing data will not be indexed

okey, and how to restart the server? or dont i have to? just rebuild the index?

read the fulltext docs it explains
restart the server

no it does not, i already read it … they just say things like “and than restart” … but i dont know what and how
no link to a page like “how to restart the server”
the problem is, thats i live system … i cannot just try around
the first try has to be the right try

seems odd that you dont know the basics about controlling a server

first mysqladmin shutdown?
thats right
i am no server admin
but i have to change the settings now, cause i implementet select against match … and a select on this way isnt usefull without a min_word_len of 3
:/

most people practice on a dev server, they dont experiment on a live one

but i dont have one
our test server is windows
our live server is unix debian
its not my fault its historical … and i have now the problems with this shi* :/
lots of problems, like utf8
i try now /etc/init.d/mysql reload if you dont like to help me
fuck
restart failed

what are you trying to do=
?

to change a config value
and than restart

added ft_min_word_len = 3 in [mysqld]

Reload success

than tried /etc/init.d/mysql restart

restart failed

http://dev.mysql.com/doc/refman/5.0/en/fulltext-fine-tuning.html
try man mysqladmin

and check your errorlog

it probably won’t restart becuase there are active connections to it

ahhh now it has restartet automatically? *weird*

hi
anyone know where the data directory is located on debian?
and if I replace my old data file (from Windows) will it still work? anyhelp would be highly appericated.

/etc/mysql/data/
i dont know if you can use your data file (what do u mean? conf file?) from windows under debian

gargantua, better to do a mysldump

No, not /etc/… /var/lib/mysql is the usual place
Yes, the data files should work

hi guys, I have table containing languages, what is the command to use different collation for each column?
never mind, got it
bye

see ya

‘./admin/entries.frm’ (errno: 13)
hmmm
doesn’t seem so cross-compatible

permissions not correct

oh?
what’s the permission have to be?
world-writable?

mysql:mysql

?

no

sorry noob

it is not allowed to be world-writable

what does it have to be?

thats a security reason for mysql
i duno exactly sry, but i know … not world writable ^^

I just checked, it’s not world-wriable.

What is it now?
ls -la /var/lib/mysql/admin

http://pastebin.org/1051

You see the problem?

I believe a file is not there?
no?

Regarding permissions…
As archivist mentioned

chown -R mysql.mysql /var/lib/mysql

ls -la /irc/seekwill

chown mysql * && chgrp mysql *
oooh, -R might help on both of those too

same error
do I need to restart anything?

He’s not in the correct dir

oh my life

no?

chown -R mysql /var/lib/mysql && chgrp -R mysql /var/lib/mysql

ok I’ll cd to the correct dir

Copy + paste that command
Then restart mysql

same thing

Hello.

oh ho ho

Is there something wrong with this? SELECT id,title,post,date FROM posts WHERE post INCLUDES $search

killbox:~# mysql restart
Access denied for user ‘root’@'localhost’ (using password: NO)

INCLUDES?

-p then, and enter your pwd

$search ? It’s not php?

xyzu, where did you get that syntax from

It is my var

Unknown database ‘restart’

what’s wrong

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

sorry I’m a noob

ps -A | grep mysql
Type that

!tell xyzu about syntax

xyzu DO NOT INVENT SYNTAX (well, unless you have commit rights). Just follow the syntax described in manual. Thanks.

0

kill 123

O.k

Where 123 is is the pid

ok

No

no?

I thought INCLUDES is syntax

yes

xyzu, no

mysqld_safe &

gargantua Are you trying to restart the server?

yeah seekwill.

mysqld_safe is the mysql server program

Is there a to serch a feild in a database?

Use mysqladmin restart

isn’t there a more effiecnt way of doing it?

search*

Probably, but you don’t generally need to restart mysql

Good point, why are you restarting, gargantua ?

because I changed the permission on the data directory.

You don’t need to restart

xyzu, there are many ways

Unless of course yuo’ve killed the process off anyway… then you’ll have to keep on going (you’re pretty much there)

no?

I am making a search app for my blog with ajax :/ I need to search the post field.

Then restart mysql

You don’t need to

ok
but it still not working.
same error.

What error?

Table ‘admin.entries’ doesn’t exist

Restarting that won’t help
The table does not exist…

wtf…

xyzu, then learn about fulltext indexes and match against(‘words’)

no it does exist.

I bet it doesn’t

the .frm files is there, and phpmyadmin is listing it.

ls -la /var/lib/mysql

gargantua, .frm on its own is not a lot of use, did you copy the ibdata, eg was it an innodb table you wanted to copy

http://pastebin.org/1053
a href=”http://pastebin.org/1053″http://pastebin.org/1053/a
I thought it was a simple process of replacing the old data with the new one, obviously I was wrog
wrong*

I don’t think he copied his InnoDB files

do a a mysqldum as I said
p

how do I do that?
I just copied the mysql/data folder.

ls -la /var/lib/mysql/data

and replaced only the subdirectories of the actual databases.

mysqldump database db.sqq then copy that file accross
..sql

http://pastebin.org/1054

oh hey look innodb files

:O

copy resulting .sql to other box mysql dbnamedb.sql

what resulting .sql?

mysqldump database db.sql then copy that file accross

Got error: 1045: Access denied for user ‘root’@'localhost’ (using password: NO) when trying to connect
sorry
I’ll use -p
Got error: 1146: Table ‘tbbs.boards’ doesn’t exist when using LOCK TABLES

this should be done on the source of the data

Is there a way to trim a var to 259 chars?
(that is from a database)

!m xyzu string functions

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

you mean I have to be in the directory
?

no on the correct computer

Is there a way to insert in a database whose encoding is utf-8 data from another database where encoding is ISO?
And by the way
HI ALL
Cause I get weird characters instead of the expected french accents

it is converted automatically, if from the same instance
if you use insert .. select ..

It should
BUT

you can always try to conevert it with the “iconv” command. Before you insert the data.

It’s an INSERT INTO table1 …. SELECT FROM table2 where table1 and table2 are in two separate dbs
One is ISO, the other UTF-8
Thus the problem
Is iconv available as a mysql command?

its a linux shell command.

Or should I dump the data, iconv the file
And then reinject it
OK
Maybe I could do that conversion in my PHP script

it would work also.
i think php web hosting have functions for that.

try it on an empty new table, it should work fine
insert .. select ..

hi. Mysql5 does not support INDEX ON tablename(colname1 ASC, colname2 DESC), right? In my case, the query is “SELECT * FROM tablename WHERE fk=1234 ORDER BY created_timestamp DESC LIMIT 1 “. Is there a way to have this query optimized?

if you have php mysql web hosting 4.1 or newer, 4.0 then won’t work

I’m sorry, but what should I try exactly, as we’ve discussed several solutions

“warning: connect to mysql server localhost: Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’” but when i do: “mysqladmin –protocol=socket –socket=/var/run/mysqld/mysqld.sock” i can connect to the socket, someone has an idea?

insert .. select .. if you have mysql 4.1 or newer

hello everyone. I am struggling with what would appear to me a simple task in that i am selecting certain fields from a table(customers). in one field i have a list of comma seperated items(id’s) such as 1,5,24,33. in table two(interests) i have these ids and their respective text based
string ‘books’ ‘cds’ etc… is it possible to take the comma seperated list and join to retrieve the items or do i need two queries here?

But… That’s what I did

marc-andre: check so /etc/my.cnf point out your mysql socketfile.

index on 2 columns should work, since the first col is = comparison, it can search the index from the end to front when you do desc

And insert … select

result?

An*
Sorry
Let me explain more clearly
I did an insert … select (inserting in a UTF-8 table some data from an ISO table)
And it didn’t work fine

if you can pastebin the show create table of both table, it may be helpful

id did already “sudo -u postfix mysqladmin –protocol=socket –socket=/var/run/mysqld/mysqld.sock” and it worked

Damn
Both tables are in fact latin1
I guess the existed beforehand
Sorry for the inconvenience

marc-andre: ofcourse it worked when you point out your socket file. But its nopt nice to do that al the time.
bmarc-andre: ofcourse it worked when you point out your socket file. But its nopt nice to do that al the time./b

1. Why “using where”, not “using index”. Anyway, 1 row to fetch is ok. But if I add an ORDER clause…

marc-andre: if you point int right in my.cnf you dont have to use –socket switch.

58

weird statement when = and order by same col

58 (I do have index on “fk” and “issued_at” together)

How do you order 1234, 1234, 1234, 1234? Which one goes first and which last?

explain show you which index is used if any, is the index chosen?

How exactly the index is defined? (fk, issued_at) or (issued_at, fk) ? The difference is quite big

even without –socket=xxx does it work…

the index looks like KEY `forecasts_loc_issued_desc_index` (`location_id`,`issued_at`) in the dump
and the query is to find the most recent forecast for the given location

THat’s good. Follow kimseong advices and you will be fine

“forecasts_loc_issued_desc_index” indeed.

58 and find this surprising

How is this surprising?

I wanted to fetch just one record. There are blobs.

The optimzer expects about 58 rows to match fk=1234 condition and LIMIt will cut that to one
Don’t you see the difference between rows examined and rows returned?

but order clause will make it dump all 58 to a temp. file and sort
certainly I see

this will return single row, but must examine 1 million in order to do it

1

Ofcourse

but with order by, it is 58. So it makes me think that there is a chance that sorting is very inefficient in my setup.

LIMIT N makes mysql stop after first N rows are found. Without ORDER BY it could be any N rows. With ORDER BY it *must* fetch all rows first
If you wish you can find MAX(issued_at) first for fk=1234 and see if that will be faster

but all the required info sits in the index

For only 58 rows I doubt it will be any faster, but who knows

there may be more than 58 eventually

SELECT … FROM t WHERE fk = 12345 AND issued_at = (SELECY MAX(issued_at) FROM t WHERE fk = 12345) AS temp;

so this is rather fundamental – whether we can avoid the sorting operation by crafting the index in a right way, in the presence of the fk

try EXPLAIN with that one and also try how long time both take

1, and “Select subquery optimized away”

how do I see if a db column is utf-8?

if i got a warning by sourcing an sql batch. how to know some detail about the problem? it does not tell me anything more

hi there
I’m creating a stored procedure, but I want to return the value 0,0 when the result of a query is NULL… is this possible?

why not do it at the application level?
I think it’s more appropriate there

not really, I want to return the price of an order, when the sum of the ordered products is NULL is want 0
can you help me with this?

methinks the data should not be able to return a null

Can’t you just check if the field is null and treat it in the application as 0?

but use if to fix

I think I don’t really understand the problem

I’m looking for a way to return 0 when a result is NULL.. thats all

COALESCE(val,0)
Where val can be an expression.
So COALESCE(SUM(field),0) is fine.

it works!

i can connect from the console directly over the socket to mysql, but postfix can’t connect over the socket, what is going wrong?

anyone know if there are any security risks using a mysql server from a different server host (and provider) than the one the php files are located on?

marc-andre, ls -l
aoirthoir, its better to stay local

?

tibyke, yes most likely.
but the provider does not offer triggers, which I nee
need

You can connect with SSL if security is a big concern.
!man secure using ssl

see http://dev.mysql.com/doc/refman/5.0/en/secure-using-ssl.html

snoyes, thanks. I’m assuming thats part of php also?
looking up the link

!php openssl

php.ini variable name= default= http://php.net/

that’s not right
http://www.php.net/openssl/

tahnks
er thanks

http://www.php.net/manual/en/function.mysqli-ssl-set.php

snoyes thanks a lot

hello

can it be that because the mysql acts as slave that postfix can’t connect over socket?

can anybody tell me if i can log slowquery in 2 different paths? or if i can (how?) change the permissions to the file..

Hi! I observed that there is a difference between system time and mysql time. How do I need to sync both the times without restarting MySQL? I am using MySQL 5.0

marc-andre: I suppose it’s possible. What build of postfix are you using?

2.2.10

Did you compile it yourself, or did it come prebuilt on your distribution? Was it compiled with the –with-mysql option?

I’ve finally finished my procedure, now I want to do: select product_costs(p_id) as bruto_price, bruto_price / 1.09 as netto_price; so I only need to call product_costs once because.. is that possible?

not with aliases. Maybe with user variables, if you bracket them correctly.
xaprb wrote about that a little while ago

do you have a link?

i use ubuntu 6.06LTS and installed postfix-mysql as well

Hi! I observed that there is a difference between system time and mysql time. How do I need to sync both the times without restarting MySQL? I am using MySQL 5.0

http://www.xaprb.com/blog/2006/12/15/advanced-mysql-user-variable-techniques/

Can anyone help?

thanks a lot snoyes

marc-andre: Ok, that’s as much as I could guess from Google, so it’s now over my head.
Is the difference one of time zones, or is it off by minutes?

My php mysql web hosting timezone is set to SYSTEM. So I think it should/will take system time zone. And it is differs by almost 5 hrs.

Is MySQL’s time in UTC?
that is, does this apply? http://dev.mysql.com/doc/refman/5.0/en/timezone-problems.html

Sorry got disconnected.

Do you see ’system’ for both results here? SELECT @@global.time_zone, @@session.time_zone;

Yes

What time does your system and mysql say it is right now?

4 UTC

so your system is reporting UTC

Yes

and mysql is showing eastern

Yes, but the timezone is SYSTEM rt? So does it not mean that it is set to System time?

I suspect the issue is that the whole system is on eastern, and the command you used to get the time from the OS translates it back to UTC

ok
So How do I find wat my whole system is at?

don’t pass the -u to the date command

dont pass it while updating the date or not to pass it while getting the output?

while getting the output

I am not

what do you get from date +%Z

return UTC
hwclock also returns UTC

try setting the TZ environment variable to the appropriate value (EST5EDT, I think)

ok
So now you are saying that MySQL will reset its time to the system time?

I assume you’re in eastern time, and I’m trying to get the system to report it correctly instead of in UTC.
Unless that’s what you want it to do.

I want the system to report UTC
And I want MySQL to reflect UTC

and what does MySQL say for SHOW VARIABLES LIKE ’system_time_zone’;

SHIT!! It says EDT
Now how do I change it?

you’ve changed system time zone, after you started MySQL, right ?

yes

http://dev.mysql.com/doc/refman/5.0/en/time-zone-support.html discusses setting the time zone

Hi Folks!
Got a count() questions w/ this query: SELECT s.id, s.shot_num, s.shownum, count(n.id) FROM shots s, notes n WHERE shownum = 101 AND n.shot_id = s.id GROUP BY s.id

thanks mate…you have been very helpful.

set it to UTC, then when you restart MySQL, it should pick it up from the server again, which is now UTC.

well, you cannot change system_time_zone after you started MySQL

oh

Works as expected if there’s a note for the shot, but it skips the shots row if there’s no note for the shot…

but you can change timezone from SYSTEM to UTC

use a LEFT JOIN
….FROM shots s LEFT JOIN notes n ON n.shot_id = s.id

So you mean to say I need to restart MySQL to change the timezone….

You can set the time_zone variable from system to utc without restarting.

ok

example, SET GLOBAL time_zone = ‘+0:00′;

Thanks muchly (:

AVE!
howto update enum list?

Trying to update a row, or change the allowed values?
!man update

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

!man alter table

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

update for the first, alter table for the second.

CREATE TABLE `my_enum` (`city` enum (‘one_city’,'other_city’,'more_city’))
howto add city?

ALTER TABLE my_enum MODIFY city enum (‘one_city’, ‘two_city’, ‘red_city’, ‘blue_city’);

Is there a free tool for taking two schema SQL dumps and producing the ALTER statements necessary to get from one schema to the other?

tanks,
insert into city….

Don’t know of one per say, but you could take MySQL Table Sync, run it against the information_schema, and mangle the output from INSERT/UPDATE/DELETE statements to ALTER TABLE statements.
Ah, to insert new rows, INSERT INTO my_enum (city) VALUES (‘one_city’);

Hello everybody, is it possible in MySQL 5 to create a stored procedure that creates a MySQL-Query in a text-Variable and executes the query from that variable?

!man prepare

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

can you join mysql-nl?

sure, sec

other server?
dutch one…

Carry uhm….which one is that?

^
met x

sorry ik ben niet zo’n irc held

voor iedereen
toegang…

hoe heet die server precies dan?

send you message..
no private messages…
found you on freenode…

0 into the

hi, how long is it supposed to take to convert a table of 3.7 million lines from myisam to innodb?

a while.

well, it’s a fast machine
so what’s ‘a while’ ?

depends, eg how many indexes

just one, the primary key
oh wait, that was the other table, three indices

did you optimize innodb first?

yes
i just dropped those two indices, let’s see if it goes faster
wow, it’s done already

ubuntu

Ubuntu is an ancient African word meaning ‘I can’t configure Debian’

lol

hehe

want a FREE iPhone? Go here to Score Yourself a FREE iPhone http://www.YourFreeiPhone.com/index.php?ref=3922029 Super easy.

Please don’t spam us.

whoops

afternoon all
UPDATE `link_building_csv` set `alexa` = ‘$alexa’ WHERE substring_index(links, ‘/’, 3) = ‘{$host}’; this runs but doesnt update anything

Man, the banlist here is out of hand

how does one drop all tables in a database?

drop the db is the easy way
but that loses the procedures as well

Hey folks. When I insert into a table, and a column has a length constraint, but I try to stuff more characters into that field.. MySQL doesn’t give me an error, it lets the insert happen, but truncates the string. Is there a setting to make the insert fail in this case?

if you set sql_mode to strict_all_tables, maybe

$sql2 = mysql_query(“UPDATE `link_building_csv` SET `alexa` = ‘$alexa’ AND SET `host` = ‘$currentHost’ WHERE `id` = ‘{$id}’”) or die(mysql_error());
has the error
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘SET `host` = ‘https://www.poserfish.com‘ WHERE `id` = ‘13352” at line 1
what am I missing?

aww, why’d you ban that guy? i really NEEEEED a new iPhone!

Me, too! What was I thinking?!

using an enum() field, or making a new table of types, and using a relationship?

Dom2, it is a csv list of key = value to set, not “AND SET” etc

i tried it with , and got the same error

Dom2, so SET alexa = ‘val’, host = ‘host’ WHERE …

ah
i had an extra set

i’d go with the table of types. that can be managed much more easily than an enum field, which would mean updating the table to expand the potential types.

then linking by key?

i would assume contraints could be set. not sure of the syntax on that though.
hm, manual says only innodb tables support foreign key constraints. FK support for myisam is allegedly coming in 5.2.

what do you do if you have a type of data that will use anywhere between 11 and 20 fields? Do you create 10 tables for each size? one table and just have somewhere between 0 and 9 fields left blank? or design a table so that each value is its own record?
the fields would be Floats, and there are likely to be about a million records on average

is each set of data have some associative relationship, or is it a list?

Oh, I didn’t contribute to it

Of course not, you are useless!

i’m not sure exactly what you mean, the order is important, and they are associated with an entity

whoaa

Annoying, huh

hello
how can i learn all user password on mysql ?
i need a tip or command?

Why do you want to?

i want to learn all users password on mysql

Why?

because all user password i am modifiye exchange server ..

Exchange doesn’t use MySQL

yes i know ,

Then you still haven’t answered the question

just my mysql users have same password on … exchange ..
i was setup new exchange , i created users accound on exchange ..
but my users want to use same password on exchange ..

i’d probably do a table that has 3 columns — set id, entity, value. that way you’re not wasting space on a bunch of null fields.

so i need all users password on php mysql web hosting ,

smart security policy

my english is very bad so sory ..
cant i learn it ?

there might be some application or method to get mysql to authenticate against AD. you shouldn’t need to know users’ passwords.

that would be preferred even though it means 15 times as many records in an already large table?

Hello, what is the way to ‘GRANT’ root connect from any host?

GRANT ALL ON *.* TO ‘root’@'%’

Password?

swordfish

thx

!man grant syntax

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

having a rule for @’%’ also requires a rule for localhost right?

“You cannot easily or reliably decrypt MySQL user passwords”

no.

You would have to do a dictionary attack
And hope
“for the most part”

Well,

heh, i don’t think i’d even consider allowing root to connect from anywhere but the local server, and maybe a trusted remote machine at most.

I’m doing for my university a simulation of an online book shop web site, and I have a problem… User can buy a specified quantity of books. So I check, before updating a table containing the orders, that there are enough books. But how can be sure that between the query and the update no
other users have already purchased that book, and maybe there are not enough books available?

Use a transaction

snoyes, I have a thing in JSP that it autocommit

don’t use it.

snoyes, yesterday too they told me to use a transaction for another problem… I’m developing with jsp (java) and I see this autocommit

turn off autocommit.

snoyes, ok so if I just disable autocommit, it’s done right?

provided you’re using a transactional table, like innodb

snoyes, yes I’m using InnoDB

you could also implement your own locking, use the GET_LOCK() function

Therion

a big fat meanie pooh pooh head

i dont understand you
can you make basic english ?
basic = easy

It can’t be done.

“Cannot do it”

ok

?No puedes hacerlo!

snoyes, the fact is that autocommit is enabled usually for my database… if I disable it just for doing this query and this update, will it work? or probably not, because other database hosting access have autocommit enabled?

It should work.

snoyes, ok thanks a lot

you suggested that I have set, entity, value… but if the order is important do I also need a field for that? or could I rely on the order of the records?

assuming the order of entities is the same in every group, you could have a second table that has your entities and their orders and join on that to keep the values in order without needing to store the order in each group.

sorry, i mean the order of the values, not the order of the entities

maybe we’re thinking of “entities” in different contexts.

i have 11 values for an entity e.g. 3, 1, 5, 7, 9, 11, 13, 15, 17, 19, 21 some entities might have up to 20 values, and it is important that i know that 3 comes before 1 in this case

okay, that’s not how i was interpreting “entities”, that’s what i was calling a set. what i was thinking was the first value would be “foo”, the second “bar”, etc. if there’s no named relationship, you could just use ordinals in the second column.

set, ordinal, value; 5, 1, foo; 5, 2, bar; … ?
sorry this is so basic, the db course i took didn’t help me at all for any real application

set 1, order 1, 3; set 1, order 2, 1; set 1, order 3, 5; set 1, order 4, 7; … (using the first four values from your list above)

ok that’s what I thought you meant, awesome. thanks a ton

hello my process said me that he can t connect to mysql database cause he need a root access without password
i heard i can change defined a user for mysql

hi all!

Hi one!

I’ve 2 tables (an online table and a backup) i want found the differences (record presents in the backup and not present in the actual online), what can i do?

toolkit

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

a not in b

SELECT a.* FROM a LEFT JOIN b ON a.id = b.id WHERE b.id IS NULL;

Depending on where the tables are and what you want to do, one of those two approaches ^

i’ve both in the same db, i dumped it and restored in a test databas
*database
i call it table_backup table_online

hello, how do I SELECT paticular SESSION VARIABLE. I know I get entire list with SHOW SESSION VARIABLES; but I just want to get value of system_time_zone. thanks.

is it possible to make loops in mysql?

@@session.system_time_zone perhaps?

Unknown system variable ’system_time_zone’
i already tried that
and select @@global.system_time_zone;
ok, solved. this is but in mysql. I can achive what I need with show variables LIKE ’system_time_zone’;

anyone have any ideas on his organisation problem i have….
basically, I exported all my hotmail emails from outlook into over 500 files, renamed them all from .eml to .txt then merged them into 1 big .txt file… problem is ive got headers and email crap above and below the actual emails i want to keep – anyone have any ideas how i could get rid of
them… (i know this isnt mysql related but im desprate and dont want to resort to doing it manualy over a few hours)

Did they remover the mysql control center from the downloads?

hello folks, i am try to connect to a mysql server from a client machine running identical versions. http://rafb.net/p/yC07Ds76.html . would appreciate some help on how to fix it

external

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

i don’t think they’ve offered mysqlcc for download for a while now.

no, but i still run it

yeah, so do i. i find it gets a bit flaky after it’s been running for a while though.

yeah, its prolly leaking memory

ah, it’s on sourceforge.net — and jeez, i must be running an ancient version.

whats current?

0.9.8.

k i’m running 0.9.4

i’m running 0.9.2-beta (!)

hehe

sigh. the win32 distribution is compressed with rar?! i don’t have any rar tools on this box.

there are free linux tools for it

yeah. i can punt it over to my debian box, unrar it, and bring it back. yay.
(maybe.)

oh noes

I have a field called hours which contains a number which could be decimal. I am looking for a sql queried that will total the values in all rows for the feild “hours”
can anyone help

why won’t @sum work

which “could be” decimal?

as in 10.5

do you store binary in there too?

Theo is advocating that Phillycheese place over here

can anyone help

yikes!

What is the question actually?
SELECT SUM(hours) FROM your_table; ?

salle thankyou
that’ll do it

hi. I don’t know what indexes I should add to this table http://pastebin.ca/623483 I ‘ll be doing selects based on the word and position fields and updates based on id. I’ll also need select with order by based on votes. Should I add an index to each
field?

foutrelis, match indexes to queries

you’re right

No. For every permutation of fields.

no thats not right

so if I have where word=’something’ and position=8 I add a (word, position) index right?

if the select uses it that way

ok

I want to display this on a php page
will.. mysql_query(“SELECT SUM(week1hrs) FROM rotadata”);

It also can depend on the distribution of data.

hmm rotadata was/is a company name

the word field will contain unique elements whereas the position will have 1, 2, or 3
I’ll google for some index tuts and see what I can learn

You can learn quite a bit just by trying as you go with real data. It’s relatively painless to add/drop indexes to see the change in behavior. Relative to the size of the set. Sometimes creating the index can take a while.

Thanks for the help dude

If you’re dealing with small sets (a few million records), you shouldn’t have too much trouble.

okay, this mysqlcc win32 distribution is ALL kinds of screwed up. INSTALL.txt says there’s to run setup.exe. there’s no setup.exe. so i moved my current install out of the way and copied it in there. when i run it, it still claims to be 0.9.2-beta. (i’m not expecting a fix, i’m just
ranting.)

I am trying to display the result of a query on a php page.
$result = mysql_query(“SELECT SUM(week1hrs) FROM rotadata”) or die(mysql_error());
“.$result. “”;

Not really a question for #mysql channel you know

however that returs a value “Resource id #4″

indeed
Consider examples from php manual

learn php

can you shed some light?

PHP sucks
Perl for life

php rulez

perl

the nice thing about perl is you can just slam your fists at the keyboard at couple of times, and you have something that runs and does something, you just wont know what and the maintenance of it will be impossible

try var_dump($result);

http://php.net/mysql – read the first example and then the rest of the manual section about MySQL

$result is a resource, var_dump won’t show anything meaningful, you need to get data with a function like mysql_fetch_assoc().

MySQL’s default port is 3306 right

Yes

http://gr2.php.net/function.mysql-result

have tried var_dump and echo mysql_result($result) but no joy

mysql_result($result, 0) ?

read what i just said.

foutrelis that worked thanks

great

is there a way to find out how much innodb space a particular database is using?

foutrelis again thanks – your a god

3

what are dynamic rows?

Assuming your context, they are rows that are not fixed length. Like if you used VARCHAR or TEXT fields.

oh

can 4.2 foreign key constraints?

Huh?

err 4.1

only on innodb tables.

Yes, version 4.1 supports FK

There is variable row size format in MyISAM engine

thanks

InnoDB engine supports FK since 3.23

I’m looking in the MySQL Administrator program and it says some of my tables are Fixed and some are Dynamic

myisam is supposed to support FK in 5.2.

It’s fine

You can trust Administrator about that

oh I don’t think it’s a problem, I was just wondering if there were some magic features I didn’t know about
oh I don’t think it’s a problem, I was just wondering if there were some magic features I didn’t know about

Did they remover the mysql control center from the downloads?

yes. get it from sourceforge.

“notagain”

(though the win32 0.9.8 package seems to be broken.)

mysqlcc is not developed for years now

control center eh?
eh, phpMyAdmin 4 life
and by life I mean attention span

there, FINALLY got 0.9.8 working. though the new pinball machine noises while it opens database connections is INCREDIBLY obnoxious.

what did you have to do?

i recopied the mysqlcc.exe from my temporary unzipped directory. i’m at a loss to explain why it grabbed the old .exe from the old path the first time i ran it, but that’s what appears to have happened.

does mysql support “silent” installs with the options needed by the installer being passed via the command line or some other method?

that sounds like more of a function of the installer than the application itself.

right, sorry.

hey all

can anyone clarify how exactly triggers work with replication? the documentation seems to imply they don’t, but doing some testing they appear to work fine.

hy
i could need help with a little query

shoot

i want to select two fields out of the database

same table?

select * from user where firstname.lastname=”MaxMusterman”
yes
in my php-script the username is firstname.lastname

how do you create a user as root?

now i want to check if the user is in the database
any idea?

grant all on databasename.* to ‘username’@'host’ identified by ’somepassword’;

“select * from user where username = “.$firstname.$lastname.”;”
like that?

does tmpdir= require a trailing slash? MySQL 5.0.27 on redhat

do your select and then take a look at the rowcount

no, i have no field username, just firstname and lastname
i cant change the fields

I found a bug online, filed with mysql against some version of 5.1, now fixed, where tmpdir did require a trailing /, but I can’t tell if that bug existed before 5.1

they have to be like this

where firstname=”x” and lastname=”y”

and the default value if you don’t set tmpdir is “/tmp/”

i cant change the loginfield too^^
i get the username as firstname.lastname
thats how the user is typing it in

is there a column in your table called firstname.lastname?

no firstname ond lastname
i have the fields id, firstname, lastname, password and email

“select * from user where firstname=”.$firstname.” and lastname=”$lastname.” limit 1″;

but i get just one variable
the variable $user is like “firstname.lastname” with the dot in the mittle
now i have to look in the database for username.lastname = $user

“select * from user where firstname+lastname=”.$firstnamelastname.” limit 1″;
oh, dot in the middle eh?

yes
firstname+’.'+lastname?

check concat http://dev.mysql.com/doc/refman/4.1/en/string-functions.html#function_concat

okay, thank you!

select * from user where concat(firstname, “.”, lastname) = $user

You are better off not doing that…
Use string functions to split $user

SELECT * FROM `user` WHERE CONCAT(firstname, ‘.’, lastname) = ‘Max.Mustermann’;
that is working for me
thanks for help

might want to take seeekwill’s advice
definitely make sure that both firstname and lastname can’t have periods in them

Your way won’t use an index and will be slow.

awesome advice!

If I want to change a column from a VARCHAR(64) to an INT(3), would this syntax be correct? ALTER TABLE my_table CHANGE timezone INT(3) NOT NULL;
?

Why INT(3)?
!tell PuppiesOnAcid about zerofill

PuppiesOnAcid http://hashmysql.org/index.php?title=Zerofill

i put the password with in the query

lol awesome

So I want to do INT(0) actually

no

@ seekwill

Just leave it out

Alright, but either way, I don’t think my query is correct
What did I fuck up?

so i just need one query

sandrot:
!m PuppiesOnAcid alter table

PuppiesOnAcid see http://dev.mysql.com/doc/refman/5.0/en/alter-table.html

I know, but I don’t know if I need a CHANGE clause or what
I can’t figure out which one I need

isn’t it change column newcolumn varchar(xx)

There are examples

Is a schemata the same thing as a database?

Why does zerofill always seem to default to 11?

I’d gander to say no just because a database can mean a lot of things.
can you give a column an alias?

a schemata includes the databases, indexes and triggers and whatnot

ha. i finally get back to work, bring up the new version of mysqlcc, open up a table, try to run a query, it crashes and burns. sigh. maybe 0.9.2 wasn’t so bad after all.

ok well I’m talking about what you get when do you
use whatever;

user whatever implies that “whatever” is a database

user?

*use

ok

recopy DLLs from temp folder, rerun, now it works. egad, no wonder mysql.com disowned it. the internals must be a train wreck.

well they switched to mysqladmin

I must be retarded. I can’t get mysql query browser to work
via mysql administrator

don’t feel bad. i installed that too, and i felt retarded trying to make it do anything at all.

pizza_biz, I got it now. But is there any way I can edit fields via gui?
I don’t need to work with mysql in general, just need to change a few fields this once

in mysql admin? if you click on edit below the result set, you should be able to click on and modify a field.

yeah
pizza_biz, edit feild in popup editor is greyed out
but im logged in as root
wtf

Profanity is the inevitable linguistic crutch of the inarticulate blockhead

the_wench, Thanks for that the_wench
Then I’m a blockhead.
And convoluted language of the type you are using, the_wench is the sign of an arrogant jerk.
So let’s call it even.

turing

Yay I passed the test. I am human after all

are you doing a straight select query, or are there joins and stuff? i know mysqlcc doesn’t allow editing on joined data and other complex queries.

Ah I found the edit button
hooray
i didnt realize you have to click edit to enable editing and then right click on bugs to edit them

what database permission do I have to have for a database to show up in the list when I do SHOW DATABASES; ?

I’m trying to implement a friends system and subsequently an activity feed. I’d love to get the activity feed down to 1 sql call but just finding friend primary keys requires a join. I figure I’ll grab the ids and then create a large “OR” list (select * from stories where author_id = f1 or
author_id = f2…). I have at least 5 different tables that will make up the activity feed. Does anyone have any experience with grabbing lots of
data from multiple tables? Anyone have any advice?

sandrot, ala facebook?

HABTM

that’s the idea…
are you implying that a new table should be created?

sure

I’m desperate. I have a long query that works good if I execute it from the terminal in mySQL, but prints random characters in my JSP page… can I paste my query somewhere if somebody can take a look at it, please?
the problem is for a GROUP_CONCAT () in my SELECT that prints html code too
it works if I don’t print html code
but it mySQL from terminal, looks like it prints the correct html code

hello, can i setup a column as a Foreign key after its already populated with data?

yes
!tell GionnyBoss about pastebin

GionnyBoss try http://pastebin.ca

http://pastebin.ca/623606
thanks
this is difficult to explain, but looks weird to me
probably it is more related to Java than to mysql

Can you show the output?

ALTER TABLE `netipm`.`SiteInfoNew` ADD CONSTRAINT `FK_SiteInfoNew_2` FOREIGN KEY `FK_SiteInfoNew_2` (`PriEMS`) REFERENCES `SiteInfoNew` (`PSI`) ON DELETE CASCADE ON UPDATE CASCADE;

GionnyBoss, ?

Should that ? in the group concat be inside a ”

this is the command i’m trying

150

http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html

MySQL Error Number 1005 Can’t create table ‘./netipm/#sql-8e1_f298d.frm’ {errno: 150}

archivist, it is a prepared statement
archivist, first ? is “?” character, second one is the title to search for

So you pass the statement a “?” character?

snoyes, yes

why?

Im pointing to the unquoted ? as snoyes does

i’m not able to do that command since i dont’ have elevated privs.

snoyes, because I need the “?” character and I don’t want that the ? will be taken as a parameter for a prepared statement

why not just do authordetails.jsp?id=’, authors.id, ‘”‘…

snoyes, will it be counted as a parameter for the prepared statement? or not, because it is inside the ” already?

It’s inside a string, so it should not be counted.

http://pastebin.ca/623613
snoyes, well, I tried as you are saying, too… it doesn’t work anyways

Can you get the privileges? If not, you need to show the SHOW CREATE TABLE statements for SiteInfoNew.

the strange thing is that if I print “authors” form my result set, it display random strings
instead of the right html code
snoyes, the very weird thing is that it prints the right html code if I don’t use the ORDER BY

That’s the part that’s of interest. You said the command line client does it correctly.

snoyes, yes
snoyes, but I don’t know why I get this strange error… it works without the order by. Now it is more than a hour that I try to understand this… and I don’t know what to check

snoyes “GionnyBoss it works if I don’t print html code”

show us the incorrect output

http://pastebin.ca/623613

That’s the output from the terminal
you said the problem was on the jsp page

snoyes, yes

so show us the jsp page

http://pastebin.ca/623619

[B@b00ec2

there is my show create table SiteInfoNew;

snoyes, I get this string, but it is random... sometimes changes it but it always start with [B@ ...
for the "authors" column

PriEMS is allowed NULL, PSI is not. They must be the same.

I guess I will do it without the html part...
I'm doing this for an exam at my university
I did a good work, but I would like to understand this
I think I will do it without the html part and then ask to my teacher if he knows why it doesn't work

a foreign key constraint fails.

thanks to all for your help
this is really really strange, I should be missing something easy somewhere

Hm, I'm not sure what's going on here.. I can't connect as my root user, despite having a root@'%' entry, and the password being correct
aside from those two things, is there anything else that could be wrong?
this all started after I deleted the "empty" user. '%'@'%' no password, just USAGE

skip-networking and bind-address _again_
ever heard of mysql.com/doc?
*sigh*
use drop user for deleting users

who, me?

What error do you get when attempting to connect?

#1045 - Access denied for user 'root'@'64.252.107.207' (using password: YES)
I can reset the password again, but I don't think thats the problem

mysql.com/grant

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY PASSWORD '...' WITH GRANT OPTION

I assume you can login as some other user (on localhost or something). What do you see from SELECT user, host FROM mysql.user;

I'd say that's correct
yea
user root, host '%'
is the relevant one

What others are there?

uh, root @ localhost, root @ mydomain, and a user I added

Is the password for root@mydomain different than the one for root@'%'?

shouldn't be, no

I am trying to remove the unsigned attribute of an int in a database but I keep getting a #1025 - Error on rename. Any clue about this? I was trying to use this field as the reference in a foreign key constraint, and was having problems. I saw that the two fields did not have the same
attribute that was the problem.

uffh
!join

What's the host of that user you added?

!faq join

$
er, %

isnt there an idiots guide to join faq here

joins

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

actually, wait
the user I added has a specific host, of my webserver (not the box I'm attempting to login through)
I feel stupid now.. I must have changed the password
i just reset all the root passwords, and it works
sorry

!man mysql_install_db

see http://dev.mysql.com/doc/refman/5.0/en/mysql-install-db.html

Column 'dnsname' in field list is ambiguous
what the hell
select dnsname, email, sysadmin, ipaddr from ip_data left join mx_data on ip_data.dnsname = mx_data.dnsname;

Thre are two `dnsname`, one in each table.
Which one do you want?

dnsname is shared between mx_data and ip_data. shouldnt that work?

you have dnsname in both tables

You have dnsname in both tables. do you want SELECT ip_data.dnsname, or mx_data.dnsname?

quite obvious

ahh ok

When joining table with ON, MySQL doesn't quite treat them as the same. If you joined them with USING, you'd have no problem here.
that is, ...LEFT JOIN mx_data USING (dnsname)
depending on your version, of course.

hm. my results are funky. my joins backwards i think
how do you limit both tables you select from to do a join
eg, field a from table b, field a, b, c from table a, joined on b.a

select a.b, a.a,a.b,a.c
er
select b.a, a.a,a.b,a.c

so ugh.
i hate joins. i never ever ever can make them work right.

Joins are fun and easy

i want to join mx_data.dnsname with dnsname, email, sysadmin, ipaddr from ip_data

Awesome!

my problem is the only join i can make work, includes all the stupid null entries that arent in mx_data

so take the LEFT off

You can limit them most commonly with join criteria and a WHERE clause.

aha, got it
what does left do?

As snoyes says, or... WHERE field NOT NULL ... if you want to filter out nulls due to an outer join or not.

select mx_data.mxrecord, ip_data.dnsname, email, sysadmin, ipaddr from ip_data join (mx_data) on (mx_data.dnsname=ip_data.dnsname)\G
that seemed to do what i needed

field IS NOT NULL, that is

That hashmysql link in the joins factoid compares left joins to other types.
joins

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

gotcha. am reading that again more closely

hai
If I want multiple WHEREs do I need an AND or a comma?

AND

hey guys, im looking for a way to do a differential sync of two version of a DB

ok

toolkit

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

checking it out

random question, is MySQL suitable for only online applications or offline to?

both

cool

I use it a lot offline

but wouldn't you need to have everyone that has the app having a mysql server installed on their pc?

!man embedded

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

thats a no then?

right, unless you count putting the server inside the client as installing it on their pc.

well thats nifty
my dad's office have an outdated database system that I thought he could use a makeover
btw this channel is much cooler than ##php :p
everyone is serious there

if only we could abuse people like ##php

NO MORE JOKES

seekwill start the abuse!

yes, seekwill
stop goofing around.

god damn kids

get off the lawn

*sigh*
I wish I had a lawn

I wish I had a lamb

I wish I had a lamp

!man multiple server

see http://dev.mysql.com/doc/refman/5.0/en/multiple-server-clients.html

hey, is there any information about using mysql with large numbers of tables per database (I was thinking 2000) tables per dataabse..

Your file system will complain about it before MySQL will.

make sure your system has enough file descriptors available

okay

lol

but please normalise a bit better

you guys are all----riiiight

not really possible in this situation
its either databases with large numbers of tables, or large numbers of databases

or denormalize

it would be 2000 tables.. if I split that up to the max, its 20 tables in 100 dataabses :/

split it up to the MAX!

http://dev.mysql.com/doc/refman/5.0/en/table-cache.html

okay, I'll look at it

thanks all

support?
darn, thought there was a bot to answer that
is this the right channel for support questions?

regarding?

i am wondering how to look at what types of queries are being made to a live db
w/o query mode

show processlist;
enable the general query log, look at the output

could anyone point me to a troubleshooting guide for mysql? When I try to connect to a database locally, it's nice snappy and fine. If i try to connect to it from another machine, it's slow as hell to authenticate

ok thanks
let me see how that works

Maybe you have a DNS resolution issue.

and it looks like all my dns is in place
that was my first thought
but everything seems to be in place and resolving forwards and backwards just fine

take the DNS part out. Use the IP.

If you run mysqld with --skip-name-resolve how does it go?

let me check

Check the network traffic to be sure.

network traffic is very low

That's not what I mean.

ok

You will be able to see any network related gaps in time/responses.

anyway to get pretty mysql selects from mysql client? long lists always get fumbled up

G'day!

replace ; with \G

Is there an easier way of doing 2 UPDATE queries in 1? Such as to shorten these: UPDATE `table` SET `order` = '3' WHERE `id` = '1'; UPDATE `table` SET `order` = '5' WHERE `id` = '2';

will try, thanks!

CASE/WHEN

UPDATE table SET order = CASE id WHEN 1 THEN 3 WHEN 2 THEN 5 ELSE order;

snoyes is my #mysql hero for the day!

\o/

i found the problem

ahhh I see, - though what is the ELSE order for at the end?

it was because when I built this guest ( Xen type setup ) I forgot to install the locales

!man ndb

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

!man cluster

Don't change order for any other cases.

see http://dev.mysql.com/doc/refman/5.0/en/mysql-cluster-backup-concepts.html

SET order = order , which is the effective operation for the non-matching cases, will not actually cause an update for those rows.

I think you mean me - thanks for that

Yes. I do.

thanks snoyes and Xgc

welcome

thanks

Hi. I'm trying to connect to a mysql database using the MySQLdb python module, via an ssh tunnel on port 13306, but whenever I specify a port, it ignores it and tries to connect to a socket instead. Any ideas?

specify the host as 127.0.0.1

you brilliant man, I JUST thought of that while reading the _mysql documentation
seems there's a glorified if statement checking if it's localhost somewhere

yeah, something like that.

thank you muchly

mp
er, np

is the 100-500 million records range (with 8 integer fields per record) outside the scope of InnoDB? Is there something I should be looking at or considering? I don't see an easy way to split the table up...

innodb should be fine

Hum, I can't get this CASE THEN/ELSE query thing working: http://paste2.org/p/4873 could someone take a look?

ok thanks

Try another form: CASE WHEN field=1 THEN 101 WHEN field=2 THEN 102 ELSE 999 END

AlexC_, the backticks around the WHEN `38` etc may be problematic (though i can't say for sure)

INSERT INTO email (sender,sname,sdom,szn,rcpt,rname,rdom,rzn,numrcpt,numattach,size,mailref,xmailer,dates,times,flags,msghash,rplyto,forward,type,recnt,subject,timeadj,folder,utime,senderLoc,rcptLoc,insertTime,recpath,class,score,groups,ghour,gmonth,gyear)VALUES
('ryan.o'rourke@enron.com','ryan.o'rourke', 'enron.com','com','ryan.o'rourke@enron.com','ryan.o'rourke','enron.com','com',1,0,733,'100393307630947410.1075858634498.JavaMail.evans
any idea why that query wouldn't work?

Of course, assign that to whatever you want.

d'oh, is it because of the ' 's?

mike8901_, quote escaping problems

replace with \' ?

so have no SET = ?

I didn't show the SET.

mike8901_, thats one option, but a lot of libraries to mysql have an escape string function of some kind as wekk

SET fieldx = CASE WHEN field=1 THEN 101 WHEN field=2 THEN 102 ELSE 999 END

gsm4, eh?
I'm using c#, btw.

To place a single quote in a string/varchar, the standard calls for '' ... example: 'That''s correct' ... MySQL allows \' as well.

hey, any idea on this one? http://pastebin.com/m73c7a7a6

is either better?

But you can also have other issues with the external language, php, c, java, etc.

hum no that's not working either

yeah I had to double escape \\' to get a single quote
I think '' is better

What is the exact error message?

AlexC_, did you try it without the backticks on the values? i.e. around 38 etc?

Did you notice I didn't use backtics or quotes?
Don't add unnecessary fluff unless you know what you're doing.

gsm4 yes I removed them ... error is: 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1, in /foo.php on line 107

Error INSERT INTO email (sender,sname,sdom,szn,rcpt,rname,rdom,rzn,numrcpt,numattach,size,mailref,xmailer,dates,times,flags,msghash,rplyto,forward,type,recnt,subject,timeadj,folder,utime,senderLoc,rcptLoc,insertTime,recpath,class,score,groups,ghour,gmonth,gyear)VALUES
('ryan.o'rourke@enron.com','ryan.o'rourke', 'enron.com','com','ryan.o'rourke@enron.com','ryan.o'rourke','enron.com','com',1,0,3260,'10039743182047280.1075858635378.JavaMail
hm
arghghg

mike8901_, php?

same problem
c#

ryan.o'......

yes, I see

http://doc.otrs.org/2.1/en/html/c238.html#installation-on-windows -- I wonder if they have a license....

I thought I fixed it in my code

Oh. A php error. Looks like a problem with constructing the SQL string.

whats wrong in such query UPDATE traffic SET impressions=impressions+1, amount=amount+0 WHERE id='30345'

id=30345

not a PHP error, it's a MySQL error .. ?

mike8901 use the real escape string function whatever its called in c#

The error suggests a string construction problem.

most probably, I am dynamically constructing it
I just can't see whats wrong

Maybe you didn't end the sSQL properly.

tibyke, why not quoted?

Syntux, it was ...'foo'bar'....

tibyke, sorry, didn't get it

ah, wrong thread, sorry

http://paste2.org/p/4877- thats what I have atm

echo the string before executing it and show the final SQL in the pastebin.

Syntux, its integer, no need to quote

that is it,

No END ??
SET fieldx = CASE WHEN field=1 THEN 101 WHEN field=2 THEN 102 ELSE 999 END

Ah didn't know I needed that

I typed it.

doh, must have miss-read it =D sorry
hum, though - still not working

Try something smaller. Test it in the mysql command line client first.
or query browser.

alrite, hold on

It's really a waste of time to shoot blindly in php without knowing the *exact* SQL you want to construct.

tibyke, ah cool, thanks

thanks, I have it working now

You're welcome.

good afternoon all. is there a way to add an index to a table and not have it throw an error if there is already one there by the same name?
(sort of like IF NOT EXISTS)

i have 2 users with full permissions. how do i revoke their privledges to specific databases?

what is the proper term for using PASSWORD() in order to make it impossible to reverse password?

huh?
you mean like PASSWORD() creates a one-way hash ?

yes
what is that term called
is that encryption?

that's what it is called
a hash

hmm
okay thank you

it isn't technically encryption since you can't decrypt it

that what i thought

a hash is one way, encryption can be decrypted

just making sure
so saying one way hash is redundant?

pretty much yeah

okay cool
thanks!

any techie would understand hash to be one-way

I dunno - what if your hash algorithm is x + 1

so how severe is that considered a security issue if you do not hash your passwords?

then it isn't a hash function
that would something akin to a Cesar cipher

should I do set global max_user_connections=150 or rather edit my.cnf ?

that is a pretty horrid question. what algorighm are you using, what is the key, how is the key stored?

edit

well i am learning/researching this

a pure edit of my.cnf doesn't affect the live server, so you'd have to restart mysql for it to take affect.

so i know of two methods to store a password
varchar and varchar with PASSWORD()

"WHERE [usercontent] ORDER BY [usercontent]” – is there anything they could do besides subqueries and unions that would have a high risk of compromising the server?

thanks

It’s not a very good hash function, but I don’t know of anything in the definition of a hash function that says it isn’t one.

i found this in my.cnf “set-variable=max_user_connections=20
“
i feel like it’s wrong syntax
i’ve set it this way [mysqld]
max_connections=150
max_user_connections=150

? did that help?

not really. if you encrypt it rather than hash it, then you have to have the algorithm and key available to decrypt it. there are different algorithms to encrypt data. DES, 3DES, AES, etc. The benefit to a crypt is that you can unencrypt it and display it. with a hash you cannot

nm

Anyone? I’m sure I’m overlooking something exploitable…

so it all depends on: if some one gets your database can they decrypt the data? if they do, does it matter? do you need it decrypted? those sorts of questions
seems like unless you sanitize your variables you’d be open to an sql injection there
drop tables, etc

Rikaelus, allways use real_escape_string

They’re not able to fake a second query, PHP’s MySQL functions disallow that

or prepared statements.

so perhaps you should ask in #php ?
(aka, i’m not sure what types of things php can and can’t do wrt sql injection )

does mysql provide a method of having a unique column in addition to the primary key

Well, I’m wondering if there’s anything else they could put in the query that’d compromise the server. I’ve anticipated faked secondary queries, subqueries, and unions, already. I suppose they could freeze things up nicely with an uncontrolled “order by” clause…

you mean a UNIQUE key ?

Yes. UNIQUE(field, list, here) in your create/alter table statement.

It’s just a very bad idea. Handle this another way.

I’m fairly sure, but not possitive, that subqueries and union would be the only ways available to them to bring in additional tables, since they have no injection capability prior to the “where” clause
heh.. easier said than done =(

does it have to be of a particular type

The field? No.

You really don’t want them providing direct SQL at all.
Not even fragments, unless you do something advanced, like designing a strict scanner/parser for the fragments.

When you consider how complex a WHERE clause can legitably be, do you have any recommendations on alternative ways to give them the same flexibility outside direct injection?
eh.. you needn’t answer that; not really a MySQL question there

That’s like wanting to provide a way for users to inject C code into a C program safely. The damn designer of the program probably could never write the original program safely, let alone some poor user.

the best (only?) way to access mysql through java is JDBC right?

It’s just not practical. provide a strict framework to generate the SQL.
If you really think you need that capability.
and make sure you provide role / access restrictions where appropriate.

Rikaelus, contain them to the use of provided indexes eg drop downs

Just from internal uses I know I do. These queries are sitting behind a webservice, and the user-specified where-clause is necessary to reduce the amount of data being passed to the client to only the required subset. Else the client can be overwhelmed by data. When talking UIs, that can add
very long seconds to load times.

anyone got the sytax for UNIQUE on ALTER TABLE ?

Unforutunately I’m dealing with a new webservice that, in addition to being used internally, will be accessible by our customers, themselves.

Google: mysql alter table
It’s right there.

Rikaelus, it will be brought to its knees by your ideas at the moment
unworkable

do i want an index or a key

Which is why I was hoping to control the chaos

control by not allow

Specifically disallow things that will put it on its knees, and let everything else through

Any simple cross join, even by mistake, could kill the system.
How do you know when it will result in bad behavior .vs. good?

Rikaelus, wikipedia was killed a number of times by such queries

Have I missed an avenue they could take to create a cross-join?

Rikaelus, you will never guess the stupidity/cunning of users

I worked technical support for a few years. Don’t bet on it. =P

You’ve only said you covered it. I don’t know. Are you disallowing subqueries in the WHERE clause? So field IN (select… ) is not allowed?

My current plan was to run an “explain” the query ahead of time and look for any table inclusions of subquery and union types. If detected, the query wouldn’t have executed conventionally.
I should also point out that users won’t have visibility into actual table or column names. All of those are masked; being presented to and accepted from them in a different format. Granted that doesn’t prevent them from taking wild-guesses at what the names truely are.
uI should also point out that users won’t have visibility into actual table or column names. All of those are masked; being presented to and accepted from them in a different format. Granted that doesn’t prevent them from taking wild-guesses at what the names truely are./u
Thanks for hearing me out on the concept. It’s often hard to find that much for crazy ideas in IRC. =D

i cannot login via phpmyadmin anymore, when i edit my mediawiki articles i get ý instead of e.g. ä; on the other hand programs (like amarok) can login to the db normally. any ideas?

What happened since the last successful connection?

i am not sure; i installed a php script-collection called ITMS

Maybe you lost configuration in php.ini. Hopefully that’s all. Do you have a backup?
or whatever you use for php configuration.

i guess so – i will check (for) the backups and do diffs …
by the way: do you know if it’s normal that i cannot change the char-coding at the login screen of phpmyadmin to something else but utf-8?)

It doesn’t sound like you did anything to the database. This sounds like a php issue.
Don’t you want utf-8?

utf-8’s probably your best option..

i want it – but i don’t know if the char-enc is related to the pass i provide

I don’t understand.
That wouldn’t be user specific.

i mean if the pass i have contains an “ö” and it was stored in iso then it would be a different character in utf of course

i keep getting 1452 error when trying to add a FK?

but as i said i dunno if thats the way i think it could be

how can I (using the command line) get a count of rows in a table? I need it to be highly efficent, since it has around 500k rows.

You can’t login, yet you can edit your articles. Explain that.

mike8901_, select count(*) from table;

is that efficent?
i.e. how long would it take to run on a table with 500k rows?
roughly speaking

instant for myisam

myisam? is that the default?

its a maintained number

for example the user “wikidb” which connects can (as it seems) still login to the db – while i cannot (using the same username)

not really instant, but it works
it took like 5 secs to run

innodb then

then again, I am doing lots of queries to the database

but i just saw that my php.ini was changed 4 days ago – something comes to my mind – mom

If that character was stored with ISO, it won’t equal the same character passed as UTF-8

I’m storing all the enron emails in a mysql database

Or at least I don’t think it would

php would be my guess, not mysql.

i guess you could be right. i remembered changing the php.ini for installing egroupware. i added “mbstring.func_overload = 7″ …