because MySQL cannot handle four byte UTF-8 characters, is there any way to get it to error when such a character is inserted into the database?
instead of just silently truncating?

harveyd, hehe, thanks for the tip

hey there, can someone please help me out with something?
its somewhat off topic, but i dont know where else to ask

well
it's what we're here to do.

im have to design a program for an assignment, part of this assignment is to make a structured diagram
but im not sure if my logic is correct
http://s192.photobucket.com/albums/z101/goneburger/
the above link is my structured diagram
could you pretty please take a look and tell me what you think?

next time link us directly to the pics, not everyone wants to look at your personal pictures

oh ok, sorry
http://i192.photobucket.com/albums/z101/goneburger/structureddiagram-1.jpg
thats the one
the hexagons are the decisions, circles are loops, and squares are processe's

EXIT

forgot to mention, its not complete as yet
what do you thing Gargantua?
does it look about right?
i mean logic wise?
oh could someone please help? im really stuck, ive got logic block lol
boh could someone please help? im really stuck, ive got logic block lol/b

Hello
anybody of you migrated from mysql to mssql2000

hello
can i ask a question related to phpadmin and inodb?

Rinner no, that would be the fool's choice - use sql host 2005 instead

i am from argentina… please sorry for my bad english

adaptr, i want to connect sql enterprise manager with my sql 2000

sql 2005 comes with a ..slightly better product
that also handles all mssql 200 installations

adapt i see.
adapt, for some reason i am not able to connect to the sql hosting database server, i did not create it as an instance , normal default

can one have multible values for SET in one query?

humm newbie question here.. I have the error : #1040 - Too many connections while trying to connect to phpmyadmin or any of my sites.. what could be the cause of that? My server does not have more than 2000 unique visits per day, so I doubt it'S traffic related… any ideas on what/how to
check??
How can I add more connections to the db??
according to mysql manual I can add more connections to mysql.. how can I restart mysql with more connections??

anyone here uses phpMyDamin with non-english mwsql databases? i have a question

hi guys, I wonder if you wouldn't mind me quizing your collective expert brains?! I am trying to get to the bottom of how wordpress works. See the following diagram, please. The comments table has a column of comment_post_ID INT(10) and posts table has ID BIGINT(19). I know that the
application somehow inserts the correct post id to the comment table, despite them being different data types. Does anyone have any idea h
ow and why this is done? http://img482.imageshack.us/my.php?image=wordpressmodelsj7.gif
ie how and why the data types can/are be different

most likely a mistake made made by the WordPress folks.
hal; The data types to not differ, both are INTEGER, just the *size* of them differs.

it's unlikely you'll have more than 65k comments for a post, or more than 2+ billion blog postings. Nothing wrong with those types.
and the numbers in parens have no effect on storage size, just diplay, and only when used with zerofill.

I think his issue is the "mismatch" of datatype size for the foreign key reference.

they're integers, as you pointed out. No mismatch.

Technically he is correct INT !== BIGINT

like varchar != char? ;^)

to do any comparison MySQL needs to cast INT to a BIGINT. Would be better to have them match.
No, those are much greater in difference.
More like VARCHAR !== VARCHAR2
stabilize your connection please.

[RainMkr], if I only could…

ISP issues?

I gotta mow the lawn before the temp hits 100. later.

I'll quit FreeNode, I guess, I'm only talking on QNet right now

weather kcha

3 AM EDT / 2007.08.11 1353 UTC Wind: from the NE (040 degrees) at 10 MPH (9 KT):0 Visibility: 10 mile(s):0 Sky conditions: clear Dew Point: 68.0 F (20.0 C) Relative Humidity:

Living in the midwest?

[RainMkr], yeah, it seems like it's my ISP

[RainMkr], threnody thank you for the information. I agree that the datatypes should match, even though it is unlikely to cause an issue. In this instance, the engine for both tables is MyISAM. Would mysql allow this if they were InnoDB?

sure. Just about any RDBMS would.

I have a table called `banned_words`. It has only one column, `word` (VARCHAR(32)). I have banned words in it. It works great, but in one situation, I want to do a wildcard, like "fuck*you" or "fuck%you". I am checking the table with the query: "SELECT COUNT(*) FROM `banned_words` WHERE
LOCATE(`word`, 'hello everyone lala blabla i am a nice cat fuck, you and i eat grass')". What can I do to have LOCATE understand the w

[RainMkr], really? Are you sure? Surely it should check that the types match before allowing it?

Well, postgres and oracle allow it.
I don't have windows where, let alone MS SQL Server, so I can't speak to that.

[RainMkr], well, I didn't expect that! OK, thank you. I am not sure I understand the numbers in parenthesis though. What did threnody mean when he said it only effects display? Displaying where?

One index cannot have multiple "kinds", right? Like both PRIMARY AND UNIQUE?

it's kind of inclusive
a primary key is by definition unique

It is not…
(Is it?)
Hrm. You're right.

if the ZEROFILL attribute is given and and the value of 123 is stored in an INT(10), when you issue a SELECT you will get back: 0000000123

I have a table called `banned_words`. It has only one column, `word` (VARCHAR(32)). I have banned words in it. It works great, but in one situation, I want to do a wildcard, like "fuck*you" or "fuck%you". I am checking the table with the query: "SELECT COUNT(*) FROM `banned_words` WHERE
LOCATE(`word`, 'hello everyone lala blabla i am a nice cat fuck, you and i eat grass')". What can I do to have LOCATE understand the
And my ISP is probably going down again any minute.

if it was INT(5) You would get: 00123

[RainMkr], and that is the only instance when it is used?

basically, yes.

Am I still on-line?

[RainMkr], great! Thank you!

REGEXP()

not != ?

dunno

sorry, vice versa.

oops

existed before !=; that said php mysql web hosting support !=

Soon I'm going with a shotgun to my ISP's office.
What the hell are those incompetent idiots doing?
[RainMkr]: Yes, but where? Around `word`?

[RainMkr], ah, so MySQL supports != ?

Sounds silly to support two different syntaxes.

Yes, but I would suggest not using it and sticking with

why not?

[RainMkr]: If you know, please reply before my connection dies again. :/

It's non-standard.

oh

I'd have to look up the regexp() function.

so are there versions of MySQL that don't support it or something?

The computer world is fully of boogy-traps in the form of non-standards syntax.

No, != is not part of the SQL:2003 standard for testing inequality.

I'm confused, but what do I know, you're the proffessionals

