Re: Help with formatting of 1:n relationship
I think what you are looking for is something like this: - SELECT CONCAT(A.FIELD1,',',GROUP_CONCAT(B.FIELD2)) FROM TABLE1 A LEFT JOIN TABLE2 B ON A.ID=B.ID_TABLE1 - Cheers Claudio 2009/1/26 Baron Schwartz ba...@xaprb.com Hi, On Mon, Jan 26, 2009 at 11:29 AM, Vikram Vaswani bacc...@vsnl.com wrote: Hi Jerry Thanks for your input on this! I'm using an excel library that accepts a SELECT as input and generates an XLS file with the records as output. I'd like to use this where possible. However I don't know if it's possible to write a SELECT that compresses a 1:n relationship into a single row. Is this possible, and if yes, could someone show me how? Or could you suggest another way in which I could achieve the above required output? Does whatever tool you are using have any place where you can manipulate the data between the SELECT and the creation of the XLS? If not,I think you need a user-defined function for this, or perhaps you can do it with a user-defined procedure. Unfortunately the tool doesn't let me manipulate the data. It simply reads the result set and pops each field into a separate column in the XLS. So any formatting I do has to be part of the SELECT. I did consider a procedure but the problem is that the client is still using MySQL 4.x, which afaik doesn't support stored procedures. An upgrade is not something they can do at this point, as they're using s shared host so the server isn't really under their control. I think what you're really looking for is a pivot table or crosstab report in SQL itself, right? Since you're manipulating this data in Excel, maybe you can do it there, because honestly it's better suited for that than MySQL is. But, if you need to do it in SQL, you can search the list archives -- there is something about it pretty much every week or so :) -- Baron Schwartz, Director of Consulting, Percona Inc. Our Blog: http://www.mysqlperformanceblog.com/ Our Services: http://www.percona.com/services.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=claudio.na...@gmail.com
Re: Help with formatting of 1:n relationship
I don't think so unfortunately. He says in his first mail: I'm using an excel library that accepts a SELECT as input and generates an XLS file with the records as output. So, probably csv is not going to help him, which is a shame :) Walter OlinData: Professional services for MySQL Support * Consulting * Administration http://www.olindata.com On Tue, Jan 27, 2009 at 10:20 AM, Claudio Nanni claudio.na...@gmail.com wrote: I think what you are looking for is something like this: - SELECT CONCAT(A.FIELD1,',',GROUP_CONCAT(B.FIELD2)) FROM TABLE1 A LEFT JOIN TABLE2 B ON A.ID=B.ID_TABLE1 - Cheers Claudio -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
UPDATE ... where max(datecolumn)
I have a table like this: ID PRODUCT DATEORDERED 152005-02-18 222008-03-13 322008-11-21 462009-01-21 A field has been added for current order to this table: ID PRODUCT DATEORDERED FLAGCURRENT I need to update all records in the table (about 400,000) so that the most current order has FLAGCURRENT=1 (else 0). Is there an optimal way of doing this with a single query?
Broken Database issue - tables vanishing
Newbie question Something - or maybe me is breaking my database. this happened before with the same DB - so,using phpMyAdmin, I just rebuilt the structure again and all was fine for a few minutes - then I created a basic user with Privileges for the db and within a few minutes the DB broke with the issue below. Any ideas what happened or how to get it back? (since it's brand new, I have no backups but will in the future) Thanks in advance - dave using: - 10.4.11 OSX - MAMP 1.72 http://mamp.info/en/index.php - coldfusion 8 dev edition - phpMyAdmin 2.11.7.1 on the phpMyAdmin - page showing localhostmydatabasestructure table - action - then columns: records type collation - size - overhead all show: in use - when i click on a table it says: #1146 - Table 'mydatabase.contacts' doesn't exist hmmm... -- Thanks - RevDave Cool @ hosting4days . com [db-lists 09] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Autoconf --basedir
Anyone knows how to figure out the mysql basedir string using Autoconf and then pass it to Automake for an embedded application? Thanks in advance, -Alex
Mysqld fails to start
Hello List, mysqld fails to start, and emits the following error messages in /var/log/mysqld.log 090127 10:00:30 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql /usr/libexec/mysqld: Table 'mysql.plugin' doesn't exist 090127 10:00:30 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it. 090127 10:00:30 InnoDB: Operating system error number 13 in a file operation. InnoDB: The error means mysqld does not have the access rights to InnoDB: the directory. InnoDB: File name ./ibdata1 InnoDB: File operation call: 'open'. InnoDB: Cannot continue operation. 090127 10:00:30 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended So, I tried to run mysql_upgrade: # mysql_upgrade Looking for 'mysql' as: mysql Looking for 'mysqlcheck' as: mysqlcheck Running 'mysqlcheck'... mysqlcheck: Got error: 2002: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) when trying to connect FATAL ERROR: Upgrade failed So, it seems like a chicken and egg thing??? Cannot run mysql_upgrade bcause the server is not running. Cannot run the server because I must first run mysql_upgrade. PS: I know nothing about configuring and setting up mysql. Amarok2 needs a running mysqld in order to organize music tracks collections ... etc. I need help to get mysqld started and running and to add users that are allowed to access and modify their databases. Could someone send me a set of user commands to get thing going? These are all the mysql related packages on my system: mysql-5.1.30-1.fc7.remi mysql-libs-5.1.30-1.fc7.remi sqlite-3.4.2-1.fc7 libdbi-dbd-pgsql-0.8.1a-2.fc7 sqlite-3.4.2-1.fc7 postgresql-libs-8.2.9-1.fc7 hsqldb-1.8.0.8-1jpp.5.fc7 postgresql-libs-8.2.9-1.fc7 mysql-server-5.1.30-1.fc7.remi mysqlclient15-5.0.67-1.fc7.remi mod_auth_mysql-3.0.0-3.1 mysqlclient15-5.0.67-1.fc7.remi sqlite2-2.8.17-1.fc6 php-pgsql-5.2.8-1.fc7.remi sqlite-devel-3.4.2-1.fc7 postgresql-python-8.2.9-1.fc7 postgresql-8.2.9-1.fc7 postgresql-server-8.2.9-1.fc7 mono-data-sqlite-1.2.3-5.fc7 mysql-devel-5.1.30-1.fc7.remi mysqlclient15-devel-5.0.67-1.fc7.remi mod_auth_pgsql-2.0.3-3 mysql-connector-odbc-3.51.12-2.2 libdbi-dbd-mysql-0.8.1a-2.fc7 mysqlclient15-devel-5.0.67-1.fc7.remi php-mysql-5.2.8-1.fc7.remi Cheers, JD -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Mysqld fails to start
Hello JD, You need to look beyond the first error messages: /usr/libexec/mysqld: Table 'mysql.plugin' doesn't exist 090127 10:00:30 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it. but keep reading: 090127 10:00:30 InnoDB: Operating system error number 13 in a file operation. InnoDB: The error means mysqld does not have the access rights to InnoDB: the directory. InnoDB: File name ./ibdata1 InnoDB: File operation call: 'open'. InnoDB: Cannot continue operation. That's what is really keeping mysqld from starting. You just need to chown/chmod the data directory or run mysqld as the proper user. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Mysqld fails to start
Hi JD, I believe the error about mysql.plugin is a red herring - it needs fixing, but it's not the reason MySQL isn't starting. The second error is the one about InnoDB not having access rights to the directory. Check that /var/lib/mysql is writeable by the MySQL user (usually MySQL.) Make sure this is done, then MySQL will start, then you can run mysql_upgrade. HTH, Andy JD wrote: Hello List, mysqld fails to start, and emits the following error messages in /var/log/mysqld.log 090127 10:00:30 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql /usr/libexec/mysqld: Table 'mysql.plugin' doesn't exist 090127 10:00:30 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it. 090127 10:00:30 InnoDB: Operating system error number 13 in a file operation. InnoDB: The error means mysqld does not have the access rights to InnoDB: the directory. InnoDB: File name ./ibdata1 InnoDB: File operation call: 'open'. InnoDB: Cannot continue operation. 090127 10:00:30 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended So, I tried to run mysql_upgrade: # mysql_upgrade Looking for 'mysql' as: mysql Looking for 'mysqlcheck' as: mysqlcheck Running 'mysqlcheck'... mysqlcheck: Got error: 2002: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) when trying to connect FATAL ERROR: Upgrade failed So, it seems like a chicken and egg thing??? Cannot run mysql_upgrade bcause the server is not running. Cannot run the server because I must first run mysql_upgrade. PS: I know nothing about configuring and setting up mysql. Amarok2 needs a running mysqld in order to organize music tracks collections ... etc. I need help to get mysqld started and running and to add users that are allowed to access and modify their databases. Could someone send me a set of user commands to get thing going? These are all the mysql related packages on my system: mysql-5.1.30-1.fc7.remi mysql-libs-5.1.30-1.fc7.remi sqlite-3.4.2-1.fc7 libdbi-dbd-pgsql-0.8.1a-2.fc7 sqlite-3.4.2-1.fc7 postgresql-libs-8.2.9-1.fc7 hsqldb-1.8.0.8-1jpp.5.fc7 postgresql-libs-8.2.9-1.fc7 mysql-server-5.1.30-1.fc7.remi mysqlclient15-5.0.67-1.fc7.remi mod_auth_mysql-3.0.0-3.1 mysqlclient15-5.0.67-1.fc7.remi sqlite2-2.8.17-1.fc6 php-pgsql-5.2.8-1.fc7.remi sqlite-devel-3.4.2-1.fc7 postgresql-python-8.2.9-1.fc7 postgresql-8.2.9-1.fc7 postgresql-server-8.2.9-1.fc7 mono-data-sqlite-1.2.3-5.fc7 mysql-devel-5.1.30-1.fc7.remi mysqlclient15-devel-5.0.67-1.fc7.remi mod_auth_pgsql-2.0.3-3 mysql-connector-odbc-3.51.12-2.2 libdbi-dbd-mysql-0.8.1a-2.fc7 mysqlclient15-devel-5.0.67-1.fc7.remi php-mysql-5.2.8-1.fc7.remi Cheers, JD -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Mysqld fails to start
JD wrote: Hello List, mysqld fails to start, and emits the following error messages in /var/log/mysqld.log 090127 10:00:30 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql /usr/libexec/mysqld: Table 'mysql.plugin' doesn't exist 090127 10:00:30 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it. 090127 10:00:30 InnoDB: Operating system error number 13 in a file operation. InnoDB: The error means mysqld does not have the access rights to InnoDB: the directory. That's most likely to be the real error. Or, at least, the most important one. InnoDB: File name ./ibdata1 InnoDB: File operation call: 'open'. InnoDB: Cannot continue operation. 090127 10:00:30 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended So, I tried to run mysql_upgrade: # mysql_upgrade Looking for 'mysql' as: mysql Looking for 'mysqlcheck' as: mysqlcheck Running 'mysqlcheck'... mysqlcheck: Got error: 2002: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) when trying to connect FATAL ERROR: Upgrade failed So, it seems like a chicken and egg thing??? Cannot run mysql_upgrade bcause the server is not running. Cannot run the server because I must first run mysql_upgrade. I think you're being misled by the fact that there are two errors: A missing table and a file system that mysqld doesn't have access to. Fix the file permissions first, and then try starting mysqld. You'll then be able to run mysql_upgrade to fix the missing table problem (which is a warning, not a fatal error, hence why you need to have the server running to be able to fix it). Mark -- http://www.good-stuff.co.uk - Stuff, some of it good -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Mysqld fails to start
I am running as root. I dont understand what rights or permissions mysqld needs beyond those of root. Also, ./ibdata1 is relative to what path? I get this message no matter what dir is my current working dir. I searched in my home dir and in /root home dir. There is no such file or directory called ./ibdata1 nor is there any file or dir called mysql.plugin Wasnt mysqld supposed to create all that at startup?? This is what the top of the log file says: 071215 15:36:09 mysqld started InnoDB: The first specified data file ./ibdata1 did not exist: InnoDB: a new database to be created! 071215 15:36:09 InnoDB: Setting file ./ibdata1 size to 10 MB InnoDB: Database physically writes the file full: wait... 071215 15:36:10 InnoDB: Log file ./ib_logfile0 did not exist: new to be created InnoDB: Setting log file ./ib_logfile0 size to 5 MB InnoDB: Database physically writes the file full: wait... 071215 15:36:10 InnoDB: Log file ./ib_logfile1 did not exist: new to be created InnoDB: Setting log file ./ib_logfile1 size to 5 MB InnoDB: Database physically writes the file full: wait... InnoDB: Doublewrite buffer not found: creating new InnoDB: Doublewrite buffer created InnoDB: Creating foreign key constraint system tables InnoDB: Foreign key constraint system tables created 071215 15:36:10 InnoDB: Started; log sequence number 0 0 071215 15:36:10 [Note] /usr/libexec/mysqld: ready for connections. Version: '5.0.45' socket: '/var/lib/mysql/mysql.sock' port: 3306 Source distribution 071215 15:47:01 [Note] /usr/libexec/mysqld: Normal shutdown 071215 15:47:01 InnoDB: Starting shutdown... 071215 15:47:02 InnoDB: Shutdown completed; log sequence number 0 43655 071215 15:47:02 [Note] /usr/libexec/mysqld: Shutdown complete 071215 15:47:02 mysqld ended Then at re-start: 071215 15:47:17 mysqld started 071215 15:47:17 InnoDB: Started; log sequence number 0 43655 071215 15:47:17 [Note] /usr/libexec/mysqld: ready for connections. Version: '5.0.45' socket: '/var/lib/mysql/mysql.sock' port: 3306 Source distribution 071217 0:03:27 [Note] /usr/libexec/mysqld: Normal shutdown 071217 0:03:28 InnoDB: Starting shutdown... 071217 0:03:29 InnoDB: Shutdown completed; log sequence number 0 43655 071217 0:03:29 [Note] /usr/libexec/mysqld: Shutdown complete 071217 00:03:29 mysqld ended 090121 09:32:40 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql /usr/libexec/mysqld: Table 'mysql.plugin' doesn't exist 090121 9:32:41 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it. 090121 9:32:41 InnoDB: Operating system error number 13 in a file operation. InnoDB: The error means mysqld does not have the access rights to InnoDB: the directory. InnoDB: File name ./ibdata1 InnoDB: File operation call: 'open'. InnoDB: Cannot continue operation. 090121 09:32:41 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended 090121 09:33:41 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql /usr/libexec/mysqld: Table 'mysql.plugin' doesn't exist 090121 9:33:41 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it. So, how are ibdata1 and mysql.plugin are supposed to be created? Baron Schwartz wrote: Hello JD, You need to look beyond the first error messages: /usr/libexec/mysqld: Table 'mysql.plugin' doesn't exist 090127 10:00:30 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it. but keep reading: 090127 10:00:30 InnoDB: Operating system error number 13 in a file operation. InnoDB: The error means mysqld does not have the access rights to InnoDB: the directory. InnoDB: File name ./ibdata1 InnoDB: File operation call: 'open'. InnoDB: Cannot continue operation. That's what is really keeping mysqld from starting. You just need to chown/chmod the data directory or run mysqld as the proper user.
Re: Mysqld fails to start
Deamon runs as root. ls -ld /var/lib/mysql/ drwxr-xr-x 5 mysql mysql 4096 2009-01-27 10:00 /var/lib/mysql/ So what other perms does root need? Andy Shellam wrote: Hi JD, I believe the error about mysql.plugin is a red herring - it needs fixing, but it's not the reason MySQL isn't starting. The second error is the one about InnoDB not having access rights to the directory. Check that /var/lib/mysql is writeable by the MySQL user (usually MySQL.) Make sure this is done, then MySQL will start, then you can run mysql_upgrade. HTH, Andy JD wrote: Hello List, mysqld fails to start, and emits the following error messages in /var/log/mysqld.log 090127 10:00:30 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql /usr/libexec/mysqld: Table 'mysql.plugin' doesn't exist 090127 10:00:30 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it. 090127 10:00:30 InnoDB: Operating system error number 13 in a file operation. InnoDB: The error means mysqld does not have the access rights to InnoDB: the directory. InnoDB: File name ./ibdata1 InnoDB: File operation call: 'open'. InnoDB: Cannot continue operation. 090127 10:00:30 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended So, I tried to run mysql_upgrade: # mysql_upgrade Looking for 'mysql' as: mysql Looking for 'mysqlcheck' as: mysqlcheck Running 'mysqlcheck'... mysqlcheck: Got error: 2002: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) when trying to connect FATAL ERROR: Upgrade failed So, it seems like a chicken and egg thing??? Cannot run mysql_upgrade bcause the server is not running. Cannot run the server because I must first run mysql_upgrade. PS: I know nothing about configuring and setting up mysql. Amarok2 needs a running mysqld in order to organize music tracks collections ... etc. I need help to get mysqld started and running and to add users that are allowed to access and modify their databases. Could someone send me a set of user commands to get thing going? These are all the mysql related packages on my system: mysql-5.1.30-1.fc7.remi mysql-libs-5.1.30-1.fc7.remi sqlite-3.4.2-1.fc7 libdbi-dbd-pgsql-0.8.1a-2.fc7 sqlite-3.4.2-1.fc7 postgresql-libs-8.2.9-1.fc7 hsqldb-1.8.0.8-1jpp.5.fc7 postgresql-libs-8.2.9-1.fc7 mysql-server-5.1.30-1.fc7.remi mysqlclient15-5.0.67-1.fc7.remi mod_auth_mysql-3.0.0-3.1 mysqlclient15-5.0.67-1.fc7.remi sqlite2-2.8.17-1.fc6 php-pgsql-5.2.8-1.fc7.remi sqlite-devel-3.4.2-1.fc7 postgresql-python-8.2.9-1.fc7 postgresql-8.2.9-1.fc7 postgresql-server-8.2.9-1.fc7 mono-data-sqlite-1.2.3-5.fc7 mysql-devel-5.1.30-1.fc7.remi mysqlclient15-devel-5.0.67-1.fc7.remi mod_auth_pgsql-2.0.3-3 mysql-connector-odbc-3.51.12-2.2 libdbi-dbd-mysql-0.8.1a-2.fc7 mysqlclient15-devel-5.0.67-1.fc7.remi php-mysql-5.2.8-1.fc7.remi Cheers, JD -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Mysqld fails to start
JD, please post the ouput from: ls -altr /var/lib/mysql thanks Claudio JD wrote: Deamon runs as root. ls -ld /var/lib/mysql/ drwxr-xr-x 5 mysql mysql 4096 2009-01-27 10:00 /var/lib/mysql/ So what other perms does root need? Andy Shellam wrote: Hi JD, I believe the error about mysql.plugin is a red herring - it needs fixing, but it's not the reason MySQL isn't starting. The second error is the one about InnoDB not having access rights to the directory. Check that /var/lib/mysql is writeable by the MySQL user (usually MySQL.) Make sure this is done, then MySQL will start, then you can run mysql_upgrade. HTH, Andy JD wrote: Hello List, mysqld fails to start, and emits the following error messages in /var/log/mysqld.log 090127 10:00:30 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql /usr/libexec/mysqld: Table 'mysql.plugin' doesn't exist 090127 10:00:30 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it. 090127 10:00:30 InnoDB: Operating system error number 13 in a file operation. InnoDB: The error means mysqld does not have the access rights to InnoDB: the directory. InnoDB: File name ./ibdata1 InnoDB: File operation call: 'open'. InnoDB: Cannot continue operation. 090127 10:00:30 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended So, I tried to run mysql_upgrade: # mysql_upgrade Looking for 'mysql' as: mysql Looking for 'mysqlcheck' as: mysqlcheck Running 'mysqlcheck'... mysqlcheck: Got error: 2002: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) when trying to connect FATAL ERROR: Upgrade failed So, it seems like a chicken and egg thing??? Cannot run mysql_upgrade bcause the server is not running. Cannot run the server because I must first run mysql_upgrade. PS: I know nothing about configuring and setting up mysql. Amarok2 needs a running mysqld in order to organize music tracks collections ... etc. I need help to get mysqld started and running and to add users that are allowed to access and modify their databases. Could someone send me a set of user commands to get thing going? These are all the mysql related packages on my system: mysql-5.1.30-1.fc7.remi mysql-libs-5.1.30-1.fc7.remi sqlite-3.4.2-1.fc7 libdbi-dbd-pgsql-0.8.1a-2.fc7 sqlite-3.4.2-1.fc7 postgresql-libs-8.2.9-1.fc7 hsqldb-1.8.0.8-1jpp.5.fc7 postgresql-libs-8.2.9-1.fc7 mysql-server-5.1.30-1.fc7.remi mysqlclient15-5.0.67-1.fc7.remi mod_auth_mysql-3.0.0-3.1 mysqlclient15-5.0.67-1.fc7.remi sqlite2-2.8.17-1.fc6 php-pgsql-5.2.8-1.fc7.remi sqlite-devel-3.4.2-1.fc7 postgresql-python-8.2.9-1.fc7 postgresql-8.2.9-1.fc7 postgresql-server-8.2.9-1.fc7 mono-data-sqlite-1.2.3-5.fc7 mysql-devel-5.1.30-1.fc7.remi mysqlclient15-devel-5.0.67-1.fc7.remi mod_auth_pgsql-2.0.3-3 mysql-connector-odbc-3.51.12-2.2 libdbi-dbd-mysql-0.8.1a-2.fc7 mysqlclient15-devel-5.0.67-1.fc7.remi php-mysql-5.2.8-1.fc7.remi Cheers, JD -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Help with a query.
Hi, I'm somewhat stumped by how to set up a single query that does the following. Currently I'm accomplishing this through multiple queries and some PHP 'glue' logic, but it should be possible in a single query I think, and it's bugging me that I can't figure it out. If anyone has any ideas, I'd appreciate it. Here's the situation: CREATE TABLE `a` ( a_id int(11) AUTO_INCREMENT, b_id int(11), a_date datetime ); CREATE TABLE `b` ( b_id int(11) AUTO_INCREMENT, b_data varchar(128) ); CREATE TABLE `c` ( a_id int(11) AUTO_INCREMENT, b_id int(11), c_date datetime ); each item in `a` has a 1 to 1 relationship to `b`, and each item in `c` has a 1 to 1 relationship with `b`. Sometimes these correspond, i.e. there's a row in `b` that relates to both `a` and `c`, but not always. What I'm doing is looking for a sum of data from a and c for a particular date range that shows it's corresponding b.b_data row. Each row should contain: count(a_id), b_id, b_data, count(c_id) and if there's no corresponding data, the columns should be null. Like a three way left join to table b sort of, include all rows of b, and if there's no corresponding data for a or c, just fill the columns with nulls. I can get queries to execute like this: select count(a_id), b_id, b_data, count(c_id) from a right join b using (b_id) left join c using (b_id) group by b_id but it's different results than when I do two inner joins (one for ab and one for bc) and combine the data. Any thoughts? Thank you! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Setting the value for the EDITOR variable
Dear All, I would like to be able to do that from the cnf file, as I want to have a different editor for MySQL commands than the one I use when writing shell scripts. When I put the following in the .my.cnf file, [shell] EDITOR=/usr/local/bin/pico export EDITOR I get no error messages when mysql starts up, but the value of the EDITOR variable does not change. Is there any way I can achieve that from the cnf file? Thanks, Xristos _ Show them the way! Add maps and directions to your party invites. http://www.microsoft.com/windows/windowslive/events.aspx
Re: Mysqld fails to start
ls -altr /var/lib/mysql total 20548K drwxr-xr-x 2 mysql mysql 4096 2007-12-15 15:36 test/ -rw-r--r-- 1 mysql mysql 5242880 2007-12-15 15:36 ib_logfile1 drwxr-xr-x 2 mysql mysql 4096 2007-12-15 16:10 ampache/ -rw-r--r-- 1 mysql mysql 10485760 2007-12-17 00:03 ibdata1 drwxr-xr-x 2 mysql mysql 4096 2009-01-21 14:12 mysql/ srwxrwxrwx 1 mysql mysql0 2009-01-27 14:13 mysql.sock= -rw-r--r-- 1 mysql mysql 5242880 2009-01-27 14:13 ib_logfile0 drwxr-xr-x 53 root root 4096 2009-01-27 14:14 ../ drwxr-xr-x 5 mysql mysql 4096 2009-01-27 15:48 ./ Claudio Nanni wrote: JD, please post the ouput from: ls -altr /var/lib/mysql thanks Claudio JD wrote: Deamon runs as root. ls -ld /var/lib/mysql/ drwxr-xr-x 5 mysql mysql 4096 2009-01-27 10:00 /var/lib/mysql/ So what other perms does root need? Andy Shellam wrote: Hi JD, I believe the error about mysql.plugin is a red herring - it needs fixing, but it's not the reason MySQL isn't starting. The second error is the one about InnoDB not having access rights to the directory. Check that /var/lib/mysql is writeable by the MySQL user (usually MySQL.) Make sure this is done, then MySQL will start, then you can run mysql_upgrade. HTH, Andy JD wrote: Hello List, mysqld fails to start, and emits the following error messages in /var/log/mysqld.log 090127 10:00:30 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql /usr/libexec/mysqld: Table 'mysql.plugin' doesn't exist 090127 10:00:30 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it. 090127 10:00:30 InnoDB: Operating system error number 13 in a file operation. InnoDB: The error means mysqld does not have the access rights to InnoDB: the directory. InnoDB: File name ./ibdata1 InnoDB: File operation call: 'open'. InnoDB: Cannot continue operation. 090127 10:00:30 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended So, I tried to run mysql_upgrade: # mysql_upgrade Looking for 'mysql' as: mysql Looking for 'mysqlcheck' as: mysqlcheck Running 'mysqlcheck'... mysqlcheck: Got error: 2002: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) when trying to connect FATAL ERROR: Upgrade failed So, it seems like a chicken and egg thing??? Cannot run mysql_upgrade bcause the server is not running. Cannot run the server because I must first run mysql_upgrade. PS: I know nothing about configuring and setting up mysql. Amarok2 needs a running mysqld in order to organize music tracks collections ... etc. I need help to get mysqld started and running and to add users that are allowed to access and modify their databases. Could someone send me a set of user commands to get thing going? These are all the mysql related packages on my system: mysql-5.1.30-1.fc7.remi mysql-libs-5.1.30-1.fc7.remi sqlite-3.4.2-1.fc7 libdbi-dbd-pgsql-0.8.1a-2.fc7 sqlite-3.4.2-1.fc7 postgresql-libs-8.2.9-1.fc7 hsqldb-1.8.0.8-1jpp.5.fc7 postgresql-libs-8.2.9-1.fc7 mysql-server-5.1.30-1.fc7.remi mysqlclient15-5.0.67-1.fc7.remi mod_auth_mysql-3.0.0-3.1 mysqlclient15-5.0.67-1.fc7.remi sqlite2-2.8.17-1.fc6 php-pgsql-5.2.8-1.fc7.remi sqlite-devel-3.4.2-1.fc7 postgresql-python-8.2.9-1.fc7 postgresql-8.2.9-1.fc7 postgresql-server-8.2.9-1.fc7 mono-data-sqlite-1.2.3-5.fc7 mysql-devel-5.1.30-1.fc7.remi mysqlclient15-devel-5.0.67-1.fc7.remi mod_auth_pgsql-2.0.3-3 mysql-connector-odbc-3.51.12-2.2 libdbi-dbd-mysql-0.8.1a-2.fc7 mysqlclient15-devel-5.0.67-1.fc7.remi php-mysql-5.2.8-1.fc7.remi Cheers, JD -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Help with a query.
Micah, each item in `a` has a 1 to 1 relationship to `b`, and each item in `c` has a 1 to 1 relationship with `b`. Sometimes these correspond, i.e. there's a row in `b` that relates to both `a` and `c`, but not always. So in a given b row, the b_id value might match an a.a_id, a c.a_id, or both? Whatever the purpose of this ambiguity, it seems to undermine your query objective. PB - Micah Stevens wrote: Hi, I'm somewhat stumped by how to set up a single query that does the following. Currently I'm accomplishing this through multiple queries and some PHP 'glue' logic, but it should be possible in a single query I think, and it's bugging me that I can't figure it out. If anyone has any ideas, I'd appreciate it. Here's the situation: CREATE TABLE `a` ( a_id int(11) AUTO_INCREMENT, b_id int(11), a_date datetime ); CREATE TABLE `b` ( b_id int(11) AUTO_INCREMENT, b_data varchar(128) ); CREATE TABLE `c` ( a_id int(11) AUTO_INCREMENT, b_id int(11), c_date datetime ); each item in `a` has a 1 to 1 relationship to `b`, and each item in `c` has a 1 to 1 relationship with `b`. Sometimes these correspond, i.e. there's a row in `b` that relates to both `a` and `c`, but not always. What I'm doing is looking for a sum of data from a and c for a particular date range that shows it's corresponding b.b_data row. Each row should contain: count(a_id), b_id, b_data, count(c_id) and if there's no corresponding data, the columns should be null. Like a three way left join to table b sort of, include all rows of b, and if there's no corresponding data for a or c, just fill the columns with nulls. I can get queries to execute like this: select count(a_id), b_id, b_data, count(c_id) from a right join b using (b_id) left join c using (b_id) group by b_id but it's different results than when I do two inner joins (one for ab and one for bc) and combine the data. Any thoughts? Thank you! Internal Virus Database is out of date. Checked by AVG - http://www.avg.com Version: 8.0.176 / Virus Database: 270.10.12/1909 - Release Date: 1/22/2009 7:08 AM
Re: Mysqld fails to start
Ah, try this: chmod ug+rw ibdata1 and the same for your iblogfile0 and iblogfile1 files.. Walter OlinData: Professional services for MySQL Support * Consulting * Administration http://www.olindata.com On Wed, Jan 28, 2009 at 12:48 AM, JD jd1...@gmail.com wrote: ls -altr /var/lib/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Setting the value for the EDITOR variable
On Jan 27, 2009, at 5:44 PM, Xristos Karvouneas wrote: Dear All, I would like to be able to do that from the cnf file, as I want to have a different editor for MySQL commands than the one I use when writing shell scripts. When I put the following in the .my.cnf file, [shell] EDITOR=/usr/local/bin/pico export EDITOR I get no error messages when mysql starts up, but the value of the EDITOR variable does not change. Is there any way I can achieve that from the cnf file? No. MySQL option files are for setting MySQL program options, not environment variables. -- Paul DuBois Sun Microsystems / MySQL Documentation Team Madison, Wisconsin, USA www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Mysqld fails to start
Hi JD, just try a simple thing, rm /var/lib/mysql/ib_logfile1 rm /var/lib/mysql/ib_logfile2 to remove the innodb log files, sometimes you can have this problems, did you copy the database between servers? where are the .pid and .err files? Claudio 2009/1/28 JD jd1...@gmail.com ls -altr /var/lib/mysql total 20548K drwxr-xr-x 2 mysql mysql 4096 2007-12-15 15:36 test/ -rw-r--r-- 1 mysql mysql 5242880 2007-12-15 15:36 ib_logfile1 drwxr-xr-x 2 mysql mysql 4096 2007-12-15 16:10 ampache/ -rw-r--r-- 1 mysql mysql 10485760 2007-12-17 00:03 ibdata1 drwxr-xr-x 2 mysql mysql 4096 2009-01-21 14:12 mysql/ srwxrwxrwx 1 mysql mysql0 2009-01-27 14:13 mysql.sock= -rw-r--r-- 1 mysql mysql 5242880 2009-01-27 14:13 ib_logfile0 drwxr-xr-x 53 root root 4096 2009-01-27 14:14 ../ drwxr-xr-x 5 mysql mysql 4096 2009-01-27 15:48 ./ Claudio Nanni wrote: JD, please post the ouput from: ls -altr /var/lib/mysql thanks Claudio JD wrote: Deamon runs as root. ls -ld /var/lib/mysql/ drwxr-xr-x 5 mysql mysql 4096 2009-01-27 10:00 /var/lib/mysql/ So what other perms does root need? Andy Shellam wrote: Hi JD, I believe the error about mysql.plugin is a red herring - it needs fixing, but it's not the reason MySQL isn't starting. The second error is the one about InnoDB not having access rights to the directory. Check that /var/lib/mysql is writeable by the MySQL user (usually MySQL.) Make sure this is done, then MySQL will start, then you can run mysql_upgrade. HTH, Andy JD wrote: Hello List, mysqld fails to start, and emits the following error messages in /var/log/mysqld.log 090127 10:00:30 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql /usr/libexec/mysqld: Table 'mysql.plugin' doesn't exist 090127 10:00:30 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it. 090127 10:00:30 InnoDB: Operating system error number 13 in a file operation. InnoDB: The error means mysqld does not have the access rights to InnoDB: the directory. InnoDB: File name ./ibdata1 InnoDB: File operation call: 'open'. InnoDB: Cannot continue operation. 090127 10:00:30 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended So, I tried to run mysql_upgrade: # mysql_upgrade Looking for 'mysql' as: mysql Looking for 'mysqlcheck' as: mysqlcheck Running 'mysqlcheck'... mysqlcheck: Got error: 2002: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) when trying to connect FATAL ERROR: Upgrade failed So, it seems like a chicken and egg thing??? Cannot run mysql_upgrade bcause the server is not running. Cannot run the server because I must first run mysql_upgrade. PS: I know nothing about configuring and setting up mysql. Amarok2 needs a running mysqld in order to organize music tracks collections ... etc. I need help to get mysqld started and running and to add users that are allowed to access and modify their databases. Could someone send me a set of user commands to get thing going? These are all the mysql related packages on my system: mysql-5.1.30-1.fc7.remi mysql-libs-5.1.30-1.fc7.remi sqlite-3.4.2-1.fc7 libdbi-dbd-pgsql-0.8.1a-2.fc7 sqlite-3.4.2-1.fc7 postgresql-libs-8.2.9-1.fc7 hsqldb-1.8.0.8-1jpp.5.fc7 postgresql-libs-8.2.9-1.fc7 mysql-server-5.1.30-1.fc7.remi mysqlclient15-5.0.67-1.fc7.remi mod_auth_mysql-3.0.0-3.1 mysqlclient15-5.0.67-1.fc7.remi sqlite2-2.8.17-1.fc6 php-pgsql-5.2.8-1.fc7.remi sqlite-devel-3.4.2-1.fc7 postgresql-python-8.2.9-1.fc7 postgresql-8.2.9-1.fc7 postgresql-server-8.2.9-1.fc7 mono-data-sqlite-1.2.3-5.fc7 mysql-devel-5.1.30-1.fc7.remi mysqlclient15-devel-5.0.67-1.fc7.remi mod_auth_pgsql-2.0.3-3 mysql-connector-odbc-3.51.12-2.2 libdbi-dbd-mysql-0.8.1a-2.fc7 mysqlclient15-devel-5.0.67-1.fc7.remi php-mysql-5.2.8-1.fc7.remi Cheers, JD -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=claudio.na...@gmail.com
Re: Mysqld fails to start
I did the chmod. Then # ls -latrR /var/lib/mysql /var/lib/mysql: total 20548K drwxrwxr-x 2 mysql mysql 4096 2007-12-15 15:36 test/ -rw-rw-r-- 1 mysql mysql 5242880 2007-12-15 15:36 ib_logfile1 drwxrwxr-x 2 mysql mysql 4096 2007-12-15 16:10 ampache/ drwxrwxr-x 2 mysql mysql 4096 2009-01-21 14:12 mysql/ drwxr-xr-x 53 root root 4096 2009-01-27 14:14 ../ srwxrwxrwx 1 mysql mysql0 2009-01-27 16:35 mysql.sock= -rw-rw-r-- 1 mysql mysql 5242880 2009-01-27 16:35 ib_logfile0 -rw-rw-r-- 1 mysql mysql 10485760 2009-01-27 16:35 ibdata1 drwxrwxr-x 5 mysql mysql 4096 2009-01-27 16:35 ./ /var/lib/mysql/test: total 12K drwxrwxr-x 2 mysql mysql 4096 2007-12-15 15:36 ./ drwxrwxr-x 5 mysql mysql 4096 2009-01-27 16:35 ../ /var/lib/mysql/ampache: total 16K -rw-rw-r-- 1 mysql mysql 65 2007-12-15 16:10 db.opt drwxrwxr-x 2 mysql mysql 4096 2007-12-15 16:10 ./ drwxrwxr-x 5 mysql mysql 4096 2009-01-27 16:35 ../ /var/lib/mysql/mysql: total 788K -rw-rw-r-- 1 mysql mysql 10330 2007-12-15 15:36 user.frm -rw-rw-r-- 1 mysql mysql 1024 2007-12-15 15:36 time_zone_transition_type.MYI -rw-rw-r-- 1 mysql mysql 0 2007-12-15 15:36 time_zone_transition_type.MYD -rw-rw-r-- 1 mysql mysql 8748 2007-12-15 15:36 time_zone_transition_type.frm -rw-rw-r-- 1 mysql mysql 1024 2007-12-15 15:36 time_zone_transition.MYI -rw-rw-r-- 1 mysql mysql 0 2007-12-15 15:36 time_zone_transition.MYD -rw-rw-r-- 1 mysql mysql 8686 2007-12-15 15:36 time_zone_transition.frm -rw-rw-r-- 1 mysql mysql 1024 2007-12-15 15:36 time_zone_name.MYI -rw-rw-r-- 1 mysql mysql 0 2007-12-15 15:36 time_zone_name.MYD -rw-rw-r-- 1 mysql mysql 8606 2007-12-15 15:36 time_zone_name.frm -rw-rw-r-- 1 mysql mysql 1024 2007-12-15 15:36 time_zone.MYI -rw-rw-r-- 1 mysql mysql 0 2007-12-15 15:36 time_zone.MYD -rw-rw-r-- 1 mysql mysql 1024 2007-12-15 15:36 time_zone_leap_second.MYI -rw-rw-r-- 1 mysql mysql 0 2007-12-15 15:36 time_zone_leap_second.MYD -rw-rw-r-- 1 mysql mysql 8624 2007-12-15 15:36 time_zone_leap_second.frm -rw-rw-r-- 1 mysql mysql 8636 2007-12-15 15:36 time_zone.frm -rw-rw-r-- 1 mysql mysql 1024 2007-12-15 15:36 tables_priv.MYI -rw-rw-r-- 1 mysql mysql 0 2007-12-15 15:36 tables_priv.MYD -rw-rw-r-- 1 mysql mysql 8947 2007-12-15 15:36 tables_priv.frm -rw-rw-r-- 1 mysql mysql 1024 2007-12-15 15:36 procs_priv.MYI -rw-rw-r-- 1 mysql mysql 0 2007-12-15 15:36 procs_priv.MYD -rw-rw-r-- 1 mysql mysql 8875 2007-12-15 15:36 procs_priv.frm -rw-rw-r-- 1 mysql mysql 1024 2007-12-15 15:36 proc.MYI -rw-rw-r-- 1 mysql mysql 0 2007-12-15 15:36 proc.MYD -rw-rw-r-- 1 mysql mysql 9691 2007-12-15 15:36 proc.frm -rw-rw-r-- 1 mysql mysql 1024 2007-12-15 15:36 host.MYI -rw-rw-r-- 1 mysql mysql 0 2007-12-15 15:36 host.MYD -rw-rw-r-- 1 mysql mysql 9416 2007-12-15 15:36 host.frm -rw-rw-r-- 1 mysql mysql 17408 2007-12-15 15:36 help_topic.MYI -rw-rw-r-- 1 mysql mysql 333276 2007-12-15 15:36 help_topic.MYD -rw-rw-r-- 1 mysql mysql 8770 2007-12-15 15:36 help_topic.frm -rw-rw-r-- 1 mysql mysql 15360 2007-12-15 15:36 help_relation.MYI -rw-rw-r-- 1 mysql mysql 7281 2007-12-15 15:36 help_relation.MYD -rw-rw-r-- 1 mysql mysql 8630 2007-12-15 15:36 help_relation.frm -rw-rw-r-- 1 mysql mysql 14336 2007-12-15 15:36 help_keyword.MYI -rw-rw-r-- 1 mysql mysql 77815 2007-12-15 15:36 help_keyword.MYD -rw-rw-r-- 1 mysql mysql 8612 2007-12-15 15:36 help_keyword.frm -rw-rw-r-- 1 mysql mysql 3072 2007-12-15 15:36 help_category.MYI -rw-rw-r-- 1 mysql mysql 20916 2007-12-15 15:36 help_category.MYD -rw-rw-r-- 1 mysql mysql 8700 2007-12-15 15:36 help_category.frm -rw-rw-r-- 1 mysql mysql 1024 2007-12-15 15:36 func.MYI -rw-rw-r-- 1 mysql mysql 0 2007-12-15 15:36 func.MYD -rw-rw-r-- 1 mysql mysql 8665 2007-12-15 15:36 func.frm -rw-rw-r-- 1 mysql mysql 9494 2007-12-15 15:36 db.frm -rw-rw-r-- 1 mysql mysql 1024 2007-12-15 15:36 columns_priv.MYI -rw-rw-r-- 1 mysql mysql 0 2007-12-15 15:36 columns_priv.MYD -rw-rw-r-- 1 mysql mysql 8820 2007-12-15 15:36 columns_priv.frm -rw-rw-r-- 1 mysql mysql256 2007-12-15 16:10 user.MYD -rw-rw-r-- 1 mysql mysql 1314 2007-12-15 16:10 db.MYD -rw-rw-r-- 1 mysql mysql 2048 2007-12-17 00:03 user.MYI -rw-rw-r-- 1 mysql mysql 4096 2007-12-17 00:03 db.MYI drwxrwxr-x 2 mysql mysql 4096 2009-01-21 14:12 ./ -rw-rw 1 mysql mysql 12288 2009-01-21 14:12 .swp drwxrwxr-x 5 mysql mysql 4096 2009-01-27 16:35 ../ I restarted mysqld (service mysqld restart) and this is what got written in the mysql.log 090127 16:40:09 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql /usr/libexec/mysqld: Table 'mysql.plugin' doesn't exist 090127 16:40:09 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it. 090127 16:40:09 InnoDB: Started; log sequence number 0 43655 090127 16:40:09 [ERROR] Can't open and lock privilege tables: Table 'mysql.servers' doesn't exist 090127 16:40:09 [ERROR] Column count of
Re: Mysqld fails to start
I stopped mysqld. I removed those logfiles I did not copy any databe between servers. I only have one machine. I would not even know what to copy, as I know nothing about databases. I have no idea where the .pid and the .err files came from. I restarted the daemon, and this is what it wrote into the /var/messages/mysqd.log 090127 16:54:15 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql /usr/libexec/mysqld: Table 'mysql.plugin' doesn't exist 090127 16:54:15 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it. 090127 16:54:15 InnoDB: Log file ./ib_logfile0 did not exist: new to be created InnoDB: Setting log file ./ib_logfile0 size to 5 MB InnoDB: Database physically writes the file full: wait... 090127 16:54:15 InnoDB: Log file ./ib_logfile1 did not exist: new to be created InnoDB: Setting log file ./ib_logfile1 size to 5 MB InnoDB: Database physically writes the file full: wait... InnoDB: The log sequence number in ibdata files does not match InnoDB: the log sequence number in the ib_logfiles! 090127 16:54:15 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... 090127 16:54:15 InnoDB: Started; log sequence number 0 44044 090127 16:54:15 [ERROR] Can't open and lock privilege tables: Table 'mysql.servers' doesn't exist 090127 16:54:15 [ERROR] Column count of mysql.db is wrong. Expected 22, found 20. Created with MySQL 50045, now running 50130. Please use mysql_upgrade to fix this error. 090127 16:54:15 [ERROR] mysql.user has no `Event_priv` column at position 29 090127 16:54:15 [ERROR] Cannot open mysql.event 090127 16:54:15 [ERROR] Event Scheduler: An error occurred when initializing system tables. Disabling the Event Scheduler. 090127 16:54:15 [Note] /usr/libexec/mysqld: ready for connections. Version: '5.1.30' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL) by Remi Claudio Nanni wrote: Hi JD, just try a simple thing, rm /var/lib/mysql/ib_logfile1 rm /var/lib/mysql/ib_logfile2 to remove the innodb log files, sometimes you can have this problems, did you copy the database between servers? where are the .pid and .err files? Claudio 2009/1/28 JD jd1...@gmail.com mailto:jd1...@gmail.com ls -altr /var/lib/mysql total 20548K drwxr-xr-x 2 mysql mysql 4096 2007-12-15 15:36 test/ -rw-r--r-- 1 mysql mysql 5242880 2007-12-15 15:36 ib_logfile1 drwxr-xr-x 2 mysql mysql 4096 2007-12-15 16:10 ampache/ -rw-r--r-- 1 mysql mysql 10485760 2007-12-17 00:03 ibdata1 drwxr-xr-x 2 mysql mysql 4096 2009-01-21 14:12 mysql/ srwxrwxrwx 1 mysql mysql0 2009-01-27 14:13 mysql.sock= -rw-r--r-- 1 mysql mysql 5242880 2009-01-27 14:13 ib_logfile0 drwxr-xr-x 53 root root 4096 2009-01-27 14:14 ../ drwxr-xr-x 5 mysql mysql 4096 2009-01-27 15:48 ./ Claudio Nanni wrote: JD, please post the ouput from: ls -altr /var/lib/mysql thanks Claudio JD wrote: Deamon runs as root. ls -ld /var/lib/mysql/ drwxr-xr-x 5 mysql mysql 4096 2009-01-27 10:00 /var/lib/mysql/ So what other perms does root need? Andy Shellam wrote: Hi JD, I believe the error about mysql.plugin is a red herring - it needs fixing, but it's not the reason MySQL isn't starting. The second error is the one about InnoDB not having access rights to the directory. Check that /var/lib/mysql is writeable by the MySQL user (usually MySQL.) Make sure this is done, then MySQL will start, then you can run mysql_upgrade. HTH, Andy JD wrote: Hello List, mysqld fails to start, and emits the following error messages in /var/log/mysqld.log 090127 10:00:30 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql /usr/libexec/mysqld: Table 'mysql.plugin' doesn't exist 090127 10:00:30 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it. 090127 10:00:30 InnoDB: Operating system error number 13 in a file operation. InnoDB: The error means mysqld does not have the access rights to InnoDB: the directory. InnoDB: File name ./ibdata1 InnoDB: File operation call: 'open'. InnoDB: Cannot continue operation. 090127
Re: Help with a query.
Peter Brawley wrote: Micah, each item in `a` has a 1 to 1 relationship to `b`, and each item in `c` has a 1 to 1 relationship with `b`. Sometimes these correspond, i.e. there's a row in `b` that relates to both `a` and `c`, but not always. So in a given b row, the b_id value might match an a.a_id, a c.a_id, or both? Whatever the purpose of this ambiguity, it seems to undermine your query objective. PB - This is for a click through system, in this example, 'a' would record click throughs, and 'c' would record follow ups. B is the lead information. Sometimes there's no click through to a lead contact, and sometimes there's a click through with no follow up. Sometimes both. Does that help? -Micah -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Mysqld fails to start
OK, I located a weblog on how to change the password and it worked. I then stopped and started the serever, and this is what it belched out in the mysqld.log: 090127 18:17:16 [Note] /usr/libexec/mysqld: Normal shutdown 090127 18:17:16 InnoDB: Starting shutdown... 090127 18:17:17 InnoDB: Shutdown completed; log sequence number 0 44044 090127 18:17:17 [Note] /usr/libexec/mysqld: Shutdown complete 090127 18:17:17 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended 090127 18:17:18 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql /usr/libexec/mysqld: Table 'mysql.plugin' doesn't exist 090127 18:17:18 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it. 090127 18:17:18 InnoDB: Started; log sequence number 0 44044 090127 18:17:18 [ERROR] Can't open and lock privilege tables: Table 'mysql.servers' doesn't exist 090127 18:17:18 [ERROR] Column count of mysql.db is wrong. Expected 22, found 20. Created with MySQL 50045, now running 50130. Please use mysql_upgrade to fix this error. 090127 18:17:18 [ERROR] mysql.user has no `Event_priv` column at position 29 090127 18:17:18 [ERROR] Cannot open mysql.event 090127 18:17:18 [ERROR] Event Scheduler: An error occurred when initializing system tables. Disabling the Event Scheduler. 090127 18:17:18 [Note] /usr/libexec/mysqld: ready for connections. Version: '5.1.30' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL) by Remi So, how do I fix those errors about 'mysql.plugin' and mysql.db and mysql.user For that matter why does it even have a mysql.user ??? Should it not be mysql.root? Walter Heck wrote: We're slowly getting there ;) that is the root account for the mysql server, not the linux root account :) when you installed mysql, you chose a root password. If you didn't, look up how to reset your mysql root account's password. Plenty of info on the web :) good luck! Walter OlinData: Professional services for MySQL Support * Consulting * Administration http://www.olindata.com On Wed, Jan 28, 2009 at 1:47 AM, JD jd1...@gmail.com wrote: I did the chmod. Then # ls -latrR /var/lib/mysql /var/lib/mysql: total 20548K drwxrwxr-x 2 mysql mysql 4096 2007-12-15 15:36 test/ -rw-rw-r-- 1 mysql mysql 5242880 2007-12-15 15:36 ib_logfile1 drwxrwxr-x 2 mysql mysql 4096 2007-12-15 16:10 ampache/ drwxrwxr-x 2 mysql mysql 4096 2009-01-21 14:12 mysql/ drwxr-xr-x 53 root root 4096 2009-01-27 14:14 ../ srwxrwxrwx 1 mysql mysql0 2009-01-27 16:35 mysql.sock= -rw-rw-r-- 1 mysql mysql 5242880 2009-01-27 16:35 ib_logfile0 -rw-rw-r-- 1 mysql mysql 10485760 2009-01-27 16:35 ibdata1 drwxrwxr-x 5 mysql mysql 4096 2009-01-27 16:35 ./ /var/lib/mysql/test: total 12K drwxrwxr-x 2 mysql mysql 4096 2007-12-15 15:36 ./ drwxrwxr-x 5 mysql mysql 4096 2009-01-27 16:35 ../ /var/lib/mysql/ampache: total 16K -rw-rw-r-- 1 mysql mysql 65 2007-12-15 16:10 db.opt drwxrwxr-x 2 mysql mysql 4096 2007-12-15 16:10 ./ drwxrwxr-x 5 mysql mysql 4096 2009-01-27 16:35 ../ /var/lib/mysql/mysql: total 788K -rw-rw-r-- 1 mysql mysql 10330 2007-12-15 15:36 user.frm -rw-rw-r-- 1 mysql mysql 1024 2007-12-15 15:36 time_zone_transition_type.MYI -rw-rw-r-- 1 mysql mysql 0 2007-12-15 15:36 time_zone_transition_type.MYD -rw-rw-r-- 1 mysql mysql 8748 2007-12-15 15:36 time_zone_transition_type.frm -rw-rw-r-- 1 mysql mysql 1024 2007-12-15 15:36 time_zone_transition.MYI -rw-rw-r-- 1 mysql mysql 0 2007-12-15 15:36 time_zone_transition.MYD -rw-rw-r-- 1 mysql mysql 8686 2007-12-15 15:36 time_zone_transition.frm -rw-rw-r-- 1 mysql mysql 1024 2007-12-15 15:36 time_zone_name.MYI -rw-rw-r-- 1 mysql mysql 0 2007-12-15 15:36 time_zone_name.MYD -rw-rw-r-- 1 mysql mysql 8606 2007-12-15 15:36 time_zone_name.frm -rw-rw-r-- 1 mysql mysql 1024 2007-12-15 15:36 time_zone.MYI -rw-rw-r-- 1 mysql mysql 0 2007-12-15 15:36 time_zone.MYD -rw-rw-r-- 1 mysql mysql 1024 2007-12-15 15:36 time_zone_leap_second.MYI -rw-rw-r-- 1 mysql mysql 0 2007-12-15 15:36 time_zone_leap_second.MYD -rw-rw-r-- 1 mysql mysql 8624 2007-12-15 15:36 time_zone_leap_second.frm -rw-rw-r-- 1 mysql mysql 8636 2007-12-15 15:36 time_zone.frm -rw-rw-r-- 1 mysql mysql 1024 2007-12-15 15:36 tables_priv.MYI -rw-rw-r-- 1 mysql mysql 0 2007-12-15 15:36 tables_priv.MYD -rw-rw-r-- 1 mysql mysql 8947 2007-12-15 15:36 tables_priv.frm -rw-rw-r-- 1 mysql mysql 1024 2007-12-15 15:36 procs_priv.MYI -rw-rw-r-- 1 mysql mysql 0 2007-12-15 15:36 procs_priv.MYD -rw-rw-r-- 1 mysql mysql 8875 2007-12-15 15:36 procs_priv.frm -rw-rw-r-- 1 mysql mysql 1024 2007-12-15 15:36 proc.MYI -rw-rw-r-- 1 mysql mysql 0 2007-12-15 15:36 proc.MYD -rw-rw-r-- 1 mysql mysql 9691 2007-12-15 15:36 proc.frm -rw-rw-r-- 1 mysql mysql 1024 2007-12-15 15:36 host.MYI -rw-rw-r-- 1 mysql mysql 0 2007-12-15 15:36 host.MYD -rw-rw-r-- 1 mysql mysql 9416 2007-12-15 15:36 host.frm -rw-rw-r-- 1
Re: Mysqld fails to start
mysql.user is a table name, it is in the form of database_name.table_name. Mysql has a system database conveniently called mysql :) 090127 18:17:18 [ERROR] Column count of mysql.db is wrong. Expected 22, found 20. Created with MySQL 50045, now running 50130. Please use mysql_upgrade to fix this error. I don't know how you got to where you are now, but it seems like your system database is from MySQL 5.0.45, while you seem to be running 5.1.30. couple of options: 1) try mysql_upgrade again, using root and it's password this time 2) remove the mysql dir inside /var/lib/mysql and then use mysql_install_db to recreate them properly 3) just remove mysql completely and reinstall from scratch Normally I would recommend 2, but in your case I would probably recommend 3 since you just need mysql to run amarok2 (i would recommend songbird anyway: http://www.getsongbird.com/ ;) ) and are not planning on doing anything serious with mysql. Depending on the distro you are using, it can be as simple as yum remove mysql and yum install mysql (removing /var/lib/mysql in between). Your choice though :) good luck! Walter can you try running mysql_upgrade OlinData: Professional services for MySQL Support * Consulting * Administration http://www.olindata.com On Wed, Jan 28, 2009 at 3:21 AM, JD jd1...@gmail.com wrote: OK, I located a weblog on how to change the password and it worked. I then stopped and started the serever, and this is what it belched out in the mysqld.log: 090127 18:17:16 [Note] /usr/libexec/mysqld: Normal shutdown 090127 18:17:16 InnoDB: Starting shutdown... 090127 18:17:17 InnoDB: Shutdown completed; log sequence number 0 44044 090127 18:17:17 [Note] /usr/libexec/mysqld: Shutdown complete 090127 18:17:17 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended 090127 18:17:18 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql /usr/libexec/mysqld: Table 'mysql.plugin' doesn't exist 090127 18:17:18 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it. 090127 18:17:18 InnoDB: Started; log sequence number 0 44044 090127 18:17:18 [ERROR] Can't open and lock privilege tables: Table 'mysql.servers' doesn't exist 090127 18:17:18 [ERROR] Column count of mysql.db is wrong. Expected 22, found 20. Created with MySQL 50045, now running 50130. Please use mysql_upgrade to fix this error. 090127 18:17:18 [ERROR] mysql.user has no `Event_priv` column at position 29 090127 18:17:18 [ERROR] Cannot open mysql.event 090127 18:17:18 [ERROR] Event Scheduler: An error occurred when initializing system tables. Disabling the Event Scheduler. 090127 18:17:18 [Note] /usr/libexec/mysqld: ready for connections. Version: '5.1.30' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL) by Remi So, how do I fix those errors about 'mysql.plugin' and mysql.db and mysql.user For that matter why does it even have a mysql.user ??? Should it not be mysql.root? Walter Heck wrote: We're slowly getting there ;) that is the root account for the mysql server, not the linux root account :) when you installed mysql, you chose a root password. If you didn't, look up how to reset your mysql root account's password. Plenty of info on the web :) good luck! Walter OlinData: Professional services for MySQL Support * Consulting * Administration http://www.olindata.com On Wed, Jan 28, 2009 at 1:47 AM, JD jd1...@gmail.com wrote: I did the chmod. Then # ls -latrR /var/lib/mysql /var/lib/mysql: total 20548K drwxrwxr-x 2 mysql mysql 4096 2007-12-15 15:36 test/ -rw-rw-r-- 1 mysql mysql 5242880 2007-12-15 15:36 ib_logfile1 drwxrwxr-x 2 mysql mysql 4096 2007-12-15 16:10 ampache/ drwxrwxr-x 2 mysql mysql 4096 2009-01-21 14:12 mysql/ drwxr-xr-x 53 root root 4096 2009-01-27 14:14 ../ srwxrwxrwx 1 mysql mysql0 2009-01-27 16:35 mysql.sock= -rw-rw-r-- 1 mysql mysql 5242880 2009-01-27 16:35 ib_logfile0 -rw-rw-r-- 1 mysql mysql 10485760 2009-01-27 16:35 ibdata1 drwxrwxr-x 5 mysql mysql 4096 2009-01-27 16:35 ./ /var/lib/mysql/test: total 12K drwxrwxr-x 2 mysql mysql 4096 2007-12-15 15:36 ./ drwxrwxr-x 5 mysql mysql 4096 2009-01-27 16:35 ../ /var/lib/mysql/ampache: total 16K -rw-rw-r-- 1 mysql mysql 65 2007-12-15 16:10 db.opt drwxrwxr-x 2 mysql mysql 4096 2007-12-15 16:10 ./ drwxrwxr-x 5 mysql mysql 4096 2009-01-27 16:35 ../ /var/lib/mysql/mysql: total 788K -rw-rw-r-- 1 mysql mysql 10330 2007-12-15 15:36 user.frm -rw-rw-r-- 1 mysql mysql 1024 2007-12-15 15:36 time_zone_transition_type.MYI -rw-rw-r-- 1 mysql mysql 0 2007-12-15 15:36 time_zone_transition_type.MYD -rw-rw-r-- 1 mysql mysql 8748 2007-12-15 15:36 time_zone_transition_type.frm -rw-rw-r-- 1 mysql mysql 1024 2007-12-15 15:36 time_zone_transition.MYI -rw-rw-r-- 1 mysql mysql 0 2007-12-15 15:36 time_zone_transition.MYD -rw-rw-r-- 1 mysql mysql 8686 2007-12-15 15:36
Re: Mysqld fails to start
Problem with yum remove mysql is that it will also remove all it's dependents, whereas yum install mysql will not restore the dependents. Also, you are probably right re: 5.0.x and 5.1.x At anywho, I ran: }# mysql_upgrade --user=root --password='theNewPassword' Looking for 'mysql' as: mysql Looking for 'mysqlcheck' as: mysqlcheck Running 'mysqlcheck'... mysql.columns_priv OK mysql.db OK mysql.func OK mysql.help_category error: Table upgrade required. Please do REPAIR TABLE `help_category` to fix it! mysql.help_keyword error: Table upgrade required. Please do REPAIR TABLE `help_keyword` to fix it! mysql.help_relationOK mysql.help_topic error: Table upgrade required. Please do REPAIR TABLE `help_topic` to fix it! mysql.host OK mysql.proc error: Table upgrade required. Please do REPAIR TABLE `proc` to fix it! mysql.procs_priv OK mysql.tables_priv OK mysql.time_zoneOK mysql.time_zone_leap_secondOK mysql.time_zone_name error: Table upgrade required. Please do REPAIR TABLE `time_zone_name` to fix it! mysql.time_zone_transition OK mysql.time_zone_transition_typeOK mysql.user OK Repairing tables mysql.help_categoryOK mysql.help_keyword OK mysql.help_topic OK mysql.proc OK mysql.time_zone_name OK Running 'mysql_fix_privilege_tables'... OK So, you want me to 1. Stop the server 2. Remove /var/lib/mysql/mysql right? 3. Restart the server Correct? Walter Heck wrote: mysql.user is a table name, it is in the form of database_name.table_name. Mysql has a system database conveniently called mysql :) 090127 18:17:18 [ERROR] Column count of mysql.db is wrong. Expected 22, found 20. Created with MySQL 50045, now running 50130. Please use mysql_upgrade to fix this error. I don't know how you got to where you are now, but it seems like your system database is from MySQL 5.0.45, while you seem to be running 5.1.30. couple of options: 1) try mysql_upgrade again, using root and it's password this time 2) remove the mysql dir inside /var/lib/mysql and then use mysql_install_db to recreate them properly 3) just remove mysql completely and reinstall from scratch Normally I would recommend 2, but in your case I would probably recommend 3 since you just need mysql to run amarok2 (i would recommend songbird anyway: http://www.getsongbird.com/ ;) ) and are not planning on doing anything serious with mysql. Depending on the distro you are using, it can be as simple as yum remove mysql and yum install mysql (removing /var/lib/mysql in between). Your choice though :) good luck! Walter can you try running mysql_upgrade OlinData: Professional services for MySQL Support * Consulting * Administration http://www.olindata.com On Wed, Jan 28, 2009 at 3:21 AM, JD jd1...@gmail.com wrote: OK, I located a weblog on how to change the password and it worked. I then stopped and started the serever, and this is what it belched out in the mysqld.log: 090127 18:17:16 [Note] /usr/libexec/mysqld: Normal shutdown 090127 18:17:16 InnoDB: Starting shutdown... 090127 18:17:17 InnoDB: Shutdown completed; log sequence number 0 44044 090127 18:17:17 [Note] /usr/libexec/mysqld: Shutdown complete 090127 18:17:17 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended 090127 18:17:18 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql /usr/libexec/mysqld: Table 'mysql.plugin' doesn't exist 090127 18:17:18 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it. 090127 18:17:18 InnoDB: Started; log sequence number 0 44044 090127 18:17:18 [ERROR] Can't open and lock privilege tables: Table 'mysql.servers' doesn't exist 090127 18:17:18 [ERROR] Column count of mysql.db is wrong. Expected 22, found 20. Created with MySQL 50045, now running 50130. Please use mysql_upgrade to fix this error. 090127 18:17:18 [ERROR] mysql.user has no `Event_priv` column at position 29 090127 18:17:18 [ERROR] Cannot open mysql.event 090127 18:17:18 [ERROR] Event Scheduler: An error occurred when initializing system tables. Disabling the Event Scheduler. 090127 18:17:18 [Note] /usr/libexec/mysqld: ready for connections. Version: '5.1.30' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL) by Remi So, how do I fix those errors about 'mysql.plugin' and mysql.db and mysql.user For that matter why does it even have a mysql.user ??? Should it not be mysql.root? Walter Heck
Re: Mysqld fails to start
Actually, just restarting the server should do the trick. It seems like mysql_upgrade did it's job correctly. Can you restart teh server and check the logs? Walter OlinData: Professional services for MySQL Support * Consulting * Administration http://www.olindata.com On Wed, Jan 28, 2009 at 4:13 AM, JD jd1...@gmail.com wrote: Problem with yum remove mysql is that it will also remove all it's dependents, whereas yum install mysql will not restore the dependents. Also, you are probably right re: 5.0.x and 5.1.x At anywho, I ran: }# mysql_upgrade --user=root --password='theNewPassword' Looking for 'mysql' as: mysql Looking for 'mysqlcheck' as: mysqlcheck Running 'mysqlcheck'... mysql.columns_priv OK mysql.db OK mysql.func OK mysql.help_category error: Table upgrade required. Please do REPAIR TABLE `help_category` to fix it! mysql.help_keyword error: Table upgrade required. Please do REPAIR TABLE `help_keyword` to fix it! mysql.help_relationOK mysql.help_topic error: Table upgrade required. Please do REPAIR TABLE `help_topic` to fix it! mysql.host OK mysql.proc error: Table upgrade required. Please do REPAIR TABLE `proc` to fix it! mysql.procs_priv OK mysql.tables_priv OK mysql.time_zoneOK mysql.time_zone_leap_secondOK mysql.time_zone_name error: Table upgrade required. Please do REPAIR TABLE `time_zone_name` to fix it! mysql.time_zone_transition OK mysql.time_zone_transition_typeOK mysql.user OK Repairing tables mysql.help_categoryOK mysql.help_keyword OK mysql.help_topic OK mysql.proc OK mysql.time_zone_name OK Running 'mysql_fix_privilege_tables'... OK So, you want me to 1. Stop the server 2. Remove /var/lib/mysql/mysql right? 3. Restart the server Correct? Walter Heck wrote: mysql.user is a table name, it is in the form of database_name.table_name. Mysql has a system database conveniently called mysql :) 090127 18:17:18 [ERROR] Column count of mysql.db is wrong. Expected 22, found 20. Created with MySQL 50045, now running 50130. Please use mysql_upgrade to fix this error. I don't know how you got to where you are now, but it seems like your system database is from MySQL 5.0.45, while you seem to be running 5.1.30. couple of options: 1) try mysql_upgrade again, using root and it's password this time 2) remove the mysql dir inside /var/lib/mysql and then use mysql_install_db to recreate them properly 3) just remove mysql completely and reinstall from scratch Normally I would recommend 2, but in your case I would probably recommend 3 since you just need mysql to run amarok2 (i would recommend songbird anyway: http://www.getsongbird.com/ ;) ) and are not planning on doing anything serious with mysql. Depending on the distro you are using, it can be as simple as yum remove mysql and yum install mysql (removing /var/lib/mysql in between). Your choice though :) good luck! Walter can you try running mysql_upgrade OlinData: Professional services for MySQL Support * Consulting * Administration http://www.olindata.com On Wed, Jan 28, 2009 at 3:21 AM, JD jd1...@gmail.com wrote: OK, I located a weblog on how to change the password and it worked. I then stopped and started the serever, and this is what it belched out in the mysqld.log: 090127 18:17:16 [Note] /usr/libexec/mysqld: Normal shutdown 090127 18:17:16 InnoDB: Starting shutdown... 090127 18:17:17 InnoDB: Shutdown completed; log sequence number 0 44044 090127 18:17:17 [Note] /usr/libexec/mysqld: Shutdown complete 090127 18:17:17 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended 090127 18:17:18 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql /usr/libexec/mysqld: Table 'mysql.plugin' doesn't exist 090127 18:17:18 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it. 090127 18:17:18 InnoDB: Started; log sequence number 0 44044 090127 18:17:18 [ERROR] Can't open and lock privilege tables: Table 'mysql.servers' doesn't exist 090127 18:17:18 [ERROR] Column count of mysql.db is wrong. Expected 22, found 20. Created with MySQL 50045, now running 50130. Please use mysql_upgrade to fix this error. 090127 18:17:18 [ERROR] mysql.user has no `Event_priv` column at position 29 090127 18:17:18 [ERROR] Cannot open mysql.event 090127 18:17:18 [ERROR] Event Scheduler: An error occurred when
Re: Mysqld fails to start
Hey... Thanks a *LOT Walter!!!* *It WORKED!!!* The mysqld.log file now says: 090127 19:16:10 [Note] /usr/libexec/mysqld: Normal shutdown 090127 19:16:10 InnoDB: Starting shutdown... 090127 19:16:10 InnoDB: Shutdown completed; log sequence number 0 44044 090127 19:16:10 [Note] /usr/libexec/mysqld: Shutdown complete 090127 19:16:10 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended 090127 19:16:11 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql 090127 19:16:11 InnoDB: Started; log sequence number 0 44044 090127 19:16:11 [Note] Event Scheduler: Loaded 0 events 090127 19:16:11 [Note] /usr/libexec/mysqld: ready for connections. Version: '5.1.30' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL) by Remi Cool. Now Amarok2 cannot continue to bitch at me, but I will try Songbird. Cheers, JD Walter Heck wrote: mysql.user is a table name, it is in the form of database_name.table_name. Mysql has a system database conveniently called mysql :) 090127 18:17:18 [ERROR] Column count of mysql.db is wrong. Expected 22, found 20. Created with MySQL 50045, now running 50130. Please use mysql_upgrade to fix this error. I don't know how you got to where you are now, but it seems like your system database is from MySQL 5.0.45, while you seem to be running 5.1.30. couple of options: 1) try mysql_upgrade again, using root and it's password this time 2) remove the mysql dir inside /var/lib/mysql and then use mysql_install_db to recreate them properly 3) just remove mysql completely and reinstall from scratch Normally I would recommend 2, but in your case I would probably recommend 3 since you just need mysql to run amarok2 (i would recommend songbird anyway: http://www.getsongbird.com/ ;) ) and are not planning on doing anything serious with mysql. Depending on the distro you are using, it can be as simple as yum remove mysql and yum install mysql (removing /var/lib/mysql in between). Your choice though :) good luck! Walter can you try running mysql_upgrade OlinData: Professional services for MySQL Support * Consulting * Administration http://www.olindata.com On Wed, Jan 28, 2009 at 3:21 AM, JD jd1...@gmail.com wrote: OK, I located a weblog on how to change the password and it worked. I then stopped and started the serever, and this is what it belched out in the mysqld.log: 090127 18:17:16 [Note] /usr/libexec/mysqld: Normal shutdown 090127 18:17:16 InnoDB: Starting shutdown... 090127 18:17:17 InnoDB: Shutdown completed; log sequence number 0 44044 090127 18:17:17 [Note] /usr/libexec/mysqld: Shutdown complete 090127 18:17:17 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended 090127 18:17:18 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql /usr/libexec/mysqld: Table 'mysql.plugin' doesn't exist 090127 18:17:18 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it. 090127 18:17:18 InnoDB: Started; log sequence number 0 44044 090127 18:17:18 [ERROR] Can't open and lock privilege tables: Table 'mysql.servers' doesn't exist 090127 18:17:18 [ERROR] Column count of mysql.db is wrong. Expected 22, found 20. Created with MySQL 50045, now running 50130. Please use mysql_upgrade to fix this error. 090127 18:17:18 [ERROR] mysql.user has no `Event_priv` column at position 29 090127 18:17:18 [ERROR] Cannot open mysql.event 090127 18:17:18 [ERROR] Event Scheduler: An error occurred when initializing system tables. Disabling the Event Scheduler. 090127 18:17:18 [Note] /usr/libexec/mysqld: ready for connections. Version: '5.1.30' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL) by Remi So, how do I fix those errors about 'mysql.plugin' and mysql.db and mysql.user For that matter why does it even have a mysql.user ??? Should it not be mysql.root? Walter Heck wrote: We're slowly getting there ;) that is the root account for the mysql server, not the linux root account :) when you installed mysql, you chose a root password. If you didn't, look up how to reset your mysql root account's password. Plenty of info on the web :) good luck! Walter OlinData: Professional services for MySQL Support * Consulting * Administration http://www.olindata.com On Wed, Jan 28, 2009 at 1:47 AM, JD jd1...@gmail.com wrote: I did the chmod. Then # ls -latrR /var/lib/mysql /var/lib/mysql: total 20548K drwxrwxr-x 2 mysql mysql 4096 2007-12-15 15:36 test/ -rw-rw-r-- 1 mysql mysql 5242880 2007-12-15 15:36 ib_logfile1 drwxrwxr-x 2 mysql mysql 4096 2007-12-15 16:10 ampache/ drwxrwxr-x 2 mysql mysql 4096 2009-01-21 14:12 mysql/ drwxr-xr-x 53 root root 4096 2009-01-27 14:14 ../ srwxrwxrwx 1 mysql mysql0 2009-01-27 16:35 mysql.sock= -rw-rw-r-- 1 mysql mysql 5242880 2009-01-27 16:35 ib_logfile0 -rw-rw-r-- 1 mysql mysql 10485760 2009-01-27 16:35 ibdata1 drwxrwxr-x 5 mysql mysql 4096 2009-01-27 16:35 ./
Are dates stored as String? Or Integer?
Are date columns stored as String or Integer in a MyISAM table? I am trying to squeeze more speed from my application and a date column is used in most of the indexes for my tables. I'm wondering if changing the Date data type to an Integer is going to speed the queries up. I'm using Delphi and internally it represents dates as float so using integers will speed up the Delphi code. But the main slow down I have is with executing the queries. The dates are used in the indexes, sorting, and in a few table joins. So is there a speed advantage of switching the dates to integer? TIA Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Are dates stored as String? Or Integer?
Absolutely, there is a significant speed benefit from using appropriate date/timestamp will speed up your lookups. They are stored not as ints per-se but are binary encoded in a similar manner. Read this carefully before before you undertake any conversions. http://dev.mysql.com/doc/refman/5.0/en/date-and-time-types.html You may also save considerable space.. from http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html DATE3 bytes TIME3 bytes DATETIME8 bytes TIMESTAMP 4 bytes YEAR1 byte - michael dykman On Wed, Jan 28, 2009 at 12:31 AM, mos mo...@fastmail.fm wrote: Are date columns stored as String or Integer in a MyISAM table? I am trying to squeeze more speed from my application and a date column is used in most of the indexes for my tables. I'm wondering if changing the Date data type to an Integer is going to speed the queries up. I'm using Delphi and internally it represents dates as float so using integers will speed up the Delphi code. But the main slow down I have is with executing the queries. The dates are used in the indexes, sorting, and in a few table joins. So is there a speed advantage of switching the dates to integer? TIA Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=mdyk...@gmail.com -- - michael dykman - mdyk...@gmail.com - All models are wrong. Some models are useful. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Are dates stored as String? Or Integer?
At 11:43 PM 1/27/2009, Michael Dykman wrote: Absolutely, there is a significant speed benefit from using appropriate date/timestamp will speed up your lookups. They are stored not as ints per-se but are binary encoded in a similar manner. Read this carefully before before you undertake any conversions. http://dev.mysql.com/doc/refman/5.0/en/date-and-time-types.html You may also save considerable space.. from http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html DATE3 bytes TIME3 bytes DATETIME8 bytes TIMESTAMP 4 bytes YEAR1 byte Michael, So if I understand it correctly, if I switch from Date to Int(8) 4 bytes to represent 20080125, then the queries should be faster? Because it doesn't have to encode the date to a 3 byte integer or decode it when retrieving the date value? Mike - michael dykman On Wed, Jan 28, 2009 at 12:31 AM, mos mo...@fastmail.fm wrote: Are date columns stored as String or Integer in a MyISAM table? I am trying to squeeze more speed from my application and a date column is used in most of the indexes for my tables. I'm wondering if changing the Date data type to an Integer is going to speed the queries up. I'm using Delphi and internally it represents dates as float so using integers will speed up the Delphi code. But the main slow down I have is with executing the queries. The dates are used in the indexes, sorting, and in a few table joins. So is there a speed advantage of switching the dates to integer? TIA Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Are dates stored as String? Or Integer?
For the DATE type, the comparisons are performed aginst raw binary data as an integer would be. I read your first post too hastily and thought you were proposigng to store dates as string. (don't laugh, I have seen this done by people who should know better). I don't think you are going to squeeze out any performance benefit by converting to int.. you might even lose some ground to the special handling your date-as-int your app might have to do. - michael dykman On Wed, Jan 28, 2009 at 1:30 AM, mos mo...@fastmail.fm wrote: At 11:43 PM 1/27/2009, Michael Dykman wrote: Absolutely, there is a significant speed benefit from using appropriate date/timestamp will speed up your lookups. They are stored not as ints per-se but are binary encoded in a similar manner. Read this carefully before before you undertake any conversions. http://dev.mysql.com/doc/refman/5.0/en/date-and-time-types.html You may also save considerable space.. from http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html DATE3 bytes TIME3 bytes DATETIME8 bytes TIMESTAMP 4 bytes YEAR1 byte Michael, So if I understand it correctly, if I switch from Date to Int(8) 4 bytes to represent 20080125, then the queries should be faster? Because it doesn't have to encode the date to a 3 byte integer or decode it when retrieving the date value? Mike - michael dykman On Wed, Jan 28, 2009 at 12:31 AM, mos mo...@fastmail.fm wrote: Are date columns stored as String or Integer in a MyISAM table? I am trying to squeeze more speed from my application and a date column is used in most of the indexes for my tables. I'm wondering if changing the Date data type to an Integer is going to speed the queries up. I'm using Delphi and internally it represents dates as float so using integers will speed up the Delphi code. But the main slow down I have is with executing the queries. The dates are used in the indexes, sorting, and in a few table joins. So is there a speed advantage of switching the dates to integer? TIA Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=mdyk...@gmail.com -- - michael dykman - mdyk...@gmail.com - All models are wrong. Some models are useful. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Performances between different filesystems
Hello, I look for updated performances between different file systems. I have only found performances computed between 2003 and 2005, and for x86. Do you have any updated performance tables for XFS, ReiserFS and Ext3 file systems, on linux x86 AND x86_64 ? And with or without raid ? Thanks -- Sébastien Moretti -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
