!tell mrak about replication

mrak replication is restore from a backup and then an ongoing application of the binary log. You can think of replication as a constantly running recovery.

!tell jonathan about teh buttsechs

jonathan

!help

help

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

!man replicati

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

tnx a lot

hi guys

hello

I’m trying to write an sql query that will basically give me a list of parents and descendants for a given member
id, parent_id, name

!tell Psykick about trees

http://www.dbazine.com/oracle/or-articles/tropashko4 and http://www.sitepoint.com/article/hierarchical-data-database, http://jan.kneschke.de/projects/mysql/sp/ http://jan.kneschke.de/projects/mysql/sp/sp_tree.sql http://www.vbmysql.com/articles/database-design/managing-hierarchical-data-in-mysql/ http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

1, 0, item : 2, 1, item : 3, 0, item : 4, 3, item : 5, 4, item
thanks … I couldn’t quite remember what it was called
was doing a search for linked lists

nested sets might be what you are looking for
i dont understand them but i know they are teh awshum!!!

trees are pretty much what I’m after … but I don’t like the fact of an addiitional 2 fields just for locating items
I’m certain with a little bit of perserverance it is possible with a single db call and only 2 fields

you go girl!

errmm … I think you mean boi

Is there a mysql function to determine if data in TEXT field is numeric… similar to the php is)numeric function ?

!man mysqld_safe

see http://dev.mysql.com/doc/refman/5.0/en/mysqld-safe.html

Hi i am trying to select two rows from a table and treate them from one table so far i have this http://pastebin.ca/657892… Unfortuantly i dont think it works as union make anoither query and then just looses all my other querys making them invisble to
the second query
heres an example of what it looks like

kieran491, the union must both return the same number and types of fields

ohh really
well that query wont work
is there any other wya of doing it?

I think you just want a join

join wont work if your after two results on diffrent rows

Hi, I’m getting some hourly stats on my chat application (how man logins, how many signups) and I was wondering about performance..

or will it and i have been grossly miss informed

would it be better to keep the row I’m updating during the hour (constantly adding to it, several times a second) by itself and copy it across to a table of historic data.
I guess what I’m really getting at is, is there a big performance hit on updating the last row in a table with a few thousand rows in it, compared to updating the first and only row in a table?

a row being regularly updated would be best in an innodb table

its good idea to use [mysqld_safe] under debian like setting higher nice or somethink
?

this is what i tryed once it gave me no results http://pastebin.ca/658174

!man key_buffer

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

we all die some day.

cant be done?

any ideas?

Is it possible to exclude tables during replication?

replicate-ignore-table

Thank you.

or Replicate_Wild_Ignore_Table
yw

hello
I have turned off my server and I can repair my database..could you please give me some advice?
my error message is
Can’t find file: ‘CARS’ (errno: 2)
(when I type mysqlcheck -r for this table)

do you mean myisamchk ?

yes, I tryied also myisamchk

hi guys. anyone here used php mysql web hosting slow query log? where it says Query_time: , is that in seconds? milliseconds? other?

- recovering (with sort) MyISAM-table ‘CARS.MYI’
26
- Fixing index 1
and nothing else.

EoN, seconds

ok thanks archivist
i got my web host to turn on the log for a week and send it to me. some of the queries of my site take 10 seconds, later they’ll take 22, other times they’ll take 210. this basically proves that it’s not indexing/code performance, but rather the hosting environment, right?
(vastly differnt times for the same query)

maybe tool not finish yet?

Yes. “Join is all you need.” [That might be a beatles song.]

finished, and I got this same result always when I run myisamchk -r

You haven’t provided enough detail to know why you think the JOIN “didn’t work.” It’s pretty obvious you simply want to join the tables.

do you try use this table after repaire?

yes

and so?

You have a design problem, but that isn’t the issue here.

nothing changed.

what do you mean?
what error message?

anyone? re the mysql query log? trying to trouble shoot it, and the web host arent very smart

I get still this same error message, (error 2) can’t find file

The time differences could have to do with buffering / caching.

!perror 2

No such file or directory

i know

But the difference could also be due to programs running outside of your control.

EoN, at 10 secs you have a problem sort them out on your dev box

but i have correct permissions

i did not web design the Database that what i have been given to work with

sakaata, its not there, not a permissions problem

wich output of show table status ?

I see my table in show tables;

who can give me bnc account?

without any error?

Xgc/archivist: yeah im trying to establish whether the fault lies in my code, or on the hosting environment (with issues outsid emy control)

in show table status

Provide the missing detail and someone will be able to help. So far you’ve said, “it doesn’t work.” But you haven’t provided any detail.

due to the differences in time execution for the SAME query, im thinking it MUST be their fault?

but what i am after thought is a proccess in which one table contains a username and serverial referances to ID in another table now i would like to query all that data in one go but the data that is referanced is in serveral diffrent rows

!man max_allow_packet

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

without any error. but I get error 2 if I try to describe or select from table.

!man max_allowed_packet

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

SHOW TABLE STATUS not SHOW TABLES

avip:

tbla t1 LEFT JOIN tblb t2 ON … LEFT JOIN tblb t3 ON … etc.

EoN, only partly yes but at 10secs for a live query you must be using up to much processor/disk

hmm ok how dose that work?

Simple as that.
It works fine.

but wont the colums all be the same?

That’s the general form of the FROM clause you need.

Can’t find file: ‘HLASOVANIE’ (errno: 2)

No. The correlation names for the tables will all be difference, t1, t2, t3, t4, etc.

how can I join more than 2 tables?

.frm ?

See above.

ohh k this automated?

all other data is NULL.

sakaata, have you been copying databases

No. You have to type it yourself.

this file present phisically on the disk?

ahh k

yes.

sakaata, have you forgotton the ibdata files

FROM t1 JOIN t2 ON … JOIN t3 ON …

it’s InnoDB?
good point

what is InnoDB?

avip seen a few do that

ibdata files?

!m sakaata InnoDB

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

ibdata1 ib_logfile0 etc or matbe you are use file_per_table option try to read about innodb

wait Xgc dose t1, t2 ,t3 repesent diffrent tables?

you think that i am not using myi db but innodb?

Different logical tables in the query.

so that means there all diffrent tables or are they all the same table? (sorry that termonlogy has gone over my head)

i don’t know i’m just try to suggest

SELECT t1.id, t2.id FROM tname t1 LEFT JOIN tname t2 ON t1.id t2.id; — Do that on a small test table called tname and let id be type int.

I found /space/logs/innodb_logs

!m sakaata InnoDB

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

They can refer to the same or different base tables.

ok, i am reading it

In the example I showed yuo, there were two (2) base tables.
tbla and tblb were the base tables.

yep
i think i have an idea of what your talking about now

!man thread_cache_size

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

If tableA has two columns refering to tableB: SELECT ta.*, tb1.*, tb2.* FROM tableA ta LEFT JOIN tableB tb1 ON ta.ref1=tb1.id LEFT JOIN tableB tb2 ON ta.ref2=tb2.id …;

Ohh i get it now

i have a quick question

thanks Xgc

if i am doing order by, should the column that i am ordering by be indexed?

hp_ probably but maybe part of a compound index that the where clause uses etc

How do i insert a sql file again from console?

by reading the manual

\.

http://www.google.com/search?q=mysql+insert+from+command+line

mysql dbname cantrememberthat.sql

Why does mysql ignore “ON DELETE CASCADE”? The table has been created with: create table aktuelles_ra (id_aktuelles int not null, img text, pos float, foreign key (id_aktuelles) references aktuelles(id_aktuelles) on delete cascade, unique (id_aktuelles, img(10)));

Muisje:or from mysql console ‘ source /path/file.sql;’

ok thanks

MyISAM?

pardon?

what’s your default engine type?
If you want foreign key constraints, use Innodb, not Myisam

don’t know, I simply created that table on a fresh db
O_o

CREATE TABLE tbl (id int PRIMARY KEY, …) ENGINE=Innodb;

Why didn’t it complain when creating the table?
thx

the_wench innodb
!man innodb

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

myisam doesn’t do foreign keys, it’ll turn them all into indexes

I wonder what other side effects come next …

errno 150?

what engine type do I need if I want transactions?

innodb

(does mysql hosting support transactions?)

yes

ofc
what do you guys reckon is the best web hosting method of replicating mysql servers accross regions, we have servers in the UK but need to now add US based servers and have a requirement to keep them sync’d

is a 30 mb sql hosting file large?
Or relatively small.

no

can a foreign key of a innodb table reference a myisam table?

no

crap

bref, replication?

Xgc sorry to bother you again just wondering what has gone wrong here i get a error saying Unknow colum but the colum is there this is my query http://pastebin.ca/658238

isn’t that slow though?

bref, slow in what way?

I was thinking about creating a cluster, but not done it before

bref, you can not do that with geographical distances like you want

how would a cluster solve your problem?

well the sync needs to be almost instant if possible

async or sync ?

bref, replication is pretty fast normally

because as far as I’m aware servers could be placed in differing locations

hi

wtf?

im converting an old perl script that is used to import delimited files by reading line by line and doing a prepared insert to using LOAD DATA LOCAL INFILE…. however, it seems that using that keeps the newline on the last field inserted. Is there a way to make it strip the
newline?

you aren’t going to be to do synchronous replication with geographical distances

are user passwords also stored in /var/log/mysql/mysql-bin.00XX?

bref, iirc mysql doesn’t yet support multi-master replication, so as long as you do inserts into the main UK database, you could have a replica in the US to speed up selects

I have a table with results, thats the structure of it: uid,pid,user_id,item_id,criterion_id,points. Now I want the total of points sorted like this: Sum all the points for entries in that table with the same item_id. Do this for every entry and sort it…

sounds good, as most of the DB work is reads anyway

mysql doesn’t support multi-master?

it does with ndb cluster, but I didn’t think it did with traditional replication…

you can do multi-master, but it doesn’t do conflict resolution
so if you only update UK data in the UK and only US data in the US, then you are fine
if you update all data anywhere, it can be a problem

ah

it could potentially be any data anywhere

SELECT SUM(points) FROM tx_t3fratingtool_rating_results AS R WHERE R.item_id=2, Now I don’t want that for a specific but for all of them that don’t have equal item_id… So for all the entries with the same item_id seperately… but as one result. I hope someone understands what I mean. In the
end there should be something like: item_id:1:points:10,item_id2:points:20 and so on

Never mind sorted it out thanks

!man skip-bdb

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

group by?

SELECT item_id,SUM(points) FROM tx_t3fratingtool_rating_results GROUP BY item_id …

flung, HarryR omg too easy
now I need to join before I do that… this should work without problems I guess

Right, I’m on the very first step here; should I delete the test accounts, add a new one or just change the password????

how can I sort afterwards by the summed points?

yah, SELECT SUM(points) AS total_points ORDER BY total_points

bref, sounds like you want something that doesn’t exist, grats

great

be better.

is it possible to have mysql have conditions on what a querys result must have in order to be apart of the finaly result so say if a = 0 then dont return anything above 4?

hmm what do you mean?

cant be done?

wtf do you mean?
!man where

see http://dev.mysql.com/doc/refman/5.0/en/extended-show.html

!man select

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

looking for somthing more than where

like, WHAT?
be specific in what you ask
what did you try, why didn’t it work

your question is absolutely terrible. it is appalling.

return 25

!m kieran491 control flow

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

ahh k

SELECT IF(blah 20, blah, 25)

Thanks HarryR

or CASE for ansi

who needs ansi

!man show

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

any ideas on how to get LOAD DATA LOCAL INFILE to strip the newline instead of including it in the last column?

how can i send inserts, updates and deletes to a master database, and select queries on slaves?

hi !
how see the table of user and their password on mysql ?

seems google have done something that might help: http://code.google.com/p/google-mysql-tools/wiki/SemiSyncReplication

i ‘m on database mysql

bref, not really, just because it is transfered doesn’t mean it is applied yet

