Yes.

that's the 'characterisic'

well .. adding that worked

Basically the same result, except for that extra 21759 row in solution #2.

yeah.. i see.

i start my mysqld width –log-slow-queries
but i dont know ready a file with logs
in file have not the queries

it's created but it gives an error that dbname.SUM is not a function

you have a space between SUM and ( ?

i changed it now, but i'm not sure
i think not
do I need to add a space?

you should not have one.

okay, can I remove the entry from the mysql.proc table?
(drop function [name] yiels "does not exist"

then it doesn't exist.

hmm I see it in the mysql.proc table

Perhaps you spelled it wrong when issuing drop function.

i've added "_" in the names

wrap it in “ then.

okay
hmm still that SUM error
http://paste-it.net/2888

good evening

there is a space you see
good evening Syntux

take it out.

$^)
okay that works, .. only the result is NULL every time

take out the @ on line 32. That's for something else.

can you run a query and have it output into a csv file?

yes. see the INTO OUTFILE option of the SELECT syntax.

Also, try SELECT … INTO local_var …; /* using the right type for local_var */ return local_var;

!man SELECT

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

roxlus, that is.

thanks snoyes!

so the @ is used as sql host var and not as "procedure" variables?

Right.
you can use @vars in procedures if you want to, but that's not how function arguments get passed around.

does anyone know if innodb deadlock detection is an instantaneous operation, or is it a timed delay at all

okay! well it works now !

innodb's row level deadlock detection is instant
the table level deadlock is times
*timed

can I add a new "inner join" based on a certain field value?

As in, join a table sometimes but not others?

yes

probably not.

ah okay
I can probably solve it be changing the where

I've set up an ssh tunnel with 'ssh -L 3306:localhost:3306 ipaddy of server', but I've read the man pages for ssh, and can't figure out how to use this tunnel to allow mysql-admin to connect to the server on the colocated box. I got the impression

Ooooh, there is Coke in the fridge. What's up with the keg fridge?

from the ssh man page that all I needed to do was point the mysql-admin to localhost with the login credentials for the mysql server on the remote host and I'd be able to log in.

On the "client" computer, where you run mysqladmin, connect to localhost
Yes

snoyes but I can change the "and field = otherfiedl" values based on a if, right?

yes
of course it could get very slow if you do

needs some work… if you have experience you could work on that project

lol. No way! I won't be here to enjoy it

-p -h localhost it works fine.

freight__, make sure you don't try to use unix socket files instead of tcp sockets if you need a tcp connection
use the numerical ip if you want to avoid the file socket

Hrm, that may be it.. let me look.

what would I put at the ?? in http://paste-it.net/2889
line 30

and doesn't mysql supprt ssl anyway?
just so you know, I belive the "inner" keyword is optional

I think change that whole thing to something like AND (r_date_from = s_date_end AND r_date_last = s_date_start OR sp_interval != 'day')

It looks cooler with INNER

that's the spirit

Wow… this is harsh. I have a table with 2.99M rows, and I want to pick a random group of 10 out of there. I'm currently using something like "SELECT * FROM `links` ORDER BY RAND() LIMIT 10", but understandably this is really, really slow.

rand

I've created three indexes, but that doesn't seem to help a whole lot more.

order by rand

http://jan.kneschke.de/projects/mysql/order-by-rand/

Read that

I am ordering by rand?

!tell dan__t about order by rand

dan__t http://jan.kneschke.de/projects/mysql/order-by-rand/

is there a way I can do an update while I am doing a select? like I want it to update some rows but also want it to return those rows

the socket file would be 'tcp'? I just read the manpage for mysql client and for mysql-admin (again), and it does not specify what the actual file name would be.

Ok, I see it now.
Very cool.

mysql -h 127.0.0.1

only if you wrap both statements in a stored procedure.

Run that, and tell us what happens

freight__, no, the socket file is like /var/mysql/socket

Ahah!

ok thanks

rather, /var/run/mysql

it's configurable in any case
I can place it in /dev if I feel like it

Can't connect to MySQL server on '127.0.0.1' (111) I tried a few other things along the way, but kept getting the same response.
This may seem to be a ridiculous question, but I got the impression that /all/ activity on my local system that goes to port 3306 will now be forwarded to 3306 on the remote server, regardless of it's source, yes? For all intents and purposes, port 3306 on localhost is port 3306 on the colo'ed
box.

I can solve it using this: http://paste-it.net/2890

Grr.. breaking my own rule.. about to smoke in the office..

seekwill, very cool, much faster, thanks for the tip.

0

As I don't quite understand the entire query, I'm not sure as to how one would go about returning N results, not just 1.

strange thing is that when I describe my new table it says it allows nulls (YES) and the default is "NULL"

that's possible with an autoincrement, yes
0 is null,

FAIAP?
0 wouldnt that indicate

it could, it could also be a bad value that was adjusted to the column default
depending on sql mode being used

0 I would think that would mean

hello everyone
i have alot of tables, is there a way I can describe all of them, rather than going into each one and typing describe table name;

0 defaults to null in time

tables.sql

or possibly SELECT the data you need from information_schema.

tables.sql; ncim is the name of my database

What's the error message?

tables.sql' at line 1

Remove the space.

mysqldump is a commandline utility

tables.sql"

oops there was no space when i retyped it i put a space

Hrm, netstat on the colo'ed box shows mysqld listening on a unix socket if I'm reading it correctly.. secure unix web hosting 2 [ ACC ] STREAM LISTENING 773119 /var/run/mysqld/mysqld.sock

It's a shell command, not a mysql.exe command.

ooo

for the ssh tunnel to function properly, does it need to be listening on a tcp socket?

thanks guys!
usually 22 is for ssh

freight__ if ssh is not listening on a socket, there's no way you can use it

how can I order my results by the reply which has move chars?

Make sure skip-networking is not in your my.cnf. If so, comment out, restart box
s/box/mysql

s/socket/port/

Funny. That probably be the whole reason why you don't have remote access to begin with… now you won't need the tunnel

http://paste-it.net/2891 is my my.cnf

Normally, if you're using a tunnel, you'll be port forwarding a connection from one host/port X to another host / port Y

when my query returns multiple rows is it still possible to create a function of it?

The socket will have nothing to do with this.

what would, the skip_networking? that was never there, and I've commented out the bind_address
ssh is up and listening, nice as can be.

X can be equal to Y, but that's not required.
What is the symptom?

seekwill, I see how those queries that you had showed me work, however, I'm yet unable to adapt them to return to me 10 results.

Describe the details of the port forwarding you have.

Ok, here's the skinny. I have a colo'ed box running mysql 5.0.4x. I'm trying to get remote clients to be able to log in to the mysql server. the port is open but filtered on the colo system.

SELECT id,link FROM links JOIN (SELECT CEIL(RAND() * (SELECT MAX(id) FROM links)) AS id) AS r2 USING (id);

What port is open?

the tunneling is something someone here suggested. Umm.. 22, 3306 among others, on the server.

and what filtering are you talking about, a firewall filter of some kind?

are you still there?

Yes, the colo host has filtred port 3306 via a firewall rule.

No. No. I didn't ask about all the ports you have open. I'm asking about the specific ports associated with this tunnel.
If you don't want to talk about the tunnel, what do you want to talk about?

3306 only.. the command I'm using is 'ssh -L 3306:localhost:3306 ipaddy of server

Perfect.
So now connections are forwarded to the server from this host that has access.

Ok, so if that's perfect, when I try and run mysql-admin from my local system, I still get the "can't login".. umm the exact return is:.. one sec..
Can't connect to local mysql server through socket '/var/run/mysqld/mysqld.sock' (2)

freight__ is that truly a socket ? "stat /var/run/mysqld/mysqld.sock"
it frequently gets displaced without any warning from your distro

Are you sure the colo allows port forwarding?

stat? I see no 'stat' there. /var/run/mysqld/mysqld.sock
I don't actually know.. there's got to be some way I can access the bleeding mysql server remotely, n'est ce pas?

Also, your main problem is trying to use the local socket. Don't do that. Use an address:port connection.

well have to go, but snoyes really thanks a lot!!!

"stat - display file or file system status"

-p -h address:port'?

mysql –host=localhost –port=3306 …

Kk, will try that.

If you don't do that, mysql will attempt to access the local socket which has nothing to do with the tunnel.

same as before.

I don't believe you.
The socket has nothing to do with this if you issue the proper command (with proper options/arguments)

Now, one of the questions I'm trying to answer, with the command 'ssh -L 3306:localhost:3306 ip_of_server' run on my laptop here will tunnel all activitiy on port 3306 to the colocated system? i.e., if I run mysql-admin from another terminal?

mysql –host=localhost –port=3305 test; try that — it should result in an error.
That's sets up a tunnel so that any connection to your local system will be forwarded to the remote colo server.
Any connection to that local port, that is.

same as all of the others.

You're making a very fundamental mistake, but there's no way to know what that is from here.

Want me to do a screen scrape and post it to pastebin?

You can't possibly get that error.

or paste.it?

Sure.
rafb.net/paste is fine.
When you make a tcp connection directly to a specific port, that fact that there happens to be a unix domain socket or not is immaterial.

Hello. Anybody knows a good tool that can create an E/R kinda diagram from an existing MySQL database?

http://rafb.net/p/pA5nG143.html

TheGoldDIggah E/R ? as in.. Emergency Recovery ?

haha no

or is it possible you mean an Entity Relationship Diagram, also known as an ERD ?
and *only* as that

Entity Relationship
lol

I didn't type a suggestion for you to ignore it completely.

normally its known as E/R diagram

well, no - the literature spells it ERD

and *only* as that

what did I ignore?

Go back to my comment and use the options exactly as typed.

:/var/run/mysqld$ mysql –host=localhost –port=3305 test;
Can't connect to local mysql hosting server through socket '/var/run/mysqld/mysqld.sock' (2)

Sorry. You included more info than expected.

oh boy - I wonder how many times one man can repeat that line
freight__ IS THERE A SOCKET OR NOT ?

which mysql

*verify* this

Just show me that output.

what innodb_flush_method values do people use ?
and does it make much difference ?

/usr/bin/mysq

It's almost as though your options are being ignored. It shouldn't be trying to touch any socket.

life is too short

Ok, I'm a complete moron. how do I verify there's a socket?

freight__ with the command I showed you half an hour ago - stat /path/to/supposed/socket

The only thing I can think of is your /usr/bin/mysql is a shell script that is ignoring –host and –port.

from my local system, or the remote server?
Hrm.. one sec.
:/var/run/mysqld$ file /usr/bin/mysql
/usr/bin/mysql: ELF 32-bit LSB executable, Intel 80386, version 1 (SYSV), for GNU/Linux 2.6.1, dynamically linked (uses shared libs), stripped

2.6.1 ??!?

This has (should have) *NOTHING* to do with your local mysql socket being there or not. It has (should have) *NOTHING* to do with the server hosting having or not listening to a unix domain socket.
Connections to a port do not require any listener on a socket.

like I said - verify if the socket the client is connecting to actually *exists*
or drop the socket idea altogether

why does this not work! http://pastie.caboo.se/79290
omg!

http://rafb.net/p/eGlXEN35.html
so, unless I get the colo host to drop the filter, I'm pretty much screwed?

freight__ you need the *socket*, not any kind of directory

whoops, sorry, wrong paste.

http://pastie.caboo.se/79290

solved! and I dinnet even look

If you read the introduction, you'll see there are two mechanisms available for connections. Unix domain host sockets and direct host:port connections. One does not require the other.

although, strictly speaking, a listening TCP port *is* also known as a socket

If the colo is allowing you to create the tunnel and port forward or allows direct connections to port 3306, you're fine.

http://rafb.net/p/qsh0nr16.html

0 Blocks: 0 IO Block: 4096 socket — so it does exist
now the client settings in my.cnf, btw is this a local client ?

You can also independently run a local mysql server, but it can't be listening on port 3306 if you're using that for the tunnel.

help - syntax error …. i must be doing something really basic wrong: http://pastie.caboo.se/79290

and it can be listening on a secure unix web hosting domain socket file. But that has nothing to do with the tunnel not to any direct port connections.
s/not/nor

iratiku you can keep adding words to your incomplete question, but how about we skip the next 5 steps and you just *ask* it, first ?

eH?

Any command line options should override your configuration files.

what is wrong with my syntax on the paste http://pastie.caboo.se/79290

ah - that's a question
how do you know anythign is wrong with ti ?
*it

the error 1064 that comes back
i thought that was valid syntax .. i just can't see anything wrong with it

error 1064?
!error 1064
hrm

Remoev the s.*

is there anything special needed to upgrade from 5.0.33 to 5.0.45?

You don't want a column list in a delete.

http://pastie.caboo.se/79290

Also, you need some form of JOIN between those tables.
Maybe a comma is allowed too.

it is
but you'll only get an inner join

read the documentation closer. You're making simple mistakes you should have to ask about.
s/should/shouldn't

eh?
http://pastie.caboo.se/79290

and it's not suggested that you mix comma form of joins (which aren't really joins) with JOIN syntax.

