you're the first person to actually thank jbot in a whie
*while

What can I say, I'm a *polite* asshole

i'd thank him fi i knew how to use him more :-/
but he doesn't do anything for me except tell me he doesnt care if im nailing my wife

hahaha

rawbdor, im… disappointed

~tell rawbdor about don't care

rawbdor, I guess the factoid 'don't catch exception' might be appropriate:
rawbdor, don't catch exception is http://findbugs.sourceforge.net/bugDescriptions.html#REC_CATCH_EXCEPTION and http://checkstyle.sourceforge.net/config_coding.html#IllegalCatch

darnit.

[krustofski]: yes. it's not that good. which is what makes it funnier.

rawbdor, http://youtube.com/watch?v=kBVmfIUR1DA

~tell mom about jboss

The user mom is not on ##java

just cant beat that

he's been on bunches of tv shows and such

sunrise.
and in 10 seconds my whole room lights up. love it

[krustofski]: ever seen the singing-in-the-rain car commercial? http://youtube.com/watch?v=r1q98m7qJ8g

y'all need to see http://youtube.com/watch?v=ScwI7c5iiRo, while we're throwing yout00b links around

ye i got one too
one sec

lol

(this channel is temporarily a series of tubes)

dont clog them

http://youtube.com/watch?v=GOdtmdkN5dE

sec i'm only on ojacobson
's

rawbdor, talk about an ad agency that id never use

watch his hands closely around 2:15
he's playing the song himself.

interesting

anyone seen uk pool yet?

g[r]eek: not up to it yet
…………..
no way

heh

nah thats fake
he disappears halfway through the flight
but yeah i laughed hard at first
=]

i just didn't see it coming. i was like "ok, slingshot into pool cruel but funny"

yeah
exactly

and then wham

lol
…. oooops
g[r]eek: hah hidden pool

*boggle*
commons-lang has no way to concatenate an arbitrary number of arrays? Foo.

java in general doesn't have a way to do that. simplest method is to toss them into an ArrayList and then toArray() it, but i doubt that's as efficient as manually copying

Yeah. Writing my own was two minutes' work. I'm just impressed that neither Arrays nor ArrayUtils has it.
http://rifers.org/paste/show/5245
Not null-safe. Sosumi.

arrays are pretty much only used for fixed-size lists. if you know your list is going to expand/contract, Collections are just easier

chocolate rain is cool

or for feeding to MessageDigest, which is what I'm doing with them (and why only byte[] is implemented)

something about it. can't explain it

I just needed a dummy Digest algorithm that could consume an arbitrary number of chunks, to test something
no null digest in the security AP
er, API

why not use System.arraycopy()?

hole in my mental index of the API
http://rifers.org/paste/show/5246
It still passes unit tests; ship it!

ojacobson nice.

Anyone have any suggestions on unit testing something that talks on JMS topics or queues?

that email you cc'd me was sitting in my spam box this entire time. i only noticed it now. regardless, it's borked, as expected.

0am and i am eating
that's not typically a good sign

i ate at around 9pm, then again at 1, at 4 and at 7
am

heh

:P

yeah but i intend to go to bed soon =P
if you ate at 7 and then went to bed that'd be bad

sleep. pfft.

heh
more like boredom

no i haven't been to bed yet. im going to eat again in an hour or two
1am

does anyone know a short but clear comparison of List, Array, Map [maybe others too]? I'm trying to understand when i should use which
can list have mixed Object types? say, first is String, and the four other elements are integers

read thinking in java

I've sort of read it
but I can't remember

"yes, but don't."
Make a class to hold a String and a list of integers.

~tell daredemo about collections

daredemo, The Collections API is available at http://java.sun.com/javase/6/docs/technotes/guides/collections/index.html

Or a class with a string and four integer members.
Whichever's appropriate.

~tell daredemo about pojo

daredemo, pojo is Plain Old Java Objects, as opposed to things like EJBs.

~tell stickto about List

stickto, http://java.sun.com/docs/books/tutorial/collections/interfaces/list.html

oh, cool, javabot

~useless tell

when telling yourself something, just use ~something, not ~tell me about something or ~tell MyNick about something. Save those precious fingers. Mmm, Cadbury's Fingers.

ojacobson, and others, thanks

thanks
~List

http://java.sun.com/docs/books/tutorial/collections/interfaces/list.html

(test)
~javadoc String

I don't know of any documentation for String

the mental effort you'd have to put in later to understand what your list held sucks.
I've been there.

I like the bot

javadoc offline

~javadoc java.lang.String

I don't know of any documentation for java.lang.String

yeah still…
was wondering
it's been offline for a while now
~botsnack

g[r]eek++

ojacobson, I read something about "generics" today, can I make my class generic to store those String, and int values?

~botsnack

salve

if you know the types of all the items it'll hold generics buys you nothing on that front
but let's say you name it NamedFourDimensionalPoint

treat it as you would a struct.

then you could have a ListNamedFourDimensionalPoint

~tell daredemo about pojo

daredemo, pojo is Plain Old java web host Objects, as opposed to things like EJBs.

(stupid name, but you get the point hopefully)

agree with ojacobson

yeeees. agree with me.
give in. that's it. it's so easy.
*creep*

mmm pojo's

pojios. Part of a complete breakfast!

_

one more question, I've learned how to deal with paths and how to access local files, but, how do I access, say, a .txt file that is stored with .jar? and can I modify that .txt after I've made the .jar (by running the .jar itself)?
stored within .jar*

getResourceAsStream from the classloader
And no, you can't modify it without repackaging the JAR
but you can, eg., use it as a "defaults" file to create one outside the JAR if it can't find one

ojacobson, how can I make a program selfdestruct

#!/bin/rm $0

hi peoples

there are other libraries out there that do stuff for jar java hosting and zip fiels

ojacobson, but if the user is on windows

there's one library called truezip which is a virtual file system for zip / jar archives

i the Calender class i am using Simple date formater, i want to have the hour from 1-9 in 01,02,03 etc format..

why? why would you do that?
user has perfectly functional delete key

how can i have the hour in that format, having 0 if hours are between 1-9

ojacobson, what if I made a demo product that I don't want to gripple in functionality, but I only want them to run it 5 times?

Then have it (somehow) detect that it's run 5 times already and print a message on startup, then exit
("print a message" / show a registration popup / show information on how to buy the full version)

ojacobson, but this check would have to be stored outside of the .jar, right?

To answer your next question, no, there's no countProgramRuns API; you'll have to write something (And it's fairly futile; you'll be keeping honest people honest at best

unless you use a library like truezip
which lets you access the jar directly

and even if it's inside the JAR, never rely on users not knowing how to edit JAR files.

rawbdor, got it

ojacobson is right. users can just unzip it and change the file. it's just a zip file

some versions of winzip even add "unzip" context entries to jar files

ojacobson, well, I was thinking of targetting CEOs [of local small companies] and I hope they aren't much of hackers

you're in for a rude shock then… not so much hackers as "no more moral on average than anyone else"

laugh

Anyways, use the preferences API and store the activation count that way
at least then it's buried in the registry

preferences and registry are new to me, I'll have to read that, where would the "registry" be? on the opsystem?

anyone have any ideas on how to force some flushing from a Process object to its output stream?
i doubt there's a way

~pastebin

http://eugeneciurana.com/pastebin

~pastebin

http://eugeneciurana.com/pastebin

I like http://pastebin.ca/ better

b

how can you call getResource from within Ruby, I tried .class.getResource but that doesn't seem to work, nor does .class.getClassLoader, I also tried variants with .to_java_object.class.getClassLoader and .java_class.getClassLoader, nothing seems to work
crap, wrong channel, sorry

hmm any ideas how to create a second ticker? i have a downloader thread that i need to be able to throttle by seconds (download 500kB and then wait for the next second to start)
currently the downloader is just a thread that gets data into a 1024 byte buffer, and writes it to disk

morning

ill ask again since its been quiet
hmm any ideas how to create a second ticker? i have a downloader thread that i need to be able to throttle by seconds (download 500kB and then wait for the next second to start)
currently the downloader is just a thread that gets data into a 1024 byte buffer, and writes it to disk

java.util.Timer

yea, but i was thinking more about the actual implementation

else wait until second changes

idle loops hog cpu

sleep/wait do not hog the cpu

so basically, this.sleep(second-timepassed);

Idea M2 was released today btw ,)

Hi
you cant use a value you got out of your select that you have created for example IF(ParentIDIR.`IDIRHusb` = t.`IDIR`, ParentIDIR.`IDIRHusb`, ParentIDIR.`IDIRWife`) As `OtherParentIDIR` in a LEFT JOIN? for example LEFT JOIN tblir ParentName ON ParentName.`IDIR` = t.`OtherParentIDIR`
Nop/Yep?

!m current_timestamp

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

Yep/Nop?

can I backup a table's structure only instead of data as well?

mysqldump –help | less

no ideas on my problem?

Try it out, does it work?

Nop

if I use mysqldump in a script, how can I have it create a dump file with the name dbname.mmddyyy.sql where mmddyy is month, day yeat

thats why i am asking hoping for an alternitive

kieran491, just write it as a join with sensible where clauses

well i need it to do the IF stament to find out what i needs to join
*it
so theres no other way i could do that?

Couldn't you do LEFT JOIN on both clauses then do IFNULL(`husband`, `wife`) AS `other` and the other will contain the correct one?

neither are null

Do two JOINs like I suggested then have IF(ParentIDIR.`IDIRHusb` = t.`IDIR`, ParentIDIR.`IDIRHusb`, ParentIDIR.`IDIRWife`) As `OtherParentIDIR` like you've got…?
Your naming convention is throwing me off a lot here :s I find it really hard to read

yeah sorry i am about to redo it all
Unknown column 'ParentName.IDMR' in 'on clause'
LEFT JOIN tblir ParentName ON ParentName.`IDMR` = `OtherParentIDIR`

greeting… can i have a procedure argument of type SET?….

You should now have two LEFT JOINs on each of the husband and wife clauses, you are then selecting the result that isn't NULL

greeting… can i have a procedure argument of type SET?….

should be fine

then what is wron with this - CREATE PROCEDURE guestSearchMember(languages SET) BEGIN select 'fr' in languages; END;

set(values,….)
!man set datatype

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

!man datatype

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

check the set datatype defiinition
!man create table

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

silly bots

be nice to the bot

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

i love you bot

4 in this case. Our sites were down for quite a while before someone noticed

why wouldnt a really busy msyql push that LA higher

proc load or disk load

busy? since it locked out all other connections, can it be considered busy
time to get monitoring

well, with 120 threads waiting to execute, shouldnt it be higher than 3?

waiting means sleeping

ah, ok

waiting for disk means low cpu load

hm, so what do people typically use as an alarm metric for a database server if thats the case? a bad query can kill the application but the server itself isnt completely overloaded (at least from LA, CPU, etc)

monitor the number of connections as well

ok how should i describe my SET type procedure parameter… could someone give a tiny example

you provide values in the set

thanks guys

don't forget to thank the bots!

7 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't
when im trying to start mysqld_safe –user=mysql

check your installation

have you just installed

yes

then you did not run mysql_install_db

but it ain't working again
whould would be wrong with - delimiter // CREATE PROCEDURE guestSearchMember(languages set(10, 89)) BEGIN select 10 in languages; END; //

8 [ERROR] /usr/local/libexec/mysqld: Can't find file: './mysql/host.frm' (errno:
i get this now

hi guys, I want a table containing list of all countries, and their codes, plus a table containing all cities in all countries, any idea where to get that?

junis from a glance language abuse
n215 that a permissions problem now

exist something like random(number) in mysql?

random

http://jan.kneschke.de/projects/mysql/order-by-rand/

thanxs

what language abuse

junis are you trying to make languages into a user variable holding an array

yes… that is what i like to do
….is there another way that it is done

he something like update table set randome(number 25) where colum = 'x';
is that correct?

add quotes, set works with string
!m EMPER0R rand

EMPER0R see http://dev.mysql.com/doc/refman/5.0/en/mathematical-functions.html

uhmm that rand not is .. i need it.. .. need an expresion to make example random(numberX) and out a number between 0 -to- numberX

Lo.
select trackingno,date,time,B.email from HHC.hhcqueue H JOIN (
select email from veritrac.user U JOIN (
select UC1.ukey from veritrac.userco UC1 JOIN veritrac.userco UC2 ON UC1.pkey = UC2.pkey
where UC2.ukey = '_1VH0H5PHX' and UC1.ukey != '_1VH0H5PHX'
) A on U.ukey = A.ukey ) B ON B.email = H.email
WHERE H.status = 'C'
order by recno desc limit 25;
Oops
let's try that again
which is better. SQL A or B? http://rafb.net/p/puVCkx34.html

Briefly looking, A is generally better. What does EXPLAIN say?

I'm trying to figure that out.
I'm a bit confused of that output.

Paste the output in the pastebin

http://rafb.net/p/PF8xFC61.html
I know the filesort and temporary is bad

how is the explain looking if you only use joins?

is it ok to ask installation questions in here?

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

I mean not joining subselects but really joining the tables

Basically I'm using a laptop at work that belonged to someone who has now left the company. He had a copy of mysql installed, but neglected to inform anyone of the password. I've tried reinstalling after making sure all the directories have been removed completely but it's still saying it
can't apply security setting without the original password

what do you mean?
I'm not very good with joins.

mom

mom?

one moment please

groupwise max

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

Ooh

maybe this! (hope it works) select trackingno,date,time,B.email from HHC.hhcqueue H JOIN veritrac.user U ON B.email = H.email JOIN veritrac.userco UC1 ON U.ukey = UC1.ukey JOIN veritrac.userco UC2 ON UC1.pkey = UC2.pkey and UC2.ukey = '_1VH0H5PHX' and UC1.ukey != '_1VH0H5PHX' WHERE H.status =
'C' order by recno desc limit 25;
bmaybe this! (hope it works) select trackingno,date,time,B.email from HHC.hhcqueue H JOIN veritrac.user U ON B.email = H.email JOIN veritrac.userco UC1 ON U.ukey = UC1.ukey JOIN veritrac.userco UC2 ON UC1.pkey = UC2.pkey and UC2.ukey = '_1VH0H5PHX' and UC1.ukey != '_1VH0H5PHX' WHERE H.status =
'C' order by recno desc limit 25;/b

?

Anyone have a workaround?

!tell locellcount about reset root

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

holy crap henrik_.
That worked crazy well.
Thank you.
Wherever you want.
hi bitwise5
i can hear you

ok, thanks

giving it a go, but it seems to just hang. I'll leave it for five and see what happens

Try the last approach on the page. It seems to work the most reliably.

is there a way for me to set a variable in the system startup that user code can later look at?
Like I need to set a variable that tells what "type" of server the code is running on. Where type has meaning to the code my developers have written, not some mysql hosting server type.

you could set up a table to store that.

the lead developer wants a variable we set at server host start time that all his stored procedures can use for some logic decision he needs to make

which do you mean? I'm not using the enterprise edition

that's my thought, but he's dying to do it his way. and as the company founder, he gets his way if it's possible

Farther down, starting "Alternatively, on any platform,"

ah, now I see it. I'll give it a go

There's an option to run given sql hosting for each user connection. You could populate a user variable with that.

was it "init-connect" ?

thanks, what would I search the docs for for that session-init-sql thing?

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

how can i select all rows duplicated?

dupes

find them with select count(dupefield) as qty,otherfields from table group by dupefield having qty 1

uhmnot workme

hi all, do you know where can I get a database hosting of world zip codes?

Alloosh, google

I have a question about temp tables…. If 2 seperate people try to create temp tables with the same name at the same time will they have some sort of collision….

the world does not have zip codes thats a usa thang

canada has postal codes

and uk

many people said google, and I hate it when people say that, because there is nothing there

Alloosh, thats bullshit.

There is nothing on Google?

Alloosh, in the UK you pay for the list

the world has zip codes, and there are some sites that offers them.

walterwoj, no, temporary table names are distinct to the connection

there are some, but will you pay 1000 USD for 3 months?

Thanks that is what i needed….

Iam sure that there is open source data, and I asked just in case if some one know where they are

there cannot be open source postcode databases for some countries

there are, and I have one here on my machine, but the classification of cities is very detailed, which makes it not usable. there are listing the areas not the cities

read what I said not what you assumed I said

and there is also world database on mysql.com. just search world database, but it lakes some stuff
I read what you said, and please give me a country where there are no postcodes?

hello
hey, in a query with a group by… how can I count the final number of all rows resulting from the query?
count() returns the count for each group

there is a numrows function depends on the api

but that would have made the database process the whole select instead of just counting stuff
I guess I can do a SELECT FROM SELECT

!php mysql_num_rows

int mysql_num_rows ( resource result ) Get number of rows in result http://php.net/mysql_num_rows

archivist, but I use limit in my real query, I just need to count the total for the non-limited query
(as for pagination)
generalli I do a SELECT count(*) FROM …rest_of_the_query and then a SELECT * FROM … LIMIT x,y

ah then there is another for that somewhere
offsets

than the id's already passed.

Yes, offsets for pagination suck. It's why some forums close down threads when they reach X pages.
Because the forum-writers use sucky pagination.

what's the way of doing it then? if not using LIMIT

see what the wench said

oh

MediaWiki is a good example of software that does it right. Because, they kind of have to.

also discissed on..
random

http://jan.kneschke.de/projects/mysql/order-by-rand/

http://en.wikipedia.org/w/index.php?title=Grand_Duchess_Olga_Nikolaevna_of_Russia&offset=20070820014048&action=history
offset=date

but anyway, one still have to do a count(*) to get the totals, right?
otherwise I don't see how

u

why would one want to use a Snapshot to backup rather then use mysqlhotbackup/innodbhotbackup/myisamhotbackup ?
it seems that snapshots are the way to go.

does mysql have a function whereby I can take a date in a given format and extract the mysql-friendly version of the date?
I see functions for FORMATTING dates, but not conversion from the non-standard format to the standard format

STR_TO_DATE()

ah, sweet
I don't know how I missed that one on the page.
thanks, archivist

O10xz, I can't speak for anyone else, but snapshots are essentially instantaneous, irrespective of speed.
Er.
Irrespective of size.

ah, okay,

But be sure you don't have too many of them running at once.
The way they're instantaneous is that they use copy-on-write.

hrm, could you explain copy-on-write a bit?

I.e., every time you change something that's been snapshotted, the disk has to seek and write twice, once to update the real thing and once to make sure the snapshot isn't updated.
I plan on flushing logs with read lock, snapshotting, releasing read lock, copying the snapshot, deleting the snapshot.
Preferably using ionice when copying the snapshot.
If you're using InnoDB, your life is made easier by transactions. It will basically do the snapshotting for you.
At least if you're content to use mysqldump, or you can afford InnoDB Hot Copy.
In both of those cases, though, especially mysqldump, it will take some time to restore.
The nice thing about a raw file backup is you just shut down the server, move the backup in place of your damaged files, and you're done.
Of course, if you're using InnoDB that requires restoring all InnoDB tables in all databases on the server, which sucks.
Of course, if you're using InnoDB that requires restoring all InnoDB tables in all databases on the server, which sucks.
But short of InnoDB Hot Copy, snapshotting is the best solution I've heard of yet.
If you don't have slaves.
If you have slaves, then just stop one for the copy and you're set.

well, I'm a bit baffled why you have to write twice.
it makes a write to make sure the snapshot isn't updated??

Because the reason it's instantaneous is you don't actually copy it.

hrm, so what are you doing?

When you make an LVM snapshot, it just says "This is a copy of this logical volume."
And so if you want the 65472th byte of the snapshot, just go look it up in the original.
But if that byte changes, in addition to writing the change to the original volume, it has to copy the original so your snapshot isn't damaged.

Is there a way I can track changes of a database. I am trying to figure out what my CMS is doing and I want to see what changed in the DB from one page load to the next.

hrm.

Backup … isn't there proccessing invovled?

xamox, I suppose you could use binlogs.
O10xz, only a very small amount.
It has to make a note of the fact that there was a snapshot, not actually copy anything.

so, when you have snapshotting enabled, two places are being written to at the same time?

Every time you write to one. Yes.

Simetrical, alright, I'll give it a look

Simetrical, so if I do an INSERT on a table, it will write to my snapshot volume and my original data volume.

At least as I understand it.
Yes, AFAIK.

so, when I go to actually take the snap shot, it just records the two points in time when the data was last snap-shotted, from the point where you are now. then it takes that and makes it an official "snapshot"?

I'm not sure what you're asking.
I'm even less of an LVM expert than I am a MySQL expert.
You should try Googling for info.

yea,

Or asking in #lvm.

okay, well I like talking with humans about information rather then Googling.
just my prefrence
I will Google now though.

How would I update all fields for all records called 'STATE' in table 'evac_states' with the same value

!man update

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

SELECT `State` from `evac_states` WHERE `State`='Te' VALUE 'Texas'

just no where clause
yes what you said ignore my comment

UPDATE evac_states SET `State`='Texas' WHERE `State`='Te';

What's the rule on using backticks for column names? It seems like sometimes MySQL 5 requires them, and other times it doesn't…
SELECT value FROM quicki_settings WHERE key='2';

thanks fellas

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 'key='2'' at line 1
If I wrap key in `key` then it work
*works
key is a varchar

Whenever I do /etc/init.d/mysql start, it fails. How can I find out why it's failing?

However, SELECT default_shipto FROM customers WHERE id=2 LIMIT 1;, works fine

!man reserved words

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

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

ah, thanks flung
yeah, I was just reading that

quotes