Whats wrong with this stament? ” IF(t.`DeathSD` = -99999999, NULL AS t.`DeathSD`, DATE_FORMAT(DATE(t.`DeathSD`), ‘%d %b %Y’) AS `DeathSD`) ” i cant see anything wrong with it BUT Mysql gives me this error
“You have an error in your SQL syntax; ‘AS t.`DeathSD`, DATE_FORMAT(DATE(t.`DeathSD`), ‘%d %b %Y’) AS `DeathSD`) t.`I’ at line 11″

true

bref, that is more for failover, not sync’ing stuff across continents

NULL AS t. why would you prefix a table before an alias?
NULL as `DeathSD`

http://pastebin.com/d75aad856 — Is there a way to automate retrieving the next highest groupid when inserting into the table?

ok

your query is terrible.. absolutely terrible.

thanks
how can i improve it then?

why are you testing for -99999999 ?

The only way I can think of is to select the highest id, and then increment it, before inserting another row…

its the result in the table i want to replace with null

the paradigm whereby you’re having to do that, is almost definitely flawed.

sooo?
what should i do to fix it?

fail.
i dont know. keep trying.

Ahh
fail

i just think that you’re thinking in ‘procedural’, and you need to start thinking in ‘batch’/sql.

What do you mean?

exacctly.

Huh?

why dont you just STORE eitehr NULL or a DATE in DeathSD and do ‘SELECT DeathSD’?
instead of that monstrosity you have there?

the database is created by a program that is writen in access then exported to Mysql so that i can use it i have no say in its design and nor do i like it

fair enough.

IF(t.`DeathSD` = -99999999, NULL) AS t.`DeathSD`

Hello, anyone who can point me to a good reference for storing ip-adresses in mysql (both v4 and v6 – if possible).

Thanks

php.net/ip2long

don’t store them as strings please

hi. I’d appreciate one or more suggestions for an easy-to-install, easy-to-use UML generator for my mySQL database. it’s for a one-shot use so I don’t really need lots of features.

no, i was thinking of using php’s ip2long, as EoN suggested, or Mysql’s INET_ATON()/INET_NTOA(), but they return slightly different results, plus they are only ip v4 compatible from what i can see.
thank you

why do *killed* threads stay on processlist ?
32 manager localhost test 608 Killed show TABLE STATUS

until they’re rolled back?

hey people – i have the following IF statement in one of my SQL statements:
IF(‘field’ = ‘value’, “OK”, “NOT OK”)

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

but i would like to say “OK (value)” instead of just “OK”

use concat(“ok”,field)

what is there to roll back on “show table status” ?

is it possible i can use SET like this..
SET (col1,col2) VALUES (‘one’,'two’)
because i already typed it all out like that for update
i mean for insert
then i realised i need SET instead
so would save trouble having to re organise it..

you know that you can use SET col=val,col=val,… style syntax for insert as well ?

i didn’t

now you know

but can i use the above syntax for SET?

i think not, but check mysql.com/update+syntax

ok thanks

I’m not sure what that transaction did before show table status
maybe he just imported 10M rows and used show table status to confirm something
then changed his mind and killed it?

http://www.nopaste.com/p/anFHJf7Clb Can anyone help with optimising it/using better indexes please?

madriss, not a lot to do to that

hmm thats a shame, its using temporary and filesort. doesn’t that mean its going to be really slow?

Run it and see if it’s unacceptable.

it’s fine but there is only a small amount of data at the moment. later on there will be thousands of entries
so would be nice to get it as optimal as possible before that happens

It’s impossible to effectively optimize a query with non-realistic data.
Load up the table with a couple thousand rows, then run explain.

surely itll have the same thing but with the number 1000 instead of 8

Perhaps.

would it change the type or select type etc aswell then

It might. Depends on the distribution of the data and such.

ah right
so theres no better way to do the query/better indexes i can use? if thers no changes i should make now i cant see how im gona b able to change it later

How can I alter a column in a ready made table to make it auto_increment and primary key?

i dont know a lot but what i heard is that temporary and filesort = bad query

!m fifo__ alter table

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

Yes, it’s typically something to avoid. But you may not have a choice.

hm ok, thanks

You might try forcing one of the other indexes, just to see what happens.

snoyes, it doens’t show how to do auto_increment

just worrying that it will be really slow later lol. last time i made a database that ended up with 1000s of entries it was months before i realised how slow it was and then i was like ahh.. missing index lol. now im bein really careful
how would i force one of the indexes?

It’s just part of the column_definition, exactly as on the CREATE TABLE syntax.
….FROM `won` FORCE INDEX `titleid_date`, for example

ah ok thanks

Usually it is better to let the optimizer decide which index is best to use

yeah

You could even play around with swapping the order of won_date and title_id in the wrestler_id index.

ok ill try swapping them around. i assume im correct in using those 3 columns in an index for this?

seems that way.

i everyone, i have a quick question

thansk for ur help

when you use “interval 1 day”, does that refer to calendar day, or 24 hours ago (from the current time)?

ah
i changed the won_date and title_id around
now it uses temporary but not filesort
so i assume thats an improvement

It refers to the length of time equivalent to 1 day. You can add or subtract it.
Most likely.

sweet

This might hint at why that happens: http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html

hahai changed it completely and it said 87 rows instead of 8 rows shows that the index was doing something then
thanks for the link

gracias, just what i was looking for, sorry to bother, it didn’t even occur to me that I could just pop date_sub into the command window and test it myself

wich privillages need user of slave replica ?

REPLICATION SLAVE.

!man replication slave

see http://dev.mysql.com/doc/refman/5.0/en/slave-io-thread-states.html

!man grant syntax

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

its about how to get priv but i need wich 1 i use for

It’s the REPLICATION SLAVE privilege.

oh sorry 2nd url for me

hello, just wanna know what are the reports supported in mysql?

reports?

You’re being just a little vague.

yes, that is because i dont know. i would like to know.
yes, reports to display the data stored in msyql
mysql sorry

!man select syntax

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

cyzie, you write sql, its infinite

infinite within the constraints of the packet size limit.

hehe
plus or minus a bit

mm ?
infinite?
i mean display the mysql data in the web.

You’ll need to learn a programming language first.

you want to display results of a mysql table in a webpage?

cyzie, you write some code

You could also hire a programming linguist.

mmm ok

i learned how to do that last night in 15 minutes. if you want I can show you how.

linguist! thats a bit posh for the time of day mhillyer

ok, how ?

i’ll post a pastebot link.

thank you davis

you need these reports exposed to internal staff or do you need them published on the web (globally)

TodoInTX, i would prefer a login , then user can select such as, date, from what date to what date.

exactly… pentaho is going to be a lot easier than writing that yourself.
http://www.pentaho.com/products/reporting/

yay! our dev server kicked the bucket

Huzzah!

ok, thakns TodoInTX
any other suggestions?
davids?
davis?

yes

has anyone in here used mytop and would suggest using it? is it decent?

it’s ok

haha thanks archivist

you are welcome.
err, cyzie

better than typing “show processlist” all over

mytop was good (in 4.0/4.1 days) innotop is better imho

davis, the link ?

mytop hasn’t had active dev for at least 4 years

hmm.i thought I posted it.
maybe to the wrong channel. lol

Because it achieved perfection.

http://pastebin.com/m1f47b92b
that is a rewrite of this page’s example http://us.php.net/manual/en/ref.mysql.php

well, seems like it’s complete

so

hardly ;-o

what’s up with MySQL AB excluding the free software community?

la
How are they doing that windowsrefund?

thanks guy, i look into it

in what way?

they’re only providing code to their paying customers as I understand it

incorrect…

ok

They are only providing their enterprise edition source code in an easy to use tarball format to their paying customers.
It is still available to the public via BK.

or via a customer

Or via dorsalsource.

and they’re still providing Community source to everyone else in tarball format, just less frequently.. (same as they have done for the past year)

And so for 99.99% of users out there, nothing changes.

But still

still seems to not be in the spirit of the free software movement

Hush you.

just my opinion

For people like dorsalsource they need to compile from BK not tarball… no big diff for them.

I’ve always seen the company as being a bit of a whore

yeah well that’s what you get when using free software developed by a profit-oriented company

How is that not in the spirit of the free software movement? Just download a BK client

I’ve learned that the need to feed one’s spawn can often conflict with the spirit of the free software movement.

how else would you characterize any ‘company’

TodoInTX, good point

Or at least be percieved as being such.

as long as there is the community edition it’s ok for me, I’d even pay for mysql.

well, thanks for the info
I’m gonna go grab a coffee

Get me one too. Sugar and cream

yep

3 sugar and milk for me

the source distribution policy goes 100% along with the GPL. They are required to give source code to anyone receiving binaries.
no rules on binary distribution in the GPL

if you pass by the refridgerator, bring a beer for me

Well you know what “they” say…

!man replication slave

see http://dev.mysql.com/doc/refman/5.0/en/slave-io-thread-states.html

!repair
how can i do to repair tables ?

!man repair

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

Carpenter’s glue and wood clamps.

don’t forget the screws

just finished a clock case

And use Titebond. That Elmer’s brand woodglue is about as effective as well water.
uAnd use Titebond. That Elmer’s brand woodglue is about as effective as well water./u
(but with fewer newts)
Because the optimizer thinks they are long and hard.

Time to upgrade from the old 386 desktop you had in the corner?

or they are self joins and the optimiser is still thinking

speaking of long and hard, I should continue working on my pr0n site!

would recursing a result set rather than storing the results and doing a query for each of the results be slower than storing the results, freeing the set and doing the queries?

how do I change the open_files_limit on my MySQL database?

you put it into your my.cnf

^Migs^: you have to set it in the my.cnf and restart mysqld

okey dokey, thanks
uh, where’s my.cnf?
nm

/etc/my.cnf or ~/.my.cnf or $DATADIR/my.cnf

but why would the default openfiles be too small

^Migs^: depends… /etc/my.cnf or /etc/mysql/my,cnf
or…

if you have a lot of tables this could happen

too many tables methinks

or just a lot of tables in a Data Warehouse

Rince, we do see some excessive numbers in here from time to time
uRince, we do see some excessive numbers in here from time to time/u

how do i set the mysql user/pass ?

!man reset root

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

!man securing

see http://dev.mysql.com/doc/refman/5.0/en/default-privileges.html

!man grant

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

no, how do i reset the password for mysql?

reset root

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

there is an easier way of doing it

the “init-file=…” method is safer.
you could just restart with skip-grant-tables and jump in, flush privileges, then set password…

it’s so annoying when I ask someone a simple question in IM then the first thing they ask for is my phone extension

IM ?

nobody cares, lol.

hello

how can i send inserts, updates and deletes to a master database, and select queries on slaves?

mysql proxy or you take care in your application

mysql-proxy stable?

it’s still alpha
but it’s ‘pretty stable’

anyone tried sqlrelay?

using Java?

??epoch

hi. can anyone tell me what steps i need to do to enable remote access to mysql on a LAN? its mysql 5 on Ubuntu 7.0.4. I know about granting access from within mysql but i think there may be somehting at the linux level i need to change like access on port 3306.

http://dev.mysql.com/doc/refman/5.0/en/connector-j-reference-replication-connection.html

TodoInTX for my application i use java/php/python

anyone knows about epoch and date transalation? like the psql SELECT TIMESTAMP WITH TIME ZONE ‘epoch’ + 982384720 * INTERVAL ‘1 second’;

thanks

codeCowboy change the bind-address to something other than 127.0.0.1 (comment it out if you want mysql enabled on all interfaces) in /etc/mysql/my.ini — then restart mysql with /etc/init.d/mysql restart

shadfc, its my.cnf on my system. #bind-address is commented out

external

remove bind-address= and skip-networking from my.cnf and grant permission to the external ‘user’@'host’ and remove any firewall rules blocking port 3306 and make sure no overrides on the mysqld commandline

codeCowboy yeah, sorry, my.cnf is correct. check netstat -antpl to see if mysql is listening on 0:0:0:0:3306
codeCowboy if so, check the grants as the_wench said

skip-networking is in there. does that stop any connections from the network?

codeCowboy yeah, it skips networking =/, comment it and restart mysql

do you where chaps at your desk?

i do!

leet
do you have the ass cheeks cut out?

sure. is there any other way to where chaps?

how do i change the size of the max_data_length of a table, its currently at its max and im not able to write any more records to it

no sir!

i dont get it though i thought this was a 32bit limitation, im running 64bit community on RHEL4

netstat -a|grep -i mysql shows mnysql listening on 16497

archangle25, there a a page in the docs for that

??

!m archangle25 full table

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

ty, apparently google doesnt think that link is top 10 relevant to a search on mysql max table size

how do i find out what mysql pass i am using?

if you don’t know it you can not see it since it is encrypted

reset then

eth01, we already gave you the link

Greetings, everyone. I’m trying to figure out if it’s possible to select just certain columns out of the results of a “show something” command. Any pointers ?

Nope. You may be able to select the same information from the information_schema. Otherwise, you have to parse the output in the application.

show variables like..

fair enough, thanks for the answer snoyes
thanks, archivist ; I’m actually trying to get columns out of a “show table status”

I’m trying to find all unique values of a column, and how many times is each of those values listed..?

dupes

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

why not just query what you want?

@bayroot – I don’t understand the question? I don’t know how to cut down to just the desired fields from a “show table status” command
And it’s been suggested that you can’t

how do I stop mysqld in debian?!

I can, of course, do it in app logic, I was just hoping to let mysql do some of the work for me

The same information is available in information_schema.table.

ciao all

@snoyes – yep, I’m starting to find it. Thanks!

You might find this on your own, but in case not: WHERE table_schema = DATABASE()

hm, okay, I’ll make a note of that. Thanks
ah, I get it
as it turns out, I want to get usage for all databases, but that’s still a handy trick to know

/etc/init somewhere there

Thanks for the help, everyone

shadfc, removing the skip-networking did the trick. thanks!

the_wench too
np tho

and the_wench – thanks!

anyone have a good way to find differences between tables? i’ve optimized some scripts but i need to make sure the results are the same

Hey folks. I have two tables with lists of names, and I want to, essentially, find all of them that are in one table that aren’t in the other, but select table1.name from table1, table2 where not table1.name = table2.name; doesn’t seem to work

!tell shadfc about toolkit

shadfc 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.

and haptiK for taking the piss!

a not in b

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

Listen to the_wench.

hello, i have a mysql 4.1 box and a mysql 5.0 box. I want to move my data over to the 5.0 box. I have read all the online documentation but i still cant decide if its better to run a dump/restore or to jsut copy all the files over directly and run the mysql_upgrade program

ok, I’ll look

mysqldump -h 4.1server | mysql -h 5.0server is simplest, IMHO.

Ah, I see, thanks.

OK, i am actually trying that and i’m getting duplicate primary key errors on every table whos primary key uses auto_increment

how can i easily clean duplicates (of a certain column) in my db?

dupes

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

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

http://pastebin.ca/657390 getting error: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near ‘SELECT COUNT(tas1.id) FROM netoffice_tasks tas1 WHERE tas1.proj
anyone see anything that might help me out?

thanks

What is your version of MySQL?
Subqueries were not available until 4.1. I suspect you have 4.0 or 3.23.

ahh, ok… let me check

this is srtange, i’m getting duplicate primary key errors but grep shows that there is only one instance of this key

hi

What is the INSERT statement?

select fields from tablea where tablea.id=tableb.id order by tableb.foo
SELECT * FROM lexicologie.culm1 WHERE lexicologie.culm1.ref=lexicologie.culm2.culm1_ref ORDER BY lexicologie.culm2.culm1_ref

INSERT INTO accesslog (aid, sid, title, path, url, hostname, uid, timer, timestamp) VALUES (34262276,’b0cdaf346c3777ca8f292f52edff4fcb’,'Day’,'teachercal/mclaughlins/2027/01/29′,’http://www.xxxxxxxxxxxxxxxx.com/portal/teachercal/mclaughlins/2027/1′,’211.111.111.111′,0,190,1187108489);
sry for the paste
thats the insert statement
future statements will go in pastebin

And aid is the auto field?
!tell maxagaz about joins

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

Any chance you’ve already inserted these records into the database?

yes
nope, i’m starting with nothing
the dump is creating the DB

Does it drop the existing one, or just create it if not exists?

thanks

i drop the existying one before i begin the restore
i am starting this on a installation with no db’s besides test and mysql and info_schema

whats the type of aid

int(10)

are you starting with an offset to the auto inc

aid int(10) NOT NULL auto_increment
no, i’m not

GREAT! It works perfect!!

Show the exact/complete error message. Do you have any triggers?

no
hold on, error message is on the way

Hello all
Im having an issue and I want to try to get it down MySQL wise so I can work the PHP end out of it.

howdy fsteves

I am doing an INSERT INTO / SELECT FROM
I have run into an issue that I need to inject some PHP variables into the MySQL statement, but can you use VALUES in the mysql syntax with SELECT FROM?

ERROR 1062 (23000) at line 1748762: Duplicate entry ‘1098628′ for key 1
when i grep through the dump for that number i get only 1 result

You can use this form: INSERT … SELECT …;
Either provide the data via prepared statement or just dynamically build the SQL in php.

better yet
http://pastebin.ca/658480
a href=”http://pastebin.ca/658480″http://pastebin.ca/658480/a
The thing is im working with two different tables
One is a slimmed down version of the other

Yes, you can use static values in INSERT…SELECT. SELECT ‘constant’, `field`, ‘otherConstant’, `otherField` FROM…

hello guys, can somebody help me
http://pastebin.ca/658488
i am trying to do something but I dont know if it is possible

gniretar_work, that key does not match the line you pasted earlier

yea, i just grabed one of the thousands of error messages. I’l grab that specific line and paste it in pastebin. hold on.

if I have a table with user information, one row per user, then a table relating to the user table that holds demographic data with multiple rows per user, one row per item of data, is there a “better” more common way to structure this?

What’s the problem?

the first CONCAT is fine, however the second CONCAT need to use an other virtual_domain that depends on the virtual_user that used

are some examples somewhere of LEFT JOIN inside other LEFT JOIN (bu is it possible ?) ?

I bet some of your data contains nulls and the auto_increment feature is assigning a value yet to be inserted from your dump. When that existing id finally is inserted you see that error. Look for nulls or 0’s in the import.

are there

http://pastebin.ca/658494

so how do I use two different virtuals_domains

No problem, you just need to add an alias for the table the second time you join it.

null and 0 would behave the same.

OK, let be do some searching

…LEFT JOIN virtual_domains AS someAlias

Just a guess.

ah ok

If you can post a testcase that produces the behavior, that would be best.

Depends on the nature of the data, but what you describe is acceptable in the event where the type of demographic data varies for each person.
If you generally collect the same pieces of information about each user, then they can be fields in a single row rather than separate rows for each piece of info.

http://pastebin.ca/658498
like that

right, the amount of data is dynamic, depending on what client group that user belongs to

LOAD DATA LOCAL INFILE ‘D\Price List USD.csv’ REPLACE INTO TABLE `products` FIELDS TERMINATED BY ‘,’ ESCAPED BY ‘\\’ LINES TERMINATED BY ‘\r\n’ and a line from csv looks like SWA0-10-24,Air prize,A table Package,Von,10 – 24,$4.48 ,$3.36 , but all the data cames in a firast field

http://pastebin.ca/658499

you need to use the alias in the ON clause too.
no. $sql = “INSERT INTO x (blah) SELECT `LastName`, `FirstName`, `MiddleInitial`, ‘$username’, ‘$location’ FROM `evac`

just that performance is starting to suck at 1 million users ;(

Perhaps some of the data belongs in a different table?

perhaps, plus the queries need work, just wanted to check if there was another way we could structure it that might give a performance increase

sorry i do not see what you mean yet, can you give some more directions?

the problem lies in this: The table I am selecting from doesnt have the Username or Location fields

snoyes, thanks for the feedback

You did it all correct except you need to use ON virtual_aliases.domain_id=virtual_domains_aliases.id,
I see that. The query does not look for such fields. It includes them from the php variables, as constants for the query.

yes thanks i see it

Hence the different quotes used.

So it will just insert the values passed through PHP, not look for those fields in the table called for SELECT (essentially mixing and matching inside of the select statement)

what I am doing wrong ?

perfect i got it working
thanks for your help

correct.

can someone help me out with an Import SQL file?
it keeps failing

With what error?

#1136 – Column count doesn’t match value count at row 1

That’s pretty clear. It happens when you do something like INSERT INTO table (oneField) VALUES (‘oneValue’, ‘twoValues’);

Hello, I’m using an application that has MySQL as the backend. We can no longer add new records without getting the error: “#23000Duplicate entry ‘50463-0′ for key 1″ — 50432 is the last entry in the table. I can’t find one for 50463-0. I’ve ran the extended repair on the tables, but no
luck. Where to look now?

exactly
see
whats the problem with this file
http://pastebin.ca/658501

moin

moin moin

We’d have to see the structure of the categories table.

its empty

Structure, not data.
SHOW CREATE TABLE categories;

Hi friends.

You’ve done SELECT * FROM table WHERE field = ‘50463-0′ ?

I got a little problem with mysql, perl and utf8 support. one of my columns look like this:
firstname VARCHAR(255) CHARACTER SET utf8 NOT NULL

it gave me this, categories CREATE TABLE `categories` (\n `8` char(1) NOT NULL…

How do I populate BLOB columns in stream mode?

yes. nothing appears.

LastPerson, thats not complete

button to see the whole response.

better yet, use the Export button, export the structure only (uncheck the data) as SQL, and paste those results in a pastebin

categories CREATE TABLE `categories` (\n `8` char(1) NOT NULL default ”\n) ENGINE=MyISAM DEFAULT CHARSET=latin1

then I inserted 4 rows into the table, “Jörn”, “JÖrn” and both with utf8 encoding. when I let mysql compare those, ‘Jörn’ and ‘JÖrn’ are the same, those with utf8-coding are different. why?!

So, that table has only one column, but your inserts try to put in 3.

is there a way the script can be fixed
I mean

by george it works
thanks snoyes!

snoyes I got round to playing with the designer in phpmyadmin yesterday, a few foibles but works

exit

MySQL doest support Blob Streaming does it.

in the BT Tracker files, I’m supposed to import that, and 3 of the same .sql files

DirtyD, someone has a blob streaming engine for mysql

Probably what you want is to fix the table structure. Add the two missing fields.

and obviously, I made the ‘catagories’ table with nothing in it soI could insert but it just doesn’t work
how do I do that?

On the phpMyAdmin “structure” page, there’s a bit for Add [] field(s) () At End of Table…

DirtyD, google MyBS blob streaming engine

That’s the Structure page for the table, not for the database.

I’m trying to store email into a blob. The problem is some of these emails are very large. Is it true that MySQL can only store blobs that fit in mememory, and that they can’t be chunked like with Oracle?

I don’t know what values to insert though

I particularly like how simple it is.

I thought I’d just be able to import that file directly

You have to define the structure of the table. I would have thought the file you downloaded would have included that.

snoyes a bit to simple for some situations (eg a trigger for the FK)

Assuming there’s more than just the data, whatever scripts use this thing would need to know the names of the fields and such.

DirtyD, you would need to set the maxpacket size to fit them, but just store them in the file system for ease
images

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

I have no idea what I’m doing… heh

Hm
So, that query about items that are in one list bot not in another is working, but it sits on ’sending data’ for a while

big list

indeed, however even if I limit it to 1000 results it’s 10 minutes plus
Can I pastebin it to see if there’s something I can optimize?

yes add the show create table tablename for the tables

http://pastebin.com/m1ef77287

ST47, so you are doing it on views why not directly on the tables

hey, guys – I have the problem described here http://www.debuntu.org/port-forwarding-and-channel-3-open-failed-connect-failed-Connection-refused

mm.

however this sollution doesn’t work…

I may not have access to the tables

I already have band-address = 127.0.0.1 on both the server and client
I can ssh into the machine fine – it’s only when I tunnel the mysql client doesn’t want to connect

soulfreshner, check mysql permissions eg..
external

remove bind-address= and skip-networking from my.cnf and grant permission to the external ‘user’@'host’ and remove any firewall rules blocking port 3306 and make sure no overrides on the mysqld commandline

127 is not the same as localhost

is there any trick that will somehow apply your layout to your 500 and 400 errors?
oh crap
wrong channel
sorry

5 doors to the left

ha

archivist, permissions on the remote host?

6 on me xchat

How are you creating the tunnel?

yes

ssh -vvv -L 10000:kbase.sun.ac.za:3306 derick@kbase.sun.ac.za
-vvv for debugging info

Are you ssh’ing into a router or the MySQL server?

into the mysql server

Use localhost instead of kbase…
kbase will connect to host’s external IP, not 127.0.0.1
(which you bound to)

well, what do you know
as easy as that
ta seekwill

np

i have two ways of going about a query and im not sure which seems to be most effective. i havnt finished optimising either of them yet but can anyone give an indication of which way seems most efficient? i have my two explain queries here: http://www.nopaste.com/p/a750yeMNx

would I save disk space if I stored email messages as files or as blobs in a database?

not sure whether to start with the superstar table and then join it to the other two (first query) or start with the svr08test table and join the others (2nd query)

is \r carriage return?

yes \n being line feed

am i right in thinking the first of the two queries is better on that link? as it has 8 2 1 on the rows which is smaller than 13 1 1, and the first table is the one it is primarily ordered by?

well as the first line in the explain does not use an index.. keep working on it

yea im not finsihed with that yet
just does that method of gettin the results i want seem better than the 2nd query/
i wana establish which is best so i can go on to choose the right indexes from there

think about the overall problem not parts of it as you may well change it all again

i am i wana know which of the two is most efficient, cos then it gives me an idea of what angle i shud b tackling the problem from – ie. starting at the superstar table instead of the svr08test table or whichever
i think because i am ordering by superstar.name, svr08test.id, that its better to take the names from superstar first, and then link them with the stuff through svr08test. but may be wrong

madriss, allways select the smallest result set first to reduce the load

ah that makes sense. well superstar has a list of names, and svr08test links those names to the names stored in creator.name
so there will be much less rows in superstar than in svr08test, so i should start there
course there will be more stuff than that in svr08test, im simplifying

no it depends on the select which will produce the smallest set

oh ok

is it possible, using mysql regexs, to update a field and remove all links inside the field?

are there any examples of that? i dont get how a much more huge table can produce a smallest set

www.archivist.info/search/index.php/Sql this only selects from one large table but the selects are ordered

ah thanks

ok guys, just need a quick answer here
if you drop a db, it’s pretty much impossible to recover, correct?

Yep

its what backups are for
hint binlog

Well, depends on certain things

see, I just took a call from a slightly desperate client

only slightly

well it’s hard to tell exactly how bad it really is

Moscow – A Russian region of Ulyanovsk has found a novel way to fight the nation’s birth-rate crisis: It has declared Sept. 12 the Day of Conception and for the third year running is giving couples time off from work to procreate.
bMoscow – A Russian region of Ulyanovsk has found a novel way to fight the nation’s birth-rate crisis: It has declared Sept. 12 the Day of Conception and for the third year running is giving couples time off from work to procreate./b
http://www.denverpost.com/ci_6624865

^Migs^: Yeah, not really a #mysql topic

two similarly named DB’s and you drop the wrong one? I dunno
anyway, is there anything you guys can think of, short of commercial data recovery?

uh, I just thought it was interesting, that’s all, and wanted to share

Any kind of backups exist?

let’s assume not
I know they’ve had issues with them lately, so I don’t know

Look in the datadir to see if binary logs have been enabled.
*.bin files

presumeably those can then be replayed against a backup

I don’t know what would happen if you created the DB while it was logging…

then the creates will be in the log
if it goes back to the start

You can restore your last backup, then provided you have the binary logs, replay the from then
There is a snag that you might not know what position in the logs your backup was taken
uThere is a snag that you might not know what position in the logs your backup was taken/u

ok, that’s prolly enough :&)

how can icancel a mysql query.. i type soemthing and now it lagging my server
mysql work at 100% now.. i need a way to cancel my query please

connect in another session, then do show processlist, and kill the offending thread with the kill command

Vanpriest, ‘SHOW FULL PROCESSLIST’
then use ‘KILL’ to kill the process.

tat in mysql console right?

Or follow what MarkR42 just said heh.

Yes, in mysql command line
Or in MySQL 5.1, just press ctrl-C (nice new feature)

wondered if this is normal behavior for mysql 4.1, i restored a table on a mysql slave (who is a master to some other mysql db) and it zeroed the position of its master in the relay log, is this normal?

wait, ctrl-c does what now?
you can kill the query?
I wish CTRL+C worked like it does in, well you know, A SHELL.
:

dan__t, open another shell, show process list; kill the query

er, yes, that’s what I just said
I’m just bitching about my wish list.

they’re gonna recreate the db :&)

and learnt a lesson

how do i free memory after i kill tthe process, the mysql seem to still lagging even after i kill the query

OK, this issue is VERY strange

Does it show anything else under SHOW FULL PROCESSLIST?

its erroring out on the first line of the data dump

If you killed the query, it’s dead. It is no longer running.
I think you should worry about the query rather than memory.

how do i change the column seperator when piping mysql to stdout? by default it uses a tab, i want to use a | or something

Memory won’t make your shit run any faster if you’re running hardcore queries.
I’m not sure you can do that, shadfc.
How are you piping the data?

Memory will make almost anything faster, especially if you tune your mysql riht

file.out

ie. it’s in batch mode

shazow, using SELECT …. INTO OUTFILE might work better for your purposes.

KILL command tells the thread to quit, it frees all memory it was using when it quits
it doesn’t always quit immediately but usually fairly soon

mysqldump may have some other options that you would be interested in, too.

how do i deal with the column separator though? because some of my data has tabs in it, so importing into a spreadsheet breaks things

yeah.
Tell the spreadsheet to ignore tabs as a delimiter?

of course having a very big query can blow your cache away which will temporarily slow your box down because thigns which were previously cached won’t be any more.

then how do i parse the columns, if they’re tab-separated?

CSV?

there are commas in the data too
regardless, how do i make it comma separated?

INTO OUTFILE will do that.

The CSV specification details how to place properly embedded commas in the data.

hm alright

You can use FIELDS TERMINATED BY and LINES TERMINATED BY and stuff like that.
YOu can make your own delimiters for dumping data.
And, again, mysqldump might have some options

You’ll have to check whether INTO OUTFILE conforms to the CSV specification in that regard.

You can make it do so, as far as I understand.

ok
thank you

I have question related to temp tables.Is it better to create seperate stored proc to : CreateTempTable and than InsertIntoTempTable than third proc to move all data from temp table to actual table and remove temp table?

It really depends what you want to do
What your motivation is for creating the procedures in the first place

is there a way to reset autoinc IDs?

Yess, you cna use ALTER TABLE

I have a table that contains data. Than on every new restart of my program I want to download new data from server and place it to temp table

temporary tables reside ON the server
just like permanent ones

Well server I mean external server. Nothing to do with mysql

Right, well, you will have to load it with some INSERTs, LOAD DATA etc

All I want to do is to download new data( be sure that I got it all with out any errors) before truncating data from main table replacing it by data from temp table

FIELDS TERMINATED BY + INTO OUTFILE did the trick, thanks all

There is generally no advantage in doing something in a stored procedure unless it reduces the number of queries or it’s called from a trigger, event etc

Im trying to setup a schedule in a table with a stop and start date which are both datetime fields, How do I check to make sure there isnt another start and stop date that would conflict with one already in the table?

You could use transactions if you are using a transactional type
and roll back the changes if you encounter an error

I’m starting to draft-out a new database, and it’s likely to become big (lots of tables). some tables have little to do, but not nothing, with the others. I’m thinking about dispatching the tables among many databases, what are the pros and cons of using more than one database ?
(don’t quite know how to google that)

I do not have transactions on this table becouse there is no need for it.

You can use INSERT…SELECT syntax to bulk load data from one table to another, regardless of whether they’re temp or permanent

crap on a cracker. i’m trying to adapt groupwise max to a sales records calculation. works fine on the assumption that i want the latest records, but i can’t make it work relative to a particular date (if i’m generating a report on data 6 months old, i don’t want a record amount to show if it’s
only 3 months old). see http://pastebin.ca/658658 for queries/comments.

It really depends exactly what you’re doing. If you think you will have too much data for a single server to hold, or you have too many inserts or other queries, yes, maybe do that
You can logically partition your data amongst several physical servers, but it makes querying signficantly more “interesting”

Anyone has an idea why I can execute the following query with phpmyadmin but not from php with mysq_query, then I get a script error…. Here is it: http://phpfi.com/256467

Roughly what sorts of scales are you planning on doing this on?
We have around 40 servers in our production system with our production data spread across them
there are many terabytes

it really depends on the tables. some table will be constantly hammered with inserts/updates/selects, while others will be almost static.

Is there a way to create temp from actual table? Well both of them are the same. Is there something like create temporary table `tempTable` from real table. This way if I change the main table I do not have to change the definition of temp table? do you know what I mean?

Probably your best bet is to put all of the rarely changing data on to one server, and the current live changing data on another,
That’s certainly what we do
We have a single master configuration database which has (relatively) few queries
(i.e. only a couple of hundred per second, mostly selects)

may I ask how you manage to backup, or keep safe, all of this data spread over 40 servers ?

The master configuration database we do have backups of and also have readonly replicas

this happens to be a major concern here and our DBMS knowledge is a little over average

The dynamic data we have no backups of, of any kind
Our dynamic data are simply too large and too dynamic to be able to feasibly backup with cost effective technology
I suspect there are 40 Tb +

is the dynamic data vital in any way ? can you even afford to lose some of it ?

But the whole lot changes every 60 days
They are important to some extent, but we can’t reasonably back it up
even if we could back it up, some would inevitably be lost upon failure of a single compnent
unless every single server had a replication slave, which is something we might consider in the future

I see. consistency problems I presume

Is it possible to make sure 2 date ranges dont overlap?

consistency is not a major issue, as these data are fairly flat
there are in fact, no joins of any real kind in these dynamic data

Gallery only offers widths that are at most as wide as the original image

uh-oh. I looks like port 80 is blocked, or not forwarded properly
Guy-: interestin

me?

you are familiar with portforwarding and starting the apache2 server

however, the copyrighttext mystery remains, I’m still working on it

yes, you.

who is fajita referring to.

don’t mind her for now.

oh ok.

her? I always thought a fajita was an ‘it’

no, it’s definitely a girl.

call it her out of ‘respect’ xD

s/it/she’s/

I’ll take your word for it, but I can’t help wondering how you tell, with a bot

does ‘it’ talk too much?

she

she is a thousand miles away
how rude some people are:-(

Guy-: us here have a close relationship

haha

anyhow

Hi thumbs

that was funny

since you seem to be using port 80, and that most ISPs block it, consider using another port.

har har

oh man

thumbs ;p
yeah

what?

thumbs, more of a love-hate relationship, eh?
3

any port?

any port is 4good. as long as it dosen’t clash with a port in use

Hey, test
(am I alive?)

gman`: apparently, yes