Ok, my brain's fried.. time for a break. I'll hammer on this later this evening.. thanks to everyone for their help, and I apologize for any frustration I've caused.. I really am reading the docs, btw. Peace all. See you later tonight.

comma form doesn't use the on (…=…) clauses
comma form uses there where …=… format… i'm not using the comma form

I was commenting on your initial SQL, which had two tables, apparently that you intended to have a comma between.

no .. i missed a join word
its still erroring out ..i've implemented every change everyone has suggested

That's WAS the point.
Remove the ORDER BY.
What version of MySQL is this?

i swear i've been able to do that before
delete the newest record … by using order and limit on delete

We don't swear here, unless people are still using 4.0 or easlier.
earlier
select version();

5.0.22-Debian_0ubuntu6.06-log

Hello. Anybody knows a good tool that can create an E/R kinda diagram from an existing MySQL database?

Yes. delete with order by is supported.

then why did the query only work without the order by clause?

Bug?

of course

I'm not using that version. I can't really test it. If you post the create table statements, someone else could test your SQL.
That's usually a really quick/smart way to get verification.

yeah.. its always an options-
last resort for me
does anybody here want a job in missouri?

You know you've asked a erally smart question in a smart way if the answer comes without any further questions about what you really want or what your environment contains.
You know you've asked a erally smart question in a smart way if the answer comes without any further questions about what you really want or what your environment contains.
Are you running away?