Use ` around identifiers (database/table/column/alias names) and ' around strings and dates. MySQL does allow " for strings, but ANSI standard uses " for identifiers (which you can enable with ANSI QUOTES option) and dont quote numbers

is it considered "bad" to name columns the same thing as reserved words?

It IS kind of annoying to have to quote column names
and it can also get confusing

"2.6.190.56","2.6.190.63","33996344","33996351","GB","United Kingdom" the file is around 6 mb how to import it?

GeoIPCountry
?

yes it is

aren't there instructions on their web site?

smart answer, and yes there are instruction for this one, but I want to do other files also, and they are much bigger than that also

LOAD DATA INFILE
I usually write a script to do it cause I don't normally want all the fields

is it possible of script a program that check every single row to take out the email web hosting field, determinate if it has a correct structure and then update this row again?
the question is the database has 1.000.000 rows

yes its possible

I could I know data by backing up this database hosting into a file but it seems to be very hard, doesn't it?

But why would you take out the e-mail field if it has the correct structure?
No
Shouldn't be very hard at al

the database has 2 GB
IS
is 2 GB so the file is going to be absolutely huge

The script will take some time then…

I'm having good performance with a 6gb database..

and what _Lemon_ says too

Takes 20 minutes for me to import 16 million rows and index the table.
Is almost instant to query the index table

I can write a script for that, but want to try doing it with the consol

wfq - Should take maybe 5 minutes

LOAD DATA INFILE

wfq — mysqldump
Then run the script.
It also depends how you write the script

but can mysql put only data into the file?

It puts ur structure and your data

or do I have to filter by my script?

Just back it up.
Then write your script and run it
This way, if the script doesn't work, at least you have a backup

jormenz Ihave got the backup already
I have done it

Ok
what language are you writing the script in?

but if mysql could write data only I could get them by source?

What do you mean?
what I suggest is doing a select on all of the rows with bad structure
Then doing an update on each of those rows

yes that is my idea

there may be a quicker way tho

well then I don't need to do it into a file I can do that by php?
forech row execute and inser into another table?
and I can save long time, what do you think jormez?
jormez_
and another question is
given two tables can I make a diff of them by using only mysql?
I mean, could I have a third table saying what registers are differents?

welcome

thanks

How do I do in the WHERE clause, "where" something != something
WHERE col != val

Just do !=

exactly as you said it

okay
Thanks

or !(col=val)

okay i need some help with a stored procedure
somehow i have this procedure set WITHOUT a database

Using just a query, is there anyway to avoid sql injection?

how do i drop this procedure.. if i dont choose a db, it says i need to choose one.. if i choose one, then its not valid as the procedure isnt in any db
are you using php ?

Trent, database abstraction layers help a lot.

Assid, C++.

Stored procedures are perfect, if you can use them.

well.. one thing you can do is addslashes to any user accepted data.. also stored procedures can help too

Or more or less perfect from what I've heard. I can't afford a MySQL 5 dependency in any of the software I habitually work on, so I don't know personally.

could you help me with this please?

addslashes is bad.
You have to remember to do it every time.

Simetrical, How do you use stored procedures?

Assid, as I said, I've never actually used stored procedures.

hrmm.. k
google for php mysql web hosting stored procedures
!sp
no bot here ?

.

!man stored procedures

see http://dev.mysql.com/doc/refman/5.0/en/stored-procedures.html

does my client version have to match the server version to do procedures?
or are they completely irrevant?
irrelevant*

brb

anyone know of a good tool to generate a database diagram from an existing mysql database (linux if possible)

alrite back
so can someone help me witht his stored procedure please?
holy crap its gone!

what are you trying to do with your MySQL stored procedure?

http://assid.pastebin.com/db3b92ff
that SP isnt inside any darn database
how does one get rid of it

drop procedure procedurename;
or drop procedure if exists procedurename;
do you have the code
and the problem
i see the definition there, but no code

Is an NULL INT = 0?

it isnt IN any database
how is it even possible ?
i dont know how it went there

How about subqueries in InnoDB? When I tested them in MyISAM, MySQL didn't use indexes in subquery.

so i'm executing a query on a table that has 600k records, it does a count(id), avg(score), and joins another table to use in the "where" clause
problem is, the query takes about 10-15 seconds to execute

EXPLAIN query may help.

yeah, i'm using that
it's not using a key
but i'm not really sure what kind of key i should be using on such a query
i'm not really "selecting" any specific thing

you'll need an index on the two columns involved in the join condition

Xinil, show the query

because they are needed to create the table-table relationship

and the EXPLAIN

also, listen to HarrisonF

EXPLAIN SELECT count( id ) AS totalVotes, avg( score ) AS meanVal, list_series_id FROM animelist, animelist_series WHERE list_series_id !=0 AND score !=0 AND list_series_id = series_id AND completed_episodes = ( series_episodes / 5.2 ) GROUP BY list_series_id;
list_series_id is indexed
so is "id"
and so is "series_id"

Xinil, AVG(score) will require a table scan, or at least a scan of the index. Cache that value somewhere and update it dynamically, don't recalculate it every time.

i want it calculated though
i'm "updating" stats for the whole site with this query, basically

So cache it in another table.

ah
so like
get the average "for each series" when some action is performed elsewhere?

You don't have to do a table scan to update it. If there are 5273, and you add another one, SET avgscore=avgscore*5273/5274 + newscore/5274 or something.
Of course you'll have to cache those somewhere per series_id.

SELECT 4+1 UNION SELECT 6+3 INTERSECT SELECT 3+6;

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 'SELECT 3+6' at line 1

Tman SELECT is a reserved word you could try `SELECT`

o.o
could someone explain that?

Then it will be something like SELECT totalVotes, meanVal, list_series_id FROM cached_stats, animelist_series WHERE list_series_id = series_id AND blah blah, something like that.

yeah i see, simetrical
guess i'll have to build that table

And keep it updated.

"SELECT SUBSTRING_INDEX(colum,'@',-1) AS username"… Is there any way then to use 'username' as a conditional in the WHERE portion of the statement, or do I have to repeat the whole SUBSTRING_INDEX() thing again (which seems rather inefficient to me)

yeah, each time someone interacts with "series", it should update the stats
thanks for the help

Flimqy, SUBSTRING_INDEX in a WHERE clause probably requires a table scan anyway, I'd imagine, so I wouldn't worry too much about efficiency.

the reason that MyISAM gets fragmented is because it has no concept of datapages, making each record vary in the length which it can actually be written in, correct?

I would recommend you use separate columns for the part before the @ and the part after, instead.

Well the efficiency part is that it's doing the SUBSTRING_INDEX() twice that way (or is it smart internally to only do it once?)

it seems like mysql isn't letting me chain two set operations together.. which doesn't make sense..

That would require rebuilding some massive tables… which may be done eventually, but not right now.

hr, what's the differnce between SUBSTRING() and SUBSTRING_INDEX()?

actually, mysql simply won't let me use INTERSECT or EXCEPT altogether

Flimqy, it only calculates the values to return after it's decided on the rows, so it can't refer to the returned values in the WHERE clause.

SELECT 3+6 INTERSECT SELECT 3+6;

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 'SELECT 3+6' at line 1

ahh, makes sense.

Tman SELECT is a reserved word you could try `SELECT`

Flimqy, the optimizer might be smart enough to only calculate it once, but it's a fast function, so it shouldn't make much difference.

So putting something in the WHERE… is faster than HAVING … anyway (which is how I had it previously)

HI

If you're scanning the table anyway, I doubt it makes a difference.

is it possible to CONCAT multiple rows in a subquery? for example, a user can tag news posts — i want to retrieve all tags for that news post seperated by a comma — each tag is seperate row that uses the user_id and news_id as the keys

WHERE is faster, I think, but AFAIK MySQL optimizes HAVING to WHERE anyway where possible
..

GROUP_CONCAT

awesome! thank you

what's the better idea, having a new field in the "series" table, called "series_average", or having a totally new table with cached_series_average?

Xinil, probably the former, since you mention it.
I mean, you already have a table for that.

i already have stuff like "series_total_members", series_total_votes, etc
just never thought to use "avg" there, heh

if I want to select the last 5 entries of a table? how do I dod that

is there an auto increment value in the table?

nop

Or some way to track what the "last 5" are?
MySQL does not internally track insert order.

well.. I have a sort of auto increment yes
i need to use ORDER BY table DESC ?

order by auto_inc_field desc
?

LIMIT 5

that too

ty
wasn't sure

You order by a column, not the table itself

yes. can I use WHERE with ORDER BY auto_inc_field DESC ?

Try it

yes that's what I'm doing.. I finally got it.. I was using LIMIT = 5 instead of LIMIT 5
my bad

hi

is it possible to make a fulltext search with two joined tables?
most of the examples i found only use one table for fulltext search
i'm looking for an example with two tables
is something like that possible?
SELECT * FROM a, b
WHERE MATCH (a.title,b.body) AGAINST ('database');

greetings, I am trying to write a select statement that retuns all the columns for a certain table

so you don't have to type out each column?

Is there any downside to doing a "slave start;", "slave stop;" when doing delayed replications?
With several minutes in between the start and the stop?

select * from table foo;

wog yes but that's a horrible idea
unless he's debugging
vooduhal it should work fine but how are you going to guarantee that it stays delayed?

sry, i ment minerale
but i don't think there is an alternative to this
is it?

Well, the idea is to remotely do "slave start" followed by some period for the script to sleep, then "slave stop" This will be fired manually.
It's not pretty, but it's the only thing we've come up with.

wog no but it introduces several potential issues when using it from php and other languages

mysql doesn't support INTERSECT or EXCEPT does it?

how often should you run OPTIMIZE TABLE ?

every 26 minutes

seroiusly.

er, i meant weeks

hehe

seriously?

i forget which one, 26 minutes or 26 weeks. look it up

how could you check how defragmented your DB is?
I have a question, if fragmentation is not possible within InnoDB because it uses datapages to stroe the results…then what happen when a row is greater then 16Kb ? wouldn't their be multiple data pages, hence causing fragmentation?

there is always a little fragmentation inside single pages
but if a record is larger than 16k is spans multiple pages

right, but what if the pages are on completely diffreent blocks of the hardrive ?

chances are they are
well sort of
innodb tries to insert records in adjacent pages

ah.
MyISAM just doesn't really care?

the average filesystem block is 4096 bytes so every page already spans more than one block

yea.

not that it really matters since getting innodb pages to line up with blocks is nearly impossible

so then, why is it often faster to use MyISAM for reading? what makes it faster for reading then InnoDB?

it's not always faster

How to optimize a table?

possibly better locking, and maybe a more compact storage format. It depends on the exact situation.

hrm, well, a lot of people say it usually is for reads, why would that be?
well i thoguth InnoDB's row-level locking was much better then MyISAM's table level locking

Also, myisam's indexes contain file offsets, innodb's contain the whole PK, which means that reading an index range on a non-PK is sometimes more expensive in innodb
Especially if you have a "big" PK

MarkR42 hit a few of the reasons

interesting.

O10xz, it's slower if there's not lock contention.

there are probably a dozen reasons why it could, and several to the contrary

innodb is often faster for primary key lookups because once it's walked the tree to find the correct key the row is already read

yea.

innodb also has more storage overhead

why's that?

Most database read-access problems in *our* application stem from IO overhead, so less storage is always better for us.

supporting transactions takes a bit more overhead

?

also innodb's pages always cause it to use more space than storing raw rows

yea.
shouldn't you be working to get most of your working data-set into memory?

It's difficult, and yes we should.

by properly indexing it, increasing either key_cache_size or innodb_buffer_pool_size.

We've already tuned those parameters as far as possible- one major problem is that those servers are running 32-bit, sadly changing them to 64 is very, very difficult due to our software validation cycle

also, by moving the binary logs off of the disk that the data is being written onbecause th ebinary logs write sequently and having your data striped to as many hard drives as possible/
just trying to help you as you help me

We've got approx 40 servers, each with I believe around 200G of data

so you're doing local storage?

which are queried in parallel, and written to almost constantly
We have one experimental SAN box in production

ugh sans blah

which looks very good and we will probably roll it out to the others

well, the best store is local 15k RPM RAID 10 w/ Battery Backed 256m each write-cache.

It's all a matter of logistics and finances
we haev a very large number of clusters geographically diverse
all these boxes are 1u rack units

interesting, are you guys thinking about going to NDB soon?

Absolutely not

why's that?

so how do you failover mysql with a san?

we've measured its performance characteristics and it's absolutely hopeless

ebergen, as in, if your SAN goes down?

yes

you can't really.

exactly.

lol.
indeed.

The individual units are not redundant

well, you have a very low chance of that happening because the SAN itself has many CPU's/harddrives/power/etc
so the chances of the SAN going down are next to impossible.

myisam is the only storage engine that has a hope of keeping up with the write speed on sane hardware
each db box will have its own storage unit

I've never seen/heard of it happening.

probably dedicated

MarkR42, why wouldn't InnnoDB?

Because it won't insert fast enough. Trust me.

well, we ran InnoDB on our SAN and it was actually faster.

How would I optimize a certain table?

OPTIMIZE TABLE `table_name`;

Thanks.

hehe
sorry you had to ask three times.
I saw it before but I was just to engaged.

We have quite a lot of lock contention on our tables, MyISAM's parallel writing system is absolutely vital to our boxes continuing to function.
If you only ever append to a table, myisam's insert performance is very hard to beat.

well, if you have a lot of locking contention, then wouldn't InnoDB be the best bet since it has row-level locking?

No, because there would be more contention
probably

erm…I don't think so.
locking contention is usually mitigated w/ InnoDB

we've done various measurements, and we don't know exactly why, but InnoDB never keeps up

I'm most positive of that.

Moreover, we have a large number of indexes
and we probably can't spare the io time to write the larger space they would consume on InnoDB

interesting.

It's effectively a big table written every day. Each day has its own table. There are lots (16 or so?) of indexes
It's got quite a few columns

interesting.
what kind of application is this?
certianly not web I'm assuming.

It has a web interface, yes, it is an email filtering application, used by a large number of commercial users (1 million+)

My bet would be logging.

ahhh.

Every email gets at least one row in one table

what's that?

Including spams.

Comment on MarkR42

ah.

MarkR42 do you have a battery backed write cache on the controller?

It's not really my department, but I believe so.

yea, well the SAN would have a large write cache.
what kinda SAN?

hello

3Par?

Again, it's not my department. I'm a developer

ahhh.
gotchya.

Our operations team manages the hardware operationally, which includes knowing what kind of IO the production boxes use
Most of our dev uses vmware, but of course we can

yea.

can't do useful performance testing in a virtualised evnironment

very true.

So we have a few (well, actually lots) of hardware boxes knocking around for performance testing

anyone here make incremental backups with log_bin files?

necrite_, you should use snapshots + binary logs.

hey

i have a problem getting my mysql to multitread

O10xz, where can i read about that?

multithread*

that's a good question, I'm not 100% sure, but you should look into how to create LVM snapshots.

"INSERT INTO table ( cost, amount_paid, amount_due ) VALUES ( 25.00, 5.00, ( cost - amount_paid ) );

when i have log-queries-not-using-indexes on, queries that use UNION appear in the log, even though both individual queries in the union use indexes. Is that just a "feature" ?

once you know how to do that, you should just write a script that takes the snapshots and then flushes the logs.

you could write it as ( 25.00, 5.00, 25.00 - 5.00 ) and it would work

O10xz, i ll try google
again

you said that "myisam's indexes contain file offsets, innodb's contain the whole PK" … MyISAM stores the whole index in memory Im pretty sure.

You know, you can't use LVM snapshots unless you use LVM.

I was not aware of that. I must check it out.

I think you're talking about clustered vs. non-clustered indexes.

We have issues with tuning mysql memory to the level we want due to the 32-bit problem
Loading entire indexes into memory is very bad

InnoDB stores the entire primary key + rows in memory.
MyISAM just stores the primary key.

Surely that means you can't have a table whose PK is greater than your address space, which is a severe restriction for 32-bit systems

There are extenuatiing circumstances. The query is written by a script that does not have access to all that data at the right time. Will what i wrote work?

I don't think our systems currently use tables that large

interesting.

1G would be impossible on 32-bit system

moreover, if something happened to reduce the available address space, it could render a table near the limit un-selectable from,
Which I've never heard of happening

Why aren't you going 64bit?

what would going 64bit do btw? why is this a large benifit?

its more bits!

We are going to go 64-bit, at some point, but it's very difficult to get there with our software validation cycle and other priorities

MyISAM doesn't store the *whole* index in memory, unless it's small enough. It caches it, in the key cache. Or so I've always assumed.

Because you can have much bigger index buffers

"Unlimited" memory for the buffer cache.

1G

32-bit architecture can typically address up to 3GB total, but a single application trying to allocate that much is a bit excessive.

There are extenuatiing circumstances. The query is written by a script that does not have access to all that data at the right time. Will what i wrote work?

No, it is not excessive, if that app is MySQL

Who needs that much?

what's "small enough" ?

Also, these machines have 8G or 16G of physical ram

O10xz, well, it's called the "key cache", so I assume it works on usual caching principles. It caches stuff that's often used, and leaves the rest on disk.

and isn't the key cache pretty much the same as storing it in memory?

Unless the whole thing can fit in the key cache.

currently only about 2G can be used by mysql

Have you thought of taking the risk of reducing commiting to improve write speed.

(excluding OS IO buffers)

Certainly my forum's indexes are larger than the 768M I've assigned to key cache.

yea, but it's storing that index in memory while it's being cached.

We're currently looking at delayed key writes, which appear to be quite useful

hence, key cache

Not the whole index.
At least not in general.

just an offset of it?

04 01Is this legal and do what i expect: "INSERT INTO table ( cost, amount_paid, amount_due ) VALUES ( 25.00, 5.00, ( cost - amount_paid ) );" Yes or no ?

Every COMMIT needs to write to disk immediately, if you reduce the number of COMMITs, innodb could catch up.

how would you do that?

There is a flaw in that argument - we're not using InnoDB

okay, so here's my question.. how does MySQL store MyISAM keys in the cache? does it store the whole key in cache or just a porition of it? if it's just a porition, what portion is it?

I know, but you talked about performance comparisons with ioondb.
s/oon/nno/

hello
how can I use mysqldump to back up a database without passwords?

without passwords to the db? or passwords in the db?

ie. i'm trying to write a script to backup the database, but if I do -u root then I need to do -p for password.
I can't script that to cron.
without pw to db.

The fact that we're not using innodb in one part of our service doesn't mean we're not using it elsewhere - and doesn't measn we haven't tested it

you'll need the password unless mysql allows the current user to run logged in

You can either specify the password in a .my.cnf located in the home dir of the cron user, or "-ppassword"

many thanks.
i will look in the manual for how to specify the password in a .my.cnf file.

In fact I've also played with Falcon and PBXT, and my colleages have been messing with NDBcluster
In our audit database we're using MyISAM, and doing batched INSERTs of something like 1,000 rows at a time

[client]\npassword=password

ok. many thanks.

There are typically a lot of write-clients in these servers, also many readers which are doing complicated queries
Almost all the writes are INSERTs though

is client mysqldump in your text or litteraly client

we never delete and almost never update

[mysqldump] would be a better choice. [client] is a generic option.

walterwoj, no

ok. many thanks

I think I just understood why you found MyISAM faster than Innodb. Do you have an auto_increment PK?

Yes, we do.

This means InnoDb has to serialize the writes anyway. So it can't be faster than MyISAM, which is simpler. You'll need something like Oracle Sequences to get faster.

walterwoj, on the other, the same thing can be achieved with a bit of juggling

We're currently up for anything to improve performance, changing to innodb would be major. My main concern would be the additional storage requirement
We could probably find some way to assign IDs other than auto increment

Get rid of the auto_inc. PK, generate Keys in parallel.
That is why Oracle has sequences.

innodb auto_increment is horribly inefficient
heikki needs to fix it

mysql does not have sequences, moreover we'd have to do quite a lot of work to generate these IDs, it would need to generate one for each row to be inserted in a batch.
I can envisage a system to generate them safely though

batch allocation of ids isn't a difficult issue

We're currently taking major advantage of myisam's concurrent insert behaviour
Of course innodb also does concurrent inserts because of its mvcc
But we would need some fairly careful testing to see that the extra locking didn't kill it

that's great until the system crashes and you have to spend hours or days checking tables

checking the tables does take a while, true
this is mitigated a bit because we generally only write to one (major) table
There is one table per day, and we (mostly) only ever insert into the current one

I have a general question, then I can post a specific example… but we have a query which takes ~.0.02 seconds to complete. It's a little complex, but not very complex. If we switch an "and" in the statement to an "or" it seems to completely hang mysql. We have let it run for 10 minutes,
with mysql taking up 100% of the CPU. If there's a specific general answer to why this happens, that…
….would be good, if you need to sqq the SQL, I can provide it. I cannot, however, provide the tables themselves. :I

Paste the query

The OR stops index usage.

OR tends to be much slower than AND.

If your AND is making a join, then replacing it with an OR could end up with a cartesian product, which is something you Don't Want.
Also, in some cases OR makes indexes unusable

ok, is there some document about this I can read up on? And how do you work around the problem?

Your best bet would be to understand EXPLAIN

!man EXPLAIN

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

Even if it does let you use the index, it requires a range reference instead of a const reference.

It's tricky, I admit

Paste the query and we might be able to help.

But if your query uses OR incorrectly, and is actually trying to do a cartesian product, the query may never finish.
e.g. it may be trying to output 1millon x 1 million rows

yeah, that seems to be the problem. Is there a simple example? (this table has ~6700 rows)

paste the query and/or the output from the two EXPLAINs

should I pastebin or paste to the channel?

pastebin definitely

any preference? something that might beauitfy it?

Are there any other XT:Commerce haters around?

http://www.pastebin.org/893

Ok, the problem is *probably* that it's unable to use an index which it was previously using
you should of course run EXPLAIN on the query with AND and OR and spot the difference

yeah, the or/and replacement is within the parenthesis, line 6

A common method of "fixing" this is by replacing the OR query with a union
This works well provided the query returns a small number of rows.

mrpoundsign, which OR are you replacing? Not the first on that line?

we're expecting couple hundred results.
OR (d.internalDocID=p1.internalDocID AND p1.name = "revision" AND p1.propValue = "1")
(when it's an AND, it's fast. )

Personally, I'd run EXPLAIN on it

Ah.

and consider replacing SELECT blah FROM blah WHERE x=1 OR y=2 with x=1 UNION SELECT blah FROM blah where y=2

That looks like a Cartesian join, then.

ok, will try that. If it ever returns.

I think.
Yeah, you don't have a join condition on p1 or p0.

it sounds like it, from what I have been hearing here. Because it's not returning.

You're going to get way too many rows.

Consider changing to using INNER JOIN syntax on the joins
It won't change the performance, but might make it more readable

goin to try it.

If it matches p0.internalDocID, it will return the entire p1 table, since there are no conditions on it.
And vice versa.
Surely that's not what you want?
If you change it to an OR, you want to remove the second jiveDocumentProp table, I should imagine.

so INNER JOIN on d.internalDocID=p0.internalDocID and d.internalDocID=p1.internalDocID ? and keep the OR?

Can LEFT JOIN slow down queries when we don't get any row from the second table and don't define condiction of second in WHERE?

Thank you, you all rock. It's working.

to move mysql 5.0 from one fedora 7 box to another i can just sftp /var/lib/mysql right?

is it possible to replace one table's "column" with another tables "column" without using some sort of language?
and by language I mean perl /php or something
oh wait nm I have an idea

Can you help me?

migrating data by copying the data files is a bit dangerous, some my.cnf settings need to be the same
You also need to ensure that the servers are both shut down at the time

assuming im using hte fedora stock config. I didnt change any settings and i `service mysqld stop` first

And of course they need to be the exact same version

yum update mysql-server # should tak care of that

It will probably work, but be sure that they're the same version, otherwise the "mysql" database will have the wrong schema and trouble will ensue

it appears to have worked

If it only took that long, your database is either very small or your network is very fast

will I know right away that its screwed up?

Not necessarily if there are subtle problems
Do you use fulltext indexes?
If so, make sure the ft settings including stop words are the same

its a 100BASE network and the dbs total less than a million rows
how do I check that? i didnt change any settings?

diff the my.cnf files
If they are identical, and it's an identical software version, you are *probably* ok
If you're using innodb, and it didn't throw any startup errors, that's probably ok. Trickier to know whether indexes are right if you're using FT and/or weird collations etc.

why does MyISAM have a binlog?

I'm not sure that MyISAM does have a binlog

is there a way to force a rebuild of all the innodb indexes?

well it has "binlog_cache_size"
as a GLOBAL VARIABLE.

I think you might be able to rebuild the table with ALTER TABLE x Engine=InnoDB; - check that as I could be wrong
which should rebuild the entire table

hhmm, the my.cnf md5 to different numbers, ill try to find a diff

(That will be really slow on a big table)

yea, really slow indeed

okay, the my.conf are the same (nwo)

Well, if the two my.cnf are the same before you copy
then you start the 2nd one up, it should be alright I think
Provided everything else is the same.
I'd rather hope it's the same version (ideally same build) of mysql.

its the same fedora rpm package, so it should be the same
thanks

is there an undelete??????????????? for a database??????????

lol

please say YES!!!
if not ima start crying lol

Sorry, not that I know of.

lol

is using grant all on blah.* to somebody require subject a good way to setup individual certs per user?

Unless you previously backed it up

nope :'(

I guess you're shit out of luck =(

*crys*
when my boss finds out about this….

innodb tables, with binary logging enabled?

i am so DEAD
innodb?

how can I run a sql and export it's result to a sql query?

binary logging?
how do i find out?

hold on…

look for files with bin and log?
ath yes and no
you can select one value and then pass it into another query via a function or so
what are you trying to do

i run xampp
does it do innodb + binary?

look in your data directory for files named after your db like 'db_name-bin.0001'. Big ones.

is using grant all on blah.* to somebody require subject a good way to setup individual certs per user?

nope

when you select InnoDb as your table type, and enable binary logging, you can use those log files to recover a database after a disaster. Which probably doesn't help you now.

nope…
mysql should keep a copy of deleted dbs for 24hours b4 removing them
or move them to /tmp
or something

well, lesson learned. If you're going to be responsible for a database, that includes attending to backups and disaster recovery.

mysql should do nothing
dba's shoulnt delete db

You might remind your boss that he is partially responsible for putting an untrained person in the role of a dba.

yes
and more importantly who is responsible for nightly backups

How do I get the rows in tab1.col1 that are NOT in tab2.col1? I played on that quite a while… thought that should be easy…

a not in b

SELECT a.* FROM a LEFT JOIN b ON a.id = b.id WHERE b.id IS NULL;

ok anyone??? how can I export the results of a query into a sql statement?

ah, that NULL thing… i think i read it yesterday, but forgot it thank you

export sql results into sql?

subquery, aka nested query?

the_wench, pardon for misspelling your name

the_wench is a bot
thanks wench

It's been a pleasure serving you, threnody.

smart guy…

basically I am trying to replace the contents of a table with the result of a query…
I can save it as a view but that's not really what I want to do…

http://dev.mysql.com/doc/refman/5.0/en/insert-select.html

ah ok cool i didn't know of such a thin
g

How would I best store a time interval of 30 minutes? As a timestamp?

anyone in here use mysql+ssl?

Hi. Is there a good practice for keeping a table with a history of all changes to rows? I've been searching all day but couldn't find anything good. Could somebody adivice me?

to what end?

enable the query log instead?

store the schema in a svn/cvs repos?

or use innodb and binary logging?

oh duh to rows not columns

a query I run returns multiple rows. I want just the one with the most recent timestamp. anyone willing to help this noob figure out the necessary sql syntax. i'm a bit puzzled.

winmutt, threnody hmm I need it easily accessible in the application. we need to know who change what and when.

i store updates in a mysiam table
you can setup triggers on all the tables you want to log

winmutt, do you copy the whole row? is it an identical table?

but i find that doing it from the app is better
you can store $_REQUEST etc
no i store the query

thank you again sir.

winmutt, hmm but storing the query wouldn't make it easy to read it.
it's not for reading if it's ever needed, but to always show it.

select col_a, max(col_time) from table group by col_a: ?

How would I best store a time interval of 30 minutes? As a timestamp?

try order by timestamp, limit 1

hello

threnody, thanks. I guess i figured that must be a hax, b/c it's what I stumbled on. no offense

you don't store time intervals. you can store time values as a datetime, a time, or a timestamp.

555-555-5555 ?

Caliga, won't that just return one row overall?…as I said I"m a noob

I know the different types I can store it as, I'm just wondering what would be best to store '30 minutes', not an actual time.
Rather, not a specific time, but just a length of time.

that looks like text, eh?

didnt you want just one row? but listen to threnody, he's right

you going to do anything with it?

abbbb, you could store the int minutes, our * 60 if you need to keep track of the seconds. it seems like a good approach to me.

Yes, but I should store it as a time, no?

thanks a ton y'all. great help

Yes, add it to a timestamp stored elsewhere.

oh, i see what you mean. your right too

…]to create a new timestamp.

will it always be some amount of minutes, no seconds?

Yes.
Maybe hours, maybe days.
But no seconds.
So a range of 1 minute - 99 days would be fine.

I'd think about storing it as an SMALL INT. Then use DATE_ADD() to add the value to your timestamp.
date_add(`col_timestamp`, interval `col_int` minute)

ah, great
thanks.

you'll need a medium int to store minutes for 99 days.

And just store the amount of minutes as a small int?
ok

unsigned

threnody, is there any way to do what you suggested without actually selecting the max(col_time)? I mean, I really don't NEED that time in my selected table. If it's "mandatory" to make it work i'm ok with that.

okay

need to execute command mysqladmin -u someuser -p old-password … prompts for password and says too few arguments … what am I doing wrong?

pidgas, select … from … order by yourtimestamp desc limit 1;

anyone in here use mysql+ssl?

That makes alot of sense, thanks alot.

just do 'mysqladmin -u user -p'

needs specifically old-password

it should prompt for a password

it does
Too few arguments to change password
"

diogo, that only returns a single row overall

pidgas, hmm isn't that what you wanted? I must have missed something

you need to use the mysql root to change the password of other users.

do you guys know what is the term used for history tables to log the updates to rows? audit(ion)? history? log? trail? so I could search some techiniques

you want to group by col1, get the value of col2 where col3 is the max?

prompt?

each person in my table has multiple entries in a certain column. I just want the most recent one for each person.

order by timestmp

http://dev.mysql.com/doc/refman/5.0/en/set-password.html

mysqladmin -u user -p password
…………

-ppassword?

4.1.1?

select person, entry, max(date) from table group by person.
the max(date) selects the particular entry for that person.
http://dev.mysql.com/doc/refman/4.1/en/set-password.html

threnody, yeah, that's what you mentioned before and that's what I have in my query. really, i'm being compulsive so I"m sorry to be a pest. Just don't really "need" the max(date) as part of my selection. Just want to use it as part of my selection criteria. does that make sense? if it needs to
be there…no problem if there's another way to do it, i'm all ears

you could mess around with a WHERE that uses the max(date) value. I dk if it would work.

usually complains "invalid use of group function" when I try that
so i tried putting it in a "having" clause and that didn't seem to work either

the perfect is the enemy of the good.

hear hear
thanks for your indulgence
i think i won't stare a gift horse in the mouth
or rob defeat from the jaws of victory

ah, a Democrat

uh, no actually
tend towards the church of the libertarian reformed
but now i'm just avoiding work…so i must go. thanks for the help!

does mysql default install Mysqldb?

DARKGirl, not on ubuntu

some Python DBAPI module
this is Unix (OS/X)

DARKGirl, ubuntu pkg you want is python-mysqldb

i do not use Linux

oh, osx, nevermind
i think u have to install it separately regardless
try importing it from the commandline in python

blah

(sigh) yet another module that must be installed!
I have done so many installs, its making me dizzy

what osx version are u running? if it's 10.4 look at this: http://dunck.us/anabasis/archives/2006/02/23/mysqldb-on-mac-os-x-104/

aye, its 10.4.x
ok so where can I get this Mysqldb from?

http://sourceforge.net/projects/mysql-python

thankee much
I am assuming python is installed if I installed mysql/php

uh?
bring up a terminal

OR do I have to install python first

and type python

ok
Python 2.3.5 (#1, Jul 25 2006, 00:38:48)
[GCC 4.0.1 (Apple Computer, Inc. build 5363)] on darwin
Type "help", "copyright", "credits" or "license" for more information.

you're probably good to go

hmmmm

rgr

how can I tell where that is installed?

you might be at the command prompt right now

prompt

should I exit out?

sure

i wish whereis worked

you wanna know "where" as in the path?
path to python?

aye, I do

on unixy systems (ie my system) it's /usr/lib/python

the whereis db is updated (usually) once a day.

because it may need to be reinstalled if it is not in /usr/local/python
ah ha!
so I will have to reinstall python
grrrrrr

/usr/local/python seems logical too

aye, but its not there

/usr/lib/python ?

I want to use an area where all the OLD apache/mysql/php stuff is not located
because it causes problems
on OSX, it comes bundled with apache/python/mysql/php, etc
you cant get rid of it
you cant install over it

huh
maybe they put it in it's specific place for a reason?

so I am using /usr/local as the parent folder for apache2/php5, etc
I have all the updated stuff installed
i dont want to use apache 1.3.33

sorry, i have to repeat my question…
how do i list the table of table A that are not in table B?

a not in b

SELECT a.* FROM a LEFT JOIN b ON a.id = b.id WHERE b.id IS NULL;

ah,

ok, first, i need to find a python source and install it

DARKGirl, in case you're still wondering, you can tell where python is installed by going to the python prompt and then typing sys.path

if you write down the prompt (a not in b) the wench will tell you every time you forget.

lol
Why not just write down the query?

DARKGirl, you have to import sys first

sys is not defined

right, type "import sys" first

what? the_wench is wrong!

DARKGirl, have you programmed much in python?

I doubt that.

system/library/frameworks/python.framework/versions/2.3/lib/python2.3

ok, than I'm stupid

there it is

what is the problem?

no. viewvc uses python
ah well since its installed there, I wont touch it

ok, let me try it one more time first…

if it were installed anywhere in /usr/ it would be reinstalled

ah, i see

oki so i just configure/install mysqldb then

good luck to ya

much thanks

Is this channel specific to mysql install/troubleshooting, or are general SQL questions okay?

how do you insert a file into mysql table?

load data local infile
is that what you're asking?

snoop-: storing files in databases is generally a bad idea, if that is what you mean.
it could go either way. ;^)

lol

ask. if it's inappropriate, you'll be told.

I am making a basic search feature for a fulltext field in my DB. I want users to be able to enter "term" which is OR, "+term" which is AND, and "-term" which is NOT. How best would I build such a query? (For instance, if I had two terms that can be in the results, two fields that must be,
and two fields that must not be). I am banging my head against the wall trying to figure out how to place pareths, ANDs, ORs, and NOTs.

that's why google gets the big bucks. ;^)

Yeah, I googled several permuations of "sql search like and not or must 'must not'" but didn't hit anything that made any sense. :/

whats the size of a longblob?

I'm thinking that the majority of your coding is going to be in your application (php, perl), parsing out the terms with leading tokens, and then building a valid sql query.

/usr/bin/ld: can't locate file for: -lmysqlclient_r. is there a way i can specify where that file is located?

snoop-: http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html

ok, i'm going to try it a little later on…i'll be back if i have any questions, thank you very much for your help

divStatus has no properties
….includes/scripts/utils.js
Line 30

what's line 30?

http://paste.css-standards.org/18210

well then the element was not found

strange isn't it

Mace[away]: not really, it could be for any reason at all
maybe there is no element with that id
maybe it hasn't loaded yet

div id="plugins" style="color:#666;font-size:10px;margin-top:1px;line-height:13px;padding: 3px 0"/div

maybe sid is set to some weird name

Line 29 now var divStatus = frames[0].document.getElementById('plugins');

is this iframe a simple page?

the same page loads each time - the very same page!
relatively

Mace[away]: do a dump of getElementsByTagName("div"); and see if it shows up in the list

is there a way to retrieve all aggregated style attributes for a css class? e.g. all rules applying to "body", even though they may be in 3 different sheets?

yeah, firebug
you don't use firebug?

do a dump of getElementsByTagName("div")? you mean loop through it and output each id value?

Mace[away]: that or console.log
console.log is a lot easier though

console.log?

console.log, firebug's debugger output method

oh. console.log(..) ?

facebook uses it

console.log(formRef.getElementsByTagName("div")); you'll see a wonderfully formatted output of the whole array

ah

and you can click on each element, and it will highlight that part of the source
and you can easily see any mistakes you might have made

why formRef?

Mace[away]: because its easy to type

oh ok.
Apparently, frames[0].getElementsByTagName is not a function?

frames[0].documentElement.getElementsByTagName
FF2
IE6 does it differently
IE5.5 does it another way
and, ofcourse, IE5 doesn't even do it

i c

which means, if you're going for IE5+ support, you have 3 ways to support, and 1 failure / needs fallback to support

It's outputted; [div, div, div, div, div#plugin, div, div#plugins]

what does that mean? it means iframes suck!
Mace[away]: looks like you have two divs that id values
div id="plugin" and div id="plugins"

correct

now output the result of getElementById

how can i stop an ajax call
for the autocompleter
so if its searching and they keep typing it will kill it
and search for the new term

you can't .. just ignore the result

hmm…
..abort()

what object do i pass?

the XMLHTTPRequestObject is passed as 'this'

this.abort():
?
this.abort();

where are you calling this from?

im not really sure i'll have to look through the JS
they have it all prototyped out

you have to get the reference to the xmlhttprequest object
and call abort using that
blahRef.abort();

okay i'll look
thanks
is there a way to get all active calls?
so i can just abort all calls

keep a list of all the created references
and loop through calling abort

okay im off to look through it all
i'll be back with more questions

for(i in objects) objects[I].abort();

function autoComplete(..){ if(window.autoCompleteHandler) window.autoCompleteHandler.abort(); … window.autoCompleteHandler = new XHR; …. }

okay cool

he doesn't know where the xml handler reference is
its ripped javascript

found the problem, thank you! any other debugging tips while I have your attention?

hm

*shrug*

i found it ddustin

He'll still have to ignore the result

ive pretty much learned the whole script by now

Mace[away]: firebug can set breakpoitns
Mace[away]: you can also step through code, with watches
which I find extremely useful

7) (DDustin) Mace[away]: you can also step through code, with
Yeah?

yup, just hit the 'script' tab
pick the file you want to look at in the drop down
click the line number to set a breakpoint there

ahh
many thanks DDustin.

np, good luck
nice

are there any gotchas involved in putting models in places other than app/models.py?

o_O huh?

like if i have a bunch of large, special-purpose models i'd like to put into a different file
you know, to soothe my OCD
is it as simple as "from site.app.special_models import MyModel"

wrong channel, but afaik, putting models elsewhere can be tricky

oh hahahaha my bad

After writing this script; do you think I have a life? http://aza.pastey.net/70252

thanks insin

in this script they do a lot of this onClick = function(event) { doSomething }.bind(this0

you are offically crazy

does that just bind the function to the onclick event?

what's the exact syntax

I think it's as correct it can be

function(event){return this._onkeypress(event?event:window.event);}.bind(this);

what's the context
where is this done

sorry
this.obj.onkeypress=function() { function(event){return this._onkeypress(event?event:window.event);}.bind(this);

bind sets the object which the "this" variable points to when the function is executed

wtf

foo=[1,2,3,4,5]

Jan-: { 0: 1, 1: 2, 2: 3, 3: 4, 4: 5}

foo=[1,2,3,4,5,]

Jan-: { 0: 1, 1: 2, 2: 3, 3: 4, 4: 5}

Is that normal?

based it on the perl sprintf

Jan-: yes

main problem would be to handle signed and unsinged integers

it's standard not to care about trailing ,s?

http://insin.webfactional.com/tumblelog/item/10/

console.log(function(){}.bind.toString())

Jan-: some does, some doesn't

Jan-: IE cares a *great* deal, try not to do it

function (object) { var __method = this; return function () {__method.apply(object, arguments);}; }

The reason I ask is that I just realised I did it by mistake and yet everything seems OK.
It definitely cares when definint {objects} but seems OK with it on [arrays]

so bind does theFunctionRef.apply(this, [this]);

How often should data be fetched from the server when using AJAX? 250 miliseconds, 5 seconds, 15 seconds, 45 seconds?

Jan-: don't believe it for a minute - I once had a script where the size of an array randomly differed by one due to a trailing space
s/space/comma

so that is just to keep the scope within the function when its called on a different level?
or from a different scope?

precisely

more learning being done today

has anyone got a good sleep() function for js?

You can't really do that, dools.

Jan-: you mean i can't pause my script for x seconds?

You can, but you have to write it with that in mind.
there's no way of saying "stop here and wait"
What you can do is say "do this after a certain period of time"

ooh found a pretty good one using Date

so if you can split your code into two functions and call the second one from the end of the first one using setTimeout, that's the way to do it

Jan-: yeah i think i might have to do it like that

Number.prototype.bound = function(lo,hi){if(thislo){return lo};if(thishi){return hi};return this};

Jan-: { lo: undefined, hi: undefined}

Number.prototype.bound = function(lo,hi){if(thislo){return lo};if(thishi){return hi};return this}; 100.bound(0,40)

Jan-: Error: Error: missing ; before statement at line 0: Number.prototype.bound = function(lo,hi){if(thislo){return lo};if(thishi){return hi};return this}; 100.bound(0,40)

Um. What'm I doing wrong?

100.bound?
Javascript != Ruby

What d'you mean

what is bound()?
do you want splice()?

Something I'm prototyping onto number.

you can't call methods on literal numbers, can you?

I thought you could

not in Javascript
the only language I know like that is Ruby
which is why you have
Math.round(15.3)

15

Math.sqrt(15)

3.872983346207417

rather than
15.sqrt()

Error: Error: missing ; before statement at line 0: 15.sqrt()

15.sqrt();

foo = function(){return}; typeof(foo)

Jan-: function

Will IE do that too?

15.sqrt()

Jan-: do what?

return "function" for typeof on a function

sure

why do people love to use foo as an example?

because bar needs a rest now and then

I should call my pseudo-events with entirely lowercase names to match IE style coding, shouldn't I?

doing for (k in createMyHash()) will only call createMyHash() once right?

num=0; for(k in (function(){num++; return [0,1,2,3];})()); num;

1

yes sir
that is the coolest bot btw =D

Yes, he is, isn't he!
*buff* *gleam*
Sure but what does jsBot do?

var num = 5;

jsBot knows everything

speak to me of the world

How many beans make five?
Aww. It's OKies, don't be shy.

where did I leave my phone? I haven't seen it for months

=O he truly is divine, he speaks nothing and conveys what the world really is

about Jan-

Jan- is known for dangerous (1), rude (1)

HEY!

ha, he does know everything!

about Woosta

Woosta is known for brilliant (1), excellent (1), right (1), wrong (2)

:-D

about GarethAdams

GarethAdams is known for clever (1), right (1), sarcasm (1)

where'd jsBot get THAT idea?

about Jan-

Jan- is known for dangerous (1), rude (1), sarcasm (1)

~[ jsBot ]~ about wymetyme
damn this script

Hang on a minute!
I was only dangerous and rude a moment ago!

WTF is the flying nick about?

how dainty, wymetyme

Now I'm dangerous, rude AND sarcastic!

Jan-: GarethAdams karma'd you in the other chan

shhhhhh

how do you do that?!

karma GarethAdams sneaky

Unknown karma type 'sneaky'. Known types are: dcc, xpost, bad pun, brilliant, clever, dangerous, didn't google, excellent, funny, homework, informative, insightful, interesting, loud, offtopic, pastewarn, pasting, right, rude, sarcasm, troll, unfunny, wrong

about god

I don't know anything useful about god

ha

haha, beaten by your own programming

about Woosta

Woosta is known for brilliant (1), excellent (1), right (1), wrong (2)

i forgot I removed the ability to add new ones

karma Woosta rude

about Woosta

Woosta is known for brilliant (1), excellent (1), right (1), wrong (2)

You don't have enough fu

karma Jan- dangerous

OK

Ahhh
it's your silly nick changing habits

Hey I have a question. So I've got a button that says onclick=myFunction(), and it's hitting myFunction. Inside myFunction, I've got this variable that contains text, and I am able to alert() the text and it pops up. Here's the question. I've got some p tags on the page with id=textOut. How
can I get my text into those p tags?

about Jan

I don't know anything useful about Jan

about Jan-

Jan- is known for dangerous (2), rude (1), sarcasm (1)

Jan-: for a moment I thought "Woosta voodoo" was a euphemism

*sigh*

sorry

It'd be easy to get the impression you are all total nerds who think about sex all the time because you never get any.
Except you, obviously, Woosta. And you, jsBot.

karma Jan- sarcasm

OK

OK, Jan- you should be OK now

about Jan-

Jan- is known for dangerous (2), rude (1), sarcasm (2)

Erm.. Did anyone read my question and know how to do what I'm trying to do?

Bwha mngh!?
Quitit, we're too busy being offtopic

what do you think this is? A help channel?
Hehehe
2 secs and I'll read it

Yes.

document.getElementById('textOut').firstChild.data = text;

depends on what you mean by text ..

Thanks, one sec trying that

empty(yourPTag).appendChild(document.createTextNode( yourText ));
`js empty // you'll need this

