log-bin not created
I am using mysql version 4.0.12 on a solaris sparc box. My server has stopped creating the log-bin files although it is defined in my.cnf. The directory permissions of the data directory are fine. What is wrong with the setup? Sohail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Benchmark
I have the following setup : Apache 2 + mod_perl 2 mysql 3.23.54 I need to is measure the perfomance of a ticketing system (written in perl)which has web interface (html::mason, apache2) with Mysql as a backend. Users of the ticketing system can only connect to the backend via the web interface and they usually login to the system at the begining of the the day and remain connected untill they knock of. I have setup two test machines, one with Pg and the other with Mysql. Both machine have the same data (sample). I am looking for a benchmark utilty that the simulate a user session. For example, a user login in, displaying a ticket and searching for tickets; all these invlove a user connecting to the a url, like for search, a user needs to open "somehost.domain/path/to/search.html?with=arguments". The utiltly needs to simulate these actions. The following tools currently have so far caught my attention: Apache Jmeter ab I need suggestions for other utilities. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Using a foreign character set in MYSQL
Hello all. I am working with MySQL 4.0. I have a requirement to create a data-driven Web page to display Chinese text from a MySQL table. I'm completely new to this, can someone tell me exactly what I need to do to make this happen? 1. For example, how do I insert the Chinese text from my source (a Word doc) into a MySQL table without corrupting it? When I try copying and pasting it into the mysql client command-line, the data gets trashed. 2. Once it's in, how do I get it back out into my application without corrupting it? I'm using PHP 4.3 for the Web site. 3. If I need to make changes to the data from the command-line client, how can I do it, especially if the query involves using a Chinese-language string? For example, "update langdata set menutitle='SOME_MENU_TITLE_IN_CHINESE' where menutitle='SOME_OLD_MENU_TITLE_IN_CHINESE'" Looked at the online manual but am sorry to say it didn't really help much. I tried starting the server with --character-set=big5 but it didn't seem to make much difference... Thanks! Vikram -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Searching on Two Keys with OR?
Joshua, Joshua Spoerri wrote: On Tue, 5 Aug 2003, Alexander Keremidarski wrote: MySQL will never use any index for small tables. With just few rows using index adds overhead only. Table scan is faster in such cases. This is docummented behaviour. is 100,000 rows small? my simple OR queries take longer than a second. No. It is not! I referred to your test case in your previous email: Oddly, it works, but the following does not: mysql> create temporary table x (y int, z int, q int, index (y, z)); insert into x values (1,2,3), (3,4,5), (5,6,7); explain select * from x where y = 1 or z = 1; mysql> create temporary table x (y int, z int, index (y, z)); insert into x select f1,f2 from myrealtable; alter table x add q int; explain select * from x where y = 1 or z = 1; Query OK, 0 rows affected (0.00 sec) Query OK, 101200 rows affected (1.95 sec) Records: 101200 Duplicates: 0 Warnings: 0 Query OK, 101200 rows affected (1.61 sec) Records: 101200 Duplicates: 0 Warnings: 0 +---+--+---+--+-+--++-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+--+---+--+-+--++-+ | x | ALL | y | NULL |NULL | NULL | 101200 | Using where | +---+--+---+--+-+--++-+ 1 row in set (0.00 sec) Same table: mysql> explain select * from x where y = 1 or z = 1; +---+---+---+--+-+--+---+-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+---+---+--+-+--+---+-+ | x | index | y | y| 10 | NULL | 85971 | where used; Using index | +---+---+---+--+-+--+---+-+ Note that I am using MySQL 3.23.57, 4.0.14 and 4.1.0 Best regards -- Are you MySQL certified? -> http://www.mysql.com/certification For technical support contracts, visit https://order.mysql.com/?ref=msal __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Alexander Keremidarski <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer /_/ /_/\_, /___/\___\_\___/ Sofia, Bulgaria <___/ 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 BUG into MyISAM table (Linux + MySQL 3.23.56)
> Hi,
>
> I don't know if it has anything to do with your problem, I just wanted to
> comment on the use of a timestamp column in a primary key...
>
> * David Bordas
> > +--+--+--+-+-+
> > +
> > > > | Field| Type | Null | Key | Default | Extra
|
> > >
> > +--+--+--+-+-+
> > +
> > > > | Pseudo | char(16) | | PRI | | |
> > > > | Date | timestamp(14)| YES | PRI | NULL| |
> > > > | Numero | smallint(5) unsigned | | PRI | NULL
> > |auto_increment |
> > > > | Type_message | enum('0','1')| | | 0 | |
> > >
> > +--+--+--+-+-+
> > -- --+
>
> The timestamp column will automatically be updated when any other field in
> the row is updated. This kind of "magic" is normally not wanted for
primary
> keys... :)
>
> http://www.mysql.com/doc/en/DATETIME.html >
>
I know that :)
But timestamp is quite great because it only uses 4 Bytes /row where
DateTime will use 8 Bytes / row.
And i'm only doing select / insert / delete so, timestamp is good for me.
David
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
selecting count query (newbie alert)
Hi, This is my first post here so forgive me if its too simple or not right. basically what i do is: SELECT COUNT(*) FROM table1 where cno=3; (and dumping it into a variable $theResult - I am using PHP) and it gives me the result, but now i need to do this in 5 tables... I can of course run 5 count statements but was wondering if I can do the whole thing in one statement? A friend of mine told me to try "union" but when i go to the documentation of union I cant understand much and it says "UNION is implemented in MySQL 4.0.0" I have no idea what version i have. The 5 tables are pretty much the same and have the same "cno" field that i need and are named table1,table2,table3,table4,table5 Can anybody help me please? Thanks in advance. cheers, -Ryan We will slaughter you all! - The Iraqi (Dis)information ministers site http://MrSahaf.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem with data import from text file
When I try to import a text file with new data for an existing table I get the error "1148 - the used command is not allowed with this MySQL version." I have tried with both PHPMyAdmin2.3 and with MySQLFront 2.5. Both these GUI programmes have an "import from text file" command which I have used successfully several times to add entries to this table before - running just this MySQL version. I have structured the data in the text file in just the same format as previously. Both these programmes are connecting to the database successfully now and will display the contents of the existing table data and strcutre of the database etc (despite an earlier connection problem which seems to have been solved spontaneously). But both now deliver exactly this error message (and therefore I assume it is MySQL and not the interface programmes which is doing it). I cannot see that I am doing anything different. I have MySQL 4.0.12 on Windows ME PC with a local Apache server and PHP. Any ideas please? Regards Adrian Greeman PS I sent a long message about this and a connection problem yesterday which was not succint enough. Apologies -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: reindexing
You are referring to fragmentation. In this case I'm pretty sure it's just a matter of updating the indexes so that MySQL makes the correct assumptions about the distribution of data. MySQL assumes data values have an equal distribution in a database, updating the indexes will force MySQL to update it's assumptions and thus do a better job of optimizing searches. On Wednesday, August 6, 2003, at 12:17 PM, Jeff Mathis wrote: I was under the impression that InnoDB tables took care of this for you. You only need to be concerned if you add/delete repeatedly from anywhere but the end rows of the table. I'd like to know if I'm wrong about this. Adam Nelson wrote: I just did a major insert of new data and now all my selects have slowed down. The table is innodb. Is there a way to reindex everything without having to drop anything. Otherwise, I suppose I will have to drop the indexes and remake them. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Jeff Mathis, Ph.D. 505-955-1434 The Prediction Company [EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6http://www.predict.com Santa Fe, NM 87505 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search & Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: transfer InnoDb in correct order ??
Eddie, - Original Message - From: "Eddie" <[EMAIL PROTECTED]> Newsgroups: mailing.database.mysql Sent: Thursday, August 07, 2003 11:06 AM Subject: transfer InnoDb in correct order ?? > Hellu, > > I want to transfer the structure of my InnoDb database to another > mysql server 3.23. > However, I don't seem to get this to work correctly as the tables are > not transfered in the correct order such that I get a bunch a foreign > key constraints. > Even if I use the MysqlPhp admin tool, Mysql Manager, SqlYog, it > doesn't work: they make an export of the db structure but the db > structure, that consists of create table statements, isn't in the > correct order, such that when I use the export script to make the new > database somewhere else, the tables can't be created because of > foreign key constraints. > > How can solve this, without putting the create statements in the > correct order manually ? Isn't there a handy tool that first checks if > other tables should be created, and if so, it does this, before adding > the table itself ?? > > Please some help, because ordering it manually isn't an option at > this moment with so periodically transfers and so many tables :( please upgrade to a recent version of MySQL and add SET FOREIGN_KEY_CHECKS=0; to the start of your dump file(s). > Eddie Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Transactions, foreign keys, and a hot backup tool for MySQL Order MySQL technical support from https://order.mysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: alter date
Is mysql still in business? Their web page is down. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: heading off in mysql?
At 11:16 -0400 8/7/03, [EMAIL PROTECTED] wrote: Hi all, Just a quick question here. May I know how to get a result without heading in Mysql (like "set heading off" in Oracle) ? Thanks and regards, Helen The context of your question is unclear, but if you're talking about issuing clients using the mysql program, you can use the --skip-column-names option when you invoke mysql. As has already been mentioned, mysql --help can be useful for finding out these kinds of things. -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: 1 query lock all others query
For MYISAM tables selects cause table locks while INNODB does row level locking. Now if your select is taking a long time you might want to look at your index schema. If that does not work for you then split up your data. -->-Original Message- -->From: xuefer tinys [mailto:[EMAIL PROTECTED] -->Sent: Thursday, August 07, 2003 10:24 AM -->To: [EMAIL PROTECTED] -->Subject: 1 query lock all others query --> -->when i show processlist -->1 of my queries is "sending data" (SELECT ...) -->while others queries (SELECT/UPDATE) is "locked" -->what's up? --> -->_ -->与联机的朋友进行交流，请使用 MSN Messenger: http://messenger.msn.com/cn --> --> -->-- -->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: unique identifier using mysql_connect
At 11:32 -0700 7/31/03, Grant Cooper wrote: I am opening 4 different databases and was hoping when I do a mysql_query I could just grab the connection id rather than reconnecting everytime I want to query a new database. But what does that have to do with wanting a unique identifier? If you want to switch databases, just invoke mysql_select_db(), or use mysql_query() to issue a "USE db_name" statement. No need to open a new connection for each database. -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 30, 2003 9:08 PM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: unique identifier using mysql_connect At 12:43 -0700 7/30/03, Grant Cooper wrote: Can I get a unique identifier using the mysql_connect command by connecting to the same host, same user but different database during the life time of one script? I currently do a $conn_id = mysql_connect ($host_name, $user_name, $password); Then call a, mysql_select_db ($db_name,$conn_id) But I always get the same identifier. Why is this a problem? This is fairly typical behavior for PHP (which is what it looks like you're using). -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
1 query lock all others query
when i show processlist 1 of my queries is "sending data" (SELECT ...) while others queries (SELECT/UPDATE) is "locked" what's up? _ 与联机的朋友进行交流，请使用 MSN Messenger: http://messenger.msn.com/cn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: 1 query lock all others query
Check you mysqld server veriable thread_concurrency. I'm not sure that this will solve your problem since your problem has changed from a select locking to concurrent selects. -->-Original Message- -->From: xuefer tinys [mailto:[EMAIL PROTECTED] -->Sent: Thursday, August 07, 2003 11:18 AM -->To: [EMAIL PROTECTED]; [EMAIL PROTECTED] -->Subject: RE: 1 query lock all others query --> -->but one guy in #mysql said: -->[quote] -->with myisam tables if an update is running everything else is blocked, -->multiple selects are allowed to run at the same time, selects and non -->interfering inserts can run at the same time -->[/quote] --> -->so i ask in mailinglist -->because i can't get my multiple selects run at the same time. --> -->>From: "Dathan Vance Pattishall" <[EMAIL PROTECTED]> -->>To: "'xuefer tinys'" <[EMAIL PROTECTED]>, <[EMAIL PROTECTED]> -->>Subject: RE: 1 query lock all others query -->>Date: Thu, 7 Aug 2003 10:59:46 -0700 -->> -->>For MYISAM tables selects cause table locks while INNODB does row level -->>locking. -->> -->>Now if your select is taking a long time you might want to look at your -->>index schema. If that does not work for you then split up your data. -->> -->>-->-Original Message- -->>-->From: xuefer tinys [mailto:[EMAIL PROTECTED] -->>-->Sent: Thursday, August 07, 2003 10:24 AM -->>-->To: [EMAIL PROTECTED] -->>-->Subject: 1 query lock all others query -->>--> -->>-->when i show processlist -->>-->1 of my queries is "sending data" (SELECT ...) -->>-->while others queries (SELECT/UPDATE) is "locked" -->>-->what's up? -->>--> -->>-->_ -->>-->与联机的朋友进行交流，请使用 MSN Messenger: -->>http://messenger.msn.com/cn -->>--> -->>--> -->>-->-- -->>-->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] -->> --> -->_ -->享用世界上最大的电子邮件系统― MSN Hotmail。 http://www.hotmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
processlist state = null
Dear list, does anyone know what it means when it says state = NULL doing the "show processlist"? Thanks Bernd = Please note that this e-mail and any files transmitted with it may be privileged, confidential, and protected from disclosure under applicable law. If the reader of this message is not the intended recipient, or an employee or agent responsible for delivering this message to the intended recipient, you are hereby notified that any reading, dissemination, distribution, copying, or other use of this communication or any of its attachments is strictly prohibited. If you have received this communication in error, please notify the sender immediately by replying to this message and deleting this message, any attachments, and all copies and backups from your computer.
RE: MySQL Replication
-->-Original Message- -->From: Ian Neubert [mailto:[EMAIL PROTECTED] -->Sent: Thursday, August 07, 2003 1:00 PM -->To: Dathan Vance Pattishall -->Cc: [EMAIL PROTECTED] -->Subject: RE: MySQL Replication --> -->Off hand do you know any good resources on how to setup a highly -->available -->and load balanced MySQL system? Setting up replication is easy enough, Highly available systems work generally in your design but here are some suggestions based on some things I've been doing. 1) Configure the 2 masters to share the same IP with VRRP. 2) The sub master or fail-over master needs to have the same data and same bin-log so CHANGE MASTER TO can be avoided on the slaves where a auto reconnect can reliably pick up from where they left off. - Suggestions mirror over the network or write the bin logs on a shared NFS device (another point of failure) 3) Software need to monitor the master / sub-master to fail over and keep the primary master down since fail back should be done manually. This is due to order of events such as repair etc -what happens 1st. There are a few steps I'm forgetting but this should work. -->but I -->can't find much data about getting the whole thing to work together so to -->speak (highly available writes). --> -->Thanks! --> -->... -->Ian Neubert -->Director of IS -->TWAcomm.com, Inc. -->http://www.twacomm.com/ --> -->-Original Message- -->From: Dathan Vance Pattishall [mailto:[EMAIL PROTECTED] -->Sent: Thursday, August 07, 2003 12:54 PM -->To: 'Ian Neubert'; [EMAIL PROTECTED] -->Subject: RE: MySQL Replication --> --> -->That configuration doesn't seem to work if I'm reading it right. A slave -->cannot have more then one master unless mysql more then 1 mysqld process -->running on different ports is used. --> -->-->For example, if a person places an order on our site, the update is -->sent -->-->to -->-->the master server. Can that update be replicated to the slaves before -->the -->-->browser is taken to a reciept page (which is queried against the -->slaves)? --> -->Also guaranteeing an event has occurred on a slave and building -->application logic dependent on this necessity is a tough problem to -->solve and you might as well query the master since you need to verify -->the event is there anyway as part of the conditional. --> -->The reason for my last statement, I've notice in 3.23.5x that -->replication lag can occur in many situations: -->- The master is loaded and is not able to stream the binary log file as -->fast. -->- Locks on the slave preventing updates to occur -->- Load on the slave is high -->- Invalid packet errors where the slave needs to reconnect -->- Some sort of error that stops replication --> --> -->With all of the above examples, I would not have application logic -->dependent on slave events. Instead code your applications based on the -->known limitations. --> -->For example a search engine. It does not necessarily need an up to date -->all of the time index of words and matches... --> --> -->-- -->Dathan --> --> --> -->-->-Original Message- -->-->From: Ian Neubert [mailto:[EMAIL PROTECTED] -->-->Sent: Thursday, August 07, 2003 11:27 AM -->-->To: [EMAIL PROTECTED] -->-->Subject: MySQL Replication -->--> -->-->Hello all, -->--> -->-->Can anyone share their experience with replication? In particular I'm -->-->wondering how fast (or how slow?) replication occurs. Could I pull -->off -->-->the -->-->configuration below? -->--> -->--> -->-->http://linux.ianneubert.com/images/mysql_failover.png or -->-->http://linux.ianneubert.com/images/mysql_failover.pdf -->--> -->-->Thanks for your insight! -->--> -->-->... -->-->Ian Neubert -->-->Director of IS -->-->TWAcomm.com, Inc. -->-->http://www.twacomm.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] --> -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: processlist state = null
On Thu, Aug 07, 2003 at 05:42:12PM -0400, Bernd Jagla wrote: > Dear list, > > does anyone know what it means when it says state = NULL doing the "show > processlist"? Usually you see this when the 'command' is sleep. That means the thread is idle. -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! <[EMAIL PROTECTED]> | http://jeremy.zawodny.com/ MySQL 4.0.13: up 6 days, processed 212,707,492 queries (398/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql_info not very informative?
At 14:37 -0700 8/1/03, Ray Kiddy wrote:
I am trying to insert data using the C API.
Particulars:
OS: Mac OS X 10.3 (7A179)
MySQL: MySQL 4.1.0-alpha
table type: tried both MyISAM and InnoDB
I have a table:
mysql> describe testtable;
++-+---+--+-+-+---+
| Field | Type| Collation | Null | Key | Default | Extra |
++-+---+--+-+-+---+
| _PK| int(11) | binary| | PRI | 0 | |
| first | char(3) | latin1_swedish_ci | YES | | NULL| |
| second | char(3) | latin1_swedish_ci | YES | | NULL| |
| third | char(3) | latin1_swedish_ci | YES | | NULL| |
++-+---+--+-+-+---+
4 rows in set (0.00 sec)
Note that the first, second, and third columns have 3 characters of space.
When I do:
printf("insert: %s\n", insert);
int result = mysql_query(one, insert);
printf("info: %s\n", mysql_info(one));
I get:
insert: INSERT INTO testtable (_PK,first,second,third) VALUES
(1,'AAAXXX','BBB','CCC')
info: (null)
insert: INSERT INTO testtable (_PK,first,second,third) VALUES
(2,'DDD','EEE','FFF')
info: (null)
insert: INSERT INTO testtable (_PK,first,second,third) VALUES
(3,'GGG','HHHXXX','III')
info: (null)
Note that the value in the first insert, 'AAAXXX', is too long to
fit. As is 'HHHXXX' in the third insert. And indeed, I see:
mysql> select * from testtable;
+-+---++---+
| _PK | first | second | third |
+-+---++---+
| 1 | AAA | BBB| CCC |
| 2 | DDD | EEE| FFF |
| 3 | GGG | HHH| III |
+-+---++---+
3 rows in set (0.00 sec)
So, why is mysql_info not giving me any information about the data
loss that is going on here? Is that not information that might be of
interest?
http://www.mysql.com/doc/en/mysql_info.html
mysql_info() returns this information only for multiple-row INSERT
statements. Your statements insert single rows.
Is there some other call I have to make that will "prepare" for the
mysql_info call? The doc does not seem to indicate this, but one
never knows.
thanx - ray
--
Paul DuBois, Senior Technical Writer
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
Are you MySQL certified? http://www.mysql.com/certification/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
InnoDB crash - recovery
Greetings all. I have a bit of a problem here, a database i'm administering was somehow corrupted, and i'm unable to recover it in any way. Is there any way at all to recover a corrupt InnoDB database? (I read on innodb.com that it is impossible, but hope it is not) When I run a query from any InnoDB table in the database MySQL crashes with the following stack trace and errors. I'm running a GNU/Linux system and MySQL 4.0.13 from the Debian unstable. Error: trying to access field 4294967295 in rec 030807 13:53:24 InnoDB: Assertion failure in thread 180234 in file rem0rec.c line 111 InnoDB: Failing assertion: 0 ... thd=0x86e3990 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Cannot determine thread, fp=0xbe7fe898, backtrace may not be correct. Stack range sanity check OK, backtrace follows: 0x8102bc3 0x401ad75a 0x82b9a60 0x8230d50 0x822e42c 0x816952f 0x8169c84 0x816bf6a 0x816c2be 0x815e77f 0x8178c60 0x810f8e8 0x8112a15 0x810db3d 0x810d6cc 0x810d059 0x401a7d53 0x4038a3f7 New value of fp=(nil) failed sanity check, terminating stack trace! ... 0x8102bc3 mysql_binlog_send__FP3THDPcUxUs + 1419 0x401ad75a _end + 936375294 0x82b9a60 _tr_flush_block + 640 0x8230d50 page_cur_delete_rec + 5780 0x822e42c page_copy_rec_list_end_to_created_page + 392 0x816952f yyparse + 3855 0x8169c84 yylex + 1572 0x816bf6a opt_search_plan_for_table + 742 0x816c2be opt_search_plan_for_table + 1594 0x815e77f row_upd_clust_step + 431 0x8178c60 btr_compress + 3852 0x810f8e8 srv_master_thread + 172 0x8112a15 innobase_start_or_create_for_mysql + 1297 0x810db3d srv_sprintf_innodb_monitor + 425 0x810d6cc srv_suspend_mysql_thread + 1372 0x810d059 srv_table_reserve_slot_for_mysql + 473 0x401a7d53 _end + 936352247 0x4038a3f7 _end + 938328219 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
connection
I just installed mysql 4.0 .There were many problems with upgrading from 3.23 to 4.0 so I deleted 3.23 and made a fresh start with 4.0. Everything works fine now but the only problem to connect to the server is via the /usr/local/mysql/bin directory. Is there a way to connect if I am root. thanks for your help. -aaldrik __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Mysql processlist sleep time
The PHP one is indeed used less, probably by about 10% of the users while it's being tested. I was simply wondering if the idle timeouts were possibly responsible for the CPU usage problems, and I thought (rightly or wrongly?), that setting the 'xxx_timeout' options would close those persistent connections after the set number of seconds. It's just so bizarre that the mysqld program eats up all the available CPU most of the time, inevitably almost grinding things to a halt. I've searched high and low for a solution, asking advice in lots of places, tweaking loads of things here and there, and nothing seems to make any difference whatsoever. I appreciate that Windows, MySQL and PHP is not really the combination of choice though! :-) Many thanks for your reply. Gary -Original Message- From: Jeremy Zawodny [mailto:[EMAIL PROTECTED] Sent: 07 August 2003 22:55 To: Gary Broughton Cc: [EMAIL PROTECTED] Subject: Re: Mysql processlist sleep time On Thu, Aug 07, 2003 at 07:54:24PM +0100, Gary Broughton wrote: > Hi all > > I continue to have problems with the CPU usage with MySQL and PHP > under IIS 5 (Win2000). I recently rewrote our messageboards in PHP > (from ASP). I now have both online separately, and if I look at the > processlist, the times on the ASP version rarely hit double figures, > but those on the PHP version often reach several hundred (wait and > inactivity timeouts are set to 300 - I thought this would stop it?!). I'm not sure what the problem is. From your description, it sounds as if the PHP one is either used less or is more efficient about using connections, since they're idle more often. > I am at a real loss as to why the processes are not being cleared. I > am using a persistent connection at the top of the webpage, and every > MySQL query is ended with a 'mysql_free_result()' statement, including > before any redirects using the 'header' command. Hang on. You're using *persistent* connections, so why would you expect them not to persist? Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! <[EMAIL PROTECTED]> | http://jeremy.zawodny.com/ MySQL 4.0.13: up 6 days, processed 212,516,276 queries (399/sec. avg) -- 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: DB Performance - Celeron vs. P4
scuse the ignorance are u saying high cpu usage is better used on one query or better be freed up for other queries ? ram is always an issue as the queries get buffered > Jonathan Hilgeman wrote: > >>Currently, I have a Celeron 1.2 Ghz server with 512 RAM, and I'm >>considering moving to a P4 2 Ghz with the same amount of RAM. I have a >>few specific tables with several million rows of data, and it takes >>quite a long time to process that data on my current server. Does >>anyone have a good idea of the type of performance increase I'd see if >>I moved to a P4 server? > > It's going to depend of in your queries are disk bound or processor > bound. Check the processor usage when a big query is running (and not > much else is running). > > If the CPU usage is at 80-100% the query is processor bound, so you'll > see some improvement (though I can't tell you how much, other than the > clock rate scaling). > > On the other hand, if the CPU usage is low, you are likely disk bound, > so a faster processor won't help much. It would be better to add RAM, > tune your queries or get faster disks (or all three) in this case. > > --Ware Adams > > -- > 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]
Just testing please ignore
Testing ..Please ignore this email
Re: Dropping primary key/unique key
Hi! On Aug 07, Nils Valentin wrote: > [Warning: LNG TEXT - enjoy ;-)] > > Hi Victoria, > > sorry for the misunderstanding. I just tried to reproduce the sample. While I > have records what I did I did not write down all commands exactly as they > are. > > The problem(s) occured on Redhat 8 or 9 and Mysql server-max 4.0.13 (tar > format) from mysql homepage. I had to reinstall a machine to reproduce the > problem,this is why it took so long. > > > First the original problem > Redhat 9 + Mysql server-max 4.0.13 (tar format from mysql.com homepage) > > create table uksample2 (id int unique not null ,name char(30),tel > char(20))type=innodb; > Query OK, 0 rows affected (0.01 sec) > > mysql> alter table uksample2 add primary key (tel); > Query OK, 0 rows affected (0.04 sec) > Records: 0 Duplicates: 0 Warnings: 0 > > mysql> desc uksample2; > +---+--+--+-+-+---+ > | Field | Type | Null | Key | Default | Extra | > +---+--+--+-+-+---+ > | id| int(11) | | UNI | 0 | | > | name | char(30) | YES | | NULL| | > | tel | char(20) | | PRI | | | > +---+--+--+-+-+---+ > 3 rows in set (0.00 sec) > > mysql> alter table uksample2 drop primary key; > Query OK, 0 rows affected (0.03 sec) > Records: 0 Duplicates: 0 Warnings: 0 > > mysql> desc uksample2; > +---+--+--+-+-+---+ > | Field | Type | Null | Key | Default | Extra | > +---+--+--+-+-+---+ > | id| int(11) | | | 0 | | > | name | char(30) | YES | | NULL| | > | tel | char(20) | | PRI | | | > +---+--+--+-+-+---+ > 3 rows in set (0.00 sec) > > Unique key was dropped. Why not Primary key, why no error message??? Confirmed. Fixed in 4.0.15. Second example is also fixed with the same fix. Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql LOAD DATA INFILE
[snip] It's a little messy but that's the only other way I can think of doing it. Sorry. [/snip] If he has the file locally to himself he can do this via phpMyAdmin through the load text file option. Depending on the version there is always a way to load files ... even to remote servers For instance, if I am looking at the structure of a table there is a link at the bottom of the page that says "Insert data from a textfile into table" which gives you the option to browse for a local file to load into that table -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
4.0.14 standard creating user issues
We just installed mysql 4.0.14 on an xserve. When trying to add users via mascon I'm finding an issue I have not seen in previously releases. When adding a user it seems that the new column ssl_cipher is a required field. I know this is new for v4 but is it supposed to be a required field? What should the default be? I can't modify any user without being notified that it is required. Any ideas? --- Keith Schuster Schuster & Company LLC ph:704-799-2438 fx:704-799-0779 iChat/AIM:FSHSales WWW.FlagShipHosting.com WWW.Schusterandcompany.com WWW.Vsheet.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL Replication
I was trying to design it so that the slaves wouldn't know they had connected to a different master, as they both masters would have the same IP address that gets failed over based on the Linux Virtual Server software and VRRP (like heartbeat from Linux-HA). I'm beginning to think that my configuration is too complex, and would introduce too many places for failure. Off hand do you know any good resources on how to setup a highly available and load balanced MySQL system? Setting up replication is easy enough, but I can't find much data about getting the whole thing to work together so to speak (highly available writes). Thanks! ... Ian Neubert Director of IS TWAcomm.com, Inc. http://www.twacomm.com/ -Original Message- From: Dathan Vance Pattishall [mailto:[EMAIL PROTECTED] Sent: Thursday, August 07, 2003 12:54 PM To: 'Ian Neubert'; [EMAIL PROTECTED] Subject: RE: MySQL Replication That configuration doesn't seem to work if I'm reading it right. A slave cannot have more then one master unless mysql more then 1 mysqld process running on different ports is used. -->For example, if a person places an order on our site, the update is sent -->to -->the master server. Can that update be replicated to the slaves before the -->browser is taken to a reciept page (which is queried against the slaves)? Also guaranteeing an event has occurred on a slave and building application logic dependent on this necessity is a tough problem to solve and you might as well query the master since you need to verify the event is there anyway as part of the conditional. The reason for my last statement, I've notice in 3.23.5x that replication lag can occur in many situations: - The master is loaded and is not able to stream the binary log file as fast. - Locks on the slave preventing updates to occur - Load on the slave is high - Invalid packet errors where the slave needs to reconnect - Some sort of error that stops replication With all of the above examples, I would not have application logic dependent on slave events. Instead code your applications based on the known limitations. For example a search engine. It does not necessarily need an up to date all of the time index of words and matches... -- Dathan -->-Original Message- -->From: Ian Neubert [mailto:[EMAIL PROTECTED] -->Sent: Thursday, August 07, 2003 11:27 AM -->To: [EMAIL PROTECTED] -->Subject: MySQL Replication --> -->Hello all, --> -->Can anyone share their experience with replication? In particular I'm -->wondering how fast (or how slow?) replication occurs. Could I pull off -->the -->configuration below? --> --> -->http://linux.ianneubert.com/images/mysql_failover.png or -->http://linux.ianneubert.com/images/mysql_failover.pdf --> -->Thanks for your insight! --> -->... -->Ian Neubert -->Director of IS -->TWAcomm.com, Inc. -->http://www.twacomm.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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Tracking a delete
> >How can I see if a record was deleted from a database? > You can attempt to SELECT it, and if you get no result, it's not there. > But that doesn't necesarily mean that it was once there and has now been > deleted. If you want to determine that, you'll need to create a log > of record deletions. Which would be an excellent use for triggers. Sadly, that's not something mysql currently supports. Soon, though, hopefully. Maybe. Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with data import from text file solved, I think.
FYI After digging out some documentation ( RFM indeed ) it is all so simple in DBD::mysql ... As of version 3.23.49 LOCAL within LOAD DATA _may_ be disabled in the _client_ library. By adding an option to the DSN-string within the connect statement the function is re-enabled at the client side too . As per http://nl3.php.net/manual/en/function.mysql-connect.php an (lesser documented) option is to be put into the _fifth_ parameter of the mysql_(p)connect-statement. In MySQLcc, while editing a servers properties, you can enable it via the 'MySQL Options' tab. Enjoy, HansH -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: myisamchk Illegal Instruction
Yes, I think you're right on that. Any idea what the cause might be or what I can do to find out? I do have a core file, but I'm at a loss as to what to do with it... Original Message Follows From: Sergei Golubchik <[EMAIL PROTECTED]> Subject: Re: myisamchk Illegal Instruction Date: Fri, 8 Aug 2003 19:25:58 +0200 Hi! On Aug 07, Paul Mahon wrote: > > % bin/myisamchk -t ~/tmp -e BROKE/EventsBROKE > Checking MyISAM file: BROKE/EventsBROKE > Data records: 101333504 Deleted blocks: 86920613 > bin/myisamchk: warning: Table is marked as crashed and last repair failed > - check file-size > - check key delete-chain > - check record delete-chain > - check index reference > - check data record references index: 1 > - check records and index references > Illegal instruction Hmm, "Illegal instruction". It really looks like hardware problem. Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] _ Add photos to your messages with MSN 8. Get 2 months FREE*. http://join.msn.com/?page=features/featuredemail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: error creating database
lol mysqladmin -u root -p create database desenv ?? > Hi, > > I was received a error when I was trying to create a new database on > freebsd 5.1, mysql 4.0.14. The "test" database is working well. > > COMMAND: > mysqladmin create desenv > or > mysql > create database desenv; > > ERROR: > mysqladmin: CREATE DATABASE failed; error: 'Access denied for user: > '@localhost' to database 'desenv'' > > The directory "/usr/local/mysql" and subfiles owner and group are > "mysql" Anybody can help me! > > Thanks! > Paulo Fonseca Jr. > [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: STUMPED: How Can I Pull Related Info Using Subqueries/Joins?
Patrick, You need "outer joins" to do this. Try searching for "outer join sql tutorial" on Google. Hope this helps, Andy > -Original Message- > From: Patrick Crowley [mailto:[EMAIL PROTECTED] > Sent: 07 August 2003 16:51 > To: [EMAIL PROTECTED] > Subject: STUMPED: How Can I Pull Related Info Using Subqueries/Joins? > > > I'm creating a tool to browse a database of movie listings. The browser > pulls up 25 results at a time, and you can page through them using 'Next' > and 'Prev' tools. Pretty basic stuff. > > Here are my tables: > movies > directors > comments > movies_directors > movies_comments > etc... > (primary key is movies.id) > > But here's where I'm stuck: for each film, I need to pull the > movie info in > 'movies', plus any related data from other tables, like this: > > The Lord of the Rings | Peter Jackson | 3 comments > Episode II| George Lucas | 0 comments > Indiana Jones | Steven Spielberg | 15 comments > > I seem to run into problems when I try to join info from all of these > related tables. If there's a match, great. But, if not (like a > movie with no > comments), the movie is excluded from the result set. > > I've tried all sorts of SQL queries to make this work, but > nothing seems to > do the trick. > > SO, HERE'S MY QUESTION, IS THERE ANY WAY TO DO THIS QUERY WITHOUT > SUBQUERIES/MYSQL 4.0? Or would the best approach be to use PHP to > do all the > subquery lookups? > > Thanks for your help! > Patrick > > > -- > 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: Searching on Two Keys with OR?
> "ORing on two different fields" is what I have been asking about :). This is not optimized, and I don't think it is set to be optimized until 5.1 (as per someone else's comment). > Using a composite index was suggested This is bad information. It works for AND, not for OR. You have two workarounds: temp tables and unions. You can have it use one index, though. And you can give it a hint on which index it ought to use if you think you know better than the optimizer. At any rate, this is one of MySQL's deficiencies that many of us have worked around for a long time. --steve- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Mysql processlist sleep time
exactly, NULL is a good thing. It means that php isn't constantly tearing down sessions and starting new ones. The connect process is virtually always the longest step of the query (except the big queries, but the goal is to keep them minimized in an application and use good design, indexes, etc.) > -Original Message- > From: Jeremy Zawodny [mailto:[EMAIL PROTECTED] > Sent: Thursday, August 07, 2003 5:55 PM > To: Gary Broughton > Cc: [EMAIL PROTECTED] > Subject: Re: Mysql processlist sleep time > > > On Thu, Aug 07, 2003 at 07:54:24PM +0100, Gary Broughton wrote: > > Hi all > > > > I continue to have problems with the CPU usage with MySQL and PHP > > under IIS 5 (Win2000). I recently rewrote our messageboards in PHP > > (from ASP). I now have both online separately, and if I look at the > > processlist, the times on the ASP version rarely hit double figures, > > but those on the PHP version often reach several hundred (wait and > > inactivity timeouts are set to 300 - I thought this would stop > > it?!). > > I'm not sure what the problem is. From your description, it sounds as > if the PHP one is either used less or is more efficient about using > connections, since they're idle more often. > > > I am at a real loss as to why the processes are not being cleared. > > I am using a persistent connection at the top of the webpage, and > > every MySQL query is ended with a 'mysql_free_result()' statement, > > including before any redirects using the 'header' command. > > Hang on. You're using *persistent* connections, so why would you > expect them not to persist? > > Jeremy > -- > Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! > <[EMAIL PROTECTED]> | http://jeremy.zawodny.com/ > > MySQL 4.0.13: up 6 days, processed 212,516,276 queries (399/sec. avg) > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql w/ mod_perl
I've noticed that when I reboot a hung server (S1) that was using mod_perl to gain access to a remote mysql server (S2), the mysql threads on S2 persist even after S1 returns to active duty. This can (did) lead to the dreaded "Too many connections" error. I really don't want to hup S2/mysqld if I have a runaway S1. is there a way to test/purge the status of threads either from S1 or from S2? bob. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with data import from text file (part two)
"adrian GREEMAN" <[EMAIL PROTECTED]> wrote:
> I have also read the manual section on this {as I should have
> done] to try and understand it
> and how to enable reading a local file - the discussion section
> seems confusing and confused.
>
> I tried following what others have done and
> modified the ini file with set-variable=local-infile=0
> and with set-variable=local-infile=1 and with
> set-variable=local-infile[=1]
>
> and tried each
>
> I did this directly using Notepad and I did it using
> WinMySQLAdmin's modify ini section.
>
> Same error message as I have already reported on each
> modification/
>
> I obviously need to do something different
Put to the my.ini :
[mysqld]
local-infile
[mysql]
local-infile
and test LOAD DATA LOCAL with mysql command line client. Of course you need to restart
MySQL server after modification of my.ini file.
--
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: MySQL Replication
On Thu, Aug 07, 2003 at 03:22:58PM -0700, Ian Neubert wrote: > Good question :) > > I got a message from a person off the list that suggested I use > network disk mirroring or a NAS/SAN/NFS system to handle that. I'm > not sure if the mirroring would be 100% perfect, but the NAS/SAN > solution should as either server would be reading and writing to the > same physical data. > > But, then I have another point of failure. Heh. Right. > I realize that creating the perfect HA system is probably the most > difficult thing to do, and doesn't come cheaply either. However, I'm > going to think it through and try anyway :) Well, what you end up finding is that eliminating all points of failure it very, very, very difficult (and expensive). But you can try to architect things so that they're still affordable and provide minimal downtime in the event of a failure. > I've read your presentations on your website and have used that info > for my plan here, but its a little difficult to get details from > just the slides (as you even mentioned on your site) :) Yeah. And there are no upcoming tour dates. :-) > Do you bother with multi-masters? Sometimes. It's up to each group to think about the tradeoffs of multi-master vs. master/slave with a switch-over plan. > How do you ensure redundancy on the write/master server? One thing you can do is have a "backup master" that slaves from the master but doesn't get other work to do. If it has hardware as beefy as the master, then switching isn't *too* painful. Many of our groups are using that model today. But others do not. If you want to go into a lot more detail off-list, let me know. We might be able to arrange something... Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! <[EMAIL PROTECTED]> | http://jeremy.zawodny.com/ MySQL 4.0.13: up 6 days, processed 213,085,838 queries (398/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: error creating database
"Paulo Fonseca Jr." <[EMAIL PROTECTED]> wrote: > > I was received a error when I was trying to create a new database on freebsd > 5.1, mysql 4.0.14. The "test" database is working well. > > COMMAND: > mysqladmin create desenv > or > mysql > create database desenv; > > ERROR: > mysqladmin: CREATE DATABASE failed; error: 'Access denied for user: > '@localhost' to database 'desenv'' > > The directory "/usr/local/mysql" and subfiles owner and group are "mysql" > Anybody can help me! Anonymous user doesn't have premissions to create database. Connect as a root or any other user that have global CREATE privilege. -- 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: Redundant records in DataBase
Joseph Maxwell <[EMAIL PROTECTED]> wrote: > > I am uploading records from a user data entered log file with > mysqlimport on a periodic basis. I would like to prevent the duplication > of records to keep the system non-redundant. How can I best achieve > this? Create UNIQUE key on those columns that should contain non-repeatable values and use mysqlimport with --ignore option. -- 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: STUMPED: How Can I Pull Related Info Using Subqueries/Joins?
> Sounds as if your table is not normalized for that kind of query. If > there are multiple directors id'd by multiple movies you could left > outer join the movie.id with the director.id > > Make sense? Actually, it's normalized beyond that. Sometimes, movies have more than one director, so the association between movies and directors needs to be in its own table, instead of the movies table. (Otherwise, you're limited to some fixed number of directors per film.) Directors may not be the best example. Think about producers, cast, etc. Let's say I need to pull all producers for a movie. For a movie that has three producers (aka three matches with a LEFT OUTER JOIN), I'll get three rows -- all with the same movie, but different producers. Jurassic Park | Kathleen Kennedy | 35 comments Jurassic Park | Gerald R, Molen | 35 comments Jurassic Park | Lata Ryan| 35 comments How can I list all of these producers within a single row? Jurassic Park | Kathleen Kennedy, Gerald R, Molen, Lata Ryan | 35 comments Best, Patrick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MYSQL Scalability on SMPs
On Fri, Aug 01, 2003 at 03:31:02PM +0100, Khaled D Elmeleegy wrote: > > I am studying the scalability of MYSQL on SMPs on Linux. I am > wondering if any one has performed scalability studies. If so, I > would be interested in a pointer to the results; if not, I am > curious if there is interest in MYSQL's scalability. Pointers to > benchmarks used to study MYSQL would also be appreciated. Another > thing I was wondering is if anyone had experience or recommendations > in choosing the server parameters of MYSQL to tune for SMP > performance. MyISAM performance is limited right now by a global lock in the key cache. However, I believe there is work going on to fix that in the 4.1 tree. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! <[EMAIL PROTECTED]> | http://jeremy.zawodny.com/ MySQL 4.0.13: up 6 days, processed 207,503,352 queries (399/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Hardware recommendation
On Fri, Aug 08, 2003 at 04:30:21PM -0500, Jackson Miller wrote: > I am going to have to put MySQL on it's own box, and I am wondering what makes > the biggest difference with hardware. > > I am hoping to set max_connections to 1000 at least (and I will probably use > at least 500 several times a day). > > There are a few tables that are almost constantly running inserts, updates, > and selects (they are InnoDB). These tables have millions of records. > > There are many other tables that are used less frequently. > > I mostly need the inserts, selects, and updates to be FAST with a high > concurrency. > > Will I see the biggest difference by: > 1) maxing out the ram > 2) faster processors > 3) faster drives > 4) more drives > 5) more processors > > Also, I am curious if RAID or replication would be faster in my situation. In my expierience, the priority order tends to be: (1) more drives (2) faster drives (3) memory (4) more cpus (5) faster cpus Because you're likely I/O bound. So you hit the disks first. Then memory, which can be used to cache frequently used data. Last you look at the CPU(s). Today's CPUs are so damned fast, that you're often hard pressed to really max 'em out if your queries are well tuned--you just end up waiting on the disks. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! <[EMAIL PROTECTED]> | http://jeremy.zawodny.com/ MySQL 4.0.13: up 7 days, processed 252,540,858 queries (399/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Updating table based upon matching field in second table
* Dan Jones [...] > This leads me to another question. What's the standard way of handling > something like the author of a book when you have a collaboration or an > anthology with multiple authors? Normalizing. > The only way I can think of to handle it is to have a sentinel value > that indicates multiple authors, then have another table which lists the > primary key of the books table and associates it with multiple authors. Right. :) > For example: > > BookIDAuthorID > 1522 > 1539 > 1543 > 2703 > 2794 I would add a counter, to separate between 'primary' and 'additional' authors: 15 22 1 15 39 2 15 43 3 27 03 1 27 94 2 > This would show that book 15 had three authors - 22, 39 and 43 while > book 27 had two authors - 03, 94. Of course, this would vastly > complicate displaying or printing the database. A little, but it's worth it. > It would also make it > difficult for searches for a particular author to find books with > multiple authors, etc. Is there an easier way to design the database > which deals with these issues? Using an extra LEFT JOIN you can check if there are any Authors with a Counter=2: SELECT Authors.*, Books.*, IF(more_authors.AuthorID,'yes','no') AS more_authors FROM Authors,Author_Books,Books LEFT JOIN Author_Books AS more_authors ON more_authors.AuthorID = Authors.AuthorID AND more_authors.BookID = Book.BookID AND more_authors.Counter = 2 WHERE Authors.AuthorID = Author_Books.AuthorID AND Books.BookID = Author_Books.BookID AND Author_Books.Counter = 1 Book.Title like "%summer%"; This would list all books matching "%summer%", including the name of the 'primary author' and a yes/no indication if there are more authors for this book. When listing books for a specific author, you distinguish between 'primary author' and 'co-author' by checking if Counter=1: SELECT Author.*,Book.*, IF(Counter=1,'author','co-author') AS Role FROM Authors,Author_Books,Books WHERE ... HTH, -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Database formation ( . )I DONT KNOW( . )
[snip] Can any 1 tell me whats the up level commands are well u might know what i mean i know windows servers uses this type of way to go back a level such as ..\root-folder\ but whats Linux ? on my linux server how could i go back a level to go to another folder ? i already tried using ..\ but it want use it so i have no clue [/snip] cd .. (up one level) cd ../ (same) cd ../.. (two levels) cd ../../ (same) HTH -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Is query possible? (Newbie)
On Thursday, August 14, 2003, at 08:04 PM, Jennifer Goodie wrote: I have 2 tables used for an online calendar... first table fields: primary_key , start_date, event_name, event_description second table fields: primary_key, end_date Tables fields are shortened and can't be changed. My second table only contains events that have a end date. I want to create a query that will take all the fields in. If no end_date exists then set to NULL. Been playing with it all day. Hoping some advance function exists. I thought of using a temp table but there must be a better way. I am confused by your question. It think it is missing words. If you are trying to select all records from first_table that do not have a record in second_table you can use a left join and is null... SELECT * from first_table LEFT JOIN second_table USING (primary_key) WHERE second_table.primary_key IS NULL Maybe I'm missing something here, but I don't see why you would want to split your tables up that way. You can't be saving that much room, and I don't think it really goes with standard normalization conventions. If you don't want to join the two, you could use the query above to find out all of the event ID's that you need to add to the second field. The easiest way to deal with this would be to modify Jennifer's to: SELECT first_table.primary_key from first_table LEFT JOIN second_table USING (primary_key) WHERE second_table.primary_key IS NULL and export it, and then just straight import it into your second_table. If you leave what you're putting into the second_table.end_date blank, it should come through as NULL. Although, I would also definitely suggest joining the two tables. FWIW, you can do this fairly easily by: CREATE TABLE new_table SELECT first_table.primary_key AS primary_key, first_table.start_date AS start_date, first_table.event_name AS event_name, first_table.event_description AS event_description second_table.end_date AS end_date FROM first_table LEFT JOIN second_table USING (primary_key); Although this is using extremely redundant syntax, this should give you one table, where all of the end_dates that's aren't populated in second_table end up being NULL. From there you could run a query: SELECT * FROM new_table WHERE end_date IS NULL; Good luck, -Cameron Wilhelm -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySql Control Center
Hi Ulises, Is that option only available on a Windows MySQLCC ?? I didnt find it on the linux version. Best regards Nils Valentin Tokyo/Japan 2003年 8月 9日 土曜日 01:47、Cabanillas Dulanto, Ulises さんは書きました: > The option "Automatically limit SELECT queries to " must be set in the > Query Configuration Dialog. > > Regards, > Ulises > > -Mensaje original- > De: Trevor Morrison [mailto:[EMAIL PROTECTED] > Enviado el: Viernes 8 de Agosto de 2003 09:36 AM > Para: [EMAIL PROTECTED] > Asunto: MySql Control Center > > > Hi, > > I am new to using the Control Center-0.9.2-beta. My problem is that I > cannot seem to display more than 1000 rows in my result set when I know > that there is 1534 rows. I know that it is a setting somewhere, but is it > in MySql or in the Control Center somewhere. I am running this on a > Windows 2000 machine. > > TIA > > Trevor -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MYSQL Scalability on SMPs
> It looks like Igor comitted it to the 4.1 tree on the 2nd of this month: I'd assume that this change is necessary but not sufficient for the MySQL table type table locking issue... I know, I know, there is InnoDB for that, but there are reasons not to use it despite this particular wonderful advantage. --steve- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fix compilation failure on Tru64 UNIX V4.0F
>Description: ft_dump failed to link on Tru64 UNIX V4.0F with gcc 3.1: /bin/ksh ../libtool --mode=link gcc -O3 -DDBUG_OFF -mieee -DUNDEF_HAVE_GETHOSTBYNAME_R -o ft_dump ft_dump.o libmyisam.a ../mysys/libmysys.a ../dbug/libdbug.a ../strings/libmystrings.a -lm -lpthread gcc -O3 -DDBUG_OFF -mieee -DUNDEF_HAVE_GETHOSTBYNAME_R -o ft_dump ft_dump.o libmyisam.a ../mysys/libmysys.a ../dbug/libdbug.a ../strings/libmystrings.a -lm -lpthread snprintf collect2: ld returned 1 exit status make[2]: *** [ft_dump] Error 1 Obviously, snprintf is undefined (true, since the system libc lacks that function). >How-To-Repeat: Configure and build mysql 4.0.14 as described in Environment below. >Fix: myisam/ft_dump.c used snprintf unconditionally, whitout regarding HAVE_SNPRINTF. There are two other places where the test was broken: they used HAVE_SNPRINTF_ (which doesn't exist anywhere) instead of HAVE_SNPRINTF. The following patch fixes all three errors and allows the mysql build to complete successfully. === RCS file: libmysqld/RCS/field.cc,v retrieving revision 1.1 diff -up -r1.1 libmysqld/field.cc --- libmysqld/field.cc 2003/07/18 14:57:47 1.1 +++ libmysqld/field.cc 2003/08/07 23:27:22 @@ -742,7 +742,7 @@ void Field_decimal::store(double nr) char buff[320]; fyllchar = zerofill ? (char) '0' : (char) ' '; -#ifdef HAVE_SNPRINTF_ +#ifdef HAVE_SNPRINTF buff[sizeof(buff)-1]=0; // Safety snprintf(buff,sizeof(buff)-1, "%.*f",(int) dec,nr); #else === RCS file: myisam/RCS/ft_dump.c,v retrieving revision 1.1 diff -up -r1.1 myisam/ft_dump.c --- myisam/ft_dump.c2003/07/18 14:57:46 1.1 +++ myisam/ft_dump.c2003/08/07 23:30:43 @@ -130,7 +130,12 @@ int main(int argc,char *argv[]) #error #endif - snprintf(buf,MAX_LEN,"%.*s",(int) keylen,info->lastkey+1); +#ifdef HAVE_SNPRINTF + buf[sizeof(buf)-1]=0;// Safety + snprintf(buf,MAX_LEN,"%.*s",(int) keylen,info->lastkey+1); +#else + sprintf(buf,"%.*s",(int) keylen,info->lastkey+1); +#endif casedn_str(buf); total++; lengths[keylen]++; === RCS file: sql/RCS/field.cc,v retrieving revision 1.1 diff -up -r1.1 sql/field.cc --- sql/field.cc2003/07/18 14:57:47 1.1 +++ sql/field.cc2003/08/07 23:25:53 @@ -742,7 +742,7 @@ void Field_decimal::store(double nr) char buff[320]; fyllchar = zerofill ? (char) '0' : (char) ' '; -#ifdef HAVE_SNPRINTF_ +#ifdef HAVE_SNPRINTF buff[sizeof(buff)-1]=0; // Safety snprintf(buff,sizeof(buff)-1, "%.*f",(int) dec,nr); #else >Submitter-Id: net >Originator: >Organization: - Rainer Orth, Faculty of Technology, Bielefeld University > >MySQL support: none >Synopsis: Several unconditional uses of snprintf cause link failure on Tru64 >UNIX V4.0F >Severity: serious >Priority: medium >Category: mysql >Class: sw-bug >Release: mysql-4.0.14 (Source distribution) >C compiler:gcc (GCC) 3.1 >C++ compiler: g++ (GCC) 3.1 >Environment: System: OSF1 metropolitan V4.0 1229 alpha Machine: alpha Some paths: /vol/perl-5.8/bin/perl /vol/gnu/bin/make /vol/gnu/bin/gcc /usr/bin/cc GCC: Reading specs from /vol/gnu/lib/gcc-lib/alpha-dec-osf4.0f/3.1/specs Configured with: /vol/gnu/src/gcc/gcc-3.1-branch-dist/configure --prefix=/vol/gnu --with-local-prefix=/vol/gnu --disable-nls alpha-dec-osf4.0f Thread model: single gcc version 3.1 Compilation info: CC='gcc' CFLAGS='' CXX='g++' CXXFLAGS='' LDFLAGS='' ASFLAGS='' LIBC: lrwxr-xr-x 1 root system 17 Dec 15 1999 /lib/libc.a -> ../ccs/lib/libc.a lrwxr-xr-x 1 root system 17 Dec 15 1999 /usr/lib/libc.a -> ../ccs/lib/libc.a Configure command: ./configure '--prefix=/vol/mysql-4.0' '--infodir=/vol/mysql-4.0/share/info' '--libexecdir=/vol/mysql-4.0/lib' '--with-berkeley-db' '--without-bench' '--build' 'alpha-dec-osf4.0f' 'build_alias=alpha-dec-osf4.0f' -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL field data type for ISBN numbers
James Johnson wrote: Hi, I have a MySQL database for books. The ISBN field is set as varchar(15) and I've put a test ISBN number in of 1--111-11. Can someone tell me why this SQL query isn't working? $ISBN = $_GET['isbn']; $query = "SELECT * FROM book_details WHERE ISBN = '$ISBN'"; Since the field is a varchar, should wrapping the variable (1--111-11) in single quotes work? If I change the data in the table to 'abcd' and run this query $ISBN = 'abcd'; $query = "SELECT * FROM book_details WHERE ISBN = '$ISBN'"; It works. PHP Version 4.3.2 mysql-4.0.14b Thanks, James Try 'echo'ing $query to your browser and then copying the query from your browser into the mysql client and see if it works. I suspect there may be something wrong with the data after the $ISBN = $_GET['isbn'] bit, and echoing the query might make it more obvious what the problem is. -- Daniel Kasak IT Developer * NUS Consulting Group* Level 18, 168 Walker Street North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Is query possible? (Newbie)
> I have 2 tables used for an online calendar... > > first table fields: primary_key , start_date, event_name, > event_description > second table fields: primary_key, end_date > > Tables fields are shortened and can't be changed. > > My second table only contains events that have a end date. I want > to create > a query that will take all the fields in. If no end_date exists > then set to > NULL. Been playing with it all day. Hoping some advance function exists. I > thought of using a temp table but there must be a better way. > I am confused by your question. It think it is missing words. If you are trying to select all records from first_table that do not have a record in second_table you can use a left join and is null... SELECT * from first_table LEFT JOIN second_table USING (primary_key) WHERE second_table.primary_key IS NULL Maybe I'm missing something here, but I don't see why you would want to split your tables up that way. You can't be saving that much room, and I don't think it really goes with standard normalization conventions. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[Fwd: Re: Can't connect to local MySQL question]
--- Begin Message --- On 8/9/03 11:20 PM, "Andreas" <[EMAIL PROTECTED]> wrote: Andreas, Thanks for the help... The error log shows: 030810 14:52:48 mysqld started 030810 14:52:49 InnoDB: Started 030810 14:52:49 Fatal error: Can't open privilege tables: Table 'mysql.host' doesn't exist 030810 14:52:49 Aborting 030810 14:52:49 InnoDB: Starting shutdown... 030810 14:52:51 InnoDB: Shutdown completed 030810 14:52:51 /usr/local/mysql/bin/mysqld: Shutdown Complete 030810 14:52:51 mysqld ended I've bought several books on php/mysql and they all get right into the programming aspects, and what I probably need to look for is a book that talks about the architecture. I can see that the first thing I really need to do, is get a good understanding of is all the pieces that make mysql work, what they do, how & when they get created. What tools to use, preferably GUI tools. I followed the instructions on the install, and re-installed, the deleted everything & installed again. ...and I have no clue why mysqld is unhappy about "Table 'mysql.host' doesn't exist". Where in the install, etc, process was it supposed to get created, and by which piece? I think I need to take three steps back, and figure out where the beginning is? Thanks for the help... Bill Hernandez Plano, Texas BTW, your comment about mysqld being a daemon, and mysql is the client really helped, puts things in perspective. Sort of client -> server which I can relate to... > Bill Hernandez wrote: > >> I followed the install instructions at http://entropy.ch , and was able to >> get mySql & php installed on my G4 -> (OSX 10.2.6) last night. I downloaded >> Navicat and setup a password for the mysql user. I created a connection >> called myDatabase_connection to a database called myDatabase. I was able to >> get everything running OK. > > Did the server start and could you connect ? > > >> Today when I restarted the machine, I tried to launch the php program that I >> was running last night, and kept getting an error 2002 below. > > 1) Have the mysql daemon running > 2) connect with a client > > >> [Home:/usr/local/mysql] justMe# ./bin/mysqld_safe & >> [1] 479 >> [Home:/usr/local/mysql] justMe# Starting mysqld daemon with databases from >> /usr/local/mysql/data >> 030809 20:04:58 mysqld ended > > there is an error.log in the data directory. > Look there for mysql's complaints. > > >> HERE I HIT RETURN TO GET THE PROMPT AGAIN (Should I have typed the name of >> the database here, then hit return?) > > no > You are mixing up the server and client process. > mysqld is the server. It runs in the background and stores databases in > it's data directory. There can be many separate databases. > You'll select one of them later when you connect with the client. > > mysql <-- no d (=daemon) is the textmode client that comes with the > mysql package. > > >> [1]Done ./bin/mysqld_safe >> [Home:/usr/local/mysql] justMe# ./bin/mysql myDatabase >> ERROR 2002: Can't connect to local MySQL server through socket >> '/tmp/mysql.sock' (2) > > Without running server-process there is no socket to connect to. > > > start by looking at the err-file in the data-dir. > > You can run mysqld_save without the & to see more output. Sometimes the > server comes up that way. Then you wont regain access to the shell where > you ran mysqld_save. > > I had a hard time getting this kind of error. > One day I figured out, that mc caused the hick-up. mc is a textmode > filemanager I really use often. > mysqld started and died at once again. > > If mc produces this effect then perhaps other filemanaging tools do, too. > Use pure bash or xterm. > > Better yet, install mysql as a service to have it started at boot time. > > --- End Message --- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Quick ones to speed up acces!
In my earlier post i forgot to mention about limit,10,20 to be added in query. - Original Message - From: "Binay Agarwal" <[EMAIL PROTECTED]> To: "Roger Davis" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Monday, August 11, 2003 5:22 AM Subject: Re: Quick ones to speed up acces! > Thanks Roger for quick response. > > Herebelow the structure of my table and query. > > CREATE TABLE npg_search ( >sno int(11) NOT NULL auto_increment, >fileid varchar(255) NOT NULL, >caption text, >headline varchar(255), >specialins varchar(255), >keywords varchar(255) NOT NULL, >cat varchar(50) NOT NULL, >byline varchar(255), >credit varchar(255), >source varchar(255), >date date, >city varchar(100), >state varchar(100), >country varchar(100) NOT NULL, >orgtransref varchar(100), >copyright varchar(255), >extratxt text, >prints tinyint(4), >extrastr varchar(255), >bylinetitle varchar(255), >addedon timestamp(14), >PRIMARY KEY (sno), >INDEX (fileid, keywords, cat, country)); > > As shown columns fileid,keywords,cat and country are indexed. > > Query is: > > Select * from table where keywords like '%blah%' or caption like '%blah%' > or headline like '%blah%' or cat like '%blah%' > > Please help me out. > > Binay > > > - Original Message - > From: "Roger Davis" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent: Monday, August 11, 2003 4:57 PM > Subject: RE: Quick ones to speed up acces! > > > > It's called indexing and your queries should return in under 1 sec unless > > you are pulling all your records and all your data. Show us your Queries > > and you table structure and I am sure we can help. > > > > Roger > > > > > -Original Message- > > > From: Binay Agarwal [mailto:[EMAIL PROTECTED] > > > Sent: Sunday, August 10, 2003 7:08 PM > > > To: [EMAIL PROTECTED] > > > Subject: Quick ones to speed up acces! > > > > > > > > > Hi everybody! > > > > > > I am querying from a table containing more than 40,000 records. > > > Earlier when the records were 10,000 it was taking 9 sec and now > > > after 1 year and 40,000 records its taking 30 sec. Code is the same. > > > > > > I am pretty sure that it has something to do with database only. > > > No body can think of spending 30 secs for retrieving values from > > > tables unless it's very huge in the sense of millions of records. > > > > > > I don know whether I have to modify my database or do some sort > > > of restructuring or reindexing so as to make it fast enough. > > > > > > Is there some methods or optmization which can be applied to this > > > database which hasn't been touched since design to enhance the spped. > > > > > > If there are some quick ones but valued alottt please let me know. > > > > > > > > > Thanks in advance > > > > > > Binay > > > > > > --- > > > Incoming mail is certified Virus Free. > > > Checked by AVG anti-virus system (http://www.grisoft.com). > > > Version: 6.0.507 / Virus Database: 304 - Release Date: 8/4/2003 > > > > > --- > > Outgoing mail is certified Virus Free. > > Checked by AVG anti-virus system (http://www.grisoft.com). > > Version: 6.0.507 / Virus Database: 304 - Release Date: 8/4/2003 > > > > > > -- > > 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: How to speed up the access
In the last episode (Aug 09), Vinod Bhaskar said: > I have MySQL 3.23.41 in my Mandrake Linux 8.1. I am using PHP 4.0 > queries to access the MySQL database, in Apache 1.3.2 environment. > > Now a days, some of my php modules are very slow. eg., earlier a > report generation program in PHP use to take less than 30 sec to > genrate and disply the report and noiw the same report program is > taking more than 5 min to disply. Off couce the database is growing. > But there are only 4000 Records. > > What may be the problem and how to spead up the process? Please suggest. You probably need to add indexes. Chapter 5 of the MySQL manual explains it all. http://www.mysql.com/documentation/mysql/bychapter/manual_MySQL_Optimisation.html -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can't connect to local MySQL question
I forwarded your mail to the mysql list so that everyone who cares knows what is going. Bill Hernandez wrote: The error log shows: 030810 14:52:48 mysqld started 030810 14:52:49 InnoDB: Started 030810 14:52:49 Fatal error: Can't open privilege tables: Table 'mysql.host' doesn't exist 030810 14:52:49 Aborting Ah that sounds familiar. :( 1) If you don't know what InnoDB is, comment all the lines out that have innodb options in your /etc/my.cnf 2) InnoDB provides transactions and referential integrity. Neat things that you probaply not know about, yet. 3) I bet the file /usr/local/mysql/data/mysql/mysql.host exists but InnoDB isn't allowed to "see" it by wrong directory/file permissions of your file system (not MySQL !) I've bought several books on php/mysql and they all get right into the programming aspects Thats the point of PHP. They have to suppose you allready know to handle the DBMS. www.php.net www.devshed.net www.php.net Don't miss the PEAR extensions. --> pear.php.net Start with PHP's mysql standard functions to get a basic understanding. When you think you got that move up to the PEAR-DB classes. http://pear.php.net/manual/en/package.database.php and what I probably need to look for is a book that talks about the architecture. well, not really. Knowledge on dead trees is only good provided you have the time or motivation to actually read the book and of course some are crap anyway. Then there are those which fill 1000 pages with more or less important stuff but your specific problem is no where to be found. I hate it especially to read a chapter that stops right before the point where I need further info stating that from here it's too specific for this book. =8-O I bought lots of books which I should have read completely but just couldn't get the hang of. Buy it, take it home, put it on shelf, feel good that now all problems can come ... not quite. 1) Look in the (searchable!) online docu. Some with readers's comments. 2) Look at google. Real issues tend show up elsewhere and probaply are solved and documented allready. 3) Ask on a list. 4) Books ? Usually decorative at best though there are good ones, too. I can see that the first thing I really need to do, is get a good understanding of is all the pieces that make mysql work, what they do, how & when they get created. What tools to use, preferably GUI tools. that can't hurt ;) I like phpMyAdmin as maintenance client. mysql and mysqladmin in texmode ain't that bad either when you know what's going on. Then there is mysqlcc from mysql.com. GUI but I don't know if they build it for apples. ...and I have no clue why mysqld is unhappy about "Table 'mysql.host' doesn't exist". Where in the install, etc, process was it supposed to get created, and by which piece? /usr/local/mysql/scripts/mysql_install_db Your problem are the permissions not the lack of the file. I think I need to take three steps back, and figure out where the beginning is? Thanks for the help... no problem ... Andreas Germany -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie question...memo field
The following program will solve your problem. Feel free to miss use ;) -- Aftab Jahan Subedar Software Engineer Subedar Technologies Subedar Baag Bibir Bagicha #1 81/1-A North Jatrbari Dhaka 1204 Bangladesh sms://+447765341890 sms://+880171859159 http://www.ceobangladesh.com http://www.DhakaStockExchangeGame.com http://www.geocities.com/jahan.geo /* Copyright (c) 2003 Aftab Jahan Subedar mysql_last_value() Version 3.1 -- Replaces NULL column(s) with value from last available column value. Scenario Table to be operated on. table_a --- record id color 1001 BLACK 2NULLPINK NULL 002 WHITE 3NULLBLUE NULL NULLGREEN NULL 003 YELLOW 4004 BALCK Table that is converted to. table_b --- record id color 1001 BLACK 2001 PINK 2002 WHITE 3002 BLUE 3002 GREEN 3003 YELLOW 4004 BALCK This is free for public. Commercial uses require license from Aftab Jahan Subedar Software Engineer Subedar Technologies Subedar Baag Bibir Bagicha #1 81/1-A North Jatrbari Dhaka 1204 Bangladesh sms://+447765341890 sms://+880171859159 http://www.ceobangladesh.com http://www.DhakaStockExchangeGame.com http://www.geocities.com/jahan.geo -- source code found here +880171859159 sms +447765341890 [EMAIL PROTECTED] [EMAIL PROTECTED] [EMAIL PROTECTED] Has Problem? Feel free to report. License Fee: USD 25 or equivalent for Lifetime . Bank information: i. Aftab Jahan Subedar Sort Code: 800283 Account No. 07271988 Bank Of Scotland Newington Branch 51 South Clerk Street Edinburgh EH8 9PP UK ii. Aftab Jahan Subedar Savings Account No. 794-2-4403321-4 [Sort Code 794] Standard Chartered Bank 32 36 Jalan 52/4 Petaling Jaya Selangor Malaysia iii.Aftab Jahan Subedar Savings Account No. 18 1757 393 01 [Sort Code 18] Standard Chartered Bank 53 Kawran Bazar, G.P.O Box #3668 Dhaka 1215 Bangladesh compile instruction: cc -o mysql_last_value mysql_last_value.c -I/usr/local/include/mysql -L/usr/local/lib/mysql -lmysqlclient usage: ./mysql_last_value -u user -h host -d last_value_test -f table_from -t table_to -r "replace_field1 replace_fieldn" -v -p secretpassword Parameters: -d database -f from which table to copy from -t to which table to be copied to -r replace field names separated with space. upto 20 fields. -v display verbose -v -v display insert statement ;( example: ./mysql_last_value -d last_value_test -f table_a -t table_b -r "record id" -v ./mysql_last_value -d last_value_test -f table_a -t table_b -r "record" -v Training available on C/C++, Unix , MySQL (or other API) in Bangladesh and abroad. */ #include #include #include #include #include #include #include /* for freeing easily*/ char*host = NULL; char*user = NULL; char*passwd = NULL; char*database = NULL; char*sql_insert_to = NULL; char*sql_insert_from = NULL; int verbose = 1; int use_supplied = 0; char*last_value=NULL; char*replace_field_name=NULL; char*criterion=NULL; char*insert_statement=NULL; char*replace_value[20]; unsigned int*puiQuotes=NULL; char*pcQuery=NULL; MYSQL mysql; MYSQL_RES *pResult=NULL; unsigned int*puiIndexOfReplaceField=NULL; unsigned intuiNumOfReplaceField=0; unsigned long *pulFieldLengths; unsigned int uiReplaceIndex=0; void usage(void); void free_all(void); char *strupr(char *str); /*int strcmpp(const char *p1, const char *p2);*/ void append_insert(unsigned long length,char *value); unsigned int get_replace_index(unsig
Re: read_const error 127 - then MySQL dies
Richard, > thd->query at 0x89af670 = SELECT * FROM order_data WHERE viewed='' ORDER > BY order_num DESC what does SHOW CREATE TABLE order_data; say? What does CHECK TABLE order_data; say? Does it print anything to the .err log in the MySQL datadir? Please resolve the stack trace below: > Stack range sanity check OK, backtrace follows: > 0x80dbe1f > 0x4003b47e > 0x8101e09 > 0x810e90d > 0x80e6d8a > 0x80ea88b > 0x80e5ed3 > 0x80ebe0e > 0x80e50bf > 0x40035941 > 0x420da1ca > New value of fp=(nil) failed sanity check, terminating stack trace! > Please read http://www.mysql.com/doc/en/Using_stack_trace.html and > follow instructions on how to resolve the stack trace. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Transactions, foreign keys, and a hot backup tool for MySQL Order MySQL technical support from https://order.mysql.com/ - Original Message - From: "Richard Gabriel" <[EMAIL PROTECTED]> Newsgroups: mailing.database.mysql Sent: Friday, August 08, 2003 11:50 PM Subject: read_const error 127 - then MySQL dies > Hi all, > > The following keeps happening and I can't pinpoint a query that is > causing it. It did not happen in 3.23.x, but started upon upgrading to > 4.0.14. The operating system/hardware information is as follows: > > RedHat 8.0 - kernel 2.4.18SMP > 4x Xeon Processors > 4x 80GB SCSI drives (hardware RAID-10) > 2GB RAM > > The following is a log exerpt: > > 030808 15:22:09 read_const: Got error 127 when reading table > 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=8388600 > read_buffer_size=131072 > max_used_connections=184 > max_connections=1000 > threads_connected=21 > It is possible that mysqld could use up to > key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections > = 2184184 K > bytes of memory > Hope that's ok; if not, decrease some variables in the equation. > > thd=0x98772088 > Attempting backtrace. You can use the following information to find out > where mysqld died. If you see no messages after this, something went > terribly wrong... > Cannot determine thread, fp=0x98a4ad98, backtrace may not be correct. > Stack range sanity check OK, backtrace follows: > 0x80dbe1f > 0x4003b47e > 0x8101e09 > 0x810e90d > 0x80e6d8a > 0x80ea88b > 0x80e5ed3 > 0x80ebe0e > 0x80e50bf > 0x40035941 > 0x420da1ca > New value of fp=(nil) failed sanity check, terminating stack trace! > Please read http://www.mysql.com/doc/en/Using_stack_trace.html and > follow instructions on how to resolve the stack trace. Resolved > stack trace is much more helpful in diagnosing the problem, so please do > resolve it > Trying to get some variables. > Some pointers may be invalid and cause the dump to abort... > thd->query at 0x89af670 = SELECT * FROM order_data WHERE viewed='' ORDER > BY order_num DESC > thd->thread_id=42660972 > The manual page at http://www.mysql.com/doc/en/Crashing.html contains > information that should help you find out what is causing the crash. > > Number of processes running now: 0 > 030808 15:22:19 mysqld restarted > 030808 15:22:20 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 5 4012008225 > InnoDB: Doing recovery: scanned up to log sequence number 5 4012079335 > 030808 15:22:20 InnoDB: Starting an apply batch of log records to the > database... > InnoDB: Progress in percents: 31 32 33 34 35 36 37 38 39 40 41 42 43 44 > 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 > 69 70 71 72 73 74 75 > InnoDB: Apply batch completed > InnoDB: Last MySQL binlog file position 0 1058709429, file name > ./db1-bin.062 > 030808 15:22:21 InnoDB: Flushing modified pages from the buffer pool... > 030808 15:22:21 InnoDB: Started > /usr/sbin/mysqld-max: ready for connections. > Version: '4.0.14-Max-log' socket: '/var/lib/mysql/mysql.sock' port: > 3306 > > > This happens often. Any ideas? Thanks. > > -- > Richard Gabriel <[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]
mysqlbug
I have uninstall and reinstall mysql 2 to 3 three times at my home. I am trying to create a database by doing some inserts but it will not let me. It is just blank. I would really like to know what I am doing wrong or do I just need to remove all the components and start over again? I do not know what the problem is and I need help. Tammy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Form values are truncated
On 11 Aug 2003 at 8:49, James Johnson wrote: > I'm building a member signup form. Fields that contain more than one > word are being truncated when being inserted into the MySQL table. It's very unlikely this has anything to do with MySQL or PHP. It sounds like you're missing quotes around the values in your HTML form, so you're ending up with something like this in your HTML: rather than this: (The first two sets of quotes aren't necessary, but it's easier just to get into the habit of always using them.) -- 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: MySQL Replication
On Fri, Aug 08, 2003 at 12:10:18PM -0400, Adam Nelson wrote: > Also, one has to work out the cost of high availability. If you're > talking about a situation where you reduce downtime from 4 hours/yr to > .5 hours/yr and it costs you x dollars, you have to make sure that the > extra 3.5 hours of downtime would cost more than that much money. Agreed. In fact, this has come up before... http://jeremy.zawodny.com/blog/archives/000805.html Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! <[EMAIL PROTECTED]> | http://jeremy.zawodny.com/ MySQL 4.0.13: up 10 days, processed 336,775,492 queries (387/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Cant login as a user I thought I created
On Thu, 14 Aug 2003 16:48:15 -0700, Jonathan Hilgeman wrote:
>> >Delete anonymous user('') from table user. Don't forget about FLUSH
>PRIVILEGES.
>> >
>> Thanks, that worked. Why?
>
> I suggested this yesterday, at the very beginning of Mark's
>question, and I also suggested it again today in a private message, but both
>of my suggestions got ignored, I guess. Keep your eyes open next time, Mark.
>
Actually I did see it. I just didn't want to try to follow multiple
paths of advice at the same time.
Just trying to avoid a too many cooks type problem.
Mark Healey
[EMAIL PROTECTED]
This account is only for lists to which I've subscribed.
Any spammers invite the worst revenge I think I can get away with.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Converting Sequel to MYSQL
Hi all, Does anyone have any suggestion for converting a Sequel database to MYSQL? I just want to copy a Sequel database data to a Mysql one. Thanks in advance for any suggestion, Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Is query possible? (Newbie)
I have 2 tables used for an online calendar... first table fields: primary_key , start_date, event_name, event_description second table fields: primary_key, end_date Tables fields are shortened and can't be changed. My second table only contains events that have a end date. I want to create a query that will take all the fields in. If no end_date exists then set to NULL. Been playing with it all day. Hoping some advance function exists. I thought of using a temp table but there must be a better way. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with count(*)
what ever happend to a unique primary key like userID ? >>> "gord barq" <[EMAIL PROTECTED]> 08/11/03 11:15am >>> I have a table I'm using for logging purposes with a schema like: create table results ( user varchar(255) ); Where user is not a unique field and I want to find out how many unique users there are in the table. I want to do something like: select count(count(*)) from results group by user; But that doesn't work.. Any ideas? Thanks _ STOP MORE SPAM with the new MSN 8 and get 2 months FREE* http://join.msn.com/?page=features/junkmail -- 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: Quick ones to speed up acces!
MySQL does not use an index when you do "%blah%". It would use the index for "blah%" however. See the following reference. http://www.mysql.com/doc/en/MySQL_indexes.html I would suggest you alter your tables to use a FullText Search. http://www.mysql.com/doc/en/Fulltext_Search.html Roger > -Original Message- > From: Binay Agarwal [mailto:[EMAIL PROTECTED] > Sent: Sunday, August 10, 2003 7:53 PM > To: Roger Davis; [EMAIL PROTECTED] > Subject: Re: Quick ones to speed up acces! > > > Thanks Roger for quick response. > > Herebelow the structure of my table and query. > > CREATE TABLE npg_search ( >sno int(11) NOT NULL auto_increment, >fileid varchar(255) NOT NULL, >caption text, >headline varchar(255), >specialins varchar(255), >keywords varchar(255) NOT NULL, >cat varchar(50) NOT NULL, >byline varchar(255), >credit varchar(255), >source varchar(255), >date date, >city varchar(100), >state varchar(100), >country varchar(100) NOT NULL, >orgtransref varchar(100), >copyright varchar(255), >extratxt text, >prints tinyint(4), >extrastr varchar(255), >bylinetitle varchar(255), >addedon timestamp(14), >PRIMARY KEY (sno), >INDEX (fileid, keywords, cat, country)); > > As shown columns fileid,keywords,cat and country are indexed. > > Query is: > > Select * from table where keywords like '%blah%' or caption like '%blah%' > or headline like '%blah%' or cat like '%blah%' > > Please help me out. > > Binay > > > - Original Message - > From: "Roger Davis" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent: Monday, August 11, 2003 4:57 PM > Subject: RE: Quick ones to speed up acces! > > > > It's called indexing and your queries should return in under 1 > sec unless > > you are pulling all your records and all your data. Show us > your Queries > > and you table structure and I am sure we can help. > > > > Roger > > > > > -Original Message- > > > From: Binay Agarwal [mailto:[EMAIL PROTECTED] > > > Sent: Sunday, August 10, 2003 7:08 PM > > > To: [EMAIL PROTECTED] > > > Subject: Quick ones to speed up acces! > > > > > > > > > Hi everybody! > > > > > > I am querying from a table containing more than 40,000 records. > > > Earlier when the records were 10,000 it was taking 9 sec and now > > > after 1 year and 40,000 records its taking 30 sec. Code is the same. > > > > > > I am pretty sure that it has something to do with database only. > > > No body can think of spending 30 secs for retrieving values from > > > tables unless it's very huge in the sense of millions of records. > > > > > > I don know whether I have to modify my database or do some sort > > > of restructuring or reindexing so as to make it fast enough. > > > > > > Is there some methods or optmization which can be applied to this > > > database which hasn't been touched since design to enhance the spped. > > > > > > If there are some quick ones but valued alottt please let me know. > > > > > > > > > Thanks in advance > > > > > > Binay > > > > > > --- > > > Incoming mail is certified Virus Free. > > > Checked by AVG anti-virus system (http://www.grisoft.com). > > > Version: 6.0.507 / Virus Database: 304 - Release Date: 8/4/2003 > > > > > --- > > Outgoing mail is certified Virus Free. > > Checked by AVG anti-virus system (http://www.grisoft.com). > > Version: 6.0.507 / Virus Database: 304 - Release Date: 8/4/2003 > > > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > > > > > > --- > Incoming mail is certified Virus Free. > Checked by AVG anti-virus system (http://www.grisoft.com). > Version: 6.0.507 / Virus Database: 304 - Release Date: 8/4/2003 > --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.507 / Virus Database: 304 - Release Date: 8/4/2003 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mySQL & ADO?
Hello Guys, Is there support for ADO? Im a proffesional Delphi developer and must access mySQL via ADO. Kind Regards, Lennie De Villiers -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
A final Windows MySQL PHP plea
Hi all Is there anybody out there who has managed to successfully configure Win2000, IIS5, MySQL 4.0.14 and PHP 4.3.2 (ISAPI) to work with a couple of hundred users at any one time? I have chucked absolutely everything I can think of at this, but the MySQL (it seems) simply eats all the available CPU within a short space of time (regardless of users) and brings the site to a halt. My last throw of the dice today was to install all on a new Dual 1.8Ghz Pentium, with three hard disks in a RAID array, and 2GB memory, but it's achieved pretty much nothing. I am now desperate, and if anyone has any flash of inspiration for me, I'm all ears. The previous ASP version of the site runs like a dream, but there's something I'm either doing wrong, or this new combination of software simply doesn't like. Many thanks Stressed Gary
Meaning of Column time in Show Processlist
I would like to know (if possible, there is no explanation in the documentation) the exact meanning of the column "time" in the "show processlist" command. Why is it sometime so hight ? When is it reinitialize (and why) ? Is there any correlation between the time column and the variables "wait timeout" and "interactive timeout" ? Regards, Marc Mechain Atos Origin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Transactions tutorial
I'm looking for any online resources/tutorials and such about transactions. Preferable for MySQL, but others will do. Other than that, is there any good book about transactions. Again preferable for MySQL. /T -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select match from a stored delimitated string?
Thanks to both Ed and Amer for the excellent suggestions.
This is definitely the time in my project for db changes :) I think you've
both nailed what fundamental I was missing. In the long run, a bit of
re-thinking now will simplify a lot down the road.
Ed, sorry to be so dense, but by PK field do you mean a unique ID field?
Amer, re my mail bouncing... My host had numerous issues yesterday.
Hopefully all fixed today. Oh, and thanks for the book suggestion :)
Best regards,
Verdon
On 8/12/03 6:05 AM, "[EMAIL PROTECTED]"
<[EMAIL PROTECTED]> wrote:
>> On Monday, August 11, 2003, at 11:06 AM, Verdon vaillancourt wrote:
>>
>> 2) I have a table of 'projects', which are comprised of a number of
>> 'pieces'. There is a column in this projects table called 'pieces',
>> which
>> contains a delimitated string of all the 'pieces' related to that
>> 'project',
>> in the format of '1||2||3||5||12||16||17||24||25'
>
> From: Ed Leafe <[EMAIL PROTECTED]>
>
> While this is a clever approach, a more robust approach is to create a
> third table, usually referred to as an allocation table or a
> many-to-many table, which contains the links between pieces and
> projects. Assuming that both Pieces and Projects have PK fields, the
> allocation table would consist of 3 columns: its own PK, the PK of the
> Project record, and the PK of the Pieces record. There would be as many
> records for a given Project PK as there are Pieces in the Project.
>
> You can then join the Project to its Pieces as follows:
>
> select Project.*, Pieces.*
> from Project left join ProjPieces on Project.projPK = ProjPieces.projPK
> left join Pieces on ProjPieces.piecePK = Pieces.piecePK
> where Project.projPK = [some value]
>
> Note that using this design, it is just as easy to get all the pieces
> for a given project as it is to get all the projects a given piece
> belongs to.
>
>
> From: Amer Neely <[EMAIL PROTECTED]>
>
> It may be possible to cruft a query for what you want, but I'd suggest a
> slight re-design of your tables. Do some reading up on designing
> relational databases ("Database design for mere mortals" by Michael J.
> Hernandez is an excellent choice. ISBN: 0-201-69471-9).
>
> Any time you have more than one value in a field is an idication you
> should consider making that field a separate table. In your case I'd
> make a new table of 'pieces' and populate it with something that
> identifies which client AND project each piece belongs to. Maybe a
> combination of 2 fields (client id + project id). Then your select would
> include 2 'where' conditions checking client id and project id.
>
> btw, your email address bounced back - couldn't check the MX records.
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL field data type for ISBN numbers
Hi Daniel, Here's what is echoed back. It looks valid to me. SELECT * FROM book_details WHERE ISBN = '1---1' James -Original Message- From: Daniel Kasak [mailto:[EMAIL PROTECTED] Sent: Sunday, August 10, 2003 5:40 PM To: James Johnson; [EMAIL PROTECTED] Subject: Re: MySQL field data type for ISBN numbers James Johnson wrote: >Hi, > >I have a MySQL database for books. The ISBN field is set as varchar(15) >and I've put a test ISBN number in of 1--111-11. Can someone tell >me why this SQL query isn't working? > >$ISBN = $_GET['isbn']; >$query = "SELECT * FROM book_details WHERE ISBN = '$ISBN'"; > >Since the field is a varchar, should wrapping the variable >(1--111-11) in single quotes work? > >If I change the data in the table to 'abcd' and run this query > >$ISBN = 'abcd'; >$query = "SELECT * FROM book_details WHERE ISBN = '$ISBN'"; > >It works. > >PHP Version 4.3.2 >mysql-4.0.14b > >Thanks, >James > > > > Try 'echo'ing $query to your browser and then copying the query from your browser into the mysql client and see if it works. I suspect there may be something wrong with the data after the $ISBN = $_GET['isbn'] bit, and echoing the query might make it more obvious what the problem is. -- Daniel Kasak IT Developer * NUS Consulting Group* Level 18, 168 Walker Street North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
alter table froze entire machine!
Hi about 2 months ago I had trouble with alter table on large tables blocking all database activity and started a thread on this list called "alter table blocks other tables!" I tried to resolve the problems by upgrading to mysql 4.0.14, putting the database that I needed absolute best performance on a fast scsi drive all by itself. Then twice this week I needed to add a column to a large table (about a million records). The tables reside on the main system ide drive. For the first few minutes of the alter table everything was fine. I watched the file sizes of the temporary MYD and MYI files grow to about the size of the originals. Then a few minutes later tragedy, the whole machine all but locked up. commands typed on open shell sessions freeze and after typing in my username/password to start another linux session that session froze, never got a prompt. After several minutes of waiting I had to hit the reset button on the server. After it booted backup everything was fine, the table I was altering had the new column, the alter table actually finished! Like I said this happend twice! This is a RedHat 8 machine, kernel 2.4.18-14, Athlon XP 1700+, 1 gig ram. mysql was installed from mysql-standard-4.0.14-pc-linux-i686.tar.gz.tar I have successfully executed alter tables on smaller tables without problems. Also I accidently ran one of the alter tables on the slave database before running it on the master, it completed fine. It is Redhat9, kernel 2.4.20-8, P4 2.4, 1 gig ram. Mysql 4.0.12 What could possibly be causeing this? What to do to fix it? Is it a hardware problem? Kernel problem? I guess I should just put together a new machine and hope it doesn't happen to it. But thought I would post in case it helps anyone else. Thanks! Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Upgrade 3.23 ==> 4.0.13
may be you can check whether the older version is uninstalled properly before trying the new version . if u installed using rpm , then rpm -qa shows all the installed files . System wrote: > Hello All, > > I have compiled mysql-4.1.13 using the binary package these were my > configure options > ./configure --bindir=/usr/bin --libexecdir=/usr/lib --datadir=/usr/share > --includedir=/usr/include --mandir=/usr/share/man/man1/ > make > make install > > /usr/bin/mysql_fix_privilege_tables > /usr/bin/mysql_convert_table_format databasename > > I am following the link below. > http://www.mysql.com/documentation/mysql/bychapter/manual_Installing.html#Up > grading-from-3.23 > > After the installtion when i execute mysql it still shows me older version > how do i correct this? > > Any help will be appreciated. > > Thank You, > Tina. > > -- > 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: UDF Programming Problem
Jeremy Zawodny wrote:
On Tue, Aug 12, 2003 at 02:32:00PM +0800, Ariz Jacinto wrote:
i've created a simple UDF (for testing) that returns a string.
my problem is that the string that it returns, contain some
of the characters of the longest string in the column.
example:
Table
+-+
|name |
+-+
| hello world |
| goodbye|
+-+
mysql> select echo(name) from table;
hello world
goodbyerld< ???
*How do i fix this???
Maybe you can post the code and we'll help you fix it.
as you can see from my code, i'm trying to study on how to make my own
UDF coz i'm going to migrate our postgresql UDFs to MySQL...
any tips/link to tutorials will be highly appreciated
--- code --
#include
#include
#include
#include
my_bool echo_init(UDF_INIT *initid, UDF_ARGS *args, char *message);
void echo_deinit(UDF_INIT *initid);
extern "C" __declspec(dllexport) char *echo(UDF_INIT *initid, UDF_ARGS *args, char
*is_null, char *error);
my_bool echo_init(UDF_INIT *initid, UDF_ARGS *args, char *message)
{
if (args->arg_count != 1 || args->arg_type[0] != STRING_RESULT)
{
strcpy(message,"Wrong argument type");
return 1;
}
initid->maybe_null = 0;
return 0;
}
void echo_deinit(UDF_INIT *initid)
{
}
extern "C" __declspec(dllexport) char *echo(UDF_INIT *initid, UDF_ARGS *args, char
*is_null, char *error)
{
return args->args[0];
}
--- code --
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: using innodb
I can use innodb in Redhat9 but failed in redhat 7.2. I find the version of mysql in both redhat is identical. I wonder why it is the case. Version of mysql mysqladmin Ver 8.23 Distrib 3.23.56, for redhat-linux-gnu on i386 Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 3.23.56 Protocol version10 Connection Localhost via UNIX socket UNIX socket /var/lib/mysql/mysql.sock Any document to describe the replication process of the following case? innodb (master) --replication --> myisam (slave) unplug Jeremy Zawodny wrote: > > On Wed, Jul 30, 2003 at 12:34:54PM +0800, unplug wrote: > > I use rpm to update the previous version. I can use innodb in redhat 9 > > with version 2.23.56. But it failed in redhat 7.2. I wonder it is the > > kernel problem. > > Why would you suspect the kernel? How did it fail? > > > BTW, I want to ask whether I can do replication in the following case. > > > > innodb (master) --replication --> myisam (slave) > > Yes. > > Jeremy > -- > Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! > <[EMAIL PROTECTED]> | http://jeremy.zawodny.com/ > > MySQL 4.0.13: up 6 days, processed 207,625,568 queries (399/sec. avg) > > -- > 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: Joins and Unions
Hi Kim, no offense, but are you actually using the JOIN syntax correctly ? Could you post your query please ? My guess is you are doing a JOIN without any set conditions. hat would give you the result as described (which is normal). You would use the WHERE clause or for a LEFT JOIN ...the ON clause to filter to correct data which you are requesting. Please have a look once more at the JOIN syntax: http://www.mysql.com/doc/en/JOIN.html Some examples: mysql> SELECT * FROM table1,table2 WHERE table1.id=table2.id; mysql> SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id; mysql> SELECT * FROM table1 LEFT JOIN table2 USING (id); Best regards Nils Valentin Tokyo/Japan 2003年 8月 13日 水曜日 09:25、Kim Kohen さんは書きました: > G'day all, > > I'm pretty new to MySQL and very new to v4. > > This is probably going to sound strange, but is it possible to create a > union using the logic of a join? I have 4 tables and I need to join data > from them, but I'd like to display them like a Union. If I do a straight > join, I get lots of multiple entries thus: > > PageAd_num status stories > 14312-T1308 Ad Complete 1-marchingband-A33Placed > 14312-T1308 Ad Complete 1-jobs-T33Placed > 14312-T1308 Ad Complete 1-illegalbuild-A32 Placed > 14312-T1308 Ad Complete 1-haldonstfesty-T33 Placed > 14312-T1308 Ad Complete 1-jobs-T33Corrected > 14312-T1308 Ad Complete 1-car crash-T33 Placed > > Where ad_num "14312-T1308 Ad Complete" is replicated to accommodate > all the Joined data from the 'stories' table > > What I'd like to see is something like this: > > 14312-T1308 Ad Complete > 11-marchingband-A33Placed > 11-jobs-T33Placed > 11-jobs-T33Corrected > 11-illegalbuild-A32 Placed > 11-haldonstfesty-T33 Placed > 11-car crash-T33Placed > > Is this sort of thing possible? > > cheers > > kim -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A final Windows MySQL PHP plea
Gary Does your php code use persistent connections? mysql_pconnect() rather than mysql_connect() ? If so, that would ramp up the CPU usage fairly quickly, AFAIAA. Just a thought Terry --Original Message- > Hi all > > Is there anybody out there who has managed to successfully configure > Win2000, IIS5, MySQL 4.0.14 and PHP 4.3.2 (ISAPI) to work with a couple > of hundred users at any one time? I have chucked absolutely everything > I can think of at this, but the MySQL (it seems) simply eats all the > available CPU within a short space of time (regardless of users) and > brings the site to a halt. My last throw of the dice today was to > install all on a new Dual 1.8Ghz Pentium, with three hard disks in a > RAID array, and 2GB memory, but it's achieved pretty much nothing. I am > now desperate, and if anyone has any flash of inspiration for me, I'm > all ears. The previous ASP version of the site runs like a dream, but > there's something I'm either doing wrong, or this new combination of > software simply doesn't like. > > Many thanks > > Stressed Gary > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Joins and Unions
how can it be confusing ? nobody ever experienced it before? simply when you join 2 tables together if you get 5 records from the second table the first table will display 5 times aswell > Hi Daniel, > > I am still not sure what exactly you are trying to achieve. > > If you specify the WHERE condition correctly (or ON or USING for the > LEFT JOIN) then there is no duplicate entry. > > I believe what you are asking has more to do with the way how to > present the > data in the output. > > Best regards > > Nils Valentin > Tokyo/Japan > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
created new user but can't log in as that user
While logged in to my Linux server as root, I went in to MySQL and (with no databases selected, so that the "GRANT" statement would apply globally), ran the command: mysql> grant all privileges on * to bhaselto identified by ''; where is, of course, the password I wanted to use for the user 'bhaselto'. I can see an entry for that user in the 'user' table in the 'mysql' database: ++--+--+-+-+-+-+-+---+-+---+--+---++-+++ | Host | User | Password | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | ++--+--+-+-+-+-+-+---+-+---+--+---++-+++ [...] | % | bhaselto | | Y | Y | Y | Y | Y | Y | N | N | N| N | N | Y | Y | Y | where is an alphanumeric code that presumably represents the hash of the password that I entered. However, if I exit mysql and try logging in with the "bhaselto" username, it doesn't let me: [EMAIL PROTECTED] bhaselto]$ mysql -u bhaselto -p Enter password: [Here I type the password that I created above] ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) [EMAIL PROTECTED] bhaselto]$ As far as I can tell from reading http://www.mysql.com/doc/en/GRANT.html I followed the GRANT syntax correctly for creating a new user; why can't I connect to MySQL as that user? -Bennett [EMAIL PROTECTED] http://www.peacefire.org (425) 497 9002 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Temporary Table Problem - Help
Hi,
I'm running mysql version 4.0.13 and trying to create a temporary table
from a php script. For some reason, even though I have the permission
set up correctly, I can't seem to create a temporary table from the
script. The permission say that create_tmp_table_priv=Y for the user in
my connection code below. I have no problem creating the temp table with
the same sql syntax directly in the mysql command interface and no
problem creating a regular table using php?
The sql I'm trying to execute is in the following code"
$dblink= mysql_connect('localhost','pi', 'plant') or die ("Problem
with My SQL connection.\n") ;
mysql_select_db('plant_inventory') or die ("Problem with connection
to the database: plant_inventory.\n");
$sql = "CREATE temporary TABLE tmpInventory ";
$sql .= " SELECT * FROM inventory WHERE 1=0;";
$create_tmp = mysql_query($sql) or $err=("temp table not created");
Any ideas why this might be happening?
Anya Miretsky
Computer Technology Dept.
Brooklyn Botanic Garden
1000 Washington Avenue
Brooklyn, NY 11225
(718)623-7265
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sppeding up on ORed coloumns query
On Wed, Aug 13, 2003 at 12:58:06AM +0530, Binay Agarwal wrote: > Hi > > While using OR in the query, Mysql doesn't use indexes.. right? Hence takes more > time. > > If queries have to be ORed on various coloumns (demand of the application) what one > can do to speed it up. > > Table structure: > > CREATE TABLE npg_search ( >sno int(11) NOT NULL auto_increment, >fileid varchar(255) NOT NULL, >caption text, >headline varchar(255), >specialins varchar(255), >keywords varchar(255) NOT NULL, >cat varchar(50) NOT NULL, >byline varchar(255), >credit varchar(255), >source varchar(255), >date date, >city varchar(100), >state varchar(100), >country varchar(100) NOT NULL, >orgtransref varchar(100), >copyright varchar(255), >extratxt text, >prints tinyint(4), >extrastr varchar(255), >bylinetitle varchar(255), >addedon timestamp(14), >PRIMARY KEY (sno), >INDEX (fileid, keywords, cat, country)); > > As shown columns fileid,keywords,cat and country are indexed (not the composite > indexed). > > Query is: > > Select * from table where keywords like '%blah%' or caption like '%blah%' or > headline like '%blah%' or cat like '%blah%' limit 20,10 > > > Note: I can not use keywords like 'blah%' it has to be '%blah%' only... (i.e balh > can be even middle part of the words) > > Table contains 50,000 records and queries taking 7 sec which is unbelievable. > > Mysql version: 3.23.52 > > Please suggest me some solution for this. It's not the OR that's doing it. IT's the %blah% queries. Look at MySQL's FULLTEXT indexes. I suspect they'll help A LOT. -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! <[EMAIL PROTECTED]> | http://jeremy.zawodny.com/ MySQL 4.0.13: up 11 days, processed 391,895,882 queries (392/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Meaning of Column time in Show Processlist
On Tue, Aug 12, 2003 at 11:43:50AM +0200, Mechain Marc wrote: > > I would like to know (if possible, there is no explanation in the > documentation) the exact meanning of the column "time" in the "show > processlist" command. It's the amount of time that thread has been in its currnet state. -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! <[EMAIL PROTECTED]> | http://jeremy.zawodny.com/ MySQL 4.0.13: up 11 days, processed 368,692,693 queries (386/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Reading table structure and data from sql file.
Thanks Nils Binay - Original Message - From: "Nils Valentin" <[EMAIL PROTECTED]> To: "Binay Agarwal" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Wednesday, August 13, 2003 12:24 PM Subject: Re: Reading table structure and data from sql file. > Hi Binary, > > I see you want to use the original backup file, but just restore a part of > it. > > Unfortunately mysqlimport doesnt work that way. It must have the tables name > as filename. > > I also don't remember havin seen that option with the mysql client. > > All I found was "-o, --one-database" which does what is says only one database > will be updated (default database). > > Seems like for now editing the backup file is the fastest solution (on a > command line). > > Best regards > > Nils Valentin > Tokyo/Japan > > > > 2003年 8月 13日 水曜日 03:54、Binay Agarwal さんは書きました: > > Hi Nils , > > > > Thanks for quick reply. > > > > I know already about the selective Backup using mysqldump and then use it. > > But what i want is restoring the selective backup i.e restore just one > > table out of say 10 tabels structure and data. > > > > Binay > > > > > > > > - Original Message - > > From: "Nils Valentin" <[EMAIL PROTECTED]> > > To: "Binay Agarwal" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> > > Sent: Wednesday, August 13, 2003 11:59 AM > > Subject: Re: Reading table structure and data from sql file. > > > > > Hi Binary, > > > > > > You could either make a new backup of only the table (see mysqldump > > > > manual). > > > > > mysqldump --help > > > > > > Or you edit the backup file you have directly and save it under a new > > > name > > > > and > > > > > use it. > > > > > > > > > Best regards > > > > > > Nils Valentin > > > Tokyo/Japan > > > > > > 2003年 8月 13日 水曜日 03:13、Binay Agarwal さんは書きました: > > > > Hi everybody, > > > > > > > >I have backed(backup.sql) up one Mysql database which contains > > > > 10 tables using mysqldump. > > > > > > > > Now I want to read just one table structure and it's data from > > > > backup.sql > > > > > > to another database. How do i achieve it. > > > > > > > > I have tried "mysql databasename < backup.sql" but it will populate the > > > > databasename with all the 10 tables present in backup.sql which i don > > > > want. > > > > > > Please help me out. > > > > > > > > Mysql verison : 3.23.52 > > > > > > -- > > > --- > > > Valentin Nils > > > Internet Technology > > > > > > E-Mail: [EMAIL PROTECTED] > > > URL: http://www.knowd.co.jp > > > Personal URL: http://www.knowd.co.jp/staff/nils > > > > > > > > > -- > > > MySQL General Mailing List > > > For list archives: http://lists.mysql.com/mysql > > > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > -- > --- > Valentin Nils > Internet Technology > > E-Mail: [EMAIL PROTECTED] > URL: http://www.knowd.co.jp > Personal URL: http://www.knowd.co.jp/staff/nils > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Sppeding up on ORed coloumns query
Hi While using OR in the query, Mysql doesn't use indexes.. right? Hence takes more time. If queries have to be ORed on various coloumns (demand of the application) what one can do to speed it up. Table structure: CREATE TABLE npg_search ( sno int(11) NOT NULL auto_increment, fileid varchar(255) NOT NULL, caption text, headline varchar(255), specialins varchar(255), keywords varchar(255) NOT NULL, cat varchar(50) NOT NULL, byline varchar(255), credit varchar(255), source varchar(255), date date, city varchar(100), state varchar(100), country varchar(100) NOT NULL, orgtransref varchar(100), copyright varchar(255), extratxt text, prints tinyint(4), extrastr varchar(255), bylinetitle varchar(255), addedon timestamp(14), PRIMARY KEY (sno), INDEX (fileid, keywords, cat, country)); As shown columns fileid,keywords,cat and country are indexed (not the composite indexed). Query is: Select * from table where keywords like '%blah%' or caption like '%blah%' or headline like '%blah%' or cat like '%blah%' limit 20,10 Note: I can not use keywords like 'blah%' it has to be '%blah%' only... (i.e balh can be even middle part of the words) Table contains 50,000 records and queries taking 7 sec which is unbelievable. Mysql version: 3.23.52 Please suggest me some solution for this. Thanks in advance Binay
MySQL 4.0.14 & mysql_create_db()... II
Hello again... I opened mysql.h and found this... --- #ifdef USE_OLD_FUNCTIONS MYSQL * STDCALL mysql_connect(MYSQL *mysql, const char *host, const char *user, const char *passwd); int STDCALL mysql_create_db(MYSQL *mysql, const char *DB); int STDCALL mysql_drop_db(MYSQL *mysql, const char *DB); #define mysql_reload(mysql) mysql_refresh((mysql),REFRESH_GRANT) #endif --- why "old functions"? should I use new ones instead? I #define'd USE_OLD_FUNCTIONS before #include'ing mysql.h and now compiles fine, but I think it is not the best way... any comments or suggestions? thnx! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.0.14 & mysql_create_db()... II
hello
for mysql_create_db() use mysql_query() and give an
SQL Statement ("CREATE DATABASE XYZ") as an argument.
the same for mysql_drop_db ("DROP DATABASE").
for mysql_connect: use mysql_real_connect.
the same is valid if you are using the Ruby MySQL
Modul. but it is very easy to extend the Ruby MySQL
class with a create_db function
require "mysql"
class Mysql
def create_db(db)
query("CREATE DATABASE #{db}")
end
end
see
http://www.rubywizard.net/ruby-mysql.html
the Ruby API is similar to the C Api, but developmet
time is faster. so if you do not have to use C maybe
you should consider Ruby. there is also Ruby/DBI
ruby-dbi.sf.net
Markus
--
- Miguel Cardenas <[EMAIL PROTECTED]> schrieb: >
Hello again...
>
> I opened mysql.h and found this...
>
> ---
> #ifdef USE_OLD_FUNCTIONS
> MYSQL * STDCALL mysql_connect(MYSQL *mysql, const
> char *host,
> const char *user, const char *passwd);
> int STDCALL mysql_create_db(MYSQL *mysql, const
> char *DB);
> int STDCALL mysql_drop_db(MYSQL *mysql, const char
> *DB);
> #definemysql_reload(mysql)
> mysql_refresh((mysql),REFRESH_GRANT)
> #endif
> ---
>
> why "old functions"? should I use new ones instead?
> I #define'd
> USE_OLD_FUNCTIONS before #include'ing mysql.h and
> now compiles fine, but I
> think it is not the best way...
>
> any comments or suggestions?
>
> thnx!
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
>
http://lists.mysql.com/[EMAIL PROTECTED]
>
__
Gesendet von Yahoo! Mail - http://mail.yahoo.de
Logos und Klingeltöne fürs Handy bei http://sms.yahoo.de
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Multiple items in an ALTER TABLE statement
ALTER TABLE tmp DROP COLUMN col_1, DROP COLUMN col_2, DROP COLUMN col_3, DROP COLUMN col_4; Or, for short, ALTER TABLE tmp DROP col_1, DROP col_2, DROP col_3, DROP col_4; > -Original Message- > From: Adam Fortuno [mailto:[EMAIL PROTECTED] > Sent: Tuesday, August 12, 2003 6:22 PM > To: MySQL List > Subject: Multiple items in an ALTER TABLE statement > > > Was in the midst of doing something today and I attempted to drop a > number of columns in a table with the following: > > ALTER TABLE tmp DROP COLUMN col_1, col_2, col_3, col_4; > > Unfortunately MySQL gave me an error reading: > > ERROR 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 'col_2, col_3, col_4' at line 1 > > Can you not have multiple columns names in an alter statement? > > > -- > 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: MySQL 4.0.14 & mysql_create_db(), mysql_drop_db()
At 00:47 13/8/2003 +, Miguel Cardenas wrote:
Hi,
Hello list...
Am developing an application with mysql support and it works fine, but today
changed from 3.23 (deinstalled completely) to 4.0.14 and when recompiling my
program get the following errors:
---
/* mysql_v.cpp */
#include "mysql_v.h"
...
int MYSQL_V::CreateDatabase (char* database) {
return mysql_create_db(&sql_handle,database);
}
int MYSQL_V::DropDatabase (char* database) {
return mysql_drop_db(&sql_handle,database);
}
The above function is deprecated and if you want to use it
use the define USE_OLD_FUNCTIONS:
/include/mysql.h
#ifdef USE_OLD_FUNCTIONS
MYSQL * STDCALL mysql_connect(MYSQL *mysql, const char *host,
const char *user, const char *passwd);
int STDCALL mysql_create_db(MYSQL *mysql, const char *DB);
int STDCALL mysql_drop_db(MYSQL *mysql, const char *DB);
#define mysql_reload(mysql) mysql_refresh((mysql),REFRESH_GRANT)
#endif
--
Regards,
For technical support contracts, visit https://order.mysql.com/
Are you MySQL certified?, http://www.mysql.com/certification/
Miguel Angel Solórzano <[EMAIL PROTECTED]>
São Paulo - Brazil
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Joins and Unions
Hi Kim, You can create a temporary table by using a table alias. The temporary table will only be accessable by the current connection. Any other connection will not be able to use it. If you want to make a table which can be used by several connections (f.e for a join) you could create a HEAP table which remains completely in the memory, but all data goes when the server crashes or powered off, the structure remains. Best regards Nils Valentin Tokyo/Japan 2003年 8月 13日 水曜日 15:21、あなたは書きました: > G'day Nils > > > just send a reply a minute ago. I got it now. You want to change the way > > the data is presented at the screen and thats a different issue. You > > would have to make this in your apllication, not with mysql itself I > > believe. You basically want the DISTINCT function within the JOINS - but > > only applied for a certain column. > > Thats not possibe yet I believe. > > > Ah, ok, now where getting somewhere:) > > I had already tried Distinct but as you say, it can't really work with the > joins I have currently. I'm looking to see if it's possible to simply set > the displayed columns (I'm using PHP) of the redundant stuff to "". > > Which leads me to another question. I've never used temporary tables. Would > it be possible for me to do a join and form a temporary table from it? If > that's possible, I could get all the data I need for a basic Union and I'd > be happy - I think:) > > cheers > > Kim -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb on delete cascade
"R.Dobson" <[EMAIL PROTECTED]> wrote: > Hi, I have a db where I have converted all the tables innodb. I have 2 tables in > particular called gene and name. > > They both have a primary key call id. I want the primary key from name to be deleted > when the corresponding key is deleted from gene. > > It doesn't seem to be happening as yet! > > show columns from name; > +-+---+--+-+-++ > | Field | Type | Null | Key | Default | Extra > | > +-+---+--+-+-++ > | id | mediumint(8) unsigned | | PRI | NULL| > auto_increment | > | other_name | varchar(100) | | | | > | > | other_symbol| varchar(100) | | | | > | > | refseq_ID | varchar(20) | YES | | NULL| > | > | GO | varchar(20) | YES | | NULL| > | > | locus_link | varchar(20) | YES | | NULL| > | > | other_species_index | varchar(20) | YES | | NULL| > | > +-+---+--+-+-++ > 7 rows in set (0.00 sec) > > mysql> show columns from gene; > +-+---+--+-+-++ > | Field | Type | Null | Key | Default | Extra | > +-+---+--+-+-++ > | id | mediumint(8) unsigned | | PRI | NULL| auto_increment | > | name| varchar(100) | | | || > | species | varchar(100) | | | || > +-+---+--+-+-++ > 3 rows in set (0.00 sec) > > > mysql>alter table name add foreign key(id) references gene(id) on delete cascade; > > mysql> select * from gene; > ++--+-+ > | id | name | species | > ++--+-+ > | 9 | hi | human | > ++--+-+ > 1 row in set (0.00 sec) > > mysql> select * from name; > +++--+---+--++-+ > | id | other_name | other_symbol | refseq_ID | GO | locus_link | > other_species_index | > +++--+---+--++-+ > | 9 | hi | human| i | i| i | i > | > +++--+---+--++-+ > 1 row in set (0.00 sec) > > mysql> delete from gene where id=9; > Query OK, 1 row affected (0.00 sec) > > mysql> select * from name; > +++--+---+--++-+ > | id | other_name | other_symbol | refseq_ID | GO | locus_link | > other_species_index | > +++--+---+--++-+ > | 9 | hi | human| i | i| i | i > | > +++--+---+--++-+ > 1 row in set (0.00 sec) > > mysql> select * from gene; > Empty set (0.00 sec) > > The entry from name should be deleted as well? Check if table types are InnoDB with SHOW CREATE TABLE or SHOW TABLE STATUS statements. -- 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: Transferring database from one PC to other
asif mahmood wrote: hi, this is my first question on list, well i m using MySQL 4.1 and i m new user of MySQL.i want to tranfer my database from one computer to other. i want to shift the whole database to other system. so if any one can help me regarding this. if there is any script or program then plz let me know. please help me . you just have to move whole database folder f.e. /var/lib/mysql -- Sebastian Mendel www.sebastianmendel.de www.tekkno4u.de www.nofetish.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Cant login as a user I thought I created
> >Delete anonymous user('') from table user. Don't forget about FLUSH
PRIVILEGES.
> >
> Thanks, that worked. Why?
I suggested this yesterday, at the very beginning of Mark's
question, and I also suggested it again today in a private message, but both
of my suggestions got ignored, I guess. Keep your eyes open next time, Mark.
- Jonathan
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Joins and Unions
G'day Nils > just send a reply a minute ago. I got it now. You want to change the way the > data is presented at the screen and thats a different issue. You would have > to make this in your apllication, not with mysql itself I believe. > You basically want the DISTINCT function within the JOINS - but only applied > for a certain column. > Thats not possibe yet I believe. Ah, ok, now where getting somewhere:) I had already tried Distinct but as you say, it can't really work with the joins I have currently. I'm looking to see if it's possible to simply set the displayed columns (I'm using PHP) of the redundant stuff to "". Which leads me to another question. I've never used temporary tables. Would it be possible for me to do a join and form a temporary table from it? If that's possible, I could get all the data I need for a basic Union and I'd be happy - I think:) cheers kim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Joins and Unions
Hi Daniel, I am still not sure what exactly you are trying to achieve. If you specify the WHERE condition correctly (or ON or USING for the LEFT JOIN) then there is no duplicate entry. I believe what you are asking has more to do with the way how to present the data in the output. Best regards Nils Valentin Tokyo/Japan 2003年 8月 13日 水曜日 14:[EMAIL PROTECTED] さんは書きました: > well i'd like to join tables without duplicates, currently i cannot do this > unless i select the category table in a second loop to list all the > categories for that particular row/record > > > Hi Daniel and Kim, > > > > Isn't > > > >> product_name product_category > >> product_name product_category > >> product_name product_category > >> product_name product_category > >> product_name product_category > > > > the way its supposed to be ? > > > > You are using the LEFT JOIN syntax, but actually using it just as a > > JOIN. > > > > You could also use something like > > > > SELECT * FROM products p, product_cat_join,product_category pc WHERE > > p.productID=pcj.productID AND pc.catID=pcj.catID; > > > > if I remember that correctly. This does exactly the same as the LEFT > > JOIN above. > > > > A LEFT JOIN is normally used to find records which exits in one table, > > BUT NOT in the other table. > > > > So for example: > > > > SELECT * FROM products p LEFT JOIN product_cat_join pcj ON > > p.productID=pcj.productID LEFT JOIN product_category pc ON > > pc.catID=pcj.catID WHERE pc.catID IS NULL > > > > > > Anybody: Please correct me if I talk rubbish here ;-) > > > > > > Best regards > > > > Nils Valentin > > Tokyo/Japan > > > > > > 2003å¹´ 8月 13æ—¥ 水曜日 13:[EMAIL PROTECTED] > > > > ã•ã‚“ã¯æ›¸ãã¾ã—ãŸ: > >> he is getting the same problem as me as i explained > >> > >> i use joins left join and inner join > >> > >> SELECT * FROM products p LEFT JOIN product_cat_join pcj ON > >> p.productID=pcj.productID LEFT JOIN product_category pc ON > >> pc.catID=pcj.catID; > >> > >> products > >> productID > >> product > >> > >> product_cat_join > >> joinID > >> productID > >> catID > >> > >> product_category > >> catID > >> product_category > >> > >> so say there are 5 records of a product category against a product it > >> would result in > >> > >> product_name product_category > >> product_name product_category > >> product_name product_category > >> product_name product_category > >> product_name product_category > >> > >> instead of > >> > >> product_name product_category > >> product_category > >> product_category > >> product_category > >> product_category > >> > >> > Hi Kim, > >> > > >> > no offense, but are you actually using the JOIN syntax correctly ? > >> > > >> > Could you post your query please ? > >> > > >> > My guess is you are doing a JOIN without any set conditions. hat > >> > would give you the result as described (which is normal). You would > >> > use the WHERE clause or for a LEFT JOIN ...the ON clause to filter > >> > to correct data which you are requesting. > >> > > >> > Please have a look once more at the JOIN syntax: > >> > > >> > http://www.mysql.com/doc/en/JOIN.html > >> > > >> > Some examples: > >> > > >> > mysql> SELECT * FROM table1,table2 WHERE table1.id=table2.id; > >> > mysql> SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id; > >> > mysql> SELECT * FROM table1 LEFT JOIN table2 USING (id); > >> > > >> > Best regards > >> > > >> > Nils Valentin > >> > Tokyo/Japan > >> > > >> > > >> > 2003å¹´ 8月 13日 > >> > 水曜日 09:25ã€ÂKim Kohen > >> > > >> > ã•んã¯書ãÂÂã¾ã—ãŸ: > >> >> G'day all, > >> >> > >> >> I'm pretty new to MySQL and very new to v4. > >> >> > >> >> This is probably going to sound strange, but is it possible to > >> >> create a union using the logic of a join? I have 4 tables and I > >> >> need to join data from them, but I'd like to display them like a > >> >> Union. If I do a straight join, I get lots of multiple entries > >> >> thus: > >> >> > >> >> PageAd_num status stories > >> >> 14312-T1308 Ad Complete 1-marchingband-A33Placed 1 > >> >> 4312-T1308 Ad Complete 1-jobs-T33Placed > >> >> 1 > >> >> 4312-T1308 Ad Complete 1-illegalbuild-A32 Placed > >> >> 1 4312-T1308 Ad Complete 1-haldonstfesty-T33 Placed 1 > >> >> 4312-T1308 Ad Complete 1-jobs-T33 > >> >> Corrected 14312-T1308 Ad Complete 1-car crash-T33 > >> >> Placed > >> >> > >> >> Where ad_num "14312-T1308 Ad Complete" is replicated to > >> >> accommodate all the Joined data from the 'stories' table > >> >> > >> >> What I'd like to see is something like this: > >> >> > >> >> 14312-T1308 Ad Complete > >> >> 11-marchingband-A33Placed > >> >> 11-jobs-T33Placed > >> >> 11-jobs-T33Corrected > >> >> 1
Re: Advanced text search
[EMAIL PROTECTED] wrote:
> select compid,company,phone from companies where MATCH (phone) AGAINST
> ('+27-21-*', IN BOOLEAN MODE); something like that
'something like that' triggered me ... try
select compid,company,phone from companies where phone LIKE '+27-21-%';
IIRC LIKE will, as a bonus, use your index this time.
HansH
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Joins and Unions
Hi Daniel and Kim, Isn't > product_name product_category > product_name product_category > product_name product_category > product_name product_category > product_name product_category the way its supposed to be ? You are using the LEFT JOIN syntax, but actually using it just as a JOIN. You could also use something like SELECT * FROM products p, product_cat_join,product_category pc WHERE p.productID=pcj.productID AND pc.catID=pcj.catID; if I remember that correctly. This does exactly the same as the LEFT JOIN above. A LEFT JOIN is normally used to find records which exits in one table, BUT NOT in the other table. So for example: SELECT * FROM products p LEFT JOIN product_cat_join pcj ON p.productID=pcj.productID LEFT JOIN product_category pc ON pc.catID=pcj.catID WHERE pc.catID IS NULL Anybody: Please correct me if I talk rubbish here ;-) Best regards Nils Valentin Tokyo/Japan 2003年 8月 13日 水曜日 13:[EMAIL PROTECTED] さんは書きました: > he is getting the same problem as me as i explained > > i use joins left join and inner join > > SELECT * FROM products p LEFT JOIN product_cat_join pcj ON > p.productID=pcj.productID LEFT JOIN product_category pc ON > pc.catID=pcj.catID; > > products > productID > product > > product_cat_join > joinID > productID > catID > > product_category > catID > product_category > > so say there are 5 records of a product category against a product it would > result in > > product_name product_category > product_name product_category > product_name product_category > product_name product_category > product_name product_category > > instead of > > product_name product_category > product_category > product_category > product_category > product_category > > > Hi Kim, > > > > no offense, but are you actually using the JOIN syntax correctly ? > > > > Could you post your query please ? > > > > My guess is you are doing a JOIN without any set conditions. hat would > > give you the result as described (which is normal). You would use the > > WHERE clause or for a LEFT JOIN ...the ON clause to filter to correct > > data which you are requesting. > > > > Please have a look once more at the JOIN syntax: > > > > http://www.mysql.com/doc/en/JOIN.html > > > > Some examples: > > > > mysql> SELECT * FROM table1,table2 WHERE table1.id=table2.id; > > mysql> SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id; > > mysql> SELECT * FROM table1 LEFT JOIN table2 USING (id); > > > > Best regards > > > > Nils Valentin > > Tokyo/Japan > > > > > > 2003å¹´ 8月 13æ—¥ 水曜日 09:25ã€Kim Kohen > > > > ã•ã‚“ã¯æ›¸ãã¾ã—ãŸ: > >> G'day all, > >> > >> I'm pretty new to MySQL and very new to v4. > >> > >> This is probably going to sound strange, but is it possible to create > >> a union using the logic of a join? I have 4 tables and I need to join > >> data from them, but I'd like to display them like a Union. If I do a > >> straight join, I get lots of multiple entries thus: > >> > >> PageAd_num status stories > >> 14312-T1308 Ad Complete 1-marchingband-A33Placed 1 > >> 4312-T1308 Ad Complete 1-jobs-T33Placed 1 > >> 4312-T1308 Ad Complete 1-illegalbuild-A32 Placed 1 > >> 4312-T1308 Ad Complete 1-haldonstfesty-T33 Placed 1 > >> 4312-T1308 Ad Complete 1-jobs-T33Corrected > >> 14312-T1308 Ad Complete 1-car crash-T33 Placed > >> > >> Where ad_num "14312-T1308 Ad Complete" is replicated to > >> accommodate all the Joined data from the 'stories' table > >> > >> What I'd like to see is something like this: > >> > >> 14312-T1308 Ad Complete > >> 11-marchingband-A33Placed > >> 11-jobs-T33Placed > >> 11-jobs-T33Corrected > >> 11-illegalbuild-A32 Placed > >> 11-haldonstfesty-T33 Placed > >> 11-car crash-T33Placed > >> > >> Is this sort of thing possible? > >> > >> cheers > >> > >> kim > > > > -- > > --- > > Valentin Nils > > Internet Technology > > > > E-Mail: [EMAIL PROTECTED] > > URL: http://www.knowd.co.jp > > Personal URL: http://www.knowd.co.jp/staff/nils > > > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: > > http://lists.mysql.com/[EMAIL PROTECTED] -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Joins and Unions
he is getting the same problem as me as i explained i use joins left join and inner join SELECT * FROM products p LEFT JOIN product_cat_join pcj ON p.productID=pcj.productID LEFT JOIN product_category pc ON pc.catID=pcj.catID; products productID product product_cat_join joinID productID catID product_category catID product_category so say there are 5 records of a product category against a product it would result in product_name product_category product_name product_category product_name product_category product_name product_category product_name product_category instead of product_name product_category product_category product_category product_category product_category > Hi Kim, > > no offense, but are you actually using the JOIN syntax correctly ? > > Could you post your query please ? > > My guess is you are doing a JOIN without any set conditions. hat would > give you the result as described (which is normal). You would use the > WHERE clause or for a LEFT JOIN ...the ON clause to filter to correct > data which you are requesting. > > Please have a look once more at the JOIN syntax: > > http://www.mysql.com/doc/en/JOIN.html > > Some examples: > > mysql> SELECT * FROM table1,table2 WHERE table1.id=table2.id; > mysql> SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id; > mysql> SELECT * FROM table1 LEFT JOIN table2 USING (id); > > Best regards > > Nils Valentin > Tokyo/Japan > > > 2003年 8月 13日 水曜日 09:25、Kim Kohen > さんは書きました: >> G'day all, >> >> I'm pretty new to MySQL and very new to v4. >> >> This is probably going to sound strange, but is it possible to create >> a union using the logic of a join? I have 4 tables and I need to join >> data from them, but I'd like to display them like a Union. If I do a >> straight join, I get lots of multiple entries thus: >> >> PageAd_num status stories >> 14312-T1308 Ad Complete 1-marchingband-A33Placed 1 >> 4312-T1308 Ad Complete 1-jobs-T33Placed 1 >> 4312-T1308 Ad Complete 1-illegalbuild-A32 Placed 1 >> 4312-T1308 Ad Complete 1-haldonstfesty-T33 Placed 1 >> 4312-T1308 Ad Complete 1-jobs-T33Corrected >> 14312-T1308 Ad Complete 1-car crash-T33 Placed >> >> Where ad_num "14312-T1308 Ad Complete" is replicated to >> accommodate all the Joined data from the 'stories' table >> >> What I'd like to see is something like this: >> >> 14312-T1308 Ad Complete >> 11-marchingband-A33Placed >> 11-jobs-T33Placed >> 11-jobs-T33Corrected >> 11-illegalbuild-A32 Placed >> 11-haldonstfesty-T33 Placed >> 11-car crash-T33Placed >> >> Is this sort of thing possible? >> >> cheers >> >> kim > > -- > --- > Valentin Nils > Internet Technology > > E-Mail: [EMAIL PROTECTED] > URL: http://www.knowd.co.jp > Personal URL: http://www.knowd.co.jp/staff/nils > > > -- > 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: Transferring database from one PC to other
asif mahmood <[EMAIL PROTECTED]> wrote: > hi, this is my first question on list, well i m using > MySQL 4.1 and i m new user of MySQL.i want to tranfer > my database from one computer to other. i want to > shift the whole database to other system. so if any > one can help me regarding this. if there is any script > or program then plz let me know. please help me . Use mysqldump program: http://www.mysql.com/doc/en/mysqldump.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]
replication blues
Hi guys,
I've got a weirdo problem with replicating a database. Sometimes I get
some duplicate keys problems for _only_ one table. There is nothing
special about this table, it looks like:
+--+--+--+-+-++
| Field| Type | Null | Key | Default |
Extra |
+--+--+--+-+-++
| id | int(10) unsigned | | PRI | NULL|
auto_increment |
| dialer | int(10) unsigned | YES | | NULL|
|
| uid | int(10) unsigned | | | 0 |
|
| action | enum('REGENERATE','CREATE','DELETE') | YES | | NULL|
|
| acc_no | int(10) unsigned | YES | | NULL|
|
| template | int(10) unsigned | YES | | NULL|
|
| name | varchar(20) | YES | | NULL|
|
| status | enum('OKAY','ERROR') | | | OKAY|
|
+--+--+--+-+-++
And data is inserted into it with simple inserts, w/o specifing the id
(it's autoincrementing).
With a little debugging, I have located the problem. If I run 'alter
table xxx auto_increment=1' on both the master and the slave (this table
is empty at the time on both machines), and then I insert datas into the
master, they look like:
On master:
+++--+++--+---++
| 1 | 3 | 1007 | REGENERATE | NULL | NULL | NULL | OKAY |
| 2 | 4 | 1007 | REGENERATE | NULL | NULL | NULL | OKAY |
| 3 | 5 | 1007 | REGENERATE | NULL | NULL | NULL | OKAY |
| 4 | 6 | 1007 | REGENERATE | NULL | NULL | NULL | OKAY |
| 5 | 13 | 1007 | REGENERATE | NULL | NULL | NULL | OKAY |
| 6 | 14 | 1007 | REGENERATE | NULL | NULL | NULL | OKAY |
| 7 | 18 | 1007 | REGENERATE | NULL | NULL | NULL | OKAY |
| 8 | 19 | 1007 | REGENERATE | NULL | NULL | NULL | OKAY |
| 9 | 20 | 1007 | REGENERATE | NULL | NULL | NULL | OKAY |
| 10 | 21 | 1007 | REGENERATE | NULL | NULL | NULL | OKAY |
+++--+++--+--++
But on slave it looks like:
+++--+++--+--++
| id | dialer | uid | action | acc_no | template | name | status |
+++--+++--+--++
| 10 | 3 | 1007 | REGENERATE | NULL | NULL | NULL | OKAY |
| 11 | 4 | 1007 | REGENERATE | NULL | NULL | NULL | OKAY |
| 12 | 5 | 1007 | REGENERATE | NULL | NULL | NULL | OKAY |
| 13 | 6 | 1007 | REGENERATE | NULL | NULL | NULL | OKAY |
| 14 | 13 | 1007 | REGENERATE | NULL | NULL | NULL | OKAY |
| 15 | 14 | 1007 | REGENERATE | NULL | NULL | NULL | OKAY |
| 16 | 18 | 1007 | REGENERATE | NULL | NULL | NULL | OKAY |
| 17 | 19 | 1007 | REGENERATE | NULL | NULL | NULL | OKAY |
| 18 | 20 | 1007 | REGENERATE | NULL | NULL | NULL | OKAY |
| 19 | 21 | 1007 | REGENERATE | NULL | NULL | NULL | OKAY |
+++--+++--+--++
Why does it start on the id=10 on the slave? Of course, this is the
cause for the replication failures later on, because datas are deleted on
the master with 'delete from xxx where id=3', for example, action which
doesn't delete anything on the slave (because there is no id=3 entry),
thus inconsistency.
I'm using 4.0.13 on both machines.
Thanks,
bogdan
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQLDriverCS Error: can't connect ON MYSQL
All I am having an issue in using your dbProvider with ASP.NET on the Framework 1.1 Each time I make roughly 20 odd DB trips I get the following error and cannot seem to find the solution on the internet. Maybe you have come across this before. If you have would you mind passing on the solution to me Best Regards Aidan Gill Server Error in '/CSAssetRegister' Application. MySQLDriverCS Exception: MySQLDriverCS Error: can't connect. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: MySQLDriverCS.MySQLException: MySQLDriverCS Exception: MySQLDriverCS Error: can't connect. Source Error: Line 96: Dim myCommand As New MySQLCommand(query, myConn) Line 97: Line 98: myConn.Open() Line 99: Line 100:Dim myReader As MySQLDataReader = myCommand.ExecuteReader() Source File: c:\inetpub\wwwroot\CSAssetRegister\Components\BLL\Sub-Locations.vb Line: 98 Stack Trace: [MySQLException: MySQLDriverCS Exception: MySQLDriverCS Error: can't connect.] MySQLDriverCS.MySQLConnection.Open() CSAssetRegister.CSAssetRegister.Sub_LocationDB.GetSub_Locations(Int32 intSiteID, Int32 intLocID) in c:\inetpub\wwwroot\CSAssetRegister\Components\BLL\Sub-Locations.vb:98 CSAssetRegister.C_Menu.Page_Load(Object sender, EventArgs e) in c:\inetpub\wwwroot\CSAssetRegister\_Menu.ascx.vb:117 System.Web.UI.Control.OnLoad(EventArgs e) System.Web.UI.Control.LoadRecursive() System.Web.UI.Control.LoadRecursive() System.Web.UI.Control.LoadRecursive() System.Web.UI.Page.ProcessRequestMain() Version Information: Microsoft .NET Framework Version:1.1.4322.573; ASP.NET Version:1.1.4322.573 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