[RainMkr], it was interesting what you said about data types and the storage they use up. Is there a chart that you know of that summarises this for all possible mysql data types?

[RainMkr]: Did you reply?
Sorry. Somebody is remotely pulling the plug repeatedly.

I am a nice cat fuck, you and I eat grass.

!m regexp

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

Huh?

that insight into your inner dialogue was very revealing.

And as usual, the manual shows nothing related to what one searched for.

the manual does, the bot doesn't.
http://www.mysql.com/regexp

LOCATE(REGEXP(`word`), … doesn't seem to work.

it's obvious you didn't read the manual.

"23.2. MySQL C API"

[RainMkr], ah, I have found it http://dev.mysql.com/doc/refman/4.1/en/storage-requirements.html

Why the hell doesn't REGEXP work?
And why does the manual not have a single useful example?
And zero info about the function?
….

no one knows!

i'm learning sql and puzzling about the union operator. as i understood it, the union-operation is for joining 2 tables with the same number of attributes and its type equal to each other, right?

Am I supposed to sit here and GUESS what its nonsense error message means?
With no manual to speak of…

….try using the man :p

As I already mentioned, the manual doesn't have any useful info on this.
Or most other things, for that matter.
Seems like this manual was scanned in from old printed docs.

google it?

which mysql hosting function should i be using if i want to find rows in a table where a certain date field is at least 6 months old?

crolle17, yes, except the word "join" is a bit overloaded in sql context
crolle17, think of union in set theory

WHERE datecol NOW() - INTERVAL 6 MONTH

From the looks of it, it's not even POSSIBLE to use REGEXP inside LOCATE(). Is this the case?

gnari, o.k. but in a good db-schema there shouldn't be data in different tables, which are equal (for example table1 for addresses in city1 and table2 for addresses in city2). i suppose these data belong together into the same table?

Hello?

gnari, so i suppose the union operator is quite useless in good db-schema, right?

crolle17, usually not, but remember that the "tables" being unioned may be derived, that is not really tables, but the result of some other queries

hi all,which mysql engine is better for web app? myisam or innodb?

RainChen, depends on whether you care for yur data

gnari, do you have a wise example? (because in google there a only senseless example like the mentioned)
are
examples

crolle17, i admit that i tend to normalize my tables to an anal degree, and seldom need the UNION operator

some articles said that the innodb sucks on count()

gnari, yes that's what i was assuming. so to me there are no case for use.
gnari, is it correctly saying that the use of union-operator is a sign of bad normalized db?

crolle17, but a classic example is historic data. say you have a table with 30 years worth of data, but you normally only want to query the last month. it can be useful to split the table in 2, one large mostly readonly, and one small current with lots of updates. for queries needing
historical data , yu would query on the union of the 2

gnari, ah.didn't know that it is possible to make such split into a part of for updating and another one which is readonly. how to set a part of data readonly?
data
gnari, ah you mean splitting the data into two tables?

crolle17, yes. this is called partitioning

gnari, so having knowingly not normalized data?

crolle17, yes, exactly

gnari, interesting

normalize till it hurts, denormalize till it works

gnari, but then a trigger would be needed for transferring data into the history-table from time to time, right?
gnari, funny saying.

crolle17, or regular maintenance jobs, like automatic first-of-the month cron jobs
crolle17, have to go, be back in 10 …

gnari, bye

I have a C# program that writes values to a MySQL db on a remote machine
the timeout period elapsed prior to obtaining a connection from the tho pool.
it also says all pooled connections were in use and max pool sixe was reached?
I know nothing about any pool

adam300, you need to increase the size of your connection pool. what function call are you using to get a connection ?

I create a new MySQLConnection object
I got the library from MySQL.com

adam300, is there no configuration with that?

no just a connection string
I thought that it was a MySQL error but if it's a C# i need to look at that documentation

reduce the timeouts for mysql connections
that is definitely a dotnet error, yes

alright looking in the object broswer now for connection timeout and connection pool

adam300, i think you can set pool size in the connection string

client pool size, yes, which defaults to 100 AFAIK

so is it POOLSIZE=some big number?

adam300, i know nothing about C#, but are you leaving your connections open a lot?

no I close them right after I am done
that's why I thought it was a MySQL error

adam300, and are you using a lot of concurrent connection?

only 2 per minute max
and it's very small stuff
an insert with like 10 paramiters

gnari dotnet uses objects you create, when the code leaves scope the object is destructed and any connections are closed

adam300, if the default pool size is 100 (and you have not changed that), then i would guess there is a connection leak somewhere in your app

can I paste in here?

gnari so you define an object with all conneciton info, and on initialising an instance, it opens the socket, creates the connection, and logs in to mysql - all at once
NO

adam300, try first explicitly setting a large pool size, and come back if the problem persists

obviously
pasting is what you do in a pastebin
gnari there is little room for luser error *if* dotnet is used correctly…

http://pastebin.com/m5dff8b85
it's pretty simplistic

adam300, are thereother applications using this database host server?

yes, but nothing big
phpbb3 with like 12-14 users
and SVN
with 3-4 users

adam300, is it possible that these other applications are hogging all of mysql's connections?

From the looks of it, it's not even POSSIBLE to use REGEXP inside LOCATE(). Is this the case?

i suppose it is, if for instance they are not closing connections……
Can I see open connections in MySQL on the server host side?
\q
sorry wrong window
weird there is no record of that error in mysqld.log

From the looks of it, it's not even POSSIBLE to use REGEXP inside LOCATE(). Is this the case?

Catnip96_, i cannot be bothered to trowl through the logs, so please re-state your problem for me (clearly)

I am trying to use REGEXP inside LOCATE(), quite simply.
And my connection dies ever 2 minutes because my ISP is incompetent.

Catnip96_, REGEXP returns a boolean. which argument of locate did you want to supply with a boolean?

hello; how can I dump just one column of a table using update querries? Would I use mysql dump? If so, what arguments should i give it?

No, youcan't dump just a single column of a table. Moreover, what would dumping a single column achieve? With only one column, you will have either no primary key, or just a primary key.

i have one table that has good values that i want to apply to another table

gvcoman, select you column into a TEMP TABLE, and dump that
gvcoman, another table in same database?

no, in a different database
it's the same table, just that one has updated values for one column

gvcoman, i would imagine you would need more that one column, in most cases

Why not dump and restore the entire table then?

because i only want that column updated
don't want to risk other columns incase the data is different

gvcoman, how would you know in what row of the destination table each of your columns row should go ?

the ids are the same

gvcoman, the id are in another column, no ?

UPDATE table_name SET col = value WHERE entry = N;
is that possible with mysqldump?
or another utility

gvcoman, what is the type of your column ?

float

gvcoman, select concat('UPDATE dest_table set col=',col,' where id=',id) as upd from source_table;

i am unfamiliar with that syntax
what does that do?
ah, perfect
thank you so much gnari

gvcoman, seriously, what you should do is select the id column, and your datacolumn into a temporary table with 2 columns, dump that, restore it on the other server, and update the destination table using a join with the temporary table
gvcoman, the query i gave you earlier, will just generate update statements. you can then use cut and paste or any other methot to get them to the other database

yes, i ran it and it was exactly what i was looking for
thanks again

ok
gvcoman, there may be problems. for example if there is a row in source_table with no equivalent id in destination, that update will just generate an error
gvcoman, sorry, not an error, but will update 0 rows, so the data will be lost

No, it won't, it will make an update which doesn't update anything

MarkR42, i corrected myself

grari, the id is unique and is a primary key
all rows should have one

gvcoman, if id 42 in destination table has been deleted, for example
gvcoman, it may or may not be a problem for you, i just wanted to mention it

in that case then the update just won't do anything; however all other updates will work, right?

gvcoman, yes

ya, that's ok

guys, is it possible to use two WHERE clause?

'$fid'

Gargantua, no. usually one uses a AND

like that^^ ?

Gargantua, what was the problem?

'3'' at line 1

Gargantua, is id a varchar?

You have some kind of escaping problem. Convert something which should be an int into an int first, ideally use prepared queries if your language / api supports them.

id is an int

Gargantua, a good debugging technique is to have your application language display the sql you make before sending it to the database

O'right, I'll do that.

You can also enable the mysql general query log if you like. But don't do this in production (normally)

Gargantua, it would be much easier for us to spot your problem if you could show us tha actual sql , after $id and $fname has been injected

well

'3'

and don't worry, the ' in the name value is in html

Gargantua, no AND there

pardon?

Gargantua, sorry, misread

I understand, you're probably used to reading queries in caps

Gargantua, ok. first thing is to make sure your ' was quoted properly. can you try again with a $fname that does not contain quotes?

sure
ok, I changed it to the string "fname".
still same error.

Gargantua, what was the error exacly now? (hopefully no mention of Paradise)

never mind guys, it was just a case of repeated code.
thanks for the tips, appericate it.

Hi All. If I have data histories with each data has its created datetime record. How do I group each item with the latest created datetime ?

dinochopins, you bastard

join, ask, and part?

yep

besides, there was no way he can group that way

let me put some thought into the answer, and then i realize he is gone

not without using temporary tables, anyway

select item,max(created) group by item

right, of course

but apart from that, there was no way

I was referring to a recent issue of mine, where I had to group by date
and month
having only a full datetime field to group on

thumbs, you can allways GROUP BY MONTH(datetimecol)

can you?

yes
thumbs, you can grup by any expression

that'd be swell, let me try

hi all
$req = " SELECT profiles.*,user_extra.profilePic FROM profiles,user_extra WHERE profiles.nickname LIKE '%".$s."%' " ;
in my profiles table, I have "nas" and "nasky". if i search "nas", the script finds out "nas" and "nasky" but 6 times each one !
when i list results i have "nasky nasky nasky…. nas nas nas…"
why ?

You are doing a cartesian product instead of a join

has anyone got an idea how to start with selecting the nth row from a table

Tables aren't inherently ordered
So you can't.

MarkR42, uh I never used "JOIN". now it's the time ?

You can only skip *results* of a query.

from an ordered query

I don't know how to use it

You can use "LIMIT" to limit the results of a query, but it happens after the query is evaluated
If you don't join the tables, it multiples them together
with every possible combination of rows
this is a cartesian product
it is usually not what you want

SELECT * FROM table WHERE rownum % 5 = 0 ORDER BY id DESC
but in mysql

MarkR42, could u show me how to do with JOIN pls ?

No, read the docs, or just learn some SQL.

how do you go about creating entries in a new table using data from another? I basically have around 200 records in one table and I want to use one field from them to populate another field in the new table. Any ideas?
it's an ID rather than data

can do a subquery INSERT INTO table VALUES (SELECT * FROM bleh LIMIT 1)
but theres probably a better way

probably

see SELECT INTO in the manual

cheers!

yeh that was the better way

http://dev.mysql.com/doc/refman/4.1/en/ansi-diff-select-into-table.html

better yet, insert … select. http://dev.mysql.com/doc/refman/5.0/en/insert-select.html

is that what you mean?

the mysql version of select into is the insert … select thingee.

ah right, just testing it out now
very cool! Worked right off! Cheers guys

so my was was best
yay

hello
load data infile is ignoring the first row of a csv because the primary key is 0… can anybody offer help¿

anyone know where i can find a cheet sheet for php query quoting rules

you need to escape quotes, not much else to it
mysql_real_escape_string()

im talking when to “ or ' ' or " "

what do you mean by "when to"
do you mean mysql_query(""); or mysql_query('');
because thats irrelevant, it just sends a string, just a matter or constructing a valid string using either method

no if i call a straight query like SELECT * FROM table its all good but if i run SELECT * FROM $table i have to put `$table`

i much prefer using " " because you dont need to escape it to insert variables, and use ' ' to quote columns
" SELECT * FROM $table; " will work fine, replacing $table with its value

you would think but doesnt seem to in php5

"INSERT INTO table(bleh) VALUE('$bleh'); " works fine also
http://www.php.net/manual/en/language.types.string.php#language.types.string.parsing
I didnt know echo "A banana is {$fruits['banana']}."; worked, thats pretty handy

anyway to tell load data to not ignore a 0 key value (im not saving the 0, but i need to operate with it)

are md5 passwords stored as binary or text?
?

SELECT t1.* FROM mytable AS t1 INNER JOIN mytable AS t2 ON t2.id=t1.id GROUP BY t1.id HAVING COUNT(*) % 7 =0;
woohoo

:-)

