Distribution of Software

[Vinayak Mahadevan]

Hi

I have developed a software wherein the frontend is in visual basic and 
the backend is mysql 4.1. Now when I am installing the software in 
various machines do i have to install the odbc connector for mysql in 
every machine. The database is going to be a centralised database.


Thanks in advance
Vinayak

--
Vinayak Mahadevan
Systems Engineer
Magtorq Pvt. Ltd.
58-C, Sipcot Industrial Complex
Hosur - 635-126
Mobile: 98 94 90 61 61



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

config diff: query time went from 70 mins to 20 seconds

[David Sparks]

Here is a config diff that made mysql usable again.  As the database 
grew in size, buffer sizes in the config were increased to try to boost 
mysql performance.


Unfortunately it didn't work as expected.  As the config was tweaked, 
mysql slowed down even more.  Removing all settings from the my.cnf 
restored performance.


So what was the setting below that was tanking mysql performance?  I 
suspect that "innodb_log_buffer_size=32M" was the culprit.



 #skip-innodb
 key_buffer = 2048M
 max_allowed_packet = 1M
-table_cache= 1536
-sort_buffer_size   = 256M
-net_buffer_length  = 64K
-read_buffer_size   = 256M
-read_rnd_buffer_size   = 256M
-myisam_sort_buffer_size= 256M
+#table_cache   = 1536
+#sort_buffer_size  = 256M
+#net_buffer_length = 64K
+#read_buffer_size  = 256M
+#read_rnd_buffer_size  = 256M
+#myisam_sort_buffer_size   = 256M
 language   = /usr/share/mysql/english
 myisam_data_pointer_size   = 6

 # daves: this may not quite work ...
-join_buffer_size   = 256M
+#join_buffer_size  = 256M
 long_query_time= 10
 log-long-format
 log-slow-queries
@@ -117,9 +117,9 @@
 # the rest of the innodb config follows:
 # don't eat too much memory, we're trying to be safe on 64Mb boxes.
 # you might want to bump this up a bit on boxes with more RAM
-innodb_buffer_pool_size= 2048M
+innodb_buffer_pool_size= 6144M
 # this is the default, increase if you have lots of tables
-innodb_additional_mem_pool_size= 32M
+innodb_additional_mem_pool_size= 4M
 #
 # i'd like to use /var/lib/mysql/innodb, but that is seen as a 
