update”. MySQL returned error “126: Incorrect key file for table ‘./wikiwldb/wl_searchindex.MYI’;

Those c an’t be null values, except in oracle. can’t Only in Oracle will you find: ” IS NULL In MySQL: ” IS NOT NULL, in all cases. Of course, ‘NULL’ IS NOT NULL, too.

what do you mean, I dont have is null the table structure is not null for all, so why its accepting null values?

Because that INSERT will NEVER attempt to insert a NULL, unless you’re not showing some columns.

explain please!

Explain to me how you think that INSERT will attempt to insert a NULL.

well what i know is that my query which is this “insert into city (country_iso, region, city) values (’$p_country’, ‘$p_region’, ‘$p_city’)”

Expand the php. I can’t address a non-SQL question.

should not be executed because the $p_city = “”

” is not a NULL. I’ve said that several times now.

I know its not null the valuw entered into the database is empty cell.

Why do you think it should not be allowed?

so in my table the city is empty cell while it cant be null

Alloosh:null has nothing to do with ”.

because I have not null in the structure

NOT NULL doesn’t stop ”.

null means empty right ?

BECAUSE ” IS NOT NULL!!!

and empty string (”) is not the same as a null.

Is this an english problem?

no, let put it that way I have table structure like this country_iso char(2) not null, region int not null, city varchar(80) not null,

To insert a null, remove that field from the insert list or do this: insert into city (country_iso, region, city) values (null, null, null); Notice how none of these are ” strings.

I dont want to insert null, I am asking why the query got executed and now I have empty cells in my table

good morning (or evening)

an unwillingness to listen.

You don’t seem to be understanding the point. *nod*

please explain me

Your INSERT can not possible attempt to insert a null for any of those columns. So that means it will never fail due to null values. possibly

NULL means a value has not been assigned. You assigned the value ” to the field. It is therefore not NULL.

The only way your insert could possibly attempt to insert a null value is is you supply an explicit null or remove the field from the insert list. Only then will the insert fail due to null values. s/is is/is if/ ” is a non-null value and that’s why you see no error. I don’t believe there’s a clearer way to explain it.

yesterday I was too tired to continue with my quest for a better query, could someone please help me find out why the two queries in the pastebin run at so much timing difference? http://pastebin.com/d44a4fa87 I mean, the only difference is the ORDER BY removed in the slower query

My comment that: ” IS NOT NULL …. is about as clear as I can be, since that SQL can mean only one thing and doesn’t really require any further english knowledge.

the query in the pastebin is incomplete, I’ll add the part that makes all those joins needed

Try the following statements. Consider the results: select 1 is null; select ” is null; select null is null; or consider these results: select 1 is unknown; select ” is unknown; select null is unknown;

http://tinyurl.com/2yu2te

http://pastebin.com/d57265fe5 can someone tell me why or how an order by can change the execution plans? …and why the first query is so much faster also, I wanted to ask… right now I have just around 1000 products in my products table, and only 240 categories… how do one manages to map user searches to good queries in producs and category tables that have much larger sets (think of 2 or three million products and 2000 categories) for instance if a user wants to either find all products belonging to a given supercategory or if he wants to find a product whose category name contains certain given words (as in the query I just posted)

hi

hi..

ppl i got a small problem when trying to connect to mysql mysql Can’t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock’ i’m under centos with a default install of mysql from the raining answers i undersstand that my question is noobish

service mysqld start

an admin deleted the root user from mysql… i tried starting with skip-grant-tables and added a user again, however mysql says “ERROR 1290 (HY000): The MySQL server is running with the –skip-grant-tables option so it cannot execute this statement”

seekwill, the restart fail

What part of restart, the shutting down or starting up?

seekwill, the shutting down sucess , the start fail

any idea how to readd the root/admin user?

cat /var/log/mysqld.log

can not use GRANT with it enabled, you need to INSERT

seekwill, http://pastebin.com/m4dc09dd8

hey, should I user or not left joins vs inner joins? !man join

see http://dev.mysql.com/doc/refman/5.0/en/join.html

Thanks Leith that worked

great

hello what is the best way to put numbers like XX,XX on field ?

prompt?

dont think you can… which OS?

Im trying to create my own and not connect to an external im using Fedora 5

then do /etc/init.d/mysqld start as root

okay, its up but… how can I set the username password for it?

fresh installed?

yes

mysqladmin -uroot password ’s3kr3t’ mysqladmin -uroot -hMYHOSTNAME password ’s3kr3t’

okay and localhost will be my host hehe

in that case dont bother thats what the first one did no wait you have to do it

so i want to put localhost?

just replace localhost with your public IP mysqladmin -uroot -hMYPUBLICIPADDRESS password ’s3kr3t’

okay hey trengo, thanks alot! I’ve been looking for a simple tutorial to cover this exacct thing, but havent been successful

jackrabbit130 the rpm install tells you to do it now, you have to connect with mysql -uroot -ps3kr3t mysql create database blah; grant all on blah.* to blahuser@’localhost’ identified by ’s3kr3tt00′; grant all on blah.* to blahuser@’%’ identified by ’s3kr3tt00′; and voila you can now connect as blahuser and play with its own little database, blah

hehe this is much better than dealing with phpMyAdmin no loopholes, thanks!

cheers

hi anyone around?

moo

ask

“I have a question”, Don’t ask: “Is anyone around?” or “Can anyone help?”. Just Ask The Question. Also, please read: http://workaround.org/moin/GettingHelpOnIrc

