the command that doesn't work:
load data infile '/opt/appservers/dbimport_test6.txt' into table TEST_IMPORT character set utf8 fields terminated by ';';

Jimi what version?

mysql 5.0.22

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

data structures
can I msg the_wench?

flung not tested
it reads the msg but may not repond

ok

its not registerd so cant repond

archivist, you know if load data with character set is supported by my version? i have no idea how to check that

its in the docs jimi
have you created the table yet

ah, yeah I just noticed it is from 5.0.38+…

I get the same error and have not created the table, in v4.1

yeah, the table is created. but I can drop and recreate it.

hi

some data gets inserted, but where there is a å, ä or ö character, it gets currupted

how can i reset the auto increment for my db tables
?

I think the load data infile is a bit buggy it failed with a csv for me last week and a script works ok

i never had any probs with load data

!m drtanz alter table

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

as long as the file's clean

well yes

hmm…. but with big data transfers, isn't the load data statement the prefered way? the manual says it's 20 times faster then regular insert statements

well I buld batch inserts

my prob is trying to use oracle syntax in mysql dataloads

oracle syntax in a csv file? you mean like the dates?

I was reading an excel csv last week when i got failure

Jimi no, terminated by '"' separated by ','
that kind of mistakes

actually, transfering from oracle to mysql is just what I'm doing… any other recommended way maybe? this will run every night (clearing the table first)

Jimi how large data sets?

at the moment about 80.000 rows. maybe 10MB as a csv file

just select the updates to the oracle db each day

huh? where? a script? how?
run on the mysql-side or the oracle side?

i'd perl it
i move more than that with a perl script across two mysql DBs

php has a nice csv read

hmm… don't know perl or php… this is part of a system in java (tomcat web server). do I have to write my own database select and insert statments in java?

how can i truncate multiple tables
such as TRUNCATE tables modules, users

drtanz, script

Jimi you do
java's fast too
and the oracle jdbc connector's good
you'll be fine

I'm creating a table for a hit counter, is it ok to use type "TEXT" where I'll be storing number of hits?
or is there a better one?

but it doesn't really feel right… doing all that coding with all that overhead, just because i can't import the file as utf8
is there some other mysql statement/command that changes the character set that is used during file import?

INT maybe?

why would you use TEXT to store a number?

no idea

stick with numeric types when possible.

mediumint it is. thanks

!m rick111 numeric types

rick111 see http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html

just founds that ^^

Hi!
How i can add new base?

concrete base?

!m Jimi default character set

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

mysql base.sql not help, because this overwrite my old base.

didek edit the file then

Ok.

change / remove the create statements to suit your needs

4", then what should i write in the query? i mean how we use date column in where

'2006-03-11 11:04:04'

?

flung, trying

How can I view the indexes associated with a certain table?

!man SHOW INDEXES

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

!man SHOW INDEX

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

thanks

indexes or indices?

both correct in English AFAIK

I solved the character set problem by converting the imput file. but I notice I still have problems with the dates. the format is 31.12.1999, can I make mysql to interpret that?

is it possible for a query to return only when there is some data to return?

not really. It will always return a recordset. But this can be an empty one and you can check for that. mysql_num_rows for instance

hi, Is it normal that LIKE returns a,Ä and A for ä too?

apollo13 depends on colation

a query will block if the table it needs is locked, right? until it is unlocked?

it mat time out and return

something like utf8_unicode_ci, would utf8_bin better?

may

oh I see the collation for the db is utf8_general_ci. Do you have any docs about this behaviour?

!man collation

see http://dev.mysql.com/doc/refman/5.0/en/collation-character-set-applicability-table.html

hm

thx

selecting a temporary table created by a stored procedure via php/mysqli will only return the first result in my script, but phpmyadmin / console does return the correct stuff?
any idea why php / mysqli acts this way?

druid remember phpmyadmin is probably using it so look at your code

http://phpfi.com/253505
this is the code and the sp

can you unlock a single table?

which utf-8 collation would I choose if a should be != ä but == A? I don't find a hint in the docs

http://pastebin.ca/640497 . Currently I have a query that lists all the 'closed' topics by closed datetime, how do I also find the message (message.*) with the highest ID for each closed topic?

apolllo13 then try the …bin collation

hmm, funny I tried it but it is not working, what should I set in my.cnf that utf8_bin is an overall default?

or try the binary keyword in the comparison

nope binary but does not help as 'A' is != 'a' then, but I'll try again

or write your own collation

hmm, I can't
are the collations described somewhere?

hi all, where can I tell mysqladmin (besides the command line switch -S) where to find the socket. Why does it look in /tmp despite the fact that I installed a ubuntu mysql-server where the socket path is /var/run/mysqld/mysql.sock
I havent touched config so far

in /etc/my.cnf there may be a line line "socket=/tmp/foo.sock" - change that to your desired setting. Maybe. But I'm just a n00b, so you might be safer to ignore me.
line like*

well, there is no /etc/my.cnf, but a /etc/mysql/my.cnf where the path is correct (i.e. not pointing to /tmp)

CrypTom /etc/mysql/my.cnf is correct

any ideas? I am still totally lost

!tell tomize about groupwise max

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

yes, but why is mysqladmin looking in /tmp when it is correctly listed in /etc/mysql/my.cnf

Anyone know how, in SQL, to say "select email_address from table1 t1, table2 t2,… where email_address appears in at least two tables"?

maybe there is a way to change all strings in table to uppercase ?

!man string fu

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

I know, it was just because catweasel wrote /etc/my.cnf, btw how can I test if there is the same problem on my system (I am using ubuntu too…, maybe I have a different conf file….)

in the processlist almost nothing happens, but the mysql hosting process uses a lot of cpu in top.. how can i find out what its doing?

apollo13, do you have mysql-server installed?

the_wench, I have table with field username where strings are uppercase and lowercase how i can make all uppercase using sql host ?

yes

and UPPER() fucnction of course

I installed it and tried to change/set the root pwd using the command: /usr/bin/mysqladmin -u root password 'verysecret'
I had to add: -S /var/run/mysqld/mysql.sock because it complained that the socket was not there (there being /tmp/mysql.sock)