no….. too overloaded with database crap to do
don't have enough time to find answers .. to busy collecting issues

*smile* Thought you were trying to get a replacement.

no.. just exhausted

I see someone read the documentation for you. You must really be tired.

I just created a new set of system tables with mysql_install_db, and I can't log into set the root password, even when I try the commands it gave me.. should I just go through the lost root password process, or is there an easier method

Ok. Still trying to think of a quick way to get N random results from a database hosting of 3M rows.
Anyone have any suggestions?

how can i set the value of one field for all records to 1?

UPDATE row='1'
no WHERE

I don't understand it.. I just reset the root password with the –init-file method, and I still get Access denied errors

with what command ?

uh, mysql -u root -P xxxx
er
mysql -u root -P xxxx -p

and you are surprised that doesn't work ?
mysql cannot *set* a password where there is none
just run mysql

Any luck? Port forwarding worked well in XP to itself. I just forwarded 3316 to 3306 locally. ssh -L 3316:localhost:3306 localhost
Even windows can do it.

tried it without the -p, didn't work either

devicenull look in my.cnf if any options are set there
if they are, remove them
and run mysql

Nothing that should stop me from logging in

how do you know ?

because, it's just stuff that sets directories
datadir, etc

under the [client] header, obviously
not the server, it has nothing to do with this

[client] isn't even there

sigh

fixed it.
ran with –skip-grant and did it that way

i have mysql set up on system running gentoo
from that machine i can connect with the mysql command line interface no problem…
but I can't connect from a remote machine for some reason
is there a setting somewhere that I am unaware of?

yes, there is

why would this query be returnin 29 rows
SELECT * FROM mytable ORDER BY FieldID DESC LIMIT 85 90

remove skip-networking and/or bin-address from my.cnf and restart
*bind-address

where is my.cnf?
n/m

and you're running Gentoo ? in /etc/mysql, of course

I know… dumb question
ok… skip-networking was already commented out
I commented out bind-address
ok… so now it's denying me because my remote system's ip is showing as part of the username and it's denying that then
when i create the user… do I need to a wildcard or something?

yes

just *

user@%

?
hmm… I thougtht ath's what i did
it says if you don't use a hostname it default to %
oh well… ti works if I specify it

hi, im working in mysql promt, is how can i change from a db to another

hey guys
i enabled binary logging to set up replication, but haven't fixed that yet.. however, i have run out of space on quite an important server, and it's all the binary logs. Is there any way to purge them all?
can i just set the master status to off and remove them or something?
it's quite important to me to get this done quickly, so i am asking at the same time as searching for answers using other mediums..

use db_name;

vice-versa, gracias, sabes si es que se puede restringir el acceso con mysqlbrowser?

English

vice-versa, sorry I forgot I was in this channel

np

i can't flush the logs because the service is not running any more, and the only real space i can save is with these logfiles…

vice-versa, thank you, do you know if it is possible to "disallow"(not allow) the access by mysqlquerybrowser ?

put it this way. If i just remove this line from my.cnf 'log-bin=mysql-bin'.. then remove the logfiles, and start the service.. will i break everything, or is it quite safe to do that?

no, authentication isn't client centric

vice-versa, but the admin can do it?

hi. i need help with a trigger please.
mysql 5.0.33
the trigger creates a record in an audit table for every field that is updated.

mysql authentication is client host dependant, depends to how the user is setup

this works in test environment (5.0.37)
in production, if there are multiple fields updated, it will only write the first value. if there is only one field, sometimes it writes it, sometimes not.
also, increment is set up for replication, so it increments in 10s, instead of 1s

Can anyone help me with a query?

are you saying i should check my user permissions?

i don;t know the problem, but if can connect from 1 host but not another cold be the user account client host restriction

That almost certainly is it.
Unless the ISP of the host that can't connect has certain ports blocked both ways or something on that computer is causing trouble.

hey can you timestamp a record on a read?

select now(),* from …

no, it can connect.
that's not the problem.

Does the server say anything?

explain problem again since I don't have earlier message when i just join

no, no errors.
well, i think i resolved it.

Heh.

had a table with a trigger on it.
the trigger only fired once.
upgraded the server to 5.0.45 and it's working.
now.
now checking user permissions to see if i can remove any.

Can anyone help me with a query?

hi

kimseong, do you do any courier imap or pop3 installs?

?

just trying to find a way to track the last time an account was logged into

force an update

how can i cout number of rows that any field is repeating???

select never write anything

count sorry

select now(),* changes the timestamp?

