Best way to store numeric data?

I've got a huge table going, and it's storing a load of numeric data. Basically, a percentage or single digit rank, one or two digits before the decimal and fifteen after, like this: 6.984789027653891 39.484789039053891 What is the most efficient way to store these values? I will be frequently

Re: ERROR 1071: Specified key was too long. Max key length is 500

Shawn, I tried both of those suggestions and they work well, with reducing the size of the fields being the way I'm going to go. Thanks for your help! Cheers, Jon On Feb 1, 2005, at 1:12 PM, [EMAIL PROTECTED] wrote: Jon Wynacht <[EMAIL PROTECTED]> wrote on 02/01/2005 03:33:13 PM: > Hi, > >

RE: Problem with SELECT SQL_CALC_FOUND_ROWS

I do see that the bug was closed over 2 years ago - I guess then it was a persistent problem, but now it is limited to the configuration of the MySQL Server. As I have found out today, Quadrupling the stock memory limits on the MySQL Server solved the problem...not sure what will happen when the da

Re: ut8 turkish character problem

What does [Show VARIABLES LIKE "%char%" ;] return ? (B (BHow does the character look when selected on the command line ? (B --> If correct then inserting works fine (B --> If not corect check your character settings (B (BYou might also find the information below useful: (B (Bhttp://www.be-k

Re: Problem with SELECT SQL_CALC_FOUND_ROWS

I'm confused. That bug was closed over 2 years ago. What makes you think it applies here, and why do you say it is still "unresolved"? Michael Michael Dykman wrote: Matt, If you go through the various build options tried in that bug report, you will see that it is a subtle bug and apparently st

Re: DATE problem

Mike Johnson wrote: From: René Fournier [mailto:[EMAIL PROTECTED] I'm trying to count rows that were added today. The column that I am counting on is in DATETIME format, so there are hours and minutes and seconds recorded, but I only need to check the date $sql = "SELECT

Re: ERROR 1071: Specified key was too long. Max key length is 500

John Wynacht writes: >CREATE TABLE `APPROVAL` ( >... > `APPROVAL_APPLICATION` varchar(255) NOT NULL default '', > `FOREIGN_ID` mediumint(9) unsigned NOT NULL default '0', > `FOREIGN_TYPE` varchar(255) NOT NULL default '', >... > PRIMARY KEY (`APPROVAL_ID`), > UNIQUE KEY `APPROVAL_APPLICA

RE: Nested query bug

[snip] >> >> No, it isn't ignored...it just returns a FALSE for the IN statement [/snip] More info "The word IN is an alias for = ANY. Thus these two statements are the same: SELECT s1 FROM t1 WHERE s1 = ANY (SELECT s1 FROM t2); SELECT s1 FROM t1 WHERE s1 IN(SELECT s1 FROM t2); Howeve

help please : ERROR 2006: MySQL server has gone away

Hi, I have this error when I am connected in mysql: ERROR 2006: MySQL server has gone away. No connection. Trying to reconnect... I only did this command: show databases; and received the error. I noticed that if I don't send a command since 25 sec, I have the error. Also, my variable wait_

RE: Problem with SELECT SQL_CALC_FOUND_ROWS

Matt, If you go through the various build options tried in that bug report, you will see that it is a subtle bug and apparently still unresolved. I couldn't tell you with any degree of confidence will fix it. The bug report does show a couple of configure options for PHP which seem to elimiate i

Re: ERROR 1071: Specified key was too long. Max key length is 500

Jon Wynacht <[EMAIL PROTECTED]> wrote on 02/01/2005 03:33:13 PM: > Hi, > > I'm wondering if anybody can help me decrypt what this error code is > referring to: > > ERROR 1071: Specified key was too long. Max key length is 500 > > This is the offending table creation script: > > CREATE TABLE

RE: Problem with SELECT SQL_CALC_FOUND_ROWS

Ok I installed PHP 4.3.10 and it still has not fixed the problem. If I remove the SQL_CALC_FOUND_ROWS from the query, it works no problems! This is very strange behavior! Matt Babineau Criticalcode w: http://www.criticalcode.com p: 858.733.0160 e: [EMAIL PROTECTED] -Original Message- Fr

Re: Mysqldump unusable, bugged?

Tobias Asplund wrote: On Tue, 1 Feb 2005, matt_lists wrote: now we have this added on a few tables in the dump DATA DIRECTORY='E:\mysql\data\campbell\' INDEX DIRECTORY='E:\mysql\data\campbell\' DATA DIRECTORY='E:\\mysql\\data\\campbell\\' INDEX DIRECTORY='E:\\mysql\\data\\campbell\\'

RE: DATE problem

From: René Fournier [mailto:[EMAIL PROTECTED] > I'm trying to count rows that were added today. The column that I am > counting on is in DATETIME format, so there are hours and minutes and > seconds recorded, but I only need to check the date > > $sql ="SELECT >

Fw: Res: Re: mysqld ended Can't get mysql running

Forwarded to the list - Shawn - Forwarded by Shawn Green/Unimin on 02/01/2005 03:49 PM - "Debora Gomes.unitech" <[EMAIL PROTECTED]> wrote on 02/01/2005 06:33:43 PM: > > The operating system is Red Hat 7.3 > The MySQL version is 4.1.9 > The error appears when i run ./configure in mysql/

Re: mysqld ended Can't get mysql running

[EMAIL PROTECTED] wrote: We need more details in order to help: What operating system are you on? Which version of MySQL are you attempting to run? How are you attempting to start the MySQL server? Has it worked before or is this a new installation? What errors are being reported to your error log?

RE: Problem with SELECT SQL_CALC_FOUND_ROWS

Weird thing is that I am running PHP 4.3.9I guess I can upgrade and see what happens? Matt Babineau Criticalcode w: http://www.criticalcode.com p: 858.733.0160 e: [EMAIL PROTECTED] -Original Message- From: Michael Dykman [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 01, 2005 12:

Re: Problem with SELECT SQL_CALC_FOUND_ROWS

Matt, I suspect your problem is PHP, not MySQL. refer to http://bugs.php.net/bug.php?id=16906&edit=1 On Tue, 2005-02-01 at 15:20, Matt Babineau wrote: > Hi All- > > I'm running a query that uses SQL_CALC_FOUND_ROWS for my search engine on a > real estate site. The problem is that I get

Re: mysqld ended Can't get mysql running

Debra, look in /opt/mysql/data for an error file which should contain some hint of why it stopped.. could be many things.. permissions on the data files, full disk broken paths, etc... - michael dykman On Tue, 2005-02-01 at 15:43, Debora Gomes.unitech wrote: > Hi ALL > > > it report

RE: Nested query bug

[snip] Not only that, shoudlnt it say "column does not exist?" It does not return an error, it ignores the whole nested query [/snip] Not really, because the entire sub query is being viewed as an OR condition (because of using IN). -- MySQL General Mailing List For list archives: http://lists

DATE problem

I'm trying to count rows that were added today. The column that I am counting on is in DATETIME format, so there are hours and minutes and seconds recorded, but I only need to check the date $sql = "SELECT COUNT(table.id) FROM table WH

Re: Mysqldump unusable, bugged?

On Tue, 1 Feb 2005, matt_lists wrote: > >> now we have this added on a few tables in the dump > >> > >> DATA DIRECTORY='E:\mysql\data\campbell\' INDEX > >> DIRECTORY='E:\mysql\data\campbell\' > > > > DATA DIRECTORY='E:\\mysql\\data\\campbell\\' INDEX > > DIRECTORY='E:\\mysql\\data\\campbell\\' > >

RE: Problem with SELECT SQL_CALC_FOUND_ROWS

Ok here is the code chunk: $rows = is_numeric($_GET['rows']) ? $_GET['rows'] : 5; $stRow = 0; // SEARCH CODE $sql = "SELECT SQL_CALC_FOUND_ROWS propertyData.*, propertyDataBulk.propertyDesc FROM propertyData LEFT JOIN propertyDataBulk ON propertyData.id = propertyDataBulk.pro

ERROR 1071: Specified key was too long. Max key length is 500

Hi, I'm wondering if anybody can help me decrypt what this error code is referring to: ERROR 1071: Specified key was too long. Max key length is 500 This is the offending table creation script: CREATE TABLE `APPROVAL` ( `APPROVAL_ID` mediumint(9) unsigned NOT NULL auto_increment, `APPROVAL_CU

Re: Problem with SELECT SQL_CALC_FOUND_ROWS

"Matt Babineau" <[EMAIL PROTECTED]> wrote on 02/01/2005 03:20:49 PM: > Hi All- > > I'm running a query that uses SQL_CALC_FOUND_ROWS for my search engine on a > real estate site. The problem is that I get an error when I run my query: > > "Warning mysql_query(): Unable to save result set in /

ut8 turkish character problem

I have mysql-4.1.9 installed. I am trying to insert and select turkish characters. My tables are utf8. I tried everything but not succesfull. turkish characters returns to "?" or crops on turkish character. "example: insert: Bingöl, select: Bing" What is the secret of inserting turkish characters

Re: Nested query bug

matt_lists wrote: Jay Blanchard wrote: [snip] the bug is, the nested query on the first statement is ignored [/snip] No, it isn't ignored...it just returns a FALSE for the IN statement False should give no records, it's an "IN ()" sense none match the condition instead it gives me every recor

Re: mysqld ended Can't get mysql running

We need more details in order to help: What operating system are you on? Which version of MySQL are you attempting to run? How are you attempting to start the MySQL server? Has it worked before or is this a new installation? What errors are being reported to your error log? The default location of

Problem with SELECT SQL_CALC_FOUND_ROWS

Hi All- I'm running a query that uses SQL_CALC_FOUND_ROWS for my search engine on a real estate site. The problem is that I get an error when I run my query: "Warning mysql_query(): Unable to save result set in /clients/search.php" My Query is: SELECT SQL_CALC_FOUND_ROWS propertyData.*, propert

Re: Nested query bug

Jay Blanchard wrote: [snip] the bug is, the nested query on the first statement is ignored [/snip] No, it isn't ignored...it just returns a FALSE for the IN statement False should give no records, it's an "IN ()" sense none match the condition instead it gives me every record?! -- MySQL General

RE: Nested query bug

[snip] the bug is, the nested query on the first statement is ignored [/snip] No, it isn't ignored...it just returns a FALSE for the IN statement -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Nested query bug

Jay Blanchard wrote: [snip] SELECT A.*, b.* FROM tablea a WHERE BLAH IN ( SELECT CORCOL BLAH FROM tableC WHERE c1 = 'c' AND c2= 'c' ); [/snip] This query is just badly formed, for instance, where is the table aliased 'b' in your from statement? Are you showing the complete query? Here's th

mysqld ended Can't get mysql running

Hi ALL it reports back # Starting mysqld daemon with databases from /opt/mysql/data STOPPING server from pid file /opt/mysql/data/acetona.anp.net.pid 050201 02:43:51 mysqld ended Att. Débora S. Gomes Analista de Tecnologia - Redes Agência Nacional do Petróleo - ANP Tel.: 21 3804-0786

RE: Nested query bug

[snip] SELECT A.*, b.* FROM tablea a WHERE BLAH IN ( SELECT CORCOL BLAH FROM tableC WHERE c1 = 'c' AND c2= 'c' ); [/snip] This query is just badly formed, for instance, where is the table aliased 'b' in your from statement? Are you showing the complete query? -- MySQL General Mailing List Fo

Re: Nested query bug

matt_lists wrote: Having a strange bug with nested queries SELECT A.*, b.* FROM tablea a WHERE BLAH IN ( SELECT BLAH FROM tableC WHERE c1 = 'c' AND c2= 'c' ); this works, but it should not there is no "BLAH" column in table C If I change it to this, it works correctly, as far as I can tell,

Nested query bug

Having a strange bug with nested queries SELECT A.*, b.* FROM tablea a WHERE BLAH IN ( SELECT BLAH FROM tableC WHERE c1 = 'c' AND c2= 'c' ); this works, but it should not there is no "BLAH" column in table C If I change it to this, it works correctly, as far as I can tell, still working on v

Removing 3.23, installing 5.02

Hi folks, I've got a Fedora Core 3 box that came with MySQL 3.23.xx, recently updated to 3.23.58. I wanted to wipe the box clean and install 5.0, but the Add/Remove Packages (and the manual RPM command) both failed on multiple dependencies, so I (foolishly?) took matters into my own fat little

Re: flush-hosts/tcpwrapper problems

Hello. On some systems tcpwrapper works with MySQL not that well as expected. For example see: http://bugs.mysql.com/bug.php?id=5652 And some bugs aren't fixed in old 3.23.xx versions. In my opinion, your problem is related to DNS. You may disable DNS lookups by starting mysqld with the

Re: Mysqldump unusable, bugged?

matt_lists wrote: matt_lists wrote: Found the problem now we have this added on a few tables in the dump DATA DIRECTORY='E:\mysql\data\campbell\' INDEX DIRECTORY='E:\mysql\data\campbell\' the restore barfs on this not sure how to remove this, looking at options now, it only puts this on a couple

How much is cached

Hi all. I'm implementing a filesystem that resides inside a MySQL database using FUSE[1]. The actual data of my files is stored in fields of type BLOB and what I want to know is: Does MySQL cache the contents of a BLOB in its buffer cache? What I mean is, if I do two consequtive calls to the da

Re: SELECT on string field

Hi Arcangelo! Try this: SELECT COUNT(*), url, SUBSTRING_INDEX(url, "?", 1) AS page_name FROM sites GROUP BY page_name SUBSTRING_INDEX will return the leftmost (or rightmost) part of a string before the ocurrence of a delimiter ("?" in this case. See Documentation:

RE: SELECT on string field

[snip] I've a field with www.s.com/p.php?id=3 www.s.com/p.php?id=4 www.s.com/p.php?id=5 if a do a query with the atribute 'group by' i want that the system regroup all that is after the '?' is that possible? SELECT field WHERE all that is after the '?' GROUP BY all that is after the '?' the r

SELECT on string field

Hi, I've a field with www.s.com/p.php?id=3 www.s.com/p.php?id=4 www.s.com/p.php?id=5 if a do a query with the atribute 'group by' i want that the system regroup all that is after the '?' is that possible? SELECT field WHERE all that is after the '?' GROUP BY all that is after the '?' the result

Re: Column Permissions?

I have seen this type of security implemented through multiple VIEWs but not directly by the storage engine. You will need to create a view for your user that exposes only out those rows designated for that user. The drawback to this is that you will need to issue one view for each user or grou

Re: Tables damaged

Hello. If your tables are MyISAM and you think that they are damaged, I suggest you to stop the MySQL server, make a backup of the data directory (just copy to other place) and then run a myisamchk program to repair them. See: http://dev.mysql.com/doc/mysql/en/repair.html http://dev.

passing environment variable to an SQL script

Hi everybody. I do a lot of maintenance things on the DB by piping SQL files to my MySql DB. What would be very useful is to find a way of passing very simple *arguments* to these scripts. The only way I can think of it is using a nasty trick > mysql --set-variable=wait_timeout=1234 < file.sql

flush-hosts/tcpwrapper problems

Hi, I've got an odd setup that I need to diagnose. The customer won't allow us to upgrade very far with the OS or MySQL. Its currently on a BSD/OS 4.0.1 system, and 4.2 is the highest we could possibly be allowed to go. We have upgraded to mysql-3.23.58 (Customer won't le

RE: SQLSTATE mapping problem

Hi all, Can anybody suggest whether it is due to version incompatibility?? We are using MySQL 4.0.21 and MySQL ODBC Driver 3.51. Thanks in advance Narasimha -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 01, 2005 2:53 PM To: mysql@lists.mysql.

What are the reasons?

Hi everybody, What are the possible reasons for runaway MySQL process on Sun Solaris, beyond joins on fields with NULLs. Regards, Mikhail Berman

mysql-query-browser and SuSE 8.2

Hi: I have not been able to find a precompiled version of mysql-query-browser for SuSE 8.2, either at mysql web site, or using google. Compiling from source fails because of version differences in libxml-2.0, for instance. Anybody know of a URL where I can find a version that will work with SuS

Re: utf8 x latin

Thanks for your help Gabriel. I will use the latin1 with case sensitive. Andre On 1/31/05 7:32 AM, "Gabriel PREDA" <[EMAIL PROTECTED]> wrote: > I think you should use: latin1_general_ci and on columns with spanish you > should use latin1_spanish_ci. > > English, French, Portuguese not having

Tables damaged

Hi, Snort had had problems to insert data into the table so, at i opened a connection to the server and dit: use snort; i got this error: Din't find any fields in table 'data' Din't find any fields in table 'event' Din't find any fields in table 'tcphdr' Database changed With phpmyadmin these tab

Create index if not exists????

I'm writing an install script and need to create an index if it does not exists. I've tried SQL statements but none of them work. Basically I'm looking to do something like $sql[] = "ALTER TABLE `exp_members` ADD INDEX IF NOT EXIST `idx_members_username` ( `username` )"; Is this possible? Thank

Premature InnoDB conversion.

MySql 4.0.14 We tried to convert MyISAM table format to INNODB format, it took forever to finish the process, someone intervened and killed the process through Task Manager. Now can't run Mysqld-nt, running it with the --console reports the followings. Your help is appreciated. Microsoft Wind

Re: Non root user MySQL install possible?

Hello. > If it is possible to do a local install as a non privileged user, can > someone please point me in the right direction, to a guide or > something? Yes it is possible. Here is a part of my configuration file. [client] port= 3718 socket = /home/gleb/mysqls/t

RE: Non root user MySQL install possible?

> -Original Message- > From: j [mailto:[EMAIL PROTECTED] > Sent: dinsdag 1 februari 2005 11:20 > To: Mark > Cc: mysql@lists.mysql.com > Subject: Re: Non root user MySQL install possible? > > Sorry, I don't mean running MySQL as root, I mean I do not have root > access on the server I am t

Re: Help with a query using multiple LEFT JOINS

In article <[EMAIL PROTECTED]>, "Graham Cossey" <[EMAIL PROTECTED]> writes: > I'm hoping someone can help with a little problem I'm having with a query. > In the query below I wish to return as least one row per tbl1, however I am > only getting rows where there is at least an entry for tbl2 : >

Re: Non root user MySQL install possible?

Sorry, I don't mean running MySQL as root, I mean I do not have root access on the server I am trying to install MySQL on - for myself, under my home directory. I run into permission problems (because I am not root, I can't alter anything under /var). I am trying to edit the necessary configs so th

RE: Non root user MySQL install possible?

> -Original Message- > From: j [mailto:[EMAIL PROTECTED] > Sent: dinsdag 1 februari 2005 10:09 > To: mysql@lists.mysql.com > Subject: Non root user MySQL install possible? > > Hi. Before asking my question I would like to state that I have looked > through the installation documentation,

Query problem

Hi! I have a problem with a query. I have a given amount of values (id:s) (for example 1,2,6,12,77,78,79,122,123,124), these are related to a table, "pref", and belongs to a specific category, "pref_cat"-table. So, 1,2,6,12 are connected to id 1 in "pref_cat", 77,78,79 to id 2 and 122,123,124 t

Query question

Hi, all I hope somebody can help me. Situation Three tables 1. ++++ | id | name | region | ++++ | 13 | Name1 | 1 | | 15 | Name2 | 2 | | 47 | Name3 | 1 | | 57 | Name4 | 2 | | 65 | Name5 | 2 | | 77

RE: Help with a query using multiple LEFT JOINS

Thanks Tom Doing as you suggested as well as applying the "st.group = 'B'" in the tbl4 ON achieved what I wanted. Graham > -Original Message- > From: Tom Crimmins [mailto:[EMAIL PROTECTED] > Sent: 01 February 2005 00:03 > To: Graham Cossey > Cc: mysql@lists.mysql.com > Subject: RE: Help

SQLSTATE mapping problem

Hi all, We are using MySQL ODBC 3.51 driver for db connection. When executing an SQL statement using SQLExecDirect() function and if there is a syntax error in the SQL statement, the SQLSTATE returned by the driver is "23000" (which stands for invalid constraint/duplicate keys.) instead of "42

Re: "missing" my.cnf file?

Hello. The MySQL server can be started without a configuration file, only with command line options. You can manually specify the location of my.cnf file with --defaults-file=path command line option. See: http://dev.mysql.com/doc/mysql/en/program-options.html sol beach <[EMAIL PROTE

Re: I want to get schema infomation in MySQL DB for schema.

Hello. The MySQL server have INFORMATION_SCHEMA tables, but they are implemented in MySQL 5.0.2 and not production ready yet. See: http://dev.mysql.com/doc/mysql/en/information-schema.html For obtaining information about database metadata use different SHOW statements. See: http://dev

Re: Replication suddenly stops on mysql 4.1.7 with Slave_IO_Running: No

Hello. > But I use 4.1.7, not 4.0.21 ...weird. As said at: http://dev.mysql.com/doc/mysql/en/news-4-1-8.html "Fixed a bug which caused a crash when only the slave I/O thread was stopped and started. (Bug #6148)" I suggest you to upgrade to the latest release (4.1.9 now).

Non root user MySQL install possible?

Hi. Before asking my question I would like to state that I have looked through the installation documentation, and have also successfully performed MySQL installations on WindowsXP (along with Apache and PHP), as well as on Linux machines that I have administrative (root) access on. However, I am h

Re: Replication suddenly stops on mysql 4.1.7 with Slave_IO_Running: No

Gleb Paharenko schrieb: Hello. I've looked through the bug database, and the only thing that I've found was an already-closed bug: http://bugs.mysql.com/bug.php?id=6148 I had been looking around the Changelogs, but I had not found that one. Sounds pretty much like my problem :( But I use 4.1.