oki, say I allow users to register, but I require the account to be validated by the admin before they can login. And then the user logs in before its validated. How do I cause PHP to load the page that tells them their account has not yet been activated?

not a mysql question. try ##php

Hey

oops
lol
i thought i was in #php

someone told me a long time ago that the "count" command for mysql is quite slow and uses lots of memory, that I should user "select" instead. Is that true?

Aquaman, usually they are used together

SELECT COUNT(id) FROM users WHERE user = '".$user."'
I got that
but I just remembered what that guy told me about using count
so, is it ok? or should I use any other thing instead?
I need to get the result of the query as fast as I can

Aquaman, if you need the count, use count()
Aquaman, in that query, it seems to me that an index on users should make it quick

for example making the id field an index?

`user`

Hello. I can't find the list of all supported options (that can go into a my cnf file) in the docs.
I'm particularly looking for something to tell the libmysql to bind to a particular interface (source ip).
Any tips?

bind-address will tell the server to binding to an interface
server being mysqld

I need it for the client.
Do you know of a LD_PRELOAD wrapper to do it?

Why does a client need to bind to an interface?

Because this particular machine has many ip's, one for each user, and mysqld is blocking access from the ip that all users are currently sharing for outgoing connections. fun.
So I'd like to use my incoming ip also for outgoing connections, so that mysqld doesn't block me.

