| |
| |
| |
|
Page: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18
<shabbs> !perror -1
<shabbs> heh
<gwidion> Mahound: what do you do for inserting?
<shabbs> somethin's really messed up here
<Mahound> LOAD DATA INFILE
<gwidion> ok - that won't work for a straight update - but you can LOAD DATA INFILE to a temporary table, from which to perform an update query.
<teite> shabbs: maybe you should dump your stuff ;)
<shabbs> teite: that's the problem, server crashes during a dump :)
<teite> shabbs: i see ;)
<Leithal> shabbs: How about a SELECT .. INTO OUTFILE?
<shabbs> haven't tried that
<Leithal> If I were you I would be trying right about now
<teite> Leithal: whats the difference between mysqldump and select ... into outfile?
<gwidion> Mahound: another way iis to mount you r CSV file staright as a table with the CSV engine. I never tried this, bit shall be no harder than creating a table with structure equal toyour CSV file, and engine=CSV, and then overwriting the CSV file MySQKL creates with yours, and flushing the tables
<Darien> LOAD DATA INFILE is the best way
<Darien> bring it in to a real database
<Leithal> INTO OUTFILE and LOAD DATA INFILE, as Darien notes is "best" (faster)
<Leithal> They are done in two very different ways as well
<jimmyxx> hihi, can anyone help - I'm stumped by a really basic problem: I've just created a myisam table - a column called keywords - with just keywords in, I've set it as fulltext - but its not returning any results when I query it, the cardinality of the column was 0 - ive just repaired the table now it says 1, for some reason fulltext is working?! any ideas? thanks
<shabbs> !man load data
<SQL> (LOAD DATA INFILE Syntax) : http://dev.mysql.com/doc/mysql/en/LOAD_DATA.html
<Leithal> One goes and get's all the table information etc. (mysqldump), the other does not - shabbs mentioned earlier that selects are fine, mysqldump crashes, I'm wondering perhaps whether he has some screwed table information as well as / instead of data corruption
<Leithal> select into outfile should pinpoint that
<teite> i see
<jimmyxx> anyone?
<tradergt> beuller?
<Leithal> jimmyxx: Is there one value in the table?
<jimmyxx> there are two rows each with about 10 keywords in
<Leithal> There's your problem
<jimmyxx> shouldn't the cardinality be 2 if there are two rows each containing unique keywords?
<Leithal> Add more rows, and everything should start working
<jimmyxx> ah right
<jimmyxx> cool ill do that now - thanks!
<Leithal> (if a keyword is in >= 50% of the rows, it is not indexed)
<Leithal> 2 rows.. 50/50 split
<teite> hmm, how long should a ibbackup from a 100gb database take?
<teite> 100gb innodb tablespaces
<jimmyxx> than Leithal
<tradergt> teite: 2 mins
<teite> tradergt: hehe funny ;)
<tradergt> teite: it depends on many factors, how long is it taking?
<teite> it's about 90 minutes
<teite> it's not too bad
<armakuni> Anyone know if it is possible to order by a substring? The field lools like this: "PoH 1995: 3 44-49" and I would need to order by year...
<inviso> armakuni: yes, but it ignores indexes.
<inviso> armakuni: break your data apart into several fields and you'll be better off.
<armakuni> Yeah, I would too. Problem is its not med who made tha table..
<teite> just wonder how i could improve the backup speed
<inviso> armakuni: then fix it?
<armakuni> 40 000 posts...
<tradergt> teite: netapps?
<ZiNoX> does mysql 5.0.15 over MyISAM engine support Constrains? (PK,FK,Unique)
<teite> tradergt: netapp filer?
<tradergt> teite: its all io, so faster disks, things of that nature
<tradergt> if its on scsi, using two scsi disks would be faster
<inviso> armakuni: 1) alter table 2) update table set year=split_year_out(the_ugly_field) ... Amount of data doesn't really matter.
<teite> tradergt: maybe it's cpu, compressing takes time
<gwidion> armanuki : It still willbe faster to fix the table once, and have an indexed query everytime later.
<teite> but i dont have enough disk space localyy
<tradergt> I have 400g here and it takes about 3-4 hours depending on how active the db is
<teite> i could mount a nfs storage and do the backup their
<armakuni> inviso : Maybe thats what I should do.
<teite> without compression
<shabbs> ok, I think I need to drop all of my innodb tables and just delete the tablespace or somethin
<tradergt> your db server slow or something?
<inviso> armakuni: yeah, it would make a big difference in query speeds. If you need some help getting it done, let us know.
<ZiNoX> does mysql 5.0.15 over MyISAM engine support Constrains? (PK,FK,Unique)
<teite> tradergt: no, it's idle in backup time
<gwidion> ZiNoX: Not FK, Unique and PK,a re allright.
<shabbs> ZiNoX: it supports PK and Unique, not FK
<tradergt> why you using ibbackup if your taking down the server anyway
<gwidion> armakuni: Not to mention a difference for retrieving the data with simpler queries as well.
<armakuni> inviso : thanx. but If I still wanted to learn how to order by a substring. how would I do that?
<teite> tradergt: the server runs still, but not many users are online that time
<gwidion> armakuni: ORDER BY substring(filed_name, index_1,index2)
<tradergt> the compression shouldbt be a problem
<ZiNoX> gwidion: well, my DBA has made a relation using PK and FK in a table, then performed a query and it worked fine!
<teite> i just see that the disk has busy % about 50%
<teite> ZiNoX: the fk is a fake
<ZiNoX> teite: how come! can u explain?
<armakuni> gwidion : ok. that seems easy enough. problem is the first part varies between two and three characters...
<gwidion> ZiNoX: The sintax for FK is accepeted in the MyISAM engine, but does not work as so. However, it is not hard to perceive that any app that uses FK has to be coherent to it's usage nonetheless.
<gwidion> ZiNoX: So, unless your app was trying to insert rubish, like invalid FKs, it would work
<armakuni> i would need to order by the four characters following on a space
<gwidion> armakuni: Thatstarts to get a bit uglier - you have to find the index for the space char preceding the year and the ": " after it something like (me tries statement first)
<ZiNoX> so if MyISAM is not supporting FK which i do build relations with, what is it good for then! :S
<teite> ZiNoX: use innodb if you need fk
<teite> afaik there is no big change for myisam storage with mysql5, is it?
<Veratien> Right. MySQL has just packed up and died for no bloody reason. I am now getting this error: ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2). I've restarted the pissing daemon at least 30 times in the hope that it's PMT but NO, it's STILL not working. I have not changed anything. I have not touched anything. There is no reason that the MySQL daemon should not be working. WHY DID IT BREAK
<Veratien> !?! >:@
<Veratien> </stressed sysadmin>
<archivist> calm down and look at the error log in the data dir
<ZiNoX> anyone with experience in making ERP over MySQL DB?
<gwidion> armanuki: still there?
<armakuni> yep
<gwidion> armanuki: I got confused with tjhe "position" syntax for a while.
<gwidion> Here it is:
<gwidion> armanuki: ORDER BY substring(field,locate(' ',field),4);
<gwidion> This same substring expression is what you should use to create an "year" field on the same table/
<SpComb> :o
<SpComb> shouldnt a channel like this block colours?
<Veratien> archivist: What error log am I looking for? Can't find it... :/
<Veratien> Reasons you shouldn't use Plesk: It moves things into stupid places...
<gwidion> armakuni: it is important that you understand what it does: the "substring" function pick s a sting *** first argument, a position as second, a lenght as third. We p*** as "position" the result of the locate function, which returns the position of the first space in the string.
<gwidion> armakuni: If you say what went wrong it will be easier for me to say what to change.
<armakuni> $sql="select * from tidsort where (nyckel1 like '".$_REQUEST['search_word']."%' or nyckel2 like '".$_REQUEST['search_word']."%' or nyckel3 like '".$_REQUEST['search_word']."%') and referens != '' order by ORDER BY substring(referens,locate(' ',referens),4) desc limit $start, $inlaggPerSida";
<Solara> What would i set a text type to fit 2.5 mb of text in it?
<gwidion> armakuni: You just post one example of the referens field. I ***umed that all have the author initials,a singgle space, then a 4 digit year.
<gwidion> armakuni: You can check it putting " substring(referens,locate(' ',referens),4) as year " in the column par of the sect statement and printing it out to see what is happening.
<gwidion> armakuni: oh, just wait...did you put a space between the ' ' ?
<grogoreo> hi
<hhoffman_> anyone have problems compiling DBD::mysql for mysql 5 under Redhat AS3?
<gwidion> armakuni: It won't work ths way.
<gwidion> you really should extract the yera to a separete column then.
<grogoreo> I have two tables, members and writers. I want to link the username field in members with the same in writers. I want the username field in members to be the primary key (*) and the username field in writers as the # (sorry I can't remember the name!). Like in Access you can set up relationships. How would I go about doing this in MySQL?
<gwidion> armanuki: you can use the very same expression as part of an update query. But I guess that iof some return invalid, you willbe better running this update query manually on each row using your client progrmaing language.
<gwidion> armakuni: Not - it will work with a single mysql statement.
<gwidion> (i tried here with an empty string,a nd it returns no error)
<gwidion> armakuni: not hard - just enter into the MySQL client (it will be better than through PHP )
<Veratien> Does anyone else know what might cause MySQL to just die?
<gwidion> and do : alter table tidsort add column year char(4);
<Veratien> Since I can't find any errors in the mysqld.log file, or any client log files...
<gwidion> and then UPDATE tidsort SET year=substring(referens,locate(' ',referens,4));
<gwidion> select * from referens limit 30; to check
<gwidion> armakuni: oops - I forgot- one have to add "1" to that index.
<gwidion> re-issue the update statement like this:
<gwidion> armanuki: UPDATE tidsort SET year=substring(referens,locate(' ',referens) + 1,4) );
<armakuni> near )
<gwidion> UPDATE tidsort SET year=substring(referens,locate(' ',referens) + 1,4 ); even...
<grogoreo> do I have to use a InnoDB type table to do relationships?
<jiggster> no?
<gwidion> hmm? and querying then says what?
<armakuni> if i emptied the column and did: UPDATE tidsort SET year=substring(referens,locate(' ',referens,5)); then?
<gwidion> you may try - but this statement is wrong - it just got you the 3 digits by chance - the first closing ")' is in the wrong place.
<gwidion> try it - you do not even have to empty the cloumn before - it will be overwritten.
<gwidion> I undrestood it now, without the third parameter to substring, it selects up to string end - but only the first 4 chars are stored in the column (counting the empty space)(
<gwidion> substring(referens,locate(' ',referens)+1); then
Return to mysql
or
Go to some related
logs:
politics
football
unixboard
tranny rar files
|
|