Page 1 of 2
NMS and Postgres - Problems with case-sensitive Tablenames
Posted: Tue Jun 02, 2009 12:51 pm
by zappodrom
Hi out there,
I use NMS V2.2.2 and Postgres 8.3. The NMS subscribe form brought me > 70 Users. Now I want to write a Newsletter for the subscribers but I can't see any Mailadress in the userlist.
While searching for a solution I found the brief notes in the wiki.
Code: Select all
If you move a site to a new server, and NMS no longer sees your lists,
the reason may be that the new server has case-sensitive database tables.
If you're using MySQL, you can add the following line to your my.cnf, which will fix it:
lower_case_table_names=1
I think case-sensitive tablenames are the problem for me too, but how can I solve the Problem?
Is there a fix or a configuration option like above for Postgres too?
Re: NMS and Postgres - Problems with case-sensitive Tablenames
Posted: Tue Jun 02, 2009 1:49 pm
by zappodrom
Hi,
getting some more information for the helping people out there
Code: Select all
CMS Made Simple Version: 1.5.2
Installed Modules:
* CMSMailer: 1.73.14
* FileManager: 0.4.3
* MenuManager: 1.5.3
* ModuleManager: 1.2.1
* News: 2.9.2
* nuSOAP: 1.0.1
* Printing: 0.2.6
* Search: 1.5.2
* ThemeManager: 1.0.8
* TinyMCE: 2.4.11
* CGExtensions: 1.15.1
* NMS: 2.2
* FormBuilder: 0.5.11
* FormBrowser: 0.2.3
* Captcha: 0.4
Current PHP Version: 5.2.6-0.1~lenny1
PHP Configuration:
* Maximum Post Size: 8M
* Maximum Upload Size: 2M
* PHP Memory Limit: 128M
* PHP Safe Mode: Off
* Maximum Execution Time: 30 seconds
* Session Save Path: /var/lib/php5
PostgreSQL Server Version:
Server Software: Apache/2.2.9 (Debian) mod_auth_pgsql/2.0.3 PHP/5.2.6-0.1~lenny1 with Suhosin-Patch mod_ssl/2.2.9 OpenSSL/0.9.8g
Server API: APACHE2HANDLER
Server OS: Linux v 2.6.26-1-686 on i686 architecture
Directory Permissions:
* tmp/cache - 0777
* tmp/templates_c - 0777
* uploads - 0775
* modules - 0775
Browser: Mozilla/5.0 (X11; U; Linux x86_64; de; rv:1.9.0.7) Gecko/2009030423 medienservice-weerts.de/1.0 (GNU/Linux) Firefox/3.0.7
The Debug-Output from Adminpanel:
Code: Select all
Generated in 1,242205 seconds by CMS Made Simple using 44 SQL queries and 10814656 bytes of memory (peak memory usage was 11368280
Debug: (0.121176) - (2568908)
<br>
(postgres7): SELECT sitepref_name, sitepref_value from cms_siteprefs<br>
Error (0): <br>
Debug: (0.135165) - (2660700)
<br>
(postgres7): SELECT * FROM cms_userplugins<br>
Error (0): <br>
Debug: (0,161531) - (3161640)
<br>
(postgres7): SELECT * FROM cms_modules WHERE admin_only = 0 AND active = 1 ORDER by module_name<br>
Error (0): <br>
Debug display of 'End of include':(0,569541) - (8086688)
Debug: (0,578803) - (8087772)
<br>
(postgres7): SELECT c.content_id, c.content_name, c.content_alias, c.menu_text, c.titleattribute, c.hierarchy, c.metadata, c.id_hierarchy, c.prop_names, c.create_date AS c_create_date, c.modified_date AS c_date, c.cachable, c.last_modified_by,t.template_id, t.encoding, t.modified_date AS t_date FROM cms_templates t INNER JOIN cms_content c ON c.template_id = t.template_id WHERE c.content_alias = 'newsletter' AND c.active = 1<br>
Error (0): <br>
Debug: (0,58137) - (8087012)
<br>
(postgres7): SELECT MAX(modified_date) AS thedate FROM cms_content c<br>
Error (0): <br>
Debug display of 'Start Loading Hierarchy Manager':(0,583171) - (8086908)
Debug display of 'Load Content Operations':(0,583342) - (8086908)
Debug display of 'End Load Content Operations':(0,603198) - (8546584)
Debug display of 'starting tree':(0,603439) - (8547164)
Debug: (0,610611) - (8697444)
file needs loading
Debug: (0,615122) - (8740240)
<br>
(postgres7): SELECT * FROM cms_content WHERE parent_id = -1 ORDER BY hierarchy<br>
Error (0): <br>
Debug display of 'ending tree':(0,640156) - (9118652)
Debug display of 'End Loading Hierarchy Manager':(0,64037) - (9113524)
Debug: (0,640501) - (9113728)
start findNodeByTag
Debug: (0,64111) - (9113832)
end findNodeByTag
Debug: (0,651578) - (9262764)
<br>
(postgres7): SELECT template_id, template_name, template_content, stylesheet, encoding, active, default_template, modified_date FROM cms_templates WHERE template_id = '19'<br>
Error (0): <br>
Debug: (0,686717) - (10007616)
<br>
(postgres7): SELECT eh.tag_name, eh.module_name, e.originator, e.event_name, eh.handler_order, eh.handler_id, eh.removable FROM cms_event_handlers eh
INNER JOIN cms_events e ON e.event_id = eh.event_id
ORDER BY eh.handler_order ASC<br>
Error (0): <br>
Debug: (0,757185) - (10190556)
<br>
(postgres7): SELECT * FROM cms_content_props WHERE content_id = '25'<br>
Error (0): <br>
Debug: (0,763944) - (10200064)
<br>
(postgres7): SELECT DISTINCT A.css_id,A.media_type,B.assoc_order
FROM cms_css A, cms_css_assoc B
WHERE A.css_id = B.assoc_css_id
AND B.assoc_type = 'template'
AND B.assoc_to_id = '19'
ORDER BY B.assoc_order<br>
Error (0): <br>
Debug display of 'Start of Menu Manager Display':(0,767732) - (10271532)
Debug: (0,768465) - (10273356)
load properties called
Debug: (0,770902) - (10274236)
<br>
(postgres7): SELECT * FROM cms_content_props WHERE content_id = '15'<br>
Error (0): <br>
Debug: (0,777019) - (10277152)
<br>
(postgres7): SELECT * FROM cms_content_props WHERE content_id = '16'<br>
Error (0): <br>
Debug: (0,780978) - (10282860)
<br>
(postgres7): SELECT * FROM cms_content WHERE content_id = '34'<br>
Error (0): <br>
Debug: (0,782198) - (10290700)
load from data is loading properties
Debug: (0,782408) - (10290924)
load properties called
Debug: (0,784698) - (10293768)
<br>
(postgres7): SELECT * FROM cms_content_props WHERE content_id = '34'<br>
Error (0): <br>
Debug: (0,789714) - (10296460)
<br>
(postgres7): SELECT * FROM cms_content WHERE content_id = '35'<br>
Error (0): <br>
Debug: (0,793388) - (10308856)
<br>
(postgres7): SELECT * FROM cms_content_props WHERE content_id = '35'<br>
Error (0): <br>
Debug: (0,797949) - (10311120)
<br>
(postgres7): SELECT * FROM cms_content WHERE content_id = '36'<br>
Error (0): <br>
Debug: (0,802217) - (10323628)
<br>
(postgres7): SELECT * FROM cms_content_props WHERE content_id = '36'<br>
Error (0): <br>
Debug: (0,80964) - (10332200)
<br>
(postgres7): SELECT * FROM cms_content_props WHERE content_id = '17'<br>
Error (0): <br>
Debug: (0,813526) - (10334164)
<br>
(postgres7): SELECT * FROM cms_content WHERE content_id = '18'<br>
Error (0): <br>
<br>
(postgres7): SELECT * FROM cms_content_props WHERE content_id = '18'<br>
Error (0): <br>
Debug: (0,845909) - (10485400)
<br>
(postgres7): SELECT * FROM cms_content WHERE content_id = '19'<br>
Error (0): <br>
Debug: (0,849279) - (10497664)
<br>
(postgres7): SELECT * FROM cms_content_props WHERE content_id = '19'<br>
Error (0): <br>
Debug: (0,851341) - (10501004)
load properties called
Debug: (0,854652) - (10501316)
<br>
(postgres7): SELECT * FROM cms_content_props WHERE content_id = '21'<br>
Error (0): <br>
Debug: (0,858955) - (10509724)
<br>
(postgres7): SELECT * FROM cms_content WHERE content_id = '37'<br>
Error (0): <br>
*** Cut here, because the message exceeds the maximum allowed length ***
It seems like there is a problem with linebreaks in the queries. Can anyone tell me which file has to be modified to get things work?
Re: NMS and Postgres - Problems with case-sensitive Tablenames
Posted: Tue Jun 02, 2009 1:54 pm
by calguy1000
Error(0) indicates no error.
Re: NMS and Postgres - Problems with case-sensitive Tablenames
Posted: Tue Jun 02, 2009 2:30 pm
by zappodrom
calguy1000 wrote:
Error(0) indicates no error.
OK for that. Please take a look at the complete Debug-Output:
http://nopaste.info/1dbb80e4e4.html
Thanks
Re: NMS and Postgres - Problems with case-sensitive Tablenames
Posted: Wed Jun 03, 2009 6:06 am
by zappodrom
Hmm, if Error (0) indicates no error I could'nt find something wrong in the Debug-Output.
And also no hints in the logfiles of postgres and apache.
Is there another way to solve the problem?
Re: NMS and Postgres - Problems with case-sensitive Tablenames
Posted: Fri Jun 05, 2009 9:04 am
by zappodrom
Hi Calguy,
I've just tried the NMS-Module on another Server with MySQL-Support and with _exactly_ the same CMSms-Installation as on the original (Postgres-) Server (1:1-Copy). The only different is the Provider.
With MySQL the NMS works perfectly so I think there's really a problem with Postgres-Support. Is there a chance to get NMS running with postgres? Is there someone who has this working?
How can I get more verbosity to find some more error messages? $debug=true seems not bring enough information (for me) that help's me to get this solved.
I hope someone can help me.
Re: NMS and Postgres - Problems with case-sensitive Tablenames
Posted: Fri Jun 05, 2009 10:54 am
by alby
zappodrom wrote:
With MySQL the NMS works perfectly so I think there's really a problem with Postgres-Support. Is there a chance to get NMS running with postgres? Is there someone who has this working?
ok, today I test and report to Calguy
Alby
Re: NMS and Postgres - Problems with case-sensitive Tablenames
Posted: Fri Jun 05, 2009 11:15 am
by zappodrom
alby wrote:
zappodrom wrote:
With MySQL the NMS works perfectly so I think there's really a problem with Postgres-Support. Is there a chance to get NMS running with postgres? Is there someone who has this working?
ok, today I test and report to Calguy
Alby
OK, thanks Alby.
Until now, I've found maybe the beginning of an solution.
I've changed line 126 in function.admin_users_tab.php to this.
Code: Select all
#$query .= " GROUP BY A.userid LIMIT $start_record, $num_per_batch";
$query .= " GROUP BY A.userid, a.uniqueid, a.email, a.username, a.disabled, a.confirmed LIMIT $num_per_batch";
This lists the users but gives also some notice on top of the Userlist:
Code: Select all
Notice: Undefined index: pagecount in /var/www/www.foo.bar/tmp/templates_c/NMS^%%63^63A^63A268F8%%module_file_tpl%3ANMS%3Busers.tpl.php on line 45
Maybe this helps.
Re: NMS and Postgres - Problems with case-sensitive Tablenames
Posted: Fri Jun 05, 2009 1:22 pm
by alby
zappodrom wrote:
This lists the users but gives also some notice on top of the Userlist:
notice are not a problem (you see with active debug only)
Alby
Re: NMS and Postgres - NMS Does not Support PostgreSQL
Posted: Sun Sep 06, 2009 3:00 am
by GBR
zappodrom wrote:
alby wrote:
zappodrom wrote:
With MySQL the NMS works perfectly so I think there's really a problem with Postgres-Support. Is there a chance to get NMS running with postgres? Is there someone who has this working?
ok, today I test and report to Calguy
Alby
OK, thanks Alby.
Until now, I've found maybe the beginning of an solution.
I've changed line 126 in function.admin_users_tab.php to this.
Code: Select all
#$query .= " GROUP BY A.userid LIMIT $start_record, $num_per_batch";
$query .= " GROUP BY A.userid, a.uniqueid, a.email, a.username, a.disabled, a.confirmed LIMIT $num_per_batch";
This lists the users but gives also some notice on top of the Userlist:
Code: Select all
Notice: Undefined index: pagecount in /var/www/www.foo.bar/tmp/templates_c/NMS^%%63^63A^63A268F8%%module_file_tpl%3ANMS%3Busers.tpl.php on line 45
Maybe this helps.
It appears that the root cause is not the case of the table names, but rather the module's lack of support for PostgreSQL. Unfortunately this is too common a theme in CMS Made Simple.
zappodrom, I believe that you're on the right line.
godzone raised this in feature request #2593, where Calguy2000 seemed a little surprised that anyone would expect his module to support PostgreSQL and, worse, the fixes supplied by godzone have been removed from the feature request so I can't even apply them manually.
For a better result, refer to the changes proposed in this thread where the import of themes in to CMS Made Simple didn't work:
http://forum.cmsmadesimple.org/index.ph ... 94050.html
And this one for Front End Users:
http://forum.cmsmadesimple.org/index.ph ... 300.0.html
In both cases, the fix appears to have been incorporated in 1.6.
I will also update feature request #2593 to request that godzone's fixes are reposted with the intention of creating a set of patches.
Regards
Graeme
Re: NMS and Postgres - NMS Does not Support PostgreSQL
Posted: Tue Sep 08, 2009 3:29 pm
by Pierre M.
Hello,
GBR wrote:
It appears that the root cause is not the case of the table names, but rather the module's lack of support for PostgreSQL. Unfortunately this is too common a theme in CMS Made Simple.
Hopefully the ORM in 1.7 (and the return of SQLite) will make CMSms core and modules more DB agnostic, hence support for PostgreSQL better.
Pierre M.
Re: NMS and Postgres - Problems with case-sensitive Tablenames
Posted: Tue Sep 08, 2009 3:37 pm
by calguy1000
Re: NMS and Postgres - Problems with case-sensitive Tablenames
Posted: Tue Sep 08, 2009 5:19 pm
by Pierre M.
Thank you for this opinion in blogpost.
I stand optimist for 1.7 with the ORM and the return of SQLite making CMSms core more DB agnostic and paving the way for modules to be dbagnostic also. Because I think it the long run it could help module writers to abstract the db and stay in the API.
But I understand better MySQL specific dependancies of some modules, thanks to your post.
May be modules ratings should have a db field. Anyway the PostgreSQLovers (like me) should submit freindly patches to modules they want (taking care of not impacting MySQL functionality).
Pierre M.
Re: NMS and Postgres - Problems with case-sensitive Tablenames
Posted: Thu Sep 10, 2009 3:45 am
by GBR
Thanks Robert.
I accept your position on this. I don't have to agree, but I accept it. Just as I appreciate all the work that you and the other developers put in to the product.
Oh, and sorry about inflating your nick - I should have checked.
GBR
Re: NMS and Postgres - Problems with case-sensitive Tablenames
Posted: Tue Sep 22, 2009 10:47 am
by Pierre M.
Little thinking : db specific modules should state it (MySQL only) in their help/doc/forgedashboard.
For example guy1 writes StageBeforePublish module (an imaginary sortof backup-restore db) which is MySQL only implemented. Then guy2 can derive it and writes pgStageBeforePublish, a PostgreSQL port of same functionality. Each Dev maintain his own work and opinions.
Pierre M.