Hmm.. Well, I don't know how to do that, but in the mean time you can probably set up an ssh tunnel

(..if ssh supports binding the source ip. Checking.)
yep it does, options -b.
Nice tip.
Thanks.

oh, it's socks
heh

hi
when using fulltext search is it possible to return which column the term was found in?

I don't think so

bummer
have u ever seen in phpmyadmin how it puts a border around the column that match the search?

Have you ever seen the code that powers phpMA?

nope
is that good or bad?

Hi, I need a way to check if a value allready exists, before it is getting inserted again.
or better said if a row exists.

I have already had that problem.

okay?

INSERT IF NOT EXISTS …

ah, perfect, I knew that this is possible
another thing I would like so solve very hard, I find and mega slow, when i do it with php
and multiple selects, would you like to try yourself?

i despise php. sorry.

isnt that what a primary key prevents?

so right now to complete a task, i have a loop inside of a loop (php) to first look up which modules are to be displayed on the main page, and then run a loop within the module class to display the news in the order and format based on what the module row says. can i do this in one mysql
query/
i'm sorry, imMute. what do you use then?

PietroGiovanni, yes, it should, but as I do inserts without a primary key, I cant really choose

i see…

Here is the function which is way to slow:
http://rafb.net/p/JyCmJY40.html
I am importing geoinformations from geonames.org
I allready downloaded the database and tried to import the informations as I needed them
but its ten times faster, when I do the import directly from the geonames.org REST Service
But its still taking 47 hours, so it would be cool, if I could figure out a faster solution.

Perl
what scale does MySQL Administrator display the "data length" at?

what is the difference between max_connections and max_user_connections ?

the former is max over the whole server, the latter is max per user

squitta, wouldnt bulk insert be faster?