database :-(
 # and upstream wants things to be under /var/lib/mysql/, so that's the 
route

@@ -134,7 +134,7 @@
 # sensible values range from 1MB to 
(1/innodb_log_files_in_group*innodb_buffer_pool_size)

 innodb_log_file_size   = 1G
 # this is the default, increase if you have very large transactions.
-innodb_log_buffer_size = 32M
+innodb_log_buffer_size = 1M
 # this is the default, and won't hurt you.
 # you shouldn't need to tweak it.
 set-variable   = innodb_log_files_in_group=2

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Making ORDER BY RAND() more random

[Graham Anderson]

is there a way to make  ORDER BY RAND()  at bit more spontaneous ?
I am using it in a php statement:
$sql = "SELECT media.id,
artist.name as artist,
artist.spanish as bio,
artist.purchaseLink,
artist.picture,
media.spanish as trackName,
media.path,
media.quality,
mediaType.id as mediaType
FROM artist, media, playlistItems, mediaType
WHERE playlistItems.playlist_id = $myID
AND playlistItems.media_id = media.id
AND media.artist_id = artist.id
AND media.mediaType_id = mediaType.id
ORDER BY RAND() LIMIT 0, 30";


the result seems to be pretty predictable 
is there a way to improve RAND() or is there something better ?



g


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Foreign key support in MyISAM

[Josh Trutwin]

On Fri, 30 Sep 2005 13:08:31 -0700
Jacek Becla <[EMAIL PROTECTED]> wrote:

> Hi,
> 
> I had asked similar question few days ago, and then checked with
> the developers as no one was able to answer on this mailing list.
> I was told it is very likely we'll get it in 5.2.

Thanks - now that 5.0 is release candidate I wonder how soon it will
be before 5.1 alpha is released?

Josh

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Global Replace

[John Berman]

Sound advice, I have no done as suggested and my application handles the
Nulls's


Thanks

John B

-Original Message-
From: Sujay Koduri [mailto:[EMAIL PROTECTED] 
Sent: 30 September 2005 14:26
To: Scott Noyes; [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Subject: RE: Global Replace


Yes, I don't think you have to do lot of changes to your application to
achieve this. As scott mentioned, always try to keep minimum(whatever is
really useful) data in the DB, either for more performance or for using less
disk space.

sujay

-Original Message-
From: Scott Noyes [mailto:[EMAIL PROTECTED] 
Sent: Friday, September 30, 2005 6:43 PM
To: [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Subject: Re: Global Replace

> We have a database on MySql 4 and it contains many tables. In each 
> field in the table in the past were there was no data to display we 
> simply left the field blank, we now want to replace a null entry with  
> No Data

I advise you to reconsider.  You are reducing the flexibility and
usefullness of your data by doing this.  Functions designed to take
advantage of NULL values will be lost to your applications.  Your database
will consume more disk space, and probably take longer to search.

Leave the database fields as NULL, and design the output of your
applications to display "No Data" where appropriate.

--
Scott Noyes
[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]




-- 
No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.344 / Virus Database: 267.11.9/115 - Release Date: 29/09/2005



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Foreign key support in MyISAM

[Jacek Becla]

Hi,

I had asked similar question few days ago, and then checked with
the developers as no one was able to answer on this mailing list.
I was told it is very likely we'll get it in 5.2.

Jacek



Sujay Koduri wrote:

does anyone have an idea when mysql guys are going to include foreign key
support in myisam tables.
i have read that it is slated for a future release, but it is not mentioned
when it is actually scheduled for.
 
reference -- http://dev.mysql.com/doc/mysql/en/ansi-diff-foreign-keys.html
 
 
sujay





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

timing queries?

[Jacek Becla]

Hi,

Does anyone know how to execute SQL statements from a text file
such that the summaries "X rows in set (X.YZ sec)" are printed
for each query?

Neither of these do it:
  mysql < batch-file
  mysql -e 'source batch-file'


'source batch-file' run inside mysql command-line does it,
but then I need to manually copy and paste the results.

Thanks,
Jacek

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

How to sort Query Browser's connection list?

[Mihail Manolov]

Hi all,

It's not a big deal, but I can't seem to find how to sort or order my 
connection entries in "Options->Connections" view tree in MySQL Query 
Browser. (See attached screenshot.)


Any ideas?


Thanks!

Mihail


--
Mihail Manolov
Government Liquidation, LLC
Special Projects Leader
202 558 6227


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: HELP - Group_Concat broken after update

[Ed Reed]

I meant 600k not 6meg. Sorry

>>> "Ed Reed" <[EMAIL PROTECTED]> 9/30/05 9:46:32 AM >>>
That's not true. 

Group_Concat can already return more than 6meg. It's set by 
group_concat_max_len variable. The fact is that I've been told there are 
security issues corrected in the 4.1.14a update and I can't apply them because 
it will break my already existing apps.

Is there a fix?

- Thanks

>>> Dan Nelson [EMAIL PROTECTED] > 9/27/05 11:54:35 PM >>

>>>Probably to allow for a result longer than 255 characters, I guess.
>>>
>>>-- 
>>>Dan Nelson
>>> [EMAIL PROTECTED] 

Re: MySQL Query Browser

[Ed Reed]

I think Control Center is a much better tool then the combination of Query 
Browser and Administrator. I still use Control Center for my day to day stuff 
and only check out Query Browser when new versions come out to see if they've 
solved the major problems.
 
I think they've gone completely in the wrong direction with these tools and I 
even told members of MySQL-AB at this years user conference. I gave a written 
list of suggestions and not a single one of them has been addressed in the 
program. It's definitely not an intuitive application and could be so much 
better if they'd just take the best ideas from all the other tools that are 
already out here. I don't think they've done that; it seems as if they've 
created this thing based on the way they _want_ to use it and not the way users 
_need_ to use it.


>>> Pooly <[EMAIL PROTECTED]> 9/30/05 8:57:04 AM >>>
2005/9/30, Sujay Koduri < [EMAIL PROTECTED] >:
>
> I am not saying MysQL Query Browser is anyway bad or inferior, but as for my
> experience sqlyog is very good. There is a free version which you can use
> for executing SQL queries, ofcourse you will be stripped of some advanced
> features.
>
> You can run multiple queries at once using shift+F5. I suggest you to try
> this out once.
>


the no longer supported Mysql Control Center, can do it as well. It
supports single/multiple query without tricks. You can even directly
edit your dataset (and not do the annoying : "edit"+ do your stuff +
"apply changes" of MysqlQuery Browser )

> sujay
>
> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
> Sent: Friday, September 30, 2005 7:14 PM
> To: Rob Agar
> Cc: 'Mysql '
> Subject: RE: MySQL Query Browser
>
> "Rob Agar" < [EMAIL PROTECTED] > wrote on 09/29/2005 08:27:37 PM:
>
> > hi Scott
> >
> > > How do I run more than 1 queries in MySQL Query Browser?
> >
> > The only way I've found is to put the queries in a .sql file and load
> > it via File > Open Script. It doesn't accept multiple typed in
> > queries, even if they are separated by semicolons.
> >
> > hth
> > Rob
> >
> >
> >
> He can just start a new script tab, can't he? That's how I do it. I don't
> know why they have two different types of tabs (one for multi-statements and
> one for single statements) but they do.
>
> File -> New Script Tab
>
>
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql 
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] 
>
>


--
Pooly
Webzine Rock : http://www.w-fenec.org/ 

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql 
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] 

Re: HELP - Group_Concat broken after update

[Ed Reed]

That's not true. 
 
Group_Concat can already return more than 6meg. It's set by 
group_concat_max_len variable. The fact is that I've been told there are 
security issues corrected in the 4.1.14a update and I can't apply them because 
it will break my already existing apps.
 
Is there a fix?
 
- Thanks

>>> Dan Nelson [EMAIL PROTECTED]> 9/27/05 11:54:35 PM >>

>>>Probably to allow for a result longer than 255 characters, I guess.
>>>
>>>-- 
>>>Dan Nelson
>>>[EMAIL PROTECTED] 

Re: MySQL Query Browser

[Pooly]

2005/9/30, Sujay Koduri <[EMAIL PROTECTED]>:
>
> I am not saying MysQL Query Browser is anyway bad or inferior, but as for my
> experience sqlyog is very good. There is a free version which you can use
> for executing SQL queries, ofcourse you will be stripped of some advanced
> features.
>
> You can run multiple queries at once  using shift+F5. I suggest you to try
> this out once.
>


the no longer supported Mysql Control Center, can do it as well. It
supports single/multiple query without tricks. You can even directly
edit your dataset (and not do the annoying : "edit"+ do your stuff +
"apply changes" of MysqlQuery Browser )

> sujay
>
> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
> Sent: Friday, September 30, 2005 7:14 PM
> To: Rob Agar
> Cc: 'Mysql '
> Subject: RE: MySQL Query Browser
>
> "Rob Agar" <[EMAIL PROTECTED]> wrote on 09/29/2005 08:27:37 PM:
>
> > hi Scott
> >
> > > How do I run more than 1 queries in MySQL Query Browser?
> >
> > The only way I've found is to put the queries in a .sql file and load
> > it via File > Open Script. It doesn't accept multiple typed in
> > queries, even if they are separated by semicolons.
> >
> > hth
> > Rob
> >
> >
> >
> He can just start a new script tab, can't he? That's how I do it. I don't
> know why they have two different types of tabs (one for multi-statements and
> one for single statements) but they do.
>
> File -> New Script Tab
>
>
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>
>


--
Pooly
Webzine Rock : http://www.w-fenec.org/

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: MySQL Administrator crashes upon launch...

[Nuno Pereira]

Wolfram Stebel wrote:

Am 30.09.2005 16:29 Uhr schrieb "Nuno Pereira" unter
<[EMAIL PROTECTED]>:

in Users/user/Library/Logs/CrashReporter/java.crash.log
one mile of traces

Regards
Wolfram

**

Host Name:  Erde
Date/Time:  2005-09-30 15:13:52.351 +0200
OS Version: 10.4.2 (Build 8C46)
Report Version: 3

Command: MySQL Administrator
Path:/Applications/MySQL Administrator.app/Contents/MacOS/MySQL
Administrator
Parent:  WindowServer [70]

Version: ??? (1.1.0)

PID:2192
Thread: 0

Exception:  EXC_BAD_ACCESS (0x0001)
Codes:  KERN_PROTECTION_FAILURE (0x0002) at 0x

Thread 0 Crashed:
0   libSystem.B.dylib  0x900031e8 strlen + 8
1   libSystem.B.dylib  0x9001f880 sscanf + 88
2   com.mysql.MySQLToolsCommon 0x100354e0 myx_get_mysql_version + 96
3   com.mysql.MySQLToolsCommon 0x100355b4 myx_get_mysql_major_version +
24
4   com.mysql.MySQLToolsCommon 0x10001e50 -[MConnectionPanel(Private)
connectionFinished:] + 216
5   com.apple.Foundation   0x92890760 __NSFireMainThreadPerform +
276
6   com.apple.CoreFoundation   0x9077c108 __CFRunLoopPerformPerform +
104
7   com.apple.CoreFoundation   0x9074bc8c __CFRunLoopDoSources0 + 384
8   com.apple.CoreFoundation   0x9074b1bc __CFRunLoopRun + 452
9   com.apple.CoreFoundation   0x9074ac3c CFRunLoopRunSpecific + 268
10  com.apple.HIToolbox0x93129ac0 RunCurrentEventLoopInMode +
264
11  com.apple.HIToolbox0x931290cc ReceiveNextEventCommon + 244
12  com.apple.HIToolbox0x93128fc0
BlockUntilNextEventMatchingListInMode + 96
13  com.apple.AppKit   0x93623e44 _DPSNextEvent + 384
14  com.apple.AppKit   0x93623b08 -[NSApplication
nextEventMatchingMask:untilDate:inMode:dequeue:] + 116
15  com.apple.AppKit   0x9362006c -[NSApplication run] + 472
16  com.apple.AppKit   0x937108bc NSApplicationMain + 452
17  com.mysql.Administrator0x5a24 _start + 392 (crt.c:267)
18  com.mysql.Administrator0x5898 start + 48

Thread 1:
0   libSystem.B.dylib  0x9002ca78 semaphore_wait_signal_trap + 8
1   libSystem.B.dylib  0x9003125c pthread_cond_wait + 508
2   com.apple.Foundation   0x9288a0a0 -[NSConditionLock
lockWhenCondition:] + 68
3   com.apple.AppKit   0x936c07f8 -[NSUIHeartBeat
_heartBeatThread:] + 324
4   com.apple.Foundation   0x92882f34 forkThreadForFunction + 108
5   libSystem.B.dylib  0x9002c3b4 _pthread_body + 96

Thread 2:
0   libSystem.B.dylib  0x9002ca78 semaphore_wait_signal_trap + 8
1   libSystem.B.dylib  0x9003125c pthread_cond_wait + 508
2   com.apple.Foundation   0x92886448 -[NSConditionLock lock] + 52
3   com.apple.Foundation   0x928905a4
-[NSObject(NSMainThreadPerformAdditions)
performSelectorOnMainThread:withObject:waitUntilDone:modes:] + 716
4   com.apple.Foundation   0x928902b8
-[NSObject(NSMainThreadPerformAdditions)
performSelectorOnMainThread:withObject:waitUntilDone:] + 120
5   com.mysql.MySQLToolsCommon 0x10002040 -[MConnectionPanel(Private)
connectThread:] + 144
6   com.apple.Foundation   0x92882f34 forkThreadForFunction + 108
7   libSystem.B.dylib  0x9002c3b4 _pthread_body + 96

Thread 0 crashed with PPC Thread State 64:
  srr0: 0x900031e8 srr1: 0x1200f030
vrsave: 0x
cr: 0x82002202  xer: 0x   lr: 0x9001f880
ctr: 0x900031e0
r0: 0x9001f880   r1: 0xbfffe310   r2: 0x0044
r3: 0x
r4: 0x100ae024   r5: 0xbfffe4e8   r6: 0xbfffe4ec
r7: 0x
r8: 0x6e48476d   r9: 0x  r10: 0x10034a04
r11: 0xa00063fc
   r12: 0x900031e0  r13: 0x  r14: 0x0001
r15: 0x
   r16: 0x0001  r17: 0xb2d0  r18: 0x
r19: 0x0035ce40
   r20: 0x  r21: 0x  r22: 0x0001
r23: 0x0030cb80
   r24: 0x  r25: 0x0030cb88  r26: 0x0030cc98
r27: 0x046a
   r28: 0xbfffe34c  r29: 0x  r30: 0x100ae024
r31: 0x9001f830

Binary Images Description:
0x1000 -0x54fff com.mysql.Administrator ??? (1.1.0)
/Applications/MySQL Administrator.app/Contents/MacOS/MySQL Administrator
0x1000 - 0x100d6fff com.mysql.MySQLToolsCommon ??? (1.0)
/Applications/MySQL
Administrator.app/Contents/Frameworks/MySQLToolsCommon.framework/Versions/1.
0.0/MySQLToolsCommon
0x8fe0 - 0x8fe51fff dyld 43.1/usr/lib/dyld
0x9000 - 0x901a6fff libSystem.B.dylib /usr/lib/libSystem.B.dylib
0x901fe000 - 0x90202fff libmathCommon.A.dylib
/usr/lib/system/libmathCommon.A.dylib
0x90204000 - 0x90257fff com.apple.CoreText 1.0.0 (???)
/System/Library/Frameworks/ApplicationServices.framework/Version

Re: Simpleupload/search

[Gleb Paharenko]

Hello.



Probably some web-development mailing list of forum is a better

place for your letter, than MySQL list. From MySQL side I can

advice you not to store images in the MySQL DB, usually it is

better to have them in the file system and to store links to the

images in the database.



John wrote:

> I am trying to make a script that has an admin and

> search area. The admin area let's the user upload 10

> photos and a discription for a property, then select

> from drop down menus for bedrooms,bathrooms,price for

> rental, golfing or not comunity,comunity name and code

> number"each has it's own for faster search if you know

> it i.e you own the place." then same for other end for

> search fields. Is there an easy way to do this or a

> program out now that does this in php/mysql?

> 

> Thanks

> 

> 

>   

> __ 

> Yahoo! Mail - PC Magazine Editors' Choice 2005 

> http://mail.yahoo.com

> 



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [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: How can I access results in a singel dimentional array?

[Sujay Koduri]

Mysql_store_result stores all the results in a single buffer. But at one
time you can only access one row. It is like a array of structures, where
each structure has one row info. And the call to mysql_fetch advances this
rowcount by one. And coming to storing all the results in one char*, it is a
simple loop you can use.

While(fetch from mysql) {
Catch all columns of a row in output bind vars
Append all these to the char * using some delimiter. (actually you don't
need to know the number of rows before hand, you can do a malloc here
itself)
}
Return (char*)

sujay

-Original Message-
From: Lefteris Tsintjelis [mailto:[EMAIL PROTECTED] 
Sent: Friday, September 30, 2005 7:13 PM
To: mysql@lists.mysql.com
Subject: How can I access results in a singel dimentional array?

Hi,

Is there an easy way to access directly the results after a SELECT and a
mysql_store_result() into a single dimentional char *array[], given that I
already know the (fields * rows) value? Are the total results stored in a
single buffer or is it done on a per row basis? How does it work?

Thnx,

Lefteris

--
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: MySQL Query Browser

[Sujay Koduri]

I am not saying MysQL Query Browser is anyway bad or inferior, but as for my
experience sqlyog is very good. There is a free version which you can use
for executing SQL queries, ofcourse you will be stripped of some advanced
features.

You can run multiple queries at once  using shift+F5. I suggest you to try
this out once.

sujay

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Friday, September 30, 2005 7:14 PM
To: Rob Agar
Cc: 'Mysql '
Subject: RE: MySQL Query Browser

"Rob Agar" <[EMAIL PROTECTED]> wrote on 09/29/2005 08:27:37 PM:

> hi Scott
> 
> > How do I run more than 1 queries in MySQL Query Browser?
> 
> The only way I've found is to put the queries in a .sql file and load 
> it via File > Open Script. It doesn't accept multiple typed in 
> queries, even if they are separated by semicolons.
> 
> hth
> Rob
> 
> 
> 
He can just start a new script tab, can't he? That's how I do it. I don't
know why they have two different types of tabs (one for multi-statements and
one for single statements) but they do.

File -> New Script Tab


Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

How can I access results in a singel dimentional array?

[Lefteris Tsintjelis]

Hi,

Is there an easy way to access directly the results after a SELECT and a
mysql_store_result() into a single dimentional char *array[], given that
I already know the (fields * rows) value? Are the total results stored
in a single buffer or is it done on a per row basis? How does it work?

Thnx,

Lefteris

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: MySQL Query Browser

[SGreen]

"Rob Agar" <[EMAIL PROTECTED]> wrote on 09/29/2005 08:27:37 PM:

> hi Scott
> 
> > How do I run more than 1 queries in MySQL Query Browser?
> 
> The only way I've found is to put the queries in a .sql file and load it
> via File > Open Script. It doesn't accept multiple typed in queries,
> even if they are separated by semicolons.
> 
> hth
> Rob
> 
> 
> 
He can just start a new script tab, can't he? That's how I do it. I don't 
know why they have two different types of tabs (one for multi-statements 
and one for single statements) but they do.

File -> New Script Tab


Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Re: Global Replace

[SGreen]

"John Berman" <[EMAIL PROTECTED]> wrote on 09/29/2005 06:05:42 PM:

> Hi
> 
> We have a database on MySql 4 and it contains many tables. In each field 
in
> the table in the past were there was no data to display we simply left 
the
> field blank, we now want to replace a null entry with  No Data
> 
> 
> I have no problem doing this on an individual field in each table like 
so
> 
> UPDATE mc_centralgirls SET mc_centralgirls.notes = "No Data"
> WHERE (((mc_centralgirls.notes) Is Null));
> 
> 
> but it's a big job.
> 
> 
> Can I do this at table level across all fields, or ideally at database 
level
> ?
> 
> Regards
> 
> John Berman
> 
> 

You can't do it on a database level but you can do it to an entire table 
at once. You will still need to individually declare which columns need 
fixing, though.

option A:
1) Copy the old table's design to a new table. In the new table 
redefine any nullable columns you no longer want to be NOT NULL and change 
the default value to 'No Data' (or whatever is appropriate for that 
column). 
2) INSERT all of the records from your old table into the new 
table.

INSERT new_tablename (col1, col2, ... , colN)
SELECT col1, col2, ..., colN
FROM old_tablename;

3) Verify the accuracy of your data import. Fix any problems and 
repeat until INSERT generates the data you want.
4) use RENAME TABLE to swap the names of the new table and the old 
table

RENAME TABLE new_tablename to old_tablename, old_tablename to 
new_tablename;

5) use DROP TABLE to get rid of the old data under the new name.