Use an UPDATE sql statement.

I grepped the whole /etc/ there is no occurence of /tmp/mysql.sock

thank you very much

It works fine for me

thanks

UPDATE tbl … SET field=some_function(field);

this is an admittedly unlikely possibility, but check for a file in your homedir ~/.my.cnf that might be overriding /etc/mysql/my.cnf?

my.cnf:# Remember to edit /etc/mysql/debian.cnf when changing the socket location. Maybe debian.cnf is pointing to /tmp ?

thanks, I've already found that comment. debian.cnf is also - correctly - pointing to /var

Xgc, great! thanks!

what does a quick (for '*.sock' ) grep thorugh /etc/mysql show?

there was no .my.cnf in /root, but I was going to create it in order to store the new pwd

sry meant: sudo grep -R '.sock' *

that's what I meant earlier, I tried to grep the entire etc, there always just the correct reference

ok sry, my mistake
ubuntu, mysql version?

latest
thanks for helping, gotta go to a meeting, cu

I have got latest to. strange, cu
hmm, is there a sql hosting statement to dump connection info like collation character set etc?
btw, using utf8_bin makes everything completly case sensitiv…

SHOW VARIABLES

thx

is there a way to get the warnings from the last statement (that I can see using SHOW WARNINGS) can be inserted into a table, or written to a file?
the output from SHOW WARNINGS; looks just like a table. but I can't deal with it as a regular table

You could turn on a tee, and everything shown will get piped to a file.

how can I set the default connection collation systemwide? and does the connection does override server/db_collation?

is null?

COALESCE(val, 0)

cheers!

snoyes, ok, but will tee work with a script? and if it involves maybe 100.000 warnings, will this be slow?

Probably. Why not figure out what's wrong with the script and fix it so there are no warnings?

snoyes, what if there is nothing wrong with the script, but with the import data (different each time)?

Use the script to adjust the import data until it meets whatever requirements.

Jimi you should learn what the data size limits are

Hello together

hello folks, I'd like to know if it's possible to do a score-based text search in mysql where a search for 'setti' finds stuff like 'settings' as well.

!man fulltext

see http://dev.mysql.com/doc/refman/5.0/en/fulltext-fine-tuning.html

mhb where field like 'setti%'

My Name is andy, and i wonder if there is somebody around who has tiome to help me with a dificult mysql selcetion problem

snoyes, if the data is corrupt in some rows, how can the script fix that? all I want is to be notified of this (by noticing the warnings in the output file)
archivist, what data size limits do you mean? *confused*

You can set mysql to always show warnings. You can capture the output. Yes, if you have lots of warnings, that will be a lot of output, and maybe slow. The solution to it being slow is to make it not generate warnings, by using correct data.

I have made following SQL statement but it returns me a group error:
$sql ="SELECT Count(s.id) as total FROM $tab_event s LEFT JOIN $tab_termin t on t.vid=s.id WHERE MAX(t.ende) '".time()."' GROUP BY s.id;";

Hello!

Hallo

hallo RadioLechtal

hello the_wench

Place agregates (MAX()) in HAVING clause, not WHERE.

You cannot use aggregate functions (such as MAX) in the WHERE clause. You can use them in a HAVING clause.

let's say I have two databases, but one database has one more table, the rest are the same tables
how do I find what that table is?
I mena how do I find the name of that table?

someotherfile ; diff somefile someotherfile

If you have version 5.0, you can select it from the information_schema.tables, using the standard approach for "a not in b". If not, you can get the table list (using mysqldump or show tables) and diff them.

snoyes, you are funny. your solution of "using correct data". fits in a utopia. things can go wrong, and it is impossible to always fix all errors by machine/script. if the value is "õ^­ÿî" it cant guess what date it should use, the data is corrupt and it should log the
warning. But thanks for the 'tee' tip, will do a test with it

$sql ="SELECT Count(s.id) as total FROM $tab_event s LEFT JOIN $tab_termin t on t.vid=s.id having MAX(t.ende)'".time()."' GROUP BY s.id;";
this one is also not working

I have v.5. is there an SQL command I can give?

HAVING after GROUP BY.

Of course it's impossible to anticipate all errors. But if you know that there are going to be 100K warnings, you probably know what some of them are, and you can fix those.

mom

Is there a way to "SELECT f FROM t1, t2… WHERE f occurs in more than one table, but not necessarily all"? All ways I can think of to become really horrible with 20 tables. I'm trying to find spammers by querying all my unrelated websites' member lists, to identify people who've registered
with more than one.

mmhh - the error is away, but it is not displaying any results
..-)
I´m to new with this

That's a simple join and also an indication of a possible design problem. I'd have to know more about this.

SELECT a.table_name FROM (SELECT table_name FROM information_schema.tables WHERE table_schema = 'database1') AS a LEFT JOIN (SELECT table_name FROM information_schema.tables WHERE table_schema = 'database2') AS b USING (table_name) WHERE b.table_name IS NULL;

I have 20 "user" tables, one per site. I want to identify all email addresses that occur in more than one of those tables. If it's a simple join, that'd be cool

1

Thank you I shall try that

RadioLechtal, we dont want the php version, echo the actual sql and also you may just want a where clause not a having clause

Yes. That's what I meant by a design error.
Yuo should try to correct that. You should not have N user tables.

mom

In any case, without changing that, UNION would eb one way to go.

snoyes, it will probably never bee 100.000 warnings. I just selected a high number. but if there is a case where there are lots of warnings, I want to be able to see them all later. I have set @@max_error_count=-1;

Yes. Yes I absolutely should have N tables. I don't really want the admin of any one site to have access to the userlists of any other sites, nor any of the other private-per-client data.

output.txt

SELECT Count(s.id) as total FROM tab_event s LEFT JOIN tab_termin t on t.vid=s.id GROUP BY s.id having MAX(t.ende)'1118956787';

You should ahve some type of partitioning, if you need to treat all users as one, over all sites. You should have one logical user table and a view for each site, so that individuals from one site would not have access to another site.

