Is there some way to tell mysql to not cache certain tables I have something like 10000 data tables that are
Create a table that assigns magasines to month
magazines*
are you sure about the transaction thing? i’m having a hard time finding syntax on it, after a few quick googles and hopping around the docs a bit
Yes.
You mean having a table for month and an attribute of Magazine?
I was wondering if it would be ok to have th volume, month, and year as attributes for the topic table?
I mean a table named something like months and put the columns magazine_id and month
Probably the best way to do it to assign multiple months
There would be an awfull lot of tables for months.
One table…
One table, two columns.
magazine_id and month
Not sure if I follow you. What happens when you have different months?
You put as many rows as you have months for a magazine
Here’s a silly example. I took a real use for transactions and chipped it up to show you could start/end one within a procedure. http://rafb.net/p/q3iXNK88.html
s/chipped/chopped/
Do you think it would be bad idea to put volume, month, and year in the topics table?
If you don’t have a procs database, you can just run that directly: mysql … file.sql
Yes if you have multiple months…
Be careful. It drops the procs database first.
Each volume has its own month and year right?
procs in nothing special for me. It might be for you if happen to use that name.
hmm, do you have to set a delimiter?
Some magazines have volumes, but most of them don’t. Most of the magazines come out monthly.
no worries, i’m working on a local php mysql web hosting test server, nothing production
Just name a table “assign”, and put in columns: magazine_id, month, year, volume
The DELIMITOR is a kludge that exists until MySQL fixes their procedure scanner/parser. It shouldn’t be needed.
Then just create as many rows in that table as you need for each magazine_id
i think perhaps the fact that i wasn’t using a delimiter caused the problem?
hmm, no wonder my procedure didn’t work
but you think they’ll fix it?
The parser should be able to handle hierarchical / block structure procedures without getting confused about the start end of a sub-statement within a procedure.
(is it a known priority of some sort?)
Not that I’m aware. I think it’s pretty bad, personally.
My concern was when you have two articles in the same month (same edition) of the magazine or when you have two different months of the same magazine. Potentially this would create two different magazine ID for the same magazine within the same table. This is what I was trying to
avoid.
Statement blocks should be able to use the same delimitor internally that are used by the outer block or procedure statement itself.
No it wouldn’t….
yeah, it does seem fairly unnecessary; i mean, it should be able to form a ’stack’ of the calls, you’d think? sadly, even MSSQL does better than that…
You’re creating a table named magazines and putting the column magazine_id in there and then assigning it an auto_increment
Then you’re telling it which months it was in on the assign table
Correct. The parser should be able to handle that. It’s a very common construct.
is it common to use ‘@’ variables within stored procedures in mysql, or is it even valid?
(just curious, because otherwise i wouldn’t see the point in @ user variables)
I’m not sure it’s valid. To be honest, I only really care about stored procedures. The command line client language seems a bit limited.
I suspect it’s just used in the command line client itself.
So what you are recommending is to break up the Magazine Table into two different tables called Magazine and Assign?
fair enough. so i guess it’s definitely necessary to be careful when naming stored procedure variables so not to step on any column accidentally.
Can it be true that my site is about 20 seconds on each pageload just becouse 150 is online at the same time, and theres min. 8 queries pr. page… the database is on its own server..
*nod*
If you want to be able to assign multiple volumes or months or years to the same magazine, then yes
how do I do a select and insert in a single statement ?
Is there some way to tell mysql to not cache certain tables. I have something like 10,000 data tables that are used almost randomly and a few management talbes.. I think the data tables keep knocking the management tables out of memory which are always used. I would like to force them to stay
in memory. Is the only way to do this to tell mysql to make them as HEAP tables and load them from disk at startup?
INSERT …. SELECT …;
well, you’ve been a tremendous help yet again. i truly appreciate the insight and the examples you’ve put together
OK. Just a point of clarification. What attributes specifically are in the Assign Table?
now, to see if i can make this stored procedure work as intended
http://dev.mysql.com/doc/refman/5.0/en/insert.html
cool
You’re welcome.
looks easy
thx
month, volume, year
Ok. Thanks for the insight. I was trying to figure out how to handle this problem. Just out of curiousity, what could happen if the volume, month and year were put in the topics table?
Uh. Topics?
The way I’m having you do it, you can assign multiple volumes/years/months to the same issue
If you have it in the same table with something else, chances are you can’t
You’d be breaking some rule of putting an array inside of a row .
Yes. There is a topics table. Topics table has a TopicID, VendorID, and Product. VendorID goes to the Vendor table and may or may not be in the relationship.
0 as the default value on a DATETIME column, i jsut get #1265 – Data truncated for column ‘edited’ at row
why could this be?
that “rule” is the first normal form that is violate
d
usually, codd country policy will come and get people violating first normal form
or huh wait, row 24….
aha NULL values
I really suck at visualizing other peoples databases, the way I’m having you do it should work from what you’ve said, though
I assign all of my news articles categories the same way, since a user can select up to a certain number
Just make sure you liberally use indexes
You have done a great job at describing some options. I was baffled by this problem and think your suggestion should work for me. Thanks for your help.
Not a problem. I was once baffled by the same problem
Xgc, I’m figuring out the LIMIT problem, but I think your solution was the one for me. I’m 100% positive I can get PHP to break it up into multiple tables based on the position
My current database design has a topic table linking to a weak entity that includes TopicID and MagazineID. The weak entity is between the Topic Table and the Magazine Table. Your suggestion of adding one more table should take care of the problem.
Thanks a bunch, Xgc
Perhaps it will help you a lot if you learn that what in practice is known as a Table in theory is called Relation.
Table like (id, username, password) for example describes the relationship between username it’s id and it’s password
so what do you call it when two tables got a relationship betwen them?
When you have some new relationship to describe adding one more table is exactly the way of describing that
I call them tables
henke37 you call it a relationship. 
and don’t forget to mention to USE INDEXES whenever you have a relationship. lol
I think I will stick to my current terminology
hey, somone helped me with this a few days ago, but had a nightmare and lost stuff and having a bit of a problem redoing it
Teh point is to realize that a table is not just a buch of data. It’s much more than that
in mysql, trying to select every nth row
Can the table called ASSIGN be a weak entity between the Topic Table and the Magazine Table in my case?
it was using an inner join
I have no idea what do you mean by “weak entity” to be honest
uI have no idea what do you mean by “weak entity” to be honest/u
Weak entities are used to break up a many-to-many relationship.
Part of the ER Diagram jargon.
What’s wrong with many-to-many relationship so you want to break it?
I have a quick question for you. Can ASSIGN be a weak entity between the Topic Table and the Magazine Table in my case?
Like there are many topics to many magazines?
yes
I was wondering if I could add volume, month, and year to the weak entity or whether or not I should create a separate table?
I’d use another table, to be quite honest. Unless you feel like pulling apart all of the years, months, and volumes and displaying them only once when you have duplicate topics per magazine
ok. That answers that. Is it ok to leave the weak entity that I have and just add the table you suggested?
Should be fine
ok. Thanks for your help. IT was quite insightfull.
Hehe, thanks. I ask enough of this channel that I figure I should contribute once in a while
http://pastebin.com/m6b65c666 is the query, i think its close
but just trying to select every nth row
You just need to fix your join criteria.
ON t1.id=t2.id … or something similar.
ah, where t1.id = t2.id, oops
http://pastebin.ca/656065 Could someone help me pls
thanks, that looks good
You’re welcome.
kind*
I can run the syntax with no error. What version of MySQL, and are you sure that’s the code giving the error?
Just a quick question for you. Would the ASSIGN table be a weak entity in this case?
hey friends
i am using mysql Ver 14.12 Distrib 5.0.44, for pc-linux-gnu (i686) using readline 5.2
i’ve got mysql running on centos, my hosts file is setup correctly. when i hit my page and watch tcpdump apache tries to connect to 192.168.0.57.mysql – after that times out the page loads fine. My database connection is setup as localhost, and localhost is 127.0.0.1 – php host isn’t setup with any default hosts, can anyne explain this behaviour?
I think the error then is with some other statement.
You out there?
Same here. Copy/paste works like a charm
then any suggestions?
It could be some non-printing character somewhere
spooky spooky i’d be thinking
I’d suggest one more cold Ledenika, but my workday is over
are you from BG?
Yep.
im scared guys
of what, rick111 ?
when you use localhost in mysql hosting it uses the socket file not tcp
According to the error message there’s something between U and L NULL at `ID` int(30) unsigned NOT NULL,
Which is good.
People who never get scared die young
ahhh
the socket file eh
which is why you see nothing in tcpdump
yes I do
i am not only watching ip/tcp
my eyes are peeled
“localhost is 127.0.0.1″ is wrong assumption in MySQL world
i have rewriten the whole line myself through nano
thanks, i feel inspired to go back in for another shot
so i dont think there is something inbetween
MySQK interprets the localhost as “connect via unix socket mysql.sock” while 127.0.0.1 means TCP/IP connection to the specified port
thanks salle you the man
Try copy/paste from your pastebin entry http://pastebin.ca/656065 It works
any mysql-proxy fokls around?
Me? Man? I was under impression I’m stupid bot connected to some IRC server
hi, when i restore mysql from a mysqldump, it takes forever because it creates a local bin-log file. is there a way to disable it in my.cnf?
hehe
rvhi, i think if you use -e in your mysqldump options the import will go quicker
since it does multiple row inserts in 1 query
set sql_log_bin = 0; in the same session you import from
i’m trying to ‘vi /var/lib/mysql/mysql.sock’ but it’s lol’ing at me, even tho I have root, take it I can’t edit this baby?
Xgc ( or anyone else for that matter) a quick question
the sock file isn’t editable
bummer
how to I resolve ones issue?
http://pastebin.com/m7c9524c6 the query selects every 5th row, however the start row seems unpredictable
it’s not really a file
its starting on the 3rd row
That is the nature of selecting non-aggregate fields when using a GROUP BY. Try perhaps SELECT MIN(t2.id) FROM…
You have some missing ids (probably deleted).
hi.. i wondering this case, i have a single row.. and on every entry i want to calc the timedifference between the entry before.. can i do that?
well.. just calc the time difference on 2 columns in 2 different rows
and as snoyes mentioned, you should select t1.id, not t2.id.
Here’s one way: http://www.artfulsoftware.com/infotree/queries.php#86
i ran “mysql -uroot -pzzz mysql.fulldump.sql”, how do i set the sql_log_bin = 0 ?
cheers, taking a look
snoyes, thanks… will take a look at the example
can anyone suggest a way I can get the before-MAX row of a grouped result? ie. instead of the MAX(), i want the row just before the MAX()
ORDER BY + LIMIT
the missing id’s seem fine, I deleted them on purpose
Another way is to assign a user variable, something like SELECT time – @oldTime, @oldTime := time FROM table. Be careful, since there’s no guarantee that the assignment will be evaluated after the comparison. See xaprb’s blog for information on making sure that’s evaulated properly.
that works if I’m focusing on one value of the primary key field.. i actually want what you said but for each group of rows
it selected every Nth row, its just the start row that doesnt work for me
Pick the MAX() of the group with values less than MAX(field).
snoyes, then the best host way will be to have a start and a stop time in every single row.. just as in the example?
What starting row do you expect?
Perhaps. Kinda depends on the data.
ah.. ok.. that could work
I wouldnt mind the first row, or the Nth
It’s certainly simpler if you do, but it’s not alway possible.
snoyes, yeah.. will for a time tracking sheet.. so its like.. how much time you spend on every project
I mean, with your posted data, what did you expect the first row to be?
is this the correct channel for mysql help?
(as opposed to development discussion)
thanks for help
http://pastebin.com/m735a2f41 another example
Seems to me 12 is correct.
yep
ok
riddle me this:
i would expect 8 or 11 in this example
(every 4th row)
i’m working with a table full of broadway shows
You have the same mistake.
and the results need to be sorted alphabetically by the name of the show
t1.id is not an aggregate.
it seems to be ordering from the top in fact
t2.id is.
but the client wants “The “, “A “, and “An ” at the beginning of the titles to be ignored
so i found this little example:
select `show_name` from `shows` order by TRIM(LEADING “the ” FROM LOWER(show_name))
Sorry. My mistake.
selecting from t1 / t2 does the same
Oh. You’re now ordering by a bad field.
i’m curious if there’s a way to trim several different strings instead of just “the “
ORDER BY t1.id
Now this may not be what you really want., but it’ll fix the result relative to t1.id.
its somewhat the same, but i would prefer them ordered by date as opposed to id
If you still have problems, post the create table statements and insert statements for your data.
in all honesty im not understanding this join fully
You can’t order by t2.date In MySQL, t1.date will be fine, but is not standard.
There’s not a convenient one. You can nest several calls to TRIM.
to explain it easily, I have a table with id,date, I want to select every nth row order by date
I don’t need the description. I need the data if you still have problems.
Your intent is obvious. I just want the same data you’re playing with.
ah cool, 2 seconds
You can install the regexp UDF from http://udf-regexp.php-baustelle.de/trac/ to maybe get the results you’re after.
guys im working on resolving the socket and have a work around if all else fails thanks. does anyone know where the socket gets it’s settings from tho? it’s weird
what setting for what?
resolving what?
Anyone know a good host that can handle a digg/slashdot effect?
Fr your logic: ON t2.id = t1.id
and make sure you select t1.id if you GROUP BY t1.id
ebergen I want to know why my server host is looking at mysql.sock and coming back with an internal IP of 192.168.0.54 – when that address exists nowhere
ahh, that looks right
it’s putting the willies up me
thanks, not much of an sql host person, would have taken me ages to figure that one out
“Coming back?”
via PHP mysql is setup to connect to ‘localhost’
Delixe, i ran a service on a celeron that survived digg
try :/patch/to/mysql.sock
it depends on how your site is setup.. more so then the host
when i hit my server and monitor the activity, my server tries to connect to 192.168.0.54.mysql
Hey, does this syntax exist? “SELECT * FROM foo WHERE id IS IN [2, 33, 89]“
do you have some kind of default setup in php.ini?
NewMonarch try () instead of []
no, php.ini is blank for host setup
that’s why it’s using the socket and not TCP
you mean get it to create a new .oskc?
You were making a list from the max to the min. So if you checked the largest row in your result was the 5th (or 4th for %4) from the top.
heh I figured it ordering from the top, but didnt understand why
Thanks. Also needed to lose the “IS”.
Hey Xgc. For that query you built for me earlier, I can just do something like LIMIT=v1.modules.limit for the second bit, can’t I?
im gonna try delete the sock
then we’ll see who’s laughin
LIMIT only supports constants.
Xgc, agh.
If it were that easy, I would have included it.
lol
Your problem could use windowing and rank support.
why don’t you strace php and see what it’s actually resolving/connecting
instead of randomly deleting things
Until that’s available, it requires a bit of sql magic to handle.
Don’t mean to be a pest. I have one more question for you. Would your table called ASSIGN be a weak entity in this case?
I don’t think so.
It’s a one to many
ok. Thanks. This has been a big help.
Xgc, I’m thinking I may just go without limiting support as it is
Xgc, I’ll just limit it to 10
How do I clear a table and reset autoid counters?
empty
tracing is harder
it deletes it’s self when the service stops :/
what am I doing wrong here? select customers.first_name, count(invoices.*) from customers left join invoices on invoices.customer_id = customers.id group by customers.id;
depends on the error msg
let me ask my crystal ball
/o\
here let me put it a different way
is it possible to get a count of invoices per customer without grouping on customer?
in this case, I am only selecting where customer_id = xx
so a count of all rows returned would suffice
zamadara, i think your problem is the invoices.* in the count(). try count(invoiced.id)
Is it possible to reset an AutoID column? Or do I have to drop and recreate?
truncate table tblname;
hey guys, I upgraded mythtv versions and I’m having some database problems. I remember there was some generic little thing you could run on a mysql database that would clean up corruptions but I forgot what it was called….ideas?
zamadara, that was of course supposed to be count(invoices.id)
yeah, is there a way to just get the entire row count from inside the query?
select *, row_count() from …
You can also ALTER TABLE tblname auto_increment=1; to just reset it without touching the data. But then you have obvious issues.
I discovered that count() is not what I want
Thank you, a lot
zamadara, do you want the rowcount of the result of your query in each row
zamadara, if so, do it in the application
or: select t1.*, v1.cnt FROM tbl t1 JOIN (SELECT COUNT(*) cnt, customer FROM tbl GROUP BY customer) v1 ON t1.customer=v1.customer;
You out there?
When using innodb, and transactions (BEGIN … SOMETHING … COMMIT), is there any problem with also sending some queries that are NOT as part of a transaction? Or do all queries need to be followed by a commit?
Are all column names allowed if I always surround them with `colname` ?
or do I still need to worry about reserved words
!man identifiers
see http://dev.mysql.com/doc/refman/5.0/en/identifiers.html
as long as you always use back-ticks you are fine
thank you both.
Hello Peeps
If I have a user table, and another table that references the user id, what should I name the columns? user.id tableTwo.userID ? user.userid tableTwo.userid ? user.id tableTwo.user ?
aaroncampbell, queries that are not after a BEGIN, are in their own transaction
is there a method that is frowned upon or smiled upon?
aaroncampbell, and do not need COMMIT
aaroncampbell, but they cannot be rolled back, either
Sembiance if they are both called user.id that is fine
well you would not have 2 user.id sorry
so table1.userid, table2.userid
so that’s a popular/accepted method them?
tableone.tableoneid tabletwo.tableoneid
Are you doing a left join ? inner join ?
or just a simple query
I’m not doing any queries at the moment, just curious
designing some tables, nothing complicated
Something like SELECT * FROM table1 t1, table2 t2 WHERE t1.userid = t2.userid
thanks
you don’t do it like that.
“GRANT ALL PRIVILEGES ON `username\_%`.* TO ‘username’@'%’ IDENTIFIED BY ‘username’ WITH GRANT OPTION;”. Why cant they change their password through mysqladmin? I get “Access denied”.
select * from table1 t1 inner join table2 t2 ON t1.userid = t2.userid
I was close 
Hrm.. What are my options if I wanted to see if N was 1 year ago?
which functions would I be most interested in?
thanks guys good night
dan__t, http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html
Thanks.
when i encrypt an md5 password in a mysql table can i use php’s md5 function or do i need to use the mysql one?
php to encrypt before puttin into table
they will work the same
Dont to preference I suppose
Why would a INNER JOIN not give results
If I do a LEFTJOIN it displays all left data .visa versa with RIGHT JOIN displays all right data
But they wont show togther
no intersecting data?
From what I can see there is
Im going mad
watch out for leading and trailing blank spaces in strings.
NI have no errors
here is the query
SELECT tables_football.*, lad_football_league_winner.* FROM tables_football LEFT JOIN lad_football_league_winner ON tables_football.team = lad_football_league_winner.selection
that gives all the left data only
If I do a INNER it displays no results
then there are no matching ‘…tables_football.team = lad_football_league_winner.selection…’ data, even if it looks like there is.
Can i do a LIKE test then
just as a matter of interest
replace ON with WHERE?
still no results
trim(tables_football.team) = trim(lad_football_league_winner.selection) ?
still no results
select distinct selection from lad_football_league_winner; returns results?
Yep
Got the results
select distinct team from tables_football; returns results?
Yep
all good
matching records between the two?
there will be some yes
not all of them
no, but at least one?
yea plenty enough
Its a team name
try your original left join with WHERE instead of ON
i have
no results
no errors
no error means the query ran, but there were no matching results.
take one of the teams from the ’select distinct team from tables_football’ query and do ’select * from lad_football_league_winner where selection like ‘%thatTeam%’;
1 year old
SELECT column1, column2, … FROM some_table WHERE date_column ( NOW() – INTERVAL 1 year );
1 year ago.
Got a result yes
using the wildcard ‘%’ implies to me that you have leading or trailing blankspaces or newline characters in the field.
the field `selection` in lad_football_league_winner.
Hmmm
And I still owe you a beer next time I’m in Denver, [RainMkr].
Thank you.
You’re not in Denver any more?
Nosir.
They look fine
I cant understan
d
Oh, where do you live now?
Move back to PHX?
Moved back to Phoenix.
Yeap.
Couldn’t pay me enough to live there.
haha.
Yea… that’s why I have an interview on Weds.
in Denver?
Can I PM to show you
Naw, down here. Been hitting the job market bigtime up there, though.
Can’t find many leads.
no pm’s, thanks.
dinner. later.
ok
can I am you?
Will the names in the files are the same
that was bad I’m sorry
I’ll go do my laundry now
Hmm if the table character set is different could that be the problem
Got any leads up there, [RainMkr]?
Nope. I’m not active in the local business community up here. One of the down sides of working from home. No social networking
hmm.. are there any way that i can present minutes in only 0 15 30 or 45? looking for anyting called round or ceil.. but it does not seems riht
ROUND() and CEILING() both exist
anyone happen to know if there is a way to save a table that was “lost” in an upgrade?, we upgraded from 4.x to 5.x and everything worked, except two databases had all their tables “disapear”, they are visable when I do a “show tables;” but they tell me they are not there in phpmyadmin when I
try to browse them or look at them
is it safe to kill an innodb checktable ?
flupps|US, yeah.. but im not sure if they fit.. maybe anyone had a suggestion..
am I stupid or is there a reason a table would be in “show tables;” but “select * from tablename;” tells me table ‘tablename’ doesn’t exist
spec chars in name?
no
the name is barcode
we upgraded from 4.x mysql to 5.x and all the databases converted fine and everything works but one program
and its database all tables inside it look like they are there, but say they arn’t when I try to select or insert them
REPAIR TABLE barcode tells me the same thing, table ‘barcode’ doesn’t exist
does anyone know if it is it safe to kill an innodb checktable ?
hi
is it some “magical” command like myisamchk for innodb tables?
my mysqld is not starting anymore due to coccupted innodb data
that’s too bad.
you’ll have to use innodb_force_recovery options and dump your tables
It’s fairly safe, so far I haven’t heard of anyone dying or hurt themselves too badly
hey
problem is, it does not work at all
meaning what?
how do I delete rows from a table where there are duplicate columns?
as in – say a column has an ip_address and I want to get rid of the more recent rows that have the same ip_address
to which value did you set innodb_force_recovery?
from 0 to 6
without any change in behaviour
what happened?
oh, seriously corrupted filesystem, due to raid 5 failure
okay I think I figured out what happened, when I upgraded from 4 to 5 all innodb tables are broke … so somehow I have to recover the innodb file
sounds bad, all FS errors fixed?
the data may well be completely corrupted
seems badly corrupted if all settings don’t work
that’s why I want to check them offline
what’s in the lock?
like myisamchk does
lock?
damn it… log!
(fs is now clean)
log?
yeah your mysql error log.
there should be some innodb crash info
there is no external tool for innodb (all recovery happens on mysqld start)
you might want to grab your backups.
k im looking but the log files tells me I can’t read them cause they are owned by a process
I am running mysql on windows
notepad doesn’t care about that
so if a table is corrupted, all the innodb tables are inaccesibles, and it can stop mysqld starting?
Why do I even know that
good call I was using wordpad
oh god I feel stupid
first line of my log
oh sorry.
The first specified data file .\ibdata1 did not exist:
yeah or it even crashes because of inexpected data
um. no its because I forgot to copy my datafile back in…
Then it’s not ‘broken’, it’s ‘gone’
hmm, ok, pretty sad
hrm
but I can’t copy over it
mysql is shutdown but something is still using the file
turn mysql off first
reboot
well if even the highest level of forcing recovery doesn’t work…
You do have backups I hope
I hope for the client he have some backups, yes
and don’t use raid 5 for databases.
Ah lucky you heh
whats wrong with raid5 for a database?
I use raid 5 for vserver storage
slow writes.
the client do whatever he want on it
raid5 is _really_ slow on writing
especially on a journaled filesystem
(you should move the jounal on a separate device)
nice, a simple taksmanager kill of mysql and replace the innodb file and restart solved all of my issues…
mysql on a vserver… well.
shouldn’t be so important
it not really is
yeah
but dedicated servers are so cheap these days…
Hello! I have a problem you guys may find interesting I’m trying to select a bunch of threads from my database that have the most comments. So there’s two tables.. threads and comments. I’m thinking select from threads LEFT OUTER JOIN comments ON comments.thread_id = threads.id.. That will
give me a bunch of threads with comments
one entry per comment
then the count of all the unique thread ID’s is equal to the thing I want to order by
then I can select distinct
but I’m not sure how to count unique thread ids in the entries.
why so complicated?
select count(*), thread_id from comments group by thread_id
interesting
I think “group by” was the thing I’m missing
“count(*) … group by” flattens records into unique data entries, right?
indeed
you get the count for every thread_id
righto. Cool. So if there are two comments with thread_id of 10, then I get a result of (thread_id, count(*)) (10,2)
indeed
and I guess I can do ORDER count(*)
ORDER BY that is
yes
awesome. Thanks a lot
might get slow with a lot of data
heh. Yeah, I’ll throw a limit 0,20 in there
then it’ll take longer
Oh :/
makes no difference
limit is applied after ordering in most cases
Right, it has to count the entire database anyway.
hmm.. well, I’ll cache the page fragment so it doesn’t do that so often then!
you will only save time in data transfer
(
that’s what you get for going mac
humbug
finally someone tested the sudden motion sensor
yep but not on purpose
it survived without a problem
the sensor or the macbook pro?
haha both
there should be debian packages for sphinx.
just compile it. stop being a baby.
oh no compiling sucks.
time consuming
ubuntu doesn’t have it either
Hey, could anyone point me to a good tutorial or howto or just something about optimising MySQL queries?
nils_ gentoo doesn’t have a package either
there is no fun in gentoo packages
it’s 345K, how long could the build take?
well it’s more the repeating on many many servers that bothers me
scriptualize it
damnit, I want binaries!
well whatever.
dammit ?
you will have binaries after you build them
maybe I should build my own deb.
oh. right.
with blackjack and hookers
are they all the same server?
different arch / os version / distribution
haha
the guy who set the stuff up did a good job in choosing something different for every box he set up
!wench beer nils_
Here nils_ have a cool beer
I considered buying a fish to slap him, but there is no fish that large.
!man fish
Sorry – I have no idea what function you’re talking about! but try http://dev.mysql.com/fish
!woman fish
hmm that’s kind of discriminating
yea he said he was going to add some !woman command but i suppose he never did
could lead to some woman site then
(yes, I’m talking pr0n here)
pr0n?
you know anything else specifically involving women?
haha
how can i run a mysql query directly from the command line? something like mysql [dbname] [query]? I know little about bash…
mysql -e “query;”
oh that’s easy, thanks
unless query is a file… in that case what you said is fine
ok, i get it
or even echo “query;” | mysql dbnamme
Hmm.. So I’m trying to actually get all the thread data instead of just the ids
not sure if that’s possible..
it sure is!
cool It’d be a left outer join, right?
more thought of an inner join…
Oh
left join
got it
select count(comment.id), thread.* from comment inner join thread on thread.thread_id = comment.thread_id group by comment.thread_id;
something like that (supposedly wrong as I’m not good with sql)
goodbye!
Hmm.. Close.. It doesn’t appear to be doing the grouping properly
hm
!man purge
see http://dev.mysql.com/doc/refman/5.0/en/purge-master-logs.html
hmm.
Sorry, my fault, I misread what you had
I did group by comment.id instead of comment.thread_id;
Thank you, this works really great. That’s so helpful, I’ve learned a lot
“SELECT COUNT(*) FROM Table” should take on a table with 16,273,184 rows ?
Cause it’s taking me a really long time
And I’m also curious, is this the fastest way to get the number of rows this table has?
I just did a count(*) on a table with 28331389 rows… “1 row in set (0.00 sec)”
sure there isn’t a lock or something that is preventing the query from executing?
depends on the engine.
How is that possible
It took me like 5 minutes
I’m using SQLite
Generally SQLite is just as fast if not faster than MySQL
it generally depends on visibility of the rows
I’m not preventing anything.
Just using a standard sqlite 3 database
well this channel is not about sqlite.
mysql does very quick count(*) selects, as long as there is no WHERE or ORDER BY. Your problem is probably a lack of indexes.
Hrm
you mean myisam
though I just saw that sqlite comment, and I have no expertise with that.
Ok
I don’t understnad it.
I’m using myisam so I think a count(*) probably just looks in the table metadata
it does.
I import those rows in 11 minuts from a 3.5gb text file
I think that’s fairly good performance
So why should this be so slow?
maybe sqlite doesn’t store row visibility information in the index?
I’ll look it up
11 minutes to import, it probably takes less than 11 minutes to count
cat file | wc -l
err don’t even need the |
wc -l filename
and yes I realize that this probably doesn’t help you whatsoever
It does take less than 11 minutes to import
5 minutes to count.
But that’s still too long if I need to run lots of queries
any index? or primary key? these can be used to count, depends to implementation though
can you prepend EXPLAIN to your query and get an idea of what indexes are used?
my advise is you should use mysql
mysql is too bulky for my purposes
this is for a windows application i’m writing
well maybe it’s bulky, but you can always ask for help in #mysql
need a local db for every person who installs
bulky? you can even have it embedded
I know, but not small like sqlite.
indeed, and it’s cheap
Sqlite is 300kb dll
yeah
but no fast count() implementation in this 300kb
Might be.
I just haven’t found it yet
might be
9
I’d just want to give you the subtle hint that most here can only speculate about sqlite’s inner workings and optimization
that would be.. give me comments from today. But I’m really trying to say “give me comments less than 24 hours old.”
Ok, thx anyways
where date_posted = NOW() – INTERVAL 24 HOUR
flawless. Thank you.
generally I agree with you that you won’t need a full blown client/server rdbms for your purpose.
I’m going to figure out how to optimize it.
I’m sure it’s possible
“On the other hand, some operations are as much as 50 or more times faster with synchronous OFF.”
don’t know, maybe sqlite isn’t caching enough and so stresses the disk because of the windows file cache (is there even any?)
udon’t know, maybe sqlite isn’t caching enough and so stresses the disk because of the windows file cache (is there even any?)/u
is there a sql command i can run to see if a specific stored_proc exists on a database ?
Yeah, i’m going to increase cache_size, etc
you need memory to cache
other than CALL?
yeah…
300kb becomes 300mb after caching
ROUTINES table in information_schema?
defeat the purpose of the smallfootprint
ugh. I would just like to say that windows sucks. That will be all. Thank you and good night
word.
apparently it doesn’t feel like connecting to my network drives anymore so I can’t get a file off of my vm so I can work with it
Ok, i dont think synchronous being off made any diffrence really
speaking of evil corporations… how about that SCO stock price? Looks like it ended -71.8% today
hmm not long and I will be the owner of sco, I saved like 100$ for that.
of course I guess going from 1.50 to 50 doesn’t sound as bad as “-71.8%”
1.50 is a lot
well at least for SCO
ToeBee how long does SELECT * take?
On that big table
hmm probably a while…
yeah its definitely taking “a while”
2 minutes 10 sec
the issue is effective indexing, not table size.
not when you’re doing a select * from table
Mine seems to be really fast
But might just be a difference in how im doing it
a select * is faster than a select count(*) ?
yea
seems like it
is your engine MyISAM or innodb?
heh neither. he is using sqlite
My apache2/php engine can’t connect to mysql through the socket file that was set up. Is there a command line method for checking the socket?
my bad, didn’t scroll back far enough. Sorry.
Ah nevermind, it’s slow.
dont suppose there is a way to get more details on what is causing the folllowing error ? Can’t create table ‘./sphere/FilterQueryMatch.frm’ (errno: 150) (errno: 1005)
Let’s see how long tho
!perror 1005
some foreign key constraint failing?
!perror 150
Foreign key constraint is incorrectly formed
Hopefully not much more than the count
its on a create table…
is the socket actually there? I’m guessing the mysql server didn’t start up properly and the socket doesn’t exist
the file is there with 777 permissions
I’m looking for a way to see if they’re acutally connected
ah. well you could try the mysql commandline client
I can connect with command line (mysql -u …) but the apache2/php connection barfs withCan’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’ (13)
erm so far 3 minutes on the select *
permission denied
!perror 13
Permission denied
yeehaw, getting better
evil
evil is clearly defined at http://www.parseerror.com/sql/select*isevil.html
Took 4 minutes on a select * for the 16 million
Wierd that count(*) is the same as select * isn’t it?
BING! got it. the /var/run/mysqld dir had no permissions for reading by any but root
desc (varchar), value (int) …. ‘desc’ can be repeated. I want to add up all of the ‘value’s for each ‘desc’ and order that. is that quick/possible?
yes and no… apparently it is just doing a select * and then counting the result set to give you a count(*)
Ie, if I have (‘foo’, 2),(‘bar’,3),(‘bar’,4),(‘foo’,10) … I want a return of: (‘foo’, 12),(‘bar’, 7)
that’s what I figured.
Maybe there’s some metadata on the number of rows?
maybe… thats a pretty sqlite-specific issue though
ok, but in mysql it uses metadata to get count?
select desc, sum(value) group by desc order by desc
Anyone have any tips for converting characters like \\r\\n3. To something more web friendly?
if you are using the myisam engine, yes. I think if you are using innodb it has to do something else because the metadata may or may not be correct depending on the status of other transactions
Ok, i think tere’s a “sqlite_master” table
That must allow me to get the number of rows in a table
Is a few minutes acceptable for a query on a db this size?
depends on the query and the data… I can pull the ~50 values I need out of that 28 million row table in under a second. Deleting values from it takes tens of minutes
Yeah, I think I could pull 50 pretty fast
just took 5 minutes
for SELECT * and it returned only 400 records
careful though. “desc” is technically a reserved word. Either change your column name or always enclose it in backticks (`)
join #madpenguin
lol.
sorry
create tigger test BEFORE INSERT ON test FOR EACH ROW SET NEW.setting = NEW.id
id is the autoincrement field
but NEW.setting is not set to right value, it is always 0
insert has not take place, so auto_inc not available yet
is there a way to get the value?
AFTER INSERT trigger won’t work eithere
either
after insert trigger, but cannot set that way anymore, try to do another update
Is there a way to make auto_increment not leave any gaps in numbers?
Hello, does anyone recomend using temp tables? Are they a good idea or a bad one? I need to search a table of data that is set up relationally and the PHP to search it is killing me. Would a creating a temp table to run query off be a good idea? Willi tgo away when the current link
disconnects?
those are really used, or almost used numbers
ToeBee – I’m indexing all my columns for a test.
Query should be a lot faster now
temp tables can be useful… just depends on what you are doing. And yes, they will go away when the connection is closed. Also, they are safe for concurrency
!man temporary table
see http://dev.mysql.com/doc/refman/5.0/en/temporary-table-problems.html
heh well I suppose that is a good page to take a look at
I do not think i will have any of those problems. There isn’t an easy way to convert multiple relational tables into one is there?
if i have 5 categories and 1000000 articles in those 5 cateogries, is it faster to do “select * from articles group by categories” or “select * from articles where category in (list) limit 5″?
oh, wait, nvm
Anyone have experience with application partitioning here? If so, how is it working for you? I’ve tried 5.1 partitioning and on some queries is blazes on others it slows it down considerably ..
if it can prune on you partitioned col, it will be faster, if cannot, it cold be slower since there is likely to be multiple smaller index to be searched
you can create a table from any select statement. Also, there are views. (version 5.0+ only)
yes but is it relistcally possible to do with multiple relational tables?
ToeBee – I indexed all columns. Same select that took 4 minutes takes under a second now
can you prepend EXPLAIN to your query and get an idea of what indexes are used?
I knew what indexes I had — none.
I wanted to know how fast it’d be without indexes..
Obviously it’s going to be slow without them
I wanted to know how fast it’d be without indexes..
Lol
;^)
to tell the truth I had a smiliar revelation with indexes. Very simple query was taking 30 seconds on an old P3. Decided to try one of those newfangled “index” thingies and *bam!* 0.3 seconds!
I’ve never worked with a DB this size
So I didn’t know the impact indexes would have.
Now that I do, performance is good enough for me
just don’t go overboard… too many indexes slow things down again
Yeah
kimseong, I think I can prune with the application or a procedure .. not sure but I think I can prune
always in moderation
hmm so THATS what I’ve been doing wrong with my beer…
what is this moderation of which you speak?
!m threnody moderation
Sorry – I have no idea what function you’re talking about! but try http://dev.mysql.com/moderation
indexes slow inserts and updates. if you put EXPLAIN before your select query, you can see which indexes are used, and which are wasted (and can be eliminated, perhaps.).
‘nuf for the night. later.
sure… I have queries that pull from 4 or more tables. Thats what relational databases are good at
In the sql console how do I import a complete db from my HD?
what format is it in?
it’s a .sql dump
then I think you should be able to do “source /path/to/file”
ok
SOURCE “path/to/file”; ?
or you can do it from out on the command line. “mysql -u user -p database filename”
how do a select a db with SOURCE?
don’t think you need quotes around the file but it probably won’t hurt
you don’t… you select it before you use source
USE dbname
thx
it worked
horray
horray
k
I have a column (invoice_number) in a table (invoices) that I’d like to automatically increment each time. However MySQL won’t let me because I can only have one auto_increment column per table–and my primary key column (id) is already using it.
Any ideas?
there is no workaround to put more than one auto_increment column in one table.
is there a natural key you can use instead of the id column?
Anyone know of a known issue using WITH ROLLUP showing column values instead of nulls in 5.0.45?
my question would be why do you want two auto increment fields?
ToeBee, Yeah, I was just asking myself the same question :/
I think I don’t need two
The number of cases where you really would need two are few and far between.
yeh
when you run OPTIMIZE TABLE, might you still run ANALYZE TABLE after that, or is it unnecessary?
moo
weird
hi
if i do a mysql -user blah database_name foo.sql does that overwrite everything in the database i’m loading into?
no
like, if table bar exists in foo.sql and in database_name, will the data be only from foo.sql ?
no
unless foo.sql truncates the tables
foo.sql was created with mysqldump
if you didn’t use any options that are destructive then foo.sql can only add to the database
what is the easiest way to “truncate” the entire database so that the data only comes from foo.sql?
i’m trying to get to a previous state
truncate meaning truncate each table or truncate as in drop all the tables
dropping all the tables is fine
as foo.sql will recreate them
drop database foo; create database foo;
mysqldump –add-drop-table would do that when creating the dump (If you do this often)
–add-drop-tables that is
okay, cool
assuming there are no extra tables in the databases
i’ve already got the dumps [from the last couple of months]
i’m trying to recover data that was overwritten
Oh you had to go and add that caveat Eric.
okay, this is doable, thanks!
what happened to firewire?
eh
I have EricB on efnet
plus this way people somewhat associate me with PS and my blog
plus that firewire guy is an ass
haha
bad rep
Hehe.
I need to update two rows at the same time in one update statement.. (I’m updating a two-textarea form with one submit button)…
my $update_sth = $dbh-prepare(qq{UPDATE taxonomy_terms set description=? where term=?});
That’s my UPDATE for the one area (description), how do I add in the ‘term’ part?
just (UPDATE taxonomy_terms set description=?,term=? where term=?)?
Good call setuid.
That could be a problem
If ‘term=CarKeys’ and I change term to ‘Car Keys’, will it be able to complete that update?
The WHERE is filtered, then the update is applied, you should be fine.
Nada, it doesn’t let me change that field
Not in that query
hello
What is the error setuid?
mhillyer, No error, it just doesn’t perform the update on the field its doing the ‘where’ clause on
Just a sec, I’ll pastebot the code
http://rafb.net/p/o5K8Pm60.html
Perhaps I should SELECT the row id, and use that instead of term
What happens if you run the query directly?
it runs far far away
yep, now it works.. had to use id instead
http://rafb.net/p/JpxyJC29.html
Is ID your PK?
hello i would like to return messages and using LIMIT 0,20 only how can I still have a field that tells me all the available rows?
COUNT(id) doesnt work for me
Convert URL to hash with application to save storage (data,index) and data length processing time.
spookje–: Use SQL_FOUND_ROWS, just a sec.
what is wrong with mysql.h
I cant use it with mingw
spookje–: http://dev.mysql.com/doc/refman/5.1/en/information-functions.html#function_found-rows
Sorry, you need to phrase that in the form of a question.
mhillyer, Yes
Then your new query is better anyway.
Sorry about that –I was going to try to solicit some discussion, by tailing that with “please offer wisdom, a link or discussion.”
Really sorry that that.
No worries, it just kinda hung out there with no context is all.
help
!m [name] [function] !man [function] !m41 !m50 !m51 !man !man41 !man50 !man51 !manmt !manndb !manqb manwb see aide (fr) hilfe (de) ayuda (es)
not sure if it got 5.1 :/
spookje–: The function is older than 5.1
!m41 sql_calc_found_rows
sql_calc_found_rows see http://dev.mysql.com/doc/refman/4.1/en/logical-operators.html
!m mingw
mingw see http://dev.mysql.com/doc/refman/5.0/en/.html
What are you trying to do?
I cant use mysql.h with mingw
cool works
Surely your ambition is a bit more specific than ‘use mysql.h with mingw’.
it is incompatible
Work with me here f0rqu3. What is the end goal?
hi mhillyer
Hey!
ltns
how’s life?
Not bad, traveling my brains out.
I am adding mysql support to quake3
ahhh you doing what rainmkr used to do?
I include the mysgl.h in the main header used by the server
all paths are set
Actually I moved on, got a job as a pre-sales engineer for an email server company.
it whines about SOCKET
MySQL support in Quake3…
oh really? what company? and what kinda products? mxlogic? zimbra?
Place called OmniTI Computer Consulting, division called Message Systems.
oh wow, cool Wez and Chris and all
It’s a high volume MTA, used by big sites like Match.com and TechTarget.
so you’re in baltimore proper?
Nah, still in Canada.
i’ll be in va next week
so no one used mysql c api with mingw
Recruited SeekWill actually.
is he in md?
Nah, he’s in SoCal.
wow.. cool
i know some of the omniti guys due to their involvement in the php community
i met shifflet and wez in chicago
He does all his work by phone or email so his location is not as key.
I do all mine either by phone/email on onsite so same situation.
They are good guys.
ahh cool
sad
http://pincel3d.deviantart.com/art/Minimalistic-Child-62190683
so you’re pre-sales type stuff..
how much does the product cost?
Yeah, my ability to sound like I know what I am talking about has proven invaluable.
Lists at the price of a small car.
works for everyone on irc..
small korean car? or small german car?
Italian ;0
hmm fiat’s are relatively cheap..
Hehe
I have a huge american car
pwns
does it work with mysql too?
No, just with Quake3.
Wait, do you mean my product or his car?
his car
Aah.
Mail server that works with Quake3 = best way to delete spam ever.
your product sounds sane.. i wanna know what else works with mysql in f0rqu3’s world
… it is for frag logging
so regulars can see their stats
doesn’t it already do that?
I thought such things already existed.
nope
not directly
they generate a log file. then you need to parse it with some script
And that is… bad?
yes
i can guarantee you there are already sources out there that will parse the log file for you
Parsing logs lends to stability, the drop of a database connection does not bring down the system.
And often you can load the log straight to MySQL without a script as long as it’s delimited.
it is a game stability is not a real issue
It is a game, it doesn’t need fancy direct to database logging.
he needs realtime stats
For that matter, I support an enterprise mail server, we don’t even bother with direct to database logging.
wtf
Profanity is the inevitable linguistic crutch of the inarticulate blockhead
Just parsers that push to the DB.
Or in my case, a LOAD DATA statement.
mhillyer, Ok, when I submit the form that updates these two fields (based on PK, as above), and the page redraws, it redraws based on the original param that was used to access it…
you need to wait for next the log file parse to see your new stats
not to mention ingame command !stats to see your stats
Yeah, but you’re a little busy playing to check stats in the meantime.
with log parsing you will always see the old stats,
Wouldn’t !stats come from the internal system tallies?
So for example, “index.pl?edit=AltarBoy”, which is then updated in the db to be “Altar Boy” (with a space), but the page redraws after submitting, and edit=AltarBoy is used… not edit=Altar%20Boy
Sounds like an app-dev issue more than a MySQL one.
mhillyer, True, I’m just wondering how I’d re-query in the middle of a POST request, through MySQL’s UPDATE clause
confusing as that sounded
How often are these logs updated?
rethink your page drawing logic..
With a redirect at the end of the process?
after each frag
And yet this is not fast enough for you f0rqu3?
cool working awesome
didnt knew that it exists
i was plannig to do a SUBQUERY lol
wanted to ask how to do that
How can anyone who checks their stats that often stay alive?
hrm
BANG! *wonder how many frags I have now* BANG *wonder how many frags I have now* BANG *wonder how many… BLAM
seriously..
mhillyer, …
Yes?
!op mhillyer
!op f0rqu3
msg chanserv help
What is the correct way to enter “no data” for a field when the null value is set in MySQL Administrator?
bots have changed
are there any drawbacks if ill be creating the innodb table in the first place.. rather than from MyISAM then convert to InnoDB
??
Weird, I saw a typo in the MOTD and now that I op it’s gone.
it got scared of you
No, make the type you need off the bat.
since the tables will come from MyISAM? i can edit the dump file or dump it as is and convert it to Innodb…
Set it to NULL?
screw mingw
what’s the best way…
The tables only exist in a dump file so far?
I will compile it on linux
have you seen q4max stuff?
Hi, I have a question multiple databases vs single database. I was wondering what would be more efficient. I have 5 stores and can potentially grow to 20, would it be more efficient if I create a separate db for each store or have them all in the same database with a “store_id” field for
shared tables?
Are you using Query Browser?
i’m trying to find a good solution to store a lot of customer contact data. each customer (company) has many representatives and so on, so it’s a bit complicated. what kind of database should I use? go for some ldap hierarchy or a relational database? i have no clue
looks closed source
yes
No difference from a technical perspective, it’s more about layout.
Right click, look for clear field data or something like that.
Previously I just had to put single quote and a space or two between the two single quotes and it would work and for some reason it isn’t working this evening.
thanks my problem is just mingw. I stole the implementation from quake2world
mhillyer, would a single database have slower queries because there would be more records?
Use Outlook?
btw __LCC__ in mysql.h is plain stupid
You will be joining tables or joining databases, so either way the record count is the same.
YOUR MOM!!!
lcc is so obsolete
BURN!
umm… you mean outlook with active directory (ldap) as database?
I mean just use outlook.
Do you have a front end yet?
Hey EricHerman
good morning, sir
how’s life treating you?
There’s a lot of factors in a recommendation padde.
Record count would be the same but the total number of records (ex: in the products table) would be larger because that table now has the products of 5 stores. So would total records affect a query if I’m specifically calling for records where store_id=”x” ?
I am still getting an incorrect integer value. I dont think the MySQL Query browser is recognizing the “notnull” setting in MySQL Administrator.
Not bad, lots of travel these days.
no, nothing. and this is for a company, many people have to access it. outlook with some local storage is out of the question
mhillyer, I’ll be flying to the States (seattle) in just 2 more days.
I thought you said NULL was allowed.
how will people need to access it? Why?
generally, open source is muchly prefered, and microsoft office is on the ‘get rid off list’
Not in Seattle anymore EricHerman?
mhillyer, oh no. I’m in Amsterdam, NL
Why do they need to access this data? How do they use it?
I think I am out of the loop. How long are you on the continent EricHerman?
My better half in in the “Medical Informatics” masters program at the Universitit Van Amsterdam
mhillyer, yeah, I’ve been out of the states for a year or so.
let me think… send mails, write quotations/invoices, looking up the right person to contact/call
and we are seriously considering living in Europe some-what permanently, actually.
I may be in Seattle before the end of the month myself.
really?
Install SugarCRM.
It would be funny if our paths crossed.
Are you planning on catching up with Brian Aker and other Seattle-ites?
Yeah, have an onsite install at a seattle customer potentially coming up.
i’ll have a look, thanks for the suggestion
So far only looked up flight prices, was going to figure out the rest as I went.
I’m hoping to bump into some folks, like Brian, but I have no solid plans.
Last time I was in Seattle I did dinner with Gerry, never connected with the rest.
I’ll have more time this time around.
Did you get my last message. Basically I wanted to know that since I’m sticking all stores in a single db sharing the same tables then the tables (ex: products) would have more records. Would the queries run slower if I’m sticking in the WHERE clause “store_id=’1′”?
Gerry’s great fun. Always finding stuff to enjoy in life.
If you index it well I wouldn’t see any reason not to just go with a store_id.
try to get to the zoo
i had a blast there
That’s why he’ll live longer.
I have changed MySQL Administrator to allow NULL Value in the field and it still won’t take it. Currently the datatype is set to small integer which is more than enough space for this tiny number.
But you want to set the number to NULL Okee?
most of the entries for this field are supposed to be blank (no value).
Thanks, so I guess it’s relatively the same. Single database simplifies things for me because I won’t have to keep track of multiple DBs, but at the same time I guess I’ll need to watch out for security (make sure one store doesn’t have access to another store’s data)
And you found the right-click option I described.
You also eliminate overlap where both stores carry the same #7 widget.
I did but all that does is clear the area where you enter your data. It just saves you the step of manually rerasing it.
Exactly.. for example all stores will have access to all the records in the table “countries”
Is there a way to automatically insert a row…but if it’s already there, just update it?
Okee, applying your change should set the field to NULL IIRC.
Or should I just do a select and work from there.
REPLACE zackattack
!m REPLACE
REPLACE see http://dev.mysql.com/doc/refman/5.0/en/.html
thank you
Well it should set it, but it acts like it isn’t set
wait
that links sucks
http://dev.mysql.com/doc/refman/5.0/en/replace.html
After this problem occured I tried just entering zero and it took it. So now I am trying to update the field with zero and it won’t let me leave it blank.
thanks mhillyer
Sorry Okee, I’m going on memory, let me find the doc page.
ok. This is quite frustrating and important because virtually all of the tuples have a blank area for this field.
Hmm, the doc is missing the right-click option for setting a field to null. Who wrote the damn manual for the query browser?!?