sweet!

like sugar, baby

hush fajita

shush

huh?

hush

I have a dumb, but hopefully easy question regarding mod_rewrite… and I’ll bet you get tons of those every day :$

gman`: you should ask it.

I’m trying to get *all* url requests in a directory to be sent to a php hosting file as a query

that’s easy.

www.mysite.com/somedir/index.php?src=blah.jpg

but transparent, so the end user just sees the first URL
yeah, I can’t seem to get it to work though
RewriteRule ^(.*) index.php?src=$1
(thats my current rewrite rule)

garbage

try putting junk in your htaccess file. If it’s being read, you will get an Internal Server Error when accessing that resource.

gman`: make sure the htaccess file is being read first

yeah, its being read
I mean, when I request that URL
what happens is my PHP file reports back to me that $_GET['src'] = index.php
which just boggles my mind

can you give us a sample url?
and did you put RewriteEngine On in your htaccess file?

yes
sample as in for you to view?

yes

ok, hold on

did you see my last message thumbs ?

no

i think i changed my port to 8080, but now i get a router login page when i try to access my dns site from the other system on the remote LAN

your router is most likely using 8080 for administration purposes
try 81 instead

okay

never mind which port, just pass it!

wow that’s really annoying
is the port specified in httpd.conf also, or just vhosts.conf?

