modx xpdo is taking a long time to refresh data

Question

I am using xpdo in MODX Revolution 2.7.3 to insert and extra data from custom tables in the same database as the modx installation. Everything works fine except that it takes about 20 minutes for changes in the tables to show up in the xpod query.

For example, I have a table of contacts with an adult field. The in a form I have a dropdown box where the options are the adult contact. It works fine but when you change the adult status of a contact the options in the dropdown takes 20 minutes to reflect the change.

See my code below

$class='Contacts';

$fields = array_keys($modx->getFields($class));

$collections = $modx->getCollection($class);

foreach($collections as $collection) {

    if($collection->get($fields[4])=='YES'){
    $output .= '<option value=' . $collection->get($fields[0]).'>'.$collection->get($fields[1])." ".$collection->get($fields[2]).'</option>';
    }

}


return $output;

There is only one table involved and the code for creating the table is:

    CREATE TABLE `cbdb_contacts` (
  `contactID` int(11) NOT NULL,
  `firstname` varchar(15) COLLATE utf8_unicode_ci NOT NULL,
  `lastname` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `dob` date NOT NULL,
  `adult` enum('YES','NO') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'NO',
  `mobile` text COLLATE utf8_unicode_ci NOT NULL,
  `landline` text COLLATE utf8_unicode_ci NOT NULL,
  `address` text COLLATE utf8_unicode_ci NOT NULL,
  `email` text COLLATE utf8_unicode_ci NOT NULL,
  `comments` longtext COLLATE utf8_unicode_ci NOT NULL,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

    -- Dumping data for table `cbdb_contacts`
--

INSERT INTO `cbdb_contacts` (`contactID`, `firstname`, `lastname`, `dob`, `adult`, `mobile`, `landline`, `address`, `email`, `comments`, `timestamp`) VALUES
(38, 'Tex', 'Brown', '2020-06-01', 'NO', '', '', '', '', '', '2020-07-12 18:34:19'),
(39, 'Mary', 'Brown', '2020-06-01', 'YES', '', '', '', '', '', '2020-07-06 19:03:23'),
(40, 'Pamela', 'Brown', '2020-06-01', 'YES', '', '', '', '', '', '2020-07-08 08:13:11'),
(41, 'Eddy', 'Green', '2020-06-01', 'NO', '', '', '', '', '', '2020-07-06 19:04:19'),
(42, 'Sheila', 'White', '2020-06-01', 'NO', '', '', '', '', '', '2020-07-12 18:54:03'),
(43, 'Dan', 'Black', '2020-06-01', 'NO', '', '', '', '', '', '2020-07-08 08:20:25'),
(134, 'Annete', 'Pray', '0000-00-00', 'NO', '', '', '', '', '', '2020-07-12 19:23:02'),
(133, 'Alex', 'Grey', '0000-00-00', 'NO', '', '', '', '', '', '2020-07-12 19:10:14'),
(132, 'Princess', 'Brown', '0000-00-00', 'NO', '', '', '', '', '', '2020-07-11 22:43:55'),
(131, 'Prince', 'Black', '0000-00-00', 'NO', '', '', '', '', '', '2020-07-11 22:39:22'),
(129, 'Tom', 'Smith', '0000-00-00', 'YES', '', '', '', '', '', '2020-07-11 22:34:32'),
(128, 'James', 'Dean', '0000-00-00', 'YES', '', '', '', '', '', '2020-07-11 22:14:19'),
(127, 'Peter', 'Paul', '0000-00-00', 'NO', '', '', '', '', '', '2020-07-11 22:08:52'),
(130, 'Tess', 'Logan', '0000-00-00', 'NO', '', '', '', '', '', '2020-07-11 22:38:35');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `cbdb_contacts`
--
ALTER TABLE `cbdb_contacts`
  ADD PRIMARY KEY (`contactID`),
  ADD KEY `firstname` (`firstname`) USING BTREE;

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `cbdb_contacts`
--
ALTER TABLE `cbdb_contacts`
  MODIFY `contactID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=135;
COMMIT;

Please provide the generated SQL statements, plus `SHOW CREATE TABLE` for the table(s) involved. — Rick James, Jul 13 '20 at 03:49
@Rick James I have edited the question and added the create table. Generated sql is:- `Select * FROM cbdb_contacts` — Sheils, Jul 13 '20 at 17:01
How many rows in the table? Let's see the table. What is the size of the table (`SHOW TABLE STATUS` will provide the gigabytes) — Rick James, Jul 13 '20 at 17:10
I have updated the question to provide the dummy data that I currently have in the table. Not sure who to run the SHOW TABLE STATUS query — Sheils, Jul 13 '20 at 17:19
What client do you use? Workbench? phpmyadmin? commandline "mysql"? something else? — Rick James, Jul 13 '20 at 17:20
The database is in phpmyadmin. But I am calling it from modx revo — Sheils, Jul 13 '20 at 17:27
phpmyadmin can probably show info about the table; try that. If not, go to the generic window that lets you run arbitrary SQL — Rick James, Jul 13 '20 at 17:33
yes I am still developing. Will have many more once it is in full use — Sheils, Jul 13 '20 at 17:34
Switch from MyISAM to InnoDB. Even so, 14 rows cannot cause anything to be slower than, say, 1 second, not 20 minutes. So, I don't think the database is at fault. — Rick James, Jul 13 '20 at 17:41
Switch from MyISAM to InnoDB had no effect. I think the cache is being done in modx — Sheils, Jul 13 '20 at 17:47
@Sheils, just as an experiment, can you try turning of the global cache? https://docs.modx.com/current/en/building-sites/settings/cache_disabled — Chris Haas, Jul 13 '20 at 17:51
The Query cache, in most case, should be turned off. `innodb_buffer_pool_size` is very important when using InnoDB: http://mysql.rjweb.org/doc.php/memory The MODX "cache" in your link is totally different. — Rick James, Jul 13 '20 at 18:02

Chris Haas · Accepted Answer · 2020-07-14T12:47:18.067

1

It appears that caching is enabled by default when using getCollection(), try passing false as the third parameter.

$collections = $modx->getCollection($class, null, false);

You might also have to pass an empty array as the second parameter, the documentation is a little conflicting.

$collections = $modx->getCollection($class, [], false);

Edit

Apparently snippet results are also cached and don't hit the database even, unless they are called using the no-cached syntax, [[!tag]]. Using the ! directive should bypass the caching of this specific snippet which is okay since this is such as minimal database query.

edited Jul 14 '20 at 12:47

answered Jul 12 '20 at 15:13

Chris Haas

53,986
12
141
274

Hi Chris, Thanks for responding. But sorry neither option seems to work. I have a table of contact with "adult" as one of the fields. The code in my question loop through the collection of contact ans check is the adult field(field[4]) is yes. If so the name and surname is added to a option list which is used in a select element on the webpage. I expected that if I change the adult status of a contact from YES to No in phpmyAdmin, then refresh the page the option list will change accordingly. But this is not happening – Sheils Jul 12 '20 at 18:47
If this is the table with 14 rows, I can't set that "cache" vs not could be the issue. – Rick James Jul 13 '20 at 17:42
I think a code to refresh the modx object is required.Just don't know what it is – Sheils Jul 13 '20 at 17:51
Hi Chris, I have set Cache default cacje_default to No. The change the adult status of one of the contact. Then try to view the site in chrome, firefox, firefox private mode, Edge and still observe the same problem – Sheils Jul 13 '20 at 18:06
Are you able to run raw SQL code and bypass modx completely, just for testing purposes? Could you try running a standard mySQLi query against the database just to see if that is picking things up? And I apologize, but I just have to ask, is there replication involved in this setup, where maybe something is out of sync because of a primary/replica sync delay? – Chris Haas Jul 13 '20 at 18:09
Chris I change the call to the snippet to unchache then back to cache and know it is working. Not sure if it is that or the global cache to a while – Sheils Jul 13 '20 at 18:21
Will wait a bit to re-test without global cache – Sheils Jul 13 '20 at 18:22
1

Oh, you are in a snippet. If this is all that is in your snippet, I see no reason why you can't invoke it as uncached always with a `[[!tag]]`. Otherwise, are you going to always control how data gets put into the database? If so, you should be able to invalidate/clear the cache on update/insert/delete/etc. If you are modifying outside the system, I would recommend create a custom page to be invoked by a special URL that you hid to purge the cache as needed. Caching is great, but only as great as the invalidation logic. – Chris Haas Jul 13 '20 at 18:51
Ok I have just checked after an hour and it still work. The snippet is called like this `` from within a chuck. It know works when new adult contact is entered from the browser side. Still have to refresh the page but once I write the javascript to refresh the select element after next contact is add it should work fine. Fingers crossed – Sheils Jul 13 '20 at 19:54
Chris let me know if you have further contribution before a close the question – Sheils Jul 13 '20 at 19:56
@Sheils, I've updated the answer above to include the results of the thread. Hopefully someone else in the future will find the information valuable, too. – Chris Haas Jul 14 '20 at 12:47
Thanks for Assisting guys – Sheils Jul 14 '20 at 16:52

modx xpdo is taking a long time to refresh data

1 Answers1