this should return a nuber of 2

Particularly if a privacy leak could cause them to get arrested by their local authorities, as is the case with some of the sites for places like the middle east or China. A church website has no need to have read access to the user records for a police site, and so on.

That's just one approach.
Using separate tables doesn't solve your privacy issue at all. So don't try to use that to solve the problem.

I never need to "treat all users as one" - in fact, it is very unlikely, unless they are spammers, that anyone will ever register on more than one site.

it is a table with events and one table that holds all dates for the event, and i will count all events where all dates are in the past

Using separate databases and access passwords does address the privacy problem so far as I can see.

Back to your question. You just asked to find matches over all user tables (2 or more user tables having the same value).

I've tried FULLTEXT, read the documentation about it, but I still can't find a word when using a part of it only. Do I need to use regexp for that?

Explain how that's not treating all users as one set?

thanks snoyes

In that one single query, they are treated as one set, and the query will be run as root, who is the only user with read access to all databases. Regular site admins do not have access to any DB other than their own.

Hello everybody

You could use a regular expression, but then you don't get any ranking. A boolean fulltext, with * appended to your search term, might be what you're after.

Since this is a query that would be run monthly at most, it's hardly something for which I would optimise my database design for

this is sort of an emergency, how do I set max-connections again? php mysql web hosting 5

