ugly SQL for a report...

I have the following query that is running VERY slowly. Anyone have any suggestions? --- SELECT pnr.ID ID_pnr, pnr.reservationdatetime, pnr.conf_number, pnr.created_by, GROUP_CONCAT(pp.name_last,', ',pp.name_first ORDER BY name_last DESC SEPARATOR 'br') names, (SELECT

Re: select sum(qty*unitpri) wrong result

In the last episode (May 29), wang shuming said: Hi, table a1 qty int(10) , unitpri float(10,2) 35019.80 ... 350 19.80(total 48 lines) select sum(qty*unitpri) from a1 332639.99 ,it should return 332640.00 mysql version 4.1.19 If you want exact arithmetic,

Re: ugly SQL for a report...

On 5/29/06, Cory wrote: I have the following query that is running VERY slowly. Anyone have any suggestions? --- SELECT pnr.ID ID_pnr, pnr.reservationdatetime, pnr.conf_number, pnr.created_by, GROUP_CONCAT(pp.name_last,', ',pp.name_first ORDER BY name_last DESC SEPARATOR 'br') names, (SELECT

Keyword - MySQL 4.1

Dear All, While migrating from MySQL 4.0 to MySQL 4.1, I found that the database could not be loaded in 4.1 because MySQL 4.1 uses a reserved keyword div that was not reserved in 4.0. Is there anyway to get around the issue? Thanks in advance. Manoj -- MySQL General Mailing List For

Re: Keyword - MySQL 4.1

From the manual: http://dev.mysql.com/doc/refman/4.1/en/legal-names.html An identifier may be quoted or unquoted. If an identifier is a reserved word or contains special characters, you must quote it whenever you refer to it. (Exception: A word that follows a period in a qualified name must

Re: where more than 2 column not in a table

[EMAIL PROTECTED] wrote: Sory if my question makes you confused I've a problem with my sintax, I don't know how to select a list, whether it's not registered in another table, but it have 2 IDs. Not enough info. Show the Create Table statements, a bit of data, and the desired result. PB

Re: ugly SQL for a report...

SELECT pnr.ID ID_pnr, pnr.reservationdatetime, pnr.conf_number, pnr.created_by, GROUP_CONCAT(pp.name_last,', ',pp.name_first ORDER BY name_last DESC SEPARATOR 'br') names, (SELECT SUM(pf.base_fare*(SELECT COUNT(1) FROM pnr_passengers pp WHERE pp.ID_pnr=pnr.ID )) FROM pnr_fares

Binding mysql to more than 1 ip address but not to all

Hello all, I have seen that there are some people asking the same question. How to bind to multiple ip addresses but not to all? I have the need to do the same. I am running multiple Mysql servers on a cluster (Not mysql cluster). The cluster has 2 nodes and they are connected to a SAN to get

Re: LOAD DATA FROM MASTER stops unfinished with Query OK

It seems nobody has a clue here :( I've given up with MySQL replication... Hope it will work in 5.1 ... BTW: Any official info or estimate about the production release? Bgs wrote: Nope... pure myisam... sheeri kritzer wrote: yeah, I'd be willing to guess that you're mostly innodb. LOAD

What's wrong in this Innodb status log?

Hi, after having noticed occasional load spikes I created an Innodb monitor; follows an excerpt of the output where I can read that certain transactions don't start and that accessing a table (H.albero) with a very low amount of records (185) seems to take a lot of time. What's wrong with

max size of TEXT columns

hi listers, 1. environment: [EMAIL PROTECTED] ~ uname -a Linux myhost.mydomain.com 2.6.16-1.2080_2.rhfc5.cubbi_suspend2 #1 Wed Mar 29 12:54:32 CEST 2006 i686 i686 i386 GNU/Linux [EMAIL PROTECTED] ~ localhost.(none) show variables like version%;

Re: i'm at a complete loss?

On Sun, 28 May 2006 20:17:53 -0400 Daniel McQuay [EMAIL PROTECTED] wrote: Hello list, I just installed MySQL on my FreeBSD box here at home and am having a few problems with privileges. I can connect to mysql using; mysql -u mysql and there is no password needed. However, when I try to

Re: ugly SQL for a report...

On 5/29/06, Cory Robin wrote: SELECT pnr.ID ID_pnr, pnr.reservationdatetime, pnr.conf_number, pnr.created_by, GROUP_CONCAT(pp.name_last,', ',pp.name_first ORDER BY name_last DESC SEPARATOR 'br') names, (SELECT SUM(pf.base_fare*(SELECT COUNT(1) FROM pnr_passengers pp WHERE

Re: max size of TEXT columns

At 17:06 +0200 5/29/06, schlubediwup wrote: 2. problem according do the mysql docu TEXT/BLOB fields depend solely on the db environment and can grow to any length: 11.4.3. The |BLOB| and |TEXT| Types The maximum size of a |BLOB| or |TEXT| object is determined by its type, but

RE: max size of TEXT columns

FWIW, IMO LOB handling is really where MySQL lags behind all other enterprise database solutions. Firebird, DB2, Oracle, et al all are able to stream LOB data to and from disk so that it does not all need to be loaded in memory (multiple times for a single LOB). I would call this a bug but others

text datatype

hi i am creating web newsprogram by php and mysql. how to i can use text data type for saving contents of articles in database? when i use text format,contents save in the tables unless eny new line. howto i can insert \n element in text format? -

Re: text datatype

When you´ll catch data for a text field, you ought to use a textarea/textarea. When you´ll pull it from the database to the screen, you have to use nl2br($field) to conver \n to br. ali asghar torabi parizy [EMAIL PROTECTED] escreveu na mensagem news:[EMAIL PROTECTED] hi i am creating web

MySql GUI

Hello I understand that there is a free gui that will allow an administrator or user to create databases and maybe even add, update and modify rows in tables. It is something like MySQL Controller. Does this still exist and what is the link? Andrew -- MySQL General Mailing List For list

MySql Limitations??

hi... I need to store something like a couple of million rows is a MySql table. Is that ok or do I have to split them up. I intend to index each of the columns that I will need to access so as to speed up access. Insertion will be done only when there is very little or no load on the server

Running Two Different Versions of MySQL

Hi, I have a machine [Solaris 9 x86] that has MySQL v3.22.xx installed. I am not familiar with the applications [quite a few] that use this instance. I need to install an application that requires a newer version of MySQL. I plan to install v5.x. separately - rather than upgrade the old

Re: MySql GUI

At 12:31 -0700 29/5/06, AndrewMcHorney wrote: I understand that there is a free gui that will allow an administrator or user to create databases and maybe even add, update and modify rows in tables. It is something like MySQL Controller. Does this still exist and what is the link? Maybe

Re: MySql GUI

On Monday 29 May 2006 21:31, AndrewMcHorney wrote: Hello I understand that there is a free gui that will allow an administrator or user to create databases and maybe even add, update and modify rows in tables. It is something like MySQL Controller. Does this still exist and what is the link?

Re: MySql GUI

AndrewMcHorney wrote: Hello I understand that there is a free gui that will allow an administrator or user to create databases and maybe even add, update and modify rows in tables. It is something like MySQL Controller. Does this still exist and what is the link? Andrew Hi - MySQL

Re: MySql Limitations??

Harish TM wrote: hi... I need to store something like a couple of million rows is a MySql table. Is that ok or do I have to split them up. I intend to index each of the columns that I will need to access so as to speed up access. Insertion will be done only when there is very little or

Re: MySql GUI

At 14:02 -0700 29/5/06, Dan Trainor wrote: I highly suggest staying away from PHPMyAdmin. Why in particular? -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ The world is proof that God is a committee. -- Bob Stokes -- MySQL General Mailing List For list archives:

Re: ugly SQL for a report...

Cory, One way to lose the duplicate queries is to assign the count per pnr.id to a user var, and calculate via that value: SELECT pnr.ID AS ID_pnr, pnr.reservationdatetime, pnr.conf_number, pnr.created_by, ( SELECT @pp_cnt := COUNT(1) FROM pnr_passengers pp WHERE pp.ID_pnr = pnr.ID

Re: i'm at a complete loss?

Thanks Greg, I did try that here is what happened. boxster# mysql -u root -p Enter password: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) boxster# So I really just don't know what to do from here. I'm up for any ideas if any one has 'em. On 5/29/06, Greg

Re: i'm at a complete loss?

Log into the server, and type, use mysql; without the quotes. Look at the user table - that defines what user can connect to the database, the ip addresses they can use, and the password they must provide. For example, you could enter, INSERT INTO USER (host, user, password) values

Re: i'm at a complete loss?

When I log in using mysql which by the way is the only user that I can log into with I get snip mysql USE mysql; ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'mysql' /snip I have to log in to using mysql -u mysql and it drops me into the mysql command line. It seems as

RE: i'm at a complete loss?

Hi Daniel, Sounds like there are a few records missing out of the mysql tables. Did you run mysql_install_db? http://dev.mysql.com/doc/refman/4.1/en/unix-post-installation.html Regards --- ** _/ ** David Logan

Re: i'm at a complete loss?

Yeah when I ran it the first time I get this message: snip boxster# /usr/local/bin/mysql_install_db --user=mysql Installing all prepared tables Fill help tables To start mysqld at boot time you have to copy support-files/mysql.server to the right place for your system PLEASE REMEMBER TO SET A

Re: i'm at a complete loss?

Just for shits and giggles I tried boxster# /usr/local/bin/mysql_install_db --user=root and then tried logging in as root mysql -u root and now it says snip ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) /snip I googled that and found no resolve to

RE: i'm at a complete loss?

Hi Daniel, Is this actually the instance that you have just installed? I know that sounds trite but if it is running during the installation maybe it hasn't been able to start the new server and you are still trying to connect to the old one. Kill the server currently running, restart it and try

Re: i'm at a complete loss?

Nah, that's not the case my friend. I made sure that when I installed mysql that the old one was completely removed. But, I do think I figured something out. I was reading the MySQL documentation and it said that if I was having problems with mysql_install_db that I should move the mysql

RE: i'm at a complete loss?

Ah, this is a little different. I know there are a couple of OS's (redhat AFAIK) that place the mysql socket in a different location http://dev.mysql.com/doc/refman/4.1/en/problems-with-mysql-sock.html Your socket has probably been placed in a spot where the server is not expecting it. You may

Re: i'm at a complete loss?

Well, good news! after renaming the mysql to mysql.old and running mysql_install_db --user=mysql and then restarting the machine I am now able to run mysql as root by typing mysql -u root -p and just hitting enter when prompted for a password. Now I guess I just got to set the password

Re: MySql Limitations??

Hi We have tables of 1.5M rows 25 fields, in heavy use with frequent inserts and updates on a P4 with 1GB of RAM. Performance is fine, and the table size is 400MB, so you should not have any problems. Peter On 29/05/06, Chris W [EMAIL PROTECTED] wrote: Harish TM wrote: hi... I need

select 9000000000000.10*19.80 wrong result 178200000000001.97

Hi, select 9.10*19.80 wrong result 1782001.97 if = 9000.10*19.80 right result178201.98 This is similiar to sum(qty*unitpri) result . Best regards Shuming Wang

Re: where more than 2 column not in a table

CREATE TABLE `club_get_match` ( `id_match` bigint(100) NOT NULL default '0', `name_club` varchar(30) NOT NULL default '', `status` varchar(30) default NULL, `prediction_match` varchar(5) NOT NULL default '', `equal_match` char(3) NOT NULL default '', `score` varchar(50) default NULL,