http://coldfusion.com logo
Join Slack
Powered by
Yo. Any reason why on some of our Lucee instances ...
# lucee
a
Yo. Any reason why on some of our Lucee instances the 
cf_session_data
table is created as:
Copy code
CREATE TABLE `cf_session_data` (
  `expires` varchar(64) NOT NULL,
  `cfid` varchar(64) NOT NULL,
  `name` varchar(255) NOT NULL,
  `data` longtext NOT NULL,
  UNIQUE KEY `ix_cf_session_data` (`cfid`,`name`,`expires`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
And on others:
Copy code
CREATE TABLE `cf_session_data` (
  `expires` varchar(64) NOT NULL,
  `cfid` varchar(64) NOT NULL,
  `name` varchar(255) NOT NULL,
  `data` longtext NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
All are MySQL 8, Lucee 5.3.9.160. One difference is that the ones that have the KEY are driven by a commandbox docker image, the others are just driven by the standard lucee docker image. I can't think why that would matter though? I'm not so worried about the charset/collation stuff, but I am concerned about the lack of unique key. I have found this: https://docs.lucee.org/guides/Various/FAQs/technical-FAQs/database-session.html#tip-manually-check-cf_session_data-table-for, but that alludes to how the table might not be set up properly, rather than why it might not be set up properly. Other googling only turned up with the same issue; no actual answers.
z
a
right so it mighta just spat an error out somewhere that we didn't know to look for, and shrugged.
😐
That explains that then.
there's a chance the user the DSN is using doesn't have perms to alter tables (which oughtn't be that unusual, I should think)
But I would have thought that it would struggle to create the table at all, if it couldn't create an index. Hrm. However I am now wondering why the user we use to connect to the DB has enough perms to create tables?!
Confirmed. We got an error when it tried to create. Trying again, it's because of perms, and that is legit. @zackster your error handling there is swallowing some useful stuff, eg the original exception includes this useful info:
Copy code
ERROR 1142 (42000): ALTER command denied to user
But because that code you pointed to creates a new exception, and doesn't bother to include the previous one, all we end up with is:
Copy code
Failed to create unique index on cf_session_data;lucee.runtime.exp.DatabaseException: Failed to create unique index on cf_session_data
Which omits the useful stuff. I think as a rule if you are catching an exception and bubbling another one up, it'd pay to include the original exception in the second one. Or... hey... just don't monkey with the original exception as that's the one that is useful? Anyway, problem "solved"... thanks for pointing me in the right direction there! I wonder if there ought to be a rethink around having Lucee create that table? I'm surprised our DSNs can even create the table, let alone the index, which apparently needs further perms still. And I'd've thought in general DSN users would only be able to perform CRUD operations on table data. At the very least the docs should be tweaked on https://docs.lucee.org/guides/Various/FAQs/technical-FAQs/database-session.html> I also note it only discusses setting up DSNs in the Admin UI, which would be the exception rather than the rule these days, wouldn't it, when they can be done in Application.cfc?
z
yeah the "new" exception needs at least the cause added. personally, i'm a big fan of were possible fail early, failing hard, rather than limping on.
✅ 2
one thing whičh makes this challenging is we don't have per an application level admin UI, where we could add validation
a
Oh yeah
I mean you could just say "if you wanna use DB-based session storage, create a table like this [SQL here]"
👍 1
rather than try to be clever, but in a way that will be prone to failure
g
For the doc link provided by @Adam Cameron, There is a section "TIP: manually check...." Any "good / real" reason why these aren't the defaults? I am aware that creating indexes, adds load to a DB server. But if your data is large - you surely want the indexes??? And if it isn't large, is adding a couple of indexes as a default... is it a "genuine" problem? I will create a PR for this page - just adding in (a refined version of_ Ensuring that the user used by the DSN, to create the database, has appropriate rights. That at the moment Lucee does displays; 
Failed to create unique index on cf_session_data;lucee.runtime.exp.DatabaseException: Failed to create unique index on cf_session_data
and that it may be caused by the user that the DSN is using, not having appropriate rights, ensure it has ALTER rights. checked. ---- Also clean up the text just above the example SQL code and ensure that the SQL provided uses LONGTEXT instead of TEXT.
z
I've created a ticket about improving the exception https://luceeserver.atlassian.net/browse/LDEV-4248
👍🏼 1
⭐ 1
m
Sorry for the late addition, have been out at a conference. Our lucee instances never have drop/create/alter permissions on the db, and I can't provide it to lucee either, as we also don't have access to accounts that have them. We have to pass structure changes to a dba team from a different organization to get reviewed an scheduled to have those changes made. IMO, Adams recommendation for providing what the recommended creates/alters are would help to solve this more than anything else. Not just for getting them created, but more importantly to verify the created match expectations.
z
the DDL to create the table(s) with indexes is listed on the FAQ page??
Open in Slack
PreviousNext
Powered by