search

partkeepr / PartKeepr

Open Source Inventory Management
http://www.partkeepr.org
GNU General Public License v3.0
1.38k stars 401 forks source link

PostreSQL 8.4: SQL error after login into Partkeepr 0.1.8 #268

Closed apex- closed 11 years ago

apex- commented 11 years ago

The following SQL exception is thrown after a login into Partkeepr 0.1.8 (does not happen with Partkeepr 0.1.7):

Here is the full report of this event:

SQLSTATE[42803]: Grouping error: 7 ERROR: column "p0.id" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: SELECT p0.id AS id0, COUNT(p1_.id) AS sclr1 FROM PartAttach... ^

Exception

PDOException

Backtrace

PDOException

Request

{"method":"GET","headers":{"session":"d1rgo6q1c364no81ih26nq53k4"},"jsonData":null}

Response

{"status":"systemerror","success":false,"exception":{"message":"SQLSTATE[42803]: Grouping error: 7 ERROR: column \"p0.id\" must appear in the GROUP BY clause or be used in an aggregate function\nLINE 1: SELECT p0.id AS id0, COUNT(p1.id) AS sclr1 FROM PartAttach...\n ^","exception":"PDOException","backtrace":"#0 \/usr\/share\/pear\/Doctrine\/DBAL\/Connection.php(628): PDOStatement->execute()\n#1 \/usr\/share\/pear\/Doctrine\/ORM\/Query\/Exec\/SingleSelectExecutor.php(46): Doctrine\DBAL\Connection->executeQuery('SELECT p0.id A...', Array, Array, NULL)\n#2 \/usr\/share\/pear\/Doctrine\/ORM\/Query.php(260): Doctrine\ORM\Query\Exec\SingleSelectExecutor->execute(Object(Doctrine\DBAL\Connection), Array, Array)\n#3 \/usr\/share\/pear\/Doctrine\/ORM\/AbstractQuery.php(595): Doctrine\ORM\Query->_doExecute()\n#4 \/usr\/share\/pear\/Doctrine\/ORM\/AbstractQuery.php(420): Doctrine\ORM\AbstractQuery->execute(Array, 1)\n#5 \/home\/tbruese\/partkeepr\/src\/backend\/PartKeepr\/Part\/PartManager.php(176): Doctrine\ORM\AbstractQuery->getResult()\n#6 \/home\/tbruese\/partkeepr\/src\/backend\/PartKeepr\/Part\/PartManager.php(109): PartKeepr\Part\PartManager->getAttachmentCounts(Array)\n#7 \/home\/tbruese\/partkeepr\/src\/backend\/PartKeepr\/Manager\/AbstractManager.php(108): PartKeepr\Part\PartManager->getResult(Object(Doctrine\ORM\Query))\n#8 \/home\/tbruese\/partkeepr\/src\/backend\/PartKeepr\/Part\/PartService.php(24): PartKeepr\Manager\AbstractManager->getList(Object(PartKeepr\Manager\ManagerFilter))\n#9 \/home\/tbruese\/partkeepr\/src\/backend\/PartKeepr\/Service\/ServiceManager.php(123): PartKeepr\Part\PartService->get()\n#10 \/home\/tbruese\/partkeepr\/frontend\/rest.php(56): PartKeepr\Service\ServiceManager->call()\n#11 {main}"},"timing":0.11868190765381}

Server Configuration

doctrine_orm_version: 2.2.0 doctrine_dbal_version: 2.2.1 doctrine_common_version: 2.2.0 php_version: 5.3.3 maxUploadSize: 8388608 availableImageFormats: A,AI,ART,ARW,AVI,AVS,B,BGR,BMP,BMP2,BMP3,BRF,BRG,C,CALS,CAPTION,CIN,CIP,CLIP,CMYK,CMYKA,CR2,CRW,CUR,CUT,DCM,DCR,DCX,DDS,DFONT,DNG,DOT,DPS,DPX,EPDF,EPI,EPS,EPS2,EPS3,EPSF,EPSI,EPT,EPT2,EPT3,ERF,FAX,FITS,FRACTAL,FTS,G,G3,GBR,GIF,GIF87,GRADIENT,GRAY,GRB,HALD,HISTOGRAM,HRZ,HTM,HTML,ICB,ICO,ICON,INFO,INLINE,IPL,ISOBRL,JNG,JP2,JPC,JPEG,JPG,JPX,K,K25,KDC,LABEL,M,M2V,M4V,MAP,MAT,MATTE,MIFF,MNG,MONO,MOV,MP4,MPC,MPEG,MPG,MRW,MSL,MSVG,MTV,MVG,NEF,NULL,O,ORF,OTB,OTF,PAL,PALM,PAM,PATTERN,PBM,PCD,PCDS,PCL,PCT,PCX,PDB,PDF,PDFA,PEF,PFA,PFB,PFM,PGM,PGX,PICON,PICT,PIX,PJPEG,PLASMA,PNG,PNG24,PNG32,PNG8,PNM,PPM,PREVIEW,PS,PS2,PS3,PSD,PTIF,PWP,R,RADIAL-GRADIENT,RAF,RAS,RBG,RGB,RGBA,RGBO,RLA,RLE,SCR,SCT,SFW,SGI,SHTML,SR2,SRF,STEGANO,SUN,SVG,SVGZ,TEXT,TGA,THUMBNAIL,TIFF,TIFF64,TILE,TIM,TTC,TTF,TXT,UBRL,UIL,UYVY,VDA,VICAR,VID,VIFF,VST,WBMP,WMF,WMV,WMZ,WPG,X,X3F,XBM,XC,XCF,XPM,XPS,XV,XWD,Y,YCbCr,YCbCrA,YUV

apex- commented 11 years ago

I have just tested Partkeepr 0.1.8 on PostgreSQL 9.1.7. Same problem...

Drachenkaetzchen commented 11 years ago

Will verify soon. Note to myself: Write unit test for this and make the unit tests run on MySQL+PostgreSQL 8+9, probably also MSSQL and others.

rphoover commented 11 years ago

In src/backend/PartKeepr/Part/PartManager.php, line 170 needs to have p.id added to the GROUP BY clause. See patch below:

--- PartManager.php.orig 2012-11-05 08:31:13.000000000 -0700 +++ PartManager.php 2013-02-13 14:45:58.000000000 -0700 @@ -167,7 +167,7 @@ return array(); }

apex- commented 11 years ago

Yes that fixes the problem. Thank you very much. I would also appreciate it if there would be some integration tests for the databases actually supported by partkeepr. Also before doing a release at least some basic checks should be carried out.

Drachenkaetzchen commented 11 years ago

That could be done with unit tests; however, there is a coverage of maybe 1% of the whole system. The project is in desperate need of more developers, I simply can't handle things alone anymore.

getSurreal commented 11 years ago

I hear you Timo. Let me know how I can help.

Drachenkaetzchen commented 11 years ago

@getSurreal you can help by:

Drachenkaetzchen commented 11 years ago

As mentioned in bug #308 , PostgreSQL support will be dropped from my side. As I am still the only one active developer and I have virtually no idea about PostgreSQL, I can't support PostgreSQL any longer. However, I'm happy to integrate all patches being sent to me.

However, the original issue described in this bug has been applied, and I'll be closing the bug now.