I have a question Say I have a table with a column names and I want to call up 10 results by a search query but
read_log_event(): ‘Event too big’, data_len: 1187290165, event_type: -52
Could not read entry at offset 115378:Error in log format or read error
is max_packet set the same on master and slave?
CONSTRAINT `0_49` FOREIGN KEY (`component`, `product`) REFERENCES `components` (`value`, (SELECT name from products where id = `product_id`)) ON UPDATE CASCADE
or somthing like that
??
where should I check ?
MarkR42 and gnari – Thanks for the time – I’ll look into the derived tables
In your server config, my.cnf
MarkR42: probably a load data infile statement, the loaded file is stored in the bin log
1 GB
That should work
1G
need to increase max_allowed_packet
there are a bunch of settings which really, really need to be the same on master and slave in my.cnf
Really the whole thign should be the same, except server_id, which must be different
max_allowed_packet = 1M
Right, but is it higher on the master?
no, it’s the same
increase that to 1.5G and see if it works
And it was forever, including when that binlog was written?
yes, I never changed it
but i guess even the bin log is only 1GB in size each file
the binlog is about 100 mb in every file
i love you MySQL
good day
1187290165 is 1 GB right?
Anyone who know if its possible to do SELECT from within the FOREIGN KEY REFERENCES tag?
what for?
1
thats 1 GB right?
updating old mysql database ( bugzilla ) and there are different columns now
ops 100 mb
kimseong, 1Gb is 1073741824 bytes
this is the old one: CONSTRAINT `0_118` FOREIGN KEY (`component`, `product`) REFERENCES `components` (`value`, `program`) ON UPDATE CASCADE
1000A GigaByte is 1,073,741,824 Bytes or 1,024 MegaBytes.
zOMG GOOGLE THANKz
I just want to confirm that the number is in range of 1 GB, don’t want the exact value
so, if I increase the allowed packet size to 1.5G will the problem solve itself ?
need the `program` to be SELECT name FROM products WHERE id = `product_id`– from components table
after I restart both servers ?
hopefully
only the slave will do
1 GiBi is 1,073,741,824 bytes, or 1024 MiBi. 1 GB every now and then means 1000000000 bytes, or 1000 MB, and every now and then it’s the same as a GiBi
it depends who’s talking
and puppies are cute
so I should change it only on the slave ?
and what they’re talking about
if it’s an HDD manufacturer they use 1000MB, that way they can say that their HDD is bigger that it actually is
why would someone ever want to base64 encode database records?
what’s a *quick* way to find a set of fields present in one table and not in another?
information_Schema
query?
work it out yourself, information_schema.columns
wow – thats some nice stuff right there – Thanks a bunch !
sorry, didn’t ask the question right
Jippi, the subselect in the FROM clause, aliased as `cnts` is the derived table
I changed that setting
and restarted the server
the error is still there
El_Neng, NOT IN() , or WHERE NOT EXISTS(), or LEFT JOIN with WHERE y.id IS NULL
e.g. i have two tables `credits` and `credit_channels` – would this work?: insert into credit_channels (mfo, channel_no, channel_rep) values (select distinct MFO, chan, chan_rep from credits WHERE credits.mfo != credit_channels.mfo)
?
if that fail, re setup your slave again
Its very nice – though it only select rows where the count is above 0 ?
El_Neng, skip the VALUES part
ok, what i’m concerned with is that credits has 300,000 records and about 30 columns
Jippi, change the JOIN to a LEFT JOIN
i think that might actually make the server grind to a hold
halt
impossible, they are in different locations and the db is about 200G
ah, of course
isn’t there any command that would instruct the slave to re-download the binary log ?
pastebin the show slave status
Jippi, and probably change the IF() test to cnts.shopCount IS NULL
is there a better way to do this than credits.mfo != credit_channels.mfo?
chan & chan_rep are tied to a particular mfo
using a stored procedure to automatically base64 encode and decode all input and output to the database
Yeah, thanks !
El_Neng, wait. explain again exactly what you ant
want
two tables – credits and credit channels
can you check the size of the file cdr2-relay-bin.003101
credits contains mfo, channel_num and channel_rep fields, that are not present in credits_channels
4
channel_num and channel_rep are tied to an MFO
i need to import the missing MFO, channel_rep and channel_num from credits into credits_channels
El_Neng, so you want to insert missing records into credits_channels
are you able to stop the mysqld slave and make a duplicates of the whole datadir?
no, as I said, the db is 200G
as backup before trying something
aha, ok, this I could do
backup on the same server
gnari, hi
If you are using myisam exclusively, you can do a lock tables then backup the datadir, then unlock them (Don’t forget to save the binlog position)
El_Neng, do you want only one record in credits_channels for each MFO or for each (MFO,channel_num,channel_rep) combination ?
If you are using innodb exclusively, you can use mysqldump with a transaction
However, on a 200G database that is going to take a while, like many hours
I stopped the server and now I’m copying the data
and what should I do after that ?
Well, set up the slave in the normal way
damn GPRS
use CHANGE MASTER
gnari, it dropped after
would this work better? insert into credit_channels (mfo, channel_no, channel_rep) (select distinct MFO, chan, chan_rep from credits WHERE credits.mfo NOT IN (select distinct MFO from credits))
But when copying data from the master to the slave, always remember to store the bin log and position it was stopped at
otherwise you’re in trouble
It is *highly* recommended to have more than one slave
I cannot do that
that way, if one slave breaks, you can recover it from the other
they are in different locations
without taking the master down
In which case, have another slave somewhere else
after that, stop slave; reset slave; change master to …. mysql-bin.001516, 4725345
the slave has executed everything till that point, so restart slave from that point and see if that works
yes, I’d like that, but when my boss hates the word “investment”
_El_Neng, no that would not work
Yes, well, a 200G database sounds important
should I delete any binary log ?
I assume you actually require some availability on it?
yes, but I barely convinced them that we need a replication server
Really, you need two replicas
is credit_channels empty before the query?
otherwise if one replica goes wrong, you can’t recover it without affecting the master
sucks
it’s a really bad implementation of replication
hi
well, 5.1 has row-based replication which is less susceptible to nondeterministic queries
is it stable yet ?
Personally, I rather like MySQL replication, it is quite straightforward and really useful
No, 5.1 is not in GA yet
well, it’s not quite straightforward
occasionaly my utf8 chars get replaced by questionmarks
I just have a pb with replication 
for example, let’s assume someone fscked a table on the slave
any solution to that?
the server said there is a crash in a table on the slave
I would think that there is a command to get that table from the master
and now it’s ok but the replication is locked !
If you have a smallish table which is bust on the slave, you can reload it on the master and have those commands replicate out
may I have to remake all the binary from mater ?
So suppose it’s a small table, you can just dump and reload the table in the master and those commands will go out
What does SHOW SLAVE STATUS\G show on the slave?
_El_Neng, if i understand what you want to do correctly, namely you want to insert into table B those rows (x,y,z) from A that are not already present in B. you want only ine of each (x,y,z) combination
Reconnecting after a failed master event read
almost all the solutions I got here to replication problems were applicable to small databases
_El_Neng, are you still there?
It is true it is easier with small databases, fixing a broken table is not that hard, unless it’s really big
Do the comms work between your slave and master? Can you log on to the master interactively from the slave box?
insert into B select distinct x,y,z FROM A WHERE NOT EXISTS (select 1 from B where b.x=A.x and B.y=A.y and B.z=A.z)
do you have an idea when 5.1 will be stable ?
and, will it have a command like “get all data from a table to the slave” ?
i’m testing
No, it won’t have a command like “get all data from a table to the slave”
However, you can implement that command yourself fairly easily, but it will take a long time if the table is big
MarkR42, yes it cans
actually I need something like “get all new data from master to slave (only to a table)”
Well, your best bet I would say, is to write a script which locks all tables on the master, waits for the slave to catch up to that point (you can use the wait slave pos function for this), then dumps the table on the master at that point having stopped the slave, reloads the table on the
slave and restarts the slave
the slave has executed everything till that point, so restart slave from that point and see if that works
You’d have to do the “lock all tables” trick because it’s absolutely vital that it gets loaded at the right point
after that, stop slave; reset slave; change master to …. mysql-bin.001516, 4725345
MarkR42, have u got an idea about that ?
confirm these bin log still in the master
MarkR42, slave and master use the same version and then run well since 5 days
Well, have a look at mysql.log that sometimes contains more info
I wonder why a day after an outage that something as basic as a dns server has not been fixed “25.0% of queries will end in failure at 213.136.49.179 (dns2.mysql.com) – query timed out”
ok
I have to enable that
No, it’s normally enabled
It may be that your master details for the slave are wrong in some way, e.g. password has changed, wrong host name etc
which is causing the io thread to fail
it’s curious
I don’t update the slave since the bug occure
I don’t mean the query log, I mean the server error log
ok
Don’t enable the general query log
sorry
what is the definition for mysql error log ?
i’m lost
It’s normally put in /var/log/mysql.log or something
nop
.err
I don’t have that
or some systems shove it in syslog
is it define in the /etc/mysql/my.cnf ?
ok syslog
[ERROR] Error reading packet from server: Got packet bigger than ‘max_allowed_packet’ bytes ( server_errno=2020)
,replication resumed in log ‘lanai-bin.000065′ at position 10686733
ok
and so … how to well configure the slave to use this master on that point ?
max_allowed_packet = 16M
master
1M on slave
I have just to have the same value ?
Yes, definitely
Fortunately, you can just stop the slave, change the value and when it restarts it should continue
hey i have a problem with character set
what would be the right character set for spanish?
great …
thank you !!!
utf8. utf8 is right for everything.
mysql messes my utf8
In which case you have made an error somewhere in your application- you should fix it so it works with utf8
no, it works
but some record get characters replaced with questionmarks
mysql does not “mess your utf8″
In which case, you are using it incorrectly in some way
it only happens occasionally
i already changed to utf8 but still dont show á / é / í / ó /ú / ñ
In which case you’ve done something wrong. You need to ensure that your application uses utf8 consistently, your tables are all utf8, AND your mysql client charset it utf8 (if your app is expecting it to be)
In practice, these things are not difficult, yet people seem to get them wrong all the time
hi there
Do not, under any circumstances, store utf8 data in a latin1 column
Like Wordpress does!
The problem most people have is that they convert to using utf8 but still continue to use latin1 as their client charset- don’t do this- issue SET NAMES utf8
These sorts of mistakes can lead to a database full of rubbish
mysqldump -u user -p pass -a -e stage_de8. unfortunately all umlaute are gone. btw he is the admin and now i have to tell him how to export that database 
is this just the wrong command?
Probably nothing. mysqldump does not change the encoding of anything.
Assuming you backed it up and restored it correctly with mysqldump, no encoding should change in any way
yeah, i checked the syntax he used and for me everything seems to be fine. but when i open the .sql file there are just “,” for the umlaute
mysqldump files are NOT text files suitable for loading in your editor
your editor probably doesn’t understand the encoding
mysqldump files should be treated as binary. On no account edit them with a text editor
im on a mac and i opened it with coda and textmate.
Well, don’t open it with anything other than mysql client
ok. i will try that.
If the file has ever been saved in an editor, it is probably ruined.
i didnt get how to store data in a latin1 column
hi , is there a quick way to duplicate record into the same table ?
Convert all tables to utf8. SET NAMES utf8. Get the client app to use utf8 throughout. It will just work.
MarkR42 ok, then i will just extract it from the archive and insert it directly into the db. can i use phpmyadmin for that? or do i have to use the command line tool?
On no account use phpmyadmin.
Use the command line tool to insert it into your local dev database. Then you can examine the data for umlaute
Make sure that whoever took the dump didn’t use phpmyadmin. I don’t trust it to work correctly.
btw that is normally not my job, but now i have to deal with it now
i converted the field to utf8 but stills when i show it to the browser, it does not works
when i changed myisam to innodb via alter table.. do i have to reindex it?
Sorry just Disconnected dont know if my message got though so i will resend it
is it possible to say have PMarage.`HusbGivenName` As `FatherGivenName`, PMarage.`HusbSurname` As `FatherSurname`, say be joined and returned as one result?
That is due to some inconsistency. Your application must use utf8 consistently throughout.
When you change engine, all the indexes will be recreated in the new engine.
heh… was not mysql bug
thank you for support
thanks..
ok i’ll change all tables to utf8, there is any fast way to do that?
ALTER TABLE on each table
mysql administrator
I’m afraid an ALTER TABLE will take a long time on a large table
is there a quick way to duplicate records into the same table ?
Don’t use GUIs to make changes to database structure. Script every change so you can successfully apply it to production in the same way
Absolutely ANY schema change needs to be scripted carefully so you can reproduce it exactly in your production environment
otherwise development and production will quickly become hopelessly out of sync
ok, so should i go scripting in pure shell?
datebase.mysql.dump. can i use this command anyway: mysql -u USER -p DBNAME dump.sql ?
No, SQL scripts, obviously.
Probably.
guess i’ll just try it out
You will want to alter every table to charset utf8. This means you need one command per table. You should obviously script this operation so you can do the same on production (and any other environments)
ok, what’s the script for changing a table’s character set?
Obviously before such a change, back up the database
Read the ALTER TABLE doc, it tells you
You really want to test this script very carefully in your dev environment to ensure it’s right
sure, i’ll make a backup first
Then you can restore the backup if there is a problem, so you can start again from fresh
Once the script works, you can apply it to your production environment, after all necessary testing etc.
How can you join two results together in mysql to form one result?
The importance of scripting and testing these changes
kieran491, UNION
In any of the standard SQL ways
ok, i’ll try it out
not using union to say pull a and b out but as one result
so not |a|b| but |a b| or |ab|
but i am still confuse, the database shows the correct characters inside the database (if that make any sense), but when i show it in the browser there is the problem
Probably there is either some unnecessary conversion happening, or the browser is being told the character set is something other than what it is
What character set does the browser think it is? What mysql client charset are you using?
like how php all you have to do is $newvalue = $a . $b thats all i am after a method of doing somthing like that
kieran491, CONCAT?
well the character set that appears in administrator gui is cp152 wester european
then i tried change it to utf8 one of the fields with that kind of characters and nothing happen
Hi, I have an old MySQL directory contains the ibdata and iblogfile files, etc…
how can I insert them into a new database?
Just what i needed niuq thanks
what do you mean?
Hey guys, how could I drop all the tables matching certain prefix?
pmjdebruijn, run a mysqld against that set and mysqldump it
correct version as well
can I store a RTF file in mysql db? what type of field should I set it to?
ok, but there are no tools to do that automatically?
no
ok
Billium, throw that into TEXT field
not binary?
no need for binary
rtf is plain text
no
rtf is formatted text
uhm. I mean it is coded as text
oh
encode
d
so I won’t loose the formatting by storing it as text
nope
excellent
MarkR42 thanks for the tips worked out fine. thank you.
What is the best way to have ‘enable/disable’ column in a table – enum(y,n) or 1,0 tinyint(1) ? (or something third?)
Jippi, tinyint(1)
Allright
*changes from enum to tinyint(1) *
hi i need help with a problem of characters
I expect the problem is in your application, not mysql
what language is it written in? Does that language support unicode properly? (Hint: Java does, PHP does not, Perl sortof.)
how could it be the application?, i am just making a query
i am using PHP
How are you making a query?
using php
Right, PHP has no understanding of encodings
Therefore it is up to you to ensure that everything is consistent, and to ensure that no unwanted conversions happen
yeah?
You must also ensure that the browser uses the right encoding to interpret the output
how can i verify that?
AFter you connect to the database, always set the character set with SET NAMES utf
“SET NAMES utf8″
Web browsers typically haev a feature to show what encoding they think the page is in
In mozilla, use “page info”
Or “View page info”
ok i am looking for that in mozilla
page info” or the context menu
it says encoding utf8
(In my version of Firefox)
That’s good
Then you must do “SET NAMES utf8″ after each database connection
ok
how can i do that?
otherwise mysql may be doing some conversion, if it thinks you want latin1
it’s a php command?
or mysql?
No, a MySQL
Issue that command on the connection immediately after connecting.
Also check that it worked
you know that command?
I am not going to teach you PHP
i thought you said it was mysql…
GRRR
I think I shall give up now
lol
plz don’t
ok give me a sec, i’ll search the command
mysql_query(“SET NAMES utf8″);
^^ php
You are obviously issuing SQL commands elsewhere in your application
thx buddy ^^
Jester-|: That is not the only way, depends what AP niuq uses
just do that after each one
Use the same API and the same connection, make sure you issue SET NAMES utf8; BEFORE anything else with that connection
ok mark, i’ll try it out
morning
i putted that query after my connection, and still not working
Are you absolutely sure it’s using the same connection, and that command succeeded?
What is “not working”?
Are you absolutely sure the existing data in your database are correct?
I mean, encodign-wise?
about the connection, i only have one header file, that makes the connection, i putted the query exactly after connecting to the database
That seems reasonable
about the data in the database, i think there is a problem, i’ll change all character set to the tables, just the way you told me
Changing the character set of the tables won’t help if your existing data are rubbish
If there was previously an inconsistency, crap data could have got in, and there is no way of fixing it
What I’m suggesting will only help your application work in the future
Migrating existing crud data could be quite tricky
migrating?
or u can pull in all ur data in a php script and fix the data
i just thought that
and reinsert it
ohh
but in the database everything it’s ok ( i think), so there is no point doing that i think
Well, it’s tricky if there was previously an encoding error, and different parts of your data have different encoding errors
A lot of people think the data in the db are right when they’re wrong, and vice versa
because they use a lame client to view it
well so, i could try that i think
You still haven’t really explained what the problem is?
ok
Can you enter non-latin1 data into the system
?
www.casasdeelsalvador.com/index.php
click any part in the map
Say @0240
and you shall see the text with some marks “?” those are the characters
not sure of that
i could try give me a sec
doesnt happen to me
what doesnt happen to you?
ahh sorry i get it .. ? in words
yeah
spanish ?
yes sir
You’re outputting these characters wrong somehow
My guess is that you’re spitting out latin1 when the page is utf8
not sure how could i be making that
this page look ok in windows-1252 encoding
have u tried playing with ur meta tags
you data is in that encoding
btw sorry about my english, i am just learning
html lang=”es”
i havent
meta http-equiv=”content-Type” content=”text/html; charset=iso-8859-1″
try them 2
see if it help
ok
No, do not do that
i have same troubles sometimes with the £ charachter
Your data out of your database is somehow being converted to latin1
it should not be
ok
niuq, please make dump of your table
If you did SET NAMES utf8;
niuq, I’ll show how to fix your data
you mean using mysqldump all my database?
I think the data in the db are probably right, but there is some unwatned conversion going on
like making a backup
niuq, yes
Just the table.
ok ok
We don’t want any of your private data
give me as sec plz
i dont mind ^^
it’s not that private
Hmm, I must visit El Salvador
looks nice
MarkR42, I got it – no unwanted conversion occurs
lol
MarkR42, but data in db is encoded in windows-1252 charset
How is that possible if the tables are utf8?
May I see please?
tables are said to be utf8, but data isn’t
MarkR42, set windows-1252 encoding in browser, and data will look ok
Hmm
but then the rest of the page is wrong as it’s in utf8
yes
The data have been inserted into the database by a lame application
so wee need to convert data
yes
convert columns to binary then back again?
i suggest to make a dump
then replace SET NAMES ‘utf8′ with appropriate encoding
then put that data back in database
mysql must make necessary conversion
I suggest that niuq fixes the application which inserted the lame data so it works correctly
ok just give me a sec, i’ll figured out how to make a dump for a single table, i’ve only have made that for the entire database
humm, maybe that step is needed to
niuq, where did you get that data?
that page it’s only for information, so i just get the text and copy & paste
some data with php scripts
something must have inserted the data
Well, the application you used to insert is broken and should be fixed
before anything more is added
mysql administrator
a gui for linux
Shun that gui, it’s obvoiusly broken
I don’t trust ANY gui
sorry
mysql query browser **
niuq, so, you made the dump?
Can someone perhaps tell me why this db replicate from the master twise. I have comepletly uninstalled and reinstalled mysql. (and deleted core config files and /var/lib/mysql/*) but after a fresh installl it still did the same. this is show slave status output that gives me the idea it does
the replication twise Replicate_Do_DB: tv,tv | Replicate_Ignore_Table: tv.job_queue,tv.log_search,tv.job_queue,tv.log_search
everything happen twise. is there a command to manually tell it to do it ones only
Do you mean twice?
i am now, give me as sec, i’ll figured out how to make a dump for a single table, i’ve only have made that for the entire database
MarkR42, my bad spelling mistake.
mysqldump databasename tablename
ok thx
MarkR42, any idea?
ok i made the dump
!pastebin
niuq, open it in text editor
ok
Ick, using text editors on mysqldump files
niuq, find line “SET NAMES ‘utf8′”
ok
there it is
niuq, replace ‘utf8′ with ‘cp1252′
i thought i have to go he opposite way, nvm i’ll do what you say
ok done
then apply this dump to database
ok
mysql -d database dump
afaik
how is it going?
humm
need to additional edit, is SET NAMES commented out?
niuq
ohhh sorry
i tried this way
mysql -u root casas proyecto table.sql
i set the bind address to 127.0.0.1, but the 3306 port still shows when I remote scan to the regular ip… any idea why?
niuq, was SET NAMES line commented out?
no…
ok
so, did restore work?
should i?
uhm
mysql -u root casas proyecto table.sql i am writing that, not sure what i am doing wrong
please take back a look at table.sql
ok
/*!40101 SET NAMES utf8 */; – does that string look like this?
yes
exactly
remove /*!40101 and */;
ok
so only SET NAME cp1252 left
SET NAMES
SET NAMES cp1252
ok
without the ;?
humm
SET NAMES cp1252;
ok
with ;, yes
ok, now save it and proceed with applying
done
mysql -u root casas table.sql
ok ok
done?
Unknown character set: ‘cp1252′
humm
try latin1 then
ok
SET NAMES latin1;
like that
yes
morning
ok
niuq, done?
the restore worked
now should i’ll try with browser?
humm
yes
or i have to do something else
try that
looks like my plan faild
ok let me try
yes it failed
shame on me
I’m going home, bye
now withing the table, the characters changed…
morning all
niuq, try reinserting data with mysql explorer, may help too
ok, I’m off
ok, thx for your help
im off too
i have a table with 2 colums, name and age, im trying to get a list of all the ages and the number of people that are that age, anyone point me in the right direction?
I have a question. Say I have a table with a column “names” and I want to call up 10 results by a search query, but I want it to skip over duplicate entries, still returning 10 results. Could someone help me with this?
thewikies look up SELECT DISTINCT
Quozt, I will google that, thank you.
Thanks twice, this looks like exactly what I need. Way to go!
np
COUNT(*) GROUP BY age
SELECT age, COUNT(*) FROM table GROUP BY age;
really wish the editor whould so returns :/
hi every time this query “http://pastebin.ca/660904” runs i get 4 results and i am only expecting 1
any reasons why this is happening?
sorry also noticed this error
select `access`.`childs`.`IDIR` AS `IDIR`,concat(`access`.`childs`.`GivenName`,_utf8′ ‘,`access`.`childs`.`Surname`) AS `ChildName` from `access`.`tblcr` `t` left join `access`.`tblir` `Childs` on(((`access`.`t`.`IDMR` = 7) and (`access`.`childs`.`IDMRParents` = 7))) where (`access`.`t`.`IDMR` =
7) order by `access`.`t`.`Order`
that I dont know, but why do you have ‘order by’ in there if you’re expecting only 1 row?
i am expecting 2
ahh crap type
2
*typo….
oh ok
Yeah sorry about that
I have saved a “RTF” type file into a mysql db. When I retrieve that now, to view it, it puts the web page code in & the RTF looks like this.. what did I do wrong?
{\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fnil\fcharset0 MS Shell Dlg;}{\f1\fswiss\fprq2\fcharset0 Microsoft Sans Serif;}} {\colortbl ;\red84\green84\blue84;\red0\green0\blue0;}
Billium, what is wrong with it?
it is showing my web page as part of the RTF, and its showing the RTF code
first off, I don’t want the web page code included.. secondly, I just want to view the text, not the codes behind it
Billium, what is the database question here? are you asking how to display RTF in web page?
yes gnari sorry
Try running the result through this PHP function (if it’s PHP?!): function encode_html($string) { return(str_replace(array(‘&’, ”, ”, ‘\”, ‘”‘), array(‘&’, ‘<’, ‘>’, ”’, ‘”‘), $string)); }
how come it stored the web page code along with the RTF?
It replaces &, , , ‘ and ” with the html equivalent
Wait, what?
Billium, might be a question of content_type http header
the db does what its told to
Are you saying the html of the webpage is stored inside the database too?
application/msword
how do i get the definition of a view in mysql 5?
yes Lemon
Then it’s a data entry problem
Billium, time for you to do some thinking about what you want to do
I want to upload the RTF into a db.. and have a clickable link to view/download that RTF file
Well have a look at what’s exactly in the database at hte moment when you’ve uploaded the file. Is there html in that?
So no ideas why i am geting four results when i am only intrest in two result (the other 2 results are duplicats of the first 2)
and is the page that’s fetching the data from the database trying to insert html around it?
the content field is set to mediumblob
Not the field type, but the actual content…
One method that could be used is to store the data physically on the hard disk. Then use the database to keep track of the location
$fp = fopen($tmpName, ‘r’);
$content = fread($fp, filesize($tmpName));
$content = addslashes($content);
Is that PHP about to fill the database field?
$tmpName = $_FILES['userfile']['tmp_name'];
yes, its PHP
i need to check a view in mysql5 … is there a way to get the definition-statement of an existing view?
If you’re just trying to get the contents of a file, use file_get_contents (or something similar sounding), it’s the fastest function for that stuff
documentation? dev.mysql.com
pastebin the code of the all the page that retrieves the rtf document and I’ll look at it
i just find about create/drop views … nothing to get the view-definition itself?
Sorry, I have no idea about views xirjs, but if it’s possible… it’ll be in the documentation
views are very possible
you know how to get the definition of an existing view?
but the performance isn’t as good as without them if you treat them as a table in a complex query
I find it’s good practice (especially when echo’ing file contents like this not to include it as you may accidentally include a new line at teh end of the file!
guys, is it possible to have a table which has one column which is the foreign key to TWO other tables? The MySQL CLI allows it to be created, but will it work? See http://pastebin.ca/660920
okok, can you copy and paste everything that this file echo’s? Remove content-* headers and you shouldn’t have a problem seeing it
Also, why are you sending a content-length header? PHP/Apache should be able to find that out no problem
I remarked out the head(“Content) stuff.. http://pastebin.ca/660923
Any ideas why my results are doubled
header
hi every time this query “http://pastebin.ca/660904” runs i get 4 results and i am only expecting 1
hi @all can somebody help me please with a regexp and replace statement i tried to replace some signs from my column …. THX ..
i am expecting 2 sorry
yes I do, give me a sec
ok
How is that file supposed to be?
instead of { ?
Billium, the correct Content Type is text/rtf
beats me
the content type is being set as application:msword
application/msword excuse me
http://rafb.net/p/OyMsxq61.html THX
can anyone offer me some advice for my question above?
If I save that file as asdf.rtf it opens perfectly fine in OpenOffice.org
What exactly is your browser trying to do with the file? It sounds like gnari is right here
and why msword? It’s not an msword specific document… afaik
hal, what column is that ?
what are the results?
just text
gnari, basically the two foreign keys in that table – FK_user_data_check_1 and FK_user_data_check_3
hal those are 2 different columns
is IDMR unique?
Your joined table has 2 rows in it?
gnari, I am sorry – let me paste the code again…
the table its join has more than 2 tables but it is join a table that should have only 2 results for that query
*rows
is IDMR unique?
http://rafb.net/p/OyMsxq61.html … THX
there are 2 rows that contain IDMR there the results i am expecting instead of geting 2 results i get 4
Hi people, could somebody please tell me how to do fast fulltext searches with mysql
Take a look at this: http://pastebin.com/m6656077e
if you take a look at the deviant art website you will see, that they have round about 400 Million media assets in there databases
if you have two rows in each table, when you join them, there will be 4 rows
and if you type a search string, the result is apearing in about two to four seconds
gnari, this is the correct post. Sorry about before http://pastebin.ca/660935
there ment to be joined to the end of the row
squitta, mysql fulltest is not the fastes search out there, use Lucene
I have the geonames reference table lying around here, it is containing round about 6.4 Million entries and it takes 14 seconds to find fifteen entries over a filltext
gnari, FK_user_data_checks_1, FK_user_data_checks_4, FK_user_data_checks_5, FK_user_data_checks_6
archivist, what is lucene?
an open source search engine
gnari, well really data_field_id
just like how _Lemon_ has his set out
except for the last one
archivist, okay I found it, are you using it, is there need for having a java application running
archivist, i am working with php, but ah let me just read it its okay )
Lemon, when I open the file instead of save (which I will try next) it not only opens that file, but it also includes the web page code that I am on at the moment, including graphics
squitta, I dont use it I wrote my own search to suit me
hal, i think mysql only checks the first fk constraint.
okay i see, is it fast too?
hal, mysql really should either check both, or disallow it, of course
archivist, how many entries do you search?
how do these fulltext search engines work? Do they use the database files? Does the data have to be stored separetely?
gnari, yes, i agree, but from a practical point of view, do you think it will work?
Lucene is separate
Mine is db based
Wait just fixed it
Thanks
What was the problem kieran?
flung, each word of a page is indexed, and then the frequency of each word is counted
archivist, okay, same with me. I am having all files stored in the filesystem while i am writing search keys in a fulltext index field
archivist, but i realized, that this can get very slow
it does yes
hal, for fun i tested this in postgresql. postgresql allows it, and checks both constraints
flung, but you have to have a table of excluded incidental words too, like a and of
Are you doing this in IE? It could be *another* IE bug of some sort? because if I remember…. it does like to open files like msword in the browser, is the page in a frame? Was there any other PHP files included before the one you showed me? At the moment the php looks fine
archivist, but thank you very much, I will start to use lucene )
I store counts and build a search sql against my own index
thanks, that makes sense
gnari, that was a good idea
I dont exclude words/letters
archivist, what does that mean, you are result index in your system against your document index
flung, http://en.wikipedia.org/wiki/Stop_words
hal, it looks like mysql does it correctly too
archivist, no i have my own stop word lists, I am working with an unlimted number of languages
flung, http://www.tartarus.org/~martin/PorterStemmer/
because “r.a. smith” needs the the letters etc
archivist, thats why the stopword of mysql are not really performing the work for me as needed
www.archivist.info/search/index.php/Sql
part of mine
gnari, logically, it seems to me that it should not work, for a FK in one table to rely on two other tables. however, it would save me a lot of work if it can work
archivist, but words like this might need less storage if only words like are stored in the index and all documents containng this and but are not found.
would it be possible to keep the words in some sort of count + order, like Liv Zempel?
flung, http://en.wikipedia.org/wiki/Stem_%28linguistics%29
hal, it looks like it does. logically it should, although i have not thought out implications of any ON DELETE CASCADE
do you just know these links?
flung, well, it is just the theory that you would need to know to implement a search engine for your database.
when I try to save the file, and open it, it gives me an error about links missing
however, when I copy the echo, and save it.. it works fine
try changing the file Content-Type header to text/rtf
gnari, yes, I agree – when it wants to perform an UPDATE, checks for all occurences of the data_field_id in the referenced tables, will it really check all of them? I suppose unless someone has tried to do it before, the only way of knowing is to test it
$fileType = $_FILES['userfile']['type'];
archivist, so how many database entries did you make available thru your search and how fast is a query?
Yes, I know you’re saving what the browser tells the script the file is, but for testing purposes… *try it*
squitta, quantities are not high yet, fast
archivist, I invented a kind of neural networking allgorythm which decides after a search has been performed, which documents are the most relevant for the user instead of only performing a fulltext search
archivist, really, why dont you just take to geonames.org and download yourself the geoNames Database from there
its keeping 6.4 Million entries. Its mad well for testing.
Im about to load some real data in an archive, that will text it
archivist, you are working with wiki?
no
and yes
what do you mean?