that depends on your distro

ah, makes sense
i’ll check both

ok
got it
http://www.phpleaders.com/cms/web/mm_travel_photo.jpg

gman`: it seems to work here
gman`: I see php code

Debug mode enabled…
right?

?

gman`: yes

fajita debian is the best!

….but debian is “we complicate it for you so you don’t have to” or see /usr/share/doc/apache*/README.Debian* or http://wiki.apache.org/httpd/DebianLikePlatform…

yeah, but if you scroll down, the $_GET is wrong

gman`: ok
rewritelog

rewritelog is http://httpd.apache.org/docs-2.0/mod/mod_rewrite.html#rewritelog http://httpd.apache.org/docs/mod/mod_rewrite.html#rewritelog. Use this logging to help design and track rewrite; see also rewriteloglevel; and cannot be used from .htaccess

rewriteloglevel

rewriteloglevel is http://httpd.apache.org/docs/2.0/mod/mod_rewrite.html#rewriteloglevel. RewriteLogLevel 9 is log everything…

with that url the $_GET['src'] should be “mm_travel_photo.jpg”

ze php, she scares me ;p

same luck with port 81

lol

try http://localip:81

it tried to resolve it to http://localip.copm:81/ but still no luck

…. .com?

yeah sorry fat fingers

is your server even listening on port 81 now?

not sure, all i did was forward the port for apache

you need to change the Listen directive, and restart apache.

do i need to restart apache each time i change the port :-/ lol
didn’t even think of it til now
oh man, gotcha
hm, from the system running apache, firefox gives ‘Not Found The requested URL / was not found on this server Apache Server at dave.homeip.net Port 80′
even though i though i changed all my directives to 81

what does the error log say, exactly?

for every rewrite rule, do I need a rewrite cond?

(error log is located..) :-/

gman`: in some cases.
defaout layout

sorry…

/var/log/apache2/error_log

what about in this scenario where I just want to redirect everything?

nothing of notable interest in the error_log
there’s no real errors in the error_log

Good evening / Bonsoir

ugh, still no luck.

greetings

greetings user

it changed from ‘The requested URL / was not found on this server.’ to ‘Firefox can’t establish a connection to the server at dave.homeip.net.’

that probably means apache is not started

i’ve done ‘apache2 -k restart’ and checked the error_log and says ‘resuming normal operations’

I’m sorry, I have to run
ask any other helper, they will give you a habd

no worries, thanks for everything.

make sure it is running, with “netstat -pntl”

is that command included in the apache2 command?

no
it is a linux shell command

oh duh, yeah
1 0.0.0.0:* LISTEN

so apache listens on port 81
are you testing it on port 81 ?

http://yourserverip:81
is the way to access it

thanks for trying to help me out earlier…turned out to be neither apache nor firewall issues, but conflicting static IPs on my network…take care

can i access the page from the browser within my LAN?
i’m getting a timed out error on remote windows system.
but i can see the page perfectly if i access it from my linux machine.

well davo http://laniptoserver:81
like http://192.168.0.3:81

hrmm, I’m having no luck with mod_rewrite for something that ought to be pretty simple

gman`, rewrites are never too simple..

lol
well…
I want http://www.phpleaders.com/cms/web/mm_travel_photo.jpg to rewrite to http://www.phpleaders.com/cms/web/index.php?src=mm_travel_photo.jpg

correct. that does work from the browser on the linux box, but not any remote system

Currently using “RewriteRule (.*) index.php?src=$1″
but the php file receives $_GET['src'] = “index.php”, not “mm_travel_photo.jpg”

gman`: phpinfo() will likely show you which env var to use for the rewriten url

gman`: btw, you really dont want to do that,

why?

because

yeah that’s a bad idea

why so?

looping

looping is http://rewrite.drbacchus.com/rewritewiki/Looping

gman`: your machine can get pwnt

ok, so what if I put in the rewrite an exemption for php files?

does work. Does this make sense?

also in your php you gotta becareful how you use that src variable

yeah, to avoid src=../../whatever_file_in_home_dir

gman`: not really

something else, related to looping?

