Engine Condition Pushdown Optimization. Index Condition Pushdown Optimization. Nested Join Optimization. Outer Join Optimization. Outer Join Simplification. Multi-Range Read Optimization. Constant-Folding Optimization. Function Call Optimization. Window Function Optimization. Row Constructor Expression Optimization. Avoiding Full Table Scans. Optimizing Subqueries with Materialization. Derived Condition Pushdown Optimization. Optimizing Performance Schema Queries. Optimizing Data Change Statements.
Optimizing Database Privileges. Other Optimization Tips. Primary Key Optimization. Foreign Key Optimization. Multiple-Column Indexes. Comparison of B-Tree and Hash Indexes. Optimizer Use of Generated Column Indexes. Optimizing Database Structure. Artefactual takes no responsibility for unexpected outcomes due to direct database manipulation, as this is not part of the intended use of the application.
Once again, please proceed cautiously, and at your own risk! Data backup. Once you enter the command, you will also be prompted for the database password you created for the MySQL user during installation.
If you are loading a backup from an older version of AtoM into a newer one, there are some additional steps - most importantly, running the AtoM database upgrade task, which will execute a series of migration scripts to update your database schema to what AtoM expects for the latest version. Additionally, please note that finding aids, cached XML, and digital objects are not stored in the database - instead, these are typically found in the uploads and downloads directories, found just below the root AtoM installation directory.
If you are loading your data into a different AtoM instance, you may need to migrate these directories separately. Please see the Upgrading and Data backup documentation for more information:.
Back to the top. The basic syntax to access the MySQL command prompt is like so:. An example: Assuming that your database name is atom and your user and password are both root , you could access the prompt like so:. Notice that there is a space between the -u and root , but NOT between the -p and the root password. Alternatively, you can leave no password following the -p, and you will be prompted to enter it by the command prompt before proceeding.
You can now input a SQL query directly. You can exit the MySQL command prompt at any time simply by typing exit. You will be returned to the unix command-line interface. We strongly recommend that you back-up all of your data prior to manipulating the database! If possible, you should test the outcome on a cloned development instance of AtoM, rather than performing these actions on a production site without testing them in advance.
See: Backing up the database. This section covers queries that are sometimes useful for database troubleshooting, tuning, logging, and maintenance activities. From the MySQL documentation :. DBAs can set the global SQL mode to match site server operating requirements, and each application can set its session SQL mode to its own requirements.
We do not recommend arbitrarily changing the SQL mode from those recommended in the Create the database step of the installation documentation. However, if you do need to make changes, there are two ways you can accomplish this. The first method is via SQL. To change the global SQL mode settings, use the following general pattern:.
Use commas to separate modes - an example:. You may need to restart MySQL after making changes. Run the following after exiting the MySQL command prompt:.
The second way of changing the SQL mode is via the separate mysqld configuration file. These steps are performed via the unix command-line - if you have already entere the MySQL command prompt, exit now. If you missed that step and have not previously created a file in this location, you can do so with the following:. You can then use the nano command-line text editor or the text editor of your choosing to open the file for editing:. With any new AtoM release that includes changes to the database, we include a schema migration script that, when run, will make the necessary changes in the the database to conform to the expected schema.
During the upgrade process , these migration scripts are typically executed when the upgrade task is run - see:. When seeking support, it can be useful to first check the current schema version of your database, so it can be compared against the expected version for the release. See: Application version for more information. However, in some cases you may want to check this via SQL query. From the MysQL documentation :. By default, this log is disabled in MySQL, and it is generally recommended to keep it disabled in a production installation, as it can rapidly grow quite large.
However, for troubleshooting, development, and performance tuning, it can be useful to see all queries going through MySQL. This should give you a file path and log name - you can exit the MySQL command prompt and use the unix command-line interface to navigate to the file and view its contents. Left running, the log file will grow rapidly as AtoM is used and may consume valuable system resources. To disable the log:. If the setting is enabled, but the AtoM server is not properly configured to use SSL, then login attempts will redirect users back to the homepage.
For more information, see:. Occasionally administrators will enable this setting without understanding the configuration prerequisites, and will find themselves locked out of the site, unable to disable the setting again to gain user interface access. When this happens, you can use SQL to disable the setting, thereby regaining access to your site.
Where XX will be an ID number. Replace XX in the following query with the returned ID number, and check the current setting configuration:. If the value shows 0, the setting is not enabled. Replace XX in the above example with the ID value returned in the first step. Additionally, if your default installation culture is not English, then change culture value in the example query above to the 2-letter ISO language code of your culture.
AtoM relies on Gearman in order to execute certain long-running tasks asynchronously to guarantee that web requests are handled promptly and work loads can be distributed across multiple machines. Examples of operations in AtoM that are handled by the job scheduler include moving or publishing archival description records; generating reports or finding aids ; managing PREMIS right inheritance , running most imports and exports via the user interface ; and more.
Occasionally, the atom-worker may stall mid-process, or there is currently no worker available to handle the job. When this happens, users will typically see a error message in the web browser. These issues can be often be resolved by restarting the job scheduler - for more information, see:. However, in some cases, the atom-worker may simply stall again - often when a particularly large job has been run, and available system resources have been exhausted before the job could complete.
AtoM does include a command-line task that can clear all running and queued jobs, as well as clear the job history from the database. However, if you have other jobs in the queue, they will all be lost with this command - as will all previous job history. Instead, SQL can be used to target a specific problematic job and remove it from the queue, allowing other jobs to remain. To do so, we first need to find the id of the job we want to kill, before deleting it from the database:. We can now use the id value of the target job to delete just that job from the database, like so:.
You will need to restart the job scheduler afterwards. The following queries do not relate to a specific entity type in AtoM - rather, they provide general information or global actions that affect numerous entities, or do not relate to specific entities at all. There are a few ways you can access the object IDs for your records. For archival description records, the first method is to export the target descriptions as a CSV file - on export, AtoM will populate the legacyId column of the resulting CSV with the object ID value for each row.
Here is an example:. Note that we can also perform the opposite query, for cases where you know the object ID perhaps from an earlier query against a related table , and you want to find the slug of the related record:. Consequently, if we know the title or authorized form of name of the target record, we can use that to look up the ID value. For example, with archival descriptions , you can use the following query to look up the object ID based on the title of the target description:. Here is an example of looking up the object ID of an authority record when we know the authorized form of name:.
Replace Actor name here with the authorized form of name of the target authority record. An example of using an accession number to look up the ID of an accession record :. In some cases, you may wish to replace the existing slugs in AtoM - particularly if they have been randomly generated because the user-supplied data from which the slug is normally derived e. Which one comes first when MySQL processes the query? Improve this question. RichardTheKiwi k 24 24 gold badges silver badges bronze badges.
Chris Abrams Chris Abrams Add a comment. Active Oldest Votes. Improve this answer. Marc B Marc B k 39 39 gold badges silver badges bronze badges. Capp Argo Capp Argo 1 1 gold badge 4 4 silver badges 11 11 bronze badges. Good to know, thanks, cristian! I was curious about this from a performance perspective.
This reply is long time ago. If ordering is done by using an index, this is very fast. Sign up or log in Sign up using Google. Sign up using Facebook.
0コメント