Hello. is it safe to replace mysql db with another from a previous install, as a way to restore users ?
Hello. is it safe to replace the mysql db (i'm not talkin about the the rdbms here) with another from a previous install, as a way to restore users ?
also, is there life on #mysql ?

one could do that, and then run mysql-upgrade or whatever it's called (if the old db version was older)

no same version

better would be to recreate the users with new grant statements.
then you won't need mysql-upgrade

they're the same v, but why better to do it manually ?

(last sentence was a reply to your last statemetn)

(last question was a reply to your last statement)

well, you wouldn't have to stop the server

that's no prblm

Sounds like you know what you're doing

i'm its only user
no i dont''

then start by backing up your …/data/mysql directory

that's why im asking
thanks

stop the server, copy over the data/mysql/ dir and contents, and restart the server
I'd use rsync to move over the mysql data files
but start with a backup so you can restore in case of catastrophy

ok thanks a lot centosian

surely

that's very helpful advice

mksm what is a bulk insert?

read on up load data infile in the manual
err, s/on up/up on/

hi, does anyone have a good site that has a collection of example of using mysql command line ?

the mysql docs?

a_meteorite , mysql docs too much for me as a beginner, im looking for a short collection of mysql command

!m cyzie tutorial

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

/usr/bin/mysqladmin -u root -h opensuse.site password 'NewPassword' ?

i mean is it same password as the one set with /usr/bin/mysqladmin -u root password 'NewPassword' ?

sounds daft, but when you specify a length for an integer field it's the number of digits right?

hmm fount it out, those are two different passwords, and changed them after loging in set password = password("thereispassword");

locellcount, i would not bother specifying integer length

if do changes on the grant, do i need to execute the flush priviledges and why ?

okey doke

the_wench, report
the_wench, tutorial
!report

is there a better way to write select * from t1 where t2_id not in (select id from t2); ?
better = more efficient

left join… where field_from_t2 IS NULL
that was for you

thanks
select * from t1 left join t2 where t2_id is null - ?
select * from t1 left join t2 on t1.t2_id = t2.id where t2.id is null ?

fetch rows from the modules table, and then fetch rows from the news table based on what the modules table says. the modules need to be split up in as many different styles as i like with news rows belonging to eacb module as i specify in the modules table. is there a way to do this in one
query?

Hello
I replaced the mysql/mysql database folder with an older one ,but now can't shutdown server because debian-sys-maint can't access the database . The folder's owner and group are mysql, and i had updated debian-sys-maint's password in the mysql.user table .
now i can't restart the server
'Access denied for user 'debian-sys-maint'@'localhost' (using password: YES)

guys, what is MySQLi?

php.net/mysqli

can someone pleasae help ?
'Access denied for user 'debian-sys-maint'@'localhost' (using password: YES)

hello, do you know any software (linux) that is able to draw a diagram of the selected database?
there is DBDesigner, but there are very opensource-type problems with compatibility (can't run it)

SELECT `Tag`.`id`, `Tag`.`name` FROM `tags` AS `Tag` WHERE `id` IN (82, 63, 62, 72) and I would like to know how can I perserve the order in which I am looking for them?
I would like id 82 to be the first one.

order by
desc
^ ^
google order by desc
i may be wrong on th e syntax but its something like order by id DESC;
folks I need help :

stumbler, I don't think that will work for him

why ?

If he wants to preserve the exact order he put them in
Since they aren't perfectly descending

ah
how do I update user info in the mysql.user (or mysql db) from another older mysql db , without touching a specific user ?

….

62 ORDER BY desc won't work

SELECT `Tag`.`id`, `Tag`.`name` FROM `tags` AS `Tag` WHERE `id` IN (82, 63, 62, 72) and I would like to know how can I perserve the order in which I am looking for them?
notice that ^

i don't know

hm this sucks

!man grant

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

there isn't a way is there…

^^

thanks
but i don't remember all the passwords of the old users
that's the thing

you can't, n this context

Yeah Delixe, I was curious and have been looking and can't see a way right away besides sorting it later in whatever language you're using.

yeah there's gotta be a logic to your "sort" so use it in the code

heh yeah looks like it

unless,

Because I have my tags in one table and then their ids in another

you do a compound query

Well, I know nothing about using expressions in the order by

Then I SORT the ids in second table
And want them to come out nicely in the first.
bah

You may be able to find more info and put a expression in the order by.

MySQL has too limited functionality.
It sucks

Well, it's not oracle.

and how can you do that in MSSQl or Oracle ?

no, your issue is a table misdesign

and how can you do that in MSSQl or Oracle ?

he can't
he did a poor design with his database

yeah Thumbs is prolly right. Generally when you can't achieve something, and if it's that important, it means poor design.

Actually my table is designed very well.

then order by another field

i'd like to know the logic behind the order you want
exactly

I have a table that will be updated a lot. I'm thinking about using innodb so I can take advantage of row level locking so multiple people can update different rows. Is this a sane plan? What drawbacks am I not aware of by going with innodb?

It looks like I'll just have to do it code wise, thanks.

Delixe, still there man?

yes.

Delixe, I think I have your solution, but you might not like it.

it if requires table redesign don't bother

Nope…

alright what's up

Delixe, create a temporary table with those 4 values you're trying to select, with each one having a second field that is a "sortby" field of sorts, 1 for the first, 2 for the second, 3 for the third, 4 for the 4th, and then join it with your select above and sort by the temporary table's
sortby field.
Depending on your experience with temporary tables, that might be nasty and new for you. But a good learning experience, heh

It's probably just easier doing it through code

Exactly,

And faster!
yeah it's already 90% coded anyay
lo
lol*
yep done.
lol

lol.

I swear one day I am making my own database software
MySQL sucks balls.

how is mysql not fulfilling your requrements, exactly?

select tm_club_name,tm_club_district, tm_club_url from clubs where tm_club_url is not null order by tm_club_district; - how can I print a subtotal of rows for each tm_club_district ?

It needs a recursive feature, deep searching based on a field.
Something like categories of categories.
Luckily for me, my framework that I code on takes care of it for me.
MySQL needs to be able to do multiple inserts in a single query, and multiple counts

well as far as 1) is concerned, it sounds like a terrile idea where performance is concerned
mysql CAN do multiple inserts in one query.

oh it can?

insert into tbl (f1, f2) VALUES (1, 2), (3, 4), (5, 6)

ah

now, define 'deep' searching

I guess I came to the wrong place. Sorry.

IF you have categories and want to find categories under that category and categories under that category for instance.

huh?

thumbs, I asked a question but was ignored, it seems.

subtotal of rows?

that sounds like you need table normalisation

number of rows returned for each tm_club-district

You mean I need another table saying what categories are attached to exactly what parents? That's absurd legin

select count(id), f1, f2, f3 FROM …. GROUP BY f1, f2, f3

hmm

legin?

lol oops
That's absurd thumbs*

not really. Table normalisation allows for cleaner queries, and more efficient data access, at the expense of insertion time.

Not quite sure that's what I am after.
oh well, forget I asked.

perhaps you can ask your question more clearly, then?

SQL_CALC_FOUND_ROWS might work nelgin?
yeah I agree with thumbs.
thumbs, so for every child, I need a table that lists its parents and its parents parents?

if you design your table properly, a single table can serve that purpose.

Hi, I'm installed mysql 5, its running but I couldn't add users… how I do that? I used mysql -u root

OK, when I run the query, I get a number of rows, ordered by tm_club_district

that sucks thumbs

and then, GRANT command

Hence why MySQL needs to be made better
Yes Neglin, so what do you want?

!man grant

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

^^

I want the number of rows for each district after it has displayed the rows.

I SAW

hrhr

for each district

yes, grouping will do that.

AloBlumenau, read it again then.
de4dpixel, group by

yep grouping will do that

mysql.com/doc, start reading

read my initial response again

Delixe

tizag.com - Pretty good guide.
huh?

nice help

Delixe = Delixe.

I tried it and it didn't appear to work, maybe your response was as clear as my quetion

well you must read the documentation, and tell us EXACTLY what you tried
then, we'll tell you what you did wrong.

You're going to have research GROUP BBY

GRANT ALL PRIVILEGES ON *.* TO 'london'@'localhost' IDENTIFIED BY 'pass' WITH GRANT OPTION

Group by doesn't print all the rows though, right?

nothing happened before I use it

nelgin, the docs tell it all

no, it grouips them.
groups, too

and when I try access using phpmyadmin, londo user, I got an error my pass is wrong

OK, well I must have missed something then.

#1045 - Access denied for user 'london'@'localhost'

AloBlumenau, other users work fine from PMA?

no, there isnt anyone

nelgin, yeah, you missed reading the freaking manuals
AloBlumenau, not even root?

using root I got same error

tibyke, why don't you shut the fuck up?

so what do you think then? do you think at all?

Geez.

nelgin, i would, but you just keep asking rtfm issues.

If I could get it to work, I wouldn't be asking for fukcing help would I?

nelgin, so just start freaking reading before asking

http://www.tizag.com/mysqlTutorial/mysqlgroupby.php

hi, can someone help with just an create table syntax i´ve got the error 1005 ?

!perror 1005
grr

sorry??

pastebin your statement, please

Guest_666, look up error 1005 in the manual, but it must be some sort of syntax error

paste here?

pastebin

try http://pastebin.ca or http://pastebin.mysql-es.org

no in a bin

after you create a user, try to log in using the mysql console

RIght, given that example, it prints one line per "type"

correct.

Hence, I am getting one line per district, not 10 lines

ok

ah

ok tnx

same error

http://pastebin.ca/653891

So, as far as I can tell, group by isn't going to do what I need.

Access denied for user 'london'@'localhost' (using password: YES)

nelgin, why don't you give us an example then.

give us a couple of sample rows, and how you want them arranged
perhaps then we'll understand what you need.

guys, is it really true that mysql 4.1.20 does not have a boolean data type?

OK, here's an example.
select tm_club_district from clubs where tm_club_url is not null order by district
This returns 4967.

hal, it has

5[04thumbs5]1: http://pastebin.ca/653891

how to list mysql users?

do not use colours in here, please

tibyke, that's silly, isn't it?!

hal, what? why?

The first 26 rows contain "01". I want it to print the 26 rows then tell me it printed 26 rows. Then it'll print 58 rows for "02" then I want it to tell me it printed 58 rows etc

sorry

AloBlumenau, select whateer from mysql.user;

ie, I want it to print all the rows for district 01 and then tell me how many rows it printed, then do the same for district 02, then 03, etc.

there should be an way of interpretting 1/0 yes/no true/false all as the same

do you want to print the count on EACH line?

hal, and yes, there is

No, not on each line.

hal, mind the fine manual at mysql.com/doc

and if it output nothhing
?

AloBlumenau, on what query?

ok then. Where exactly?

Something like
01
3 rows
02
2 rows

if you wrote the ouput on a pastebin, this would be MUCH easier.

*sigh*

lol

thumbs. probably.
I'll do it after dinner. bbl

"here, guess what I'm trying to output, and write the query for me"
sure!

tibyke, there is no way of doing it is there?

hal, what? are you in write-only mode, or sometimes you read answers to your questions?

tibyke, does not compute. Not enough data. Please reboot

hal, ok

hi, can someone help with just an create table syntax i´ve got the error 1005 ? http://pastebin.ca/653891

tibyke, well, I feel the boolean functionality could be better implemented, but I guess we have to be grateful for what the lord provides to us, eh?!

Guest_666, you forgot to paste the error msg you get
hal, yes

sorry i´ll update

* Checking for corrupt, not cleanly closed and upgrade needing tables.
what does it means?

AloBlumenau, which part cant you understand?

is it an error?
or just a checking?

no, its not
just read it.

[tibyke]: http://pastebin.ca/653908 there is

I cant login even if root user

I am investigating a database at the moment, and there is a row that contains the following data. Can anyone offer any suggestions on how it may be used? Are the colons or semicolons the delimiters, do you think? a:1:{s:10:"subscriber";b:1;}

I installed kmysqladmin
and same error

AloBlumenau, check the presence of your mysql database files in your datadir

hi guys I have a problem with a corrupt table

hal, its a seraialized php array
Kalavera, good for you

how I do that? I installed mysql from synapitc

Table 'traffic' is marked as crashed and last (automatic?) repair failed

AloBlumenau, contact your system adminstrator

tibyke, thank you

Kalavera, mysql.com/repair

fuck U asshole
are you stupid

tibyke, Repair does not work

why are you here?

I try it a lot of times

Guest_666, ls -l and df -h

AloBlumenau u´r from brazil man?

AloBlumenau, stay cool.

if you don't have something to say, shut up

how did you try to repair it, exactly?

tibyke, thank you very much - I am reading up on that now

tibyke sorry man i don´t get it?

yes I am

REPAIR TABLE table_name;

Guest_666, check permissions and disk space

and mysqlrepair table_name
and mysqlcheck -r table_name

those should work.
do you have a backup?

well it doesn't work
mmmm I don't think so , well is a mysar table

[AloBlumenau]: intaum para de ficar sujando os brazileiro aki cara fica xingando num leva a nada na boa

mysar?

Guest_666, english ffs!
omg, even more morons here than on ircnet

thumbs, yeah! is a log analyzer for squid

[tibyke]: i say to AloBlumenau to stop fighting and saying shit around

mysql based
it has a traffic table and this table does not work

Guest_666, thats right, but stick to english, please

regardless, do you keep backups?

well hold on

Kalavera, you can try myisamchk

[tibyke]: ok sorry and about the permissions and disk space, it´s all ok

yeah I try that first

fuck you too

I paste the result in a pastebin

as patient as we were, I find your attitude counter-productive

[AloBlumenau]: got the message, stick to english, congrags!

http://www.pastebin.ca/653916 - that is

you could try dropping the index

no
I don't know if this table has an index

the error message suggest it

http://www.pastebin.ca/653921
yeah It has indexes
how to drop it ? and what index I need to drop?

um, I am not sure.

tibyke, thanks for the pointer on serialized arrays - I am more clued up on it now, as there is plenty of info in the php manual. Appreciated

http://www.pastebin.ca/653924
this is an explain for this table

hal, yw

any idea?

not really. Sorry.
perhaps someone else can lend a hand?

no problem thumbs thankyou at all
XD maybe, I think to drop all table and redo

well, do you want to salvage the data?
if not, simply drop the db

This DB only has access_log information from squid
and I see the problem came from two months ago
Yeahh drop all database is an option in this case, is not critical

drop database mysar;

Query OK, 6 rows affected (2.92 sec)
mysql
that's it thank you

great
in the future, please make backups.

TNHX eveybody

yeah , usually i do that but this is not a case I learn about it

you can usually repair most tables
but nothing is better than solid backups.

Hi guys

Hello

I need some help to solve a problem with mysql

Go on…

googd

hi henaro
mysql, mysqladmin, and so on a message appears
Floating point exception
what does it means?

I'm not too sure . _ .

what dist? what mysql?

ok
Rhel 5 + MySQL 5

from rpm?

yes

rpm -qa | grep -i mysql | xargs rpm -V

UPS!
Unsatisfied dependencies for MySQL-client-community-5.0.45-0.rhel5.i386: libc.so.6(GLIBC_2.4), rtld(GNU_HASH)

hrhr
you are missing something, or you forced/nodeps-ed an install/upgrade

and now?

it is not a mysql issue

I seem to be getting an error when connecting to MySQL ._.

it's a red hat issue
install the mysql packages properly.

ok thanks

henaro, fascinating

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

henaro, very nice!

!perror 2002
make sure mysqld is running first

I do

I want to replace values in MySQL, e.g. 5 to 10, 10 to 4, 4 to 5, etc. How to do it?

mysql.com/update

I can't make 5 UPDATE queries.

CASE
!man case

see http://dev.mysql.com/doc/refman/5.0/en/case-statement.html

\o/

So has anyone encountered that error before? OR know how to make it not error?

But if MySQL update 5 to 10, won't it update 10 to 5 again?

google for it, you'll get 1000s of hits
which os?

Ubuntu 7.04

ps -aux | grep mysql

I googled and tried everything I saw

is it running?

*sigh*

Yeah
0 grep

that's your grep command

henaro, damnit, your mysqld is not running

not the server

omfg

Well it won't start

mysqld –console –user=mysql

then why the hell you say its running?

it does not update over and over again - only once

8 [ERROR] Do you already have another mysqld server running on port: 3306
Because of that

so kill all mysqld processes first (if you cant stop the server)

it checks the current value in the field and returns another value. then it moves in to the next row

okay

killall mysqld_multi; killall mysqld

however, don't i need stored procedure?

no process killed

My script has to be capable with SQLite - i wonder if it has such syntax.

henaro, fuser -n tcp 3306

hey, if i create a column '`id` INT( 255 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,' what is the biggest id ?

Didn't do anything . _ .

henaro, then just start the bitch with the ubuntu init script

what's that fuser supposed to return?

2147483647

thanks

flupps|US, pid of process using tcp port 3306

* Starting MySQL database server mysqld [fail]
D:

Hey all.

Hello sir

flupps@piglet:~$ fuser -n tcp 3306
3306

UPDATE albums_serialized SET other_discs='' WHERE blz_id IN ('a', 'b', 'c');
or:
UPDATE albums_serialized SET other_discs='' WHERE blz_id IN ('a'), ('b'), ('c');
Or are neither of those correct?

eyeRmonkey, php.net/update
i mean mysql.com/update, read it

php.net?

Hehe.

SettlerX, yes

oops
flupps|US, prolly you have an older version of fuser

i can't find any information on how to use "IN" with static values (as opposed to using a subquery).

eyeRmonkey, the one that does not generate a syntax error is the correct one

Hey
is there any way to do something along the lines of this (in PHP) SELECT * FROM foo WHERE strtolower(bar) = 'baz'?

http://dev.mysql.com/doc/refman/5.0/en/case-sensitivity.html

Thanks.

lower
eyeRmonkey, if you are still looking for it, http://dev.mysql.com/doc/refman/5.1/en/comparison-operators.html#function_in

thanks. i still was.
UPDATE albums_serialized SET other_discs=NULL WHERE blz_id IN ('0-20501','00-01');

is it possible to specify distinct in a join? in order to help mysql know where the redundancy is going to occur

johnjosephbachir, probably not the way you think

how could I get my db from a drive.. i cant chroot and use mysqldump .. ?

a what from what?

i managed to boot up in a live cd and mount my root where my db is.. seems the fs is messed up and wont boot and i dont know how to fix it.. i just need to get the db

recover from daily backup

:-/

stealth, what os, distribution ?

Gentoo Linux

stealth, do you think the data directories are ok ?

one day i tried to access my server and I got nothin but input/ouput error.. all i could do was hard reboot with a livecd.. i managed to use testdisk and find a superblock and rebuild the fs.. it mounts now and everything looks to be there.. (it wont boot though, keeps saying fs has errors
and runs fsck)

does your livecd have mysql installed?

yea

stealth, and you cannot chroot to the mounted disk?

im trying.. used a old dsl cd was layin around and was 2.4 kernel and said was too old.. got a new knoppix booting now..

if the mysql installed in your livecd is same version as you had on your hd, you might try to use that one, but the version probably needs to be exactly the same
stealth, best solution is to try to get chroot to work, and use your original mysqldump to dump the database

yea i know eee

stealth, another option is to install same gentoo on another disk, and after mysql install symlink your original datadirectories into /var/lib/mysql or wherever gentoo puts it
stealth, problem with gentoo, is that it is difficult to get the exact same setup, due to all the build options

i should have my make.conf somewhere.. i do have everything backed up (somewhat) .. just faster to get it off the disk
gnari, i got in the chroot, but mysql cant run in the chroot… i get the .sock error
from mysqldump

stealth, did you start mysqld ?

yea in the chroot.. /etc/init.d/mysql start but i get .. /sbin/functions.sh: line 286: /dev/stderr: No such file or directory

stealth, does not sound good
stealth, sorry, this has gone out of my expertise. you need a real mysql hacker

theres no file to just pull it off of?
oohh..i found my all_databases.sql ahh geeze wheww… mainly from here i suppose its just learning would love to know how far the system is really screwed .. i did learn quite a bit about ext3 and recovery… suppose ill start using my old tape drive more now

well the data directories (/var/lib/mysql on my non-gentoo machine) could be copied into equivalent directory of a working mysql setup, but you would have to make sure the build options are as similar as possible, and the same mysql version of course

ah ill copy that, imma rebulid the machine with another hard drive and the same make.conf…
i still dont get what happend.. the drive seems fine

stealth, ah well, shit happens.

yea indeed

hi guys
just little confirmation before going foward
we can JOIN between two databases rights ?
database is just a directory with tables it doesn't slow down the server ?
it's like joining two table from the same DB right ?

Hiya! I'm using MySQL and I have an images table, a user table, and an image_votes table. I'm trying to say "Give me an image from the images table, but only if the user hasn't yet voted on it"
I'm not sure how to phrase that.
the image_votes table has user_id and image_id columns, and the vote column.
would any nice soul know how to accomplish that?

That just requires a SELECT images left join image_votes ON … WHERE image_votes.primary_key IS NULL …

"SELECT images.* FROM images LEFT OUTER JOIN image_votes ON image_votes.image_id = images.id WHERE images.parent_id IS NULL AND (image_votes.user_id IS NULL OR image_votes.user_id != #{authed_user.id})
is what I came up with

I've left out the SELECT list and the FROM keyword.

it seems to work.. but I'm not fully sure I totally understand i t

Ooops. I didn't leave that out.
That's correct.

Cool. I'm not sure exactly why I need to specify: image_votes.user_id IS NULL OR
image_votes.user_id != any_integer_here should return true if user_id is NULL right?

You can simplify your solution.
I don't know what images.parent_is is null means or why that would be needed for this.
You want to move any image_votes tests into the ON clause.

well, that's an implementation specific thing.. if parent_id is NULL it means that image is not a thumbnail
which is the only kind of image I want to vote on
ugly I know. :/

hi,all. how do i get the length of a string?
len / strlen seems not working

Are you checking only for one user?

DavidLeon, every tried mysql.com/doc?

Thank you for your help.. I have to go, sorry.
But yes, only one user

string functions

Just trying to say "return the images the user has not voted on yet"

SELECT images.* FROM images LEFT OUTER JOIN image_votes ON image_votes.image_id = images.id AND image_votes.user_id = 'thisuser' WHERE image_votes.user_id IS NULL;

oh thx

That'll find only images that haven't been voted on by this user.
Add the parent_id logic as well to handle the thumbnails.
SELECT images.* FROM images LEFT OUTER JOIN image_votes ON image_votes.image_id = images.id AND image_votes.user_id = 'thisuser' WHERE image_votes.user_id IS NULL AND images.parent_id IS NULL;

could numbers implicitly be cast to any other types?

yes

where does mysql saves the user&password?

in $DATADIR/mysql
bofh

8-)