Yes. That's the point. If you don't want to (or think you can't) change the design, just use a UNION.

I'll try that. Thank you very much!

set-variable = max-connections = 100 or somthing like that

Like I said, thanks, and I shall try that

If I was to create a proprietary application using the MySQL++ API, would I be required to buy a commercial licence/use the GPL?

Also look into paritioning. There are some new mechanisms coming that might help here. Separate tables (by site) that can be treated as one.
partitioning

snoyes, thanxs, that worked like a charm, redirecting the output and input!

At run time, SET max_connections = 100; In the confirguration file, max_connections = 100
Probably.

Thanks, I'll look into that too

what would it hinge on?

snoyes, since when isn't it set-variable anymore, because that prevents mysql from starting up

Whether you distributed the application, or just used it on your own servers and the only thing distributed is data.

snoyes, I just simulated 130.000+ warnings, and it was finished in seconds. great

No, I would distribute it
How much does a Mysql commercial licence cost?

maybe i got to make this with two statements -URGs my brain is burning away LOL

This application is POS software
So, I'd have as many Mysql servers as there was POS in the shop (i.e, a lot)

It may still work that way too.

It might be cost prohibitive if I was obliged to buy so many licences, you see

set global

You'd have to ask someone in the sales department.

they ignored me when I suggested I didn't have to buy a licence
well
they didn't return my call

Salesmen? Ignoring someone when there's a chance of money? I don't believe it.

I don't mind paying
but
someone has to apply a little commonsense
not charge a flat, per server fee
so that my distributed model would be ten times more expensive then a central model
thats senseless

I'm sure they have some setup for that. What number did you call?

The local Mysql number
for here in Ireland
They didn't seem to have a clue
how much does a commercial licence cost?
are we talking thousands?

I don't know.

sternocera, you might not need to buy a license though
sternocera, if you just make an app that calls mysql you dont

I know, but I'm distributing the app you see

That's not necessarily true.

Its proprietary software
but I'm not re-compiling mysql

Compilation isn't important. What's important is if your application requires or includes MySQL.

sternocera, a lot will have to do then with how you are making the calls.
snoyes, exactly

through mysql++
a wrapper to the mysql library

So it seems aas if im stucking with this problem - does anybody know where i can get some help live - fx with skype or icq?

Call the Enterprise sales team, I'm sure they can get you in touch with the right folks. +1 208-514-4780

aye

are you makeing a commercial app which will be closed source?

Yes

You must have a commercial licence if it includes any of the MySQL libs.

snoyes how do I find dups in a table? parentid is what im looking for dups of

I don't mind buying a licence, provided Its no more then about $500

dupes

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

per unit

sternocera, OEM licensing is much different than Enterprise licensing

talk to MySQL. Nobody else can give you a definitive answer.

The enterprise license is £345/server/year (from https://shop.mysql.com/) - so I suspect you want to discuss "embedded application" licensing with them rather than the enterprise license.

yeah
thanks

I just realised that the (null) values in the import text file gets imported as '(null)' text for varchar fields. how do I make them truelly null?

Jimi, you are using LOAD DATA INFILE?

Replace them with \N in the text file, or adjust your import script to look for that value and replace it.

HarrisonF, yes

So - i will testing an working a bit with that having clause - Thanks to the_wetch and Xgc for helping

you can use the @var syntax with the SET clause in LOAD DATA to process the data as you load it

\N… ah, thats the one… I had tried \n before… bad case
think we talk about different things here, or how do you mean I should use the @var syntax?

LOAD DATA…(normalColumn, @nullableVarchar) SET varcharField = IF(@nullableVarchar = 'NULL', NULL, @nullableVarchar);

Hi there
Do you know upon which version mysql support store procedures?

dererk, 5.0 and above

HarrisonF, Perfect! Thanks for your time!

ah ok. thanks snoyes and HarrisonF. but will probably just fix the export to use \N

in regards to mysql 5.0, can you have a view with multiple joins, that you're allowed to update the results?

hey guys, i created a mysql table, made some changes, dropped the old table and reran my new script. a bunch of old constraints/fk's are still sitting there even though they dont exist in my new version, how do i get rid of these ??

bye bye!

a-l-p-h-a: Sometimes. You can only update one table at a time in the view. It also depends on what subqueries you have.

snoyes, well… they're all related… and there's a many:many relationship as well, which maybe tricky. I'll test it out

The constraints are on the table in question, or in other tables and refer to this table?

what is "over head" ?

empty space

Assuming you refer to the overhead reported by phpMyAdmin, it's the space left from deleting rows in the middle of the table.

why would space be left on a table ?

takes too long to remove , so left to be filled when a new row is inserted

I upgraded from 4.1 to 5.0 using the Debian packages. I'm at a loss now why mysql is running at a 100% in top when it does not seem to be under any load. I'm wondering if a I have a bad table or something that is causing it to freak out. Any ideas?

do i have to care about overhead alerts on phpmyadmin ?

Hey guys. I have a UNIX Timestamp stored into my php mysql web hosting Database for birthdays. Ideally, I'd like to make a query which will group this information by month and than sort asc by day. Ideally, this will display the birthdays for everyone in
a given month, each year. However, I'm having major problems sorting this properly. Any help?

MrBojangles, firstly you shouldn't be storing the value in the database as a unix timestamp
MySQL (and generic SQL) has TIME, DATE, DATETIME and TIMESTAMP types for a reason

Anybody have any recommendations for the best way to backup a Linux-based MySQL database?

SELECT DAYNAME(FROM_UNIXTIME(birthday)),DAYOFMONTH(FROM_UNIXTIME(birthday)) FROM people WHERE MONTH(FROM_UNIXTIME(birthday)) = 1 ORDER BY birthday ASC

TSCDan-work i do a tar and a mysqldump
both

MrBojangles, notice FROM_UNIXTIME() around all of your unix timestamps

hey hey
fatpelt you active?

Trengo, You mean tarring the actual files?

Yes.

TSCDan-work exactly
the whole /var/lib/mysql dir

Trengo, But the server must be stopped for that, correct?
Or at least locked.

no not really

But what if someone is writing to it in the middle of the backup?

its really just a belt on top of the braces
it keeps writing?
you get an incoherent backup?

Which makes it useless.

ideally youd just use a single transaction (innodb) or lock all tables in the database

want better? read up on mysql backups

or use an LVM snapshot

reset permissions
hey bot where are you?
!man reset permissions

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

reset root

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

hey archivist

HarryR, I've set the query to what you showed me and adjusted the table name. You had the field name set properly. After this query is ran, what is the proper way to view the information? I'm using PHP to run my queries.

!man resett

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

MrBojangles, either use date() inside PHP to format your unix timestamp, or format the date within MySQl

I'm fine with MySQL converting. I was able to pull the contents of the timestamp out of mysql by basically saying date('m', $row['birthday']), but I can't figure out how to sort it based on that, since MySQL does ORDER BY. I guess I'm asking is once I run the query you helped me make, how can I
display this information? I tried echo $row['DAYNAME'], with no avail. I appriciate your help by the way.

This is a really basic question, but what would the syntax be for selecting the min(id) and it's associated name

Actually, I think I answered my own question by playing around with it. Never mind.

select min(id), name from table . . .

select id, name from table where id = min(id);

hello! i have a syntax related question. i've found a query whose scheme looks like this: select count(*) from(select a from b)X
what stands the X for?
any good hints, articles or links anybody?

aliasing the table as X maybe

Trevelyan`: You can't have an aggregate function in WHERE

ooop, HAVING then

Trevelyan`: yes, you are totally right x)

Derived tables need an alias

when creating 2 tables in phpmyadmin ,, how to connect a foriegn key from table1 to primary key table2
?
seems no body … ok bye

hey seekwill

Who me?

yah hi
still havn't fixed my problem XD

Are you sure you got the right guy?

yah
Lol
You were on last night
right?

I don't remember last night..

rofl

I may have been intoxicated

lol ok

being on irc sober is like trading stocks without insider information. pointless.

hehe

haha

hi! if i have one account model, but several account types that may have different requirements not shared across the board, is it better to have tables for each account type? like Client, Customer, Employee, Admin.. things like that

still asleep ?

yes

looool

zZzzZzzZZZZzzZZzZZZ

Ok , u brought this 2urself .. answer this question then
when creating 2 tables in phpmyadmin ,, how to connect a foriegn key from table1 to primary key table2

phpmyadmin ?s put us into coma
zzzzzzzzzzzzzz

zzzzzzzzzzzzz

*connect*

I will wake u up

i don't know anything about sql, im the wrong guy to ask :]

HAHAHA

lol

NO you do

i dont!

lol! Dude, help the guy out!

SELECT min(SubMasterCategoryID) AS min_sid FROM SubMasterCategories smc JOIN MasterCategories mc ON smc.MasterCategoryID=mc.MasterCategoryID WHERE mc.name=? This doesn't return the min id, it's like that min(SubMasterCategoryID) gets ignored

hahahah

:X

EvanLugh is the expert in phpMyAdmin and foreign keys

sssh

it returns a min_sid , its just not the minimum one

haha

Yes , it's clear

lmfao
oh yes
gaxoink
gazoink*

DO I need to relate them in the structure of the desiging
of these ables

yes
tables
yes
tables
lol

or I can match them when using SELECT

SELECT=1

SELECT = 1?
really ?

yes sir
no not really
i made it up

lol
I knew that
because it doesnt make sense

i was joking i was joking

LOLLL

please dont patronise me!!
LOL

D

SELECT id, otherFields FROM table ORDER BY id LIMIT 1;

what he said

You do not have defined relationships at table creation time. You can join them as needed suring select.
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

!man joins

see http://dev.mysql.com/doc/refman/5.0/en/joins-limits.html

snoyes, where does the order by go in a join, prior to the join or after it?

after.

snoyes . you mean I should do that while SELECTING data ?

You can.

yes

CoooLLL

AFK dont worry ppls ill be back =']

I understood it ! lol

thanks guys bye

I thought I can't get any thing useful from IRC
D:

9 [ERROR] Can't start server : Bind on unix socket: Address already in use". I checked my processes and there is no mysql running at all, any help here with

but you got me

If you want foreign keys between tables, you can do that too, but you have to use InnoDB tables, and they don't gain you anything at select time; they just add constraitns when you want to insert/update/delete.

Hyjak you have the correct permissions?

AHA snoyes

EvanLugh, i chowned the directory /usr/local/mysql recursively to myself
and checked the processes as root

you mean I can delete the bunch of them and their relating data

hmm

from PK and FK
using the relating in structute

Yes, you can define what happens when you try to delete a parent row when there are children rows.

) Lol I understood again \
Thanks snoyes ,, PERFECT