count(distinct colname)
that returns the timestamp, won;t change anything in the table
select is read only

damn

Just add an update query.

kimseong, and in a inner join query where i have multiple rows ??
i need to count how many times col1 has fix value when col2 is changing
any sugest?

hi there, how do i achieve the same as installing a distro's 'mysql-devel' package when compiling from source?

Hey. I have a fairly large and complicated MySQL database running a webapp I'm building. I want to build a diagram of some kind that outlines the structure, but it would take a fairly long time to do by hand. Does anyone know of any tools that will pick up foreign key relationships and the
like and build something useful?

7" to
like formatdate(a.date, MM-DD-YYYY) would be nice

willrjmarshall: do the gui mysql tools from the official mysql site do that yet?

I don't know. I didn't even know there were official GUI tools

how do I log which user is logging into mysql and the results?

!m bahamat general query log

bahamat see http://dev.mysql.com/doc/refman/5.0/en/query-log.html

thanks
how can I turn up the verbosity?

i have a query "select count(val1), …."; how can i order the recordset by row1??
can i define an alias for row1??
or do any as ORDER BY row1
??
can i order a count???
mm, i solved

is there any way I can make mysql tell me more of what is going on?

good evening

generally, at what ammount of rows in a table does mysql become slow?
without optimizations
except from primary key

There's no single answer.

that's not even a question.

It depends on your system, cache and buffer settings, etc.

can someone take a look at my query. http://www.pastebin.ca/622676 i keep getting right syntax to use near '' at line 1

right syntax or wrong syntax

you named a table #__fb_whoisonline ?

wrong
ya the #__ is replaced with a define but oh wait i bet i need to use “ for those

Can someone help me with a query?

i meant generally. at 1 million rows, 2 million rows?
where the selects really get to take 10 seconds

I meant, not possible to answer with the information given.

Hi, I'm trying to build a query with match against, but the columns needs to be qualified because i get an ambiguous error. Can I do this like: …MATCH (`Classified`.`title`,`Classified`.`body`) AGAINST….

I can take a system that runs query X in 20 minutes or (easily) more and change a single configuration parameter to reduce the query time to a few seconds.

it might help if you were to remove all the defines and php code and try the query at the console, then post any error message in their entirety.

yes was just doing that and found my issue i believe, will update soon as i know thanks so much.
yep found the error mistypes a column name hehe silly me, thanks

glad it worked out.

The behavior will also depend on many other factors, including the query itself, available indexes, the various ways you can partition the data,e tc.

do your own benchmark
and there is different definition of slow

Can someone help me with a query?

hey guys im looking for an alternative to phpmyadmin

SQLyog. I love it. Just my two cents.

I dont use half of its features anyway I just want to be able to create tables and check thier content etc

The number of rows might have almost no impact on the result at all if designed carefully.

joe_-, cool ill go look for it in ports
joe_-, thats a windows only thing

No its not
http://www.webyog.com/downloads/betas/MONyog/MONyog-0.16-1.i386.rpm

joe_-, according to its site:
ah

Oh sorry

im reading some forum

Nevermind. That's MONyog.

Why this doesn't work? …WHERE MATCH (`Classified`.`title`,`Classified`.`body`) AGAINST….

joe_-, I cant find anthing at all in freebsd ports

Sorry, I just assumed since you didn't specify an OS

joe_-, im looking for an alternative in freebsd

error message?

Heh, command line?

joe_-, why would I use command line when I have Xorg running?

*shrug*
I do @ work usually.

I normally use phpmyadmin
but I moved over to lighttp for my default www server (this is only a laptop)
and as I work with perl/cgi just got rif of php altogether

Shame.

Can't find FULLTEXT index matching the column list

joe_-, I class it as a sanity advantage

heh

you need a fulltext index to search with the same column

why does innodb seems to take up loads more CPU time than myisam ?

yes, those index separately exists, I can't use them in conjunction

does anyone know how i can make the query logging more verbose so I can troubleshoot my ssl client connection?

you need a combined index

ohhh, I see…thanks

http://pastebin.ca/622611

joe_-: whats the problem

how can I do if I have to perform more than one update and I want to perform all of them or no one? I remember something about autocommit but I don't know exactly… can somebody give me a clue, please?

multi table update if they re related, otherwise use transaction on transactional storage engine

kimseong, what's a multi table update? I'm using InnoDB as engine… and I have some foreign keys for those tables that I want to update

innodb allows transactions, so you can do start transaction; all the updates; commit;

MySQL doesnt throw an error, just never completes. I wind up locking up the server before I get so much as a peep from the query.

joe_-: maybe it is processing

kimseong, ok thanks. And will it be a problem that I am using prepared statements?

joe_-: show processlist

Well, it's taking too long It is processing but it needs to complete inside of, ~1000ms

should be fine
joe_-: try to EXPLAIN and optimise your query

I can only take EXPLAIN so far. I'm not that good at this stuff =/

kimseong, I think I got it. I'm using JSP… do you think that it will work if I set autocommit off, then I do my updates, and then I do the commit?

probably

kimseong, ok thanks a lot

as a test, do a rollback to make sure

kimseong, ok thank you

joe_-: you have 4 table join and a complicated maths operation, you expect to finish fast, better have a good optimised query or indexes

My other one, that doesn't use the similar table finishes in roughly 700ms
Would I just be better off breaking the statement down into chunks and having PHP do all the heavy lifting?

Hi all. I need some help changing the root password for MySQL on OSX (bsd, linux-like), but I'm having some trouble following the online docs. Anyone know 'nix well enough to help out?

Okay how about this one, that math operating returns a multiple rows for a single artist. What if I just want to get the row with the lowest result from that math operation for each individual artist?

mysqladmin: connect to server at 'localhost' failed
'Can't connect to local MySQL server through socket '/opt/local/var/run/mysql5/mysqld.sock' (2)'
'/opt/local/var/run/mysql5/mysqld.sock' exists!

i'm converting an existing MyISAM database to InnoDB and creating foreign keys, however have just come across the following error:
1452 - Cannot add or update a child row: a foreign key constriant fails

joe_-: It can often help to post the create table statements for the tables involved. Also show the other SQL you refered to.
joe_-: and note the number of rows in each table.

Will do.

joe_-: Also post the explain plan for the bad behaving query.

http://pastebin.ca/622730 That's the create tables for the ones in question.
http://pastebin.ca/622731 explain on query