hey guys, i have a mysql dump file and im trying to add an id field as the primary key to it, but all the entries are seperated by just a comma so when i add a field i get an error of column count

derek, add it in the db then
derekj212, i mean

what do you mean

If you can generate the dump with an insert field list for each insert, you can add the column without that problem.

i only have the dump, i dont have access to the orignal db

Your problem is without the field list the values list much contain all the fields ni the table, which you are changing.
That's why it fails.

you can redump, or use an awk/perl/sed oneliner

yeah i get that, i just dont know what to do to fix it

Then import the data and alter to add teh column later.
Just add the ALTER TABLE statement to the end of the dump file.

if i add an id column later can i get it to become the primary key, autoincrement and populate it?

Yes.

how can i do that?
i already have a db loaded with the data

alter table your_table add id int auto_increment primary key;

and that will populate the id?

Yes.

oh wow way easier than i thought

So you just need to add that to the end of your dump file, if you're still asking the original question.

can i just alter the table if i already dumped it without the id?

You can add the alter to the dump file or perform the alter manually after importing.
The moment you alter the table in the DB, the dump file is out of date.

the dump file is just one that has ZIP code info i downloaded, so im not worried about it

If we're ignoring the dump file, yes, you can always just add a new primary key with the above form.
Assuming you don't have a primary key for this table.
If you have one, you'd have to drop the key first.