and then you find a micheal jackson row, deletes the parent row and embeds itself into the child rows below

LOL

i joke i joke good thing you got it fixed =]
umm but i need the user Fatpelt

EvanLugh (Y) )F_

because it took me 1 hour to explain my problem

I thought foreign keys do provide a speed increase over indices in each table

I'm leaving now

ok bye.

will be back when getting qus
thanks guys
Bye Bye

EvanLugh, any thoughts that could help me out?

snoyes, this is weird. This query doesnt return the minimum min_sid
SELECT SubMasterCategoryID AS min_sid FROM SubMasterCategories smc JOIN MasterCategories mc ON smc.MasterCategoryID=mc.MasterCategoryID WHERE mc.name=? ORDER BY min_sid LIMIT 1

Hyjak, if you've already checked via root

flung where did you get that idea

and nothing shows up i'm clueless

snoyes, yet if I remove the LIMIT it returns 9 records

So what about anyone else, anyone can help me?

Aren't foreign keys a hash or tree of the two indices combined, so that there is no need to match the two of them together?

i need to connect to a mysql db from hosts with dynamic ip addresses, is there a safe way to do this, previously i've been using GRANT with a specific ip, but clearly this won' work here?

flung they are a description of the join idexes must exist (except for a db engine where a direct pointer exists)
flung I have seen direct pointers in db tables but not in mysql afaik (yet)

I haven't looked at mysql code. I thought in theory they were (or could be) faster

they are faster

just not in mysql

cuts out index seeks

or just not in innodb

not seen them (not looked everywhere)

I have a table with rows which serve as basic tree nodes (each row holds the id of its parent), how do I select the rows while also getting for each one the count of direct children it has?

tree
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

Does it return the lowest one that has a matching row in the other table?

snoyes, no its not ordering correctly. the limit returns a row that is true to the join, its just not the lowest one

i need to connect to a mysql db from hosts with dynamic ip addresses, is there a safe way to do this, previously i've been using GRANT with a specific ip, but clearly this won' work here?

summary of those articles is that adjacency lists don't work well with SQL. Use a preordered tree

hey guys - what's wrong with this query?
insert into email_marketing.2007_07_25_recipients(email) values (select (email) from foo.consumers where emailing_active = '1')

Drop values
Drop ( ) in your select

any ideas ?

preordered means a node has a fields with all it's parents, in order?

9 [ERROR] Can't start server : Bind on unix socket: Address already in use". I checked my processes and there is no mysql running at all, any help here with

So if you remove the limit, you get 9 other rows, and one of them has a lower id?

is there any significant precautions I should take to upgrade MySQL from 3.23 to 4.1 ??

snoyes, exactly

Can we see those rows?

snoyes, sure one sec let me dump them for you

They are secret rows

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

thanks

no, it has a left and right value

well, it's interesting that there are different ways to approach this
i'm reading it right now, thanks

hey all. does the query cache cache results for subqueries or just for the full query? i've got a subquery that i run twice as part of different full queries and i'm wondering if it would be better for me to drop that in a temp table instead of running it twice

Just full query

ok. i'll drop this out in a temp table then.
!man create

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

i need to connect to a mysql db from hosts with dynamic ip addresses, is there a safe way to do this, previously i've been using GRANT with a specific ip, but clearly this won' work here?

!man create table

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

can you use dns hostnames?

can you use dynamic dns?

fatpelt, i'm using ec2, which allocates a new ip/hostname when an instance is created, i want to be able to 'check in' that hostname/ip with mysql to access the db
will dynamic dns work for this?

your only issue would be in how often it changes and doing a 'flush hosts', but it seems that should work ok

fatpelt, what is flush hosts?

mysql is going to cache the dns to ip mapping. flush hosts clears that mapping for all hosts

If you can specify the client hostname, then it might work with dynamic dns

flung, what do you mean?

if you can connect as 'user'@'dynamic.dns.name', but I'm not sure how mysql determines the hostname

flung / megasquid : you can put hostnames in the mysql.* tables. it just does a normal dns style lookup on connection. like i said though, you'll need to flush hosts when the ip address changes or you'll loose connectivity

when the ip changes how will you do a flush hosts? and how will you put the new IP in?

after you flush hosts, it will realize that it doesn't have the information on connection and it will re-do the dns lookup
as for how to get it to flush hosts, the only way i can think of is to have a cron job that does it for you on change.

help please. how do you modify %apple%orange% to make it independent of order?

REGEXP
oh, you're saying in conjunction with dynamic dns, you can just flush hosts and it will find the new ip

Flung exactly

flung difficult

ok, I thought you were saying instead of dyndns

DavidHKMrPowers, that sort of search will be dog slow

or a self join

archivist flung , so….?

use fulltext

i mean all of the words should exist but not necessary to be in the order

so what?

col like %apple% and col like %orange%, is it still slow archivist ?

yes

can give me some hints what fulltext is for?

any like with a leading % cannot use an index

What is the difference between adding 2 indexes and adding 1 index with 2 fields?

SettlerX, depends on the queries using the indexes

you can use both fields
you can use both fields, but you can't use only the second field
2 indexes you can't use both fields

:o if A and B are indexes, can't i select A,B or using WHERE b=1 or a=1?

is there a libmysql.lib that is available for 5.0.45?
need to make a .a file from it….. can't find anywhere

you can SELECT WHERE A=1 OR B=1 but it will only use one of the indices

or both, in 5.0

only 1 index can be used in one query? E.g. if we use primary, shouldn't we use another?

I guess not

and when we have 2 fields in one index? the second field can't be accessed?

argh
ok, I'm too tired to explain

hi, the only to check if there is no more space on my tables is running SHOW TABLE STATUS from my_db; ?
way+

can anyone help me figure out some stupid permissions thing? I can login with this- mysql -u slave_user -h 66.218.55.77 -p but when I do SLAVE START; I get this–
306': Error: 'Access denied for user 'slave_user'@'66.218.55.80' (using password: YES)' errno: 1045 retry-time: 60 retries:

