Resolved Bug


This bug has been marked as resolved.

mysql error: [1052: Column 'ptID' in where clause is ambiguous]

Permalink 0 0 Browser Info Environment
In my sitemap,

After clicking advanced search -> customize Results:

When I added a custom attribute, I got this database error:

mysql error: [1052: Column 'ptID' in where clause is ambiguous] in EXECUTE("select p1.cID, pt.ctHandle from Pages p1 left join PagePaths on (PagePaths.cID = p1.cID and PagePaths.ppIsCanonical = 1) left join PageSearchIndex psi on (psi.cID = p1.cID) inner join CollectionVersions cv on (cv.cID = p1.cID and cvID = (select max(cvID) from CollectionVersions where cID = cv.cID)) left join PageTypes pt on (pt.ctID = cv.ctID) inner join Collections c on (c.cID = p1.cID) left join CollectionSearchIndexAttributes on (CollectionSearchIndexAttributes.cID = p1.cID) where 1=1 and ptID IS NULL and (REPLACE(CollectionSearchIndexAttributes.ak_Verkrijgingsperiode, "\n", "||") like '%||Voorjaar||%' ) and (p1.cPointerID < 1 or p1.cPointerID is null) and p1.cIsTemplate = '0' and p1.cIsActive = '1' and (p1.cIsSystemPage = 0) order by cDateModified desc limit 0,10 ")


Now /index.php/dashboard/sitemap/search/ aint working anymore because of the error
Status: Resolved
View Replies: View Best Answer
Remo replied on at Permalink Reply
Remo
What do you mean by custom attribute? Is it an attribute type you created yourself or just an attribute you've added yourself?

I'd assume that the searchForm method isn't properly implemented but I'd have to know the attribute type in order to be able to check the code.
nielsb85 replied on at Permalink Reply
In this case I created an attribute select. the handle was "verkrijgingsperiode"

So the type is select (default concrete attribute type) and the attribute was named with handle verkrijgingsperiode)

When I went to page search -> Customize results -> selected the additional attribute and saved it, the error came up and the page search page can't be used anymore.
Remo replied on at Permalink Reply
Remo
there are different types like text, select, number.
nielsb85 replied on at Permalink Reply
Yes I started to understand what you ment after I posted my message, sorry. Message has been altered.

Attribute Type was select.
Remo replied on at Permalink Reply
Remo
I'm afraid but I'm not able to reproduce this. Create a new select attribute and was able to use it in the page search screen without a problem.

I can see in your query that this is causing the problem:
ptID IS NULL


But unfortunately I have no clue where this code is added.
nielsb85 replied on at Permalink Reply
I dont think the problem is that ptID = null but that the table Pages and the table CollectionVersions both have a column ptID and in the query this is not prefixt by the table name.
Remo replied on at Permalink Reply
Remo
Yes kind of. That's because in previous version we had ptID only on the page object, it wasn't versioned.. In 5.6 (?) it became part of CollectionVersions..

We could fix your problem if we'd completely remove ptID from pages but we could also fix it by using proper aliases. Both things would work.

I'd actually prefer your solution, cleaning up the code seems to be a good idea in general but I'm not entirely sure if it's not in use in some old legacy code.
Remo replied on at Permalink Best Answer Reply
Remo
We might be able to use this code in /concrete/helpers/upgrade/version_5603(?).php

$db = Loader::db();
$columns = $db->MetaColumns('Pages');
if (isset($columns['PTID'])) {
    $db->Execute('alter table Pages drop column ptID');
}


you can create a file called fix.php in /tools and open it in your browser with /index.php/tools/fix/ and the column should be gone. But please don't do this in a production site without creating a backup first. Don't want you to blame me for breaking your site ;-)
nielsb85 replied on at Permalink Reply
I have tried adding more attributes to the list. This seems to work fine. Also with other select attributes.

On a side note: The attribute that breaks is the only one that has a handle that is written (by accident) with a cammelcase "Verkrijgingsperiode"

But I doubt that has annything to do with it.

I will try your solution, but I will have to make a copy of this project first as it only happens with this specific attribute and I am not able to reproduce it with any other attributes.

Thank you so far, Ill let you know if it is fixed with your solution.
nielsb85 replied on at Permalink Reply
This seems to fix the problem. I can now add that specific attribute to that list. No other errors found so far when the column was removed.