right now the primary is the zip but its a string which is screwing things up
so i needed to either switch it to an integer and save the data or just add a new column to use

ALTER TABLE to drop the primary key (not the string column, just the key) and then alter table to add the new primary key.

but the string column IS the key i think

If you want to toss the old key AND column, you can just drop the column.
The key and the column are separate constructs.

oh ok, no i cant drop the whole column
but ill drop the key
then add the id field as primary key

Right. That was my point.

yeah just making sure i got it right
so i need to specify primary key, autoincrement and anything else
?

Just as I typed it.

oh ok awesome thanks

Whether you use a signed int or unsigned int is up to you.

its just going to be 1….n
so unsigned would prob make more sense

alter table your_table add id int unsigned auto_increment primary key;

ok ill try it now

Perfect! That worked, thank you

thanks it works

Xgc's just helping everybody.

Lucky guess.

Xgc is an AI, a local instance of Yahweh, hand coded by William Gibson and Neal Stephenson…

i'd like to return the minutes/hours days since a timestamp from now() using sql, can anyone point me to some help?
really just so people can see how long it's been since a post has been submitted.
no?
just ping me if you want to help.

have a table with 3+ fields, addr1, addr2 and timestamp, want to select rows based on distinct addr1 with the most recent timestamp, what is the simplest way to do this with a select statement?