you probably want to manually run start slave as root?

holy cow. 86400 x ?

not if it's the only one being searched on
are you talking about the data, or the index?

oh right so did you grant the replication slave priv toi that user?

yes

i'm talking about 2 fields in one index

bsdfox__; see http://dev.mysql.com/doc/refman/5.0/en/replication-howto.html

then just to test I granted ALL permissions to slave_user on both machines

on the master?

I've followed about 5 howtos.. keep getting this stupid error
yeah on master and slave
I can login to the master from the slave machine as user slave_user no problems..

your password is wron gi guess

heh I wish it were that easy

archivist if each value is within 50 chars should i still use fulltext?

yes

So?

when you did CHANGE MASTER.. MASTER_PASSWORD.. should be the same as set on the user acount

i've read the match against thing
but…fulltext has many restriction…

use Lucene or write your own

btw, is full text faster than %apple% if i just need to search a single word?

do a SET PASSWORD to make sure its correct.. and make sure your setting it on the right user.. 'slave_user'@'%' or 'slave_user'@'66.218.55.80' or whatever you used when you granted originally

right. that's what I used

you dont have require SSL turned on do you?

I can login and use the password.. I know it's right
no

btw, is full text faster than %apple% if i just need to search a single word? archivist

are you using @'%' or @ the host?

@'%'

DavidHKMrPowers, like has to scan the entire table, fulltext is a short index lookup
tias

Try it and see, its quicker to type it on your system and try it than wait for one of us to tell you its ok

run SHOW GRANTS ON 'slave_user'@'%'; and put in pastebin or privmsg to me
(do that on the master)

ok
didn't like that syntax.. I just logged in as slave_user and did SHOW GRANTS; though

sorry still half asleep, its FOR not on

ok
that shows the same
you get my PM?

yeh

mysql -u slave_user -p -h mario.desinc.net lets me login from the slave (luigi.desinc.net) to the master (mario)

what versions are you using ?

5.0.45
5.0.45-log FreeBSD port: mysql-server-5.0.45

how can I find how much space is available my schema? (the one that I am using, for instance)

on both sides?

I only have mysql prompt access to a remote host to check it.
hints?

msaraujo, there are no standard limits to space

so, no worries about filesystem limits?

depends on the filesystem

linux ext3 probably

ZFS

IF i make queries with only one field (which are candidates to indexes) in WHERE, should I create 2 indexes instead of one with 2 fields?

I mean, I do not have accces to log files (remote host)
access+

I wish I could have zfs

archivist but…i've made the column to be a index already. is it the same?

no

fulltext doesn't seach a column in the whole table
btw…if it's 100,000 rows only, do i needa use fulltext?
since i found that there are 5-6 restrictions in fulltext…
4 chars or above….it strip out common words..etc
i've tried nutch(something from lucene)

whats so hard about putting a fulltext index on a table and trying it

looking up the syntax

I wrote my own as it was back in the days when I was using paradox

Can I add a field to an aready existing table filled with data?

yes

wonderfull
Even it that field dont accept NULL values?

set a sensible default

ahh.. Thanks clearing that out.

ho can i set listening only on address x.x.x.x ? ?

one ip or all
note that means 127.0.0.1 wont work as well as another ip

one ip

bind-address=

ok thanks

archivist may i still search %apple% in fulltext search?
i_like_apple.doc
so searcing apple should return i_like_apple.doc

the word will be split on _ i think that case so no need

What indexes should I use for this query? http://nopaste.com/p/aYfdaxFCab
it's not looking good at the moment lol

archivist but it could be ilikeapple.doc

madriss, bad explain you dont really mean that do you '$srch' —-

ok shall i just replace that with some random word

and the concat wastes time

yeah but it is needed to pull the ratings data
i have a dodgy link between the rating ID and the other ID as u can see

can't search ilikeapple.doc with %apple% in fulltext?

here's one without '$srch' http://nopaste.com/p/a3zYy20pkb
so assuming i need the concat.. how can i improve it with indexes or whatever?

it has to concat all 6443 rows in the rate table find a better way

easy to say.. but i have 6443 rows already in that format lol… and need to link them up with the data somehow
my next system has a much better rating system.. but here it has dodgy ID's so I have to use concat to link it up

rates probable ok the the need to add the C and ?

yeah its a weird system lol
its not always C

so put the C in another col

hmm.. i really dont wana completely rearrange my rating system lol

you do if you want speed

see 2007caws
when its using that table.. its a C
it could be 2007caes
then its an E
i know its a bit of a mess, but theres a new system that wil be in wider use in a few months that will be much better
would still like this to be as optimised as it can be without completely redoing it all

having a system that stops indexes being used will allways be slow

why cant it still use an index?

its in a function

hm ok

archivist help please

thanks for ur help

hi everybody

DavidHKMrPowers, I have helped but you need to understand the basics of indexing and what can and cant be done

hi would i use a foo.sql file?

archivist ya…fulltext split words to make a list of words….but can it still match %xxx% for each word?

no

:

trigramsearch!

mysql foo.sql

snoyes, thx will try that

have you written one yet domas|TW

domas|TW thanks. it's like google

or too busy playing with young ladies in TW

ladies, yes!

not very beautiful, huh?

http://commons.wikimedia.org/wiki/Image:Wikimania_2007_Domas_and_the_girls.JPG \o/

some are very good.
but generally not very good

DavidHKMrPowers, google wont find apples in ilikeapples

archivist i mean trigram search is like google

nope
google probably uses trigram search for 'did you mean' feature

seach mozillla may return mozilla
*searching

no, it does not

but you said 'did you mean'

thats comon typo finding

though it offers you to search for 'mozilla'

common

ya…i just meant it…

on the other hand
google may have found something cooler than trigram searches

hello everybody

really?

they have many clever people in there

knowing them, probably yes

researchers from universities
?

researchers from google
;-)

they started at a uni

I just had a discussion on search
was just telling to someone, "how can we get free volunteers working on search, when there're few powerhouses plus hundreds of startups"