joe_-: cartesian product, all ALL
foreign jey contraint violated
*key

It was working earlier today, then I added some indexes to try and speed it up and then this all happened. I've since removed those indexes in an attempt to see if for some reason that fixes it.

my innodb seems to using up loads of CPU, especially in 'wa' state

wa ?

I presume Im doing loads of transactions to disk

each commit make at least 1 disk write

kimseong, i checked and every foreign key is available in the referenced table

can I not buffer up the disk writes though ?

what statement you are doing? update
Durability reason

joe_-: Why does your SQL use Events and you posted an stEvents table?

is there things to speed up the disk write though ?

there is an innodb option to group the write to once a second, but will loss data in case server crash

I pasted the query from my PHP code, in PHP it uses a global config variable $SQL['Events'], for readability purposes I replaced the table names with something easier to understand.

yes kimseong

joe_-: No problem. Easily adjusted.

If need be, I can of course run the distance calc in PHP. I imagine PHP may be quicker on the up-and-up concerning that, although I'm not sure

the new value of the update cause fk violation

"mysql -ppassword /var/lib/backup/sqlfile.sql.gz" but the file needs to go through gunzip first, and the output of gunzip must be directed to mysql.. .. How do I do this?

joe_-: Why would a date be a text type?

mysql -ppassword `gunzip /var/lib/backup/sqlfile.sql.gz` hope this works

…sad, sad legacy code.
Timestamp was giving me some issues back in the day if I recall correctly.
Then again, that was a good while ago. Although I do plan on addressing and changing that field, it's not the top of my list on priority =/

otherwise gunzip /var/lib/backup/sqlfile.sql.gz | mysql -ppassword

Just curious.

Heh. Yeah I saw that and remembered it myself. Cringed a bit too.

kimseong, That first example made my bash go wild.. Had to put it down before it would bit me..

i am in awe
installed apache2.2 + php5 + mysql5

does it all work?

Call to undefined function mysql_connect()", so i check with phpinfo(), and there's no mysql module

joe_-: Adding the appropriate indexes should make a big difference. Show the explain plan after you add them, if you still have questions.

PHP Warning: PHP Startup: Unable to load dynamic library 'c\php\\ext\\php_mysql.dll'
something is off

Will do.

Havlox reboot if you havent, it wouldnt load the modules on my windows machine until i did that

reboot apache, right? not the computer

joe_-: The explain plan should look more like this: http://rafb.net/p/ZwC6dS60.html

no the whole computer

well thats the one thing i haven't tried

joe_-: Minus the difference in number of rows.

i had that same problem on my laptop

lol. I just removed *all* indexes. Setup only a handful and the full query is finishing in 157ms now.
Is there anyway to make it only return one row, the one with the lowest distance, per artist?

i have tried setting "extension_dir" to cphp\ext, \ext, .\ext,
tried putting the folder in the path environment

joe_-: Sure. Use a form of GROUP BY and JOIN.

but i will restart, its worth the shot

joe_-: http://dev.mysql.com/doc/refman/5.0/en/example-maximum-column-group-row.html

Can you explain? I got lucky with getting these queries to work. I know enough to get in trouble, but not do neat stuff.
nm. mmkay, thanks.

You're welcome.

I'll sift through it and figure it out
Ahh, it's perfect

can anyone recommend a good way to update/display information via the web? PHP? PERL? I've been using PHP to simply display the results of a query, but i'm not terribly impressed thus far…

You have to tell PHP what to do with your data

yea

some people like ruby

its just not very dynamic from what i'm seeing

I made an awesome (if I say so myself) way to show my data just now with the help of Xgc
What?
Seriously?
PHP is as dynamic as *you* make it.

well, i would like to write something where I can type in a Letter and all the records except for those starting with that letter go away, then type another and so on
yea, don't get me wrong, i'm not a great programmer

Well, what you're trying to do is somewhat advanced, but by no means new.

but i have a SH*T TON of data to display and i'm having a hard time displaying it effectively and updating it

What do you need to do with it? What is the display for?

Xajax/php could help you with that

mostly informational

I hate Xajax =/
For that you may just want to use an SQL gui

yea, i have that

ive had good luck with xajax

but i'm hoping to eventually that I won't be the only person updating this data
and that others will be using/updating/etc…

I'd rather make use of the JS class I'm already using for any thing else.

How do I make a column stop being auto_increment?

but Tables aren't very for displaying data in my opinion

Well you have to figure how they're going to edit the data. Do you need a CMS perhaps?

joe_- oh well that makes sense, i didnt want to have to write much js

Mootools or jQuery. Mootools is fast, jQuery a bit more "strong" but learning them has been great. I'm like, 98% PHP but I love those classes.

we basically have about 4000 sites on our network, all with their own IP allocations, and very specific details and I would like to find a way to have a central respository for the data

joe_- you can do database access through jquery?

write now we are using 100's of spreadsheets
its so crappy

Well that's not exactly AJAX and I'd doubt you'd want to.

hey guys, anyone know how i would get the results from two tables in one query?

You need to join them perhaps?

Most everything Im using xajax for is updating things based on queries, i do use jquery for a few other things

of union
or union

How do I make a column stop being auto_increment?

k

What I do is when PHP (the client) needs something updated, it hits up $.post on jQuery which goes to the server and goes to an "AJAX Controller" and it knows what to do.

alter table and modify the column removing the auto_increment

I'm trying to figure out the syntax for that

joe_- cool, do you happen to have an example i can look at?

The request passes through my normal authentication framework, if it fails, it gets rejected, if not it passes onto the AJAX control.
They're at work but I imagine I could explain it fairly easily.

ahh thats ok, i dont want to waste your time

any help?

eep, modules still won't work

Hey all, I can't seem to get sudo mysqld_safe5 to run on OSX, meaning that I can't seem to start mysqld. Any help on this? I installed using Macports.

apache error log still says it wont find the modules in 'c\php\\ext\\php_mysqli.dll'
why does it use double slashes?

No worries. A quick "cheap" example is to do a post to index.php that looks like http://www.yourwebsite.com/index.php?action=ajax&method=updateUserInfo&name=bob in which case index.php
would go "Oh, this needs to goto my AJAX controller" and the AJAX controll would see if it had a method (function) for updateUserInfo and, if so, pass along execution to that function.
It makes AJAX just as dynamic as Xajax and is less "bloated". Big upside is, it's all your own code

