source command in interactive mode (unix host)
I use MySQL in both a Unix real C/S config and then on my laptop standalone. The documentation on the Web site showing how to take commands from a file is simple enough. source file_name_containing_commands; and this works just fine in Windows, but in UNIX, no matter how I try to resolve the filename e.g., $HOME/my/source file_name_containing_commands; or explicitly dereferencing the path to the command file, I get syntax error. Of course, it doesn't work with the same as Windows line above since the server executing the CLI looks to its local dir. I've spent several hours on this simple, but frustrating problem. Yes. I know the workaround is run in batch mode with the mysql being fed a source file_name_containing_commands arg, but I am now simply confounded by my [mis]understanding of this simple feature. thanks in advance. I also did many searches across the archives before posting, so if I missed a prior post forgive me. -walt
Re: MySQL Package Install for Mac OS X 10.3 (Panther)
G'day Sam I am upgrading to Panther and I need to install MySQL. Does anybody know if the Mac OS X Installer Package v10.2 (Standard) works with 10.3? If it doesn't then what would be the best installation method on a clean 10.3 installation? FWIW, I upgraded to Panther on the weekend and MySQL 4.0.12 is still humming along just fine. I don't use the MySQL build, I use MySQL Complete from www.serverlogistics.com . I have seen something strange with PHP (can't echo anything shorter than 17 characters) but I believe they are working on new builds anyway. cheers kim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Package Install for Mac OS X 10.3 (Panther)
My bad, I should have been more precise. I am not upgrading - I have done a clean install. So, now I need to re-install MySQL. -Thanks On Tuesday, October 28, 2003, at 11:00 PM, Kim Kohen wrote: I am upgrading to Panther -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
help with startup accesss
Hello, I've reinstalled my system (Mac OS 10.3 Server). In trying to get MySQL going again. I can run mysql_install_db, but I can't access the mysql db and I can not set a root user password. If I try to login: mysql -u root it says access denied to [EMAIL PROTECTED] If I simply type mysql (no quotes) I get into MySQL, but cannot affect the MySQL db. I seem to remember that perhaps the answer is to chown -R on MySQL's data files, but I'm not sure where/which those are! I have the following: /var/mysql/ (2 folders are in there 'test' and 'mysql', among a few other files -- that must be the mysql db?); I also have /usr/share/mysql/ (in here are the .cnf files, e.g., my-huge.cnf, et al). I do not see a folder called data anywhere, or I haven't found it. :-( HELP, please? Ted R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Is MySQL Relational? (was: Foreigner keys in MySQL?)
Hi, For decades, people have debated in comp.databases.theory and elsewhere how a 'relational database' should be defined. Codd's original 1970 paper sketches the relational algebra as a query language of a relational database, though Codd is not very precise about what the query language exactly should be. The paper also mentions integrity constraints. Relations in the 1970 paper are defined as mathematical sets, that is, no duplicate rows in tables or any query results are allowed. Thus, no SQL database is Codd-1970-relational, because SQL allows duplicate rows. Codd's 12 rules, from about 1987, demand that a 'relational database' must satisfy several strict conditions. For example, any view 'theoretically updateable should be updateable with the data manipulation language'. Mainly because views and tables are pretty much the same in the theory. No existing database is even close to being Codd-12-relational. Indeed. On the other hand, for example, Elmasri and Navathe in their university textbook define a 'relational database' less strictly, and mention Microsoft Access and Oracle as examples of an 'RDBMS'. Thus, MySQL apparently is Elmasri-Navathe-relational, but not Codd-1970-relational. The same holds for DB2, Oracle, and MS SQL Server. I guess it also comes down to what you're used to work with. If you're used to using domains, check constraints etc, you might consider any database engine that doesn't have these, a lesser database engine. Luckily - as said before - things are improving. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Is MySQL Relational? (was: Foreigner keys in MySQL?)
Begging to differ, no vendor that I'm aware of claims to be compliant with a paper or textbook. They tend to comply with an adopted standard such as: ANSI/ISO/IEC 9075-1(through 5):1999 ISO/IEC 9075-1(through 5):1999 Yes, a strange thing too IMHO. The SQL standard defines a so-called standard for accessing (relational) data. But it doesn't define a relational database engine - it defines a way to access data it and partly based on the relational theory. Collectively known as SQL:1999. While I'd offer that MySQL *is* a relational database, (even though I'm very new to the environment); I think there's some ways to go before MySQL has core compatibility with SQL:1999. That's what's more important to me, the adoption of accepted standards. Ha, I beg to differ when it comes to accepted standard here... it might be some kind of standard, but the implementations are almost always different. And I believe the SQL standard has gone wy beyond it by defining triggers and procedures as well. Then again - if the (PSQL) language would be standard, you would be able to switch engines pretty quick :-) With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Limit Optimization??
That sooo cool... It was the very perfection of beauty. but i didnt' know.why my index (ClassID, Auditing, CreatedTime) are slowly as matt's (ClassID, Auditing, CreatedTime, ArticleID) could matt explain why? Thx matt.. On Tue, 28 Oct 2003 02:06:16 -0600 Matt W [EMAIL PROTECTED] wrote: Hi, OK, did you just create key1 and CreatedTime? If you didn't have them before and don't need them, you can remove them and just create this index for your query (you can definitely replace key1 with this): ALTER TABLE article ADD INDEX (ClassID, Auditing, CreatedTime, ArticleID); And then the query should only use the index for execution. Then you can of course run the second query to get all columns you want: SELECT * FROM article WHERE ArticleID IN (Comma seperated list of ArticleIDs from first query) ORDER BY CreatedTime; Hope that helps! Matt -- Avenger [EMAIL PROTECTED] PHP http://www.phpe.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with insert data
hi,
the MySQL syntax said:
INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
[INTO] tbl_name [(col_name,...)]
VALUES ((expression | DEFAULT),...),(...),...
[ ON DUPLICATE KEY UPDATE col_name=expression, ... ]
or INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
[INTO] tbl_name [(col_name,...)]
SELECT ...
or INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
[INTO] tbl_name
SET col_name=(expression | DEFAULT), ...
[ ON DUPLICATE KEY UPDATE col_name=expression, ... ]
also while insert into user(...) failed is an MySQL problem, while after
INSERT or INTO follows table name an not a function.
Regards,
Rafal
At 17:58 28.10.2003, Rafal Kedziorski wrote:
hi,
I'm using MySQL 4.0.13 and 4.0.14 (4.0.16 I will test tomorrow). And I
have problems with this query:
insert into user(class_id, retail_id, mandant_id, language_id,
user_data_id, nickname, login_name, password, status, creation_date,
last_login_date) values ('35F7A660096411D89BC0D1907F01', '1234', 2, 1,
null, null, '[EMAIL PROTECTED]', 'demo', 8, '2003-10-28 17:31:36', null)
but the same query with space after user
insert into user (class_id, retail_id, mandant_id, language_id,
user_data_id, nickname, login_name, password, status, creation_date,
last_login_date) values ('35F7A660096411D89BC0D1907F01', '1234', 2, 1,
null, null, '[EMAIL PROTECTED]', 'demo', 8, '2003-10-28 17:31:36', null)
works.
Regards,
Rafal
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Corruption and weird service terminations
Hi Matt, I'm sorry to say that even after upgrading to MySQL-4.0.16, our server is still suffering from the exact same symptoms, albeit on a Linux system. We have recently done some serious testing to rule out hardware failure, but the error persists. Tables corrupt and mysqld restarts with a signal 11 error code approximately every tenth minute. Any words of the wise will be greatly appreciated.. :) From: Matt W Date: October 28 2003 2:59am Subject: Re: Corruption and weird service terminations For the corruption, upgrade to 4.0.16, since it may be caused by a corruption bug in versions before 4.0.15. Matt -- Kind regards, Marcus Brofeldt [EMAIL PROTECTED] www.Heste-Nettet.dk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Howto reduce size of MYISAM files after deleting records?
Thanks. Whith InnoDB tables I have to do something similar or it's not necessary? Iago. -Mensaje original- De: Dathan Vance Pattishall [mailto:[EMAIL PROTECTED] Enviado el: martes, 28 de octubre de 2003 19:49 Para: 'Iago Sineiro'; 'MySql Mail List' Asunto: RE: Howto reduce size of MYISAM files after deleting records? Shutdown the mysql server Goto the datadir (/var/lib/mysql) Goto the db directory Type myisamchk -r -S -a *.MYI this will reclaim the bad blocks caused by the delete. - Dathan Vance Pattishall - Sr. Programmer and mySQL DBA for FriendFinder Inc. - http://friendfinder.com/go/p40688 ---Original Message- --From: Iago Sineiro [mailto:[EMAIL PROTECTED] --Sent: Tuesday, October 28, 2003 9:52 AM --To: MySql Mail List --Subject: Howto reduce size of MYISAM files after deleting records? -- --Hi. -- --I delete a lot of records of one MyISAM table in MySQL 4.0.16. After that --I --execute mysqladmin refresh and now the size of the files of the table is --the --same than before. -- --Is something wrong or is necessary doing something more to reduce the --size --of the files? -- --Thanks in advance. -- --Iago. -- -- -- --MySQL General Mailing List --For list archives: http://lists.mysql.com/mysql --To unsubscribe: --http://lists.mysql.com/[EMAIL PROTECTED] ---Publicidad Únete a los miles de sin pareja en Meetic... ¡te vas a enamorar! http://www.iespana.es/_reloc/email.meetic -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
moving databases
Ok, I've set my root password and have found (by creating a db) that the databases are stored (Mac OSX Server 10.3) in /private/var/mysql. I have my old system on a separate hard disk; however, mysql is not functioning there, but I do have my databases there. Can I copy these database to their new home? How will MySQL recognize them? (What is the best way in my situation to accomplish this move? (Again, MySQL is not available to 'run' where they are.) Thanks in Advance! TR -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL/InnoDB-4.0.16 +Optimizer behaviour
Hello all, Default optimizer behavior has changed in 4.0.16 (since 4.0.14) for this simple question: select state_id, orderdata_id from order_delivery where orderdata_id in (3193340,3193343,3193346,3193349,3193352,3193355) and is_deleted=0 order by xtime desc CREATE TABLE `order_delivery` ( `orderdata_id` int(11) NOT NULL default '0', `state_id` int(11) NOT NULL default '0', `xtime` datetime NOT NULL default '-00-00 00:00:00', `admin_user_id` int(11) NOT NULL default '0', `note` text NOT NULL, `is_deleted` int(1) NOT NULL default '0', KEY `orderdata_id` (`orderdata_id`), KEY `is_deleted` (`is_deleted`), KEY `xtime` (`xtime`) ) TYPE=InnoDB | 4.0.14 === mysql EXPLAIN select state_id, orderdata_id from order_delivery where - orderdata_id in - (3193340,3193343,3193346,3193349,3193352,3193355) - and is_deleted=0 order by xtime desc; ++---+-+--+-+--+--+-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | ++---+-+--+-+--+--+-+ | order_delivery | range | orderdata_id,is_deleted | orderdata_id | 4 | NULL | 5 | Using where; Using filesort | ++---+-+--+-+--+--+-+ 1 row in set (0.25 sec 4.0.16 === ++--+-++-+---++-+ | table | type | possible_keys | key| key_len | ref | rows | Extra | ++--+-++-+---++-+ | order_delivery | ref | orderdata_id,is_deleted | is_deleted | 4 | const | 228021 | Using where; Using filesort | ++--+-++-+---++-+ 1 row in set (0.15 sec) Due to this simple queries with WHERE IN() became aprox. 30 time slower in my case. MyISAM in not affected. Have anyone ever seen such problems with InnoDB tables in 4.0.16? -- Best regards, Sergey S. Kostyliov [EMAIL PROTECTED] Public PGP key: http://sysadminday.org.ru/rathamahata.asc -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: moving databases
that will be recognized very well, just check the file ownership after copying directory and files. all the data directory and files should be owned by mysql only Nitin - Original Message - From: [EMAIL PROTECTED] To: MySQL List [EMAIL PROTECTED] Sent: Wednesday, October 29, 2003 2:23 PM Subject: moving databases Ok, I've set my root password and have found (by creating a db) that the databases are stored (Mac OSX Server 10.3) in /private/var/mysql. I have my old system on a separate hard disk; however, mysql is not functioning there, but I do have my databases there. Can I copy these database to their new home? How will MySQL recognize them? (What is the best way in my situation to accomplish this move? (Again, MySQL is not available to 'run' where they are.) Thanks in Advance! TR -- 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: Error 1148 and 1045
Steve Buehler [EMAIL PROTECTED] wrote: I have upgraded my mysql version to 3.23.58 and am coming up with a couple of errors using phpmyadmin to Insert data from a textfile into table I didn't get this on the previous versions of mysql. Here are the errors: SQL-query : LOAD DATA INFILE '/tmp/phpKFlQ2b' INTO TABLE `annsubscribers` FIELDS TERMINATED BY ',' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\r\n'( `email` ) MySQL said: #1045 - Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) User must have FILE privilege to use LOAD DATA INFILE. --- SQL-query : LOAD DATA LOCAL INFILE '/tmp/phpsj1WGM' INTO TABLE `annsubscribers` FIELDS TERMINATED BY ',' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\r\n'( `email` ) MySQL said: #1148 - The used command is not allowed with this MySQL version You should run server and client with --local-infile option: http://www.mysql.com/doc/en/LOAD_DATA_LOCAL.html --- Can anybody shed some light on how to fix this? I presume the first is a permission problem that is from the new way that MySQL handles things. The second is just a mystery to me. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [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: Insert ... Select question
I use this syntax but I have privilege problem. Thenk you anyway - Original Message - From: Nitin [EMAIL PROTECTED] To: IEEIO AAOOCO [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Monday, October 27, 2003 5:20 PM Subject: Re: Insert ... Select question of course, the syntax is: insert into db3.table2 (column list) select column list from db1.table1 for more information have a look at http://www.mysql.com/doc/en/INSERT_SELECT.html Enjoy Nitin - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, October 27, 2003 8:28 PM Subject: Insert ... Select question Hello list I want to insert ... select data from table1 of db1 to table2 of db3. Is that possible? Thank in advance -- 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]
SAP DB
Hi, Does anyone knowns SAP DB and can tell me if it's a good database? Thanks! Catia Crepaldi System developer Catho Online -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SQL for Running Total?
What is the trick for writing a SQL that gives me running totals of some values? For example, I have a table with values like this: Field1 1 2 3 4 and I want a SQL to return something like this: 1 3 6 10 TIA, Ivo Karindi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL for Running Total?
Ivo [EMAIL PROTECTED] wrote: What is the trick for writing a SQL that gives me running totals of some values? For example, I have a table with values like this: Field1 1 2 3 4 and I want a SQL to return something like this: 1 3 6 10 You can do something like: SELECT @sum:=0; SELECT @sum:[EMAIL PROTECTED] FROM table_name; -- 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]
after upgrade to 4.0.16
Thanks for the new MySQL server. My MySQLcc works fine now. But.. My PHP cann't connect anymore to the server. The lib is not there i think. Because i get an error mysql_connect() doesnot exist. What to do ?? I need my server with PHP and MySQL for a demostration. Help With regards, Rob J Wierenga Yard-IT BV +31-50-404 2555 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: moving databases
In a message dated 10/29/03 4:18:28 AM Eastern Standard Time, [EMAIL PROTECTED] writes: Ok, I've set my root password and have found (by creating a db) that the databases are stored (Mac OSX Server 10.3) in /private/var/mysql. I have my old system on a separate hard disk; however, mysql is not functioning there, but I do have my databases there. Can I copy these database to their new home? How will MySQL recognize them? (What is the best way in my situation to accomplish this move? (Again, MySQL is not available to 'run' where they are.) I have not used Mac OSX, but I had a similar situation migrating my company to Linux from Windows2000 in our datacenter. I just moved the files over as I recall. You need to create a sub-directory under the DataDir (looks like under /private/var/mysql in your case) and I restarted MySQL. NOTE: All my tables were MyISAM. This may not work with InnoDB tables. HTH -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Creating backups
Hi there, Does anyone have some tips or advice for creating backups of all the tables and data contained therein? Do you use custom solutions or perhaps open-source projects? -Erich- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Log file format?
Hassan Schroeder [EMAIL PROTECTED] wrote: If this is documented anywhere, I sure can't find it, so -- The query log fields are headed: Time, Id, Command, Argument. What is 'Id'? It's a process id. You can see it in the output of SHOW PROCESSLIST. -- 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: Creating backups
mysqldump...lots and lots of options http://www.mysql.com/doc/en/mysqldump.html hth Jeff Erich C. Beyrent To: [EMAIL PROTECTED] [EMAIL PROTECTED]cc: yrent.net Subject: Creating backups 10/29/2003 09:03 AM Please respond to Erich C. Beyrent Hi there, Does anyone have some tips or advice for creating backups of all the tables and data contained therein? Do you use custom solutions or perhaps open-source projects? -Erich- -- 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: Creating backups
Read the manual at the section: Disaster Prevention and Recovery Database Backups with mysqldump you can dump a whole database. hans At 09:03 29-10-03 -0500, you wrote: Hi there, Does anyone have some tips or advice for creating backups of all the tables and data contained therein? Do you use custom solutions or perhaps open-source projects? -Erich- -- 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: LOAD DATA warnings
[EMAIL PROTECTED] wrote: mysql LOAD DATA LOCAL INFILE './2003-01/MKR_OCAK.txt' INTO TABLE quantis_mkr; Query OK, 271392 rows affected (4 min 2.95 sec) Records: 271392 Deleted: 0 Skipped: 0 Warnings: 61 How can I see those warnings? I checked the error log but nothing shows up there. From version 4.1.1 you can use SHOW WARNINGS command: http://www.mysql.com/doc/en/SHOW_WARNINGS.html What about earlier versions? I'm using 4.0.13 Can I see those warnings in 4.0.13? Unfortunately you can't see warnings for LOAD DATA before 4.1.1. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [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]
crashed tables after copy
Over the last two days, we have upgraded the OS on our two database servers. We have one that handles the database for our content and the other handles the database for our forums. For completeness sake, they were both running RH 7.1 with kernels patched for ReiserFS. The data was stored on a ReiserFS, Raid 5 partition. Both databases were running 4.0.15a. Here is the problem we had. We shut down server A cleanly and rsync'd the database dir over to server B. Shortly after doing this, we started getting 145 errors on some of the data we had just moved. We stopped mysql, repaired (myisamchk -of) the tables and got going again. Shortly after that, the tables were crashed again. We then repaired again, dumped to a .sql file, then recreated the tables from scratch. From that point on, no problems. We upgraded server A to RH 9. We then moved all the data from server B over to server A using rsync. Again, shortly after, crashed tables. repair, no good. dumped the tables in question, rebuilt from scratch, no problem. So, is there something I did that could have caused this? It was not the same tables each time. While I don't plan on moving the data around any time soon, I do need to set up a slave and don't want to screw anything up. I normally rsync or tar up the data on the master as LOAD DATA FROM MASTER has not proven reliable over WAN for me. -- Brian Moon, dealnews.com, Inc. dealnews.com News on computer hardware bargains dealmac.com The latest Mac bargains Comparison shop @ dealram.com dealcam.com dealink.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: after upgrade to 4.0.16
Rob Wierenga wrote: Thanks for the new MySQL server. My MySQLcc works fine now. But.. My PHP cann't connect anymore to the server. The lib is not there i think. Because i get an error mysql_connect() doesnot exist. What to do ?? snip If php gives you the error that mysql_connect is a call to an undefined function, it's not a MySQL error. It means your version of php was not compiled with MySQL support. What OS? What version of PHP? Source, binary, RPM install of PHP? -- By-Tor.com It's all about the Rush http://www.by-tor.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Upgrade to MySQL 4.1.0-alpha [No Virus found]
Hello,
I'm trying to upgrade to version 4.1.0-alpha of MySQL from version 3.23.51
on a Windows 2000 platform.
I've downloaded the binary distribution of 4.1.0-alpha and followed the
install process(README.1st) for an upgrade install.
At the step :
* Execute the 'mysql_fix_privilege_tables.sql' script in the 'scripts
directory:
c:\mysql\bin\mysql c:\mysql\scripts\mysql_fix_privilege_tables.sql
i got the error message : ERROR 1046 at line 1: No Database Selected.
Indeed, the script does not specify a particular database when attempting
to ALTER table user...
Then i tryed to run this script with a mysqlfront client(connected as root)
on the 'mysql' database and got the following error list :
1060 - Duplicate column name 'File_priv'
Query:
ALTER TABLE user add File_priv enum('N','Y') NOT NULL
=
1060 - Duplicate column name 'Grant_priv'
Query:
ALTER TABLE user add Grant_priv enum('N','Y') NOT NULL,add References_priv
enum('N','Y') NOT NULL,add Index_priv enum('N','Y') NOT NULL,add Alter_priv
enum('N','Y') NOT NULL
=
1060 - Duplicate column name 'Grant_priv'
Query:
ALTER TABLE host add Grant_priv enum('N','Y') NOT NULL,add References_priv
enum('N','Y') NOT NULL,add Index_priv enum('N','Y') NOT NULL,add Alter_priv
enum('N','Y') NOT NULL
=
1060 - Duplicate column name 'Grant_priv'
Query:
ALTER TABLE db add Grant_priv enum('N','Y') NOT NULL,add References_priv
enum('N','Y') NOT NULL,add Index_priv enum('N','Y') NOT NULL,add Alter_priv
enum('N','Y') NOT NULL
=
1060 - Duplicate column name 'ssl_type'
Query:
ALTER TABLE user
ADD ssl_type enum('','ANY','X509', 'SPECIFIED') NOT NULL,
ADD ssl_cipher BLOB NOT NULL,
ADD x509_issuer BLOB NOT NULL,
ADD x509_subject BLOB NOT NULL
=
1054 - Unknown column 'Type' in 'columns_priv'
Query:
ALTER TABLE columns_priv change Type Column_priv
set('Select','Insert','Update','References') DEFAULT '' NOT NULL
=
1064 - You have an error in your SQL syntax. Check the manual that
corresponds to your MySQL server version for the right syntax to use near
'func ADD type enum ('function','aggregate') NOT NULL' at line 1
Query:
ALTER func ADD type enum ('function','aggregate') NOT NULL
=
1060 - Duplicate column name 'Show_db_priv'
Query:
ALTER TABLE user
ADD Show_db_priv enum('N','Y') DEFAULT 'N' NOT NULL AFTER alter_priv,
ADD Super_priv enum('N','Y') DEFAULT 'N' NOT NULL AFTER Show_db_priv,
ADD Create_tmp_table_priv enum('N','Y') DEFAULT 'N' NOT NULL AFTER
Super_priv,
ADD Lock_tables_priv enum('N','Y') DEFAULT 'N' NOT NULL AFTER
Create_tmp_table_priv,
ADD Execute_priv enum('N','Y') DEFAULT 'N' NOT NULL AFTER Lock_tables_priv,
ADD Repl_slave_priv enum('N','Y') DEFAULT 'N' NOT NULL AFTER Execute_priv,
ADD Repl_client_priv enum('N','Y') DEFAULT 'N' NOT NULL AFTER
Repl_slave_priv
=
1060 - Duplicate column name 'max_questions'
Query:
ALTER TABLE user
ADD max_questions int(11) NOT NULL AFTER x509_subject,
ADD max_updates int(11) unsigned NOT NULL AFTER max_questions,
ADD max_connections int(11) unsigned NOT NULL AFTER max_updates
=
1060 - Duplicate column name 'Create_tmp_table_priv'
Query:
ALTER TABLE db
ADD Create_tmp_table_priv enum('N','Y') DEFAULT 'N' NOT NULL,
ADD Lock_tables_priv enum('N','Y') DEFAULT 'N' NOT NULL
=
1060 - Duplicate column name 'Create_tmp_table_priv'
Query:
ALTER TABLE host
ADD Create_tmp_table_priv enum('N','Y') DEFAULT 'N' NOT NULL,
ADD Lock_tables_priv enum('N','Y') DEFAULT 'N' NOT NULL
=
The joined file mysql_structure.sql contains my 'mysql' database structure.
* Another thing i can report is that when i attempt to stop the server
executing the command :
c:\mysql\bin\mysqladmin -u root shutdown
i can see the memory freeding in the task manager, but the process
mysqld-opt.exe is still running... Is it normal ?
* One of the reasons i am interested in version 4.1.0 is that it manages
replication with user variables and temporary tables.
Do i have to upgrade my master server(version 3.23.51) too or is it
possible to get safe replication only by upgrading my slave(s) ?
If i don't need to upgrade the master, can i use my slave'databases
(except 'mysql') to recover data in case of disaster ?
Thank you for your help.
Olivier Doucet(See attached file: mysql_structure.sql)(See attached file:
README.1st)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: problem with MySQL and latest release of Mac OX X (Panther)
'mysql -A sampdb' hangs as does every other mysql command. There are no recent entries in hostname.err. -Original Message- From: Dathan Vance Pattishall [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 28, 2003 5:52 PM To: Anderson, James H [IT]; [EMAIL PROTECTED] Subject: RE: problem with MySQL and latest release of Mac OX X (Panther) Could you look at you're hostname.err file and see if it says checking table? Also try mysql -A sampdb - Dathan Vance Pattishall - Sr. Programmer and mySQL DBA for FriendFinder Inc. - http://friendfinder.com/go/p40688 ---Original Message- --From: Anderson, James H [IT] [mailto:[EMAIL PROTECTED] --Sent: Tuesday, October 28, 2003 2:03 PM --To: 'Dathan Vance Pattishall'; [EMAIL PROTECTED] --Subject: RE: problem with MySQL and latest release of Mac OX X (Panther) -- --BTW, nothing's changed in my.cnf... -- --.my.cnf - --[client] --host=localhost --user=jim --password=password -- --No query, I just entered 'mysql sampdb'. It hung indefinitely; I had to --kill --the window. --Since I couldn't get into mysql I don't know the value of any variables. -- -- ---Original Message- --From: Dathan Vance Pattishall [mailto:[EMAIL PROTECTED] --Sent: Tuesday, October 28, 2003 4:42 PM --To: Anderson, James H [IT]; [EMAIL PROTECTED] --Subject: RE: problem with MySQL and latest release of Mac OX X (Panther) -- -- --Can you post you're my.cnf options / your query in question / how long --have you seen it hang / what status it is in / and your show status --vars. -- -- -- --- Dathan Vance Pattishall -- - Sr. Programmer and mySQL DBA for FriendFinder Inc. -- - http://friendfinder.com/go/p40688 -- -- -Original Message- From: Anderson, James H [IT] [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 28, 2003 1:06 PM To: [EMAIL PROTECTED] Subject: problem with MySQL and latest release of Mac OX X (Panther) Much to my surprise and chagrin, I'm having a problem using MySQL --after upgrading to the latest release of Mac OS X this past weekend. As far --as I can tell, the server seems to be running, but when I attempt to --execute the mysql command it just hangs. Any ideas? Thanks, jim -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: crashed tables after copy
I'm experiencing the same problem. Everytime when I shut down mysql and use myisamchk to do the maintenance, I'll get a few tables crashed. In phpmyadmin they showed a status of in use. I have to repair them to get the db working. Rainer - Original Message - From: Brian Moon [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, October 29, 2003 11:16 PM Subject: crashed tables after copy Over the last two days, we have upgraded the OS on our two database servers. We have one that handles the database for our content and the other handles the database for our forums. For completeness sake, they were both running RH 7.1 with kernels patched for ReiserFS. The data was stored on a ReiserFS, Raid 5 partition. Both databases were running 4.0.15a. Here is the problem we had. We shut down server A cleanly and rsync'd the database dir over to server B. Shortly after doing this, we started getting 145 errors on some of the data we had just moved. We stopped mysql, repaired (myisamchk -of) the tables and got going again. Shortly after that, the tables were crashed again. We then repaired again, dumped to a .sql file, then recreated the tables from scratch. From that point on, no problems. We upgraded server A to RH 9. We then moved all the data from server B over to server A using rsync. Again, shortly after, crashed tables. repair, no good. dumped the tables in question, rebuilt from scratch, no problem. So, is there something I did that could have caused this? It was not the same tables each time. While I don't plan on moving the data around any time soon, I do need to set up a slave and don't want to screw anything up. I normally rsync or tar up the data on the master as LOAD DATA FROM MASTER has not proven reliable over WAN for me. -- Brian Moon, dealnews.com, Inc. dealnews.com News on computer hardware bargains dealmac.com The latest Mac bargains Comparison shop @ dealram.com dealcam.com dealink.com -- 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]
Finding information in the last record
Being real new to MySQL I am having difficulty finding information on how to retrieve data from the last record in a database. I have tried using the SELECT LAST_INSERT_ID() statement but that only works on a per connection basis and doesn't help all the times. Basically I want to goto the end of file and pull the data from a particular field (sku) from the last record/row. If anyone could point me in the right direction I would appreciate it Jess --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.530 / Virus Database: 325 - Release Date: 10/22/03 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Finding information in the last record
Hi Jess, Being real new to MySQL I am having difficulty finding information on how to retrieve data from the last record in a database. Mind you - the last record doesn't have to be the last record if you're running an application with multiple users. That said, the last record in any table using a numeric PK should be the record with the highest PK value: select * from table_name order by pk_column desc limit 1 I have tried using the SELECT LAST_INSERT_ID() statement but that only works on a per connection basis and doesn't help all the times. Basically I want to goto the end of file and pull the data from a particular field (sku) from the last record/row. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Finding information in the last record
this should work SELECT sku from [table] order by [key] desc limit 1 hth Jeff Hunter, Jess [EMAIL PROTECTED]To: [EMAIL PROTECTED] RC.ORG cc: Subject: Finding information in the last record 10/29/2003 10:50 AM Being real new to MySQL I am having difficulty finding information on how to retrieve data from the last record in a database. I have tried using the SELECT LAST_INSERT_ID() statement but that only works on a per connection basis and doesn't help all the times. Basically I want to goto the end of file and pull the data from a particular field (sku) from the last record/row. If anyone could point me in the right direction I would appreciate it Jess --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.530 / Virus Database: 325 - Release Date: 10/22/03 -- 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]
inconsistent query times for same query
Hi everyone. I'm using MySQL 4.0.16 with W2K/SP4 on a 512KB RAM/1.2GHz
Athlon machine (my dev workstation).
I'm having a strange problem: when I execute a query via the MySQL Control
Center, it takes 10 seconds to return. About three seconds into the query,
I issue a mysqladmin processlist and see the State as sending data. To
me, that means the query is done and the server is sending the data to the
client (I'm running both on same machine). The CPU is only at about 4%, but
the hard drive light is flashing like an XMas tree on crack.
I examined the query using Explain and it's using the correct index as I
specified.
If I re-execute the query, it returns in .13 seconds!!!
I thought maybe it was cached by the client so I kill the client and
execute it again - .13 seconds. I restart the server and do it again - .13
seconds. So it doesn't look like anything's being cached. Variables
query_cache_size=0 and query_cache_type=DEMAND, it seems like no caching
should happen. This happens to other queries as well.
I just don't get it. Why the difference?
The table test has 1,000,000 records, so I'm definitely not complaining
about a subsecond response - but if the public hits the production server
and I get the 10 second version for every query, I'm in trouble. Since
users dynamically create the queries, I can't count on them being cached -
so any moderately complex query seems to exibit this behavior. I really
feel like I need to understand why it's happening so I can fix any
underlying problems.
Any ideas
Here's the query - because the users choose several options to build the
query, it's dynamically built via a Java class (don't think that's
relevant, but..):
SELECT
UID,UserName,Gender,City,State,Zip,Country,Age,Photo,OnLine,LastLogon FROM
test USE INDEX (big) WHERE Status=2 AND Viewable=1 AND Age =18 AND Age =
99 AND Photo!='' AND Height =66 AND gender IN ('b') AND Weight =100 AND
Height =60 AND Weight =150 LIMIT 250
The following columns are in the big multi-column index:
Status, Viewable, Online, Age, Height. See the table definition below.
here are the relavent columns:
+---+---+--+-+--+---+
| Field | Type | Null | Key | Default
| Extra |
+---+---+--+-+--+---+
| UID | int(11) | | PRI | 0
| |
| UserName | char(20) | | UNI |
| |
| Status| tinyint(1)| | MUL | 0
| |
| Viewable | tinyint(1)| | | 0|
|
| City | char(30) | YES | | some city
| |
| State | char(2) | YES | | CA
| |
| Country | char(3) | YES | | USA
| |
| Zip | char(10) | YES | MUL | 90210
| |
| Age | tinyint(2)| YES | MUL | NULL
| |
| Gender| char(1) | YES | | NULL
| |
| Height | tinyint(3)| YES | | NULL |
|
| Weight| char(3) | YES | MUL | NULL
| |
| OnLine| tinyint(1)| YES | | NULL
| |
| Photo | char(30) | YES | | /path/path/photo.jpg |
|
+---+---+--+-+--+---+
Thanks for any insight!
-Bob
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
FW: problem with MySQL and latest release of Mac OX X (Panther)
I should add that I can access the database via a java program, just not using the mysql command. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How do I have 3.23.54 and 4.0.15 running at the same time?
Thanks for the tip. Before I sent my message I tried different my.cnf files. The basedir made the trick for the pid-file. Now, how do I connect to mysql in the other port? When I use mysql -umysql -p --port 10441 it takes me to it but is finding the data for 3306. I tried login to the machine as a different user. I went to the basedir/bin. I cannot find a switch for datadir. Thanks Juan Egor Egorov on 10/22/03 at 6:21 PM +0300 wrote about: Re: How do I have 3.23.54 and 4.0.15 running at the sam Juan M. Quiroz [EMAIL PROTECTED] wrote: Hi, That's basically my question. I want to be able to run the 2 releases on the same OSX 10.2.8 machine. Is there a way? Yes. Values of the --port, --socket, --pid-file must be different for each server. Check also this section of MySQL manual: http://www.mysql.com/doc/en/Multiple_servers.html -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: inconsistent query times for same query
There is caching but it is at OS level: on first query, data are
fetched from disk; other queries read directly from file system cache.
Hope this helps
Joseph Bueno
[EMAIL PROTECTED] wrote:
Hi everyone. I'm using MySQL 4.0.16 with W2K/SP4 on a 512KB RAM/1.2GHz
Athlon machine (my dev workstation).
I'm having a strange problem: when I execute a query via the MySQL Control
Center, it takes 10 seconds to return. About three seconds into the query,
I issue a mysqladmin processlist and see the State as sending data. To
me, that means the query is done and the server is sending the data to the
client (I'm running both on same machine). The CPU is only at about 4%, but
the hard drive light is flashing like an XMas tree on crack.
I examined the query using Explain and it's using the correct index as I
specified.
If I re-execute the query, it returns in .13 seconds!!!
I thought maybe it was cached by the client so I kill the client and
execute it again - .13 seconds. I restart the server and do it again - .13
seconds. So it doesn't look like anything's being cached. Variables
query_cache_size=0 and query_cache_type=DEMAND, it seems like no caching
should happen. This happens to other queries as well.
I just don't get it. Why the difference?
The table test has 1,000,000 records, so I'm definitely not complaining
about a subsecond response - but if the public hits the production server
and I get the 10 second version for every query, I'm in trouble. Since
users dynamically create the queries, I can't count on them being cached -
so any moderately complex query seems to exibit this behavior. I really
feel like I need to understand why it's happening so I can fix any
underlying problems.
Any ideas
Here's the query - because the users choose several options to build the
query, it's dynamically built via a Java class (don't think that's
relevant, but..):
SELECT
UID,UserName,Gender,City,State,Zip,Country,Age,Photo,OnLine,LastLogon FROM
test USE INDEX (big) WHERE Status=2 AND Viewable=1 AND Age =18 AND Age =
99 AND Photo!='' AND Height =66 AND gender IN ('b') AND Weight =100 AND
Height =60 AND Weight =150 LIMIT 250
The following columns are in the big multi-column index:
Status, Viewable, Online, Age, Height. See the table definition below.
here are the relavent columns:
+---+---+--+-+--+---+
| Field | Type | Null | Key | Default
| Extra |
+---+---+--+-+--+---+
| UID | int(11) | | PRI | 0
| |
| UserName | char(20) | | UNI |
| |
| Status| tinyint(1)| | MUL | 0
| |
| Viewable | tinyint(1)| | | 0|
|
| City | char(30) | YES | | some city
| |
| State | char(2) | YES | | CA
| |
| Country | char(3) | YES | | USA
| |
| Zip | char(10) | YES | MUL | 90210
| |
| Age | tinyint(2)| YES | MUL | NULL
| |
| Gender| char(1) | YES | | NULL
| |
| Height | tinyint(3)| YES | | NULL |
|
| Weight| char(3) | YES | MUL | NULL
| |
| OnLine| tinyint(1)| YES | | NULL
| |
| Photo | char(30) | YES | | /path/path/photo.jpg |
|
+---+---+--+-+--+---+
Thanks for any insight!
-Bob
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Creating backups
but if my table is sooo big.. eg if large than 1 G. so use the mysqldump is more and more slowly speed. Hans van Dalen [EMAIL PROTECTED] ??:[EMAIL PROTECTED] Read the manual at the section: Disaster Prevention and Recovery Database Backups with mysqldump you can dump a whole database. hans At 09:03 29-10-03 -0500, you wrote: Hi there, Does anyone have some tips or advice for creating backups of all the tables and data contained therein? Do you use custom solutions or perhaps open-source projects? -Erich- -- 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]
LIMIT and placeholders
Hi, This is rather a Perl DBI question than a MySQL one. I have recently upgraded a MySQL 3.23 server to 4.0. It resulted in some of my queries (issued using Perl DBI) starting to fail. It turned out to be that queries like $sth = $dbh-prepare(SELECT whatever FROM something WHERE anything LIMIT ?,?); $sth-execute(1,2); are no longer working (...you have an error in your query...). However, this works: $sth = $dbh-prepare(SELECT whatever FROM something WHERE anything LIMIT 1,2); $sth-execute; so the problem is with the placeholders in the LIMIT clause. Interestingly, other placeholders not in the LIMIT part work. For example $sth = $dbh-prepare(SELECT whatever FROM something WHERE anything=?); $sth-execute(123); and $sth = $dbh-prepare(SELECT whatever FROM something WHERE anything=? LIMIT 1,2); $sth-execute(123); are OK. Any ideas what causes this behaviour? Maybe I have not upgraded my DBI DBD packages properly? Or are LIMIT placeholders no longer supported in DBI with MySQL 4? Thanx, - Fagyal Csongor -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Installation Help
Hi, Im trying to install a newer version of Mysql on our Red Hat 2.1 AS. I'm still new to Linux Installations. I can't seem to figure out what im doing wrong. This is what I have tried, can somebody point me in the right direction [EMAIL PROTECTED] root]# rpm -i MySQL-server-4.1.0-0.i386.rpm error: failed dependencies: MySQL-server conflicts with mysql-server-3.23.58-1.72 MySQL conflicts with mysql-3.23.58-1.72 [EMAIL PROTECTED] root]# rpm -U MySQL-server-4.1.0-0.i386.rpm error: failed dependencies: libmysqlclient.so.10 is needed by mod_auth_mysql-1.11-1 libmysqlclient.so.10 is needed by MySQL-python-0.9.0-2 libmysqlclient.so.10 is needed by perl-DBD-MySQL-1.2216-4 libmysqlclient.so.10 is needed by php-mysql-4.1.2-2.1.6 Thanks, Mike. ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager. This footnote also confirms that this email message has been swept by MIMEsweeper for the presence of computer viruses. www.mimesweeper.com **
Re: inconsistent query times for same query
Thanks Joseph.
I just to another look at the query and it actually takes 65 seconds on the
first run, not 10 seconds, so this is important for me to understand.
So my next questions are:
Is there any way to tune the OS file system cache?
Is there any way to flush it or examine what's in it?
Seems like if I wait a while, the long query happens again. Looks like it
flushes itself after some time - how often does it flush itself?
Thanks again,
-Bob
|-+
| | Joseph Bueno |
| | [EMAIL PROTECTED]|
| | der.com |
| ||
| | 10/29/2003 11:36 |
| | AM |
| ||
|-+
---|
|
|
| To: [EMAIL PROTECTED]
|
| cc: [EMAIL PROTECTED]
|
| Subject: Re: inconsistent query times for same query
|
---|
There is caching but it is at OS level: on first query, data are
fetched from disk; other queries read directly from file system cache.
Hope this helps
Joseph Bueno
[EMAIL PROTECTED] wrote:
Hi everyone. I'm using MySQL 4.0.16 with W2K/SP4 on a 512KB RAM/1.2GHz
Athlon machine (my dev workstation).
I'm having a strange problem: when I execute a query via the MySQL
Control
Center, it takes 10 seconds to return. About three seconds into the
query,
I issue a mysqladmin processlist and see the State as sending data.
To
me, that means the query is done and the server is sending the data to
the
client (I'm running both on same machine). The CPU is only at about 4%,
but
the hard drive light is flashing like an XMas tree on crack.
I examined the query using Explain and it's using the correct index as
I
specified.
If I re-execute the query, it returns in .13 seconds!!!
I thought maybe it was cached by the client so I kill the client and
execute it again - .13 seconds. I restart the server and do it again -
.13
seconds. So it doesn't look like anything's being cached. Variables
query_cache_size=0 and query_cache_type=DEMAND, it seems like no caching
should happen. This happens to other queries as well.
I just don't get it. Why the difference?
The table test has 1,000,000 records, so I'm definitely not complaining
about a subsecond response - but if the public hits the production server
and I get the 10 second version for every query, I'm in trouble. Since
users dynamically create the queries, I can't count on them being cached
-
so any moderately complex query seems to exibit this behavior. I really
feel like I need to understand why it's happening so I can fix any
underlying problems.
Any ideas
Here's the query - because the users choose several options to build the
query, it's dynamically built via a Java class (don't think that's
relevant, but..):
SELECT
UID,UserName,Gender,City,State,Zip,Country,Age,Photo,OnLine,LastLogon
FROM
test USE INDEX (big) WHERE Status=2 AND Viewable=1 AND Age =18 AND Age
=
99 AND Photo!='' AND Height =66 AND gender IN ('b') AND Weight =100 AND
Height =60 AND Weight =150 LIMIT 250
The following columns are in the big multi-column index:
Status, Viewable, Online, Age, Height. See the table definition below.
here are the relavent columns:
+---+---+--+-+--+---+
| Field | Type | Null | Key | Default
| Extra |
+---+---+--+-+--+---+
| UID | int(11) | | PRI | 0
| |
| UserName | char(20) | | UNI |
| |
| Status| tinyint(1)| | MUL | 0
| |
| Viewable | tinyint(1)| | | 0
|
|
| City | char(30) | YES | | some city
| |
| State | char(2) | YES | | CA
| |
| Country | char(3) | YES | | USA
| |
| Zip | char(10) | YES | MUL | 90210
| |
| Age | tinyint(2)| YES | MUL | NULL
| |
| Gender| char(1) | YES | | NULL
| |
| Height | tinyint(3)| YES | | NULL
|
|
| Weight| char(3) | YES | MUL | NULL
| |
| OnLine| tinyint(1)| YES | | NULL
| |
| Photo |
Re: LIMIT and placeholders
On 29 Oct 2003 at 18:00, Fagyal, Csongor wrote: Any ideas what causes this behaviour? Maybe I have not upgraded my DBI DBD packages properly? Or are LIMIT placeholders no longer supported in DBI with MySQL 4? I don't think the MySQL version is relevant. The change is in DBD::mysql 2.9002, which now assumes all values substituted for placeholders are strings unless specifically told otherwise. Apparently there were situations where quotes were not being added when they should have been, which is dangerous. It's hideously annoying, since it happens even when the variable in question has never been used in anything but a numeric context, but the only place it really matters is in limits. MySQL automatically converts strings to numbers when necessary in most places, but not in limits. The arguments to LIMIT must be integer constants. You have to use bind_param() to identify the parameter as an integer. Rudy Lippan, the author of DBD::mysql, had a message about it on the dbi-users list: http://groups.google.com/groups?selm=Pine.LNX.4.44.0307291622390.14615 -10%40elfride.ineffable.net -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Creating backups
avenger wrote: but if my table is sooo big.. eg if large than 1 G. so use the mysqldump is more and more slowly speed. I don't have one gig tables, but I do have a few that come in at a few hundred meg. I actually dump them to another machine on my network (100mbit NIC cards) and it usually takes no more than a four or five minutes. Since I do the main dump once a day at 1am, it could run for a half hour and I wouldn't care. For critical data (mainly our accounting database), I do hourly dumps. Those tables are relatively small, so it doesn't take more than 40 or 50 seconds (I've only seen it take that long when things have been pretty busy). -- A. Clausen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to define a required field in a table?
Michael,
Thanks for informing me on that one. I was not aware of that problem
with multi-row inserts. For the sake of consistency with other DBMS's,
I don't use those, and now I'm quite glad. In fact, this attribute of
mySQL also applies to UPDATE statements, which allow the same columns to
be set to null. It's further evidence that the database is not the
place to be validating your data, despite the available syntax. Clients
are who need to check the values.
Kevin
This behavior is not specific to Load Data Infile, it is general to
mysql. You might want to take a look at the docs at
http://www.mysql.com/doc/en/constraint_NOT_NULL.html. The
first line
is, To be able to support easy handling of non-transactional tables,
all fields in MySQL have default values. Mysql
automatically converts
missing or illegal values to column defaults, because in the
non-transactional case, it cannot roll back when you have an illegal
value in the the 300th row of a 500 row insert.
Kevin's example is actually a special case. Consider:
mysql create table testnull (X varchar(10) not null);
Query OK, 0 rows affected (0.51 sec)
mysql insert into testnull values (null);
ERROR 1048: Column 'X' cannot be null
mysql insert into testnull values ('first'), (null), ('third');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 1
mysql select * from testnull;
+---+
| X |
+---+
| first |
| |
| third |
+---+
3 rows in set (0.01 sec)
As you can see, you only get an error in the single row insert case.
For multiple rows, the illegal NULL is replaced with the
column default
('' in this case), as you see using LOAD DATA INFILE.
The short term solution is to validate the data in the input file
before it gets imported. I was hoping there was an option
in Load Data
Infile to prevent it from converting NULL's to '' or 0. I'd rather
have invalid data rejected than converted.
Mike
The usual solution is the one you mention: Verify your data before
inserting or loading, rather than counting on mysql to reject
bad data.
Another option might be to initially define the columns to
allow NULL,
as there are NULLs in the data. You could then find the bad data by
searching for the NULLs:
select * from my_table where should_not_be_null_col IS NULL;
Then do whatever is appropriate. Of course, which way is easier will
depend on your situation.
There is an alternative solution, but it may be overkill compared to
what you want. You can build a copy of mysql from source with the
-DDONT_USE_DEFAULT_FIELDS compile option. I have never tried
this, but
as I understand it, this will turn off defaults completely, yielding
errors for missing or illegal values. Note that I believe you cannot
even explicitly set a default for a column if you use this
option, but I
could be wrong.
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql? [EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: inconsistent query times for same query
[EMAIL PROTECTED] wrote: Thanks Joseph. I just to another look at the query and it actually takes 65 seconds on the first run, not 10 seconds, so this is important for me to understand. So my next questions are: Is there any way to tune the OS file system cache? This is very OS system dependent and I don't know the answer for W2K (I am a Unix/Linux guy ;). However it seems that the basic rule in almost any OS is: Use all memory not used by running processes for file caching. Is there any way to flush it or examine what's in it? I don't think you can examine it. On Unix/Linux systems, I use a trick to flush it : I read a file bigger than RAM size. (Unix command: cat big_file /dev/null) Seems like if I wait a while, the long query happens again. Looks like it flushes itself after some time - how often does it flush itself? It is probably flushed because the OS needed some memory to read another file. On Linux, files stay in the cache as long as the system don't need to reclaim memory; least recently accessed memory is then reused. I suppose that W2K has the same behavior. If those long queries are a problem, you should consider adding more RAM to your server. Thanks again, -Bob Hope this helps Joseph Bueno -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SAP DB
* Catia Crepaldi Does anyone knowns SAP DB and can tell me if it's a good database? I have never tried it, but just FYI, SAP DB is going to be MaxDB shortly, here is some more info: URL: http://www.sapdb.org/ URL: http://www.sapdb.org/7.4/sapdb_mysql.htm URL: http://www.mysql.com/press/release_2003_24.html URL: http://www.mysql.com/sapdb/ URL: http://www.mysql.com/sapdb/faq.html URL: http://www.mysql.com/sapdb/timeline.html -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Deleting from multiple tables
I am working on some database tables to handle messages on an intranet. One table looks like this msg_id | subject | message | date there is another table used to track senders and recipients that looks like this msg_id | sender_id | recipient_id | Now what I would like to do is delete any messages that are older than 45 days and the corresponding records in the second table. How can I delete matching records from both tables at the same time? thanks, Luis R. Lebron Project Manager Sigmatech, Inc
More LEFT JOIN Headaches
Please help -- by brain is fried... I have three tables: groups, members, and a link table called group_members. There is a record in group_members for each member that belongs to a given group. A member may belong to several group. I want to get a results set that shows all members where a given member, call him Jim, also belongs. Thus if Jim is a member of groups 1 and 3, Mary is a member of 3, and Bob belongs to 2 and 3, I want a results like the one below. I know how to get to this using an intermediate temporary table, but I'd like to vaid that if there's a way. The queries I have tried either give me all members in all groups, or only records where Jim is a member. My closest query follows, then a sketch of my tables with a results set at the end. Can someone please tell me where I an blowing it? Thanks - Dan Hansen SELECT groups.name AS group_name, members.username AS username FROM group_members LEFT JOIN groups ON groups.id = group_members.group_id AND group_members.member_id = 1 (This is Jim, it will be replaced by a variable in PHP code) LEFT JOIN members ON members.id = group_members.member_id ORDER BY groups.name === members --- id name --- 1Jim 2Mary 3Bob === groups --- id name --- 1Red 2White 3Blue === group_members --- id member_id group_id --- 11 1 21 3 32 3 43 2 53 3 DESIRED RESULT SET group_name username Red Jim Blue Jim Blue Mary Blue Bob DESIRED RESULT SET group_name username Red Jim Blue Jim Blue Mary Blue Bob (SHOULD NOT GET THIS ONE) WhiteBob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql Performance Question
Rainer, To find out if there is any query that is taking too long to run, enable the log-slow-queries option and set long_query_time to some initial value like 5 or 10 (it's in seconds). Then check your slow queries log file and try to understand why those queries are taking too much time to run. After you optimize those queries, lower the long_query_time value and keep looking at the slow queries log, until you get almost no slow queries or your long_query_time is near 1 (or zero!) If you didn't understand, just let me know :) Regards, Sergio. On Wed, 22 Oct 2003, Rainer Sip wrote: Thanks for your input. I'll try give my.cnf a try. I'm not technical at all and am unable to describe the queries myself. Attached some output from the server (Sorry for the long post): SHOW STATUS - +--++ | Variable_name| Value | +--++ | Aborted_clients | 106| | Aborted_connects | 1 | | Bytes_received | 1386152010 | | Bytes_sent | 1017147314 | | Com_alter_table | 0 | | Com_analyze | 0 | | Com_backup_table | 0 | | Com_change_db| 404084 | | Com_delete | 954139 | | Com_flush| 0 | | Com_insert | 38972 | | Com_insert_select| 0 | | Com_kill | 0 | | Com_load | 0 | | Com_select | 5274720| | Com_set_option | 0 | | Com_show_binlog_events | 0 | | Com_show_binlogs | 0 | | Com_show_create | 0 | | Com_show_databases | 4 | | Com_show_fields | 2 | | Com_show_grants | 0 | | Com_show_keys| 0 | | Com_show_logs| 0 | | Com_show_master_status | 0 | | Com_show_new_master | 0 | | Com_show_open_tables | 0 | | Com_show_processlist | 8 | | Com_show_slave_hosts | 0 | | Com_show_slave_status| 0 | | Com_show_status | 9 | | Com_show_innodb_status | 0 | | Com_show_tables | 56 | | Com_show_variables | 20 | | Com_slave_start | 0 | | Com_slave_stop | 0 | | Com_truncate | 0 | | Com_unlock_tables| 0 | | Com_update | 1412924| | Connections | 403875 | | Created_tmp_disk_tables | 688| | Created_tmp_tables | 105261 | | Created_tmp_files| 0 | | Delayed_insert_threads | 0 | | Delayed_writes | 0 | | Delayed_errors | 0 | | Flush_commands | 1 | | Handler_commit | 0 | | Handler_delete | 27226 | | Handler_read_first | 611592 | | Handler_read_key | 2133888023 | | Handler_read_next| 2198593130 | | Handler_read_prev| 278| | Handler_read_rnd | 34278228 | | Handler_read_rnd_next| 249766468 | | Handler_rollback | 0 | | Handler_update | 1007151| | Handler_write| 184271195 | | Key_blocks_used | 106432 | | Key_read_requests| 3936331483 | | Key_reads| 100865 | | Key_write_requests | 1897202| | Key_writes | 168| | Max_used_connections | 234| | Not_flushed_key_blocks | 0 | | Not_flushed_delayed_rows | 0 | | Open_tables | 1024 | | Open_files | 1055 | | Open_streams | 0 | | Opened_tables| 6601 | | Questions| 17691302 | | Qcache_queries_in_cache | 1564 | | Qcache_inserts | 5109816| | Qcache_hits | 9173397| | Qcache_lowmem_prunes | 89550 | | Qcache_not_cached| 183073 | | Qcache_free_memory | 4490312| | Qcache_free_blocks | 1239 | | Qcache_total_blocks | 4393 | | Rpl_status | NULL | | Select_full_join | 712| | Select_full_range_join | 119| | Select_range | 24655 | | Select_range_check | 0 | | Select_scan | 407032 | | Slave_open_temp_tables | 0 | | Slave_running| OFF| | Slow_launch_threads | 0 | | Slow_queries | 2892 | | Sort_merge_passes| 0 | | Sort_range | 244321 | | Sort_rows| 2279797622 | | Sort_scan| 151217 | | Table_locks_immediate| 7551643
Table design help
I have a small table that contains company contact information: Table: Contacts Key Name Address Phone Email Website etc.. What I need to do is associate the type of industry the business servers from a list of about 40 industries... My initial thinking was to create another table that contains all of the industries along with unique key for each. So table would look like Table: Industries Industry Key The problem is, some businesses server many different industries. So my question is, can I add another column to the contacts table that would associate many different industry key's to the contact? What is the best way to design this database to talk with PHP? Thanks, Casey DeBerry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Log file format?
Egor Egorov wrote: It's a process id. You can see it in the output of SHOW PROCESSLIST. So, each Id represents a thread, and each thread represents a connection? So an entry like this in the query log: 031029 9:56:43 6 Quit :: would represent a connection returned to the pool through an explicit close or timeout? (in this case pool being a DataSource managed by Tomcat) -- Hassan Schroeder - [EMAIL PROTECTED] Webtuitive Design === (+1) 408-938-0567 === http://webtuitive.com dream. code. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Package Install for Mac OS X 10.3 (Panther)
I used the MySQL Complete package from Server Logistics and it installed fine on my clean Panther install. Also, the MySQL Preference Pane and CocoaSQL work fine as well. Thanks, -Sam On Tuesday, October 28, 2003, at 11:08 PM, Sam Carpenter wrote: My bad, I should have been more precise. I am not upgrading - I have done a clean install. So, now I need to re-install MySQL. -Thanks On Tuesday, October 28, 2003, at 11:00 PM, Kim Kohen wrote: I am upgrading to Panther -- 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: Change of root password
I can't get through I am doing as follow. Is it right or I am doing something wrong? [EMAIL PROTECTED] parminder]# /usr/local/mysql/share/mysql/mysql.server stop Killing mysqld with pid 31796 Wait for mysqld to exit\c done I can't use mysqld [EMAIL PROTECTED] parminder]# /usr/local/mysql/libexec/mysqld --skip-grant-tables Fatal error: Please read Security section of the manual to find out how to run mysqld as root! 031029 18:10:34 Aborting 031029 18:10:34 /usr/local/mysql/libexec/mysqld: Shutdown Complete But when i use mysqld_safe session never finishes. Any way mysql server is started. I have to close the console and start it again. [EMAIL PROTECTED] parminder]# /usr/local/mysql/bin/mysqld_safe --skip-grant-tables Starting mysqld daemon with databases from /usr/local/mysql/var I can't find what is going wrong On Tue, 2003-10-28 at 18:51, Barry Byrne wrote: -Original Message- From: Parminder Singh Chauhan [mailto:[EMAIL PROTECTED] After long struggle I (A newbee to linux) was able to setup mysql4.0.16 and I don't know what I have done, I set the root password which I don't know. Now I can't access the mysql. Is there anyway of changing root password.? and how? Start mysqld with the --skip-grant-tables option. Change the password and then run 'mysqladmin flush-privileges' to make the change take effect. - Barry -- Parminder Singh Chauhan
RE: Table design help
you are going to want a 'buster' table... also known as a many-to-many table so you have: contacts Contact_Key Industries - Industry_Key Contact_Industry_assoc -- Contact_Key Industry_Key -Original Message- From: DeBerry, Casey [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 29, 2003 1:09 PM To: '[EMAIL PROTECTED]' Subject: Table design help I have a small table that contains company contact information: Table: Contacts Key Name Address Phone Email Website etc.. What I need to do is associate the type of industry the business servers from a list of about 40 industries... My initial thinking was to create another table that contains all of the industries along with unique key for each. So table would look like Table: Industries Industry Key The problem is, some businesses server many different industries. So my question is, can I add another column to the contacts table that would associate many different industry key's to the contact? What is the best way to design this database to talk with PHP? Thanks, Casey DeBerry -- 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: Table design help
Casey, I would consider, on the most basic level, a method like this: Contacts (all the company contact info, etc) using primary key contact_id - IndustrialRelationships table - industry_id primary key on Industries table (all the different type of industries) So, in the IndustrialRelationships table, it's simply two columns... the contact_id for the contact, and the industry_id for the industry. Thus, you can put an arbitrary number of industries associated to a single contact. Then, later on, you can use this data to determine how many contacts you have for a given industry. To get the data back out, you could use a JOIN perhaps. This is very simple, but it should get you pointed in the right direction. I might suggest getting a book about or reading some pages regarding data modeling for more information on normalization. -- R. Deuce I have a small table that contains company contact information: Table: Contacts Key Name Address Phone Email Website etc.. What I need to do is associate the type of industry the business servers from a list of about 40 industries... My initial thinking was to create another table that contains all of the industries along with unique key for each. So table would look like Table: Industries Industry Key The problem is, some businesses server many different industries. So my question is, can I add another column to the contacts table that would associate many different industry key's to the contact? What is the best way to design this database to talk with PHP? Thanks, Casey DeBerry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Change of root password
to help the mysqld_safe 'finish' you'll need to append an '' to your command, which will have the process start in the background and give you your prompt back. /usr/local/mysql/bin/mysqld_safe --skip-grant-tables now you can hit the enter key, and you'll get your prompt back and the server will still be running. although i'd heed the warnings about runnign your server as root. hth Jeff Parminder Singh Chauhan To: MySql [EMAIL PROTECTED] [EMAIL PROTECTED]cc: t.com Subject: RE: Change of root password 10/29/2003 01:30 PM I can't get through I am doing as follow. Is it right or I am doing something wrong? [EMAIL PROTECTED] parminder]# /usr/local/mysql/share/mysql/mysql.server stop Killing mysqld with pid 31796 Wait for mysqld to exit\c done I can't use mysqld [EMAIL PROTECTED] parminder]# /usr/local/mysql/libexec/mysqld --skip-grant-tables Fatal error: Please read Security section of the manual to find out how to run mysqld as root! 031029 18:10:34 Aborting 031029 18:10:34 /usr/local/mysql/libexec/mysqld: Shutdown Complete But when i use mysqld_safe session never finishes. Any way mysql server is started. I have to close the console and start it again. [EMAIL PROTECTED] parminder]# /usr/local/mysql/bin/mysqld_safe --skip-grant-tables Starting mysqld daemon with databases from /usr/local/mysql/var I can't find what is going wrong On Tue, 2003-10-28 at 18:51, Barry Byrne wrote: -Original Message- From: Parminder Singh Chauhan [mailto:[EMAIL PROTECTED] After long struggle I (A newbee to linux) was able to setup mysql4.0.16 and I don't know what I have done, I set the root password which I don't know. Now I can't access the mysql. Is there anyway of changing root password.? and how? Start mysqld with the --skip-grant-tables option. Change the password and then run 'mysqladmin flush-privileges' to make the change take effect. - Barry -- Parminder Singh Chauhan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Got error 22 from table handler
I have an app that (rapidly) adds items (via individual insert statements as items are parsed in from a file) into the database. I seem to be randomly getting got error 22 from table handler on what appears to be perfectly valid INSERT queries. perror 22 gave: Error code 22: Invalid argument I'm using multithreading in my app, but I make sure to LOCK/UNLOCK my tables before and after use. What would cause this? Adam Clauss [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: More LEFT JOIN Headaches
I don't think you really want a LEFT JOIN. (See http://www.mysql.com/doc/en/JOIN.html). What you do need, I believe, is a join of group_members with itself to get member IDs on one side whose group ID matches Jim's group ID on the other side. Try something like this: mysql SELECT groups.name AS group_name, members.name AS username - FROM group_members AS g1, group_members AS g2, groups, members - WHERE g1.member_id=1 AND g2.group_id=g1.group_id - AND members.id=g2.member_id AND groups.id=g2.group_id - ORDER BY group_name; ++--+ | group_name | username | ++--+ | Blue | Jim | | Blue | Mary | | Blue | Bob | | Red| Jim | ++--+ 4 rows in set (0.00 sec) I won't promise that's the best way. Michael Dan Hansen wrote: Please help -- by brain is fried... I have three tables: groups, members, and a link table called group_members. There is a record in group_members for each member that belongs to a given group. A member may belong to several group. I want to get a results set that shows all members where a given member, call him Jim, also belongs. Thus if Jim is a member of groups 1 and 3, Mary is a member of 3, and Bob belongs to 2 and 3, I want a results like the one below. I know how to get to this using an intermediate temporary table, but I'd like to vaid that if there's a way. The queries I have tried either give me all members in all groups, or only records where Jim is a member. My closest query follows, then a sketch of my tables with a results set at the end. Can someone please tell me where I an blowing it? Thanks - Dan Hansen SELECT groups.name AS group_name, members.username AS username FROM group_members LEFT JOIN groups ON groups.id = group_members.group_id AND group_members.member_id = 1 (This is Jim, it will be replaced by a variable in PHP code) LEFT JOIN members ON members.id = group_members.member_id ORDER BY groups.name === members --- id name --- 1Jim 2Mary 3Bob === groups --- id name --- 1Red 2White 3Blue === group_members --- id member_id group_id --- 11 1 21 3 32 3 43 2 53 3 DESIRED RESULT SET group_name username Red Jim Blue Jim Blue Mary Blue Bob DESIRED RESULT SET group_name username Red Jim Blue Jim Blue Mary Blue Bob (SHOULD NOT GET THIS ONE) WhiteBob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: after upgrade to 4.0.16
I think that mysql_connect is not defined in the MySqlClient lib. I try to use it in a C proram but I get a link error. I have to use mysql_real_connect. Try to install compat library. Santino At 9:35 -0600 29-10-2003, John Nichel wrote: Rob Wierenga wrote: Thanks for the new MySQL server. My MySQLcc works fine now. But.. My PHP cann't connect anymore to the server. The lib is not there i think. Because i get an error mysql_connect() doesnot exist. What to do ?? snip If php gives you the error that mysql_connect is a call to an undefined function, it's not a MySQL error. It means your version of php was not compiled with MySQL support. What OS? What version of PHP? Source, binary, RPM install of PHP? -- By-Tor.com It's all about the Rush http://www.by-tor.com -- 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: after upgrade to 4.0.16
Rob Wierenga wrote: My PHP cann't connect anymore to the server. The lib is not there i think. Because i get an error mysql_connect() doesnot exist. PHP is linked to a certain build of the mysql client library. If you exchanged the mysql server and all of the client stuff, you'll need to rebuild PHP, I suppose. What to do ?? Reinstall the mysql client stuff that came with your linux distribution. You can have several versions of the client libraries on your host. You might still run into problems with the new password encoding in MySQL 4.1 if you try to access it with older clients. Live is tough, I know ;) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Change of root password
Thanks
Now I am able to get back the cursor
but I can't change the password
[EMAIL PROTECTED] parminder]# /usr/local/mysql/bin/mysql -u root
mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 4.0.16-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql SET PASSWORD FOR [EMAIL PROTECTED]('353715');
ERROR 1132: You must have privileges to update tables in the
mysql database to be able to change passwords for others
mysql
On Wed, 2003-10-29 at 18:49, [EMAIL PROTECTED] wrote:
to help the mysqld_safe 'finish' you'll need to append an '' to your
command, which will have the process start in the background and give you
your prompt back.
/usr/local/mysql/bin/mysqld_safe --skip-grant-tables
now you can hit the enter key, and you'll get your prompt back and
the server will still be running.
although i'd heed the warnings about runnign your server as root.
hth
Jeff
Parminder Singh
Chauhan To: MySql [EMAIL PROTECTED]
[EMAIL PROTECTED]cc:
t.com Subject: RE: Change of root password
10/29/2003 01:30
PM
I can't get through
I am doing as follow. Is it right or I am doing something wrong?
[EMAIL PROTECTED]
parminder]#
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Several tables or huge single table?
Dear all I have a table with more than 100'000 records for a single product. At the end, I will have tables for 26 prodcuts. There is basically no dependence on other tables. Somewhere I read that one should avoid a large number of tables in a database. Is it more recommendable to have a single huge table for all products togehter or is it better to keep 26 distinct tables? Thanks for a hint, Wolfgang Bauer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Data modelling tool
Hi Group, Sorry i am posting the same message asking about data modelling tool. I am looking for the best data modelling tool for the mysql 4.0 dbms. Specifically i am looking for the generalization/specialization relationship support within the tool. Thanks in advance. Hardik - Do you Yahoo!? Exclusive Video Premiere - Britney Spears
RE: DB not restoring from dump file
Matt W mailto:[EMAIL PROTECTED] on Monday, October 27, 2003 5:29 PM said: As to why mysqldump would create a dump file with a syntax error in it, that's because *you* (or the application creator) used a reserved word for a column/index name (bad idea) and mysqldump I always try to be careful about this and I even compared all the column names in my db to the reserved word list on the MySQL site and didn't find any matches. Chris. -- Don't like reformatting your Outlook replies? Now there's relief! http://home.in.tum.de/~jain/software/outlook-quotefix/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: DB not restoring from dump file
On Wed, 2003-10-29 at 14:18, Chris W. Parker wrote: Matt W mailto:[EMAIL PROTECTED] on Monday, October 27, 2003 5:29 PM said: As to why mysqldump would create a dump file with a syntax error in it, that's because *you* (or the application creator) used a reserved word for a column/index name (bad idea) and mysqldump I always try to be careful about this and I even compared all the column names in my db to the reserved word list on the MySQL site and didn't find any matches. It may be best to always use the --quote-names option to mysqldump, which would avoid any problems you might encounter with reserved words being used in column and table names. -- Andy Bakun: when uselessness just isn't enough [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: DB not restoring from dump file
Andy Bakun mailto:[EMAIL PROTECTED] on Wednesday, October 29, 2003 12:27 PM said: It may be best to always use the --quote-names option to mysqldump, which would avoid any problems you might encounter with reserved words being used in column and table names. Already done. :) Chris. -- Don't like reformatting your Outlook replies? Now there's relief! http://home.in.tum.de/~jain/software/outlook-quotefix/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
More LEFT JOIN Headaches
Dan, You don't need a LEFT JOIN here. Left join lets you keep all of the rows in one table in a join, even when there are no matching rows in the second table. You do have to use the group_members table twice, once to find all the groups to which Jim belongs, and again to find all of the members of those groups. Here's a select statement that does the trick. SELECT G.NAME, M.name FROM group_members A, group_members B, groups G, members M where A.member_id = 1 and A.group_id = B.group_id and G.id = B.group_id and M.id = B.member_id ORDER BY groups.name; However, some comments on left joins... Your proposed example has no WHERE clause, so ALL rows of group_members will participate in the join. The group_members.member_id in the ON clause only limits the rows of groups which will be used in the join, rather than perhaps contributing nulls. (That's why you see a row for White.) A brief description of left joins: SELECT * FROM table1 LEFT JOIN table2 ON join_condition; returns: (1) all rows returned by: SELECT * from table1, table2 where join_condition; (2) for each row in table1 that does NOT participate in (1), a row from table1 with the table2 columns filled in with nulls Then, you can add a WHERE clause to further restrict the returned rows; in particular, you can use table2.column IS NULL to get rows from table1 which do NOT match table2. Note that the join_condition in the ON clause does not restrict what rows from table1 participate in the result--without a WHERE, you get at least one row in the result for each row of table1. The ON clause DOES affect what rows of table2 get joined to rows of table1. HTH, Bill Date: Wed, 29 Oct 2003 11:57:54 -0600 To: [EMAIL PROTECTED] From: Dan Hansen [EMAIL PROTECTED] Subject: More LEFT JOIN Headaches Please help -- by brain is fried... I have three tables: groups, members, and a link table called group_members. There is a record in group_members for each member that belongs to a given group. A member may belong to several group. I want to get a results set that shows all members where a given member, call him Jim, also belongs. Thus if Jim is a member of groups 1 and 3, Mary is a member of 3, and Bob belongs to 2 and 3, I want a results like the one below. I know how to get to this using an intermediate temporary table, but I'd like to vaid that if there's a way. The queries I have tried either give me all members in all groups, or only records where Jim is a member. My closest query follows, then a sketch of my tables with a results set at the end. Can someone please tell me where I an blowing it? Thanks - Dan Hansen SELECT groups.name AS group_name, members.username AS username FROM group_members LEFT JOIN groups ON groups.id = group_members.group_id AND group_members.member_id = 1 (This is Jim, it will be replaced by a variable in PHP code) LEFT JOIN members ON members.id = group_members.member_id ORDER BY groups.name === members --- id name --- 1Jim 2Mary 3Bob === groups --- id name --- 1Red 2White 3Blue === group_members --- id member_id group_id --- 11 1 21 3 32 3 43 2 53 3 DESIRED RESULT SET group_name username Red Jim Blue Jim Blue Mary Blue Bob DESIRED RESULT SET group_name username Red Jim Blue Jim Blue Mary Blue Bob (SHOULD NOT GET THIS ONE) WhiteBob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
show databases problem
Dear All, I've tried searching the web to save me having ask for help. I'm trying to remove my user's ability to use SHOW DATABASES. In the past, I have added skip-show-database to /etc/my.cnf. However, on this installation, any user can see all the database names! Despite my efforts to stop them being able to. I am using FreeBSD 5.1. I am using mysql version 4.0.15a. So far, the only think I can think of is that I migrated from a (freebsd) package of mysql...I think version 4.0.10? I copied the same mysql database across to the new server. Can anyone enlighten me on how to restrict users from seeing databases other than their own? Even if they can't see any...that's fine too! Dickon...
how to debug mysql command hang?
The database is working fine, by the mysql command hangs indefinitely. Any thoughts how I can determine the cause? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Several tables or huge single table?
Hi Wolfgang, I'd say it somewhat depends on what data you have and what you really are going to use the records for. If you never need to retrieve data for more than one product at a time and there are significant differences in the data set between the products then maybe you might split it. Otherways you will complicate things a lot by not having them in the same table. And is the data set (not) the same for all the products ? And will you get more products in the future (ending up adding tables) ? I would anyway go for the one table solution if there isn't any significant reason to split it on 26 separate tables. And then of cause the data normalisation issue is another thing. Jan -Original Message- From: W. Bauer [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 29, 2003 21:41 To: [EMAIL PROTECTED] Subject: Several tables or huge single table? Dear all I have a table with more than 100'000 records for a single product. At the end, I will have tables for 26 prodcuts. There is basically no dependence on other tables. Somewhere I read that one should avoid a large number of tables in a database. Is it more recommendable to have a single huge table for all products togehter or is it better to keep 26 distinct tables? Thanks for a hint, Wolfgang Bauer -- 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: show databases problem
In 4.0.2 SHOW DATABASES became a privilege, and if you don't have that privilege then you can see only 'your own' databases. Maybe your privileges were changed during the upgrade? http://www.mysql.com/doc/en/Show_database_info.html Chris -Original Message- From: Dickon Newman [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 29, 2003 1:55 PM To: [EMAIL PROTECTED] Subject: show databases problem Dear All, I've tried searching the web to save me having ask for help. I'm trying to remove my user's ability to use SHOW DATABASES. In the past, I have added skip-show-database to /etc/my.cnf. However, on this installation, any user can see all the database names! Despite my efforts to stop them being able to. I am using FreeBSD 5.1. I am using mysql version 4.0.15a. So far, the only think I can think of is that I migrated from a (freebsd) package of mysql...I think version 4.0.10? I copied the same mysql database across to the new server. Can anyone enlighten me on how to restrict users from seeing databases other than their own? Even if they can't see any...that's fine too! Dickon... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Size of embedded libmysqld library
I'm working on an embedded Linux project on the MIPS platform. We have rather limited space for storing binaries and libs and also limited RAM so I was trying to trim down libmysqld as small as possible. Currently the libmysqld.a is about 8MB in size and this is after an objdump --strip-unneeded on all the object files inside of the .a archive. Does anyone have any specs on how small libmysqld can be made and what kind of memory overhead it would have? I'm also interested in how resistant MySQL is to corrupted databases. This project is on a portable battery powered device and so the machine could crash at any time. Any tips would be greatly appreciated. Josh Green -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Multiuser Programming
I am doing transfer from MS SQL. In MS SQL while I connect as USER1 my table will be create as User1.TableName. This will avoid conlict with other user. Now, how do I do that in Mysql? Or, maybe there is such system in Mysql. Is there any Mysql reference talk about this matter?
RE: Multiuser Programming
I'm not familiar with that function in MS SQL, and you're a bit unclear, but it looks like you might be able to use temporary tables. http://www.mysql.com/doc/en/CREATE_TABLE.html Temporary tables are on a per-connection basis, and are unique per connection (each connection can have a temporary table that won't conflict with others). Temporary tables are dropped as soon as the connection is dropped. Chris -Original Message- From: William IT [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 29, 2003 5:01 PM To: [EMAIL PROTECTED] Subject: Multiuser Programming I am doing transfer from MS SQL. In MS SQL while I connect as USER1 my table will be create as User1.TableName. This will avoid conlict with other user. Now, how do I do that in Mysql? Or, maybe there is such system in Mysql. Is there any Mysql reference talk about this matter? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Multiuser Programming
Chris said: I'm not familiar with that function in MS SQL, and you're a bit unclear, but it looks like you might be able to use temporary tables. http://www.mysql.com/doc/en/CREATE_TABLE.html Temporary tables are on a per-connection basis, and are unique per connection (each connection can have a temporary table that won't conflict with others). Temporary tables are dropped as soon as the connection is dropped. Chris -Original Message- From: William IT [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 29, 2003 5:01 PM To: [EMAIL PROTECTED] Subject: Multiuser Programming I am doing transfer from MS SQL. In MS SQL while I connect as USER1 my table will be create as User1.TableName. This will avoid conlict with other user. Now, how do I do that in Mysql? Or, maybe there is such system in Mysql. Is there any Mysql reference talk about this matter? I thought this list wanted bottom posting? Sorry if I've got it backwards. How does connection pooling figure into this? I would assume a drop table command would be needed since from MySQL's point of view, the connection never gets dropped. William R. Mussatto, Senior Systems Engineer Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Multiuser Programming
-Original Message- From: Chris [mailto:[EMAIL PROTECTED] I'm not familiar with that function in MS SQL, and you're a bit unclear, but it looks like you might be able to use temporary tables. Looks like he's asking if MySQL supports namespaces. Is there anyway to simulate namespaces in mysql? -Original Message- From: William IT [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 29, 2003 5:01 PM To: [EMAIL PROTECTED] Subject: Multiuser Programming I am doing transfer from MS SQL. In MS SQL while I connect as USER1 my table will be create as User1.TableName. This will avoid conlict with other user. Now, how do I do that in Mysql? Or, maybe there is such system in Mysql. Is there any Mysql reference talk about this matter? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MyISAM table corrupting in 4.0.16???
I just upgraded an embedded server application to use 4.0.16 (from 4.0.12) and now I am quite consistently getting a corrupt table. I am now getting Got error 127 from table handler returned as the error. Copying the data directory into a true SQL server and executing a CHECK on the involved tables gives: 1 clients is using or hasn't closed the table properly This sounds like I locked it, but didn't unlock it? But I am positive I did. I use the following procedure: AddItem() LockTable InsertItem UnlockTable Just to double check, I have the a function for LockTable and a function for UnlockTable. These functions keep a counter and increment it/decrement it each time it is called. I verified that prior to running this query (an UPDATE query) that the counter is 0 - which indicates that everytime I locked the table, I unlocked it. Note that this same code did not cause this problem with 4.0.12 (had problems in other places, which are the reasons I just upgraded this). Any ideas? Adam Clauss [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mySQL crash
Dear, We encountered mysql (innodb) crash, and we can't recover the database after the crash. Could you please help use to investigate the problem. Thanks in advance for your help. The following is copied from the mysql error log: 031027 17:16:12 mysqld started 031027 17:16:12 Warning: setrlimit couldn't increase number of open files to more than 1024 (request: 5130) 031027 17:16:12 Warning: Changed limits: max_connections: 1014 table_cache: 64 031027 17:16:13 InnoDB: Database was not shut down normally. InnoDB: Starting recovery from log files... InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 30 3440731580 InnoDB: Doing recovery: scanned up to log sequence number 30 3445974016 InnoDB: Doing recovery: scanned up to log sequence number 30 3449948971 031027 17:16:13 InnoDB: Starting an apply batch of log records to the database... InnoDB: Progress in percents: 0 1 031027 17:16:14 InnoDB: Assertion failure in thread 12292 in file page0page.c line 450 InnoDB: Failing assertion: page_cur_rec_insert(cur2, page_cur_get_rec(cur1), mtr) InnoDB: We intentionally generate a memory trap. InnoDB: Send a detailed bug report to [EMAIL PROTECTED] mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=67108864 read_buffer_size=1044480 031027 17:16:14 mysqld ended Our my.cnf is as below: # Example mysql config file for small systems. # # This is for a system with little memory (= 64M) where MySQL is only used # from time to time and it's important that the mysqld deamon # doesn't use much resources. # # You can copy this file to # /etc/my.cnf to set global options, # mysql-data-dir/my.cnf to set server-specific options (in this # installation this directory is /var/lib/mysql) or # ~/.my.cnf to set user-specific options. # # One can in this file use all long options that the program supports. # If you want to know which options a program support, run the program # with --help option. # The following options will be passed to all MySQL clients [client] #password = your_password #port = 3306 socket = /var/lib/mysql/mysql.sock # Here follows entries for some specific programs # The MySQL server [mysqld] port= 3306 socket = /var/lib/mysql/mysql.sock skip-locking key_buffer = 64M max_allowed_packet = 1M table_cache = 512 sort_buffer_size = 4M net_buffer_length = 2K thread_stack = 64K read_buffer_size = 1M datadir=/mysql_main/data/ max_tmp_tables = 64 thread_cache_size = 5 max_connections = 1024 query_cache_limit = 16M query_cache_size = 8M # Don't listen on a TCP/IP port at all. This can be a security enhancement, # if all processes that need to connect to mysqld run on the same host. # All interaction with mysqld must be made via Unix sockets or named pipes. # Note that using this option without enabling named pipes on Windows # (using the enable-named-pipe option) will render mysqld useless! # #skip-networking server-id = 1 # Uncomment the following if you want to log updates #log-bin # Uncomment the following if you are NOT using BDB tables skip-bdb # Uncomment the following if you are using InnoDB tables innodb_data_home_dir = innodb_data_file_path = /mdr_smpp/data/innodb/ibdata1:4G;/mdr_smpp/data/innodb/ibdata2:4G; /mysql_main/data/innodb/ibdata3:10M:autoextend:max:4G innodb_log_group_home_dir = /mdr_http/data/innodb/log1/ innodb_log_arch_dir = /mdr_http/data/innodb/log1/ # You can set .._buffer_pool_size up to 50 - 80 % # of RAM but beware of setting memory usage too high innodb_buffer_pool_size = 128M innodb_log_files_in_group = 4 innodb_additional_mem_pool_size = 4M # Set .._log_file_size to 25 % of buffer pool size innodb_log_file_size = 32M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 2 innodb_lock_wait_timeout = 80 #innodb_force_recovery=4 [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates [isamchk] key_buffer = 8M sort_buffer_size = 8M [myisamchk] key_buffer = 8M sort_buffer_size = 8M [mysqlhotcopy] interactive-timeout Regards, TS Wong Tong Seng, Wong Senior Software Engineer Radius-ED Sdn Bhd (Head Office - Asia ) Suite 3B-20-3, Level 20, Block 3B, Plaza Sentral, Jalan Stesen Sentral 5, KL Sentral, 50470 Kuala Lumpur Malaysia Tel : +603 2260 3700 Fax : +603 2260 3708 www.radius-ed.com http://www.radius-ed.com/ This message is intended only for the use of the person(s) to whom it is addressed and may contain information that is priviledged or otherwise protected from disclosure. If
bind-address (s) ?
Hi; New subscriber to the list, searched the archives for any thread on this with no joy so sorry if it's a re-post. Can I have 2 bind-addresses in my.cnf ? Changing ports is not an option. Dual honed Sun Box running MySQL 4.x Thnks Scot Needy [EMAIL PROTECTED] 757-664-2217 Yahoo.id scot11us AIM.id scot11us ICQ.id 179544536 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Creating backups
Here's a little how to on using cron and scp to do automated backups of your data: http://www.spaceprogram.com/knowledge/cron_scp.html Only thing you need to add is the a mysqldump line to your cron script before calling the scp. Travis A. Clausen wrote: avenger wrote: but if my table is sooo big.. eg if large than 1 G. so use the mysqldump is more and more slowly speed. I don't have one gig tables, but I do have a few that come in at a few hundred meg. I actually dump them to another machine on my network (100mbit NIC cards) and it usually takes no more than a four or five minutes. Since I do the main dump once a day at 1am, it could run for a half hour and I wouldn't care. For critical data (mainly our accounting database), I do hourly dumps. Those tables are relatively small, so it doesn't take more than 40 or 50 seconds (I've only seen it take that long when things have been pretty busy). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
lock timeout error with MySQL 4.0.16 / InnoDB tables
When testing my application code I note that mysql_query() does not return a non-zero result when a lock timeout occurs -- the error is detected when I try to retrieve the result set and get a NULL value. However, this finding is not consistent; for example in one block of code a mysql_query() (using SELECT COUNT(*) WHERE login_name='joe' LOCK IN SHARE MODE ) will correctly return a non-zero value if I have deliberately locked (FOR UPDATE) the target row previously from another terminal window. However, in the next block of code with a similiar query eg. SELECT COUNT(*) ...LOCK IN SHARE MODE (applied to a different table with the target row deliberately locked with a FOR UPDATE), mysql_query() returns 0 and only when mysql_store_result() returns NULL can I retrieve the lock timeout error code. Why does mysql_query() not consistently return the lock timeout error code? Ken -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MyISAM table corrupting in 4.0.16???
Narrowed it down - it doesn't seem to have anything to do with the table locking. I removed ALL locking/unlocking and it made no difference. Still getting there error. What would cause the 127 error? (perror says: 127 = Record-file is crashed) Adam Clauss [EMAIL PROTECTED] -Original Message- From: Adam Clauss [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 29, 2003 9:21 PM To: [EMAIL PROTECTED] Subject: MyISAM table corrupting in 4.0.16??? I just upgraded an embedded server application to use 4.0.16 (from 4.0.12) and now I am quite consistently getting a corrupt table. I am now getting Got error 127 from table handler returned as the error. Copying the data directory into a true SQL server and executing a CHECK on the involved tables gives: 1 clients is using or hasn't closed the table properly This sounds like I locked it, but didn't unlock it? But I am positive I did. I use the following procedure: AddItem() LockTable InsertItem UnlockTable Just to double check, I have the a function for LockTable and a function for UnlockTable. These functions keep a counter and increment it/decrement it each time it is called. I verified that prior to running this query (an UPDATE query) that the counter is 0 - which indicates that everytime I locked the table, I unlocked it. Note that this same code did not cause this problem with 4.0.12 (had problems in other places, which are the reasons I just upgraded this). Any ideas? Adam Clauss [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