http://forums.mysql.com/read.php?115,164990,164990#msg-164990

(that was about "wikipedia search sucks"

ya…wiki search is bad..

not as much as mysql.com search

:-)

as well as pphp.net
*php.net

you cant search for keywords like in on mysql.com

thats very stupid query for the schema like that

uh ?
the query is really slow and I have to optimize it before going on prod. again

but I'm too tired/lazy
mysql.com/enterprise \o/

lol okay
I'm not selling things, it's for my private website

heheeeeee
does it make the job any easier?

nope
but i don't care about mysql.com/enterprise

hehe, me neither
I'm on vacation \o/

me too, this is why I code !

sleep

can you load data but omit one column?

load which data ?

load data from infile

Yes

i'm sorry but I don't understand

problem is, i'm trying to load results from a table in mssql into mysql but the field time which is datetime is giving incorrect datetime value
so i want to just omit that column but i can't find out how

did you try using the mysql toolkit ?
maybe it will convert it properly?

okay, it's not for me

you should export - the column if nothing else
you can't ommit it on import

Put a user variable in the coulmn list.

xport mins*
minus*

LOAD DATA…(realField, realField, @dummy, realField)

good afternoon all, I have some questions concerning mysql optimization

everybody has one enhancer

of course they do
basically I have a box w/ a decent amount of memory
32G
running MySQL 5.0 on FreeBSD (64bit)

ok trying to change it now and need to insert the ID's from Rate into 2007caws.rateid, but only where Rate.Item = something else. What is wrong with it? INSERT INTO 2007caws (2007caws.rateid) SELECT (Rate.id) FROM Rate, 2007caws WHERE (Rate.Item = CONCAT('C',2007caws.id,'?'))

my current my.cnf: http://pastebin.mozilla.org/180252
I have executed a query
it's been sorting result for a while
on a 606mb InnoDB database
does not seem to be a high number of tmp_disk_tables
my key_reads to key_read_requests value was not jacked up

mysql_virtual_mailbox_domains.cf — host do i have to put local or my domain name.. bla.com

wrong channel for that
go ask the postfix people

its part of the mysql

no .. it's not

i see.. well tx

that is obviously postfix specific
i set up the same thing myself
there are tons of tutorials for it

my bad johnny:
i m getting an exmaple from the tutorials

hey guys
I just got what seems like a good idea for how to store multiple data entries in one row of one column

no, it's not a good idea

:-)

if each data entry needed to be 6 digits i could have one long integer
thats not bad is it?

silly

this is probably a stupid question, but can anyone vouch for the intel quad xeon's mysql performance? I'm buying a couple new servers and planning on getting those cpus but I might get dual opterons instead if they're really that much faster (cost considerably more)

dual opterons vs. quad xeon?

my only other option is to have a seperate table and put each set of 6 digits in it's own row

and the dual opterons are more expensive?
the xeons are 64-bit with em64t?

right
I'm looking at a 2.4ghz quad

One quad core Xeon vs. dual CPU dual core Opterons?

Saberu, think about the wasted time spitting the field up and the inability to have sensible indexes

yes

ahhh, i got it, i thought it was 4 cpus for the xeon, not 4 cores
bahhh, i got it, i thought it was 4 cpus for the xeon, not 4 cores/b
2-2 core opteron, 1-4 core xeon

the xeon system is about $2900, the opteron is about $3900

Go with the MacPro

hehe
i would suspect the opteron would be faster, but i can't say by how much

For $1k more, you would expect it to

I'm sure they are, with the integrated MMU

bsdfox__ I can, however, vouch with some degree of accuracy for dual DC opterons.. we run a half dozen of those (HP Proliant 385) and they are um.. fast
amazingly fast, in fact

I'm more interested in the performance of the new quad xeons
I know the opterons are blazing fast

sorry, no idea

For MySQL, you care more about disk and memory

if the memory supports full interleave you can't beat the opterons, double the accesses
indeed, which is why opterons are good - number crunching is hardly an issue for mysql
raw powerz0rz
or, faster pipelines, for most poeple
*people

Power usage and temp is such a big thing these days

hey can anyone help me? I have a table called transactions that has an "amount" attribute. It also has a "type" column which is either "Credit" or "Debit".. Is there any way to return the records of users whose credits are greater than their debits?

heh

attribute ? column ?

I mean column instead of attribute
I can sum up the amount of records whose type is "Credit" and I can do the same for "Debit", but I don't know how to calculate the difference of them in a single query, and return only the records where the credits are greater than the debits

debits;

0;

hehe

mine's shorter!

vive la difference

most people don't brag about that HarrisonF

Mine accounts for an unknown type.
I dunno, had to think of some reason.

thanks

0;

is an enum() function for storing data in the table faster than storing just integers then having an index in your script to assign the int to each data value?

and that even uses nested IF statements!
bsdfox__, perl people often brag about having it shorter than others too

Probably, but less flexible.

ok thanks! Speed is everything hehe ^^

If the list of possible values will never ever change, and it's reasonably short, and you won't get confused by the funky sorting that results, enum is fine.

is there any way to also display the difference between credits and debits?
using credits - debits results in Unknown column 'credits' in 'field list'

i have a quick question - when using ALTER TABLE to ADD a column, is there a "IF NOT EXIST" option or similar?

!man alter table

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

if it's not there, then no

yes - I've been to the help page - I was looking for an easy answer without having to read down through every minute detail of that very packed page

we dont want to read it for you

no, I wouldn't expect you to - I was hoping there were experts in the channel who just knew the answer
clearly there are no such helpful experts here - and I apologize for having wasted your time

reading tightly packed pages is what people who work with such techie stuff as dbs do

hehe

two questions, are you here for help.. or to help
if you choose for help, then it asks you if you've done such things..
like read docs, whatever
and then when you come in , your answers are shown to moderators
if you obviously lied
then you can be k/b easily
perhaps by even non moderators

there were thoughts to add more language ability to the bot for common questions

it'd be easier to put them in a web page

is this a support chat room?

