Re: Cannot execute query - Can't find file: (error: 9)
According to perror: perror 9 Error code 9: Bad file number This is an operating system error code: http://dev.mysql.com/doc/mysql/en/operating-system-error-codes.html Out of curiosity, have you tried running myisamchk or some CHECK TABLE commands yet? http://dev.mysql.com/doc/mysql/en/table-maintenance.html Cheers, --V Frank Bax wrote: At 10:07 AM 4/5/05, Frank Bax wrote: At 04:27 PM 4/4/05, Frank Bax wrote: Cannot execute query. snip my SQL statement Can't find file: './donor/list_lst.frm' (errno: 9) - - I got the same error last week on a different table. Today I notice that there is a table in another database on same system producing the same error. I attempted to access mysql cli, but it just locked up after entering password. Start/stop mysql and mysql cli at least started but issued errors about some tables even before I entered a command. I decided to reboot and the problem goes away (for a while). MySQL 4.0.20 - OpenBSD 3.6 Forgot to mention a couple of things: 1) The file that mysql complains about does exist. # ls -ltr donor/list*.frm -rw-rw 1 _mysql _mysql 8694 Jan 15 09:43 donor/list_lst.frm 2) When problem recurs (as it did on both databases this morning), the same file in each database is affected each time error appears. 3) In both databases (on same system) it is frm files in the error message. Since OpenBSD 2.8, there is a default limit of 128 open files for daemon processes. Add --open-files-limit=2048 to mysql startup. http://dev.mysql.com/doc/mysql/en/openbsd-2-8.html The suggested changes to /etc/login.conf were not necessary on my system. Does MySQL ever close the file(s) associated with table(s), or once open do they stay open until shutdown? Frank -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL server disconnects when executing simple Select statements++
You say it usually crashes near the same record? Could you post the record information and also the query which is being run? Also, is there any information in your hostname.err file? If mysqld is bailing (and it appears that it is), it ought to be writing something useful to the error log. Cheers, --V mos wrote: I have a compiled application that accesses a dedicated MySQL 4.1.1 server with MyISAM tables on Win XP. For some reason when the application is in a loop doing simple single table Select statements, the MySQL server after 48 seconds of processing will upchuck with the error Can't connect to MySQL server on 'localhost' (10048). This machine has the server and application on it and no one else is using it. MaxUsedConnections=3 and connections=3974 after it crashes. It is doing about 20 queries per second before it crashes. It usually crashes near the same record. Seconds after the crash if I have another application do a Show status I get an error Lost connection to MySQL server during query. If I wait a few seconds and re-run it, I get the status results. It is running on Win XP AMD 3500+ with 1g ram. There is plenty of memory because the queries are returning only a dozen rows, maybe less. Any idea how I can eliminate the crashing? Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump: Got errno 5 on write
According to the output of perror: perror 5 Error code 5: I/O error Are you having problems with your disk? Maybe syncing or bad sectors or even something as simple as out of space? Best of luck, --V Mihail Manolov wrote: Have you seen this before: mysqldump: Got errno 5 on write I have started getting this error recently in my email box - the message is result of my daily backup cronjob. Ideas? rgrdz, Mihail Manolov -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Cascade problem now error:
Have you tried perror yet?
[EMAIL PROTECTED] (ping-300) 120 perror 150
MySQL error: 150 = Foreign key constraint is incorrectly formed
Cheers,
--V
Scott Purcell wrote:
Thanks,
I updated my script and all looks good. But now I get an error when tryng to
issue this command.
Any ideas?
Thanks,
Scott
mysql ALTER TABLE MENU_GROUP_REL ADD CONSTRAINT PK_MENU_GROUP_REL FOREIGN
KEY(d
ata_id) REFERENCES MENU_GROUP(id) ON DELETE CASCADE;
ERROR 1005: Can't create table '.\builder\#sql-260_d.frm' (errno: 150)
mysql
- mysql \s
--
mysql Ver 12.21 Distrib 4.0.15, for Win95/Win98 (i32)
Connection id: 13
Current database: builder
Current user: [EMAIL PROTECTED]
SSL:Not in use
Server version: 4.0.15-max-debug
Protocol version: 10
Connection: localhost via TCP/IP
Client characterset:latin1
Server characterset:latin1
TCP port: 3306
Uptime: 10 days 1 min 3 sec
Threads: 1 Questions: 364 Slow queries: 0 Opens: 115 Flush tables: 1 Open t
ables: 0 Queries per second avg: 0.000 Memory in use: 8324K Max memory used:
8631K
--
UPDATED SCRIPT BELOW:
DROP DATABASE builder;
GRANT ALL PRIVILEGES ON *.* TO [EMAIL PROTECTED] IDENTIFIED BY 'spurcell' WITH
GRANT OPTION;
// sequence stuff
// mysql CREATE TABLE sequence (id INT NOT NULL);
//mysql INSERT INTO sequence VALUES (0);
//Use the table to generate sequence numbers like this:
//mysql UPDATE sequence SET id=LAST_INSERT_ID(id+1);
//mysql SELECT LAST_INSERT_ID();
CREATE DATABASE builder;
use builder;
CREATE TABLE menu_sequence (id INT NOT NULL);
insert into menu_sequence VALUES (0);
CREATE TABLE MENU_GROUP (
id int NOT NULL,
parent_id int NOT NULL DEFAULT '0',
sort int,
visible VARCHAR(1) NOT NULL DEFAULT 'T',
) type=INNODB;
ALTER TABLE MENU_GROUP ADD CONSTRAINT PK_MENU_GROUP PRIMARY KEY(id);
UPDATE menu_sequence SET id=LAST_INSERT_ID(id+1);
INSERT INTO MENU_GROUP values (last_insert_id(), 0, 1, 'T');
UPDATE menu_sequence SET id=LAST_INSERT_ID(id+1);
INSERT INTO MENU_GROUP values (last_insert_id(), 0, 2, 'T');
UPDATE menu_sequence SET id=LAST_INSERT_ID(id+1);
INSERT INTO MENU_GROUP values (last_insert_id(), 0, 3, 'T');
UPDATE menu_sequence SET id=LAST_INSERT_ID(id+1);
INSERT INTO MENU_GROUP values (last_insert_id(), 0, 4, 'T');
UPDATE menu_sequence SET id=LAST_INSERT_ID(id+1);
INSERT INTO MENU_GROUP values (last_insert_id(), 1, 1, 'T');
UPDATE menu_sequence SET id=LAST_INSERT_ID(id+1);
INSERT INTO MENU_GROUP values (last_insert_id(), 3, 1, 'T');
UPDATE menu_sequence SET id=LAST_INSERT_ID(id+1);
INSERT INTO MENU_GROUP values (last_insert_id(), 4, 1, 'T');
CREATE TABLE MENU_TYPE (
id INT NOT NULL AUTO_INCREMENT,
attribute_type varchar(200) NOT NULL,
primary key (id)
);
INSERT INTO MENU_TYPE (attribute_type) values ('jsp'), ('menu'), ('cat_name');
CREATE TABLE MENU_GROUP_REL (
menu_type varchar(200),
data_id int NOT NULL,
display_name varchar(250),
link varchar(250),
) type=INNODB;
ALTER TABLE MENU_GROUP_REL ADD CONSTRAINT PK_MENU_GROUP_REL FOREIGN
KEY(data_id) REFERENCES MENU_GROUP(id) ON DELETE CASCADE;
INSERT INTO MENU_GROUP_REL values (1, 1, 'Company', 'companyInfo.jsp');
INSERT INTO MENU_GROUP_REL values (3, 2, 'Communities', 'communities');
INSERT INTO MENU_GROUP_REL values (1, 3, 'Contact', 'contact.jsp');
INSERT INTO MENU_GROUP_REL values (1, 4, 'Inventory Homes', 'invHomes.jsp');
INSERT INTO MENU_GROUP_REL values (1, 5, 'About Me', 'about.jsp');
INSERT INTO MENU_GROUP_REL values (1, 6, 'Customer Service', 'custService.jsp');
INSERT INTO MENU_GROUP_REL values (1, 7, 'Open Sunday', 'opensunday.jsp');
-Original Message-
From: Artem Koltsov [mailto:[EMAIL PROTECTED]
Sent: Thursday, January 27, 2005 10:29 AM
To: Scott Purcell; mysql@lists.mysql.com
Subject: RE: cascade on delete problem
Hello Scott,
Make sure your tables are InnoDB type:
CREATE TABLE table_name ( table_def ...) ENGINE=InnoDB;
If you have default MyISAM tables, it won't work because they don't support
foreign keys.
-Original Message-
From: Scott Purcell [mailto:[EMAIL PROTECTED]
Sent: Thursday, January 27, 2005 10:17 AM
To: mysql@lists.mysql.com
Subject: cascade on delete problem
Hello,
I apologize for a possible simple question, but I am having
trouble with the below code.
I have three simple tables. In short, menu_group has an id,
that is referenced in the menu_group_rel. When a user
deletes an id from the menu_group, I wanted the entry in
menu_group_rel (data_id) to also be deleted. They act as
one piece of data.
I have gone through the docs, but when I delete a line from
the menu_group, it does NOT delete the entry from the menu_group_rel?
Does anyone see anything wrong with the following?
Thanks,
SQL: ###
DROP DATABASE builder2;
GRANT ALL PRIVILEGES ON *.* TO [EMAIL PROTECTED] IDENTIFIED
BY 'spurcell' WITH GRANT OPTION;
CREATE
Re: calculated field
To the best of my knowledge MySQL does not have a way to do this on the fly such that a field value is contigent upon the values of other fields in the table. Does it have to be stored in the database at all? Seems to me that calculated values often are best handled at query time rather than taking up unnecessary bytes. Of course, it would depend upon upon the amount of data stored and the amount which needs to be calculated. If that does not work for you, you may want to investigate your options for calculation during table creation and data insertion. There are plenty of functions which will help with this and they're all well documented in the online manual. Cheers, --V Javier wrote: Hi All I've an Excel document that have some data but there are a column that has a formula related to other cells of the same row. Now I need to put data in a db but the problem is with this calculated data. A friend of mine that use Oracle told me that I could define a field like field1 = field2 + field3, but I can't discover this option in Mysql. It's possible to make it ? Any other idea about it ? Thanks in advance Javier -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Illegal mix of collations with 4.1.7
Ever since we upgraded to 4.1.7, we've been seeing a lot of errors similiar to this one: ERROR 1267 (HY000): Illegal mix of collations (utf8_general_ci,IMPLICIT) and (latin1_swedish_ci,COERCIBLE) for operation 'locate' The query which generated this particular error is this: SELECT COUNT(*) FROM holdsplaced WHERE timestampDatePlaced = 2004113004 AND INSTR( sPatronName, 'bubba' ) != 0; But other queries have also been kicking this error out as well. The server is being started with the following options (some altered to protect the innocent): mysqld would have been started with the following arguments: --basedir=/dbs/tpp/mysql-4.1 --datadir=/dbs/tpp/mysql-4.1/data --port=portnum --socket=/dbs/tpp/mysql-4.1/mysql.sock --user=username --log-error=/dbs/tpp/mysql-4.1/logs/ping.err --log=/dbs/tpp/mysql-4.1/logs/ping.log --default-character-set=utf8 The problem, I'm sure, is that --default-character-set=utf8 option, but I don't know much beyond that. Google searches aren't helping much with this one. Something is out of alignment somewhere, I'm just not sure what it is. Any help? Thanks in advance, --V -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Illegal mix of collations with 4.1.7
Ah! Many thanks. That appears to be our problem here: mysql show variables like 'colla%'; +--+---+ | Variable_name| Value | +--+---+ | collation_connection | latin1_swedish_ci | | collation_database | utf8_general_ci | | collation_server | utf8_general_ci | +--+---+ I'll work on getting all the connections onto the same collation page. Very many thanks again! --V Santino wrote: Hello, I think your tables have a collation different from the connection collation. Open mysql client: mysql show variables like 'colla%'; +--+---+ | Variable_name| Value | +--+---+ | collation_connection | latin1_swedish_ci | | collation_database | latin1_swedish_ci | | collation_server | latin1_swedish_ci | +--+---+ 3 rows in set (0.04 sec) and if are different set the collation in your my.cnf file. Santino At 8:59 -0800 30-11-2004, V. M. Brasseur wrote: Ever since we upgraded to 4.1.7, we've been seeing a lot of errors similiar to this one: ERROR 1267 (HY000): Illegal mix of collations (utf8_general_ci,IMPLICIT) and (latin1_swedish_ci,COERCIBLE) for operation 'locate' The query which generated this particular error is this: SELECT COUNT(*) FROM holdsplaced WHERE timestampDatePlaced = 2004113004 AND INSTR( sPatronName, 'bubba' ) != 0; But other queries have also been kicking this error out as well. The server is being started with the following options (some altered to protect the innocent): mysqld would have been started with the following arguments: --basedir=/dbs/tpp/mysql-4.1 --datadir=/dbs/tpp/mysql-4.1/data --port=portnum --socket=/dbs/tpp/mysql-4.1/mysql.sock --user=username --log-error=/dbs/tpp/mysql-4.1/logs/ping.err --log=/dbs/tpp/mysql-4.1/logs/ping.log --default-character-set=utf8 The problem, I'm sure, is that --default-character-set=utf8 option, but I don't know much beyond that. Google searches aren't helping much with this one. Something is out of alignment somewhere, I'm just not sure what it is. Any help? Thanks in advance, --V -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: C api incompatability from 3.x to 4.1
I provided the list below for our programmers, who also are dealing with a switch from 3.23 to 4.1. Perhaps it would be of some help for you. Cheers, --V - We've already hit a couple of API-related problems with the new version of MySQL. To try to make things a little easier, and because I love our programmers, I've sifted through the change history of MySQL 4.1 to pick out all the changes specifically related to the C API. Most won't apply to us. For a complete list of all MySQL changes, hit this link: http://dev.mysql.com/doc/mysql/en/News.html And now for the list: * Added new mysql_get_server_version() C API client function. * Added mysql_set_server_option() C API client function to allow multiple statement handling in the server to be enabled or disabled. * The mysql_next_result() C API function now returns -1 if there are no more result sets. * Warning: Incompatible change! Renamed the C API mysql_prepare_result() function to mysql_get_metadata() as the old name was confusing. * Added mysql_sqlstate() and mysql_stmt_sqlstate() C API client functions that return the SQLSTATE error code for the last error. * Warning: Incompatible change! Renamed prepared statements C API functions: Old Name New Name mysql_bind_param() mysql_stmt_bind_param() mysql_bind_result() mysql_stmt_bind_result() mysql_prepare() mysql_stmt_prepare() mysql_execute() mysql_stmt_execute() mysql_fetch() mysql_stmt_fetch() mysql_fetch_column() mysql_stmt_fetch_column() mysql_param_count() mysql_stmt_param_count() mysql_param_result() mysql_stmt_param_metadata() mysql_get_metadata() mysql_stmt_result_metadata() mysql_send_long_data() mysql_stmt_send_long_data() Now all functions that operate with a MYSQL_STMT structure begin with the prefix mysql_stmt_. * Warning: Incompatible change! The signature of the mysql_stmt_prepare() function was changed to int mysql_stmt_prepare(MYSQL_STMT *stmt, const char *query, unsigned long length). To create a MYSQL_STMT handle, you should use the mysql_stmt_init() function, not mysql_stmt_prepare(). * C API enhancement: SERVER_QUERY_NO_INDEX_USED and SERVER_QUERY_NO_GOOD_INDEX_USED flags are now set in the server_status field of the MYSQL structure. It is these flags that make the query to be logged as slow if mysqld was started with --log-slow-queries --log-queries-not-using-indexes. * Added support for unsigned integer types to prepared statement API (Bug #3035). * Warning: Incompatible change! C API change: mysql_shutdown() now requires a second argument. This is a source-level incompatibility that affects how you compile client programs; it does not affect the ability of compiled clients to communicate with older servers. See section 21.2.3.51 mysql_shutdown(). * Fixed a bug in client-side conversion of string column to MYSQL_TIME application buffer (prepared statements API). (Bug #4030) * Fixed a buffer overflow in prepared statements API (libmysqlclient) when a statement containing thousands of placeholders was executed. (Bug #5194) * The mysql_change_user() C API function now frees all prepared statements associated with the connection. (Bug #5315) * Fixed bug in libmysqlclient that fetched column defaults. * Fixed mysql_stmt_send_long_data() behavior on second execution of prepared statement and in case when long data had zero length. (Bug #1664) * You can now call mysql_stmt_attr_set(..., STMT_ATTR_UPDATE_MAX_LENGTH) to tell the client library to update MYSQL_FIELD-max_length when doing mysql_stmt_store_result(). (Bug #1647). * Fixed memory leak in the client library when statement handle was freed on closed connection (call to mysql_stmt_close after mysql_close). (Bug #3073) * Fixed mysql_stmt_affected_rows() call to always return number of rows affected by given statement. (Bug #2247) * Fix for a bug that caused client/server communication to be broken when mysql_set_server_option() or mysql_get_server_option() were invoked. (Bug #2207) * The MySQL server did not report any error if a statement (submitted through mysql_real_query() or mysql_stmt_prepare()) was terminated by garbage characters. This can happen if you pass a wrong length parameter to these functions. The result was that the garbage characters were written into the binary log. (Bug #2703) * Fixed bug in client library that caused mysql_stmt_fetch and mysql_stmt_store_result() to hang if they were called without prior call of mysql_stmt_execute(). Now they give an error instead. (Bug #2248) * Fixed a bug in mysql_stmt_close(), which hung up when attempting to close statement after failed mysql_stmt_fetch(). (Bug #4079) * Fixed potential memory overrun in mysql_real_connect() (which required a compromised DNS server and certain operating systems). (Bug #4017) * Fixed a bug that caused libmysql to crash when attempting to fetch a value of MEDIUMINT column. (Bug #5126) * Fixed that
Undefined symbols compiling against 4.1.7 on Solaris 2.8
We're trying to get 4.1.7 to play nicely with our code, but something isn't cooperating. First of all, the MySQL installation: Version 4.1.7, compiled from source using GCC 3.3. The source compile is required because we need a lot of default settings to use our own paths. This is the configure used for the compile (some values altered here to protect the innocent): CC=gcc CFLAGS=-O3 \ CXX=gcc CXXFLAGS=-O3 -felide-constructors -fno-exceptions -fno-rtti \ ./configure --prefix=/path/to/mysql \ --libexecdir=/path/to/mysql/bin \ --with-mysqld-user=ouruser \ --with-unix-socket-path=/path/to/mysql.sock --with-tcp-port=ourport \ --without-docs --without-bench --with-extra-charsets=complex \ --enable-thread-safe-client --enable-local-infile \ --localstatedir=/path/to/mysql/data \ --with-prefix=/path/to/mysql \ --with-low-memory --enable-assembler --disable-shared It appears to function well on its own. However, when attempting to compile a program which requires libmysqlclient.a... compiling -o progname /path/to/progname.c Undefined first referenced symbol in file __floatdisf /path/to/mysql/lib/libmysqlclient.a(libmysql.o) __floatdidf /path/to/mysql/lib/libmysqlclient.a(libmysql.o) __cmpdi2 /path/to/mysql/lib/libmysqlclient.a(libmysql.o) ld: fatal: Symbol referencing errors. No output written to progname compile:error=256,command=compiling -o progname /path/to/progname.c It's worth noting that we are only experiencing this problem on Solaris 2.8. Another 4.1.7 installation on OSF1 5.1 is chugging along nicely with no complaints. Google contains many references to these undefined symbols, encountered mostly when compiling other products. Yet none of those references contain an actual SOLUTION to the problem. Could someone out there with a bit more ld/gcc/C API experience than I have (which is not setting the bar high, I assure you) lend a hand dispersing these clouds? Many thanks, --V -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Undefined symbols compiling against 4.1.7 on Solaris 2.8
Unfortunately, the --disable-shared is intentional. For various reasons, our programs use static libraries whenever possible rather than dynamically linking. Do you think the LDFLAGS and/or --enable-libgcc still help for static libraries? Many thanks for your help so far, --V Ken Menzel wrote: I ran into problems trying to use static libraries on Solaris 9. I think you may not really want --disable-shared, the shared libs do make life a bit easier. But try these solutions: LDFLAGS=$LDFLAGS -L/usr/local/lib/gcc-lib/sparc-sun-solaris2.8/3.0/ -lgcc or --enable-libgcc if you are using a recent gnu configure. This link the gcc lib with your program. I don't know what you are trying to link but here is a php reference. http://bugs.php.net/bug.php?id=16826edit=1 - Original Message - From: V. M. Brasseur [EMAIL PROTECTED] To: MySQL Listserv [EMAIL PROTECTED] Sent: Wednesday, November 03, 2004 1:18 PM Subject: Undefined symbols compiling against 4.1.7 on Solaris 2.8 We're trying to get 4.1.7 to play nicely with our code, but something isn't cooperating. First of all, the MySQL installation: Version 4.1.7, compiled from source using GCC 3.3. The source compile is required because we need a lot of default settings to use our own paths. This is the configure used for the compile (some values altered here to protect the innocent): CC=gcc CFLAGS=-O3 \ CXX=gcc CXXFLAGS=-O3 -felide-constructors -fno-exceptions -fno-rtti \ ./configure --prefix=/path/to/mysql \ --libexecdir=/path/to/mysql/bin \ --with-mysqld-user=ouruser \ --with-unix-socket-path=/path/to/mysql.sock --with-tcp-port=ourport \ --without-docs --without-bench --with-extra-charsets=complex \ --enable-thread-safe-client --enable-local-infile \ --localstatedir=/path/to/mysql/data \ --with-prefix=/path/to/mysql \ --with-low-memory --enable-assembler --disable-shared It appears to function well on its own. However, when attempting to compile a program which requires libmysqlclient.a... compiling -o progname /path/to/progname.c Undefined first referenced symbol in file __floatdisf /path/to/mysql/lib/libmysqlclient.a(libmysql.o) __floatdidf /path/to/mysql/lib/libmysqlclient.a(libmysql.o) __cmpdi2 /path/to/mysql/lib/libmysqlclient.a(libmysql.o) ld: fatal: Symbol referencing errors. No output written to progname compile:error=256,command=compiling -o progname /path/to/progname.c It's worth noting that we are only experiencing this problem on Solaris 2.8. Another 4.1.7 installation on OSF1 5.1 is chugging along nicely with no complaints. Google contains many references to these undefined symbols, encountered mostly when compiling other products. Yet none of those references contain an actual SOLUTION to the problem. Could someone out there with a bit more ld/gcc/C API experience than I have (which is not setting the bar high, I assure you) lend a hand dispersing these clouds? Many thanks, --V -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Undefined symbols compiling against 4.1.7 on Solaris 2.8
On a whim, I removed the --disable-shared from the configure options and was greeted by a lot of unresolved symbols from libz.a and this pleasant message: ld: fatal: relocations remain against allocatable but non-writable sections collect2: ld returned 1 exit status Google search says to use GNU ld, but that didn't help matters any. Right now, it's either (1) --disable-shared set, MySQL 4.1.7 completes the 'make' successfully but there are unresolved libmysqlclient.a symbols, or (2) --disable-shared NOT set and MySQL 4.1.7 fails during the make at the point where libtool is handling libmysqlclient.la Neither seems like fun to me. Anyone have any other ideas/pointers? --V V. M. Brasseur wrote: Unfortunately, the --disable-shared is intentional. For various reasons, our programs use static libraries whenever possible rather than dynamically linking. Do you think the LDFLAGS and/or --enable-libgcc still help for static libraries? Many thanks for your help so far, --V Ken Menzel wrote: I ran into problems trying to use static libraries on Solaris 9. I think you may not really want --disable-shared, the shared libs do make life a bit easier. But try these solutions: LDFLAGS=$LDFLAGS -L/usr/local/lib/gcc-lib/sparc-sun-solaris2.8/3.0/ -lgcc or --enable-libgcc if you are using a recent gnu configure. This link the gcc lib with your program. I don't know what you are trying to link but here is a php reference. http://bugs.php.net/bug.php?id=16826edit=1 - Original Message - From: V. M. Brasseur [EMAIL PROTECTED] To: MySQL Listserv [EMAIL PROTECTED] Sent: Wednesday, November 03, 2004 1:18 PM Subject: Undefined symbols compiling against 4.1.7 on Solaris 2.8 We're trying to get 4.1.7 to play nicely with our code, but something isn't cooperating. First of all, the MySQL installation: Version 4.1.7, compiled from source using GCC 3.3. The source compile is required because we need a lot of default settings to use our own paths. This is the configure used for the compile (some values altered here to protect the innocent): CC=gcc CFLAGS=-O3 \ CXX=gcc CXXFLAGS=-O3 -felide-constructors -fno-exceptions -fno-rtti \ ./configure --prefix=/path/to/mysql \ --libexecdir=/path/to/mysql/bin \ --with-mysqld-user=ouruser \ --with-unix-socket-path=/path/to/mysql.sock --with-tcp-port=ourport \ --without-docs --without-bench --with-extra-charsets=complex \ --enable-thread-safe-client --enable-local-infile \ --localstatedir=/path/to/mysql/data \ --with-prefix=/path/to/mysql \ --with-low-memory --enable-assembler --disable-shared It appears to function well on its own. However, when attempting to compile a program which requires libmysqlclient.a... compiling -o progname /path/to/progname.c Undefined first referenced symbol in file __floatdisf /path/to/mysql/lib/libmysqlclient.a(libmysql.o) __floatdidf /path/to/mysql/lib/libmysqlclient.a(libmysql.o) __cmpdi2 /path/to/mysql/lib/libmysqlclient.a(libmysql.o) ld: fatal: Symbol referencing errors. No output written to progname compile:error=256,command=compiling -o progname /path/to/progname.c It's worth noting that we are only experiencing this problem on Solaris 2.8. Another 4.1.7 installation on OSF1 5.1 is chugging along nicely with no complaints. Google contains many references to these undefined symbols, encountered mostly when compiling other products. Yet none of those references contain an actual SOLUTION to the problem. Could someone out there with a bit more ld/gcc/C API experience than I have (which is not setting the bar high, I assure you) lend a hand dispersing these clouds? Many thanks, --V -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: rounding problem
This must be a bug that was fixed in the 4.1 version. I see the same thing as Tom, using both 3.23.49 and 4.0.20... Oh, wait. The ever-helpful manual comes through again: From the manual documentation on ROUND(): Note that the behavior of ROUND() when the argument is halfway between two integers depends on the C library implementation. Different implementations round to the nearest even number, always up, always down, or always toward zero. If you need one kind of rounding, you should use a well-defined function such as TRUNCATE() or FLOOR() instead. http://dev.mysql.com/doc/mysql/en/Mathematical_functions.html#IDX1363 There are many references to this bug in the MySQL Bug Tracker. All of them reinforce that this is not a bug but rather a variance in C library implementation. http://bugs.mysql.com/search.php?search_for=roundstatus=Allseverity=alllimit=10order_by=cmd=displaydirection=ASCbug_type=Anyassign=php_os=phpver=bug_age=0 Cheers, --V Edgar Meij wrote: Hmmm, peculiar... Tried it on 4.1.6-gamma-nt and works fine: SELECT ROUND(3.575, 2); = 3,57 SELECT ROUND(3.565, 2); = 3.56 The round() function probably cuts off the last bit... More info: http://lists.mysql.com/myodbc/8 Regards, Edgar -Oorspronkelijk bericht- Van: Tom Butterworth [mailto:[EMAIL PROTECTED] Verzonden: maandag 25 oktober 2004 19:35 Aan: [EMAIL PROTECTED] Onderwerp: rounding problem Hi I seem to be having problems returning the expected results when using the mysql ROUND() function. Rounding 3.565 to 2 decimal places i would expect to return 3.57 however using SELECT ROUND(3.565, 2); it returns 3.56. While using SELECT ROUND(3.575, 2); works as expected returning 3.58. I am using mysql version 3.23.54. Any help much appreciated. Cheers Buttie -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Command that I believe should work...
If you make that column a TIMESTAMP data type and leave the default as NULL, it will automatically use the current date/time if no value is entered. Please note, this will only work this way for the first TIMESTAMP column in the table. Also, if ever you update a row containing TIMESTAMP columns, the first TIMESTAMP column will automatically be updated to use the new current date/time. I suggest hitting the user manual or Paul DuBois' 'MySQL' book for further information. http://dev.mysql.com/doc/mysql/en/Date_and_time_types.html Cheers, --V Robert Adkins wrote: ...but doesn't. I am attempting to create a table using the following... CREATE TABLE INVOICE ( INV_NUMBER INTPRIMARY KEY, CUS_CODEINT NOT NULL REFERENCES CUSTOMER(CUS_CODE), INV_DATE DATETIME DEFAULT NOW() NOT NULL, ); I receive an error message stating that there is an error with 'NOW()' Which doesn't make sense to me. I understand both Oracle and MS-SQL Server use a similar format for putting the date and time into a field when a new row is created. At least that is my goal. Is there a very different method of doing this under MySQL 4.0.21? Thanks for any assistance you may offer. -Rob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Connector/J Null pointer in initializePropsFromServer
I am passing on this question from a coworker: Hello -- This has been posted on the forums.mysql.com for a week with no replies. We have been using Connector J 3.0.6 for more than a year at hundreds of customer sites and on our in house servers. When I updated the jar to 3.0.15-ga we can still get to our external sites running MySQL 3.23.37. Our in house server has MySQL 3.23.37 runing on port 1031 and MySQL 4.0.20 running on port 1032. When connecting to either sever with 3.0.15-ga, I get a null pointer error. (Our in house servers are Suns and Alphas.) When I switch back to Connector J 3.0.6 with the exact same java code, everything works! Our CAPI programs running on the server work fine, the MySQL control center works fine. What properties are being intitialized? Why is it crashing? The connection url is: jdbc:mysql://205.111.111.111:1032/statistics?user=iiipassword=M4useUnicode=truecharacterEncoding=utf8 SQLException: Unable to connect to any hosts due to exception: java.lang.NullPointerException ** BEGIN NESTED EXCEPTION ** java.lang.NullPointerException STACKTRACE: java.lang.NullPointerException at com.mysql.jdbc.Connection.initializePropsFromServer(Connection.java:3237) at com.mysql.jdbc.Connection.createNewIO(Connection.java:1799) at com.mysql.jdbc.Connection.init(Connection.java:450) at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:411) at java.sql.DriverManager.getConnection(DriverManager.java:512) at java.sql.DriverManager.getConnection(DriverManager.java:193) at com.iii.MySQLConnect.openDatabase(MySQLConnect.java:124) ... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to extract Particular Tables from a Dump file taken with mysqldump
Why don't you use the mysqldump program to dump only those tables you want and not the entire database? http://dev.mysql.com/doc/mysql/en/mysqldump.html Cheers, --V Buchibabu wrote: Hi, Please let me know how to extract few tables from a dump file, which is taken with mysqldump. I know it extracting a file. But the thing is I would like to do it with mysql. Thanks, Buchibabu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to extract Particular Tables from a Dump file taken with mysqldump
There is no MySQL-supplied utility for this purpose. Perl, awk, or some other scripting language will have to be called into play. --V Anil Doppalapudi wrote: The actual problem is we maintain regular all-databases mysqldump.it is a automated script and after taking complete backup we purged data later we identified that some required data is missed in a particular table. so we require restore of only that particular table. we tried extracting only that particular table data using so awk and other stuff but it is taking very long time that table contain 120 million records. we want to know is there any mysql utility to extract only specified table from entire dump Thanks -Original Message- From: V. M. Brasseur [mailto:[EMAIL PROTECTED] Sent: Friday, October 08, 2004 11:41 PM To: Buchibabu Cc: [EMAIL PROTECTED] Subject: Re: How to extract Particular Tables from a Dump file taken with mysqldump Why don't you use the mysqldump program to dump only those tables you want and not the entire database? http://dev.mysql.com/doc/mysql/en/mysqldump.html Cheers, --V Buchibabu wrote: Hi, Please let me know how to extract few tables from a dump file, which is taken with mysqldump. I know it extracting a file. But the thing is I would like to do it with mysql. Thanks, Buchibabu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
UTF8 pre-4.1?
*I* understand that Unicode utf8 support does not happen until version 4.1 (I can read the manual), but there are some in the office who are skeptical and somehow believe that there is support for it in earlier versions (as early as 3.23, according to them). To set all of our minds at ease, could someone else please confirm or deny for me that MySQL does not support utf8 prior to version 4.1? I don't believe I'm wrong in this case, but if I am I would very much like to know. Many thanks, --V -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: UTF8 pre-4.1?
Many thanks, Tim. I don't know to what use they intend to put the strings which they are storing, but I'll pass on this information to the developers with the question. Thanks again, --V Little, Tim wrote: There isn't support for it prior to that. But you absolutely can store and retrieve UTF8 with no problems in pre-4.1 versions. The only problems are functions like UpperCase and sorting facilities will likely behave inconsistently with respect to your expectations. Here we used 3.x and 4.1 (original Windows-binary release version, ie., preUTF8) and they worked beautifully (but we never used the collation/sorting of MySQL against the text fields. -Original Message- From: V. M. Brasseur [mailto:[EMAIL PROTECTED] Sent: Thursday, September 16, 2004 1:57 PM To: [EMAIL PROTECTED] Subject: UTF8 pre-4.1? *I* understand that Unicode utf8 support does not happen until version 4.1 (I can read the manual), but there are some in the office who are skeptical and somehow believe that there is support for it in earlier versions (as early as 3.23, according to them). To set all of our minds at ease, could someone else please confirm or deny for me that MySQL does not support utf8 prior to version 4.1? I don't believe I'm wrong in this case, but if I am I would very much like to know. Many thanks, --V -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: RESEND: Compilation of MySQL with OpenSSL in alternate location.
OpenSSH != OpenSSL. Just because you have one installed doesn't mean that the other is. Apparently the MySQL compilation using OpenSSL requires OpenSSH for some reason. You might want to try installing it and giving the compile another whirl: http://www.openssh.com/ Also, the exact output of the ./configure message you're receiving might help people get a better handle on exactly what the problem might be. Cheers, --V E SA wrote: All, I already sent this message once, but I got no answer. Has naybody successfully compiled MySQL with OpenSSL NOT in /usr/local/ssl? I can not use stunnel, and I have not found anything in google. Please let me know. - - - - - - - - - - - - - - - - - - - - - - - - - - - - Original post: All, I am trying to compile MySQL with OpenSSL in an alternate location ( /data/OpenSSL ). When I do that either configure will say that no installation of OpenSSH is there (even with the --with-openssl-includes= and --with-openssl-libs= ), or it will compile without OpenSSL support ( show variables like ... ). My configure is as follows: ./configure --prefix=/data/MySQL --exec-prefix=/data/MySQL \ --enable-thread-safe-client --enable-assembler \ --enable-local-infile --with-extra-charsets=complex \ --disable-shared --with-openssl=/data/OpenSSL \ --without-docs --with-vio --with-openssl-includes=/data/OpenSSL/include/openssl \ --with-openssl-libs=/data/OpenSSL/lib Has anybody successfully compiled MySQL with OpenSSL support in an alternate location? The MySQL version is 4.0.20. Any help would be appreciated. __ Do you Yahoo!? New and Improved Yahoo! Mail - Send 10MB messages! http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: RESEND: Compilation of MySQL with OpenSSL in alternate location.
E SA wrote: Mr. Brasseur, Miss Brasseur, but you could not have known so no offense is taken. OpenSSH is installed in the server in its default location. Still, no luck. Configure goes on without problems, and then make gives me the following error message: gcc -DDEFAULT_CHARSET_HOME=\/data/MySQL\ -DDATADIR=\/data/MySQL/var\ -DSHAREDIR=\/data/MySQL/share/mysql\ -DDONT_USE_RAID -I. -I. -I.. -I./../include -I../include -I./.. -I.. -I.. /data/OpenSSL/include/openssl -O3 -DDBUG_OFF -MT libmysql.lo -MD -MP -MF .deps/libmysql.Tpo -c libmysql.c -o libmysql.o gcc: cannot specify -o with -c or -S and multiple compilations make[2]: *** [libmysql.lo] Error 1 make[2]: Leaving directory `/data/software/tars/mysql-4.0.20/libmysql_r' make[1]: *** [all-recursive] Error 1 make[1]: Leaving directory `/data/software/tars/mysql-4.0.20' make: *** [all] Error 2 Any other ideas? This problem is not related either to OpenSSL or to OpenSSH. The main error above is gcc: cannot specify -o with -c or -S and multiple compilations. That means the problem lies with GCC or, more specifically, with the parameters which are being passed to it. A quick Google search on the GCC error turned up some hints. For starters, according to this article, you should have a look at your imake: http://gcc.gnu.org/ml/gcc/1998-11/msg00367.html It is, however, an old posting and therefore might not apply in this case. Which version of GCC are you using? You may need to consider upgrading it. Many of the references turned up in the Google search speak of this as a good idea. Here is the Google search which was performed. Please read through the references available here and give some things a try: http://www.google.com/search?hl=enlr=ie=UTF-8q=%22gcc%3A+cannot+specify+-o+with+-c+or+-S+and+multiple%22btnG=Search Cheers, --V Thank you for your help! --- V. M. Brasseur [EMAIL PROTECTED] wrote: OpenSSH != OpenSSL. Just because you have one installed doesn't mean that the other is. Apparently the MySQL compilation using OpenSSL requires OpenSSH for some reason. You might want to try installing it and giving the compile another whirl: http://www.openssh.com/ Also, the exact output of the ./configure message you're receiving might help people get a better handle on exactly what the problem might be. Cheers, --V E SA wrote: All, I already sent this message once, but I got no answer. Has naybody successfully compiled MySQL with OpenSSL NOT in /usr/local/ssl? I can not use stunnel, and I have not found anything in google. Please let me know. - - - - - - - - - - - - - - - - - - - - - - - - - - - - Original post: All, I am trying to compile MySQL with OpenSSL in an alternate location ( /data/OpenSSL ). When I do that either configure will say that no installation of OpenSSH is there (even with the --with-openssl-includes= and --with-openssl-libs= ), or it will compile without OpenSSL support ( show variables like ... ). My configure is as follows: ./configure --prefix=/data/MySQL --exec-prefix=/data/MySQL \ --enable-thread-safe-client --enable-assembler \ --enable-local-infile --with-extra-charsets=complex \ --disable-shared --with-openssl=/data/OpenSSL \ --without-docs --with-vio --with-openssl-includes=/data/OpenSSL/include/openssl \ --with-openssl-libs=/data/OpenSSL/lib Has anybody successfully compiled MySQL with OpenSSL support in an alternate location? The MySQL version is 4.0.20. Any help would be appreciated. __ Do you Yahoo!? New and Improved Yahoo! Mail - Send 10MB messages! http://promotions.yahoo.com/new_mail __ Do you Yahoo!? Yahoo! Mail - 50x more storage than other providers! http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table crashed! Please help
The manual knows all: http://dev.mysql.com/doc/mysql/en/REPAIR_TABLE.html Cheers, --V Monet wrote: I was working on a table, doing some simple update on table, query is like: Update temp SET Q1 = 14, REVIEWCOMMENTS = CASE WHEN REVIEWCOMMENTS='WHO2' THEN '' WHEN REVIEWCOMMENTS LIKE '%,WHO2' THEN TRIM(TRAILING ',WHO2' FROM REVIEWCOMMENTS) WHEN REVIEWCOMMENTS LIKE 'WHO2,%' THEN TRIM(LEADING 'WHO2,' FROM REVIEWCOMMENTS) ELSE REPLACE(REVIEWCOMMENTS, 'WHO2,', '') END WHERE QID IN (3029,3041,3053,3076,3120,3121,3128,3133,3134); It runs well. Then I did query to pull out all updated records: select qid, qd5,q1, reviewcomments from temp where qid IN (3029,3041,3053,3076,3120,3121,3128,3133,3134) order by qid asc; there is no records return. Then I opened temp table found that there is no records at all. But temp tables should contain 67 records. I dont know what happened. Those two queries are so normal and ran several times today. Since I have dumped file, I tried to recovery by run dumped sql to restore data into temp table. But then an error message pop up said something like Table handler got some problem. So I cant recovery my temp table in that way. Further more I cant close Navicat (one mysql interface). It always got an error pop up saying something like memory access violate. I restart my computer, reopen temp table, error message like this 1016 cant open file temp.MYI. (error 145) showed up and my table lost everything, no data, no columns. I really have no any experience on this kind of situation, please help. Many thanks and much appreciated. Monet __ Do you Yahoo!? Yahoo! Mail - 50x more storage than other providers! http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: can not find file *.MYI
Your index files appear to have disappeared during your archive. You can rebuild them though. Have a look at the Table Maintenance and Crash Recovery section of the manual: http://dev.mysql.com/doc/mysql/en/Table_maintenance.html Pay particular attention to the REPAIR TABLE syntax. That will give you want you need. Cheers, --V Yann Larrivee wrote: Hi keep getting this error ERROR 1017: Can't find file: *.MYI. What i did is simple , archived all the database files et placed them on a new server (i should have done a dump) Now i can do a desc of all the table but i can't select any data from my tables. Is there any way to recreate all the MYI files. I don't really care about the content, but i dont feel like recreating all the structure. I did look in the documentation, but it does not give any sugesiton on how to solve this issue. Thanks. Yann Larrivée -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Before I go searching (shameless)
Here is the manual. I suggest you try a search for LIMIT in the context of a SELECT. http://dev.mysql.com/doc/mysql/en/index.html However what it sounds like you really ought to do is give a walk through the tutorial. http://dev.mysql.com/doc/mysql/en/Tutorial.html If, after exploring these two links, you still have questions feel free to post them and people will be glad to be of assistance. RTFM, --V Stuart Felenstein wrote: I'm tring to extract all records belonging to one ID in a table. BUT! I want to do it in piece meal. Meaning a record at a time. I am assuming I need some kind of count mechanism. Example: I want to see the first 3 purchases this member made. Instead of grabbing them all in one shot, I want to break those 3 out in 3 seperate results set Results Set1: Purchase 1 Results Set2: Purchase 2 Results Set3: Purchase 3 This is probably easy. Thank you, Stuart -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DELETE FROM mysql.user?
USAGE is a synonym for no privileges. Yes, it's kind of a confusing term which implies this user can use the system when in fact the situation is exactly the opposite. http://dev.mysql.com/doc/mysql/en/User_resources.html Cheers, --V Mark C. Stafford wrote: Should I care that USAGE still shows up after all rights have been revoked? It feels like a potential security hole... -- working around the system to ensure a clean environment -- i feel like i shouldn't have to do this...but want to start with a clean slate DELETE FROM mysql.user WHERE user = 'jdoe'; DELETE FROM mysql.columns_priv WHERE user = 'jdoe'; DELETE FROM mysql.tables_priv WHERE user = 'jdoe'; DELETE FROM mysql.db WHERE user = 'jdoe'; FLUSH PRIVILEGES; SHOW GRANTS FOR 'jdoe'@'192.168.%'; /* [localhost -- root] ERROR 1141: There is no such grant defined for user 'jdoe' on host '192.168.%' */ -- simulating new user GRANT SELECT ON test.* TO 'jdoe'@'192.168.%' IDENTIFIED BY 'still-got-usage'; GRANT INSERT ON test.* TO 'jdoe'@'192.168.%'; GRANT UPDATE ON test.* TO 'jdoe'@'192.168.%'; FLUSH PRIVILEGES; SHOW GRANTS FOR 'jdoe'@'192.168.%'; /* ++ | Grants for [EMAIL PROTECTED] | ++ | GRANT USAGE ON *.* TO 'jdoe'@'192.168.%' IDENTIFIED BY PASSWORD '4a8930bb6abf3967' | | GRANT SELECT, INSERT ON `test`.* TO 'jdoe'@'192.168.%' | ++ */ -- simulating user's departure from organization REVOKE SELECT ON test.* FROM 'jdoe'@'192.168.%'; REVOKE INSERT ON test.* FROM 'jdoe'@'192.168.%'; REVOKE UPDATE ON test.* FROM 'jdoe'@'192.168.%'; FLUSH PRIVILEGES; SHOW GRANTS FOR 'jdoe'@'192.168.%'; /* ++ | Grants for [EMAIL PROTECTED] | ++ | GRANT USAGE ON *.* TO 'jdoe'@'192.168.%' IDENTIFIED BY PASSWORD '4a8930bb6abf3967' | ++ */ --try it again, sam REVOKE USAGE ON *.* FROM 'jdoe'@'192.168.%'; FLUSH PRIVILEGES; SHOW GRANTS FOR 'jdoe'@'192.168.%'; /* ++ | Grants for [EMAIL PROTECTED] | ++ | GRANT USAGE ON *.* TO 'jdoe'@'192.168.%' IDENTIFIED BY PASSWORD '4a8930bb6abf3967' | ++ */ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: column types for intger, float or double field include arrays?
Numeric types are simply that: numbers. They cannot be arrays. http://dev.mysql.com/doc/mysql/en/Numeric_types.html Incidentally, why would you need this? Maybe the group can come up with a viable alternative for you. Cheers, --V Raghudev Ramaiah wrote: hi i have used column types such as intger and float and double for fields in the MySQL tables. can i use arryas of integers, floats and doubles as column type? i.e., can a field ina table be an integer array or float array ? ex: count[20] integer is it allowed and is it an array? thanks and regards Raghu - Do you Yahoo!? Win 1 of 4,000 free domain names from Yahoo! Enter now. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to use use for a specific host
Are you using the mysql client to connect? If so, you can use the -h and -D flags: mysql -h hostname [-u username -p -P port] -D databasename The -D flag isn't even necessary. The command above is equivalent to this one: mysql -h hostname [-u username -p -P port] databasename http://dev.mysql.com/doc/mysql/en/mysql.html If you're writing your own client, you chould check the API documentation for the language you're using. If you're using a completely different client, check the docs for it. Be one with your manual. Or be one with your many manuals. I leave it to you to figure out that existential math. Cheers, --V Brian Menke wrote: I'm trying to specify a host name and database name to do an update to a table. I can't quite figure out what the correct syntax is? Is it something like: USE [EMAIL PROTECTED] I have tried several permutations of that but can't quite seem to get the syntax just right. Thanks. -Brian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: bad too many connections error (os x)
Michael Winston wrote: Hi- We've been running into a pretty serious problem for the past several versions of mysql 4.0 running on OS X (both client and server). Every once in a while we wake up to find the too many connections error coming up. There really aren't too many connections (we have our max set to 99) - it's the type of message that appears when a wrong password is used too many times (and I'm 100% sure this isn't happening). Now, the problem is that once this message starts appearing we can't even connect with mysqladmin as root. That extra connection that mysql promises doesn't exist. The only way we can shut down mysql is to perform a 'kill -9' (then restart the server and repair all the tables). And we can't reproduce this problem at will. This is driving us nuts. Before I report this as a bug I wanted to know if anyone else has seen something like this or has any suggestions of how to narrow down the problem. Thanks! Michael We've run into this problem ourselves, also using 4.0 but on a 64bit AIX. The problem we found was that some queries were firing off threads which never ended. These threads blocked other threads, which blocked other threads... A logjam resulted with all connections ended up being used by the offending threads. The fix was to *ahem* fix our queries so they'd close their database connections once they were complete. You may wish to do a code inspection and verify that every open connection has a matching close. Best of luck, --V -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: specify data type in select statement
Perhaps a casting function would help you: http://dev.mysql.com/doc/mysql/en/Cast_Functions.html If case-sensitivity isn't an issue, you may want to consider converting from char(8) binary to just char(8). Cheers, --V walt wrote: We've run into a problem where binary char column types crash our c++ application when migrating from RedHat 7.3 to RedHat ES. Is there a way to specify the data type to be returned in the select statement. In the below example, selecting office_id will crash the app if it's left as a binary column. I'm looking for something like select charoffice_id from office; create table office ( office_id char(8) binary not null, office_name varchar(50) ); Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Is their still any reason why Stored Procedure calls are not supported in MySql?
Stored procedures are in the works for MySQL 5.0: http://dev.mysql.com/doc/mysql/en/TODO_MySQL_5.0.html The manual is your friend. Cheers, --V Jacob, Raymond A Jr wrote: Firstly, I do appologize for my ignorance in advance. I read the message regarding PRODUCT() and thought to myself:A perl interpreter can be run in a C program. Some one must have written a userdefined function that can execute perl code. Googling I found http://software.tangent.org/download/myperl-0.8.tar.gz at http://tangent.org. Looking at the readme file myperl is mysql userdefined function that executes perl code contained in a table in the select statement. The command line version of mysql also has the ability to start an editor. Theoretically, one could: 1. create a table: Create table user.sparky.functions ( name TEXT NOT NULL , code TEXT NOT NULL ); 2. \e myfunc.mypl 3. LOAD DATA IN FILE myfunc.mypl INTO TABLE user.sparky.functions; 4. /* assume no errors */ Select myfunc(code, colum_data) from user.sparky.functions, data_table where (code = myperl_code ) and ( column_data = what I am looking for ); If and I stress if my assumptions are valid, then stored procedure calls could be written in any interpreted language with an interpreter that can be linked into mysql. Of course from a security stand point this could be dangerous but chrooted brain dead interperter with limited functionality and limits on the amount of disk space and memory that can be used should solve those problems. One interesting consequence of the development of infrastructure to support the development of stored procedure calls is that IDE developers that support mysql would have new market to potentially exploit in a corporate enviroment i.e. run code and get metrics such as how long it to process data i.e. Hi,Lo, Avg. In conclusion, I appologize for my ignorance again however I must still ask: Is their still any reason why Stored Procedure calls are not supported in MySql? Thank you, Raymond -- Date: Mon, 30 Aug 2004 11:29:35 -0400 To: Thomas Schager [EMAIL PROTECTED], [EMAIL PROTECTED] From: Sapenov [EMAIL PROTECTED] Subject: Re: PRODUCT() function - calculating the product of grouped numeric values Message-ID: [EMAIL PROTECTED] Hello Thomas, You probably may consider to write a UDF to do that. Here is a list of available UDF extensions for 4.0.20 - http://mysql-udf.sourceforge.net/ Regards, Khazret Sapenov - http://tangent.org myperl (default) 0.8 2004-01-12 07:58:51 About myperl allows you to execute Perl from inside of MySQL. It can either be stored in a row, or it can be specified in your SQL statement. You also can select other columns which will be passed to Perl. Changes * Added support for the perl interpreter to persist for requests * re-aranged package to meet CPAN spec. * Fix for myperlgroup (it was calling itself too frequently) TAR/GZ http://software.tangent.org/download/myperl-0.8.tar.gz -- Date: Mon, 30 Aug 2004 09:48:55 -0400 To: Per Lonnborg [EMAIL PROTECTED] From: Michael Stassen [EMAIL PROTECTED] CC: [EMAIL PROTECTED] Subject: Re: SELECT, ORDER one DISTINCT kolumn? Message-ID: [EMAIL PROTECTED] Per Lonnborg wrote: Hi, Here´s a question from a newbie: I have a database containing ET-times for drivers and their cars. On the web startpage (http://www.lanechoice.net/~pelle/br/) i have made a Top-10 list containing the 10 fastest times. I'll guess column names based on the web page. I have 2 problems: 1. How to select just ONE (the fastest of course) time per uniqe drivercar? SELECT namm, fabrikat, MIN(tid) AS fastest_tid FROM ettan GROUP BY namm, fabrikat ORDER BY fastest_tid DESC LIMIT 10; 2.IF the driver/car has run some race faster than 7,5 secs, he/she is disqualified and should not be on the Top-10 at all. SELECT namm, fabrikat, MIN(tid) AS fastest_tid FROM ettan GROUP BY namm, fabrikat HAVING fastest_tid = 7.5 ORDER BY fastest_tid LIMIT 10; The query I use right now is: Select * from ettan where tid =7.5 ORDER BY tid LIMIT 10 /Per Stockholm, Sweden Note that I only selected aggregate values and grouped columns. If you need the values of non-grouped columns (ort, datum, 60fot, hast) which correspond to the minimum times, you'll need to do a bit more. The manuaal describes 3 methods http://dev.mysql.com/doc/mysql/en/example-Maximum-column-group-row.html. ___ Skicka gratis SMS! http://www.passagen.se Michael -- -- End of mysql-plain Digest *** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slow Queries on Fast Server?
Have you checked the Optimization section of the manual yet?
http://dev.mysql.com/doc/mysql/en/MySQL_Optimization.html
It's probably the best place to start.
Cheers,
--V
[EMAIL PROTECTED] wrote:
I'm running into a problem with some queries running on a dedicated mysql server (2.0
GHz, 2GB RAM). Fulltext searching really exemplifies this as most MATCH, AGAINST
queries are taking 5-20 seconds. Performance was excellent for some reason one day
(0.2 - 0.75 seconds) but it was only fast for a day or so.
Here's the rundown:
TABLE: fulltext_table (some_id, the_text)
Rows: 3,237,981
Type: MyISAM
Size: 920.8 MB
QUERY: SELECT COUNT(*) FROM fulltext_table WHERE MATCH (the_text) AGAINST ('blue');
or
QUERY: SELECT SQL_CALC_FOUND_ROWS some_id FROM fulltext_table WHERE MATCH (the_text) AGAINST ('blue') LIMIT 0, 20;
Both are problematic. I even tried placing a limit of 2 on the first query but it
didn't improve anything. The table has a fulltext index on the column and is
optimized. No other users are connected to the server.
Is there a RED FLAG in here somewhere?
MySQL configuration settings (using my-huge.cnf template):
key_buffer = 500M
max_allowed_packet = 1M
table_cache = 512
sort_buffer_size = 10M
read_buffer_size = 2M
myisam_sort_buffer_size = 64M
#thread_cache = 8
thread_concurrency = 8
#- Modifications --- #
ft_min_word_len = 3
set-variable = table_cache=1024
set-variable = max_heap_table_size=64M
set-variable = tmp_table_size=128M
set-variable = query_cache_limit=2M
query_cache_type=1
Performance Test:
SELECT COUNT(*) FROM fulltext_table WHERE MATCH (the_text) AGAINST ('white');
+--+
| COUNT(*) |
+--+
|95074 |
+--+
1 row in set (27.83 sec)
Statistics for vmstat 1 (my apologies if this doesn't look pretty):
---
procs memory swap io system cpu
r b swpd free buff cache si sobibo incs us sy id wa
0 0 19500 17800 42432 177172800 060 11330 0 0 99 1
0 1 19500 21524 42428 1765728 7240 960 0 536 444 5 1 82 12
0 1 19500 19512 42424 176782000 2348 0 912 592 0 1 50 49
0 1 19500 17788 42424 176954000 1980 0 868 588 0 1 51 48
0 1 19500 17568 42424 176976000 2300 0 723 401 0 0 50 49
0 1 19500 17704 42428 176962000 193620 662 364 0 0 51 49
0 1 19500 17560 42428 176976400 2224 0 696 400 0 0 51 49
0 1 19500 17504 42424 176982400 2136 0 670 380 0 0 51 49
0 1 19500 17616 42424 176971200 2228 0 693 415 0 0 51 49
0 1 19508 17608 42420 176972408 2348 8 692 389 0 0 50 50
0 1 19508 17532 42428 176979200 1896 108 654 366 0 0 50 49
0 1 19512 17644 42424 176968404 2220 4 720 450 0 1 50 49
0 1 19516 17620 42420 176971204 2104 4 707 424 0 0 51 48
0 1 19516 17744 42420 176958800 2476 0 762 462 0 1 50 49
0 1 19516 17532 42416 176980400 2292 0 719 401 0 0 51 49
procs memory swap io system cpu
r b swpd free buff cache si sobibo incs us sy id wa
0 1 19516 17388 42424 176994000 221616 699 388 0 0 51 49
0 1 19516 17632 42420 176970000 1836 0 629 380 0 0 51 49
0 1 19516 17596 42420 176973200 2112 0 661 374 0 1 51 48
0 1 19516 17580 42416 176975200 1836 0 631 396 0 0 51 49
0 1 19516 17624 42416 176970800 2036 0 654 368 0 0 51 49
0 1 19516 17556 42420 176977200 188016 643 381 0 0 50 50
0 1 19516 17652 42420 176967600 1984 0 657 380 0 0 51 49
0 1 19516 17532 42416 176980000 1940 0 646 386 0 1 50 49
0 1 19516 17520 42416 176981200 1832 0 631 389 0 0 50 49
0 1 19516 17548 42412 176978800 2052 0 648 387 0 1 50 49
0 1 19516 17700 42412 176963600 244028 741 448 0 0 50 50
0 1 19516 17656 42408 176968400 2384 0 683 412 0 1 50 49
0 1 19516 17676 42408 176966000 2316 0 679 387 0 1 50 49
0 1 19516 17624 42404 176971200 2128 0 652 407 0 1 50 49
0 0 19516 19056 42404 17697520040 0 13240 0 0 97 2
Statistics for top command:
-
PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME CPU COMMAND
4784 root 15 0 488 488 420 S 0.2 0.0 0:00 0 vmstat
3979 mysql 16 0 68128 52M 2188 S 0.1 2.6 0:06 1 mysqld
3982 mysql 15 0 68128 52M 2188 S 0.1 2.6 0:05 2 mysqld
1 root 15 0 512 512 452 S 0.0 0.0 0:05 2 init
2 root RT 0 00 0 SW
Re: Slow Queries on Fast Server?
Could you send the output of an EXPLAIN for your query?
--V
[EMAIL PROTECTED] wrote:
Have you checked the Optimization section of the manual yet?
http://dev.mysql.com/doc/mysql/en/MySQL_Optimization.html
Oh yes, as I've attempted to configure the my.cnf file for best performance. The query is correct. The
fulltext index is correct as I built the fulltext index on the single column (took 9 minutes) and even did
repair and optimize on the table... so I don't think its the index. I'm thinking
its the server config...
- John
[EMAIL PROTECTED] wrote:
I'm running into a problem with some queries running on a dedicated mysql server (2.0
GHz, 2GB RAM). Fulltext searching really exemplifies this as most MATCH, AGAINST
queries
are taking 5-20 seconds. Performance was excellent for some reason one day (0.2 - 0.75
seconds) but it was only fast for a day or so.
Here's the rundown:
TABLE: fulltext_table (some_id, the_text)
Rows: 3,237,981
Type: MyISAM
Size: 920.8 MB
QUERY: SELECT COUNT(*) FROM fulltext_table WHERE MATCH (the_text) AGAINST ('blue');
or
QUERY: SELECT SQL_CALC_FOUND_ROWS some_id FROM fulltext_table WHERE MATCH (the_text)
AGAINST ('blue') LIMIT 0, 20;
Both are problematic. I even tried placing a limit of 2 on the first query but
it didn't improve anything. The table has a fulltext index on the column and is
optimized. No other users are connected to the server.
Is there a RED FLAG in here somewhere?
MySQL configuration settings (using my-huge.cnf template):
key_buffer = 500M
max_allowed_packet = 1M
table_cache = 512
sort_buffer_size = 10M
read_buffer_size = 2M
myisam_sort_buffer_size = 64M
#thread_cache = 8
thread_concurrency = 8
#- Modifications --- #
ft_min_word_len = 3
set-variable = table_cache=1024
set-variable = max_heap_table_size=64M
set-variable = tmp_table_size=128M
set-variable = query_cache_limit=2M
query_cache_type=1
Performance Test:
SELECT COUNT(*) FROM fulltext_table WHERE MATCH (the_text) AGAINST ('white');
+--+
| COUNT(*) |
+--+
|95074 |
+--+
1 row in set (27.83 sec)
Statistics for vmstat 1 (my apologies if this doesn't look pretty):
---
procs memory swap io system cpu
r b swpd free buff cache si sobibo incs us sy id wa
0 0 19500 17800 42432 177172800 060 11330 0 0 99 1
0 1 19500 21524 42428 1765728 7240 960 0 536 444 5 1 82 12
0 1 19500 19512 42424 176782000 2348 0 912 592 0 1 50 49
0 1 19500 17788 42424 176954000 1980 0 868 588 0 1 51 48
0 1 19500 17568 42424 176976000 2300 0 723 401 0 0 50 49
0 1 19500 17704 42428 176962000 193620 662 364 0 0 51 49
0 1 19500 17560 42428 176976400 2224 0 696 400 0 0 51 49
0 1 19500 17504 42424 176982400 2136 0 670 380 0 0 51 49
0 1 19500 17616 42424 176971200 2228 0 693 415 0 0 51 49
0 1 19508 17608 42420 176972408 2348 8 692 389 0 0 50 50
0 1 19508 17532 42428 176979200 1896 108 654 366 0 0 50 49
0 1 19512 17644 42424 176968404 2220 4 720 450 0 1 50 49
0 1 19516 17620 42420 176971204 2104 4 707 424 0 0 51 48
0 1 19516 17744 42420 176958800 2476 0 762 462 0 1 50 49
0 1 19516 17532 42416 176980400 2292 0 719 401 0 0 51 49
procs memory swap io system cpu
r b swpd free buff cache si sobibo incs us sy id wa
0 1 19516 17388 42424 176994000 221616 699 388 0 0 51 49
0 1 19516 17632 42420 176970000 1836 0 629 380 0 0 51 49
0 1 19516 17596 42420 176973200 2112 0 661 374 0 1 51 48
0 1 19516 17580 42416 176975200 1836 0 631 396 0 0 51 49
0 1 19516 17624 42416 176970800 2036 0 654 368 0 0 51 49
0 1 19516 17556 42420 176977200 188016 643 381 0 0 50 50
0 1 19516 17652 42420 176967600 1984 0 657 380 0 0 51 49
0 1 19516 17532 42416 176980000 1940 0 646 386 0 1 50 49
0 1 19516 17520 42416 176981200 1832 0 631 389 0 0 50 49
0 1 19516 17548 42412 176978800 2052 0 648 387 0 1 50 49
0 1 19516 17700 42412 176963600 244028 741 448 0 0 50 50
0 1 19516 17656 42408 176968400 2384 0 683 412 0 1 50 49
0 1 19516 17676 42408 176966000 2316 0 679 387 0 1 50 49
0 1 19516 17624 42404 176971200 2128 0 652 407 0 1 50 49
0 0 19516 19056 42404 17697520040 0 13240 0 0 97 2
Statistics for top command:
-
PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME CPU COMMAND
4784
Re: LIKE question
How about... SELECT foo FROM bar WHERE LCASE(this) = that; Although there are always other ways to do it, of course. Cheers, --V Schalk Neethling wrote: If I search a field for 'doone' and one of the fields contains 'Lorna Doone' what is the best comparator to use as LIKE is skipping this one and I imagine '=' will do the same? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to Reset a field
You could also have done this: TRUNCATE TABLE usarios; That will drop the table completely and then recreate it anew (and empty). It will give you a fresh start. --V Victor Pendleton wrote: Try alter table usuarios auto_increment = 1; -Original Message- From: Yusdaniel Rodriguez Espinosa To: [EMAIL PROTECTED] Sent: 8/23/04 9:15 AM Subject: How to Reset a field Hello I have a db with table usuarios I erased all data in this table but Id is a autoinc and he have the value of the last value. How I can Reset the field ID thanks --- Hola Yo tengo una db con una tabla usuarios, yo borre todos los campos de la db pero el campo id que un autonumerico que se incrementa automaticamente se queda con el valor del ultimo campo, como puedo restablecer el valor de Id a 0 cada vez que limpie la db??? gracias Yusdaniel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Purchase Options
Because he's a good sport and really wants to support the MySQL development effort? Either that or he hadn't heard of the GPL'd option, but I prefer to believe the former. :-) Cheers, --V Daved Daly wrote: all I want to do is host some web applications locally for our internal use. Why, specificaly do you need a commerical license then? -Daved The information contained in this communication is confidential and may be legally privileged. It is intended solely for the use of the individual or entity to whom it is addressed and others authorised to receive it. If you are not the intended recipient you are hereby notified that any disclosure, copying, distribution or taking any action in relation to the contents of this information is strictly prohibited and may be unlawful. Most of your disclaimer is a waste of text.. I always find them humorous tho. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fairly lame question
MySQL provides a number of functions for handling dates and times: http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html Cheers, --V Stuart Felenstein wrote: I think this can be done, but tried a few times with no success. I want a column in a table that sets a timestamp. Instead of passing a value from the form though is there a way mySql would automagically stamp the records as they are inserted ? and as a side note - would table type make any difference. 4.0.20 - standard Thank you, Stuart -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multiple MySQL Versions on Same Linux Box
You'll probably want to have a look at section 5.10 in the manual: http://dev.mysql.com/doc/mysql/en/Multiple_servers.html Also, the mysqld_multi documentation may come in handy: http://dev.mysql.com/doc/mysql/en/mysqld_multi.html Cheers, --V Lou Olsten wrote: We have successfully (and easily) installed multiple versions of MySQL on Windows and are now trying to do the same on Linux. We have a Fedora machine successfully running 4.1, but we want to add 5.0 to it as well. We installed 4.1 with RPM, so it put a bunch of MySQL files all over the place. I've downloaded the 5.0 tar and unpacked it into its own directory, then ran the install_db script. It ran OK, but no data files were created in the mysql or test directories; they're both empty. Does this sound right? Is there a step-by-step guide to installing multiple versions on the same box? I've been through the docs and while they address management of multiple, and a vague instruction on installing, it doesn't appear to be that detailed. Thanks, Lou -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: datadir specification, etc
In my experience, where the my.cnf file is concerned, mysqld does not care what you define for the value of the --datadir flag. The order of precedence for locating my.cnf files is: 1) /etc/my.cnf 2) my.cnf in the COMPILED-IN DEFAULT datadir 3) .my.cnf in the user's $HOME That compiled-in default makes all the difference. You can start mysqld with as many different renditions of the --datadir flag as you want, but if one of them isn't the default path which was compiled in at build time (using the --localstatedir flag for configure) then mysqld will not automatically locate any my.cnf file in the specified datadir. For instance, I start all my servers with a --datadir=/path/to/mysql/data flag and have a my.cnf file in that directory. However, depending upon the platform and installation, the mysqld server will be looking in a number of different (and often non-existent) locations for the my.cnf file instead, such as /usr/local/mysql/var or similar. This is something which has caused many headaches on the machines I administer, occassionally leading to an intricate web of links to allow the server to locate the appropriate file. These links are not the only way to direct the server towards the appropriate my.cnf file. To be honest, they're only a hack and I wouldn't recommend them. One way to handle this is to rebuild MySQL from a source distribution, using the appropriate configure flags to set new default paths to be compiled into the binaries. A much easier way is to use the --defaults-file and/or --defaults-extra-file flags when starting the mysqld server. These flags--and not the value of any datadir flag--are what really tell mysqld where to locate the options file(s) it should use. The one drawback I've found with these flags is remembering to use the same flag(s) on any client programs which are run and training users to do the same. This has been enough of a pain to make it worth my while to deal with the web of links at this point. When all the machines are upgraded to MySQL 4.0.20 later this year, they will be receiving self-compiled binaries with our own flavor of default paths so none of these workarounds will be necessary. For more information about this sort of thing, check this page in the manual: http://dev.mysql.com/doc/mysql/en/Option_files.html Also, Paul DuBois' MySQL book has good information presented in a very accessible manner. Cheers, --V sean c peters wrote: I am 100% convinced that mysql 4.1.3 beta is not properly reading the my.cnf configuration files. If i remove the /etc/my.cnf file and try to start mysql 4.1.3 with (im working from /usr/local/mysql-4.1.3/bin) ./mysqld_safe i get the following output: (mccoy is the name of the machine im on) touch: /usr/local/mysql-4.1.3/var/mccoy.err cannot create chown: /usr/local/mysql-4.1.3/var/mccoy.err: No such file or directory Starting mysqld daemon with databases from /usr/local/mysql-4.1.3/var ./mysqld_safe: /usr/local/mysql-4.1.3/var/mccoy.err: cannot create If i remove the /var/mysql-4.1.3/my.cnf file, i get the same output as above, so its not being read either way. And i did specify /var/mysql-4.1.3/ as my datadir with .configure when building 4.1.3 if i put the /etc/my.cnf file back, i get the following: A mysqld process already exists So clearly, the /etc/my.cnf is being read, but the /var/mysql-4.1.3/my.cnf is not. so i guess it doesnt matter what i specify in there at this point. One strange thing is that ./msqyd_safe tries to use the databases in /usr/local/mysql-4.1.3/var/ But i specified a different datadir with configure! my configure --prefix=/usr/local/mysql-4.1.3 but why should that matter? In fact, when i installed 4.1.3 (make install), the directory /usr/local/mysql-4.1.3/var/ was NOT created. I dont think most of the info ive given matters, because my run-time configuration doesnt appear to be the problem. I dont believe that my build configuration took effect properly. Does any of this make sense? Still completely lost. thanks sean peters [EMAIL PROTECTED] *** Here's some my.cnf data, if it really matters *** Here is part of the /var/mysql-4.1.3/my.cnf file: [client] port= 3307 socket = /tmp/mysql-4.1.3.sock pid-file= /usr/local/mysql-4.1.3/mysql-4.1.3.pid datadir = /var/mysql-4.1.3/ [mysqld] port= 3307 socket = /tmp/mysql-4.1.3.sock pid-file= /usr/local/mysql-4.1.3/mysql-4.1.3.pid datadir = /var/mysql-4.1.3/ And here is info from /etc/my.cnf file: [client] port= 3306 socket = /tmp/mysql.sock [mysqld] port= 3306 socket = /tmp/mysql.sock -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [Probable SPAM] Re: datadir specification, etc
Which flag did you use to define the datadir for configure? --datadir doesn't do the trick. --localstatedir does. Also, you might want to consider setting --prefix as well. --V sean c peters wrote: The problem is that i did build 4.1.3 florm a source distribution, and set the datadir via configure to be /var/mysql-4.1.3/, and it doesnt read my.cnf from there anyway. In regard to the section of the manual mentioned: http://dev.mysql.com/doc/mysql/en/Option_files.html It states: DATADIR represents the location of the MySQL data directory. Typically this is `/usr/local/mysql/data' for a binary installation or `/usr/local/var' for a source installation. Note that this is the data directory location that was specified at configuration time, not the one specified with --datadir when mysqld starts. Use of --datadir at runtime has no effect on where the server looks for option files, because it looks for them before processing any command-line arguments. So, even by specifying the datadir at the command line when starting mysql, according to this documentation, mysql wont even bother looking in the command line specified datadir for a my.cnf On Friday 13 August 2004 10:15, V. M. Brasseur wrote: In my experience, where the my.cnf file is concerned, mysqld does not care what you define for the value of the --datadir flag. The order of precedence for locating my.cnf files is: 1) /etc/my.cnf 2) my.cnf in the COMPILED-IN DEFAULT datadir 3) .my.cnf in the user's $HOME That compiled-in default makes all the difference. You can start mysqld with as many different renditions of the --datadir flag as you want, but if one of them isn't the default path which was compiled in at build time (using the --localstatedir flag for configure) then mysqld will not automatically locate any my.cnf file in the specified datadir. For instance, I start all my servers with a --datadir=/path/to/mysql/data flag and have a my.cnf file in that directory. However, depending upon the platform and installation, the mysqld server will be looking in a number of different (and often non-existent) locations for the my.cnf file instead, such as /usr/local/mysql/var or similar. This is something which has caused many headaches on the machines I administer, occassionally leading to an intricate web of links to allow the server to locate the appropriate file. These links are not the only way to direct the server towards the appropriate my.cnf file. To be honest, they're only a hack and I wouldn't recommend them. One way to handle this is to rebuild MySQL from a source distribution, using the appropriate configure flags to set new default paths to be compiled into the binaries. A much easier way is to use the --defaults-file and/or --defaults-extra-file flags when starting the mysqld server. These flags--and not the value of any datadir flag--are what really tell mysqld where to locate the options file(s) it should use. The one drawback I've found with these flags is remembering to use the same flag(s) on any client programs which are run and training users to do the same. This has been enough of a pain to make it worth my while to deal with the web of links at this point. When all the machines are upgraded to MySQL 4.0.20 later this year, they will be receiving self-compiled binaries with our own flavor of default paths so none of these workarounds will be necessary. For more information about this sort of thing, check this page in the manual: http://dev.mysql.com/doc/mysql/en/Option_files.html Also, Paul DuBois' MySQL book has good information presented in a very accessible manner. Cheers, --V sean c peters wrote: I am 100% convinced that mysql 4.1.3 beta is not properly reading the my.cnf configuration files. If i remove the /etc/my.cnf file and try to start mysql 4.1.3 with (im working from /usr/local/mysql-4.1.3/bin) ./mysqld_safe i get the following output: (mccoy is the name of the machine im on) touch: /usr/local/mysql-4.1.3/var/mccoy.err cannot create chown: /usr/local/mysql-4.1.3/var/mccoy.err: No such file or directory Starting mysqld daemon with databases from /usr/local/mysql-4.1.3/var ./mysqld_safe: /usr/local/mysql-4.1.3/var/mccoy.err: cannot create If i remove the /var/mysql-4.1.3/my.cnf file, i get the same output as above, so its not being read either way. And i did specify /var/mysql-4.1.3/ as my datadir with .configure when building 4.1.3 if i put the /etc/my.cnf file back, i get the following: A mysqld process already exists So clearly, the /etc/my.cnf is being read, but the /var/mysql-4.1.3/my.cnf is not. so i guess it doesnt matter what i specify in there at this point. One strange thing is that ./msqyd_safe tries to use the databases in /usr/local/mysql-4.1.3/var/ But i specified a different datadir with configure! my configure --prefix=/usr/local/mysql-4.1.3 but why should that matter? In fact, when i installed 4.1.3 (make install), the directory /usr/local/mysql-4.1.3/var/ was NOT created
Re: problem with tables crashing
$ perror 145 Error code 145: Error 145 occurred. 145 = Table was marked as crashed and should be repaired I think `myisamchk` needs to come into play here (both on slave and master at this point). http://dev.mysql.com/doc/mysql/en/myisamchk_syntax.html http://dev.mysql.com/doc/mysql/en/Repair.html Cheers, --V Johan Jonkers wrote: Hi, I'm using mysql 4.0.12 and I got a database that holds the DMOZ data (master/slave config). Now this is a (to me anyways) pretty big database(4 million some links, with fulltext indexes). However I have a problem with it. For some reasons I can't get a grip on, the table crashes. (error 145). Due to an error on the slave I wanted to resync the slave by copying the master database and all, and followed the instructions on http://dev.mysql.com/doc/mysql/en/Replication_HOWTO.html. However, after bringing up the slave, I got a error 145 on the slave, and also on the master :-( Can someone please tell me why this happened, how I can stop this from happening again as repairing the table takes a LONG time Any help would be really appreciated Regards, Johan Jonkers -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: C API: mysql_options and mysql_real_connect
Paul DuBois wrote: At 13:03 -0700 8/11/04, V. M. Brasseur wrote: Assuming a my.cnf file which looks like this: [client] port=3306 socket=/path/to/mysql.sock [app] user=appuser password=apppwd host=my.host.com Ignore for now the insecurity of putting a password in the my.cnf file. This is mostly a hypothetical question at the moment. Calling mysql_options(MYSQL, MYSQL_READ_DEFAULT_FILE, /path/to/my.cnf); and mysql_options(MYSQL, MYSQL_READ_DEFAULT_GROUP, app); in the client will read the options in these two groups. How, if at all, would something like this be useful to mysql_real_connect? From my research it appears that you still need to specify the user, host, pwd and port (assuming TCP/IP connection) when calling mysql_real_connect(), so setting these parms in the my.cnf file does not really help for this scenario. Something (a non-API function, most likely) would still need to parse the file separately and grab the parms for passing to mysql_real_connect(). Is this an accurate assessment? No. If you pass NULL in the mysql_real_connect() params, the values from the option file(s) are used. Even for the password param? The mysql_real_connect() write-up in your MySQL book says that a NULL passed for password results in allowing connections only if there is no password in the mysql.user.password column for the current user. Perhaps having the password defined via a mysql_options() call trumps this NULL behavior? --V -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL won't start... was runnnig fine
This line probably should have been a good hint: InnoDB: File operation call: 'tmpfile'. Any idea why the server crashed in the first place? That's not the sort of thing you want just randomly happening, I'd imagine. Although it might make life a bit more interesting... Cheers. --V Chris Blackwell wrote: I have resolved this issue now, it was actually the permission on /tmp that had become corrupted as a result of the crash I can only assume chmod 777 /tmp chmod +t /tmp this fixed the problem but could have been fixed within minutes not days if the error message had told me which directory was causing the problem :/ chris -Original Message- From: Egor Egorov [mailto:[EMAIL PROTECTED] Sent: 12 August 2004 10:26 To: [EMAIL PROTECTED] Subject: Re: MySQL won't start... was runnnig fine Chris Blackwell [EMAIL PROTECTED] wrote: now when ever i try to start mysql i get the following error in log 040812 09:52:34 mysqld started 040812 9:52:34 Warning: Asked for 196608 thread stack, but got 126976 040812 9:52:34 InnoDB: Error: unable to create temporary file 040812 9:52:34 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 operation call: 'tmpfile'. InnoDB: Cannot continue operation. 040812 09:52:34 mysqld ended [EMAIL PROTECTED] egor]$ perror 13 Error code 13: Permission denied i have chown -R the /var/lib/mysql directory So anyway permission denied. Check the rights on /var/lib/mysql itself -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: my.cnf not read in 4.1.3
Are you sure that the server is using the my.cnf file which you are changing? After making the change, what is the output of --print-defaults for mysqld? If it's not as expected, odds are good that the server is getting its defaults from a different location. --V [EMAIL PROTECTED] wrote: Hi all: I am running MySQL 4.1.3 under Debian. I have the problem that my options in my.cnf are not going into effect. I want to increase max_heap_table_size from the default of 16M to 400M (I have 4GB of memory), but putting that line into my.cnf doesn't do anything. (Yes it is in the mysqld section of the file.) Also I have tried doing a set global max_heap_table_size=4; in the mysql command line but it doesn't change the value either, according to SHOW VARIABLES. What am I doing wrong? Regards, Ryan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: my.cnf not read in 4.1.3
What else is in the my.cnf file? For instance, when I run `mysqld --print-defaults` on my system I see this: mysqld would have been started with the following arguments: --basedir=/path/to/mysql-4.0 --datadir=/path/to/mysql-4.0/data --port=1032 --socket=/path/to/mysql-4.0/mysql.sock --user=username --log=/path/to/mysql-4.0/logs/this.log --log-slow-queries=/path/to/mysql-4.0/logs/this-slow.log --log-error=/path/to/mysql-4.0/logs/this-err.log --log-long-format --tmpdir=/path/to/mysql-4.0/tmp Each of those parameters is an option set in the my.cnf file. So I'd imagine that if you have anything at all in the [mysqld] group in the file you'd be seeing them in the --print-defaults output. If there are no objections from your sysadmin, I suggest moving your my.cnf file from /etc/mysql straight into /etc. Either that or into the default datadir for your installation. Both should work better for you (but the /etc/my.cnf location would be a sure-thing). Cheers, --V [EMAIL PROTECTED] wrote: The output of mysqld --print-defaults is blank, so that means it should be looking for my.cnf in the default location, which is /etc/mysql/my.cnf, right? I am indeed editing the file in that location. Ryan Are you sure that the server is using the my.cnf file which you are changing? After making the change, what is the output of --print-defaults for mysqld? If it's not as expected, odds are good that the server is getting its defaults from a different location. --V [EMAIL PROTECTED] wrote: Hi all: I am running MySQL 4.1.3 under Debian. I have the problem that my options in my.cnf are not going into effect. I want to increase max_heap_table_size from the default of 16M to 400M (I have 4GB of memory), but putting that line into my.cnf doesn't do anything. (Yes it is in the mysqld section of the file.) Also I have tried doing a set global max_heap_table_size=4; in the mysql command line but it doesn't change the value either, according to SHOW VARIABLES. What am I doing wrong? Regards, Ryan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: C API: mysql_options and mysql_real_connect
Paul DuBois wrote: At 8:06 -0700 8/12/04, V. M. Brasseur wrote: Paul DuBois wrote: At 13:03 -0700 8/11/04, V. M. Brasseur wrote: Assuming a my.cnf file which looks like this: [client] port=3306 socket=/path/to/mysql.sock [app] user=appuser password=apppwd host=my.host.com Ignore for now the insecurity of putting a password in the my.cnf file. This is mostly a hypothetical question at the moment. Calling mysql_options(MYSQL, MYSQL_READ_DEFAULT_FILE, /path/to/my.cnf); and mysql_options(MYSQL, MYSQL_READ_DEFAULT_GROUP, app); in the client will read the options in these two groups. How, if at all, would something like this be useful to mysql_real_connect? From my research it appears that you still need to specify the user, host, pwd and port (assuming TCP/IP connection) when calling mysql_real_connect(), so setting these parms in the my.cnf file does not really help for this scenario. Something (a non-API function, most likely) would still need to parse the file separately and grab the parms for passing to mysql_real_connect(). Is this an accurate assessment? No. If you pass NULL in the mysql_real_connect() params, the values from the option file(s) are used. Even for the password param? The mysql_real_connect() write-up in your MySQL book says that a NULL passed for password results in allowing connections only if there is no password in the mysql.user.password column for the current user. Perhaps having the password defined via a mysql_options() call trumps this NULL behavior? Yes, that's correct. I take it that you're not finding this to be true? I can't tell yet, as I haven't gotten the coding done. This was mostly a fact-finding excursion, setting up expectations for when I've finally finished with my changes. Many thanks for the assist. You've cleared up a lot for me. --V -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
C API: mysql_options and mysql_real_connect
Assuming a my.cnf file which looks like this: [client] port=3306 socket=/path/to/mysql.sock [app] user=appuser password=apppwd host=my.host.com Ignore for now the insecurity of putting a password in the my.cnf file. This is mostly a hypothetical question at the moment. Calling mysql_options(MYSQL, MYSQL_READ_DEFAULT_FILE, /path/to/my.cnf); and mysql_options(MYSQL, MYSQL_READ_DEFAULT_GROUP, app); in the client will read the options in these two groups. How, if at all, would something like this be useful to mysql_real_connect? From my research it appears that you still need to specify the user, host, pwd and port (assuming TCP/IP connection) when calling mysql_real_connect(), so setting these parms in the my.cnf file does not really help for this scenario. Something (a non-API function, most likely) would still need to parse the file separately and grab the parms for passing to mysql_real_connect(). Is this an accurate assessment? --V -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL-Max
Jon Miller wrote: I've been asked to setup a MySQL DB for critical mass usage. The programmer is asking that I use MySQL-Max. Does this have to be installed along with the regular MySQL version or is it a single product on it's own? From the MySQL/downloads page for 4.0: The Standard binaries are recommended for most users, and includes the InnoDB storage engine. The Max version includes additional features such as the Berkeley DB storage engine and other features that have not been exhaustively tested or are not required for general usage, such as user-defined functions (UDFs), and BIG_TABLE support. When these features have matured and proven to be stable, they will be incorporated into future releases of the Standard binaries. http://www.mysql.com/downloads/mysql-4.0.html --V -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Rename/Create Database from Existing Database
Wong Zach-CHZ013 wrote: Hi 1- Is there a command to rename an existing database ? 2- If not, how does one create a new database from an existing database ? could you show me the steps? Any help is appreciated. Thanks Have you tried the mysqlhotcopy util? http://www.mysql.com/doc/en/mysqlhotcopy.html --V -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