gman`: you can put in http://someexploitedserver.co.communist/ownedlololol.php

Hi, I was in here a few days ago and had someone link me to a nice 2.2 or higher only solution to having apache have only one file serve all content for a virtual host or directory that didn’t use mod_rewrite… but I lost the link. does anyone know what it was again ?

well it doesn’t process the file
it just outputs contents
so it won’t run any php code

1′ works on my local system, is something mixed

http://wiki.apache.org/httpd/RewriteVirtualHandler

…..(oh that was bad)
^^ should have been ‘http://dave.homeip.net:81/‘ works on my system

Action system will pass path info, right ?

You could presumably use ErrorDocument for that, but it wouldn’t work for POST data.

DrBacchus, exactly
DrBacchus, I’m trying to store the bulk of my code outside of my document_root for security reasons

is it possible/normal that “PerlSetVar Foo” works inside Files, but “PerlSetVar Bar” doesn’t?

how can i do to replace with apt-get the config files?

am i doing anything right with this DNS stuff?

this is a mf stuff apache2 doesn start

so question
RewriteCond %{REQUEST_URI} !^index\.php(.*)
RewriteRule (.*) index.php?src=$1 [L]
that should NOT be activated if the .htaccess file is in mysite.com/somedir/ — and the url is mysite.com/somedir/index.php?blah

davo dunno, what did you do ?

hi. need help with a caching issue. i generate thubnail images with php, and it happens that there is a new image with the exact same filename. now the brwoser only get the new image on manual reload. can someone point me to the right point to tweak to fix that ? it’s rather unhandy as it is now

you can set a no-chache header, but it’s up to the browser to honour that

naturally. what i don’t really understand right now why it happens at all. from the server’s point those are just plain images stored on the filesystem that change once in a while .. shouldn’t the browser check if cached images are still current and get the answer that it changed from apache ?

erm.. no, HTTP doesn’t work that way

I have a little problem with the server-status. It doesn’t work in local but : “Allow from localhost” is in… Anyone have the solution ?

okay then .. mind to explain where i’m wrong ?

Questions of the form “what’s wrong with …” can’t be answered unless we know what you’re trying to do.

it depends on the browser actually doing this check, and the default is to set some kind of expiry (ergo caching) for all content that’s not marked as dynamic, so as to reduce bandwidth
so to have the browser even get the message that it should check more often, you’d have to set a non-standard header value
and even then, it’s all up to the browser what it does with that information

tough cookies

Julia does it work anywhere else ?

okay … how can i set that header for a image file ? i know how to do that for a dynamic php page but never treid for a semi-static file

why would it be any different ?

I want it work with a Cacti script and normally it 403 Forbidden when i’m not in localhost
sorry i’m french

Julia what does the location block look like ? put it in a pastebin, not here
also,

also, is it the same person possibly

step one

whatever the problem, step one is to look in the error log (and any other logs that may apply, such as suexec, mod_rewrite, or mod_security).

I’m sorry Cacti take the IP of my server and not “localhost”… (The next time I read the #apache topic :p )

you also need to switch it to terse output specifically, for cacti to make use of it
byou also need to switch it to terse output specifically, for cacti to make use of it/b

Just a last question “Allow from localhost” If I want allow 3 hosts, what is the syntax ?

graphical plotted apache http goodness

noodl, using the Location/Action method you suggested, which I really like the sound of, I get the following error: Request exceeded the limit of 10 internal redirects due to probable configuration error. Use ‘LimitInternalRecursion’ to increase the limit if necessary. Use ‘LogLevel debug’ to get a backtrace.

Julia just insert more lines

Oh nice Ty for your help

let’s see your config (and any htaccess files you might have, in case they’re hiding rewrite rules) http://dpaste.com/

Very very well OH YEAH Have a nice day (I think :p)

I’m sure I have no rewrite rules at all
http://dpaste.com/hold/16068/
for the offending virtualhost, I’ll go paste my main config

you don’t have ‘virtual’ set, though i’m not sure if that’ll help
without ‘virtual’, action only works for requests that match existent files

1′ does not connect and http://dave.homeip.net doesn’t connect from another system on the same

http://dpaste.com/16069/
noodl, I set virtual and I tried not setting virtual

davo how have you ensured that you can resolve the hostname locally to your internal IP ?

1/’ on the linux box gives ‘Firefox can’t establish a connection to the server at

which linux box ?

noodl, adding virtual back makes the exact same result happen

the one i am on right now and trying to host the dns page.

“dns page” ?

well, i dunno the exact terminology.

well, better get crackin’!

i only have one linux system, and then a windows system on the same home LAN.

networking is a rather exact science
use dyndns’s port forward option to be able to use port 80 from the outside
unless that’s not possible on free accounts

Hi lads and lasses… Is there a module or something I’m missing? I’m trying to run som CGI apps in my cgi-bin, and I’m just getting the binary app data instead of apache running the file
I set chmod to 755 and I still just get the data. :/

nothign to do with FS perms
cgi

cgi is http://httpd.apache.org/docs/2.0/howto/cgi.html or http://wiki.apache.org/httpd/ScratchPad/EnablingCGI

hm, i have no idea. i didn’t know doing a simple webpage from a DNS was such an undertaking…

go read the second link

errr

the cgi?

the cgi is !python, but that shouldn’t matter

This page does not exist yet. You can create a new empty page, or use one of the page templates. Before creating the page, please check if a similar page already exists.
Useful link! ¬_¬

davo a simple webpage is indeed pretty simple, but the way you think it comes “from DNS” already indicates that you don’t really understand the technologies involved

before, only AddHandler in vhost context. it’s not surprising that it’s looping.. do you now regex lookahead assertions?

indeed, i admit 100% i have no clue what i am doing, but i didn’t think it was a crime to try.

1. outside DNS must resolve to your IP, 2. inside DNS must resolve to inside IP, and 3. you must have arranged the appropriate access to apache
by whatever means necessary
you seem to have covered 1., so you’re stuck on 2.
which is odd, since most people would attempt to get hat working *first*

hm, i was able to do the first two until i switched apache from using port 80 to port 81, then all hell broke loose.

how did you switch apache, exactly ?

noodl, I don’t know much if any about regexp

servertokens dave.homeip.net:81

Couldn’t get a useful value for http://dave.homeip.net:81

in the 00_default_vhost.conf
changed all 80 values to 81

it looks like you have more problems than you thought, since 1. isn’t working either

1 was working, until i changed 80 to 81

davo, try something in the 2800 range

so why did you do that, then ?

ok well the issue is perhaps that there’s a loop happening because your handler matches your handler. i don’t think Location (or LocationMatch) support negation, so you’re left with using a *clears throat* negative zero-width lookahead assertion. see man perlre for the syntax, or shout and i’ll look instead
ok well the issue is perhaps that there’s a loop happening because your handler matches your handler. i don’t think Location (or LocationMatch) support negation, so you’re left with using a *clears throat* negative zero-width lookahead assertion. see man perlre for the syntax, or shout and i’ll look instead

or, alternatively, get a decent ISP

someone here suggested changing 80 to 81 since perhaps my ISP wasn’t allowing ports to forward on 80.

http://wiki.apache.org/httpd/RewriteVirtualHandler

my ISP isn’t decent?

ok, i wrote that page, so if it’s wrong then blame me

oh, okay

does anyone here live in the US and use Cox ISP (no jokes about the name, mind you)

davo if they don’t allow you to run the services you want, then no, it’s not a decent ISP

noodl, I think the rewrite one may work since index.php should be the only file in the document root, except of course for an occasional temp file…

but how can i be sure it’s them that is not allowing me to run the services that i want, and not just me being a total n00b and not having a clue that i am doing it wrong.

try Action foo-bleh /script.php virtual // SetHandler foo-bleh outside of Location context (just in your vhost)

easy – switch it back to port 80, verify that your internet connection is forwarding the port to your server, and ask somebody to check

ok cool, i’ll try that again

still gives error

okay cool, i can see my page on my local host
http://dave.homeip.net:81 works on my linux box

well, yes – but what does that prove
servertokens dave.homeip.net:81

Couldn’t get a useful value for http://dave.homeip.net:81

but it doesn’t work on the windows system
no it’s http://dave.homeip.net:80 now

why would you expect it to ? I suggest you stop guessing and start applying the scientific method
test, verify, change, repeat

noodl, I think the re-write rules are best

they often are

noodl, without the rewrite_condition, they still work, too

what else do i need to verify?

i’m just off to bed but i’ll try and verify that Action stuff tomorrow

noodl, okie, thanks
noodl, before you go
is it possible to use .. in a rewrite rule ?

uh, .. in a regex means two of anything

oh
aah, absolute paths work just fine

‘cept LF

(.)(.) — with memory

norks have memory?

so the file name . means “match 0 or more characters except LF” ?

servertokens http://wootgnu.org/ondve/

Apache/2.2.4 (FreeBSD) PHP/5.2.3 with Suhosin-Patch mod_ssl/2.2.4 OpenSSL/0.9.8e

ehh
servertokens http://wootgnu.org/ondve/

phear

there

norks++

Apache in disguise

sporks++

mod_security for winxp

there are no norks! there is only.. SPORK

lol

duh

A child of five could understand this! Fetch me a child of five

I’ll spork-feed your eyeballs to a child of five!
we need weirder factoids

spoon

Because it hurts more, idiot!

that’s rather straightforward, ’spaz

child of five … interesting family life there

Hehe.

Hehe. is that the sort of thing mod_wombat/mod_lua will do too? hopefully with a less terse syntax?

you try digging out someone’s heart with a spork. then tell me how straightforward it is…

it wasn’t me – it was the bot ! (mimicks Will Smiths unendurable fakeness)

s/spork/spoon/

servertokens dave.homeip.net:80

Couldn’t get a useful value for http://dave.homeip.net:80

servertokens aarcane.strangled.net

Apache

servertokens ichigo.aarcane.strangled.net

Couldn’t get a useful value for http://ichigo.aarcane.strangled.net

hrrm
fajita must not have IPv6

excuse me?

ah so it’s not just me

afais, fajita got your servertokens

servertokens aarcane.strangled.net

Apache

it just looks like you’re borked.

megaspaz, but not for ichigo.aarcane.strangled.net

ya think ;P

ah missed the ichigo one…

ichigo is only accessible via ipv6

davo why not start by trying the steps you have been offered up til now ? do the port forward thing on dyndns, get your internal DNS figured out, and (optionally) set up iptables to pull things back to normal
aarcane so it’s a different apache instance, then ?

not necessarily

adaptr, yeah, it’s my internal testing server for working on stuff

the bind isn’t done on vhost level, it can’t be – it’s done on the Listen

yeah
well mebbe not
namevirtualhost ipv6:80

i am trying as best i can to apply every step that i have been offered. i know i am not anywhere up to par with any of this, but i thought i am trying to learn from my mistakes.

namevirtualhost ipv4:80

and mebbe yes, trust an old TCP nerd on this

with different listen directives. i don’t see why you’d need 2 apache instances running

you could set that, fine – but TCP doesn’t care, it will bind to either or – and always to either or

i have the apache port fowarded in my router, but not sure how to do the portfoward thing on dyndns.

apache has no ‘fluence on that
davo look around the settings for it

you should probably start trolling around the dyndns panel

“trolling” ? heh

okay fair enough, this troll can take a hint. thanks again everyone.

trolling == exploring

erm…

well in my context

not from my experience. but that’s fine, i get the jist.

heh

anywho, thanks again.
btw, there is no dyndns channel

I call it “incontextinence”
he didn’t say channel, he said panel – the web thingy you use

of course.

Hia ll, I think that my SSL configurations are wrong (phpinfo tell that the port used is 80 and the DocumentRoot is not the one that I set…) My site is hoste on my Personnal PC on an address located at dyndns.org. Anyone have an idea ?

not really, no – except that SSL usually uses port 443, not 80
step one

whatever the problem, step one is to look in the error log (and any other logs that may apply, such as suexec, mod_rewrite, or mod_security).

it said “You configured HTTPS(443) on the standard HTTP(80) port!” but In the config file I used the 443 port…

there are several places where you must configure the port

I put it only in the file /etc/apache2/vhosts.d/00_default_ssl_vhost.conf

ssl

ssl is *Secure Sockets Layer. Ask me about mod_ssl or ssl vhosts see also http://httpd.apache.org/docs/2.2/ssl/

finally someone doing exactly what i am hoping to do

hardly, unless you mean misconfiguring apache

you know what i mean ;p

well, no – your issue was DNS-cluelessness, and port problems – not configuring SSL

okay fine, anyways.
i was directing that towards metres, perhaps he might understand my situation.

and we don’t ?

oh i am sure you do, all too well.

well, to be honest, I *dont’* exactly know what your situation is, because you haven’t exactly made it easy to figure out

what way do you guys recommend to identify a ‘leaking’ apache process? it just eats up all system memory and all the swap, taking down the whole server remarkably.

tibyke most likely PHP, not apache
tibyke but you could strace apache or mod_php, or whatever you have running

adaptr, yep, sure, it must be php, but just cant identify it. apachetop and/or server-status doesnt help too much
its mod_php4 shared apache module
strace the initial apache process you mean?

if you have to, yes – although I don’t know how much that’ll tell you

im afraid not too much

you could log it and grep it for memory calls, see if something doesn’t free()

i can try starting to disable each vhost one after another, and see what changes (for e.g. a couple of minutes)

actually, it’s also very unlikely that mod_php causes leaks – it will be a script you are running

of course its a script, but thru mod php, it must be a user error of a php coder

eliminating vhosts may be the first step towards isolating it, of course

I’m using apache 2.2.3 on Debian. I have a perl fastcgi script that apache calls, but I want that script to run as a different user from what apache runs as. I’ve tried created a wrapper shell script that just has “sudo -H -u myOtherUser /path/to/fcgi_Script.pl”, but that doesn’t seem to be working. Can anyone suggest a better way?

okay, okay, i’ll stick to that, thanks

is it related that I configured ServerName metres.homelinux.com:80 ?

do not put the port in the hostname.

thanks thumbs

however, that will not prevent apache from functioning properly
it’s just a bad habit

now phpinfo told me that I used port443

that’s an improvement.

Do the DocumentRoot define in the SSL section is useless ? when I type my address with https, does it supposed to use this directory ?

it will use the documentroot of your SSL vhost.

So i’m still having an error somewhere…

how so?

Hmm… strictly functionality-wise, is there any difference between Alias / /path/to/root and DocumentRoot /path/to/root?

no
however, Documentroot is required in a vhost.

it show me the http directory when using https…

faq1

exactly. For example, NameVirtualHost *:80 must be used with VirtualHost *:80

Really? I’m pretty sure that I’ve seen a bunch of vhosts without documentroots (that didn’t give an error).

well for good measure.
it might work now without one.
using an alias is awkward either way

Yeah- I just switched them all, just making sure that I don’t change anything

Hi all

All is not in today, pibefision

hey, is there some way to check for some sort of “flag” in a URL, and use a rewrite condition only if that flag is not present? – but strip it before say, a PHP script sees it?
(well, in a request_uri)

Apache using 280M+, is that normal?

it could be

Can someone help me? I’m having problems setting up an Apache2. I’ve created a new user, and wrote a directive on http.conf to a new dir, on that user domain /home/newuser/site/htdocs. All the files are owned by the new user, and my apache runs as apache user. I’m getting a permissions problem when i try to connect. (403). What must i do? make the user from the apache group???? how

what does the error log say, exactly?

403. Forbidden. Cannot read from that directory.

what does the error log say, exactly?

I’ve chmodded all the files and dirs to 755 also.

what does the error log say, exactly?
are you gonna ignore me one more time?

OK thumbs, let me see.
2 2007] [crit] [client 200.114.197.233] (13)Permission denied: /home/dezeos/.htaccess pcfg_openfile: unable to check htaccess file, ensure it is

(13)

(13) indicates a file permissions problem. Make sure that Apache can read the file(s) and directories being accessed. See also http://wiki.apache.org/httpd//13PermissionDenied

^^

you rule, gonna check this. Thanks.

see, less guessing, more troubleshooting.

Tks. Finnaly solved. Whas a permission problem on the dir. Thanks THUMB!!!

no problemo

you around?

yep

would you be willing to give me some advice on how you got dyndns working on your personal pc?

no problem

if not, i understand
woudl you send me a msg pls?

have you got it ?

yes, have you got it too?

nope

i got your msg and replied 2 times
please try again

/join #MetresAndDavo

okay will do. thanks

how can i parce all html like php
?
all whatever.html like php document
with .htaccess

with .htaccess is it possible of limiting put, post, and delete in webdav?

hello

hola, roger_padactor.

i just started using ubuntu. i’ve used slackware before, and installed apache and all i want to be able to do is add and edit files in my www dir so i can start doing my web stuff. but it wont let me any ideas?

you need to set yourself a specifc goal.
php

!lart geaaru

ugh. / with nodev might work as long as you’re running udev, but if it fails…
uugh. / with nodev might work as long as you’re running udev, but if it fails…/u

Yeah, but I still often have to scroll up several pages

!lart

woot – 4.0 running in VirtualBox on XP in 128 MB Ram…Hot

having the window bigger than 80×20 helps too

no, it’s w00t

wot?

right

1280×1024

no, in that case, it’s probably more wtf

not big enough ;p

characters?! wow.

heh, indeed

jelly-home: pixels

j/k, i’ve got a 128×45 window on irssi here, on 1024×768 screen

thk for your help

whatcher done with the 3 lines missing?

Using Konversation, here

actually, lemme check
128×35

ah
128×48 is the size of the console with standard font size

!hug KDE
!smite GNOME

you must have a big hand
and no aesthetic values

jelly-home: mm, i don’t like it too tall, but plenty wide

buy a widescreen monitor!

send me one ;p

The only two alternatives to KDE when it comes to beauty are Enlightenment and fvwm-crystal

lots of american girls fit that description too

E is unfinished and fwvm-crystal is too minimal for my taste

heh heh

jelly-home: what widescreen monitor?

hey MangosDebian are you around?

man, people with $$ to burn

Can’t afford that big monitors

dunno, one that is plenty wide but not too tall.

jelly-home: no, I was meaning the description of american girls == widescreen… but maybe that was a bit too obtuse.

oh

s/obtuse/unfunny

/

well my remark wasn’t that funee either. But as the late F. Mercury would say, fat bottomed girls make the rocking world go ’round

s/girls/boys

suit yourself

er, no, freddie mercury /should/ have said that

yeh, freddie was two spirited
er, gay, as you’d say

G4 in a beige box is teh roxor. my OF promt is 10x faster. lol kthx

hehehe
forgot the ‘byez’

now you make me envious, my OP prompt on an Ultra 5 is sluggish
bai

even better

now to rid myself of this ugly powermac shell

icanhasframebuffer?

ooh an ultra 5. i had an ultra 5 and 10 back in the day at nortel – excellent machines

do they even exist still?
(nortel

they sure do

dunno, i left in 2002 , unfortunately handed notice in a day before redundancies were announced
bdunno, i left in 2002 , unfortunately handed notice in a day before redundancies were announced/b

if by “excellent” you mean, crappy IDE and PCI – PC knockoff, then yes

sbus whore

yeah, but built like a volvo!
you could chuck one out the window and still boot solaris
ide is crap, admittedly, mine had scsi disks and everything was on the network

burnt my scsi controller on one… apparently scsi is not hotpluggable O:-)

some isn’t no
the bus and drive have to support it

jelly-home: you yanked a regular 68pin or something?

ya!

hehe

sigh

i’ve done it on non-hotplug scsi, but i took the device off the bus first, then spun it down, then unplugged it, carefully but quickly
same moron from before

you could have left the ban, debhelper
or did it…
oh it did

it’s the same ban
did it on the same line

never seen debhelper ban anyone before

it’s rare, but nice to see

adding an extra pin to the Pentium M to stop normal socket 478 use is just mean.
they’re so cheap and sexy

wire cutters

or silly adapters

it’s a bit sad to think about how much time and effort has gone into writing a bot that can kickban based on a join flood… how many more useful things could that time have been spent on *sigh*

with moronicity comes having to do that shit :/
some people can’t control themselves…
or don’t want to

The snapshots failed to compile

hmmph.

plenty of less useful things too

the mill

I can think of much bigger wastes of time/effort
e.g. war

for sure

there is that

!start a war

from memory, start a war is GWB says you have WMD!!!! Mwhahaha

pastebin.ca/66510
pastebin.ca/665410 *

people is apt-move right way to create debian mirror?

erm, you could use that, but there’s better ways

pastebin.ca/665410 third time lucky
ugh http://pastebin.ca/655410

whatever has Iraq done to you?

simonrvn, yes I know rsync is better, but it’s give error.

i wasn’t thinking of rsync actually

debmirror?

for instance

hmm… lots of dodginess in the new realtek code?

“Lost terminal” == “shit, i didn’t mean to close the $^*%#$@ close box!!!!”
er, click the … bah

“Lost terminal”… hey, who moved the VT420 off my desk?

the new realtek code looks like it might be based against a different kernel.

jelly-home: hehe

jelly, oil
jelly, and terrorism, also made some companies very rich
jelly-home, only at the cost of a view lives!

So what do I do? I need sound

bah .. the etch bacula-director-postgres package is messed up … found two bugs in it already, nothing serious though

has anyone got DSPAM working on a debian box?

noone has oiled me. sadly.

jelly-home: heh heh heh
no. i tried and tried, and finally gave up on it and gave it the old -P

i know a few people using it

simonrvn, yeah i had some trouble with it too

i must be stupid then

im thinking of building a *shudders* ubuntu box just for it

how are you trying to use it?

petemc, anyone on this channel?

dont think so, exim peeps

the lure of the dark side strong is, yes

etc.

just tunneling the emails to dspam i guess ?

i’m still using exim4 heavy + ldap + spamassassin, heh
oh and clamav

dont they stuff on their wiki about postfix integration?

they do

petemc, i gotta do more homework on it
hmmmm they do now?
hrm, ill look into it

has postfix, exim integration

what about couirer
because i use maildrop etc…

don’t remember seeing it

as I said before… there’s nothing more that I can suggest.

rather than throwing all the emails into one big file (slow)

check though

i use maildrop – courier etc

Know anyone who knows a bit about ALSA, themill?

#alsa

tried their any of their mailing lists yet?

jelly-home: Nope
Been in there since last night

can I start creattion be apt-move then continue update with debmirror?

hi guys
i keep getting segmentation fault with my apache….what is the first step to solve this?

check your hardware, memory and disk

try running apache without any extra modules loaded and see if you can narrow it down to one particular module or directive.

k
i will minimize modules, thanks

k

argh

y

this is a debian package of apache?

hello!

hi

no it is xampp

k

latest xampp

I must go to take my olaunch
I must go to take my launch

one more try ambra

that’s not going to help… and it makes it pretty much impossible for us to help you.

going into space?

….

!xampp
!google xampp

themill….alright, i am running xampp
8 2007] [notice] child pid 3507 exit signal Segmentation fault
9 2007] [error] server reached MaxClients setting, consider raising the MaxClients
2 2007] [notice] caught SIGTERM, shutting
3 2007] [notice] Apache/2.2.4 (Unix) DAV/2 PHP/5.2.3 mod_apreq2-20051231/2.5.7 mod_perl/2.0.2 Perl/v5.8.7 configured — resuming normal

hi, is there any way to have “sort” return only the first 8 resulting lines? (if needed with other command line tools)

what a plum

man head

I couldn’t find anything about it in the man page…
ah yes

head -n8

waves even

I got four modules that all use eachother and thus can’t rmmod them
What do I do?

thats sane, going over max-clients segfaults apache…

the command line is so powerful, with sed perl and all other tools, I love it

not necessarily related

they can’t use “each other”, not really possible to have circular kernel module dependencies

actually… how is an apache child segfaulting only a “notice” in apache’s error reporting?

jelly-home: Well, maybe there’s one way out of it, but I just did rmmod -r

and it worked?

Yes
But after re-modprobing it, I still lack sound

can anybody help me out with screen resolution problem in Gnome?

my rmmod manpage doesn’t say anything about -r

please describe your problem

norbert_, i get maximum 800×600 resolution availabe in screen resolution selector, while videocard and monitor both support 1024×768 resolution too
norbert_, so, i want higher resolution, but don’t know how to get it

you could try running: # dpkg-reconfigure xserver-org
xserver-xorg

I wanna to install iptables on a red hat and I get notice: Checking for new apt-get details: Password: and after entering
password, Host key not found. Any ideas why I can’t install iptables on
a linux server host ?

try #redhat

sorry this channel is for debian

Any .usa citizen with a tv card?

lots!

I believe the top level domain is .us, Davidos

u?

!tell Davidos about u

!U

Damnit Jim! It’s YOU. Y-O-U. Not *U*. U is a letter. YOU is a word. See ne1, or wud. Dutch for ‘you’ (formal singular). See http://ars.userfriendly.org/cartoons/?id=20041201

no

But ask your question

the redhat’s apt use rpm technology and has a different philosofy than the origianal apt

Someone who has, PM me, got some offer\

what kind of offer?

do you have a question about debian?

No

then wtf?

goodbye, then

Davidos, has big tits, blonde, tight ass? then im your guy!
……………..

all the above, but a penis as well
erm, to clarify, i wasnt talking about me

there was a radio phone-in this morning about emigration from .uk, /many/ coming to .au

that’s because this summer has been so shit

!wasnt

s/summer/last ten years

:%s/summer/last/

yeah, well. I can’t comment on anything prior to last summer

this country is fucking going way down the pan

*pom pom* me like command prompt, me go hack

we always have quit a large number of english emigrations

can’t wait for spring

which country, so i can avoid it?

since about 1788

yeah, but you watch. going through the roof. .au is in my top three destinations, i’m leaving the sinking, rotten ship fairly soon

yes, and they keep getting worse as time goes on

all i need to know is, does .au have ethernet for me to connect my laptop? ;D

no. we don’t know what germs are either

no, we still use carrier pigeons, but they’re really fast

yeah you lot are good at sport

just how many Australians do we have on channel at the moment?

5

+me

lots of idle ones

i included you already

aussie aussie aussie!

you mean Australians….

australian internet is apparently pricey due to limited connectivity to the rest of the world?

oi oi oi

yes, cable bandwidth to the states is the cause

well, we are on a big island

theres a new cable going in apparently

then you can retire the pigeons

its pricey due to a limited market that can exploit people quite well too ( yes thats you telstra )

thats the excuse they give for high prices anyway

it’s certainly a lot more expensive that .uk. My mum has a 256k/256MB adsl in .au for the same price as my 8M/4G adsl in .uk

for a general consensus on .au braodband amongst geeks/leechers.. http://forums.whirlpool.net.au/forum-threads.cfm?f=100

you have 4Gbps upload? :O
or is that the quota?

no, I’m just a lazy bastard and making up new ways of writing things… quota

wtf debian, spamd[12061]: pyzor: check failed: internal error

can i install debian with just the first and the second cdroms?

You can install debian with just the fifty-megabyte netinst CD.

but idon’t have internet connexion

then the first two should be enough, yes
generally the first is enough for most

even with gnome wm??

yes

thanks liable
i’ll try
i have debian sarge installed without Xorg
just the shell
how can i upgrade to etch without losing data ?

interesting with just cd’s
apt-cdrom should be able to add the cd’s, then follow the release notes

!tell omind about sarge-etch

!topicsmite omind

And the wrath of /TOPIC descended with terrible fury upon omind. And all the people marveled, saying, Behold, we too should read the /TOPIC, lest we be stricken. And all the people read the /TOPIC, and went away edified.

stupid question. can you have an underscore in a register your domain name name?

apt-cdrom should manage loading the CDs so it has the new package list then you should be fine.

no, read the relevant RFC

i dont know what the relevant rfc is..

can i use apt-cdrom with just two cds ?

do you know what google is?

should be ok

using amd64-etch, i was after a basic install, to then put fluxbox on, should i have only optioned standard install and not desktop environment?

though there is _domain.x.x.x for dk /dkim so its probably a conviention more that a rule

umm.. no? -_-

ok
thanks

who would have thought that typing domain name rfc into google would provide a clue
yep. desktop installs gnome and lots of stuff, maybe kde as well, i don’t remember

some registrars will have more strict rules about what you can register compared to what the actual system will cope with. For example, .au didn’t allow dictionary words within .com.au for many years

ah k, reload then

or purge packages you don’t want in aptitude

whislt i normally would aggree with that. to search for the rfc, and then to try and translate the rfc into something understandable to work out if you can or not have a _ in it is a long road to follow. I mean lets be honest, if you have the knowledge and its a simple yes/no answer i dont see why people make such a bit deal out of going to google things

k, so i can totally remove gnome from a runing debian?

butanwho, thank you for the answer

running*

thats because in my local database the person with priveleges on the database is “root”
and the user in the website is b14_718346
so how do i solve this issue??
basically the question is how to take care of user permissions with mysqldump

/join #mutt

hello people
“Incorrect file format ‘proc’”

How do you get this error?

when trying to execute a stored procedure
salle

the cookie monster!

repair table mysql.proc use_frm;
and the problem disappears for an hour
and then it comes back again

Is the site dev.mysql.com down now?
I am unable to access that.
The rest of the internet is fine.
Can anyone confirm this?

fine for me

hmmm

for me too

Do not know what the hell is wrong with my network!!!!

just tested the dns and its ok

Ok.. Any other place from where I can get the MySQL 5 manual?
oh I have it..
along with the installation..
I just added a new user account using the CREATE USER command,
The host of that user is set to as %
what does that mean?

any host. % is a wildcard, like * in Unix

oh.. okay
Thats fine.. exactly what i wanted..

hello

hi, all

is there a way to select a random item in query like SELECT n1, SUM(n2) FROM table GROUP BY n1, where SUM should be replaced by a commend electing random item ?
i tried with RAND(), but it seems to be imposiible
*selecting

i having a problem in a sting that have insert into xyz (f1) values(‘R-Vision 32′ Condor (Unit 5504)’)

Why doesn’t the following work?: UPDATE `phpbb_users` SET `username_clean` = LOWER(`username`);

What does it do?

how to insert with ‘ qout

escaped it

\’

whats wrong?

how i applied addslashes($str);
but id not working
its*

It gives an error: mysql said: #1062 -

near where?
lower?

how do i escaped the simpe qout

it doesn’t say, I’m guessing so yep

just add a \ before the ‘

I start my mysql with –log-slow-queries. I was use this sintax “./mysql-server.sh start –log-slow-queries=/usr/local/www/apache22/data/log/mysql.log” This is correct?

but its a dynamic values from csv file i applied addslashes to it
but its not working

or is there any way to select a raandom item for each distinctive field ?

hello, i’m looking for an article to get some informations about how big a mysql table can get (on a standard server, without much optimisation). can anybody give me a hint?

how big you need?
and what storage engine

am i right that a table used almost only for read can get with no problem to 100′000 rows?)

I’m using phpMyAdmin and it doesn’t tell me where the error is

for now it’s innodb… but i can change to myisam.

index is the key no matter how big the table is
even millions rows is ppossible
you sure? provide the complete error you get

it’s about an exchange rate table and it will be searched by two currency ids and the date

yep, http://rafb.net/p/w7SqBz42.html

would you suggest to create an index on that 3 columns?

possible, depends what you search

Hey . Thanks for the reference to ‘prepare’! This way it works just as I need it !!

the documentation says error 1064 is: Error: 1064 SQLSTATE: 42000 (ER_PARSE_ERROR) – I have no idea what that means really

a_l_e, make indexes to match the queries

works if you spell ‘UPDATE’ correctly.

i will search for the exchage rate for one date and two currency ids!

“UDPDATE phpbb_users” must be UPDATE

what is your primary key?

oops, doesn’t work with “UPDATE” either – sorry for confusing you
http://rafb.net/p/JgQerb56.html

there is an extra field for the primary key. but i could change to a composed key if needed (but i don’t know if a date can be used as part of a primary key)

strange it’s the same error though

1064 1= 1062

update and insert may cause index reorganization

oh,

1062 is duplicate key error
you must have a unique key defined on username_clean

hi everyone!

1062 – Duplicate entry

i should never have updates, only delete and insert and those will happen six times a day

eep – thanks guys that solved it

Hopefully there is some sql guru here I’ve got a table with: name, date_start, date_end. Now I want to get the “names” for a given date.

the goal would be, to have the insert happening in max 2 seconds each and the read being faster than a second.

then you have to benchmark it
maybe you want to take a look at myisam concurrent insert, and merge table for faster delete, keep more records and drop 1 whole table

SELECT names FROM table WHERE $theDate BETWEEN date_start AND date_end;

insert and delete will never be concurrent.
i don’t understand which table drop i should look for
… and yes, i think that i’d better just benchmark it… it doesn’t have to be that difficult!

thanks, but I incorrectly formulated my question.. sorry… I’ve got two dates as ‘input’.

roxlu_, and the table has two dates as well?

yes

ok. And you’re looking for what? Where they overlap the range in the table? Completely enclosed by it?

I’ll make a paste with an example
yes

yes to which?

to you
http://paste-it.net/2856

ppl, what is method for db backuping that store unicode characrters ?

2007.01.04 and 2007-08-31 .. Than I want to return “super” and “duper” as names

$start

Thanks

hi,

would it also be possible to get a list with the days and the name into which the given dates belong?

Meaning you want a list of each day across that 8 month period?

each thread made by threads-create(“sub”); reads only the subroutine given?
or the whole .pl script?

zOrK, wrong channel?

SELECT * FROM (SELECT n1, n2 FROM table ORDER BY rand()) AS dt GROUP BY n1;

ohhhhhhh
thanks
sorry

oh, thanks
and it’s the straghtest way ?
anyway, i’ll use it

It’s not very efficient, so if the table is big, you might not like the results, but I can’t think of a better approach right at the moment.

thanks
my table won’t have more than 3000 element, i think
*s

Probably won’t make any difference then.

spammed again?

yes

why can’t wiki updates be moderated

#2006 – MySQL server has gone away
where did it goto ?

Or at least stick a captcha on there or something

is it possible to create triggers after selects? suppose i want to update a “last_accessed” timestamp on a row when its selected?

captcha is losing effectivness on a daily basis

snoyes latest versions can have a captcha

i think captcha will be the inspiration for true machine literacy
one approch i’ve heard about is presenting the image on another site for a human to read and then supply that answer

half the $#@*$! captchas i run into *i* can’t even read, and last i checked, i’m human.

make sure you have a belly button

has anyone seen a problem before that when you do a select (select * from table where field =’1234′) and you can see the data is there, but the query comes up blank?

it’s either a belly button or a reset switch. . . .
nope. it’s a belly button.

Perhaps some whitespace in the field?

I’ve even copied the data and used it in the select

Our captcha doesn’t have to be one of those ‘read the text off the image’ things. We could just have fill in the blank SQL questions: SELECT * ____ tableName;
Is your WHERE clause really more complicated than you show? Can we see the real query, and the rows you say should be selected?

hi all

forumtab.sql

probably

can the ’screen’ cause any problem
I tried this command and it doesn’t produce the forumtab.sql file correctly
the file size created is 0

why use screen? so you don’t have to wait? i.e. does it work w/o screen?

are you trying to call the persistent shell session ’screen’?

I using screen because afraid the connection between my pc and server host droped

can you give screen commands to run like that? usually, you would use screen interactively…

so it cannot be done?

hm, yes, it seems you can… but i’m not sure what that will do.

so launch screen first, then your mysqldump

output redirect is redirecting the screen output instead of the mysqldump

hm? sure. type “screen” and enter. on the new (Screen) shell, run your command
or use nohup.

oic

” AND programnumber = ‘1000024′ AND tdate = ‘2007-01-01′ AND tdate = ‘2007-07-31′ ORDER BY studentname ASC

pilot error

And a row that you have which satisfies all those conditions?

oh man.. sorry had a phone call…

you can the detach from the screen, and later re-attach to it. if the connection breaks while you are attached, you can also re-attach (using some force option, i don’t recall the details)

yeap
that trick worked

snoyes are you there?

no wonder I am seeing like matrix

aye

it should. but even when i select just that programnumber is selects nothing
*it

Show the row. Copy and paste into a pastebin or something.

I had backup those tables, and how can I restore it to other existing database?

or use mysqldump and include just the relevant rows.

if I have a datetime type field, how do I select e.g. just the day month and year in a different order
I’ve troubles finding an example for that

!man date_format

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

!man format_date

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

sigh
!man date and time functions

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

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_date-format
there

ok thanks

how can I extract the date only from a timestamp ?

DATE()

I think the url you juse got when joining would have enough information

how do i create a user with all privs on a database?

!man grant syntax

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

snoyes, using that table I pasted I need to get the price for a product in a certain time period.. can you maybe help me with that?

How does that differ from your original query?

I’ll show you, i’m making a paste

anyone use freetds and ssl?

i just found the white space (blush)

mysqldump -h localhost -u v2user -p v2 forumtab.sql

what happens?

here is the paste: http://paste-it.net/2857

it tried dumping data to the last table

do you have a couple of minutes?

it should create new tables and put in the data

mynullvoid, mysql to import not mysqldump

archivist?
I had created the sql file uing mysqldump

So you want the price of a house, and depending on the date, it should be tierh 10,- or 20,- ?

now to append my database

Yes, and you use mysql to import it. php mysql web hosting file.sql

indeed, but.. you can rent a house for a given period

ok

ok, and that price is per what? per day?

now, I want to calculate the price for a given ‘rent-period’ which can overlap
yes per day

Interesting.

so I can rent a house (1) from 2007-01-03 untill 2007-08-01 ..
interesting indeed
I wanted to generate a list with the price as the first step… but I’m not sure it that is the correct way

You could create a table that holds just every date possible from now until the end of the decade or so.
Then join against that.

yes, or maybe use an inner join to create a list with days and use that?
an inner join on the SEASONS table

yes

can you help me with that?

SELECT * FROM dateList JOIN seasons ON dateList.date BETWEEN seasons.date_start AND seasons.date_end

but I mean w/o the extra table

The only way to really do that is to build the table with a ginormous UNION statement, which is excessively tedious.

ah okay.. and can I create a temporary table with the dates?

You could, but I’d just keep a normal table around with them.

okay, so than I need to add new entries whenever a new season is added?

yes, or just keep the table long enough that it doesn’t matter.
http://paste-it.net/2857#bottom

and seasons will contain all the days?

Unless you need the daily break down, see that paste

i c, but I don’t really understand how the seasons table will look like?

of course, that assumes you can make the price field a normal numeric type (int or decimal), rather than the string shown.
Seasons will be just like you show, except change the price from 10,- to just 10

yes okay
so I don’ t need a list with all the dates?

Not unless you need MySQL to return a daily breakdown.
If you just want the final price, there’s no need.

whats a daily breakdown?
ah okay
I indeed need the total price

2007-01-01, $10. 2007-01-02, $10. 2007-01-03, $10….

ah okay
but when I use you example, what will happend when a reservation overlaps multiple seasons?

That’s what the LEAST() and GREATEST() functions are doing.

so LEAST() returns the ’start’ day in the period?

end day, but yes.
We want either the date_start or the $start value, whichever is later
and the date_end or $end value, whichever is earlier

okay, I’ve to think this over

jan knesche (sp?) has a blog post about this issue somewhere

But thanks a lot. I’m gonna read up on these functions
really?
do you know the address?

I’m looking

#2006 – MySQL server has gone away
please assist?

!man gone away

see http://dev.mysql.com/doc/refman/5.0/en/gone-away.html

danke
If you need big queries (for example, if you are working with big BLOB columns), you can increase the query limit by setting the server’s max_allowed_packet variable, which has a default value of 1MB that was it ;( preciate it wench

I think it vanished when I switched the blogs
http://web.archive.org/web/20060221171213/jan.kneschke.de/blog/38 ?

yep, that one
^

who simplified the query dramaticly:

“

Thanks a lot!

That’s why I remember it.
a few days later I realized I could have replaced the IF() statements with GREATEST() and LEAST().

it was really an eye-opener after working on the query on paper for several ours
I’ll republish the article in the new blog

so what is/was the merlin project?
Must still be secret.

hello guys, I am trying to create a slightly unsimple query and I was wondering if someone here could help. It’s INSERT with ON DUPLICATE KEY UPDATE. Only the UPDATE part should happen only WHERE DATE(created) = CURDATE() and HOUR(created) = $hour : ). Meaning that if duplicate found outside of
these conditions – the new row has to be inserted instead.

http://mysql.com/products/enterprise/advisors.html

in your example, can’t I use the “end_date” all the time, instead of using GREATEST?

No, because if they don’t stay to the end of the range, you’ll be charging them too much.
and GREATEST goes with the start_date anyway.

ahh of course

Store those fields together as a separate field, with a unique constraint on them.

Where’s the Coke?

You can use a BEFORE INSERT trigger to populate that field from the created field, instead of having to do it in your application, if you wish.

hmm..we might be fresh out
didnt you bring any with you ?

so this would be correcct: http://paste-it.net/2859

*sigh*

the number of days per period

I’m testing replication, and when I run SHOW SLAVE STATUS\G, I see that slave_io_running is NO. Anything I should be looking for?

snoyes, i added “unique” to all fields which – in combination – have to be uinque.. and I was trying to do INSERT INTO table1 (r1,r2) SELECT (r1, r2) FROM table2 ON DUPLICATE KEY UPDATE r1=r1+VALUES(r1), r2=r2+VALUES(r2) WHERE DATE(created) = CURDATE() AND HOUR(created) = $hour… Am I totally
off course with this? (Doesn’t seem like anything that you said)..

looks correct

Thanks; calculations with dates are always hard
do you know a good book where stuff like this is explained?

Since indexes in MySQL don’t support functions, you’ll need to add a field to table1 for createdDateHour, with a UNIQUE index on that.
Populate this field with the date and hour from created, like CONCAT(DATE(created), HOUR(created))
Not these sorts of calculations in particular. It’s all just careful application of the various operators and functions available, which are describe in the manual.

snoyes, i see… This makes sense… This would narrow the scope of uniqueness check only to entries belonging to this hour.

and a general book on sql?

book

http://www.kitebird.com/mysql-book

what?

Suppose to be THE book.

ahh
book

http://www.kitebird.com/mysql-book

ahh hahah a bot

bot

I’m *not* a bot! I’m a real buxom wench

hi how do i kill a particular process?
from the output in show process list

kill pid;
pid being the process number
!man kill

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

okay thanks

I hadn’t seen that [CONNECTION | QUERY] syntax added to kill in 5.0. Sweet.

would this be possible? http://paste-it.net/2861 (selecting into a variable and using that)

Is there a way to (actually, I’m sure there’s a way…maybe “how do I”) pull data based on a conditional? Something like: SELECT if(There are parens in `stlye`){strip parens}else{`style`} as style FROM…

CONCAT(DATE(created), HOUR(created)) — i should do it right on the insert. so if insert the “created” field gets generated on insert (current timestamp) then CONCAT(DATE(created), HOUR(created)) would still work within the same insert?

SELECT REPLACE(style, ‘(‘, ”) FROM table;
if you replace created with NOW(), then yes.

it needs to be a bit more involved. I need to replace “\(.*\)” but replace doesn’t allow for regex

snoyes, my created gets generated with default set to CURRENT_TIMESTAMP, is that sufficient?

For the created field, yes. But for the other two, probably not.

AH , wait, you said to replace created with NOW() within CONCAT?

So strip parens, and everything between them? What if they are unmatched? What if there are multiple? Nested?

snoyes, (that was at you)

correct

that’s my point. I got it to do what I want using this: TRIM(CONCAT_WS(‘ ‘, TRIM(SUBSTRING_INDEX(`style`, ‘(‘, 1)), TRIM(SUBSTRING_INDEX(`style`, ‘)’, -1)))) as style

So I had this dream where MySQL charged licensing fees based on throughput…

But if there are no parens, then it duplicates the contents of style

Yeah, I think you’ll end up with something like that, unless you install the regexp udf.

sounds more like a nightmare

Seems like the real nightmare would be billing for that.

snoyes, thanks a lot. you get overloaded here i see. : ) I appreciate your help.

seekwill they would be richer than m$ then

There’s a regexp udf? Where I can do something like: SELECT regexp(…) ?

Can’t open file: ‘entity.MYI’ (errno: 144) but what does that actually stand for

hehe

Yep. you’ll find it on google.
!perror 144

Table is crashed and last repair failed

I think I switched to PG though!

should i do repair table?

yes

I’m testing replication, and when I run SHOW SLAVE STATUS\G, I see that slave_io_running is NO. Anything I should be looking for?

But there is no way to do a conditional? “SELECT (condition)?opt1:opt2 as Name” type of thing?

issue START SLAVE; see what happens.
Sure, SELECT IF(condition, trueValue, falseValue)
!man flow control functions

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

Thanks. While the regexp might be cleaner, this would support more servers…

oh
!man control flow functions

see http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html
see a href=”http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html”http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html/a
see a href=”a href=”http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html”http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html/a”http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html”http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html/a/a

when you get to be my age you’ll be more concerned with Flowmax functions.

0 am on the local cable channel? Something to add to your garden hose so that it could also chop
0 am on the local cable channel? Something to add to your garden hose so that it could also chop

hello guys
is there any function available for find a text in a row, and return 1 if found, 0 if doesn’t exist
?

threnody, you that old as well?

!man string functions

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

lemme check

0

snoyes, thank you

what do i have to change that my slave gets access to the master? for the moment i get: Slave I/O thread: error connecting to master ‘repl@domain.tld:3306′: Error: ‘Host ‘xxx.xxx.xxx.xxx’ is not allowed to connect to this mysql hosting server’ errno:
1130 retry-time: 60 retries: 86400

you need to use the GRANT statement to allow that repl@domain.tld user replication privileges.
!man grant syntax

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

older.

threnody, but have caught up with me yet

How many times a day do you use the term “whipper snapper”?

kidz these days

i get this error: ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES

what statement did you attempt?

GRANT FILE ON database.* TO repl@IP_of_Slave IDENTIFIED BY ‘password’;

Why are you granting file?
grant replication slave.

11H14ello

ok, now just to get the picture, i have to grant replication slave on the masterserver for user repl@ipaddress_of_slave identified by password, correct?

correct

I’m new to this company and inherited a server that has mysql installed, but the config wasn’t documented by my predecessor. How do I recover the root username and password for mysql? thanks.

reset root

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

but what is on the slave? do i just create the user repl with the same pw? or do i have to grant something?

I’m keeping my wording terse to avoid spillover. MySQL 5.0.41, Debian Etch. Colocated box, host says port 3306 is open. my.cnf bind_address is ip of nic. user host connection priv is ‘%’. nmap and netstat show 3306 closed. I need remote login, what am I missing/doing wrong?

What’s for lunch?

open firewall

marc-andre: You’d specify the given user/password in the MASTER_USER and MASTER_PASSWORD fields of the CHANGE MASTER command.

no idea how I got that nic.. anyway, it is open.

try to telnet to the port

good question

make sure the server is started.

by masterhost, can i give there the ip address of the master?

yes

You said that California Tortilla place was good?

Heh – seen Pablo Francisco’s impersonation of the governor of CA as a tortilla vendor?

yeah, it’s not bad… beats chipotle/qudoba/tijuana-flats

snoyes, could you tell me why this generates an “Operand should contain 1 column(s)” error? INSERT INTO table1 (r1, r2, r3) SELECT (r1, r2, r3) FROM table2 ON DUPLICATE KEY UPDATE r1=r1+VALUES(r1), r2=r2+VALUES(r2); …

drop the () in the select list.
….SELECT r1, r2, r3 FROM

snoyes, ah, ima try now

telnet to 3306 timed out, no connection, telnet to 80 let me in.

probably the firewall isn’t allowing inbound connections on that port.

if I do ‘nmap localhost -p3306′ from the server, it should give me the status of the port as open, regardless fo firewall settings, yes?
Yeah, just did it on my laptop, and it shows 3306 as open.
rather, ‘nmap -p3306 localhost’
And a netstat -a shows no listener on 3306.

external

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

so literally comment out hte line bind_address= line in my.cnf?
doing now.

and the skip-networking

freight__ unless you know how to bind it properly
how come i see so many people with these same issues in here, does the installer bind automatically now?

No. People do weird things

distros fsck the standard install

Distros can’t bind to the external nic
ip
Well..

some probably see it as a security issue… having a database hosting that is wide open to connections from anywhere by default is not the best of practices

how do i limit the databases to be replicated on the slave? i followed the howto on mysql.com but the slave tries to replicate the whole server

MySQL ships root with no password…

marc-andre: You can either configure the master to only write the relevant db to the binlog, or configure the slave to only read the relevant db from the binlog.

Plus, when a Linux distro binds itself to the external nic…. (instead of lo)

marc-andre: http://dev.mysql.com/doc/refman/5.0/en/replication-options.html#option_mysqld_replicate-do-db

is there some nice way to tell mysql not to die on error? I’m wanting to update all updatable data in a (unique) column, and just ignore when mysql can’t update it

UPDATE IGNORE

oh cool, thanks!

-p -h ipaddy. Before I call my colo host and start complaining, is there any settings other than the firewall rulesets that could be preventing me from connecting?

s/is/are/

What distro?

Debian

GOOD LUCK

Etch to be specific.

#1046 – (duplicate)

Heh, thanks. grin

is the goal to get it to allow a remote connection, or to not allow a remote connection?

To get it to allow remote connections.

Personally, I’d juse ssh tunnels…
Especially for administration

Rigth now I jsut need to get the bloody thing to work, but you are correct.

ssh tunnels would work.
Pretty easy

if its at a colo they may not let 3306 in the building

Ok, I’ll bite.. where do I start reading?

ssh -L 3306:localhost:3306 host.server.com

how can I know where are my MYD, MYI files stored? (redhat)

/var/lib/mysql
find / -name ‘*.frm’

thanks seekwill

When’s lunch?

is there a way to find that out by doing some query in the mysql cli?

show variables like ‘datadir’?

soon?

Whenever you’re ready. Do you go with Wez and Luke these days?
Of course, Wez has enough sugar at his desk to last him a.. uh… another hour.

you has drive?

Mike has the keys

can i use more then one ip by bind-address?

no

Hah, do you ever eat with Mike?

hmm… probably you, mike, and luke should go… then i can convert you all to the proper side of the force

hah

“proper side of the force” – holding your pinkies up when electro-shocking some rebel scum.

PINKIEEEEEEEEEEEEEEEEEEEEEEE!!!!!!!!!!!!!!!!!!!

how can i permit the slave to connect without blocking the connections on localhost?

remove bind address completely.
external

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

Greetings Y’all

thx

Mike and I are ready
well, give me a sec

heh
howdy

Did we decide on something?

yes

you have that response on a macro, don’t you. grin

bot

I’m *not* a bot! I’m a real buxom wench

Ahh good.
snoyes is a robot.

but enough about my personality…

I met him and recall a distinct whirring sound.

the wench has no macros

Where did I read that most people fail the turing test?

the wench passes

Based on some of the captchas I have seen I would believe it.

turing

Yay I passed the test. I am human after all

i cant post comments in wez’s blog because always get defeated by his capatcha system

heh

I figured the laser canon and metallic skin would have been a better giveaway, but perhaps you were distracted that day.

are you related to samus aran ?

Well it was California.
You kinda blended in.

I’m up to try that California Tortilla place

Heh, debian.org’s round robin upgrade system keeps sending me to a server hosting in France.

You have a problem with the French?

The bounty hunter? We don’t need their kind of scum.

Not in the slightest, acutally.

What’s wrong with a free trip to France?

you have to go to france to use it

Aah, good point.

I actually appreciate the Fench attitude, because there are no pretenses, if they dislike you, you know it. It’s refreshing.

The airport can’t be that bad, just fly in, collect the miles and fly back.
The French don’t dislike, they hate.

and their food.. *tasty*
So, seekwill, I’ve run the line you posted, and am in the server. How do I get mysql-admin, for example, to run remotely and display here?

You connect to localhost

is there a way to use server status variables in a select statement? ie: select 100 – ( ( Key_reads / Key_read_requests ) * 100 );

One of the more recent French shows of affection by Zidane.

Luke coming?

when I issue START SLAVE, it says Query OK, but nothing happens

Now what do you see when issue isse SHOW SLAVE STATUS\G

the same
lemme verify something

Is there an error shown?
Either in show slave status, or in the error log?

nothing in either

but it still shows slave io is not running?

correct
and I don’t see any connections via netstat
on master, that is

i have a table of products, and i’d like to record specific details that change depending on the type; what’s the best way to capture this?

What does the slave status say in slave_io_state?

Slave_IO_State:

luke gave me some mumbling about lunch with laura

I thought she left. Oh well. Let’s go!

but there are some other folks who might join in… more out of townies

ah

for examples, some records in “products” would be of type “car”, “boat”, “airplane” (made-up example)
and i want to have different details for each
so my first instinct would be to have tables “products”, “car_details”, “boat_details”, “airplane_details”

and nothing in Last_Error?

0
Last_Error:

and the Master_Host, _User, and _Port are all correct?

yes

Depending on the nature of the date, you can do it that way, or you can just create a key/value table of details, that is, product_id, detail_type, detail_value.

yeah, those were the two ways i was considering
any big pros/cons of each?
obviously i’d have to have an established set of types to do the multiple-table way
but i’d have to have an established set of detail_types for your way

or you establish them as you add them

are you still arond?
around

and it’s less work to add a product that way

Correct. The later is more flexible in that you can add more information without changing table structure. However, if the details require different field types, that’s a problem.

You are supposed to do mysqld –debug to produce a trace file , no?

Example, price is best stored in a decimal field, but color should be a string.

mm, good point
yeah, i def. have multiple datatypes for my details

4 [ERROR] mysqld: unknown option

some booleans, some decs

Hey there

I’ve got a table with a field “product_type” , when this value is “house” I want to use the field “house_groupid” to join on a table… else I want to use “general_groupid” to join…. is that possible?

could someone explain why my primary key doesn’t help this query, and how i could index this to make it faster? — http://www.pastebin.ca/622049 (query, explain, and key posted)

In windows, when i run mysqldump –all-databases -u etc
Where does the dump get saved? I can’t find it

JOIN otherTable ON IF(product_Type = house, house_groupid, general_groupid) = someOtherField

grant usage on database.* to ‘user’@'localhost’ identified by ‘password’; still result in this error: warning: connect to mysql server 127.0.0.1: Access denied for user ‘user’@'localhost’ to database ‘database’?

If you don’t direct the output somewhere, it just goes to the screen.

Doh

path/to/file

Thanks snoyes
snoyes++

Okay, thanks snoyes
The output looked matrix-like
lol

marc-andre: are you connecting with mysql -h 127.0.0.1 or mysql -h localhost?

Hmm

pizza_biz, ord_type not like ‘%C%’ cant use an index

C:/MySQLdump/

Didn’t work
Do i need to create the dir?

Put an index with ord_date listed first (and drop the USE INDEX statement so MySQL can pick that one)
Your WHERE statement doesn’t reference the store_num, but store_num is first in your primary key, so the primary key is useless in that query.

i only added the use index to see if it would help, and it didn’t. same either way.

Right.. err

You need to give it a file name.

Hmm

so just add an index on ord_date, any other fields?

Ahh
Just out of interest

Probably not for that query.

Why is the syntax -uroot
rather than -u=root

My server keeps crashing on restart

Cause with -u=root, it says “No username, =root”
lol

You can choose -u root, -uroot, or –user=root

-u root doesn’t work
-uroot does

I look in the .err log and I see a trace , it give me a link about using a program to read the trace

Okay, so to restore a DB dump..

I’ve not experienced that with -u. With -p, yes.
mysql /path/to/file.sql

but I don’t see an example for how to use the program

Thanks snoyes
Okay, rebooting

in a long import is there any way to tell how far it is through the process? i disabled logging to speed it up

ah yeah, adding indices to this table sucks. over 6 million rows. this is gonna be a while. (poor old slow server.)

the size of the data file?

pizza_biz, best to get rid of the like as well

hmm yeah could use that although that will only tell me that it is still working away

i need that to filter out non-applicable rows though.

hi! how may i add UNIQUE index on two columns simultaneously?

pizza_biz, find a better way or at least get rid of the leading %

UNIQUE(column1, column2)

hi!
i don’t want to let similar combinations of some column values…

ok

how are you today btw?

hungry.

time for a dunkin donut
almost worth flying to the usa for

say you have a list of transaction that have a date and a month and you are grouping by month to sum the transactions and display the total.. Is there an easy way to get the query to display 0 for months that don’t exist in the result set?

Hey
How do i un-dump my dump
xD

mysql /path/to/file.sql

i did that
But, it says this–
ERROR 1064

You’d need a table with the list of months; join against that.

See, the version i dumped from was 5.0.41-community-nt

snoyes, thanks

“MySQL dump 10.11″

and the version you’re importing to?

Err.. the newest one, freshly downloaded
5.0.45-community-nt

There ought to be a — at the beginning of that line in the dump file.

yeah, there is
– MySQL dump 10.11
Aha, i fixed it.. i think
Sorry, i’m migrating from PostgreSQL
Okay, it’s populated..
Thanks a lot snoyes
Hey, is it alright if i ask a question or two?

Hey guys, how do I get “IF NOT EXISTS” for CREATE DATABASE in mysqldump files?
I mean, it’s in the file, but it’s commented out and is shown as “/*!32312 IF NOT EXISTS*/”
Apparently the numbers are the same all the time for everyone, too…

hi all

If you look it’s this
CREATE DATABASE /*!32312 IF NOT EXISTS*/ ‘database_name’

creating a schema is same as creating a table in mysql 5.x ?
or schema is a database?

what is the capacity of a mysql table? how many rows can it handle efficiantly?

/IGNORE #mplayer ALL -PUBLIC -ACTIONS

if i need to search around 90,000 records, like 10000 times per hour

So you’re saying I should do a search replace?

Yeah, might as well
:-/

That’s a conditional comment.
It means that for version 3.23.12 and later, IF NOT EXISTS should be parsed. For earlier versions, it will be ignored.

That’s not a trivial question and requires details about your hardware, OS and application(s), as well as your concept of efficient.

schema and database are essentially the same.

ohh!

The answer is also impacted by your DB design.
What do you mean by “search”. Show specific SQL.
In general, 90,000 records is considered very small.

is this possible: http://paste-it.net/2866 ?

probably, but the manual says not to assign and then refrence a variable in the same statement (because the evaluation order is not guranteed.

okay

So just replace @end with r_date_last in your SUM statement.

You might consider reading through some of these: http://www.mysqlperformanceblog.com/

hi, can i do a mysql search with a wildcard
like any record with http:// in it ?

WHERE field LIKE ‘%http://%’

so % is the wildcard
thx

% and _ for LIKE. There’s also some support for regular expressions with RLIKE and other operators.

k thanks

thnx

Hi
One of my users have a strange problem. When he logs in my FreeBSD server via SSH from his Linux pc, and starts mysql client (v4.1.22), after entering the password the client eits with SIG11. If I login to the same server from my Linux box with his user ID, I can start the very same mysql
client. Does anyone have idea what can be the root of the problem?

Any recommendations for crash-proofing a MyISAM-heavy system? Every time the server crashes (it happens frequently), I lose lots of records when the automatic recovery kicks in
like [Note] Found 7657 of 7694 rows when repairing ‘./site/table’

Isn’t that a segfauly
segfauly
t
lol

) It is
I have a core file too
but mysql was not compiled with debug sybols

Hmm, the only thing i can think of then is that it’s the person’s SSH client and/or some kinda network problem

symbol
s

I’m new to MySQL (Postgres convert.. ) but i’m looking at src now
And google )

He even tried to log in from a different pc too. The problem is the same.

kumi, don’t use MyISAM if you expect lots of crashes (why does it crash a lot?)

I think the PSU is going bad

heh, sounds like you have bigger issues then

xD
err
What do you use for the backups

just a periodic mysqlhotcopy cron script

Hm

hello all

does binary logging improve recoverability? I have it turned off

can anyone tell me if I can use wildcards to delete multiple tables?

since I don’t replicate

dex

Not as such, but you can use information_schema to build a drop table query
It can help, since you can use it to replay updates made since your last full backup.

oh,

hi all
Can I change path /var/lib/mysql without recompiling?

drop all tables

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

all I need is set datadir?

bit of a modification to that ^

or I need to recompile?

ok, thanks

What are “good” uses of MySQL temp tables?

I am a newbie. How do I import a database file into MySQL?

Handling aggregate records that would benefit from an index (hence no derived tables/subqueries), but that are too dynamic to maintain normally.
mysql path/to/file.sql

Ah :-O
So, such as?

I am using mysql query browser, a graphical gui.

Okee

Open Script

Then click “Execute” on the right-hand side
:3

Say you have a table that stores all the hits on your web site, and you want to find out how many of them result in someone actually buying some of your products. With millions of records, querying against that hits table can be unwieldy.

Oh

However, you can create a temp table at run time that loads summary data from that table for just the products you’re interested in.

Mhm, and then once you’re done.. pow, right in the kisser?
xD
Bang, zoom — straight to the DELETE TABLE ?

One of these days…

I think there is an error in the query we created (or… i probably made the mistake )

“I’m going to stimulare the economy by buying an American car” ?

, )

still there?

Impossible. All queries created in this channel are automatically verified by an independent consultant, and are guaranteed error free.

I didn’t verify it in here

0 and

Cool, snoyes

I continued after you helped me on th eway

Just reading about it on the dev center

the problem with that pasted query, is that it forgets some days when a season overlaps

Can you turn that dbf into a CSV or something like that?

thanks the_wench

can you help me with that?

snoyes-senpai: What’s the 6.0/Pidgeon all about?

yes I can load it into excel
which then could make csv

That’s the ranking algorithm borrowed from Google.
ok, from there you can use LOAD DATA INFILE.

OH! Really?

!man load data infile

see http://dev.mysql.com/doc/refman/5.0/en/load-data.html

I’m going to look at that for sure

Yeah. http://www.google.com/technology/pigeonrank.html

;D
Nah, i mean the 6.0/Falcon

0 because original format is

A new transactional storage engine. Some see it as an attempt to build a replacement for InnoDB, which Oracle bought.

You’re like a IRC Wikipedia bot
With added sass

MySQL can probably handle that if you define it as a TIME field.

?

Can you show the rows that get skipped that shouldn’t?

sweet ok I will look into that… thanks very much!

they don’t skip, but when a interval overlaps multiple seasons it forgets one day.. I’ll paste what I mean

ah, perhaps that expression in the select list should be TO_DAYS() – TO_DAYS() + 1

http://paste-it.net/2868 this is a result
yes, but than it adds 1 to much (for the last overlapping season)

file.sql; how do i load file.sql into new database on new server? thanks

In the example you show, it should be + 1 in both cases. Can you show an example where it should not be + 1?

mysql file.sql

yes
ahh the problem is that I want to count the nights, not days
like this; http://paste-it.net/2869

So, if the s_date_end is 2007-07-31, did they stay the night of the 31 or not?

yes

ok, do TO_DAYS() – TO_DAYS() + (s_date_end = r_date_last)
I think.

okay gonna check

I dunno. I still think it’s going to be + 1 in all cases, but map out all the possiblities and check.

that works

need advise concerning union. i have a table with some kind of linked list (means some items have parent by parent_id). to filter for childs or parents, a union would be the key, right?

I think it must be: + (r_date_last = s_date_end) don’t you think?
the r_date_last is the reservation end date

I dunno. Try it and see which one gives the right answer.

s_date_end is the season end.

ARK
I’ve got another problem
I can’t RENAME my temp tables

Maybe. What do you mean by “filter for childs or parents”?

do i use ALTER?

!man alter

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

thanks, what if i dumped only one databases, just add a “create databases whatever; use whatever;” to the beginning of my sql dump? or is there a fancier way? thanks again

Why do you need to rename a temporary table anyway?

Is there a way to force a case sensitive GROUP BY for an alias, since BINARY doesn’t seem to work for them?

there is a mysqldump option for that, but you could also just do: mysqladmin crate dbname ; mysql dbname file.sql
s/crate/create/

cool thanks, IRC is fun

xgc, you there?

is mysql more widely used in industry compared to postgre? I’m trying to decide which to use

#mysql has a mandatory sense of humor in the channel topic. That should be enough to make your decision.

snoyes, for example all childs (which don’t belong to a specific parent) and the parents themselves.

ever heard of LAMP? linux apache mysql perl?

trees

http://www.dbazine.com/oracle/or-articles/tropashko4 and http://www.sitepoint.com/article/hierarchical-data-database, http://jan.kneschke.de/projects/mysql/sp/ http://jan.kneschke.de/projects/mysql/sp/sp_tree.sql http://www.vbmysql.com/articles/database-design/managing-hierarchical-data-in-mysql/ http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

^

in a deadlock situation, is mysql returning immediately, or is it waiting some timeout period for the locks to be released?

heh ok snoyes convinced me first

That used to be a joke. I’m not so sure anymore.

would;nt lamp in that fasion be an argument against him, since he is using RoR ?

ruby on rails isn’t really used much in industry, though
yeah, i was just noticing that contradiction,

I was jus’ experimenting, is all

but coding skills and sql skills are transferrable between languages and db servers somewhat

I’m lost…. I study computational complexity theory for a living but want to learn web app programming…. everything is so fragmented though it seems in the webapp community and everyone has their oppinion

I’m reading the docs here and it says to retry transactions that return the ‘deadlock’ situation as the error. This however seems quite wasteful to me, if the deadlock exists because of a transaction that may take a few seconds to complete, then I could be reissuing the transaction thousands
of times in that period

http://rafb.net/p/8oIWu055.html

Just pick one at random. You can do most anything you need in either one. After you’re comfortable, learn the other one. When you have an important project, you can decide which one is best suited.
A significant portion of “which one is better” is really “which one do you know well enough to make it do what you want”

ok, fair enough

this query is correct right: http://paste-it.net/2870

There’s no need to use the IF construct; MySQL doesn’t differentiate between the boolean ‘true’ value and the number 1.

okay, but it would be a correct query?

seems to. The parser and results will tell you better than I can.

yes,but the strange thing is, that it returns 0 for the second season when it overlaps 2 seasons
o days in the second season I mean

Did you run the testcase I gave you?
You can place a SELECT inside the NOT IN (…) clause.

yeah.. i couldn’t get it to work. the wrong results kept coming back.

s_date_end)

That’s the purpose of the LEFT JOIN I showed.
Not possible.
The testcase I gave you produced correct results.

so i can stick a SELECT in the IN () and it would build the string for me?

Did you change it?
Yes.

awesome!

It doesn’t produce a string. It generates a list / set.

how did you build that one test? what app were you using?

Just run it through mysql command line client. I created it wish an editor by hand.
s/wish/with

can you show the result set for that?

mysql test myscript.sql

i need to learn how to do that so i can paste those in here if i ever need help again

;$ ahh is c the problem indeed. its when the last reservation day is on the start day of a new seasons

thanks a bunch for your help!

You’re welcome.
Your SQL is not correct.
It has the same problem you were trying to solve yesterday.

it works perfectly

That is, if there are assignments for some photo to another article, this will return that photo, even though it’s already assigned to the story in question.

I have a table with parts for a computer I want to xulid. When I do SELECT (unitprice*quantity), it gives me the cost of each row. Now how do i tell it to do that and then add all the rows?

You really need to work on your listening skills and be open to mistakes you may have made.
You can’t do what you’re attempting without some form of aggregation.

SELECT SUM(unitprice * quantity)

i’m open to mistakes. God knows I am not perfect! But the query i pasted accounts for any assigned items to the parent story in the NOT IN(), so they will not appear in the results.

Thanks.

That’s not the issue.

there is a preceding query that’s not pasted which populates the NOT IN()

what’s the easiest/best way to convert a table from myisam to innodb
?

Show that other query. Maybe that’ll help.

ok

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

Are these the photos (in the NOT IN clause) that are already assigned to this story?

yep

That’s fine.

nice. it took over an hour to add the store_num index to the table, but the query now runs in 10 seconds instead of over 1000.

I assume you mean the date index.

yeah. that.

ALTER TABLE table_name ENGINE = InnoDB;

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

Question. Don’t you want to show all photos that have not been assigned to this story?

yep, i want to show all photos that have not been assigned to this story and that’s what the results are coming up as now that i added the NOT IN() to the query

also vastly sped up another regular query on that table. bonus.

I’ve read that innodb is the only engine for mysql that is safe to backup using lvm snapshots? Is this true?

!man lvm

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

heh, sorry

define “safe”

not likely to cause corruption of a backup is made during an lvm snapshot via tar or similar unix commands and then later restored

That’s fine. Note that you will potentially show photos more than once with this query, unless you use DISTINCT or something equivalent.

Make sure you flush tables with read lock first; then it should be ok for MyISAM too.

but innodb doesn’t require tables to be flushed?

Assign the same photo to two other stories, but not the target story. See what happens.

ok thanks for the tip. i didn’t think of that.

correct, unless you want to know the binary log position, so you can restore from the binary log for all changes made after the snapshot

and DISTINCT won’t help you in this case, not with the SELECT list you have.

Hi all. I’m pretty much a noob to web programming in general. In the past week I’ve been looking through some php & mySQL stuff. One thing that continues to confuse me is how to associate one element of a table with multiple elements of another. For instance, an element of the members
table having a list of friends from the members table, or maybe an element of the members table having a list of watched posts from the forum tabl
n?

shite

If you remove the [a] columns from the select list: SELECT DISTINCT s.ID, s.photo_headline FROM …; will work fine.

You have a members table, and a posts table, and a membersWatchedPosts table, and connect them all with a JOIn
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

Anyone know what a “PMA Database Not OK” error under phpmyadmin means?

ok

I think I’ve got the correct query, interested?

sure

snoyes and the_wench, thanks much

You’re welcome.
I bet the_wench won’t say “You’re welcome.” She’s rude like that.

is there a way to request the mysql to use SO_KEEPALIVE on inet sockets at all?
s/the//

http://paste-it.net/2883

should I see any problem changing the engine type on the “mysql” database tables?

please correct me if i’m wrong

I wouldn’t do that.

why is that?

I do it from memory to myisam every day
going to/from innodb may be trickier

I’d like everything to be innodb
due to my lvm snapshots for backup

How are you going to alter the db, host, and user tables?

as root?
this is why I am asking the question

Try it and tell us the reesult.

heh
I don’t know if I like that answer

Error on rename of ‘./mysql/#sql-6a4_8′ to ‘./mysql/db’ (errno: -1)

is there a way during install to specify that the mysql database uses innodb?

The rest of the tables (minus the log tables, which have to be CSV) worked fine.

I’ve already restored a copy of the database that was made using tar and lvm snapshots successfully due to a disasterous situation, but I’ve heard that it’s not a great idea. Could I be hosed since the mysql database is myisam?

There’s a huge reason why MySQL couldn’t use InnoDB for the mysql DB…. If only I remembered…

looks good to me.

thanks

Probably because you can disable the innodb engine.

Possibly!

the mysql database contains plugin information to load innodb now!

CREATE TABLE chicken ENGINE=egg;

would it be possible to put that query in a stored funct/procedure, so I can get the price for a given reservation?

yep

i’d tried that yesterday, with just a simple function but i couldn’t get it to work, though I think it seems quite simple

Can you show what you tried, and what didn’t work about it?

i think i removed it
i was just trying
http://paste-it.net/2885
a href=”http://paste-it.net/2885″http://paste-it.net/2885/a
yeah.. I know.. didn’t had time to read the manual :$

Not the point. The point is the stupid tab autocompletion.

ah

:-

oh, yes, that helps immensly.
lol

haah
I’ll read up on procedures… will you be here after 30min?

probably

okay great!
would it be better to use a functions or procedure?

I’d go for a function, unless you want this thing to return a full result set all by itself.

only the price will do
when I create this function that returns the price for a product, and I use it for all the products in my product table, woudl that be hard on performance? i mean harder than just joining everything?

probably. How much difference it makes depends on how big the table is and such.

okay, lets say around 100.000 products
but not all at once..
using LIMIT

No telling. The only sure-fire approach is to try it both ways and see.

ok
hmm but when I use the function(), the query that calculates the price is executing another query for each product, else it will be just one query
or handles mysql this more clerverly?

It’s as you describe. Whether that’s better or worse is very difficult to say.

i love mysql databases

whew, i think it’s finally done, Xgc!
you were right, i did have to use DISTINCT, Xgc.

*nod* You could have better names for the story_assign table. I believe I misread your description. I still don’t think, based on the results, you ahve the right answer yet. You seem to return a superset of the photos I think are correct.
s/names/column names/

when I want to return the result of lets say: select id from products where id = 10; how would I put that in a stored functions?

CREATE FUNCTION yourFunction() RETURNS int RETURN SELECT id FROM products WHERE id = 10;

ah okay

You seem to return a list of IDs that contain 21759, for the sample case.

Xgc, if i could get good at running those tests i would be able to past exactly what i’m working with.

I may still have bad data, given that I misunderstood the table descriptions you gave.

if you’re still around later i might be able to run the test and paste the whole thing with data

I corrected my SQL and the only difference between your SQL and mine is yours returns 21759 in the result and mine doesn’t.

I’m fairly new to MySQL and have a little question. Is it possible to somehow make references in a table to another table?

very odd

Yes, using a VIEW.
or a foreign key, if that’s what you mean.

Xgc, are they the same thing?

No.

Okey, i’ll look both up. Thanks a lot.

One allows you to create a fake table that refers to 1 or more tables internally, returning data from them all.

And the other?

The other (foreign key) allows one table to have columns that refer to a unique key in a second table.
It’s like a pointer.

That’s what i want
Cheers, thanks for it.

The term is “foreign key constraint”.
I think that’s just the standard that requires unique.

standards. bah.

hi guys
i wanna create a search engine with mysql you guy can tell me what do you think about it

Good idea. Grab the new http storage engine, and just point it at Google.

i want to make a scalable architecture , not extremly fast, but scalable
this is not for web search engine, only object (blob) indexing,
I want to index around 10 millions of object and even more
and i know that full text search isn’t scalable at all i know how bad it is on big amount of text
so here is my though:

why not use an existing search engine?

because i don’t know any scalable search engine

lucene, for example

can we clusterise it ?
i would create 2 deamon, one index one DATA servring
2 tables
1 index table contain (hash of key word (md5), hash of data)
data table would be (hash of data, gzipped blob of my object)
index table has 1 line per keywork contained in the object
data table has 1 line per object
so if i index 10 object that have 10 words in each, i will have 100 rows in index

I’m trying to create this procedure: http://paste-it.net/2887 but I get an error about the return

during a search, i broadcast the keyword query on my cluster of INDEXs daemon
and the INDEXs daemon return’s the HASH’s of the data matching

?!? wtf is that, google makes jokes ?
woops, bad timing

You refer to the pigeon thing?

yes

Every April 1

SELECT data_hash where kw_hash = md5(‘games’)

do you know why?

what do you guys think, this is actualy the way google worksm but with MySQL it’s obvious
Xgc, ?

wrap () around the whole select

For your enjoyment, when you have a moment: http://rafb.net/p/jGB78X22.html

ahh

RETURNS INT RETURN (SELECT…GROUP BYr_id)
or else declare an int variable, use SELECT INTO to populate it, then return it.

This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled

In your case, I think it’s READS SQL DATA

but those values aren’t used I read in the docs?

so RETURNS INT READS SQL DATA RETURN (…

sweet, Xgc! and you did this in mysql comman?

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