DROP TABLE `new_tablename`;



option B: Use an UPDATE statement combined with the COALESCE() function to 
replace all NULL values with the value you want

UPDATE target_table
SET col1 = COALESCE(col1, 'No Data')
, col2 = COALESCE(col2, 'No Data')
, col3 = COALESCE(col3, 'No Data')
, col4 = COALESCE(col4, 'No Data')
...
, colN = COALESCE(colN, 'No Data');


Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

RE: Global Replace

[Sujay Koduri]

Yes, I don't think you have to do lot of changes to your application to
achieve this. As scott mentioned, always try to keep minimum(whatever is
really useful) data in the DB, either for more performance or for using less
disk space.

sujay

-Original Message-
From: Scott Noyes [mailto:[EMAIL PROTECTED] 
Sent: Friday, September 30, 2005 6:43 PM
To: [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Subject: Re: Global Replace

> We have a database on MySql 4 and it contains many tables. In each 
> field in the table in the past were there was no data to display we 
> simply left the field blank, we now want to replace a null entry with  
> No Data

I advise you to reconsider.  You are reducing the flexibility and
usefullness of your data by doing this.  Functions designed to take
advantage of NULL values will be lost to your applications.  Your database
will consume more disk space, and probably take longer to search.

Leave the database fields as NULL, and design the output of your
applications to display "No Data" where appropriate.

--
Scott Noyes
[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: Global Replace

[Scott Noyes]

> We have a database on MySql 4 and it contains many tables. In each field in
> the table in the past were there was no data to display we simply left the
> field blank, we now want to replace a null entry with  No Data

I advise you to reconsider.  You are reducing the flexibility and
usefullness of your data by doing this.  Functions designed to take
advantage of NULL values will be lost to your applications.  Your
database will consume more disk space, and probably take longer to
search.

Leave the database fields as NULL, and design the output of your
applications to display "No Data" where appropriate.

--
Scott Noyes
[EMAIL PROTECTED]

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Where to store comments?

[Harald Fuchs]

In article <[EMAIL PROTECTED]>,
Jonas Geiregat <[EMAIL PROTECTED]> writes:

mysql> create table foo (id int NOT NULL comment 'test foo en bar');

This is a comment on a column which apparently gets displayed by SHOW
CREATE TABLE only by later versions, e.g. 4.1.14.  Table comments go
after the closing parenthesis:

create table foo (id int NOT NULL) comment 'test foo en bar';


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: daemon crash when shutting down large databases

[David Sparks]

Gleb Paharenko wrote:

>Hello.
>
> > = 77591546 K
>
>
>Really - something is wrong with your memory settings - MySQL is using
>about 77G of memory
>

Unfortunately getting the daemon to not go above the theoretical limit
has tanked performance.  In reality I never see the daemon go above 45%
RAM usage when using the settings that can theoretically go to 77GB RAM
usage.

What if I added a 80GB swap file?  Would this not make sure there is
available RAM if the daemon really needs it and eliminate all memory
exhaustion cases from the crash?

Cheers,

ds


> (or you have such a cool server :)! Please send the 
>output of 'SHOW VARIABLES' statement, 'SHOW STATUS' statement and your 
>configuration file. Include the amount of physical memory.
>
>
>
>David Sparks wrote:
>  
>
>>mysql usually crashes when being shutdown.  The machine is a dual AMD64 
>>w 8GB RAM running mysql-4.1.14 on Gentoo linux with a ~40GB database.  I 
>>had similar crashes running 4.0.24 on an x86 running a ~275GB database.
>>
>>I always use `mysqladmin shutdown` rather than the init scripts to 
>>shutdown the daemon.
>>
>>Are there any known problems with shutting down large databases?
>>
>>Thanks,
>>
>>ds
>>
>>050923 10:41:58  InnoDB: Starting shutdown...
>>050923 10:44:00InnoDB: Assertion failure in thread 1174235488 in file 
>>os0sync.c line 634
>>InnoDB: Failing assertion: 0 == pthread_mutex_destroy(fast_mutex)
>>InnoDB: We intentionally generate a memory trap.
>>InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
>>InnoDB: If you get repeated assertion failures or crashes, even
>>InnoDB: immediately after the mysqld startup, there may be
>>InnoDB: corruption in the InnoDB tablespace. Please refer to
>>InnoDB: http://dev.mysql.com/doc/mysql/en/Forcing_recovery.html
>>InnoDB: about forcing recovery.
>>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=2147483648
>>read_buffer_size=33550336
>>max_used_connections=217
>>max_connections=768
>>threads_connected=0
>>It is possible that mysqld could use up to
>>key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections 
>>= 77591546 K
>>bytes of memory
>>Hope that's ok; if not, decrease some variables in the equation.
>>
>>
>>
>>
>
>
>  
>


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]