GRANT ALL PRIVILEGES ON hlstatsx* TO gameserver@reset4 IDENTIFIED BY password and I cant login to mysql like
Copying the datafiles
raar, if they are myisam tables I use a script or you could use cron cp /var/lib/mysql/dbname /backuplocations -a (on ubuntu)
okay, thanks
Easiest to back up… or restore?
ah cool, thanks
back up
raar, the data files themselves might be located elsewhere on your system, and if they are innodb it might not be that easy
Make sure to lock the tables
seekwill, yes I do /etc/init.d/mysql stop
You don't need to stop. They can still read. Just can't write
it literally takes bout 2 minutes to back up our entire system
Maybe for yours…
how do I examine the MySQL connection pool for detecting connection leaks?
seekwill, aye I know..but for now stopping was the simplest..and yes many others are going to have much longer wait times than me
RobRoy, still trying to look
not sure if I will have an answer for you before I have to go home
RobRoy, what version of mysql are you running?
If I create a temporary table (create temporary table now as select now() as now, how do I reference it?
just like any other table.
np, thanks for trying we may just have to take the app down and run this massive query we're on mysql 5, forget exact can get it if you need it
thanks guys
Syntax?
CREATE TABLE myTable SELECT * FROM someOtherTable; SELECT * FROM myTable;
"SELEDCT command denied to user … for table now"
RobRoy, no problem, with mysql 5 you can do it as a stored proc and its really easy.
is it temporary.now, temp_now, temp.now, now, or what?
whatever you named it.
oh sweet, I haven't done stored procs, you have a snippet I can work from?
In the above case, "now"?
yes
what differentiates a temporary table from a non-temporary table, then?
RobRoy, I wont by the time I leave for home but it only takes me half an hour to get home and then I can get on and write the code for you if you want
When you disconnect, it gets dropped.
And no other connection can see it.
I'll look into it and stick around, hopefully I'll have it figured out before then? thanks so much
karsten, if you are going to use now you might want to backtick it… select * from `now`;
Assuming I've got a default db "mydefaultdb", I see: "Table 'mydefaultdb.now' doesn't exist"
RobRoy, stored procs are a lot easier than folks think
cool, reading up now
Was there an error when you tried to create the table?
anybody know how to look at the connection pool?
RobRoy, but in the case of what your doing, a cursor will definitely do it, in a two stage process
your best bet is:
well hold on
Not that I can tell.
I'm using mysql query browser under Linux. I got the "executing query" message.
No report on table creation though.
err..
select count(*), path, type from users group by path, type;
actually in the cursor it would be like this:
select count(*), path, type from users group by path, type into @count, @path, @type….
ok thats not exactly right but when I get home it wont take me long to write the stored proc
looking at it from a stored proc perspective its super easy
awesome, I'll take a stab until then, ttyl
RobRoy, ttyiab
Journey, SHOW FULL PROCESSLIST
oh, too bad - but schoonm : found the answer
I just needed to pass an extra "count_query" param, to avoid the rewrite
thanks!
i'm trying to set up a mysql cluster with 5.0… I have a db that normally was about 700 mb but when trying to add it to my cluster it's full errors. Reading around and running ndb_size shows that the server needs 4.5GB ram minimal… This is weird that
a database that was 700mb large suddenly is now 4.5GB big….
i've read of a calculation ((SizeofDatabase × NumberOfReplicas × 1.1 ) / NumberOfDataNodes), it looks like i can limit the ram needed by having more datanodes…
my question is whats the difference from a replica and a datanode?
right now i have 3 servers in the config (i have more servers to add), server 1 and 2 are running ndbd and mysql, server 3 is running ndb_mgmd and thats it…
RobRoy, actually i am almost done with the proc
i just have one question, why did you use path like instead of path =?
the stored proc I am going to give you will be a path = …but you can play around with it at its core and make the mods you need… but be sure to test on sample data
awesome! I'm pulling my hair out trying to figure it out…was about to mention that, we don't want exact matching paths, we want to match an ID inside a string like this anywhere: /1/4/6/436/6
RobRoy, ok well we may have to work on this more when I get home, this may or may not work I am going to test it now
so that group by path probably won't work right? ok
well its going to give us problems
but we can figure it out
I didn't see a full description. But here's a hint based on what I think you asked for: http://rafb.net/p/K3A5Ot72.html
Ok. It's a little more than a hint.
why not use the regex match instead?
im back (clocked out)
win 9
checking it out now, thx
Xgc, that basically sovles the issue? ima head out then come back to talk to yall about it when I get home
im still interested in fixin it via the stored proc too
brb yall
have 150,000 rows so this is killing my local mysql
No particular reason. This was just to give RobRoy a working form.
That can happen with a poor design.
yeah, we've switched our app's functionality so it performs well now, just need to run this once to fix old data
I have perl roots (decaying over time). Would like to see a regex extraction in mysql some day.
The list within a cell isn't a good idea.
That would be helpful.
yeah, I'm sure there are better ways, we do need a nice way to get all children/grandchildren of a user tho
is it better to have another table with hierarchy?
One value/atom per cell is expected.
yeah, I'm taking over a whipped together project and would have done it that way, maybe we'll switch this over
A list would be represented by another table with 1-N or N-M relationship to the parent table.
yep
Stored calculated values are also something to avoid. Use materialized views (not yet in MYSQL) for those special cases.
I posted a closer example to http://pastebin.ca/616914 with a bit of sample data, still not working right as it has level 1 for all…I'm gonna keep plugging away
Define "not working right".
Iv'e tested the behavior. It's fine.
Well, you do have a separator issue, however.
sorry, the level for id 2404 should be 2
This is another problem with this approach.
Level?
I posted another http://pastebin.ca/616921 which fixed a typo
field 'level'
I didn't see "level" in your original question.
Explain it to me.
How do you expect level to impact the result?
Okay, path shows the ancestor tree for infections. I infect you, you infect Joe, Joe infect Sue… Sue's path would be /Me/Joe/Sue
and my 'level' would be 2
since I infected two people
Note that … ON u2.path LIKE CONCAT('%',u1.id,'%/') … is going to match improperly. %2% will match 1234 for instance.
so, leaving the crappy design aside, we want to get a count of all rows that match ID/
You need to match %/2/% and make sure your lists are terminator on both sides with /list/.
well it has that forward slash, but I suppose 2/ would match 12/
correct
You have to change it.
changed.
So you only care about the first element in the list?
That doesn't sound like a complete description.
Sorry, will try better. No we care about /Me/ showing up anywhere in the path. I.e. I get a point for every user row that has my ID in it's ancestor path
updated to http://pastebin.ca/616928
Hi!
Step back for a minute. …
Hey there.
Tell me what one row in the user means.
HI
user table, that is.
Hail!
hi people. dumb users welcome here or is it more for the devs?
Both!
Therion, nice
Though if you mean MySQL devs those are mostly in #mysql-dev
better dumb users than dumb terminals
I'm new here. Is there a channel devoted on regex on this or another server?
or wait…
i'd rather have the dumb terminals. at least they listen when i tell them what to do.
okay, each user is a unique user of this app, they get points for "inviting" other users to sign up, so if I invite you to the app, your path will include my /ID/
You would usually find help on that on a language or application level… like #perl for perl regular expressions
So the list is all the users you've gotten to sign up?
we're switching over functionality of the app to not use this count(*) to get level, but need to run this query once to update everyone's level
in fact i'm looking for a little help concerning characters encodings. my db doesn't honors accents (at least i think)
correct
@Therion Thanks, do you know if there are any channels for Java Enterprise Edition?
#java would surely cover it There are several related channels
Ok. and what is the calculated count supposed to reflect? The count of all the users directly or indirectly signed up by you.
@Therion great, Thanks
correct, so number of rows where your /ID/ appers
all my variables are set to utf-8 (checked with show variables like '%char%'; )
No.
That's not correct.
but if i record something like 'été' then it's recorded as '?t?'
how isn't it? maybe it's that - 1 since my user row has my ID as well
If my description above is correct, the answer can only be found recursively, given your current schema.
oh wait. the database is in latin1. how can i convert it to utf-8 ?
You don't want to count people who've signed you up, for your total.
I have http://pastebin.ca/616939 working as multiple queries with PHP. So you're saying I have to stick with this or do a cursor to achieve what I want?
correct, we don't include MY id in the user record of someone else who signed me up though so we're okay there
path, is /grandpa/mom/me
always ends with me
You misunderstood me.
One moment. Phone.
k
hey hey
ok back.
hi RobRoy did the solution Xgc solve the problem?
still working on it, if you've got a stored proc solution I think that might be the way to go
RobRoy, opening it up, it was giving me some errors so I have to correct them,
then I'll pastebin
Ok. Back. You said you want to find the number of people directly or indirectly signed up by user X. That means, count the people in their direct list plus all the people in those people's list. If you meant something else, correct me before we go forward.
X, 'list' — the number of entries in this list is the number of people X has signed up.
Correct or not?
close, except that the paths include ALL ancestors…so user X signs up Y, Y signs up Z, Z signs up A….path for A is '/X/Y/Z/A/'
so i don't need to recurse
that make sense?
Oh. So you have duplication there as well.
correct
So what's the problem. Just count the items in the list. You don't need a join at all.
This can't be right. We're not on the same page yet.
It's too simple to be correct.
You must be asking for something else.
Xgc, aye. Ive got a stored proc that works when path = path but not for sub paths as he is trying to accomplish
http://pastebin.ca/616939, but it's very slow. I was wondering if I could get it all in SQL. I want to update every row's 'level' with the count that we're talking about as these 'level' fields have stale data
I am going to post what I have anyhow, even though its not the solution just yet
How do you direct a message to a specific person but not make it private? ie. Therion sent me a message and it turned red on my screen
It doesn't help to say that. You need to describe the real algorithm based on the current data.
if the list isn't correct, you can't solve anything.
RobRoy, Xgc that only works where path = path from the cursor…so clearly it needs more work
Xgc, agreed.
I'm trying….I want to iterate through each user record. So I'm at user record X, now I want to get count(*) where path LIKE %/X/% and put that value into X's level field, etc.
etc. meaning now do the same for user Y
Take the posted data and tell me what the totals should be for each user.
user Z, and so on
ah ok now i understand RobRoy
RobRoy, how do you determine its user X? from the username?
sorry, that posted data doesn't show the whole picture, changing now
'id'
That's not correct or you'd be happy with my solution.
That's exactly what I calculated, minus the termination problem you have. You just need to correct that detail. CONCAT('%/',id,'/%')
apologies, okay…for the data on http://pastebin.ca/616959, user 2404 should have level 2, 9200143 should have level 1, 502377204 should have level 1, the rest 0
ya just saw that, one sec
and 519470541 should have level 1 as well…your code appears to be working now: http://pastebin.ca/616960
and 519470541 should have level 1 as well…your code appears to be working now: a href="http://pastebin.ca/616960"http://pastebin.ca/616960/a
Does it perform well enough?
hard to tell, it's going to take a while
I'll try with a limit on the first select
If you don't want to count the current user in the list, SELECT …, COUNT(*)-1 cnt
i was just doing it in the users.level=v2.cnt -1
That's fine.
k, still trying to find a way to test this to see how long it may take…ugh
thanks everyone for the help. you've been amazing
how can you read the warning made by a query?
Query OK, 0 rows affected, 4 warnings (0.08 sec)
show warnings;
ok thanks
I just got my first job in f*cking I.T woooooooooo
congrats
)
took years and tears hahaha
can anyone help with a character set problem?
show variables like '%char%'; returns only utf8 things
try show character sets;
show create database hosting returns a utf8 database
or actually show character set;
yep got some things
now choose a character set and try SET NAMES NAME
and go read the manual
and did both earlier (set names and chapter 10 of the manual)
'\xE9\xE9\xE9A' for column 'title' at row 1 |
what character set would you like to use?
the table - the connection - the client
kostja_osipov|zz, utf8 sounds good.
ok, I'm off to bed. good luck!
if i grep char /etc/mysql/my.cnf i get also only utf8
ok thanks
good night
Hi, how do I query a database to see what charset it's using?
DESCRIBE TABLE tablename
Thanks!
Actually, it's SHOW CREATE DATABASE dbname.
Im investigating the use of SELECT SQL_CACHE. I have set query_cache_type to DEMAND. Can some tell me how long it take before the cache expires, and can than be configured?
it expires when the table is updated
it can be configured with my sql_cache_ttl patch
Can someone take a look at a query of mine and tell me why it takes so long to run (if it doesn't freeze my server) ?
I'm sure I'm doing something wrong, I've never really used JOIN before and don't understand them that well.
does mysql have a memory limit on 32bit arch?
like all apps, yeah. to 4G
* /etc/init.d/mysql: ERROR: The partition with /var/lib/mysql is too full!
that's why it is called 32bit-app
joe_-: post it on a pastebin.
what do I do now?
free up space.
thumbs it's a LVM setup
ok, then increase its size.
what a question
I think your partition is too full.
joe_-: make sure stZipData.zip and stEvents.zipcode are indexed
One sec, I think they are. I'll doublecheck.
there is plenty of space on the server
joe_-: also, running EXPLAIN on your query might prove beneficial
Huh. Now it fails.
if it was that obvious I wouldn't have asked
What's explain?
!man explain
see http://dev.mysql.com/doc/refman/5.0/en/explain.html
Thanks.
sure
how much free space does your LVM have right now?
like 250GB
joe_-: as far as I'm concerned, your JOIN is proper.
Surprising.
It was that index *slaps forehead*. There wasn't one on my zipdata table.
joe_-: ah. Beautiful.
joe_-: how long does it take to run now?
Damndest thing is, it was working last night. Today it just locked up the server.
1078ms
thumbs /var/lib = /dev/mapper/server-root 785122312 490904792 254344924 66% /
joe_-: not bad.
perhaps mysql hosting is not seeing the whole LVM
Will, zipdata is all zips + lat/lon and events has about 4k in records. So, I don't know how that speed stacks up relative.
joe_-: selecting stEvents.* is also a bad idea.
joe_-: if you can, enumerate the columns
Yeah, it's still in testing somewhat so it was kinda hacked together.
Will do.
joe_-: lastly, do run explain. It will reveal surprising results.
Saw that, thanks for the heads up on that command.
sure.
My host was about to get mad I'm sure. Shared box and I was driving CPU up to 20 on top
what about if the kernel has PAE support in it to handle more ram, will mysql be ok too?
Oh and real quick. A lot of my cols are TEXT, would it be better to set them as varchar and set the len?
thumbs got it working now just reinstalled all the mysql stuff
odd.
joe_-: yes, I believe so.
Mmkay thanks for everything. Have a good day.
joe_-: no problem.
get real hardware
PAE is crap
Is it possible to do an update that will insert a new record if there is no matching field to update? Or vice versa - an insert that will update instead if a particular clause matches?
look in the manual of INSERT ON DUPLICATE UPDATE
s/of/for/
thanks
i need help here please
don't ask to ask, just ask.
ok, this might sound simple, but i can't seem to get it to work properly. I need to pull records from table A that belong (inner join) table B, along with the total of B for A, but only grab where total = N
hello everyone…
I have a mysql 5 database hosting hosted and I have inside three tables that have latin1.swedish collation… and I need to change that collation to UTF8…. how I could that with a SQL Sentence?
thanks in advance
http://dev.mysql.com/doc/refman/5.0/en/charset.html
ALTER TABLE tbl_name
[[DEFAULT] CHARACTER SET charset_name] [COLLATE collation_name]
that's it…. lemme try it
thanks
SELECT total_votes, total_value, used_ips FROM $tableName WHERE id='$id' ==== I get the following error: 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 'WHERE id=''' at line 1
it's difficult to tell if your php variables actually contain the values you think they do. I'd say $id doesn't. Can you hard code the sql?
it is variable.. so no… but you think it is because of an unset var?
"…'WHERE id='''…" sure looks like it.
ok… thanks threnody..
add an 'print $id' somewhere on your page and see
k, thanks…
by the way, if you're not screening php webhosting input values for sql injection, your app might soon be owned…
what's the best web hosting way to raise an error in a BEFORE UPDATE trigger?
duplicate key
wow. i wish so badly we used postgresql here. that's the worst hack ever.
thanks for your help.
threnody, thanks for the tip..
np
threnody, Ok I fixed it so that $id now is set.. but, I still get the same error message
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 'WHERE id='8'' at line 1
a syntax error
is id's type a string or some kind of text?
best guess would be an error before the WHERE but thats as much as I can say without the full query
SELECT total_votes, total_value, used_ips FROM $tableName WHERE id='$id'"
$id is a variable
thats not SQL
are the variables being expanded correctly in your string?
yes…
so for instance as in the error I got id='8'
for whatever programming language you're using, trying looking at the actual sql query after that string is evaluated, but before it's executed.
I suspect $tableName isn't what you think it is. print out the actual SQL
what is the data type of the database column `id`?
also, if id isn't a string you need to lose those quotes.
aha! ToeBee you got it
I think it's that $tablename isn't resolving
you're 2 or 2 in your php coding. ;^)
nope… hehe wasn't that..
for*
what is the data type of the database column `id`?
:P atleast if it was my script…
never assume your query is getting to mysql like you expect. Always print out the entire query and make sure it runs in a mysql console
next guess is the one you still haven't answered yet
what is the data type of the field id
jp-: he refuses to answer, and must be punished.
id is a varchar
took me a sec
varchar(11)
okay
that "punished" threat was looming..
so in your php right before you open your mysql connection and execute that query, add echo $myquery; exit;
still available
and paste the result here
ToeBee, that's good advice .. ill take it..
jp-, ok
can anyone recommend a windows mysql gui other than SQLyog?
there are some mysql admin tools on the mysql website
gui
GUI tools can be found at http://dev.mysql.com/downloads/gui-tools/5.0.html phpMyAdmin at http://www.phpmyadmin.net/ and even navicat or http://www.webyog.com/en/
nah not web based. im over phpmyadmin
speaking of gui, when are they gonna pull workbench out?
and im not really enjoying using SqlYog
be a man, get putty, and hit the cli.
the joke being high class places don't sell miller high life
lol nah
don't talk about the champagne that way
so mysql have developed their own gui
those gui tools at the mysql site have been out a while
good ?
jp-, ok I have solved that problem.. it turned out that $table wasn't resolving..
very.
better than sqlyog huh
Thank you everyone for you help… !
they're alright, never used sqlyog
well
mysql administrator had a problem last time i used, quite a few
the mysql console is better than any gui tool
not sure if they've been resolved, don't mess with them really
alright loading it
i still run mysql control center
which one do i wanna use
for importing data
mysql query browser?
there is a gui tool designed just for importing data
i forget the name of it, read the descriptions
theres only 4 different programs tryina find it
i think its query browser
ok nah its not
last i recall it didn't generate tables exactly right, i had to edit pretty much every create table stable it generated to get the correct equivalent of the data source
s/stable/statement/
not sure where that came from….
it still took care of the bulk of the word though
maybe its migration toolkit
gah, work… gonna head out for a little bit. good luck.
yeah, i believe that was it
k it's hel of a weird interface
nah man this isn't it
this is like
migrating
what are you moving from?
all i ant to do
is upload & import a CSV file from my local computer to my web mysql database
trying to find a GUI that will do that
http://www.modwest.com/help/kb6-253.htmlhttp://www.modwest.com/help/kb6-253.html
whoops, http://www.modwest.com/help/kb6-253.html
quite a few sites that discuss importing csv data into mysql
google it, you don't need a gui for it, you can do it with the mysql command line client
yeah i know
i know man i dont have command line anyway
its shared hosting
my options are PHPmyADMIn or GUi
write a php script to load the csv and insert the data
then you probably has no choice of the gui too, can only use what is provided by the host
yeah i do
i'm using SQL yog currently
it sorta sucks
if you can connect gui clients remotely to it, you can probably connect with the mysql command line client from a remote computer
screw commandline man
what is command line i never even seen this before
where i download it
i think i'm gonna go have that smoke… later on folks.
EMS data import 2007 for mysql looks promising sof ar
How can I change a password for a user (as root) when I dont know that user's password?
!man set password
see http://dev.mysql.com/doc/refman/5.0/en/set-password.html
how do you represent slashes in mysql?
\
two slashes (\\) does nothing
wouldn't that cause an escape character?
hm
duno
hm oh :/
guess I have to put four slashes for it to work with c#
\\\\
do varchars use space?
if they are empty?
anyone?
when I use UPDATE, can I tell SET to append to the existing data?
nyc-h0st: I would think maybe 1 byte… not sure
yes. a varchar?
Yeah.
set field = concat(field, 'newstuff')
ok so its just 1 byte?
because microsoft sql actually allocates space for the whole string even dough its empty
nyc-h0st: hmm not sure… that was a (somewhat) educated guess
Does it work the same way for a text field?
nyc-h0st: mysql doesn't though right?
I think so
dont know whats why i'm here
:-)
:P
why is storing binary data(BLOB) so inefficent in mysql?
inefficent?
takes a long time to execute any query(even on localhost) with any size of blob data(even 5mb)
well first of all how big is your result, and second of all how big is the average blob?
the BLOB is around 5mb usually
but how big is your resultset?
it's not that big of a deal, I'm just curious
if your client is pulling 200MB
it would actually make sense
it's intended to be a local or very fast gigabit network app
In MySQL, if there's a varchar(100) does it use up more disk space if all 100 characters are used versus 2 characters, or does it use the same space because 100 characters are pre-allocated?
KlAndrews i just found a nice article on this
it allocates just what it needs
just curious if there's anything that I can do to increase the efficiency since storing the BLOB is the biggest bottleneck in the program
minimum 1 byte
maximum strlen + 1
to account for null terminaton
nyc does it allocate just what it needs for int types, too?
no
May I have the URL to the article please?
http://dev.mysql.com/doc/refman/5.0/en/data-types.html
thank you
it was actually part of the ref manual, i was looking at a page that was parsing parts ofit
this might be better http://dev.mysql.com/doc/refman/4.1/en/storage-requirements.html
oh I guess your page links to that one
anyway an integer is always 4 bytes
regardless if you say INTEGER(5) or INTEGER(10)
and actually I don't think the strings are null terminated… iirc it stores the length of the string in the first byte
might be doing it that way
yep there it is. "For example, to store a TINYTEXT value requires L characters to store the value plus one byte to store the length of the value."
it would be more efficient this way they wouldnt have to seek the null
I want to execute a select statement and add columns to the result and assign fix values to the columns. I am trying ++ SELECT *, q1 = 'dog' FROM 2050_members ++ but this is n't working. Can I do what I am trying?
'dog' as q1
I want a column called q1 to take on the value 'dog'
i know
'dog' as q1
Sorry , I understand now. Thanks. Will try
why would you want to hard code a result in a relational database?
eh, it comes in handy sometimes
Can this be done without a redundant subquery (inner AS perhaps)? UPDATE `t1` SET `c1`=(SELECT sum(`t2`.`c1`) FROM `t1` WHERE `t1`.`c2`= (SELECT `c2` FROM `t2` WHERE `c3` = %s)) WHERE `c2`= (SELECT `c2` FROM `t2` WHERE `c3` = %s)
hi, how do i change user and password for mysql
!m laststars set password
laststars see http://dev.mysql.com/doc/refman/5.0/en/set-password.html
ok since i am trying to get my coppermine photo gallery to work.. but error
anyone used EMS Mysql manger
what does this mean
Right quotation
Set a character or a number of characters, which denote unquoting in the imported string
in relation to importing a CSV file
GRANT ALL PRIVILEGES ON hlstatsx.* TO 'gameserver'@'reset4' IDENTIFIED BY 'password'; and I cant login to mysql like so: mysql -u gameserver -p, I get: ERROR 1045 (28000): Access denied for user 'gameserver'@'localhost' (using password: YES), if I specifiy the host with -h reset4 i get ERROR
2003 (HY000): Can't connect to MySQL server on 'reset4' (111). What's the word?
give the user privileges on localhost too
err byteframe rather
hi
Can anybody help me?
select host, user from mysql.user;
+——–+————+
| host | user |
+——–+————+
| reset4 | gameserver |
| reset4 | root |
+——–+————+
2 rows in set (0.00 sec)
I erased all the localhost and anyonymous accoutns, was that bad?
depends on your needs, but it sounds like you need localhost
Ill change all that I quess, but, whats the differance? Why are their those two by default?
how can we found the second highsest salary from an employee table?
you probably want to keep root@localhost
I can login into that account fine.
Should be able to use ORDER and LIMIT to acheive that?
Is there any easy way to change the host of the accounts without doing it all over again?
suppose the salary field values are 10000,10000,80000,8000,5000..I want 8000..is it possible using that?
well i guess first you'd need to filter the duplicates out with distinct or such
select * from employees where salary (selec max(salary) from employees) ORDER BY salary DESC LIMIT 1
thanks.. let me check with that..
try SELECT DISTINT column_name FROM table_name ORDER BY column_name DESC LIMIT 1,1
doing what all over again?
vice-versa, changing both of the above accoutns host's to localhost.
should I just drop the mysql.user database and rerun mysql_install_db?
no
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY 'password';
etc. etc.
You are so smart.
missing grant option ?
any luck?
accounts.
Yes ..it seems to be working ….. thanks alot …
there is no difference per se, the host is the one being used to connect from. Your identity is based on two things, the host you're connecting from and you user name.
s/you/your/
!Access Control
http://dev.mysql.com/doc/refman/5.0/en/connection-access.html
hmm
hi
I am using phpmyadmin to try to make an edit to a table, but when I press the Go button after making a change, the change does not stick
i tried restarting the server and repairing the table
I cant change a specific row in one of the tables, and it gives no error when I try
any ideas what the problem is?
anything in your logs?
j perl
anyone good with mysql replication around?
I get this message, when I run LOAD DATA FROM MASTER..
LOAD DATA FROM MASTER;
Error running query on master: Access denied; you need the RELOAD privilege for this operation
DELETE LOW_PRIORITY FROM RedeemItem JOIN Redeem ON RedeemItem.idRedeem=Redeem.idRedeem WHERE Redeem.status=0×20 ; got error sql syntax ?
Isn't the message clear enough?
And the error text 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 'JOIN Redeem ON RedeemItem.idRedeem=Redeem.idRedeem WHERE Redeem.status=0×20' at line 1
densin JOIN is a reserved word you could try `JOIN`
indeed , how do I remove a readlock on a specific table?
I do wrong syntax ? " delete table1 join table2 on ….. wherer …. " incorrent?
ALTER TABLE cubecartstore_inventoryXX CHANGE cat_id cat_id text;
trying to set column cat_id to type TEXT
and the error msg is…
my crystal ball is broken
duplicate column name cat_id
ALTER TABLE cubecartstore_inventoryXX CHANGE cat_id2 cat_id text;
FROM where
join can't use with delete ?
azuranz, you cant have two with the same name.
DELETE LOW_PRIORITY RedeemItem FROM RedeemItem JOIN Redeem ON RedeemItem.idRedeem=Redeem.idRedeem WHERE Redeem.status=0×20;
?
If you want to delete from first table only
oh! thak a lot
ok so this line alone is ok? ALTER TABLE cubecartstore_inventoryXX CHANGE cat_id cat_id text;
good day all
is anyone able to help me out with encodings please?
i have a database collated to utf8_general_ci that stores data in cp1251 (cyrillic)
weird? i know…
how do i convert it so it collates to 1251, stores the data in cp1251 and doesn't lose any of the data already in it?
Call for trouble
i don't know how that happened…
Easiest way. Create new table with same structure, but BINARY charset. Then copy the data with INSERT … SELECT .. and check if it looks OK
ok, 1 sec
Then ALTER TABLE charset = cp1251, check again and then simply remove old table and rename new to old
It's possible to do it in place, by single ALTER on original table, but having backup copy is always better
yeah, i have two, just in case ^_^
ok, i'm on the case now!
If you have copy it should be possible to simply alter the table and change the charset to cp1251
without going through binary?
Not ALTER … CONVERT, just set the charset for *each* column manually
i think i might have already tried that
ah
Yep. Changing the charset only does not touch the data. It is CONVERT which does it
salle, and the convert works on the whole table? or do i have to do it on each column manually as well?
Correct!
Now you can guess what happened
what i'm going to have to spend 1.5 hours converting tables???
Column(s) used to be with cp1251 and somehow it was altered to utf8 *without* convert
uColumn(s) used to be with cp1251 and somehow it was altered to utf8 *without* convert /u
salle, hold on
what's the correct convert syntax to use in my case - there are quite a few…..
mornin
am i in the right place for mysql help?
anyone there?
yes
someone
hello
lo
am i in the right place for mysql help?
sure, you are
i've got a query quesion
ask a question and somebody may wake up
question too 
ok
Any dev type folks here? when mysqld start it attempts to set the rlimit_stack; can I tell it not to do that?
ok here's the run down:
I've got two tables, the story table which has stories and an assign table (id, parent_id, assigned_id, rank) where i assign photos to the stories. What the query is trying to lookup is any photos for a specific date. The catch is that we want to eliminate photos from our results that have
already been assigned to the parent story we are editing.
this is a sample of my query
SELECT s.ID, s.photo_headline, a.assigned_id, a.parent_id
FROM story s
LEFT JOIN story_assign a
ON s.ID = a.assigned_id
you can try to change the code yourself and compile it, but not sure what is the implication
WHERE (a.assigned_id IS NULL OR a.parent_id != "$this_parent")
AND s.id != "$this_parent"
AND s.publish_date = "$this_parent"
stop that
pastebin
*sigh*
sorry
pastebin?
thanks; I was hoping more like a flag for mysqld
i don;t know, i never read the code, maybe there is a thread_stack settings or something
not sure if that is what you are referring to
maybe that value can be configured with an option
Anyways from the query i pasted above you can see we added in some logic so that it wouldn't find any photos that are assigned to the parent, but we didn't add in anything to filter out photos that are already assigned to another story. So essentially, if a photo is assigned to the parent story
we are editing and other stories it still shows up in the query results because it is NOT NULL and the a.parent_id != $this_parent.
anyone know how to asemble the query the right way?
thread_stack; and it does change the error I'm getting
I'm trying to get mysqld to run on a mips router
wow, firmware?
ya, with an external USB key for data storage
interesting
I'm surprised I've gotten as far as I did considering the Makefiles aren't even cross-compile compatible
and considering my level of programming is very limited
how much memory is it using or have
32MB I have; 4MB free currently
the problem is I have a crippled version of libc; so limits are not compiled in
then better to change the code
mysql has an embedded library
anyone?
does that work?
what is $this_parent ?
this parent is the story that i am editing. the query is designed to look for photos that i can assign to my parent story ($this_story)
it is both an id and a date?
i'm trying to exclude any photos from my rsults that i've already assigned to the parent
my problem is that if the photo is assigned to another story or parent AND it's asigned to my parent then it still comes up in my results instead of being excluded
I want a daemon with query caching
oh.. nope i messed that up in my example.. the date is another variable
deamon as in allowing client connect?
and stay resident in memory, yes
ok, then embedded does not work, it does not allow external conenction
does it stay in ram?
I don't see how it could
i don;t know how this will work
i cannot visualize what you are doing
bummer
maybe you can remove the whole WHERE, see the complete result and then determine how the WHERE can filter the rows for you
i'm not very experienced with left joins, right joins. that's my problem.
yeah, so remove the WHERE and see what result you get, then apply the where manually and learn from there
you should be able to see why the rows is in yuor result after doing this exercise
after understanding the problem, then try to find the solution
when i say "(a.assigned_id IS NULL OR a.parent_id != "' + $this_parent + '")" i'm missing the fact that the photo could be assigned to another story therefore it wouldn't be NULL ot would be NOT NULL then the parent_id would never = $this_parent as well
so i need to account for not only what i have in the WHERE, but another story assign as well.
or try to remove 1 of the OR condition at a time
i tried that too.
i'm wondering if this can even be done in one query
salle i need your presence :'(
another suggestion: make your script print out the entire query after evaluating the $this_parent variables then copy/paste it into a mysql console and run it there
get it right in the console, then fit it back into your script
i've done that already. i just tried to simplify my example for here.
ah
good night
see how in that one the parent_id is 21759, but the photo is already assigned to another story "21742" plus it's assigned to 21579. the assigned table just has id, parent_id, assigned_id, rank. so if the assigned_id field is not null and the parent_id is not equal to 21759 i'll still get a
result.
which i don't want to get the photo in my result at all if it's assigned to 21759
because i'm only looking for photos that aren't assigned to it
i'm not sure if left join, right join is enough for what i'm trying to do.
hey guys, what's the maximum size of the binary field?
or more specifically, how do i convert a smalltext field to binary without losing data?
thanks!
BLOB ? 16M
hoi weigon__
just ALTER TABLE … CHANGE …
moin lukas
ALTER TABLE tbl_name CHANGE col_name BINARY?
hi all, please is there a simple way to tell mysql to resynchronise itself the databases for replication? (and not having to do a backup manually, transfer it, etc)
hello
is it possible to dump only a specific subset of data?
dev.mysql.com/alter
yeah, been looking at alter, but the syntax is so ambiguous…
if you just have shutdown the slaves, just start them again
select id,name from table into outfile '/var/tmp/blah.txt'; ?
if you changed a lot it might be faster the set up the slave again with a full-backup
or dumping a column ?
or backing up?
you only need CHANGE or MODIFY
how do i tell it to change to BINARY?
actually they got unsynchronized, maybe i did a change on the slave directly (instead of doing it on the master)
there's the "LOAD DATA FROM MASTER" command , but it is deprecated
ALTER TABLE tbl CHANGE fld BLOB;
and basicly I wanted to avoid to have to use mysqldump & co
well, the optimal thing would be to be able to perform a query "select name, somethingelese from table where conditions=true" and to export the result to a csv file
you know the difference between BINARY, VARBINARY and BLOB ?
well, not in any great depth
i have a problem with encodings and salle kindly suggested i change the field to binary first, and then to the right encoding
or collation
to BLOB he meant|said
this is also documented in the manual, btw
i've been looking for something to solve it since yesterday lunchtime
basically my tables for some reason remained utf8_general_ci after i imported cp1251 data in them
http://dev.mysql.com/doc/refman/5.0/en/alter-table.html scroll down to the example with CONVERT TO CHARACTER SET
somehow they work fine, spitting out cyrillic correctly on the main host, but after i import the same tables on a different server, it all screws up
btw "ALTER TABLE jos10_content CHANGE title_alias BLOB" gives Syntax errors
again, http://dev.mysql.com/doc/refman/5.0/en/alter-table.html scroll down to the example with CONVERT TO CHARACTER SET
ok
so do i need to change to BLOB first?
and do i need to use collation?
a different collation
i would like to perform a query "select name, somethingelese from table where conditions=true" and to export the result to a csv file. any hint?
just do what the manual says
says CONVERT TO CHARACTER SET character set
but do i need to go throgh the BLOB step first?
i tried doing some conversions yesterday, but that screwed up the data.,…
did you READ the what it says ?
The preceding operation converts column values between the character sets. …
read that too
i'm using 4.1.21
i must be on a different page
http://dev.mysql.com/doc/refman/5.0/en/alter-table.html
search the page for "Warning: The preceding operation converts column values between the character sets."
ok
and read what comes after that
ah
that might be useful…
stop thinking
just follow my words
hehe, it is hard to relax!
pfiou why does mysqldump produce SQL statements containing errors
make sure to have backup before trying any charset changes, can easily cause data loss by mistake
(no ` around the fields)
there is probably an option for that
-Q
–quote-names
either or
kimseong, thanks - i'm working on a copy
been reading the "How not to ruin your day" section on hashmysql.org the other day…
hi everyone!
how many characters can a tinytext field contain?
depends to charset, it is 255 bytes
so like a varchar?
supposed to be 255 chars, but software and manual does not tally
varchar(255) is 255 char, tinytext is 255 bytes, different for 1 byte charset
I really want to be sure the client has enough space, is it wise to choose for text than?
"enough" ? how about LONGTEXT ?
what about performance? is there a difference between a text/longtext?
they both involve temptables when you read from them
odb|fidel_
hm
just limit your client to not send gigabytes
okay, than I'll stick to the "text" type..
yyyy-mm-dd, is there a way to change it to dd/mm/yyyy whenever I do a select?
only for groupbys!
DATE_FORMAT()
weigon, the internet is a bit slow here… i tried changing to blob then to text as the manul said: the text is different, but it's still garbled when i view it setting the right encoding in the browser
and you are sure that the encoding between db and app is correct ?
not really…
to simplify your setup make everything utf8
SET NAMES utf8;
it's already utf 8
Hi ! Could anyone give me a efficient PHP file/soft to dump a base on a shared host please ?
SELECT LENGTH(fld), CHAR_LENGTH(fld) FROM tbl …
if this is utf-8 (and you have !ascii chars), then CHAR_LENGTH LENGTH
how to select date string from timestamp? Y-m-d H:i:s.. thanks
DATE_FORMAT
I know DATE_FORMAT but isn't it possible to create a trigger/procedure that changes the default format automatically ?
there are no SELECT triggers
so it's not possible?
write a VEW
VIEW
Would using GFS in redhat be a good option for providing high availability for mysql or is replication still my best bet?
weigon, should i be using CONVERT instead of CHANGE? because that procedure is not doing the data any good…
check out DRDB
pfff
thanks !
replication synchornisation sucks a lot
Both are sortof different approaches, if you want file systme level redundancy check out DRBD
huhmz, ZFS?
DRBD this way
but none the less Replication vs DRBD sortof achieve different goals, if HA is your only goal a DRBD/Heartbeat based solution is probably what you want
i just heard it's _really_ good
ZFS isn't an interesting solution for HA database
it's slow for that and QFS/UFS work just as well with cluster
hi, i understood that with the EXPLAIN statement, you could check if your table needed indexes.
But i'm not really sure what to read from the output of the query i fire off
Redhat does not have ZFS
SELECT DATE_FORMAT( FROM_UNIXTIME(1081548000), FORMAT_AS_SEEN_IN_http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html
weigon thanks
i have two servers. I want them to failover to each other gracefully without needing interaction from me
http://www.pastebin.ca/617491 this is my output from the explain statement
and can i print the distinction in days or secconds between two timestamps?
select
you could divide, like: (time_end - time_start) / 24 * 60 * 60
hi experts
Thumann, thats not using indexes
no, because it says 'all' right?
i upgraded my mysql server from old 4.0.x to 5.0.38 now some of my content is garbled. Is that because the UTF-8 Encoding?
Thumann possible keys null
ah yes, silly me
yes, probably
does it suggest what indexes i should make?
is there a way in MySQL you can have a many-to-many look up table and have 3 attributes where only 2 are ever used..so for example, if you have customerId as the main references but then s/he can either be linked to elementAUid or ElementBUid… but not both?
oh oh. Is there a helper program that can correct this issue? something like mysql-iconv iso-8859-1 utf-8 DB?
weigon, could i do a direct convert from utf8 to cp1251?
withouth it actually messing with the characters?
depends to what the data is in the field now
because the caracters that appear in the database when it is viewed in UTF8 are the ones that represent cyrillic when they are normally viewed in some sort of western encoding
if the data matches the charset of the column, it is easy, if not, thats tricky
you can always convert to binary then to proper charset
domas are there examples out there? Because I really do not want to mess my content up
select hex(col) from table, then determine what is actually stored
domas, i've dones that on several coulmns now, but the data gets converted to gibberish when i try to CHANGE to the proper character set
the exact steps is in the manual
backup first
do you have a section number? Then i will immediately dive into reading
the character set chapter of the manual, something like convert from 4.0 to 4.1
thx
it is in the 5.0 manual also, correct?
if not check the 4.1, not too sure
I want to keep track of when someone updated a table… do I need to write a trigger/procedure for that?
trigger will be good, store the changes as audit log into another table
audit log?
create your own audit log
i mean your own table
ah and store the changes in there?
yes
is it possible to write a trigger for any DB update/delete/insert ?
can even track the user and other information like time, old and new data
not really, create a stored procedure and create triggers to call the sp
sp?
ah
but I need to create the trigger for each table?
yes
okay
maybe you want to take a look at mysql proxy
and kimseong let say I want to keep track of all update-queries, is that possible?
in such a way that I insert a trigger that stores the update/insert as string in another table?
or the binary log has them, but no extra information like which user
it's for a web application, so I only have one user
maybe can get the user if you use the general log
then use the binary log
so it's not possible to "get" the insert/update query in a procedure and store that as text in another table?
4' i'm pretty uninterested in formatting it into a mysql timestamp. is there some mysql function that will do it for
that is already a mysql timestamp
when i do select date from table i see a bunch of values like this: t
oops
20070607164134
are you sure it's really a TIMESTAMP column?
timestamp(14)
weird. well, anyway, you can insert dates in that format into it
okay thanks
insert into * from table
aaaarrgghhh!
I know this is totally unrelated , but is it possible to connect to mssql from linux .. using a console or such
?
mssql'
not mysql
maybe microsoft knows.
ALTER TABLE jos10_content MODIFY title BINARY(255);
oops
http://www.easysoft.com/cgi-bin/productdownload.cgi?p=30
heh
ok thanks
Eugene_:
hehe
feck!!! fecking encodings!!!
I got a question about my query seen in http://phpfi.com/249439 - In the second to last line I cant group by on one column but I can on another - why is that?
whats the name of the client?
that site requires registration
yeah, no idea…
http://www.faqs.org/docs/Linux-mini/MSSQL6-Openlink-PHP-ODBC.html
http://swik.net/User:sallubhai420/blog+about+Linux,+Windows,+Programming,+Server+Administration/Installing+MS+SQL+Server+Client+Library+on+Linux%2FUnix/bcadi
when I want to create a stored-routine that updates the date of a table when someone inserts/updates data, do I need a stored-function or stored-procedure?
http://www.linuxjournal.com/article/5732
someone?
trigger
yes, but a trigger uses a stored proc/function right?
not necessarily
ok
whats whata drbd+hb setup gives you
0)?temp,0;" possible in sql?
im coming from a java/c++ background
it is, see if-statement in the docs
tyvm
.. /usr/bin/mysqladmin: connect to server at 'localhost' failed — i keep getting that..what could be wrong?
dotz server not running? (whats the full message)
im thinking either the server isnt running or username/password
i'm trying to do a /etc/init.d/mysql start
which distro are you using?
2 mdserver3 /etc/init.d/mysql[28271]: 0 processes alive and '/usr/bin/mysqladmin –defaults-file=/etc/mysql/debian.cnf ping' resulted
debian etch
ah
no idea with debian sorry
u could try asking in the #debian channel if there is one on freenode
how is rdrbd diffrent from GFS?
drbd is basically raid1 over the network
and then you run ext3 on top of it, one fails, the other one picks it up and when the other node comes back they sync the data back up
GFS is a clustered filesystem, where all nodes can access it, far more complex
likely to be much slower
(dont know that for a fact, but at a guess most likely)
but the semantics to do file locking over a clustered fs are far more compelx and laggy
ok
whats the different between using single and double quotes
i have a working mysql machine, which i want to set up exactly the same on another machine.. can i just copy the /var/lib/mysql over after installing mysql?
as long as you shut the first server down first, and you are going to the same version, thats fine
What should I choose, InnoDB or MyISAM?
in rdrdb only one node can access it?
when I create a BEFORE INSERT trigger, can I add values to the insert?
correct
of course even with GFS you can still only start the mysql server on one server so this is not really a disadvantage..
Fiskah_, depends what you want to do with your db
what other files should i copy too?
why can i only start on one server?
with gfs
you can't have 2 servers running off the same data files…
they would stomp over each other and corrupt the data
thats not quite 100% true theres some provision for doing it in MyISAM, but you don't want to do that anyway
if you want more than 1 server active running mysqld with the same data you want replication
if you just want the second server to take over and sit inactive otherwise, you probably want drbd +heartbeat
oh so it would only be good for HA, but load balancing is a no no with both gfs and drdb in any case?
ok i see
:
sorry for the noob question
but anyone know whats the difference between single and double quotes when doing an INSERT
I need help with my query - it wont let me RIGHT JOIN a temporary table http://phpfi.com/249445
(a tutorial link is an acceptable answer 
)
5, can I use a date
quotes
Use ` around identifiers (database/table/column/alias names) and ' around strings and dates. MySQL does allow " for strings, but ANSI standard uses " for identifiers (which you can enable with ANSI QUOTES option)
Fanty just invert and use left
so what is the recommend action with a string that has a single quote ?
convert it to \' ?
yeh if you want that you need replication
luur yes or use real_escape_string
ah nice
thanks
oh while im here,
is it safe to store html in a database
ish
dosent change anything - and it should be a RIGHT JOIN when I want one entry in the result for each row in the table I join it with shouldn't it?
if its nothing important then you should be fine.
although i'd encrypt it using md5
Fanty no inner
is there a way to select a previous row? something like "SELECT temp, prev.temp, FROM weather;"
eth01 speaking to me
yes
md5 encryption?
i was thinking about just running a htmlentities on the string and putting it in
there really ought the be a sollution that is both HA and HP and requires no interaction when a node fails 
so how does gfs and drdb differ in capabilities? what can gfs do that drdb cannot?
I'm creating a web application where users can login, using a custom login system I created for the application. Can I use / define a user ID that I use in triggers to update fields like: table.created_by table.updated_by ?
somebody?
I could set the ID of the user in a variable .. and use that in a trigger right?
hmmm nobody huh… 
term for correctly shutting down a database
starts with sq i think?
http://pastebin.ca/617574
The LEFT JOIN produces multiple instances of the cgs_order rows when the TxVendorCode appears more than once in cgs_payments
I only ever want one instance of each cgs_order row to be returned, whenever there is a cgs_payments.TxType of "PAYMENT" AND (cgs_payments.Status = 'OK' OR cgs_payments.Status = '3DAUTH')
weigon, hey man, you still around? there is really no wayi can get it to work…
I'd like a way of Limiting cgs_order to only ever return one row
hi. got a problem - i did: grant all on jabberd.* to 'jabberd@%' identified by 'password'; but when i try to connect using mysql -ujabberd -ppassword -Djabberd i get: ERROR 1045 (28000): Access denied for user 'jabberd'@'localhost' (using password: YES)
i know that this information will probably not be enough for fixing the problem, but what can i do to get more information and eventually fix the problem?
5.0.32-Debian_7etch1-log Debian etch distribution
how about 'jabberd'@'%', or 'jabberd'@%?
checking.
great. works.
thanks a lot tibyke
cool
hmm .. are "user" and "db" the only tables that i have to clean after this failed grant?
i mean - i have bad data there now.
man drop user
*sigh*
ah. sorry.
!man drop user
see http://dev.mysql.com/doc/refman/5.0/en/drop-user.html
see a href="http://dev.mysql.com/doc/refman/5.0/en/drop-user.html"http://dev.mysql.com/doc/refman/5.0/en/drop-user.html/a
\oXo/
thanks. i seem to have it now working.
great
what is another term for "rundown" in terms of database?
5 10 Connect Access denied for user 'debian-sys-maint'@'localhost' (using password: YES)
dotz, cant you read? let me spell it for ya…
i'm getting a different error../usr/bin/mysqladmin: connect to server at 'localhost' failed
keep saying i can't connect to local server
my /etc/mysql/debian-start is missing.. how can i retrive it back
how do i check where the database is stored
Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock'
it's stored where you installed it 
you may want to use find
it should also say that in my.cnf
