gday i did an rsync of a whole box for xen but when i try to run queries on some tables on the new box supposedly
hi
hi
I need to log the select statements to a specific database (this is temporary for debugging only)
what options do I have?
I need t do it at mysql level, the app is not capable of such tracing
i am not sure if you can log these to db.. but you can make mysql log all queries to logfile
yes yes
to a logfile
but the logger logs only update/insert statements, doesn't it?
I need the selects
donno.. i always thought all queries are written, inserts or selects never mind… hm
slow queries are definitely logging selects
scratch that, you are right
I got confused with binlog
which indeed does not log selects
my bad
well yeah, binlog is about changes to db, while query log is for debug..
any idea how i can group query result by quarter-hours?
That could mean several things. Here's one: GROUP BY ceil(some_minutes/15)
hi
Anyone who could help me out with something propably really simple or everyone idiling?
(=sleeping)
ask
"I have a question", Don't ask: "Is anyone around?" or "Can anyone help?". Just Ask The Question
how can i say in mysql, that i need those records whose ids are not in a table's column?
okay, I create a table mytable with ID field that is INT(10) NOT NULL AUTO_INCREMENT and first field is then 1, I need to set first field to 0
I've set AUTO_INCREMENT = 0 on my query but that didn't help.
Listen to the_wench …
a not in b
SELECT a.* FROM a LEFT JOIN b ON a.id = b.id WHERE b.id IS NULL;
thx
You're welcome.
CREATE TABLE mytable(ID INT(10) NOT NULL AUTO_INCREMENT, PRIMARY KEY(ID), vdata TINYINT NOT NULL DEFAULT '1', fdata VARCHAR(250) NOT NULL DEFAULT '') AUTO_INCREMENT = 0
whats the query that tells you how many rows were found by a select statement, regardless of the LIMIT imposed on that select
I read from somwehre that AUTO_INCREMENT = value would set auto incremency to start from specific value.
able to help me ?
http://dev.mysql.com/doc/refman/4.1/en/limit-optimization.html
Talking to the bot?
That's correct. Do you have a problem with auto_increment?
You didn't actually ask a question.
g'day. i did an rsync of a whole box (for xen), but when i try to run queries on some tables on the new box (supposedly exact image of the old one), i get this error: "ERROR 1033 (HY000): Incorrect information in file: './rt3/Users.frm'". this Users.frm file has exactly the same md5sum as the
original. why might this happen?
you mysqldumped it?
.. no
i did an rsync on the original box while in single user mode (obviously i was not running mysqld or any other services)
Seems odd.
some tables seem fine too, others not
select * from tbl limit 10; and select * from tbl order by id limit 10; gives me two different results, is this a normal behavior?
yes
how can it be?
hm
why do you expect otherwise?
fritz[]: if you don't specify an order the order is undetermined
well, I would expect same result, but in second case that's ordered by ID … I'm getting totaly different results (another rows, not order problem)
fritz[]: the order is performed before the limit
can you take a look? pastebin.ca/637002
fritz[]: you should always specify an order
I see
.. especially if you are using limit
yes. auto increment starts from 1 even that I have set auto_increment = 1
That's correct behavior, don't you think?
yes. auto increment starts from 1 even that I have set auto_increment = 0
0 is not valid.
sorry, auto_increment should be set to 0.
I need it to start from 0.
Not possible.
Use a trigger.
trigger?
MySQL 5.0+ has triggers. Read the auto_increment docs to find out why 0 is not valid. 0 has special meaning, much like null.
nah, I'll just do INSERT INTO mytable (ID, vdata, fdata) VALUES('0','0','location)
Won't work if the field is auto_increment.
Okay, ALTER_TABLE ?
You won't be able to use 0 with auto_increment.
whats the mysql function for field value length?
It doesn't mean anything if there are ID's missng for e.g. ID1 missing, but first entry _must_ have ID=0
length() and char_length()
ah yes
char_length is what i want ay
can I do that? Change it with ALTER TABLE without running to troubles later?
Don't use auto_increment if you care about the specific values.
Yes. That will cause trouble later.
:/
Just try to copy data between two tales or dump and import that table.
s/tales/tables
The 0 will cause you great pain.
Why?
I jst explained it. NEVER use auto_increment (or sequence in oracle) if you care about the specific values.
It is a PHP software that I've been working with last 6 months, fields can be added and removed, except that first field that should always have ID 0.
Every chance the engine gets, it'll try to change the 0 to the next non-0 auto_increment value.
If I'd need to not use 0 in that, I'd need to write this thing from the beginning again.
Don't use 0 with auto_increment and more generally, don't use auto_increment if you care about the specific values being assigned.
I _need_ to use auto_increment because these fields can be removed and added but same ID number should never be used again.
This is fundamental. If you assumed otherwise, you've made a basic mistake.
This is not correctable, unless you remove the 0 or remove the auto_increment behavior.
When I started this project, I was planning to use my own database system designed for this purpose only, then I heard that I shouldn't as mysql for e.g. supports everything I ever could dream, so I'll study how to use it and now I hear that it won't
support most basic thing?
That's like saying "I need 1 to act like 2". It just won't happen, no matter how much you designed your application around 1 being 2..
If I remove the 0, my software no longer works as new ID number isn't being generated by automaticly and it doesn't keep record in anywhere about already used ID numbers.
If I remove the auto_increment, my software no longer works as new ID number isn't being generated by automaticly and it doesn't keep record in anywhere about already used ID numbers.
You aren't making sense. What possible harm could come from removing your use of 0 as a key value?
Programs security features break down totally.
What? You depend on a specific key value of 0?
YES
that's a huge mistake in any database.
Fundamental mistake.
never ever depend on having some specific value assigned when using automatically assigned keys.
That's why there was auto_increment = 0
Never ever expect them to be sequential. Never ever expect them to be always increasing.
The only thing you can depend on is that they are unique.
why? what kind of auto_increment is that then?
That's it.
Period.
That's enough, 2 doesn't have to come after 1, they just must be unique.
if 1 has been used and then deleted, it should not be used ever again.
There are all sorts of ways any of those assumptions can break when using transactions or in other cases.
Xgc is this possible?: SELECT * FROM users WHERE username='$username' OR email='$email' ?
Sure, but you should guard against injection problems.
yes it is.
Xgc trust me it is protected against sql injections with php webhosting :p
Look into prepared statements.
so why doesn't it say this about auto_increment on mysql.org where it tells about auto_increment?
If I'd knewn this thing I wouldn't need to rebuild my program from beginning?
but it's just that it reads in some secret location!
or only people who read mysql's source happen to know this..
and when this comes to programming, NULL = nothing 0=value of 0
so you can't even picture this thing as mysql does something that isn't quite standard, suddenly 0 equals to NULL?
what is the idea behind that?
It's in the notes following the main docs section. it should be explicit in the main section.
notes?
who reads notes when there is a manual?
All online documentation has a notes section where people can add information.
Xgc how do I lock into prepared statements?
Look?
*Look
well what are prepared statements?
php has prepared statement support for MySQL.
It's a way to bind variables to a statement, so that the data is transfered without the need for escaping special characters.
anyone know how well mysql hosting handles trigonometry functions over a innodb table?
you know where I can read documentation about it?:p
oh, with 5 million records?
is it supposed to be in section navigation part possibly?
http://dev.mysql.com/doc/refman/5.1/en/index.html
Lots of information: http://www.petefreitag.com/item/356.cfm
http://dev.mysql.com/tech-resources/articles/4.1/prepared-statements.html
thanks :p
You're welcome.
Xgc so I have to remake my mysql class for it?:p
It's something you might want to learn for another time.
yeh thanks :p
but atm I'm checking every input on bad characters :p
That's error prone, but if you're careful (perfect), you'll be fine.
error prone?
http://dev.mysql.com/doc/refman/5.1/en/example-auto-increment.html
check comments, poset by gary affonso on march 29 2005 5:46pm
poset=posted
okay it reads in comments and I didn't read that part as usually comments is part where people speak about problems they have ran into..
anyone used the spatial extension?
okay, time to start doing the whole program from beginning then because mysql uses something that is non-standard, now Xgc, tell me this one thing, if I set auto_increment = 1 even though that it defaults to 1, is the first entry's ID _always_ 1 then?
Maybe I can help you with a solution. You haven't told me why you think you need to use 0.
it's a long story. You know forum system called SMF?
Listen closely. You should NEVER EVER exact any specific auto_increment number to be assigned. Just stop trying to do that. If you need specific values, assign them yourself.
s/exact/expect
yeah, thanks for help, that doesn't really help, if I'd need to set these values manually I'd need to create another table holding "used" values..
All you know is they will be the current auto_increment value for the table (or larger).
Even that probably isn't guaranteed.
But why on earth do you need a specific value?
That's your main problem.
because this specific value is used when processing data from another tables and also from files.
Why do you care which value is assigned? Just let it be assigned and if you need to know what it was, use SELECT last_insert_id(); per connection.
because 0 is hard coded!
For what purpose? What does 0 mean to the application?
I can hardcode it to any other number too, that just happens to take awful lot of time.
It means that if this specific value is 0, then administrator rights are set.
if it's something else, rights are set according to that number.
there are rights for every ID.
This is for some user table?
this if for system, it's not just for user, it's for _ALL_ that happens, 0=root
uthis if for system, it's not just for user, it's for _ALL_ that happens, 0=root/u
That is a serious problem. You need to separate that sort of meaning from the auto_assigned key.
you mean I should ditch the auto_increment?
This is not a mysql-specific issue. All databases that have auto_assigned values (or sequences in oracle) have this behavior. You would never assign meaning to an auto_assigned value prior to the assignment.
how can I capture the id of a row that is just inserted in the same inserting query?
No. You should ditch your assignment of meaning to a key prior to knowing you have the key.
if I cannot understand, sorry, there is a lingual barrier.
Insert Administrator record. It now has a key. You use that key by joining the record against other tables where type='admin'
You don't assume the key value of an auto_assigned column.
once again, that would mean that I need to program this whole thing from beginning.
If you need a specific value, you would NEVER do that in an auto_assigned column.
Correct. That was a critical mistake.
Now you know.
why does smf then use similar technology.
user's ID's use auto_increment.
You probaly misunderstand something in the application. No one would use an auto_assigned field to hold those values.
there is just one value that is hardcoded, 0.
I just need to know what _first_ value is and it never should change.
I understand how unix systems do this. it's a mistake to implement that with an auto_increment field in any database.
and if current forum's ID number is 0, this forum hosting has ability to create and delete forums (not boards, this software adds support for global database and easy and simple creation/removal of new forums and portals)
At least now (hopefully) you know not to do this again. If you have requirements for values, don't auto assign them. Explicitly assign them.
let me ask one thing. If I create this table with parameter auto_increment = 1 and after that I add a record to this table, is it always 1 ?
If you don't touch it, yes.
the first field cannot be touched with this software, it's supposed to be static and program protects it.
So you could, although not suggested, assign the first N values and start the auto_increment value at N+1. 0 is the exception.
or I could change my program to use 1 instead of 0.
To be safe, you could do that during system initialization / installation, prior to running the application.
You could.
this 1 id is added during installation.
1=first
Just push id=1 out of the way and assign your admin to that record.
admin is assigned by another table by other values. 0 as domainID means that user is admin also on any other domainID.
If you were lucky enough to design this with a constant that represents the admin id = 0, you could change this one constant in your application to 1; fix a few records and poof, all set.
but if I walk through _all_ the code and change domainid 0 to domain id 1 I should be able to get out of the problem.
While you're in there, use a constant identifier, not 1.
Impossible due to program nature.
Do you like php as a language?
no.
I use usually C.
This seems like an extreme restriction to not be able to do that easily.
I gave myself 2 days time to learn php until I started this project.
Wel, I wouldn't hold C up as any kind of proper example.
by program's nature I mean that this isn't actually an independent program.
It is a modification for SMF forum system. A huge modification.
But I try to keep it's code like the original is as much as possible.
Adding a constant to for e.g. Settings.php would change this a lot.
Is this open source?
my program or smf?
SMF.
yes and no. Smf 1.x.x is and 2.x.x is not. This is for 1.x.x
It's a php program, so yes, it is open source
to get 2.x.x beta you would need to pay big bucks
http://www.simplemachines.org
why do you ask?
my program also is open source and doesn't need to be paid, but it's not available until it's finished.
it's unfinished.
or why something has been implemented in such a stupid way I'll clean up as soon as it's finished and without this problem, that day should have been next friday.
it seems I'm talking just by myself..
Just curious what you were modifying.
I have now changed my installation to set first domainid value to 1 instead of 0. Now I need to walk through all code to use 1 instead of 0.
bulletin board system(=forum) but my code can tolerate also portal setup (tinyportal in this case)
bulletin board system(=forum) but my code can tolerate also portal setup (tinyportal in this case)
1.1.3 doesn't look too large.
1.1.2 isn't that large either. My mod is designed for 1.1.2 and I'll migrate to 1.1.3 as soon as it's finished but 1.1.3 does not have that many changes so migration propably doesn't need any changes at all.
unpacked is about 5-7 megabytes and modifications of mine add about 300kb to it.
hi there! does (mysql 3) has a random function? (i want to select a random ID)
select id from table order by random limit 1; I think
Use the docs or use google to find them: Google: mysql random … yields … http://dev.mysql.com/doc/refman/4.1/en/mathematical-functions.html
random
http://jan.kneschke.de/projects/mysql/order-by-rand/
Ok. That one isn't so helpful.
order by rand you mean Xgc ?
No. Use the first link I gave.
oke
Sorry. I missed your full question. Use the second link, shon by the_wench.
shown
roxlu, to you.
huh?
Mouse is all over the place.
Phone rings, baby crying, dogs barking. Sometimes I get the wrong nick.
no worries
Don't use order by rand() unless your list is very small. The post by Jan is pretty good.
hola
moin
moin moin
thanks
so I can't use order by rand() with large items?
large database I mean
Performance degrades significantly with larger sets. Read the article. If the performance is acceptable to you, fine.
okay
I'm using mysql_fetch_object… and when I add in a COUNT(id) into my SQL line it breaks saying "supplied argument is not a valid MySQL result resource".. can I not use COUNT when using mysql_fetch_object?
95kb left for source to walk through until hardcoded value 0 has been to 1.
though I'm using mysql 3.x for this :$
Good luck with that.
the database will have around 5000 items
mysql 3 doesn have ceil 
3.x doesn't have lots of necessary fluff. Get used to it.
Try truncate() instead.
where can I download the old version of MySQL Query Browser?
Downloads / archives section.
ok
truncate deletes a table??
Xgc, Great! Thanks very much
Is there any way to debug why an auth is failing?
Experience has taught me that the error message is usually helpful
In other words, expand your question
I've got two db servers with identical db's. I can connect to both from one, and neither from the other. The only error I get is from the client telling me access denied.
I don't know if its a host issue, password issue, no ide.
I've flushed the host cache, flushed the user cache.
I've got a %.mydomain.com, for the user. I was hopeing to find in the server logs somewhere (invalid client host, invalid username, invalid password) something.
didn't see anything, not sure if I'd have to enable that or now.
s/now/not/
Usually mysql doesn't resolve hosts
usually? When would it?
A lot of distros have –skip-name-resolve in the init scripts or in the stock my.cnf
k, I'll replace with an ip.
Look closer: http://dev.mysql.com/doc/refman/4.1/en/mathematical-functions.html#function_truncate
It's usually unwise to use hosts as your application would ground to a halt if the NS server goes away briefly, or if under heavy load, need to resolve a huge amount of addresses
same thing with ip.
name() is a function reference.
Ok, is the old_password set in my.cnf ?
old_passwords, yes.
Mysql changed the authentication thingies between 4 and 5 or 3 and 4 (?), so it might need to be set
Both machines are stock RHEL5.
Have you tried using mysql -h rather than 'the application' ?
Its very puzzling. I've never had issues authenticating in the psat.
yeah, this is all with mysql.
Its a backup user, each db backups up the other.
Paste the grant
GRANT SELECT,LOCK TABLES on *.* TO 'backup'@'10.8.34.%' IDENTIFIED BY 'mypass'
FLUSH PRIVILEGES; after that, right ?
yeah
I'm pretty sure the problem is on the client.
I can't connect to any db on either box from one host. All other hosts, and applications are able to connect (just found that out)
Can you telnet to any host on port 3306 ?
grant and revoke imply flush privileges
I gotta dash, I'll be in in an hour or two if you haven't solved it by then.. if you have, please let me know what it was
Note taken, cheers
yeah, its an access denied error, not a connect error
k, thanks for the help.
do you have any anon accounts (username = "")?
select * from mysql.user where user = ""
I do, and from the host I'm having issues with (the db started there)
is that it?
yes
your 10.8.34.% hostmask is likely less specific than the hostmask for the anon acct.
so the anon acct is taken.
neither username backup nor "" are wildcards,
and the more specific entry is chosen
Can't have an anon user and non-anon user from the same host?
yes. but if one entry has a wildcard and the other not, the non-wild entry is chosen as it is more specific.
having anon accounts is discourages, anyway.
Hi there. Is there a documentation of the SQL Syntax understood by MySQL 3.23.58 ?
mysql_secure_installation will delete them
and merlin will flag them.
we don't need it.
okay, multismf has been hardcoded to use domainid 1 instead of 0 to determine that this is base forum(base forums admin has rights on any other forum), auto_increment = 1 is set (although it's default, setting it shouldn't cause problems).
"delete from db where user = ""; delete from user where user = ""; flush privileges"
I didn't know about mysql_secure_installation.
its working fine now, thanks.
now you do. all will be good. 
Can anybody tell me if JOINs are possible in MySQL 3.23 ?
http://dev.mysql.com/doc/refman/4.1/en/join.html
Note that INNER JOIN syntax allows a join_condition only from MySQL 3.23.17 on. The same is true for JOIN and CROSS JOIN only as of MySQL 4.0.11.
CROSS JOIN with criteria (ON clause) is a mistake.
MySQL should probably fix that, if the docs really reflect the implementation. Bug report time.
CROSS JOIN table factor
there.
Xgc exactly this is my problem . I have an program using ON clause
And mysql said its a bug
Your program is correct for current implementations of MySQL. MySQL should be fixed to not allow that ON clause.
I can't tell you about 3.x. That's way too old for my memory.
If you're trying to use an ON clause for cross join, don't. It's improper.
Use a standard [INNER] JOIN for that.
I have to wonder why he had to resort to a CROSS JOIN, too
uI have to wonder why he had to resort to a CROSS JOIN, too/u
the last time I used that was 1996
and I was just starting then
I've used cross join to solve general questions that don't often come up in practice.
perhaps
maybe you can tell me what you tried to solve one day Xgc, it's intriguing
It's used when you wnt to generate all combinations to determine which of those possible tuples don't actually exist.
oh
then it does look like a decent debugging solution indeed
the manual literally says thats a difference from standard, so people are kindly advised not to
by the way thanks for the tip with grouping, worked like a charm
No problem.
Hi, on mysql5, what is the way to use log to see 'errors' etc ?
query log
the_wench doesn't know that one.
query log logs all queries??
If enabled.
but, I only want to log 'errors' or something like this, I have an strange insert into that 'freezes' the php script and I have no 'log' for that
Hmm… I'd still want to see the entire log.
can you tell me the exact line to add on my.cfg?
http://dev.mysql.com/doc/refman/5.0/en/query-log.html
thanks
on [mysqld] log = /path/query.log ?
Looks good.
thanks
there's a huge performance penalty using query logs though
Too bad flushing doesn't roll the query log.
Using mysqladmin can I see the exact query that was freezed? I have its pid (srroy my english)
Xgc. Can you tell me how to change this SQL Stetement, that there is no JOIN anymore (or a join MySQL 3.23 understands): http://phpfi.com/252971
The log isn't going to indicate which query caused the freeze unless you have no other traffic at all.
What's the error? The basic JOIN seems ok, unless you mistyped something.
any idea why selecting via php/mysqli a temporary table created by a stored procedure will only return the first resultß
Are you fetching until there are no more rows in the result set?
yeah
I don't use 3.x, so you'll have to post the *exact* error.
Hmm. Wait. I have to loo into my maisl
mails
You have an error in your SQL syntax near 'ON
pages.uid=tx_jppageteaser_list.pid
WHERE
pages.uid IN (207) AND pages.' at line 11
oh. i thaugt would be one line
http://phpfi.com/252974
You hav a version of MySQL that doesn't support an inner join? haha. Try using a table list (comma separated) and place the criteria in the WHERE clause.
Nice.
Try removing parts of the select list to make sure something there isn't breaking in 3.x, especially the CASE/WHEN clause.
Just for testing.
Hello.
xgc any idea?
Not off hand.
http://rafb.net/p/2WnqnY72.html
this is the stored procedure
If I create a unique index on two columns, do I also get the performane gains of having those columns indexed, or should I also create separate indexes for the columns?
What is the exact version of MySQL? select version();
There's a chance you simply need to add the INNER keyword.
FROM pages INNER JOIN tx_jppageteaser_list ON
http://rafb.net/p/fJ2oRj77.html
this is the code
How do you know there are more rows?
-p'
cause in console / phpmyadmin it returns more rows
if I specify a corrupt table specifically, it'll fix it, but in a full scan, it just skips them
Xgc is it posible to download the 3.32.58 anywhere? It not my server
3.23.58
http://rafb.net/p/fWGJUq38.html
so your stuck on 3.x?
being stuck on 3.x sucks
s/your/you're/
INNER JOIN should do it.
i have no idea WHY the only want this
Xgy okay. Thx. I'll try
how old is this server?
He installed mysql via paper tape.
That's how old.
hehe
can you help me please , my problem comes that works for all fields exept for one is called text_log that 'never ends', it is possible to speed up textlong fields using SELECT?
Yeah. really old. And there is a banking company on this sever. maybe thats why the company don't want to upgrade
em.. and they let you in on the server that has some info of banking company on it?
It's safe. That version of MySQL doesn't support SELECT.
heehee
lol
what CAN it do?
)))))))))))
lol
It crashes a few times a day, keeping the admins busy. they don't want to update for fear of not being needed.
It has just the website of a banking company. No money transfers or stuff like that
would it be possible to somehow fetch variable content to CREATE TABLE statement? (perhaps with PREPARE within a procedure?) with 5.0.37?
You can dynamically generate SQL in procedures.
some doc about his?
*this
See PREPARE
Xgc sn't there a possibility for me to fetch a mysql 3.23.58 version anywhere?
so in principle it is doable, right?
Not that I'm aware.
If it allows you to issue DDL statements. I think it might.
OK, thanks,will try to figure it out
Here's a hint: http://rafb.net/p/Lo3lE189.html
a href="http://rafb.net/p/Lo3lE189.html"http://rafb.net/p/Lo3lE189.html/a
how do I retrieve columns which were updated by an sql UPDATE in jdbc ?
i mean rows
via SELECT. You can also try using a trigger.
hi
can i get some support with querys from here?
update foo set x=y, select x from foo where x=y ? does not work since foo can have changed rows between those two calls
You think abusing the local foo is funny or something?
i figured out there is an limit with query length.. how can i change this..?
Transactions might help.
not available in the engine
You're out of luck. You have several ways to attempt to track this. If this is critical, add a modified_by field that every client specifies. This can be used to separate updates made by X from updates made by Y.
no one heard of this query length?
o.O
max packet?
what is a good way to generate modified_by ?
its not the max size of the packet
That would be application specific.
ok im running l2j, and the sql are quite big
it has to be unique for each client
and i have to put in sql files like every 10 pages ;
INSERT INTO `spawnlist` VALUES
to get it running
it has that in start, but after that is too many codes for it to add em in db
That's up to you to design for this application.
That's usually due to the max packets setting. What is the current setting?
16m
….
What's l2j?
lineage 2 java
game server
How large is this insert?
2,8mb
and it has lines like one millin billion zillion
and thats what the problem is..
that insert into has to be every 10th page or something, cause there is some limit..
how do I select the connection id / thread assigned to my connection?
Post the output of: show variables like '%max%';
o.O
what u want to see?
tell me what to do
im pretty new to this whole mysql things :-
hi. I was wondering if there was a command to convert text to it's canocial form
I have a VARCHAR with a maximum length of 30 which I want to read in canocial form, without having to use an external method to convert it
show processlist; — It should be here.
Just post the output to the pastebin. (rafb.net/paste)
output of what?
it disconnect from mysql server cause too many entries?
*-?
Post the output of: show variables like '%max%';"
canonical*
and how do I get that info from the show processlist view as a sql select?
anything?
select connection_id();
and others: http://dev.mysql.com/doc/refman/5.0/en/information-functions.html
http://dev.mysql.com/doc/refman/4.1/en/miscellaneous-functions.html#function_uuid
that was what I need for modified_by
Depends what you mean by "read".
yo, thanks for pointers on PREPARE/EXECUTE, I managed to do what I wanted (though it looks quite ugly )
Dynamic SQL *is* ugly.
An application should almost never use DDL… unless this is a developer tool, something like MySQL query browser, workbench or administrator.
well, I would be ahppy to avoid it if there another way, so far I couldn't find any
and in the case of temp tables, there's no need to be dynamic, since the name space is connection specific.
I have a value like "androiddata" which I want to parse through
username (mysql) = "Android Data"
username (query) = "androiddata"
"androiddata" so it finds the username
What logic would you use to do that? lower case and remove whitespace?
convert to canonical form.
it strips all punctuation and space from the variable and converts to lowercase
I've never seen that in MySQL. You can use lower() and replace()… or create your own function that does this.
gah
MySQL 5.0 supports stored functions and procedures.
This doesn't appear to be supported by the SQL standard.
ok
A stored function appears to be the most common solution, available in MySQL.
I'm interested in taking the mysql certification, has anyone taken it? or heard anything about how difficult it is?
hi; is it possible to do a "LEFT JOIN" into a table from another database ?
Yes.
FROM db1.tblanme LEFT JOIN db2.tblname ON …
Xgc; LEFT JOIN databasename.tablename ON databasename.tablename.fieldname = databasename.tablename.fieldname
Xgc; ok, thanks.
See Federated Storage Engine if the databases are remote with respect to each other.
xgc; thanks, but they're running under the same mysql server, i should be fine.
hi - i installed PhpMyAdmin on feisty fawn, but when i try to access it at http://localhost/phpmyadmin - it tells me Access denied for user 'www-data'@'localhost' (using password: YES) ( i login with the correct root password )
mysql passwords are independent from the os ones
i mean the correct mysql password
Can you connect from the command line?
yeah
i can log into mysql via command line with the correct root password
you have phpmyadmin configured to use the wrong login name
where do i find the config file for that?
I've no idea, personally I recommend that you avoid using phpmyadmin for anything important
php documentation.
e.g. where you don't want to lose all your data
its just for local development
it worked before though
thats why im wondering
the power cord on my server got kicked…restarted it and now i can't connect to mysql
Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock
http://saved.im/njaxmjy4zho/phpmyadmin2911.png
What error messages do you get on the mysql log?
hmm, i'll check
perror 2
!perror 2
No such file or directory
Just a guess.
in /var/log/mysql/mysql.err ?
Just show the *exact and full* error.
You pasted part of it.
any Idea what I could do? Normally on ubuntu all you do is install and then it implements with the webserver and simply works - it did before, but now it doesnt
Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (111)
It's entirely a php configuration issue.
111; i get 2 if i remove the conf file
obviously
See the docs for that application.
Ok
Thanks
This is presumably due to mysql failing to start up - it generally does this if there is something seriously wrong, and it would have logged it in its error log. Consult that.
MarkR42: that's what is so odd…mysql.err and mysql.log are both empty
What happens if you try to start the service manually?
not sure what you mean. that's the error when i try to start it
either with `mysql` or `/etc/init.d/mysql start`
stupid power cord
No UPS?
Xgc: no /cry
I don't remember what's that's like, to run without.
:P
Does /var/run/mysqld even exist?
yeah
Sorry but I don't find any phpmyadmin related topic in the PHP Manual, is it a command to edit in my php.ini?
phpmyadmin should have installation documentation.
phpmyadmin is neither a component of php nor mysql, but some other guys misguided, failed attempt to produce a robust application
Consult its documentation or its support channels if there is a problem. We just use mysql here
and likewise, ##php just use PHP.
Xgc/MarkR42: Thanks, the manual helped, problem solved
I strongly recommend you avoid the use of phpmyadmin in a production environment
especially its backup / restore
It's really just for small testing purposes
nothing special
I'm trying to normalize my data, but I have many duplicates
a primary-key, bigint 'id', and a string 'artist'
groupwise
groupwise max
http://jan.kneschke.de/projects/mysql/groupwise-max/ http://dev.mysql.com/doc/refman/5.0/en/example-maximum-column-group-row.html
Ehm exuse me the addres to phpmyadmin is php.etc.com, is there a way I can get there through cmd instead, easier on the eyes….
I want to configure the database/tables and etc through cmd.exe how do I connect and do that?
anyone done a mysql certificaiton?
You can use the mysql command line client, as described in the documentation
In fact I recommend you stay away from phpmyadmin
MarkR42 why do you recomend that? ANd yes I want to learn through CLI, and what documentation are you reffering to?
why do you not recommend phpmyadmin?
I don't recommend phpmyadmin because I am aware of several bugs, which may have been fixed in some way, which caused it to lose or corrupt data
MarkR42 So how do I use the MySQL command line client?
As described in the mysql manual
ah ok, for the most part I just do simple select and update queries with it, seems to work ok
MarkR42 ehm I'm not reading that….
It is a command line application, shipped with mysql, and has many applications
And many, many options
MarkR42 the database is not in my computer
its on another server
Nevertheless, the mysql command line client can connect to a remote database, provided the server lets you
the server probarly lets me
is it through CMD.exe
or if you have ssh/shell access you can login to that and then do it
In win32, you can invoke mysql command line from cmd.exe, but it is not mandatory
what does mandatory mean? as in Must?
yes
Yes, you can invoke it from any other application if you like, even directly from the win32 run box if you really want
If I needed to use mysql on win32 however, cmd.exe would probably be my preferred choice to run it
MarkR42 Alright then, ehm so what would you guess the adress is? right now its mysql.etc.com
im on win64 '
the server address is whatever your server is, contact your dba for more information
vista
You can use the 32-bit client on win64, there is also a win64 version
MarkR42 I cant get in touch with my DBA at the moment, however I got correct login and etc…
I'm afraid I cannot remotely seem to read your DBA's mind, my telepathy is not that strong
MarkR42 it's really just bending time and stuff, you get to learn it
no
the question was
what command do I use to connect to it?
telnet?
ssh?
Certainly not, typically something like mysql -u username -p -h hostname databasename
As I said, read the docs for a full explanation
mysql is not recognized…. etc
COuld you please link me it?
In which case, you've forgotten to install it,
I dont have sql on my computer
No, I will not- it is straightforward to find it
its on a database
The client can connect to a remote server
do I need to have a database on my computer to connect to a remote server?
sounds dumb..
No, you do not.
You just need the software
And what is that called?
It's called mysql
Or rather, the client parts
http://dev.mysql.com/doc/refman/5.0/en/
is that the manual you were reffering to?
Is it possible to have MySQL output headers in a query ? I would like my gridview to display the column titles.
Yes, that is the manual
I've no idea if the windows installer allows you to install just the client parts, but if you do a complete install but don't run the sevrer, that will definitely give you what you need.
I have two left joins on my query to the same table, but I want the same field name for the table joined to have two differente names (one per left join), how would I go about doing that?
aliases
not familiar with that,
select `field` AS foo1, `field` AS foo2
select x.x as y, x2.x as y2
ok
oh yea
but the problem is, this is a single field that I need to call twice
backticks doesn't look that good
so alias the remote field
Akuma, maybe you realy want to use an union and/or a subquery?
better yet, pastebin your query
or it might be as simple as the if function
sure
ok
IF/CASE/WHEN
one sec
http://rafb.net/p/DfSClk25.html
basically, I have two fields in projet that call fields in organisations
what I want to do is access a certain field on organisation (same for both left joins) with different names
ok
basically a different name for the same field on organisations for each left join I have
you need to alias your tables
i.e.
you could use a subquery and an union all
or an if function
LEFT OUTER JOIN organisation org1 …. organisation org2
then org1.fieldname, org2.fieldname
you could even alias those fieldnames, if you like.
is this two different queries I need to do?
no
you can reuse the same query.
hmm… I might have to read a bit more doc on left joins
SELECT projets.* FROM projets LEFT JOIN organisations org1 ON org1.oid = projets.oid
then
LEFT JOIN organisations org2 ON organisations.oid = projets.manda_asso
err
LEFT JOIN organisations org2 ON org2.oid = projets.manda_asso
SELECT … org1.fieldname AS foo1
then
Oh!
then org2.fieldname AS foo2
so I can basically do the aliasing before defining the table aliases?
you need to define the table alias as you left join them.
follow what I wrote.
Is there a command for mysql to decrease or increase stored value or is it better that I use select in php to get that value first and then update it?
SELECT projets.* FROM projets LEFT JOIN organisations org1 ON org1.oid = projets.oid LEFT JOIN organisations org2 ON org2.oid = projets.manda_asso
jake81, you can just use field +
field + number
eh.. with update?
but then I only get projets.*
I pretty much spoon fed you there
of course, modify the SELECT statement as needed.
Ok
you can select from projects, org1 or org2
ok
that was what I wasn't clear on
Thanks
jake81, correct
no problemo
you mean like this: UPDATE settings SET value+11 WHERE VARIABLE='myvariable' LIMIT 1 ?
if I want to increase it by 11.
note that I corrected your orignal LEFT JOIN statements. They were incorrect.
how can i get a unix timestamp from a timestamp field using jdbc?
VARIABLE and VALUE where VARIABLE is name of variable or other identifier and VALUE is where numeric value is stored?
I might have ran into trouble with it. value seems to store contents in format text. For this specific value anyway, it only uses numbers, but field type is text. Will it work?
CAST it as INTEGER
or INT
eh, CAST?
it should still work
i think
CAST the VARCHAR field as INT
just to be safe.
okay, so I should do this:
eh sorry..
I don't know how to use it with update..
pastebin your current query, please
my current query has not been created yet, I am just trying to figure it out
I did not follow your requirements. What is your current goal?
UPDATE settings SET CAST(value AS INT)+11 WHERE VARIABLE='myvariable' LIMIT 1
anyone know what this error could be "'Unknown column 'RoleId' in 'field list'' "?
I have a table called settings. It contains 2 fields, variable and value. variable is tinytext and value is text.
that looks OK. Of course, if the field is VARCHAR, you might have to cast the result back to VARCHAR(length)
one of the fields is called for e.g. as "amount"
or in my example I called it myvariable.
the column RoleId does not exist in that table.
solution: don't select it
hello, i recently installed mysql on my ubuntu box and it's up and running, however i can't connect to it remotely, even though i forwarded port 3306 through my router…is remote access disabled somehow by default?
if they are int ot tinyint, no casting is necessary
argh.. I'll try to find myself an example with google about using update with cast..
they are text.
pastebin the output of DESC tablename; please
infamouse, possibly ubuntu did that
I cannot as it's not created yet
please create the table first.
my program is not in state where it could be executed.
you prolly shouldn't use tinytext for a variable name
and at least use varchar
It's not my idea, I cannot change that as this is modification to existing product.
will check a thing, can i create a role id then it should work?
well you're throwing a lot of stuff in here, without anything to rely on. I need to know the specifications.
if you alter your table, sure.
!man alter
see http://dev.mysql.com/doc/refman/5.0/en/alter-table.html
^^
johnny, how can i determine that and if so, allow remote connections?
perhaps skip-networking in /etc/mysql/my.cnf
that's how i always run mine
if i wanna query remote db, i ssh in first
there is records already, then it should work?
sure.
just tell me what you want to know? table: settings contains 2 fields: variable(tinytext) and value(text). there are records, I'm interested in this one that has variable=totalMembers and value=something. Value is always numeric, I need to increase this number by specific value calculated by my
program.
you can add columns to existing tables with records.
ok, this is much better already. I have actual data to work from.
bok, this is much better already. I have actual data to work from./b
yeah, sorry. It was hard to explain when you do a lot of things at same time
uyeah, sorry. It was hard to explain when you do a lot of things at same time /u
buyeah, sorry. It was hard to explain when you do a lot of things at same time /u/b
oooh, innocent bystanders
UPDATE tbl SET value = value + 42 WHERE variable = #number AND value = 'value';
that would do it.
will it?
value's type is text? Oh.. You made there a small conversion
thanks. I hope it works, as currently I have no way to test it
I used single quotes for value, as you said it's text
I did not use it for variable, as you said it's int
eh..
as far as updating the variable column, it's int, so no conversion is required.
value is a field in table which has TYPE text.
yes, I covered that already.
okay, guess what.. I'll try it in phpmyadmin.. Then I should know if it works or not without trying to execute my software..
oh shit, it's text. Sorry/
yeah
then you do need conversion. Hold.
means that it get's it from field value
UPDATE tbl SET value = CAST(CAST(value AS INT) + 42 AS TINYTEXT) WHERE variable = #number AND value = 'value';
UPDATE tbl SET value = CAST(CAST(value AS INT) + 42 AS TINYTEXT) WHERE variable = '#number' AND value = 'value';
indicate a value passed as an argument.
as per the man pages.
now it looks better to me too although I'm far from being expert.
this version will work
should it be TEXT instead of TINYTEXT?
value's type is text and variable's type is tinytext
I'll leave that up to you to match them
the idea is the same.
you have error near 'INT) + 42 AS TEXT) WHERE variable = 'totalMembers' AND value='value' at line 1
I forgot prefix..
i need to try some more, don´t really know the prob
nope.. It's still wrong..
i have a table with two cols.. user_id, subscribers.. each user has a list of subscribers.. i want to get the list of id's
ordered with highest number of subscribers
select count(user_id) as cid, user_id from subscribers_table group by user_id order by cid asc; doesn't seem to work
anyone konw how to fix this?
actualy i think i fixed it.. nm
thanks
pastebin your current query
johnny, i'm not seeing anything regarding skip-networking in my.cnf…do i insert that into it?
variable(tinytext) and value(text) are fields in table smf3_settings
I just lost data. Please bear with me
any solutions?
I am still recovering my data
for some reason, i can't remotely access my mysql server, i have port 3306 forward through my router, but still no luck…is there a setting in mysql that's preventing this?
remote
remove bind-address= and skip-networking from my.cnf and grant permission to the external 'user'@'host' and remove any firewall rules
that was for you, infamouse
i want to store 2 time values in a db to later calculate the difference. do i use datetime or timestamp or just time?
[bonobo]: I'd use datetime
thanx, Davey
I'll be back later, wife insists that it's my turn to cook so I need to go do my duty. I'll come back later. If you happen to know what causes problem, you can always put it in a query for me..
uI'll be back later, wife insists that it's my turn to cook so I need to go do my duty. I'll come back later. If you happen to know what causes problem, you can always put it in a query for me../u
its my first direct contact with mysql. came to the conclusion that no cms has what i want
create temporary table foo — is this threadsafe? i.e. is "foo" only visible on same connection, or should i generate unique table names for this ?
foo is visible to the world, may it be known!
hah
foo, not you
i meant my temporary table
lets call it bar then
Is that a threat?
hehe
so, should i use unique names for those tables or i can use something like "bar" or "baz" in the application and it would suffice ?
oh
found an answer in the manual
imagine that! :p
You can use the TEMPORARY keyword when creating a table. A TEMPORARY table is visible only to the current connection, and is dropped automatically when the connection is closed. This means that two different connections can use the same temporary table name without conflicting with each other
or with an existing non-TEMPORARY table of the same name. (The existing table is hidden until the temporar
(quote)
ok, that is very good
Hello, I am Gene Ray. I have a doctorate in cubicism and I am the wisest human and inventor of the Time Cube. www.thewisesthuman.com www.timecube.com
I have become interested in SQL databases.
the wisest human, eh? good story
I feel I can use a database such as MySQL or Postgresql to communicate my Time Cube theory to the word animals that are educated stupid (professors).
I will have four computers (a cube has four sides) placed around the earth.
These computers will record the position of the sun related to the earth by communicating with a central database.
This will prove my Time Cube in terms understandable by the educated stupid.
Any person, even the most educated stupid, will clearly see the MySQL table shows time four the four different days per rotation.
What Life Force is more powerful than the Sun? The masculinity Sun and the femininity Earth form a binary of Opposites at Center of Universe - Greater than either Sun or Earth, debunking all Oneism Gods taught by religious/academic Word Animals.
This will also proove that a cube has four sides, a top, and a bottom.
Spelling is irrelevant to anyone with superior logic.
rofl
This makes me the best programmer and the wisest human.
I consider myself wiser than Einstein.
No human alive has been able to measure my intelligence. Psycologists can do no more than assume I am schizophrenic!
This is why I have awarded myself the Doctorate of Cubicism.
No other human has ever acheived this.
…
who is this lunatic?
Gene Ray, apparently
horrible web design, by the way
Damn, Gene.
huge fonts make for painful reading of long documents
the first site gives me a headache
that background image is horrendous
Test
You are too inferior to read my web site.
hahaha
I have no problem reading it because I am not a word animal.
a … word animal?
My web site has more wise information than evil Google.
ALTER TABLE geneRAY ENGINE=blackhole;
someone here who could help me with a little problem?
Google is an index of knowledge that is really stupidity.
its not a matter of being able to. It just that it makes it more work than it is worth for some rant against religion
huge font = more scrolling
ask your question, and someone might
NASA's Moon Landing was far less of an achievement than Time Cube discovery, for I have Cubed the Earth, with 4 simultaneous corner days in 1 rotation of Earth. (singularity belief scientist can't comprehend T.O.E.)
anyway… did you have a mysql question or what?
k..
Yes.
REVOKE NONSENSE ON mysql.* FROM 'generay'@'%';
I want to setup a MySQL database to prove my theory to word animals.
then please ask it instead of spewing nonsense
i actually started to optimize my sql-queries, so i tried this one for my stats:
SELECT * FROM user_statistic LEFT JOIN users ON (users.id = user_statistic.userid) WHERE descr='".$descr."' ORDER BY descr,score DESC
but i doesn't really work
how is it not working, exactly?
i want to order the stats in categories like building or sience, but it always shows me the same categorie
$descr is for the categorie's name
well WHERE descr='".$descr."' would only return results matching that particular category
so the ordering does not make sense.
This is why all computers are evil. Their CPU gives stupid lies when you multiply a negative by a negative!
that wasn't a question. You want to set up a mysql server? go for it. It can be downloaded from http://www.mysql.com
thats called "math"
Math is built upon evil concepts.
how dare you, GeneRay
Because everyone here is educated stupid, I will leave.
math is holy
you're wasting your time.
I am above God.
yeah but its kind of funny
Good bye, word animals.
kthxbye!
And visit www.timecube.com sometime.
no
we won't
I already did
lol
I had the displeasure of loading it in my browser
Hello, I am writing my very first mysql stored procedure How do I iterate over a column? I am looking for something like a foreach in mysql stored proc.
well now that THAT is done with… I'm going to change my laundry and take a shower
where were you? You could have kicked him!
shit. something that was a valid query in mysql 4.x has a syntax error in 5.x
how about you tell us what the error is, exactly?
http://rafb.net/p/YaBmD418.html
not an easy query to read, but i'm guesing that theres some variable i'm not allowed to use anymore
like 'condition'
is 'condition' a column name?
if so, put it between backsticks
its not
) AS condition
try AS `condition`
meh, that was the problem
you're not allowd to do 'as condition' in mysql 5.45
er mysql 5.0.45
all uses of condition have to change to `condition` or another name
ok.
thumbs, have you recovered your data?
almost done.
90%
thankfully, my backup was good.
how did you end up loosing your data in the first place..?
I dropped the wrong table.
I need to reindex it afterwards
so it should be OK.
okay..
argh..
UPDATE settings SET value = CAST(2 AS TEXT) WHERE variable = 'totalMembers';
even this errors.
If I change CAST(..) to '2' it's just fine.
http://www.engadget.com/2007/07/28/man-gets-bsod-message-tattooed-on-his-arm/
CAST is awfully documented. I was able to find a lot of examples when people need to use CAST with SELECT, but no examples at all about using CAST with UPDATE. No any mention, thousands of pages found but they all are just release notes and bug reports of mysql..
don't use TEXT
use VARCHAR(length of field) instead
i.e., VARCHAR(20)
I don't know length of field. It's type is text
so use a large enough number, and humour me.
please.
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server verion for the right syntax to use near 'VARCHAR(20)) WHERE `variable` = 'totalMembers'' at line 2.
amused?
heh
pastebin the whole query, again, please.
UPDATE `smf3_settings` SET value = CAST( 2 AS VARCHAR( 20 )) WHERE `variable` = 'totalMembers';
ok
okay.
sec
UPDATE `smf3_settings`SET value = value + 1 WHERE `variable` = 'totalMembers'
I got customers to tend to
It doesn't matter even that value's type is text, it seems to mysql can go around it.
so no CAST needed anyway.
ok
brb
I need to drop a whole bunch of tables. I'm looking for something like this "drop table foo*"
Put all foo* tables in teh same database and drop that… or use information_schema or show tables like 'foo%' from a shell script to generate the drop statements.
Thanks!
hi guys
Hi, I have a select that uses a field as longtext but is very very long, and only one select maybe uses 15m, any idea how can I speed that?
hi
can mysql be used with sql on rails?
sql on rails?
yea
its a new web development paradig
u make apps in PURE sql
never heard of it
sounds interesting.
pure sql sounds scary
not really. Makes for secure code.
since everything is server-side
ya u cant make sql injection
also it supports ajax and scriptacus
so does my sql support sequel on rails?
secure??
hmm
and you cant inject sql because of that?
uh huh…
jeepers do you have an URL for that?
as long as you take no input from users. ;^)
threnody not much point then
theres a screen cast
which is very web 2.0
the point is that you don't rely on client-side validation.
thats a joke
you do know that, right?
Your browser is insufficiently AJAX-y to
render the content pointed to by this link.
aha!
its just a joke
yes, it's a dummy site.
making fun of the RoR site
and idea 
what?
its a joke?!
yes
are you making fun of us too?
http://www2.sqlonrails.org/down
hahaha
"sudo -u mysql mysql_install_db5" i get: "mkdir: /opt/local/var/db: No such file or directory" (i am using macports hence that specyfic path). does that mean i have some user rights (mysql user/group) problems or what?
look at the links
ya i know my browser isnt compatible im using an old version
probably no /opt/local
maybe i should install ajax extension
jeepers you ARE making fun of us
ok, we're not falling for it anymore
Darn.
hii
oh no
I have a table with the columns "id int, order int, name text, desc text".
Uh-oh, desc
and when I run this query
I liked the DOS 3 terminal though. made me nostalgic.
insert into `forums` (name, desc)
values ('$fname', '$fdesc')
Quote desc
It's a reserved word.
`desc`
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 'desc) values ('fhj', 'fj')' at line 1
Or just call your column "description"
ah
thanks
No problem.
quote
Use ` around identifiers (database/table/column/alias names) and ' around strings and dates. MySQL does allow " for strings, but ANSI standard uses " for identifiers (which you can enable with ANSI QUOTES option)
is there some type of mysql bug I'm not aware of for example I'm trying to run a query wher salesid 84 and export it into csv through my adminstration section on my main website which I have a simple little php file written up, well it exports them just fine into a csv file, but whenever I
have 20 rows for this certain salesid number it tries do download the php script? any other orders that have 20 rows, it will export them just fine, hop
d-media: You're trying to blame a bug in your web site on MySQL?
Basically yes, because if you knew exactly what I was talking about then you'd understand.
d-media: Can you ask the question in a way that shows the MySQL function or query that isn't doing what you expect?
yes
k
hold on let me show you
I don't see how you'd ever understand.
d-media: Don't include any php detail. Just the SQl will do fine.
k
You just might have a point.
i can tell already you don't have any idea of why it would be doing this to trust me, watch ok go here http://pastebin.com/m2b4a6d4a
thats my sql
now when I run that sql lets say I do salesid 84 if salesid returns 20 rows it don't export my csv file right, but if I do any other sales id it works just fine, now salesid 84 export fine until it exactly hits 20 rows
d-media: Are you sure about that? Do you know me?
but if salesid 64 has 20 rows it will export it just fine
I do know your good at this
thats why I'm glad your in the channel
every one else thinks its something else
then there like Idk
d-media: since this sql wouldn't create a file, I'm assuming your php takes the result set and writes a file?
run the query in a mysql console. If it works there, the problem is somewhere else
see when i try to export salesid 84 it tries to download the file csv.php, but if it don't have 20 rows the headers in php will bring the window up for export.csv
yes
want me to pastebin it?
it sounds like a bug in the php data handling portion
it could be
d-media: That has nothing to do with the database engine. This is a php issue.