A function to empty a node of content: function empty(node){ while(node.firstChild) node.removeChild( node.firstChild ); return node }

I assume that by some you mean one, because you can't have more than one element with the same id

That worked, deltab. Thanks!
Well two p flags
Er tags
One with the id

elements, actually

Uh huh

tags are what mark the start and end of an element

is firstNode.data cross browser?

is a start tag, and is an end tag

Yeah, so they're tags
p tags

but it's the tags and everything between them that make up the element

Ah ok.

firstChild is
Jan-: heh

karma deltab insightful

OK

karma Woosta overweight?

Unknown karma type 'overweight?'. Known types are: dcc, xpost, bad pun, brilliant, clever, dangerous, didn't google, excellent, funny, homework, informative, insightful, interesting, loud, offtopic, pastewarn, pasting, right, rude, sarcasm, troll, unfunny, wrong

Bah.

Yes, firstChild is cross browser, but is setting .data cross-browser?

Jan-: maybe you shouldn't've started with a chess playing program

In my garage?

hi. any idea how to get a location.href='URL' to work with IE?

I don't remember that bit

Gr!

how doesn't it work?

how can i get my page to scroll when there are layers outside the visible area?
document.body.style.overflow = auto?

you need quotes around the value

right
but will that scroll my page when the layers are outside the visible area?

not by itself

right
but right now there are no scroll bars
and i cant see the bottom of my page

you can use the element's scrollIntoView method for that

i just want scroll bars

why is the new iphone so clever?

How did you get rid of them?

you get one?
they are just not active
they are disable because it doesnt read any content to scroll to i guess
in FF

Jan-: Security: it means blind people can't steal your phone coz they can't find the damned buttons.

nevermind
i got it

I understand.
….it doesn't have buttons?

Jan-: most of one side is a multitouch screen

Oooo.

(Everyone knows blind people are responsible for 84% of crime)

Yeah.

deltab, http://majorstuenzoo.no - the top navigation don't work in IE 6

It's to support our extravagant lifestyles.
Someone has to keep me in white sticks.

Yup
Those damned sticks must cost a lot
Bah

Actually they f'in do, as well.
If you want a nonsucky one.

I bet they're made of ivory from third-world-children's teeth

Ooh, a new type I haven't heard of!
I prefer the rhino horn myself.
It's an aphrodisiac.
As if I need one

Jokes aside, I'm not surprised they cost a lot .. I've noticed that anything that's truly needed costs a hell of a lot

Small market.
Also they're made of carbon fibre.

Licking you cane at the bus-stop must get you a LOT of weird looks ..

Yeah, I only do that when nobody's looking.
It's my best friend, you know.
Just like everyone's guide dog is their best hosting friend.

"Yeah, officer, she was sitting there, licking her cane .. yeah, that's what I said, licking her cane .. then suddenly she's like .. all over me!"

verkakte guide dog users
*spit*

Hahahah

"Oooh, Timmy is my Best Friend in the Whole World!"
"I don't know How I'd Get On Without Him"

so there's this huge rift in the blind community? the doggy people, and the non-doggy people ..

*mutter*
And the braille people and the non braille people
and the born blind people and the not born blind people
The one thing nobody ever thinks about with disability is how fricken POLITICAL it is.

wow
I know in the paralympics there's all this fuss over who is disabled enough and who isn't ..

I have heard of people who didn't want to get their sight fixed beyond a certain point or they'd be ineligible to play goalball.
Disabled sports, though.
What a fucking gip.
I mean this one guy has been the world champion blind runner in about twelve categories for the last ten years.
To me this implies that the field is not bursting with talent.

to input elements automatically return a new line if you put them next to something?
*do

no. But that's not a JS question

work with IE?

There are inline elements (input) and there are block elements (div)

sorry let me go to the HTML channel to make sure my question is related
usorry let me go to the html channel to make sure my question is related/u

Oh, on a similar note, Woosta, you wannaknow what someone said to me over coffee after a meeting today?

(1) it's not in the spec IIRC (2) It's IE, Duh!

#web

"Wow, I'm so impressed you're able to hold down a job."

hahahaha

Woosta, any idea how to fix this with IIRC?

Oh I love unintentional condescention

Just as I love beating people about the head with an aluminum baseballbat.

you have to put an onchange on the select itself

Woosta, how?

afk

hey everyone

how was dindins, MONO`?

Jan-: pretty good, i had spaghetti (not srue if that's spelled right though haha)
*sure
anyoen doin anything exciting?
*anyone

yeah, definitely
I'm creating a html dom class

hm?

it's for a bit of code, html untidy, that takes a perfectly validating document and breaks it

lol nice

*twang*
*dischord*

…and that playlist has been playing for almost 2 days

How many times has "Like a Virgin" played?

huh?
none

oh okay
nevermind

lol

It was a famous madonna song

ok, so explain why i'd listen to that? lol

I thought it highly unlikely that you would, so I thought it would be funny
But I guess that failed

good idea lol

is there a help chan for yahoo UI toolset?
oh no, no more turbo

how come I can't function myobj(){var Name="bob";} dude=new myobj(); document.write(dude.Name); ?

Ohhh man I wish WSH stdinput was nonblocking

i know i can in myobj have like this.getname = getname ; function getname(){return Name;}

_koft, when you declare a var like that, it's like making a private variable

i wish i had something that could play .mov files .

isn't there a way to get to member vars in an object withouth having to go through a helper function?
ya, it's sayin undefined

that's the thing - it's not a member var - it ceases to exist when the function execution terminates

function myobj(){ this.Name = "bob";}

ORRrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrr

ok, so it's making room for that stuff on a stack and exiting scope

function blah(){} blah.name='blahblah';

so i have to make the var in global space and tack it into the object?

ding ding ding we have a winner
(that's a yes)

i'm learning
it's conceptually a little weird for me

if you find js weird you probably dont wanna get to know me too well =p

i had this idea that java script worked in ways which it apparantly doesnt

although that will give you the same property for every object…

javascript = 1 word [just for future reference]

well, thanks for the tip

=]

http://developer.mozilla.org/en/docs/A_re-introduction_to_JavaScript

i havn't touched javascript in like 5 years, i don't remember all this DOM stuff from before

it wasn't there before i dont think

or JSON for that matter
but in the past week i've been doing the ajax thing, i never realised how much this stuff has grown up

Anyone know how to pick up a shift-click in JS? I've tried listening for the keyCode but it doesn't seem to come through. I'm using Safari, but it would need to be a cross-platform solution.

you have to say: "Hi, everybody!"
Have you tried looking at window.event.shiftKey?

I hadn't no. I did some googling but kept finding what looked like really old code so I was dubious about using that in case it was IE proprietary or something.

Well, window.event.shiftKey certainly is
but I believe there's a Firefox equivalent

Ah. Right.
It had that look about it.
Hmm. I might just use drag selection anyway.

hey everyone

Oh good.
Well. At least it's not my code.

:p

anti-safari4win http://tinyurl.com/yukj22
anti-safari4win a href="http://tinyurl.com/yukj22"http://tinyurl.com/yukj22/a

that's at least 300% gay

Yeah. And I'm not even homophobic.

exactly

My friend Major Eisborg is… well, she's into girls.
Her name isn't actually Major Eisborg.

Yeesh. I wish I'd just dugg it.

She's just about the cutest, sweetes, least obvious lesbian I've ever come across.
So obviously she's now named after the gigantic scary military character in "The Fifth Element"
bSo obviously she's now named after the gigantic scary military character in "The Fifth Element"/b

heh

Oh, you know who I mean?
Nobody does usually.
OK, it's official.
Mplayer is incapable of playing several files in a row.
It crashes, regardless of whatever way you do it.
All I can do is literally to launch a separate instance for every file.
This sucks like an open airlock./

well, I've seen the movie :p

how can i pass my object from one function prototype to another?
as an argument that is

"Major Eisborg will accompany you - as your wife."

Jan-: WORKS FOR ME.

huh?

Mplayer =]

Oh sure it works in an "open a file, play it back" kinda way.
But "open a file, play it a bit, then open another, play that a bit, go back to the first one, sit paused for a while, go onto another file and play to the end"
…..no way.

Weirdo.

who?

ey i'm outta here
peace

I just did a document.getElementsByClassName(some_class), but when I try to operate on the id (of some, I'm guessing it's not all) in an iteration (for element in elements), I get that element.id has no properties
(using Firefox 2.0.0.4)

are you sure that element.id is defined before you start calling methods on it?

oh, yeah they all have ids
I did a little prompt test with… one sec lemme pastie

(using Firefox 2.0.0.4) - update :p

http://pastie.caboo.se/75576
…. that's new enough
unless there's some specific error in that version

oh, nvm.. thought we were at 2.1 branch allready

the length was 16, exactly the same as my own count of the correct element, but there were a ton of undefined#undefined
I guess those are what I'm asking about

you don't give me what elements is

elements = document.getElementsByClassName (as above)

and that method is prototype specific?

I just pasted the test code… I can paste the whole function… one sec
I believe so

have you checked how it's done , and how it's surposed to return data?

http://pastie.caboo.se/75576 (i.e. refresh the page )

(and are you sure the elements returned got a ID at all ?)

http://wiki.script.aculo.us/scriptaculous/show/Document.getElementsByClassName

uhm… that's a world of difference from what you first pasted…

waaait.. http://pastie.caboo.se/75576
that wouldn't return the key, would it
shouldn't it be prompt_string += element.className + "#" + element.id + "\n"; ?

all of the elements selected with the className I specified do have ids. The undefined elements in my prompt were in addition to the sixteen supposedly specified by the the elements.length
nope… for requires elements[element]; the error is NOT in the debug stuff (prompt, etc.) the problem is when I do elements[element].id.someStringMethod
erm…. StringFunction
the first paste was just a demonstration of my debug code
that's the whole function

http://jquery.com/blog/2007/07/01/jquery-113-800-faster-still-20kb/ new jquery… allegedly faster… everything else raped it for me

the problem is in line 12 of the paste

why are you trying to stringify an element?

(I replaced the little id method for debugging after it didn't work the first time), I'll fix that (change it back to the original code) and see if anything changes
updated the paste, the problem is now in line 11 (deleted that bad line and switched the code back)

make sure that the id exists before calling a method on it. it's that simple
you're getting a bunch of elements by their class name, they might not have id
you may also want to try DOM getAttribute("class"); maybe?

is DOM another library? or is it in the javascript host base?

DOM is Document Object Model. that method is DOM level 1. you should really google DOM because working with javascript in browsers it's kinda useful…

(I know what DOM stands for)
sorry, that was supposed to go before you replied

DOM is a bunch of object prototypes implemented in the JavaScript of the browser

what do I call getAttribute on? I'm assuming document? I don't see it documented in the tutorial site I have been using as a reference

you call it on the element object

alright, but if I'm calling it on the element object, how will that help me if I can't verify that this function (getElementsByClassName) is passing me correct elements?

uhm… could you not use getAttribute to verify that the function is giving you correct elements?

okay, I got an error: elements[element].getAttribute is not a function, so I guess it's not passing me correct elements… O_o

it's not passing you Elements period

possibly it's passing me an object similar to an element, but not an actual element object
because I'm definitely getting ids on the sixteen elements that are supposedly in the array
but then, going on to the methods below, it gives me the .id has no properties…
my point is that line 5 and line 12 have the exact same code, (speaking of the elements[element].id), but I get output on the first, but not the second, whether I call the first or not
is there some kind of freezing that can occur on objects in javascript that I don't know about? We have them in ruby, but I haven't heard about them here

Was someone looking for me in here earlier?

AJAX/JSON question. Storing a JSON string in a database. should this string start with "var myvar= { prop: … " Or just with the object literals (i.e. "{ prop: …" )

I don't know if you're still here, but I want you to look at this: http://pastie.caboo.se/75581, the iteration stops when it gets to a psuedo-element with undefined id AND class
also, when I make the elements = elements.slice(0,15) (0,14)… my last element is deleted and an undefined element is appended to the end of the array
apparently, I only have 15 elements, but the #findByClassName appends that last element to make the length equal to 16

yay, that's great. that's also really fucking annoying to have to walk all the way to my computer because you keep saying my damned nickname

well then you should have said that you wanted me to stop. If you don't want to help me, that's perfectly fine. I'm trying to spot an error in the prototype code

hi, i have a quick question. With javascript you can't save the contents of a form in the clients pc?… i think the answer is no… i think it can be a security breach if was posible…

you can, in a cookie

depends on how much content, doable using a cookie as preaction said but there's no access to the filesystem, with the exception of …. IE ^_^

cookies, Flash local storage, Google Gears

thanks both of you… now three… yes, i know… in the cookies yes… but no filesystem acces
a economist friend want's to do some like that… i tell you i think wasn't possible… there are a lot of other methods to do what he wants… but… economist… you know…

if this is for his own Form material being saved for later retrieval on his browser, is doable with many extensions or plugins
in Opera you can use the 'Wand' for repetitive personal info, to have it autofill form fields

deltab… i don't know what google gears are… now a google and i know… google continues growing… they are inteligent…

http://gears.google.com/

CommandPrompt, they want's to send a form to other people, then the people complete it and send you the result

also HTML5 local storage

CommandPrompt, yes, i'm looking that url

….

sorry for my english… not "send you the result", "send him the result"

they want to send an auto-filled Form to people?

CommandPrompt, no, they want to send a normal form to people with questions, checkboxes, etc, , then the people complete the form and send him the results

…
and what part of the form requires filesystem access on the client?
sounds like a simple form submission

yes… i tell him that… put the form in a free web server host and get the results here… but he wants the forms saves the results in a file, then the people send this file to him

the server can do that

in the server you can use anithing else… php… perl… databases… save files… with this you do a very simple form who writes the results to a db or a file
the problem is he doesn't want to use a server
he want's to send the form in a mail to the people… not put it in a server… but i'm gonna tell him… put the form in a server and don't bother

tell him to suck it up and get some free hosting. www.ej.am has free hosting with perl and php as long as you post 10 posts. (and never have to again, just visit the forum every so ofter)

I have pound '£' character stored in the database and when I use xmlhttprequest GET object to retrieve this character, the respond is '?' character. Help.

and ruby on rails hosting is now as low as $4 bucks a month server side is the best way to go

the web server is sending back a bad content-type header

preaction, urmm.. what content type should I put?

well, what encoding are you using?

thanks jontec, www.ej.am looks very good… perl and php… there aren't many free servers with perl

I'm a RoR guy, but I used to have them host a forum. It's pretty stable.

preaction, to be honest, I don have any idea. none I guess or the default which I do not know..

the default encoding the browser uses is probably the same encoding the system uses.
i imagine the page's encoding and the encoding your ajax application is sending back are two different values
so i imagine you might want to use firefox's page info to figure out what encoding the page is in

why not just the £*, I know it'll store with the db, whether or not you can pass a * will be dependant on whatever's stepping down the data into the db

..
in Firebug, you can check the XMLHttpRequest() object and the http headers sent along with it, it'll show you what encoding type the webserver is sending as well as the content-type

erm.. nope, not £*, £

jontec, you're right. storing £ instead of '£'. urm.. how do I get other currency character like yen, dollar etc?

I was using a book, I can give you the values, but they're probably on the net someplace

what is this called? encoding ?

I have no idea, I just use it… this is a dated book, but I use it as a reference for colors and things like that… "The following table lists the extended charater set for HTML, also known as the ISO Latin-1 Character Set."
the Latin-1 would be the encoding, I believe
the yen symbol is ¥ and dollar sign is $
what CommandPrompt was referring to (Firebug) is a developer extension for Firefox. I have no idea where you find that information in Firebug, however, this is only my second day using it.

hehe

care to tell us, I'd like to know
oh, wait is it the Net tab?

[net] tab [xhr] section, clicck on the [+] to expand the request entry, it has 2 tabs, one is the .responseText and the other is the HTTP headers sent
or if it shows, in the [console] tab, just expand the [+] it'll show the 2 tabs too

that's pretty nifty

anyone here use jquery?

(I don't have any idea what that is)

pretty helpful stuff
www.jquery.com
but i have a question so if anyone uses it lmk

owkey, got it. thanks guys

Hey, I have a ajax call and I assign a function to my ajax object's onreadystatechange like so http://pastebin.ca/600931 how can I make it so I can assign the result to a variable outside of the function I assign onreadystatechange?

The paste 600931 has been copied to http://erxz.com/pb/3394

only way I presume is to store in a global document variable

what package do i need for wmv codecs? is this in the repos?
oops wrong channel

hehe

hmm

row: http://www.free-codecs.com/download/K_Lite_Codec_Pack.htm
….
peepsalot rather

ah sweet it is working now

anyone can point me in good direction for learning how to remove elements withing page using xpath ? ;$

ah I think I know what my problem is.
I submit a form catch form submission, do call in ajax it works so I remove the form via removeChild from parent node and then I can't stop the form submission
so just ends up submiting page anyway
bah
silly me

CommandPrompt, i'm on linux

on wmv?

mplayer, vlc?

… get vlc or mplayer
mplayer has wmv codecs

mplayer has just about every codec

it has a bundle called the 'Essentials'
wmv is in it

Hey how does one cancel a form submission from with in javascript? not via returning false to onsubmit or whatever that is?
ie form.submit(false); or something?

…
why?
just don't submit it

er, I call something via onsubmit="return !somefunc();" if func returns true it kills submission, but I remove the form itself in that function in one case and then it submits anyway.
in fact its submitting every time

.
onsubmit="return false"
no?

yeah but if can't make ajax call for whatever reason want form to submit
as all ajax related features fall back to normal form usage
so I return true if I can call ajax, false if I can't
so flip that true to false and it should stop submit
must be a error in my javascript stopping function returning
looking into it now
as I swear this always worked before

dude..

dude! you've got a Dell!

onsubmit="somefunc(); return false;"
:p
and "ajax" is such a annoying term
because.. it's a buzzword! not a technical term
lol !
more buzzwords!

fine, I make a http request via javascript to submit form in background.

why would you ever do that ? -.-

because it floats my boat?
I tried return false after function as one of first things.

while (sleep(TheDeathArt)) { touch; }

something in my javascript is killing it I think

if any error is thrown futher execution is stopped
and your form would submit
but any debugger should reveal so

apparently not, no idea why its doing this.
screw it
fix it later, its not important

jesus…. with IE sometimes the page works, sometimes I get an "object doesn't support this property or method" error and SOMETIMES i get "operation aborted"
wtf
worked 6 times in a row
this time "operation aborted"

well… IE :p

its pisses me off so bad that such a shitty browser has such a big market share

in time people will learn

hmm lets see if using output buffers fixes it

"you can buy a Ford in all colours you want, as long as you choose black"

will that load all of the nodes before it starts executing javascript?
*elements
oops sorry let me go to the PHP channel

there is some javascript hosting methods to check for the document is fully loaded

i know the problem is i cant find the code thats trying to access the DOM

just scope all the code
and put it inside a tigger

i tried wrappign the whole script in (jquery) if $(document).ready) {

should work then ?

didnt…
haha
maybe i didnt wrap the right script…
hmmm
dammit…. everything in all of the scripts are functions… nothing is being executed on page load
what is something that would cause "object doesn't support this property or method" AND "operation aborted"
but one or the other randomly… and other times the page loads fine

timing on certain created objects most likely
try delay it with 400ms or something
what do your script DO ?

the one that has a problem is my autocompleter that i stole from facebook

:p

fastest one ive ever seen

hey all
i ant to assign 'var abc = x.innerHtML()'
but i want a string to be assigned not the reference
that is i dont want the contents f abc changing when x.innerHTML is changed

w0t?
you do know there's no innerHTML() method?

innerHTMl , sorry about the () .

so
what's the Q?
innerHTML is just a string, not an object and thus it doesn't return a reference, just a string

when i assign a variable to innerHTML , and i chaange the contents of innerHTML
the contents of teh variable also change
i just want to store the contents of the innerHTML in a variable in such a way that it wontt change
i mena i want a string value assigned to the variable and not the reference

…
I don't think that is the case

yeah if you assign a var to innerHTML it wont change when you change innerHTML

it is changing .

then you're changing it
er… solar_ant

i will check the code
thanks

http://www.ameritime1.com/newsite/template.php
a href="http://www.ameritime1.com/newsite/template.php"http://www.ameritime1.com/newsite/template.php/a

hi, i want to define an object HEADER within another object INTERFACE (something like inner classes). How can do so?

how to insert an element for example a div or whatever by js?

DOM

`html2dom @ crolle17

html2dom: Woosta's (x)html to DOM convertor .. convert xhtml chunks to DOM scripting: http://rick.measham.id.au/paste/html2dom.htm

Woosta, ah. appendChild

Amongst other methods, yes

Woosta, or createElement …

you need both
you need the code produced by that page

nice page.

Put the HTML you wish to create in the top left, click convert, the code produced is the code you need

Woosta, yes. very nice and comfortably

how to get the final width and height properties of "div" and "a href" elements ?? i tried http://paste.css-standards.org/18227 but in IE i always get it as "auto" instead of the final px values. any ideas ??

offsetWidth

hi. if i want to create something using DOM in a script tag in the body to be rendered in-place in the body, how do i do that?
use document.write(new_element.outerHTML) or something?

hi
with http://www.ajaxtutorial.net/index.php/2006/07/07/ajax-generic-form-parser/
sendRequest is not defined
but is is defined

Is there no way with html DOM to assign a data stream to an image, in order to change its content… rather than changing the img.src property?
Something like img.data = datastream_respresenting_image

hiya
does anyone know why document.write("text"); puts the text in a new window when you have it inside a function?
noone?

i believe using base64 encoding that is possible.
http://www.google.com/search?q=img+src+data+base64

Interesting, thanks. Should that work also for medium-size images, you think?

var = new Array( 'key'='val' ) and var={key:'val'} ?
and var = new Array(1, 2, 3); and var = [1, 2, 3]; ?

well i don't think new Array( 'key'='val' ) is really valid, is it?

The first appears to be some crazy mix of JavaScript Array creation and perl hash creation and is garbage.
The second is almost an example of assinging an object to an array in JavaScript, but fails since var is a reserved keyword.
The third and final examples are, IIRC, just different syntaxes for the same thing (except they also suffer the issue that var is a reserved keyword)

and key=array is impossible?

In JavaScript, yes.

What can i use insted of it? Object?
And are objects slower?

var myObject = { key:'value' }
Slower then what? In JS everything is an object.
An array is just an object with a few extra properties.

but i can also iterate through these objects - {key:'val'}

for foo in bar
umm for (foo in bar) { } even.

but I don't know if it works in older UAs
And how to get user's language in JS file?

wtttttttttttttttf
how does this NOT work
function makeEl() {
var newEl = document.createElement('div');
newEl.style.width = '300px';
newEl.style.height= '300px';
newEl.className = 'contentElement';
newEl.innerHTML = "test test test test test";
document.getElementById('content').appendChild(newEl);
}
you would.

I've warned you about pasting before. Don't do it again. Next time it will be a ban.

uh huh

http://phpfi.com

what's the easiest safe way of getting the text within an element?

.innerHTML or .data i'd say. but check out http://www.quirksmode.org/dom/w3c_core.html and http://www.quirksmode.org/dom/w3c_html.html

where the mouse is with an onclick?

i'd say use prototype's Event.observe, Event.pointerX/Y, and position: absolute the div at top,left px

do you have an example of this please?

any good tutorial on how to make a webring?

Don't multipost

ok sorry

:/

Hi. I'm using JSON as data format when requesting data via an XHR. I want to traverse this data in javascript, but i can't use the .length method (as in for(i=0; data.lengthi; i++) ) on json objects… have do you iterate a json object?

for (foo in bar)

thanks.. how's is that performance-wise compared to the "old" way of traversing an array…

who here has used overlib before?

I've never compared them. And the 'old' way is the right way of traversing an array. Looping over an object's properties and looping over an array are different jobs.

Okay, I'm considering combining json and "native javascript array" in my "data stream" to be able to traverse properly

hi all

quit

can somehow view all of the properties of an object?

for (foo in bar)

hey.. anyone knows whats worng here? http://rafb.net/p/FIR30867.html

krupa^: You are trying to use JS when a regular submit button will do fine.

kritical, as dorward said, you can use a form submit
but IF you want to use javascript
your problem is that you're getting the form element, then trying to use document.x.
you can simply do x.submit();

this is the whole script
http://rafb.net/p/7j1DZw66.html

once you have retrieved the form. You can even do document.getElementById('formid').submit();

check it out

your script login is just wrong

Thank you all !!

krupa^, you need document.getElementById

to pass arguments to function that I prototyped within an object, do I have to pass it through the parent object first?
guess not it sseems to work

Anyonw here has a clue on how can I send to a web server my DOM generated code using JS?

hi, I've made a php hosting script that parses alot of information from some xml files, and i'm looking for a javascript/function that could display a 'progress bar' while it's running.. any hints on that?

so is firefox

but firefox is a memory hog even now

i got enough RAM, plus no other browser has the extensions i need

morning

Hey jottinger. :3

Hi everyone!
How would I get localized month names in Java?
No calendar, no DateFormat, just month names?
DateFormatSymbols, that is it.

how can i get all flesfrom a date which where modified befo today
all files from adirectory

~javadoc io

I don't know of any documentation for io

http://java.sun.com/j2se/1.5.0/docs/api/
check the docs
ok its too early, im not helping anyone till i wake up
Straight from Jonathan Schwartz's weblog, Sun is changing their ticker symbol from SUNW to JAVA:
thats awsome

Is there a way to recursively add jar files to with java -classpath?
s/to//
(except via using ant..)

hi all. http://science.nasa.gov/realtime/jtrack/Spacecraft.html - here we have applete. can i run it out of browser ?

~tell raxelo about applets

