error in instructions to set mysql root password?
When I install and start the MySQL server on a new machine, it outputs: PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! To do so, start the server, then issue the following commands: /usr/bin/mysqladmin -u root password 'new-password' /usr/bin/mysqladmin -u root -h sls-ce12p12.dca2.superbservers.com password 'new-password' However if I try executing both of these commands as the instructions say, I get an error on the second command (in the text below, I have replaced my sample password with 'america'; I wouldn't really use that as my password): [r...@sls-ce12p12 ~]# /usr/bin/mysqladmin -u root password 'america' [r...@sls-ce12p12 ~]# /usr/bin/mysqladmin -u root -h sls-ce12p12.dca2.superbservers.com password 'america' /usr/bin/mysqladmin: connect to server at 'sls-ce12p12.dca2.superbservers.com' failed error: 'Access denied for user 'root'@'localhost' (using password: NO)' Knowing now what the syntax means, it seems clear why the second command fails. The first command executes with no authentication required because no password has been set for the root user yet. But by the time you execute the second command, the root user password has already been set, so if the MySQL server sees the second command as an attempt to set the 'root'@'localhost' password, it will fail because you're not authenticated. Now, I experimented some more on different machines, and on some machines, the second command gives an error and on other machines it doesn't. On all new mysql installations, there are separate entries created in the mysql 'user' table for 'root'@'localhost' and 'root'@'hostname', where hostname is the hostname of the machine. The deciding factor that determines whether or not the second mysqladmin command gives an error, appears to be, whether hostname on that machine resolves to 127.0.0.1, or resolves to the public IP address of the machine instead. IF hostname RESOLVES TO 127.0.0.1: then when you run the command: /usr/bin/mysqladmin -u root -h sls-ce12p12.dca2.superbservers.com password 'america' the MySQL server sees you connecting to IP address 127.0.0.1. So, rather than trying to update the 'root'@'sls-ce12p12.dca2.superbservers.com' password, it thinks you're trying to update the 'root'@'localhost' password. Of course this password has already been set by the previous command, so the second command fails. IF hostname RESOLVES TO THE PUBLIC IP ADDRESS INSTEAD: then when you run the command: /usr/bin/mysqladmin -u root -h sls-ce12p12.dca2.superbservers.com password 'america' the MySQL server sees you connecting to the public IP address, resolves that to the public hostname, and thus thinks you are trying to update the 'root'@'sls-ce12p12.dca2.superbservers.com' entry in the user table. And it allows that, because that password is still blank. So for all users who are in the former situation (where hostname resolves to 127.0.0.1), it appears that trying to run the second mysqladmin command will give them an error. It's not a catastrophe because at that point the biggest security hole -- the blank root password -- has been fixed. But it's still inadvertently training the user that security instructions are sometimes unreliable, or that sometimes in order to get things working, you have to skips parts of the security instructions and cut corners (which could be a bad habit if they ever end up skipping over something important). Plus, if an unprivileged user ever did figure out how to connect as 'root'@'hostname', they would be granted full MySQL root privileges, since that account is still unprotected. The upshot of all this is: Would it be a good idea for the MySQL installation program, rather than always telling the user: PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! To do so, start the server, then issue the following commands: /usr/bin/mysqladmin -u root password 'new-password' /usr/bin/mysqladmin -u root -h sls-ce12p12.dca2.superbservers.com password 'new-password' to instead test whether 'hostname' resolves to 127.0.0.1, and if it does, to skip giving the user the second command? Or to give the user some other command that would set the 'root'@'hostname' password without giving an error? -Bennett -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How to compare 2 columns in different tables.
John Furlong wrote: My question is, can the argument in AGAINST() refer to a column in another table or does it have to be a specific string you are searching for? If the MATCH() function won't work, any suggestions on how else to compare table1.name against table2.name? The columns are defined as VARCHAR. Does anyone have an answer to this? I, too, would like to know how to use fulltext to compare data between two columns (as opposed to comparing data between a column and a pre-defined string). Is there any way to do this? Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How to compare 2 columns in different tables.
You may be able to get what you need with three queries. Join the tables on the column of interest. By default you'll get a list of the matches. Then you can modify the join by adding a WHERE clause that looks for table2.nameis null, and again by modifying the WHERE clause so it looks for table1.name is null. hth, Arthur On Tue, Sep 15, 2009 at 6:52 AM, Mark Goodge m...@good-stuff.co.uk wrote: John Furlong wrote: My question is, can the argument in AGAINST() refer to a column in another table or does it have to be a specific string you are searching for? If the MATCH() function won't work, any suggestions on how else to compare table1.name against table2.name? The columns are defined as VARCHAR. Does anyone have an answer to this? I, too, would like to know how to use fulltext to compare data between two columns (as opposed to comparing data between a column and a pre-defined string). Is there any way to do this? Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=fuller.art...@gmail.com -- Semi-retired SQL guru, interested in interesting projects not YAFOES (yet another order entry system).
Re: How to compare 2 columns in different tables.
Hello Mark and John, Mark Goodge wrote: John Furlong wrote: My question is, can the argument in AGAINST() refer to a column in another table or does it have to be a specific string you are searching for? If the MATCH() function won't work, any suggestions on how else to compare table1.name against table2.name? The columns are defined as VARCHAR. Does anyone have an answer to this? I, too, would like to know how to use fulltext to compare data between two columns (as opposed to comparing data between a column and a pre-defined string). Is there any way to do this? Mark The manual answers this question quite clearly. From the page: http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html Full-text searching is performed using MATCH() ... AGAINST syntax. MATCH() takes a comma-separated list that names the columns to be searched. AGAINST takes a string to search for, and an optional modifier that indicates what type of search to perform. The search string must be a literal string, not a variable or a column name. Unfortunately, you cannot use the column name directly. However you may be able to construct and execute a search query using the PREPARED STATEMENT commands using the data from your other table. http://dev.mysql.com/doc/refman/5.0/en/sql-syntax-prepared-statements.html -- Shawn Green, MySQL Senior Support Engineer Sun Microsystems, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
MySQL-Client Segfaults
Hi everybody,
I'm currently installing MySQL 5.0.85 from source on Debian Lenny, but do
expirience
Problems with the MySQL-Client.
# uname -a
Linux corleone 2.6.26-2-amd64 #1 SMP Wed Aug 19 22:33:18 UTC 2009 x86_64
GNU/Linux
# The following configure-options are used:
./configure --prefix=/usr/local/mysql --with-charset=latin1
--with-mysqld-user=mysql --with-comment=x86_64-SMP --without-debug
--enable-thread-safe-client --with-client-ldflags=-all-static
--with-mysqld-ldflags=-all-static --disable-shared
I'm able to start the database successfully *but* I've I try to connect with the
MySQL-Client - the MySQL-Client dies with a segfault as follows:
server:#/usr/local/mysql/bin/mysql -u root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.84 GOFRESH-x86_64-SMP
Segmentation fault
Any help greatly appreciated.
Thank you,
Werner
PS: Here is the strace of the mysql-Call
corleone:/usr/local/mysql/bin# strace /usr/local/mysql/bin/mysql -u root
execve(/usr/local/mysql/bin/mysql, [/usr/local/mysql/bin/mysql, -u,
root], [/* 15 vars */]) = 0
uname({sys=Linux, node=corleone, ...}) = 0
brk(0) = 0x2647000
brk(0x2647f70) = 0x2647f70
arch_prctl(ARCH_SET_FS, 0x26478b0) = 0
brk(0x2668f70) = 0x2668f70
brk(0x2669000) = 0x2669000
ioctl(0, SNDCTL_TMR_TIMEBASE or TCGETS, {B9600 opost isig icanon echo ...}) = 0
ioctl(1, SNDCTL_TMR_TIMEBASE or TCGETS, {B9600 opost isig icanon echo ...}) = 0
dup(1) = 3
close(3)= 0
stat(/etc/my.cnf, {st_mode=S_IFREG|0644, st_size=2212, ...}) = 0
open(/etc/my.cnf, O_RDONLY) = 3
fstat(3, {st_mode=S_IFREG|0644, st_size=2212, ...}) = 0
mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) =
0x7f611564a000
read(3, # The following options will be p..., 4096) = 2212
read(3, ..., 4096)= 0
close(3)= 0
munmap(0x7f611564a000, 4096)= 0
stat(/usr/local/mysql/etc/my.cnf, 0x7fff1d649b30) = -1 ENOENT (No such file
or directory)
stat(/root/.my.cnf, 0x7fff1d649b30) = -1 ENOENT (No such file or directory)
socket(PF_FILE, SOCK_STREAM, 0) = 3
fcntl(3, F_SETFL, O_RDWR|O_NONBLOCK)= 0
connect(3, {sa_family=AF_FILE, path=/var/run/nscd/socket...}, 110) = -1
ENOENT (No such file or directory)
close(3)= 0
socket(PF_FILE, SOCK_STREAM, 0) = 3
fcntl(3, F_SETFL, O_RDWR|O_NONBLOCK)= 0
connect(3, {sa_family=AF_FILE, path=/var/run/nscd/socket...}, 110) = -1
ENOENT (No such file or directory)
close(3)= 0
open(/etc/nsswitch.conf, O_RDONLY)= 3
fstat(3, {st_mode=S_IFREG|0644, st_size=475, ...}) = 0
mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) =
0x7f611564a000
read(3, # /etc/nsswitch.conf\n#\n# Example ..., 4096) = 475
read(3, ..., 4096)= 0
close(3)= 0
munmap(0x7f611564a000, 4096)= 0
open(/etc/ld.so.cache, O_RDONLY) = 3
fstat(3, {st_mode=S_IFREG|0644, st_size=14229, ...}) = 0
mmap(NULL, 14229, PROT_READ, MAP_PRIVATE, 3, 0) = 0x7f6115647000
close(3)= 0
access(/etc/ld.so.nohwcap, F_OK) = -1 ENOENT (No such file or directory)
open(/lib/libnss_db.so.2, O_RDONLY) = -1 ENOENT (No such file or directory)
stat(/lib, {st_mode=S_IFDIR|0755, st_size=4096, ...}) = 0
open(/usr/lib/libnss_db.so.2, O_RDONLY) = -1 ENOENT (No such file or
directory)
stat(/usr/lib, {st_mode=S_IFDIR|0755, st_size=12288, ...}) = 0
open(/lib/x86_64-linux-gnu/libnss_db.so.2, O_RDONLY) = -1 ENOENT (No such
file or directory)
stat(/lib/x86_64-linux-gnu, {st_mode=S_IFDIR|0755, st_size=4096, ...}) = 0
open(/usr/lib/x86_64-linux-gnu/libnss_db.so.2, O_RDONLY) = -1 ENOENT (No such
file or directory)
stat(/usr/lib/x86_64-linux-gnu, {st_mode=S_IFDIR|0755, st_size=4096, ...}) = 0
munmap(0x7f6115647000, 14229) = 0
open(/etc/ld.so.cache, O_RDONLY) = 3
fstat(3, {st_mode=S_IFREG|0644, st_size=14229, ...}) = 0
mmap(NULL, 14229, PROT_READ, MAP_PRIVATE, 3, 0) = 0x7f6115647000
close(3)= 0
access(/etc/ld.so.nohwcap, F_OK) = -1 ENOENT (No such file or directory)
open(/lib/libnss_files.so.2, O_RDONLY) = 3
read(3,
\177ELF\2\1\1\0\0\0\0\0\0\0\0\0\3\0\0\1\0\0\0\340\37\0\0\0\0\0\0@..., 832) =
832
fstat(3, {st_mode=S_IFREG|0644, st_size=47520, ...}) = 0
mmap(NULL, 2143528, PROT_READ|PROT_EXEC, MAP_PRIVATE|MAP_DENYWRITE, 3, 0) =
0x7f611543b000
mprotect(0x7f6115445000, 2097152, PROT_NONE) = 0
mmap(0x7f6115645000, 8192, PROT_READ|PROT_WRITE,
MAP_PRIVATE|MAP_FIXED|MAP_DENYWRITE, 3, 0xa000) = 0x7f6115645000
close(3)= 0
access(/etc/ld.so.nohwcap, F_OK) = -1 ENOENT (No such file or directory)
open(/lib/libc.so.6, O_RDONLY)= 3
read(3,
Source-Install 5.0.85 on Debian Lenny: MySQL-Client segfaults
Hi everybody,
I'm currently installing MySQL 5.0.85 from source on Debian Lenny, but do
expirience
Problems with the MySQL-Client.
# uname -a
Linux corleone 2.6.26-2-amd64 #1 SMP Wed Aug 19 22:33:18 UTC 2009 x86_64
GNU/Linux
# The following configure-options are used:
./configure --prefix=/usr/local/mysql --with-charset=latin1
--with-mysqld-user=mysql --with-comment=x86_64-SMP --without-debug
--enable-thread-safe-client --with-client-ldflags=-all-static
--with-mysqld-ldflags=-all-static --disable-shared
I'm able to start the database successfully *but* I've I try to connect with the
MySQL-Client - the MySQL-Client dies with a segfault as follows:
server:#/usr/local/mysql/bin/mysql -u root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.84 GOFRESH-x86_64-SMP
Segmentation fault
Any help greatly appreciated.
Thank you,
Werner
PS: Here is the strace of the mysql-Call
corleone:/usr/local/mysql/bin# strace /usr/local/mysql/bin/mysql -u root
execve(/usr/local/mysql/bin/mysql, [/usr/local/mysql/bin/mysql, -u,
root], [/* 15 vars */]) = 0
uname({sys=Linux, node=corleone, ...}) = 0
brk(0) = 0x2647000
brk(0x2647f70) = 0x2647f70
arch_prctl(ARCH_SET_FS, 0x26478b0) = 0
brk(0x2668f70) = 0x2668f70
brk(0x2669000) = 0x2669000
ioctl(0, SNDCTL_TMR_TIMEBASE or TCGETS, {B9600 opost isig icanon echo ...}) = 0
ioctl(1, SNDCTL_TMR_TIMEBASE or TCGETS, {B9600 opost isig icanon echo ...}) = 0
dup(1) = 3
close(3)= 0
stat(/etc/my.cnf, {st_mode=S_IFREG|0644, st_size=2212, ...}) = 0
open(/etc/my.cnf, O_RDONLY) = 3
fstat(3, {st_mode=S_IFREG|0644, st_size=2212, ...}) = 0
mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) =
0x7f611564a000
read(3, # The following options will be p..., 4096) = 2212
read(3, ..., 4096)= 0
close(3)= 0
munmap(0x7f611564a000, 4096)= 0
stat(/usr/local/mysql/etc/my.cnf, 0x7fff1d649b30) = -1 ENOENT (No such file
or directory)
stat(/root/.my.cnf, 0x7fff1d649b30) = -1 ENOENT (No such file or directory)
socket(PF_FILE, SOCK_STREAM, 0) = 3
fcntl(3, F_SETFL, O_RDWR|O_NONBLOCK)= 0
connect(3, {sa_family=AF_FILE, path=/var/run/nscd/socket...}, 110) = -1
ENOENT (No such file or directory)
close(3)= 0
socket(PF_FILE, SOCK_STREAM, 0) = 3
fcntl(3, F_SETFL, O_RDWR|O_NONBLOCK)= 0
connect(3, {sa_family=AF_FILE, path=/var/run/nscd/socket...}, 110) = -1
ENOENT (No such file or directory)
close(3)= 0
open(/etc/nsswitch.conf, O_RDONLY)= 3
fstat(3, {st_mode=S_IFREG|0644, st_size=475, ...}) = 0
mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) =
0x7f611564a000
read(3, # /etc/nsswitch.conf\n#\n# Example ..., 4096) = 475
read(3, ..., 4096)= 0
close(3)= 0
munmap(0x7f611564a000, 4096)= 0
open(/etc/ld.so.cache, O_RDONLY) = 3
fstat(3, {st_mode=S_IFREG|0644, st_size=14229, ...}) = 0
mmap(NULL, 14229, PROT_READ, MAP_PRIVATE, 3, 0) = 0x7f6115647000
close(3)= 0
access(/etc/ld.so.nohwcap, F_OK) = -1 ENOENT (No such file or directory)
open(/lib/libnss_db.so.2, O_RDONLY) = -1 ENOENT (No such file or directory)
stat(/lib, {st_mode=S_IFDIR|0755, st_size=4096, ...}) = 0
open(/usr/lib/libnss_db.so.2, O_RDONLY) = -1 ENOENT (No such file or
directory)
stat(/usr/lib, {st_mode=S_IFDIR|0755, st_size=12288, ...}) = 0
open(/lib/x86_64-linux-gnu/libnss_db.so.2, O_RDONLY) = -1 ENOENT (No such
file or directory)
stat(/lib/x86_64-linux-gnu, {st_mode=S_IFDIR|0755, st_size=4096, ...}) = 0
open(/usr/lib/x86_64-linux-gnu/libnss_db.so.2, O_RDONLY) = -1 ENOENT (No such
file or directory)
stat(/usr/lib/x86_64-linux-gnu, {st_mode=S_IFDIR|0755, st_size=4096, ...}) = 0
munmap(0x7f6115647000, 14229) = 0
open(/etc/ld.so.cache, O_RDONLY) = 3
fstat(3, {st_mode=S_IFREG|0644, st_size=14229, ...}) = 0
mmap(NULL, 14229, PROT_READ, MAP_PRIVATE, 3, 0) = 0x7f6115647000
close(3)= 0
access(/etc/ld.so.nohwcap, F_OK) = -1 ENOENT (No such file or directory)
open(/lib/libnss_files.so.2, O_RDONLY) = 3
read(3,
\177ELF\2\1\1\0\0\0\0\0\0\0\0\0\3\0\0\1\0\0\0\340\37\0\0\0\0\0\0@..., 832) =
832
fstat(3, {st_mode=S_IFREG|0644, st_size=47520, ...}) = 0
mmap(NULL, 2143528, PROT_READ|PROT_EXEC, MAP_PRIVATE|MAP_DENYWRITE, 3, 0) =
0x7f611543b000
mprotect(0x7f6115445000, 2097152, PROT_NONE) = 0
mmap(0x7f6115645000, 8192, PROT_READ|PROT_WRITE,
MAP_PRIVATE|MAP_FIXED|MAP_DENYWRITE, 3, 0xa000) = 0x7f6115645000
close(3)= 0
access(/etc/ld.so.nohwcap, F_OK) = -1 ENOENT (No such file or directory)
open(/lib/libc.so.6, O_RDONLY)= 3
read(3,
FW: cache-panel DB issue
I am trying to move my cache into ndb but when I use the ndb engine the query time is 4 min versus myisam which is 7 seconds. This is due to the fact it is doing a full table scan on ndb and an index on myisam. I was hoping someone could help me fix this or at least explain why this is happening. I have included the select statements , the select statement and the explain select for both the ndb and the myisam. Cache database (ndb) Select distinct t0.panelId from cache1 t0, cache2 t1 where t0.panelid=t1.panelid; (282.54 s) CREATE TABLE `panel`.`cache1` ( `panelId` int(11) DEFAULT NULL, KEY `panelid` (`panelId`) ) ENGINE=ndbcluster DEFAULT CHARSET=utf8; CREATE TABLE `panel`.`cache2` ( `panelId` int(11) DEFAULT NULL, KEY `panelid` (`panelId`) ) ENGINE=ndbcluster DEFAULT CHARSET=utf8; Explain select distinct t0.panelId from cache1 t0, cache2 t1 where t0.panelid=t1.panelid; ++-+---+--+---+-+-+- -++-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---+--+---+-+-+- -++-+ | 1 | SIMPLE | t0| ALL | panelid | NULL| NULL| NULL | 534063 | | | 1 | SIMPLE | t1| ref | panelid | panelid | 5 | cache.t0.panelId | 1 | Using where | ++-+---+--+---+-+-+- -++-+ Panel database (myisam) select distinct t0.panelId from cache3 t0, cache4 t1 where t0.panelid=t1.panelid; (6.5 s) CREATE TABLE `panel`.`cache3` ( `panelId` int(11) DEFAULT NULL, KEY `panelid` (`panelId`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; CREATE TABLE `panel`.`cache4` ( `panelId` int(11) DEFAULT NULL, KEY `panelid` (`panelId`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; explain select distinct t0.panelId from cache3 t0, cache4 t1 where t0.panelid=t1.panelid; ++-+---+---+---+-+-+ --+++ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---+---+---+-+-+ --+++ | 1 | SIMPLE | t0| index | panelid | panelid | 5 | NULL | 534053 | Using index; Using temporary | | 1 | SIMPLE | t1| ref | panelid | panelid | 5 | panel.t0.panelId | 1 | Using where; Using index; Distinct | ++-+---+---+---+-+-+ --+++ Thanks Joshua Gordon
