New install problem

• • 
  • 6 Posts
  shinji Reply #1, 18 years, 10 months ago

  After completing a new install of Modx I get this when I try to visit the main page of the website.
  
  ? MODx Parse Error ?
  MODx encountered the following error while attempting to parse the requested resource:
  ? Execution of a query to the database failed ?
  ? ? ? SQL: SELECT DISTINCT sc.id,sc.pagetitle,sc.description,sc.parent,sc.alias FROM `shinji`.modx_site_content sc LEFT JOIN `shinji`.modx_document_groups dg on dg.document = sc.id LEFT JOIN `shinji`.modx_documentgroup_names dgn ON dgn.id = dg.document_group WHERE sc.parent = ’0’ AND sc.published=1 AND sc.deleted=0 AND (1=’1’ OR NOT(dgn.private_webgroup<=>1)) ORDER BY sc.menuindex ASC;?
  
  
  I have shell access so I executed the command directly onto the database and ended up with this error.
  
  ERROR 1: Can’t create/write to file ’/var/tmp/#sql_373b_0.MYD’ (Errcode: 17)
  
  
  Any suggestions?
  
  BTW, /var/tmp is chmodded 0777 so it should of been writable.? I mean come on.
  
  My website url: http://shinji.chaosnet.org/modx/
  I am running version 2.0 RC3
  
  The straight Etomite install works fine.
  
  *edit*
  I checked out the query some more and dropped the keyword DISTINCT. Now it works but since it is supposed to only give unique names I think this might have an adverse effect if I use it in the script. Can anyone give me a modified query for the various SELECT DISTINCT statements? I was reading that there is a way to make it faster and not use the DISTINCT keyword.
• • 
  • 1,732 Posts
  xwisdom Reply #2, 18 years, 10 months ago

  What version of MySQL are you running?
  xWisdom
  www.xwisdomhtml.com
  The fear of the Lord is the beginning of wisdom:
  MODx Co-Founder - Create and do more with less.
• • 
  • 6 Posts
  shinji Reply #3, 18 years, 10 months ago

  Quote from: xwisdom at Jun 26, 2005, 06:33 AM

  
  What version of MySQL are you running?
  

  
  mysql Ver 4.0.24 for netbsd (alpha)
  
  
  BTW, I went ahead and did a test run of Modx without the DISTINCT keyword in the sql queries. I edit manager/includes/document.parser.class.inc.php and changed the 6 references to the SELECT DISTINCT command to be just plain SELECT and Modx started working fine. However I don’t know of any of the possible side effects to removing the keyword. I reverted the file back so that you can see the error on my website.
• • 
  • 1,732 Posts
  xwisdom Reply #4, 18 years, 10 months ago

  Now that’s strange.
  
  It would appear to me that you’re having some problems with the installation of MySQL
  
  The DISTINCT keyword is used when working with secure web pages. Maybe you can try testing some select distinct statements on your mysql server and see if it generates the same message. I’ve tested the select distinct on Mysql 3.23 and hight and it worked ok.
  
  
  xWisdom
  www.xwisdomhtml.com
  The fear of the Lord is the beginning of wisdom:
  MODx Co-Founder - Create and do more with less.
• • 
  • 6 Posts
  shinji Reply #5, 18 years, 10 months ago

  Quote from: xwisdom at Jun 26, 2005, 03:22 PM

  
  Now that’s strange.
  
  It would appear to me that you’re having some problems with the installation of MySQL
  
  The DISTINCT keyword is used when working with secure web pages. Maybe you can try testing some select distinct statements on your mysql server and see if it generates the same message. I’ve tested the select distinct on Mysql 3.23 and hight and it worked ok.
  
  
  

  
  So you are saying it might be an installation problem with mysql?? If so I will post that as a possible reason for this issue on the internal boards so that the admin will see it.
  
  This is an extremely stripped down query.? It only uses SELECT with the DISTINCT QUERY and FROM (so that it knows what table).
  
  SELECT DISTINCT sc.id,sc.pagetitle,sc.description,sc.parent,sc.alias FROM `shinji`.modx_site_content sc;
  
  This causes the same error.? Once again.? Dropping the DISTINCT keyword allows the query to run.? 3 others that are connecting to the same server also get the same error with other queries.? Namely ones using ORDER BY or GROUP BY in the query.? Dropping that part always makes it so that it works but the effect is obvious.? Now that is getting off the topic at hand though so...
  
  
  *edit*
  I don’t run the server. This is a server run by another group of people who have been very nice. They previously were running Mysql 3.23 and Netbsd 1.62 then upgraded to Netbsd 2.02 and Mysql 4.0.24. That is when the issue cropped up. We don’t use secure websites (no https).
• • MODX Staff
  • 10,725 Posts
  opengeek Reply #6, 18 years, 10 months ago

  Quote from: shinji at Jun 26, 2005, 03:39 PM

  
  So you are saying it might be an installation problem with mysql? If so I will post that as a possible reason for this issue on the internal boards so that the admin will see it.

  
  Yes, this is a problem with MySQL. Here is a posting about a similar problem and solution by changing a simply mysql.ini setting -- http://bugs.mysql.com/bug.php?id=9551
  Jason Coward
  Chief Architect @ MODX
  http://www.jasoncoward.com | http://twitter.com/drumshaman | https://github.com/opengeek
• • 
  • 6 Posts
  shinji Reply #7, 18 years, 10 months ago

  Thanks. I have posted a message appropriately to the internal boards of the server I am hosted on. Hopefully this will get resolved.
• • 
  • 1,732 Posts
  xwisdom Reply #8, 18 years, 10 months ago

  I think the problem is releated to creating temporary tables in MySQL. According to MySQL docs in some case whenever the DISTiNCT keyword is used with ORDER BY a temporary table is created.
  
  I think we might be removing the distinct keyword in TP3 to speed things up a bit
  xWisdom
  www.xwisdomhtml.com
  The fear of the Lord is the beginning of wisdom:
  MODx Co-Founder - Create and do more with less.
• • 
  • 6 Posts
  shinji Reply #9, 18 years, 10 months ago

  Quote from: xwisdom at Jun 26, 2005, 08:27 PM

  
  I think the problem is releated to creating temporary tables in MySQL. According to MySQL docs in some case whenever the DISTiNCT keyword is used with ORDER BY a temporary table is created.
  
  I think we might be removing the distinct keyword in TP3 to speed things up a bit
  

  
  So are you saying it is safe to change the sql statements and remove DISTINCT keyword from them?
• • 
  • 1,732 Posts
  xwisdom Reply #10, 18 years, 10 months ago

  Quote from: shinji at Jun 26, 2005, 08:47 PM

  
  So are you saying it is safe to change the sql statements and remove DISTINCT keyword from them?
  

  
  Not as is. TP3 will have some improvements that will not requuire the DISTINCT keyword in most cases
  xWisdom
  www.xwisdomhtml.com
  The fear of the Lord is the beginning of wisdom:
  MODx Co-Founder - Create and do more with less.

This discussion is closed to further replies. Keep calm and carry on.