Counting from diffrent tables, problem

I had wroted query SELECT r.`id`, COUNT(s.`id`) AS `sk` FROM `sodybos` as s RIGHT OUTER JOIN `rajonai` as r ON (s.`rajonai_id` = r.`id`) GROUP BY r.`id` But this query run's slow, now I want to optimize it, maybe write it without JOIN command If I wrote new query (it's more faster) select

Re: InnoDB speed problems

Matthias, if you can tolerate losing a few last transactions in a power outage or an OS crash, you can set innodb_flush_log_at_trx_commit=2 Have you shut down mysqld and restarted it after populating the tables? MySQL only updates index cardinality statistics when you run ANALYZE TABLE or

Re: can NOT drop the database

Feng, do you have some non-MySQL file in that database directory under the datadir of MySQL? Naturally, MySQL will not drop the directory if it contains something more than just MySQL tables. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level

Re: can NOT drop the database

Awesome I did create some text files in that database directory few days ago. And you are right, that does the trick. After I moved those text files, the *drop* works!!! :-) thanks Heikki. BTW, how can I rename a database? feng - Original Message - From: Heikki Tuuri [EMAIL

Re: can NOT drop the database

Wang, - Original Message - From: Wang Feng [EMAIL PROTECTED] To: Heikki Tuuri [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, October 01, 2003 10:30 AM Subject: Re: can NOT drop the database Awesome I did create some text files in that database directory few days ago. And

Can't connect to MySQL from remote

I have some problems with MySQL 3.23.52 on RedHat Linux 8.0. I can connect to mysql when I am on the same machine but I can't connect to it from another machine. Here is the error I get: mysqladmin -u cedric -h gdvi02 version mysqladmin: connect to server at 'gdvi02' failed error: 'Lost

Select statement to get the difference

How to tell it in MySQL I have 2 tables and i want to find difference (the id exists in one table but in the other not) between them. With SELECT attachment_id from tbl_attachment, tbl_msg2atta where attachment_id = atta_id i receive all id wich are equals in both tables. I need to know

Re: Select statement to get the difference

On Wed, 1 Oct 2003 10:56:59 +0200 Stefan Berger [EMAIL PROTECTED] wrote: I have 2 tables and i want to find difference (the id exists in one table but in the other not) between them. With SELECT attachment_id from tbl_attachment, tbl_msg2atta where attachment_id = atta_id i receive

MySQL sorts Norwegian/Scandinavian Characters wrong

Hi, When I try to sort the alphabet, the three characters only used for the norwegian language, Æ, Ø and Å, are sorted wrong. They should be sorted in the order ÆØÅ, but they're sorted ÅÆØ. I read some place that I could change the character-set to danish, and that that would solve the issue (as

Re: Counting from diffrent tables, problem

Vaidas Zilionis aka Why2liz [EMAIL PROTECTED] wrote: I had wroted query SELECT r.`id`, COUNT(s.`id`) AS `sk` FROM `sodybos` as s RIGHT OUTER JOIN `rajonai` as r ON (s.`rajonai_id` = r.`id`) GROUP BY r.`id` But this query run's slow, now I want to optimize it, maybe write it without JOIN

Re: Select statement to get the difference

Stefan Berger escribió: How to tell it in MySQL I have 2 tables and i want to find difference (the id exists in one table but in the other not) between them. With SELECT attachment_id from tbl_attachment, tbl_msg2atta where attachment_id = atta_id i receive all id wich are equals in both

How to specify --local-infile[=1]?

The manual tells that the option LOAD LOCAL INFIL can be specified at the command line client by setting the --local-infile[=1] -flag. How do I do that? Sincerely Victor -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:

Re: InnoDB speed problems

Heikki, if you can tolerate losing a few last transactions in a power outage or an OS crash, you can set innodb_flush_log_at_trx_commit=2 Does that speed up the thing? I should make some testing. Have you shut down mysqld and restarted it after populating the tables? MySQL only updates

Re: How to specify --local-infile[=1]?

Victor Sp?ng Arthursson [EMAIL PROTECTED] wrote: The manual tells that the option LOAD LOCAL INFIL can be specified at the command line client by setting the --local-infile[=1] -flag. How do I do that? You can put local-infile in the my.cnf: [mysqld] local-infile ... [mysql] local-infile

Fw: Problem deleting data

- Original Message - From: Andrew Pattison [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, October 01, 2003 12:02 PM Subject: Problem deleting data I have a strange problem with MySQL 4.0.15 . When I delete an entire table, then run a repair on that table, it recovers all

Re: can NOT drop the database

Hi Heikki, a similar problem happened to me and I got the error: ERROR 1051: Unknown table '#sql-ffa_2,#sql-2b2_30' After reading your post, I checked in the data directory and there are two files there, both of file type data: #sql-2b2_30.frm #sql-ffa_2.frm I certainly didn't put them there

Re: NOT problems

Here's my schema and data: create table person (name char(5)); insert into person values ('Bob'); insert into person values ('Jane'); In mySQL 4.1-alpha, 4.0.15a, and 3.23.58, I get the following results: mysql SELECT * FROM person WHERE NOT name = 'Bob'; Empty set (0.00 sec)

Creating indeces with Innodb

Hi Somebody knows what are the buffers that MySQL uses when creating indeces in InnoDB tables? I increased the sort buffer, the tmp_table_size, the buffer_pool, the buffer_log, the log_file but i didn't have performance increase... Anyone have this problem too? What buffers are used? I'm

Rolling back DDL statements

Hi all, I was wondering, would there be any point at all in being able to have DDL statements as part of a transaction? There is one database I know of that has this as a big selling point. Would any readers of the list be able to find a use for such an animal? Regards, Chris -- MySQL

[Fwd: SHOW FIELDS FROM myTable]

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 This belongs on the [EMAIL PROTECTED] mailing list. Thanks! -Mark - Original Message Subject:SHOW FIELDS FROM myTable Date: Tue, 30 Sep 2003 22:46:00 -0500 From: Mike Griffin [EMAIL PROTECTED] To: [EMAIL

Sql question

Mysql 3.. I can't figure this one out I need to move data from one mysql table to another The hurdle for me is adding additional column values. Here is what I have. insert into mytable (column1, column 2, column3) (Select thiscolumn From anotherTable), '1', now(); It's the 1 and the

Re: Can't connect to MySQL from remote

[EMAIL PROTECTED] wrote: I have some problems with MySQL 3.23.52 on RedHat Linux 8.0. I can connect to mysql when I am on the same machine but I can't connect to it from another machine. Here is the error I get: mysqladmin -u cedric -h gdvi02 version mysqladmin: connect to server at

Re: can NOT drop the database

Adam, you can use the innodb_table_monitor http://www.innodb.com/ibman.html#InnoDB_Monitor and the advice at http://www.innodb.com/ibman.html#InnoDB_troubleshooting_dict to resolve the problem. Best regards, Heikki Innobase Oy http://www.innodb.com InnoDB - transactions, row level locking,

Re: can NOT drop the database

Feng, - Alkuperinen viesti - Lhettj: Wang Feng [EMAIL PROTECTED] Vastaanottaja: Heikki Tuuri [EMAIL PROTECTED] Lhetetty: Wednesday, October 01, 2003 11:26 AM Aihe: Re: can NOT drop the database BTW, how can I rename a database? if you only have MyISAM tables, you can shut down

Re: Sql question

Keith Schuster [EMAIL PROTECTED] wrote: Mysql 3.. I can't figure this one out I need to move data from one mysql table to another The hurdle for me is adding additional column values. Here is what I have. insert into mytable (column1, column 2, column3) (Select thiscolumn From

RE: Sql question

you want to do insert into mytable (column1, column2, column3) (select thiscolumn, '1', now() from anotherTable); Mysql 3.. I can't figure this one out I need to move data from one mysql table to another The hurdle for me is adding additional column values. Here is what I

newbie SELECT question

Hi, I have a table containing page referral URLs gleaned from users browsing my website. Is there a way for me to use SELECT based on a portion record's contents? eg table contents: http://www.yahoo.com/adirectory/apage.htm http://google.net/adirectory/anotherpage.php I wish to return only

Re: Can't Access DB from MySQLCC

Randy Chrismon [EMAIL PROTECTED] wrote: Don't think this is the issue. If it were, I wouldn't be able to use MySQLCC on my own local database which is also 4.1.0 alpha, would I? Do you use password when you connect to the local 4.1 MySQL server? Yes. No password, no entry. This is the

Re: can NOT drop the database

As long as two databases are on the same disk you can also rename from one database to another: RENAME TABLE current_db.tbl_name TO other_db.tbl_name; Works!! :-) But the thing is: by doing this, although the tables have been *moved* from the old database to the new one, the old database

MySQL and mounted /mysql/data/ directories

Hello All, I want MySQL to see several drives scattered on several machines, all with MySQL installed on them. If I mount all of the /mysql/data/ directories to each of the machines will all the separate machines be able to see each others' databases in a transparant way? Or, alternatively,

Re: Rolling back DDL statements

Harald Fuchs wrote: In article [EMAIL PROTECTED], Chris Nolan [EMAIL PROTECTED] writes: Hi all, I was wondering, would there be any point at all in being able to have DDL statements as part of a transaction? There is one database I know of that has this as a big selling point. PostgreSQL

RE: newbie SELECT question

Could look at instr? -Original Message- From: Graham Nichols [mailto:[EMAIL PROTECTED] Sent: 01 October 2003 14:54 To: [EMAIL PROTECTED] Subject: newbie SELECT question Hi, I have a table containing page referral URLs gleaned from users browsing my website. Is there a way for

Re: Problem deleting data

Just to confirm that this is not a file permissions problem, I can use this statement: DELETE FROM stock WHERE supplier LIKE '%' and it doesn't exhibit this problem, but a plain: DELETE FROM stock does. I'm thinking that perhaps this is a bug in MySQL but I'm not sure. If anyone has any

How can i make mysql to print date and time automatically?

Hi, im develping an application, and was wondering how can i make mysql get time and date from pc and print it automatically in the time field and date field so the user dont have to worry about entering the coorect time and date. Is this posible, ive created a databse with fields hour(time)

setting an alias to a table column

hi, Is there a way of setting an alias to a column name when you create the table or by using the alter command. My problem is i have 2 versions of the same table with one column having a slightly different name (e.g old column name is id, new column name is a_id). Now, to save having to

Re: How can i make mysql to print date and time automatically?

On Wed, 2003-10-01 at 10:09, Emilio Ruben Estevez wrote: Hi, im develping an application, and was wondering how can i make mysql get time and date from pc and print it automatically in the time field and date field so the user dont have to worry about entering the coorect time and date. Is

RE: DATE_ADD Dynamic Interval

Sorry, I realise I made a mistake in my schema and my query below, they should read: - UPDATE foo_table SET NextDate=DATE_ADD(foo_table.NextDate,Period) WHERE NextDate=NOW() - CREATE TABLE `foo_table` ( `ID` bigint(20) NOT NULL auto_increment, `Period` enum('INTERVAL 30

locked threads

Hi guys, Do you have any thoughts on how to track down a locked thread/query? Basically we've had instances where a query locks up and causes a cascade of locked queries and the db freezes up. By the time it's had enough of an effect for us to see the symptoms (no new queries run) there are a

strange date problem

Hi I'm running this query: INSERT INTO `events`( `domain`, `title`, `body`, `begin`, `end`, `frequency`, `author` ) VALUES( 01, 'Test 2', 'Event is not displaying correct date.', '2003-10-25 24:00:00', '2003-10-01 24:00:00', '0', 'Rachel' ) I get no errors from mysql. (MySQL 3.23.54) on a

SHOW FIELDS FROM table?

My code is shown below, I can select * from employees but I cannot get any data back from SHOW FIELDS, I do not receive an error, in fact, something is returned but I'm not sure what. I can execute the command: SHOW FIELDS FROM test.employees in the Control Center just fine, even

Re: strange date problem SOLVED

I'm running this query: INSERT INTO `events`( `domain`, `title`, `body`, `begin`, `end`, `frequency`, `author` ) VALUES( 01, 'Test 2', 'Event is not displaying correct date.', '2003-10-25 24:00:00', '2003-10-01 24:00:00', '0', 'Rachel' ) I get no errors from mysql. (MySQL 3.23.54) on a

Blob fields

I people, Who knows how to insert an image file into blob fileds. Thanks. Angelo Carmo. VI-Veiculo de Ideias / Supermercados Horta Tel: 289899670 Tlm: 912179154 [EMAIL PROTECTED]

Re: Blob fields

On Wed, Oct 01, 2003 at 05:51:18PM +0100, Angelo Carmo wrote: I people, Who knows how to insert an image file into blob fileds. Lots of us know how. And we've discussed it on the list about 600 times already. I'm sure you'll find an answer in the list archives. Jeremy -- Jeremy D.

Re: MySQL and mounted /mysql/data/ directories

Hm. I think this is more of a mount question. If you have two machines, each with a /mysql/data directory then you cannot mount /mysql/data from one machine to the other machine and have the directories 'merge' into one big tree including 'all' contents. If you mount overtop of a directory that

How to write this query

I've run into a situation where i dont know how to best write a query. For a base example, consider these 3 tables: CREATE TABLE A ( A_IDINT NOT NULL PRIMARY KEY, A_data text ); CREATE TABLE B ( B_IDINT NOT NULL PRIMARY KEY, A_IDINT NOT NULL,

RE: How to write this query

You're on the right track with LEFT JOIN. Just continue the thought... Try: SELECT A_data, B_data, C_data FROM A LEFT JOIN B ON A.A_ID = B.B_ID LEFT JOIN C ON A.A_ID = C.C_ID WHERE A.A_ID = 4; -Original Message- From: sean peters [mailto:[EMAIL PROTECTED] Sent: Wednesday, October

[Stats] MySQL List Stats; September 2003

-- Searchable archives for this list are available at http://www.listsearch.com/mysqltalk.lasso --

Re: How to write this query

Unfortunately that wont always work either. For instance, assume that there is an A record with A_ID = 4 And that there is a C record where A_ID = 4, but NO B record where A_ID = 4 So, executing the query: SELECT A_data, B_data, C_data FROM A LEFT JOIN B ON A.A_ID = B.B_ID LEFT JOIN C ON

Re: newbie SELECT question

How about this? SELECT SUBSTRING_INDEX(SUBSTRING(url, LOCATE(//, url) + 2), '/', 1) AS domain FROM referals Michael On Wed, 1 Oct 2003 14:54:24 +0100, Graham Nichols [EMAIL PROTECTED] wrote: Hi, I have a table containing page referral URLs gleaned from users browsing my website. Is there a

RE: How to write this query

Now I'm lost. Do you really mean to be joining A.A_ID against B.B_ID? Seems like it should be A.A_ID = B.A_ID... That's a traditional naming condition. If so, my recommendation should have been: SELECT A_data, B_data, C_data FROM A LEFT JOIN B ON A.A_ID = B.A_ID LEFT JOIN C ON A.A_ID = C.A_ID

Updated: How to write this query

Sorry, I had an error in my query. The fixed query with the entire post follows. Thanks for the responses to the incorrect one, im pretty sure that the suggestions will still fail for the previously indicated reasons, even with the modified query. ORIGINAL POST: (fixed) I've run into a

Fwd: [ANN] INTRODUCING LDMLMySQL

FYI. A 3rd party developer has just come out with an awesome Web browser based GUI for managing MySQL databases. Check it out! To: [EMAIL PROTECTED] Date: Wed, 01 Oct 2003 15:15:51 -0400 Subject: [ANN] INTRODUCING LDMLMySQL From: Jim Van Heule [EMAIL PROTECTED] INTRODUCING LDMLMYSQL THE OPEN

Re: Updated: How to write this query

On Wed, 1 Oct 2003 16:58:26 -0500, sean peters [EMAIL PROTECTED] wrote: [...] | So ive been running a query like: | SELECT A_data, B_data, C_data FROM A, B, C | WHERE A.A_ID = B.A_ID | AND A.A_ID = C.A_ID | AND A.A_ID = 4; | [...] | | What i really want is to get the A_data from A, and if there

Sig 4 on DEC ALPHA running RedHat 7.2

I installed MySQL 4.0.15-MAX on my DEC AlphaServer 1000a running Redhat 7.2 and the compile and install went perfect, without any problems. My problem is this: MySQL will allow me to input data into Databases, however if I try to extract information from said databases, MySQL blows up with a

Changing the sort ordering of an existing ENUM field

hi, I have a table with a 'priority' field defined as an ENUM: priority enum('SOMEDAY','NOW','SOON') NOT NULL I would like to change it to the following so the sort order is more intuitive/desirable. priority enum('NOW','SOON', 'SOMEDAY') NOT NULL I am guessing that some form of ALTER TABLE t

Re: Any ideas on how to authenticate to mysql thru PAM?

On Sun, Sep 21, 2003 at 11:42:49PM -0700, Mike Klein wrote: Sorry for repeat email...but this seems like an omission in mysql functionality. Sooo many apps come w/pam support, or the ability to use ssl. I realize that in order to use an ssl cert, you'd somehow need to lookup the subject

query time of 4294967294 with 4.0.14

Hi, Using 4.0.14 on Linux. Often very small queries are reported in processlist and in the slow log to have taken about 136 years. Fortunately they don't really! ;-) I thought it was curious and I didn't see it in the change log for 4.0.15. From the slow log: # Time: 031001 15:39:59, #

Re: query time of 4294967294 with 4.0.14

On Wed, Oct 01, 2003 at 05:54:25PM -0700, Ask Bjørn Hansen wrote: Hi, Using 4.0.14 on Linux. Often very small queries are reported in processlist and in the slow log to have taken about 136 years. Fortunately they don't really! ;-) I thought it was curious and I didn't see it in the

Re: Select statement to get the difference

I need to know which id is not present in the other table. Stefan: A left join should do it: select t1.id from t1 left join t2 on t1.id=t2.id where t2.id is null I learned this from this mailing list about two weeks ago. HTH. Randy -- MySQL General Mailing List For list archives:

I need to know which id is not present in the other table.

Do you use password when you connect to the local 4.1 MySQL server? Yes. No password, no entry. This is the answer why you can connect to the local 4.1 MySQL server with MySQLCC. Set up a password and you should get Client does not support .. error, too. I guess I didn't make myself clear... I DO

Re: Wrong Thread: (sorry) I need to know which id is not present in the other table.

Response should have been to problem connecting to 4.1.0 Sorry about that. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Can I misuse the list for a brief minute? - ODBC = MSSQL

Not sure if this is what you're looking for but try http://www.freetds.org I use it with php to talk to an MS SQL server. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Is something like this even possible? (Result set ordering of grouped data)

Hello, I have a table of items that change over time. These items are in a few categories. So my table looks something like the following simple version. +--++--+-+ | category | itemid | timemodified | currentdata |