toUser conversation for each subject). So two rows with different subjects with same fromUser or toUser respectively becomes two conversations…

sedeki, select fromUser, toUser, subject from yourtable group by fromUser, toUser, subject

i only want to return the latest message in the conversation… i’m writing something gmail-like gnari

groupwise max

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

hmm.. I set a password on mysqladmin but now I cant login or make changes I tried mysqladmin -p’password’ but no luck

you login using mysql not mysqladmin mysql -uUSER -pPASS

okay, I can del/create databases from there?

e.g. mysql -uroot -pishouldreallyreadthemanual you could

my password is setup for the mysqladmin account not the mysql account

mysqladmin is a management software for the database, mysql is the mysql client

yes, im having problem with the management software and not the clent for example, when I use the command “create database” I get a authentication error

man mysqladmin

did that

jackrabbit130, you need to supply the correct password *sigh*

hehe

it gave me the paramater -p I tried the paramater -p(passwordhere) but no luck tibyke, I set up the password using “password new-password” but, since I have changed the password, I cannot make administration changes

jackrabbit130, so that should be ok

what does lower in SELECT LOWER(… mean?

lowercase ever heard of mysql.com/doc, XoZ ?

lowercase? it puts all values into lowercase?

[root@new-host /]# mysqladmin create database db1 connect to server at ‘localhost’ failed ‘Access denied for user ‘root’@'localhost’ (using password: NO)’

cant find info about select lower( in mysql.com

SELECT DISTINCT `subject` FROM `messages` WHERE `ownerID` = 1 AND `fromID` = (SELECT ….) I’m stuck here….

if you don’t include the -p, mysql assumes you know what you’re doing and doesn’t ask for the required password.

jackrabbit130 … you need to include the password

the_wench, gnari….: Am I on the right track?

I see, so with every command i must include the password paramater

if your password is not empty, yes

okay it worked, db1 has been created

jackrabbit130 you could also use mysql to get a mysql shell

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

http://rockj.pastebin.com/d23232bbe , why does that message appear when in my config of the mysqlserver it has : max_connect_errors = 0 ?

ahhhhhhhhhhhhhhhnnnnnnnnbn

XoZ, search harder

arra7, got me a nice link

because 0 is the number of errors to accept. See http://dev.mysql.com/doc/refman/5.0/en/blocked-host.html

sedeki, does your message table have a primary key?

yes `id`

sedeki, is the highest id the same as last message?

the highest `date` is the last message (the unix timestamp of which it was sent)

sedeki, select m.* FROM (select fromID, toID, subject, max(`date`) as `date` from messages group by fromID, toID) as g JOIN messages as m using (fromID, toID, subject, `date`)

what does USING in LEFT JOIN customer USING(cust_num) mean/

m.* = messages.* ? or a non-existant alias?

XoZ, it is the same as ON (customer.cust_num=othertable.cust_num) sedeki, add a “AS m” after messages

arra7, thanks.

sedeki, wait sedeki, there was one in the query. are you getting an error? select m.* FROM (select fromID, toID, subject, max(`date`) as `date` from messages group by fromID, toID,subject) as g JOIN messages as m using (fromID, toID, subject, `date`)

three messages with different fromID’s has been addressed to my toID. they all have the same subject (`adID`) but that query only gave me one message. also is it possible to be sure that ownerID = %my_user_id% in what query do i add where `userID` = %my_user_id% ?

sedeki, is that not what you asked?

each “conversation” has (fromID = %user1% AND toID = %user2%) OR (toID = %user1% AND fromID = %user2%), and an unique subject (`adID`) for each conversation

sedeki, you said you wanted one output row for each (fromID,toID,subject) combination

if you send me two messages regarding two different subjects we both see two different conversations yes my head is mixed up but i think it’s on the right track

sedeki, i think my Mysql was correct, except that you now introduce that toID and FromID can be switched you probably can adapt it to your use

hello all

i wanna take differential backup of mysql any body know what should i do ?

check the manual for “incremental backup” and “binlogs”

incremental backup not good for me now i take backup

what is a differential backup ?

in full and incremental but want to take differetial merge full backup and incremental plz help me

hi guys

hu

i have a question about select performance i have a big database ( 6 milion rows ) and i make different selects in it but every select takes about 5.20 seconds it doesn’t matter if I have limited it with LIKE statements or I use the whole database any idea what stages the select goes? or where i can find more information at least

do you have any indexes?

yes the select is something like SELECT SUM(length),COUNT(*) from sniff_connections WHERE timestamp = 1188165300 AND timestamp = 1188165600 AND ipsrc LIKE ‘%’ AND ipdst LIKE ‘192.168.29.2′ AND proto LIKE ‘%’ AND layer4_1 LIKE ‘%’ and layer4_2 LIKE ‘%’ but i suppose the problem comes from the big table, because if the table is small, the same select executes faster

how many rows are returned?

1 bye

see ya

how bad is it using SELECT COUNT(*) FROM (subquery) vs. the COUNT directly in the subquery instead ? for my pagination system I’m thinking of doing the former instead of trying to “build” the count query every time

why the subquery?

’cause it’s easier to make the count that way the subquery is the actual select that will bring the search results and I’d want to have the total number of the resulting query for pagination (then I’m using limit on the query to do the pagination) Trengo, for example if a query is to return 20 results, I’d want to have that 20 to calculate the number of pages, then I’d use limit on the query to get the page and just the number of items I want to display on each page

Kronuz doesnt look like a very good idea

why? how should I do it then?

replace select blah with select * replace select blah with select count(1)

?? hmm

make your query text into two parts the select and the from

what about the distinct ? or unions ?

?

nevermind, I think it’s working as it is right now… I’ll worry if later I start having problems (I did change the count(*) for count(1) tho’)

used to be faster on oracle, the habit stuck

oh so it’s the same in mysql ?

oh yeah

hehe

i just never checked if its any faster

anyone seen a double-entry accounting program that makes CLEAN ues of mysql? i’ve seen a couple with tables all over the place.. account, transaction, journal

does SELECT DISTINCT acts over the whole selected columns ?

brett- needs lots more tables

ah, care to elaborate? (trying to think this through in my head)

is there no way of telling to find distinct rows on just certain selected columns without using a group by ?

for multi-uesr support obviously it’ll need a users table and such..

and the plan and the codes and defaults and imports

?

there’s lots more transaction headers and lines

plan, codes, defaults, imports? for double entry accounting?

several lookup codes

hmm

oh aye try and do it “hmm now i need this” “now i need that”

well, i made a quick simply ruby one but, lol, it’s not functional yet..

unless you do it all “hardcoded” but then you find out its limited

yeah i guess thats what i was thinking keep the db as simple as possible and hardcore everything that can be hardcoded

ouch never

well not EVERYTHING but everything besides the data i.e., the stuff that will never change

everything will including VAT rates and such and vat applicability

yeah very true

exchange rates

i’m thinking the basics could be covered with just three tables though

theres loads

anything else could be added

oh yea

i *think*

even just one big flat table

yeah hmm i didnt even think of that that would be the quickest way by far

its easy for quick failsafe data entry a nightmare for automatching

In Oracle we can do things like this to get the second group of 20 items from an ordered list: SELECT r, id FROM (SELECT DENSE_RANK() OVER (ORDER BY TRUNC((id-1)/20)) as r, id FROM xxorder) v1 WHERE r=2 ORDER BY id; Maybe MySQL will provide similar support in the future.

I installed MySQL and configured a password with it. Then the computer restarted with a hardware error, and now I can’t log into MySQL when the password is right. any solution for this problem?

Salah which OS?

Trengo, Windows XP Home Edition, SP2

Salah ups sorry, cant help, dunno windows

That was just an example. There are many ways to use these constructs.

Trengo, not me either..

hey, can someone please help me find out why a query of min is not using certain indexes I think it should be using? what exactly does Using temporary; Using filesort mean in an explain ?

Something like: Used a temporary table and sorting performed that is not in memory.

and that sounds bad, is it?

If that’s the only solution it’s fine.

oh

Check this: http://dev.mysql.com/doc/refman/5.0/en/explain.html Read the section on “Using filesort” and “Using temporary”

I see my problem is hard to find. Is there any ways to reset the password when I can’t remember it?

Several.

Xgc, can you please explain? Xgc, forget it

Im making a MAX() on a field that is not an int, it is a varchar, how “CAST” it to int?

good I think I’m finally close to get the better query for what I want

Hello where can i get MySQL libraries for mingw?

hey all just curious … is the cost to do “select( (field1*field2)/field3 ) from table” very high? as opposed to having an extra field and writing a stored procedure that would, on every insert, automatically recalculate it so i could directly select that field? (does having those fields indexed / not indexed make a difference?)

ok back

how can i see what the max_packet_size is set to ? i suppose it’s actually “max_allowed_packet” select @@max_allowed_packet; thanks

1) No. 2) Don’t do that. 3) No.