cool, that makes sense

hey all do any of you guys use msqlcc

For escaping the slashes. Is the .dll there?

yes, they are all there
in cphp\ext

Try misnaming a dll you know is being included and seeing what happens.

the "extension_dir" is set to "cphp\ext"

Change a working DLL to like your_dll.dll2 and see what it says. Does it have slashes then in the error log?

there are no working dll's, all of the ones i have included (like crypt, bz2, mysql etc) end up with a "The specified procedure could not be found"
in the apache error.log

Wait… in your apache log?
You probably need those in your PHP config.

for now, i have only uncommented extension=php_mysql.dll
in php.ini

What version of PHP?

5.3.2

you put in the right module filename?

I think in that version mysqli should naturally be included.

php on windows provides 3 module dll files depending on the version of apache

I use ADODB anyways =/

im on windows, running apache 2.2.3, mysql 5.0, in the apache config, i am using php5apache2_2.dll

kk… so php is working… it's just some mysql call from php…

damnit, union is difficult

Pastebin your query

yes, php works fine (phpinfo() shows up), but it wont load the extensions

k

Im not great, but I may be able to help.
What OS?

windows xp sp2

Development "sandbox" setup?

indeed

WAMP program?

no, manually installed all of them

http://www.pastebin.ca/622776 — im trying to get all the fields in the query in the result

Ah. I'm a wuss and downloaded WAMP. Works outta the box

e.g. id, login, email, userlevel, vpsid, hostname, ip etc

All on one line?

yeah

i might end up getting wamp, if all else fails

It's quick and easy. I love it.

still learning

Is there anything between the two tables that are similar?

Man here is how i always do it in windows. Install apache 2.2, install php5 and MAKE sure to select 2.2 apxs module. Reboot at this point. Then go install mysql. and it works

joe_- - username field is the same in both.

I download WAMP and hit run

hehe

SELECT * FROM users, vpsservers WHERE users.username=vpssservers.username;

aka "login"
Unknown column 'vpssservers.login' in 'where clause'

What columns do you have in vpsservers?

id,login,hostname,vpsid etc

huh.

nm works now

lol. Mmkay.

vpsservers spelt wrongly

Dont you love that.

haha

phycho do you understand the error message, why people don't read error message nowadays

i did, but couldnt understand where i went wrong
because they both looked similar
6am had something to do with

ok it seems to be working now
apparently, i had a waaay old libmysql.dll in my windows/system32 folder
after replacing, mysql functions are restored
what PITA

i had an operation that did order by RAND(), which is obviously a bad idea
i should fake it above sql shouldn't i?

hello
if i create select x as l from j where xm why it cannot find the l?

if an innodb DB is 100% in memory…. will DELETEs still be slow? I think it would just start using the WAL and no more reads…..

Tell the complete story. What are you expecting and what is the actual behavior?

select *, DATE_ADD(`pd_sales`.`sold_date`,INTERVAL 14 DAY) as "expired_date" from `pd_unit` inner join `pd_sales` on `pd_unit`.`unit_no`=`pd_sales`.`unit_no` where `pd_unit`.`company_code`='01' AND `pd_unit`.`phase_code`='1A' AND `pd_unit`.`project_code`='PAKA' AND `pd_unit`.`sales_flag`='1' AND
`pd_sales`.`active_flag`='A' and `pd_sales`.`sold_date` !="0000-00-00" and `pd_sales`.`snp_date` ="0000-00-00" and expired_date = '2
Above is the query.When i count date sold date plus date_Add 14 days then i want to check if expired date more then current day
it said it cannot found the expired date column

hi

the exact error you get?

Use a HAVING clause to test derived column names or use a derived table.

with jdbc, can i connect to a mysqld that's only listening on a unix socket ?

select id as abc from xint having abc2;
or… SELECT abc FROM (select id as abc from xint) v1 WHERE abc2;

it cannot found the expired_date column.
I have got the solution.It seem when you create a column.It wasn't ready for it until you query it

Derived columns are determines after the WHERE clause expressions are evaluated. The WHERE clause has access to the FROM list only, not the SELECT list.

6) i tried select SUM(finish - start) from table; to try and get the total number of hours, but it gave me a number that was far too large. is there a way to do

My solution are DATE_ADD(`pd_sales`.`sold_date`,INTERVAL 14 DAY) = current_date()

Yes. You can recalculate the expression.

was that recalculate comment mean for me?

can anyone point me in the direction of a ncurses admin package for mysql?

ncurses admin package?
i dont know of any

yea, i got a friend that just wants to run queries and the poor bastard is doing inserts at CL

i only ever use the cli

i'm not sure what you need with ncurses

a cheesy shell

i set my pager as vi using \P vi - and if i'm writing a big query i just write it in vi then execute with mysql -u user –password=pass query.sql

so aka a freindly terminal app that is like phpadmin like thing?

yea, probably.
i am not familiar with mysql, but ive used stuff like that for mysql in the past

No. You could calculate number of seconds from the epoch, then determine the difference and convert that to hours.

when i'm in CL mode i deal with mysql client
aka i dont know a friendly command line interface, imo that would be a hard thing to do

be less difficult than writing one for php
ill pass this info on to him though, thanks for your help

See: UNIX_TIMESTAMP(date)

okay, so SUM(UNIX_TIMESTAMP(finish) - UNIX_TIMESTAMP(start)) — is there a corresponding DATE or TIME function to go back from the timestamp to hours?

FROM_UNIXTIME()

great! thanks

If you wanted hours, you'll want to divide by 3600.

if i use ltrim then i can get it in the correct : separated format
rather than hours as decimals
hrm… cours that will only work if it's less tan a day

How do I make a column stop being auto_increment?

ALTER TABLE

I'm looking at the manual page, don't see it there.
nvm, just found it

Are it better using myisam or innodb for two query ?
or more?
since php lock mutiply query in one statement?

hello
i have a question about pulling mysql results from php.
if there's a space in a value, it doesn't pull the entire value (of varchar())
just the first word untilt he space

whats "ROW_FORMAT=COMPACT" and "ROW_FORMAT=FIXED" and do they apply to InnoDB?

http://dev.mysql.com/doc/refman/5.0/en/data-size.html
http://dev.mysql.com/doc/refman/5.0/en/create-table.html

is there a grouping function that'll return NULL if there is a null value amonst those to be grouped?