i had really good experience with the muckl script
and xmpp based rooms
it uses HTTP binding and a js library.. so it stays nice and updated as people talk
instead of polling

what is the syntax to disable errors - meaning if a command fails, keep going

what command?

alter
i want to add a column, if it does not already exist

good question
i don't know

!man alter

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

seekwill, too late

Typically, that kind of query you don't want to be really automated.
How am I too late?

is the_wench a bot?

i already did it

Hell no!

yes..

What did you do?

aaa!man alter for him

I didn't see it

we but he doesnt want to read it

does the the_wench require the ! to be first char? or does it ltrim ?

no

to which?

depends on the need

i noticed that you gave me links to the page - I challenge you to demonstrate that the answers to my questions are in there

lol
Cute

kek

turing

Yay I passed the test. I am human after all

The fact that there is no answer is an answer in of itself.

man alter table

for man yes

!man alter table
ok.. there's my answer..
it must be first char

that's excellent - so then here's my new question: given an arbitrary query, how does one hide errors?
so that the SQL will continue on failure

IGNORE is a MySQL extension to standard SQL. It controls how ALTER TABLE works if there are duplicates on unique keys in the new table or if warnings occur when strict mode is enabled. If IGNORE is not specified, the copy is aborted and rolled back if duplicate-key errors occur. If IGNORE is
specified, only the first row is used of rows with duplicates on a unique key, The other conflicting rows are deleted. Incorrect values are truncated to the closes
t matching acceptable value.

GRANT SELECT ON mailserver.*
TO mac@localhost or bal.com
IDENTIFIED BY 'mac';

boy, you type fast

johnny, but also its reading the chan so responds to other factoids not in the mysql manual

I have twelve hamsters typing

EPA inc

You can suppress errors in your code as well

archivist, for example?

reset root

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

bye

see ya

seekwill well, yes - you need at least 6 to reach all the keys, so having a dozen is like typing with two fingers, really

yes - that's what I'm looking for - is the syntax "SUPPRESS ERRORS" or similar? I'm happy to read the manual - once I know what to search for

so i was talking to my friend about how to reset root, and he said…
lol.. i'm abusing your bot…

TO do i put localhost or the bal.com?

oh boy

lol

go ?

is that what you going to do go?

adaptr, they have 4 feet tho ..

well, go

johnny and you imagine they can use all 4 for coordinated typing ?

sure

cats, maybe, but gerbils ?

if hamsters can type, they must be some sort of trained hamsters

they're bascially helpless little twits

or magic ones
they survived this long
on the earth without going extinct, they must have something going for them

until they bite you, of course

cats paws are too big for typing on a normal keyboard..

you guys cannot even help
fuck
its a fuckin simple yes or no question

`mac`, nobody answers my question either yet i don't bitch about it

oh great now you got the fuckin power
do wtf you wan a do

How about be more considerate? No one here has to help you?

i get you..

`mac`, i promise if you answer mine, i'll answer yours ..

i help others
why cant people help me
i m not asking damm to much
till i get this right

plus your name is annoying to type
with the `

Stop bitching and whining

johnny fuckin boy shut up

lol
seekwill, please?

like i like you name

just for a moment?

fuck off

`mac`, only you know your machine name

let me lookin to that

`mac`, now you'll never get an answer from me
ever

'cept for your public one, and that can be banned in a matter of moments…

i don't give a fuckk

3

Please watch the language. It hurts my eyes

sorry

oh what a sad little bigot

we don't need your none since commanets
concordantly while you first question may be the most pertanant you may or may not realize it is the most erelevant

aloha?
ah, works now

people like that don't deserve help

like you where helping

who me?

speak for yourself, little one - there's no "we" here as far as you're concerned

not jonny boy

that's cuz you weren't askin any kinda decent question

Let's drop this

ok..
let me put him ignore

that's just disrespectful

—› Now ignoring (johnny) for

so, who wants to help me with my question

what was it, now ?

announcing your ignores, especially multiple times, is a faux pas.

I'm deaf from all the noise

i've got a nested set style table
that stores multiple trees

heh

aaah relations up the wazooo.. bliss

and i'm attempting to query it in such a way that i get first level results sorted by some criteria

Don't want to wait for Xgc?

saner design

Or new question?

same, but if he wanted to answer, he would have by now

—› Now ignoring (adaptr)

load date infile 'login.csv' into table login fields terminated by ','; error 1261 row 238 doesn't contain data for all columns there is no row 238

`mac`: You don't need to announce your ignores… just ignore them.

if was op.. that kinda behaviour deserves a k/b

ok..

That's why you're not an op

thank god

no.. i'm not an op cuz i don't here

haliluya

err don't come in here
often enough

haha, right?

this is something you really have to sink time into

Not really

i just don't get it i m readint his example file

trust me, that's hardly an important criterium

it says to put local..
so i m just wan a make sure..

`mac`: Just trying it isn't going to hurt
`mac`: What is the question?

I'm gstarting to worry, the way he keeps talking to himself

some people are too nice i guess

i try both.. it says access deny

i run support chats elsewhere, and it gets rough with some people

Oh we like it rough

people sure get demanding of other people's free time

Hi to everybody

so if I tell `mac` that he's a little turd, he won't hear me now?

hello snuki_mac_de

(just checking)

who can help me to install mysql on a macbook

He may not hear you, but I can!

http://dpaste.com/15687/

seekwill and ?

the example to make it like that.. but i m not sure what to use in the host = feild

can i get help with mysql querys here

seekwill, i don't try to see people out for direct questions, that just puts them on the spot

ask

"I have a question", Don't ask: "Is anyone around?" or "Can anyone help?". Just Ask The Question
"Is anyone around?" or "Can anyone help?". Just Ask The Question/b

ok

good one..

http://dpaste.com/15687/
new post

how do I start mysql, I try "mysql start" but it is not working

johnny, adapter I shall have to put the back door in the bot like fajita

did you run mysql_install_db ?
fajita ?

another chan

`mac`: Put in host how postfix will access your mysql server. So if MySQL is on the same server, use localhsot