so I could use adn extra p[air of eyes because I cant find my error. http://pastebin.com/m63bcb759

add the error message

you know I think I see part of it

double where?

well for one I am grouping by the key

I don’t think that would result in an error. But having two WHERE keywords would

http://pastebin.com/m79e4bfca ah haha

and look at that. The error message tells you exactly where to look

I know it was a stupid mistake

thanks!

A bigger mistake is the GROUp BY clause. Just remove it. You’re welcome.

Yeah I know it was redudndant

It does nothing functionally, apart from possibly wasting resources. Technically, it is incorrect, even though MySQL allows it.

I have mysql running in internal network, like i have to login to my remote ip and ssh from there to 192.168.1.4 and then login to mysql. I want to use mysql yog to directly connect to mysql on 192.168.1.4 from outside how is it possible?

portforward

remote

remove bind-address= and skip-networking from my.cnf and grant permission to the external ‘user’@'host’ and remove any firewall rules

and yes, port forwarding. However I would really consider this… it has security implications

you mean ssh tunnel?

no… forward the mysql port to the mysql server on your router

Ahh, Okay.

if I make a varchar(16) can I store a string in it with 32 characters? or does it get truncated also is there any disadvantage to making everything a varchar(255)

it will get truncated varchar(255) will likely result in some extra memory used and cpu over head packing/unpacking rows

no it wont ‘

it won’t what?

won’t result in extra memory or cpu overhead

you sure about that?

yep

char(255) probably would

yep, varchar won’t

when mysql passes a row with a varchar to a storage engine it passes the entire size

what about when I do selects from the cli, will varchar(255) automatically make really wide columns?

it’s then up to the storage engine to shrink/expand it so varchar(255) will allocate 255 bytes when passing that field to the engine memory overhead.

ok technically it may do that.. but that is probably not an area you want to do your optimization on

right

i think the extra 1 or 2 k of memory for a split second won’t kill me

for one row no

I’m doing logging

but what about hundreds or thousands of rows per second

no more than 50 rows per second most likely

it still won’t compute to more, we did 4000qps

 

Personal jsp web hosting provider

 

vluther I saw some benchmarks yesterday that showed the row unpack/pack was the slowest thing when doing group by because it’s done multiple times per row

we were doing logging/inserting like rycar is, it alla depends on what the end product will do i guess.

on inserts it won’t be noticable but that’s not a reason to just set everything to varchar(255)

well, true

my end product is just storing the logs until a processing program can come by later to digest them

why can’t I do a fulltext search from a view of a table with a fulltext index ? is there no way of doing this?

no there is a way of not doing it

i’m having strange problems with this query it is almost like the ORDER BY clause is causing it to run VERY slowly if i take out the ORDER BY clause, no problem if i leave it in, it might take 40 seconds to return results has anyone encountered this? is this a bug in mysql? i realize the question is broad.. i can give more specifics if anyone is interested

wow, what’s the size of the table/returned results?

with what command the structure of a table can be displayed? desc and describe?

desc means descending

hey, how can I know if a given query will behave okay with much more rows than there are currently in the database?

it’s used when setting which column to order by

so which then?

perhaps using the explain and knowing how the database will change one can say that… no?

generate random data and try it out

but how? hmm

so which then?

guessing requires you know the internals it is easier to generate a useful dataset to test against it should match your current dataset

I know the database well and I know what tables will grow and which wont (most likely)

You can usually make a fairly good guess from looking at the query and the EXPLAIN, but the only surefire way is to test it. And the good guess requires you have a good idea of how everything works.

Simetrical, that’s what I meant, the “good guess”

If anything scans a table, it will probably scale horribly.

’show create table table_name;’

If it does a filesort *and* scans the table, even worse.

growing datasize means caches might get used less efficiently

not sure what u mean?

with small dataset you may run completly from memory

If it examines a number of rows independent of the size of the table, you should be fine.

with what command do you display the structure of the table?

type that command at the mysql prompt to display the structure of the table.

not sure if you can read

So check the “rows examined” in EXPLAIN output and see if you appear to be examining a lot of rows that won’t be returned.

nvm its describe of c

got gui?

Simetrical, if you see the tables (three currently) and the explain for three critical queries, and I tell you how the database will most likely change, could you tell me if you think it’ll scale well? would it be easy?

just the mysql shell

Kronuz, possibly.

hm, ok, nevermind then

okay, let me poste the tables and the explain

hmmz If any column value in an arithmetic expression is null, what will be the result?

got gui on the pc you’re working at at all?

NULL

or error?

NULL

no, rather just start using mysql in shell To restrict the rows returned from an SQL Query, you should use which clause btw?

LIMIT

IFNULL(`col`, 0)

or where of course WHERE/CONDITION also works, just if you need some spec results back or nvm, only WHERE I guess

http://pastebin.com/d2fdf7061

Character strings are enclosed in double quotation marks eh in single right?

quotes

Use ` around identifiers (database/table/column/alias names) and ‘ around strings and dates. MySQL does allow ” for strings, but ANSI standard uses ” for identifiers (which you can enable with ANSI QUOTES option) and dont quote numbers

so double quotation marks?

Kronuz, what are the queries and their EXPLAINs?

hold on, making them http://pastebin.com/d74f04aae

Kronuz, my guess is it will die horribly on very large tables. Almost all the tables have type ALL, which means a table scan.

hold on, that one I knew about it (sort of) but for that one the categories table won’t grow too much more it’ll stay at 240 rows or 300 the table that’ll be growing will be products and product_categories (each product can have from one to 5 categories)

Hmm. That *looks* like it will scale, then, but I could easily be wrong. How many products do you have in the database now?

so the products_categories will be in average 1.6 times the size of the table products (which currently is just 750 rows) 750

Then it should be okay, probably, as you add more products.

http://pastebin.com/d35214ef2 (that’s the one that actually does the searching)

I’d imagine that would also scale fine with more products. But the only good way to find out is to test.

http://pastebin.com/d1c3bd153 the three are very similar, ’cause I ran them with the same user criteria but it’s the most complicated query one can get from the user simpler criteria generates simpler queries

As long as you see 1’s and 2’s in the appropriate “rows” entry, it will probably scale okay.

but those ones use categories and also search for stuff in full text but those are most likely from the number of products and will grow as products list grows it took me a lot (and I mean a lot) of time getting this queries optimized this way… but I wasn’t sure they would scale it’d be nice if I could optimize the first one further http://pastebin.com/d4e54fb77 maybe someone here is a MySQL guru and can help me optimize that one a little more (or any of the two others)

isn’t there a way to get the date but no time using a default value for the mysql fiel? field*

anyone can point me to a free GUI for MySQL. Thanx. BTW I am running Ubuntu 7.0.4

dev.mysql.com

thanx

anyone?

gui

GUI tools can be found at http://dev.mysql.com/downloads/gui-tools/5.0.html phpMyAdmin at http://www.phpmyadmin.net/ and even navicat or http://www.webyog.com/en/

[domon], I don’t understand your question.

well CURRENT_TIMESTAMP puts it in the format YEAR-MO-DA Hour:min:sec i just need the year-mo-da current_timestamp is being used somewhere else _ i’ll just do it with PHP i guess

Is it possible to have a database on an external harddrive, and access it from a host operating system? external hard drive does not have an operating system

how do I select unique values from a field only? is it possible?

select myfield from table where field = “value”; is that what you want?

no…

select distinct `field` from table;

thanks

ah ok, sorry about that

Eagle-101: sure. But I doubt it would be recommended

Eagle-101, sure it’s possible. Your computer doesn’t care where it reads its data files from. It may, of course, be slower.

ok sure, so how do I tell mysql to look for the database file on my external hard drive?

Eagle-101, just set the data folder appropriately.

the my.cnf file datadir=…

ok, but I don’t want to affect other databases on my internal drive thanks

err well that changes the data dir for all databases

I can just move the others so if the hard drive is /dev/sda1/ do I just put that?

eek no you need to format it and mount it

well yeah, sorry assuming its mounted point it at /mnt/sda1/

yes. And if you are using linux you might actually be able to use symlinks to store only one database on the external drive..

ah makes sense, and of course I’m using linux!

as hot as linux is, some people still don’t

heh, I know I suppose I’ll alocate 200GB for mysql Thanks.

is there an equivalent for CURRENT_TIMESTAMP that matches the date type in mysql (for default value)

er… CURRENT_TIMESTAMP ?

that doesn’t match the date type.. _

in what way?

it matches timestamp, not date

[domon], ah, i misread your question: current_date;

ahhh “Invalid default value for ‘dateAdded’”

1,0hello, back with another question

[domon], are you trying to use it as a default?

yes..

0,1SELECT itemid FROM main_shop_stock WHERE shopid=’ashopidnumberhere’

[domon], sorry, it does not match in that way

so there’s no way to set a default value for the current date

evenstar, argh what is it with the coloring?

How can I tell MySQL to randomly NOT include some rows it would normally gather in that query? its my script, sorry

distinct?

Would that make it so it would randomly gather different rows every time the query ran?

oh…misread your question

its ok *it’s

evenstar, SELECT itemid FROM main_shop_stock WHERE shopid=’ashopidnumberhere’ and itemid not IN (…)

is (…) part of the query?

evenstar, rather SELECT itemid FROM main_shop_stock WHERE shopid=’ashopidnumberhere’ order by random() LIMIT 5;

Oooh I see how yer doing this. That is so cool I never thought of limiting it to a certain amount of results every time but it makes sense.

evenstar, the func is probably rand(),not random()

rand is it

select …. where rand()0.25

I have another questions. In one of these shops (shopid #2 if it matters) there are like over 100 item ids. I don’t care how many ids it pulls as long as they are random and different every time. What do you recommend I sent the limit to? That is so freaking cool

evenstar, that one will give each a 25% chance of being included

ok Let me try it and see how it works, hang up ^_^ *hang on, my bad

‘random’ and ‘different every time’ pose a contradiction..

well you can’t make them ALWAYS be different _

what you want is a pseudorandom but *defined* order for your items

Just when this shop restocks each day I want the items it puts in to be random so people can’t for sure count on the exact same items being there every day.

I only know one technique to make that happen, and it’s done in the application rather than mysql

(This isn’t a real shop, it’s part of a game, by the way.)

ORDER BY rand() has the unfortunate drawback that it’s HORRIBLY SLOW. At least AFAIK.

the other method is slow too

I think it works fine as long as your table isn’t over a couple hundred thousand rows or so

they should be equally slow

Would it be just as slow to have my PHP script do the figuring out of what to pull and what not to? I don’t expect this table to even get over 2,000 any time soon.

evenstar, if the primary keys is a range of integers without gaps then you can do it much faster

my primary keys are auto increment integers

evenstar, but as soon as there are more WHERE conditions involved, you create gaps so to speak, an other methods will be biased in some way or another

Oh and this script that controls this MySQL query only runs once a day.

it’s not really that important.. as ToeBee mentioned

It’s not one that runs every time loads a page. *every time somebody

evenstar, in that case these will probably do

Okie Now I’m gonna go try out the code you guys showed me and see if it works according to how I envisioned it to work Be back in a little bit.

SELECT * FROM news_comments WHERE post_id=”24″ ORDER BY id DESC — Would this be a valid SQL query?

after using mysqladmin -u root -p shutdown to shut down the server, how do I re-start it from the command line?

what distro are you using?

5.0.37 on os x

it’s a really long one, do you have to to it from the command line? there is a control panel thingy that starts it /bin/sh /usr/bin/mysqld_safe –datadir=/var/lib/mysql –socket=/var/lib/mysql/mysql.sock –log-error=/var/log/mysqld.log –pid-file=/var/run/mysqld/mysqld.pid

ok.. yeah I neglected to install the control panel thingy when I installed. I need to re-do the download of the old 5.0.37 distro to get that control panel installer.

or just go to system preferences and click on the mysql icon at the button, then click the start server button

rycar wow, thanks.

did you have the icon in system preferences?

no

Technical difficulties totally unrelated to this. I could be a little bit.

no. and when i installed, I did it from source. hopefully the preferences tool that comes with the 5.0.37 .dmg (if that dmg is still available) will be compatible with my source install of 5.0.37.

http://pastie.textmate.org/95602

or maybe i’ll “just” upgrade to 5.0.45

I’m sure the tool will be compatible as long as you installed to the same directories and the installer does the tool is on my system at /Library/PreferencePanes/MySQL.prefPane

Okay, I’m back, and I’m doing something wrong SELECT itemid FROM main_shop_stock WHERE shopid=’1′ ORDER BY rand()0.25 Each row is supposed to have only a 25% chance of being pulled

order by rand(), or where rand().25, not both

mmk hang on Change it to this? SELECT itemid FROM main_shop_stock WHERE shopid=’1′ ORDER BY 0.25 oh nvm hang on It’s working. You guys are so freaking awesome I coulda never figured this out from just googling endlessly for hours xD *loves this chat*

read a book - it’s the best way to learn

I have 3 or 4 books. I hate them. I can never find what I’m looking for.

You don’t know enough to know what to look for.. read them!

What do you mean? My site has thousands of people that play it.

it wasn’t an insult

I wasn’t trying to be mean either.

but, you don’t know syntax - you need to read, not reference. my opinion

Mean read a book straight through and do all the programs they have you write? I’ve been thinking about going through my PHP and MySQL bible but a lot of the stuff is so irrelevant to my site it turns me off

I dunno, up to you. I never write those sample ones “when all you have is a hammer, everything looks like a nail” You have to know all the stuff these tools can do you for you Er. I win @ typing

Yeah. I’m learning more every day. I want to be good as somebody like you some day

In particular I used to do so much extra work in PHP

I’m just umm…an intermediate coder. Not bad but not as good as I could be either.

when all I really needed was the right query.

I know how you felt

Now that I’m studying (my)sql, that problems is going away quickly

I just recently found out you can do simple evaluations right in a mysql query (e.g. set onhand = onhand + 2500) Do you go straight through a book or just look for things that have relevance to your site/programs/whatever? Everything I do revolves around my sim game.

In any sizable project, I think pretty much all of it becomes relevant. at the very least, you need to know about it so you know that you don’t need it

Oh, I see

I don’t think I’ve ever finished a coding book though, haha

You know where is something I have always wanted to know about MySQL but didn’t know where to find it out *there Let me figure out a way to explain it…it’s sort of complicated but would be the best thing sinc esliced bread if I could find out how to do it, if it’s possible Okay say I have this table called shop_stock and let’s say in it it has the column itemid. The item id corospons to the idnumber in another table called items. The item table not only has the item ids but also the names of each item “e.g. Blue Tennis Ball”. I want to query shop_stock and order the results in ABC order according to the names in items.

Of course, use a join

Join? Where do I look that up? Is it just called join?

yeah, there are several types too

do I just search for join in the MySQL documentation?

probably

Ok

or “mysql join tutorials” on google or whatever. I’m telling ya, you have no idea what’s waiting for you. good stuff, just read++ for a while

Now I need some more help. If you could teach me this simple thing that has eluded me, I could understand syntax examples so much better. Then I could get more out of books and the manual. Let me pull a book out, hang on

I’d probably say joins are the single most important feature of a relational DB like mysql

I believe that I hated not knowing how to do what I explained omg….things would be so much simplier o.0

while($row=mysql…) { mysql_query(”something with $row[’id’]..”); } Then you need to be using a table join in a single query Haha… hmm.. I just logged into a machine which runs fortune when you log in. My fortune: If you only have a hammer, you tend to see every problem as a nail. — Maslow

lmao

creepy

yeah int preg_match_all ( string $pattern, string $subject, array &$matches [, int $flags [, int $offset]] ) see the [’s and stuff to show you like what is optional and what is required or whatever? Well they confuse the life out of me. And I can’t find anything that explains it o.0

yeah that’s standard notational stuff. items in [] are optional See the ‘Conventions used in this book’ section at the beginning of any programming book

let me look specifically for that, hang on

should be able to find an explanation

blahhhhhhh all it had was stuff I already knew *gets in another book* well except one thing I didn’t know … meant “and so on” (e.g. SELECT field1, field2,… from table) GRRRRR I can’t find any conventions explination in my MySQL Bibile (yes, I know it’s outdated) see why I could just shoot these books?

hi… how can I disable the query status output? that one that says “query ok, 1 row affected”

I can’t find anything about notations or conventions in this stupid book I’m thumbing through the chapters to see if it just tells it to you whenever they first start using a certain type of notation ([ ] things for instance) “recall from my discussion about the conventions used in this book” ….WHERE DID YOU SAY THAT MR AUTHOR? omg I need to chill out this book is making me hot under the collar He pry put it in like the middle of some seemingly random and unnecessary paragraph o.0 oooh I found it YES!!

means required and [ ] means optional, yay xD

err wait no that’s wrong This is so confusing I’ll read it to you, maybe you can help me.

mysql -oB

denote required portions of syntax for the command, square brackets such as [ ] denote additional modifiers and parentheses such as ( ) denote optional portions of commands.”

tells you what the bare bones are of the command that MySQL will accept without an error?

http://dev.mysql.com/doc/refman/5.0/en/manual-conventions.html

with the NDB storage engine, I know that joins are normally “bad” (e.g. the data has to be pulled up to a sql node for the results to be composed)…does this mean that subselects are oftentimes better to use in place of a join when possible? (e.g. I need data from multiple tables returned in a query, is a subselect usually as efficient or more as using a join or multiple separate select statements)?

and how do I set the default database?

Ok, I’m reading that now psilo2. I’m stuck on one part. TRIM([[BOTH | LEADING | TRAILING] [remstr] FROM] str) what does the str mean, put a string or variable with a string in it there? I’m gonna be doing something in my database so if anybody answers PM me so you’ll make my script beep wow this chat is dead I have another question now too x)

I’d like to make my database available to remotely connect to it on port 3306. I have mysql 5.0.38 and I know where my.cnf is stored. How do I make it happen?

remote

remove bind-address= and skip-networking from my.cnf and grant permission to the external ‘user’@'host’ and remove any firewall rules

is it possible to fetch data from tables at the same time, where both tables have a same field name, and order the result by that field? from 2 tables

I asked almost the same question as you and they told me to read up on the join umm…comand something er rather

thanks worked!

evenstar, and did you have luck?

I haven’t tried yet. I’m still finishing up a script that pertained to the first question I asked, l o l. They told me, however, that being able to look at the data in one table while you query another is the cornerstone to this type of database Let me paste the exact question I asked to you

select t1.col_1_2, t2.col_1_2 from t1 join t2 using col_1_2 order by t1.col_1_2;

t1= name of 1st table?

the trim() function removes whitespace from a string. The ’str’ is the string in question.

col equals column name? Oh ok SELECT itemid FROM main_shop_stock WHERE shopid=’3′ AND rand().75 how do I guarantee at least 1 row gets selected?

arra7; producto.precio, tipo_habitacion.precio from producto join tipo_habitacion using precio order by producto.precio; gives me error 1064, error in sql syntax

show entire error pls

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘producto.precio, tipo_habitacion.precio from producto join tipo_habitacion using’ at line 1

is there a ’select’ at the beginning of that query?

May I change my text color so we’re easier to tell apart? We’re both typing a lot of questions and I want to make sure we don’t confuse anybody

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘precio order by producto.precio’ at line 1 I forgot the select, but with select it give me the latest error I just gave you

try ‘…using (precio) order by…’

empty set, prices are not the same

that means the query is correct. you have no matching prices.

I don’t have matching prices

agreed

I will rewrite and elaborate my question

Did you see my question? Do I just need to wait? Or do you need me to ask it again? Not trying to be annoying, just want to know if you’re waiting until you’re done with chuy.

you can’t ‘guarantee’ you’ll get a record returned unless your query matches your data. And I have no idea what you’re trying to do with rand()

I have two tables, clientsgone, and currentclients (a hotel app), both tables have the same information, so I need to display the info that I have in both tables, but the table must be ordered by entrance field (datetime field)

Based on what matches the WHERE shopid=’an id number will be here’, it is supposed to have a 75% chance of pulling any of those rows and putting them in the result set.

so if I first display clientsgone, and then currentclients, order is not guaranteed. is this even possible using mysql?

what is the difference in the two tables? client status?

The shop id will always be 1 2 or 3. This table has many rows in it whose shopid’s are 1 2 or 3

arra7, yeah, but I have to have currentclients because that’s where my app fetches the data of the hotel status.

select * from main_shop_stock WHERE shopid=’1′ this will pull 44 rows every time, however, with select * from main_shop_stock WHERE shopid=’1′ AND rand().25 each of those 44 rows still match the search criteria but this only gives them a 25% chance of ending up in the result set.

perhaps you should have one customers table with a unique id for the customer, and a table for visitors, with unique id, date in, date out..

this guy in #qt found the solution (SELECT nombre, precio FROM producto) UNION ALL (SELECT nombre, precio FROM tipo_habitacion) ORDER BY precio;

good news indeed.

maybe evenstar can benefit with the same info?

select * from main_shop_stock WHERE shopid=’3′ will pull only 3 rows. select * from main_shop_stock WHERE shopid=’3′ WHERE rand().75 gives them all a 75% chance of ending up in the result set but since there are only 3 rows that will match the search criteria ending up with 0 rows in the result set is common place. I do not want that. I want to guarantee at least 1 goes into the result set.

I don’t believe that I will be able to assist you. Perhaps someone else.

You can call me Starry

yeah evenstar, but I will not grab your IRC client’s attention if I write starry

My mIRC doesn’t beep or anything when somebody types the nick I am using I don’t have that on I suppose I should tho *goes and messes with it* Oh poopie I can’t remember how to do it. I haven’t done it since like ‘02 o.0 My script was an older mIRC then too Ugh, where did they put it I used to do it through the mIRC options

G’day, I know this is for mysql, but I’m trying to write a validation rule in MS Access that aint worknig

I doubt many people here are familiar with MS Access.

Do you know a channel where people might be familiar?

Maybe if you go to the site that has the online manual for that, maybe you can find a page that lists help chats for it

Probably not on freenode.

ahahahahaha cheers anyway

That’s how I found this chat. By going to the MySQL manual and searching the site for chats. And a page on it told me about this chat.

evenstar, MS normally doesn’t put so much emphasis on community assistance.

I’m not sure if official MS documentation will have IRC links…

Generally it’s OSS that directs people to community IRC chats or whatever.

What’s OSS?

what’s OSS?

lol

ahahahaha

Freaking mind reader!! hehe

You’re the mind reader, you’re just faster at typing ahaha

pfffft! lol

Open-source software.

Aaah

Oh. MS isnt open source I don’t think, right?

ahahahahahha Not in a million years

I hate Microsoft

will MS ever be Open Source

Greedy….word I pry can’t say here o.0

sure you aren’t trolling ?

Do you know of a site in the same vein as searchirc.com?

M$ is a convicted criminal monopolist

Did you know on x-box you have to pay a monthly fee to do the online features on your games? Play station 3 doesn’t charge.

very unlikely they will ever change without major government intervention

PS3 graphics pwn XBox all to heck too x) *hugs her PS3*

Apple has acknowledged open source to a fair extent, interestingly. WebKit is based on KHTML. LGPL, and they opened their source trees.

Hey didn’t Microsoft buy out Macintosh?

evenstar, . . . no.

uh, no

That’s what somebody told me. They have two masters degrees too o.0 what the heck

Alright catchas

bye

see ya

M$ has some a good chunk of Apple Corp. stock, that’s all not even a controlling position

Oh, ok I’m still shocked somebody as knowledgable as her could have that wrong tho o.0 Maybe I misunderstood her. Anyhoo, does anybody that just started talking recently know how to guarantee at least 1 row meeting the criteria of the query gets put in the result set? SELECT * from main_shop_stock WHERE shopid=3′ AND rand().75 I assure you there are rows whose shopid is 3 erm I messed up that code xD SELECT * FROM main_shop_stock WHERE shopid=’3′ AND rand().75 Hey is there any command opposite from the LIMIT command? *e.g. “… LIMIT 5″ Like, oh I don’t know “… AT LEAST 5″ (psudeo code). You know…something that instead of limiting the result set, won’t let it be less than, say, 5?

that doesn’t make sense… what if there are only 3 rows in the table?

Maybe I could phrase it a different way….how about it automatically retrieves 1 random row, and then the rest have a 75% chance of being included?

the RAND() function returns a pseudo-random number between 0 and 1.0. Your queries WHERE clause calls for a select of a particular shopid AND a random number less than .75. If either condition is not met, the record set is empty.

what’s the easiest command to use inject a .sql file into a database?

if both of the conditions are met, then you will get all the rows that match the shopid.

It doesn’t matter

mysql -u root -p dbName backup.sql

It can select them all. I just don’t want it selecting 0.

thanks arra7

This code is going to be run once a day. It’s so what it stocks in the shops on my game will be randomly chosen from the items that can be sold in that shop.

your use of rand() in this way isn’t useful.

But I never want 0 rows to be chosen because then that shop won’t restock at all that day.

select * from table order by rand() limit 1;

what about the fastest way to empty a table? (I’m lost without phpmyadmin…)

NOW())

empty forever?

The Rare Item shop for instance. There are three possible items that it can sell. When it is restocked at night I don’t want it to always get stocked with all three items. I want between 1 and 3 of them to be stocked. Then people will never know for sure if the item they want will be for sale when they go there. (All shops restock once a day)

TRUNCATE TABLE table_name;

thanks well, I’m going refill but there’s some stuff I want out of it

evenstar, see, you should implement supply and demand. None of this “random” silliness.

that removes all rows.

does it remove the tables to? or just rows?

I remember in Neopets how people would just refresh constantly for when a store would restock, and buy the items ridiculously cheap.

supply and demand? o.0

no

how would I get rid of everything (rows and tables)?

DROP TABLE table_name;

It’s because the prices and quantities were fixed, and demand vastly outstripped supply. Quite ridiculous, overall.

I don’t want what’s in stock to change every time you load the shop

thanks

Never mind me, I’m not being entirely serious. Although it would be cool if more games respected basic economics.

Well the users have their own shops they can sell stuff in How hard an item is to get, how desirable what it does is, and how much it costs to get from the main shops influence what it sells for in the user shops. In fact some items can’t even be bought in the main shops. You have to get them other ways. What I don’t like about my shops is every night they restock the EXACT same items into the shops. Blah. Where is the motivation to buy off the players? Into the main shops that is. Players control what is in their shops. By the way this is NOT a virtual pet game. This is a dog breeding game with realistic genetics for how they are built, how they act, and if they have any genetic health problems (e.g. hip dysplasia).

is there a way to have the current datetime to be default for a column?

All the traits are determined polygenetically And they’re all autosomal (sp?), not sex-linked (aka gender-linked) I didn’t bother with any sex-linked traits. ummm possibly try putting now() in for the default and make sure you don’t put any ‘ ‘ quotations around it so it doesn’t think it’s a string

evenstar, but wouldn’t now() be replaced with today’s date? i mean, so that it defaults to the same date whenever you insert?

Hmm…it could o.0

the date when i altered the table hmm

Try it and tell me if it works, lol

ok

When I log the now() datetime I have the PHP script control it (e.g. mysql query(”insert into yada yada yada (`yadayadayada`, `datetime`) VALUES (’yadayada’, now)”) oops that was supposed to be now()

no, no functions allowed for default values, other than for data type timestamp. See the manual.

evenstar, ah, that’s good

Like my sign up form….when it inserts the user information, it puts the datetime in there with it.

it will do what you want to do, but there are a few caveats.

works great for me

arra7, where should i look?

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html

And yay I was useful to somebody xD I think I’m getting better at MySQL. Used to peoples questions made me go o.0 because they were so different from my own experiences. Which were you referring to as having caveats? Making now() a default or using now() with the INSERT INTO statement?

sorry, look here http://dev.mysql.com/doc/refman/5.0/en/date-and-time-type-overview.html

arra7, thanks arra7, i want to know what evenstar asked you above. what do you mean with caveat? if you don’t mind explaining

psilo came back, lol oh, nevermind (he?)’s still dead x) nuuuuuu! lol

Is there any more specific channel concerning MySQL legally? I am a head of another OSS project, and wanted to talk with someone familiar on how to set up the project (copyright-wise for contributors) for later creation of a corporate entity / foundation

NOW()) whats wrong ?

change = to IN

k

NOW() gives 598

Hey, I’m trying to dump a table from version 5 of mysql and insert it back into version 4.0 of mysql, but I’m getting errors on “collate latin1_bin” when I try to import it. Is there a way I can dump it with some kind of version compatibility control?

Affordable php5 hosting.