CaT[tm]: IF(COUNT(IF(field IS NULL, 1, null))=0, 1, null)
CaT[tm]: IF((COUNT(*)-COUNT(field))0, null, 1)

Anybody doing any data warehousing with mysql?

nope
well at least im not

I'm going to supporting a corporate workgroup in trying to organize all the data they've stored in Excel fles.
files

hello all

ew

I decided on something similar. case count(*) when count(field) then etc - just testing it now.
thanks but. nice to know I';m on the right track

hi, what is the difference between using a varchar vs a text (performance wise)?
i read that varchar is not padded, and presumably text isn't either, it seems to say that text is slower, what in the implementation makes it slower?

Hi, I have a DATETIME field named created, I want to do something like WHERE created = '2007-07-17'
I wanted to get results even if the field has time data
is there a solution other than LIKE '2007-07-17%' ?

!man date and time

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

pretty sure there is a better solution somewhere in there
probably use date_format to strip out the time and then you can just do =

what are the implications in using e.g. varchar(10) vs varchar(500)?

or you could probably do WHERE created = '2007-07-17' AND created '2007-0718' - I think mysql will implicitly add zeros for the time
storage space mostly I think

hmm don't think it'll add zeros

hello people

wow, im getting 25% speed improvement just from changing to use O_DIRECT

how to query for a date between two other dates
kinda what you wrote but with date functions

i think there's a BETWEEN directive, not sure if that helps much

yea, BETWEEN doesn't count the two ends

ok well then what about my date_format suggestion?

what about it ?

try it?

69) where b.id = null, could this cause any possible optimisation/inefficiency problems

don't know about that but you won't get the results you want… "where b.id IS NULL" is the correct way to compare to null

That will be fine.

ah thanks ToeBee, probably saved 30m of frustration somewhere down the track

Just test for NULL correctly.

righto cheers

null is also fine, just for fun.

Moin

moin moin

hi. I have a question about the C api.. if i use 'mysql_use_result', can i call 'mysql_free_result' before i have finished going through all of 'mysql_fetch_row'?..
hmm, no :/
"When using mysql_use_result(), you must execute mysql_fetch_row() until a NULL value is returned, otherwise, the unfetched rows are returned as part of the result set for your next query. "

hmm which collation applies best when using utf8 character set for german texts? utf8_roman_ci, can't really find any info about that in information schema (using 5.0)

can some one help me thing through a query fwith me. http://www.pastebin.ca/622937

I'm trying to do something that I don't really know how to explain.. but I'm doing a query that is returning 2 rows — I'm doing a select from table1 innerjoining table2 where table2.column = table1.column
table2 has 2 entries that match, so I'm getting 2 results back
(table 1 only has one row)
I only need one column value out of table 2, but I want that column info from both rows to appear in 1 row in my query
in my query result
can anyone understand that?

you want to represent two rows of data in one row?

kinda?
ok

you could use group_concat

table1 = a listing of games that have been played
table2 = a list of the teams that played in the game
I want to do a select for games
and inner join the team info
but not have it return 2 results (when 2 teams are associated with the game)
because then if I have something that displays the info, I'll get the same game information for each team that played in the game
get what I'm trying to do?
and the problem?

in my user table each user has a list of friends, what is the best way to store the friends

where can i find documentation for the IN(a,b,c) command? (its hard to search for

right now i have a column for friends and keeps a list of user ids seperated by semicolons

hiho
"Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL", how can i fix the problem? can i somehow delete the query?

possibly create a table of (user.id, friend.id)

Hey
Is there a function in MySQL that tells the connecting IP?
Shows mine if I connect, John Doe's if he connects.

its part of select current_user()

No it's not since it's an anonymous connection.

ah
select user() doesn't work either?
then show processlist;-)

Yeah it did
^^

can anyone help with my question? (stated above)

marc-andre: you can skip it
marc-andre: or you can adjust slave schema to allow execution
marc-andre: or you can add error to ignore list
(userid,friendid)

lol, ok

or userid,sortcolumn,friendid
;-)

thx

i dont think i got what you said

nevermind
some day you'll get it
morning salle

…

possibly create a table of (user.id, friend.id) - ah didnt see this

now i understand what you said :p

Hi, we've got replication working with a master and a slave. now I'd like to have the master db twice on my slave one for backup/recovery and one to run some statistical progs on it - so any ideas/way how to replicate to two databases on one host?

Hi, what is the way to get a copy of the structure of a database without the 'data' ?

create table like
ah, database… mysqldump –no-data

can someone look at this and see if something pops out that could be wrong. http://www.pastebin.ca/623005

Beardbar, not sure if it makes sense, but try to remove the '' , using the values as numbers
and do WHERE ((rank_min = 10) AND (rank_special = 0))

Beardbar, if you have integers, dont use quotes

k testing, left in there where vairables used to be
perfect thanks chap.
noted, thanks mate!

"warning: connect to mysql server localhost: Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock'" but when i do: "mysqladmin –protocol=socket –socket=/var/run/mysqld/mysqld.sock" i can connect to the socket, someone has an idea?

thanks
mysqldum -B mydatabase –no-data, it is the correct way?

Yes, should work.

the last question please , how to import the result to a new mysql database?
the 'dump' to another DB

Can't you guess that?

mydatabase.dump"

mysql dump.sql

thanks alot!

do you have a good reference on jdbc with mysql?

http://dev.mysql.com/doc/refman/5.0/en/connector-j.html

"warning: connect to mysql server localhost: Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock'" but when i do: "mysqladmin –protocol=socket –socket=/var/run/mysqld/mysqld.sock" i can connect to the socket, someone has an idea?

marc-andre: Bad permissions on the file?
ls -l /var/run/mysqld/mysqld.sock
Is the postfix user allowed to use it?

hi all; is there a way around this error: "Warning: mysql_connect() [function.mysql-connect]: Client does not support authentication protocol requested by server; consider upgrading MySQL client" without upgrading the client? i have no control over the client

you can down-grade your password hashes in the mysql.user table
SET PASSWORD FOR user@host = OLD_PASSWORD("supersecret");

or set old password = 1

that only works for new passwords
aehm … it only changes the meaning of the PASSWORD() function to be the same as OLD_PASSWORD()
the hashes in the mysql.user table are still in the new form

which file?

marc-andre: The socket file

SELECT user, hostname, password FROM mysql.user; and check if your password starts with a * or not

