NMS and Postgres - Problems with case-sensitive Tablenames

[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?

[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?

[calguy1000]

Error(0) indicates no error.

[zappodrom]

Error(0) indicates no error.

OK for that. Please take a look at the complete Debug-Output: http://nopaste.info/1dbb80e4e4.html

Thanks

[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?

[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.

[alby]

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

[zappodrom]

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.

[alby]

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

[GBR]

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

[Pierre M.]

Hello,

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.

[calguy1000]

I wrote about my oppinions about CMSMS and postgres here:

http://calguy1000.com/Blogs/15/60/why-i-don-t-develop-for-postgres.html

[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.

[GBR]

I wrote about my oppinions about CMSMS and postgres here:

http://calguy1000.com/Blogs/15/60/why-i-don-t-develop-for-postgres.html

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

[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.