Thanks :)
Remo replied on at Permalink Reply
Remo
Great, thanks for confirming!
I'm not sure if it's the right version/branch in github but I've created a pull request for thishttps://github.com/concrete5/concrete5/pull/716...

Even if I couldn't reproduce your problem, an employee of mine was confused before because he was looking at ptID in Pages and it never changed.
mkly replied on at Permalink Reply
mkly
It doesn't look like we've been able to track down the cause of this issue. The fix here doesn't so much correct an underlying cause as correct the after state.

I see the pull requests for removing the table, but since there isn't a specific tie between the two I am going to close this unless there is some more information on the root cause.

Best Wishes,
Mike
Remo replied on at Permalink Reply
Remo
Mike, I'm sorry but you're wrong, there's definitely a connection between the two. It fixed the issue for me and the original bug reporter.

When you have a SQL query with two tables and both contain the same column, you have to use "Table.Column" instead of just "Column". Instead of adding "Table.", we can also remove one column.

I just checked the master branch on github, there issue is still here. Inhttps://github.com/concrete5/concrete5/blob/master/web/concrete/core... there's a filter which adds ptID without an alias. This will cause a problem unless you add the table name (or its alias) or merge the pull request I've created

Either way, it's definitely a bug in the core!
mkly replied on at Permalink Reply
mkly
Indeed you are absolutely correct. Bug status revised.

Best Wishes,
Mike
andrew replied on at Permalink Reply
andrew
Fixed in github and will be released in 5.6.1

concrete5 Environment Information

# concrete5 Version
5.6.0.2

# concrete5 Packages
None

# concrete5 Overrides
controllers/dashboard, controllers/mobile_main.php, controllers/page_types_mobile, controllers/main.php, controllers/page_types, elements/users, helpers/public_asset_library.php, helpers/stats.php, helpers/twitterfy.php, helpers/food_categorie.php, helpers/slug.php, helpers/site_mail.php, helpers/show_breadcrumb.php, helpers/shorter.php, js/dashboard_functions.js, libraries/view.php, mail/share_mobile.php, mail/mfc_newRegistration.php, mail/tip.php, mail/startProductCode.php, mail/mfc_shareProduct.php, mail/mfc_newProduct.php, mail/mfc_newRegistrationMfc.php, mail/proeven.php, mail/share.php, models/product, models/page, models/verkoop, single_pages/dashboard, themes/tmp.php, themes/mobile_foodcast, themes/foodcast, tools/users, tools/generate_vimeo.php, tools/image-delete.php, tools/edit_collection_popup.php

# Server Software
Apache/2.2.22 (Amazon)

# Server API
apache2handler

# PHP Version
5.3.13

# PHP Extensions
apache2handler, bz2, calendar, Core, ctype, curl, date, dom, ereg, exif, fileinfo, filter, ftp, gd, gettext, gmp, hash, iconv, imap, json, ldap, libxml, mbstring, mcrypt, mhash, mysql, mysqli, OAuth, openssl, pcre, PDO, pdo_mysql, pdo_sqlite, Phar, Reflection, session, shmop, SimpleXML, soap, sockets, SPL, sqlite3, standard, tokenizer, wddx, xml, xmlreader, xmlrpc, xmlwriter, xsl, zip, zlib.

# PHP Settings
max_execution_time - 30
log_errors_max_len - 1024
max_file_uploads - 20
max_input_nesting_level - 64
max_input_time - 60
max_input_vars - 1000
memory_limit - 128M
post_max_size - 8M
safe_mode - Off
safe_mode_exec_dir - <i>no value</i>
safe_mode_gid - Off
safe_mode_include_dir - <i>no value</i>
sql.safe_mode - Off
upload_max_filesize - 2M
ldap.max_links - Unlimited
mysql.max_links - Unlimited
mysql.max_persistent - Unlimited
mysqli.max_links - Unlimited
mysqli.max_persistent - Unlimited
pcre.backtrack_limit - 1000000
pcre.recursion_limit - 100000
session.cache_limiter - nocache
session.gc_maxlifetime - 7200
soap.wsdl_cache_limit - 5
safe_mode_allowed_env_vars - PHP_
safe_mode_protected_env_vars - LD_LIBRARY_PATH

Browser User-Agent String

Mozilla/5.0 (Windows NT 6.1; WOW64; rv:16.0) Gecko/20100101 Firefox/16.0