Im using an application that has MySQL as the backend We can no longer add new records without getting the error
!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
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
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
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
) but i'm looking at src now