maybe the TO_DAYS will help

znoG im trying timestampdiff; in a sec

this is not a mysql specific question but more of a general development question so its a bit OT We use SVN for its intended use, but I always have an issue with the DB part of things. If I change the DB structure, I have to let all the other developers know and they made have made changes
themselves which makes syncing a bit of a nightmare. What do you guys use?
ideally we should share the same DB, but using port forward of 3306 (through SSH) makes things a bit slow

is it slow cause it's saturday night?

figured it out, select distinct(addr1),addr2,max(timestamp) from table group by addr1

maybe? select distinct(addr1) from table orderby timestamp asc
you might be adding superflous functions, or im mis understanding what you're doing.

arg..could someone explain the difference to me for these two queries:
SELECT images.* FROM images LEFT OUTER JOIN image_votes ON image_votes.image_id = images.id AND image_votes.user_id = 1 WHERE image_votes.user_id IS NULL AND images.parent_id IS NULL AND image_votes.skip IS NULL AND (image_votes.ip IS NULL OR image_votes.ip != 416841577) LIMIT 0, 1;
SELECT * FROM image_votes WHERE image_votes.ip IS NULL OR image_votes.ip != 416841577;
the first one returns an image, the second one does not
however, they both should not return an image.
it's something weird about that "image_votes.ip IS NULL OR"
if I have in the above query, the above query fails to work.

The difference is the LEFT JOIN (which is an outer join).

Right.. I was trying to isolate the SQL
the behavior I'm seeing is the difference between
SELECT images.* FROM images LEFT OUTER JOIN image_votes ON image_votes.image_id = images.id AND image_votes.user_id = 1 WHERE image_votes.user_id IS NULL AND images.parent_id IS NULL AND image_votes.skip IS NULL AND (image_votes.ip IS NULL OR image_votes.ip != 416841577) LIMIT 0, 1;
and
SELECT images.* FROM images LEFT OUTER JOIN image_votes ON image_votes.image_id = images.id AND image_votes.user_id = 1 WHERE image_votes.user_id IS NULL AND images.parent_id IS NULL AND image_votes.skip IS NULL AND (image_votes.ip != 416841577) LIMIT 0, 1;
is different.
even though "ip" is not null
(the only thing that's changed is at the very end.. I removed "image_votes.ip IS NULL OR"
okay… Now I can't even reference "image_votes.ip" in any way and have the query succeed. Gah.
okay
moving it into the "ON" clause appears to have fixed it
so weird..
Ohhh!
I can't reference anything about the image_votes in the WHERE clause
well..no..
not sure what goes into the ON clause then. Hrm.

Criteria in the WHERE clause and criteria in the ON clause of an outer join behaves much differently.

yes.. I'm trying to learn now.

That's the point of the outer join.
Failure of the test in the ON clause does not remove any rows of the left table frm the result. Failure of the same test in the WHERE clause removes the corresponding left table rows from the result set.

I see
what does failure of the ON clause do?

For an outer join the left table rows remain and the right table row where a match could not be found, the columns return null.
That's for a LEFT OUTER JOIN.

okay
I'm getting the feeling that whever I test in the ON clause needs to exist in both tables
is that correct?
or..

No. That's the point of the left outer join. If there are matching rows in both tables, there's no need for an outer join.

matching columns?

LEFT OUTER JOIN is used when you want all the rows of the left table plus rows of the right table (where matches are found) and null otherwise.

but if I'm doing SELECT images.* FROM images…. won't only images.* be returned?

When I talk about the result of the JOIN, I'm not atrlking about what you have in the select list. That's immaterial.

Oh
so internally it returns both the left and the right table columns together.

A JOIN produces a table. You can select whatever you want from that resulting table.