raxelo, Check the topic, read the Wiki… Essentially we try to avoid them

oh

raxelo, you can try appletviewer.exe

i don't know how to pass parameters ..
and i got IO exception (with no message). maybe somebody can help me with sport-interest )

where's the problem? opening/clicking works fine so far

i mean. can i run it without browser
using appletviewer
but it needs parameters
…. that is a problem .

where does it need parameters?

you can see the src of html page (where it's placed) and see parameters passed in that applet (it shows satellite position)
http://pastebin.com/dd5521ca - how can i pass them to appletviewer ?

pass the html page to appletviewer?

thanks a lot !!! i forgot how to use it . thanks

how to use recordset object in java?

i built a JMenu were i added a JSlider using JMenu.add(JComponent)
the JSlider is displayed so far but it's not working as expected
nothing happens when i actually click on the thumb
but when i click somewhere else the slider moves until it's min/max is reached
the jmenu is not toplevel btw.
http://rafb.net/p/okkWWa42.html
anyone an idea what i could do wrong or how one should add components to jmenus (if that's possible at all)
aha! that thing only fails with sun java servlet hosting 1.5.0.11 (maybe others too) but works with java6

hmm interesting

} … if it doesnt exist, inside the if block i generate a file with the same name unzipping from a 2nd file. after the if block, the the handler f will already contain the newly unzipped file, or do i have to run some command to update it?

it shud contain the newly zipped file .. File is just an abstraction afaik

thanks

I don't know if anybody has used ActiveRecord (ruby world), but is there an equivilant framework in java? I need to do ORM/Database work, but don't want all the overhead of hibernate. Is there a good lightweight library to do that?

jdbc is lightweight (compared to hibernate), if that does what you want

I'm trying to extend the File. What are the requirements for this? javac says it needs a contructor File() but I can't see how this works

•ijk• extend File?

I'm doing class myfile extends File {} with the neccesary import statement

hmm dunno why wud u do that . but whats the problem
if u specify the constructor its fine i guess

constructor for myfile or File?

public myfile() {super()}

myFile
but i think it shud be call the super(string) constructor or something
File does not have a default constructor
hence the problem

okay. I did this: http://pastebin.ca/669193
6 lines
ah got it. called super with pathname. thanks
just couldn't understand the compiler's message

use an ide mate

i have a castor question…
when it marshals my stuff, it puts a type for all children of my object, but not for the root (owner) object
so when i unmarshall it elsewhere, it says it can't instantiate the main object

nmx, I'd prefer not to write the sql host myself, I just want more 'magic' to happen behind the scenes rather than me having to fill out XML and create beans and such.

~tell cschneid about jpa

cschneid, jpa is Java Persistence API

with 1.5, no XML hosting configuration. just injection.

hey there delilah!

good tune

if I do timerTask.cancel() can I later call timerTask.run() to resume it? or need I instantiate it and call scheduleAtFixedRate again?

Can anyone point me towards the documentation on UrlPattersns for Filters?

filters of?

i dont understand…

you mean url-patterns for filters in servlets?

anyone else notice that Sun's changing their stock symbol?

ianp, yes

welcome to yesterday
=p

http://java.sun.com/j2ee/1.4/docs/tutorial/doc/

This whole week I've been running hours to days behind. Nothing new

chapter 11

hehe
well if it helps you any i thought yesterday was friday

i thought today was thursday.
boy would I have been pissed tomorrow…

haha
ok stupid question, but would i be better off learning jpa or learning hibernate and then doing jpa?

JPA

yeah i think so too

re
yo!

YO YO YO. WAZZAPP??!?! (funky motions)

lol
you guys remember the wazzup commericials?

if you use hibernate annotations, you'd be learning 90% of JPA anyway.

get my email?
WAAASAAAABI!

hmmm ok

duh.
i did get your mail.

I was on the right track. That's stupid, but looks right.

s/Session/EntityManager/ and you're about 90% there

basically "it's up to the appserver to map resource references to jndi names properly, that's not a specism"

k

although it should be - but define a "say hello" bean and map it for that

hi guys

i've actually reverted the stuff we were fiddling with in my local project, because we're having db40 performance problems - for a 1.4second total call, over a second is spent getting / retrieving a db4o object from the store.

which is dumb but simple and should work

which is just plain wrong.

bleargl

im working on building a backend cms system for a client and im using hibernate at the moment, so i guess ill finish it out with hibernate and then pick up learning JPA.

JCR!!!!
why the bloody hell do people do that!

anyone here from england?

Geeez

as cheeser said, you're sort of going the bass-end way around

jottinger?

if you're using annotaitons, you're most of hte way there. if you're not, when you start doing JPA you're gonna do the "HOLY CRAP this is a lot easier to deal with than hibernate xml files."

heh ok

that's why i use jboss. because i have much self-hate.

i was about to say

JEE 5

why using xml files in hibernate
you can do it with annotations on pojos
but the new jpa rocks

there's no reason to use the hibernate xml files.
there's no reason to use the hibernate XML host files.

The power of Sun compels you! The power of Sun compels you!

[TechGuy] fails! for i am LAWFUL GOOD!

su-hoens`rZ: sorry, I use a lot of anglicism without being anglican

jottinger oh well

lol

Failed to parse source: null
how handy. :-/

episcopal then?

jew!

watch your mouth you dirty… oh. i get it.

hahaha
hey, I
I'll have a test case soon, since I need something like this too

ok

besides, I need a few more hours

Good morning, dude.

yo

Check out the front page. I just posted Bobby's article.

I'm trying to install the Java 1.5 SDK on my Ubuntu linux machine. I have downloaded the self-installing binary file java_ee_sdk-5_01-linux.bin from the Sun web site. When I run the file, though, it unpacks some data and then hangs indefinitely.

that's not the JDK
that's the JEE libraries.

Okay.
Thanks.\

~tell yrlnry about ubuntu

yrlnry, ubuntu is https://help.ubuntu.com/community/Java . If more help is needed installing/using Java in Ubuntu, try #ubuntu-java

You need to download something that says "java_se" or some such.

Is java_ee_sdk-5_01-linux.bin what I want here?
Wait, no.\
Geez, I ended up at the same place.

what was that php thing again
~php

vlii, php webhosting is the solution of choice for relaying mysql errors to web users

No.

Sorry, that was a mispaste.
I'm going to follow the directions at https://help.ubuntu.com/community/Java now.

PHP is a mechanism for dynamically generated web pages that's better than ColdFusion but worse than Wicket/JSF/JSP.

~predator

jottinger, I have no idea what predator is.

~php

jottinger, php is the solution of choice for relaying mysql errors to web users

I like javabot's explanation better

Either that, or go find the right download from Sun. I never use the Ubuntu installers for Java. Distribution-specific installation creates other problems later.
Heh.

just heard a complaint of php mysql errors on some other channel, I remembered javabot had something to say on the topic

if I made a class extending File, should it not be possible to cast a file to MyFile with (MyFile) f?
it compiles alright, but fails at runtime

are you sure the object is really a MyFile?

so .addAll() actually creates copies of the objects in the List huh?

no it's a File, but essentially it's the "same" class since I just extended it

you can't convert objects between types
it is what it is.

okay. it's that I need File's listFiles() method and that this does not work on my extended class
but thanks

if you have broken a superclass method, you're doing it wrong.

does anyone know whether .addAll(..) defined in List actually creates new objects when it inserts them?

it does not

hmm so it just copies the reference?

yes

same as add huh nmx?
thanks

any idea what I'm doing wrong? I just did a dummy implementation of the class extending File, but as said the method indeed is broken.

post a test case and explain what you mean by 'broken'

okay

how to return the second entry in the map?
iterate over it?

you can iterate over its entry set, though if you are not using a sorted map you may not get what you expect

A hashmap is basically a collection of key value pairs. Even if you use SortedMap you would get elements in ordered sequence based on natural ordering, which is not necessarily the order in which you inserted the elements. Try using LinkedHashMap for predictable iteration order.

I did this, includes compiler messages: http://pastebin.ca/669257
I recognize that the problem is listFiles always returns a File object even when it should be "JpegFile"

myFile.listFiles() function returns an array of File objects and you are trying to iterate over it using a Jpeg object.

I thought it would return JpegFile objects when I extended File. is this do-able?

I don't know what kind of class is JpegFile so I can't comment on that. Are you using some third party libraries?

I mean, it's essentially the same class. I just need to add a few extra variables later on.
no third parties
perhaps I have misunderstood the "extends" keyword to some extent

I am quite beginner with mock objects. Can you give me an advice on how to test this method (there is a static method call that is a problem for me)

ijk, extends states that you are subclassing a class

Does JpegFile extend File?

http://pastebin.com/ma39070f

yes JpegFile extends File

Then try something like : for (File j : myFile.listFiles())
And inside the function, cast 'j' to JpegFile.

I tried that earlier. nmx said it's not possible to cast it - compiler says "java.io.File cannot be cast to JpegFile"

Then you need a constructor for JpegFile which will take File object as a argument and return a proper JpegFile object…
I thought the listFiles contained JpegFile and hence I asked you to try that method.
If the listFiles returns a normal array, you need to write some logic to get JpegFile out of File.

i've been wanting to get started doing some web development with jsp… should i be using struts, server faces or whatever, or something else?

mmm cape cod bbq chips
wicket

well, I'm not really sure how I'd get around doing that! the current constructor I have for JpegFile just calls super()

Then make a new constructor which does something like "super(passedFileObject);"

•ijk• the listFiles() will return File objects only
u cannot cast them to JpegFile

~javadoc JpegFile

I don't know of any documentation for JpegFile

you're in luck! there's no such thing!

hehe
presumably u want a list of all files in a directory that are jpegs ?
use a NameFilter and pass it to listFiles

~aolbonics

cheeser, aolbonics is talking like a retard using speech as if you were on AIM or using single letters for you, are, you are, you're, see, etc. Talking like this is frowned upon in ##java and may result in you being silenced. You have a full keyboard so use it. We don't care if you talk like an
idiot with your friends but we don't want to sit through stuff like this: http://forums.oracle.com/forums/thread.jspa?threadID=499980&start=0&tstart=0

but am curious what u wanna do by extending File as JpegFile

~tell abhi27 about aolbonics

abhi27, aolbonics is talking like a retard using speech as if you were on AIM or using single letters for you, are, you are, you're, see, etc. Talking like this is frowned upon in ##java and may result in you being silenced. You have a full keyboard so use it. We don't care if you talk like an
idiot with your friends but we don't want to sit through stuff like this: http://forums.oracle.com/forums/thread.jspa?threadID=499980&start=0&tstart=0

http://www.theonion.com/content/video/in_the_know_should_we_be_shaming

hmm I dont get what the fuss is about
the reason for the popularity of english is its flexibility - and replacing u for you is not abusing the language in any way
they should actually start incorporating this in the OED

~u

U is Dutch for "you" or an aolbonic meaning "I am stupid".

~v

abhi27, I have no idea what v is.

well you are not so smart
hehe

~tell abhi27 about stupid bot

abhi27, you're the one talking to a robot. Duh.

well i am already labeled as stupid

grrrr

Hi guys, question about deploying a Hibernate webapp in Tomcat. This is the only app (so far) that's going to be running in the app server; is there any advantage to using JNDI to supply JDBC connections as opposed to allowing Hibernate to manage the pool (c3p0) itself? This is my first
production java host webapp…

c3p0 is not a good connection pooler. Listen to Hibernate when it says so

dbcp

well taking everything into account - dbcp

[TechGuy]: why do you say that? there's a 3 page thread in the Hibernate forums where Gavin recommends avoiding dbcp… i didn't notice the date though, it may have been a while back…

its the one that comes with tomcat

ok, overwhelming support for dbcp then

also as you said 'so far' it means later you might have more apps in there

anything's possible, although I don't forsee it for quite some time

tomcat will collect all the connections that are being unused which helps

dbcp works internal to tomcat and you can configure it to run as a part of your application

I was just trying to minimize the number of differences between my app's production config and it's development config… I develop using Maven/Jetty
didn't want to rely on the app server host if it all possible
that is, for configuration/the like

you can bundle dbcp in your war

that jus strengthens the case for dbcp

ok… so if I juse dbcp do I have to use JNDI hosting with it, or can I use it directly from the webapp?

you don't need jndi, no.

directly from the webapp

awesome
thanks guys

how do you know all are guys!

so, provided I'm not wanting to share the same connections amongst more than one app, is it safe to say that jndi provides no advantages in my scenario?
I was using guys as a generic term

there are no female programmers
=P

lol

and whoever said there was, is lying out of their ass

hehe

maybe you don't visit the newsgroups..

i was told at freshman orientation that the CS department was full of hot beautiful women that wore bikini's to class…
if i ever find the person who told me that i will hurt them! =P

Here's the thread I was talking about ("Please migrate away from DBCP")… of course it is 3 years old and may no longer apply: http://forum.hibernate.org/viewtopic.php?t=931132

•hironimus• jndi is useful if you want to share the pool across web applications on the same server

that's what I was thinking, thanks for confirming it.

Hello. non-static method createDocument(java.lang.String) cannot be referenced from a static context
what does it mean?

i tend to agree with the poor user who got caned by the hibernate team… we have been using hibernate and dbcp for long now and no problems so far

okey doke… well as I said this is all new to me so I was only going by what I had read

•youssef• simply what it says - you are calling a non-static method from static location (probably main?)

I suppose it'll be easy enough to switch from one to the other if I experience problems

im having real trouble finding hibernate-tools.jar

yes I am calling from main
all methods that are not in main have to be static?

•hironimus• depends on your architecture but in most cases yes - we dont have a JSR to standardize object pools
•youssef• well you can create an object of the class whose method you are calling and then call the method on the object

do (class where method is found).createDocument(somestring)

in netbeans, does anyone know how to get jee5 as an option in creating a web project? I've only got j2ee 1.4?

er yeah create a new object of that class then use that to call the method

•cs02rm0• its the age of eclipse man

i've tried, so many times, but it's just too buggy.

thanks

fix your client. it's spitting out crap around tab completed names.

seen from eclipse, swing was the source for swt

damn mirc users

eclipse once was swing based, so ibm decided to create swt

lol

its the invision client .. can you paste what it prints?

Ucs02rm0U its the age of eclipse man

the 'U' are in reverse video for me.

abhi27:i see small dots around your highlights

At the time, it probably made sense.

yeah they are actually the dots in unicode
upgrade your client to support unicode

lol

thanks

However, I think that time has (largely) passed.

yeah, so there's netbeans

Yeah, I've used NetBeans, it's nice. But, where I work, we mostly have standardized on Eclipse, so I use that instead.

IDEA++

brb

Eclipse *= a billion

so, no one knows how i can get netbeans to offer me EE 5 as an option? at this rate i'm going to have to try eclipse yet again. *shudder*

Curious, I have a device that is using standard Java and Encrypted Java - was wondering if it's possible to use standard java to decrypt the encrypted java? Help would be great on this!

offer you what?

A few ex-IDEA users here often lament the fact that management here no longer approves IDEA licenses, and tells people to use Eclipse instead.

yeah. eclipse is crap.

Cheap-asses

[TechGuy]: Yes. Cheap in a stupid way. IDEA licenses would easily pay for themselves in improved productivity (or so I hear).

"Java Persistence currently requires that the many-to-one side always be the owner" — why is this? Is this laziness in the spec?

damn I'm gonna hafta download an eval of IDEA now…

Is there really no simple way to do a bidirectional many-to-one that uses a join table?

why would you want a join table in a many-to-one?

If anyone can help, it would be much appreciated - otherwise I need to spend $1500US just to use this application!

Is it that Eclipse is just plain crap, or is it just far inferior to IDEA?

it's far inferior.

that's the way the existing schema works

it beats a sharp stick in the eye…

Heh.

did you try using @JoinTable ?

The framework architecture for Eclipse is okay. It's just the plugins themselves are shit

maybe the relationship from the many to the one is optional, and the db doesn't support nullable foreign keys?

no, I didn't try anything. I was just reading and it said the spec disallows it…k.

that wouldn't reall resolve that problem.

[TechGuy]: well that's the point. you can't build an *integrated* developer environment by throwing together a bunch of crappy plugins

Eclipse doesn't really bill itself as an IDE

that's why eclipse will always suck. that and IBM did business with the nazis

it's a platform!
blahblahblah

sure it would… the join table would have the foreign key on both the one and the many… if there was no relationship, there'd be no row

you'd still have FKs on the two end points

true, but you wouldn't have a need to have nullable fields with FKs in that case… without the join table you would

Please, I need to know - I have a device that runs Java, but some of the packages are encrypted and some are not - do you think that Java is decompiling the encrypted packages within java or would this happen externally?

~tell Tossbag about wina

if the many has a field that references the one, and you can't make it null and still have a FK, then how you do you have a "many" with no "one"?

Tossbag, Why is nobody answering? There are several possible reasons: 1) Nobody knows. 2) Everyone's too busy. 3) No one wants to talk about it.

Question doesn't make much sense. Though it's perfectly possible that there is Java code decrypting (not "decompiling") the data..

Eclipse is definately a platform, my project uses our own eclipse-based tools heavily
uEclipse is definately a platform, my project uses our own eclipse-based tools heavily/u

what tools are those?

are you trying to crack a program that you don't have a license for?

I don't know how to ask the question! Basically I can write Java for this device, but only if I purchase this rather expensive compiler - however looking into it, all it does is encode the java that one writes, uploads the encrypted .jar file to the device and the device runs java that decrypts
the code - I wanna know if there is a way to still write the java without using the expensive compiler? I have the files, could you help?

Question….. When you create a thread does it automatically "go away" when the run function completes? Or is there a way you have to stop it and clean up? Not quite sure… There is a stop method mentioned, but it says it's "depreciated"

"deprecated"
it'll get GC'd like every other java object

Ok so at the end of run it should get GC'd automatically then?

what is the device? And probably not. You have to extract the key data from the device that is being used to sign and extract the code.

3d model viewer/shader dev environment, map editor, script editor, java tools, etc

can we use Tossbag to play "Hackey Sack"?

doesn't seem like the sort of tools you'd need to develop in house

The device is AMX - www.amx.com, used to have a it's own language but now moved to java - half is encoded half is not? Surely the encoded files are decoded using java? yes?

Depends on what you're doing. You obviously like to assume things

I'm just wary of people who develop their own dev tools

Can someone please look at the files, tell me if I'm barking up the wrong tree - 5 sencs off your life, would be much appreciated!

more than likely the expensive compiler just digitally signs the code. with the signature all is lost

It's rarely needed in kiddie land where you're making basic crud applications

Is the signature within the java or external?

it's in the compiler probably
it's rarely needed even when working on the biggest apps. that's why it's the kind of thing I'm suspicious of

But if the device is running compiled code, then how would it read encrypted code without decompiling it first?

Tossbag, why are you trying to decrypt these files anyway?

Can you look at the files? You will understand what I mean..?

because the device has the public key

again, "biggest" has nothing to do with it

well really, the other key

There is no key on the device, I have the snapshot of the CF, no keys, just java!

Tossbag. are you asserting that compiled code can't be encrypted?

one key in the compiler is used to encrypt, the other key in all the devices is used to decrypt. you need to extract the first key from the compiler. or it might work the way bluray players work

sounds like someone's trying to hack a device

or, according to the RIAA, "use"

somebody has probably already hacked the device so your best bet is to look around
there are few commercial non-industrial systems out there that haven't been broken

There is only ONE device - this is the same for everyone - One package is fine, the next is enxrypted the other is not… I' not after a hack, I just want an answer? Can someone look at the files and see if it is pointing to a class that decrypts the other classes?

Tossbag, since jar files are easily read, and byte code easily reverse engineered they MAY encrypt certain files for security purposes
Tossbag, did you say this is a mobile device?

Seriously, think about it - you want to quit your job and freelance, however you can't because the product you develop for has just gone Java, however you need to spend $1500US to program for the device.. Would you not want to understand how it all works - BTW that's $1500US per CPU license!
So there's the Office PC and the onsite PC!

dbcp? don't know that one

can you look at the files for me please?

Sounds like you just got screwed by a proprietary technology. Live by the sword and die by the sword, my friend!

Tossbag, so, it cost 1,500 for SDK access?

welcome to capitalism. you'll have to look around, nobody here will prolly be willing to break the system for you

indeed. such things are likely to get you … "removed"

The system does not need to be broken - someone just needs to quickly look at the files and say - yes, it points to this class, here is what your looking for!

why not talk to the vendor?
if you don't wanna pay $whatever to dev for the device maybe there's a dev license

There excuse is they don't want IT to converge with AV otherwise there industry will suffer - eg. AV charges 40% mark up compare to the IT %5 markup!

Tossbag, even if you can discover which class reads the encrypted files, wouldn't that be useless without the public key?

maybe the files are just rot13'd

There is no public key - same files for everyone that uses the device! There are no keys…
what is rot13'd

dear lord.
it's clear no one wants to or is able to help.

Sure, I may not know much about Java - it just sits me that I spend half my life helping people that don't understand technology only to try to get some help and no-one wants to offer a hand….

Tossbag, the premise that all users likely have the same emebedded public key, does not preclude the necessity for a public key to decrypt the files. LOL

ebg 13 vf n irel fvzcyr pvcure

lol

well, you'd need a *private* key to decrypt

exclude*

All fun and games! just needed help… thanks anyway

and we didn't have any to offer.
don't get pissy

Sounds like you've got a project on your hands

OOPS! I stand corrected by cheeser, the *private* key is used to decrypt
uOOPS! I stand corrected by cheeser, the *private* key is used to decrypt/u

the public/private words don't really mean anything

So, how could a class find the path to WEB-INF?

meeper, in and of themselves no, but those terms were chosen by standards bodies so that we can understand one another when we discuss it.

ServletContext.getResource/getResourceAsStream?

and pass "WEB-INF"?

not sure, why do you need WEB-INF?

Seriously, I would really appreciate it if someone can take 5 minutes to look at these files - please download for www.iefx.com.au/lib.zip - I don't understand Java enough to understand it - however this is the OS files, this is what runs the entire thing - if you look under the Morpheus.jar
file you will see the encrypted data, while the rest of the SUN Java runtime files are not encrypted. There will be one time in your life where you

Need to load configuration files. Right now I have the properties files within the same package and am doing something like: classWithinPackage.getClassLoader().getResource(classWithinPackage.getPackage().getName().replaceAll("\\.", "/")).toString().replaceFirst("file:", "")

Tossbag, I thought you were going away

at least you're showing the channel your problem now
That makes things much easier

then check what ServletContext does when you call getResource/getResourceAsStream, it sounds like it is what you want.
then check what ServletContext does when you call getResource/getResourceAsStream, it sounds like it is what you want.

•octoberdan• what is it you are trying to do?

How very matrix of them )

note that ServletContext does not do the same thing as ClassLoader.getResource

Load a properties file in WEB-INF. I don't like keeping my configuration files directly in the packages

They named all there crap after Matrix - what's worse is it used to be named after Lord of the Rings!

•octoberdan• if you are using a servlet container to load all that, then the default class loader will point to WEB-INF directly

hi all

ROFLMAO, no way!

yeah, that's encrypted or obfuscated in some way, the class file's magic is wrong

does anyone know if it's necesary to add external repositories to install VE on eclipse?

•octoberdan• rather depends if you use websphere / tomcat etc etc

Is there a cross-container way of doing it?

•luiX_• come over to eclipse

Yes it is wrong, but why are the other files right? there is no more files that the OS runs, that's the whole package!

abhi27, have just seen the channel

did you check my suggestion yet?

nope .. websphere handles separately and tomcat handles separately, I think spring does something, but you will need to check that as i havent used it to do that

. o O (wonder if there's a RedPill.jar that contains the decryption key?)

lol

I can ensure that it doesn't get deployed to websphere, that's alright.

actually ernimril's suggestion might do the trick .. perhaps thats how spring does it

I'll give it a shot now

just put your props file in web-inf\classes
it should work in almost all containers

There needs to be a java.lang.ID-10-T error

then use getResourceAsStream

That's what I'm trying to get away from

public class IDTenTError extends Throwable

well i jar all my classes so my classes folder is empty

This is not a standalone program

its pretty easy =\

hmmm, now I just need to figure out how to get an instance of ServletContext to my class…
Is there a static way of getting a reference to it?

…

Here are the complete OS files www.iefx.com.au/amx.zip - that's the entire OS wothout the LIB files as above - it runs the LIB directory on boot, I can see the Servlets startup - this is my point, is it possible to decrypt these files within normal java? Can java execute at run time or compile
time only?

geez, Tossbag, if the platform was a profitable ISV venture for you before it was ported to Java, why would it not be now? If you don't want to learn Java, then why bother decrypting the files? If the $1500 is a bit steep for you, but you see a profit potential in the platform, just get a
part time job and buy the license, dude.

well, vxWorks is the OS and that's proprietary
It may be in that binary somewhere

ah

On Startup, servletHTTPContext, Jetty 4.2 ,socketListner and org.mortbay.jetty.Sever@f69db9 all startup, they are all part of the Lib files - surely there must be a file pointing to decrypt the rest?
It's more complex than just $1500 + $1500 - I want to understand, I want to understand why I could write the Java and it gets encypted, I want to understand why an 18 year old can replace me overnight because he knows more Java than I do - they use to have a proprietary language - that I could tell
you everything about. Now it's all changed! I just want to understand…

Tossbag its the nature of the industry

Can I write java and upload it, or does each file need to be encrypted? Isn't trying to understand these things what makes us all unique? Why do you wirte Java and not some other language? How would you feel if SUN turned around and said to you, that you now have to pay $3000 just to write
apps? Would you be happy with that?

proprietary languages are harder to update and maintain for a company that is also trying to sell a product. Java is an open standard that can work on literally any machine so its much easier for a company to use java and worry about what it will run on later.

Tossbag, I'm assuming from your comments that the platform / framework is dependent upon ISVs for it's success. Would make sense to increase ISV pool, by leveraging the ubiquity of Java on said devices.

thats probably why they went with java. if sun started demanding money from people to use its language, then you would see an outcry from the java devs and then a massive push to move from the language

Yes it would, but what about the people that devoted their life to the old ways? Now these new ways are slowly getting rid of the old users - if you look at the files you will see it's the same magic number in each? I want to know if Java can execute Java script at runtime or if it does
require it to be complied prior…

oh good geez…

java script is wrong word…

Java must be compiled prior
Java 6 adds programmatic compilation abilities

thats like saying we shouldnt use C or any high level langauge because it puts people who use assembly out of their jobs
or people who used to write old main frame code

I get your point and it makes sense - I'm obviously not getting my point across - I do appreciate your help though, I guess from here it is a case of accepting the obvious and picking up the Java books and learning it, whether it's encrypted or not… I guess I need to face the facts, it's
turned to Java and I need to start writing it!

Tossbag, there are some third party Java libraries that can parse Javascript at runtime, but Javascript is not Java. If it's Java code it will have to be compiled to byte code on the fly at runtime, and then executed.
If I'm wrong about this, someone feel free to correct me.

So Java code can be compiled on the fly, not necassarily at compile time?

WTF is this "encrypted Java" crap coming from?

clientTable = new HashtableString, String();, what's the proper way to make the value element a map as well?

uh no. But Java, in v6, can programmatically run its compiler

How can I find out what version this is running? Can I tell from the OS files?

java -version

Without that ability can I look at any of the classes?

Tossbag you should read up on java
that will help you in your quest to figure out what to do
~tell Tossbag about RBI

Tossbag, RBI is http://java.sun.com/docs/books/tutorial/reallybigindex.html

map = new HashtableString, MapString, String(); ?

Do you think that the files that are Encrypted are just compressed using another method of zip?
uDo you think that the files that are Encrypted are just compressed using another method of zip?/u

what do you mean by encrypted?
….

class files are not encrypted. They are in bytecode form

Would that be correct though? That's what I'm trying to figure out…

yes. why do you think it's not?

You added "?" like it was a question making me think you were not sure

Sorry. I am sure, the "?" was there because i thought I might be oversimplifying your problem
I will be more clear in the future.

Look at the files www.iefx.com.au/lib.zip - while the OS files are www.iefx.com.au/amx.zip - there are plenty of JAR files (Fundemental Files) that are normal Java files, but then there are a couple of key files that are not the standard format, I want to know if they are being parsed in by
one of the classes and decrypted?

The best way to learn is to try things out. Surely you must be having a IDE or compiler…

My IDE lacks things…
Compiler is on a server

Tossbag, was the previous "proprietary langauge" a compiled language, scripting language, or VM language?

Does eclipse have drag and drop to create an interface?

#eclipse

Thanks

Class files have a standard.. yes let's say there Magic Byte is 0xCA, 0xFE, while the other files have a magic byte of 0×6B, 0xBA
Complied language running on a risc processor - but very limited.

map = new HashtableString, HashtableString, String(); ?

It was converted to byte code for the Risc (MIPS)

Curious, since you said you have been using dbcp with hibernate… are you using JNDI, or are you using a custom (i.e. non-Hibernate provided) ConnectionProvider class?

Tossbag, so what you are actually trying to do is decompile the Java bytecode files, thus reverse engineering the application?

y0

there are the Piped(Input/Output)Stream classes and i need their mechanisms for a purpose that requires a larger ring buffer like 50-500 MB, but PIPE_SIZE is final :/, what's the easiest way to adopt their mechanisms with a larger PIPE_SIZE?

•hironimus• custom class, some sorta extension of a hibernate class which ties hibernate to c3po

Just trying to understand what it is doing with these encrypted files? But the more I look at them the more they looked compressed, rather than encrytped - they have the same start byte code for each file…!

thanks… probably something like this: http://wiki.apache.org/jakarta-commons/DBCP/Hibernate? just making sure

Tossbag which file?

What if you do a .put on a Map where the key already exists?

it replaces and returns the original value and puts the new value

Tossbag, which files look compressed? the jar files or the .class files?

first question… no that won't work because you are initializing the Hashtable as a different type than the container. Second question, the new value will replace the old one

Ah ok

Do you think these files could be decompressing the other files - foundation\java\util\jar\JarOutputStream.class or foundation\java\util\zip\CheckedInputStream.class?

•Luke_S• and the old will be returned - its very important sometimes

So with the structure I have String, String,String

Tossbag, jar files can be decompressed with a jar tool

The Class Files

If I want to update or add the inner element I have to update the outer key?

•hironimus• indeed

cool, many thanks

Tossbag, .class files are byte code and will have to be "decompiled".

no
oh

no… just get a reference to the inner map you want to update and call its put method, no need to replace the entire thing.. unless that's what you want to do

well yes they could be used to decompress the files

yes, and they can be except a couple of them, which are the importnat files, that's where I am trying to work out, how are they doing it?

So if I do something like
inner = outer.get("key");
inner.put("key", "value") will update inside outer?

yes

Ah ok

Tossbag, perhaps some of the class files were obsfucated just to prevent folks like you from doing what you are trying to do

And if I want to create a new element I would do outer.put("key", new HashtableString,String);

however watch out for nulls if the outer key doesn't exist in the map

Then use the previous method to update it?

yup

Probably, but the question being is where are they deobsfuctating them? In java?

Ok I think I got this now
*

Tossbag, they dont need to deobfuscate the code at runtime

they dont need too, they have the source

Why? Don't they need to turn back into original bytecode?

context.getResource("/WEB-INF").getPath() gives me "/localhost/appname/WEB-INF"

~tell Tossbag about sol

Tossbag, sol is Shit Out of Luck - in other words, no chance.

Tossbag no, thats the whole point in obfuscating code so people cannot steal it.

Tossbag, no, most obsfucation tools rename the entites in the human readable Java code to something unintelligable prior to compiling to byte code, so that if it is reverse engineered, it'll look like "gobbledy gook".

•octoberdan• if this is something new - go spring

Ok then, that makes more sense - so there is a possibility that the bytecode is obsfucated on the PC, then uploaded to the device, while JAVA knows how to read the obsfucated bytecode, without decoding it! Yes?

now he gets it

Too late…

what is a good data structure that is a map and it is sorted

SortedMap

Thank you for your help.

JLearn haha I guess I should have checked that first

yw, I think

~javadoc SortedMap

JLearn, please see java.util.SortedMap: http://java.sun.com/javase/6/docs/api/java/util/SortedMap.html

Does anyone here use Hibernate, Toplink (Essentials), OpenJPA, or JPOX?
Or a JPA implementation which is not one of those four?!

All of them ?? At once?

"or JPOX"

~tell RProgrammer about anyone

RProgrammer, Instead of asking whether anyone works with something you need help with, please save time by asking your actual question. If someone knows and wants/has time to help, perhaps he/she will.

Ok:
Hibernate does not honor optional entity relationships
Toplink does not allow the datasource to be overridden when creating an EntityManager or EntityManagerFactory at runtime
OpenJPA requires some strange form of compile time enhancement which I was hoping someone here would have experience with
JPOX Does not actually support the JPA specification (it's JDO)
But if anyone knows of another implementation I would love to hear about it

how much overhead difference is there between a List and a SortedMap
that we could iterate over
meaning, I would like a Map so that I could immediately go to an element, but i would also like to iterate across it
in a sorted manner

•sleepster• well not sure but you can get the valueSet/keySet from teh map and treat it as a collection to iterate over
•RProgrammer• All I can give is - http://java-source.net/open-source/persistence

I suppose use Map.keySet() to get the Set of keys, pass that as an argument to a List constructor, sort the List, and iterate your list of keys. That's one way at least.

•RProgrammer• you might want to try over at #hibernate

Ok, but #hibernate seemed pretty dead

RProgrammer, some communities prefer forums, boards, and / or newsgroups to IRC

Yeah, but they're not responding on the forums.

I have a panel that implements Scrollable, yet the scrollpane that I am adding it to is still scrolling when it is resized less than the panels preferred size and not the one returned by, getPreferredScrollableViewportSize(), does this function not do that?

niiiice sun microsystems is switching its NYSE symbol to JAVA

people in suits are really clamoring that move I'm sure.
and by clamoring I really mean saying "wtf" to themselves

yeh

i would like to know who thought of that idea

could someone tell me why scrollpane in this code is not starting to scroll at 50,50? The scrollableJPanel class there returns that… http://rafb.net/p/yXByvf59.html

Does anyone here use an entire database per client and ORM?
If so, would you please tell me how you do it…?

sorry here it is with the imports http://rafb.net/p/PeRnls52.html

what?

did you say… an entire DB per client?

Yes

um

Very large scale systems
Each client needs over 30 tables (entities)

wow

And to give an idea, there are users within the clients

well that's expected

Right

you might try asking your actual question.

This is my question. How does one do this with JPA?
I just recently found out that Toplink simply doesn't support this

it's not a toplink issue, it's an ORM issue

But the only alternative I can think of is to have a column in each table for Client ID

use spring and inject the DS into toplink by client
and don't ever do this again

Actually, toplink caches the jdbc connection per persistence unit

Lesse… can't create separate EntityManagers…

Right

hibernate can

Have an EntityManagerFactory and programmatically set things like that

or at least, spring+hibernate supports that.

Ok, but hibernate has a different problem

i still don't know what you're trying to do.

any Java Devs here from Austin?

I want to have a DB per client. But if that is a bad way to do things, someone say so.

is this scrollable interface broken or something? http://rafb.net/p/PeRnls52.html

it sounds horrid
why do you think you need a DB/client? and wtf is a "client" ?

A paying customer
sorry

dear lord

ok you need to read up on relational databases i think

The only alternative I can think of is a primary key column in each table

…

every table will have a PK.
with the exception of a join table at least.
8^)=

col = new ArrayListMPPProductBOMLine(); and I do this: (MPPProductBOMLine[])col.toArray(); and I am getting a java.lang.ClassCastException: [Ljava.lang.Object; what am I doing wrong?

there?

(MPPProductBOMLine[])col.toArray(new MPPProductBOMLine[col.size()])

hm…
so am I crazy to leave a small to mid-sized company to go work for a startup while I"m in the middle of a project?
lol

heh i had someone here at work ask me why i use tables (which mostly resembles objects) instead of flat files since the design was not "relational"
depends svm

cheeser, thanks man

does the startup have potential and will it be here in 1 year?

They've existed for 2 years so far.
In an extremely volatile industry

does anyone know if the scrollable interface or scrollpane is broken??????/
because this code according to the docs i believe should work. http://rafb.net/p/PeRnls52.html

I've had a guy tha tassumes a "Table is a class and a row is an object."
And tried to write an object-relational mapping based on that. Failing to account for inheritance.

svm_invictvs, talk about suckass when that encounters association tables

yeah…

Is there a base convert function in java?

/part

I can't believe no one out there ever needs to have scrollPane only scroll on a smaller size than preferredSize

huh?

re
why's this a valid code? "String[] strings = {"C", "D", };"

because it is

first I thought a 'null' will be the third element, but I've realised the array will contain only 2 elements

JLS answers the question

enums are the same ;]

How easy is it to get started in J2ME, and what development environment would people recommend for it?

I would suggest using Netbeans mobility pack
and it would be a peace of cake after J2SE ;]

lol

s/peace/piece/

am considering writing a WCAP todo list client

it has a graphical GUI designer also ;]

because I'd actully use it

hi all. i have installed jre (.bashrc path, classpath points to jdk directory) but when i run java it uses one from jre. where can i change this to run from jdk ?

the jdk is the jre+dev tools.
but if you have a separate JRE installed, just update your PATH to point the JDK's bin/

i know, but java from jre is different from java from jdk !

so fix your path
there's nothing magical going on there.

mine PATH is pointing to jdk's version !!!

'which java' returns the java from the JDK install dir?

i simply run "java -version" and get version of jre, but path points to jdk !

. . .

8^)=

Take a deep breath.
Now run: which java
Check the path.

)

and then echo $PATH to double check

/usr/bin

so file /usr/bin/java to see where it goes.

oh. thanks a lot ! i'v to think as linux user )

jchauncey makes me think of poodles and other snobby little dogs

haha some one built a robot that does the knife/hand trick from aliens
that's awesome

link?

give it a try and let us know how that goes.

http://www.5voltcore.com/index.html?/content/khcb.html
too bad they couldnt get bill paxon for the demo :0

http://www.youtube.com/watch?v=AR-ljmQqIjk

I/win 6
sorry :/

you lose!

always happens

8^)=

r0bby 0 IRC client 1
maybe I should stick with my aliases

~kancho amki
~kancho amnesiac
my apologies amki
8^)=

cheeser, hahahaha that's from Naruto!
:P

man
even by IT standards you guys are a bunch of dorks

i've never watched/seen that.

uhm?

??

tmccrary, sorry?

https://ksl.dev.java.net/

cheeser, buuu, shame on you dude

get to work!
i'm not 12 years old

We all know we're dorks, don't rub it in!

cheeser, me neither

s/We all/I/
s/we're/I'm/

You're not a dork r0bby

~be r0bby

FileNotFoundException is a subclass of IOException; Exception won't directly catch that.

Yes I am

You're a bastard

hey! he knows who his mom is!

s/bas//

lol

well, reasonably sure at least.

cheeser, you're r0bby's regex

…

wow, KSL sounds perfect for my project!

Look on the bright side, I can take every bit of abuse thrown at me, and gracefully.

Damn straight

tmccrary, do you have any problem?
tmccrary, any personal problem you want to share with us?

What?

I don't take it personally, I'll just get even one day.

tmccrary, calling us dorks isn't that nice you know

Sweet, sweet revenge
Who said I have to be nice to bunch of dorks?
I was joking anyway

i'd kill everyone in this room for one sweet, sweet drop of beer

hehe

cheeser, two please…
cheeser, it's friday for god sake… beer is a must

++beer

My class implements Runnable. When I call myclass.run() from another class, the first class's execution is blocked. How to I fire off a new thread and have the originating thread continue unblocked?

new Thread(myclass).start()

ah!

is their a j#.net room on freenode? havent found it

huh, people still use j#?

lol, people use j#?
WhatTheFuzz:
##csharp is good for .net questions I find

yea rookie like me need a visual java… unless you know of a visual java better than vstudio's
eclipse is paper notepad…i need a lil drag n drop in my life

uh, why do you need visual java

~tell WhatTheFuzz about newbie ide

WhatTheFuzz, newbies shouldn't use IDEs. http://weblogs.java.net/blog/editors/archives/2007/02/you_better_get.html and http://weblogs.java.net/blog/gsporar/archive/2007/02/tools_that_do_t.html

ouch
I hope this is an elaborate joke
But I suppose he fits the profile of your average .net programmer

java and .net are similar enough that the average .net programmer is about as good as the average java programmer

yeah… the average .net programmer just switched over from VB and "needs a lil drag n drop in his life"

lol from c# actually
close

You don't see that in Java, because all of Java' gui design tools suck

So I tried running jsizer with batik so it'd produce SVGs…

did you get it to work?

not quite.

why not?

I got sidetracked.
Then went to bed.'

ah…
damn it, you have to prioritize, you can sleep when you are dead!

I'll play with it more…s
lol

just for fun, try running jsizer on rt.jar, there is one class that stands out..
it is actually quite non-fun that the corba parts take up soo much complexity…

lol
OMG CORBA
I lke Josh Bloch good examples of bad API design.
he goes, "I won't tell you where these names come from…"
"BUt its' CORBA"

reminds me of the movie "Taxi"

lol
That movie was crazy
Robert DeNerio
Or however the fuck it's spelle.

yeah, and the policeman used to make such names, like "Cobra, Zen" etc

What's the full RFC on FTP?
945 or something, right?

959
~rfc 959

are there any good programs for detecting deadlocks? my application is locking up in some native call for Java AWT

ftp://ftp.rfc-editor.org/in-notes/rfc959.txt or http://www.faqs.org/rfcs/rfc959.html - depending on your preference.

C-\ or Ctrl+Break in the console you are running in
most debuggers and profilers also have the ability to show deadlocks…

Nothing that's free, as far ask I know.
??
hm

my profiler jmp, can do it, it is for java 1.2 up to java/5 only though

hi
any one using the iText for pdf creating ?

many people do…

I have problem with firefox
it just won't show the pdf generated

is the pdf generated correctly if you open the pdf on its own?

firefox handles html, firefox does not handle pdf:s

you need a pdf plugin
set it up to open it with Adobe Acrobat
+Reader

ah.

yeah ok
but how to bypass that

you can't

damn

if you find the adobe acrobrat browser plugin, it can open within firefox, but not w/o that.

*meow*

thanks guys

ok thought there is a way out of this situation
thank you

giggity

If I have 32bit operating system running on 64bit CPU, do I download 64bit jdk or 32bit jdk?

32?

32

oh thank you

Walter and Perry!
lol
So a priest and a rabbi are walking down teh road…

oh crap. I smell a bad joke.

The priest and the Rabbi encounter a boy and the priest turns to the Rabbi and says, "Hey, you wanna screw that little kid?"
And the Rabbi goes, "Out of what?"
The funny thing is, half my family is Catholic and half my family is Jewish.

hahaha
~svm_invictvs++

svm_invictvs has a karma level of 9, jottinger

svm_invictvs++

svm_invictvs has a karma level of 10, r0bby

you deserve that

Oh god, where did I get 10 karma points?

~karma

I guess the factoid 'help karma' might be appropriate:
To increment someone's karma, use ~nickname++, e.g., ~hironimus++. To decrement, use — instead of ++. To view someone's karma level, use ~karma nickname, e.g., ~karma hironimus

I know *how* karma points are added.

no, that was for my benefit, I didn't know
newbie here

ah

~tell hironimus about javabot

http://javachannel.net/wiki/pmwiki.php/FAQ/Javabot

that will help

I suppose there's a way to have javabot privately msg you eh?
ok
thx

Yes, /msg javabot hi

gotcha, sorry all

I just added a basic pdf export to jsizer…

ah.
cool

itext is nice…
now just to rotate the graphics…

It took me a little bit of time to find out where you actualy rended the graphics.
It didn't help I was also tired as hell.

do you know about some channels related to UML modelling?

msg chanserv

is there a way to get the list data from a JList?

how?

hello
if I become a born against christian will Jesus Christ suck my dick in heaven?

http://irc.netsplit.de/channels/?net=freenode

Try #politics

i think thats a question for #philosophy

geez
everyone keep sending me to different channel

who sent you here?

what's wrong with this code? seqNum = ((long) buf.getInt()) & 0×00000000FFFFFFFFL; eclipse complains 'type mismatch: cannot convert from long to int'

ok, politics then phil
#C++ sent me here

lol

is the Java AWT source code supplied with the JDK 6.0?

abhorrent, got any good answer yet?

did you install jdk6 update 2?

src.zip contains the java code

Thanks, joed

the native code is not available as easy…

sleepster, jni code is not included

hmm.. I think I need the native code…

but you can get most of the code from openjdk…

what type is seqNum ?

thanks
well I am deadlocking in Java.Awt Toolkit.initIDs

if it's an int, you need to cast the result of the expression before assigning it

pfn do you know where I could get the JNI code?

openjdk, as ernimril said
why would you want it

oh I see..
well my call is deadlocking on Toolkit.initIDs
which is a JNI call

why are you calling .initIDs

well I am not directly..
I am calling Toolkit.loadLibraries()

and why are you doing that?

to ensure that the necessary native libraries are loaded

Toolkit?

yeah

is there such a method?

Toolkit.loadLibraries()
yeah

where is this documented?

uh, you are using extra native libraries?

String _x = "abc"; how to get the reference address of _x ?

_x

not the value hmm.

what are you looking for exactly

how to print it then?

string is immutable

how to print the address where _x is stored

why are you looking for that?

there are no addresses in java

java doesn't offer such a capability

but objects have reference

you can try system.identityHashCode

~pbv

Java only supports pass by value, not pass by reference (references to objects are passed by value). See http://tinyurl.com/ynr5d3, http://tinyurl.com/ywlv6d (especially http://tinyurl.com/yvppac), and http://tinyurl.com/4wgdh (search for "Passing Reference Data Type Arguments")

yes. references are opaque

which is effectively memory location

hmm.. I am looking at the moment for the call

no pointer arithmetics
the clostest you will get is System.identityHash

I didn't mean pointer arithmetics
ok
Object _a = new Object(); System.out.println(_a);
returns java.lang.Object@3e25a5
what's @3e25a5
isn't it an address?

the reference value

identityhashcode

or that

reference value ok.. which is a virtual address or something?

oh I must apologize.. so I am creating a new Color class which is a part of the AWT.. this Color class calls Toolkit.loadlibraries
I was thinking that the code seemed too clean to be mind
mine

what exactly are you trying do to?

where is toolkit.loadlibraries documented?

it depends on the vm. it often is a heap address/offset of some type.

~javadoc ToolKit

I don't know of any documentation for ToolKit

~javadoc Toolkit

cheeser, please see java.awt.Toolkit: http://java.sun.com/javase/6/docs/api/java/awt/Toolkit.html

when you compare two objects.. basically == will compare their identityHash right?

there it is.
i need to fix that case-awareness

cheeser, look again :p

i was just looking for Toolkit

Duesentrieb, hence it is an address.. hm

b0r3d, basically, yes

~javadoc System.loadLibrary(*)

cheeser, please see java.lang.System.loadLibrary(java.lang.String): http://java.sun.com/javase/6/docs/api/java/lang/System.html#loadLibrary(java.lang.String)

how i can solve a problem, with slow querys. i've many inserts, and this get slow selects.. i've about 8.000 querys/s

how can i say
where field1 = field2+"string"

guys i'm thinking of a good way to store multiple shipment addresses. the schema is quite clear, just got an addresses table with firstname, lastname, street, etc. but the frontend… i'm not so sure when i should UPDATE the address, or INSERT a new one.. say they only change the street
number because they mistyped.. should i INSERT or somehow ON DUPLICATE KEY over some of the fields….
thinking tihnking…

I am running apache 2.XX php 5 and my sql host 4.1 on win98se however I can't get mysql 4.1 to run, when I installed it it didn't ask for a root password, after opening up a ms dos prompt and cd'ing to the bin directory typing in mysql -u root it askes
for a password since I didn't give it a password leaving that blank it says 10061 error
it used to work if I launched an init txt file with the root and password set, and then opened up a new ms dos window and launched mysqlnt now it doesn't even do that all I et is 10061 errors

Trigger in wrong schema
What's that all about?

"PC Load Letter" What the fuck does that mean?

It should be noted that I'm not a developer and has had nothing to do with triggers, I'm merely trying to rename a database

PC Load Letter means load letter size paper into your printer.

i can't find help on gettext.. gahhh

does anyone know what could be wrong with mysql 4.1 not being connected on win98?

create table exercise_xml (id int(11));

Table 'exercise_xml' already exists

drop table exercise_xml;

Unknown table 'exercise_xml'
This is also a bit strange?
Ah, stray .frm

.

Hi, i have a question regarding character set and collations in MySQL5.2, anyone firm with that?

just ask

5.2?

"Hello World"

ehh… MySql 5
5.0.41-log
I already tried a but with CONVERT and CAST… but then I end up with "Hell? W?rld"

Trigger in wrong schema and get "Empty set" when doing 'show triggers;', isn't that a bit contradictory ?

SELECT CONVERT(_latin1'Müller' USING utf8);
SELECT CONVERT(_utf8'Müller' USING latn1);

in both cases you get "Müller" but I want "Muller"
a UNIQUE constrain on a varchar column makes a check on that…
SELECT 'Müller' = 'Muller'; return 1

how to connect database and web page ?

You'll need a scripting language to go between - PHP, Perl, ASP, whatever.

what about java ?

That can work.

you will have to have a look at JDBC with Java

snoyes pm

You'll need to register your nick and identify to nickserv before you can send a pm.

i dont know much about these things please some one tell me

I have a table of sales. How could I get the Average sale for each day of the week?
average number of sales, i mean

SELECT WEEKDAY(saleDate) AS day, AVG(sales) FROM table GROUP BY day;

oh… I guess you need a subselect for that…

Is there a way to query the mime type of a field using the 'file' program in linux… or some other way that doesn't involve extracting the data into an external script?
that is, of a blob field

maybe something like: SELECT WEEKDAY(saleDate) AS weekday, AVG(cnt) FROM (SELECT saleDate, COUNT(sales) as cnt FROM tb_sales GROUP BY day) AS sub GROUP BY weekday;

mysql -e "SELECT blobField FROM table WHERE id = x" | file -

oh, snoyes, thanks

please help!! what does index mean?

Hello using mysql with php, if I have a remote database, mysql_pconnect is better than mysql_connect?

key

You know what the index in the back of a book does? An index in mysql does the same thing.

still no body here knowing how to turn "Hellö Wörld" into "Hello World"?

what's the difference between group two columns as a unique unit and grouping two columns as a index unit?

the file command has a –mime argument

UNIQUE says that combination has to be unique. INDEX or KEY just means it can look them up fast; no statements about the uniqueness

snoyes it's a category

what's a category?

chaper 1, chapter 2, etc
snoyes i see!!! so after making two columns indexed, should i make that group unique or make each of them unique?

There's no need to do both INDEX and UNIQUE. UNIQUE is an index, plus a unique constraint.

snoyes oops!
i need a unique combo of two columns

Then put UNIQUE(field1, field2)

you can make a unique index on two columns

wich information contain the file db.frm?

thanks! but i don't know why some other people told me to use index for this..
snoyes flung please help one more things simply. i found two other choices: fulltext and primary. why are they simply?

DavidHKMrPowers, unique(.. is an index

archivist just got that

.frm files contain the format of the data

primary is like unique, but doesn't allow nulls. You can only have one primary index on a table. fulltext is for when you need to search text data for keywords.

flung is there a way to repair thi file?

you can try REPAIR TABLE, but I kinda doubt that will work

snoyes i see! thanks
i always set the first column to be primary so it doens't matter

java servlet hosting applet is for web page design is it ?

flung, i have strange entries in db.frm, things from squid and squid reports is that normal??

no
web application

applets can be embedded in web pages, but do not use them for the whole web page.

also applet is for virus too

how are you reading the frm?

flung well i just make a cat db.frm
flung can you take a look of the paste?

it should just be a bunch of garbage
binary

i have front end designed with applet how to connect it to database(mysql)

wie kann ich collation und den character set mit dem mysql client auslesen bei mysql hosting 4.0.24

if you can read it like a text file, then it's beyond repaid
repair

ups

DavidHKMrPowers snoyes ?

with the jdbc link I already gave you.

how do I get the charatset and the collation from my database host with the mysql client in mysql 4.0.24

flung excuse me, what is mean? must i reinstall mysql?

snoyes ok

at least recreate that database

flung, http://www.pastebin.ca/635895

5.0.26 … should "CREATE FUNCTION BEGIN … SQL/PSM code … END" work?

yes

I am getting tons of syntax errors… I can define a 'null' function (just an empty begin end block), but anything else causes an error.

if you know the users and permissions, you can create a new database, then cp your old tables over

Did you remember to change the delimiter?

go on… (I didn't)
what is the delimiter?

http://dev.mysql.com/doc/refman/5.0/en/begin-end.html

flung, you mean create a new database using command line? with root user?

yeah, make sure you back up first

how can i view FK constraints on the table? and, additionally, refering a table?

snoyes thank you meet you later
bye

see ya

comment is not good, it contains only part of that , plus it gets overwritten by "innodb free"

flung but mysql doesnt start…. how can i create new database if mysql dont start

mysql_install_db
or something like that

wow! so I can't use ',''s at all unless I change it?

brr…. how to use utf8 in stored functions? Incorrect string value: '\xFCller' for column 's' at row 1
http://www.paste2.org/p/5234

It's kinda the same concept as having to escape quotes inside strings.
so do DELIMITER anythingHereExceptASemiColon
create function blah blah blah
DELIMITER ;

should this work though… http://www.paste2.org/p/5235

that comma on line 5 should be a semicolon, and you pick a new delimiter to use on line 7.

hrm… a semicolon on line 7 causes an error.

of course, because you didn't change the delimiter before line 3.
http://www.paste2.org/p/5236

ahhhh… thanks
I didn't realise the BEGIN, END block had its own (original) delimiter.
heheh… how to find out what I set the delimiter to?
OK, everything is working nice

You can pick whatever delimiter you like.
within reason

I had 'broken' my prompt by selecting something random.

please can anyone help? how to use utf8 in stored functions? I get the error Incorrect string value: '\xFCller' for column 's' at row 1

CALL randomTest();
sorry, I don't knwo

hi everyone, i'm having trouble getting = to work on a DATE field
i definately have fields in the table which have entries for 2007
but when i do WHERE `joindate` = '2007'
it only returns records from 2006
any ideas please?

i have 2 tables in one DB that look alike(same info), how can i compare them, and look at differences?

2006 is less than 2007…
'2007-01-01' is greater than the string '2007'

yeah, but i have the equals there too, right? 2007 is less than or equal to 2007 yeah?

you could do WHERE joindate '2008', or you could do where YEAR(joindate) = 2007, or WHERE joindate = '2007-12-31'

hmm
see, i'm wanting to be able to do
WHERE joindate = 2007
or this:
WHERE joindate = 2007-03
or:
WHERE joindate = 2007-03-17

hi, good morning
I have a little BIG problem I have a FUll DB backup that I need to import to a empty slave…
the problem is that the whole SQL file is about 50Gbs
and the process takes forever….
there's a way to improve the importing speed?

!man speed of insert statements

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

snoyes, in a similar situation, i do LIKE %2007% or LIKE %2007-03% and it does what i want
any ideas?

I gave you three options.

is there any way to get the current result set row number in a separate column?

but i can't see any of them working once i start to get months and/or days involved

the_wench, I checked it before, most of those tricks aplly only to MyIsam

http://www.xaprb.com/blog/2006/12/02/how-to-number-rows-in-mysql/

but the whole backup is InnoDB

getting error …. "ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111)"?

$FILE_NAME

i tried with mysql_safe & but still there

it should be fast ..

I'd take the approach of turning your value into the latest possible date for that condition. If you only have a year, append '-12-31' to it. If you have a year and month, append 31 to it. If you have year, month, and day, you're all set.

snoyes, yeah, looks like that might be the best way to do it. thanks for your help!

snoyes, thanks alot, the described approach works fine

Starting mysqld daemon with databases from /var/lib/mysql
STOPPING server from pid file /var/run/mysqld/mysqld.pid
6 mysqld
[1]+ Done mysqld_safe
Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock"?

Hey everyone, how can I query 2 records and switch 2 field values?

anyone know how to compare tables(if they have same fields) and look at diferences
?

toolkit

how to start mysql server in kubuntu

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.

ty

snoyes how to start mysql server

!man unix post installation

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

what is that 'benchmark' function?
I seem to remember you can itterate over sql hosting to get some stats…

!man benchmark

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

!man information functions

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

heh… easy when you know how!
thanks again snoyes
either mysql is dead or my watch has stopped

I need a query to order some Topics based on "popularity", which would be the volume of votes and whatnot over time, I have a basic query, but I had to scope the time interval to the past 24 hours, but then everything else is not included in the set, and if the site slows down, my Top N list
gets shorter, does anyone know a better way to do this?

is there a way to get mysqldump to dump just table information, routines, etc?

mysqldump -d

ah thanks, I've been looking at –help etc didn't see that

it's between -t, –no-create-info and -N, –no-set-names

are index names supposed to be unique in the whole database and not just in the table?

no, unique within the table is sufficient.

Hmmm… I'm having a weird problem with doing updates. mysql starts thrashing the disk on one update but is fine with update which is almost 100% similar to the one which works fine
*sigh* great sentence there
hold on a sec
So I have a big table which I'm hoping to normalize a bit. So I take two column out of it and give them primary keys and build indices for the datas. Then I'd like to update the key-numbers back to the big table and it's done like this: update bigt, smallt set bigt.key = smallt.id where bigt.data =
smallt.data

toma, OK
and one column works fine and the other doesn't?

You need to specify more complete requirements. I don't think you know what they are yet. What, exactly, do you want this query to produce?

yes
the machine just seems to do a lot of disk-access

and you checked that both columns are indexed?

yes

what are the two datatypes?

varchar( 15 )

(both columns are indexed in both tabels?)
are the datatypes the same in all tables?

there is index for bigt.data and for both of the smallt.data's
yes

sivaji@sivaji-desktop:~$ sudo mysql -u root
Access denied for user 'root'@'localhost' (using password: NO)

toma, I didn't follow your notation.

i am not getting mysql console why ?

toma, you will probably find an error of the kind I am suggesting somewhere.

please someone help i am very new to mysql

check the count(*) query over the join for both columns.
what is your root password?
wait… I mean try -pSomething
or -p then hit return when it prompts

faceface linux

yesss

faceface ?

you just installed mysql?

yes

read about setting up accounts

can I check if a index already exists? (and FK)

ummm, what do you mean?

yyya i read i cant understand /

toma, your update needs a join right?
read more

well, it doesn't use the sql-join

faceface why did you ask my root password ?

toma, I thought I knew what you were doing, but now I don't

because I'm a noob in case you didn't notice

your 'mysql root' is not the same as your linux root

does anyone know how to replicate an INTERSECT in mysql?

faceface that is not my root password

toma you have table "one", with two varchar(15) cols, and you want to normalize the data?
by default mysql installs one user, the so called 'root' user. That user has a 'null' password by default.
now go figure!

Yes.

so you create table "one_a" and "one_b" to hold the distinct values of column a and b?

yup

can I check if a index already exists? (and FK)

and one_a and one_b both have auto-incriment PK,s that you want to update back into table "one" as FK's

how to get mysql console ?

Actually… to get real picky… default installs four users and the password is an empty string, not NULL.

automatically or manually?

exactly

;-) tell that to ksivaji !
toma, so you have to do two update queries, both using joins

hi; i have what should be an easy q

automatically

don't knwo

please someone tell me how to get mysql console ?

toma, so you have to do two update queries, both using joins …
try adding a '-p' to the command you posted

i have some ruby code, but really is just the query here http://pastie.caboo.se/82796

yes. but I don't use sql command JOIN with doing it. I was using update a,b set a.fk = b.pk where a.data = b.data

sivaji@sivaji-desktop:~$ sudo mysql -u root -p
Enter password:
Access denied for user 'root'@'localhost' (using password: YES)

Your MySQL root user has a password on it. You, or someone who installed MySQL, set one up. You have to provide the correct password.
You don't have to do sudo

toma, looks good… I think

did you use the option -p ?

drop the sudo…

roxlu yes
faceface ok

it may be useful to note i have class_uid as a primary key. so here's the issue. when i run this, no course_div is set to 'l' (the letter), eventhough a puts right before it shows that the var. course_div holds 'l'

"using passowrd: yes" implies that…

toma, select count(*) from one inner join one_a on one.a_data = one_a.a_data

access denied access denied access denied access denied what to do ?

or however your real col names translate… you said… "a.data = b.data" - join on that and count(*)
and what did you enter for teh pwd?
i.e. just hit return

ok, a sec

If you don't know the correct password, you will have to reset the root password

he is just bumbling, I bet its still a blank string.

mysql root password or system root password ?

Na. If it was a blank screen, leaving off the -p would have let him in

ok, now try -h localhost

mysql root password.

dang… I didn't anticipate that question
host issues I bet

His error message indicates he is already connecting to localhost.

grrr!!!

faceface i dont remember the password

stop taunting me!!!

Sorry! Just trying to help

;-)
just joking around

seekwill how to change ?
mysql root password ?

If you can follow directions…
!tell ksivaji about reset root

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

Read the WHOLE page first.

wow! smart

Services " i am using kubuntu

Keep reading

I think this will take longer than a sec, the whole machine is bogging down ;P

Like I said, the WHOLE page…

ok

gentoo–

( it's running the update in the background so… )
maybe I'll try to abort it before testing more

toma, are you looking at the join that gave the quick update?
or the join that gives the slow update?

the slow update

and how many rows in each table?
toma, try the join for the quick update… and prolly terminate the update. I think you have a problem with your join.

bind-address = local host or ip i am not going to access any remote database

the big table is around 62 million rows and the small tables are about one million and the other is 8 million. The fast one to update is suprisingly the 8 million row table

Just comment it out for the time being

Okies, I'll test the join-version then

seekwill what do you mean ?

hello all, care to share your opinions on serving 5mb to 15mb file downloads from mysql opposed to the file system (via php)?

images

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

Why are you touching that setting?

Both joins should give 64 million. And just to double check, all four 'data' columns are indexed and of the same datatype?

seekwill it is there in config manual

Is it in the reset root manual?
Fix one problem at a time.

yup, they are

seekwill no it is set to local host i suspect my probelm may be because of that ?

no

toma and how fast was the quick update?

Your problem is that you do not know your MySQL root password

50 minutes

anyway, 'explain' both joins and try to figure it out

If that was the problem, you would not be getting that error message

ok

okies, I'll try that. Thanks

toma, also look at the server config… you could boost performance by increasing the amount of memory allocated to certain buffers.
at 64 million rows you have to start thinking about tweeking the server!

any other performance issues with serving large files?

(assuming your server is decent)

already did look at them but I thought that I'd need to know more about how the internals work before starting to do tweaks

toma, if you find any good docs on internals let me know!

4-way opteron / 8G ram

but boosting one or two buffers is all you need typically
by boosting you can safely read 'maxing out'

yeah, I tried to look if there is some generic cache I could use but it looked like that the OS disk cache is all that it needs. So increasing the query cache probably wouldn't help at this point
but I dunno
I'm very much of a newbie with databases

sort_key off the top of my head

Is there a round-to-even in MySQL?

can you use MOD to do it?

key_buffer_size is also critical.

if I want to do something like a != to in my where clause what is the syntax?

WHERE nick != 'will'

'xamox'

seekwill, thx

is preferable (standard).

seekwill, is that case senstive?

Depends on collation.

Xgc, excuse my ignorance, but what is collation?

http://dev.mysql.com/doc/refman/5.0/en/charset-collate.html
http://dev.mysql.com/doc/refman/5.0/en/charset-charsets.html

hi

The collation will determine if it"case insensitive"
The collation will determine if it's "case insensitive" (ci) or not.

Xgc, is collation mysql specific?

"it" == comparisons.
No.
You'll find similar issues in other databases.

it means sort order ish

Xgc, thx

if i run a select and the output is just too long. how do I stop it?

http://dev.mysql.com/doc/refman/5.0/en/charset-collations.html

BlkPoohba, use a better where clause

i know that now and then.

lol

Chapter 10 (in the 5.0 docs) has other useful detail. 10.4 might be a good place to start.

Xgc, alright, thanks for the help.

Bah. Just read all of 10.

how do I show the sql to create a table from the mysql prompt?

!man show create table

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

!man show constraints

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

ty

eh heh

just kill the process
and in future, dont use massive result returns

SHOW COLLATION Syntax !

how can i see FKs?

!man show create table

once, i had a query that wanted to return nearly 10 million rows

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

++
+=10000

needless to say, the server didnt like it

create table ? and what if i need to know what references some table?

Bizzy is the reason MySQL added the –safe-updates option

anyone know of any research or published benchmarks on serving files from the db vs. filesystem?

besides, this is not machine readable really (the show create table)

if I'm doing a sub select, hom can I reference a column in the parent select, simply using the column name doesn't work as expected, but it does run

faceface, bot does not understand your version of the nick++ syntax

what?

snoyes:–

information_schema contains some of that kind of information

thanks

handy feature to prevent queries that return too many rows.

I thought that was the –i-am-a-dummy option

Use a JOIN and refer to the parent table in the ON clause or the WHERE clause, depending on which is more appropriate.

same thing, less insulting name.

'', aID, (SELECT name FROM people WHERE id=bID) FROM reservations

last time I suggested the –i-am-a-dummy option, the user thought I was making fun of him and got insulted.

When is specifying the join criteria in the WHERE more appropriate?
That's the best part!

so there's no way to reference bID? AS doesn't work either

"more appropriate" meant, depending whether this is join criteria or filtering criteria.

That happened once when I was recommending "gone away". He thought I was brushing him off.
Oh

Does bid exist in the reservations table?
and the people table?

i need some info plz

yes

select cod_dist from distritos where distrito = "Aveiro"
select concelho from concelhos where cod_dist = result from 1st sql command here

If so, WHERE id=reservations.bID)

how can i do this ?
simple way ?

oh that's so obvious, thanks

You're welcome.

anyone ?

!man subqueries

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

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

or a plain join

the reason it returned that many is because i read a mysql doc wrong on joining, because it wasnt worded right

when using a unique key and there is a duplicate, can it skip that record and keep going?

i managed to join a table to its self, and return every row, for each row

Impossible. All MySQL docs are perfect in every respect.
INSERT IGNORE…

or ON DUPLICATE KEY …

sweet

Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111)
getting this error ?

is this Ok or is there a more optimized way? SELECT * FROM tbl WHERE name IN ('foo', 'bar', …);

++

fritz[]: Given no other information, that's fine.
fritz[]: Having said that, it sometimes doesn't take much digging to uncover a mis-, improper, poor use, even in such a simple example.

how do I set collation_connection from the CLI?
I am having hidous function / collation collions
collisions

i want to install mysql-5.0.45 can i install rhel version for centos 4.5????
bcoz there is no mysql5.0.45 for centos

Will these work in .my.cnf ? "SET NAMES 'charset_name'" and "SET CHARACTER SET charset_name;"

Hello. Mysql doesn't work. Help? I'm trying to migrate to a different server. And any time there's non-ascii data, I get garbage.

And there is one for RHEL?

there are custom rpms for 5.0.45 i have installed on rhel4
one sec
http://remi.collet.free.fr/rpms/el4.i386/
a href="http://remi.collet.free.fr/rpms/el4.i386/"http://remi.collet.free.fr/rpms/el4.i386//a

Any idea what character set I should use to be able to see UK pound signs?

was the only way i could get a mysql server = 5.0.22 and keep my old dbs and their deps

I guess rhel is ok on centos

why 5.0.45 ? whats wrong with 5.0.lowerthan45 if you're not taking an 5.1?

yah no doubt

i want to install mysql-5.0.45 at centos4.5 there is no specific version of mysql for centos at mysql.com … will u pls guide which will compatible for centos4.5 ?

what happens when you try "rpm -i rhel-version"

Well, the point of using something like CentOS is that all the versions stay consistant…

i installed mysql-5.0.45 dynamically liked generic version … but its deamon does't start ..

If it isn't in centosplus (or whatever that repo was called) then I wouldn't install it. Doesn't CentOS5/RHEL5 have MySQL 5.0.x?

so i remove it

I swear it's +m in here…

is 'set character set latin1;' at the cli the same as "character-set=latin1" in .my.cnf?
because "SHOW VARIABLES LIKE '%coll%'" says it aint… which bakes the question, what is?
because "SHOW VARIABLES LIKE '%coll%'" says it aint… which bakes the question, what is?

I think you mean "begs the question"

oh…
I am baking

Why the hell am I getting garbage data?! The character sets are the same, the collation is the same…
It's STUPID!

when I login and issue "set character set latin1;", then "SHOW VARIABLES LIKE '%coll%';" gives me what I want. But I want this to happen every time by default.

It's STUPID!
IT DOES NOT DO WHAT I WANT IT TO DO

the weird thing is that "character-set=latin1" *is* a valid option in .my.cnf

so whats the proper way to repair an innodb table? dump it, drop it, and reimport?

Well, when I copy the raw data files from one box to another and get different results, I think it's pretty bloody stupid.

How do you know it's corrupted?

if you are getting really bad errors that is the best way.

Yeah, except dumping introduces crap data… _

but you may have to drop *every* innodb table!

create table student(name varchar(10));

No database selected
how to select database ?

use the force luke

can anyone answer why "character-set=latin1" in .my.cnf is different from "set character set latin1;" at the cli?
please read a book

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

MNSFDGJDFGHWERQG£!%£5~!£Y5~VG! 5~8~

how can …

same. character sets: same. data files: same. select results? WRONG! ASKDFJDFG @H5~Q%%%%%%%%%%%%%%%%Y~ASD

I don't think you're using the correct irc character set. Everything you type is coming out as black blocks.

everyone on the machine or everyone in the database?

I'm beating up my keyboard.

depends on how you set up innodb

Or do you mean /everything/ ?
How's that

Xgc faceface ok ok but in oracle its different you need not say use database

when ever using a reports db where all the tables are innodb, mysql will crash. I did a check on everything and it says its ok. innodb_table_monitor looks fine.

In Oracle '' IS NULL. I'm not sure this is really significant to MySQL.

its only when the report tries to make a new table

We don't use sqlplus here.

it makes the .frm file but nothing else

so sqlplus is different is it ?

Very.

guys, I'm doing a round robin with two read servers but getting huge amount of stat NULL
any idea?

collation_connection | latin1_swedish_ci
I want "latin1_general_cs" 'by default'
that is what I get when I issue a "SET CHARACTER SET latin1" from the CLI

hello folks

Dont forget the bots!

curse you bot!

what was the easiest method of selecting 10 highest results from a column?

sort by limit 10;

ASC

faceface thank you, trying
oh that too
DESC or ASC
let me try

ASC LIMIT 10; for the 10 highest results

how do I select all things that are not 2,4, or 6.

WHERE field NOT IN (2,4,6);
:s/field/value

cool

whatever you get the picture. sorry, just woke up.

dan__t hm
i just tried that with select and it didn't work

what's the query

can't never rememeber the damn syntax.. will start a diary soon

how can can I get the collation and character set via mysql client of an database in mysql 4.0.24 "SHOW CHARACTER SET" doesn't work ;/

oh boy… character sets and functions … what a pain in the ass!
show variables like …

dan__t select name,time from table sort by time asc limit 10?
why wouldn't this one work?

Sort by?

order by

err

sort by limit 10;

what's the syntax exactlly

i tried to follow that

Try ORDER BY

thank you xgc,seekwill - that worked!

robboplus, That was to you.

my server cs / col == my client cs / col == my connectin cs / col … I believe … I believe my table has the right cs / col and I create a function from the table and a table from the function … then I try to join the two tables and and … implicit col conflict

yes xgc
finnaly got it SORTED BY

Are you using phpMA?

thank you folks, now i will write it down before i forget the damn thing again

so long… thanks for help people
its time for me to go home

btw is it possible to recalculate a result on the fly? like when i'm getting result in seconds and i would like it converted to hours or days/hours/minutes even?
or is that too dumb to ask at all?

Calling Oracle a ho now?

e.g. 557792 seconds isn't really telling much

How many seconds are there in an hour?

seekwill 3600

557792/3600

Xgc just oracle

seekwill yes but is it possible to make mysql count this for me?
and output results in hours?

yes..

that's what i mean

SELECT (557792/3600);

just put that in there

!m robboplus date and time fun

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

seekwill can i substitue the raw value with column name?

Yes

seekwill thank you so much - checking
just had NO idea it was possible lol

Anything is possible

he he
oh my…
that works!
lol nice

Maybe I already know what that is like

OMG REALLY?

yesyes

Therion:

with CASE, is there a way to do something like WHEN (a or b) THEN result ?

seekwill… how about precision?

We don't do precise things here

seekwill if i get 155.0089, i would like only TWO after .

It's all relative here

lol
ok is that rounding up then? my english is really poor..
i'm trying though

Two after?

Every database supports a different (declarative) SQL-like language. There's a standard that no one fully supports and everyone has extensions to it. Almost every database also supports a procedural langauge, which incorporates basic SQL in the language.

i mean 155.00 instead of 155.0089
the default number of digits after dot is 4, how can i make it two?
uthe default number of digits after dot is 4, how can i make it two?/u

Both the declarative and procedural languages each database supports is different than any other database. That's just the nature of the business.

ROUND() or TRUNCATE()

ok

hello everybody
is there a way to get the structure of a table via a SQL statement in MySQL?

hi, i'm attempting query a nested set table by depth, but it is complicated by the fact that many trees are stored in 1 table and i'm having trouble making my query return correct results

like mysqldump

!m lupino3 show create table

lupino3 see http://dev.mysql.com/doc/refman/5.0/en/show-create-table.html

been reading this information http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

snoyes thank you! checking

mysqldump -d

thanks a lot!

snoyes Xgc bye meet you later

i was able to make the earlier queries work like showing depth by a root node
by adding a few self joins
but it doesn't seem to be as easy to modify to get the depth by subtree

bye, thanks again

Depth in subtree = depth in full tree - depth of root of subtree in full tree.

sure
http://pastie.textmate.org/82827
that's the table + data + query
oops.. forgot pastie doesnt' wrap

I'm serious. If you know the depth of any node in the full tree and you know the target node and the root node of the subtree, simple subtraction is the answer.

that's what is happening, the problem is the result set seems to include other subtrees
or rather data from other subtrees

Quick ?: How do I use the ego (\G) command? Do I write the query first and end it with ego or what?

so the count is off

SELECT * FROM table\G

I'll try that. Thanks.

i've tried various combinations of moving the self joins and ANDing the tree root node attributes
i've been able to get the full tree, leaf nodes, path, total depth

It'll probably look messy, but the idea is simple. I don't have time to dive into this and I don't think you've fully described your intent. What subtree are you talking about? Maybe that's part of your problem.

the problem starting in trying to get part of a tree by depth

rafb.net/paste might work better.

it currently does that in the application code

Oh. Sorry. Ignore that comment.

but it is causing heavy performance problems on sites with large numbers of comments

does CASE not work inside of CONCAT() ?

as in, if you were to set the render depth to say 3, it would grab ALL the comments and then filter out by depth in the application code
or rather grab all the comments for a specific object

It should. Yes.

hello people, how can i create an array and use it inside a stored procedure?

hey I have a question, how do I copy a single mysql table entry to another table?

no array types in MYSQL.

That's what I figured. but can you find what's wrong here? http://rafb.net/p/S2UR2J83.html

!m NeosVortex insert select

NeosVortex see http://dev.mysql.com/doc/refman/5.0/en/insert-select.html

thank you snoyes

Hi to all

I understand that looks useless right now, but I'm just trying to get the output i want for an UPDATE to a table.

I've a problem with a query

basically doing some randomization of some data.
but anyway, mysql is claiming syntax error
I tried adding parenthesis in places, no luck

Pasting the query eliminates 99% of guessing

and it works fine without CONCAT
seekwill, talking to me?

Making a general statement

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

is there a way to get mysql to begin an auto_increment at a certain number?

These are common problems in databases that don't directly support recursive constructs.

Xgc, yes.. read that many times

fedx_, yes. AUTO_INCREMENT=x at the end of the create table statement.
where x is the number you want

in many articles that refer such data data structures

nice. thanks

Xgc, i went through that article i pasted a link to earlier, and it works fine within its simple table
and in fact, it seems to work fine when there are no other trees in the db

what does it mean when all queries states is NULL in show processlist ?

i was able to solve that in most cases with the aforementioned self joins on modid,itemtype,objectid

If you're around later (after work), I'll have time to look at this.

when is that?

4 or 5 hours.

Xgc, i just want to make sure you know that i've quite a few other things before coming in here
tried*

It's obvious you've spent time on this.

i've worked support chat.. so i know how it is
when it is paid for.. you don't expect pre research.. when not tho.. that's when you get pissed

hey everyone, JOIN question here

hi, i was wondering if it is possible to give access to an entire class of ip on mysql ? like 10.10.0.0 to grant access to 10.10.0.1-255

and your post is almost perfect. I can probably figure out what you expected, but a description of the expected output would be helpful. I see the actual output.

into a specific user

10.0.10.%

i'm writing a script to organise a games tournament. i have a table of teams (id | name) and a table of matches (team1_id | team2_id) - how can i write a JOIN statement to get the names for BOTH teams?

snoyes thank you gona test it

What's the best bet version for a new production server? 5.0.41 community?
er, 5.0.45

SELECT team1.name, team2.name FROM matches JOIN teams AS team1 ON matches.team1_id = team1.id JOIN teams AS team2 ON matches.team2_id = team2.id

thank you very much, i'll give that a try now

select software.machine,count(software.machine),count(sw.machine) from software,sw where software.machine = sw.machine group by software.machine; it just sits there and gives no results.

is there a pastebin for this channel?

pastebin

3 children and each of those having 2 children, and choose depth 1, then i want the 3 children

try http://pastebin.ca or http://pastebin.mysql-es.org

err depth 0 that is..

http://pastebin.ca/636103

select machine,count(software) from software group by machine; works and select machine,count(software) from sw group by machine works

How can I do something like that?

worked great, thank you again.

delete quotes2 from quotes2 join quotes on…

that doesn't even look like valid syntax …

try it and see.

delete from quotes2 join quotes on quotes2.quote = quotes.quote; ?

no.
!man delete syntax

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

what is wrong with this? select sw.machine,count(sw.software) as sw,count(software.software) as software from sw,software group by sw.machine;

There's no join condition. You get cross join/cartesian product. Lots'o'rows.

So node.xar_cid = 1 is the chosen root node of your search tree and HAVING depth = 1 indicates that you want the root node and 1 level below?
I just want to be sure I understand your exact conditions.

correct and for every + 1 i want 1 more level
and if depth is ommitted, then i want the whole thing

Right, from the chosen root.

the pid field will be useless if this works out

the manual states that when you use FLUSH TABLES WITH READ LOCK at a global level, it is not released until an explicit UNLOCK TABLES is sent
does this mean I do not need to keep a session open to keep the locks in place

Yes. left and right are meant to replace that.

so i load the article, get the node marked as root for that objectid, and then use it

i.e., I can pipe the command to mysql at the beginning of my script and end?

!man insert select syntax

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

!m NeosVortex insert select

NeosVortex see http://dev.mysql.com/doc/refman/5.0/en/insert-select.html

i.e. echo "FLUSH…" | mysql …; do stuff; echo "UNLOCK TABLES"

Xgc, the current approach is hybrid, and the maintainer quit

tree per object

guess not

^chewie: For sh or bash; (printf "blah;\n"; cat file.sql; printf "end;\n") | mysql -options dbname

how to run the phpmysql

If I have a 'reservation' that contains 'byID' and 'forID', both which point to an ID in the 'person' table, what's the best way, in SQL (not in my code) when doing SELECT * FROM reservation and I need to join against 'person' and return p.Name when byID's join returns a value, and if not,
forID's .Name ?
in otherwords I want a conditional join, I can't choose the data in my application, I have to choose it within the SQL

Just return them both, with two joins against the person table.

like I said, the app needs 'name', I can't alter that
so if forID would return '', then I want the value that could come from byID

I am trying to use group by and order by but it would seem 1 is cancelling the other — any idea's?

rhelik, use the if function

i still haven't quite grasped the concept of the insert select, if I have a table named request and a second table named backup how do I copy the entire entry where the rid = 5?

Your description isn't complete. Include your logic for using byID or forID conditionally. If this is the result of two outer joins, use COALESCE() to pick the non-null id and join that with the person table.

SELECT c.*, d.* FROM `columns` c, `columns_data` d GROUP by d.cid ORDER by d.id DESC

'', (SELECT p.name FROM person AS p WHERE p.ID=forID), (SELECT p.name FROM person AS p WHERE p.ID=byID)) FROM reservation

the d.id DESC does not work ?

rhelik, do you know of table aliases?

forID and byID will ALWAYS have value in the reservation table, but the 'person' entry will sometimes have an empty 'name'
person AS p

If you have two ids, one of which is non-null, COALESCE(id1, id2) will return the non-null id.
Use that in your join with person.

well COALESCE treat '' as null?
will

see seekwill

No.

I'm pretty sure the app inserts ''

Then use CASE / WHEN.

i still haven't quite grasped the concept of the insert select, if I have a table named request and a second table named backup how do I copy a single entry where the rid = 5?

or IF(). Either is fine.

how do i find the ip address of a database?

well do you know the hostname?

yeah, localhost

hah

and the port

where do you want the IP address, in your code, or within a SQL query?

databases don't have IP addresses, database servers do

cute but you know what he meant

but i don't think he did
thus why i said something

i am writing a facebook application, and they ask for it
i will just give them the ip address of my site

yea, it will be the same

I'm assuming its not possible to insert into multiple tables all at once is it?

no
Could try a trigger

I need help with a forum type query…anyone?

if it's possible to insert into a VIEW, you could

seekwill, thanks actually its just for the final transfer of data from the legacy system

but why not just do mulitple inserts

rhelik, actually that's what Im gonna be doing I just wanted to cheat cause its a lot of records

heh final, usually data transfer is at an early stage

and yes I did think about using views also
archivist, unfortunately we have to run the legacy and the new at the same time

then messy scripts ftw

so ive created it a module at a time..that data which the new system isnt ready to create yet..we bring over each night

even if you could, it wouldn't be faster

but this next step will be the last time I have to do it

i have a unique col set to a default value which is obviously not unique… i can't do an auto_increment because it;s not a primary key.. is there any way around this?

rhelik, ah I assumed it would be

nah, if it supported it, it would probably just be a loop of INSERTs
but it wasn't a bad thing to consider, ideally if MySQL supported it, it's because they could do it faster than using 2 or 3 INSERTS by you

well i have been up for a couple days
so my thinking is …thinning
by its not all bad..my belly is getting fatter!

well then backup before you mass insert

rhelik, no need..cause the only data I import is the data they dont change…the stuff they are alrady doing with the new system was deprecated from the legacy prior
just one mod at a ttime…butnow a bunch of modules are ready

how would I eliminate duplicates from a table?

very carefully, I would try looking into a sub select (DELETE actually)

delete dupes

If you have a unique ID and the name may contain duplicates then DELETE t1 FROM table1 t1 JOIN table1 t2 ON t1.idt2.id AND t1.name=t2.name if you have other fields that need to be taken into consideration extend the join as needed

perfect. thanks

ya know that dude always says the right thing..its like he's memorized it

That's the proper behavior. You need to specify the value for each insert. Using yet another auto_increment field isn't really appropriate, even if allowed. Maybe you've chosen an improper primary key.

dude? it's called the_WENCH.

dude is a generic word like she is a generic word
"when god created man
she was only kidding"
stuff like that.

dude is not a pronoun

"Dude" generally refers to a male…

she remembers it in a mysql database

insert if not exists into tbl?

insert ignore
and have a unique key on the thing that exists

and if it exists, will insert_id() give me it's id?

anybody here use the mozilla calendar add on (Lightning) for Thunderbird, man I got beta v.7 and it's really sweet

hey guys anyone know how to show the code used to create a table?

!man show create table

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

you mean you have a table and you want the SQL to (re)create it?

yes

SHOW CREATE TABLE tablename I think

thanks guys

don't forget to thank the bots!

fuck the bots
oh sh.t, I didn't even see that
beat me to it

lol
oo is that against the rules here?
or is that php

has anyone ever tried doing a Mysql+DRDB+HA with replication?

DRDB?
Not sure if replication is considered HA…

has anyone ever tried doing a Mysql+DRDB+Heartbeat with replication?

What's DRDB?

Distributed Replicated Block Device

Have you looked into Cluster/NDB?

yes.. but its till using memory… im waiting for 5.1

can anyone give me some help on why this isn't working? I'm trying to get data from 4 different tables, they all have a forigen key in commmon and i'm trying to get all the data that links the tables together through the foreign key, here's the code used to create and my select statement:
http://rafb.net/p/Qbsr3w78.html

lokieee, check your data

I did and I get lost seeing how they are all communicating with eachother
thats why I thought this might make the connection clear
i think its the connections table that is screwing me up….the referencing of the entityId key I don't understand, zEndpoint or aEndpoint both reference the entityId in entity, thats why i used "or" so if either match

SELECT c.ID,c.name,s.step FROM f3s_cats c INNER JOIN f3s_cat_struct s ON c.ID=s.slave ORDER BY s.slave ???

If any tables may not contain matches, you'll need to use an outer join.

how do i take the average of x number of columns from a particular row

It uses "filesort" and "temporary"

SettlerX, indexing probably

thanks Xgc i'll read up on outter joins

cat_struct table has only 3 fields with "int" values so i don't know if making indexes in this table has any sense
however in "cats" table i have primary key on ID field

Otherwise, the inner join you used will only show results where all joins are successful (they all find matches).

What do you think? 2 queries or one JOIN will be faster?
or subquery? but how?
i have to get "step" from cat_struct and ID, name from cats.

Also, are you sure you have entries where connects.aEndEntityId = connects.zEndEntityId

2 selects is faster than join ?

actually no that will never happen

I have a feeling you're missing a join against entity.
Ah. Then your SQL can never return results.
You need to join entity twice, once for aEnd and once for zEnd
The same entity will never match them both.

yesterday, a 250,000 row update across 4 tables took over 10 hours.. i cancelled it, made a primary key out of the column i hunt for in my UPDATE, and it took less than 2 minutes.

-or- use an OR expression.
With proper ()'s

ahh oaky thanks!

It's always the last place you look.

if I change listen port in mysql coniguration, does it need a complete restart or a reload will do?
configuration*

Probably a restart. It won't hurt to try a SIGHUP.

if you change anything in the configuration you need to restart
the my.cnf

ahh ok..

Not according to documentation.

how do i take an average of, say, colA, colB, colC in one particular row?

according to the source sighup calls reload_acl_and_cache()

That's all? ouch.

it will flush logs, privs and tables
and query cache

how do i say not like ?

not like.

that isn't working.

what is SysDate-(1/24) exactly (ORACLE) and how do I make the mysql version?

where like … or like … or like … and not like …

!man operator precedence

see http://dev.mysql.com/doc/refman/5.0/en/operator-precedence.html

hmm I am unable to compile mysql version higher than 5.0.41, everything fails with "make[1]: *** No rule to make target `@MAINTAINER_MODE_TRUE@', needed by `Makefile'. Stop."

is there a way i can limit the amount of records somthing prints out?

there is a limit clause for select statements in mysql
(also for other statements, but they don't print out a lot of stuff)

ahh thanks i see it now

can anyone point me to some good join tutorials?

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

SELECT NOW() - INTERVAL 1 HOUR;

the_wench, thx

so? 2 queries or 1 join?

hmm I think I'll file this compile thing as bug report

Hi! I was wondering if InnoDB offers anything like MyISAM full-text?

lucene

Searching through a lot of text? Want to do it like someone who knows what they're doing? Use an independent indexing/searching engine like Lucene. http://lucene.apache.org/

you can use external fulltext search engines, sphinx for example

hmmm, is it complicated to implement?

more effort than myisam fulltext, but better results (faster)

if i'm not searching through a lot of text?
just varchar columns?

how do i take an average of, say, colA, colB, colC in one particular row?

SELECT (colA+colB+colC) / 3 AS avg [..]

well, what if one of the columns is NULL, then i want it / 2

ok that makes the thing more complicated

Can anyone give me a suggestion for retrieving a list of the most recent entry for each user in a log table… if I had an ID field and a date field, I want to show the most recent item for each ID

ah

groupwise max

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

if i'm not searching through a lot of text?
just varchar columns?

Just use LIKE?

slow.

for simple varchar columns?

yes!

Is it good practice to always use ASC/DESC together with ORDER BY?

you can't use an index with like if you have wildcards on both sides.

It seems really bad to leave it out.

sphinx looks mighty complicated to implement

well nothing is free…
either you get slow performance or you need to invest some time
so if you need 4 hours to build it, it might make more sense to wait some seconds for your data everytime

Or you can get MySQL Enterprise (R) today with a 25% discount!
Is it good practice to always use ASC/DESC together with ORDER BY? It seems really bad to leave it out.

Teh default is well defined by the standard. Leaving it out for ASC is common / acceptable.

Hrm.
I don't like it one bit.

It's guaranteed by the standard and all databases (that matter) recognize it.
You don't need to use the behavior.

I mean it makes the code hard to read.
BTW… ASC/DESC seem reverse in Windows…

Not if you know the behavior. But that's really your choice.

With the arrow pointing down, I would expect A-Z, but instead it's Z-A.

Can anyone give me a suggestion for retrieving a list of the most recent entry for each user in a log table… if I had an ID field and a date field, I want to show the most recent item for each ID… I just can't think of a _good_ way of doing it

already did
groupwise max

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

Sorry, my client timed out
I couldn't tell if my Q even went through - sorry

excuses excuses

'A' 'Z'. That's why DESC (top to bottom) would be down arrow.

=Z
Makes no sense…

It's an english term.

Descending (DESC), arrow pointing down, should show A, B, C… Z.

lol

Like you're diving into a hole with Z in the bottom.

Do you think of going down when traversing from A to Z?

And A is the beginning point.

Do you think of going down when traversing from 1 to 100?

100, 99, 98…
Hmm… I guess I see your point there.

Correct.

But it looks strange when we have letters…

Would it help to imagine that you're just using base 36, and don't happen to have any values 9 or less?

Yes. It makes sense to me now. I think.
But GRAPHICALLY, it looks wrong.
Visually.

They're just numbers in disguise.
That was to you, Mr. 96.

# You're the numbers in disguise… oh, yes, you are… #

No rule to make target `client.o', needed by `mysqld'. Stop.
funny again

I discovered the first (verified) bug that affected me yesterday.
And not only that, but I upgraded without problems.
Also for the first time.

I hate it.

What are you doing?

compiling mysql
well sort of
not really compiling as there are always errors

Freshly or updating?

freshly

What OS?

Linux

What distro?

Debian etch

Through "ports"/packages or sources?

with source deb from unstable distribution as I want most recent version

Oh… why is that?
I never use anything but super stable myself.
Of any software.

and some special compile flags as there is many stuff in it I don't need

Doesn't sounds like your error has anything to do with your flavor of Linux.

I guess something is wrong in configure

Can I ask on what you people use unstable?

I won't ever use unstable on any productive system
one upgrade and everything is broken
funny thing is, I didn't use the –without-client.o flag…

if i want to take the average among 4 columns in a particular row, am i better off using a select 4 values and reading them in with my PERL DBI and doing the averaging in perl?

Testing. I've actually used Alpha in production, but there was a good reason for that. That was back when 4.1 was alpha and 4.0 and prior was really unusable from a database perspective, unless you NEVER had use for more than the most trivial selects.

The kind of problem you can't resolve via google

Snice 99% (or more) of the typical developers didn't know what a derived table was, it's understandable that people would be happy with 4.0 or less.

I have a field that's of the type 'text'

You will be better off learning about normalization.

hmm why the hell does it use g++ for co9mpiling

But I cant seem to get all of my data into it/out of it
It might have something to do with the newline characters in the text, but I cant be certain

hmm and there is indeed no rule to create client.o

what is the relationship xgc

damn it, whatever I do, it doesn't compile

Then no mattter how many samples you have, SELECT tbl.id, COALESCE(AVG(measure),0) FROM tbl LEFT JOIN measurement ON tbl.id=measurement.cat_id GROUP BY tbl.cat; — will always be right.
Sorry. That should be AVG(measurement.measure) to be clear

ah ty i will look into that. yes, i do expect to have changing number of columns in the future as well, so i woudl definitely profit from that

Using that form, your limitation of 2 or 3 or 4 values per row vanishes and you can now treat them as groups / aggregates the way you should.

thats quite nice

So
Am I using the right column type

Doing things like AVG(), MIN(), MAX() or COUNT() are now trivial.

And I dont understand why I'm having a hard time getting data into/out of it

DAY etc.?!?!?!?!???!!??!!?!?!!!!?!?!??!

?

XFS vs EXT3 - any thoughts?

file a feature request
XFS seems to perform better for most operations, but in mysql sense I think there isn't a large difficulty as you mostly read from large files
from a small amount of large files

I have a field that's of the type 'text', but I cant seem to get all of my data into it/out of it
Can anyone help?

Wow… it must suck to develop on MySQL when you have to document every little change you make.

is it possible to use a spit() type function in mysql on the query line?
ie, select * from blah wehre field=split('user.shit', '.');
so it splits on the period

no
p–: so if the filed is user.shit it should do field='user' OR field='shit'?

I have a field that's of the type 'text', but I cant seem to get all of my data into it/out of it, I get a truncation warning when I select from it, and I cant seem to use the mysql client to see it all.

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

aha

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

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

ya, its worth a try

yes i think it is

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

and what is that?

i think you want ENT_QUOTES

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

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

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

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

ok

htmlentities($row[laksjflasdf

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

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

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

are you sure your query returns a result?

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

hi

yes i am pretty sure

niuq, then I have no clue

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

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

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

how to select ten words before it ?

i dunno im scared

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

snoyes, thank you, I got the function working.

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

codgio is apparantly a numeric field, not a string fiel

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

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

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

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

roaet, pastebin.ca

What does EXPLAIN says

explain says?

hehe.

!man explain

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

what's a "dirty block" ?

!beer

ok. i know. but why return 1 row?

Context?

!wench beer salle

Here salle have a cool beer

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

hard drive section

Thanks bot! Good bot! Have a cookie!

actually a memory section

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

roaet, no index use at all

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

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

jormenz__, nothing wrong with count(*)

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

hehe use a proper db

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

mysqldump –your-favorite-options

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

thx

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

Thought so

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

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

mysqldump –help | less

roaet, and excessive use of subqueries rewrite as a join

and read. There are lot of useful options

yeah, I'm reading

I'm not very skilled with joins.

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

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

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

Yeah, I guess that'd make sense

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

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

Yes you do, and I am cheaper than Bagoor.

hehe

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

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

I can change it easily, but thanks for the offer

Nah, I'm Canadian.

so enjoy

Thanks

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

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

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

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

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

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

Another tricky thing is progress bar on impot.

Does SQLite do triggers?

yes

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

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

make a guess

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

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

Nah, that would be too easy.

That'd work

Sure, if you like it easy.

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

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

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

normalize

?

normalization

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

There we go, read those.

is that to me?

Thanks.

no.. ok

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

biased

ha!

It ain't braggin if it's true.

good song.

That's also a song?

yeah..
Dan Bern - Tiger Woods

!m roaet rew

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

ah thank you.

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

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

or if its better to just have large tables

Oh yeah, I remember that song!

Ah, got it tarr.

!m roaet how mysql uses i

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

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

hi

hello

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

last_insert_id

what does that do

guess

give last inserted id?

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

.NET and SQLite?

yes

tibyke not what I need

Zee1ot, thats what you asked for, dont fool around

Works v. well so far

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

hehe borked language as well as db

yeah
http://sqlite.phxsoftware.com/

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

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

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

Zee1ot, so select it, or generally ask sensible questions

without running another query

Why can't text fields have a default value?

I prefer Ruby for personal

"for personal"

Well, personal projects.

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

Things that i'm not selling to people

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

Zee1ot, you have already been given the answer

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

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

Ask nicer.

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

bah

Talking to me?

Are you talkin to me?

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

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

No, strings are not allowed in INTs

Ask yourself, is an empty string an integer?

Saberu, er a bit silly

ok i'll use 0

Or NULL

Why can't text fields have a default value?

They can.

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

Then you're doing it wrong.

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

What does the column store?

A csv list

Why?

Things in a players inventory and what they have equipped.

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

gee. that's what tables are for.

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

Read it again for the current column in question.

Hexxeh, use table for tabular data

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

No.
Read the article.

MySQL

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

this chan is damn funny this friday

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

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

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

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

normalization is god

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

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

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

Good, keep going.

Separate your data into as many tables are possible
*as

and sprinkle some indexes among the tables that it is good

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

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

no slow serializing

*table

One further Hexxeh, use group_concat()

Does it for me?

Yes it does.
And verily, it kicketh ass.

How would I use that exactly?

!man group_concat

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

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

ORDER BY datecol DESC LIMIT 1

^– easy peasy

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

thanks

Replace 52 with the var for my charid, obviously.

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

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

Yes.

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

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

are there any alternative to stored procedures in mysql 4.1?

hm
the alternative is to not use stored procedures

Client-side code.

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

False.

can i do that with just one query?

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

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

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

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

what's the difference between having and where?

Any suggestions on a dual dual-core setup?

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

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

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

how to change mysql user password?

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

A function to return the date from a datetime value
?

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

threnody - So what I have should work then

no. update table set column…

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

lol
why would you do that?

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

works with concat(field1," ",field2)

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

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

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

no

Value then ?

"SELECT * FROM table LIMIT 100, 150"

nor is value

ok, god
good*
So what I have sould work

what's with the curly braces?
{}

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

begone, demon!

What's with the .NET hating.

and sqllite threnody

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

gosh it must be great then.

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

an abacus works

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

beats me. ask on #vbnet

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

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

this is a place for mysql not sqllite

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

Hello.

And there's no good sqlite channel

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

kamoricks - So put ` ` around Value I guess

How do I set mysql to strict mode?

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

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

!man adding new user

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

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

Got it. Thx guys

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

why does it have to be web based?

phpmyadmin.

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

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

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

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

!gui
!man gui

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

gui

Is it the theme?

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

The UI is horrible

lol tada

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

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

http://sourceforge.net/projects/perlmyadmin

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

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

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

I'm using that right now

you wouldn't

But it's not spot on, either

although i guess you could do a subselect

nothing like ON DUPLICATE KEY for that?
:-\

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

no not for that

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

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

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

But you love the docs for it?

The documentation browsing is really neat, yes.

Aah, all good then.

No.

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

SHOW PROCESSLIST;

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

Aah, perhaps not all good then

How do I do a trigger on table deleted?

I don't so SQLite, sorry.

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

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

whole table
Drop TABLE

I don't recall seeing a trigger on that.

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

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

doubtful.
Thanks anyhow

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

What happened to corrupt it?

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

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

What were those changes?

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

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

Are there any tools to do incremental backup for mysql?

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

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

like A/B/C/D/E

as in storing the difference in data between last snapshots

You could.

thanks guys

don't forget to thank the bots!

cya later

All DB types have a .frm

would that be the best option?

my error.

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

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

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

Only one flag per thing?

yes.

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

but joins are very costly right?
on memory?

Everything has its cost.

but aren't JOINS very costly? or no?

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

well I haven't added the table yet.

But someday you will want to modify it.

hi

why's that mhillyer ?

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

threnody, yea, whats wrong with it being innodb?

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

I did.

Eat more.

I was mistaken in my belief. Please disregard.

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

I did.

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

anyone know?

Then you're doing it wrong.

It finished..

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

:

hi FordPref

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

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

how do I do that?

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

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

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

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

oh

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

you sure there's no way? :

mhillyer, what was your search string?

Right now i'm using Ruby and VB.NEt

there's about 100 of them

Well, you know what I mean

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

all I need is the same prefix on all of them

MySQL Error 1033

vb for desktop apps, php for web?

Yup

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

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

Sounds fine to me.

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

Would depend on what you are designing.

Some tables.

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

All my data?

Whatever your designing for.

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

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

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

yes

Did you change versions in the process?

mhillyer, no

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

seems like corruption =X

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

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

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

everything
i had a mysqldump

Hello

Well the dump should restore fine.

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

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

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

PovAddict - yeah… do you know how ?

not really

I'd wager you regenerate them from backups.

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

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

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

What if I don't really know the structure

you're absolutely screwed, I guess

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

Pov - awesome

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

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

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

Did that work?

trying it right now… not sure yet

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

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

Yes.

spiderman, use a fulltext index

Yes.

thank you i try that one

good

mhillyer, didn't work =(

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

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

mhillyer, thanks anyways

i need help

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

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

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

ah right thanx

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

like… show duplicates?

Yeah, like show duplicates

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

Thanks PovAddict, I'll have a look

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

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

Say the MySQL server is dedicated to the task

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

1 GROUP BY title;

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

r2.UserID AND `Status` = 1)

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

I appreciate your help, but that gives a syntax error

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

mysql 4.1.20

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

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

Thank you

well I pulled it out of… guess where
all untested

you rock

1 GROUP BY title;

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

Also error

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

No problem, thanks

erm… you're doing boolean logic on strings…

mm .. yes.

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

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

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

1 order by wordid desc; works

:o

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

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

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

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

uh . i understood …

you want to return something else if title is null?

yes

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

thanks

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

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

not having innodb compiled into mysql/installed/configured?

is innodb enabled

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

change them back…?

in my.cnf

archivist, i mean, whats the directive?

–skip-innodb

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

show variables; see whats enabled

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

is this sqllite?

mysql

saw a commandline override the other day in a startup script

archivist++
do 'show innodb status;'

hmm…. that is saying skip-innodb is enabled

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

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

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

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

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

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

what is the purpose of 'where 1'?

to return everything?

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

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

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

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

operating system has its caches too.

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

no… just regular ol mysql

out of curiosity, why would you disable caching?

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

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

Why does the order of the id's matter?

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

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

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

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

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

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

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

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

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

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

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

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

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

temp tables can be big too can't they?

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

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

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

And to stop the db, I just lock it?

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

http://pastie.caboo.se/88824

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

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

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

Thanks
I'll do that now

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

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

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

interesting
makes sense

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

would cut down on seek issues i assume

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

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

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

hmm. well query_cache_size is 0

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

it doesn't sound disabled, does it?

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

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

so it's storing that index in memory.

ah

once you call it.

is there any way to preload my indexes?

yes.
!preload
!man preload

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

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

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

yeap joelmichael

well this could be a lifesaver, thanks

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

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

I'm pretty positive, yes.

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

joelmichael…I'm looking at it again

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

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

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

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

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

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

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

like this

show create table table_name;

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

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

yes, I tried that as well.

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

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

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

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

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

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

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

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

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

glad you found it

Thanks for the help.

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

Yeah, sounds like a smart idea.

actually it covers my dumb. ;^)

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

live version over teh interwebs, test and dev gigabyte?

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

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

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

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

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

gotta walk the dog. later.

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

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

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

yeah friday

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

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

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

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

The LIKE keyword ignores case by default, IRRC.

Have a subproducts table with the three dimension fields.

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

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

thanks a lot

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

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

DESC (SHOW TABLES); #perhaps ?

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

or a procedur
e

hey, long time no see, chadmaynard

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

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

Ok ill try it thanks.

mysql would be the database name

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

you are a supernoob?

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

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

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

it isnt

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

depends on element and o/s

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

database administration assumes a certain attention to detail.

new to me

well.. job done.. thanks guys

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

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

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

alas, no

ah well, long shot

C.D.?

O.D.?

not that either

damn

dwm, curiously enough

I was close

chadmaynard–

haha

heh

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

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

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

hm

vimdiff is a good choice

or diff

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

usually you need to create the user and DB yourself

depends on the CMS

How do I do that?

PHPMyAdmin is easy to use

which CMS?

the CMS' INSTALL file should explain it

Isn't phpmyadmin very unsecure?

you using Windows?

only if you set it up over HTTP

no, who says that ?

haha i love these answers

I'm sshed into debian, from an ubuntu laptop

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

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

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

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

you shouldn't, it tries to do math.

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

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

they ususally include a .sql file or something

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

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

like that?

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

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

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

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

truncate all of the tables

is that safe (for database)?

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

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

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

ok. what's the command?

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

you have to type TRUNCATE TABLE name of table

Anyone cheaper than Abebooks?

How can I list users with access to the database?

but this database has many many tables.

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

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

write a shell script

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

show grants

yea, I have all

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

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

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

yes

interesting.

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

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

the indexed dat ais the actual row

I understan clustered-indexe's

yes any additional indexes point to the clustered index

interesting.

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

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

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

show variables like 'datadir';

prompt?

yes

datadir; and there was an error.

show variables like 'datadir';

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

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

what is a tuple?

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

between

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

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

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

120×120 pixels
max

o, too late
heh

atthis size you could store them on the DB

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

but the rule of thumb is store links to the Filesystem

sid, did it mess it up badly?

thought so, just wanted to make sure

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

thanks

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

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

version?

5.0

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

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

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

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

yep

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

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

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

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

how would I make a temp table a MEMORY table.

CREATE TEMPORARY TABLE t (columns…) ENGINE=MEMORY

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

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

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

Those will be memory tables unless they are larger than tmp_table_size

ah.

Check the status variable 'created_tmp_disk_tables'

SarahS, where are you from?

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

E-bola: anything in the error log?

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

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

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

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

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

E-bola: anything interesting in SHOW SLAVE STATUS?

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

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

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

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

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

how do i get it to resync?

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

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

start slave;

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

E-bola: SHOW WARNINGS;

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

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

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

strict mode

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

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

can that be enabled for a single table?

probably not

i never used Triggers with mysql before

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

one primary key
many uniques

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

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

sweet, that did it. thanks domas

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

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

how do i set the mysql root password

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

how do i reset the mysql root password

reset root

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

mm
via phpmyadmin should be easier to do it?

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

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

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

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

both
awsome, ill go have a look

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

http://pastebin.ca/662022

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

well put Isotopp

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

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

it is not working I am getting a weird column error
any thoughts?
hello. I need to know how to do this selection insert

http://pastebin.ca/649083
why does it give me that error (in the paste)

are you there?

show us the full error message. "weird column error" doesn't tell us much

Operand should contain 1 column(s)

hey, umm.. I'm trying to make a game database.. its a simple project.. here is an example. gamedatabase.no-ip.org

INSERT INTO testingdb.tempo1 (first_name, last_name, primary_address_street, primary_address_city, primary_address_state, primary_address_postalcode, email1, email2, phone_home, phone_mobile, description) SELECT (first_name, last_name, primary_address_street, primary_address_city,
primary_address_state, primary_address_postalcode, email1, email2, phone_home, phone_mobile, lead_source_description) FROM nhmc_sugar.leads WHERE assigned_user_id LIKE '

Operand should contain 1 column(s)
wait I think I figured it out

so if someone can help me. since I'm more of a designer than coder.

please ask a specific question, don't ask us to design your database for you

ok this is my question. I have the idea, for a form submit. It will use php to add a game to a database. I dont know much of anything about coding.

just some guy. have you checked /whois ?

an example of what it will look like is gamedatabase.no-ip.org

your code is outputting PHP tags rather than executing php webhosting code, this looks like a bug

I need help still I am not able to get this to work still

For help with PHP try ##php, although even they won't tell you how to make something from scratch

INSERT INTO testingdb.tempo1 (first_name, last_name, primary_address_street, primary_address_city, primary_address_state, primary_address_postalcode, email1, email2, phone_home, phone_mobile, description) SELECT (first_name, last_name, primary_address_street, primary_address_city,
primary_address_state, primary_address_postalcode, email1, email2, phone_home, phone_mobile, lead_source_description) FROM nhmc_sugar.leads WHERE assigned_user_id LIKE '5d8efe7
Operand should contain 1 column(s)
I am still getting the same error

No () around your select list.

Try removing the brackets
Try the select on its own in the mysql client. If it doesn't work, an insert…select definitely won't

You should have them in the insert field list, just not in the select part.

ok
yes awesome it worked

What's the difference between a unique key and a unique index?

nothing. key == index

That's what I thought…

I'm not your guy. sorry. busy with other work.

I just got confused when reading the mysql hosting documentation for alter table.
ALTER TABLE blah ADD UNIQUE {INDEX|KEY}

{ | } means you have to pick one of them
compared to [ ] meaning it's optional

Yes, I realize { | } means pick one or the other and that [] is optional… that's fairly standard notation.

Not everyone knows that…

But the option of picking one or the other for some reason led me to believe that there was a difference between a key and an index in mysql.
I now realize I was just over analyzing that the syntax meant.

The syntax doesn't tell you whether the choices are synonymous or not.

I need a little bit of help.

No, it doesn't.
So I assumed they were not synonymous.

Have to read the manual for that, and sometimes it's surprising. Occasionally, it even changes between versions.

I'm trying to create a php login form, which checks a mysql database and sees if the info provided and the info from the database match, but it isn't letting me in, even with blatantly correct info.

What does "isn't letting me in" mean?

Well, the code sends you back to the login page if the info is incorrect.
And i keep getting sent back to the login page.
http://pastebin.ca/649107 - code for the login

use mysql_real_escape_string around those values from $_POST, or some unscrupulous cur will come along and hack your db.

mysql_real_escape_string? (sorry, i'm fairly new to mysql)

!php mysql_real_escape_string

string mysql_real_escape_string ( string unescaped_string [, resource link_identifier ]) Escapes special characters in a string for use in a SQL statement http://php.net/mysql_real_escape_string

Don't use the PASSWORD() function

PASSWORD() makes it more secure?

Use a different function, like sha or md5

!php sha

php.ini variable name= default= http://php.net/

o-o
Will sha or md5 work on mysql 4?

Yes
Next step, for debugging purposes, echo $sql, then copy and paste into the command line client / phpMyAdmin/SQLyog/whatever and see what results you get.

I'm still on step 1 lol.

Is there a mysql benchmarking program (like bonnie for mysql) that I can use for analysis of a mysql setup?

There are some advisory tools available when you buy Enterprise.

i suggest install collectd
installing rather

winmutt, thanks, i'll look tha tone up

it will help to some degree

where /what do i set samba user/computer so a winbox can login
man samba mentions .tdb but fails to say how to change

Guest = ok ?

heh I was ranting in wrong window

http://www.linuxheadquarters.com/howto/networking/samba.shtml

thnks

UPDATE tempo1 SET id = CONCAT('arg020708-', id);

Duplicate entry '0' for key 1

Dell memory is SOOOO expensive

I think it's made of gold

hehe

Is there any gold used in the production of memory chips?

or Michael Dell has to poop out every single one…

Might be worth keeping old memory around just to melt it down…

requires lots of industrial lubcricants
hmm don't know, but generally hardware contains some gold and other stuff
heard of a guy who got fired for taking the companies electronic trash home and extracting gold and other stuff from it

pretty toxic stuff in their, too

yes

there*

so if you have a wife that bothers you, you could try the Reiser method

as usual, the stuff is getting crapped all over the 3rd world

all with old hardware

And fun magnets in hard drives.

Any help on this error?
Call to undefined function: mysqli_connect() in /home/bavirtua/public_html/login.php on line 3

If i'm in the mysql hosting linux client and I want to clear the screen like using linux "clear" how can i do that?

Usually that means you don't have mysql enabled I believe.

control-l

Did you try ctrl-l?

Thank you!

function mysqli_connect($user, $host, $password) { return popen("/usr/bin/mysql -h $host -p$password -u $ user"); }

You just asked the same question in #postgresql and got the same answer.

has your php ever been able to connect to mysql?

It is enabled. I've used other script using mysql

In addition, I told you that ctrl-l is more of a shell convention than linux convention.

Yes.

I didn't ask the same question, I asked one for psql client and one for mysql client

You need the "mysqli" extension enabled.
It's a ##php question

My company supports both so I wanted to know, and I just tried it in the shell and yeah, it's the same as clear on the shell

Are you really buying Dell memory?

no. Kingston probably

'We live only to serve the shell.' I like that. New email sig.

I need some help I am trying to update a table and I keep getting a Duplicate entry error and when I look at the data I am seeing only one record

already an oil companies slogan

lol

SiliconG, but its already in the destination table

Is it possible to connect to the db without using mysqli?

some guy in here mentioned that there are problems with other memory manufacturers in dell hardware…

I am unfamiliar with mysqli

but well, you can buy the same ram twice and still save money

mysql_connect?

Try mysql_connect() maybe?

Not sure if I like Kingston any better. I am upgrading a friend's Dell right now actually. Just bought two 512MB sticks for $30 each. Corsair I believe.

i run kingston in it now

what kind of hardware?

Just about to

it is not in there I have looked

i have to buy the fully buffered, yada yada yada, ddr2 stuff

I want to update a column in the temp table

it's a dell pc

I just want to concat a string to the id

ok, we talked about servers back then

I just got a whole mess of errors.

"what kind of car do you drive?" - "A blue one!"

what I am trying to do is update one column in the temp table and it is not working

I don't know enough about php to know what the difference is between mysqli_connect and mysql_connect.

http://pastebin.ca/649152 — The errors i got. http://pastebin.ca/649154 — the code used.

But I suspect the folks in #php would know.

Okie dokie

Well, these are pretty obvious.

I'll try there

Access denied for …

SiliconG, duplicate means the id is already in the table

ok - I dont want to insert anything I want to update the id field I want to concat to it
there is nothing in there like that
they are already in there right - so how do I just add to the id
update right?

update to change

ok how do I do that?

!man update

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

I am a frustrated newbie
can I show you the way I got the data into the temp table

is there a signal one can send to mysqld and have it do a graceful shutdown?

update tablename set field = somevalue where field = someothervalue;
what o/s?

the socket got removed and the root privs are for the socket
debian

But if field has a unique constraint, it could possibly fail right?

try signal 15

I want to update them all;

So drop the WHERE clause.

update tablename set field = somevalue, field2 = somevalue, field3 = somevalue where field = someothervalue;

Are you sure that's what you really want though?

all fields or all rows
all fields or all rows?

ok id int AUTO_INCREMENT PRIMARY KEY - I want to add concat a string to that number
for all records in the temp table

you can't

An int can't have a string on it.

should I kill -15 the mysqld_safe process or the mysqld instance?

I'd recommend a bit of reading, perhaps the tutorial in the manual
have to kill mysqld_safe first, or it mught restart the mysqld
might*

can I just change the type to char(36) then?

trying a few other means before the kill

how do I change the type then

there was probably a good reason to choose an nt auto_increment
int*

there was not - so now I want to change it how do I do that

!man alter

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

what's the reccomended ammount of memory memory_buffer_size should be equal to in terms of a % of memory? 80%?

memory_buffer_size?
I think you just made that one up.

uhm

well, killing mysql_safe (no signal specified) seems to have cleared it up
now I wonder if the tables are corrupted or not

hrmm

!tell O10xz about tuning

O10xz http://www.mysqlperformanceblog.com/2006/09/29/what-to-tune-in-mysql-server-after-installation/

is there a web site when you put a word, it gives the word in many languages automaticaly ?

google.com

I need better harddrives

http://pastie.caboo.se/85797

Great. Banned from ##php. Thats useful.

That doesn't mean you come back here…

It's a free country. Sheesh.

Maybe where you live.

Dude, i aint even looking for help this time.

Then you're being way off topic…

You lot where aswell…
Sheesh, why does everyone hate me.

Because you're cheese.

But everyone loves cheese? (unless your lactose intolerant)

don't worry, I also got banned some time ago

What for?

nothing, they banned my whole ISP

Ooooer
I was cheeky to two admins :-[
How do i select a database?
Cos my login script is giving me a no database selected error.

*sigh*

…
Nobody knows how to select a database?

how do i generate a report(count) of record.answered and record.unanswered per number based on this setup. account has many numbers. record belongs to numbers.

group by … order by…

in one sql statement? can i generate unanswered answered busy etc?

USE dbname;

would that be in mysql_query()?

`paul, i do not know what you are talking about, but the answer is probably SUM(CASE WHEN TEHN 1 ELSE 0 END)

it's for the mysql console.

MonsterCheese, in client application, usually it means connection

No database selected.

specify a valid database name, then.

does it need the username prefix, thumbs?

I HAVE NO THUMBS

Obviously so, thumbs. ty.

I HAVE NO SEEKWILLS
the username?
start a terminal, run the mysql client, and connect to a database

MonsterCheese, how about reading the fscking manuals instead of asking dummy questions again and again/

No need for that language

no offense, just common sense

And the reason i don't read the manuals is, they're too long and half the time i don't understand em

well either way, I probably just grabbed a hot potato

MonsterCheese, thats bad attitude!

have you read the mysql manual?

GIVE ME DIRECTLINK IM HIGHPAID MANAGER I HAVE NO TIME TO USE GOOGLE
*sigh*
thumbs, he cant, his time is more valuable than ours!

oh ok then.

hello, i'm in process of moving my client's hosting accounts to another, dedicated server. One the current server database is running locally and all client's apps connect to it through localhost. In case of new server, database is on other machine, listening on some certain ip. Is there some
method to "redirect" localhost connections to that new address ?

oh for fuck sake why the fuck is everyone is the goddamn mother fucking world against me!?

MonsterCheese, if you do not understand the manual, that just means that you need to read it a few times more

well, now Im not surprised at all why you were banned from ##php
bwell, now Im not surprised at all why you were banned from ##php/b

we don't spoon feed people

MonsterCheese, you are not worth being banned

we expect people to actually read the manual and / or google a couple times
if you can't even make that effort, then why would we help you?

Oh thanks alot tibyke. I'm gunna go now and jump off a cliff.

lets get ontopic again then

MonsterCheese, do not forget to report back on how well it goes

well, you would want to replicate all the data to the new server
then configure your application to use the new IP/PORT

…

this way, you can have next to no downtime.

right, but these are my client's apps, and there's quite a few of them

well you're can't simply 'redirect' the mysql connection.

i was trying to do something with iptables but with no effect

a replication server would really be the best solution

anyone here know if the rails add_index migration will look at your current rows and add indexes to all of them?

you can do a perl -pi -e "s/localhost/[newipaddress]/" in the files, zoldar

hello, setting up backups for mysql4.1 99% myisam tables. can i consider "mysqldump –lock-tables –complete-insert …." to be consistent? (somehow i just can't trust mysqlhotcopy, especially when the manpage says "STILL BETA")

but afaik it requires special format of tables ?

I don't recall if one table format can't be replicated
perhaps someone here can confirm that

i mean innoDB/myISAM

I know what you mean.
I don't want to steer you wrong.

or maybe i mistake it with cluster

better ask the other helpers here

ok nevermind, thanks for effort

help this guy, please.

With?

zoldar, cluster needs it, but not replication
cluster need ndb, or ndbcluster

replication has no restriction on the table types, doesn't it?

right

Correct. Current replication is statement based.

thanks, that sounds promising - just one question - isn't it passive-active relation then ?

thank you.

passive-action?

can there be two peer databases syncing with each other? both being actively used ?
ok, i'll just take a look in the docs, thanks for pointer

zoldar, no direct update/insert to slave, only master

He's asking master to master

ah, sorry

so, that's possible ?

Yes

I believe the concept you are looking for is "multimaster replication"

But I don't know how to set one up properly

Auto-Increment in Multiple-Master Replication ?

me neither… I've just seen it mentioned in here before

zoldar, but you can just simply dump your old databases and restore it to the new box

looks risky
i have no problem with that, the thing is, that all client's apps have multiple, hardcoded references to database by localhost

sucks for them, they'll have to update

sucks for me then…. ok, just thought there's some relatively easy workaround

Any idea how to fix this syntax error? –http://pastebin.com/d367684ac Error message is posted here –http://pastebin.com/d731fde34

group by c.CompanyName (if it's in that table) afaik
and qualify the fields you select when using names for tables

ok. I will take a look. Thanks Ekimus.

well there is an automated way to do it if you just need to replace the hostname

filename1 && mv filename1 filename

i'll have to do it more or less that way then , thanks for help

CompanyName is in the Customers Table and Customers Table is already declared by northwind.Customer as opposed to c.Customer. I am wondering if something else isn't going on here?

all

I did try it with c.Customer and still got the same error message.

ciao
how can i change varchar fields from latin1_swedish_ci to latin1_general_ci? is there a way to do it on all fields or better all tables?
do i have to do it manually field by field?

hmh

Whats a reliable way of getting a list of databases from mysql? Db in mysql.db is not.

um, show databases;

is there a way to change the collation and charset of all tables/fields in a db?

Lumber1:

hello
i lost my mysql server, how can I extract my sql data bases from linux? what's their location ?
bi lost my mysql server, how can I extract my sql data bases from linux? what's their location ?/b

what do you mean "lost"

depends on the installation
anyway, search on your disk for files with the .myd extension
or MYD

indeed, how did you lose the server?
you!

me?

is that your *real* name?

… If i told you i'd have to kill you

hehe

ha
hmmm
stupid /nick isn't working!

my name is chadmaynard

Lumber1 … it's supposed to be Lumberg!

all imposters tonight

archy

gaim is a bad irc client

why is gaim a bad irc client?

because it wasn't designed to be one?

maybe

hmmm. i've had no problems with it. well, using pidgin, but still.

is it possible to ask mysql to set a random value of a given range for some field?

why?

robboplus, yes

gnari hm
!man random

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

!man rand

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

oh hm…

random

http://jan.kneschke.de/projects/mysql/order-by-rand/

!man rand(

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

does anyone have a procedure or a function that lists all tables and then all fields and changes their charset and collate ?

I'm trying to drop a row in my table with:
ALTER TABLE `customers` DROP `last_credit_card`;
I get this error from MySQL:
Error on rename of './quickiwds/#sql-ac9_36' to './quickiwds/customers' (errno: 150)

!perror 150

Foreign key constraint is incorrectly formed

doh
thanks chadmaynard

150

http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html

Anyone play Forza2 on Xbox Live?

how do a automate a mysql backup?

make a cronjob that runs mysqlhotcopy or the like

an anyone here make a procedure that lists all tables on on each changes the charset and collation?

Sal`: did not understand

i need a way to change collation to all fields, tables and databases
so i was thinking that maybe it could be done with a procedure

alter table can change for 1 table

yes, but thtere are a lot of tables and fields

SELECT * FROM links WHERE created_at = 2007-08-07 AND created_at 2007-08-08
where created_at is a datetime

i want to find a way to do them all with 1 command

why care about fields if you want to change all fields in the table

basically trying to find out if the datetime's day is equal to today

Sal`: maybe can use a shell script

date(created_at)

because if you change the table collation, fields collation doesn't change

oh, well, thats even easier, where created_at = now()

Sal`: CONVERT

i think that a procedure would be better

er, actually, still need date()

Hi All. I have a DB with thousands of phone numbers but they are not stored in a nice format. How can I update my db so all the numbers are converted from this format: 2125551212 to this: (212) 555-1212?

Sal`: i know convert in alter table can change the charset, should work for collation

where date(datetime) = curdate()

convert!!!??? i was using alter
let me see that

Sal`: alter table has a convert keyword

there you go, curdate(), I blanked out for a minute.

datetime = curdate() and datetime curdate() + interval 1 day

convert wors only for charset, can't use collation
so the only solution is a procedure with "show tables" gets the output and does an alter %table% …..

does anyone know if there's a web resource to tell me exactly what files _mysql.so requires and where they should be located?

Sal`: can also do collate, you can set both the charset and collate at the same time with convert

the manual says no

http://dev.mysql.com/doc/refman/5.0/en/alter-table.html
CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]

Sal`: I don't think you can use the results of SHOW in an sql statement unless you're using some application code to process it

knnylggns, ldd?

a function and a procedure could do it
but i don0t know how to build them

lol, well, there's that

Sal` prepare statement if alter table is allowed in prepared statement

thats the alter table… i already tried that.. but it means, as i said, dot it one tabel at the time

Sal`: have you looked at querying the information_schema and using that?

flawless! thank you so much

good

what can i do with that?
i'm looking at the info_schema right now…. then?

Sal`: you can use a "select" to get the table names

show

Who can talk in Chinese with me?

Sal`: querying the info_schema as an alternative to using SHOW statement results in other sql statements.

Sal`: problem with show is that how do you use the result in a stored proc

Who can talk in Chinese with me? I need some help.

why? doesn show return data like a query?

Sal`: no

Sal`: you can use cursor with select and process 1 table name at a time, show probably cannot
Sal`: then use the table name and generate a prepared statement and execute it
Sal`: i hope prepared statement works with alter table
Sal`: too bad, alter table not supported in 5.0
Sal`: a simple shell script should do it

kimseong++

ok, i'll do it with a scripting language
yes
thanks anyway

hi guys

I am trying to do a JOIN between 3 tables 2 of the tables. 2 of the tables have the same data element and this is undesirable because it gives me an ambiguity error. Is there a way to limit the data element from this table and still attach it?

I have a small problem with simple mysql query
http://pastebin.com/m599bf045
AsText(bounding_box) as bounding_box it works, but if I use "as Layer.bounding_box it doesn't

hi

how can I make the 2nd one work ?

can i see any MySql configure " for much "Large" data" ??

quote it
my-huge.cnf ?

kimseong i dont have any files my-huge.cnf

thanks

./usr/share/doc/mysql-server-5.0/examples/my-huge.cnf.gz
i open theat .gz file no ?

that is a sample config file

so is theer conf for huge mysql ?

how huge?

my web site send php data to mysql ..in large amount " data info" so CPU get up to 99% ..i must configure Mysql and php

cialis huge, viagra huge

anyone know how to check if a columnName exists in a table in DBI?

information_schema

what command do i use to allow root access remotely

!man grant

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

^^
!man remote

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

!man remote access

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

should i hash the passwd first?

sigh

create user root@'%'
you get a non priviledged root with no password

hey folks
this legal? select * from sometable where uid in (10,13,3424,2342,32)

snoop-: try it first

ok kimseong
i think it will work but am skeptical
curious as to which is quicker, a query with 40 or's or an in statement w/ 40 elements

snoop-: it is handled the same way internally

so the in would be better since my search string is smaller

snoop-: which is faster for you to type

therefore I would use the IN notation because it gives a better overview of the logic

true

what use is a non prived root?

yeah, that's logic for you

typo less likely

it is shorter and takes less communication and parsing
you can connect remotely

well, as the parsing expands the IN it might not be so But the parsing is really, really minimal

what if i need to crete tables, etc remotely?

you should have another user with just enough priv, instead of root with full priv

ok, my point is - how can I hash a password in SQL when creating the user?
what ever privs i do
i still need a passwd

the create user and grant syntax shows you that

what?

identified by 'password' plain text

ok

is it possible to combine different types of indexes into a multi-column index - such as an index first overlapping an ordinary int field and secondary using e.g. FULLTEXT or creating a SPATIAL FIELD?

hi

I would e.g. like one table with a lot of POINTs for a lot of different customers. I don't like the idea of creating a table per customer

guys I have a little trouble
I have just added two fields to tables
alter table hhg_interviews add column photo_big char(255) not null after photo;
alter table hhg_interviews_sv add column photo_big char(255) not null after photo;
now, I'm told to remove them
how can I do it?

alter table hhg_interviews drop photo_big;
alter table hhg_interviews_sv drop photo_big;

thanks a lot pbro

every 8 hrs ..mysql database is losing its connection after the idle time,how to avoid it?

sounds like a default connection limit

timeout when you leave it idle

What have to do to avoid it?

'By default, the server closes the connection after 8 hours or 28800 seconds if nothing has happened. You can change the time limit by setting the wait_timeout variable when you start mysqld via your servers my.cnf'

why bother, mysql cli reconnects, and why keep it when you do not use it for so long

Thanks threnody.

thanks thats done the trick.

you should probably close your connections when you're done with them.

mysql cli reconnects? How come?
ok

if you use mysql cli, it reconnects when connection loss, if your own application, you can code it to reconnects

oh! ok

what's the function to get the last row inserted?

LastRow() ?

last_insert_id()

aha!
ty seekwill

ah, thanks

I know that I can determine the number of child nodes in Modified Preorder Tree Traversal with some simple math. But what I can't figure out, or find, is how to do it in the reverse?
That is, can I use the left and right value of a certain node and figure out how many parents it has?

http://www.mrnaz.com/static/articles/trees_in_sql_tutorial/mptt_display.php

pap great thanks.

hehe
that's probably the most quoted article i've ever written hehe

it's you !!

indeed it's me

nice work, ty

any time

MrNaz excellent article. this is a keeper.

given how popular that is i've been meaning to clean it up and fix all the spelling and grammar errors all over it

MrNaz question… If there are going to be more than one major category, should they all be placed under a master 'root' node?

no
i'd give each major category their own ID and mave 3 separate trees

ok. so they should all be siblings at the very top level?

no not that either

interesting. i see.
so i'd need a tree_id field then.

so there'd be a table with a list of categories
id, category_id, lft, rgt ….
yes, the category_id is essentially a tree_id
http://www.mrnaz.com/static/articles/trees_in_sql_tutorial/mptt_deployment.php under "Multiple Trees per Table"

great thanks. i'll check it out.

is there a startup script to have mysql ndb management started upon boot?
or is there a reason why a startup script doesn't come with the package?

mrnaz i'm a bit confused about using multiple trees. let me use my case as an example. i'm building a shopping cart and i'll have to organize the products. if one category is called Electronics, does that mean the top nodes in the tree will be (1)TVs(2) and (3)Stereos(4)?

firstly… are you sure that you need trees for that?
it's only ever going to be 2 levels deep
why not just have a products table that references to a categories table

yeah that's right.
but i'll have several levels of classifications for products.

ok

could a product ever show up in more than one category?

in that case your categories table should contain a single tree, and your products table just refers to the categories id

pap yes

danggun in that case your product table and categories table should be related using a link list

yes, trees are not best for your purpose.

mrnaz so then my initial table layout is ok?

Pap i disagree…

mrnaz yes. that's the way i've got it planned so far. tables: categories, products, and products_categories

1) a "categories" table with a tree of categories, 2) a products table with your products and 3) a link list relating products to categories
Pap does that make sense or am i missing something?

mrnaz yes exactly. products_categories does just that. it will be defined as "product_id, category_id".

danggun sounds fine to me

yeah

no

mrnaz great. so then back to my original question. do i have one master 'root' node or do i simply start with the top level categories being siblings with no common parent?

it's unlikley that you'll end up with millions of categories, so i'd just use one root node

(2)electronics(3), (4)stereoes(5) OR (1)electronics(2), (3)stereos(4) ?

mrnaz you're right.

you can have a tree like the second description
— that's not a valid
as you

I have a table called "censored_word" with only one column, called "words". How do I check if a string contains any of these entries?
SELECT COUNT(*) FROM censored_words WHERE LOCATE('word', string)?
s/"words"/"word"/
Hmm… no.
I'm stuck. Please help.

WHERE string LIKE '%word%'; -

How can I make this query any more efficient:

Catnip96, SELECT 1 FROm censored_words WHERE string='word'

http://pastebin.com/m6e85396f

start over : )

No.

start over?

No?

You're thinking about it backwards.
I must use LOCATE…

why?

Are you saying that it's as efficient as it'll get?

Because the table is the one containing the words to check.
Not the string.
Is that a joke?

you want to see if the value in the database is in the provided string?

That is honestly the worst nightmare of an SQL query I have ever seen, and hopefully will ever see.
Um… I thought I put it clearly. :/

I'm using MySQL to store binary data in a 'blob' column. At first glance, it appears my application program is inserting all of the data correctly, but MySQL is magically truncating it to 65535 bytes. My max_allowed_packet is not being exceeded, because I have it huge (500M) and I get errors
if I try and upload anything larger than that. This truncation is happening silently. If the number wasn't suspiciously a power of two, I might think it's my applicati

!man AGAINST

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

Catnip96, You have a row for each word in censored_words, correct?

Sorry but I am not sure what's wrong, it's huge but other than that what would you do differently?

Yes. And this works, but I need to check for extra chars around this.

Catnip96, %% should work…

I really cannot imagine why you have to make such a query.
Really bad database layout from the 1970s?
….

No, I am doing a FULLTEXT search using multiple keywords.

Ever heard of formatting your code?
This is impossible to read.

I echoed this from PHP catnip

select * from foobar where LOCATE(barfoo, 'search');
like that?

Did you even see my example?

http://pastebin.com/d387159b3

SELECT COUNT(*) FROM censored_words WHERE LOCATE('word', string)
But I also need to check for special characters around the words.

yes so what's the problem? switch the args and be gone

So how do I add that check?

what is a special character?

There's your formatted version, by the way I am asking for advice.

Not important. How do I add it any string to this check?

I don't have a clue what you are talking about… ?

!man LOCATE

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

So you criticize and have no comments–wonderful.

trollish isn't it?

Yes it is.

Eh? Nobody could help you with that blob of text.
So I asked for a properly formatted one.

I think that you have the wrong approach

http://pastebin.com/d387159b3

Yes.
And now somebody can probably help.

blog length limitation is the same as text. 65k in your case. try a mediumblob or longblob. http://dev.mysql.com/doc/refman/5.0/en/blob.html

threnody to the rescue

well great.

tell me the benefit of using trees (which I am clueless about) vs. relational tables.

i had not even heard the term tree in years

oh humbug! there I go, assuming that blob is like it is in other DBMS'! thanks for finding that

relational tables for the win.
So…anyone for maximizing efficiency in:
http://pastebin.com/d387159b3

In highschool they used that term in my MS access class : )

it is almost always an error to store files in databases, instead of a pointer to the file. Be careful.

In "… LOCATE(`some_column`, 'huge string of text') …", is it possible for me to add characters before and after `some_column`?

CONCAT?

I agree, although in this case we have multiple web/app servers, and figuring out a good distribution scheme that 'works' is out of our realm of expertise

!man CONCAT

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

you have a good reason not to use 'where `col` like '%text%'; ?

Yes… because that is backwards.

sorry

I need to check the other way around.

no indexes either way (%foo% or locate)

Well… it doesn't seem like CONCAT can be used inside LOCATE… or can it?
No indexes?

did you try to use concat inside locate?

Well, I am trying to figure out how to use it.

is there anyone using Ubuntu 7.04 and using some GUI tool (myslqcc) in Ubuntu?

Ah…
Well, it seemed to work, chadmaynard. Thanks for telling me about CONCAT.

when you do (select id, description, prioritize, date_added, date_completed, status from items where list_id = 16 and status = 'complete' order by date_completed asc)
union
oops
wrong button
i apologize

Does not MySQL have any sort of convenient built-in string concat…ation stuff?
I love how you apology on three seperate messages.

concat(cola, colb, colc, …)

CONCAT() is built in

Well, I meant without using a function.

Catnip96, i panicked

Like PHP's 'foo' . 'bar'.
This is just me being curious.

Does anyone know how to FULLTEXT search on multiple keywords using one query in an efficient manner?

operators are functions

This is probably not what you want, but what about OR string LIKE '…' OR string LIKE '…' OR…?

I said FULLTEXT search, that would incorporate MATCH();

Evil FULLTEXT indices. :/

How are they evil?

Because they obviously make things more complicated.

fulltext is a lot less evil than '%foo%' or locate
(if evil = slowness)

Indeed, so does anyone know how to do FULLTEXT search on multiple keywordS?

how can i debug why my mysql cluster isn't starting? i can't get the ndb_mgmd running and i don't get any errors why

nope, is that what you are trying to do?

yes.

Is this a huge table? Is this actually necessary at this point?

Yes it's a big table.

So it takes like seconds to run with LIKE?

I've determined you're not very smart.
Additionally you have a very condescending attitude.

modifying the database is done by running the appropriate query?

Yes.

well… yes? the question is a bit ambiguous

There's plenty of GUI tools that will let you do so though without actually knowing the query.
SQLylog is one of them.

fair enough. I have a dbi that gives me five functions: open, query, get_status, get_row, and close.

are you using boolean fulltext or ….

No boolean.
http://pastebin.com/d387159b3

I would use query to modify the database (mysql backend), then, yes?

Look for yourself chadmaynard.
Yes j85wilson.

I know you didn't specify it but you aren't using Fulltext properly in your queries

look in the manual for ALTER
alter

also, terminology question, what verb is applied to query? run, execute, issue, what?

so i thought you may be confused

I am not running FULLTEXT properly?

i say run

all of the above, depending on who you talk to

That query executes just fine…

thanks, Delixe, threnody, ToeBee.

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

tizag.com is good to for newbies j85wilson.

I know it executes properly that doesn't even infer that it is right.

again, thanks.

It's been working the way I expect for so long, itneresting.
So tell me how should I do it then/

for easy intro, try the latest Larry Ullman book on MySQL

hold up a second let me make an example out of your pastbin. Would you mind posting your schema as well?

sure

bah, my needs are simple and I think I'm smart. Web tutorials will suffice (unless they don't ). Plus I'm on a grad student budget.

me, I go to Borders with a legal pad and pen 3x/week, grab a coffee and make notes.

nice

if I have to buy, I buy used from amazon

I just download illegally from an e-Learning torrent site called BitMe.org.

just don't create any database models until you learn normalization

That's how I learn.

bitme.org? sounds delicious, thanks

that is a reflection on your character, as much as you'd wish to tart it up.

what is this normalization you speak of? I can normalize a wave function, but not sure about a database.

It's a systematic process of removing redundancy (in short) from a database. Google "database normalization" and you'll get more links than you want, although the first few should be good

all database goodness flows from the sweet springs of 3rd form normalization

kk thanks

abandon all hope, ye who don't normalize

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

I was afraid I'd end up learning a whole lot… Not that that is a bad thing at all.
I was writing an IRC bot (markov chain based), and my hash tables filled up memory (plus I had to worry about saving and restoring them), so I figured a database was probably the better solution.

probably
I'd be lazy and do it flat-file

retrieving information from a flat-file is going to be O(size of file).
retrieving data from a hash table is faster (much faster), and I'm presuming that so is retrieving from a db.

you have to explicitly create indexes to achieve such perf (generally log2n)

j85wilson, You might try SQLite if you don't want to have a SQL server running for your IRC bot…

I've already got one running for my webmail interface (but it handles itself entirely, so I never messed with the db for that).

ah

otherwise sqlite would have been a natural choice.

Also look @ the docs for MySQL indexing; you'll have to create indexes on attributes you wish to query, otherwise you've just got a giant flat file with some overhead

kk thanks bobross1

I believe the docs indicate this, but indexes are B+ trees on your attribute(s) and thus can be expensive to maintain on any data write (insert, update), so don't go crazy and index everything

hey guys … just wondering if anyone had a better idea for me on this query … its been about 15 min and its still running … table a has 1 field … aa …. and is 2000 rows. table b has one field .. bb… and has 10 rows… table c has one field .. cc… and that has 16k rows… what I
am trying to do is combine aa and bb when the combination exist in any row in table c.
this is what I have … SELECT CONCAT(aa,' ',bb), CONCAT (aa,' ',bb,'s'),cc FROM a,b LEFT JOIN c ON(c.cc LIKE 'aa%bb%') WHERE cc IS NOT NULL

yowza. is that LIKE statement supposed to be a join condition?

an example would be aa='my' and bb='dog' and cc ='my bloody dog died' …. i would want to then return back'my dog'
yeah bobross1 … thats why i think something might work better

Hola PHP Wizards… I'm having a hell of a time with wordpress (argh!) … Any help you can offer is greatly appreciated : here is the information: http://pastie.caboo.se/85855

but logically, i believe thats what i want to do
so I am inner joining a and b, to get ALL combinations …. the LEFT JOIN'ing table c to that with the like ….
and its still running :-p

you're getting a crazy cartesian product is that what you really want

for a and b, yes
then i want to select from that combination , IF my example holds tru
true

What is the exact thing for C — what is 'aabb'? right now that's just a string, but is that supposed to be data from the tables a b ?

"I've determined you're not very smart."
I've determined that you're a rude fuck.
Who cannot format your crappy SQL code.

k

But have fun with your incomprehensible crapcode.
Retard.

right …. aa would be 'my' .. bb would be 'dog' and cc could be 'my purple and brown dog died'

… that was a stream of invective the likes of which I have not seen for some time.

if 'my' and 'dog' came together in the inner join of a and b …. then if that sentence existed in c, i would want to output 'my dog'

lovely

you might try something like SELECT … FROM( SELECT a CROSS b ) INNER JOIN c …

as far as I see …. my query should logically give me the right result … i just dont know if it will take until I wake up
but that does not satisfy my condition of aa and bb existing in a string of c together

and then I am not sure if your C condition is what you expect, oughtn't it be ON c.cc LIKE( concat( a.whatever, '%', b.whatever, '%' )

I'm looking for something that gives users the possibility to easily add new columns to tables and add new relations .. all within a GUI which is usable by non-IT staff… any hints? this is for project management with rapidly changing and per-customer different tables…

ribht, but you just said that the LIKE as my join is why it is taking so long…

I believe that's called suicide
but if you like giving your customers guns with which to shoot themselves, you can install PHPMysqlAdmin and be done with it

well, right now they're on excel - and it can't get worse

navicat is pretty good

Well, my guess is the join condition is incorrect, thus you're getting a x b x c rows, which is probably not what you want. Grouping a x b together in a subtable solves that nicely, then you can focus on making sure C is joined correctly
also, since you are outer joining, you're getting EVERY row of a x b which is likely a BigNumber

oh *** i was thinking it did a and be .. and then did c

thanks, i'm looking at it

Why can't I have both a FOREIGN KEY and UNIQUE constraint on a column?
phpMyAdmin whines

SELECT CONCAT(aa,' ',bb), CONCAT (aa,' ',bb,'s'),cc FROM (SELECT a CROSS b) LEFT JOIN c ON(c.cc LIKE 'aa%bb%') WHERE cc IS NOT NULL
that errors ( I have never used a cross join, or nested queries like that )

padde, there's always ODBC + Excel

remember that LIKE 'aa%bb%' is a string — so it is literally looking for aa followed by anything followed by bb followed by anything; it's not using the value from the table

yeah thats dumb … i see why you did the concat… but it is still erroring before that

so, using excel as a frontend to a real database…

SELECT cc, concatted, CONCAT( concatted, 's' ) FROM ( SELECT concat( aa, ' ' bb ) AS concatted FROM a,b ) AS derived_table INNER JOIN c ON cc = CONCAt …

does this work well? can new columns be created? how does excel then show relations?
is it possible to work on the same tables simultaneously?

excel can read data only

i see

just pass in a select statement

Can't Access be jury-rigged to talk to a SQL DBMS and manipulate it via Access forms ?

access can read/write

SELECT CONCAT(aa,' ',bb), CONCAT (aa,' ',bb,'s'),cc FROM (a,b), c WHERE c.cc LIKE concat( a.aa, '%', b.bb, '%' )
look possibly faster?

openoffice also can

i moved it to another inner join with the LIKE in the where….

i'd really like to rule out access that would be another program which makes this company dependent on MS office stuff excel is bad enough

still not looking to run fast ….

openoffice then

my ndb tables are not being replicated, how do i fix this?

It's possible, I'd compare the runtimes of each to see

306.5 M combinations ….

confirm it is ndb

i'll have a look at its database functionality after lunch. thanks for the suggestions so far

i havent gottent it to complete yet to where I could compare

yeah, that's nutso. Silly question, perhaps, but what is it that you're trying to do? Maybe there's a better way

i just created the database and created an ndb table but i don't see it on my other nodes
ui just created the database and created an ndb table but i don't see it on my other nodes/u
how do i figure out the problem?

other sql nodes?
try to create the database there too
autodiscovery of database may not exist, I cannot confirm this

i have key phrases in a and b …. i have sentences in c ….. i want to list all combinations of a and b where there is a sentance in c which contains both phrases

i did create the database on all nodes first then i created the table and i only see the table on the node i created it on

did you add ENGINE=NDB

no, i set the engine to… ENGINE=NDBCLUSTER
is it supposed to be NDB?

ndbcluster works too
confirm it with SHOW CREATE TABLE tablename

padde, never used ODBC… sorry, but I use navicat all the time and I love it!

ok, in my user database, I have mail_admin@% (I think this is for roundcube), root@127.0.0.1, debian-sys-maint@localhost, root@localhost, wordpress@localhost, and root@mydomain.tld.
do I need three roots?!

depends

yes, this is my sql command: create table test_clust ( id int(5) ) engine=NDBCLUSTER;

hi ladies and gents, im having trouble with parse_str() … anybody have any experience with this? here's my code: http://pastie.caboo.se/85855

I'm never going to access this db from outside of the box it is running on, and so neither should anyone else.

do a SHOW on ndb_mgm to make sure those mysqld are connected to the cluster
all your 3 root are for the local machine, right?

yes.

actually i just did that and it says that the server i'm not seeing replication on is not connected to the ndb_mgm… why is this?

try restart that mysqld
if still not connected, then check if there is a slot in SHOW and ndbcluster option is enabled on the mysqld config

ok its showing now

old_text, old_id and old_flags. I'm trying to add a fulltext index on that table, but it won't let me.
# time mysql mediawiki -e "ALTER TABLE ep_de_text ADD FULLTEXT(old_text);"
Column 'old_text' cannot be part of FULLTEXT index

the problem could be that mysqld was started before the mgm server, so it did not join the cluster
what is the datatype of old_text ?

| old_text | mediumblob | NO | | | |

maybe blob not supported

UPDATE home_runs SET hr = 756 WHERE player = 'Barry Bonds'

kimseong, Hrm, ok… but why can this person do the same thing? http://lists.wikimedia.org/pipermail/wikitech-l/2007-August/032616.html
I'm trying to replicate his situation, so I can test the timings

maybe this person has a TEXT type instead of BLOB

ok i have another problem… anything i try to insert into that table keeps coming in as a null value… ie: insert into test_clust values (id='test');… then if i do a select * from test_clust it shows NULL for the column value

kimseong, that would be very odd, since the data itself requires it to be blob

do you know why everything i am inserting is coming out null?

You mean something like bigtext?

You aren't inserting it?

you are inserting the result of the comparison
id should be null, compare to 'test' gives you null
mediumtext since you have mediumblob
!m winblows insert

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

oh sorry i'm retarded, duh
heh, in that case it works

hello
I am thinkin to backup all my mysql's server onto a remote server. My problem is I need to define a backup regime but I have no idea what I should start by

Ok, here goes

I have seen scripts and software as Amanda, Bacula and http://www.debianhelp.co.uk/mysqlscript.htm
and much more
but what should I take into account? For example, all my tables are miysam and none is bigger than 1 gb

if my database usage grows larger than what i have DataMemory set to then would my database crash?
and i will not be able to insert more data into my database until i increase that limit?

the question is I suppose the normal procedures is by script or whatever to do a mysqldump of all databases, zip and send them to the remote's server

you will get table full error

but what about if a server does not have space enough in the local hard disk to do that
?
I should to run the mysqldump against the remote server

run mysqldump from the remote machine connecting to the mysqld server

is there a way to set it so that it could just keep using as much memory as it needs? i basically want it to have an unlimited amount of memory

no, the memory are preallocated

Kimseong, What about if I an handling important information and I need to encrypt them?

you can increase it in small quantum with a rolling restart of the nodes without downtime
ssh connection

well if i reach what is the maximum memory i allocated then when i change it do i need to restart the whole cluster thus causing some down time?

Kimseong, what about if these servers do not have firewalls

monitor the usage

what do you mean by a rolling restart that won't cause downtime?

Kimseong, ssh connection? So you mean I have to write an script than mysqldump run on ssl connectios because I don't want to do it manually

change the config in mgm server, restart it, then restart 1 data node at a time

that has to be done automatic

make sure the data node has been fully started before restartign the next one
mysqldump connect to the mysqld, the connection can be ssh encrypted
not ssh, ssl

how do you monitor how much memory all your ndb tables are using so you can upgrade the memory limit before something bad happens?

that sounds fine. Even so I am afraid of open a 3306 port because of the servers do not have firewalls. Is it possible to decide in my.conf who is able to access to mysql by IP i.e?

Firewall it out

run it on a non-standard port

mysql user can restrict the client host

hey, is there a mysql gui tool that will allow you to create php forms drag and drop that query and and post to mysql db's?
or rather a 'suggested' one?

guys, the problem is I have found with they have open the standar port without any protection and the servers connect each other without restriction… It is unbelieveble!!!
but what kimseong says it sounds good

what is PHP?

and maybe you can force that user to use ssl connection, and you need the necessary ssl certificates

the devil

php manual might have the definition

kimseong, even so they will continue working with the former users that are allowed to connect to the rest of the servers. Arghhhh. I don't know if some servers are open for everyone

~man
!man

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

!man fulltext

see http://dev.mysql.com/doc/refman/5.0/en/fulltext-fine-tuning.html

nabfphp

We are not a backup channel for ##php.

does the MaxNoOfTables setting mean the max number of tables in a database or just max number of tables overall that ndb will support?

should be max number of ndb tables, but sometimes hidden tables are created

and I suppose mysqldump handles copies of table that are currently in use

is there a command or script that will give you your current ndb usage? ie: tables, memory, index memory, etc?

is not there a show database status as tables to know how big the databases are?
how could I know the database's size besides using the system's commands?

what's the speedest way to check if a table exists in a db? select count(*) from tab ? select 1 from tab where 1=2 ?

A258, show tables like '%partial_table_name%';

i checked : if db got many tables, this is not the fast one
show tables like 'tabname'

A258, I have a db here with 918 separate tables
It returns the table name in less than 1/2 second

mmm

doh
# time mysql mediawiki -e "ALTER TABLE ep_de_text ADD FULLTEXT(old_text);"
/tmp: write failed, filesystem is full

select count(*) from tab — 375ms
select * from tab where 1=0 — 215 ms
select 1 from tab where 1=2 — 195ms
show tables from db like 'tab' — 236 ms

why when I go to /var/lib/mysql I just find ibdata for innodb and not any information about miysam's table?

Because ibdata isn't for MyISAM

setuid, yes I know but where is the miysam's information then?

setuid - db has 90 tables, tab has 165 000 rows

A258, That's pretty small

select count(*) from ep_en_text;

+———-+
| count(*) |
+———-+
| 5365743 |
+———-+

dear! you are big
i found bigger table slow down a lot …

Depends on your keys, indices, engine

is there any online tool where i can like layout my db structure with diagrams?

i got an apache log table - not pk, no key, 11 000 000 rows, then i have to cut it
myisam

How can i compare the shema of two database?

ricknick, show create table table_name;

it sounds comparing manually.
any tools that i can use which tell that which tables is different or schema has changed

all dump 1000

DIY PHP loop the db

huh?

this will write the memory usage to the logs
run on the mgm client

where do i write that?
ok

most mysql tools can export db schema - you can use php editor to compare 2 exported schema

can try using information_Schema

which log file would that write to? /var/lib/mysql-cluster/X or?

cannot remember, logs on the data node if I remember correctly, log extension

is there a way to output that onto the ndb_mgm program instead of the logs?

don;t think so

kimseong, why am I not able to find myisam's files frm, myd in my default location for mysql's database (in debian /var/lib/mysql) I can just see innodb

show variable like 'datadir'
then search the subdirectory with the database name

good morning

happy yesterday

hi everyone. I have a timestamp column, but when I query I got a date time format. How do I return a unix timestamp when I do select query ?
thoughtful, happy yesterday too

mysql.com/date and time functions
you'll find a function that converts to unix timestamp (epoch counter)

arjenAU, so we need extra function for that ?

on input you should put a datetime in also, or use NOW(), or use the CURRENT_TIMESTAMP magic in your table create.
yes. it is stored as a 4 byte epoch, it's just the in/output that's ISO datetime.

arjenAU, ah great.. will take a look at the site. Thanks arjenAU !

if you just need the epoch always, and don't need the magic in the table create, you would use an INT UNSIGNED instead
then it's stored the same but you get it back the way you want it. but it's really not bad to use the function on output… harmless.

arjenAU, hm… I think that's the thing I want. You see, I'm using PHP and each time I have to use date('d-M-Y', strtotime($row["dateField"]))
that's an overhead
so everytime I need to convert to Unix's timestamp first

nah, you can stick it in the SQL

arjenAU, that's why. Thanks for the clue arjenAU !

With a .sql table create script what should one prefix comments with?

When I run mysql -u root I get ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
I'd appreciate any help

KanRiNiN, do you have the root password?

I thought I defined it.
I apologize, I'm a noob.
I'm the admin, so yes

if so you will need to add '-p' to your 'mysql -u root' command
that tells the mysql client that you wish to specify a password

so mysql -u root -p password whateverichoose

no, just "mysql -u root -p"
then you will be prompted to type the password

excellent. I'm following the Feisty guide, so it's been smooth so far

this way the password will not appear at any time in plaintext
I know of the show create table statement, is there a way to do this for every table in a database?

So for example, I get the ERROR 1133 when I run SET PASSWORD FOR ''@'localhost' = PASSWORD('newpwd');
So I assume I need to do the same for my localhost name
and create a user/password for it?
again, localhost and newpwd are obviously changed

KanRiNiN, but that initial '' specifies a user with no username, is this correct?
try running 'select user, host from mysql.user' to determine all the accounts that mysql knows about

ah, so it should be SET PASSWORD FOR 'root'@'mylocalhost'?

if that's the user account you plan on changing the password for then yes

got it.

hi all
I need some help with sql

Quick question (I've googled and found conflicting answers)
INT(4) - is that 4 bytes? 4 digits? 4 bits? 4 parsecs?

4 parsecs. definitely 4 parsecs.