the permissions are 777 on the socketfile and the owner is mysql:mysql

d'oh

okay checking now

hi everyone

thank you

If I distribute my product, that uses mysql, and in my product I silently install mySQL, but no more thant that, then use it from my software, and I charge a fee for my software. Does that make my software GPL?

yes it does
The main point of GPL is *distribution*. That's what makes the difference.

So if I didn't distribute mysql with my software, but just write it as a prerequisite it would be fine?

i'm not sure about that - is his product really a derivate work of mysql, just because the installer installs mysql?

Whether your charge for it or not it is irrelevant for GPL
When you distribute GPL product your entire distribution must be GPL-compatible

yes, I understand, but I would want to have the people who buy it not be able to distribute it

Hi guys, I'm trying to setup replication on one of my servers, the server was setup before I took control over it and it does not seem to have a my.cnf. Where would I create one (for RHEL 4)?

ok, thanks, that makes it clear

yeah begin with *

his app links against the libmysqlclient (GPL) and he distributes it, yep

Chrysalis, and how do you silent install it ? could you share that ?

now it is up to you how you handle it. Perhaps you want to create a 'olduser' account for each user with a OLD_PASSWORD()
upgrading the clients to use the 4.1+ protocol is highly prefered

Don't tell the customer what do you install

I don'tk now if that's possible, but it can be non-silent as well

hmm no can't change client

otherwise they can't use Stored Procedures or Prepared statements

marc-andre: sudo -u postfix mysqladmin –protocol=socket –socket=/var/run/mysqld/mysqld.sock
Try if that works

salle, lol
was asking what is the command line to silent install mysql, i have never been able of getting it using the mysql docs at least in windows

i used the set query you gave me

and now all have "supersecret" as password ?
double check, that the hashes are shorter now and don't start with a *

yes, that works

no it is set to my own password (!) and don't start with * anymore; thanks a lot for your help
opme
crap sorry; wrong virtual desktop

Every windows msi can be silently install, however mysql comes as a setup.exe not sure it runs an .msi

ok, ty Chrysalis

check the options of msiexec

hello.. i have my tables encoded in latin1_swedish_ci. when i make a dump with "select into outfile" i get something strange. the result file seems to be multibyte since danish or swedish letters are translated to a couple of weird chars. unfortunately it is not utf-8… any hint?

ty u too weigon, googleing

/quiet /passive /q… are your options

nice, i'm just reading about that

hello

I'm trying to setup replication on one of my servers, the server was setup before I took control over it and it does not seem to have a my.cnf. Where would I create one (for RHEL 4)? I already tried creating one in /etc/my.cnf and it does not seem to take effect on restart of
mysqld

any distro i have tried looks always for /etc/my.cnf
in first place
try to create and invalid /etc/my.cnf
and restart mysql
if it doesn't start.. there is your location

$ mysql –help 2&1 | grep my.cnf
here it is /etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf /etc/my.cnf

http://eugeneciurana.com/pastebin/pastebin.php?show=3214
?

zap-clone: how about printing the error-msg too ?

one second, it's in german, need to recreate it in english again

zap-clone: that's fine

/etc/my.cnf /var/lib/mysql/my.cnf ~/.my.cnf

zap-clone: you have to use ` instead of ' for the field names (if sql_mode is not set to ANSI_QUOTES)

http://eugeneciurana.com/pastebin/pastebin.php?show=3215
hmm, the ' are created by a PreparedStatement Object in Java

zap-clone: the ' in the field-names ?
zap-clone: fieldnames are not preparable

yes
oh, then that is the problem…

what would be the best way to database multiple blogs? create a database/table for each blog or just manage all blogs from one table by blog id

looks like the jdbc prep-stmt emulation didn't took that into account

then I need to figure out a way to handle the field names…hmmm…
thanks

somewhen you want to be able to scale by splitting them into seperate instances

i do not follow. can you explain further

create a database for each blog-instance
you can apply access-privileges on that level and you can move a blog to its own database later if you like
the blogs itself are independent I hope

that would be the most efficient way? If I am hosting say atleast 1000 blogs.

yes. split them

ok

hehe
I'd have one huge table
esp. if going after million blogs

time to merge the en and de wikipedia

hehe

thats 'thousand'
if we'd have millions of wikis, they'd definitely live clustered in biggish tables
if we'd have millions of wikis, they'd definitely live clustered in biggish tables
you don't need big iron for that
you still can split your databases
'mysqldump -w "instanceid in (a,b,c)" | mysql -h anotherbox

damn!

^^ haha

Indeed I could use dammit instead

hi all. Can anyone tell me why the following line fails with ERROR 1064 (42000)?
ALTER TABLE events RENAME COLUMN count TO r_count;

`count`
What is the exact error message?

.oO( why is everyone too shy to print the error-msg they get ? )

there is no RENAME COLUMN syntax

you don't run nude around city, do you?!!?

wanna see pix ?

Use CHANGE

gief!

hi, somebody have try to make a jdbc polling?

hello.. i have my tables encoded in latin1_swedish_ci. when i make a dump with "select into outfile" i get something strange. the result file seems to be multibyte since danish or swedish letters are translated to a couple of weird chars. unfortunately it is not utf-8… any hint?

exact error is:
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 'COLUMN count TO r_count' at line 1

PhoenixAndy COLUMN is a reserved word you could try `COLUMN`

There is no RENAME COLUMN syntax. Use CHANGE COLUMN instead

cheers. That worked
ok, one more problem
UPDATE liberty_content SET primary_attachment_id=( SELECT attachment_id FROM liberty_attachments_map lam INNER JOIN liberty_content lc ON ( lc.content_id=lam.content_id ) WHERE lc.content_id=liberty_content.content_id AND lc.content_type_guid='treasuryitem' );
ERROR 1093 (HY000): You can't specify target table 'liberty_content' for update in FROM clause

Hi. I have a problem with the MySQL Query Browser: It does not show any schemas in the list when connected to my server. The user does not have the rights to execute SHOW databases. Does anyone know how to get the tables of my database there?

hi @all is there a poibility saving the result of a statement like SELECT * FROM x into a variable in a stored procedure ? THX ..

you nearly wrote the correct syntax for it
SELECT … INTO var;

i've tried allready SELECT * from x INTO variable_name it does not work
i get an error that said that only one row can be passed to the variable not the whore result